/**
 *  PURPOSE  : DefaultItemChanged for wave generation process
 *  AUTHOR   : Changed By Sumit On 28/11/2012 WM1ESUN006 
 *  Obj_name : w_wavegen
 */


package ibase.webitm.ejb.wms;

//changed by sumit on 03/10/12 imported class to find printer in system. start.
import java.awt.print.PrinterJob;
import javax.print.PrintService;
//changed by sumit on 03/10/12 imported class to find printer in system. end.
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;



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

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.SaleOrdItmGen;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

@javax.ejb.Stateless
public class WaveGenerationIC extends ValidatorEJB implements WaveGenerationICRemote, WaveGenerationICLocal{
	
	/**
	* The method defined with no parameter and returns nothing
	*/
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}
	
	
	/**
	 * The public method is used for itemchange of required fields which return called overloaded method
	 * Returns itemchange string in XML format
	 * @param currXmlDataStr contains the current form data in XML format
	 * @param hdrXmlDataStr containspoic_wavegen_default_data always header form data in XML format
	 * @param allXmlDataStr contains all forms data in XML format 
	 * @param objContext represents form no
	 * @param currentColumn represent Current COlumn
	 * @param editFlag the mode of the transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm 
etc
	*/
	public String itemChanged(String currXmlDataStr, String hdrXmlDataStr, String allXmlDataStr, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currDom = null;
		Document hdrDom = null;
		Document allDom = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			if (currXmlDataStr != null && currXmlDataStr.trim().length()!=0)
			{
				currDom = genericUtility.parseString(currXmlDataStr); 
			}
			if (hdrXmlDataStr != null && hdrXmlDataStr.trim().length()!=0)
			{
				hdrDom = genericUtility.parseString(hdrXmlDataStr); 
			}
			if (allXmlDataStr != null && allXmlDataStr.trim().length()!=0)
			{
				allDom = genericUtility.parseString(allXmlDataStr); 
			}
			errString = defaultItemChanged( currDom, hdrDom, allDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception :WaveGenerationIC :itemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
		}
		System.out.println ( "returning from WaveGenerationIC itemChanged" );
		return errString;
	}// end ItemChanged Method
	
	/**
	 * The public overloaded method is used for itemchange of required fields 
	 * Returns itemchange string in XML format
	 * @param currDom contains the current form data 
	 * @param hdrDom contains always header form data
	 * @param allDom contains all forms data 
	 * @param objContext represents form no
	 * @param editFlag the mode of the transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
    */
	public String defaultItemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		
		String siteCode = "";
		String errString = "";
		int currentFormNo = 0;
		String columnValue = "";
		String sSQL = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n" );
		GenericUtility genericUtility = GenericUtility.getInstance();
		java.sql.Date dateFrom = null;
		String custCodeFrom ="";
		String custCodeTo = "";
		String singleLotSl = "";
		String masterPackAllow = "";
		String activePickAllow = "";
		String stockToDockAllow = "";
		String saleOrderFrom = "";
		String saleOrderTo = "";
		String printLabel = "";//Added by sumit on 24/11/12
		String waveMode = ""; //Added by sumit 06/12/12
		String parcelPackAllow = "";//Added by sumit 29/12/12
		String custCodeDlv = "";//added by sumit on 13/05/13
		//changed by sankara on 22/06/13 Start...
		java.sql.Date dateTo = null;
		HashMap hm = new HashMap();
		String tranDate =""; 
		String tranDateTo ="";
		//changed by sankara on 22/06/13 End...
		try
		{
			String applDateFmt = genericUtility.getApplDateFormat();
			String dbDateFmt = genericUtility.getDBDateFormat();
			
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
			System.out.println("site code ->["+siteCode+"]");
			
			System.out.println("Current Column> > > > ->["+currentColumn+"]");
			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			
			valueXmlString.append("</Header>\r\n" );
			
			switch ( currentFormNo )
			{
			  	case 1:
				{
								
					if ( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
					{
						System.out.println("--------------item default --------------");
						valueXmlString.append( "<Detail1>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append( siteCode ).append( "]]></site_code>\r\n" );
						valueXmlString.append( "<item_ser__from><![CDATA[" ).append( "00" ).append( "]]></item_ser__from>\r\n" );
						valueXmlString.append( "<item_ser__to><![CDATA[" ).append( "ZZ" ).append( "]]></item_ser__to>\r\n" );
						valueXmlString.append( "<item_code__from><![CDATA[" ).append( "00" ).append( "]]></item_code__from>\r\n" );
						valueXmlString.append( "<item_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></item_code__to>\r\n" );
						valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );
						valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
						valueXmlString.append( "<sale_order__from><![CDATA[" ).append( "00" ).append( "]]></sale_order__from>\r\n" );
						valueXmlString.append( "<sale_order__to><![CDATA[" ).append( "ZZ" ).append( "]]></sale_order__to>\r\n" );
						//Added by Chandni 30-May-2012
						valueXmlString.append( "<cust_code__dlv__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__dlv__from>\r\n" );
						valueXmlString.append( "<cust_code__dlv__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__dlv__to>\r\n" );
						//Ended by chandni 30-May-2012
						//Changed by sumit on 27/07/12 set PREVIEW in wave_task default 
						valueXmlString.append( "<wave_mode><![CDATA[" ).append( "R" ).append( "]]></wave_mode>\r\n" );
						//Changed by sumit on 18/09/12 for single lot and Packing allow start. 
						valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
						valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
						valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
						valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
						//Changed by sumit on 18/09/12 for single lot and Packing allow end.
						//Changed by sumit on 18/09/12 adding print_label and printer name start.
						//changed by sankara on 09/04/14 print label set by default no start.
						//valueXmlString.append( "<print_label protect ='1'><![CDATA[" ).append( "" ).append( "]]></print_label>\r\n" );
						valueXmlString.append( "<print_label protect ='1'><![CDATA[" ).append( "N" ).append( "]]></print_label>\r\n" );
						//changed by sankara on 09/04/14 print label set by default no end.
						valueXmlString.append( "<printer_name protect ='1'><![CDATA[" ).append( "" ).append( "]]></printer_name>\r\n" );
						//Changed by sumit on 18/09/12 adding print_label and printer name end.
						
						valueXmlString.append("<parcelpack_allow protect ='1'><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
						valueXmlString.append("<master_override><![CDATA[").append("N").append("]]></master_override>\r\n");
						valueXmlString.append("<parcel_override><![CDATA[").append("N").append("]]></parcel_override>\r\n");
						//Changed by Rohan on 12-07-13 for shipment type default to as per routing
						valueXmlString.append("<ship_type><![CDATA[").append("A").append("]]></ship_type>\r\n");						
						valueXmlString.append( "</Detail1>\r\n" );
					}
					//Changed By Pragyan 21/06/12 Statrt
					else if ( currentColumn.trim().equalsIgnoreCase( "sale_order__from" ))
					{
							
							columnValue = genericUtility.getColumnValue("sale_order__from",currDom);
							custCodeTo = checkNull(genericUtility.getColumnValue("cust_code__to",currDom));
							saleOrderTo = checkNull(genericUtility.getColumnValue("sale_order__to",currDom));
							System.out.println("sale order from ["+columnValue+"] ");
							System.out.println("Hello sale_order__from");
							valueXmlString.append( "<Detail1>\r\n" );
							if(columnValue != null && !columnValue.equalsIgnoreCase("00"))
							{
								
								System.out.println("Hello sale_order__from2");
								//Changed by sumit 20/09/12 getting cust code start.								
								//sSQL = "SELECT DUE_DATE FROM SORDER WHERE SALE_ORDER = ?";
								//Changed by sumit on 29/12/12 changing sql for parcel picking 
								//sSQL = " SELECT S.DUE_DATE,C.CUST_CODE, C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW" +
								//Changed by sumit on 14/05/13 modified sql and join start.
								/*sSQL = " SELECT S.DUE_DATE,C.CUST_CODE, C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW, W.PARCELPACK_ALLOW "+
										" FROM SORDER S, CUSTOMER C, WAVE_TYPE W  WHERE SALE_ORDER = ? " +										
										" AND C.CUST_CODE = S.CUST_CODE " +
										" AND C.WAVE_TYPE = W.WAVE_TYPE ";*/
								//Changed By Sankara 02/aug/13 to take single lot from saleorder[WM3DESUN002]
								//sSQL = " SELECT S.DUE_DATE,S.CUST_CODE,S.CUST_CODE__DLV, C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW," +
								sSQL = " SELECT S.DUE_DATE,S.CUST_CODE,S.CUST_CODE__DLV, S.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW," +
										" W.STOCK_TO_DOCK_ALLOW, W.PARCELPACK_ALLOW "+
										" FROM SORDER S, CUSTOMER C, WAVE_TYPE W  WHERE SALE_ORDER = ? " +										
										" AND C.CUST_CODE = S.CUST_CODE__DLV " +
										" AND C.WAVE_TYPE = W.WAVE_TYPE ";
								//Changed by sumit on 14/05/13 modified sql and join end.
								System.out.println("queryString="+sSQL);
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1,columnValue);
								rs=pstmt.executeQuery();
								
								if(rs.next())
								{
								//  changed by sankara on 22/06/2013 Due date From set as min due date in below 	
									//dateFrom = rs.getDate(1);
									custCodeFrom = rs.getString("CUST_CODE").trim();
									singleLotSl = rs.getString("SINGLE_LOT");
									masterPackAllow = rs.getString("MASTER_PACK_ALLOW");
									activePickAllow = rs.getString("ACTIVE_PICK_ALLOW");
									stockToDockAllow = rs.getString("STOCK_TO_DOCK_ALLOW");
									parcelPackAllow = rs.getString("PARCELPACK_ALLOW");
									//Changed by sumit on 13/05/13 add cust code delivery 
									custCodeDlv = rs.getString("CUST_CODE__DLV").trim();
								}
								
								if(dateFrom != null)
								{
									//changed by sankara on 22-06-2013 trandate is already defined as varible in method
								    // String tranDate = genericUtility.getValidDateString( dateFrom.toString() , dbDateFmt, applDateFmt );
									 tranDate = genericUtility.getValidDateString( dateFrom.toString() , dbDateFmt, applDateFmt );
									//String dateFromInString=dtFormat.format(dateFrom);
									System.out.println("currDateStr=+++++++++++++"+tranDate);
									System.out.println("Hello sale_order__from3");
									valueXmlString.append( "<due_date__from><![CDATA[" ).append( tranDate ).append( "]]></due_date__from>\r\n" );
									//Changed by sumit on 20/09/12 setting value of cust_code__from.
									
									if( columnValue.trim().equalsIgnoreCase(saleOrderTo.trim()))
									{
										valueXmlString.append( "<cust_code__from><![CDATA[" ).append( custCodeFrom ).append( "]]></cust_code__from>\r\n" );
										valueXmlString.append( "<cust_code__to><![CDATA[" ).append( custCodeFrom ).append( "]]></cust_code__to>\r\n" );
										//Changed by sumit on 14/05/13 adding cust_code__dlv start.
										valueXmlString.append("<cust_code__dlv__from><![CDATA[").append(checkNull(custCodeDlv)).append("]]></cust_code__dlv__from>\r\n");
										valueXmlString.append("<cust_code__dlv__to><![CDATA[").append(checkNull(custCodeDlv)).append("]]></cust_code__dlv__to>\r\n");
										//Changed by sumit on 14/05/13 adding cust_code__dlv end.
										valueXmlString.append( "<single_lot protect ='0' ><![CDATA[" ).append( checkNull(singleLotSl) ).append( "]]></single_lot>\r\n" );
										valueXmlString.append( "<master_pack_allow protect ='0' ><![CDATA[" ).append( checkNull(masterPackAllow) ).append( "]]></master_pack_allow>\r\n" );
										valueXmlString.append( "<active_pick_allow protect ='0' ><![CDATA[" ).append( checkNull(activePickAllow) ).append( "]]></active_pick_allow>\r\n" );
										valueXmlString.append( "<stock_to_dock_allow protect ='0' ><![CDATA[" ).append( checkNull(stockToDockAllow) ).append( "]]></stock_to_dock_allow>\r\n" );
										//Changed by sumit on 29/12/12 display parcel pick allow
										valueXmlString.append("<parcelpack_allow protect ='0' ><![CDATA[").append(checkNull(parcelPackAllow)).append("]]></parcelpack_allow>\r\n");
									}
									else
									{
										valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );
										valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
										//Changed by sumit on 14/05/13 adding cust_code__dlv start.
										valueXmlString.append("<cust_code__dlv__from><![CDATA[").append("00").append("]]></cust_code__dlv__from>\r\n");
										valueXmlString.append("<cust_code__dlv__to><![CDATA[").append("ZZ").append("]]></cust_code__dlv__to>\r\n");
										//Changed by sumit on 14/05/13 adding cust_code__dlv end.
										valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
										valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
										valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
										valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
										//Changed by sumit on 29/12/12 display parcel pick allow
										valueXmlString.append("<parcelpack_allow protect ='1' ><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
									}
									System.out.println("Hello sale_order__from4");
									if(pstmt!=null)
									{
										pstmt.close();
										pstmt=null;
									}
									if(rs!=null)
									{
										rs.close();
										rs=null;
									}
								}
								else
								{									
									valueXmlString.append( "<due_date__from><![CDATA[" ).append("" ).append( "]]></due_date__from>\r\n" );
									//Changed by sumit on 20/09/12 setting value of cust_code__from in case of no data.
									valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );
									valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
									//Changed by sumit on 14/05/13 adding cust_code__dlv start.
									valueXmlString.append("<cust_code__dlv__from><![CDATA[").append("00").append("]]></cust_code__dlv__from>\r\n");
									valueXmlString.append("<cust_code__dlv__to><![CDATA[").append("ZZ").append("]]></cust_code__dlv__to>\r\n");
									//Changed by sumit on 14/05/13 adding cust_code__dlv end.
									valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
									valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
									valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
									valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
									//Changed by sumit on 29/12/12 display parcel pick allow
									valueXmlString.append("<parcelpack_allow protect ='1' ><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
								}
							}
							else
							{
								valueXmlString.append( "<due_date__from><![CDATA[" ).append("" ).append( "]]></due_date__from>\r\n" );
								//Changed by sumit on 20/09/12 setting value of cust_code__from in case of no data.
								valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );	
								valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
								//Changed by sumit on 14/05/13 adding cust_code__dlv start.
								valueXmlString.append("<cust_code__dlv__from><![CDATA[").append("00").append("]]></cust_code__dlv__from>\r\n");
								valueXmlString.append("<cust_code__dlv__to><![CDATA[").append("ZZ").append("]]></cust_code__dlv__to>\r\n");
								//Changed by sumit on 14/05/13 adding cust_code__dlv end.
								valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
								valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
								valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
								valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
								//Changed by sumit on 20/09/12 setting value of cust_code__to in case of no data.
								//Changed by sumit on 29/12/12 display parcel pick allow
								valueXmlString.append("<parcelpack_allow protect ='1' ><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
							}
							valueXmlString.append( "</Detail1>\r\n" );
						
					}
					else if ( currentColumn.trim().equalsIgnoreCase( "sale_order__to" ))
					{
							
							columnValue = genericUtility.getColumnValue("sale_order__to",currDom);
							saleOrderFrom = checkNull(genericUtility.getColumnValue("sale_order__from",currDom));
							custCodeFrom = checkNull(genericUtility.getColumnValue("cust_code__from",currDom));
							valueXmlString.append( "<Detail1>\r\n" );
							//System.out.println("columnValue1="+columnValue);
							//System.out.println("Hello sale_order__from");
							//changed by sankara on 22/JUN/13 for setting min max due date[Start..]
							hm = getDueDate(  saleOrderFrom,columnValue,conn);					
							dateFrom =(java.sql.Date)hm.get("MIN_DATE");
							dateTo =(java.sql.Date)hm.get("MAX_DATE");
					        System.out.println("DATEFROM"+dateFrom);
							
							 if(dateFrom == null )
							 {
								 System.out.println("INSIDEFORLOOP");
								 valueXmlString.append( "<due_date__from><![CDATA[" ).append("" ).append( "]]></due_date__from>\r\n" );				
							 }
							 if(dateTo == null )
							 {
								 System.out.println("INSIDEFORLOOPTO");
								 valueXmlString.append( "<due_date__to><![CDATA[" ).append("" ).append( "]]></due_date__to>\r\n" );				
							 }
						
							//changed by sankara on 22/JUN/13 for setting min max due date[end]	
							
							
							if(columnValue != null && !columnValue.equalsIgnoreCase("ZZ"))
							{
								
								System.out.println("Hello sale_order__from2");
								//Changed by sumit 20/09/12 getting cust code start.								
								//sSQL = "SELECT DUE_DATE FROM SORDER WHERE SALE_ORDER = ?";
								//
								//sSQL = " SELECT S.DUE_DATE,C.CUST_CODE, C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
								//Changed by sumit on 14/05/13 modified sql and join start.
								/*sSQL = " SELECT S.DUE_DATE,C.CUST_CODE, C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW, W.PARCELPACK_ALLOW "+
										" FROM SORDER S, CUSTOMER C, WAVE_TYPE W  WHERE SALE_ORDER = ? " +										
										" AND C.CUST_CODE = S.CUST_CODE " +
										" AND C.WAVE_TYPE = W.WAVE_TYPE ";*/
								//Changed By Sankara 02/aug/13 to take single lot from saleorder[WM3DESUN002]
								//sSQL = " SELECT S.DUE_DATE,S.CUST_CODE,S.CUST_CODE__DLV, C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW," +
								sSQL = " SELECT S.DUE_DATE,S.CUST_CODE,S.CUST_CODE__DLV, S.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW," +
										" W.STOCK_TO_DOCK_ALLOW, W.PARCELPACK_ALLOW "+
										" FROM SORDER S, CUSTOMER C, WAVE_TYPE W  WHERE SALE_ORDER = ? " +										
										" AND C.CUST_CODE = S.CUST_CODE__DLV " +
										" AND C.WAVE_TYPE = W.WAVE_TYPE ";
								//Changed by sumit on 14/05/13 modified sql and join end.
								
								System.out.println("queryString="+sSQL);
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1,columnValue);
								rs=pstmt.executeQuery();
								
								if(rs.next())
								{
								//  changed by sankara on 22/06/2013 Due date To set as max due date in below 	
									//	dateFrom = rs.getDate(1);
									custCodeTo = rs.getString("CUST_CODE").trim();
									singleLotSl = rs.getString("SINGLE_LOT");
									masterPackAllow = rs.getString("MASTER_PACK_ALLOW");
									activePickAllow = rs.getString("ACTIVE_PICK_ALLOW");
									stockToDockAllow = rs.getString("STOCK_TO_DOCK_ALLOW");
									parcelPackAllow = rs.getString("PARCELPACK_ALLOW");
									//Changed by sumit on 13/05/13 add cust code delivery 
									custCodeDlv = rs.getString("CUST_CODE__DLV").trim();
								}
								if(dateFrom != null)
								{						
									//changed by sankara on 22/06/13 for setting min max due date [start]
									//String tranDate = genericUtility.getValidDateString( dateFrom.toString() , dbDateFmt, applDateFmt );
									tranDate = genericUtility.getValidDateString( dateFrom.toString() , dbDateFmt, applDateFmt );	 
									tranDateTo = genericUtility.getValidDateString( dateTo.toString() , dbDateFmt, applDateFmt );	 
									//System.out.println("currDateStr=+++++++++++++"+tranDate);
									//System.out.println("Hello sale_order__from3");
									valueXmlString.append( "<due_date__from><![CDATA[" ).append( tranDate ).append( "]]></due_date__from>\r\n" );	
									valueXmlString.append( "<due_date__to><![CDATA[" ).append( tranDateTo ).append( "]]></due_date__to>\r\n" );	
									//changed by sankara on 22/06/13 for setting min max due date [end]
									valueXmlString.append( "<cust_code__to><![CDATA[" ).append( custCodeTo ).append( "]]></cust_code__to>\r\n" );
									if( columnValue.trim().equalsIgnoreCase(saleOrderFrom.trim()))
									{
										valueXmlString.append( "<cust_code__from><![CDATA[" ).append( custCodeTo ).append( "]]></cust_code__from>\r\n" );
										valueXmlString.append( "<cust_code__to><![CDATA[" ).append( custCodeTo ).append( "]]></cust_code__to>\r\n" );
										//Changed by sumit on 14/05/13 adding cust_code__dlv start.
										valueXmlString.append("<cust_code__dlv__from><![CDATA[").append(checkNull(custCodeDlv)).append("]]></cust_code__dlv__from>\r\n");
										valueXmlString.append("<cust_code__dlv__to><![CDATA[").append(checkNull(custCodeDlv)).append("]]></cust_code__dlv__to>\r\n");
										//Changed by sumit on 14/05/13 adding cust_code__dlv end.
										valueXmlString.append( "<single_lot protect ='0' ><![CDATA[" ).append( checkNull(singleLotSl) ).append( "]]></single_lot>\r\n" );
										valueXmlString.append( "<master_pack_allow protect ='0' ><![CDATA[" ).append( checkNull(masterPackAllow) ).append( "]]></master_pack_allow>\r\n" );
										valueXmlString.append( "<active_pick_allow protect ='0' ><![CDATA[" ).append( checkNull(activePickAllow) ).append( "]]></active_pick_allow>\r\n" );
										valueXmlString.append( "<stock_to_dock_allow protect ='0' ><![CDATA[" ).append( checkNull(stockToDockAllow) ).append( "]]></stock_to_dock_allow>\r\n" );
										//Changed by sumit on 29/12/12 display parcel pick allow
										valueXmlString.append("<parcelpack_allow protect ='0' ><![CDATA[").append(checkNull(parcelPackAllow)).append("]]></parcelpack_allow>\r\n");
									}
									else
									{	
										valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );
										valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
										//Changed by sumit on 14/05/13 adding cust_code__dlv start.
										valueXmlString.append("<cust_code__dlv__from><![CDATA[").append("00").append("]]></cust_code__dlv__from>\r\n");
										valueXmlString.append("<cust_code__dlv__to><![CDATA[").append("ZZ").append("]]></cust_code__dlv__to>\r\n");
										//Changed by sumit on 14/05/13 adding cust_code__dlv end.
										valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
										valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
										valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
										valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
										//Changed by sumit on 29/12/12 display parcel pick allow
										valueXmlString.append("<parcelpack_allow protect ='1' ><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
										valueXmlString.append("<direct_pick_loc><![CDATA[").append("").append("]]></direct_pick_loc>\r\n");
										//Chnaged by Rohan on 12-07-13 for shipment type default to as per routing
										//valueXmlString.append("<ship_type><![CDATA[").append("").append("]]></ship_type>\r\n");
										valueXmlString.append("<pallet_no><![CDATA[").append("").append("]]></pallet_no>\r\n");
										
									}
									//System.out.println("Hello sale_order__from4");
								}
								else
								{
									 //changed by sankara on 19/jun13 steeing due date from to  blank satrt..
									//valueXmlString.append( "<due_date__to><![CDATA[" ).append("").append( "]]></due_date__to>\r\n" );
									valueXmlString.append( "<due_date__from><![CDATA[" ).append("").append( "]]></due_date__from>\r\n" );
									valueXmlString.append( "<due_date__to><![CDATA[" ).append("").append( "]]></due_date__to>\r\n" );
									//changed by sankara on 19/jun13 steeing due date from to  blank end..	
									//Changed by sumit on 20/09/12 setting value of cust_code__to in case of no data.
									valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );
									valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
									//Changed by sumit on 14/05/13 adding cust_code__dlv start.
									valueXmlString.append("<cust_code__dlv__from><![CDATA[").append("00").append("]]></cust_code__dlv__from>\r\n");
									valueXmlString.append("<cust_code__dlv__to><![CDATA[").append("ZZ").append("]]></cust_code__dlv__to>\r\n");
									//Changed by sumit on 14/05/13 adding cust_code__dlv end.
									valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
									valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
									valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
									valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
									//Changed by sumit on 29/12/12 display parcel pick allow
									valueXmlString.append("<parcelpack_allow protect ='1' ><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
									valueXmlString.append("<direct_pick_loc><![CDATA[").append("").append("]]></direct_pick_loc>\r\n");
									//Chnaged by Rohan on 12-07-13 for shipment type default to as per routing
									//valueXmlString.append("<ship_type><![CDATA[").append("").append("]]></ship_type>\r\n");
									valueXmlString.append("<pallet_no><![CDATA[").append("").append("]]></pallet_no>\r\n");
									
								}
								
								if(pstmt!=null)
								{
									pstmt.close();
									pstmt=null;
								}
								if(rs!=null)
								{
									rs.close();
									rs=null;
								}
								
								
							}
							else
							{
								// changed by sankara on 22/06/2013 for setting min max due date start...
								//valueXmlString.append( "<due_date__to><![CDATA[" ).append("").append( "]]></due_date__to>\r\n" );

								tranDate = genericUtility.getValidDateString( dateFrom.toString() , dbDateFmt, applDateFmt );	 
								tranDateTo = genericUtility.getValidDateString( dateTo.toString() , dbDateFmt, applDateFmt );	 
								//valueXmlString.append( "<due_date__to><![CDATA[" ).append("").append( "]]></due_date__to>\r\n" );							
								valueXmlString.append( "<due_date__from><![CDATA[" ).append( tranDate ).append( "]]></due_date__from>\r\n" );	
								valueXmlString.append( "<due_date__to><![CDATA[" ).append( tranDateTo ).append( "]]></due_date__to>\r\n" );	
								// changed by sankara on 22/06/2013 for setting min max due date end...
								//Changed by sumit on 20/09/12 setting value of cust_code__to in case of no data.
								valueXmlString.append( "<cust_code__from><![CDATA[" ).append( "00" ).append( "]]></cust_code__from>\r\n" );
								valueXmlString.append( "<cust_code__to><![CDATA[" ).append( "ZZ" ).append( "]]></cust_code__to>\r\n" );
								//Changed by sumit on 14/05/13 adding cust_code__dlv start.
								valueXmlString.append("<cust_code__dlv__from><![CDATA[").append("00").append("]]></cust_code__dlv__from>\r\n");
								valueXmlString.append("<cust_code__dlv__to><![CDATA[").append("ZZ").append("]]></cust_code__dlv__to>\r\n");
								//Changed by sumit on 14/05/13 adding cust_code__dlv end.
								valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
								valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
								valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
								valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
								//Changed by sumit on 29/12/12 display parcel pick allow
								valueXmlString.append("<parcelpack_allow protect ='1' ><![CDATA[").append("").append("]]></parcelpack_allow>\r\n");
							}
							valueXmlString.append( "</Detail1>\r\n" );
						
					}
					//Changed sumit on 24/11/12 item_changed for print label start.
					else if ( currentColumn.trim().equalsIgnoreCase( "print_label"))
					{
					    //Changed by sumit on 24/11/12 getting print label value
						valueXmlString.append( "<Detail1>\r\n" );
						printLabel = genericUtility.getColumnValue("print_label",currDom);
						System.out.println(" printLabel "+printLabel);
						if("Y".equalsIgnoreCase(printLabel))
						{
							valueXmlString.append( "<printer_name protect ='0'><![CDATA[" ).append( "PDFCreator" ).append( "]]></printer_name>\r\n" );
						}
						else
						{
							
							valueXmlString.append( "<printer_name protect ='1'><![CDATA[" ).append( "" ).append( "]]></printer_name>\r\n" );
							
						}
						valueXmlString.append( "</Detail1>\r\n" );
					}
					//Changed sumit on 24/11/12 item_changed for print label end.
					//Changed sumit on 06/12/12 item_changed for wave_mode start.
					else if ( currentColumn.trim().equalsIgnoreCase( "wave_mode"))
					{
						valueXmlString.append( "<Detail1>\r\n" );
						waveMode = genericUtility.getColumnValue("wave_mode",currDom);
						System.out.println(" waveMode "+waveMode);
						if("P".equalsIgnoreCase(waveMode))
						{
							//changed by sankara on 09/04/14 print label set as No in item change
							//valueXmlString.append( "<print_label protect ='0'><![CDATA[" ).append( "Y" ).append( "]]></print_label>\r\n" );
							//valueXmlString.append( "<printer_name protect ='0'><![CDATA[" ).append( "PDFCreator" ).append( "]]></printer_name>\r\n" );
							valueXmlString.append( "<print_label protect ='0'><![CDATA[" ).append( "N" ).append( "]]></print_label>\r\n" );
							valueXmlString.append( "<printer_name protect ='1'><![CDATA[" ).append( "" ).append( "]]></printer_name>\r\n" );								
						}
						else
						{
							valueXmlString.append( "<print_label protect ='1'><![CDATA[" ).append( "" ).append( "]]></print_label>\r\n" );
							valueXmlString.append( "<printer_name protect ='1'><![CDATA[" ).append( "" ).append( "]]></printer_name>\r\n" );							
						}
						valueXmlString.append( "</Detail1>\r\n" );
					}
					//Changed sumit on 06/12/12 item_changed for wave_mode END.
					//Changed By Pragyan 21/06/12 end
					//Changed sumit on 18/09/12 item_changed for cust_code__from start.
					//else if ( currentColumn.trim().equalsIgnoreCase( "cust_code__from" ))
					/*else if ( "cust_code__from".equalsIgnoreCase( currentColumn ))
					{
						
						System.out.println(" from cust_code__from ");
						valueXmlString.append( "<Detail1>\r\n" );						
						custCodeFrom = checkNull(genericUtility.getColumnValue("cust_code__from",currDom));
						custCodeTo = checkNull(genericUtility.getColumnValue("cust_code__to",currDom));
						System.out.println(" custCodeFrom ["+custCodeFrom+"] custCodeTo ["+custCodeTo+"]");
						sSQL = 	" SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
								" FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
						pstmt = conn.prepareStatement(sSQL);
						if( custCodeFrom.trim().equalsIgnoreCase(custCodeTo.trim()))
						{
							pstmt.setString(1, custCodeFrom);
							rs = pstmt.executeQuery();
							if( rs.next())
							{							
								valueXmlString.append( "<single_lot protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("SINGLE_LOT")) ).append( "]]></single_lot>\r\n" );
								valueXmlString.append( "<master_pack_allow protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("MASTER_PACK_ALLOW")) ).append( "]]></master_pack_allow>\r\n" );
								valueXmlString.append( "<active_pick_allow protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("ACTIVE_PICK_ALLOW")) ).append( "]]></active_pick_allow>\r\n" );
								valueXmlString.append( "<stock_to_dock_allow protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("STOCK_TO_DOCK_ALLOW")) ).append( "]]></stock_to_dock_allow>\r\n" );
							}
							if( pstmt != null)
							{
								pstmt.close();
								pstmt = null;
							}
							if( rs != null)
							{
								rs.close();
								rs = null;
							}
						}
						else
						{
							valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
							valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
							valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
							valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
						}
						valueXmlString.append( "</Detail1>\r\n" );
						
					}
					else if ( currentColumn.trim().equalsIgnoreCase( "cust_code__to" ))
					{
						valueXmlString.append( "<Detail1>\r\n" );
																				
						custCodeFrom = checkNull(genericUtility.getColumnValue("cust_code__from",currDom));
						custCodeTo = checkNull(genericUtility.getColumnValue("cust_code__to",currDom));
						System.out.println(" custCodeFrom ["+custCodeFrom+"] custCodeTo ["+custCodeTo+"]");
						sSQL = 	" SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
								" FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
						pstmt = conn.prepareStatement(sSQL);
						if( custCodeFrom.trim().equalsIgnoreCase(custCodeTo.trim()))
						{
							pstmt.setString(1, custCodeFrom);
							rs = pstmt.executeQuery();
							if( rs.next())
							{							
								valueXmlString.append( "<single_lot protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("SINGLE_LOT")) ).append( "]]></single_lot>\r\n" );
								valueXmlString.append( "<master_pack_allow protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("MASTER_PACK_ALLOW")) ).append( "]]></master_pack_allow>\r\n" );
								valueXmlString.append( "<active_pick_allow protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("ACTIVE_PICK_ALLOW")) ).append( "]]></active_pick_allow>\r\n" );
								valueXmlString.append( "<stock_to_dock_allow protect ='0' ><![CDATA[" ).append( checkNull(rs.getString("STOCK_TO_DOCK_ALLOW")) ).append( "]]></stock_to_dock_allow>\r\n" );
							}
							if( pstmt != null)
							{
								pstmt.close();
								pstmt = null;
							}
							if( rs != null)
							{
								rs.close();
								rs = null;
							}
						}
						else
						{
							valueXmlString.append( "<single_lot protect ='1' ><![CDATA[" ).append( "" ).append( "]]></single_lot>\r\n" );
							valueXmlString.append( "<master_pack_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></master_pack_allow>\r\n" );
							valueXmlString.append( "<active_pick_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></active_pick_allow>\r\n" );
							valueXmlString.append( "<stock_to_dock_allow protect ='1' ><![CDATA[" ).append( "" ).append( "]]></stock_to_dock_allow>\r\n" );
						}
						valueXmlString.append( "</Detail1>\r\n" );
					}
					*///Changed sumit on 18/09/12 item_changed for cust_code__from end.
				}
				break;
			}
			valueXmlString.append( "</Root>\r\n" );
		}
		catch(Exception e)
		{
			System.out.println("Exception: WaveGenerationIC: defaultItemChanged(Document dom): " + e.getMessage() + ":");
			e.printStackTrace();
			errString = genericUtility.createErrorString(e);
			
		}
		//Changed by sumit on 13/05/13 closing connection start. 
		finally
		{
			try
			{
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception es)
			{
				es.printStackTrace();
				errString = genericUtility.createErrorString(es);
			}
		}
		//Changed by sumit on 13/05/13 closing connection end.
		return valueXmlString.toString();		
	}
	// changed by sankara on 18/JUN/13 for setting due_date__from and due_date__to[Start..] 	
		public HashMap getDueDate( String saleOrderFrom, String saleOrderTo,Connection conn )throws RemoteException, ITMException
		{
			String sql = "";
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			java.sql.Date minDueDate = null;
			java.sql.Date maxDueDate = null;
			HashMap hm = new HashMap();
			GenericUtility genericUtility = GenericUtility.getInstance();
			try
			{
				//changed by sankara on 16/10/13 for getting due date as confirmed sale order only
				//sql = " SELECT MIN(DUE_DATE),MAX(DUE_DATE) FROM SORDER WHERE SALE_ORDER BETWEEN ? AND ? ";
				sql = " SELECT MIN(DUE_DATE),MAX(DUE_DATE) FROM SORDER WHERE SALE_ORDER BETWEEN ? AND ? AND CONFIRMED = 'Y' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, saleOrderFrom);
				pstmt.setString(2, saleOrderTo); 
				rs = pstmt.executeQuery(); 
				if(rs.next())
				{
					minDueDate = rs.getDate(1);
					maxDueDate = rs.getDate(2);
					hm.put("MIN_DATE",minDueDate);
					hm.put("MAX_DATE",maxDueDate);
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;

				}
				if(rs != null)
				{
					rs.close();
					rs = null;

				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
			finally
			{
				try
				{
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;

					}
					if ( rs != null )
					{
						rs.close();
						rs = null;
					}

				}
				catch ( Exception e )
				{
					e.printStackTrace();
					throw new ITMException(e);
				}
			}	
			return hm; 	 	 
		}
		// changed by sankara on 18/JUN/13 for setting due_date__from and due_date__to[End..] 	
	private String checkNull( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		return inputVal;
	}
	
	/**
	 * The method is defined without any parameter and returns blank string
	 */
	@Override
	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}

	/**
	 * The public method is used for converting the current form data into a document(DOM)
	 * The dom is then given as argument to the overloaded function wfValData to perform validation
	 * Returns validation string if exists else returns null in XML format
	 * @param xmlString contains the current form data in XML format
	 * @param xmlString1 contains all the header information in the XML format
	 * @param xmlString2 contains the data of all the forms in XML format
	 * @param objContext represents the form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */	
	@Override
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext,String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currFormDataDom = null;
		Document hdrDataDom = null;
		Document allFormDataDom = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();    

		try
		{
			System.out.println( "xmlString ::" + xmlString);
			System.out.println( "xmlString1 ::" + xmlString1);
			System.out.println( "xmlString2 ::" + xmlString2);
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				currFormDataDom = genericUtility.parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				hdrDataDom = genericUtility.parseString(xmlString1); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				allFormDataDom = genericUtility.parseString(xmlString2); 
			}
			errString = wfValData( currFormDataDom, hdrDataDom, allFormDataDom, objContext, xtraParams);
			System.out.println( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return (errString); 
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of required fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currFormDataDom contains the current form data as a document object model
	 * @param hdrDataDom contains all the header information
	 * @param allFormDataDom contains the field data of all the forms 
	 * @param objContext represents form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */
	@Override
	public String wfValData(Document currFormDataDom, Document hdrDataDom, Document allFormDataDom, String objContext, String xtraParams) throws RemoteException, ITMException
	{   				
		String columnValue ="";
		String errString = "";
		String sql ="";
		String singleLot ="";
		String masterPackAllow = "";
		String activePickAllow = "";
		String stockToDockAllow = "";
		String parcelPackAllow = "";
		String directPickLoc = "";
		String palletNo = "";
		//Changed By Pragyan 25/feb/13 To change the validation with ship type
		String  shipType = "";
		String custCode = "";
		//Changed by sumit on 03/10/12 
		String printer = "";
		String printLabel = "";//Added by sumit.
		
		//Changed by sumit on 17/11/12 for validating sale order start.
		String itemSerFr = "", itemSerTo = "", custCodeFr = "", custCodeTo = "", custCodeDlvFr = "", custCodeDlvTo = "";
		String saleOrderFr = "", saleOrderTo = "", sDateFr = "", sDateTo = "", siteCode = "", itemCode = "";
		String itemCodeFr = "", itemCodeTo = "";
		java.sql.Timestamp dateTo =  null, dueDate =  null, dateFr =  null;		
		//Changed by sumit on 17/11/12 for validating sale order end.
		
		int count = 0, cnt = 0;
		int rowCount = 0;
		int noOfChilds = 0;
		int noOfParent = 0;	

		GenericUtility genericUtility = GenericUtility.getInstance();		

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		

		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		Set errSet = null;
		
		//Changed by sumit on 19/11/12 created variable's for validation near expired item start 
		PreparedStatement pstmtStock = null;
		ResultSet rs1 = null;
		String sSingleLotSql = "", sSQL = "";
		DistCommon discommon = new DistCommon();
		int minSelfLife = 0;
		boolean isActives = false;
		double pendingQty = 0;
		Map itemCodeMap = new HashMap();		
		String saleOrderTran = "", orderByStkStr = "";
		String resrvLoc = "", casePickLoc = "", activePickLoc = "", deepStoreLoc = "", partialResrvLoc = "";
		boolean bappend = false ;
		double balStockQty = 0, holdQty = 0;
		//Changed by sumit on 19/11/12 created variable's for validation near expired item end 
		
		try
		{	
			int currentFormNo = 0;				
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverITM" );
			connDriver = null;

			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );

			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			NodeList parentList = currFormDataDom.getElementsByTagName( "Detail" + currentFormNo );
			NodeList childList = null;	
			noOfParent = parentList.getLength();		

			switch ( currentFormNo )  // Switch Case for form number
			{
			case 1:
			{	
				childList = parentList.item(0).getChildNodes();
				noOfChilds = childList.getLength();

				for ( int ctr = 0; ctr < noOfChilds; ctr++ ) //Loop for each node of current detail
				{	
					Node childNode = childList.item( ctr );
					if( childNode.getNodeType() != Node.ELEMENT_NODE )
					{
						continue;
					}
					String childNodeName = childNode.getNodeName();
					if ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					//Changed By Sankara as this warning is not required as single lot value has been changed in sales order.start
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
					/*if ( "single_lot".equalsIgnoreCase( childNodeName ) )
					{
						singleLot = checkNull(genericUtility.getColumnValue( "single_lot", currFormDataDom ));
						//Changed by sankara on 26-06-13 for customer code as customer code delivery
						//custCode = genericUtility.getColumnValue( "cust_code__from", currFormDataDom );
						custCode = genericUtility.getColumnValue( "cust_code__dlv__from", currFormDataDom );
						if(singleLot.trim().length() > 0 )
						{
							sql = 	"SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
									"FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCode);
							rs = pstmt.executeQuery();
							if( rs.next())
							{								
								if( !singleLot.equalsIgnoreCase(rs.getString("SINGLE_LOT")))
								{
									//errList.add( "CHMSTPKALL" );
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "CHSINGLELT" );
									//errFields.add( "" );
								}
							}
							if( rs != null )
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null )
							{
								pstmt.close();
								pstmt = null;								
							}
						}
					}*/
					//Changed By Sankara as this warning is not required as single lot value has been changed in sales order.end
					if ( "master_pack_allow".equalsIgnoreCase( childNodeName ) )
					{
						masterPackAllow = checkNull(genericUtility.getColumnValue( "master_pack_allow", currFormDataDom ));
						parcelPackAllow = checkNull(genericUtility.getColumnValue( "parcelpack_allow", currFormDataDom ));
						//Changed by sankara on 26-06-13 for customer code as customer code delivery
						//custCode = genericUtility.getColumnValue( "cust_code__from", currFormDataDom );
						custCode = genericUtility.getColumnValue( "cust_code__dlv__from", currFormDataDom );
						if(masterPackAllow.trim().length() > 0 )
						{
							sql = 	"SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
									"FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCode);
							rs = pstmt.executeQuery();
							if( rs.next())
							{								
								if( !masterPackAllow.equalsIgnoreCase(rs.getString("MASTER_PACK_ALLOW")))
								{
									//errList.add( "CHMSTPKALL" );
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "CHSINGLELT" );
									//errFields.add( "" );
								}
							}
							//Changed by sankara on 01/08/13 for both parcel and master allowed for wave start.	
							/*if("Y".equalsIgnoreCase(parcelPackAllow) && "Y".equalsIgnoreCase(masterPackAllow))
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "ONSELCMP" );
							}*/
							//Changed by sankara on 01/08/13 for both parcel and master allowed for wave end.
							
							if( rs != null )
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null )
							{
								pstmt.close();
								pstmt = null;								
							}
						}
					}
					//Changed By Pragyan 25/feb/13 To change the validation with ship type.start
					else if ( "parcelpack_allow".equalsIgnoreCase( childNodeName ) )
					{
						parcelPackAllow = checkNull(genericUtility.getColumnValue( "parcelpack_allow", currFormDataDom ));
						//Changed by sankara on 26-06-13 for customer code as customer code delivery
						//custCode = genericUtility.getColumnValue( "cust_code__from", currFormDataDom );
						custCode = genericUtility.getColumnValue( "cust_code__dlv__from", currFormDataDom );
						if(parcelPackAllow.trim().length() > 0 )
						{
							sql = 	"SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW, W.PARCELPACK_ALLOW " +
									"FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCode);
							rs = pstmt.executeQuery();
							if( rs.next())
							{								
								if( !parcelPackAllow.equalsIgnoreCase(rs.getString("PARCELPACK_ALLOW")))
								{
									//errList.add( "STOCK2DOC" );
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "CHSINGLELT" );
									//errFields.add( "" );
								}
							}
							//Changed by sankara on 01/08/13 for both parcel and master allowed for wave start.
							/*if("Y".equalsIgnoreCase(parcelPackAllow) && "Y".equalsIgnoreCase(masterPackAllow))
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "ONSELCMP" );
							}*/
							//Changed by sankara on 01/08/13 for both parcel and master allowed for wave end
							if( rs != null )
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null )
							{
								pstmt.close();
								pstmt = null;								
							}
						}
					}
					//Changed By Pragyan 25/feb/13 To change the validation with ship type.end
					else if ( "ship_type".equalsIgnoreCase( childNodeName ) )
					{
						masterPackAllow = checkNull(genericUtility.getColumnValue( "master_pack_allow", currFormDataDom ));
						parcelPackAllow = checkNull(genericUtility.getColumnValue( "parcelpack_allow", currFormDataDom ));
						shipType = checkNull(genericUtility.getColumnValue( "ship_type", currFormDataDom ));
						
						if(("Y".equalsIgnoreCase(masterPackAllow)) && !("A".equalsIgnoreCase(shipType) || "M".equalsIgnoreCase(shipType)))
						{
							errFields.add( childNodeName.toLowerCase() );
							errList.add( "ATLSTSELMP" );
						}
						else if(("Y".equalsIgnoreCase(parcelPackAllow)) && !("A".equalsIgnoreCase(shipType) || "P".equalsIgnoreCase(shipType)))
						{
							errFields.add( childNodeName.toLowerCase() );
							errList.add( "ATLSTSELPP" );
						}
						else if(!("Y".equalsIgnoreCase(parcelPackAllow) || "Y".equalsIgnoreCase(masterPackAllow) ) && ("M".equalsIgnoreCase(shipType) || "P".equalsIgnoreCase(shipType)))
						{
							errFields.add( childNodeName.toLowerCase() );
							errList.add( "ATLSTSENAN" );
						}
						
					}//directPickLoc
					else if ( "direct_pick_loc".equalsIgnoreCase( childNodeName ) )
					{
						directPickLoc = checkNull(genericUtility.getColumnValue( "direct_pick_loc", currFormDataDom ));
						int countLoc = 0;
						if(directPickLoc != null && directPickLoc.trim().length() > 0 )
						{
							sql = 	"SELECT COUNT(*) FROM LOCATION WHERE LOC_CODE= ? AND DIRECT_PICK = ?" ;
									
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, directPickLoc);
							pstmt.setString(2, "Y");
							rs = pstmt.executeQuery();
							if( rs.next())
							{					
								countLoc = rs.getInt(1); 
								if( !(countLoc > 0))
								{
									//errList.add( "STOCK2DOC" );
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "INVDPIKLOC" );
									//errFields.add( "" );
								}
							}
							
							if( rs != null )
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null )
							{
								pstmt.close();
								pstmt = null;								
							}
						}
						
					}
					else if ( "pallet_no".equalsIgnoreCase( childNodeName ) )
					{
						directPickLoc = checkNull(genericUtility.getColumnValue( "direct_pick_loc", currFormDataDom ));
						palletNo = checkNull(genericUtility.getColumnValue( "pallet_no", currFormDataDom ));
						int countLoc = 0;
						if(directPickLoc != null && directPickLoc.trim().length() > 0 )
						{
								if(palletNo != null && palletNo.trim().length() > 0 )
								{
									sql = 	"SELECT COUNT(*) FROM PALLET_NO WHERE PALLET_NO = ? AND STATUS= ?" ;
											
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, palletNo);
									pstmt.setString(2, "A");
									rs = pstmt.executeQuery();
									if( rs.next())
									{					
										countLoc = rs.getInt(1); 
										if( !(countLoc > 0))
										{
											//errList.add( "STOCK2DOC" );
											errFields.add( childNodeName.toLowerCase() );
											errList.add( "INVPLTNO1" );
											//errFields.add( "" );
										}
									}
									
									if( rs != null )
									{
										rs.close();
										rs = null;
									}
									if( pstmt != null )
									{
										pstmt.close();
										pstmt = null;	
									}
								}
								else
								{
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "NVLPLTMND" );
									
								}
						}
						
					}
					//Changed by sumit on 03/10/12 validating printer service name with existing printer in system start.
					else if ( "active_pick_allow".equalsIgnoreCase( childNodeName ) )
					{
						activePickAllow = checkNull(genericUtility.getColumnValue( "active_pick_allow", currFormDataDom ));
						//Changed by sankara on 26-06-13 for customer code as customer code delivery
						//custCode = genericUtility.getColumnValue( "cust_code__from", currFormDataDom );
						custCode = genericUtility.getColumnValue( "cust_code__dlv__from", currFormDataDom );
						if(activePickAllow.trim().length() > 0 )
						{
							sql = 	"SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
									"FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCode);
							rs = pstmt.executeQuery();
							if( rs.next())
							{								
								if( !activePickAllow.equalsIgnoreCase(rs.getString("ACTIVE_PICK_ALLOW")))
								{
									//errList.add( "CHACTPKALL" );
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "CHSINGLELT" );
									//errFields.add( "" );
								}
							}
							if( rs != null )
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null )
							{
								pstmt.close();
								pstmt = null;								
							}
						}
					}
					else if ( "stock_to_dock_allow".equalsIgnoreCase( childNodeName ) )
					{
						stockToDockAllow = checkNull(genericUtility.getColumnValue( "stock_to_dock_allow", currFormDataDom ));
						//Changed by sankara on 26-06-13 for customer code as customer code delivery
						//custCode = genericUtility.getColumnValue( "cust_code__from", currFormDataDom );
						custCode = genericUtility.getColumnValue( "cust_code__dlv__from", currFormDataDom );
						if(stockToDockAllow.trim().length() > 0 )
						{
							sql = 	"SELECT C.SINGLE_LOT, W.MASTER_PACK_ALLOW, W.ACTIVE_PICK_ALLOW, W.STOCK_TO_DOCK_ALLOW " +
									"FROM CUSTOMER C, WAVE_TYPE W WHERE C.WAVE_TYPE = W.WAVE_TYPE AND C.CUST_CODE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, custCode);
							rs = pstmt.executeQuery();
							if( rs.next())
							{								
								if( !stockToDockAllow.equalsIgnoreCase(rs.getString("STOCK_TO_DOCK_ALLOW")))
								{
									//errList.add( "STOCK2DOC" );
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "CHSINGLELT" );
									//errFields.add( "" );
								}
							}
							if( rs != null )
							{
								rs.close();
								rs = null;
							}
							if( pstmt != null )
							{
								pstmt.close();
								pstmt = null;								
							}
						}
					}
					//Changed by sumit on 03/10/12 validating printer service name with existing printer in system start.
					//Changed by sumit on 24/11/12 changing field name 
					//else if ( "printer".equalsIgnoreCase( childNodeName ) )
					else if ( "printer_name".equalsIgnoreCase( childNodeName ) )
					{
						//Changed by sumit on 24/11/12 changing field name .
						//printer = checkNull(genericUtility.getColumnValue( "printer", currFormDataDom ));
						printer = checkNull(genericUtility.getColumnValue( "printer_name", currFormDataDom ));
						//Changed by sumit on 24/11/12 adding print label for validation
						printLabel = checkNull(genericUtility.getColumnValue( "print_label", currFormDataDom ));
						System.out.println(" printer from input parameter ["+printer+"] and print label ->["+printLabel+"]");
						//changed by sankara on 22/07/14 not reuired as per pragyan
						/*PrintService[] printServices = PrinterJob.lookupPrintServices();
						ArrayList<String> arry = new ArrayList<String>();*/
						//Changed by sumit on 24/11/12 adding print label condition
						//if(printer.trim().length() == 0)
						if(printer.trim().length() == 0 && "Y".equalsIgnoreCase(printLabel))
						{
							errFields.add( childNodeName.toLowerCase() );
							errList.add( "NLLPRINTER" );
						}
						else if(printer.trim().length() > 0 && "Y".equalsIgnoreCase(printLabel))
						{
							//Changed by Dhanraj on 27JUN14 to validate printer name from net printers table.start
							//changed by sankara on 22/07/14 not reuired as per pragyan
							/*if(isRemotePrintSrvCofigured("IS_REMOTE_PRNTSRV_CONFIGRD",conn))
							{*/
								sql="select printer_name from net_printers where printer_name= ? ";
					    		pstmt = conn.prepareStatement(sql);
					    		pstmt.setString(1, printer.trim());
					    		rs = pstmt.executeQuery();
					    		if( rs.next())
					    		{
					    			System.out.println("Printer Found in  net_printers..........["+printer+"]");
					    		}
					    		else
					    		{
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "INVPRINTER" );
								}
					    		pstmt.close();pstmt = null;
					    		rs.close(); rs = null;
					    	//changed by sankara on 22/07/14 not reuired as per pragyan start.	
							/*}
							else
							{
								for (PrintService printService : printServices) 
								{
						            String name = printService.getName();
						            System.out.println("printer service name --> "+name);
						            arry.add(name);					            
						        }
								if( !arry.contains(printer.trim()))
					            {
					            	errFields.add( childNodeName.toLowerCase() );
									errList.add( "INVPRINTER" );
					            }							
								arry.clear();	
							} */
					    	//changed by sankara on 22/07/14 not reuired as per pragyan end.	
						
							//end Dhanraj 27Jun14
							
				    		//hide by Dhanraj 27Jnn14
							/*System.out.println(" in inside if");
							for (PrintService printService : printServices) 
							{
					            String name = printService.getName();
					            System.out.println("printer service name --> "+name);
					            arry.add(name);					            
					        }
							if( !arry.contains(printer.trim()))
				            {
				            	errFields.add( childNodeName.toLowerCase() );
								errList.add( "INVPRINTER" );
				            }							
							arry.clear();		*/		          
				    		//Changed by Dhanraj 27JUN14 to validate printer name from net printers table.end		           
						}
					}					
					//Changed by sumit on 03/10/12 validating printer service name with existing printer in system end.
					//Changed by sumit on 17/11/12 validating sale order on hold start.
					/*else if("sale_order__from".equalsIgnoreCase(childNodeName))
					{
						System.out.println(" in sale order sumit ");
						resrvLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);			            
						casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
						activePickLoc  = discommon.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
						deepStoreLoc = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn);
						partialResrvLoc = discommon.getDisparams("999999","PRSRV_INVSTAT",conn);
						
						siteCode = genericUtility.getColumnValue("site_code",currFormDataDom);
						itemSerFr = genericUtility.getColumnValue("item_ser__from",currFormDataDom);
						itemSerTo = genericUtility.getColumnValue("item_ser__to",currFormDataDom);
						itemCodeFr = genericUtility.getColumnValue("item_code__from",currFormDataDom); 
						itemCodeTo = genericUtility.getColumnValue("item_code__to",currFormDataDom);    
						custCodeFr = genericUtility.getColumnValue("cust_code__from",currFormDataDom);
						custCodeTo = genericUtility.getColumnValue("cust_code__to",currFormDataDom); 
						custCodeDlvFr = genericUtility.getColumnValue("cust_code__dlv__from",currFormDataDom);
						custCodeDlvTo = genericUtility.getColumnValue("cust_code__dlv__to",currFormDataDom); 
						saleOrderFr = genericUtility.getColumnValue("sale_order__from",currFormDataDom);
						saleOrderTo = genericUtility.getColumnValue("sale_order__to",currFormDataDom); 
						sDateFr = genericUtility.getColumnValue("due_date__from",currFormDataDom);
						sDateFr = genericUtility.getValidDateString(sDateFr, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
						dateFr = java.sql.Timestamp.valueOf(sDateFr + " 00:00:00");
						
						sDateTo = genericUtility.getColumnValue("due_date__to",currFormDataDom);    
						sDateTo = genericUtility.getValidDateString(sDateTo, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
						dateTo = java.sql.Timestamp.valueOf(sDateTo + " 00:00:00");	
						
						sql =" SELECT SORDER.CUST_CODE, CUSTOMER.CUST_NAME,SORDDET.LINE_NO, "
						       +"SORDER.SALE_ORDER,SORDER.DUE_DATE,SORDITEM.ITEM_CODE,"
							   +"ITEM.DESCR,SORDITEM.QUANTITY,"
							   +"(SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC) PENDING_QUANTITY,"
							   +"SORDITEM.QTY_ALLOC,SORDDET.PACK_INSTR,"
							   +"SORDER.SITE_CODE,SORDITEM.EXP_LEV, "
							   +"SORDER.PART_QTY AS PART_QTY, CUSTOMER.SINGLE_LOT AS SINGLE_LOT, SORDITEM.MIN_SHELF_LIFE AS MIN_SHELF_LIFE "//Gulzar 5/13/2012
							   +" ,SORDER.ALLOC_FLAG AS ALLOC_FLAG , WAVE_TYPE.MASTER_PACK_ALLOW , WAVE_TYPE.ACTIVE_PICK_ALLOW , WAVE_TYPE.STOCK_TO_DOCK_ALLOW, "	
							   +" (CASE WHEN SORDDET.HOLD_FLAG IS NULL THEN 'N' ELSE SORDDET.HOLD_FLAG end ) AS HOLD_FLAG "
							   +" FROM SORDDET,SORDER,SORDITEM,CUSTOMER,ITEM , WAVE_TYPE "								   
							   +"WHERE ( SORDER.SALE_ORDER = SORDDET.SALE_ORDER ) AND "
							   +"( SORDITEM.SALE_ORDER = SORDER.SALE_ORDER ) AND "
							   +"( SORDDET.LINE_NO = SORDITEM.LINE_NO ) AND "
							   +"( SORDITEM.ITEM_CODE = ITEM.ITEM_CODE ) AND "
							   +"( SORDER.CUST_CODE = CUSTOMER.CUST_CODE ) AND  "
							   +"( SORDDET.SITE_CODE = SORDITEM.SITE_CODE ) AND  "								   
							   +"( CUSTOMER.WAVE_TYPE = WAVE_TYPE.WAVE_TYPE(+)) AND "
							   +" SORDER.SITE_CODE = ? AND "
							   +" ITEM.ITEM_SER >=? AND "
							   +" ITEM.ITEM_SER <=? AND "  
							   +" ITEM.ITEM_CODE >=? AND "
							   +" ITEM.ITEM_CODE <=? AND "
							   +" CUSTOMER.CUST_CODE >=? AND "
							   +" CUSTOMER.CUST_CODE <=? AND "  
							   +" SORDER.CUST_CODE__DLV >=? AND " 	
							   +" SORDER.CUST_CODE__DLV <=? AND "  
							   +" SORDER.SALE_ORDER >= ? AND "
							   +" SORDER.SALE_ORDER <= ? AND " 
							   +" SORDER.DUE_DATE >=  ?  AND" 
							   +" SORDER.DUE_DATE <= ? " 
							   +"AND CASE WHEN SORDDET.STATUS IS NULL THEN 'P' ELSE SORDDET.STATUS end <> 'C' "
							   +"AND CASE WHEN SORDER.STATUS IS NULL THEN 'P' ELSE SORDER.STATUS end = 'P' "								   
							   //+" AND CASE WHEN SORDDET.HOLD_FLAG IS NULL THEN 'N' ELSE SORDDET.HOLD_FLAG end = 'Y'"								   
							   +" AND ((SORDITEM.QUANTITY - SORDITEM.QTY_DESP - SORDITEM.QTY_ALLOC > 0 ) "  
							   +" OR (FN_CHECK_MANUAL_STOCK_ALLOC(SORDER.SALE_ORDER, SORDDET.LINE_NO) = 0 ) )"							   
							   +"AND SORDITEM.LINE_TYPE = 'I'"							  
							   +" ORDER BY SORDER.CUST_CODE,SORDER.SALE_ORDER,SORDITEM.ITEM_CODE__ORD";					
						
						pstmt = conn.prepareStatement(sql);						
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemSerFr);
						pstmt.setString(3,itemSerTo);						
						pstmt.setString(4,itemCodeFr);
						pstmt.setString(5,itemCodeTo);						
						pstmt.setString(6,custCodeFr);
						pstmt.setString(7,custCodeTo);
						pstmt.setString(8,custCodeDlvFr); 	
						pstmt.setString(9,custCodeDlvTo); 	
						pstmt.setString(10,saleOrderFr);
						pstmt.setString(11,saleOrderTo);
						pstmt.setTimestamp(12,dateFr);
						pstmt.setTimestamp(13,dateTo);						
						rs = pstmt.executeQuery();						
						
						sSQL =	"SELECT SUM(STOCK.QUANTITY - STOCK.ALLOC_QTY - (CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END ) ), SUM(STOCK.HOLD_QTY ) "
								+"FROM STOCK,ITEM,LOCATION,INVSTAT " 
			     				+"WHERE (ITEM.ITEM_CODE = STOCK.ITEM_CODE) "
			     				+"AND (LOCATION.LOC_CODE = STOCK.LOC_CODE ) "
			     				+"AND (LOCATION.INV_STAT = INVSTAT.INV_STAT) "
			     				+"AND INVSTAT.AVAILABLE = ? "
			     				+"AND STOCK.ITEM_CODE = ? "
			     				+"AND STOCK.SITE_CODE = ? " 
			     				+"AND (STOCK.QUANTITY - STOCK.ALLOC_QTY - CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END) > 0  "
			     				+"AND (MONTHS_BETWEEN(TO_DATE(STOCK.EXP_DATE),SYSDATE) < ? ) ";
											
						sSingleLotSql = "SELECT SUM(STOCK.QUANTITY - STOCK.ALLOC_QTY - (CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END ) ), "
										+"SUM(STOCK.HOLD_QTY )" 
						 				+"FROM STOCK,ITEM,LOCATION,INVSTAT " 
						 				+"WHERE (ITEM.ITEM_CODE = STOCK.ITEM_CODE) " 
						 				+"AND (LOCATION.LOC_CODE = STOCK.LOC_CODE ) " 
						 				+"AND (LOCATION.INV_STAT = INVSTAT.INV_STAT) " 
						 				+"AND INVSTAT.AVAILABLE = ? " 
						 				+"AND STOCK.ITEM_CODE = ? " 
						 				+"AND STOCK.SITE_CODE = ? "  
						 				+"AND STOCK.LOT_NO IN (SELECT LOT_NO FROM STOCK WHERE ITEM_CODE =? AND SITE_CODE =? "
						 				+"GROUP BY LOT_NO HAVING SUM(QUANTITY - ALLOC_QTY - (CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END) ) >= ?) "
						 				+"AND (MONTHS_BETWEEN(TO_DATE(STOCK.EXP_DATE),SYSDATE) < ? ) ";
											
						if( rs.next())
						{
							do
							{									
								minSelfLife = 0;								
								isActives = false;
								String holdFlag = "";								
								pendingQty = rs.getDouble("PENDING_QUANTITY");
								itemCode = checkNull(rs.getString(6));
								siteCode = checkNull(rs.getString(12));
								//partQty = checkNull(rs.getString("PART_QTY"));
								singleLot = checkNull(rs.getString("SINGLE_LOT"));
								minSelfLife = rs.getInt("MIN_SHELF_LIFE");								
								System.out.println("pendingQty =["+pendingQty+"]");
								//System.out.println("partQty =["+partQty+"]");
								System.out.println("singleLot =["+singleLot+"]");
								System.out.println("MIN_SHELF_LIFE =["+minSelfLife+"]");
								HashMap itemVolMap = getItemVoumeMap(itemCode, "", conn);
								double packSize = (Double)itemVolMap.get("PACK_SIZE");								
								holdFlag = rs.getString("HOLD_FLAG");
								System.out.println(" hold flag ---*- >["+holdFlag+"]");
								
								if( "Y".equalsIgnoreCase(holdFlag))
								{									
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "SALEORDHOL" );
								}								
								if((pendingQty % packSize) > 0)
								{
									isActives = true;
									orderByStkStr = "AND LOCATION.INV_STAT IN(?,?,?,?,?) ORDER BY STOCK.EXP_DATE,LOCATION.INV_STAT, STOCK.LOC_CODE ";
								}
								else
								{
									isActives = false;
									orderByStkStr = "AND LOCATION.INV_STAT IN(?,?,?,?) ORDER BY STOCK.EXP_DATE, STOCK.LOC_CODE ";
								}							
								
								if(itemCodeMap.containsKey(rs.getString(6)))
								{						
									balStockQty = Double.parseDouble(itemCodeMap.get(rs.getString(6)).toString());
									bappend = false ;
								}
								else
								{									
									if ( singleLot.trim().length() > 0 && singleLot.trim().equalsIgnoreCase("Y") )
									{										
										pstmtStock = conn.prepareStatement(sSingleLotSql + orderByStkStr);
										pstmtStock.setString(1,"Y");
										pstmtStock.setString(2,itemCode);
										pstmtStock.setString(3,siteCode);
										pstmtStock.setString(4,itemCode);
										pstmtStock.setString(5,siteCode);
										pstmtStock.setDouble(6,pendingQty);
										pstmtStock.setInt(7,minSelfLife);
										pstmtStock.setString(8,resrvLoc);
										pstmtStock.setString(9,casePickLoc);
										pstmtStock.setString(10,activePickLoc);
										pstmtStock.setString(11,deepStoreLoc);
										if(isActives)
										{
											pstmtStock.setString(12,partialResrvLoc);
										}
									}
									else
									{										
										pstmtStock = conn.prepareStatement(sSQL + orderByStkStr);
										pstmtStock.setString(1,"Y");
										pstmtStock.setString(2,rs.getString(6));
										pstmtStock.setString(3,rs.getString(12));
										pstmtStock.setInt(4,minSelfLife);//Gulzar on 5/18/2012
										pstmtStock.setString(5,resrvLoc);
										pstmtStock.setString(6,casePickLoc);
										pstmtStock.setString(7,activePickLoc);
										pstmtStock.setString(8,deepStoreLoc);
										if(isActives)
										{
											pstmtStock.setString(9,partialResrvLoc);
										}										
									}
									rs1 = pstmtStock.executeQuery();
									
									if (rs1.next())
									{
										String temp = "";
										temp = rs1.getString(1);										
										if( temp !=  null)
										{
											errFields.add( childNodeName.toLowerCase() );
											errList.add( "ITEMNEXPIR" );
										}																			
									}
									if(rs1 != null)
									{
										rs1.close();
									}
									pstmtStock.clearParameters();
								}							
											
							}while(rs.next());							
							pstmt.clearParameters();						
						}
					}
					//Changed by sumit on 17/11/12 validating sale order on hold end.
*/				}
			} //End of case 1 Validation
			break;				
					
			}//End of switch statement
			errSet = new HashSet(errList);
			errList = new ArrayList(errSet);
			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;//+"<trace>"+errMsg+"</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();
		}
		catch ( Exception e )
		{
			System.out.println ( "Exception: WaveGenerationIC: wfValData( Document currFormDataDom ): " + e.getMessage() + ":" );
			//throw new ITMException(e);
			e.printStackTrace();			
		}
		finally
		{
			try
			{
				if (rs!=null)
				{
					rs.close();
					rs = null;
				}				
				
				if( conn != null  )
				{
					conn.close(); conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception : WaveGenerationIC:wfValData : " + e.getMessage() );
				throw new ITMException(e);
			}
		}
		System.out.println( "errString>>>>>>>::"+errString );
		return errString;
	}
	private String errorType( Connection conn , String errorCode )
	{
		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();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
	
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,lotWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT LOT_WEIGHT FROM"
				  +" ITEM I,ITEM_LOT_PACKSIZE L"
				  +" WHERE I.ITEM_CODE = L.ITEM_CODE"
				  +" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
				  +" AND  I.ITEM_CODE = ?";
				  
			
			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemWeight = rs.getDouble("ITEM_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");				
			}
			
			//packSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			/*itemSize = Math.floor(itmLen * itmWidth * itmHeight);
			lotSize = Math.floor((lotHeight * lotWidth * lotLen));*/
			itemSize = itmLen * itmWidth * itmHeight;
			lotSize = lotHeight * lotWidth * lotLen;
			
			dataVolumeMap.put("PACK_SIZE", packSize);
			dataVolumeMap.put("ITEM_SIZE", itemSize);
			dataVolumeMap.put("LOT_SIZE", lotSize);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	
	private boolean isRemotePrintSrvCofigured(String propertyName , Connection conn) throws ITMException
	{
		String sql = "";
		String flagRemoteRptSrvltCofigured = "N";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean isConfigured = false;
		DistCommon discommon = new DistCommon();	
		try
		{
			flagRemoteRptSrvltCofigured  = discommon.getDisparams("999999",propertyName,conn);       
			
			if("Y".equalsIgnoreCase(flagRemoteRptSrvltCofigured))
			{
				return isConfigured = true;
			}
			else
			{
				return isConfigured;
			}
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{	
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
	}
}
