/**
* PURPOSE : Validation and Item Change implementation for Stock Transfer component .
* AUTHOR  : BALU
* Date    : 19/09/2011
* 
*/

package ibase.webitm.ejb.wms;

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

import javax.ejb.*;
import javax.naming.InitialContext;
import javax.xml.parsers.*;
import org.w3c.dom.*;

import ibase.ejb.*;
import ibase.system.config.*;
import ibase.utility.BaseLogger;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerConfigurationException;
import org.xml.sax.InputSource;
import java.net.InetAddress;

@javax.ejb.Stateless
public class StockTransferIC extends ValidatorEJB implements StockTransferICRemote, StockTransferICLocal  
{
	// Validation Code. Start	
	/**
	* The method is defined without any parameter and returns blank string
	*/
	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}
	
	/**
	* The public method is used for converting the current form data into a document(DOM)
	* The currDom 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 currFrmXmlStr contains the current form data in XML format
	* @param hdrFrmXmlStr contains all the header information in the XML format
	* @param allFrmXmlStr 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
	*/	
	public String wfValData(String currFrmXmlStr, String hdrFrmXmlStr,String allFrmXmlStr, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document currDom = null;
		Document hdrDom = null;
		Document allDom = null;
		String errString = "";
		
		try
		{			
			if(currFrmXmlStr != null && currFrmXmlStr.trim().length()!=0)
			{
				currDom = parseString(currFrmXmlStr); 
			}
			if(hdrFrmXmlStr != null && hdrFrmXmlStr.trim().length()!=0)
			{
				hdrDom = parseString(hdrFrmXmlStr); 
			}
			if(allFrmXmlStr != null && allFrmXmlStr.trim().length()!=0)
			{
				allDom = parseString(allFrmXmlStr);
			}
			errString = wfValData(currDom,hdrDom,allDom,objContext,editFlag,xtraParams);
		}//end of try
		catch(Exception e)
		{
			System.out.println("Exception : [StockTransferIC][wfValData(String currFrmXmlStr)] : ==>\n"+e.getMessage());
		}
		return (errString); 
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of specified fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currDom contains the current form data as a document object model
	 * @param hdrDom contains all the header information
	 * @param allDom 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
	 */	
	 public String wfValData(Document currDom, Document hdrDom, Document allDom, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException 	
	 {
		String errString = "";
		String columnValue = "";
		String sql = "";	
		String refSer = "";
		String locCodeTo = "";
		String tranDate = "";
		String singleLotSl = "";
		String itemLotOpt = "";
		String rowCountStr = "";
		String itemCode = "";
		String siteCode = "";
		String lotNo = "";
		String lpnNo = "";
		String lpnNoTo = "";
		String childNodeName = "";
		/*Added By Dipak On 4 June 2012 Start*/
		String resrvLocInvstat = "";
		String activePickInvstat = "", locCode = "";
		String casePickInvstat = "";
		String invStat = "";
		/*Added By Dipak On 4 June 2012 End*/      
		
		//Changed by Rohan on 18-03-13 for define variable.start
		int locCount = 0;
		String locInvList = "";
		String[] locList = null;
		String invStatLoc = "";
		boolean isLocToValid = true;
		//Changed by Rohan on 18-03-13 for define variable.end
		
		int noOfChilds = 0;
		int noOfParent = 0;                            
		int count = 0;    

		double quantity = 0d,shipperSize = 0;         

		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		DistCommon distCommon = new DistCommon();
        GenericUtility genericUtility = GenericUtility.getInstance();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "", phyHanBasis= "";
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		ArrayList<String> invStatList = new ArrayList<String>();//Added By Dipak
		
        
		try
		{
			
			int currentFormNo = 0, cnt = 0;		
					
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			
			
			Node childNode =null;
			
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			
			NodeList parentList = currDom.getElementsByTagName( "Detail" + currentFormNo );
			NodeList childList = null;	
			noOfParent = parentList.getLength();

			switch(currentFormNo)
			{
				case 1 :
				{
					System.out.println("Case 1 Validation");
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						childNode = childList.item( ctr );
						if( childNode.getNodeType() != Node.ELEMENT_NODE )
						{
							continue;
						}
						childNodeName = childNode.getNodeName();						
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if ( "tran_date".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("tran_date","NULLTRANDT",userId);
								//break;
								errList.add( "NULLTRANDT" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}
						else if ( "ref_ser".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("ref_ser","NULLREFSER",userId);
								//break;
								errList.add( "NULLREFSER" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							refSer = genericUtility.getColumnValue( "ref_ser", currDom );
							
							sql = "SELECT COUNT(*) AS COUNT FROM REFSER WHERE REF_SER = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, refSer);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
							}							
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						
							if(cnt == 0)
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString( "ref_ser" , "VTREFSER1" , userId );
								//break;
								errList.add( "VTREFSER1" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}						
						}
						else if ( "site_code".equalsIgnoreCase( childNodeName ) )
						{							
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("site_code","NULLSITE",userId);
								//break;
								errList.add( "NULLSITE" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							siteCode = genericUtility.getColumnValue( "site_code", currDom );
							
							sql = "SELECT COUNT(*) AS COUNT FROM SITE WHERE SITE_CODE = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCode);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
							}							
							rs.close();
							rs = null;							
							pstmt.close();
							pstmt = null;
							
							if(cnt == 0)
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString( "site_code" , "INVSITECD" , userId );
								//break;
								errList.add( "INVSITECD" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}
						else if ( "reas_code".equalsIgnoreCase( childNodeName ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("reas_code","NULLREASON",userId);
								//break;
								errList.add( "NULLREASON" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
						}
					}// for loop end
				}//case 1 end
				break;
				case 2 :
				{
					System.out.println("Case 2 Validation");
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{	
						childNode = childList.item( ctr );
						if( childNode.getNodeType() != Node.ELEMENT_NODE )
						{
							continue;
						}
						childNodeName = childNode.getNodeName();						
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
												
						if ( "loc_code".equalsIgnoreCase( childNodeName ) )
						{	
							locCode = genericUtility.getColumnValue( "loc_code", currDom );
							if (locCode == null)
							{
								    
								errList.add( "VMLOC4" );
								errFields.add( childNodeName.toLowerCase() );
							}
							else
							{
								sql = "SELECT COUNT(*) AS COUNT FROM LOCATION WHERE LOC_CODE = ? ";

								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1, locCode);
								rs = pstmt.executeQuery();
								if ( rs.next() )
								{
									count = rs.getInt("COUNT");
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								if ( count == 0 )
								{
									errList.add( "VMLOC6" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
						}
						else if ( "lpn_no".equalsIgnoreCase( childNodeName ) )
						{	
							lpnNo = genericUtility.getColumnValue( "lpn_no", currDom );
							locCode = genericUtility.getColumnValue( "loc_code", currDom );
							
							if ( childNode.getFirstChild() == null )
							{
								// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								//errString = getErrorString("lpn_no","NULLLPNNO",userId);
								//break;
								errList.add( "NULLLPNNO" );
								errFields.add( childNodeName.toLowerCase() );
								// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
							}
							else
							{
								/*Commented and changes below by gulzar at sun on 1/12/2012
								sql = "SELECT COUNT(*) AS COUNT " +
										"FROM STOCK STOCK, ITEM ITEM, LOCATION LOCATION " +
										"WHERE STOCK.ITEM_CODE = ITEM.ITEM_CODE AND STOCK.LOC_CODE = LOCATION.LOC_CODE "+
										"AND ((CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - "+
										"(CASE WHEN STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - "+
										"(CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END)) > 0 "+
										"AND LOT_SL = ? ";
								*/
								sql = "SELECT COUNT(*) AS COUNT " +
										"FROM STOCK STOCK, ITEM ITEM, LOCATION LOCATION " +
										"WHERE STOCK.ITEM_CODE = ITEM.ITEM_CODE AND STOCK.LOC_CODE = LOCATION.LOC_CODE "+
										"AND ((CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - (CASE WHEN STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END)) > 0 "+
										" AND STOCK.LOC_CODE = ?   AND LOT_SL = ? ";
								//End changes by gulzar on 1/12/2012

								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1, locCode);
								pstmt.setString(2, lpnNo);
								rs = pstmt.executeQuery();
								if ( rs.next() )
								{
									count = rs.getInt("COUNT");
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								if ( count == 0 )
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString("lpn_no","LPNNOTEXT",userId);
									//break;
									errList.add( "LPNNOTEXT" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
							}
						}
					}// for loop end
				}//case 2 end
				break;
				case 3:
				{
					System.out.println("Case 3 Validation");
					childList = parentList.item( 0 ).getChildNodes();
					noOfChilds = childList.getLength();
					for ( int ctr = 0; ctr < noOfChilds; ctr++ )  //Loop for each node of current detail
					{	
						childNode = childList.item( ctr );
						if( childNode.getNodeType() != Node.ELEMENT_NODE )
						{
							continue;
						}
						childNodeName = childNode.getNodeName();
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if( "loc_code__to".equalsIgnoreCase( childNodeName ) )
						{
							/*Added By Dipak On 4 June 2012 Start*/
							activePickInvstat = distCommon.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
							casePickInvstat = distCommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
							resrvLocInvstat = distCommon.getDisparams("999999","RESERV_LOCATION",conn);

							invStatList.add(activePickInvstat);
							invStatList.add(casePickInvstat);
							invStatList.add(resrvLocInvstat);
							/*Added By Dipak On 4 June 2012 End*/

							itemCode = genericUtility.getColumnValue("item_code", allDom, "3" );
							siteCode = genericUtility.getColumnValue("site_code", allDom, "1" );
							locCodeTo = checkNull(genericUtility.getColumnValue("loc_code__to", allDom, "3" ));
							lotNo = genericUtility.getColumnValue("lot_no__fr", allDom, "3" );
							lpnNo = genericUtility.getColumnValue("lot_sl__fr", allDom, "3" );
							
							//Changed by Rohan on 16-04-13 For Stock Transfer ,Expired stock can not be transfer from location configured in Disparm.start
							locCode = genericUtility.getColumnValue("loc_code", allDom, "2" );
							
							sql ="SELECT COUNT(*) AS LOCCOUNT FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? " +
								 " AND LOT_NO = ? AND LOT_SL = ? AND EXP_DATE <= fn_sysdate()";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, itemCode );
							pstmt.setString( 2, siteCode );
							pstmt.setString( 3, locCode );
							pstmt.setString( 4, lotNo );
							pstmt.setString( 5, lpnNo );
							rs = pstmt.executeQuery();
							if( rs.next() )       
							{
								locCount = rs.getInt("LOCCOUNT");
								
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs =null;
							
							if(locCount > 0)
							{
								locInvList = distCommon.getDisparams("999999","NOT_TRANF_INV_STAT",conn);
								System.out.println("locInvList>>>>>>"+locInvList);
								if((!"".equalsIgnoreCase(locInvList.trim()) || locInvList != null) && locInvList.trim().length() > 0 )
								{	
									locList = locInvList.split(",");
									
									sql = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
									pstmt = conn.prepareStatement( sql );
									pstmt.setString( 1, locCodeTo );
									rs = pstmt.executeQuery();
									if( rs.next() )       
									{
										invStatLoc = rs.getString("INV_STAT");
										
									}
									
									for(int i =0; i < locList.length ; i++)
								    {	  
										System.out.println("Comparing values"+invStatLoc+"locList[i]"+locList[i]);
										if(invStatLoc.trim().equalsIgnoreCase(locList[i].trim()))
										{
											errList.add( "VTLOCNTVAL" );
											errFields.add( childNodeName.toLowerCase());
											isLocToValid = false;
											break;
										}
								    }
								}
							}
							System.out.println("isLocToValid["+isLocToValid+"]");
							if(isLocToValid)
							{
							//Changed by Rohan on 16-04-13 For Stock Transfer ,Expired stock can not be transfer from location configured in Disparm.end

								sql = "SELECT SINGLE_LOT_SL, ITEM_LOT_OPT, INV_STAT FROM LOCATION WHERE LOC_CODE = ?";//Changes are done by Dipak On 4 June 2012
								pstmt = conn.prepareStatement( sql );
								pstmt.setString( 1, locCodeTo );
								rs = pstmt.executeQuery();
								if( rs.next() )       
								{
									singleLotSl = checkNull(rs.getString("SINGLE_LOT_SL"));
									itemLotOpt = checkNull(rs.getString("ITEM_LOT_OPT"));
									invStat = checkNull(rs.getString("INV_STAT"));//Added By Dipak On 4 June 2012
									
								}
								else
								{
									// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									//errString = getErrorString( "loc_code__to", "INVLOCCODE", userId );
									//break;
									errList.add( "VMLOC6" );
									errFields.add( childNodeName.toLowerCase() );
									// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								//Changed by Rohan on 21/07/12 to move stock to any location.start
								/*Added By Dipak On 4 June 2012 Start*/
								/*
								if(!(invStatList.contains(invStat.trim())))
								{
									errList.add( "VTLOCCODE8" );            
									errFields.add( childNodeName.toLowerCase() );
								}
								*/    
								/*Added By Dipak On 4 June 2012 End*/
								//Changed by Rohan on 21/07/12 to move stock to any location.end  
								if( singleLotSl.trim().equalsIgnoreCase("Y") )
								{
									//Change by Rohan on 19/07/12 for bug Fixing
									/*
									 sql = "SELECT QUANTITY FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ?"
											  + " AND LOC_CODE = ? AND lOT_NO = ? AND LOT_SL = ?";
									*/
									sql = "SELECT QUANTITY FROM STOCK WHERE SITE_CODE = ?"
										  + " AND LOC_CODE = ? ORDER BY QUANTITY DESC";
									
									pstmt = conn.prepareStatement( sql );
									//Change by Rohan on 19/07/12 for bug Fixing.start
									/*
									pstmt.setString( 1, itemCode );
									pstmt.setString( 2, siteCode );
									pstmt.setString( 3, locCodeTo );
									pstmt.setString( 4, lotNo );
									pstmt.setString( 5, lpnNo );
									*/
								
									pstmt.setString( 1, siteCode );
									pstmt.setString( 2, locCodeTo );
									//Change by Rohan on 19/07/12 for bug Fixing.end
									rs = pstmt.executeQuery();	
									if( rs.next() )
									{
										quantity = rs.getDouble("QUANTITY");
									}
									rs.close(); rs = null;
									pstmt.close(); pstmt = null;
									
									if( quantity != 0 )
									{
										// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
										//errString = getErrorString( "loc_code__to", "INVLOCCODE", userId );
										//break;
										errList.add( "LOCNOTEMPT" );
										errFields.add( childNodeName.toLowerCase() );
										// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
									}
								}
								else
								{
									if( itemLotOpt.trim().equals("0") )  // Validation for single item_code and single lot_no
									{
										//changed by sankara on 13/09/2012 to add quantity
										//sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO <> ?";
										sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE (ITEM_CODE <> ? OR LOT_NO <> ?) AND SITE_CODE = ? AND LOC_CODE = ? AND QUANTITY > 0"; 
										pstmt = conn.prepareStatement( sql );
										//changed by sankara on 13/09/2012 to set the strings in a row based on sql[start]
									/*	pstmt.setString( 1, itemCode );
										pstmt.setString( 2, siteCode );
										pstmt.setString( 3, locCodeTo );
										pstmt.setString( 4, lotNo ); */
										pstmt.setString( 1, itemCode );
										pstmt.setString( 2, lotNo );
										pstmt.setString( 3, siteCode );
										pstmt.setString( 4, locCodeTo );
										//changed by sankara on 13/09/2012 to set the strings in a row based on sql[End]
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											count = rs.getInt("COUNT");
										}
										rs.close(); rs = null;
										pstmt.close(); pstmt = null;
	
										if( count > 0 )
										{
											// 26/10/11 manoharan all the errors should be returned so that warnings can be processed
											//errString = getErrorString("loc_code__to", "INVLOCCODE", userId );
											//break;
											errList.add( "VTLOCCODE2" );
											errFields.add( childNodeName.toLowerCase() );
											// end 26/10/11 manoharan all the errors should be returned so that warnings can be processed
										}	
									}
									else if( itemLotOpt.trim().equals("1") )  //Validation for single item_code and multiple lot_no
									{
										/*--Commented by gulzar as it is not valid for single item and single lot
										sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO <> ?";
										pstmt = conn.prepareStatement( sql );
										pstmt.setString( 1, itemCode );
										pstmt.setString( 2, siteCode );
										pstmt.setString( 3, locCodeTo );
										pstmt.setString( 4, lotNo );
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											count = rs.getInt("COUNT");
										}
										rs.close();
										rs = null;
										pstmt.close();
										pstmt = null;
	
										if( count > 0 )
										{
											errString = getErrorString("loc_code__to", "VTLOCCODE3", userId );
											break;
										}
										*/
										 //changed by sankara on 13/09/2012 to add quantity
										//sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE ITEM_CODE <> ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO <> ?";
										sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE  ITEM_CODE <> ? AND SITE_CODE = ? AND LOC_CODE = ? AND QUANTITY > 0";
										pstmt = conn.prepareStatement( sql );
										//changed by sankara on 13/09/2012 to set the strings in a row based on sql[start]
									/*	pstmt.setString( 1, itemCode );
										pstmt.setString( 2, siteCode );
										pstmt.setString( 3, locCodeTo );
										pstmt.setString( 4, lotNo );      */
										pstmt.setString( 1, itemCode );
										pstmt.setString( 2, siteCode );
										pstmt.setString( 3, locCodeTo );
										//changed by sankara on 13/09/2012 to set the strings in a row based on sql[End]
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											count = rs.getInt("COUNT");
										}
										rs.close();
										rs = null;
										pstmt.close();
										pstmt = null;
	
										if( count > 0 )
										{
											errList.add( "VTLOCCODE4" );
											errFields.add( childNodeName.toLowerCase() );
										}
									}//end of itemLotOpt == "1"
								}//else of singleLotSl =="Y"
							//Changed by Rohan on 16-04-13 For Stock Transfer ,Expired stock can not be transfer from location configured in Disparm
							}	
						}//End of case "loc_code__to"
						else if( "lot_sl__to".equalsIgnoreCase( childNodeName ) )//Case added by gulzar on 21/12/11
						{
							lpnNoTo = genericUtility.getColumnValue("lot_sl__to", currDom );
							if ( lpnNoTo == null || lpnNoTo.trim().length() == 0 )
							{
								errList.add( "NULLLPNNO" );
								errFields.add( childNodeName.toLowerCase() );
							}
							
							//Chnaged by Rohan on 10-02-12 allow lpn no start with NJ & CN & allow only 10 Character.start
							if(!lpnNoTo.startsWith("NJ") && !lpnNoTo.startsWith("CN") && !lpnNoTo.startsWith("DM") && !lpnNoTo.startsWith("EX"))
							{
								errList.add( "VTINVLPN" );
								errFields.add( childNodeName.toLowerCase());
							}
							
							if(lpnNoTo.length() != 10)
							{
								errList.add( "VTINVLEN" );
								errFields.add( childNodeName.toLowerCase());
							}
							//Chnaged by Rohan on 10-02-12 allow lpn no start with NJ & CN & allow only 10 Character.end
							
							//Changed by Rohan on 25/05/13 checking duplicate  lot_sl__new from dom and stock start.
							sql = " SELECT LOT_SL FROM STOCK WHERE LOT_SL = ? AND SITE_CODE = ? ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1, lpnNoTo );
							pstmt.setString(2, siteCode );
							rs = pstmt.executeQuery();	
							if( rs.next() )
							{
								errList.add( "DUPLOTSLNW" );
								errFields.add( childNodeName.toLowerCase() );
							}
							
							//Changed by Rohan on 25/05/13 checking duplicate  lot_sl__new from dom and stock end. 
							
						}
						else if( "quantity".equalsIgnoreCase( childNodeName ) )
						{
							itemCode = genericUtility.getColumnValue("item_code", allDom, "3" );
							siteCode = genericUtility.getColumnValue("site_code", allDom, "1" );
							locCode = genericUtility.getColumnValue("loc_code__fr", allDom, "3" );
							lotNo = genericUtility.getColumnValue("lot_no__fr", allDom, "3" );
							lpnNo = genericUtility.getColumnValue("lot_sl__fr", allDom, "3" );
							String sQuantity = genericUtility.getColumnValue("quantity", allDom, "3" );
							String sNoArt = genericUtility.getColumnValue("no_art", allDom, "3" );
							if (sQuantity == null || sQuantity.trim().length() == 0)
							{
								sQuantity = "0";
							}
							if (sNoArt == null || sNoArt.trim().length() == 0)
							{
								sNoArt = "0";
							}
							quantity = Double.parseDouble(sQuantity);
							int noArt = Integer.parseInt(sNoArt);
							int stkNoArt =0;
							double stkQuantity = 0;
							//Changed by sumit on 27/09/12 wrong sql, lot_no is invalid column name start.
							/*sql = "SELECT shipper_size FROM item_lot_packsize "
							+ " WHERE ITEM_CODE = ? "
							+ " AND LOT_NO <= ? "
							+ " AND LOT_NO >= ? ";*/
							
							sql = "SELECT shipper_size FROM item_lot_packsize "
									+ " WHERE ITEM_CODE = ? "
									+ " AND LOT_NO__FROM <= ? "
									+ " AND LOT_NO__TO >= ? ";
							//Changed by sumit on 27/09/12 wrong sql, lot_no is invalid column name start.
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, itemCode );
							pstmt.setString( 2, lotNo );
							pstmt.setString( 3, lotNo );
							rs = pstmt.executeQuery();
							if( rs.next() )       
							{
								shipperSize = rs.getDouble(1);
								
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							sql = "SELECT (quantity - case when alloc_qty is null then 0 else alloc_qty end)  as quantity,no_art FROM stock "
							+ " WHERE ITEM_CODE = ? "
							+ " AND SITE_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, itemCode );
							pstmt.setString( 2, siteCode );
							pstmt.setString( 3, locCode );
							pstmt.setString( 4, lotNo );
							pstmt.setString( 5, lpnNo );
							rs = pstmt.executeQuery();
							if( rs.next() )       
							{
								stkQuantity = rs.getDouble(1);
								stkNoArt = rs.getInt(2);
								
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							
							if (noArt > 0 )
							{
								quantity = shipperSize * noArt;
							}
							else if (noArt == 0  && quantity == 0) 
							{
								quantity = stkQuantity;
							}
							if ( quantity > stkQuantity ) 
							{
								errList.add( "VTSTKQTY" );
								errFields.add( childNodeName.toLowerCase() );
							}
							
							sql = "SELECT CASE WHEN PHY_HAN_BASIS IS NULL THEN ' ' ELSE PHY_HAN_BASIS END AS PHY_HAN_BASIS FROM LOCATION WHERE LOC_CODE = ? ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, locCode );
							rs = pstmt.executeQuery();
							if( rs.next() )       
							{
								phyHanBasis = rs.getString("PHY_HAN_BASIS");
								
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							/*if ("L".equalsIgnoreCase(phyHanBasis) && () )
							{
							}*/
						}
						
					}//End of inner for loop
					/*
					for( int prnt = 0; prnt < noOfParent; prnt++ )  // Loop for multiple details in allFormDataDom
					{
						childList = parentList.item(prnt).getChildNodes();
						noOfChilds = childList.getLength();
						
						rowCountStr = ""+(prnt+1);
						System.out.println("rowCountStr=["+rowCountStr+"]");
						
						for ( int ctr = 0; ctr < noOfChilds; ctr++ )  //Loop for each node of current detail
						{	
							childNode = childList.item( ctr );
							if( childNode.getNodeType() != Node.ELEMENT_NODE )
							{
								continue;
							}
							childNodeName = childNode.getNodeName();
							if ( childNode != null && childNode.getFirstChild() != null )
							{
								columnValue = childNode.getFirstChild().getNodeValue();
							}
							System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
							if( "loc_code__to".equalsIgnoreCase( childNodeName ) )
							{								
								itemCode = genericUtility.getColumnValue("item_code", allDom, "3", rowCountStr );
								siteCode = genericUtility.getColumnValue("site_code", allDom, "1", rowCountStr );
								locCodeTo = genericUtility.getColumnValue("loc_code__to", allDom, "3", rowCountStr );
								lotNo = genericUtility.getColumnValue("lot_no__fr", allDom, "3", rowCountStr );
								lpnNo = genericUtility.getColumnValue("lot_sl__fr", allDom, "3", rowCountStr );

								sql = "SELECT SINGLE_LOT_SL, ITEM_LOT_OPT FROM LOCATION WHERE LOC_CODE = ?";
								pstmt = conn.prepareStatement( sql );
								pstmt.setString( 1, locCodeTo );
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									singleLotSl = checkNull(rs.getString("SINGLE_LOT_SL"));
									itemLotOpt = checkNull(rs.getString("ITEM_LOT_OPT"));
								}
								else
								{
									errString = getErrorString( "loc_code__to", "INVLOCCODE", userId );
									break;
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
								
								if( singleLotSl.trim().equalsIgnoreCase("Y") )
								{
									sql = "SELECT QUANTITY FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ?"
											  + " AND LOC_CODE = ? AND lOT_NO = ? AND LOT_SL = ?";
									pstmt = conn.prepareStatement( sql );
									pstmt.setString( 1, itemCode );
									pstmt.setString( 2, siteCode );
									pstmt.setString( 3, locCodeTo );
									pstmt.setString( 4, lotNo );
									pstmt.setString( 5, lpnNo );
									rs = pstmt.executeQuery();	
									if( rs.next() )
									{
										quantity = rs.getDouble("QUANTITY");
									}
									rs.close(); rs = null;
									pstmt.close(); pstmt = null;
									
									if( quantity != 0 )
									{
										errString = getErrorString( "loc_code__to", "INVLOCCODE", userId );
										break;
									}
								}
								else
								{
									if( itemLotOpt.trim().equals("0") )  // Validation for single item_code and single lot_no
									{
										sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO <> ?";
										pstmt = conn.prepareStatement( sql );
										pstmt.setString( 1, itemCode );
										pstmt.setString( 2, siteCode );
										pstmt.setString( 3, locCodeTo );
										pstmt.setString( 4, lotNo );
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											count = rs.getInt("COUNT");
										}
										rs.close(); rs = null;
										pstmt.close(); pstmt = null;

										if( count > 0 )
										{
											errString = getErrorString("loc_code__to", "INVLOCCODE", userId );
											break;
										}	
									}
									else if( itemLotOpt.trim().equals("1") )  //Validation for single item_code and multiple lot_no
									{
										sql = "SELECT COUNT(*) AS COUNT FROM STOCK WHERE  ITEM_CODE <> ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? ";
										pstmt = conn.prepareStatement( sql );
										pstmt.setString( 1, itemCode );
										pstmt.setString( 2, siteCode );
										pstmt.setString( 3, locCodeTo );
										pstmt.setString( 4, lotNo );
										rs = pstmt.executeQuery();
										if( rs.next() )
										{
											count = rs.getInt("COUNT");
										}
										rs.close(); rs = null;
										pstmt.close(); pstmt = null;

										if( count > 0 )
										{
											errString = getErrorString("loc_code__to", "INVLOCCODE", userId );
											break;
										}	
									}//end of itemLotOpt == "1"
								}//else of singleLotSl =="Y"
							}//End of case "loc_code__to"
						}//End of inner for loop
						if ( errString != null && errString.trim().length() > 0 )
						{
							break;
						}
					}//End of outer for loop
					*/
				}//case3 end
			} //switch 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;//+"<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();
		}//try block end
		catch(Exception e)
		{
			System.out.println("Exception in StockTransferIC  == >");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( conn != null && ! conn.isClosed() )
				{
					conn.close();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :StockTransferIC:wfValData :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		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;
	}

	 // Item Change Functionality. Start from here....		
	/**
	* The public method is defined without any parameters and returns blank string
	*/
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}
	
	/**
	* The public method is used for converting the current form data into a document(dom)
	* The currDom 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 currFrmXmlStr contains the current form data in XML format
	* @param hdrFrmXmlStr contains all the header information in the XML format
	* @param allFrmXmlStr contains the data of all the forms in XML format
	* @param objContext represents the form number
	* @param currentColumn represents the value of current field.
	* @param editFlag represents the mode of transaction(A-Add or E-Edit)
	* @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	*/
	public String itemChanged(String currFrmXmlStr, String hdrFrmXmlStr, String allFrmXmlStr, 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 (currFrmXmlStr != null && currFrmXmlStr.trim().length()!=0)
			{
				currDom = genericUtility.parseString(currFrmXmlStr); 
			}
			if (hdrFrmXmlStr != null && hdrFrmXmlStr.trim().length()!=0)
			{
				hdrDom = genericUtility.parseString(hdrFrmXmlStr); 
			}
			if (allFrmXmlStr != null && allFrmXmlStr.trim().length()!=0)
			{
				allDom = genericUtility.parseString(allFrmXmlStr); 
			}
			errString = itemChanged( currDom, hdrDom, allDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception : StockTransferIC:defaul_ItemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
		}
		System.out.println ( "returning from StockTransferIC default_Itemchanged" );
		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 currDom contains the current form data as a document object model
	 * @param hdrDom contains all the header information
	 * @param allDom contains the field data of all the forms 
	 * @param objContext represents form number
	 * @param currentColumn represents the current field 
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information
	 * 
	 */
	public String itemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		String retValue = "";
		try
		{
			retValue = default_ItemChanged( currDom, hdrDom, allDom,  objContext, currentColumn, editFlag, xtraParams );
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return retValue;
		
	}
	public String default_ItemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		String sql = "";
		String itemCode = "";
		String itemDescr = "";
		String tranId = "";		
		String tranDate = "";				
		String columnValue = "";		
		String refSerFor = "";
		String reasCode = "";
		String confirmed = "";
		String confDate = "";
		String siteCode = "";	
		String siteDescr = "";	
		String empCodeAprv = "";
		String fullName = "";
		String lpnNo = "";
		String lotSl = "";
		String noArt = "";
		String locDescr = "";
		String acctCode = "";
		String cctrCode = "";
		String lotNo = "";
		String locCode = "";
		String unit = "", phyHanBasis = "";
		String grlInvstat = "";//Added By Dipak On 4 June 2012
				
		int currentFormNo = 0;
		int domID = 0;	
		
		double quantity = 0d; 
			
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
				
		StringBuffer valueXmlString = new StringBuffer();		
		
		System.out.println("hello:");
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		try
		{
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			
			columnValue = genericUtility.getColumnValue( currentColumn, currDom );
			
			DateFormat dateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();

			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");

			DistCommon dComm = new DistCommon();
			
			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 1:
				{
					valueXmlString.append( "<Detail1 domID='1'>\r\n" );
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )							
					{	
						java.util.Date currDate = new java.util.Date();
						SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
						String currDateStr = sdf.format(currDate);

						String defReasCode = dComm.getDisparams("999999","DEFAULT_REAS_CODE",conn);
						
						String loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
						String chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
						siteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
						if ( siteCode.trim().length() == 0 || "INVALID_SITE".equalsIgnoreCase(siteCode.trim()) )
						{
							siteCode = getSiteCode( xtraParams, conn );
						}
						System.out.println("siteCode = ["+siteCode+"]");

						valueXmlString.append( "<tran_id/>" );
						valueXmlString.append( "<tran_date><![CDATA[" ).append( currDateStr ).append( "]]></tran_date>\r\n" );
						valueXmlString.append( "<ref_ser__for><![CDATA[" ).append( "XFRX" ).append( "]]></ref_ser__for>\r\n" );
						valueXmlString.append( "<site_code><![CDATA[" ).append(  checkNull ( siteCode)).append( "]]></site_code>\r\n" );
						
						sql = "SELECT DESCR FROM SITE WHERE SITE_CODE = ?";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, siteCode );						
						rs = pstmt.executeQuery();	
						if( rs.next() )
						{
							siteDescr = rs.getString("DESCR");
						}
						rs.close();rs = null;
						pstmt.close();pstmt = null;

						valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull( siteDescr )).append( "]]></site_descr>\r\n" );
						valueXmlString.append( "<reas_code><![CDATA[" ).append(  checkNull ( defReasCode)).append( "]]></reas_code>\r\n" );
						valueXmlString.append( "<confirmed><![CDATA[" ).append("N").append( "]]></confirmed>\r\n" );
						valueXmlString.append( "<chg_user><![CDATA[" ).append( loginCode ).append( "]]></chg_user>\r\n" );
						valueXmlString.append( "<chg_date><![CDATA[" ).append( currDateStr ).append( "]]></chg_date>\r\n" );
						valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
					}
					valueXmlString.append("</Detail1>\r\n");
				}//case 1 end here
				break;
			  	case 3:
			  	{
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )							
					{
						lpnNo = checkNull(genericUtility.getColumnValue("lpn_no", allDom, "2"));
						locCode = checkNull(genericUtility.getColumnValue("loc_code", allDom, "2"));
						grlInvstat = dComm.getDisparams("999999","GRL_INVSTAT",conn);//Added By Dipak On 4 June 2012
						
						if( lpnNo.trim().length() > 0 )
						{
							/*--Commented and changed by gulzar on 1/12/2012
							sql = "SELECT STOCK.ITEM_CODE, STOCK.LOC_CODE, STOCK.LOT_NO, STOCK.LOT_SL, STOCK.QUANTITY," +
									"STOCK.NO_ART, STOCK.ACCT_CODE__INV, STOCK.CCTR_CODE__INV, " +
									"ITEM.DESCR ITEM_DESCR, ITEM.UNIT UNIT, LOCATION.DESCR LOC_DESCR " +
									"FROM STOCK STOCK, ITEM ITEM, LOCATION LOCATION " +
									"WHERE STOCK.ITEM_CODE = ITEM.ITEM_CODE AND STOCK.LOC_CODE = LOCATION.LOC_CODE "+
									"AND ((CASE WHEN STOCK.QUANTITY IS NULL THEN 0 ELSE STOCK.QUANTITY END) - "+
									"(CASE WHEN STOCK.ALLOC_QTY IS NULL THEN 0 ELSE STOCK.ALLOC_QTY END) - "+
									"(CASE WHEN STOCK.HOLD_QTY IS NULL THEN 0 ELSE STOCK.HOLD_QTY END)) > 0 "+
									"AND LOT_SL = ? ";
							*/

							/*Changes Are Done By Dipak On 4 June 2012 Start*/
							sql = "SELECT A.ITEM_CODE, A.SITE_CODE, A.LOC_CODE, A.LOT_NO, A.LOT_SL," +
									"C.ITEM_SER, C.DESCR ITEM_DESCR, A.QUANTITY, A.UNIT, A.GRADE, A.EXP_DATE, A.LTRAN_DATE,"+
									"A.REMARKS, D.INV_STAT, D.DESCR LOC_DESCR, A.PACK_CODE, A.ALLOC_QTY, A.MFG_DATE, A.NO_ART, " +
									"A.SITE_CODE__MFG, A.POTENCY_PERC, A.RATE, A.GROSS_RATE, A.RETEST_DATE,"+
									"A.CONV__QTY_STDUOM, A.UNIT__ALT, A.BATCH_NO, A.ACCT_CODE__INV,A.CCTR_CODE__INV , D.PHY_HAN_BASIS "+
									"FROM STOCK A, INVSTAT B, ITEM C, LOCATION D "+
									"WHERE A.LOC_CODE = D.LOC_CODE AND D.INV_STAT = B.INV_STAT "+
									"AND A.ITEM_CODE = C.ITEM_CODE AND B.AVAILABLE = 'Y' "+
									"AND (CASE WHEN B.STAT_TYPE IS NULL THEN ' ' ELSE B.STAT_TYPE END) <> 'S' " +
									"AND ((CASE WHEN A.QUANTITY IS NULL THEN 0 ELSE A.QUANTITY END) - (CASE WHEN A.ALLOC_QTY IS NULL THEN 0 ELSE A.ALLOC_QTY END)) > 0 "+
									//Changed by Rohan on 21/07/12 to move items for any location 
									//"AND D.INV_STAT = ? AND A.LOT_SL = ? ";
									" AND A.LOC_CODE = ? AND A.LOT_SL = ? ";
								
							/*Changes Are Done By Dipak On 4 June 2012 End*/
							//End changes by gulzar on 1/12/2012

							pstmt =  conn.prepareStatement(sql);
							//Changed by Rohan on 21/07/12 to move items for any location 
							/*
							pstmt.setString(1,grlInvstat);
							pstmt.setString(2,lpnNo );//Added By Dipak On 4 June 2012.
							*/
							pstmt.setString(1,locCode );
							pstmt.setString(2,lpnNo );
							rs = pstmt.executeQuery(); 
							while (rs.next())
							{								
								itemCode = rs.getString("ITEM_CODE");                     
								//locCode = rs.getString("LOC_CODE");     
								lotNo = rs.getString("LOT_NO");
								lotSl = rs.getString("LOT_SL");
								quantity = rs.getDouble("QUANTITY");
								unit = rs.getString("UNIT");
								noArt = rs.getString("NO_ART");
								itemDescr = rs.getString("ITEM_DESCR");
								locDescr = rs.getString("LOC_DESCR");
								acctCode = rs.getString("ACCT_CODE__INV");
								cctrCode = rs.getString("CCTR_CODE__INV");
								phyHanBasis = rs.getString("PHY_HAN_BASIS");
								domID++;

								valueXmlString.append( "<Detail3 domID='"+ domID +"' selected=\"N\">\r\n" );
								valueXmlString.append("<tran_id/>\r\n");
								//valueXmlString.append("<line_no/>\r\n");
								//In feed form line_no currently is not generating automatically so line_no is set as "1"
								valueXmlString.append("<line_no><![CDATA[").append("1").append("]]></line_no>\r\n");
								valueXmlString.append("<item_code><![CDATA[").append(checkNull(itemCode)).append("]]></item_code>\r\n");
								valueXmlString.append("<item_descr><![CDATA[").append(checkNull(itemDescr)).append("]]></item_descr>\r\n");
								valueXmlString.append("<loc_code__fr><![CDATA[").append(checkNull(locCode)).append("]]></loc_code__fr>\r\n");
								valueXmlString.append("<loc_descr__fr><![CDATA[").append(checkNull(locDescr)).append("]]></loc_descr__fr>\r\n");
								valueXmlString.append("<loc_code__to/>\r\n");
								valueXmlString.append("<lot_no__fr><![CDATA[").append(checkNull(lotNo)).append("]]></lot_no__fr>\r\n");
								valueXmlString.append("<lot_no__to><![CDATA[").append(checkNull(lotNo)).append("]]></lot_no__to>\r\n");								
								valueXmlString.append("<lot_sl__fr><![CDATA[").append(checkNull(lotSl)).append("]]></lot_sl__fr>\r\n");
								valueXmlString.append("<lot_sl__to><![CDATA[").append("").append("]]></lot_sl__to>\r\n");
								if ("P".equalsIgnoreCase(phyHanBasis))
								{
									valueXmlString.append("<quantity><![CDATA[" + quantity + "]]></quantity>\r\n");
									valueXmlString.append("<no_art><![CDATA[" + noArt + "]]></no_art>\r\n");
								}
								else
								{
									valueXmlString.append("<quantity><![CDATA[0]]></quantity>\r\n");
									valueXmlString.append("<no_art><![CDATA[0]]></no_art>\r\n");
								}
								valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");
								valueXmlString.append("<acct_code__cr><![CDATA[").append(checkNull(acctCode)).append("]]></acct_code__cr>\r\n");
								valueXmlString.append("<cctr_code__cr><![CDATA[").append(checkNull(cctrCode)).append("]]></cctr_code__cr>\r\n");
								valueXmlString.append("<acct_code__dr><![CDATA[").append(checkNull(acctCode)).append("]]></acct_code__dr>\r\n");
								valueXmlString.append("<cctr_code__dr><![CDATA[").append(checkNull(cctrCode)).append("]]></cctr_code__dr>\r\n");								
								valueXmlString.append("<phy_han_basis><![CDATA[").append(checkNull(phyHanBasis)).append("]]></phy_han_basis>\r\n");
								
								valueXmlString.append("<remarks/>\r\n");
								valueXmlString.append("</Detail3>\r\n");
							}
							rs.close();
							rs = null;						
							pstmt.close();
							pstmt = null;
						}//inner if  
					}//outer if
					break;				  		
			  	}// case 3
			}//switch  end
		}//try block end
		catch(Exception e)
		{
			System.out.println( "Exception :StockTransferIC :default_ItemChanged(Document,String):" + e.getMessage() + ":" );
			valueXmlString = valueXmlString.append( genericUtility.createErrorString( e ) );
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( conn != null && ! conn.isClosed() )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :StockTransferIC:default_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 inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		return inputVal;
	}
	private String getSiteCode( String xtraParams, Connection connObject ) throws ITMException
	{
		String defaultSite = "";
		String empSite = "";
		String workSite = "";
		String sql = "";
		String loginCode = "";
	
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));

			sql = "SELECT USERS.EMP_CODE, USERS.ENTITY_CODE, USERS.SITE_CODE__DEF, EMP.EMP_SITE, EMP.WORK_SITE "+
					"FROM USERS USERS, EMPLOYEE EMP WHERE USERS.EMP_CODE = EMP.EMP_CODE AND USERS.CODE = ? ";
			pstmt = connObject.prepareStatement( sql );
			pstmt.setString( 1, loginCode );						
			rs = pstmt.executeQuery();	
			if( rs.next() )
			{
				defaultSite = checkNull(rs.getString("SITE_CODE__DEF"));
				empSite = checkNull(rs.getString("EMP_SITE"));
				workSite = checkNull(rs.getString("WORK_SITE"));
			}
			if ( defaultSite.trim().length() == 0 && empSite.trim().length() > 0 )
			{
				defaultSite = empSite;
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
		}
		catch (Exception 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);
			}
		}
		System.out.println("defaultSite = ["+defaultSite+"]");
		return defaultSite;
	}
	
}
