package ibase.webitm.ejb.dis;

import java.rmi.RemoteException;
import java.text.*;
import java.util.Date;
import java.sql.*;
import java.text.SimpleDateFormat;
import org.w3c.dom.*;
import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class ChargeBackLoc extends ValidatorEJB implements ChargeBackLocLocal, ChargeBackLocRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance(); 
	String loginSite = "";

	/* public void ejbCreate() throws RemoteException, CreateException 
	{
		System.out.println("ChargeBackLocEJB is in Process..........");
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	} */
	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}

	public String itemChanged() throws RemoteException,ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1,String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String  errString = null;
		System.out.println("Validation Start..........");
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1); 
			if (xmlString2.trim().length() > 0 )
			{
				dom2 = parseString(xmlString2);
			}
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : AssetRegisterICEJB : wfValData(String xmlString) : ==>\n"+e.getMessage());
		}
		return (errString);
	}
	public String wfValData(Document dom, Document dom1,Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = " ";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String columnValue = null;
		String childNodeName = null;
		String billNo = "";
		String tranId = "";
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		String errCode = null;
		String userId = null;
		String custCode = null;
		String itemCode = "";
		String quantity = "";
		String saleQty = "";
		String saleRetQty = "";
		String unconfQty = "";
		String confQty = "";
		int cnt = 0;
		int ctr = 0;
		int currentFormNo = 0;
		int childNodeListLength;
		Connection conn = null;
		PreparedStatement pStmt=null;
		ResultSet rs = null;
		String sql = null;
		ConnDriver connDriver = new ConnDriver();
		String lineNoDet = null;
		String updateFlag = null;
    	try
		{
			System.out.println( "wfValData called" );
			conn = connDriver.getConnectDB("DriverITM");
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
				case 1 :
					System.out.println("VALIDATION FOR DETAIL [ 1 ]..........");
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if ( childNodeName.equalsIgnoreCase( "cust_code" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("cust_code","VMCUSTNUL",userId);
								break ;
							}
							else
							{
								custCode = genericUtility.getColumnValue( "cust_code", dom, "1" );
								
								sql = " SELECT COUNT(*) FROM customer WHERE cust_code = ? ";
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt( 1 );
								} 
								if( cnt == 0 )
								{
									errCode = "VMINVCUST";
									errString = getErrorString( "cust_code", errCode, userId );
									break ;
								}
								rs.close();
								rs = null;

								pStmt.close();
								pStmt = null;
							}
						}
						if ( childNodeName.equalsIgnoreCase( "tran_type" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("tran_type","VMTRNTPNUL",userId);
								break ;
							}
						}
						if ( childNodeName.equalsIgnoreCase( "type" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("type","VMSDRTPNUL",userId);
								break ;
							}
						}
						if ( childNodeName.equalsIgnoreCase( "item_ser" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("item_ser","VMITMSRNUL",userId);
								break ;
							}
						}
						if ( childNodeName.equalsIgnoreCase( "cust_code__credit" ) )
						{
							custCode = genericUtility.getColumnValue( "cust_code__credit", dom, "1" );
							if( custCode != null && custCode.trim().length() > 0 )	
							{
								sql = " SELECT COUNT(*) FROM customer WHERE cust_code = ? ";
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt( 1 );
								} 
								if( cnt == 0 )
								{
									errCode = "VMINVCUST";
									errString = getErrorString( "cust_code__credit", errCode, userId );
									break ;
								}
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
						}	
					} 
					//END OF CASE1
					break;
				case 2 :
					System.out.println("VALIDATION FOR DETAIL [ 2 ]..........");
					//String update_flag = null;
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						
						if ( childNodeName.equalsIgnoreCase( "item_code" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("item_code","VTITMNUL",userId);
								break ;
							}
							else
							{
								itemCode = genericUtility.getColumnValue( "item_code", dom );
								if( itemCode != null & itemCode.trim().length() > 0 )
								{
									sql = "SELECT count(*) from item"
									      +" where item_code = ? ";
									pStmt = conn.prepareStatement( sql );
									pStmt.setString(1,itemCode.trim());
									rs = pStmt.executeQuery();
									if( rs.next() )
									{
										cnt = rs.getInt( 1 );
									} 
									System.out.println(" COUNT =====> [" + cnt + "]");
									if( cnt == 0 )
									{
										errString =itmDBAccessEJB.getErrorString("item_code","VTINVITM",userId);
										break ;
									}
									rs.close();
									rs = null;
									pStmt.close();
									pStmt = null;
								}
							}
						}
						if ( childNodeName.equalsIgnoreCase( "lot_no" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("lot_no","VTLOTNUL",userId);
								break ;
							}
						}
						if ( childNodeName.equalsIgnoreCase( "porder_no" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("porder_no","VTBILNONUL",userId);
								break ;
							}
							else
							{
								billNo = genericUtility.getColumnValue( "porder_no", dom );
								custCode = genericUtility.getColumnValue( "cust_code", dom1 );
								tranId = genericUtility.getColumnValue( "tran_id", dom1 );
								
								sql = "Select count(1) from charge_back A, charge_back_det B where"
								     +" A.tran_id = B.tran_id "
									 +" and A.cust_code = ? "
									 +" and B.porder_no = ? "
									 +" and A.tran_id <> '"+tranId+"' ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, custCode.trim() );
								pStmt.setString(2, billNo.trim() );
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt( 1 );
								}
								pStmt.close();
								rs.close();
								if( cnt >= 1 )
								{
									errString =itmDBAccessEJB.getErrorString("porder_no","VTBILNOPST",userId);
									break ;
								}
							}
						}
						if ( childNodeName.equalsIgnoreCase( "porder_date" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("porder_date","VTBILDTNUL",userId);
								break ;
							}
						}
						/* if ( childNodeName.equalsIgnoreCase( "discount_per" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("discount_per","VTDISPNUL",userId);
								break ;
							}
						} */
						if ( childNodeName.equalsIgnoreCase( "item_ser" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("item_ser","VTITMSRNUL",userId);
								break ;
							}
						}
						if ( childNodeName.equalsIgnoreCase( "quantity" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("quantity","VTQUANTNUL",userId);
								break ;
							}
							else
							{
								quantity = genericUtility.getColumnValue( "quantity", dom );
								saleQty = genericUtility.getColumnValue( "sale_qty", dom );
								saleRetQty = genericUtility.getColumnValue( "sale_ret_qty", dom );
								unconfQty = genericUtility.getColumnValue( "unconf_claimed", dom );
								confQty = genericUtility.getColumnValue( "conf_claimed", dom );
								
								double qtyDbl = Double.parseDouble(quantity);
								double saleQtyDbl = Double.parseDouble(saleQty);
								double saleRetQtyDbl = Double.parseDouble(saleRetQty);
								double unconfQtyDbl = Double.parseDouble(unconfQty);
								double confQtyDbl = Double.parseDouble(confQty);
								
								if( qtyDbl > ( saleQtyDbl + saleRetQtyDbl - unconfQtyDbl - confQtyDbl ) )
								{
									errString =itmDBAccessEJB.getErrorString("quantity","VTINVQUANT",userId);
									break ;
								}
							}
						}
						if ( childNodeName.equalsIgnoreCase( "rate__contr" ) )
						{
							if ( childNode.getFirstChild() == null )
							{
								errString =itmDBAccessEJB.getErrorString("rate__contr","VTRATCRNUL",userId);
								break ;
							}
						}
					}//END FOR OF CASE2
					break;
			}//END SWITCH
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception ::" +e);
			e.printStackTrace();
			errCode = "VALEXCEP";
			errString = getErrorString( "", errCode, userId );									
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if( pStmt != null )
					{
						pStmt.close();
						pStmt = null;
					}
					
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					conn.close();
				}
				conn = null;
			}
			catch(Exception d)
				{
				  d.printStackTrace();
				}			
			System.out.println(" < AssetRegisterIcEJB > CONNECTION IS CLOSED");
		}
		System.out.println("ErrString ::" + errString);

		return errString;
	}//END OF VALIDATION 

	public String itemChanged(String xmlString, String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = null;
		try
		{
			System.out.println("xmlString" + xmlString);
			dom = parseString(xmlString); 
			System.out.println("xmlString1" + xmlString1);
			dom1 = parseString(xmlString1); 

			if (xmlString2.trim().length() > 0 )
			{
				System.out.println("xmlString2" + xmlString2);
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [AssetRegisterICEJB][itemChanged] :==>\n"+e.getMessage());
		}
        return valueXmlString; 
	}
	
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = null;
		int currentFormNo = 0;
		StringBuffer valueXmlString = new StringBuffer();
		String columnValue = null;
		NodeList parentNodeList = null;
		Node parentNode = null; 
		Node childNode = null;
		NodeList childNodeList = null;
		String childNodeName = null;
		int childNodeListLength = 0;
		int ctr = 0;
		int cnt = 0;
		String itemCode = "";
		String siteCode = "";
		String lotNo = "";
		String rateSell = "";
		String discPer = "";
		String rateContr = "";
		String tranId = "";
		String lineNo = ""; 
		String itmchgData = "";
		String custName = "";
		String priceList = "";
		String itemSeries = "";
		String firstName = "", middleName = "", lastName = "";
		String  chguserhdr = null;
		String type = "";
		String  chgtermhdr = null;
		String custCode = "";
		SimpleDateFormat sdf = null;
		String siteDescr = "";
		String currCode = "";
		double exchRate = 0.0;
		String custCdEnd = "";
		double saleQty = 0.0;
		double discPerc = 0.0;
		double saleRetQty = 0.0;
		double confQty = 0.0, rate = 0.0, discPerUnit = 0.0;
		double unconfQty = 0.0, rateSellDbl = 0.0, discPerDbl = 0.0, rateContrDbl = 0.0;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
		try
		{
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			chguserhdr = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgtermhdr = getValueFromXTRA_PARAMS(xtraParams, "chgTerm");
			
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			
			System.out.println("Current Form No ["+currentFormNo+"]");							
			switch (currentFormNo)
			{
				case 1:
					valueXmlString.append("<Detail1>");	
					//SEARCHING THE DOM FOR THE INCOMING COLUMN VALUE START
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					ctr = 0;
					childNodeListLength = childNodeList.getLength();
					do
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if(childNodeName.equals(currentColumn))
						{
							if (childNode.getFirstChild() != null)
							{
								columnValue=childNode.getFirstChild().getNodeValue().trim();
							}
						}
						ctr++;
					}while(ctr < childNodeListLength  && !childNodeName.equals(currentColumn));
					System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
					if (currentColumn.trim().equals( "itm_default" ))
					{
						if( loginSite != null || loginSite.trim().length()==0 )
						{
							sql = "select descr from site where site_code = ? ";
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,loginSite);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								siteDescr = rs.getString( "descr" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							
							sql = "select curr_code from finent where fin_entity = ( Select fin_entity "
							     +" from site where site_code = '"+loginSite+"')";
							pStmt = conn.prepareStatement( sql );
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								currCode = rs.getString( "curr_code" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							if( currCode != null && currCode.trim().length() > 0 )
							{
								sql = "select std_exrt from currency where curr_code = ? ";
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,currCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									exchRate = rs.getDouble( "std_exrt" );
								}	
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}

							valueXmlString.append("<tran_date>").append("<![CDATA[" + getCurrdateAppFormat() + "]]>").append("</tran_date>");
							valueXmlString.append("<eff_date>").append("<![CDATA[" + getCurrdateAppFormat() + "]]>").append("</eff_date>");
							valueXmlString.append("<site_code__log>").append("<![CDATA[" + ( loginSite )+ "]]>").append("</site_code__log>");
							valueXmlString.append("<site_log_descr>").append("<![CDATA[" + ( siteDescr )+ "]]>").append("</site_log_descr>");
							valueXmlString.append("<site_code>").append("<![CDATA[" + ( loginSite )+ "]]>").append("</site_code>");
							valueXmlString.append("<site_bill_descr>").append("<![CDATA[" + ( siteDescr )+ "]]>").append("</site_bill_descr>");	
							valueXmlString.append("<site_code__cr>").append("<![CDATA[" + ( loginSite )+ "]]>").append("</site_code__cr>");
							valueXmlString.append("<site_cr_descr>").append("<![CDATA[" + ( siteDescr )+ "]]>").append("</site_cr_descr>");								
							valueXmlString.append("<type>").append("R").append("</type>");
							valueXmlString.append("<curr_code>").append("<![CDATA[" + ( currCode )+ "]]>").append("</curr_code>");
							valueXmlString.append("<exch_rate>").append("<![CDATA[" + ( exchRate )+ "]]>").append("</exch_rate>");
							//valueXmlString.append("<cust_code__end protect=\"1\" >").append("").append("</cust_code__end>");
							valueXmlString.append("<chg_user>").append("<![CDATA["+ chguserhdr + "]]>").append("</chg_user>");
							valueXmlString.append("<chg_term>").append("<![CDATA["+ chgtermhdr + "]]>").append("</chg_term>");
						}
					}
					if(currentColumn.trim().equalsIgnoreCase("itm_defaultedit"))
					{
						custCode = genericUtility.getColumnValue( "cust_code__end", dom );
						type = genericUtility.getColumnValue( "type", dom );
						if( type != null && custCode != null )
						{
							if( type.equals("R") )
							{
								sql = "select cust_name from customer "
										+" where cust_code = ? ";
						
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									custName = rs.getString( "cust_name" );
								}	
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
							else
							{
								sql = "Select first_name, middle_name, last_name from strg_customer "
									 +" where sc_code = ? ";
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									firstName = rs.getString( "first_name" );
									middleName = rs.getString( "middle_name" );
									lastName = rs.getString( "last_name" );
								}
								custName = firstName + " " + middleName + " " + lastName;
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
						}
						valueXmlString.append("<cust_end_name>").append("<![CDATA[" + ( custName )+ "]]>").append("</cust_end_name>");	
						/*String siteCodeEdit = getColumnValue("site_code",dom);
						String itemGrpEdit = getColumnValue("item_grp",dom);
						String tranId = genericUtility.getColumnValue( "tran_id", dom );
						sql = "SELECT count(1) from ASSET_SHIFTDET"
						      +" where tran_id = ? ";
						pStmt = conn.prepareStatement( sql );
						pStmt.setString(1,tranId);
						rs = pStmt.executeQuery();
						if( rs.next() )
						{
							cnt = rs.getInt( 1 );
						} 
						rs.close();
						rs = null;
						pStmt.close();
						pStmt = null;
						
						String grpDescr = null;
						sql = "select fn_get_gen_descr('GRP_CODE','W_ITEM', ? ,'D') descr"
							  +" from dual";
						pStmt = conn.prepareStatement( sql );
						pStmt.setString(1, itemGrpEdit != null ? itemGrpEdit.trim() : "" );
						rs = pStmt.executeQuery();
						if( rs.next() )
						{
							grpDescr = rs.getString( 1 );
						}
						if( cnt == 0 )
						{
							valueXmlString.append("<site_code>").append("<![CDATA[" + ( siteCodeEdit != null ? siteCodeEdit.trim() : ""  )+ "]]>").append("</site_code>");
							valueXmlString.append("<item_grp>").append("<![CDATA[" + ( itemGrpEdit != null ? itemGrpEdit.trim() : ""  )+ "]]>").append("</item_grp>");
							valueXmlString.append("<grp_descr>").append("<![CDATA[" + ( grpDescr != null ? grpDescr.trim() : ""  )+ "]]>").append("</grp_descr>");
						}
						else
						{
							valueXmlString.append("<site_code protect=\"1\" >").append("<![CDATA["+siteCodeEdit+"]]>").append("</site_code>");
							valueXmlString.append("<item_grp protect=\"1\" >").append("<![CDATA["+itemGrpEdit+"]]>").append("</item_grp>");
							valueXmlString.append("<grp_descr>").append("<![CDATA[" + ( grpDescr != null ? grpDescr.trim() : ""  )+ "]]>").append("</grp_descr>");
						}*/
					} 	
					
					if (currentColumn.trim().equals( "cust_code" ))
					{
						custCode = genericUtility.getColumnValue( "cust_code", dom );
						if( custCode != null && custCode.trim().length() > 0 )
						{
							sql = "select cust_name from customer "
									+" where cust_code = ? ";
					
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,custCode.trim());
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								custName = rs.getString( "cust_name" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							
							sql = "select price_list from site_customer where site_code = ? and cust_code = ?";
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,loginSite.trim());
							pStmt.setString(2,custCode.trim());
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								priceList = rs.getString( "price_list" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							if( priceList == null || priceList.trim().length() == 0 )
							{
								sql = "Select price_list from customer where cust_code = ? ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, custCode.trim() );
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									priceList = rs.getString("price_list");
								}
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
							
							sql = "SELECT item_ser__inv FROM customer_series where cust_code = ? "
							     +" AND item_ser__inv IS NOT NULL ";
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,custCode.trim());
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								itemSeries = rs.getString( "item_ser__inv" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							
							if( itemSeries == null || itemSeries.trim().length() == 0 )
							{
								sql = "SELECT item_ser FROM customer_series where cust_code = ? ";
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									itemSeries = rs.getString( "item_ser" );
								}	
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
						}
						valueXmlString.append("<cust_name>").append("<![CDATA[" + ( custName )+ "]]>").append("</cust_name>");	
						valueXmlString.append("<cust_code__credit>").append("<![CDATA[" + ( custCode != null ? custCode.trim() : "" )+ "]]>").append("</cust_code__credit>");
						valueXmlString.append("<cust_cr_name>").append("<![CDATA[" + ( custName )+ "]]>").append("</cust_cr_name>");
						valueXmlString.append("<price_list>").append("<![CDATA[" + ( priceList == null ? "" : priceList )+ "]]>").append("</price_list>");
						valueXmlString.append("<item_ser>").append("<![CDATA[" + ( itemSeries )+ "]]>").append("</item_ser>");
					}
					if (currentColumn.trim().equals( "type" ))
					{
						type = genericUtility.getColumnValue( "type", dom );
						valueXmlString.append("<cust_code__end>").append("").append("</cust_code__end>");
						valueXmlString.append("<cust_end_name>").append("").append("</cust_end_name>");
						/* if( type != null )
						{
							valueXmlString.append("<cust_code__end protect=\"0\" >").append("").append("</cust_code__end>");
						}
						else
						{
							valueXmlString.append("<cust_code__end protect=\"1\" >").append("").append("</cust_code__end>");
						} */
					}
					if (currentColumn.trim().equals( "cust_code__credit" ))
					{
						custCode = genericUtility.getColumnValue( "cust_code__credit", dom );
						if( custCode != null && custCode.trim().length() > 0 )
						{
							sql = "select cust_name from customer "
									+" where cust_code = ? ";
					
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,custCode.trim());
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								custName = rs.getString( "cust_name" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
						}
						valueXmlString.append("<cust_cr_name>").append("<![CDATA[" + ( custName )+ "]]>").append("</cust_cr_name>");	
					}
					if (currentColumn.trim().equals( "cust_code__end" ))
					{
						custCode = genericUtility.getColumnValue( "cust_code__end", dom );
						type = genericUtility.getColumnValue( "type", dom );
						if( type != null )
						{
							if( type.equals("R") )
							{
								sql = "select cust_name from customer "
										+" where cust_code = ? ";
						
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									custName = rs.getString( "cust_name" );
								}	
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
							else
							{
								sql = "Select first_name, middle_name, last_name from strg_customer "
								     +" where sc_code = ? ";
								pStmt = conn.prepareStatement( sql );
								pStmt.setString(1,custCode.trim());
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									firstName = rs.getString( "first_name" );
									middleName = rs.getString( "middle_name" );
									lastName = rs.getString( "last_name" );
								}
								custName = firstName + " " + middleName + " " + lastName;
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
							}
						}
						valueXmlString.append("<cust_end_name>").append("<![CDATA[" + ( custName )+ "]]>").append("</cust_end_name>");	
					}
					if (currentColumn.trim().equals( "curr_code" ))
					{
						currCode = genericUtility.getColumnValue( "curr_code", dom );
						if( currCode != null && currCode.trim().length() > 0 )
						{
							sql = "select std_exrt from currency where curr_code = ? ";
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,currCode.trim());
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								exchRate = rs.getDouble( "std_exrt" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
						}
						valueXmlString.append("<exch_rate>").append("<![CDATA[" + ( exchRate )+ "]]>").append("</exch_rate>");	
					}
					
					valueXmlString.append("</Detail1>");
					valueXmlString.append("</Root>");	
					break;
				///////////////
				case 2:
					valueXmlString.append("<Detail2>");	
					//SEARCHING THE DOM FOR THE INCOMING COLUMN VALUE START
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					ctr = 0;
					childNodeListLength = childNodeList.getLength();
					do
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if(childNodeName.equals(currentColumn))
						{
							if (childNode.getFirstChild() != null)
							{
								columnValue=childNode.getFirstChild().getNodeValue().trim();
							}
						}
						ctr++;
					}while(ctr < childNodeListLength  && !childNodeName.equals(currentColumn));
					System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
					if (currentColumn.trim().equals( "itm_default" ))
					{
						itemSeries = genericUtility.getColumnValue( "item_ser", dom1 );
						custCdEnd = genericUtility.getColumnValue( "cust_code__end", dom1 );
						type = genericUtility.getColumnValue( "type", dom1 );
						if( type != null && custCdEnd != null )
						{
							if( type.equals("S") )
							{
								sql = "select disc_perc from disc_apr_strg where item_ser = ? "
								     +" and	sc_code	= ? "
									 +" and ? between eff_from and valid_upto ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, itemSeries.trim() );
								pStmt.setString(2, custCdEnd.trim() );
								pStmt.setTimestamp(3, getCurrdateTsFormat() );
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									discPerc = rs.getDouble("disc_perc");
								}								
							}
							else
							{
								sql = "select disc_perc from customer_series where item_ser = ? "
								     +" and	cust_code	= ? ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, itemSeries.trim() );
								pStmt.setString(2, custCdEnd.trim() );
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									discPerc = rs.getDouble("disc_perc");
								}
							}
							valueXmlString.append("<discount_per>").append("<![CDATA["+ discPerc + "]]>").append("</discount_per>");
						}
						valueXmlString.append("<item_ser>").append("<![CDATA["+ itemSeries != null ? itemSeries : "" + "]]>").append("</item_ser>");
					}
					if (currentColumn.trim().equals( "itm_defaultedit" ))
					{
						itemCode = genericUtility.getColumnValue( "item_code", dom );
						siteCode = genericUtility.getColumnValue( "site_code", dom1 );
						custCode = genericUtility.getColumnValue( "cust_code", dom1 );
						lotNo = genericUtility.getColumnValue( "lot_no", dom );
						rateSell = genericUtility.getColumnValue( "rate__sell", dom );
						discPer = genericUtility.getColumnValue( "discount_per", dom );
						rateContr = genericUtility.getColumnValue( "rate__contr", dom );
						tranId = genericUtility.getColumnValue( "tran_id", dom );
						lineNo = genericUtility.getColumnValue( "line_no", dom );
						String itemDescr = "";
						String itemSer = "";
						
						if( itemCode != null && itemCode.trim().length() > 0 )
						{
							sql = "select DESCR, item_ser from item "
								   +"where item_code = ? ";
							pStmt = conn.prepareStatement( sql );
							pStmt.setString(1,itemCode.trim());
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								itemDescr = rs.getString( "DESCR" );
								itemSer = rs.getString( "item_ser" );
							}	
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
						}
						valueXmlString.append("<item_descr>").append("<![CDATA[" + ( itemDescr )+ "]]>").append("</item_descr>");	
						valueXmlString.append("<item_ser>").append("<![CDATA[" + ( itemSer )+ "]]>").append("</item_ser>");	
						if( lotNo != null && lotNo.trim().length() > 0 )
						{
							sql = "select sum(b.quantity) as saleQty from invoice a, invoice_trace b "
							     +" where a.invoice_id = b.invoice_id "
								 +" and	a.cust_code = ? "
								 +" and	a.site_code = ? "
								 +" and	a.tran_date <= ? "
								 +" and	b.item_code = ? "
								 +" and	b.lot_no = ? "
								 +" and	a.confirmed = 'Y'" ;
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, custCode);
							pStmt.setString(2, siteCode);
							pStmt.setTimestamp(3, getCurrdateTsFormat());
							pStmt.setString(4, itemCode);
							pStmt.setString(5, lotNo);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								saleQty = rs.getDouble("saleQty");
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							
							sql = "select sum(case when B.ret_rep_flag = 'R' then -b.quantity else b.quantity end) as saleRetQty "
							     +" from sreturn a, sreturndet b "
								 +" where a.tran_id = b.tran_id "
								 +" and	a.cust_code = ? "
								 +" and	a.site_code = ? "
								 +" and	a.tran_date <= ? "
								 +" and	b.item_code = ? "
								 +" and   b.lot_no 	= ? "
								 +" and	a.confirmed = 'Y'" ;
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, custCode);
							pStmt.setString(2, siteCode);
							pStmt.setTimestamp(3, getCurrdateTsFormat());
							pStmt.setString(4, itemCode);
							pStmt.setString(5, lotNo);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								saleRetQty = rs.getDouble("saleRetQty");
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
								
							sql = "select sum(case when b.quantity is null then 0 else b.quantity end) as confQty "
							     +" from charge_back a, charge_back_det b "
								 +" where a.tran_id = b.tran_id "
								 +" and	a.cust_code = ? "
								 +" and	a.site_code = ? "
								 +" and	a.confirmed = 'Y' "
								 +" and	b.item_code = ? "
								 +" and   b.lot_no 	= ? ";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, custCode);
							pStmt.setString(2, siteCode);
							pStmt.setString(3, itemCode);
							pStmt.setString(4, lotNo);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								confQty = rs.getDouble("confQty");
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							
							sql = "select sum(case when b.quantity is null then 0 else b.quantity end) as unconfQty "
							     +" from charge_back a, charge_back_det b "
								 +" where a.tran_id = b.tran_id "
								 +" and	a.cust_code = ? "
								 +" and	a.site_code = ? "
								 +" and	(case when a.confirmed is null then 'N' else a.confirmed end)  = 'N' "
								 +" and	b.item_code = ? "
								 +" and   b.lot_no 	= ? "
								 +" and a.tran_id <> ? ";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, custCode);
							pStmt.setString(2, siteCode);
							pStmt.setString(3, itemCode);
							pStmt.setString(4, lotNo);
							pStmt.setString(5, tranId);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								unconfQty = rs.getDouble("unconfQty");
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							
							/* sql = "select ('"+unconfQty+"' - (case when b.quantity is null then 0 else b.quantity end)) as unconfQty " 
							     +" from charge_back a, charge_back_det b "
								 +" where a.tran_id = b.tran_id "
								 +" and	b.tran_id = ? "
								 +"	and	b.line_no = ? "
								 +"	and	a.cust_code = ? "
								 +" and	a.site_code = ? "
								 +"	and	(case when a.confirmed is null then 'N' else a.confirmed end)  = 'N' "
								 +"	and	b.item_code = ? "
								 +"	and   b.lot_no 	= ? ";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, tranId);
							pStmt.setString(2, lineNo);
							pStmt.setString(2, custCode);
							pStmt.setString(3, siteCode);
							pStmt.setString(4, itemCode);
							pStmt.setString(5, lotNo);
							rs = pStmt.executeQuery();
							if( rs.next() )
							{
								unconfQty = rs.getDouble("unconfQty");
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null; */
							
							//saleRetQty = Math.abs(saleRetQty);
							valueXmlString.append("<sale_qty>").append("<![CDATA["+ saleQty + "]]>").append("</sale_qty>");
							valueXmlString.append("<sale_ret_qty>").append("<![CDATA["+ saleRetQty + "]]>").append("</sale_ret_qty>");
							valueXmlString.append("<conf_claimed>").append("<![CDATA["+ confQty + "]]>").append("</conf_claimed>");
							valueXmlString.append("<unconf_claimed>").append("<![CDATA["+ unconfQty + "]]>").append("</unconf_claimed>");
						}
						
						if( rateSell != null && rateSell.trim().length() > 0 )
							rateSellDbl = Double.parseDouble(rateSell);
						
						if( discPer != null && discPer.trim().length() > 0 )
							discPerDbl = Double.parseDouble(discPer);
							
						if( rateContr != null && rateContr.trim().length() > 0 )
							rateContrDbl = Double.parseDouble(rateContr);
							
						if( rateContrDbl <= 0 )
							rate = rateSellDbl;
						else
							rate = Math.min( rateContrDbl, rateSellDbl );
							
						discPerUnit = (rate * discPerDbl) / 100;
						
						valueXmlString.append("<discount_per_unit>").append("<![CDATA["+ discPerUnit + "]]>").append("</discount_per_unit>");
					}
					if (currentColumn.trim().equals("item_code"))
					{
						itmchgData = getItemChgdata( dom, dom1, conn );
						valueXmlString.append( itmchgData );
					}
					if (currentColumn.trim().equals("lot_no"))
					{
						itmchgData = getItemChgdata( dom, dom1, conn );
						valueXmlString.append( itmchgData );
					}
					if (currentColumn.trim().equals("quantity"))
					{
						itmchgData = getCalculations( dom );
						valueXmlString.append( itmchgData );
					}
					if (currentColumn.trim().equals("discount_per"))
					{
						itmchgData = getCalculations( dom );
						valueXmlString.append( itmchgData );
					}
					if (currentColumn.trim().equals("rate__sell"))
					{
						itmchgData = getCalculations( dom );
						valueXmlString.append( itmchgData );
					}
					if (currentColumn.trim().equals("rate__contr"))
					{
						itmchgData = getCalculations( dom );
						valueXmlString.append( itmchgData );
					}
					if (currentColumn.trim().equals("item_ser"))
					{
						itemSeries = genericUtility.getColumnValue( "item_ser", dom1 );
						custCdEnd = genericUtility.getColumnValue( "cust_code__end", dom1 );
						type = genericUtility.getColumnValue( "type", dom1 );
						if( type != null && custCdEnd != null )
						{
							if( type.equals("S") )
							{
								sql = "select disc_perc from disc_apr_strg where item_ser = ? "
									 +" and	sc_code	= ? "
									 +" and ? between eff_from and valid_upto ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, itemSeries.trim() );
								pStmt.setString(2, custCdEnd.trim() );
								pStmt.setTimestamp(3, getCurrdateTsFormat() );
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									discPerc = rs.getDouble("disc_perc");
								}
								
							}
							else
							{
								sql = "select disc_perc from customer_series where item_ser = ? "
									 +" and	cust_code	= ? ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, itemSeries.trim() );
								pStmt.setString(2, custCdEnd.trim() );
								rs = pStmt.executeQuery();
								if( rs.next() )
								{
									discPerc = rs.getDouble("disc_perc");
								}
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							valueXmlString.append("<discount_per>").append("<![CDATA["+ discPerc + "]]>").append("</discount_per>");
						}
					}
					
					valueXmlString.append("</Detail2>");					
					valueXmlString.append("</Root>");					
				////////////////
			}//END OF TRY
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+ e.getMessage());
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pStmt != null )
				{
					pStmt.close();
					pStmt = null;					
				}
				if ( conn != null )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("Exception ::" + e);
				e.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}//END OF ITEMCHANGE	
	private String getCurrdateAppFormat()
	{
		String s = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			
			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
		}
		catch(Exception exception)
		{
			System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
		}
		return s;
	}
	private Timestamp getCurrdateTsFormat()
    {
        String s = "";	
		Timestamp timestamp = null;		
        GenericUtility genericUtility = GenericUtility.getInstance();
        try
        {
            java.util.Date date = null;
            timestamp = new Timestamp(System.currentTimeMillis());
            
			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
            date = simpledateformat.parse(timestamp.toString());
            timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
		}
        catch(Exception exception)
        {
            System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
        }
        return timestamp;
    }
	private String getNodeValue( Node currDet, String fldName, boolean isAttribute )
	{
		String fldValue = null;
		boolean isFound = false;
		NodeList currNodes = currDet.getChildNodes();
		int currDetLen = currNodes.getLength();
		for(int detIdx = 0; detIdx < currDetLen && !isFound ; detIdx++ )
		{
			Node currNode = currNodes.item( detIdx );
			String nodeName = currNode.getNodeName();

			if( isAttribute == true )
			{
				if ( nodeName.equalsIgnoreCase( "attribute" ) )
				{
					fldValue = currNode.getAttributes().getNamedItem( fldName ).getNodeValue();
					isFound = true;
				}				
			}
			else if ( currNode.getNodeType() == Node.ELEMENT_NODE && nodeName.equalsIgnoreCase( fldName ) )
			{
				fldValue = currNode.getFirstChild() != null ? currNode.getFirstChild().getNodeValue().trim() : null;
				isFound = true;
			}
		}
		return fldValue;
	}
	private String getItemChgdata( Document dom, Document dom1, Connection conn ) throws RemoteException,ITMException
	{
		StringBuffer itmChangeString = new StringBuffer("");
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pStmt = null;
		try
		{
			String itemDescr = "";
			String itemSer = "";
			double discPerc = 0.0;
			double rate = 0.0;
			double saleQty = 0.0;
			double saleRetQty = 0.0;
			double confQty = 0.0;
			double unconfQty = 0.0;
			java.sql.Timestamp tranDateTs = null;
			String itemCode = genericUtility.getColumnValue( "item_code", dom );
			String lotNo = genericUtility.getColumnValue( "lot_no", dom );
			String rateSell = genericUtility.getColumnValue( "rate__sell", dom );
			String discPerStr = genericUtility.getColumnValue( "discount_per", dom );
			String tranId = genericUtility.getColumnValue( "tran_id", dom );
			String tranDate = genericUtility.getColumnValue( "tran_date", dom1 );
			String lineNo = genericUtility.getColumnValue( "line_no", dom );
			String siteCode = genericUtility.getColumnValue( "site_code", dom1 );
			String siteCodeCr = genericUtility.getColumnValue( "site_code__cr", dom1 );
			String custCode = genericUtility.getColumnValue( "cust_code", dom1 );
			String custCdEnd = genericUtility.getColumnValue( "cust_code__end", dom1 );
			String type = genericUtility.getColumnValue( "type", dom1 );
			String priceList = genericUtility.getColumnValue( "price_list", dom1 );
			System.out.println("tranDate++>"+tranDate);
			tranDateTs = Timestamp.valueOf(genericUtility.getValidDateString(tranDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
			System.out.println("tranDateTs++>"+tranDateTs);
			if( itemCode != null && itemCode.trim().length() > 0 )
			{
				sql = "select DESCR, item_ser from item "
					   +"where item_code = ? ";
				pStmt = conn.prepareStatement( sql );
				pStmt.setString(1,itemCode.trim());
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					itemDescr = rs.getString( "DESCR" );
					itemSer = rs.getString( "item_ser" );
				}	
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				itmChangeString.append("<item_descr>").append("<![CDATA[" + ( itemDescr )+ "]]>").append("</item_descr>");	
				itmChangeString.append("<item_ser>").append("<![CDATA[" + ( itemSer )+ "]]>").append("</item_ser>");	
			}
			//itmChangeString.append("<item_descr>").append("<![CDATA[" + ( itemDescr )+ "]]>").append("</item_descr>");	
		//	itmChangeString.append("<item_ser>").append("<![CDATA[" + ( itemSer )+ "]]>").append("</item_ser>");	
			
			if( type != null && custCdEnd != null && itemSer != null && (discPerStr == null || discPerStr.trim().equals("0.0")) )
			{
				if( type.equals("S") )
				{
					sql = "select disc_perc from disc_apr_strg where item_ser = ? "
						 +" and	sc_code	= ? "
						 +" and ? between eff_from and valid_upto ";
					pStmt = conn.prepareStatement(sql);
					pStmt.setString(1, itemSer.trim() );
					pStmt.setString(2, custCdEnd.trim() );
					pStmt.setTimestamp(3, getCurrdateTsFormat() );
					rs = pStmt.executeQuery();
					if( rs.next() )
					{
						discPerc = rs.getDouble("disc_perc");
					}								
				}
				else
				{
					sql = "select disc_perc from customer_series where item_ser = ? "
						 +" and	cust_code	= ? ";
					pStmt = conn.prepareStatement(sql);
					pStmt.setString(1, itemSer.trim() );
					pStmt.setString(2, custCdEnd.trim() );
					rs = pStmt.executeQuery();
					if( rs.next() )
					{
						discPerc = rs.getDouble("disc_perc");
					}
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				itmChangeString.append("<discount_per>").append("<![CDATA["+ discPerc + "]]>").append("</discount_per>");
			}
			if( (priceList == null || priceList.trim().length() == 0) && ( siteCodeCr != null ) )
			{
				sql = "select price_list from site_customer "
				     +" where site_code = ? "
					 +" and	cust_code = ? ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, siteCodeCr.trim() );
				pStmt.setString(2, custCode.trim() );
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					priceList = rs.getString("price_list");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				if( priceList == null )
				{
					sql = "Select price_list from customer where cust_code = ? ";
					pStmt = conn.prepareStatement(sql);
					pStmt.setString(1, custCode.trim() );
					rs = pStmt.executeQuery();
					if( rs.next() )
					{
						priceList = rs.getString("price_list");
					}
					rs.close();
					rs = null;
					pStmt.close();
					pStmt = null;
				}
			}
			/* if( itemCode != null && itemCode.trim().length() > 0 && lotNo != null && lotNo.trim().length() > 0 )
			{
				sql = "Select rate from pricelist where "
				     +" price_list = ? "
					 +" and item_code  = ? "
					 +" and list_type = 'B' "
					 +" and lot_no__from <= ? "
					 +" and lot_no__to   >= ? ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, priceList);
				pStmt.setString(2, itemCode);
				pStmt.setString(3, lotNo);
				pStmt.setString(4, lotNo);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					rate = rs.getDouble("rate");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
			} */
			if( lotNo != null && lotNo.trim().length() > 0 )
			{
				sql = "Select min(eff_cost) as rate from min_rate_history "
					 +" where item_code = ? "
					 +" and lot_no = ? "
					 +" and cust_code = ? "
					 +" and site_code = ?";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, itemCode);
				pStmt.setString(2, lotNo);
				pStmt.setString(3, custCode);
				pStmt.setString(4, siteCode);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					rate = rs.getDouble("rate");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				if( rate == 0.0 )
				{
					sql = "Select rate from pricelist "
						 +" where price_list = ?"
						 +" and item_code = ?"
						 +" and lot_no__from <= ?"
						 +" and lot_no__to >= ?"
						 + "and eff_from <= ? "
						 + "and valid_upto >= ?";
					pStmt = conn.prepareStatement(sql);
					pStmt.setString(1, priceList);
					pStmt.setString(2, itemCode);
					pStmt.setString(3, lotNo);
					pStmt.setString(4, lotNo);
					pStmt.setTimestamp(5, tranDateTs);
					pStmt.setTimestamp(6, tranDateTs);
					rs = pStmt.executeQuery();
					if( rs.next() )
					{
						rate = rs.getDouble("rate");
					}
					rs.close();
					rs = null;
					pStmt.close();
					pStmt = null;
				}
				
				if( rate == 0.0 )
				{
					sql = "Select min(b.rate) as rate from invoice a, invoice_trace b "
						 +" where a.invoice_id = b.invoice_id "
						 +" and	a.cust_code = ? "
						 +" and	a.site_code = ? "
						 +" and	a.tran_date <= ? "
						 +" and	b.item_code = ? "
						 +" and	b.lot_no = ? "
						 +" and	a.confirmed = 'Y'" ;
					pStmt = conn.prepareStatement(sql);
					pStmt.setString(1, custCode);
					pStmt.setString(2, siteCode);
					pStmt.setTimestamp(3, getCurrdateTsFormat());
					pStmt.setString(4, itemCode);
					pStmt.setString(5, lotNo);
					rs = pStmt.executeQuery();
					if( rs.next() )
					{
						rate = rs.getDouble("rate");
					}
					rs.close();
					rs = null;
					pStmt.close();
					pStmt = null;
				}
				
				sql = "select sum(b.quantity) as saleQty from invoice a, invoice_trace b "
				     +" where a.invoice_id = b.invoice_id "
					 +" and	a.cust_code = ? "
					 +" and	a.site_code = ? "
					 +" and	a.tran_date <= ? "
					 +" and	b.item_code = ? "
					 +" and	b.lot_no = ? "
					 +" and	a.confirmed = 'Y'" ;
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, custCode);
				pStmt.setString(2, siteCode);
				pStmt.setTimestamp(3, getCurrdateTsFormat());
				pStmt.setString(4, itemCode);
				pStmt.setString(5, lotNo);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					saleQty = rs.getDouble("saleQty");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				sql = "select sum(case when B.ret_rep_flag = 'R' then -b.quantity else b.quantity end) as saleRetQty "
				     +" from sreturn a, sreturndet b "
					 +" where a.tran_id = b.tran_id "
					 +" and	a.cust_code = ? "
					 +" and	a.site_code = ? "
					 +" and	a.tran_date <= ? "
					 +" and	b.item_code = ? "
					 +" and   b.lot_no 	= ? "
					 +" and	a.confirmed = 'Y'" ;
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, custCode);
				pStmt.setString(2, siteCode);
				pStmt.setTimestamp(3, getCurrdateTsFormat());
				pStmt.setString(4, itemCode);
				pStmt.setString(5, lotNo);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					saleRetQty = rs.getDouble("saleRetQty");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				sql = "select sum(case when b.quantity is null then 0 else b.quantity end) as confQty "
				     +" from charge_back a, charge_back_det b "
					 +" where a.tran_id = b.tran_id "
					 +" and	a.cust_code = ? "
					 +" and	a.site_code = ? "
					 +" and	a.confirmed = 'Y' "
					 +" and	b.item_code = ? "
					 +" and   b.lot_no 	= ? ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, custCode);
				pStmt.setString(2, siteCode);
				pStmt.setString(3, itemCode);
				pStmt.setString(4, lotNo);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					confQty = rs.getDouble("confQty");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				sql = "select sum(case when b.quantity is null then 0 else b.quantity end) as unconfQty "
				     +" from charge_back a, charge_back_det b "
					 +" where a.tran_id = b.tran_id "
					 +" and	a.cust_code = ? "
					 +" and	a.site_code = ? "
					 +" and	(case when a.confirmed is null then 'N' else a.confirmed end)  = 'N' "
					 +" and	b.item_code = ? "
					 +" and   b.lot_no 	= ? ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, custCode);
				pStmt.setString(2, siteCode);
				pStmt.setString(3, itemCode);
				pStmt.setString(4, lotNo);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					unconfQty = rs.getDouble("unconfQty");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				/* sql = "select ('"+unconfQty+"' - (case when b.quantity is null then 0 else b.quantity end)) as unconfQty " 
				     +" from charge_back a, charge_back_det b "
					 +" where a.tran_id = b.tran_id "
					 +" and	b.tran_id = ? "
					 +"	and	b.line_no = ? "
					 +"	and	a.cust_code = ? "
					 +" and	a.site_code = ? "
					 +"	and	(case when a.confirmed is null then 'N' else a.confirmed end)  = 'N' "
					 +"	and	b.item_code = ? "
					 +"	and   b.lot_no 	= ? ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString(1, tranId);
				pStmt.setString(2, lineNo);
				pStmt.setString(2, custCode);
				pStmt.setString(3, siteCode);
				pStmt.setString(4, itemCode);
				pStmt.setString(5, lotNo);
				rs = pStmt.executeQuery();
				if( rs.next() )
				{
					unconfQty = rs.getDouble("unconfQty");
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null; */
				
				//saleRetQty = Math.abs(saleRetQty);
				itmChangeString.append("<rate__sell>").append("<![CDATA["+ rate + "]]>").append("</rate__sell>");
				itmChangeString.append("<sale_qty>").append("<![CDATA["+ saleQty + "]]>").append("</sale_qty>");
				itmChangeString.append("<sale_ret_qty>").append("<![CDATA["+ saleRetQty + "]]>").append("</sale_ret_qty>");
				itmChangeString.append("<conf_claimed>").append("<![CDATA["+ confQty + "]]>").append("</conf_claimed>");
				itmChangeString.append("<unconf_claimed>").append("<![CDATA["+ unconfQty + "]]>").append("</unconf_claimed>");
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :ChargeBackLoc :itemChange(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		return itmChangeString.toString();
	}
	
	private String getCalculations( Document dom ) throws RemoteException,ITMException
	{
		StringBuffer itmChangeString = new StringBuffer("");
		double rate = 0.0;
		double discAmt = 0.0;
		double rateDiff = 0.0;
		double amount = 0.0, endCustRateDbl = 0.0, taxAmtDbl = 0.0;
		double netAmt = 0.0, quantityDbl = 0.0, rateSellDbl = 0.0, discPerDbl = 0.0;
		double discPerUnit = 0.0;
		try
		{
			String quantity = genericUtility.getColumnValue( "quantity", dom );
			String rateSell = genericUtility.getColumnValue( "rate__sell", dom );
			String discPer = genericUtility.getColumnValue( "discount_per", dom );
			String endCustRate = genericUtility.getColumnValue( "rate__contr", dom );
			String taxAmt = genericUtility.getColumnValue( "tax_amt", dom );
			
			if( quantity != null && quantity.trim().length() > 0 )
				quantityDbl = Double.parseDouble(quantity);
				
			if( rateSell != null && rateSell.trim().length() > 0 )
				rateSellDbl = Double.parseDouble(rateSell);
				
			if( discPer != null && discPer.trim().length() > 0 )
				discPerDbl = Double.parseDouble(discPer);
				
			if( endCustRate != null && endCustRate.trim().length() > 0 )
				endCustRateDbl = Double.parseDouble(endCustRate);
				
			if( taxAmt != null && taxAmt.trim().length() > 0 )
				taxAmtDbl = Double.parseDouble(taxAmt);
				
			if( endCustRateDbl <= 0 )
			{
				rate = rateSellDbl;
			}
			else
			{
				rate = Math.min( endCustRateDbl, rateSellDbl );
				rateDiff = rateSellDbl - endCustRateDbl;
			}
				
			discAmt = (quantityDbl * rate * discPerDbl)/ 100;
			amount = rateDiff * quantityDbl;
			netAmt = amount + discAmt + taxAmtDbl;
			discPerUnit = (rate * discPerDbl) / 100;
			
			itmChangeString.append("<discount_amt>").append("<![CDATA["+ discAmt + "]]>").append("</discount_amt>");
			itmChangeString.append("<amount>").append("<![CDATA["+ amount + "]]>").append("</amount>");
			itmChangeString.append("<net_amt>").append("<![CDATA["+ netAmt + "]]>").append("</net_amt>");
			itmChangeString.append("<discount_per_unit>").append("<![CDATA["+ discPerUnit + "]]>").append("</discount_per_unit>");
			itmChangeString.append("<rate__diff>").append("<![CDATA["+ rateDiff + "]]>").append("</rate__diff>");
		}
		catch(Exception e)
		{
			System.out.println("Exception :ChargeBackLoc :itemChange(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		return itmChangeString.toString();	
	}
}
