 /*
	Developed by : Niraja
	Company : Base Information Management Pvt. Ltd
	Version : 1.0
	Date : 03/11/2005
*/

package ibase.webitm.ejb.dis.adv;

import java.rmi.RemoteException;
import java.util.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.ejb.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class SalesReturnAct extends ActionHandlerEJB implements SalesReturnActLocal, SalesReturnActRemote
{
	/*public void ejbCreate() throws RemoteException, CreateException
	{
	}

   	public void ejbRemove()
	{
	}

   	public void ejbActivate()
	{
	}

   	public void ejbPassivate()
	{
	}*/

    public String actionHandler() throws RemoteException,ITMException
	{
		return "";
	}

	public String actionHandler(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams) throws RemoteException,ITMException
	{
	    System.out.println("SalesReturn called");
		Document dom = null;
		Document dom1 = null;
		String  resString = null;
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				dom = genericUtility.parseString(xmlString);
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				System.out.println("XML String1 :"+xmlString1);
				dom1 = genericUtility.parseString(xmlString1);
			}

			System.out.println("actionType:"+actionType+":");
			if (actionType.equalsIgnoreCase("Allocate"))
			{
			  resString = actionAllocate(dom,dom1,objContext,xtraParams);
			}
			if (actionType.equalsIgnoreCase("FullSR"))
			{
			  resString = actionFullSr(dom,dom1,objContext,xtraParams);
			}
		}catch(Exception e)
		{
			System.out.println("Exception :SalesReturn :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from actionMETHOD :actionHandler"+resString);
	    return resString;
	}

	private String actionAllocate(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String errCode = "";
		String errString = "";
		String flag = "";
		String  invoiceId = "";
		String	siteCode = "";
		String	itemCode = "";
		String	locCode = "";
		String	lotNoInv = "";
		String	lotSlInv = "";
		String	taxClass = "";
		String	taxChap = "";
		String	taxEnv = "";
		String	reason = "";
		String	itemSer = "";
		String	siteCodeMfgFn = "";
		String	packCodeFn = "";
		String	chkDate = "";
		String	quantity = "";
		String stkMfgDate = "";
		String stkExpDate = "";
		String lotNo ="";
		String lotSl ="";
		String siteCodeMfg ="";
		String packCode ="";
		String	unit = "";
		java.sql.Date expDate = null;
		java.sql.Date mfgDate = null;
		int	minShelfLife = 0;
	  	int detCnt =0;
		double totStk =0.0;
		double inputQty = 0.0;
		double qty =0;
		double qtyStk = 0;

		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		/*ADDED BY HATIM ON 16/01/2006*/
		if (dom == null)
		{
			valueXmlString.append("</Root>\r\n");
			return valueXmlString.toString();
		}
		/*END*/
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		try
		{
		   	conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			//detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt");
			//Getting values from dom (current form), dom1(form no 1)
			if(detCnt == 0)
			{
				flag = genericUtility.getColumnValue("ret_rep_flag",dom);
				System.out.println("\n******************** S T A R T  ALLOCATE ****************************");
				System.out.println("SalesReturn:actionAllocate:flag:"+flag+":");
			   	if (flag.equalsIgnoreCase("P"))
				{
					siteCode = genericUtility.getColumnValue("site_code",dom1);
					invoiceId = genericUtility.getColumnValue("invoice_id",dom);
					itemCode = genericUtility.getColumnValue("item_code",dom);
					quantity = genericUtility.getColumnValue("quantity",dom);
					locCode	= genericUtility.getColumnValue("loc_code",dom);
					lotNoInv = genericUtility.getColumnValue("lot_no",dom);
					lotSlInv = genericUtility.getColumnValue("lot_sl",dom);
					taxClass = genericUtility.getColumnValue("tax_class",dom);
					taxChap = genericUtility.getColumnValue("tax_chap",dom);
					taxEnv = genericUtility.getColumnValue("tax_env",dom);
					reason = genericUtility.getColumnValue("reas_code",dom);

					System.out.println("Values From DOM :siteCode:"+siteCode+":invoiceId:"+invoiceId+":quantity:"+quantity+":");
					System.out.println("Values From DOM :lotNoInv:"+lotNoInv+":lotSlInv:"+lotSlInv+":itemCode:"+itemCode+":");
					System.out.println("Values From DOM :taxClass:"+taxClass+":taxChap:"+taxChap+":taxEnv:"+taxEnv+":reason:"+reason+":");

					//Added By Gulzar 06/01/07
					System.out.println("Checking For NULL values of taxClass, taxChap, taxEnv");
					if (taxClass == null)
					{
						taxClass = "";
					}
					if (taxChap == null)
					{
						taxChap = "";
					}
					if (taxEnv == null)
					{
						taxEnv = "";
					}
					System.out.println("Values Of :taxClass:"+taxClass+":taxChap:"+taxChap+":taxEnv:"+taxEnv+":");
					//End Add Gulzar 06/01/07
					if(quantity != null && quantity.trim().length() > 0)
					{
						qty = Double.parseDouble(quantity);
					}
				   	sql="SELECT MIN_SHELF_LIFE FROM ITEM WHERE ITEM_CODE ='"+itemCode+"'";
					System.out.println("ITEM:sql:"+sql);
					rs = stmt.executeQuery(sql);
					if(rs.next())
					{
						minShelfLife = rs.getInt(1);
					}
					System.out.println("\n minShelfLife:"+minShelfLife+":");

					chkDate = getRelativeDate(minShelfLife);
					System.out.println("SalesReturn:actionAllocate:chkDate:"+chkDate+":");
					System.out.println("SalesReturn:actionAllocate:lotNoInv:"+lotNoInv+":");
					if(lotNoInv == null || lotNoInv.trim().length() == 0)
					{
						sql="SELECT SUM(A.QUANTITY - A.ALLOC_QTY) "+
						  "	FROM STOCK A, INVSTAT B, LOCATION C	 "+
						  "	WHERE A.LOC_CODE = C.LOC_CODE "+
						  "	AND B.INV_STAT  = C.INV_STAT  "+
						  "	AND A.ITEM_CODE ='"+itemCode+"'" +
						  "	AND A.SITE_CODE = '"+siteCode+"'" +
						  "	AND A.LOC_CODE  ='"+locCode+"'"+
						  "	AND A.QUANTITY  > 0	"+
						  "	AND B.AVAILABLE = 'Y' "+
						  "	AND A.EXP_DATE >= ? ";
						pstmt = conn.prepareStatement(sql);
						System.out.println("IF::STOCK a, INVSTAT b, LOCATION c:sql:"+sql);
						pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(genericUtility.getValidDateTimeString(chkDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat())));
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							 totStk = rs.getDouble(1);
						}
					}
					else
					{
						sql="SELECT SUM(A.QUANTITY - A.ALLOC_QTY) "+
							" FROM STOCK A, INVSTAT B, LOCATION C "+
							" WHERE A.LOC_CODE = C.LOC_CODE	"+
							" AND B.INV_STAT  = C.INV_STAT "+
							" AND A.ITEM_CODE ='"+itemCode+"'"+
							" AND A.SITE_CODE ='"+siteCode+"'"+
							" AND A.LOC_CODE  ='"+locCode+"'"+
							" AND A.LOT_NO ='"+lotNoInv+"'"+
							" AND A.LOT_SL ='"+lotSlInv+"'" +
							" AND (A.QUANTITY - A.ALLOC_QTY) >= '"+qty+"'"+
							" AND B.AVAILABLE = 'Y'	"+
							" AND A.EXP_DATE >= ? ";
						System.out.println("ELSE ::STOCK a, INVSTAT b, LOCATION c:sql:"+sql);
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(genericUtility.getValidDateTimeString(chkDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat())));
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							totStk = rs.getDouble(1);
						}
					}//End else
					System.out.println("SalesReturn:totStk:"+totStk+":quantity:"+qty+":");

					if(totStk < qty )
					{
						errCode = "VTDIST16";
					}
					System.out.println("SalesReturn:ERRCODE :"+errCode+":");
					if(errCode == null || errCode.trim().length() == 0)
					{
						System.out.println("I N S I D E ERR = N U L  L ");
						sql="SELECT A.LOC_CODE, A.LOT_NO, A.LOT_SL, A.QUANTITY - A.ALLOC_QTY, A.EXP_DATE,"+
							" A.UNIT, A.ITEM_SER,A.SITE_CODE__MFG, A.MFG_DATE, A.PACK_CODE " +
							" FROM STOCK A, INVSTAT B, LOCATION C " +
							" WHERE A.LOC_CODE = C.LOC_CODE " +
							" AND B.INV_STAT  = C.INV_STAT " +
							" AND A.ITEM_CODE = '" + itemCode + "'" +
							" AND A.SITE_CODE = '" + siteCode +"'" +
							" AND A.LOC_CODE  = '" + locCode + "' " +
							" AND (A.QUANTITY - A.ALLOC_QTY)  > 0 " +
							" AND B.AVAILABLE = 'Y' " +
							" AND A.EXP_DATE >= ? ";
						System.out.println("CURSOR SQL:"+sql);
						if(lotNoInv != null && lotNoInv.trim().length() > 0)
						{
							System.out.println("I N S I D lotNoInv.trim().length() > 0 ");
							sql =  sql + "AND A.LOT_NO ='"+lotNoInv+ "' " +
									" AND A.LOT_SL='" +lotSlInv+"' " +
									" AND (A.QUANTITY - A.ALLOC_QTY) >= "+qty;//string(lc_quantity)
						}
						sql =  sql + " ORDER BY A.EXP_DATE, A.LOT_NO, A.LOT_SL ";
						//chkDate ="26/02/03";
						System.out.println("LAST CURSOR SQL:"+sql);
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(genericUtility.getValidDateTimeString(chkDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat())));
						System.out.println("chkDate ::"+chkDate);
						rs = pstmt.executeQuery();
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						while(rs.next())
						{
							locCode =  rs.getString(1);
							lotNo =	 rs.getString(2);
							lotSl = rs.getString(3);
							qtyStk = rs.getDouble(4);
							expDate = rs.getDate(5);
							unit = rs.getString(6);
							itemSer = rs.getString(7);
							siteCodeMfg = rs.getString(8);
							mfgDate = rs.getDate(9);
							packCode =rs.getString(10);

							inputQty = 0;
							System.out.println(" **** qtyStk:"+qtyStk+":quantity:"+qty+":");
							if(qtyStk <= qty)
							{
								inputQty = qtyStk;
								qty = qty - inputQty;
								System.out.println(" *** qty:"+qty+":");
							}
							else
							{
								inputQty = qty;
								qty = 0;
							}
							System.out.println(" *** mfgDate:"+mfgDate+":");
							if(mfgDate != null)
							{
								stkMfgDate = sdf.format(mfgDate);
							}
							System.out.println("stkMfgDate:"+stkMfgDate+":");
							System.out.println(" *** expDate:"+expDate+":");
							if(expDate != null)
							{
								stkExpDate = sdf.format(expDate);
							}
							System.out.println("SalesReturn:inputQty:"+inputQty+":");
							//inputQty = 1.0;
							if(inputQty > 0)
							{
								valueXmlString.append("<Detail>\r\n");
									valueXmlString.append("<ret_rep_flag>").append("<![CDATA[").append(flag).append("]]>").append("</ret_rep_flag>\r\n");
									valueXmlString.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]>").append("</item_code>\r\n");
									valueXmlString.append("<quantity>").append("<![CDATA[").append(inputQty).append("]]>").append("</quantity>\r\n");
									valueXmlString.append("<unit>").append("<![CDATA[").append(unit.trim()).append("]]>").append("</unit>\r\n");
									valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode.trim()).append("]]>").append("</loc_code>\r\n");
									valueXmlString.append("<lot_no>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
									valueXmlString.append("<lot_sl>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
									valueXmlString.append("<mfg_date>").append("<![CDATA[").append(stkMfgDate).append("]]>").append("</mfg_date>\r\n");
									valueXmlString.append("<exp_date>").append("<![CDATA[").append(stkExpDate).append("]]>").append("</exp_date>\r\n");
									valueXmlString.append("<item_ser>").append("<![CDATA[").append(itemSer.trim()).append("]]>").append("</item_ser>\r\n");
							//calling function(method) gf_get_mfg_site()
									siteCodeMfgFn = itmDBAccess.getMfgSiteCode(itemCode,siteCode,locCode.trim(),lotNo.trim(),lotSl.trim(),"M",conn);
									if(siteCodeMfgFn.indexOf("DS000") != -1)
									{
										valueXmlString.append("<site_code__mfg>").append("<![CDATA[").append(siteCodeMfg.trim()).append("]]>").append("</site_code__mfg>\r\n");
									}
									else
									{
										valueXmlString.append("<site_code__mfg>").append("<![CDATA[").append(siteCodeMfgFn).append("]]>").append("</site_code__mfg>\r\n");
									}
									packCodeFn = itmDBAccess.getMfgSiteCode(itemCode,siteCode,locCode.trim(),lotNo.trim(),lotSl.trim(),"P",conn);
									if(packCodeFn.indexOf("DS000") != -1)
									{
										valueXmlString.append("<pack_code>").append("<![CDATA[").append(packCode.trim()).append("]]>").append("</pack_code>\r\n");
									}
									else
									{
										valueXmlString.append("<pack_code>").append("<![CDATA[").append(packCodeFn).append("]]>").append("</pack_code>\r\n");
									}
									valueXmlString.append("<tax_amt>").append("<![CDATA[").append(0).append("]]>").append("</tax_amt>\r\n");
									valueXmlString.append("<tax_class>").append("<![CDATA[").append(taxClass).append("]]>").append("</tax_class>\r\n");
									valueXmlString.append("<tax_chap>").append("<![CDATA[").append(taxChap).append("]]>").append("</tax_chap>\r\n");
									valueXmlString.append("<tax_env>").append("<![CDATA[").append(taxEnv).append("]]>").append("</tax_env>\r\n");
									valueXmlString.append("<reas_code>").append("<![CDATA[").append(reason).append("]]>").append("</reas_code>\r\n");
								valueXmlString.append("</Detail>\r\n");
							}
							if(qty <= 0)
							{
								break;
							}
						}//loop end
					}//errCode null end if
				}//flag P end if
			}
			valueXmlString.append("</Root>\r\n");
			if (errCode != null  && errCode.trim().length() > 0)
			{
				System.out.println("SalesReturn:errCode:"+errCode);
				errString = itmDBAccess.getErrorString("",errCode,"","",conn);
				conn.close();
				conn = null;
				return errString;
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :SalesReturn :" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}catch(Exception e){}
		}
		System.out.println("SalesReturn :actionAllocate:Final Value :valueXmlString.toString():"+valueXmlString.toString());
		return valueXmlString.toString();
	}

	private String actionFullSr(Document dom,Document dom1, String objContext, String xtraParams) throws RemoteException , ITMException
	{

		System.out.println("\n***********************");
		Connection conn = null;
		Statement stmt = null , stmtMain = null;
		ResultSet rs = null;
		ResultSet rsMain = null;
		String sql = "";
		String errCode = "";
		String errString = "";
		String tranId = "";
		String conf = "";
		String fullSr = "";
		String reasonCode = "";
		String invoiceId = "";
		String lotNo = "";
		String lotSl = "";
		String itemCode = "";
		String setFlag = "";
		//Added on 08/06/06 as code changes in PB
		String despID = "",despLineNo = "";
		int invTraceLineNo = 0;
		//
		int detCnt = 0;
		long invLine =0;
		double qty  = 0;
		double invQty = 0;
		double prevSrqty = 0;
		double sreturnQty = 0;
		double rate = 0;
		double rateStduom = 0;
		double discount = 0;

		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		/*ADDED BY HATIM ON 16/01/2006*/
		if (dom == null)
		{
			valueXmlString.append("</Root>\r\n");
			return valueXmlString.toString();
		}
		/*END*/
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");

		  //IF SRETURN IS ALREADY CONFIRMED RETURN
			tranId= genericUtility.getColumnValue("tran_id",dom1);
			System.out.println("tranId:"+tranId+":");
			if (tranId == null)
			{
				tranId = "";
			}
			stmt = conn.createStatement();
			sql = "SELECT CONFIRMED FROM SRETURN WHERE TRAN_ID ='"+tranId+"'";
			System.out.println("SalesReturn :actionFullSr:SRETURN:sql:"+sql);
			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				conf =rs.getString(1);
			}
			rs.close();//Gulzar - 21/11/06
			stmt.close();
		    if(conf != null && !conf.equalsIgnoreCase("Y"))
			{
				fullSr = genericUtility.getColumnValue("full_ret",dom1);
				System.out.println("fullSr:"+fullSr+":");
				if(fullSr != null &&(!fullSr.equalsIgnoreCase("N")))
				{
					//detCnt = Integer.parseInt(genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt")==null?"0":genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"));
					if(detCnt == 0)
					{
						reasonCode = genericUtility.getColumnValue("reas_code",dom);
						System.out.println("reasonCode:"+reasonCode+":");
						if (reasonCode == null || reasonCode.trim().length() == 0)
						{
							sql = "SELECT VAR_VALUE FROM DISPARM WHERE PRD_CODE = '999999' AND VAR_NAME = 'REAS_CODE_SRET'";
							System.out.println("SQL :: "+sql);
							stmt = conn.createStatement();
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								reasonCode = rs.getString(1);
							}
							rs.close();//Gulzar - 21/11/06
							stmt.close();
						}
						if(reasonCode != null && !reasonCode.equalsIgnoreCase("N"))
						{
							invoiceId = genericUtility.getColumnValue("invoice_id",dom1);
							if (invoiceId == null)
							{
								invoiceId = "";
							}
							System.out.println("invoiceId:"+invoiceId+":");
							sql ="SELECT INV_LINE_NO, LOT_NO, LOT_SL, QUANTITY, ITEM_CODE, RATE, "+
							  " RATE__STDUOM, DISCOUNT, "+
							  " DESP_ID, DESP_LINE_NO, LINE_NO "+ //Added on 08/06/06 as code changes in PB
							  " FROM INVOICE_TRACE  WHERE INVOICE_ID='" +invoiceId+"'";
							//invoice_id using lt_obj;
							System.out.println("SalesReturn :actionFullSr:INVOICE_TRACE:1:sql:"+sql);
							stmtMain = conn.createStatement();
							rsMain = stmtMain.executeQuery(sql);
							while (rsMain.next())
							{
								invLine	=  rsMain.getLong(1);
								lotNo =rsMain.getString(2);
								lotSl =rsMain.getString(3);
								qty =rsMain.getDouble(4);
								itemCode = rsMain.getString(5);
								rate = rsMain.getDouble(6);
								rateStduom = rsMain.getDouble(7);
								discount = rsMain.getDouble(8);
								//Added on 08/06/06 as code changes in PB
								despID = rsMain.getString(9);
								despLineNo = rsMain.getString(10);
								invTraceLineNo = rsMain.getInt(11);

								System.out.println("invLine:"+invLine+":lotNo:"+lotNo+":lotSl:"+lotSl+":qty:"+qty+":");
								System.out.println("rate:"+rate+":rateStduom:"+rateStduom+":discount:"+discount+":");
								System.out.println("Desp ID :"+despID+": despLineNo :"+despLineNo+":invTraceLineNo :"+invTraceLineNo+":");

								//Picked total qty from invoice and line no of invoice
								invQty = 0;
								sql="SELECT CASE WHEN (SUM(CASE WHEN QUANTITY__STDUOM IS NULL THEN 0 ELSE QUANTITY__STDUOM END))"+
									"IS NULL THEN 0 ELSE SUM(CASE WHEN QUANTITY__STDUOM IS NULL THEN 0 ELSE QUANTITY__STDUOM END)"+
									"END FROM INVOICE_TRACE "+
									" WHERE INVOICE_ID ='"+invoiceId+"'  AND INV_LINE_NO = "+invLine+""+ //Single quote removed as it does not work in PB.
									" AND LINE_NO =	"+invTraceLineNo+""; // Added on 08/06/06 as code changes in PB

								System.out.println("SalesReturn :actionFullSr:INVOICE_TRACE:2:sql:"+sql);
								stmt = conn.createStatement();
								rs = stmt.executeQuery(sql);
								if(rs.next())
								{
									invQty =rs.getDouble(1)	;
								}
								rs.close();//Gulzar - 21/11/06
								stmt.close();
								System.out.println("invQty:"+invQty+":");
								//Picked total qty from sreturn for invoice and line no of invoice
								sreturnQty = 0;
								sql=" SELECT CASE WHEN (SUM(CASE WHEN QUANTITY__STDUOM IS NULL THEN 0 ELSE QUANTITY__STDUOM END)) "+
									" IS NULL THEN 0 ELSE SUM(CASE WHEN QUANTITY__STDUOM IS NULL THEN 0 ELSE QUANTITY__STDUOM END) END"+
									" FROM SRETURN A, SRETURNDET B "+
									"  WHERE A.TRAN_ID = B.TRAN_ID "+
									"  AND B.INVOICE_ID ='"+invoiceId+"'"+
									"  AND B.LINE_NO__INV = "+invLine+" "+
									"  AND (B.LINE_NO__INVTRACE = "+invTraceLineNo+ "  OR  B.LINE_NO__INVTRACE IS NULL OR B.LINE_NO__INVTRACE = 0)"+ //Gulzar 02/09/06
									"  AND B.LOT_NO ='"+lotNo+"'"+ //Gulzar 02/09/06
									"  AND B.LOT_SL ='"+lotSl+"'"+ //Gulzar 02/09/06
									"  AND A.STATUS <> 'X' " ;
								System.out.println("SalesReturn :actionFullSr: SRETURN & SRETURNDET:1:sql:"+sql);
								stmt = conn.createStatement();
								rs = stmt.executeQuery(sql);
								if(rs.next())
								{
									sreturnQty =rs.getDouble(1);
								}
								rs.close();//Gulzar - 21/11/06
								stmt.close();
								System.out.println("sreturnQty:"+sreturnQty+":");
								sql=" SELECT SUM(B.QUANTITY__STDUOM) FROM SRETURN A, SRETURNDET B "+
									"	WHERE B.TRAN_ID = A.TRAN_ID "+
									"	AND B.INVOICE_ID ='"+invoiceId+"' "+
									"	AND B.LINE_NO__INV = "+invLine+" "+
									"	AND (B.LINE_NO__INVTRACE = "+invTraceLineNo+ "  OR  B.LINE_NO__INVTRACE IS NULL OR B.LINE_NO__INVTRACE = 0)"+ //Gulzar 02/09/06
									"	AND B.LOT_NO ='"+lotNo+"' "+ //UnComment by Gulzar 02/09/06
									"	AND B.LOT_SL ='"+lotSl+"' "+ //UnComment by Gulzar 02/09/06
									"	AND B.TRAN_ID <> '"+tranId+"' "+
									"	AND A.STATUS <> 'X' " ;
								System.out.println("SalesReturn :actionFullSr: SRETURN & SRETURNDET:2:sql:"+sql);
								//into :lc_prev_srqty
								stmt = conn.createStatement();
								rs = stmt.executeQuery(sql);
								if(rs.next())
								{
									prevSrqty =rs.getDouble(1)	;
								}
								rs.close();//Gulzar - 21/11/06
								stmt.close();
								System.out.println("prevSrqty:"+prevSrqty+":");
								/*if(prevSrqty != 0 )//check for null
								{
									prevSrqty = 0 ;
								} */
								qty = qty - prevSrqty ;
								if (qty == 0)
								{
									continue;
								}
								sreturnQty = sreturnQty + qty ;
								setFlag = "Y"; // Changed by Sharon in PB script on 24th nov,05.
								/*** Commented in PB script by Sharon on 24th nov,05.
								if(sreturnQty < invQty)
								{
									setFlag = "N";
								}
								else
								{
									setFlag = "Y";
								}
								***/
								System.out.println("setFlag:"+setFlag+":");
								valueXmlString.append("<Detail>\r\n");
								//valueXmlString.append("<line_no__inv isSrvCallOnChg='1'>").append("<![CDATA[").append(invLine).append("]]>").append("</line_no__inv>\r\n"); //Gulzar 02/09/06
								valueXmlString.append("<line_no__invtrace isSrvCallOnChg='1'>").append("<![CDATA[").append(invTraceLineNo).append("]]>").append("</line_no__invtrace>\r\n"); //Gulzar 02/09/06
								valueXmlString.append("<quantity isSrvCallOnChg='1'>").append("<![CDATA[").append(qty).append("]]>").append("</quantity>\r\n");

								//Added on 08/06/06 as code changes in PB
								sql = "SELECT LOC_CODE FROM DESPATCHDET WHERE DESP_ID = '"+despID+"' AND LTRIM(RTRIM(LINE_NO)) = LTRIM(RTRIM('"+despLineNo+"'))";
								stmt = conn.createStatement();
								rs = stmt.executeQuery(sql);
								if (rs.next())
								{
									String locCode = "",locDescr = "";
									locCode = rs.getString("LOC_CODE");
									rs.close();
									stmt.close();
									if (locCode != null && locCode.trim().length() > 0)
									{
										valueXmlString.append("<loc_code>").append("<![CDATA[").append(locCode).append("]]>").append("</loc_code>\r\n");
										sql = "SELECT DESCR FROM LOCATION WHERE LOC_CODE = '"+locCode+"'";
										stmt = conn.createStatement();
										rs = stmt.executeQuery(sql);
										if (rs.next())
										{
											locDescr = rs.getString("DESCR");
											valueXmlString.append("<location_descr>").append("<![CDATA[").append(locDescr).append("]]>").append("</location_descr>\r\n");
										}
										rs.close();
										stmt.close();
									}
								}
								// End
								//rs.close();//Gulzar - 21/11/06
								//stmt.close();
								valueXmlString.append("<full_ret>").append("<![CDATA[").append(setFlag.trim()).append("]]>").append("</full_ret>\r\n");
								valueXmlString.append("<reas_code>").append("<![CDATA[").append(reasonCode.trim()).append("]]>").append("</reas_code>\r\n");
								valueXmlString.append("<lot_no>").append("<![CDATA[").append(lotNo.trim()).append("]]>").append("</lot_no>\r\n");
								//valueXmlString.append("<rate>").append("<![CDATA[").append(rate).append("]]>").append("</rate>\r\n");//Gulzar 02/09/06
								//valueXmlString.append("<rate__stduom>").append("<![CDATA[").append(rateStduom).append("]]>").append("</rate__stduom>\r\n"); //Gulzar 02/09/06
								valueXmlString.append("<discount>").append("<![CDATA[").append(discount).append("]]>").append("</discount>\r\n");
								//valueXmlString.append("<lot_sl isSrvCallOnChg='1'>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
								// 08/08/08 manoharan itemchange not required
								valueXmlString.append("<lot_sl>").append("<![CDATA[").append(lotSl.trim()).append("]]>").append("</lot_sl>\r\n");
								rate = 0;
								sql="SELECT RATE FROM INVOICE_TRACE "+
									" WHERE INVOICE_ID ='"+invoiceId+"'"+
									" AND LINE_NO =	"+invTraceLineNo+"";

								//into :lc_prev_srqty
								stmt = conn.createStatement();
								rs = stmt.executeQuery(sql);
								if(rs.next())
								{
									rate = rs.getDouble(1)	;
								}
								rs.close();//Gulzar - 21/11/06
								stmt.close();
								valueXmlString.append("<rate>").append("<![CDATA[").append(rate).append("]]>").append("</rate>\r\n");

								// end 08/08/08 manoharan itemchange not required
								//ls_old_item_code = ls_item_code
								valueXmlString.append("</Detail>\r\n");
							}//loop closing
							rsMain.close();//Gulzar - 21/11/06
							stmtMain.close();
						}//reasonCode != N (End if)
						else
						{
							 System.out.println("reasonCode is null");
						}
					}//detcnt = o (End if)
				}//fullSr != N (End if)
		 	}//conf != Y (End if)
			valueXmlString.append("</Root>\r\n");
		}
		catch(Exception e)
		{
			System.out.println("Exception :SalesReturn :actionFullSR:" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");

				conn.close();
			}catch(Exception e){}
	    }
		System.out.println("SalesReturn :actionFullSR:Final Value :valueXmlString.toString():"+valueXmlString.toString());
		return valueXmlString.toString();
	}
	public String getRelativeDate(int no) throws RemoteException , ITMException
	{
		String relativeDate = "";
		Calendar  calObject = Calendar.getInstance();
		java.util.Date  dateId =new java.util.Date();
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			calObject.setTime(dateId);
			calObject.add(Calendar.DATE,no);
			dateId = calObject.getTime();
			System.out.println("getRelativeDate():dateId:"+dateId.toString());
			relativeDate = sdf.format(dateId);
		}catch(Exception e)
		{
			System.out.println("Exception :SalesReturn:getRelativeDate() :" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
			System.out.println("getRelativeDate():relativeDate:"+relativeDate);
		return relativeDate;
	}
}