
package ibase.webitm.ejb.mfg;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.lang.*;
import javax.ejb.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import java.util.HashMap;
import java.rmi.RemoteException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.text.NumberFormat;
import javax.naming.InitialContext;

import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistStkUpdLocal;
import ibase.webitm.ejb.fin.*;
import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.utility.CommonConstants;
//import ibase.utility.CommunicationComp;
import ibase.webitm.utility.ITMException;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import org.xml.sax.InputSource;
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 javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class WOIssueReplacementPrc extends ProcessEJB implements GenerateReceiptPrcLocal ,GenerateReceiptPrcRemote //SessionBean
{
	ibase.webitm.utility.GenericUtility genericUtility = ibase.webitm.utility.GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	CommonConstants commonConstants = new CommonConstants();
	PreparedStatement pstmt = null;
	Connection conn = null;
	ResultSet rs = null;

	/*public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			System.out.println("GenerateReceiptPrcEJB ejbCreate called.........");
		}
		catch (Exception e)
		{
			System.out.println("Exception :GenerateReceiptPrcEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate()
	{
	}
	public void ejbPassivate()
	{
	}*/
	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();

		try
		{
			if(conn==null)
			{
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
			}

			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2);
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams,conn);

			System.out.println("@@@@@ final return string["+retStr+"]");

			if( retStr != null && retStr.trim().length() > 0 )
			{
				System.out.println("transaction update failed........");
				//retStr = itmDBAccessLocal.getErrorString("","VTPROUNSU9","");
				conn.rollback();
			}
			else
			{
				System.out.println("work order issue replacement generated succesfully........");
				retStr = itmDBAccessLocal.getErrorString("","PRCUSUCCE9","");	
				conn.commit();
			}
		}
		catch (Exception e)
		{
			System.out.println("Exception :GenerateReceiptPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally 
		{
			try 
			{
				if (conn != null) 
				{
					conn.close();
					conn = null;
				}
				} catch (Exception e) 
			{
				System.out.println("Exception :GenerateReceiptPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retStr;
	}		    
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams,Connection conn) throws RemoteException,ITMException
	{
		GenericUtility genericUtility = GenericUtility.getInstance();

		//java.sql.Timestamp chgDate = new java.sql.Timestamp(System.currentTimeMillis());
		String sql = "",sql2 = "";
		PreparedStatement pstmt = null,pstmtdet =null,pstmt1 = null ,pstmt2 = null ;
		ResultSet rs = null,rsdet=null,rs1 = null ,rs2 = null ;
		String chgUser = "",chgTerm = "";
		NodeList parentNodeList = null,parentNodeList2 = null;
		NodeList childNodeList = null,childNodeList2 = null;
		Node parentNode = null,parentNode2 = null;
		Node childNode = null,childNode2 = null;
		String childNodeName = "",childNodeName2 = "";
		int parentNodeListLength = 0,parentNodeListLength2 = 0, cnt=0;
		int childNodeListLength = 0,childNodeListLength2 = 0;

		String siteCode="",itemSer="";
		String retStr = "";
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();

		Object date1 = null;
		Timestamp currDate1 =new java.sql.Timestamp(System.currentTimeMillis()) ;
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-mm-dd");
		String expLev="",itemCode="",locCode="",lotNo="",lotSl="",unit="",matchPotency="",adjPotency="";
		String keyString="",keyCol="",tranSer1="";
		String tranId ="",qcReqd="",bomCode="",itemCodeRef="";
		String workOrder="",issSlip="",deptCode="",tranType="";
		String woirType="",empCode="",empCodeAprv="",status="",locGroup="",itemCodeHdr="";
		String lotNoIssue="",locUsageType="",ConsiderLotNoIssue="",mfgRef="",itemRef="";
		String stockSupplier="",supp_code__mfg="",suppCodeMfg="",reasCode="";
		String stockSql ="",insertQuery="",stock_lot_no="",stock_loc_code="",stock_lot_sl="";
		String calcPotency="";
		double  rate=0,minPotencyPerc=0,quantity=0,potencyAdj=0,grossRate=0,ctrdet=0;
		int lineNo =0,count=0,insrtCtr=0,upd=0,noArt=0,operation=0,counter=0,batchLoadCnt=0;
		double extraQty=0,issQty=0,StockPotencyPerc=0,itemstdpotency=0;
		boolean issForBatch =false;

		String errCode="",issCriteria="",errString="",orderType="";
		double issueQtyWOI=0;
		double  issueQtyBill =0,potency_adj =0,diff =0,stockBalQty=0; //,issQty1 =0;
		String unitRef="",lineType="",locCodeAlloc="",usageType="";
		String loc_usageType="",considerLotNoIssue="";
		Double refQty=0.0,allocQty=0.0,convQtyAlt=0.0,quantityAlt=0.0,quantityStd=0.0;
		HashMap diffReplaceQty = null;
		//TreeSet expLevSet = null;
		ArrayList <String>listExpLev=new ArrayList<String>();
		String supp_code__mfgSep ="", lotNoIssueTemp = "", operationStr="",tranSer="";
		String lotNoList[];

		HashMap hashMap = new HashMap(); 

		try
		{
			conn.setAutoCommit(false);
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgUser");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
			//chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chg_date");
			java.util.Date currentDate = new java.util.Date();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			Timestamp newsysDate = java.sql.Timestamp.valueOf( sdf.format(currentDate)+" 00:00:00.0");
			System.out.println("Now the date is :=>  ["+newsysDate+"]");
			workOrder = genericUtility.getColumnValue("work_order",headerDom);
			operationStr = genericUtility.getColumnValue("operation",headerDom);
			if( operationStr!= null && operationStr.trim().length() > 0 )
			{
				for (char c : operationStr.toCharArray())
				{
					if (!Character.isDigit(c)) 
					{
						retStr =itmDBAccessEJB.getErrorString("","VM OPERINV","","",conn); 
						return retStr; 
					}
				}
				operation = Integer.parseInt(operationStr);

			}
			deptCode = genericUtility.getColumnValue("dept_code",headerDom);
			System.out.println("@@@@@ data---->workOrder["+workOrder+"]::operation["+operation+"]::dept_code["+deptCode+"]");
			diffReplaceQty = new HashMap();
			double qtyPerArt=0;
			int totalPacket=0;

			// for workOrder 
			if( workOrder != null && workOrder.trim().length() > 0 )
			{
				sql = " select count(1) from workorder where work_order = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				rs = pstmt.executeQuery();
				while(rs.next())
				{
					cnt = rs.getInt(1) ;
				}	
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	
				if( cnt == 0 )
				{
					retStr =itmDBAccessEJB.getErrorString("","VTWORD1","","",conn); 
					return retStr;
				}

				sql = " select count(1) from workorder  where work_order = ? and  status = 'F' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				rs = pstmt.executeQuery();
				while(rs.next())
				{
					cnt = rs.getInt(1) ;
				}	
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	
				if( cnt > 0 )
				{
					retStr =itmDBAccessEJB.getErrorString("","VTWORDER4","","",conn); 
					return retStr;
				}


				sql = " select count(1) from workorder_iss i, workorder_issdet d where i.tran_id=d.tran_id " +
						" and i.work_order = ? and i.operation = ? " +
						" and i.confirmed = 'Y' and i.tran_type != 'R' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				pstmt.setInt(2,operation);
				rs = pstmt.executeQuery();
				while(rs.next())
				{
					cnt = rs.getInt(1) ;
				}	
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	
				if( cnt == 0 )
				{
					retStr =itmDBAccessEJB.getErrorString("","VMWORDRINV","","",conn); 
					return retStr;
				}
			}
			else
			{
				retStr =itmDBAccessEJB.getErrorString("","VMWORDRNUL","","",conn); 
				return retStr;
			}


			// for deptCode
			if( deptCode == null || deptCode.trim().length() == 0 )
			{
				retStr =itmDBAccessEJB.getErrorString("","VMDEPTCNUL","","",conn); 
				return retStr;
			}
			else
			{
				sql ="select count(1) from department where dept_code = ?  ";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,deptCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
				if( cnt==0)
				{
					retStr =itmDBAccessEJB.getErrorString("","VMDEP1","","",conn); 
					return retStr;
				}
			} 

			// for operation
			if(operation == 0 )
			{
				retStr =itmDBAccessEJB.getErrorString("","VMITEMCZ","","",conn); 
				return retStr;
			}
			if( operation > 0 )
			{

				sql ="select count(1) from workorder_bill where work_order = ? and operation = ?  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				pstmt.setInt(2,operation);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
				if( cnt==0)
				{
					retStr =itmDBAccessEJB.getErrorString("","VMOPERINV","","",conn); 
					return retStr;
				}


			} 

			// sql for getdata
			date1 = sdf1.parse(currDate1.toString());
			currDate1 =	java.sql.Timestamp.valueOf(sdf1.format(date1).toString() + " 00:00:00.0");
			System.out.println("currDate !"+currDate1);

			sql2 = " select  operation,exp_lev,item_code,quantity,unit,alloc_qty," +
					" site_code,line_type,match_potency,min_potency_perc,adj_potency," +
					" potency_adj,iss_qty from  workorder_bill " +
					" where work_order = ? and operation = ? and line_type != 'B'  and usage_type = 'R' " ;
			pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setString(1,workOrder);
			pstmt2.setInt(2,operation);
			rs2 = pstmt2.executeQuery();
			while( rs2.next() )
			{
				counter	    =counter+1;
				operation	=rs2.getInt("operation");
				expLev		=rs2.getString("EXP_LEV");
				itemCode	=rs2.getString("item_code");
				quantity	=rs2.getDouble("quantity");
				unit		=rs2.getString("unit");
				allocQty	=rs2.getDouble("alloc_qty");
				siteCode	=rs2.getString("site_code");
				lineType	=rs2.getString("line_type");
				matchPotency    =rs2.getString("match_potency");
				minPotencyPerc  =rs2.getDouble("min_potency_perc");
				adjPotency      =rs2.getString("adj_potency");
				potencyAdj   	=rs2.getDouble("potency_adj");
				issQty 		 	=rs2.getDouble("iss_qty");

				issueQtyBill = quantity + potencyAdj ;

				System.out.println("@@@@@@ workorder_bill---> issueQtyBill["+issueQtyBill+"]");

				sql =" select nvl(sum(nvl(quantity,0) + nvl(potency_adj,0)),0) " 
						+" from workorder_iss a, workorder_issdet b "
						+" where  a.tran_id = b.tran_id "
						+" and 	a.work_order= ? "
						+" and b.exp_lev = ?  " 
						+" and a.operation = ? ";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				pstmt.setString(2,expLev);
				pstmt.setInt(3,operation);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					issueQtyWOI	=rs.getDouble(1);
				}
				rs.close();
				pstmt.close();
				issueQtyWOI = getRequiredDecimal(issueQtyWOI,3);
				System.out.println("@@@@@@ workorder_iss---> issueQtyWOI["+issueQtyWOI+"]");

				if( issueQtyBill > issueQtyWOI)
				{
					diff =   issueQtyBill - issueQtyWOI ;
					listExpLev.add(expLev);
					diffReplaceQty.put(expLev,diff);
					System.out.println("@@@@@ expLev["+expLev+"]::diff["+diff+"]::issueQtyWOI["+issueQtyWOI+"]::issueQtyBill["+issueQtyBill+"]::listExpLev["+listExpLev+"]");
				}	
			}  
			rs2.close();
			pstmt2.close();
			rs2 = null;
			pstmt2 = null;

			if ( !( diffReplaceQty.isEmpty() ))			
			{
				String diffStr=""; 
				double reqQty=0.0;
				int lnNo=0;

				///// for insert into workorder_iss
				sql =" select SITE_CODE,LOC_GROUP,ITEM_CODE,ORDER_TYPE from  workorder  where work_order= ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,workOrder);
				rs1 = pstmt1.executeQuery();
				System.out.println("\nQuery Executed " + sql+ "\n\n******************");
				if(rs1.next())
				{ 
					siteCode		= rs1.getString("SITE_CODE");
					locGroup		= rs1.getString("LOC_GROUP");
					itemCodeHdr	= rs1.getString("ITEM_CODE");
					orderType		= rs1.getString("ORDER_TYPE");
				}
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1 =null;

				woirType = orderType.substring(0,1)+"I";

				sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE lower(TRAN_WINDOW) = 'w_wo_iss_replacement'";
				System.out.println("keyStringQuery--------->>"+sql);
				pstmt1 = conn.prepareStatement(sql);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{ 
					keyString = rs1.getString(1);
					keyCol = rs1.getString(2);
					tranSer1 = rs1.getString(3);				
				}
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1 =null;

				System.out.println("keyString :"+ keyString);
				System.out.println("keyCol :"+ keyCol);
				System.out.println("tranSer1 :"+ tranSer1);
				System.out.println("woirType :"+ woirType);
				String xmlValues = "";
				String tranDateStr = getCurrdateAppFormat();
				xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +	"<tran_id></tran_id>";
				xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";	
				xmlValues = xmlValues + "<woir_type>" + woirType + "</woir_type>";
				xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>"; 
				xmlValues = xmlValues +"</Detail1></Root>";
				System.out.println("xmlValues  :["+xmlValues+"]");
				TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
				tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
				System.out.println("@@@@ generated tranId :["+tranId+"]");

				//////1 insert into  workorder_iss
				insertQuery =" insert into  workorder_iss (TRAN_ID,TRAN_DATE,EFF_DATE,WORK_ORDER,OPERATION,SITE_CODE, "
						+" REMARKS,CHG_DATE,CHG_USER,CHG_TERM,DEPT_CODE,TRAN_TYPE, "
						+" LOC_GROUP,ITEM_CODE,WOIR_TYPE,STATUS,CONFIRMED ) "
						+" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
				pstmt = conn.prepareStatement(insertQuery);
				pstmt.setString(1,tranId); 
				pstmt.setTimestamp(2,currDate1);
				pstmt.setTimestamp(3,currDate1);
				pstmt.setString(4,workOrder); 
				pstmt.setInt(5,operation);
				pstmt.setString(6,siteCode); 
				pstmt.setString(7,"for Replacement");
				pstmt.setTimestamp(8,currDate1);
				pstmt.setString(9,chgUser);
				pstmt.setString(10,chgTerm);
				pstmt.setString(11,deptCode);
				pstmt.setString(12,"I");           
				pstmt.setString(13,locGroup);
				pstmt.setString(14,itemCodeHdr);
				pstmt.setString(15,woirType);
				pstmt.setString(16,"O");
				pstmt.setString(17,"N");
				upd = pstmt.executeUpdate();

				System.out.println("@@@@@@@@listExpLev.size()["+listExpLev.size()+"]");
				for (int  i=0;i <=listExpLev.size()-1; i++ )
				{
					expLev = listExpLev.get(i);
					diffStr = diffReplaceQty.get(expLev)==null?"0":diffReplaceQty.get(expLev).toString();
					reqQty =  Double.parseDouble(diffStr);
					System.out.println("@@@@@@@@@@@@---expLev["+expLev+"]::::reqQty["+reqQty+"]");
					if( reqQty > 0)
					{
						//bom 
						sql ="select a.bom_code, b.item_code__ref,a.loc_group,a.lot_no__issue "
								+" from  workorder a, workorder_bill b where a.work_order = b.work_order  "
								+" and a.work_order = ? "
								+" and b.exp_lev    = ? "
								+" and b.item_code  = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,workOrder);
						pstmt.setString(2,expLev);
						pstmt.setString(3,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							bomCode = rs.getString(1);
							itemCodeRef	= rs.getString(2);
							locGroup	= rs.getString(3);
							lotNoIssue	= rs.getString(4);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;

						sql ="select supp_code__mfg ,loc_usage_type,Consider_lot_no__issue,mfg_ref "
								+" from  bomdet  where bom_code = ?  "
								+" and item_code = ? "
								+" and item_ref    = ? ";

						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,bomCode);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,itemCodeRef);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							supp_code__mfg = rs.getString(1);
							locUsageType = rs.getString(2);
							ConsiderLotNoIssue = rs.getString(3);
							mfgRef = rs.getString(4);
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt =null;

						System.out.println("@@@@@  mfgRef [" +mfgRef + "]");

						if(  mfgRef != null && mfgRef.trim().length() > 0)
						{
							sql = "select loc_code,lot_no,lot_sl from workorder_issdet where item_code = ?  and site_code = ? "
									+" and work_order = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							pstmt.setString(2,siteCode);
							pstmt.setString(3,workOrder);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								locCode = rs.getString("loc_code");
								lotNo = rs.getString("lot_no");
								lotSl = rs.getString("lot_sl");
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;	

							sql = "select supp_code__mfg 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,lotSl);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								stockSupplier = rs.getString("supp_code__mfg");
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;	
							issForBatch =true;
						}
						System.out.println("@@@@ supp_code__mfg............"+ supp_code__mfg);
						if(supp_code__mfg != null && supp_code__mfg.trim().length() > 0)
						{
							String arrStr[] =supp_code__mfg.split(",");
							int len =arrStr.length;
							for(int j =0;j<len;j++)
							{
								supp_code__mfgSep =supp_code__mfgSep+"'"+arrStr[j]+"',";	
							}
							supp_code__mfgSep =supp_code__mfgSep.substring(0,supp_code__mfgSep.length()-1);
							System.out.println("supp_code__mfgSep"+supp_code__mfgSep);
						}

						System.out.println("@@@@@ lotNoIssue before ["+lotNoIssue + "]");
						if (lotNoIssue != null && lotNoIssue.trim().length() > 0)
						{
							lotNoList = lotNoIssue.split(",");
							lotNoIssue = "";
							for(int ctr = 0;ctr < lotNoList.length;ctr++)
							{
								lotNoIssueTemp = lotNoList[ctr];
								if (lotNoIssue.trim().length() > 0)
								{
									lotNoIssue	= lotNoIssue + ",'".concat(lotNoIssueTemp).concat("'");
								}
								else
								{
									lotNoIssue	= lotNoIssue + "'".concat(lotNoIssueTemp).concat("'");
								}
							}
						}

						//// for stock check
						sql =" select sum(quantity-alloc_qty) as stockBalQty from stock a where a.item_code = ? "
								+" and  a.site_code = ? "
								+" and ( a.exp_date > ?  or  a.retest_date < ? ) ";  
						if(issForBatch)
						{ 
							sql = sql + " and a.supp_code__mfg ='"+stockSupplier+"'" ;
						}
						else
						{
							if( supp_code__mfg != null && supp_code__mfg.trim().length() > 0 )
							{
								sql = sql + " and a.supp_code__mfg  in (" +  supp_code__mfgSep  + ") " ;
							}
						}
						if( "Y".equalsIgnoreCase(ConsiderLotNoIssue) && (lotNoIssue != null && lotNoIssue.trim().length() > 0) )
						{
							sql = sql + " and a.lot_no in ("+ lotNoIssue +")";
						}
						if( "A".equalsIgnoreCase(locUsageType) && "N".equalsIgnoreCase(ConsiderLotNoIssue) )
						{
							sql = sql + " and a.loc_code in ( select loc_code from location where loc_group ='"+locGroup+"' )" ;
						}
						//on 31/07/13 
						sql = sql +" and  (  select count(1) from qc_order  where item_code =a.item_code  and site_code= a.site_code and lot_no = a.lot_no  and status = 'U')=0 " ;

						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,itemCode);
						pstmt1.setString(2,siteCode);
						pstmt1.setTimestamp(3,newsysDate );
						pstmt1.setTimestamp(4,newsysDate );
						//pstmt1.setString(5,itemCode);
						//pstmt1.setString(6,siteCode);
						//pstmt1.setString(7,lotNo);

						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{ 
							stockBalQty = rs1.getDouble(1);
						}
						rs1.close();
						rs1=null;
						pstmt1.close();
						pstmt1 =null;

						System.out.println("\n@@@@@stockBalQty[" + stockBalQty+ "]:::reqQty["+reqQty+"]");

						if(stockBalQty >= reqQty)
						{	
							sql = "select iss_criteria  from item where item_code = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								issCriteria = rs.getString("iss_criteria") ;
							}
							rs.close();
							pstmt.close();
							System.out.println("@@@@@ issCriteria["+issCriteria+"]");

							/*
							sql ="select a.bom_code, b.item_code__ref,a.loc_group,a.lot_no__issue "
									+" from  workorder a, workorder_bill b where a.work_order = b.work_order  "
									+" and a.work_order = ? "
									+" and b.exp_lev    = ? "
									+" and b.item_code  = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							pstmt.setString(2,expLev);
							pstmt.setString(3,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								bomCode = rs.getString(1);
								itemCodeRef	= rs.getString(2);
								locGroup	= rs.getString(3);
								lotNoIssue	= rs.getString(4);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;

							sql ="select supp_code__mfg ,loc_usage_type,Consider_lot_no__issue,mfg_ref "
									+" from  bomdet  where bom_code = ?  "
									+" and item_code = ? "
									+" and item_ref    = ? ";

							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,bomCode);
							pstmt.setString(2,itemCode);
							pstmt.setString(3,itemCodeRef);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								supp_code__mfg = rs.getString(1);
								locUsageType = rs.getString(2);
								ConsiderLotNoIssue = rs.getString(3);
								mfgRef = rs.getString(4);
							}
							rs.close();
							rs =null;
							pstmt.close();
							pstmt =null;

							System.out.println("@@@@@ mfgRef [" +mfgRef + "]");

							if(  mfgRef != null && mfgRef.trim().length() > 0)
							{
								sql = "select loc_code,lot_no,lot_sl from workorder_issdet where item_code = ?  and site_code = ? "
									+" and work_order = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,itemCode);
								pstmt.setString(2,siteCode);
								pstmt.setString(3,workOrder);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									locCode = rs.getString("loc_code");
									lotNo = rs.getString("lot_no");
									lotSl = rs.getString("lot_sl");
								}
								rs.close();
								rs =null;
								pstmt.close();
								pstmt =null;	

								sql = "select supp_code__mfg 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,lotSl);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									stockSupplier = rs.getString("supp_code__mfg");
								}
								rs.close();
								rs =null;
								pstmt.close();
								pstmt =null;	
								issForBatch =true;
							}
							System.out.println("@@@@ supp_code__mfg......................"+ supp_code__mfg);
							 */
							stockSql ="select a.lot_no, a.loc_code, a.lot_sl, a.rate, a.gross_rate,a.supp_code__mfg " 
									+" ,(a.quantity-a.alloc_qty) as stockBalQty "
									+" from stock a, invstat b, item c   " 
									+" Where a.inv_stat = b.inv_stat and a.item_code = c.item_code  "
									+" and a.item_code = '" + itemCode + "' "
									+" and a.site_code  = '"+siteCode+"' "
									+" and b.stat_type <> 'S'   and b.available = 'Y' ";		
							if(issForBatch)
							{ 
								stockSql = stockSql + " and a.supp_code__mfg ='"+stockSupplier+"'" ;
							}
							else
							{
								if(supp_code__mfg != null && supp_code__mfg.trim().length() > 0)
								{
									supp_code__mfgSep ="";
									String arrStr[] =supp_code__mfg.split(",");
									int len =arrStr.length;
									for(int j =0;j<len;j++)
									{
										supp_code__mfgSep =supp_code__mfgSep+"'"+arrStr[j]+"',";	
									}
									supp_code__mfgSep =supp_code__mfgSep.substring(0,supp_code__mfgSep.length()-1);
									System.out.println("supp_code__mfgSep"+supp_code__mfgSep);
									stockSql = stockSql + " and a.supp_code__mfg  in (" +  supp_code__mfgSep  + ") " ;
								}
							}
							System.out.println("@@@@ ConsiderLotNoIssue.........."+ConsiderLotNoIssue);
							System.out.println("@@@@ locUsageType....................."+locUsageType);
							System.out.println("@@@@@ lotNoIssue before ["+lotNoIssue + "]");

							lotNoIssueTemp = "";
							if (lotNoIssue != null && lotNoIssue.trim().length() > 0)
							{
								lotNoList = lotNoIssue.split(",");
								lotNoIssue = "";
								for(int ctr = 0;ctr < lotNoList.length;ctr++)
								{
									lotNoIssueTemp = lotNoList[ctr];
									if (lotNoIssue.trim().length() > 0)
									{
										lotNoIssue	= lotNoIssue + ",'".concat(lotNoIssueTemp).concat("'");
									}
									else
									{
										lotNoIssue	= lotNoIssue + "'".concat(lotNoIssueTemp).concat("'");
									}
								}
							}

							if("Y".equalsIgnoreCase(ConsiderLotNoIssue) && (lotNoIssue != null && lotNoIssue.trim().length() > 0))
							{
								stockSql = stockSql + " and a.lot_no in ("+ lotNoIssue +")";
							}
							if("A".equalsIgnoreCase(locUsageType) && "N".equalsIgnoreCase(ConsiderLotNoIssue) )
							{
								stockSql = stockSql + " and a.loc_code in ( select loc_code from location where loc_group ='"+locGroup+"' )" ;
							}
							stockSql = stockSql +" and  a.exp_date > ? " ;
							stockSql = stockSql +" and  a.retest_date > ? " ;
							//on 31/07/13 
							stockSql = stockSql +" and  (  select count(1) from qc_order  where item_code =a.item_code  and site_code= a.site_code and lot_no = a.lot_no  and status = 'U')=0 " ;
							stockSql = stockSql +" and  ( a.quantity - a.alloc_qty ) > 0 ";
							System.out.println("\n\n\n Query Executed " + stockSql+ "\n\n******************");
							pstmt = conn.prepareStatement(stockSql);
							pstmt.setTimestamp(1,currDate1);
							pstmt.setTimestamp(2,currDate1);
							//pstmt.setString(3,itemCode);
							//pstmt.setString(4,siteCode);
							//pstmt.setString(5,lotNo);

							rs = pstmt.executeQuery();
							if(rs.next())
							{	
								stock_lot_no		= rs.getString(1);
								stock_loc_code		= rs.getString(2);
								stock_lot_sl		= rs.getString(3);
								rate				= rs.getDouble(4);
								grossRate			= rs.getDouble(5);
								suppCodeMfg			= rs.getString(6);
								stockBalQty         = rs.getDouble(7);
							}
							else
							{
								//errCode =itemCode+","+siteCode+"VTSTKEXORT";
								errCode ="VTSTKEXORT";
								System.out.println("errCode .........."+errCode);
								retStr =itmDBAccessEJB.getErrorString("","VTSTKEXORT","","",conn);
								return retStr;
							}
							System.out.println("stock_loc_code ..."+stock_loc_code);
							System.out.println("stock_lot_sl ..."+stock_lot_sl);
							System.out.println("stock_lot_no ..."+stock_lot_no);
							System.out.println("stockBalQty ..."+stockBalQty);

							sql ="select item_ser, (case when potency_perc is null then 0 else potency_perc end) "
									+" from   item where item_code = ?  ";	
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								itemSer = rs.getString(1);
								itemstdpotency= rs.getDouble(2);
							}
							if("Y".equalsIgnoreCase(matchPotency))
							{
								sql ="select (case when potency_perc is null then 0 else potency_perc end) "
										+" 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,stock_loc_code);
								pstmt.setString(4,stock_lot_no);
								pstmt.setString(5,stock_lot_sl);

								rs = pstmt.executeQuery();
								if(rs.next())
								{	
									StockPotencyPerc= rs.getDouble(1);
									System.out.println("StockPotencyPerc ..."+StockPotencyPerc);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								extraQty = (quantity / StockPotencyPerc * itemstdpotency ) - quantity;
								issQty = quantity;
								System.out.println("@@@@@@ test extraQty["+extraQty+"]quantity["+quantity+"]issQty["+issQty+"]");

								// added by cpatil on 31/07/13 for criteria type is F
								if(issCriteria != null && issCriteria.equalsIgnoreCase("F"))
								{
									sql =" select qty_per_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,stock_loc_code);
									pstmt.setString(4,stock_lot_no);
									pstmt.setString(5,stock_lot_sl);
									rs = pstmt.executeQuery();
									if(rs.next())
									{
										qtyPerArt = rs.getDouble(1);
									}
									rs.close();
									pstmt.close();

									if( (reqQty % qtyPerArt) > 0 )
									{
										System.out.println("@@@@@@@reqQty11:["+reqQty+"]");
										totalPacket = (int)(reqQty/qtyPerArt) + 1;
										reqQty = totalPacket * qtyPerArt;
										System.out.println("@@@@@@@reqQty22:["+reqQty+"]");
									}
								}
								//end

							}

							// test start 06/09/13
							String stkOpt="";
							sql = "SELECT STK_OPT FROM ITEM WHERE ITEM_CODE = ? ";
							System.out.println("sql :"+sql);
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,itemCode);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								stkOpt = rs.getString(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;


							System.out.println("@@@@@@@@stkOpt["+stkOpt+"]");
							if( "0".equalsIgnoreCase(stkOpt))
							{
								System.out.println("@@@@@@@@ inside stkOpt["+stkOpt+"]");
								//	insert into workorder_issdet	
								lnNo++;	
								batchLoadCnt++;
								sql ="insert into workorder_issdet (tran_id,line_no,exp_lev,item_code,loc_code,lot_no," +
										" lot_sl,quantity,unit,rate,gross_rate,calc_potency, potency_adj," +
										" match_potency,min_potency_perc,adj_potency,work_order)" +
										" values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,tranId); 
								pstmt.setInt(2,lnNo);
								pstmt.setString(3,expLev);
								pstmt.setString(4,itemCode);
								pstmt.setString(5,locCode);
								pstmt.setString(6," ");
								pstmt.setString(7," ");
								pstmt.setDouble(8,reqQty);
								pstmt.setString(9,unit);
								pstmt.setDouble(10,rate);
								pstmt.setDouble(11,grossRate);
								pstmt.setString(12,calcPotency);
								pstmt.setDouble(13,extraQty);	  
								pstmt.setString(14,matchPotency);
								pstmt.setDouble(15,minPotencyPerc);
								pstmt.setString(16,adjPotency); 
								pstmt.setString(17,workOrder);
								pstmt.addBatch();
								pstmt.clearParameters();

								// for stock allocation
								System.out.println("@@@@@ inside stkopt lineNo :["+lineNo+"] itemCode :["+itemCode+"] loc_code :["+locCode+"]:: reqQty :["+reqQty+"]");
								//allocQty = -1 * reqQty;

								//line = "   " + lineNo;
								hashMap.put("tran_date", new java.sql.Date(System.currentTimeMillis()));
								hashMap.put("ref_ser",tranSer1);
								hashMap.put("ref_id", tranId);
								hashMap.put("ref_line", lnNo);
								hashMap.put("item_code", itemCode);
								hashMap.put("site_code", siteCode);
								hashMap.put("loc_code",locCode);
								hashMap.put("lot_no"," ");
								hashMap.put("lot_sl"," ");
								hashMap.put("alloc_qty", new Double(reqQty));
								hashMap.put("chg_win","W_WO_ISS_REPLACEMENT");
								hashMap.put("chg_user", GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginCode"));
								hashMap.put("chg_term", GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "termId"));
								if (updAllocTrace(hashMap, conn) > 0)
								{
									System.out.println("distStkUpd.UpdAllocTrace(HashMap, Connection) : Sucessuful!");	
									hashMap.clear();
								}


							}
							// end on 06/09/13
							else if ( stockBalQty > 0 )
							{	
								//	insert into workorder_issdet	
								lnNo++;	
								batchLoadCnt++;
								sql ="insert into workorder_issdet (tran_id,line_no,exp_lev,item_code,loc_code,lot_no," +
										" lot_sl,quantity,unit,rate,gross_rate,calc_potency, potency_adj," +
										" match_potency,min_potency_perc,adj_potency,work_order)" +
										" values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,tranId); 
								pstmt.setInt(2,lnNo);
								pstmt.setString(3,expLev);
								pstmt.setString(4,itemCode);
								pstmt.setString(5,stock_loc_code);
								pstmt.setString(6,stock_lot_no);
								pstmt.setString(7,stock_lot_sl);
								pstmt.setDouble(8,reqQty);
								pstmt.setString(9,unit);
								pstmt.setDouble(10,rate);
								pstmt.setDouble(11,grossRate);
								pstmt.setString(12,calcPotency);
								pstmt.setDouble(13,extraQty);	  
								pstmt.setString(14,matchPotency);
								pstmt.setDouble(15,minPotencyPerc);
								pstmt.setString(16,adjPotency); 
								pstmt.setString(17,workOrder);
								pstmt.addBatch();
								pstmt.clearParameters();


								// for stock allocation
								System.out.println("@@@@@ normal lineNo :"+lineNo+" itemCode :"+itemCode+" stock_loc_code :"+stock_loc_code+" stock_lot_no :"+stock_lot_no+" stock_lot_sl :"+stock_lot_sl+" reqQty :"+reqQty);
								//allocQty = -1 * reqQty;

								//line = "   " + lineNo;
								hashMap.put("tran_date", new java.sql.Date(System.currentTimeMillis()));
								hashMap.put("ref_ser",tranSer1);
								hashMap.put("ref_id", tranId);
								hashMap.put("ref_line", lnNo);
								hashMap.put("item_code", itemCode);
								hashMap.put("site_code", siteCode);
								hashMap.put("loc_code",stock_loc_code);
								hashMap.put("lot_no",stock_lot_no);
								hashMap.put("lot_sl",stock_lot_sl);
								hashMap.put("alloc_qty", new Double(reqQty));
								hashMap.put("chg_win","W_WO_ISS_REPLACEMENT");
								hashMap.put("chg_user", GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginCode"));
								hashMap.put("chg_term", GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "termId"));

								//Calling DistStkUpdEJB
								System.out.println("Calling DistStkUpdEJB.....");
								if (updAllocTrace(hashMap, conn) > 0)
								{
									System.out.println("distStkUpd.UpdAllocTrace(HashMap, Connection) : Sucessuful!");
									hashMap.clear();
								}


								// end stock allocation

							}
						}
					}
				}

				if( batchLoadCnt > 0)
				{
					pstmt.executeBatch();
					pstmt.clearBatch();
					System.out.println("Record inserted into detail:: count["+batchLoadCnt+"]");
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}

				}
				else if( batchLoadCnt == 0)
				{
					System.out.println("@@@@@@ no detail record inserted ["+batchLoadCnt+"]");
					retStr = itmDBAccessLocal.getErrorString("","VTPROUNSU9","");
				}
			}
			else 
			{
				System.out.println("@@@@@ diffReplaceQty is null ");
				retStr = itmDBAccessLocal.getErrorString("","VTPROUNSU9","");
			}

			System.out.println("@@@@ errString :::["+ retStr+"]" );
			if (retStr != null && retStr.trim().length() > 0)
			{
				System.out.println("@@@@@@ connection rollback...............");
				conn.rollback();
				return retStr;
			}
			else
			{
				System.out.println("@@@@@@ connection commit...............");
				conn.commit();  //commit to transaction
				//retStr = itmDBAccessLocal.getErrorString("","VPSUCC1","");
			}
		}//outer try
		catch (Exception e)
		{
			System.out.println("Exception ::WOIssueReplacementPrc ::process():" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try{
				System.out.println("@@@@ errString :::["+ retStr+"]" );
				if (retStr != null && retStr.trim().length() > 0)
				{
					System.out.println("@@@@@@ connection rollback...............");
					conn.rollback();
					return retStr;
				}
				else
				{
					System.out.println("@@@@@@ connection commit...............");
					conn.commit();  //commit to transaction
					//retStr = itmDBAccessLocal.getErrorString("","VPSUCC1","");
				}
				//System.out.println("Closing Connection2....");
				//conn.close();
				//conn = null;
			}
			catch(Exception se)
			{

			}
		}//

		return retStr;

	}//process()

	public double getRequiredDecimal(double actVal, int prec)  throws Exception,ITMException
	{
		/*NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
			int multiply = 1;
			if (actVal < 0 )
			{
				multiply = -1;
				actVal =  actVal * multiply;
			}
			Double DoubleValue = new Double (actVal);
			numberFormat.setMaximumFractionDigits(3);
			String strValue = numberFormat.format(DoubleValue);
			strValue = strValue.replaceAll(",","");
			double reqVal = Double.parseDouble(strValue);
			reqVal =  reqVal * multiply;
			return reqVal; */
		return getReqDecimal(actVal, prec);
	}

	public double getReqDecimal(double actVal, int prec)  throws ITMException, Exception
	{
		DecimalFormat decFormat = null;
		try
		{
			String fmtStr = "############0";
			String strValue = null;
			if (prec > 0)
			{
				fmtStr = fmtStr + "." + "000000000".substring(0, prec);
			}
			decFormat = new DecimalFormat(fmtStr);
		}
		catch(Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			actVal = 0;
			System.out.println("@@@@@@ Double.parseDouble(decFormat.format(actVal)) [" + Double.parseDouble(decFormat.format(actVal)) + "]");
			throw new ITMException(e);
		}
		return Double.parseDouble(decFormat.format(actVal));
	}

	private String getCurrdateAppFormat()
	{
		String s = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());
			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;
	}	

	public int updAllocTrace(HashMap hmp, Connection conn) throws RemoteException,ITMException
	{
		String stkOpt = "", tranID = "", keyString = "", sql = "";
		String refId = "", refLine = "", refSer = "", itemCode = "", siteCode = "", locCode = "", lotNo = "",	lotSl = "";
		String chgWin="", chgUser = "", chgTerm = "";
		String sqlUpd = "";
		PreparedStatement pstmt = null;
		Statement stmt = null, stmt1 = null;
		ResultSet rs = null;
		HashMap keyGenerator = null;
		double allocQty = 0d;
		int updateCnt = 0, retVal = 0;
		GenericUtility genericUtility = GenericUtility.getInstance();
		TransIDGenerator transIDGenerator = null;
		try
		{
			stmt = conn.createStatement();
			sql = "SELECT STK_OPT FROM ITEM WHERE ITEM_CODE = '" +hmp.get("item_code").toString()+"'";
			System.out.println("sql :"+sql);
			rs = stmt.executeQuery(sql);
			if (rs.next())
			{
				stkOpt = rs.getString(1);
				if (stkOpt.equals("0"))
				{
					retVal = 0;
					return retVal;
				}
			}
			sql="SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'T_ALLOCTRACE'";
			System.out.println("UpdAllocTrace: sql:"+sql);
			rs = stmt.executeQuery(sql);
			if (rs.next())
			{
				keyString = rs.getString(1);
			}
			else
			{
				sql = "SELECT KEY_STRING FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'GENERAL' ";
				System.out.println("sql :"+sql);
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					keyString = rs.getString(1);
					System.out.println("keyString :"+keyString);
				}
			}

			java.sql.Date today = new java.sql.Date(System.currentTimeMillis());
			String tranDate1 = "";
			siteCode = hmp.get("site_code").toString();
			SimpleDateFormat sdf = new SimpleDateFormat (genericUtility.getApplDateFormat());
			tranDate1 = sdf.format(hmp.get("tran_date"));
			String transIDXmlString = "<?xml version=\"1.0\"?>\r\n<Root>\r\n" +
					"<header>\r\n</header>" +
					"<Detail1>" +
					"<tran_id></tran_id>" +
					"<tran_date>" +tranDate1 + "</tran_date>" +
					"<site_code>" + siteCode + "</site_code>" +
					"</Detail1></Root>";

			System.out.println("transIDXmlString : " + transIDXmlString);
			//GETTING DATABASE NAME
			CommonConstants.setIBASEHOME();			
			TransIDGenerator tg = new TransIDGenerator(transIDXmlString, "BASE", CommonConstants.DB_NAME);
			String tranId = tg.generateTranSeqID("ATRACE","tran_id", keyString, conn);
			System.out.println("tranId :"+tranId);
			sql= "INSERT INTO INVALLOC_TRACE (TRAN_ID, TRAN_DATE, REF_SER, REF_ID, REF_LINE, " 
					+"ITEM_CODE, SITE_CODE, LOC_CODE, LOT_NO, LOT_SL, ALLOC_QTY, CHG_WIN, CHG_USER, "
					+"CHG_TERM, CHG_DATE)VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			System.out.println("sql :"+sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			pstmt.setDate(2,java.sql.Date.valueOf(new SimpleDateFormat(genericUtility.getDBDateFormat()).format(hmp.get("tran_date"))));
			pstmt.setString(3,hmp.get("ref_ser").toString());
			pstmt.setString(4,hmp.get("ref_id").toString());
			pstmt.setString(5,hmp.get("ref_line").toString());
			pstmt.setString(6,hmp.get("item_code").toString());
			pstmt.setString(7,hmp.get("site_code").toString());
			pstmt.setString(8,hmp.get("loc_code").toString());
			System.out.println("loc_code: '" + hmp.get("loc_code").toString() + "'");
			pstmt.setString(9,hmp.get("lot_no").toString());
			pstmt.setString(10,hmp.get("lot_sl").toString());
			pstmt.setDouble(11,((Double)hmp.get("alloc_qty")).doubleValue());
			pstmt.setString(12,hmp.get("chg_win").toString());
			pstmt.setString(13,hmp.get("chg_user").toString());
			pstmt.setString(14,hmp.get("chg_term").toString());
			pstmt.setDate(15,today);
			updateCnt = pstmt.executeUpdate();
			System.out.println("insertCnt :"+updateCnt);
			if (updateCnt <= 0)
			{
				retVal = 0;
			}
			else
			{
				retVal = 1;
			}
			System.out.println("AllocQty :"+hmp.get("alloc_qty")+"\n item_Code :"+hmp.get("item_code")+"\n sitecode :"+hmp.get("site_code")+"\nlocCode :"+hmp.get("loc_code"));
			System.out.println("lotno :"+hmp.get("lot_no")+"\n lotSl :"+hmp.get("lot_sl"));

			sqlUpd =  "UPDATE STOCK SET ALLOC_QTY = (CASE WHEN ALLOC_QTY IS NULL THEN 0 ELSE ALLOC_QTY END) + "+((Double)hmp.get("alloc_qty")).doubleValue()+" " +
					" WHERE ITEM_CODE = '"+hmp.get("item_code").toString()+"'" +
					" AND SITE_CODE = '"+hmp.get("site_code").toString()+"' " +
					" AND LOC_CODE = '"+hmp.get("loc_code").toString()+"' " +
					" AND LOT_NO = '"+hmp.get("lot_no").toString()+"' " +
					" AND LOT_SL = '"+hmp.get("lot_sl").toString()+"' ";
			System.out.println("Update sql :"+sqlUpd);
			stmt1 = conn.createStatement();
			updateCnt = stmt1.executeUpdate(sqlUpd);
			//End Add Gulzar 18/01/07
			System.out.println("updateCnt  :"+updateCnt);
			if (updateCnt <= 0)
			{
				retVal = 0;
			}
			else
			{
				retVal = 1;
			}
		}
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in DistStkUpdEJB :"+sqx);
			sqx.printStackTrace();
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("The SQLException occurs in DistStkUpdEJB :"+e);			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Statements.....");
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if (stmt1 != null)
				{
					stmt1.close();
					stmt1 = null;
				}
			}catch(Exception se){}
		}
		System.out.println("retVal from DistStkUpdEJB :"+retVal);
		return retVal;
	}



}//class

