/**
 * PURPOSE : Confirmation of Stock transfer transaction
 * AUTHOR : Gulzar on 13/09/11
 */ 

package ibase.webitm.ejb.dis.adv;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.ejb.fin.InvAcct;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import javax.ejb.Stateless;

@Stateless 
public class StockTransferConf extends ActionHandlerEJB implements StockTransferConfRemote, StockTransferConfLocal
{
	/**
	 * The public function is used to confirm a transaction corresponding to the tran id it takes as an argument
	 * @param : tranId
	 * @param : xtraParams
	 */
	//changes by chitranjan for overriding of confirm method 29/11/11
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{

		String retString = "";
		Connection conn = null;
		boolean isConn= false;
		try
		{
			retString = this.confirm(tranID, xtraParams, forcedFlag, conn, isConn);
			System.out.println("retString:::::"+retString);
			//if ( retString != null && retString.length() > 0 )
			//{
			//	throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			//}
		}
		catch(Exception e)
		{
			System.out.println("Exception in [StockTransferConf] confirm " + e.getMessage());
			throw new ITMException(e);
		}
		return retString;
	}
	//changes by chitranjan in confirm method parameter add connection ,connStatus  
	public String confirm( String tranId, String xtraParams, String forcedFlag, Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{		
		String replOrder = "";
		String confirmed = "";
		String retString = "";
		String itemCode = "";
		String siteCode = "";
		String locCodeFr = "";
		String locCodeTo = "";
		String lotNo = "";
		String lotSl = "";
		String lotSlTo = "";
		String acctCodeCr = "";		
		String cctrCodeCr = "";
		//Chnaged by Rohan on 18-07-13 for define variables
		String lockCode = "";
		String lotNoTo = "";

		String sql = "";
		String updateSql = "";

		double quantity = 0.0;
		double noArt = 0.0;

		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		String packCode = "";
		String siteCodeMfg = "";
		String packInstr = "";
		String suppCodeMfg = "";
		String unitAlt = "";
		String batchNo = "";
		String unit = "";
		String grade = "";
		String remarks = "";
		String dimension = "";
		String acctCodeDr = "";
		String ediOption = "";
		String dataStr = "";
		String userId = "";
		String chgTerm = "";

		double grossRate = 0d;
		double convQtyStduom = 0d;
		double batchSize = 0d;
		double stkGrossRate = 0d;
		double stkRate = 0d;
		//Changed by Rohan on 19-08-13 for getting atual rate
		double actualRate = 0d;
		// 16/10/11 manoharan
		double stkQuantity = 0,holdQuantity = 0;
		//Changed by Rohan on 06-08-13 for define variable
		double holdQty = 0;
		PreparedStatement pstmtUpd = null, pstmtHold = null, pstmtRel = null,pstmtQC = null,pstmtStTr=null,pstmt1=null;
		ResultSet rsHold = null, rsRel = null, rsQC = null,rsStTr=null,rs1=null;
		String tranIdHold = "", sqlRel  = "", sqlHold = "",nearExpLoc = "", sqlQC = "",sql1="";
		String partialUsed = "" ,locCodeInvHold = "" ,lotSlInvHold = "" ,reasonCodeInvHold = "";
		int lineNoHold = 0, qcCount = 0;
		// end 16/10/11 manoharan
		String partialGrlLoc = ""; //added by Ashish Sonawane on 05/SEP/12 for partial GRL location Inventory Status. 
		String invStatLocCodeTo =""; //added by Ashish Sonawane on 05/SEP/12 for partial GRL location Inventory Status.
		PreparedStatement pstmtInvStat =null;
		ResultSet rsInvStat =null;


		int lineNo = 0, updCnt = 0;

		boolean isError = false;

		ArrayList errList = null;
		HashMap updateRowMap = null;
		HashMap strAllocate = null;
		HashMap stockMap = null;//added by kunal on 25/OCT/13

		StockUpdate stkUpdate = null;
		InvAcct invAct=null;
		//Connection conn = null;		
		PreparedStatement pstmt = null;
		PreparedStatement pstmtStock = null;
		ResultSet rs = null;
		ResultSet rsStock = null;

		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();
		DistCommon dComm = new DistCommon();
		double balQty = 0;
		boolean isConn=false;//added by somanath on 03.08.13
		
		String sqlStTr="";
		String refSerFor="",refIdFor="",reasCode="",invStat="",lotSlOrg="";  //,partialUsed="";
		double potencyPerc=0,grossWeight=0,tareWeight=0,netWeight=0; //,actualRate=0;
		FinCommon finCommon = new FinCommon();
		DistCommon discommon = new DistCommon();
		String acctCode="",cctrCode="",invacct="",trfacct="",itemSer="",cctrCodeDr="";  //lotNoTo="",;
		
		
		try
		{
			if ( conn == null )
			{
				isConn=false;//added by somanath on 03.08.13
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				conn.setAutoCommit(false);
				connStatus = true;
			}

			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();

			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			java.util.Date currDate1 = new java.util.Date();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);
			String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );

			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;

			updateRowMap = new HashMap();
			stockMap = new HashMap();
			stkUpdate = new StockUpdate();

			sql = "SELECT CONFIRMED, SITE_CODE FROM STOCK_TRANSFER WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				confirmed = rs.getString( "CONFIRMED" );
				siteCode = rs.getString( "SITE_CODE" );
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			if( confirmed.equalsIgnoreCase("Y") )
			{
				System.out.println("The transaction already confirmed");
				retString = itmDBAccess.getErrorString("","VTCONF8","");//added by somanath on 25.07.2013
				return retString;
			}
			else
			{
				// 24/06/12 manoharan if transfer to near expiry not to transfer the hold quantity
				nearExpLoc = dComm.getDisparams("999999","NEAREXP_LOC",conn);
				partialGrlLoc = dComm.getDisparams("999999","PGRL_INVSTAT",conn);

				sql = "SELECT LINE_NO, ITEM_CODE, QUANTITY, LOC_CODE__FR, LOC_CODE__TO, LOT_NO__FR, LOT_SL__FR, LOT_SL__TO, ACCT_CODE__CR, CCTR_CODE__CR, NO_ART ,LOT_NO__TO " +
					  ",acct_code__dr,cctr_code__dr  " +             // added by cpatil on 20/11/13 compare with pb code and adding missing field
					  "FROM STOCK_TRANSFER_DET WHERE TRAN_ID = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();

				sqlStTr = " SELECT ref_ser__for, ref_id__for, reas_code  "     // added by cpatil on 20/11/13 compare with pb code and adding missing field 
					     +" FROM STOCK_TRANSFER WHERE TRAN_ID = ? ";
					pstmtStTr = conn.prepareStatement(sqlStTr);
					pstmtStTr.setString(1, tranId);
					rsStTr = pstmtStTr.executeQuery();
					if(rsStTr.next())
					{
						refSerFor = rsStTr.getString("ref_ser__for");   
						refIdFor = rsStTr.getString("ref_id__for");
						reasCode = rsStTr.getString("reas_code");
					}
					rsStTr.close();rsStTr = null;
					pstmtStTr.close();pstmtStTr = null;
					
					
				
				// 16/10/11 manoharan quantity, hold_qty added
				sql = "select acct_code__inv, cctr_code__inv, "
						+ "exp_date, pack_code, mfg_date, site_code__mfg, "
						+ "pack_instr, supp_code__mfg, retest_date, "
						+ "gross_rate, rate, conv__qty_stduom, unit__alt, batch_no, batch_size, "
						//Changed by Rohan on 19-08-13 for getting atual rate
						//+ "unit, grade, remarks, dimension, quantity ,hold_qty "
						+ "unit, grade, remarks, dimension, quantity ,hold_qty,actual_rate ,partial_used  "
						+ ",potency_perc, inv_stat, gross_weight, tare_weight, net_weight, lot_sl__org 	  "   // added by cpatil on 20/11/13 compare with pb code and adding missing field
                        + "from stock "
						+ "where item_code = ? "
						+ "and site_code = ? "
						+ "and loc_code = ? "
						+ "and lot_no = ? "
						+ "and lot_sl = ? ";
				// end 16/10/11 manoharan hold_qty added
				pstmtStock = conn.prepareStatement(sql);

				while(rs.next())
				{
					lineNo = rs.getInt( "LINE_NO" );
					itemCode = rs.getString( "ITEM_CODE" );
					quantity = rs.getDouble( "QUANTITY" );
					locCodeFr = rs.getString( "LOC_CODE__FR" );
					locCodeTo = rs.getString( "LOC_CODE__TO" );
					lotNo = rs.getString( "LOT_NO__FR" );
					lotSl = rs.getString( "LOT_SL__FR" );
					lotSlTo = rs.getString( "LOT_SL__TO" );
					lotNoTo = rs.getString( "LOT_NO__TO" );//added by kunal on 14/oct/13
					noArt = rs.getDouble( "NO_ART" );
					acctCodeCr = rs.getString( "ACCT_CODE__CR" );
					cctrCodeCr = rs.getString( "CCTR_CODE__CR" );

					acctCodeDr = rs.getString( "ACCT_CODE__DR" );         //  cpatil
					cctrCodeDr = rs.getString( "CCTR_CODE__DR" );         //  cpatil
					
					// 16/10/11 manoharan initialise quantity, hold_qty
					stkQuantity = 0;
					holdQuantity = 0;
					// end 16/10/11 manoharan hold_qty added
					/*Added by Ashish Sonawane on 05-Sep-12 [START]*/
					///Added by jasmina DI89SUN004-08/07/08- Shifted from gbf_update_stock
					//for updating loc_code & lot_sl as null if qc order is found during stock transfer
					// 24/10/13 manoharan merged from PB, eas missing in java code
					if (quantity > 0)
					{
						qcCount = 0;

						sqlQC = "select count(1) from qc_order " 
								+ " where  site_code = ? "
								+ " and loc_code  = ? "
								+ " and item_code = ? "
								+ " and lot_no  = ? "
								+ " and lot_sl = ? "
								+ " and status <> 'C' ";

						pstmtQC = conn.prepareStatement( sqlQC );
						pstmtQC.setString( 1, siteCode );
						pstmtQC.setString( 2, locCodeFr );
						pstmtQC.setString( 3, itemCode );
						pstmtQC.setString( 4, lotNo );
						pstmtQC.setString( 5, lotSl );
						rsQC = pstmtQC.executeQuery();
						if( rsQC.next() )
						{
							qcCount = rsQC.getInt(1);
						}
						rsQC.close();
						rsQC = null;
						pstmtQC.close();
						pstmtQC = null;

						if (qcCount > 0 )
						{
							//below sql tran_id__xfrx added by jasmina 29/07/08-MF89SUN046
							sqlQC = "update qc_order set loc_code = NULL, lot_sl = NULL, tran_id__xfrx = ? " // :as_tran_id
									+ " where  site_code = ? "
									+ " and loc_code  = ? "
									+ " and item_code = ? "
									+ " and lot_no  = ? "
									+ " and lot_sl = ? "
									+ " and status <> 'C' ";

							pstmtQC = conn.prepareStatement( sqlQC );
							pstmtQC.setString( 1, tranId );
							pstmtQC.setString( 2, siteCode );
							pstmtQC.setString( 3, locCodeFr );
							pstmtQC.setString( 4, itemCode );
							pstmtQC.setString( 5, lotNo );
							pstmtQC.setString( 6, lotSl );
							pstmtQC.executeUpdate();
						}
						else
						{
							//If the above count has returned 0 then check for the lot where lot_sl is null
							sqlQC = "select count(1) from qc_order " 
									+ " where  site_code = ? "
									+ " and loc_code  = ? "
									+ " and item_code = ? "
									+ " and lot_no  = ? "
									+ " and lot_sl is null "
									+ " and status <> 'C' ";

							pstmtQC = conn.prepareStatement( sqlQC );
							pstmtQC.setString( 1, siteCode );
							pstmtQC.setString( 2, locCodeFr );
							pstmtQC.setString( 3, itemCode );
							pstmtQC.setString( 4, lotNo );
							rsQC = pstmtQC.executeQuery();
							if( rsQC.next() )
							{
								qcCount = rsQC.getInt(1);
							}
							rsQC.close();
							rsQC = null;
							pstmtQC.close();
							pstmtQC = null;
							if (qcCount > 0 )
							{
								//below sql tran_id__xfrx added by jasmina 29/07/08-MF89SUN046
								sqlQC = "update qc_order set loc_code = NULL, lot_sl = NULL, tran_id__xfrx = ? " // :as_tran_id
										+ " where  site_code = ? "
										+ " and loc_code  = ? "
										+ " and item_code = ? "
										+ " and lot_no  = ? "
										+ " and lot_sl is null "
										+ " and status <> 'C' ";

								pstmtQC = conn.prepareStatement( sqlQC );
								pstmtQC.setString( 1, tranId );
								pstmtQC.setString( 2, siteCode );
								pstmtQC.setString( 3, locCodeFr );
								pstmtQC.setString( 4, itemCode );
								pstmtQC.setString( 5, lotNo );
								pstmtQC.executeUpdate();
							}
						}
					}
					///Added end by jasmina DI89SUN004-08/07/08- Shifted from gbf_update_stock


					String sqlInvStat = "SELECT INV_STAT FROM LOCATION WHERE LOC_CODE = ?";
					pstmtInvStat = conn.prepareStatement( sqlInvStat );
					pstmtInvStat.setString( 1, locCodeTo );
					rsInvStat = pstmtInvStat.executeQuery();
					if( rsInvStat.next() )
					{
						invStatLocCodeTo = checkNull(rsInvStat.getString("INV_STAT"));
					}
					rsInvStat.close();rsInvStat = null;
					pstmtInvStat.close();pstmtInvStat = null;

					/*Added by Ashish Sonawane on 05-Sep-12 [END]*/


					pstmtStock.setString(1,itemCode);
					pstmtStock.setString(2,siteCode);
					pstmtStock.setString(3,locCodeFr);
					pstmtStock.setString(4,lotNo);
					pstmtStock.setString(5,lotSl);
					rsStock = pstmtStock.executeQuery();
					if ( rsStock.next() )
					{
						//acctCodeDr = rsStock.getString("acct_code__inv");
						//cctrCodeDr = rsStock.getString("cctr_code__inv");

						expDate = rsStock.getTimestamp("exp_date");
						packCode = rsStock.getString("pack_code");
						mfgDate = rsStock.getTimestamp("mfg_date");
						siteCodeMfg = rsStock.getString("site_code__mfg");
						packInstr = rsStock.getString("pack_instr");
						suppCodeMfg = rsStock.getString("supp_code__mfg");
						restestDate = rsStock.getTimestamp("retest_date");
						stkGrossRate = rsStock.getDouble("gross_rate");
						stkRate = rsStock.getDouble("rate");
						System.out.println("stkGrossRate"+stkGrossRate+"stkRate"+stkRate);
						convQtyStduom = rsStock.getDouble("conv__qty_stduom");
						unitAlt = rsStock.getString("unit__alt");
						batchNo = rsStock.getString("batch_no");
						batchSize = rsStock.getDouble("batch_size");
						unit = rsStock.getString("unit");
						grade = rsStock.getString("grade");
						remarks = rsStock.getString("remarks");
						dimension = rsStock.getString("dimension");
						// 16/10/11 manoharan hold_qty added
						stkQuantity = rsStock.getDouble("quantity");
						holdQuantity = rsStock.getDouble("hold_qty");
						// end 16/10/11 manoharan hold_qty added
						//Changed by Rohan on 19-08-13 for getting atual rate
						actualRate = rsStock.getDouble("actual_rate");
						partialUsed =  checkNull(rsStock.getString("partial_used"));
						
						// added by cpatil on 20/11/13 start
						
						potencyPerc = rsStock.getDouble("potency_perc");    
						grossWeight = rsStock.getDouble("gross_weight");    
						tareWeight = rsStock.getDouble("tare_weight");      
						netWeight = rsStock.getDouble("net_weight");        
						lotSlOrg = rsStock.getString("lot_sl__org");        
						
						// added by cpatil on 20/11/13 end
						
						
						
						
						/* temporary comment code by kunal on 16/NOV/13

						//added by kunal on 25/oct/13

						stockMap.put("item_code", itemCode);
						stockMap.put("site_code", siteCode);
						stockMap.put("loc_code", locCodeFr);
						stockMap.put("lot_no", lotNo);
						stockMap.put("lot_sl", lotSl);

						stockMap.put("quantity", ""+quantity);

						if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()) && invStatLocCodeTo.trim().equalsIgnoreCase(partialGrlLoc.trim()))
						{
							stockMap.put("no_art", quantity);
						}
						else
						{
							stockMap.put("no_art", noArt);
						}

						stockMap.put("tran_ser", "XFRX");
						stockMap.put("acct_code__cr",acctCodeCr);
						stockMap.put("cctr_code__cr",cctrCodeCr);
						stockMap.put("acct_code_inv",acctCodeCr);
						stockMap.put("cctr_code_inv",cctrCodeCr);
						stockMap.put("rate",Double.toString(stkRate));
						stockMap.put("gross_rate",Double.toString(stkGrossRate));
						System.out.println("stkGrossRate:::"+Double.toString(stkRate)+"stkRate::::"+Double.toString(stkGrossRate));
						stockMap.put("tran_id", tranId );
						stockMap.put("tran_date", tranDate );
						//updateRowMap.put("tran_type","ID"); 

						stockMap.put("qty_stduom", ""+quantity);
						stockMap.put("unit",unit);
						stockMap.put("grade",grade);
						stockMap.put("remarks",remarks);
						stockMap.put("dimension",dimension);
						stockMap.put("tran_type", "I");
						//updateRowMap.put("tran_type", "I");

						stockMap.put("exp_date", expDate);
						stockMap.put("pack_code", packCode);
						stockMap.put("mfg_date", mfgDate);
						stockMap.put("site_code__mfg", siteCodeMfg);
						stockMap.put("pack_instr", packInstr);
						stockMap.put("supp_code__mfg", suppCodeMfg);
						stockMap.put("retest_date", restestDate);
						stockMap.put("conv__qty_stduom", ""+convQtyStduom);
						stockMap.put("unit__alt", unitAlt);
						stockMap.put("batch_no", batchNo);
						stockMap.put("batch_size", ""+batchSize);
						stockMap.put("actual_rate", actualRate);

						errList = allocTransfer(siteCode,itemCode,locCodeFr ,lotNo,lotSl,locCodeTo ,lotNoTo,lotSlTo, quantity,stockMap, xtraParams , conn );
						System.out.println("from alloc transfer=="+errList.toString());
						retString = errList.get(0).toString() ;
						System.out.println("FROM allocTransfer 0 : "+retString);
						if ( retString != null && retString.trim().length() > 0 )
						{
							isError = true;
							break;
						}
						stockMap.clear();
						quantity = Double.parseDouble( errList.get(1).toString() );
						System.out.println("pending quantity FROM allocTransfer "+quantity);
						if(quantity <= 0)
						{
							continue;
						}
						//added by kunal on 25/oct/13 end
						 *temporary comment code by kunal on 16/NOV/13 end
						 */

					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();

					updateRowMap.put("item_code", itemCode);
					updateRowMap.put("site_code", siteCode);
					updateRowMap.put("loc_code", locCodeFr);
					updateRowMap.put("lot_no", lotNo);
					updateRowMap.put("lot_sl", lotSl);
					updateRowMap.put("quantity", ""+quantity);
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as 0 for issue [START] */
					//Chnaged by Rohan on 29-07-13 for setting no art is 0 in case of location code to is PGRL.
					//if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()))
					if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()) && invStatLocCodeTo.trim().equalsIgnoreCase(partialGrlLoc.trim()))
					{
						noArt = 0;
						updateRowMap.put("no_art", noArt);
					}
					else
					{
						updateRowMap.put("no_art", noArt);
					}
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as 0 for issue [END] */

					updateRowMap.put("tran_ser", "XFRX");
					updateRowMap.put("acct_code__cr",acctCodeCr);
					updateRowMap.put("cctr_code__cr",cctrCodeCr);
					updateRowMap.put("acct_code_inv",acctCodeCr);
					updateRowMap.put("cctr_code_inv",cctrCodeCr);
					updateRowMap.put("rate",Double.toString(stkRate));
					updateRowMap.put("gross_rate",Double.toString(stkGrossRate));
					System.out.println("stkGrossRate:::"+Double.toString(stkRate)+"stkRate::::"+Double.toString(stkGrossRate));
					updateRowMap.put("tran_id", tranId );
					updateRowMap.put("tran_date", tranDate );
					updateRowMap.put("tran_type","ID"); 

					updateRowMap.put("qty_stduom", ""+quantity);
					updateRowMap.put("unit",unit);
					updateRowMap.put("grade",grade);
					updateRowMap.put("remarks",remarks);
					updateRowMap.put("dimension",dimension);
					//updateRowMap.put("tran_type", "I");

					//changed by gulzar on 12/24/2011
					updateRowMap.put("exp_date", expDate);
					updateRowMap.put("pack_code", packCode);
					updateRowMap.put("mfg_date", mfgDate);
					updateRowMap.put("site_code__mfg", siteCodeMfg);
					updateRowMap.put("pack_instr", packInstr);
					updateRowMap.put("supp_code__mfg", suppCodeMfg);
					updateRowMap.put("retest_date", restestDate);
					updateRowMap.put("conv__qty_stduom", ""+convQtyStduom);
					updateRowMap.put("unit__alt", unitAlt);
					updateRowMap.put("batch_no", batchNo);
					updateRowMap.put("batch_size", ""+batchSize);
					//End changes by gulzar on 12/24/2011

					// added by cpatil on 20/11/13  start 
					itemSer = discommon.getItemSer(itemCode, siteCode, tranDate, "","", conn);
					cctrCode = finCommon.getAcctDetrTtype(itemCode, itemSer, "XFRX", " ", conn);
					acctCode = cctrCode.substring(0,cctrCode.indexOf(","));
					cctrCode = cctrCode.substring(cctrCode.indexOf(",")+1);
					
					if ( cctrCodeDr != null && cctrCodeDr.trim().length() > 0)
					{
						if ( acctCode == null ||acctCode.trim().length() == 0 )
						{	
							invacct = finCommon.getFinparams("999999", "INVENTORY_ACCT", conn);
							trfacct = finCommon.getFinparams("999999", "INV_ACCT_TRF", conn);
							if( "Y".equalsIgnoreCase(invacct) || "Y".equalsIgnoreCase(trfacct ))
							{	
								retString = "VTTRFACT";
								isError = true;
								break;
								//	errCode = "VTTRFACT";   // ~tSTOCK TRANSFER ACCOUNT NOT FOUND"
								//	return errCode;	
							}
						}
					}
					updateRowMap.put("acct_code__dr",acctCode);   
					if( cctrCode == null || cctrCode.trim().length() == 0 )
					{
						updateRowMap.put("cctr_code__dr",cctrCodeDr); 
					}		
					else
					{
						updateRowMap.put("cctr_code__dr",cctrCode);   
					}
					
					updateRowMap.put("ref_ser__for",refSerFor);  
					updateRowMap.put("ref_id__for",refIdFor);    
					updateRowMap.put("reas_code",reasCode);    
					updateRowMap.put("potency_perc",potencyPerc);  
					updateRowMap.put("inv_stat",invStat);    
					if( stkQuantity != 0)
					{
						updateRowMap.put("gross_weight", ( grossWeight / stkQuantity * quantity )); 
						updateRowMap.put("tare_weight", ( tareWeight / stkQuantity * quantity ));   
						updateRowMap.put("net_weight", ( netWeight / stkQuantity * quantity ));    
					}
					updateRowMap.put("lot_sl__org",lotSl);    
					
					// added by cpatil on 20/11/13  end
					
					
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );

					System.out.println("Update stock complter1111");
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					
					// 	added by cpatil on 20/11/13  start
					
					sql1 = " select inv_stat from location where loc_code = ?  ";
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, tranId);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						invStat = rs1.getString("inv_stat");
					}
					rs1.close();rs1 = null;
					pstmt1.close();pstmt1 = null;
					
					// added by cpatil on 20/11/13  end
					
					
					
					updateRowMap.put("loc_code", locCodeTo);
					updateRowMap.put("lot_sl", lotSlTo);
					updateRowMap.put("tran_type", "R");
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as quantity at the time of receipt [START] */
					//Chnaged by Rohan on 29-07-13 for setting no art as quantity  in case of location code to is PGRL.start
					/*
					if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()))
					{
						updateRowMap.put("no_art", quantity);
					}
					 */
					if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()) && invStatLocCodeTo.trim().equalsIgnoreCase(partialGrlLoc.trim()))
					{
						updateRowMap.put("no_art", quantity);
					}
					else
					{
						updateRowMap.put("no_art", noArt);
					}
					//Chnaged by Rohan on 29-07-13 for setting no art as quantity  in case of location code to is PGRL.end
					/* Added by Ashish Sonawane on 07-sep-12 as per Manoharan Sir in case of partial GRL no_art to be set as quantity at the time of receipt [END] */
					//Chnaged by Rohan on 19-08-13 for updating atual rate in transfered stock.
					updateRowMap.put("actual_rate", actualRate);
					//Added by kunal on 19/NOV/13 ADD partial_used TO MAP as per manoharan sir INSTRUCTION
					System.out.println("partialUsed=="+partialUsed);
					updateRowMap.put("partial_used", partialUsed);

					
					// 	added by cpatil on 20/11/13  start
					
					updateRowMap.put("lot_no",lotNoTo);   //  cpatil 
					updateRowMap.put("inv_stat",invStat);
					updateRowMap.put("acct_code__cr",acctCode);  
					if ( cctrCode == null || cctrCode.trim().length() == 0 )
					{
						updateRowMap.put("cctr_code__cr",cctrCodeCr);  		
					}
					else
					{
						updateRowMap.put("cctr_code__cr",cctrCode);   
					}
					updateRowMap.put("acct_code__dr",acctCodeDr);    
					updateRowMap.put("cctr_code__dr",cctrCodeDr);    
					updateRowMap.put("cctr_code_inv",acctCodeDr);    
					updateRowMap.put("cctr_code_inv",cctrCodeDr);    
					updateRowMap.put("ref_ser__for",refSerFor);    
					updateRowMap.put("ref_id__for",refIdFor);    
					
					// added by cpatil on 20/11/13  end
					
					
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					updateRowMap.clear();

					// 16/10/11 manoharan check whether inv hold is there
					// if hold_qty > 0 update the same stock hold_qty for loc_code__to
					// also reduce the hold_qty for old loc_code
					// This utility methods to update old and new location should be in 
					// a separate probably in stockupdate so that we can call the same from various places 
					if (holdQuantity > 0)
					{
						// update old location with - hold_qty
						sqlRel = "UPDATE STOCK SET HOLD_QTY = HOLD_QTY - ?"
								+ " WHERE ITEM_CODE = ?  "
								+ " AND SITE_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? ";
						pstmtUpd = conn.prepareStatement(sqlRel);
						//pstmtUpd.setDouble(1,holdQuantity); //Commented by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway
						pstmtUpd.setDouble(1,quantity); //Added by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway 
						pstmtUpd.setString(2,itemCode);
						pstmtUpd.setString(3,siteCode);
						pstmtUpd.setString(4,locCodeFr);
						pstmtUpd.setString(5,lotNo);
						pstmtUpd.setString(6,lotSl);
						pstmtUpd.executeUpdate();
						// 24/06/12 manoharan if not neare expiry location hold quantity to be transferred
						if (!nearExpLoc.trim().equalsIgnoreCase(locCodeTo.trim()) )
						{
							// update new location with + hold_qty
							sqlRel = "UPDATE STOCK SET HOLD_QTY = CASE WHEN HOLD_QTY IS NULL THEN 0 ELSE HOLD_QTY END  + ? "
									+ " WHERE ITEM_CODE = ?  "
									+ " AND SITE_CODE = ? "
									+ " AND LOC_CODE = ? "
									+ " AND LOT_NO = ? "
									+ " AND LOT_SL = ? ";
							pstmtUpd = conn.prepareStatement(sqlRel);
							//pstmtUpd.setDouble(1,holdQuantity); //Commented by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway
							pstmtUpd.setDouble(1,quantity); //Added by Ashish Sonawane on 01/SEP/12 as implication on stock due to PGRL location for partial qty putaway
							pstmtUpd.setString(2,itemCode);
							pstmtUpd.setString(3,siteCode);
							pstmtUpd.setString(4,locCodeTo);
							pstmtUpd.setString(5,lotNo);
							//pstmtUpd.setString(6,lotSl);//Gulzar on 21/12/11
							pstmtUpd.setString(6,lotSlTo);//Gulzar on 21/12/11
							pstmtUpd.executeUpdate();


							pstmtHold = null;
							rsHold = null;
							tranIdHold = "";
							lineNoHold = 0;


							//Changed by Rohan on 26-07-13 for disinct record
							//sqlHold = "SELECT  D.TRAN_ID AS TRAN_ID, D.LINE_NO AS LINE_NO, "
							sqlHold = "SELECT DISTINCT D.TRAN_ID AS TRAN_ID, D.LINE_NO AS LINE_NO, "
									+ " D.ITEM_CODE AS ITEM_CODE, D.SITE_CODE AS SITE_CODE, "
									+ " D.LOC_CODE AS LOC_CODE, D.LOT_NO AS LOT_NO,  D.LOT_SL AS LOT_SL ,D.REAS_CODE " //ADDED BY KUNAL ON 23/MAY/14 ADD REAS_CODE 
									//Changed By Rohan on 18-07-13 for getting lock_code
									//+ " FROM INV_HOLD_DET D, INV_HOLD H "
									//Changed by Rohan on 06-08-13 to get hold qty
									//+ " ,T.LOCK_CODE FROM INV_HOLD_DET D, INV_HOLD H , INV_HOLD_REL_TRACE T "
									+ " ,T.LOCK_CODE,T.HOLD_QTY FROM INV_HOLD_DET D, INV_HOLD H , INV_HOLD_REL_TRACE T "
									+ " WHERE H.TRAN_ID = D.TRAN_ID "
									//Changed By Rohan on 18-07-13 for getting lock_code
									+ " AND T.REF_NO = D.TRAN_ID "
									//Chnaged by Rohan on 09-08-13 for add join of inv hold det and inv hold rel trace.start
									//change by kunal on 29/may/14 check null value for join
									+" AND (D.ITEM_CODE = T.ITEM_CODE or (d.item_code is null or length(trim(d.item_code)) = 0 ) )"
									+" AND (D.SITE_CODE = T.SITE_CODE or (d.site_code is null or length(trim(d.site_code)) = 0 ) )"  
									+" AND (D.LOC_CODE = T.LOC_CODE  or (d.loc_code is null or length(trim(d.loc_code)) = 0 ) )"
									+" AND (D.LOT_NO = T.LOT_NO  or (d.lot_no is null or length(trim(d.lot_no)) = 0 ) )"
									+" AND (D.LOT_SL = T.LOT_SL or (d.lot_sl is null or length(trim(d.lot_sl)) = 0 ) )" 
									//Chnaged by Rohan on 09-08-13 for add join of inv hold det and inv hold rel trace.end
									//change done by kunal on 30/may/14 add conditiob for INV_HOLD_REL_TRACE table
									+ " AND ((T.ITEM_CODE = ? ) OR (T.ITEM_CODE IS NULL OR LENGTH(TRIM(T.ITEM_CODE)) = 0 ) ) "
									+ " AND ((T.SITE_CODE = ? ) OR (T.SITE_CODE IS NULL OR LENGTH(TRIM(T.SITE_CODE)) = 0 ) ) "
									+ " AND ((T.LOC_CODE = ? ) OR (T.LOC_CODE IS NULL OR LENGTH(TRIM(T.LOC_CODE)) = 0 ) ) "
									+ " AND ((T.LOT_NO = ? ) OR (T.LOT_NO IS NULL OR LENGTH(TRIM(T.LOT_NO)) = 0 ) ) "
									+ " AND ((T.LOT_SL = ? ) OR (T.LOT_SL IS NULL OR LENGTH(TRIM(T.LOT_SL)) = 0 ) ) "
									+ " AND D.HOLD_STATUS = 'H'"
									+ " AND H.CONFIRMED  = 'Y' ";
							pstmtHold = conn.prepareStatement(sqlHold);
							pstmtHold.setString(1,itemCode);
							pstmtHold.setString(2,siteCode);
							pstmtHold.setString(3,locCodeFr);
							pstmtHold.setString(4,lotNo);
							pstmtHold.setString(5,lotSl);
							rsHold = pstmtHold.executeQuery();
							balQty = holdQuantity;
							while ( rsHold.next() )
							{
								/*//check whether  there is a confirmed release hold for the same 

								sqlRel = "select count(1) from inv_hold_rel  h, inv_hold_rel_det "
									+ " where h.tran_id = d.tran_id "
									+ " and d.tran_id__hold  = ? "
									+ " and d.line_no__hold  = ? "
									+ " and h.confirmed = 'Y'";
								pstmtRel =  conn.prepareStatement(sqlRel);
								pstmtRel.SetString(1,rsHold.getString("TRAN_ID"));
								pstmtRel.setInt(2,rsHold.getInt("LINE_NO"));
								rsRel = pstmtRel.executeQuery();
								if (rsRelel.next())
								{
								}
								rsRel.close();
								rsRel = null;
								pstmtRel.close();
								pstmtRel = null;
								 */
								tranIdHold = rsHold.getString("TRAN_ID");// added by Ashish Sonawane on 05-sep-12
								//Changed By Rohan on 18-07-13 for getting lock_code
								lockCode = checkNull(rsHold.getString("LOCK_CODE"));
								//Changed by Rohan on 06-08-13 to get hold qty
								holdQty = rsHold.getDouble("HOLD_QTY");
								
								locCodeInvHold = checkNull(rsHold.getString("LOC_CODE"));
								lotSlInvHold = checkNull(rsHold.getString("LOT_SL"));
								reasonCodeInvHold = checkNull(rsHold.getString("REAS_CODE"));
								System.out.println("inv hold loc code="+locCodeInvHold+"@"+lotSlInvHold+"@"+reasonCodeInvHold);
								System.out.println("invStatLocCodeTo"+invStatLocCodeTo+"locCodeTo"+locCodeTo);
								System.out.println("Current Lock Code"+lockCode+"quantity::"+quantity+"holdQty::"+holdQty);
								//Changed by Rohan on 06-08-13 if partial qty move from pso to pso lacation.start
								//if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()))
								if(invStatLocCodeTo.trim().equalsIgnoreCase(locCodeTo.trim()) || quantity < holdQty )
								{
									System.out.println("-----Insert Block-----");
									//Changed by Rohan on 17-07-13 for updating INV_HOLD_REL_TRACE
									//insertIntoInvHold(itemCode,siteCode,locCodeTo,lotNo,lotSlTo,tranIdHold,conn);
									insertIntoInvHold(itemCode,siteCode,locCodeTo,lotNo,lotSlTo,tranIdHold,locCodeFr,lotSl,quantity,lockCode,reasonCodeInvHold,conn);
									System.out.println("@@@@ End of insertion...");
								}
								else
								{
									System.out.println("-----Update Block-----");
									// changed by sankara on 06-07-13 fixing bug updated corret lot_sl in inv_hold det.start	
									/*sqlRel = "UPDATE INV_HOLD_DET SET LOC_CODE = ?, STATUS_DATE = ? "
												+ " WHERE TRAN_ID = ?  AND LINE_NO = ?";
											pstmtUpd = conn.prepareStatement(sqlRel);
											pstmtUpd.setString(1,locCodeTo);
											pstmtUpd.setTimestamp(2,new java.sql.Timestamp( System.currentTimeMillis() ));
											pstmtUpd.setString(3,rsHold.getString("TRAN_ID"));
											pstmtUpd.setInt(4,rsHold.getInt("LINE_NO"));
											pstmtUpd.executeUpdate();    */
									sqlRel = "UPDATE INV_HOLD_DET SET LOC_CODE = ?, STATUS_DATE = ?, LOT_SL = ? "
											+ " WHERE TRAN_ID = ?  AND LINE_NO = ?";
									pstmtUpd = conn.prepareStatement(sqlRel);
									pstmtUpd.setString(1,locCodeTo);
									pstmtUpd.setTimestamp(2,new java.sql.Timestamp( System.currentTimeMillis() ));
									pstmtUpd.setString(3,lotSlTo);
									pstmtUpd.setString(4,rsHold.getString("TRAN_ID"));
									pstmtUpd.setInt(5,rsHold.getInt("LINE_NO"));
									pstmtUpd.executeUpdate();    

									// 23/06/12 manoharan
									// changed by sankara on 06-07-13 fixing bug updated corret lot_sl in inv_hold det.end

									// 23/06/12 manoharan
									//tranIdHold = rsHold.getString("TRAN_ID"); //commented by Ashish Sonawane on 05-Sep-12 and moved to line no 421 
									// changed by sankara on 06-07-13 fixing bug updated corret lot_sl in inv_hold_trace.start				
									/*sqlRel = "UPDATE INV_HOLD_REL_TRACE SET LOC_CODE = ? "
													+ " WHERE REF_NO = ? "
													+ " AND ITEM_CODE = ? "
													+ " AND SITE_CODE = ? "
													+ " AND LOC_CODE = ? "
													+ " AND LOT_NO = ? " 
													+ " AND LOT_SL = ? " ;
												pstmtUpd = conn.prepareStatement(sqlRel);
												pstmtUpd.setString(1,locCodeTo);
												pstmtUpd.setString(2,tranIdHold);
												pstmtUpd.setString(3,itemCode);
												pstmtUpd.setString(4,siteCode);
												pstmtUpd.setString(5,locCodeFr);
												pstmtUpd.setString(6,lotNo);
												pstmtUpd.setString(7,lotSl);
												updCnt = pstmtUpd.executeUpdate();
												System.out.println( "[" + updCnt + "] rows updated successfully in INV_HOLD_REL_TRACE" );
												// end 23/06/12 manoharan     */
									sqlRel = "UPDATE INV_HOLD_REL_TRACE SET LOC_CODE = ?, LOT_SL = ?"
											+ " WHERE REF_NO = ? "
											+ " AND ITEM_CODE = ? "
											+ " AND SITE_CODE = ? "
											+ " AND LOC_CODE = ? "
											+ " AND LOT_NO = ? " 
											+ " AND LOT_SL = ? " ;
									pstmtUpd = conn.prepareStatement(sqlRel);
									pstmtUpd.setString(1,locCodeTo);
									pstmtUpd.setString(2,lotSlTo);
									pstmtUpd.setString(3,tranIdHold);
									pstmtUpd.setString(4,itemCode);
									pstmtUpd.setString(5,siteCode);
									pstmtUpd.setString(6,locCodeFr);
									pstmtUpd.setString(7,lotNo);
									pstmtUpd.setString(8,lotSl);
									updCnt = pstmtUpd.executeUpdate();
									System.out.println( "[" + updCnt + "] rows updated successfully in INV_HOLD_REL_TRACE" );
									// end 23/06/12 manoharan     */
									// changed by sankara on 06-07-13 fixing bug updated corret lot_sl in inv_hold_trace det.end	
									// end 23/06/12 manoharan
								}
							}
							rsHold.close();
							rsHold = null;
							pstmtHold.close();
							pstmtHold = null;

						} // end 24/06/12 manoharan not near expiry
					}
					// end 16/10/11 manoharan
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				pstmtStock.close();pstmtStock = null;

				if ( retString == null || retString.trim().length() == 0 )
				{
					updateSql = "UPDATE STOCK_TRANSFER SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__APRV = ? " +
							"WHERE TRAN_ID = ? ";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setTimestamp(1, currDate);
					pstmt.setString(2, empCode);
					pstmt.setString(3, tranId);				

					updCnt = pstmt.executeUpdate();
					pstmt.close();pstmt = null;

					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " rows updated successfully" );
						invAct=new InvAcct();
						System.out.println( updCnt + " rows updated successfully" );
						retString=invAct.retreiveStockTrans(tranId, conn);
						System.out.println("retString@@@@@@@@"+retString);
						System.out.println("retrieve stock transfer called---");
					}if(retString==null || (retString.trim()).length()==0){
						isError = false;
					}else{
						isError=true;
					}
					////////////////////// EDI creation
					/*
					sql = "SELECT EDI_OPTION FROM TRANSETUP WHERE TRAN_WINDOW = 'w_stock_transfer_wiz' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if ( rs.next() )
					{
						ediOption = checkNull(rs.getString("EDI_OPTION"));
					}
					rs.close();rs = null;
					pstmt.close();pstmt = null;
					if ( "1".equals(ediOption.trim()) )
					{
						CreateRCPXML createRCPXML = new CreateRCPXML("w_stockinvStatLocCodeTo_transfer","tran_id");
						dataStr = createRCPXML.getTranXML( tranId, conn );
						System.out.println( "dataStr =[ "+ dataStr + "]" );
						Document ediDataDom = genericUtility.parseString(dataStr);

						E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
						e12CreateBatchLoad.createBatchLoad( ediDataDom, "w_stock_transfer", "0", xtraParams, conn );
						createRCPXML = null;
						e12CreateBatchLoad = null;
					}
					 */
					/////////////////////
				}
				////////////
			}
		}
		catch( Exception e )
		{
			try
			{
				conn.rollback();
				isError = true;
				e.printStackTrace();
			}
			catch (Exception e1)
			{
			}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//added by chitranjan connStatus if connstatus is true then commit.
				if( !isError && connStatus )
				{
					conn.commit(); 
					retString = itmDBAccess.getErrorString("","CONFSUCC","");
				}
				else if( isError && connStatus)
				{
					conn.rollback();
					//retString=itmDBAccess.getErrorString("","VCOINDIFF1","");
				}
				if ( isConn && isError )
				{
					throw new Exception("Exception while calling confirm for tran  Id:["+tranId+"]");
				}
				if(rs != null )
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if(pstmtStock != null )
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				if( conn != null && connStatus )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
	//Changed by Rohan on 17-07-13 for updating INV_HOLD_REL_TRACE
	//private void insertIntoInvHold(String itemCode ,String siteCode, String locCode ,String lotNo, String lotSl, String tranIdHold ,Connection conn) throws ITMException
	//change done by kunal on 31/may/14 reason code add in argumnet
	private void insertIntoInvHold(String itemCode ,String siteCode, String locCode ,String lotNo, String lotSl, String tranIdHold ,String locCodeFr,String lotSlFr,double holdQuantity,String lockCode,String reasonCode ,Connection conn) throws ITMException
	{
		PreparedStatement pstmtInsertInvHold =null;
		PreparedStatement pstmtMaxLine =null;
		ResultSet rsMaxLine =null ,rs = null;
		//Changed by Rohan on 17-07-13 for updating INV_HOLD_REL_TRACE prepared statement and result set.start
		PreparedStatement pstmt = null;
		String sql = "";
		String tranId = "";
		//Changed by Rohan on 17-07-13 for updating INV_HOLD_REL_TRACE prepared statement and result set.end
		int maxLineNo=0,count = 0,count1 = 0;


		try {
			String sqlMaxLine ="SELECT MAX(LINE_NO) FROM INV_HOLD_DET WHERE TRAN_ID =?";
			pstmtMaxLine = conn.prepareStatement(sqlMaxLine);
			pstmtMaxLine.setString(1,tranIdHold);
			rsMaxLine =pstmtMaxLine.executeQuery();
			if(rsMaxLine.next())
			{
				maxLineNo =(rsMaxLine.getInt(1))+1;
				System.out.println("line no to be inserted :" +maxLineNo);
			}
			else
			{
				maxLineNo =maxLineNo++;
			}

			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateTimeFormat());
			Date sysDate = new Date();
			String sysDateStr = sdf.format(sysDate);

			sql ="select count(*) from inv_hold_det where tran_id = ? and item_code = ? and site_code = ? "
					+" and loc_code = ? and lot_no = ? and  lot_sl =  ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranIdHold);
			pstmt.setString(2, itemCode);
			pstmt.setString(3, siteCode);
			pstmt.setString(4, locCode);
			pstmt.setString(5, lotNo);
			pstmt.setString(6, lotSl);
			rs =pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt(1);				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;

			if(count > 0)//aaded by kunal on 22/may/14
			{
				sql ="select count(*) from INV_HOLD_REL_TRACE  where   REF_NO = ?   and ITEM_CODE = ?  and SITE_CODE = ?  "
						+" and  LOC_CODE = ? and LOT_NO = ?  and  LOT_SL = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranIdHold);
				pstmt.setString(2, itemCode);
				pstmt.setString(3, siteCode);
				pstmt.setString(4, locCode);
				pstmt.setString(5, lotNo);
				pstmt.setString(6, lotSl);
				rs =pstmt.executeQuery();
				if(rs.next())
				{
					count1 = rs.getInt(1);				
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null ;
				System.out.println("count1="+count1);
				if(count1 > 0)
				{
					sql = " update  inv_hold_rel_trace set hold_qty = hold_qty + ? where   ref_no = ?   and item_code = ? "
							+"  and site_code = ? and  loc_code = ? and lot_no = ?  and  lot_sl = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble( 1,holdQuantity );
					pstmt.setString( 2, tranIdHold); 
					pstmt.setString( 3, itemCode ); 
					pstmt.setString( 4, siteCode );
					pstmt.setString( 5, locCode );
					pstmt.setString( 6, lotNo);
					pstmt.setString( 7, lotSl );
					System.out.println("record updated::"+pstmt.executeUpdate());

					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
				}
			}
			else
			{

				String sqlInsertInvHold = "INSERT INTO INV_HOLD_DET (TRAN_ID, LINE_NO, ITEM_CODE, SITE_CODE, LOC_CODE, LOT_NO, LOT_SL, HOLD_STATUS, STATUS_DATE , SCH_REL_DATE , REAS_CODE ) " +
						" VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

				pstmtInsertInvHold = conn.prepareStatement(sqlInsertInvHold);

				pstmtInsertInvHold.setString(1, tranIdHold);
				pstmtInsertInvHold.setInt(2, maxLineNo);
				pstmtInsertInvHold.setString(3, itemCode);
				pstmtInsertInvHold.setString(4, siteCode);
				pstmtInsertInvHold.setString(5, locCode);
				pstmtInsertInvHold.setString(6, lotNo);
				pstmtInsertInvHold.setString(7, lotSl);
				pstmtInsertInvHold.setString(8, "H");
				pstmtInsertInvHold.setTimestamp(9, Timestamp.valueOf( sysDateStr));
				pstmtInsertInvHold.setTimestamp(10, Timestamp.valueOf( sysDateStr));
				pstmtInsertInvHold.setString(11, reasonCode);
				int isInserted = pstmtInsertInvHold.executeUpdate();

				System.out.println("@@@@ Hey Row inserted !!!!!!!! .....Total :["+isInserted+"]");
				//Changed by Rohan on 17-07-13 for updating INV_HOLD_REL_TRACE.start

				tranId = generateTraceId("w_inv_hold_rel_trace", conn);
				sql = " INSERT INTO INV_HOLD_REL_TRACE ( TRAN_ID, ITEM_CODE, SITE_CODE, LOC_CODE, LOT_NO, LOT_SL, REF_NO, HOLD_QTY, LOCK_CODE ) " +
						" VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) " ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, tranId); 
				pstmt.setString( 2, itemCode ); 
				pstmt.setString( 3, siteCode );
				pstmt.setString( 4, locCode );
				pstmt.setString( 5, lotNo);
				pstmt.setString( 6, lotSl );
				pstmt.setString( 7, tranIdHold);
				pstmt.setDouble( 8,holdQuantity );
				pstmt.setString( 9, lockCode ); 
				pstmt.executeUpdate();

				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
				//sql = " UPDATE INV_HOLD_REL_TRACE SET HOLD_QTY = HOLD_QTY - ? WHERE  ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
				sql = " UPDATE INV_HOLD_REL_TRACE SET HOLD_QTY = HOLD_QTY - ? WHERE  ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?  AND LOCK_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble( 1, holdQuantity); 
				pstmt.setString( 2, itemCode ); 
				pstmt.setString( 3, siteCode );
				pstmt.setString( 4, locCodeFr );
				pstmt.setString( 5, lotNo);
				pstmt.setString( 6, lotSlFr );
				pstmt.setString( 7, lockCode ); 
				pstmt.executeUpdate();
				//Changed by Rohan on 17-07-13 for updating INV_HOLD_REL_TRACE.end

			

		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		//Changed By Rohan on 18-07-13 for closing prepared statement and result set.start
		finally
		{
			try
			{
				if (rsMaxLine != null)
				{
					rsMaxLine.close();
					rsMaxLine = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if (pstmtInsertInvHold != null)
				{
					pstmtInsertInvHold.close();
					pstmtInsertInvHold = null;
				}
				if (pstmtMaxLine != null)
				{
					pstmtMaxLine.close();
					pstmtMaxLine = null;
				}
			}

			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		//Changed By Rohan on 18-07-13 for closing prepared statement and result set.start

	}
	//Changed by Rohan on 17-07-13 for generating tran id for INV_HOLD_REL_TRACE.end
	public String generateTraceId(String windowName,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";	

		java.sql.Date effDate = null;

		try
		{
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}

			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}

			catch(Exception e)
			{
			}
		}
		return tranId;
	}//generateTranTd()
	//Changed by Rohan on 17-07-13 for generating tran id for INV_HOLD_REL_TRACE.end

	//addede by kunal on 25/oct/13
	private ArrayList allocTransfer(String siteCode,String itemCode,String locCodeFr ,String lotNoFr,String lotSlFr,String locCodeTo ,String lotNoTo,String lotSlTo,double quantitydet,HashMap stockMap,String xtraParams ,Connection conn ) throws ITMException
	{
		String errString = "";
		String sql = "";
		String refSer = "", refId = "" ,tranId = "",workorder = "";
		//double pendingQty = 0 ;
		int refLine = 0,count = 0,k = 0 ,lineNo = 0 , maxLine = 0;
		double quantity = 0 , refQty = 0 ,stockAllocQty = 0 ,invAllocQty = 0,stockQty = 0 ,pendingQty = 0 ;
		double splitQty = 0, rate = 0;
		boolean checkWOIssue = false;
		PreparedStatement pstmt = null ,pstmt1 = null,pstmt2 = null;
		ResultSet rs = null,rs1 = null,rs2 = null;
		InvAllocTraceBean invAllocTrace = null;
		StockUpdate stkUpdate = null;
		ArrayList retValue = new ArrayList();
		HashMap hashMap = null;
		HashMap tempMap = null ;
		try
		{
			System.out.println("allocTransfer called........");
			invAllocTrace = new InvAllocTraceBean();
			stkUpdate = new StockUpdate();
			pendingQty = quantitydet ;
			tempMap = stockMap;

			sql = "select  alloc_qty from  stock where site_code = ? and loc_code = ? and item_code = ? "
					+"  and lot_no = ? and lot_sl = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, locCodeFr);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, lotNoFr);
			pstmt.setString(5, lotSlFr);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				stockAllocQty = rs.getDouble("alloc_qty");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = " select sum(a.qty) from "
					+" (select ref_ser,ref_id ,trim(ref_line), sum(alloc_qty) as qty  from invalloc_trace  where site_code = ? and loc_code = ? and item_code = ? "
					+" and lot_no = ? and lot_sl = ?  group by ref_ser, ref_id, trim(ref_line)  having sum(alloc_qty) > 0 order by  sum(alloc_qty)  ) a  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, locCodeFr);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, lotNoFr);
			pstmt.setString(5, lotSlFr);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				invAllocQty = rs.getDouble(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			System.out.println("invAllocQty="+invAllocQty+"    stockAllocQty="+stockAllocQty);
			if(invAllocQty < stockAllocQty) //check stock allocate qty and invalloc_trace qty
			{
				checkWOIssue = true;
			}


			sql = "select ref_ser,ref_id ,trim(ref_line) as ref_line, sum(alloc_qty)  from invalloc_trace  where "
					+" site_code = ? and loc_code = ? and item_code = ? and lot_no = ? and lot_sl = ? "
					+"  group by ref_ser, ref_id ,trim( ref_line)  having sum(alloc_qty) > 0 order by  sum(alloc_qty)  desc  ";
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, locCodeFr);
			pstmt.setString(3, itemCode);
			pstmt.setString(4, lotNoFr);
			pstmt.setString(5, lotSlFr);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				refSer = checkNull(rs.getString( "ref_ser" )) ;
				refId = checkNull(rs.getString( "ref_id" ));
				//refLine = rs.getInt("ref_line");
				refLine = Integer.parseInt( checkNull(rs.getString("ref_line")).trim().length() == 0 ?"0":rs.getString("ref_line").trim()) ;
				refQty = rs.getDouble(4);
				System.out.println("pendingQty="+pendingQty);
				System.out.println("refSer="+refSer);
				System.out.println("refId="+refId);
				System.out.println("refLine="+refLine);
				System.out.println("refQty="+refQty);
				if(pendingQty <= 0)
				{
					break;
				}
				if(pendingQty <= refQty)
				{
					quantity = pendingQty;
					pendingQty = 0;
				}
				else
				{
					quantity = refQty;
					pendingQty = pendingQty - refQty ;
				}
				stockQty = stockQty + quantity;


				hashMap = new HashMap();
				hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
				hashMap.put("ref_ser",refSer);
				hashMap.put("ref_id",refId);
				hashMap.put("ref_line",String.valueOf(refLine));					
				hashMap.put("site_code",siteCode);
				hashMap.put("item_code",itemCode);
				hashMap.put("loc_code",locCodeFr);
				hashMap.put("lot_no",lotNoFr);
				hashMap.put("lot_sl",lotSlFr);
				hashMap.put("alloc_qty",new Double(-1*quantity)); 
				hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
				hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
				errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
				System.out.println("updateInvallocTrace  1....."+errString);
				if ( errString != null && errString.trim().length() > 0 )
				{
					retValue.add("Error");
					retValue.add(new Double(quantitydet));
					return retValue;
				}
			}//end rs loop
			System.out.println("Stock Qty="+stockQty+"  pendingQty="+pendingQty);
			if (stockQty > 0 && ( errString == null || errString.trim().length() == 0)) 
			{
				stockMap.put("quantity", ""+stockQty);
				stockMap.put("qty_stduom", ""+stockQty);
				//stock update for tran type = "I"
				errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); //updateStock called only once's
				System.out.println("stock update FOR I ....."+errString);
				if ( errString != null && errString.trim().length() > 0 )
				{
					retValue.add("Error");
					retValue.add(new Double(quantitydet));
					return retValue;
				}
				else
				{
					stockMap = tempMap;
					stockMap.put("loc_code", locCodeTo);
					stockMap.put("lot_no", lotNoTo);
					stockMap.put("lot_sl", lotSlTo);
					stockMap.put("tran_type", "R");
					//stock update for tran type = "R"
					errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); 
					System.out.println("stock update FOR R ....."+errString);
					if ( errString != null && errString.trim().length() > 0 )
					{
						retValue.add("Error");
						retValue.add(new Double(quantitydet));
						return retValue;
					}
				}

				if ( errString == null || errString.trim().length() == 0)
				{
					if(rs != null)
					{
						rs.first();
						System.out.println("iterate rs for deallcation ");
						pendingQty = quantitydet;
						do  //iterate rs for deallcation 
						{
							splitQty = 0;
							refSer = checkNull(rs.getString( "ref_ser" )) ;
							refId = checkNull(rs.getString( "ref_id" ));
							//refLine = rs.getInt("ref_line");
							refLine = Integer.parseInt( checkNull(rs.getString("ref_line")).trim().length() == 0 ?"0":rs.getString("ref_line").trim()) ;
							refQty = rs.getDouble(4);
							System.out.println("quantitydet="+pendingQty);
							System.out.println("refSer="+refSer);
							System.out.println("refId="+refId);
							System.out.println("refLine="+refLine);
							System.out.println("refQty="+refQty);
							if(pendingQty <= 0)
							{
								break;
							}
							if(pendingQty <= refQty)
							{
								quantity = pendingQty;
								pendingQty = 0;
								splitQty =  refQty - quantity;
							}
							else
							{
								quantity = refQty;
								pendingQty = pendingQty - refQty ;
							}
							hashMap = new HashMap();
							hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
							hashMap.put("ref_ser",refSer);
							hashMap.put("ref_id",refId);
							hashMap.put("ref_line",String.valueOf(refLine));					
							hashMap.put("site_code",siteCode);
							hashMap.put("item_code",itemCode);
							hashMap.put("loc_code",locCodeTo);
							hashMap.put("lot_no",lotNoTo);
							hashMap.put("lot_sl",lotSlTo);
							hashMap.put("alloc_qty",new Double(quantity)); 
							hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
							hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
							errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
							System.out.println("updateInvallocTrace 2....."+errString);
							if ( errString != null && errString.trim().length() > 0 )
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
							else  
							{
								System.out.println("DeAlloc updateInvallocTrace(HashMap, Connection) : Sucessuful!");
								System.out.println("refSer:::::"+refSer);
								if("S-ORD".equalsIgnoreCase(refSer.trim())) //sale order
								{
									System.out.println("update for sale order");
									maxLine = 0;

									System.out.println("update...  "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = "update sord_alloc_det set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ? where sale_order = ? and line_no__sord = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setString(5,refId);
										pstmt1.setInt(6,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sord_alloc_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max(line_no) from sord_alloc_det where sale_order = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into sord_alloc_det (tran_id,line_no,sale_order,line_no__sord,item_code,loc_code,lot_no,lot_sl,quantity,"
											     +" dealloc_qty,site_code,exp_lev,pending_qty,wave_flag) "
												 +" select tran_id,"+maxLine+",sale_order,line_no__sord,item_code,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',"+quantity+",dealloc_qty,"
											     +" site_code,exp_lev,pending_qty,wave_flag from sord_alloc_det where sale_order = ? and line_no__sord = ? ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted sord_alloc_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = "update sord_alloc_det set  loc_code = ? ,lot_no = ? ,lot_sl = ? where sale_order = ? and line_no__sord = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sord_alloc_det ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								
								}
								else if("D-ISS".equalsIgnoreCase(refSer.trim())) //1.Distribution Issue (DISS)
								{
									
									System.out.println("update for Distribution Issue (DISS)");
									rate = 0;
									maxLine = 0;
									sql = " select rate from distord_issdet where tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("update..  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update distord_issdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? , quantity = ?,amount = ? where tran_id = ?  and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty*rate);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated distord_issdet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max(line_no) from distord_issdet where tran_id = ?  ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into distord_issdet (tran_id,line_no,dist_order,line_no_dist_order,item_code,quantity,unit,tax_class,tax_chap,tax_env,loc_code,lot_no,lot_sl,"
											     +" pack_code,rate,amount,tax_amt,net_amt,site_code__mfg,mfg_date,exp_date,potency_perc,no_art,gross_weight,tare_weight,net_weight,pack_instr,dimension," 
												 +" supp_code__mfg,batch_no,grade,retest_date,rate_clg,rate__clg,discount,disc_amt,remarks,cost_rate,unit__alt,conv__qty__alt,qty_order__alt,pallet_wt,"
											     +" rate__alt,conv__rate_alt,return_qty,return_date,returned,batch_size,carton_no,pallet_no) "
												 +" select tran_id,"+maxLine+",dist_order,line_no_dist_order,item_code,"+quantity+",unit,tax_class,tax_chap,tax_env,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',"
											     +" pack_code,rate,15*2,tax_amt,net_amt,site_code__mfg,mfg_date,exp_date,potency_perc,no_art,gross_weight,tare_weight,net_weight,"
												 +" pack_instr,dimension,supp_code__mfg,batch_no,grade,retest_date,rate_clg,rate__clg,discount,disc_amt,remarks,cost_rate,unit__alt,"
											     +" conv__qty__alt,qty_order__alt,pallet_wt,rate__alt,conv__rate_alt,return_qty,return_date,returned,batch_size,carton_no,pallet_no  from distord_issdet where tran_id = ? and line_no =  ? ";
																					
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted distord_issdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update distord_issdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? where tran_id = ?  and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated distord_issdet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}


								}
								else if("S-DSP".equalsIgnoreCase(refSer.trim())) //Despatch
								{
									System.out.println("update for Despatch");
									maxLine = 0;
									System.out.println("update... "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update despatchdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?,quantity__ord = ?,quantity__stduom = ?  where desp_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setDouble(6,splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated despatchdet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max( line_no)   from despatchdet where desp_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;
											sql = " insert into despatchdet (desp_id,line_no,sord_no,line_no__sord,exp_lev,item_code__ord,item_code,lot_no,lot_sl,quantity__ord,quantity,loc_code,status,conv__qty_stduom,"
											     +" unit__std,unit,quantity__stduom,quantity_real,rate__stduom,invoice_id,quantity_inv,pack_instr,no_art,site_code,pack_qty,gross_weight,tare_weight,nett_weight,mfg_date,exp_date,"
												 +" chg_date,chg_user,chg_term,site_code__mfg,rate__clg,dimension,tax_amt,disc_amt,conf_diff_amt,rate__std,cost_rate,frequency,down_payment,down_payment_int,inst_amount,inst_int_amount,"
											     +" no_of_inst,line_type,conv__rtuom_stduom,pallet_wt,cust_item__ref,tran_id__invpack,retest_date,part_no,disc_schem_billback_amt,disc_schem_offinv_amt,sscc_18,pack_code,pallet_no) "
												 +"  select desp_id,"+maxLine+",sord_no,line_no__sord,exp_lev,item_code__ord,item_code,'"+lotNoTo+"','"+lotSlTo+"',"+quantity+","+quantity+",'"+locCodeTo+"',status,conv__qty_stduom,unit__std,unit,"+quantity+",quantity_real,rate__stduom,"
											     +" invoice_id,quantity_inv,pack_instr,no_art,site_code,pack_qty,gross_weight,tare_weight,nett_weight,mfg_date,exp_date,chg_date,chg_user,chg_term,site_code__mfg,rate__clg,dimension,"
												 +" tax_amt,disc_amt,conf_diff_amt,rate__std,cost_rate,frequency,down_payment,down_payment_int,inst_amount,inst_int_amount,no_of_inst,line_type,conv__rtuom_stduom,pallet_wt,cust_item__ref,"
											     +" tran_id__invpack,retest_date,part_no,disc_schem_billback_amt,disc_schem_offinv_amt,sscc_18,pack_code,pallet_no "
												 +" from despatchdet where desp_id = ? and  line_no = ?  ";
											
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted despatchdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update despatchdet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where desp_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated despatchdet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}

								}
								else if("XFRX".equalsIgnoreCase(refSer.trim())) // Stock Transfer Multiple
								{
									System.out.println("update for Stock Transfer Multiple");
									maxLine = 0;
									System.out.println("update...  "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update stock_transfer_det set   loc_code__to = ? ,lot_no__to = ? , lot_sl__to  = ? ,quantity = ? where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setString(5,refId);
										pstmt1.setInt(6,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated stock_transfer_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max(line_no) from stock_transfer_det where tran_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;
											
											sql = " insert into stock_transfer_det (tran_id,line_no,item_code,quantity,loc_code__fr,loc_code__to,lot_no__fr,lot_no__to,lot_sl__fr,lot_sl__to,"
											     +" remarks,acct_code__cr,acct_code__dr,cctr_code__dr,cctr_code__cr,no_art,loc_code__sys) "
												 +" select tran_id,"+maxLine+",item_code,"+quantity+",'"+locCodeTo+"',loc_code__to,'"+lotNoTo+"',lot_no__to,'"+lotSlTo+"',lot_sl__to, "
											     +" remarks,acct_code__cr,acct_code__dr,cctr_code__dr,cctr_code__cr,no_art,loc_code__sys from stock_transfer_det where  tran_id = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted stock_transfer_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update stock_transfer_det set   loc_code__to = ? ,lot_no__to = ? , lot_sl__to  = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated stock_transfer_det ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								}
								else if("C-ISS".equalsIgnoreCase(refSer.trim())) // Consumption Issue(CISS).
								{
									System.out.println("update for Consumption Issue");
									maxLine = 0;
									rate = 0;
									sql = " select rate  from consume_iss_det where cons_issue = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;
									System.out.println("update... "+rate+"    "+quantity+"       "+splitQty);
									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update consume_iss_det set  loc_code = ?,lot_no = ?,lot_sl = ? ,quantity = ? ,quantity__std = ? ,amount = ? where cons_issue = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setDouble(6,splitQty*rate);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated consume_iss_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max( line_no) from consume_iss_det where cons_issue = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;
											
											sql = " insert into consume_iss_det (cons_issue,line_no,cons_order,line_no__ord,item_code,quantity,unit,rate,amount,tax_class,tax_chap,tax_env,tax_amt,net_amt,"
											     +" acct_code,cctr_code,loc_code,lot_no,lot_sl,quantity__std,unit__std,conv_qty_stduom,qc_reqd,acct_code__inv,cctr_code__inv,no_art,anal_code,carton_no,pallet_no) "
												 +" select cons_issue,"+maxLine+",cons_order,line_no__ord,item_code,"+quantity+",unit,rate,"+rate*quantity+",tax_class,tax_chap,tax_env,tax_amt,net_amt,acct_code,"
											     +" cctr_code,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',"+quantity+",unit__std,conv_qty_stduom,qc_reqd,acct_code__inv,cctr_code__inv,no_art,anal_code,carton_no,pallet_no "
												 +" from consume_iss_det where CONS_ISSUE = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted consume_iss_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update consume_iss_det set  loc_code = ?,lot_no = ?,lot_sl = ? where cons_issue = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated consume_iss_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										
									}
								}
								else if("ADJISS".equalsIgnoreCase(refSer.trim())) // Adjustment Issue(ADJISS). 
								{
									System.out.println("update for Adjustment Issue");
									rate = 0;
									maxLine = 0;
									sql = " select rate from  adj_issrcpdet where  tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("1420  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update adj_issrcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ? , amount = ? where tran_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,rate * splitQty);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated adj_issrcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max(line_no) from  adj_issrcpdet where  tran_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into  adj_issrcpdet (tran_id,line_no,item_code,unit,loc_code,lot_no,lot_sl,quantity,sundry_type,sundry_code,rate,gross_rate,grade,dimension,no_art, "
													+" amount,gross_weight,tare_weight,net_weight,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,potency_perc,pack_code,mfg_date,exp_date, "
													+" site_code__mfg,conv__qty_stduom,unit__alt,conv_qty_stduom,unit_alt,theoretical_wt,retest_date,supp_code__mfg,batch_no) "
													+" select tran_id,"+maxLine+",item_code,unit,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"', "+quantity+", sundry_type,sundry_code,rate,gross_rate,grade,dimension,no_art, "
													+ rate * quantity +",gross_weight,tare_weight,net_weight,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,potency_perc,pack_code,mfg_date,exp_date, "
													+"  site_code__mfg,conv__qty_stduom,unit__alt,conv_qty_stduom,unit_alt,theoretical_wt,retest_date,supp_code__mfg,batch_no from adj_issrcpdet  "
													+" where tran_id = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted adj_issrcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update adj_issrcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ? , amount = ? where tran_id = ? and line_no = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setDouble(4,quantity);
										pstmt1.setDouble(5,rate * quantity);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated adj_issrcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}


								}
								else if("P-RET".equalsIgnoreCase(refSer.trim())) // Purchase Order Return(PRETURN). 
								{
									System.out.println("update for PRETURN");
									rate = 0;
									maxLine = 0;
									sql = " select rate  from porcpdet where tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("update..  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update porcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?, quantity__stduom = ?,net_amt = ? where tran_id = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setDouble(6,rate * splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated porcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;
										
										if(k > 0)
										{
											sql = " select max(line_no)  from porcpdet where tran_id = ?  ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into porcpdet (tran_id,line_no,item_code,purc_order,quantity,unit,rate,discount,tax_amt,net_amt,loc_code,lot_no,lot_sl,line_no__ord,"
											     +" canc_bo,vouch_qty,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,unit__rate,conv__qty_stduom,conv__rtuom_stduom,unit__std,"
												 +" quantity__stduom,rate__stduom,pack_code,no_art,pack_instr,batch_no,mfg_date,expiry_date,gross_weight,tare_weight,net_weight,status,"
											     +" potency_perc,supp_code__mnfr,site_code__mfg,reas_code,remarks,challan_qty,grade,tax_class,tax_chap,tax_env,specific_instr,special_instr,"
												 +" loc_code__excess_short,excess_short_qty,additional_cost,rate__clg,supp_challan_qty,realised_qty,item_code__mfg,spec_ref,std_rate,dept_code,"
											     +" effect_stock,physical_status,benefit_type,licence_no,acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr,form_no,"
												 +" retest_date,duty_paid,batch_size,damage_qty,sample_qty,shelf_life__type,qc_reqd,sh_qty,rejc_qty,assetinstall_qty,part_qty,partial_yn,anal_code,carton_no,pallet_no) "
											     +" select tran_id,"+maxLine+",item_code,purc_order,"+quantity+",unit,rate,discount,tax_amt,"+quantity*rate+",'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',line_no__ord,"
												 +" canc_bo,vouch_qty,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,unit__rate,conv__qty_stduom,conv__rtuom_stduom,unit__std, "
											     + quantity+",rate__stduom,pack_code,no_art,pack_instr,batch_no,mfg_date,expiry_date,gross_weight,tare_weight,net_weight,status,potency_perc,supp_code__mnfr,"
												 +" site_code__mfg,reas_code,remarks,challan_qty,grade,tax_class,tax_chap,tax_env,specific_instr,special_instr,loc_code__excess_short,excess_short_qty,"
											     +" additional_cost,rate__clg,supp_challan_qty,realised_qty,item_code__mfg,spec_ref,std_rate,dept_code,effect_stock,physical_status,benefit_type,licence_no,"
												 +" acct_code__prov_dr,cctr_code__prov_dr,acct_code__prov_cr,cctr_code__prov_cr,form_no,retest_date,duty_paid,batch_size,damage_qty,sample_qty,shelf_life__type,"
											     +" qc_reqd,sh_qty,rejc_qty,assetinstall_qty,part_qty,partial_yn,anal_code,carton_no,pallet_no  from porcpdet where tran_id = ? and line_no = ?  ";
											System.out.println("insert qry="+sql);
											
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted porcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update porcpdet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated porcpdet ="+k);
										pstmt1.close();
										pstmt1 = null;
										
									}
									
								}
								else if("R-BFS".equalsIgnoreCase(refSer.trim())) // Receipt Back Flush  
								{
									System.out.println("update for Receipt Back Flush");
									rate = 0;
									maxLine = 0;
									sql = " select rate from receipt_backflush_det  where tran_id = ?   ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;

									System.out.println("update..  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update receipt_backflush_det set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?,amount = ?,net_amt = ? where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,rate * splitQty);
										pstmt1.setDouble(6,rate * splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated receipt_backflush_det ="+k);
										pstmt1.close();
										pstmt1 = null;
										if(k > 0)
										{
											sql = " select select max(line_no) from receipt_backflush_det  where tran_id = ?  ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into receipt_backflush_det (tran_id,line_no,site_code,loc_code,lot_no,lot_sl,item_code,"
											     +" ref_no,quantity,rate,amount,tax_amt,discount,net_amt,unit,unit__doc,conv_qty_doc,qty_doc,rate_doc,no_art) "
												 +" select tran_id,"+maxLine+",site_code,'"+locCodeTo+"','"+lotNoTo+"','"+lotSlTo+"',item_code,ref_no,"+quantity+",rate,"+quantity*rate+",tax_amt,"
											     +" discount," +quantity*rate+",unit,unit__doc,conv_qty_doc,qty_doc,rate_doc,no_art from receipt_backflush_det where tran_id = ? and line_no = ? ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted receipt_backflush_det ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update receipt_backflush_det set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated receipt_backflush_det ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								}
								else if("S-RET".equalsIgnoreCase(refSer.trim())) // (Sales Return)SRETURN.   
								{
									System.out.println("update for SRETURN.");
									rate = 0;
									maxLine = 0;
									sql = " select rate from receipt_backflush_det where tran_id = ? and line_no = ?  ";
									pstmt2 = conn.prepareStatement(sql);
									pstmt2.setString(1, refId);
									pstmt2.setInt(2, refLine);
									rs2 = pstmt2.executeQuery();
									if(rs2.next())
									{
										rate = rs2.getDouble("rate");
									}
									rs2.close();
									rs2 = null;
									pstmt2.close();
									pstmt2 = null;
									

									System.out.println("update...  "+rate+"    "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update sreturndet set  loc_code = ? ,lot_no = ? ,lot_sl = ? ,quantity = ?,quantity__stduom = ?,net_amt = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,rate * splitQty);
										pstmt1.setDouble(6,rate * splitQty);
										pstmt1.setString(7,refId);
										pstmt1.setInt(8,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sreturndet ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{
											sql = " select max(line_no)   from sreturndet  where  tran_id = ? ";
											pstmt2 = conn.prepareStatement(sql);
											pstmt2.setString(1, refId);
											rs2 = pstmt2.executeQuery();
											if(rs2.next())
											{
												maxLine = rs2.getInt(1);
											}
											rs2.close();
											rs2 = null;
											pstmt2.close();
											pstmt2 = null;

											maxLine++;

											sql = " insert into sreturndet (tran_id,line_no,invoice_id,line_no__inv,item_code,quantity,net_amt,status,"
											     +" reas_code,loc_code,stk_opt,rate,lot_no,lot_sl,tax_class,tax_chap,tax_env,unit,chg_date,chg_user,chg_term,"
												 +" ret_rep_flag,eff_net_amt,conv__qty_stduom,conv__rtuom_stduom,unit__std,quantity__stduom,rate__stduom,exp_date,discount,tax_amt,"
											     +" site_code__mfg,mfg_date,unit__rate,pack_code,full_ret,item_ser,reas_code__org,no_art,rate__clg,rate__stk,cost_rate,"
												 +" expiry_deduction,rate__std,claim_qty,physical_qty,rate_std,qc_reqd,line_no__invtrace,mrp_value,sale_order,physical_status,line_type,"
											     +" invoice_id__club,part_qty,cust_item__ref,gross_weight,tare_weight,net_weight,cust_item__code,sord_line_no,contract_no,line_no__sform) "
												 +" select  tran_id,"+maxLine+",invoice_id,line_no__inv,item_code,"+quantity+","+quantity * rate+",status,reas_code,'"+locCodeTo+"',stk_opt,"
											     +" rate,'"+lotNoTo+"','"+lotSlTo+"',tax_class,tax_chap,tax_env,unit,chg_date,chg_user,chg_term,ret_rep_flag,eff_net_amt,conv__qty_stduom,"
												 +" conv__rtuom_stduom,unit__std,"+quantity+",rate__stduom,exp_date,discount,tax_amt,site_code__mfg,mfg_date,unit__rate,pack_code,full_ret,"
											     +" item_ser,reas_code__org,no_art,rate__clg,rate__stk,cost_rate,expiry_deduction,rate__std,claim_qty,physical_qty,rate_std,qc_reqd,"
												 +" line_no__invtrace,mrp_value,sale_order,physical_status,line_type,invoice_id__club,part_qty,cust_item__ref,gross_weight,tare_weight,"
											     +" net_weight,cust_item__code,sord_line_no,contract_no,line_no__sform from sreturndet where  tran_id = ? and  line_no = ? ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted adj_issrcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update sreturndet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?   ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sreturndet ="+k);
										pstmt1.close();
										pstmt1 = null;
									}
								}
								else if("W-ISS".equalsIgnoreCase(refSer.trim())) // Work order ISS .   
								{
									sql = " update workorder_issdet set  loc_code = ? ,lot_no = ? ,lot_sl = ?  where tran_id = ? and line_no = ?   ";
									pstmt1 =conn.prepareStatement(sql);
									pstmt1.setString(1,locCodeTo);
									pstmt1.setString(2,lotNoTo);
									pstmt1.setString(3,lotSlTo);
									pstmt1.setString(4,refId);
									pstmt1.setInt(5,refLine);
									k = pstmt1.executeUpdate();
									System.out.println("No of row updated workorder_issdet ="+k);
									pstmt1.close();
									pstmt1 = null;

								}
								else if("S-ALC".equalsIgnoreCase(refSer.trim())) // Manual Sorder Allocation   
								{
									System.out.println("update for Manual Sorder Allocation");
									
									System.out.println("update..  "+quantity+"       "+splitQty);

									if(splitQty > 0)//insert new record for split quantity
									{
										sql = " update sordalloc set  loc_code = ? ,lot_no = ?  ,lot_sl = ? , quantity = ?,quantity__stduom = ? where sale_order = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeFr);
										pstmt1.setString(2,lotNoFr);
										pstmt1.setString(3,lotSlFr);
										pstmt1.setDouble(4,splitQty);
										pstmt1.setDouble(5,splitQty);
										pstmt1.setString(6,refId);
										pstmt1.setInt(7,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sordalloc ="+k);
										pstmt1.close();
										pstmt1 = null;

										if(k > 0)
										{

											sql = " insert into sordalloc (sale_order,line_no,exp_lev,item_code__ord,item_code,lot_no,lot_sl,loc_code,item_ref,quantity,unit,qty_alloc,"
											     +" date_alloc,status,item_grade,exp_date,alloc_mode,site_code,conv__qty_stduom,unit__std,quantity__stduom,mfg_date,site_code__mfg,reas_code,ref_id__alloc,ref_line__no,wave_flag) "
												 +" select sale_order,line_no,exp_lev,item_code__ord,item_code,'"+lotNoTo+"','"+lotSlTo+"','"+locCodeTo+"',item_ref,"+quantity+",unit,qty_alloc,date_alloc,"
											     +" status,item_grade,exp_date,alloc_mode,site_code,conv__qty_stduom,unit__std,"+quantity+",mfg_date,site_code__mfg,reas_code,"
												 +" ref_id__alloc,ref_line__no,wave_flag from  sordalloc where  SALE_ORDER = ? and  LINE_NO = ? "
											     +" and site_code = ? and loc_code = ? and item_code = ? and lot_no = ? and lot_sl = ?  ";
											System.out.println("insert qry="+sql);
											pstmt1 =conn.prepareStatement(sql);
											pstmt1.setString(1,refId);
											pstmt1.setInt(2,refLine);
											pstmt1.setString(3,siteCode);
											pstmt1.setString(4,locCodeFr);
											pstmt1.setString(5,itemCode);
											pstmt1.setString(6,lotNoFr);
											pstmt1.setString(7,lotSlFr);
											k = pstmt1.executeUpdate();
											System.out.println("No of row inserted adj_issrcpdet ="+k);
											pstmt1.close();
											pstmt1 = null;
										}
									}
									else
									{
										sql = " update sordalloc set  loc_code = ? ,lot_no = ?  ,lot_sl = ?  where sale_order = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated sordalloc ="+k);
										pstmt1.close();
										pstmt1 = null;

									}
								}
							}

						}while(rs.next());
						//end rs loop

					}
				}
			}

			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("after invtracealloc check .. pendingQty =="+pendingQty+"  quantitydet="+quantitydet);
			quantitydet = pendingQty;

			if(pendingQty > 0)
			{

				if(checkWOIssue)//if qty alloc in work order
				{
					stockQty = 0;
					System.out.println("qty alloc in work order ");
					sql = " select inv_allocate.work_order ,inv_alloc_det.tran_id, inv_alloc_det.line_no ,inv_alloc_det.alloc_qty from inv_allocate ,  inv_alloc_det where  inv_allocate.tran_id  =  inv_alloc_det.tran_id "
							+" and case when inv_alloc_det.deallocated is null then 'N' else inv_alloc_det.deallocated end != 'Y' "
							+" and inv_alloc_det.site_code = ? and inv_alloc_det.loc_code = ? and inv_alloc_det.item_code = ? "
							+" and inv_alloc_det.lot_no = ? and inv_alloc_det.lot_sl = ?  order by inv_alloc_det.alloc_qty desc  " ;
					pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
					pstmt.setString(1, siteCode);
					pstmt.setString(2, locCodeFr);
					pstmt.setString(3, itemCode);
					pstmt.setString(4, lotNoFr);
					pstmt.setString(5, lotSlFr);
					rs = pstmt.executeQuery();
					while(rs.next())
					{

						refId = checkNull(rs.getString( "tran_id" ));
						//refLine = rs.getInt("ref_line");
						refLine = Integer.parseInt( checkNull(rs.getString("line_no")).trim().length() == 0 ?"0":rs.getString("line_no").trim()) ;
						refQty = rs.getDouble("alloc_qty");
						workorder = rs.getString("work_order");

						System.out.println("pendingQty="+pendingQty);
						System.out.println("refSer="+refSer);
						System.out.println("refId="+refId);
						System.out.println("refLine="+refLine);
						System.out.println("refQty="+refQty);
						if(pendingQty <= 0)
						{
							break;
						}
						if(pendingQty <= refQty)
						{
							quantity = pendingQty;
							pendingQty = 0;
						}
						else
						{
							quantity = refQty;
							pendingQty = pendingQty - refQty ;
						}
						stockQty = stockQty + quantity;

						sql = " select workorder_issdet.tran_id ,workorder_issdet.line_no  from workorder_iss , workorder_issdet  where workorder_iss.tran_id = workorder_issdet.tran_id "
								+" and workorder_iss.work_order = ?  and workorder_iss.site_code = ? "
								+" and workorder_issdet.loc_code = ? and workorder_issdet.item_code = ?  and workorder_issdet.lot_no = ? and workorder_issdet.lot_sl = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1, workorder);
						pstmt1.setString(2, siteCode);
						pstmt1.setString(3, locCodeFr);
						pstmt1.setString(4, itemCode);
						pstmt1.setString(5, lotNoFr);
						pstmt1.setString(6, lotSlFr);
						rs1 = pstmt1.executeQuery();
						while(rs1.next())
						{
							tranId = rs1.getString("tran_id");
							//lineNo = rs1.getInt("line_no");

							sql = "  select count(*) from workorder_issdet_sl  where tran_id   = ? ";
							pstmt2 = conn.prepareStatement(sql);
							pstmt2.setString(1, tranId);
							rs2 = pstmt2.executeQuery();
							if(rs2.next())
							{
								count = rs2.getInt(1);
							}
							rs2.close();
							rs2 = null;
							pstmt2.close();
							pstmt2 = null;
							if(count > 0) //if weighing is done then give error 
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;

						hashMap = new HashMap();
						hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
						hashMap.put("ref_ser","W-ISS");
						hashMap.put("ref_id",refId);
						hashMap.put("ref_line",String.valueOf(refLine));					
						hashMap.put("site_code",siteCode);
						hashMap.put("item_code",itemCode);
						hashMap.put("loc_code",locCodeFr);
						hashMap.put("lot_no",lotNoFr);
						hashMap.put("lot_sl",lotSlFr);
						hashMap.put("alloc_qty",new Double(-1*quantity)); 
						hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
						hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
						errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
						System.out.println("updateInvallocTrace....."+errString);
						if(errString != null && errString.trim().length() > 0)
						{
							retValue.add("Error");
							retValue.add(new Double(quantitydet));
							return retValue;
						}
					}//rs loop end
					System.out.println("Stock Qty="+stockQty+"  pendingQty="+pendingQty);
					if (stockQty > 0 && ( errString == null || errString.trim().length() == 0)) 
					{
						stockMap = tempMap;
						stockMap.put("loc_code", locCodeFr);
						stockMap.put("lot_no", lotNoFr);
						stockMap.put("lot_sl", lotSlFr);
						stockMap.put("tran_type", "I");
						stockMap.put("quantity", ""+stockQty);
						stockMap.put("qty_stduom", ""+stockQty);
						//stock update for tran type = "I"
						errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); //updateStock called only once's 
						System.out.println("stock update FOR I ....."+errString);
						if ( errString != null && errString.trim().length() > 0 )
						{
							retValue.add("Error");
							retValue.add(new Double(quantitydet));
							return retValue;
						}
						else
						{
							stockMap.put("loc_code", locCodeTo);
							stockMap.put("lot_no", lotNoTo);
							stockMap.put("lot_sl", lotSlTo);
							stockMap.put("tran_type", "R");
							//stock update for tran type = "R"
							errString = stkUpdate.updateStock( stockMap, xtraParams, conn ); //updateStock called only once's 
							System.out.println("stock update FOR R ....."+errString);
							if ( errString != null && errString.trim().length() > 0 )
							{
								retValue.add("Error");
								retValue.add(new Double(quantitydet));
								return retValue;
							}
						}
						if ( errString == null || errString.trim().length() == 0)
						{
							if(rs != null)
							{
								rs.first();
								pendingQty = quantitydet;
								System.out.println("iterate rs for deallcation ");
								do 
								{

									refId = checkNull(rs.getString( "tran_id" ));
									//refLine = rs.getInt("ref_line");
									refLine = Integer.parseInt( checkNull(rs.getString("line_no")).trim().length() == 0 ?"0":rs.getString("line_no").trim()) ;
									refQty = rs.getDouble("alloc_qty");
									workorder = rs.getString("work_order");

									System.out.println("pendingQty="+pendingQty);
									System.out.println("refSer="+refSer);
									System.out.println("refId="+refId);
									System.out.println("refLine="+refLine);
									System.out.println("refQty="+refQty);
									if(pendingQty <= 0)
									{
										break;
									}
									if(pendingQty <= refQty)
									{
										quantity = pendingQty;
										pendingQty = 0;
									}
									else
									{
										quantity = refQty;
										pendingQty = pendingQty - refQty ;
									}
									hashMap = new HashMap();
									hashMap.put("tran_date",new java.sql.Date(System.currentTimeMillis()));
									hashMap.put("ref_ser",refSer);
									hashMap.put("ref_id",refId);
									hashMap.put("ref_line",String.valueOf(refLine));					
									hashMap.put("site_code",siteCode);
									hashMap.put("item_code",itemCode);
									hashMap.put("loc_code",locCodeTo);
									hashMap.put("lot_no",lotNoTo);
									hashMap.put("lot_sl",lotSlTo);
									hashMap.put("alloc_qty",new Double(quantity)); 
									hashMap.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
									hashMap.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
									errString = invAllocTrace.updateInvallocTrace(hashMap, conn);
									System.out.println("updateInvallocTrace 2....."+errString);
									if ( errString != null && errString.trim().length() > 0 )
									{
										retValue.add("Error");
										retValue.add(new Double(quantitydet));
										return retValue;
									}
									else 
									{

										sql = " update inv_alloc_det set  loc_code = ? ,lot_no = ?  ,lot_sl = ?  where tran_id = ? and line_no = ?  ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,refId);
										pstmt1.setInt(5,refLine);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated inv_alloc_det ="+k);
										pstmt1.close();
										pstmt1 = null;

										sql = " update workorder_issdet set   loc_code = ? ,lot_no = ? ,lot_sl = ? where tran_id in  (select tran_id from workorder_iss where work_order = ? and site_code = ? )  "
												+" and loc_code  = ? and item_code = ? and lot_no = ? and lot_sl = ? ";
										pstmt1 =conn.prepareStatement(sql);
										pstmt1.setString(1,locCodeTo);
										pstmt1.setString(2,lotNoTo);
										pstmt1.setString(3,lotSlTo);
										pstmt1.setString(4,workorder);
										pstmt1.setString(5,siteCode);
										pstmt1.setString(6,locCodeFr);
										pstmt1.setString(7,itemCode);
										pstmt1.setString(8,locCodeFr);
										pstmt1.setString(9,lotSlFr);
										k = pstmt1.executeUpdate();
										System.out.println("No of row updated workorder_issdet ="+k);
										pstmt1.close();
										pstmt1 = null;

									}


								}while(rs.next()); //rs 2nd lopp end 
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;



							}


						}

					}
					quantitydet = pendingQty;
				}//check for work order alloc qty
			}//check pending qty 


		} catch (SQLException e) 
		{
			System.out.println("SQLException :allocDeAlloc :SQLException :==>\n"+e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			throw new ITMException(e);
		}
		catch (Exception e) 
		{
			System.out.println("Exception :allocDeAlloc : :Exception :==>\n"+e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("Befor Map");
		System.out.println("err string::"+errString);
		System.out.println("pending qty::"+quantitydet);
		System.out.println("Befor Map");
		retValue.add(errString);
		retValue.add(new Double(quantitydet));

		return retValue;
	}//end allocTransfer
}
