package ibase.webitm.ejb.dis;

import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;
import java.io.File;
import java.io.PrintWriter;
import java.io.FileOutputStream;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

public class StockUpdate
{
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	CommonConstants commonConstants  = new CommonConstants();
	DistCommon disCommon = new DistCommon();
	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = null;
	String termId = null;
	double quantity=0,qtyStduom=0,rate = 0,potencyPerc=0,netAmt= 0,grossWeight=0;
	double tareWeight=0,netWeight=0,rateOh=0,grossRate=0,convQtyStduom=0,noArt=0;
	double actualRate=0,batchSize = 0;
	String errString ="",itemCode = "",unit="",tranType = "",siteCode="",locationCode="";
	String lotNo="",lotSl="",checkExpiry="",tranSer="",tranId="",acctCodeCr="",packRef = "";
	String acctCodeDr="",cctrCodeCr = "",cctrCodeDr = "",lineNo="",sorderNo ="",siteCodeMfg="";
	String packCode="",itemSer="",reasCode="",invStat="",sundryType="",sundryCode="";
	String packInstr="",dimension="",acctCodeInv="",cctrCodeInv="",acctCodeOh="",cctrCodeOh="";
	String suppCodeMfg="",grade="",batchNo="",unitAlt="",remarks="",refIdFor="",refSerFor="";
	Timestamp creaDateStr=null,date=null,mfgDate=null,expDate = null,currDate=null,retestDateDate=null;
	Timestamp lastPhycDate=null,tranDate= null,retestDate =null;
	String suppCode="";  // added by cpatil
	//
	String considerAllocate = "Y",	partialUsed = "";
	//
    /**
     * Updates stock return error if fails
     *
     * @param     updateStockMap  HashMap with stock details
     * @param     xtraParams Extra parameters
     * @param	  conn   Database connection
     * @return    Error code if fails
     * @exception ITMException 
     */
	public  String updateStock(HashMap updateStockMap,String xtraParams, Connection conn) throws ITMException, Exception
	{
        PreparedStatement pstmt = null;
        ResultSet rs=null;
        
        String sql=null,errString=null,mbaseUnit=null,stkOpt=null,qcReqd=null;
		String orderType = null,stockValuation=null,minvStat=null,moverIssue=null,minvtraceNo=null;
		String mfinEntity = null,mcurrCode=null,issCriteria = null,value=null,invAcct=null;		
        Timestamp stkRetestDate=null,creaDt=null;
		String parmValueStr=null;
		double mrate=0,meffQty=0,mbefQty=0,mafterQty =0,effQty=0,mamount=0,mexcRate=0,mstkQty=0;
		double mallocQty=0,oldGrossValue=0,oldvalue=0,oldQty=0,newValue=0,newGrossValue=0;
		double totalValue=0,totalGrossValue=0,oldValue=0,qtyPerArt=0,tarewtPerArt = 0;
		double grosswtPerArt = 0,parmValue=0;
		double convFact=1;
		int count=0,mshLife=0,update=0;
		String acctCodeDr = "",cctrCodeDr = "", sqlState ="", errorCode = "";
        Timestamp mtoday = null;
        Timestamp tempTestDate = null;
        
		try
		{
			DistCommon distCommon = new DistCommon();
            FinCommon finCommon = new FinCommon();
            
            puplateCommonParameters(updateStockMap);
            
            tempTestDate = Timestamp.valueOf("1900-01-01 00:00:00");
            userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			if (userId == null || userId.trim().length() == 0)
			{
				userId = "SYSTEM";
			}
			if (termId == null || termId.trim().length() == 0)
			{
				termId = "SYSTEM";
			}
			if(this.tranType.equalsIgnoreCase("R"))
			{
				sql="SELECT MIN(CREA_DATE) FROM STOCK WHERE ITEM_CODE = '"+this.itemCode+"' AND SITE_CODE = '"+this.siteCode+"'"
				+" AND LOT_NO = '"+this.lotNo+"'";
				System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					creaDt = rs.getTimestamp(1);
					System.out.println("creaDt Initialized with  MIN(CREA_DATE) of stock:::- ["+creaDt+"]");
				}
				pstmt.close();
				rs.close();
			}
			else
			{
				creaDt = new Timestamp(System.currentTimeMillis()) ;
				System.out.println("creaDt Initialized Current date:::- ["+creaDt+"]");
			}

			// 05/02/13 manoharan check for stock if issue
			if (this.tranType.equalsIgnoreCase("I") || this.tranType.equalsIgnoreCase("ID") )
			{
				sql =" select count(1) 	from stock 	where item_code = ? "
				    +" and site_code = ? " 
					+" and loc_code  = ? " 
					+" and lot_no = ? "
					+" and lot_sl =  ? ";

                    pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,this.itemCode);
					pstmt.setString(2,this.siteCode);
					pstmt.setString(3,this.locationCode);
					pstmt.setString(4,this.lotNo);
					pstmt.setString(5,this.lotSl);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
					pstmt.close();
					pstmt = null;
					rs.close();
					rs = null;
					if (count == 0)
					{
						errString = "VTSTOCK1";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						return errString;
					}
					
			}
			// end 05/02/13 manoharan
            
            /*if gs_run_mode <> 'B' then//Batch load to be checked, currently the current Date 
                s_updatestock.currdate = ldt_today // and create date is taken as current timestamp
            else
                s_updatestock.currdate = s_updatestock.trandate
            end if*/
            
            if(currDate == null){
                this.currDate = new Timestamp(System.currentTimeMillis());
            }
			if(creaDt == null){
				creaDt = this.currDate;                
			}
            System.out.println("this.currDate "+this.currDate);
			System.out.println("creaDt :::- ["+creaDt+"]");
            if(this.invStat == null || this.invStat.trim().length()==0)
			{
				sql="SELECT INV_STAT FROM LOCATION 	WHERE LOC_CODE = '"+this.locationCode+"'";
				System.out.println("Query :::- ["+sql+"]");
                pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					this.invStat = rs.getString(1);
					if(this.invStat!=null)
                        System.out.println("INV_STAT FROM LOCATION :::- ["+this.invStat+"]");
				}
				pstmt.close();
				rs.close();
			}

			if ((!this.tranSer.equalsIgnoreCase("QC-ORD")) &&  (!this.tranSer.equalsIgnoreCase("I-PKR")) && (!this.tranSer.equalsIgnoreCase("I-PKI")) && (!this.tranType.equalsIgnoreCase("R")) && (!this.tranSer.equalsIgnoreCase("S-ISS")) && (!this.tranSer.equalsIgnoreCase("QC-TRF") )&& (!this.tranSer.equalsIgnoreCase("PR-AMD")) && (!this.tranSer.equalsIgnoreCase("W-QCS")) )
			{
				sql=" SELECT COUNT(*) FROM QC_ORDER WHERE  SITE_CODE = '"+this.siteCode+"'"
					+"AND	 LOC_CODE  = '"+this.locationCode+"'"
					+"AND	 ITEM_CODE = '"+this.itemCode+"'"
					+"AND	 LOT_NO 	  = '"+this.lotNo+"'"
					+"AND	 LOT_SL 	  = '"+this.lotSl+"'"
					+"AND	 STATUS 	 <> 'C'"; 
				System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next()){
                    count = rs.getInt(1);
				}
                System.out.println("Record from QC_ORDER:::- ["+count+"]");
                
                if(count == 0)
                {
                    sql =" SELECT COUNT(*)  FROM  QC_ORDER WHERE  SITE_CODE = '"+this.siteCode+"'"
                        +" AND LOC_CODE  = '"+this.locationCode+"'"
                        +"AND ITEM_CODE = '"+this.itemCode+"'"
                        +"AND  LOT_NO     = '"+this.lotNo+"'"
                        +"AND    LOT_SL IS NULL "
                        +"  AND  STATUS      <> 'C' ";
                        System.out.println("Query :::- ["+sql+"]");
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery();
                        if(rs.next())
                        {
                            count = rs.getInt(1);
                        }
                        pstmt.close();
                        rs.close();
                        System.out.println("Record from QC_ORDER:::- ["+count+"]");
                        if(count > 0)
                        {
                            errString = "VTUNCFQC";
                            errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
                            return errString;
                        }
                }else{
                    errString = "VTUNCFQC";
                    errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
                    return errString;
                }
				pstmt.close();
				rs.close();
			}
			
			sql =" SELECT COST_RATE,UNIT,SHELF_LIFE,(CASE WHEN STK_OPT IS NULL THEN '0' ELSE STK_OPT END ),"
			+" (CASE WHEN QC_REQD IS NULL THEN 'N' ELSE QC_REQD END ) FROM ITEM WHERE ITEM_CODE = '"+this.itemCode+"'";
			System.out.println("Query ::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mrate = rs.getDouble(1);
				mbaseUnit = rs.getString(2);
				mshLife = rs.getInt(3);
				stkOpt = rs.getString(4);
				qcReqd =  rs.getString(5);
				
			}
			pstmt.close();
			rs.close();
			
			sql = "SELECT ORDER_TYPE FROM WORKORDER WHERE WORK_ORDER ='"+this.sorderNo+"'";
			System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				orderType = rs.getString(1);
			}
			pstmt.close();
			rs.close();
						
			if(orderType != null && orderType.equalsIgnoreCase("T"))
			{
				stkOpt = checkStkOptRnd(this.itemCode,this.siteCode,conn);
			}
			if(stkOpt == null || stkOpt.trim().length() == 0)
			{
				stkOpt = checkStkOpt(this.itemCode,this.siteCode,conn);
				System.out.println("stkOpt[gfChkStkOpt] :::- ["+stkOpt+"]");
			}else{
                System.out.println("[gbfChkStkOptRnd] :::- ["+stkOpt+"]");
			}
			if(this.itemSer == null || this.itemSer.trim().length()==0)
			{
				sql = "SELECT ITEM_SER FROM ITEM WHERE ITEM_CODE = '"+this.itemCode+"'" ; 
				System.out.println("Query :::- ["+sql+"]");
                pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					this.itemSer = rs.getString(1);
				}
				pstmt.close();
				rs.close();
			}
			sql = "	SELECT 	(CASE WHEN STOCK_VALUATION IS NULL THEN 'N' ELSE STOCK_VALUATION END )"
			+" FROM ITEMSER WHERE 	ITEM_SER = '"+this.itemSer+"'" ;
			System.out.println("Query :::- ["+sql+"]");
            pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				stockValuation = rs.getString(1);
			}
			pstmt.close();
			rs.close();
			if(stockValuation == null) stockValuation="N";
			if (this.grade == null || this.grade.trim().length() == 0) this.grade = "   ";
			System.out.println("stkOpt :::- ["+stkOpt+"]");
			if(stkOpt.equalsIgnoreCase("0"))
			{
				System.out.println("Not To Update Stock>>>>>>>>>>>>>>>>>>>>>>>>");
                return errString;//errString to be made - Jiten
			}
			if(stkOpt.equalsIgnoreCase("1"))
			{
				if (this.tranType.equalsIgnoreCase("R"))
				{
					String lotNoChg = this.grade +"               ";
					this.lotNo = lotNoChg.substring(0,15);
					this.lotSl= "     ";
				}
			}
			//*********Calculating conversion quantity
			meffQty =0;
			if(this.qtyStduom == 0)
			{
				if(!this.unit.equalsIgnoreCase(mbaseUnit))
				{
					if(this.convQtyStduom != 0)
					{
						convFact = this.convQtyStduom;
					}
					ArrayList convQtyList = disCommon.getConvQuantityFact(this.unit, mbaseUnit, this.itemCode, this.quantity, convFact,conn);
					meffQty = Double.parseDouble(convQtyList.get(1).toString());
					if(meffQty == -999999999)
					{
						errString = "VMUCNV1";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						return errString;
					
					}
					if (this.convQtyStduom == 0)
					{
						this.convQtyStduom= Double.parseDouble(convQtyList.get(0).toString());
					}
					
				}
				else
				{
					meffQty = this.quantity ;
					if (this.convQtyStduom == 0) 
					{
						this.convQtyStduom=1;
					}
					
				}
				this.qtyStduom = meffQty;
			}
			if(this.lotNo == null || this.lotNo.trim().length() == 0)
			{
				this.lotNo ="               ";
			}
			if(this.lotSl == null || this.lotSl.trim().length()==0)
			{
				this.lotSl = "     ";
			}
			if (this.tranType.equalsIgnoreCase("I") || this.tranType.equalsIgnoreCase("ID") )
			{
				sql =" select rate 	from stock 	where item_code = '"+this.itemCode+"'"
				    +" and site_code = '"+this.siteCode+"'" 
					+" and loc_code  = '"+this.locationCode+"'" 
					+" and lot_no = '"+this.lotNo+"'"
					+" and lot_sl = '"+this.lotSl+"'";
					System.out.println("Query :::- ["+sql+"]");
                    pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						this.rate = rs.getDouble(1);
					}
					pstmt.close();
					rs.close();
                    ///////////////////
                    errString = calcWeights(conn);
                    if(errString != null && errString.trim().length() >0){ 
                        errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
                        return errString;
                    }
			}
			if (CommonConstants.DB_NAME.equalsIgnoreCase("DB2")){
				sql =" select quantity, inv_stat from stock where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"' for update ";
			}
			else if (CommonConstants.DB_NAME.equalsIgnoreCase("MSSQL")){
				sql =" select quantity, inv_stat from stock (updlock) where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"'  ";
			}
			else
			{
				sql =" select quantity, inv_stat from stock where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"' for update nowait ";
			}
			System.out.println("Query :::- ["+sql+"]");
            pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mbefQty=rs.getDouble(1);
				minvStat=rs.getString(2);				
			}
			pstmt.close();
			rs.close();
			if (this.tranType.equalsIgnoreCase("R"))
			{
				mafterQty = mbefQty + this.qtyStduom;
				effQty = this.qtyStduom;
			}
			else
			{
				mafterQty = mbefQty - this.qtyStduom;
				effQty = this.qtyStduom * -1;
			}
			//****************
			if((!this.tranType.equalsIgnoreCase("R")) && (!this.tranType.equalsIgnoreCase("D")))
			{
				if(mbefQty < this.qtyStduom)
				{
					if(this.invStat != null && this.invStat.trim().length() > 0){
						sql = "select overiss  from invstat where inv_stat = '"+this.invStat+"'";
					}
					else{
						sql = "select overiss  from invstat	where inv_stat = '"+minvStat+"'";
					}
                    System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next()){
						moverIssue=(rs.getString(1)== null ? "":rs.getString(1));
					}
					pstmt.close();
					rs.close();
					if(!moverIssue.equalsIgnoreCase("Y")){
						errString = "VTOVERISS1";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						return errString;
					}
				
				}
			}
            if(this.grade != null && this.grade.trim().length() == 0){
                this.grade = "   ";
            }
			if(!this.tranType.equalsIgnoreCase("R")){
				if((this.acctCodeCr==null )|| this.acctCodeCr.trim().length()==0 || this.cctrCodeCr== null || this.cctrCodeCr.trim().length()==0 ){
					sql = "select acct_code__inv,cctr_code__inv from stock where item_code = '"+this.itemCode+"'"
				  	+" and site_code = '"+this.siteCode+"'" 
					+" and loc_code  = '"+this.locationCode+"'" 
					+" and lot_no = '"+this.lotNo+"'"
					+" and lot_sl = '"+this.lotSl+"'  ";
                    
    				System.out.println("Query :::- ["+sql+"]");
    				pstmt = conn.prepareStatement(sql);
    				rs = pstmt.executeQuery();
    				if(rs.next())
    				{
    					this.acctCodeCr=rs.getString(1);
    					this.cctrCodeCr = rs.getString(2);
    				}
    				pstmt.close();
    				rs.close();
                }
			}
			
			if (!this.tranSer.equalsIgnoreCase("SCRRCP")){
				System.out.println("Transer "+tranSer);
			    String tranDateStr = genericUtility.getValidDateString(this.tranDate.toString(),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
				String xmlValues = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
				xmlValues = xmlValues + "<Header></Header>";
				xmlValues = xmlValues + "<Detail1>";
				xmlValues = xmlValues +	"<tran_id></tran_id>";
				xmlValues = xmlValues + "<site_code>" + this.siteCode + "</site_code>";
				xmlValues = xmlValues + "<tran_date>"+ tranDateStr + "</tran_date>";
				xmlValues = xmlValues + "</Detail1></Root>";
	            minvtraceNo= generateTranId("T_INVTRACE",xmlValues,conn);
                System.out.println("Generated Tran id ::- ["+minvtraceNo+"]");
	            mamount = effQty * this.rate;
	            sql = "Insert into invtrace (tran_id, tran_date, ref_ser, ref_id, ref_line, order_id, item_code, site_code, loc_code, unit,"
	             	+" lot_no, quantity, eff_qty, eff_date, chg_win,  chg_user, chg_term, lot_sl, qty_before, qty_after,"
					+" reas_code,sundry_type,sundry_code, ref_date, rate, gross_rate, grade, acct_code__dr, cctr_code__dr, acct_code__cr, cctr_code__cr,no_art, amount,remarks,"
					+ " gross_weight,tare_weight,net_weight, chg_date, inv_stat,ref_ser__for, ref_id__for) "
					+" values (?, ?, ?, ?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?, ?,?, ?, ?,?,?,?,?,?,?,?,?,?,?, ?,?,?,"
					+"?,?,?, ?, ?, ?,?) ";
             		pstmt = conn.prepareStatement(sql);
                    System.out.println("Query :::- ["+sql+"]");
	             	pstmt.setString(1,minvtraceNo);
	             	pstmt.setTimestamp(2,this.tranDate);
	             	pstmt.setString(3,this.tranSer);
	             	pstmt.setString(4,this.tranId);
	             	pstmt.setString(5,this.lineNo);
	             	pstmt.setString(6,this.sorderNo);
	             	pstmt.setString(7,this.itemCode);
	             	pstmt.setString(8,this.siteCode);
	             	pstmt.setString(9,this.locationCode);
	             	pstmt.setString(10,mbaseUnit);
	             	pstmt.setString(11,this.lotNo);
	             	pstmt.setDouble(12,Math.abs(this.qtyStduom));
	             	pstmt.setDouble(13,effQty);
	             	pstmt.setTimestamp(14,this.tranDate);
	             	pstmt.setString(15," ");
	             	pstmt.setString(16,userId);
	             	pstmt.setString(17,termId);
	             	pstmt.setString(18,this.lotSl);
	             	pstmt.setDouble(19,mbefQty);
	             	pstmt.setDouble(20,mafterQty);
	             	pstmt.setString(21,this.reasCode);
	             	pstmt.setString(22,this.sundryType);
	             	pstmt.setString(23,this.sundryCode);
	             	pstmt.setTimestamp(24,this.tranDate);
	             	pstmt.setDouble(25,this.rate);
	             	pstmt.setDouble(26,this.grossRate);
	             	pstmt.setString(27,this.grade);
	             	pstmt.setString(28,this.acctCodeDr);
	             	pstmt.setString(29,this.cctrCodeDr);
	             	pstmt.setString(30,this.acctCodeCr);
	             	pstmt.setString(31,this.cctrCodeCr);
	             	pstmt.setDouble(32,this.noArt); 
	             	pstmt.setDouble(33,mamount);
	             	pstmt.setString(34,this.remarks);
	             	pstmt.setDouble(35,this.grossWeight);
	             	pstmt.setDouble(36,this.tareWeight);
	             	pstmt.setDouble(37,this.netWeight);
	             	pstmt.setTimestamp(38,new Timestamp(System.currentTimeMillis()));//chgdate
	             	pstmt.setString(39,this.invStat);
	             	pstmt.setString(40,this.refSerFor);
	             	pstmt.setString(41,this.refIdFor);
					
					update = pstmt.executeUpdate();
                    System.out.println("No recore Insert invtrace ::- ["+update+"]");
					pstmt.close();
		
			}
            //errString = updateStockMovement(conn);
            if(errString != null && errString.trim().length() > 0) 
                return errString;
            
			
			sql = "select fin_entity  from site where site_code = '"+this.siteCode+"'";
            System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mfinEntity=rs.getString(1);				
			}
			pstmt.close();
			rs.close();
			
			sql = "select curr_code  from parameter";
            System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				mcurrCode=rs.getString(1);				
			}
			pstmt.close();
			rs.close();
			
			mexcRate = 1;
            
            //This is commented as this is exp date not used further in PB logic - Jiten 10/04/07
            //if required the code will be added
			/*if isnull(mshlife) or mshlife = 0 then
			setnull(mexpdate)
			else
			mnewexpdt = relativedate(date(s_updatestock.trandate), mshlife)
			mexpdate  = datetime(mnewexpdt)
			end if*/
			//End
            
            mamount = this.quantity * this.rate;
			count=0;
//			*********xxxxxxxxxxxxxxxx********************
			sql = "select count(*) from stock where item_code ='"+this.itemCode.trim()+"'" 
			+" and site_code ='"+this.siteCode.trim()+"'"
			+" and loc_code = '"+this.locationCode.trim()+"'"
			+" and lot_no = '"+this.lotNo.trim()+"'" 
			+" and lot_sl = '"+this.lotSl.trim()+"'";
            System.out.println("Query :::- ["+sql+"]");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);				
			}
			System.out.println("@@@@test :count::::["+count+"]");
			pstmt.close();
			rs.close();
			if(count != 0)
			{
				sql =" select quantity,alloc_qty, inv_stat 	from stock 	where item_code = '"+this.itemCode+"'"
			  	+" and site_code = '"+this.siteCode+"'" 
				+" and loc_code  = '"+this.locationCode+"'" 
				+" and lot_no = '"+this.lotNo+"'"
				+"and lot_sl = '"+this.lotSl+"'  ";
                System.out.println("Query :::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					mstkQty=rs.getDouble(1);
					mallocQty = rs.getDouble(2);
					minvStat = rs.getString(3);					
				}
                pstmt.close();
                rs.close();				
			}
			mstkQty = finCommon.getRequiredDecimal(mstkQty,4);
            mallocQty = finCommon.getRequiredDecimal(mallocQty,4);
            meffQty = finCommon.getRequiredDecimal(meffQty,4);
			if(this.tranType.equalsIgnoreCase("R"))
			{
				if (stkOpt.equalsIgnoreCase("1") || stockValuation.equalsIgnoreCase("M"))
				{
					sql ="Select   (Case when (sum(Case When quantity IS NULL Then 0 Else quantity End)) is null Then 0 else (sum(Case When quantity IS NULL Then 0 Else quantity End)) end),"
						+"(case when (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) is null Then 0 else (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) end),"
						+"(case when sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) is null then 0 else sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) end )"
						+"	from 	 stock"
						+" where  item_code = '"+this.itemCode+"'"
						+" and 	 site_code = '"+this.siteCode+"'" 
						+" and 	 grade 	  = '"+this.grade+"'" ;
					System.out.println("Query Fired :::- ["+sql+"]");
                    pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						oldQty=rs.getDouble(1);
						oldvalue = rs.getDouble(2);
						oldGrossValue =rs.getDouble(3);
					}
                    pstmt.close();
					rs.close();
					newValue = Math.abs(this.qtyStduom) * this.rate;
					newGrossValue = Math.abs(this.qtyStduom) * this.grossRate;
					totalValue = oldValue + newValue;
					totalGrossValue = oldGrossValue + newGrossValue	;
					if((oldQty + Math.abs(this.qtyStduom)) != 0)
					{
						this.rate = (totalValue)/(oldQty+Math.abs(this.qtyStduom));
						this.grossRate = totalGrossValue / (oldQty + Math.abs(qtyStduom));
					}
				}
				else if(stockValuation.equalsIgnoreCase("C")){
					sql =" Select   (Case when (sum(Case When quantity IS NULL Then 0 Else quantity End)) is null Then 0 else (sum(Case When quantity IS NULL Then 0 Else quantity End)) end),"
						+" (case when (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) is null Then 0 else (sum((Case When quantity IS NULL Then 0 Else quantity End )  * (Case When rate IS NULL Then 0 Else rate End ))) end),"
						+" (case when sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) is null then 0 else sum((Case When quantity IS NULL Then 0 Else quantity End ) * (Case When gross_rate IS NULL Then 0 Else gross_rate End )) end )"
						+" from stock "
						+" where  item_code = '"+this.itemCode+"'"
						+" and 	 site_code = '"+this.siteCode+"'" 
						+" and 	 grade 	  = '"+this.grade+"'" 
						+" And	cctr_code__inv = :'"+this.cctrCodeInv+"'";
						System.out.println("Query :::- ["+sql+"]");
                        pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							oldQty=rs.getDouble(1);
							oldvalue = rs.getDouble(2);
							oldGrossValue =rs.getDouble(3);
						}
                        pstmt.close();
						rs.close();
						newValue = Math.abs(this.qtyStduom) * this.rate;
						newGrossValue = Math.abs(this.qtyStduom) * this.grossRate;
						totalValue = oldValue + newValue;	
						totalGrossValue = oldGrossValue + newGrossValue	;
						if((oldQty + Math.abs(this.qtyStduom))!= 0)
						{
							this.rate = totalValue / (oldQty + Math.abs(this.qtyStduom));
							this.grossRate = totalGrossValue / (oldQty + Math.abs(this.qtyStduom));
				
						}
				}
				
			}//end of tranType =R

			if(this.tranType.equalsIgnoreCase("I")){
				mstkQty = (mstkQty - meffQty);
				if (count > 0)
				{
					if(this.tranSer.equalsIgnoreCase("QC-ORD"))
					{
						sql=" update stock set quantity   = quantity - ? ,"
						+" ltran_date = ? ,"
						 +" last_iss_date = ? ,"
						 +" chg_user   = ? ," 
						 +" chg_term   = ? , " 
						 +" chg_date   = ? ,"
						 +" gross_weight = gross_weight - ? ,"
						 +" net_weight = net_weight - ? ,"
						 +" tare_weight = tare_weight - ? ,"
						 +" last_phyc_date = ? ,"
						 +" potency_perc = ? " 
						 +" where item_code = '"+this.itemCode+"'"
						 +" and site_code = '"+this.siteCode+"'"
						 +" and loc_code  = '"+this.locationCode+"'" 
						 +" and lot_no 	 = '"+this.lotNo+"'"
						 +" and lot_sl 	 = '"+this.lotSl+"'";
                        System.out.println("Query :::- ["+sql+"]");
    					pstmt = conn.prepareStatement(sql);
    					pstmt.setDouble(1,this.qtyStduom);
    					pstmt.setTimestamp(2,this.tranDate);
    					pstmt.setTimestamp(3,this.tranDate);
    					pstmt.setString(4,userId);
    					pstmt.setString(5,termId);
    					pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
    					pstmt.setDouble(7,this.grossWeight);
    					pstmt.setDouble(8,this.netWeight);
    					pstmt.setDouble(9,this.tareWeight);
    					pstmt.setTimestamp(10,this.lastPhycDate);
    					pstmt.setDouble(11,this.potencyPerc);
    	
    					
    					pstmt.executeUpdate();
    					pstmt.close();
    					rs.close();
					}else{
                        sql=" update stock set quantity   = quantity - ? ,"
                            +" ltran_date = ? ,"
                            +" last_iss_date = ? ,"
                            +" chg_user   = ? ," 
                            +" chg_term   = ? , " 
                            +" chg_date   = ? ,"
                            +" gross_weight = gross_weight - ? ,"
                            +" net_weight = net_weight - ? ,"
                            +" tare_weight = tare_weight - ? ,"
                            +" last_phyc_date = ? "
                            +" where item_code = '"+this.itemCode+"'"
                            +" and site_code = '"+this.siteCode+"'"
                            +" and loc_code  = '"+this.locationCode+"'" 
                            +" and lot_no   = '"+this.lotNo+"'"
                            +" and lot_sl   = '"+this.lotSl+"'";
                            System.out.println("Query :::- ["+sql+"]");
                            pstmt = conn.prepareStatement(sql);
                            pstmt.setDouble(1,this.qtyStduom);
                            pstmt.setTimestamp(2,this.tranDate);
                            pstmt.setTimestamp(3,this.tranDate);
                            pstmt.setString(4,userId);
                            pstmt.setString(5,termId);
                            pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
                            pstmt.setDouble(7,this.grossWeight);
                            pstmt.setDouble(8,this.netWeight);
                            pstmt.setDouble(9,this.tareWeight);
                            pstmt.setTimestamp(10,this.lastPhycDate);
                            
                            pstmt.executeUpdate();
                            pstmt.close();
                            rs.close();
                    }
				}else{
					sql = "select inv_stat from location where loc_code = '"+this.locationCode+"'";
                    pstmt = conn.prepareStatement(sql);
                    rs = pstmt.executeQuery();
                    if(rs.next()){
                        this.invStat = rs.getString(1);
                    }
                    rs.close();
                    pstmt.close();
                    
                    sql= "select (case when iss_criteria is null then 'I' else iss_criteria end) "
							+" from item where item_code = ? "; //'"+this.itemCode+"'" ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,this.itemCode);
					rs = pstmt.executeQuery();
					if(rs.next()){
						issCriteria=rs.getString(1);
					}
                    rs.close();
                    pstmt.close();
					if (this.noArt==0)
					{
						this.noArt = 1;
					}
					if( issCriteria != null && issCriteria.equalsIgnoreCase("W"))
					{
						qtyPerArt=this.qtyStduom;
						tarewtPerArt = this.tareWeight;
						grosswtPerArt = this.grossWeight;
					}
					else
					{
						qtyPerArt = this.qtyStduom/this.noArt;
						tarewtPerArt = this.tareWeight/this.noArt;
						grosswtPerArt = this.grossWeight/this.noArt;
					}
					
					sql = "insert into stock  (item_code,site_code,loc_code, lot_no,lot_sl,unit, quantity,crea_date,chg_user,"
					 	+" chg_date,chg_term,exp_date, alloc_qty,item_ser,mfg_date, site_code__mfg,potency_perc,pack_code,"
						+" inv_stat,ltran_date,last_iss_date,gross_weight,tare_weight,net_weight,pack_instr,dimension,retest_date,"
						+"rate,rate__oh,acct_code__inv, cctr_code__inv,acct_code__oh,cctr_code__oh,supp_code__mfg,grade,gross_rate,"
						+" conv__qty_stduom,unit__alt,batch_no,last_phyc_date,qty_per_art,gross_wt_per_art,tare_wt_per_art,actual_rate,remarks, no_art,pack_ref)"
						+" values( ?,?,?, ?,?,?, ?,?,?, ?,?,?,?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?,"
						+" ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?)" ;
					 pstmt = conn.prepareStatement(sql);

					 pstmt.setString(1,this.itemCode);
					 pstmt.setString(2,this.siteCode);
					 pstmt.setString(3,this.locationCode);
					 pstmt.setString(4,this.lotNo);
					 pstmt.setString(5,this.lotSl);
					 pstmt.setString(6,mbaseUnit);
					 pstmt.setDouble(7,(-1 * this.qtyStduom));
					 pstmt.setTimestamp(8,creaDt);
					 pstmt.setString(9,userId);
					 pstmt.setTimestamp(10,new java.sql.Timestamp(System.currentTimeMillis()));

					 pstmt.setString(11,termId);
					 pstmt.setTimestamp(12,this.expDate);
					 pstmt.setString(13,"0");
					 pstmt.setString(14,this.itemSer);
					 pstmt.setTimestamp(15,this.mfgDate);
					 pstmt.setString(16,this.siteCodeMfg);
					 pstmt.setDouble(17,this.potencyPerc);
					 pstmt.setString(18,this.packCode);
					 pstmt.setString(19,this.invStat);

					 pstmt.setTimestamp(20,this.currDate);
					 pstmt.setTimestamp(21,this.tranDate);
					 pstmt.setDouble(22,this.grossWeight);
					 pstmt.setDouble(23,this.tareWeight);
					 pstmt.setDouble(24,this.netWeight);
					 pstmt.setString(25,this.packInstr);
					 pstmt.setString(26,this.dimension);
					 pstmt.setTimestamp(27,this.retestDate);

					 pstmt.setDouble(28,this.rate);
					 pstmt.setDouble(29,this.rateOh);
					 pstmt.setString(30,this.acctCodeInv);
					 pstmt.setString(31,this.cctrCodeInv);
					 pstmt.setString(32,this.acctCodeOh);
					 pstmt.setString(33,this.cctrCodeOh);
					 pstmt.setString(34,this.suppCodeMfg);
					 pstmt.setString(35,this.grade);
					 pstmt.setDouble(36,this.grossRate);
					 pstmt.setDouble(37,this.convQtyStduom);
					 pstmt.setString(38,this.unitAlt);


					 pstmt.setString(39,this.batchNo);
					 pstmt.setTimestamp(40,this.lastPhycDate);
					 pstmt.setDouble(41,qtyPerArt);
					 pstmt.setDouble(42,grosswtPerArt);
					 pstmt.setDouble(43,tarewtPerArt);
					 pstmt.setDouble(44,this.actualRate);
					 pstmt.setString(45,this.remarks);
					 pstmt.setString(46,"0");
                    pstmt.setString(47,this.packRef);
                     System.out.println("Query ::- ["+sql+"]");
		             
					update = pstmt.executeUpdate();
                    System.out.println("No recore Insert invtrace ::- ["+update+"]");
					pstmt.close();
					
                }
            }
			else if(this.tranType.equalsIgnoreCase("L")){
				mstkQty = (mstkQty - meffQty);
				if (count != 0){
					sql =" update stock  set quantity = ? ," 
						+" ltran_date = ? "
						+" where item_code = '"+this.itemCode+"'" 
						+" and site_code = '"+this.siteCode +"'"
						+" and loc_code = '"+this.locationCode +"'"
						+" and lot_no = '"+this.lotNo+"'"
						+" and lot_sl = '"+this.lotSl+"'";
                    System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1,mstkQty);
					pstmt.setTimestamp(2,this.tranDate);
					pstmt.executeUpdate();
                    rs.close();
                    pstmt.close();					
                }
			}//end of tranType (L)
    		else if(this.tranType.equalsIgnoreCase("A")){
    		    mallocQty = mallocQty + meffQty;
    			if (count != 0){
    				sql =" update stock  set alloc_qty = ? ," 
    					+" ltran_date = ? "
    					+" where item_code = '"+this.itemCode+"'" 
    					+" and site_code = '"+this.siteCode +"'"
    					+" and loc_code = '"+this.locationCode +"'"
    					+" and lot_no = '"+this.lotNo+"'"
    					+" and lot_sl = '"+this.lotSl+"'";
                    System.out.println("Query :::- ["+sql+"]");
    				pstmt = conn.prepareStatement(sql);
    				pstmt.setDouble(1,mallocQty);
    				pstmt.setTimestamp(2,this.tranDate);
    				pstmt.executeUpdate();
                    rs.close();
    				pstmt.close();
    			}
    		}//end of tranType (A)
    		else if(this.tranType.equalsIgnoreCase("D")){
    			mallocQty = mallocQty - meffQty;
    			if (count != 0){
    				sql =" update stock  set alloc_qty = ? ," 
    					+" ltran_date = ? "
    					+" where item_code = '"+this.itemCode+"'" 
    					+" and site_code = '"+this.siteCode +"'"
    					+" and loc_code = '"+this.locationCode +"'"
    					+" and lot_no = '"+this.lotNo+"'"
    					+" and lot_sl = '"+this.lotSl+"'";
    				System.out.println("Query :::- ["+sql+"]");
                    pstmt = conn.prepareStatement(sql);
    				pstmt.setDouble(1,mallocQty);
    				pstmt.setTimestamp(2,this.tranDate);
    				pstmt.executeUpdate();
    				pstmt.close();
    				rs.close();
    			}
    		}//end of tranType (D)
    		else if(this.tranType.equalsIgnoreCase("ID")){
    			if (count != 0){
                    HashMap invAllocTraceMap = new HashMap();
                    invAllocTraceMap.put("ref_ser",this.tranSer);
                    invAllocTraceMap.put("ref_id",this.tranId);
                    invAllocTraceMap.put("ref_line",this.lineNo);
                    invAllocTraceMap.put("site_code",this.siteCode);
                    invAllocTraceMap.put("item_code",this.itemCode);
                    invAllocTraceMap.put("loc_code",this.locationCode);
                    invAllocTraceMap.put("lot_no",this.lotNo);
                    invAllocTraceMap.put("lot_sl",this.lotSl);
                    invAllocTraceMap.put("alloc_qty",new Double(-1 * this.qtyStduom));
                    invAllocTraceMap.put("chg_user",userId);
                    invAllocTraceMap.put("chg_term",termId);
                    invAllocTraceMap.put("chg_win","  ");
                    
                    InvAllocTraceBean invBean = new InvAllocTraceBean(); 
                    errString = invBean.updateInvallocTrace(invAllocTraceMap,conn);
                    if(errString != null && errString.trim().length() > 0){
                        errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
                        return errString;
                    }
                    sql ="update stock set quantity = (case when quantity is null then 0 else quantity end) - ? ," 
    				+" ltran_date 	= ? ,"
    				+" last_iss_date = ?,"
    				+" no_art 			= no_art - ?, "
    				+" chg_user   	= ?, "
    				+" chg_term   	= ?, "
    				+" chg_date   	= ?,"
    				+" gross_weight 	= (case when gross_weight is null then 0 else gross_weight end) - ?,"
    				+" net_weight 	= (case when net_weight is null then 0 else net_weight end) - ?,"
    				+" tare_weight 	= (case when tare_weight is null then 0 else tare_weight end) - ? "
    				+" where item_code = '"+this.itemCode+"'" 
    					+"and site_code = '"+this.siteCode +"'"
    					+"and loc_code = '"+this.locationCode +"'"
    					+"and lot_no = '"+this.lotNo+"'"
    					+"and lot_sl = '"+this.lotSl+"'";
                    System.out.println("Query :::- ["+sql+"]");
    				pstmt = conn.prepareStatement(sql);
    				pstmt.setDouble(1,this.qtyStduom);
    				pstmt.setTimestamp(2,this.tranDate);
    				pstmt.setTimestamp(3,this.tranDate);
    				pstmt.setDouble(4,this.noArt);
    				pstmt.setString(5,userId);
    				pstmt.setString(6,termId);
    				pstmt.setTimestamp(7,new java.sql.Timestamp(System.currentTimeMillis()));
    				pstmt.setDouble(8,this.grossWeight);
    				pstmt.setDouble(9,this.netWeight);
    				pstmt.setDouble(10,this.tareWeight);
    				pstmt.executeUpdate();
    				pstmt.close();
    				rs.close();
    			}
            }
    		else if(this.tranType.equalsIgnoreCase("R"))
    		{ 
    			updateStockMap = updateLotInfo( updateStockMap , conn);  // added by cpatil calling function
    			
    			//if( errString != null && errString.trim().length() > 0)
    			//{
    			//	return errString;
    			//}
    			
    			if (count > 0)
				{
    				sql ="select (case when acct_code__inv is null then '    ' else acct_code__inv end), (case when cctr_code__inv is null then '    ' else cctr_code__inv end), qty_per_art"
    					+" from stock "
    					+" where item_code = '"+this.itemCode+"'" 
    					+" and site_code = '"+this.siteCode +"'"
    					+" and loc_code = '"+this.locationCode +"'"
    					+" and lot_no = '"+this.lotNo+"'"
    					+" and lot_sl = '"+this.lotSl+"'";
                    System.out.println("Query ::- ["+sql+"]");
    				pstmt = conn.prepareStatement(sql);
    				rs = pstmt.executeQuery();
    				if(rs.next())
    				{
    					acctCodeDr = rs.getString(1);
    					cctrCodeDr = rs.getString(2);
    					qtyPerArt = rs.getDouble(3);
    				}
    				value = distCommon.getDisparams("999999","STKTRFR_SUBSTORE_CCTR_CHECK",conn);
    				if (value.equalsIgnoreCase("NULLFOUND")) value = "Y";
    				if(value.equalsIgnoreCase("Y"))
    				{
    					if (!(acctCodeDr.equalsIgnoreCase(this.acctCodeInv)) ||(!(cctrCodeDr.equalsIgnoreCase(this.cctrCodeInv))))
    					{
    						invAcct = finCommon.getFinparams("999999", "INVENTORY_ACCT",conn); 
    						if(invAcct.equalsIgnoreCase("Y"))
    						{
                                errString = "VTACTMIS";
    							errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
    							return errString;
    						}
    					}
    				}
    				
    				sql = "select retest_date  from stock"
    					+" where item_code = '"+this.itemCode+"'" 
    					+" and site_code = '"+this.siteCode +"'"
    					+" and loc_code = '"+this.locationCode +"'"
    					+" and lot_no = '"+this.lotNo+"'"
    					+" and lot_sl = '"+this.lotSl+"'";
                    System.out.println("Query ::- ["+sql+"]");
    				pstmt = conn.prepareStatement(sql);
    				rs = pstmt.executeQuery();
    				if(rs.next())
    				{
    					stkRetestDate=rs.getTimestamp(1);
    				}
                    if(this.retestDate != null && this.retestDate.compareTo(tempTestDate) == 0){
                        this.retestDate = stkRetestDate;
                    }
                    if(this.retestDate != null && this.retestDate.compareTo(tempTestDate) == 0){
                        this.retestDate = null;
                    }
    				
    				if (this.tranSer.equalsIgnoreCase("QC-ORD"))
    				{
    					sql ="update stock 	set quantity   	 = quantity + ? ,"
    						+"ltran_date 	 = ?,"
    						+"last_rcp_date  = ? ,"
    						+"rate 		     = ?,"
    						+"gross_rate  	 = ?,"
    						+"chg_user    	 = ?,"
    						+"chg_term    	 = ?,"
    						+"chg_date    	 = ?,"
    						+"gross_weight	 = gross_weight + ?,"
    						+"net_weight  	 = net_weight + ?,"
    						+"tare_weight 	 = tare_weight + ?,"
    						+"retest_date 	 = ?,"
    						+"no_art		 = ?,"
    						+"dimension   	 = ?,"
    						+"last_phyc_date = ?,"
    						+"potency_perc 	 = ? "
    						+"where item_code = '"+this.itemCode+"'" 
    						+"and site_code = '"+this.siteCode +"'"
    						+"and loc_code = '"+this.locationCode +"'"
    						+"and lot_no = '"+this.lotNo+"'"
    						+"and lot_sl = '"+this.lotSl+"'";
                        System.out.println("Query :::- ["+sql+"]");
    					pstmt = conn.prepareStatement(sql);
    					
                        pstmt.setDouble(1,this.qtyStduom);
    					pstmt.setTimestamp(2,this.tranDate);
    					pstmt.setTimestamp(3,this.tranDate);
    					pstmt.setDouble(4,this.rate);
    					pstmt.setDouble(5,this.grossRate);
    					pstmt.setString(6,userId);
    					pstmt.setString(7,termId);
    					pstmt.setTimestamp(8,new java.sql.Timestamp(System.currentTimeMillis()));
    					pstmt.setDouble(9,this.grossWeight);
    					pstmt.setDouble(10,this.netWeight);
    					pstmt.setDouble(11,this.tareWeight);
    					pstmt.setTimestamp(12,this.retestDate);
    					pstmt.setDouble(13,this.noArt);
    					pstmt.setString(14,this.dimension);
    					pstmt.setTimestamp(15,this.lastPhycDate);
    					pstmt.setDouble(16,this.potencyPerc);
    					
                        pstmt.executeUpdate();
    					pstmt.close();
    					rs.close();
    				}
    				else
    				{
    					sql ="update stock 	set quantity   	 = quantity + ? ,"
    						+"ltran_date 	 = ?,"
    						+"last_rcp_date  = ? ,"
    						+"rate 			 = ?,"
    						+"gross_rate  	 = ?,"
    						+"chg_user    	 = ?,"
    						+"chg_term    	 = ?,"
    						+"chg_date    	 = ?,"
    						+"gross_weight	 = gross_weight + ?,"
    						+"net_weight  	 = net_weight + ?,"
    						+"tare_weight 	 = tare_weight + ?,"
    						+"retest_date 	 = ?,"
    						+"no_art		 	 = no_art + ?,"
    						+"dimension   	 = ?,"
    						+"last_phyc_date = ?,"
    						+"supp_code__mfg 	 = ?," 
                            +"batch_no = ? "
    						+"where item_code = '"+this.itemCode+"'" 
    						+"and site_code = '"+this.siteCode+"'"
    						+"and loc_code = '"+this.locationCode+"'"
    						+"and lot_no = '"+this.lotNo+"'"
    						+"and lot_sl = '"+this.lotSl+"'";
                        System.out.println("Query :::- ["+sql+"]");
    					pstmt = conn.prepareStatement(sql);
    					pstmt.setDouble(1,this.qtyStduom);
    					pstmt.setTimestamp(2,this.tranDate);
    					pstmt.setTimestamp(3,this.tranDate);
    					pstmt.setDouble(4,this.rate);
    					pstmt.setDouble(5,this.grossRate);
    					pstmt.setString(6,userId);
    					pstmt.setString(7,termId);
    					pstmt.setTimestamp(8,new java.sql.Timestamp(System.currentTimeMillis()));
    					pstmt.setDouble(9,this.grossWeight);
    					pstmt.setDouble(10,this.netWeight);
    					pstmt.setDouble(11,this.tareWeight);
    					pstmt.setTimestamp(12,this.retestDate);
    					pstmt.setDouble(13,this.noArt);
    					pstmt.setString(14,this.dimension);
    					pstmt.setTimestamp(15,this.lastPhycDate);
    					pstmt.setString(16,this.suppCodeMfg);
                        pstmt.setString(17,this.batchNo);
    					
    					pstmt.executeUpdate();
    					pstmt.close();
    					rs.close();
    				}
    			}//end of count
    			else
    			{
    				sql = "select inv_stat from location where loc_code = '"+this.locationCode+"'";                    
    				pstmt = conn.prepareStatement(sql);
    				rs = pstmt.executeQuery();
                    System.out.println("Query :::- ["+sql+"]");
                    if(rs.next())
    				{
                        this.invStat =rs.getString(1);
    				}
    				pstmt.close();
    				rs.close();
    				if(this.tranSer.equalsIgnoreCase("W-IRTN") || this.tranSer.equalsIgnoreCase("C-IRTN") || this.tranSer.equalsIgnoreCase("XFRX"))
    				{
    					sql = " select Min(crea_date)  from stock "
    						+"where item_code = '"+this.itemCode+"'" 
    						+"and site_code = '"+this.siteCode +"'"
    						+"and lot_no = '"+this.lotNo+"'"
    						//+"and lot_sl = '"+this.lotSl+"'" //Commented by gulzar as discussed with Manoharan sir on 05/01/12
    						+"and crea_date is not null ";                            
    						pstmt = conn.prepareStatement(sql);
    						rs = pstmt.executeQuery();
                            System.out.println("Query :::- ["+sql+"]");
                            if(rs.next())
    						{
    							creaDt = rs.getTimestamp(1);
    						}
    						pstmt.close();
    						rs.close();
    						if(creaDt != null && creaDt.compareTo(tempTestDate) != 0){
    							mtoday = creaDt;
    						}
    				}
    				sql="select (case when iss_criteria is null then 'I' else iss_criteria end) "
    					+" from item where item_code = '"+this.itemCode+"'" ;
                    System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						issCriteria = rs.getString(1);
					}
					pstmt.close();
					rs.close();
    					
					if (issCriteria != null && issCriteria.equalsIgnoreCase("W")){
						qtyPerArt		= this.qtyStduom;
						tarewtPerArt	= this.tareWeight;
						grosswtPerArt	= this.grossWeight;
					}else{
                        if(this.noArt == 0){
							qtyPerArt		= this.qtyStduom;
							tarewtPerArt	= this.tareWeight;
							grosswtPerArt	= this.grossWeight;
						}
						else{
							qtyPerArt		= this.qtyStduom / this.noArt;
							tarewtPerArt	= this.tareWeight / this.noArt;
							grosswtPerArt	= this.grossWeight / this.noArt;		
						}
					}
    	
										
					sql="insert into stock "
						+" (item_code , site_code, loc_code," 
						+" lot_no, lot_sl, unit,"
						+" quantity,crea_date, chg_user,"
						+" chg_date,chg_term, exp_date,"
						+" alloc_qty,item_ser, mfg_date,"
						+" site_code__mfg,potency_perc,pack_code,"
						+" inv_stat,ltran_date,last_rcp_date,"
						+" rate,gross_weight,tare_weight,"
						+" net_weight,pack_instr,dimension,"
						+" retest_date,rate__oh,acct_code__inv,"
						+" cctr_code__inv,acct_code__oh,cctr_code__oh,"
						+" supp_code__mfg,grade, gross_rate,"
						+" conv__qty_stduom,unit__alt,batch_no,"
						+" no_art,last_phyc_date,qty_per_art,"
						+" gross_wt_per_art,tare_wt_per_art,actual_rate,"
						+" remarks,pack_ref,consider_allocate,	partial_used, batch_size ) values "
						+"  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
					    +" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
					    +" ?,?,?,?,?,?,?,?,?,?,?,?,?, ?)";
					//System.out.println("SQL :"+sql);    
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,this.itemCode);
					pstmt.setString(2,this.siteCode);
					pstmt.setString(3,this.locationCode);

					pstmt.setString(4,this.lotNo);
					pstmt.setString(5,this.lotSl);
					pstmt.setString(6,mbaseUnit);

					pstmt.setDouble(7,this.qtyStduom);
					pstmt.setTimestamp(8,creaDt);
					pstmt.setString(9,userId);

					pstmt.setTimestamp(10,new Timestamp(System.currentTimeMillis()));
					pstmt.setString(11,termId);
					pstmt.setTimestamp(12,this.expDate);

					pstmt.setString(13,"0");
					pstmt.setString(14,this.itemSer);
					pstmt.setTimestamp(15,this.mfgDate);

					pstmt.setString(16,this.siteCodeMfg);
					pstmt.setDouble(17,this.potencyPerc);
					pstmt.setString(18,this.packCode);
    
					pstmt.setString(19,this.invStat);
					pstmt.setTimestamp(20,this.currDate);
					pstmt.setTimestamp(21,this.tranDate);

					pstmt.setDouble(22,this.rate);
					pstmt.setDouble(23,this.grossWeight);
					pstmt.setDouble(24,this.tareWeight);

					pstmt.setDouble(25,this.netWeight);
					pstmt.setString(26,this.packInstr);
					pstmt.setString(27,this.dimension);

					pstmt.setTimestamp(28,this.retestDate);
					pstmt.setDouble(29,this.rateOh);
					pstmt.setString(30,this.acctCodeInv);

					pstmt.setString(31,this.cctrCodeInv);
					pstmt.setString(32,this.acctCodeOh);
					pstmt.setString(33,this.cctrCodeOh);

					pstmt.setString(34,this.suppCodeMfg);
					pstmt.setString(35,this.grade);
					pstmt.setDouble(36,this.grossRate);

					pstmt.setDouble(37,this.convQtyStduom);
					pstmt.setString(38,this.unitAlt);
					pstmt.setString(39,this.batchNo);

					pstmt.setDouble(40,this.noArt);
					pstmt.setTimestamp(41,this.lastPhycDate);
					pstmt.setDouble(42,qtyPerArt);

					pstmt.setDouble(43,grosswtPerArt);
					pstmt.setDouble(44,tarewtPerArt);
					pstmt.setDouble(45,this.actualRate);

					pstmt.setString(46,this.remarks);
                    pstmt.setString(47,this.packRef);
                    pstmt.setString(48,this.considerAllocate);
                    pstmt.setString(49,this.partialUsed);
					pstmt.setDouble(50,this.batchSize);
                    
                    System.out.println("ITEM_CODE :"+this.itemCode);
                    System.out.println("SITE_CODE :"+this.siteCode);
                    System.out.println("LOC_CODE :"+this.locationCode);
                    System.out.println("LOT_NO :"+this.lotNo);
                    System.out.println("LOT_SL :"+this.lotSl);
                    System.out.println("QUANTITY :"+this.qtyStduom);
                    System.out.println("UNIT :"+mbaseUnit);
                    System.out.println("CREA_DATE :"+creaDt);
                   
					pstmt.executeUpdate();
					System.out.println("@@@@@@@ executed sucessfully............");
    			}
    		}
    		if(this.tranType.equalsIgnoreCase("I") || this.tranType.equalsIgnoreCase("ID"))
    		{
    			sql ="select (case when grade is null then '   ' else grade end ),rate "
    				+" from stock"
    				+" where item_code = '"+this.itemCode+"'" 
    				+" and site_code = '"+this.siteCode +"'"
    				+" and loc_code = '"+this.locationCode +"'"
    				+" and lot_no = '"+this.lotNo+"'"
    				+" and lot_sl = '"+this.lotSl+"'";
                System.out.println("Query :::- ["+sql+"]");
    			pstmt = conn.prepareStatement(sql);
    			rs = pstmt.executeQuery();
    			if(rs.next())
    			{
                    this.grade =rs.getString(1);
                    this.rate = rs.getDouble(2);
    			}
    			pstmt.close();
    			rs.close(); 
    		}
    		if(!stockValuation.equalsIgnoreCase("N")){
    			if(this.tranType.equalsIgnoreCase("R")){
    				if (stkOpt.equalsIgnoreCase("1") || stockValuation.equalsIgnoreCase("M")) 
    				{
    					sql =" update stock set  rate = (case when '"+this.rate+"' is null then 0 else "+this.rate+" end),"
    						+" gross_rate 	= (case when '"+this.grossRate+"' is null then 0 else "+this.grossRate+" end)"
    						+" where item_code ='"+this.itemCode+"'" 
    						+" and   site_code ='"+this.siteCode+"'" 
    						+" and 	grade 	 = '"+this.grade+"'";
                            System.out.println("Query :::- ["+sql+"]");
    						pstmt = conn.prepareStatement(sql);
    						pstmt.executeUpdate();
    				}
    				else if (stockValuation.equalsIgnoreCase("C"))
    				{
    					sql = " update stock set rate = (case when '"+this.rate+"' is null then 0 else '"+this.rate+"' end) ,"
    					+" gross_rate 	=  (case when '"+this.grossRate+"' is null then 0 else '"+this.grossRate+"' end) "
    					+" where item_code ='"+this.itemCode+"'" 
    					+" and   site_code ='"+this.siteCode+"'" 
    					+" and 	grade 	 = '"+this.grade+"'"
    					+" And 	cctr_code__inv = '"+this.cctrCodeInv+"'";
                        System.out.println("Query :::- ["+sql+"]");
    					pstmt = conn.prepareStatement(sql);
    					pstmt.executeUpdate();
    				}
                }
    			sql = "select count(*) from stockvalue "
				+" where item_code ='"+this.itemCode+"'" 
				+" and   site_code ='"+this.siteCode+"'" 
				+" and VALUE_KEY = '"+this.grade+"'";
                System.out.println("Query ::- ["+sql+"]");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count =rs.getInt(1);
				}
				pstmt.close();
				rs.close();
				if(count == 1){
					if (stkOpt.equalsIgnoreCase("1") || stockValuation.equalsIgnoreCase("M")){
						if (this.tranType.equalsIgnoreCase("R")){
							sql ="update stockvalue set quantity = (case when quantity is null then 0 else quantity end) + (case when "+effQty+" is null then 0 else "+effQty+" end), "
                            +"value = ((case when "+oldQty+" is null then 0 else "+oldQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end)) + ((case when "+effQty+" is null then 0 else "+effQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end))"
							+" where item_code ='"+this.itemCode+"'" 
							+" and   site_code ='"+this.siteCode+"'" 
							+" and 	VALUE_KEY 	 = '"+this.grade+"'";
                            System.out.println("Query :::- ["+sql+"]");
							pstmt = conn.prepareStatement(sql);
							pstmt.executeUpdate();
						}else{
							sql =" update stockvalue set quantity = (case when quantity is null then 0 else quantity end) + (case when "+effQty+" is null then 0 else "+effQty+" end),	"
                            +"value = (case when value is null then 0 else value end) + ((case when "+effQty+" is null then 0 else "+effQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end)) "
							+" where item_code ='"+this.itemCode+"'" 
							+" and   site_code ='"+this.siteCode+"'" 
							+" and 	VALUE_KEY 	 = '"+this.grade+"'";
                            System.out.println("Query :::- ["+sql+"]");
							pstmt = conn.prepareStatement(sql);
							pstmt.executeUpdate();					
						}
					}else{
						sql ="update stockvalue set quantity = (case when quantity is null then 0 else quantity end) + (case when "+effQty+" is null then 0 else "+effQty+" end),"
                        +"value = (case when value is null then 0 else value end) + ((case when "+effQty+" is null then 0 else "+effQty+" end) * (case when "+this.rate+" is null then 0 else "+this.rate+" end)) " 
						+" where item_code ='"+this.itemCode+"'" 
						+" and   site_code ='"+this.siteCode+"'" 
						+" and 	VALUE_KEY 	 = '"+this.grade+"'";
                        System.out.println("Query :::- ["+sql+"]");
						pstmt = conn.prepareStatement(sql);
						pstmt.executeUpdate();
					}
					sql =" select (case when quantity is null then 0 else quantity end), (case when value is null then 0 else value end)"
					+" from stockvalue"
					+" where item_code ='"+this.itemCode+"'" 
					+" and   site_code ='"+this.siteCode+"'" 
					+" and 	VALUE_KEY 	 = '"+this.grade+"'";
                    System.out.println("Query :::- ["+sql+"]");
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					double value1=0;
					double quantity1=0;
					if(rs.next())
					{
						quantity1 = rs.getDouble(1);
						value1 = rs.getDouble(2);
					}
                    System.out.println("Stock Value Quantity : "+quantity1+" Stock Value Value :"+value1);
					if (quantity1 == 0 && value1 != 0 ){
					    parmValueStr = distCommon.getDisparams("999999","ON_STKVALUE_ADJENTRY",conn);
						if (!parmValueStr.equalsIgnoreCase("NULLFOUND")){
							parmValue = Double.parseDouble(parmValueStr);
							System.out.println("Param Value "+parmValue);
                            if (Math.abs(value1) > parmValue){
							    errString = "VTSTKVERR";	
							    errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
							    return errString;
							}
                        }else{
							if(Math.abs(value1) > 1){
								errString = "VTSTKVERR"	;
								errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
								return errString;
							}
                        }
                        errString = adjustStkValue(conn);
                        if(errString.trim().length() > 0){
                            return errString;
                        }
    					sql =" update stockvalue " 
                        +"set value = (case when value is null then 0 else value end) + (case when '"+this.netAmt+"' is null then 0 else '"+this.netAmt+"' end) "
    					+" where item_code ='"+this.itemCode+"'" 
    					+" and   site_code ='"+this.siteCode+"'" 
    					+" and 	VALUE_KEY 	 = '"+this.grade+"'";
                        
                        System.out.println("Query :::- ["+sql+"]");
                        
                        pstmt = conn.prepareStatement(sql);
                        pstmt.executeUpdate();
                    }
                }else if(count == 0){
    				if (this.tranType.equalsIgnoreCase("R")){
					   sql =" insert into stockvalue (site_code,item_code, value_key, quantity,	value )"
					   	+" values (?,?,?,?,?) ";
                       System.out.println("Query :::- ["+sql+"]");
					   pstmt = conn.prepareStatement(sql);
					   pstmt.setString(1,this.siteCode);
					   pstmt.setString(2,this.itemCode);
                       pstmt.setString(3,this.grade);
					   pstmt.setDouble(4,effQty);
					   pstmt.setDouble(5,effQty*this.rate);
					   pstmt.executeUpdate();
					}else{
                        System.out.println("StockValue update failed, there is no record in stockvalue for " + this.siteCode);
                        errString = "VTSTKVERR";    
                        errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
                        return errString;// STOCK VALUE ERROR
                    }
    			}
			}//end of stockValuation="N"
		}
		catch (SQLException sqx) // 04/02/12 manoharan this catch block added for trapping record lock
		{
			while (sqx != null)
			{
				if((CommonConstants.DB_NAME).equalsIgnoreCase("oracle") && (sqx).toString().indexOf("ORA-00054") > -1)
				{
					System.out.println("The SQLException occurs in UpdatStock [Stockupdate] getSQLState [" + sqlState + "] getErrorCode [" + errorCode + "]  Exception[Recod is locked try after some time]");
					errString = itmDBAccessEJB.getErrorString("","VTLCKERR","");
				}
				else
				{
					System.out.println("The SQLException occurs in UpdatStock [Stockupdate] getSQLState [" + sqlState + "] getErrorCode [" + errorCode + "]  Exception["+sqx + "]");
				}
				sqx.printStackTrace();
				sqx = sqx.getNextException();
			}
			
            throw new ITMException(sqx);
		}
		catch(Exception se12){
		    System.out.println("Exception in UpdatStock [Stockupdate]"+se12);
            se12.printStackTrace();
            throw new ITMException(se12);
		}finally{
		    try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){}
        }
		return errString;
	}//END OF UPDATE METHOD
	
	private void puplateCommonParameters(HashMap updateStockMap) throws ITMException, Exception
	{		
        System.out.println("Comming Map :::- ["+updateStockMap+"]");
        try{
            
        
    		if(updateStockMap.get("item_code")!= null)
    		{
    			this.itemCode = updateStockMap.get("item_code").toString();
    		}
    		if(updateStockMap.get("site_code")!= null)
    		{
    			this.siteCode = updateStockMap.get("site_code").toString();
    		}
    		if(updateStockMap.get("loc_code")!= null)
    		{
    			this.locationCode = updateStockMap.get("loc_code").toString();
    		}
    		if(updateStockMap.get("lot_no")!= null)
    		{
    			this.lotNo = updateStockMap.get("lot_no").toString();
    		}
    		if(updateStockMap.get("lot_sl")!= null)
    		{
    			this.lotSl = updateStockMap.get("lot_sl").toString();
    		}
    		if(updateStockMap.get("quantity")!= null)
    		{
    			this.quantity = Double.parseDouble(updateStockMap.get("quantity").toString());
    		}
    		if(updateStockMap.get("unit") != null)
    		{
    			this.unit = updateStockMap.get("unit").toString();
    		}
    		if(updateStockMap.get("tran_type")!= null)
    		{
    			this.tranType = updateStockMap.get("tran_type").toString();
    		}
    		if(updateStockMap.get("check_expiry")!= null)
    		{
    			this.checkExpiry = updateStockMap.get("check_expiry").toString();
    		}
    		if(updateStockMap.get("tran_date")!= null)
    		{
    			this.tranDate = (Timestamp)updateStockMap.get("tran_date");			
    		}
    		if(updateStockMap.get("tran_ser") != null)
    		{
    			this.tranSer = updateStockMap.get("tran_ser").toString();
    		}
    		if(updateStockMap.get("tran_id")!=null)
    		{
    			this.tranId = updateStockMap.get("tran_id").toString();
    		}
    		if(updateStockMap.get("acct_code__cr")!=null)
    		{
    			this.acctCodeCr = updateStockMap.get("acct_code__cr").toString();
    		}
    		if(updateStockMap.get("acct_code__dr") != null)
    		{
    			this.acctCodeDr = updateStockMap.get("acct_code__dr").toString();
    		}
    		if(updateStockMap.get("cctr_code__cr") != null)
    		{
    			this.cctrCodeCr = 	updateStockMap.get("cctr_code__cr").toString();
    		}
    		if(updateStockMap.get("cctr_code__dr") != null)
    		{
    			this.cctrCodeDr = updateStockMap.get("cctr_code__dr").toString();
    		}
    		if(updateStockMap.get("line_no")!=null)
    		{
    			this.lineNo = updateStockMap.get("line_no").toString();
    		}
    		if(updateStockMap.get("sorder_no")!=null)
    		{
    			this.sorderNo = updateStockMap.get("sorder_no").toString();
    		}
    		if(updateStockMap.get("qty_stduom")!=null)
    		{
    			this.qtyStduom = Double.parseDouble(updateStockMap.get("qty_stduom").toString());
    		}
    		if(updateStockMap.get("rate")!=null)
    		{
    			this.rate = Double.parseDouble(updateStockMap.get("rate").toString());
    		}
    		if(updateStockMap.get("site_code__mfg")!= null)
    		{
    			this.siteCodeMfg = 	updateStockMap.get("site_code__mfg").toString();
    		}
    		if(updateStockMap.get("mfg_date")!=null)
    		{
    			this.mfgDate = 	(Timestamp)updateStockMap.get("mfg_date");
    		}
    		if(updateStockMap.get("potency_perc")!=null)
    		{
    			this.potencyPerc = 	Double.parseDouble(updateStockMap.get("potency_perc").toString());
    		}
    		if(updateStockMap.get("exp_date")!=null)
    		{
    			this.expDate = 	(Timestamp)updateStockMap.get("exp_date");
    		}
    		if(updateStockMap.get("pack_code")!=null)
    		{
    			this.packCode = 	updateStockMap.get("pack_code").toString();
    		}
    		if(updateStockMap.get("item_ser")!=null)
    		{
    			this.itemSer = 	updateStockMap.get("item_ser").toString();
    		}
    		if(updateStockMap.get("reas_code")!=null)
    		{
    			this.reasCode = updateStockMap.get("reas_code").toString();
    		}
    		if(updateStockMap.get("inv_stat")!=null)
    		{
    			this.invStat = 	updateStockMap.get("inv_stat").toString();
    		}
    		if(updateStockMap.get("net_amt")!=null)
    		{
    			this.netAmt = 	Double.parseDouble(updateStockMap.get("net_amt").toString());
    		}
    		if(updateStockMap.get("sundry_type")!=null)
    		{
    			this.sundryType = 	updateStockMap.get("sundry_type").toString();
    		}
    		if(updateStockMap.get("sundry_code")!=null)
    		{
    			this.sundryCode = 	updateStockMap.get("sundry_code").toString();
    		}
    		if(updateStockMap.get("curr_date")!= null)
    		{
    			this.currDate = (Timestamp)	updateStockMap.get("curr_date");
    		}
    		if(updateStockMap.get("gross_weight")!=null)
    		{
    			this.grossWeight = Double.parseDouble(updateStockMap.get("gross_weight").toString());
    		}
    		if(updateStockMap.get("tare_weight")!=null)
    		{
    			this.tareWeight = Double.parseDouble(updateStockMap.get("tare_weight").toString());
    		}
    			if(updateStockMap.get("net_weight")!=null)
    		{
    				this.netWeight = Double.parseDouble(updateStockMap.get("net_weight").toString());
    		}
    		if(updateStockMap.get("pack_instr")!=null)
    		{
    			this.packInstr = updateStockMap.get("pack_instr").toString();
    		}
    		if(updateStockMap.get("dimension")!=null)
    		{
    			this.dimension = 	updateStockMap.get("dimension").toString();
    		}
    		if(updateStockMap.get("acct_code_inv")!=null)
    		{
    			this.acctCodeInv = 	updateStockMap.get("acct_code_inv").toString();
    		}
    		if(updateStockMap.get("cctr_code_inv")!=null)
    		{
    			this.cctrCodeInv = 	updateStockMap.get("cctr_code_inv").toString();
    		}
    		if(updateStockMap.get("rate_oh")!= null)
    		{
    			this.rateOh = 	Double.parseDouble(updateStockMap.get("rate_oh").toString());
    		}
    		if(updateStockMap.get("acct_code_oh")!= null)
    		{
    			this.acctCodeOh = 	updateStockMap.get("acct_code_oh").toString();
    		}
    		if(updateStockMap.get("cctr_code_oh")!=null)
    		{
    			this.cctrCodeOh = 	updateStockMap.get("cctr_code_oh").toString();
    		}
    		if(updateStockMap.get("retest_date")!=null)
    		{
    			this.retestDate = (Timestamp)updateStockMap.get("retest_date");
    		}
    		if(updateStockMap.get("supp_code__mfg")!=null)
    		{
    			this.suppCodeMfg = 	updateStockMap.get("supp_code__mfg").toString();
    		}
    		if(updateStockMap.get("grade")!=null)
    		{
    			this.grade = updateStockMap.get("grade").toString();
    		}
    		if(updateStockMap.get("gross_rate")!=null)
    		{
    			this.grossRate = Double.parseDouble(updateStockMap.get("gross_rate").toString());
    		}
    		if(updateStockMap.get("conv__qty_stduom")!=null)
    		{
    			this.convQtyStduom = Double.parseDouble(updateStockMap.get("conv__qty_stduom").toString());
    		}
    		if(updateStockMap.get("batch_no")!= null)
    		{
    			this.batchNo = 	updateStockMap.get("batch_no").toString();
    		}
    		if(updateStockMap.get("unit__alt")!= null)
    		{
    			this.unitAlt = 	updateStockMap.get("unit__alt").toString();
    		}
    		if(updateStockMap.get("no_art")!=null)
    		{
    			this.noArt = Double.parseDouble(updateStockMap.get("no_art").toString());
    		}
    		if(updateStockMap.get("last_phyc_date")!=null)
    		{
    			this.lastPhycDate = (Timestamp)updateStockMap.get("last_phyc_date");
    		}
    		if(updateStockMap.get("remarks")!=null)
    		{
    			this.remarks = 	updateStockMap.get("remarks").toString();
    		}
    		if(updateStockMap.get("ref_id__for")!=null)
    		{
    			this.refIdFor = updateStockMap.get("ref_id__for").toString();
    		}
    		if(updateStockMap.get("ref_ser__for")!=null)
    		{
    			this.refSerFor = updateStockMap.get("ref_ser__for").toString();
    		}
    		if(updateStockMap.get("actual_rate")!=null)
    		{
    			this.actualRate = Double.parseDouble(updateStockMap.get("actual_rate").toString());
    		}
            if(updateStockMap.get("pack_ref")!= null)
            {
                this.packRef = updateStockMap.get("pack_ref").toString();
            }
            if(updateStockMap.get("consider_allocate")!= null)
            {
                this.considerAllocate = updateStockMap.get("consider_allocate").toString();
            }
            if(updateStockMap.get("partial_used")!= null)
            {
                this.partialUsed = updateStockMap.get("partial_used").toString();
            }
    		if(updateStockMap.get("batch_size")!=null)
    		{
    			this.batchSize = 	Double.parseDouble(updateStockMap.get("batch_size").toString());
    		}

        }catch(Exception e){
            System.out.println("Exception "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }
	}
    //END OF PUPLATECOMMONPARAMETERS 
	
	//********************************************Generate the TranId
	private String generateTranId(String windowName,String xmlValues,Connection conn) throws ITMException, Exception
	{
        System.out.println("Generating tran ID.....");
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		String tranId = "";
		String newKeystring = "";
		String srType = "RS";
		 try
	     {
	    	sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW)= '"+windowName+"'";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rs.next())
			{
				keyString = rs.getString(1);
				keyCol = rs.getString(2);
				tranSer1 = rs.getString(3);				
			}
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "SYSTEM", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +sql+ 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(stmt != null){stmt.close();stmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
		return tranId;
	}//generateTranTd()
    
    private String adjustStkValue(Connection conn) throws ITMException, Exception
    {
        String prdCode = "",sql = "";
        String invLink = "",errString = "",acctAdj = "",cctrAdj = "";
        double effQty = 0,afterQty = 0,beforeQty = 0;
        String updStkCctrCodeInv = "";
        String remarks = "";
        String refSer = "",adjWin = "";
        String acctCodeCr = "",cctrCodeCr = "",acctCodeDr = "",cctrCodeDr = "";
        FinCommon finCommon = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        String keyString = "",keyCol = "",tranSer = "";
        String invTraceNo = "",tranIDAdj = "",finEntity = "",currCode = "";
        double exchRate = 0;
        HashMap glTrace = null;
        try{
            finCommon = new FinCommon();
            updStkCctrCodeInv = this.cctrCodeInv;
            stmt = conn.createStatement();
            /*if gs_run_mode <> 'B' then
                s_updatestock.currdate = ldt_today
                s_updatestock.trandate = ldt_today
            else
                if isnull(s_updatestock.currdate) then
                    s_updatestock.currdate = ldt_today
                    s_updatestock.trandate = ldt_today
                else
                    s_updatestock.trandate = s_updatestock.currdate
                    end if
            end if 
            ls_prdcode = string(s_updatestock.trandate,'yyyymm')*/
            
            invLink = finCommon.getFinparams("999999", "INV_ACCT_AISS",conn);
            System.out.println("invLink :"+invLink);
            if(invLink == null || invLink.equalsIgnoreCase("NULLFOUND")){
                errString = itmDBAccessEJB.getErrorString("","VTFINPARM1","","",conn);
                return errString;
            }
            acctAdj = finCommon.getFinparams("999999", "CONS_ACCT_RNDOFF",conn);
            System.out.println("acctAdj :"+acctAdj);
            if(acctAdj == null || acctAdj.equalsIgnoreCase("") || acctAdj.equalsIgnoreCase("NULLFOUND")){
                errString = itmDBAccessEJB.getErrorString("","VTFINPARM1","","",conn);
                return errString;
            }
            cctrAdj = finCommon.getFinparams("999999", "CONS_CCTR_RNDOFF",conn);
            if(cctrAdj == null || cctrAdj.equalsIgnoreCase("") || cctrAdj.equalsIgnoreCase("NULLFOUND")){
                cctrAdj = "    ";
            }
            if(updStkCctrCodeInv == null || updStkCctrCodeInv.trim().length() == 0){
                updStkCctrCodeInv = "   ";
            }
            remarks = "Adjustment of Stock Value by System";
            
            if(this.netAmt > 0){ // adj_rcp
                refSer = "ADJRCP";
                adjWin = "w_adj_rcp";
                acctCodeDr = this.acctCodeInv;
                cctrCodeDr = this.cctrCodeInv;
                acctCodeCr = acctAdj;   
                cctrCodeCr =  cctrAdj;  
            }else{//adj_rcp
                refSer = "ADJISS";
                adjWin = "w_adj_iss";
                acctCodeDr = acctAdj;
                cctrCodeDr = cctrAdj;
                acctCodeCr = this.acctCodeInv;   
                cctrCodeCr = this.cctrCodeInv;  
            }
            sql = "select key_string from transetup where tran_window = '"+adjWin+"'";
            System.out.println("SQL :"+sql);
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                keyString = rs.getString(1);
            }
            if(keyString == null || keyString.trim().length() == 0){
                sql = "select key_string from transetup where tran_window = 'GENERAL'";
                System.out.println("SQL :"+sql);
                rs = stmt.executeQuery(sql);
                if(rs.next()){
                    keyString = rs.getString(1);
                }
            }
            
            String tranDate = genericUtility.getValidDateString(new java.sql.Date(this.tranDate.getTime()).toString(),genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat());
            String XMLString = "";
            XMLString = "<?xml version=\"1.0\"?>\r\n<Root>\r\n<header>"+
                        "\r\n</header><Detail1><tran_id></tran_id><tran_date>"+tranDate+"</tran_date>"+
                        "\r\n<site_code>"+this.siteCode+"</site_code>"+
                        "</Detail1></Root>";
            TransIDGenerator tg = new TransIDGenerator(XMLString, userId, CommonConstants.DB_NAME);
            tranIDAdj = tg.generateTranSeqID(refSer, "tran_id", keyString, conn);
            
            keyString = "";
            sql = "select key_string from transetup where tran_window = 'T_INVTRACE'";
            System.out.println("SQL :"+sql);
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                keyString = rs.getString(1);                
            }
            rs.close();
            
            
            TransIDGenerator tg1 = new TransIDGenerator(XMLString, userId, CommonConstants.DB_NAME);
            invTraceNo = tg1.generateTranSeqID("ITRACE", "tran_id", keyString, conn);
            
           sql = "Insert into invtrace (tran_id, tran_date, ref_ser, ref_id, ref_line, order_id, " +
                "item_code, site_code, loc_code, unit, lot_no, quantity, eff_qty, eff_date, " +
                "chg_win,rate, gross_rate, amount,chg_user, chg_term, lot_sl, qty_before, " +
                "qty_after,reas_code,sundry_type,sundry_code, ref_date,remarks," +
                "acct_code__dr, cctr_code__dr, acct_code__cr, cctr_code__cr, grade) " +
                "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               
           System.out.println("SQL :"+sql);
           pstmt = conn.prepareStatement(sql);
           pstmt.setString(1,invTraceNo);
           pstmt.setTimestamp(2,this.currDate);
           pstmt.setString(3,refSer);
           pstmt.setString(4,this.tranId);
           pstmt.setString(5,this.lineNo);
           pstmt.setString(6,this.sorderNo);
           pstmt.setString(7,this.itemCode);
           pstmt.setString(8,this.siteCode);
           pstmt.setString(9,this.locationCode);
           pstmt.setString(10,this.unit);
           pstmt.setString(11,this.lotNo);
           pstmt.setDouble(12,this.qtyStduom);
           pstmt.setDouble(13,effQty); 
           pstmt.setTimestamp(14,this.tranDate);
           pstmt.setString(15," ");
           pstmt.setDouble(16,this.rate);
           pstmt.setDouble(17,this.rate);
           pstmt.setDouble(18,this.netAmt);
           pstmt.setString(19,userId);
           pstmt.setString(20,termId);
           pstmt.setString(21,this.lotSl);
           pstmt.setDouble(22,beforeQty);
           pstmt.setDouble(23,afterQty);
           pstmt.setString(24,this.reasCode);
           pstmt.setString(25,this.sundryType);
           pstmt.setString(26,this.sundryCode);
           pstmt.setTimestamp(27,this.tranDate);
           pstmt.setString(28,remarks);
           pstmt.setString(29,acctCodeDr);
           pstmt.setString(30,cctrCodeDr);
           pstmt.setString(31,acctCodeCr);
           pstmt.setString(32,cctrCodeCr);
           pstmt.setString(33,this.grade);
           
           System.out.println("TRAN_ID :"+invTraceNo);
           System.out.println("TRAN_DATE :"+this.currDate);
           System.out.println("ITEM_CODE :"+this.itemCode);
           System.out.println("SITE_CODE :"+this.siteCode);
           System.out.println("LOC_CODE :["+this.locationCode+"]");
           System.out.println("UNIT :"+this.unit);
           System.out.println("EFF_DATE :"+this.tranDate);
           System.out.println("CHG_USER :");
           System.out.println("CHG_TERM :");
           
           pstmt.executeUpdate();
           
           sql = "select fin_entity from site where site_code = '"+this.siteCode+"'";
           System.out.println("SQL :"+sql);
           rs = stmt.executeQuery(sql);
           if(rs.next()){
               finEntity = rs.getString(1);
           }
           rs.close();
           
           sql = "select curr_code from parameter";
           System.out.println("SQL :"+sql);
           rs = stmt.executeQuery(sql);
           if(rs.next()){
               currCode = rs.getString(1);
           }
           rs.close();
           exchRate = 1;
           System.out.println("this.netAmt : "+this.netAmt);
           if(invLink.equalsIgnoreCase("Y")){
               if(this.netAmt != 0){
                   glTrace = new HashMap();
                   glTrace.put("tran_date",this.tranDate);
                   glTrace.put("eff_date",this.tranDate);
                   glTrace.put("fin_entity",finEntity);
                   glTrace.put("site_code",this.siteCode);
                   glTrace.put("sundry_type","O");
                   glTrace.put("sundry_code","");
                   glTrace.put("emp_code","");
                   glTrace.put("anal_code","");
                   glTrace.put("curr_code",currCode);
                   glTrace.put("exch_rate","1");
                   
                   if(this.netAmt > 0){
                       glTrace.put("dr_amt","0");
                       glTrace.put("cr_amt",Double.toString(this.netAmt));
                       glTrace.put("acct_code",acctAdj);
                       glTrace.put("cctr_code",cctrAdj);
                   }else{
                       glTrace.put("dr_amt","0");
                       glTrace.put("cr_amt",Double.toString( 0 - this.netAmt));
                       glTrace.put("acct_code",this.acctCodeInv);
                       glTrace.put("cctr_code",this.cctrCodeInv);
                   }
                   glTrace.put("ref_type","D");
                   glTrace.put("remarks",remarks);
                   glTrace.put("ref_ser",refSer);
                   glTrace.put("ref_id",tranIDAdj);
                   
                   errString = finCommon.glTraceUpdate(glTrace,conn);
                   System.out.println("Returning String :"+errString);
                   if(errString.trim().length() > 0){
                       return errString;
                   }
                   if(this.netAmt > 0){
                       glTrace.put("dr_amt",Double.toString(this.netAmt));
                       glTrace.put("cr_amt","0");
                       glTrace.put("acct_code",this.acctCodeInv);
                       glTrace.put("cctr_code",this.cctrCodeInv);
                   }else{
                       glTrace.put("dr_amt",Double.toString(0 - this.netAmt));
                       glTrace.put("cr_amt","0");
                       glTrace.put("acct_code",acctAdj);
                       glTrace.put("cctr_code",cctrAdj);
                   }
                   errString = finCommon.glTraceUpdate(glTrace,conn);
                   System.out.println("Returning String :"+errString);
                   if(errString.trim().length() > 0){
                       return errString;
                   }
                   
                   errString = finCommon.checkGlTranDrCr(refSer,tranIDAdj,conn);
                   
               }
           }
           
        }catch(Exception e){
            System.out.println("Exception e "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }finally{
            try{
                if(rs != null){rs.close();rs = null;}
                if(stmt != null){stmt.close();stmt = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        System.out.println("Returning Value from adjustStkValue : "+errString);
        return errString;
    }
    private String updateStockMovement(Connection conn) throws ITMException, Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = 0;
        String prdCode = "",errCode = "", sql = "";
        double qty = 0,amount = 0;
        double poRcpQty = 0,disRcpQty = 0,woIssQty = 0,disIssQty = 0,salIssQty = 0;
        double poRcpValue = 0,disRcpValue = 0,woIssValue = 0,disIssValue = 0,salIssValue = 0;
        double poRetQty = 0,conIssQty = 0,sretRcpQty = 0,sretIssQty = 0,adjQty = 0;
        double poRetValue = 0,conIssValue = 0,sretRcpValue = 0,sretIssValue = 0,adjValue = 0;
        double totRcpQty = 0,totIssQty = 0,openQty = 0,closeQty = 0;
        double totRcpValue = 0,totIssValue = 0,openValue = 0,closeValue = 0;
        try{
            qty = this.qtyStduom;
            amount = qty * this.rate;
            if(this.tranSer.equalsIgnoreCase("P-RCP")){
                poRcpQty = poRcpQty + qty;
                poRcpValue = poRcpValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("D-RCP")){
                disRcpQty = disRcpQty + qty;
                disRcpValue = disRcpValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("W-ISS")){
                woIssQty = woIssQty + qty;
                woIssValue = woIssValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("D-ISS")){
                disIssQty = disIssQty + qty;
                disIssValue = disIssValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("S-DSP")){
                salIssQty = salIssQty + qty;
                salIssValue = salIssValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("P-RET")){
                poRetQty = poRetQty + qty;
                poRetValue = poRetValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("C-ISS")){
                conIssQty = conIssQty + qty;
                conIssValue = conIssValue + amount;
            }else if(this.tranSer.equalsIgnoreCase("S-RET")){
                if(this.tranType.equalsIgnoreCase("R")){
                    sretRcpQty = sretRcpQty + qty;
                    sretRcpValue = sretRcpValue + amount;
                }else{
                    sretIssQty = sretIssQty + qty;
                    sretIssValue = sretIssValue + amount;
                }
            }else if(this.tranSer.equalsIgnoreCase("ADJRCP")){
                adjQty = adjQty + qty;
                adjValue = adjValue + amount;
            }
            if(this.tranType.equalsIgnoreCase("R")){
                totRcpQty = totRcpQty + qty;
                totRcpValue = totRcpValue + amount;
            }else{
                totIssQty = totIssQty + qty;
                totIssValue = totIssValue + amount;
            }
            sql = "select sum(qty),sum(val) from (select sum(a.quantity) qty,sum(a.quantity * case when a.rate is null then 0 else a.rate end) val     " +
                    "from  stock a , invstat b , location c " +
                    "where a.loc_code   = c.loc_code " +
                    "and   b.inv_stat       = c.inv_stat " +
                    "and   a.item_code      = '"+this.itemCode+"' " +
                    "and   a.site_code      = '"+this.siteCode+"' " +
                    "and   a.lot_no     = '"+this.lotNo+"' " +
                    "union all " +
                    "select ( case when (sum(eff_qty) * -1) is null then 0 else (sum(eff_qty) * -1) end ) qty , " +
                    "( case when (sum(eff_qty * case when rate is null then 0 else rate end) * -1) is null then 0 else (sum(eff_qty * case when rate is null then 0 else rate end) * -1) end ) val " +
                    "from  invtrace a , location b , invstat c , refser d " +
                    "where a.loc_code       = b.loc_code " +
                    "and   a.ref_ser    = d.ref_ser " +
                    "and   b.inv_stat   = c.inv_stat " +
                    "and   a.item_code  = '"+this.itemCode+"' " +
                    "and   a.site_code  = '"+this.siteCode+"' " +
                    "and   a.lot_no     = '"+this.lotNo+"' " +
                    "and   a.tran_date  > ? )";
                    //"and   a.tran_date  > ? ) as stk";
                    System.out.println("SQL :"+sql);
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setTimestamp(1,this.tranDate);
                    rs = pstmt.executeQuery();
                    if(rs.next()){
                        openQty = rs.getDouble(1);
                        openValue = rs.getDouble(2);
                    }
                    closeQty = openQty + qty;
                    closeValue = openValue+ amount;
                    rs.close();
                    pstmt.close();
                    
                    sql = "SELECT CODE FROM PERIOD WHERE ? BETWEEN FR_DATE AND TO_DATE";
                    System.out.println("SQL :"+sql);
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setTimestamp(1,this.tranDate);
                    rs = pstmt.executeQuery();
                    if(rs.next()){
                        prdCode = rs.getString("CODE");
                    }
                    rs.close();
                    pstmt.close();
                    
                    sql = "select count(*) from stock_movement " +
                            "where site_code = '"+this.siteCode+"' and period_code = '"+prdCode+"' " +
                            "and item_code = '"+this.itemCode+"' and lot_no = '"+this.lotNo+"'";
                    System.out.println("SQL :"+sql);
                    pstmt = conn.prepareStatement(sql);
                    rs = pstmt.executeQuery();
                    if(rs.next()){
                        count = rs.getInt(1);
                    }
                    rs.close();
                    pstmt.close();
                    if(count == 0){
                        sql = "insert into stock_movement (site_code,period_code,item_code,lot_no," +
                                "open_qty,open_value,po_rcp_qty,po_rcp_value,dis_rcp_qty,dis_rcp_value," +
                                "sret_rcp_qty,sret_rcp_value,tot_rcp_qty,tot_rcp_value,wo_iss_qty,wo_iss_value," +
                                "dis_iss_qty,dis_iss_value,sal_iss_qty,sal_iss_value,po_ret_qty,po_ret_value," +
                                "sret_iss_qty,sret_iss_value,con_iss_qty,con_iss_value,pm_iss_qty,pm_iss_value," +
                                "tot_iss_qty,tot_iss_value,adj_qty,adj_value,close_qty,close_value) values (?,?,?,?,?,?," +
                                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                        System.out.println("SQL :"+sql);
                        pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1,this.siteCode);
                        pstmt.setString(2,prdCode);
                        pstmt.setString(3,this.itemCode);
                        pstmt.setString(4,this.lotNo);
                        pstmt.setDouble(5,openQty);
                        pstmt.setDouble(6,openValue);
                        pstmt.setDouble(7,poRcpQty);
                        pstmt.setDouble(8,poRcpValue);
                        pstmt.setDouble(9,disRcpQty);
                        pstmt.setDouble(10,disRcpValue);
                        pstmt.setDouble(11,sretRcpQty);
                        pstmt.setDouble(12,sretRcpValue);
                        pstmt.setDouble(13,totRcpQty);
                        pstmt.setDouble(14,totRcpValue);
                        pstmt.setDouble(15,woIssQty);
                        pstmt.setDouble(16,woIssValue);
                        pstmt.setDouble(17,disIssQty);
                        pstmt.setDouble(18,disIssValue);
                        pstmt.setDouble(19,salIssQty);
                        pstmt.setDouble(20,salIssValue);
                        pstmt.setDouble(21,poRetQty);
                        pstmt.setDouble(22,poRetValue);
                        pstmt.setDouble(23,sretIssQty);
                        pstmt.setDouble(24,sretIssValue);
                        pstmt.setDouble(25,conIssQty);
                        pstmt.setDouble(26,conIssValue);
                        pstmt.setDouble(27,0);
                        pstmt.setDouble(28,0);
                        pstmt.setDouble(29,totIssQty);
                        pstmt.setDouble(30,totIssValue);
                        pstmt.setDouble(31,adjQty);
                        pstmt.setDouble(32,adjValue);
                        pstmt.setDouble(33,closeQty);
                        pstmt.setDouble(34,closeValue);
                        
                        pstmt.executeUpdate();
                        
                    }else{
                        sql = "update stock_movement " +
                                "set open_qty = ?, " +
                                "open_value = ?," +
                                "po_rcp_qty = ?," +
                                "po_rcp_value = ?," +
                                "dis_rcp_qty = ?," +
                                "dis_rcp_value = ?," +
                                "sret_rcp_qty = ?," +
                                "sret_rcp_value = ?," +
                                "tot_rcp_qty = ?," +
                                "tot_rcp_value = ?," +
                                "wo_iss_qty = ?," +
                                "wo_iss_value = ?," +
                                "dis_iss_qty = ?," +
                                "dis_iss_value = ?," +
                                "sal_iss_qty = ?," +
                                "sal_iss_value = ?," +
                                "po_ret_qty = ?," +
                                "po_ret_value = ?," +
                                "sret_iss_qty = ?," +
                                "sret_iss_value = ?," +
                                "con_iss_qty = ?," +
                                "con_iss_value = ?," +
                                "pm_iss_qty = ?," +
                                "pm_iss_value = ?," +
                                "tot_iss_qty = ?," +
                                "tot_iss_value = ?," +
                                "adj_qty = ?," +
                                "adj_value = ?," +
                                "close_qty = ?," +
                                "close_value = ? " +
                                "where site_code = ? " +
                                "and period_code = ? " +
                                "and item_code = ? " +
                                "and lot_no = ? ";
                        System.out.println("SQL :: "+sql);
                        pstmt = conn.prepareStatement(sql);
                        pstmt.setDouble(1,openQty);
                        pstmt.setDouble(2,openValue);
                        pstmt.setDouble(3,poRcpQty);
                        pstmt.setDouble(4,poRcpValue);
                        pstmt.setDouble(5,disRcpQty);
                        pstmt.setDouble(6,disRcpValue);
                        pstmt.setDouble(7,sretRcpQty);
                        pstmt.setDouble(8,sretRcpValue);
                        pstmt.setDouble(9,totRcpQty);
                        pstmt.setDouble(10,totRcpValue);
                        pstmt.setDouble(11,woIssQty);
                        pstmt.setDouble(12,woIssValue);
                        pstmt.setDouble(13,disIssQty);
                        pstmt.setDouble(14,disIssValue);
                        pstmt.setDouble(15,salIssQty);
                        pstmt.setDouble(16,salIssValue);
                        pstmt.setDouble(17,poRetQty);
                        pstmt.setDouble(18,poRetValue);
                        pstmt.setDouble(19,sretIssQty);
                        pstmt.setDouble(20,sretIssValue);
                        pstmt.setDouble(21,conIssQty);
                        pstmt.setDouble(22,conIssValue);
                        pstmt.setDouble(23,0);
                        pstmt.setDouble(24,0);
                        pstmt.setDouble(25,totIssQty);
                        pstmt.setDouble(26,totIssValue);
                        pstmt.setDouble(27,adjQty);
                        pstmt.setDouble(28,adjValue);
                        pstmt.setDouble(29,closeQty);
                        pstmt.setDouble(30,closeValue);
                        pstmt.setString(31,this.siteCode);
                        pstmt.setString(32,prdCode);
                        pstmt.setString(33,this.itemCode);
                        pstmt.setString(34,this.lotNo);
                        
                        pstmt.executeUpdate();
                    }
        }catch(Exception e){
            System.out.println("Exception "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }finally{
            try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        return  "";
    }
    private String calcWeights(Connection conn) throws ITMException, Exception
    {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String issCriteria = "";
        String sql = "";
        double stockQty = 0,stkGrossWt = 0,stkNetWt = 0,stkTareWt = 0,qtyPerArt = 0;
        double grossWtPerArt = 0,tareWtPerArt = 0;
        long stkNoArt = 0;
        try{
            if(this.tranSer.equalsIgnoreCase("S-ISS")){
                return "";
            }
            sql = "select case when iss_criteria is null then 'I' else iss_criteria end from item where item_code = '"+this.itemCode+"'";
            System.out.println("SQL : "+sql);
            pstmt = conn.prepareStatement(sql);
            
            rs = pstmt.executeQuery();
            if(rs.next()){
                issCriteria = rs.getString(1);
            }
            rs.close();
            pstmt.close();
            sql = "select quantity,gross_weight, net_weight, tare_weight, no_art," +
                    "qty_per_art, gross_wt_per_art, tare_wt_per_art " +
                    "from stock " +
                    "where item_code = '"+this.itemCode+"' and " +
                    "site_code = '"+this.siteCode+"' and loc_code = '"+this.locationCode+"' "+
                    "and lot_no = '"+this.lotNo+"' and lot_sl = '"+this.lotSl+"'";        
                
            System.out.println("SQL :"+sql);
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                stockQty = rs.getDouble(1);
                stkGrossWt = rs.getDouble(2);
                stkNetWt = rs.getDouble(3);
                stkTareWt = rs.getDouble(4);
                stkNoArt = rs.getLong(5);
                qtyPerArt = rs.getDouble(6);
                grossWtPerArt = rs.getDouble(7);
                tareWtPerArt = rs.getDouble(8);
            }
            rs.close();
            pstmt.close();
            if(issCriteria != null && issCriteria.trim().equalsIgnoreCase("W")){
                if(stockQty > 0){
                    this.netWeight = stkNetWt / stockQty * this.qtyStduom;
                    this.grossWeight = this.netWeight + this.tareWeight;
                }
            }else{
                if(qtyPerArt == 0){
                    if(stkNoArt == 0) stkNoArt = 1;
                    qtyPerArt = stockQty / stkNoArt;
                }
                if(qtyPerArt != 0){
                    if(stockQty > 0 && this.qtyStduom > 0){
                        this.netWeight = stkNetWt / stockQty * this.qtyStduom;
                    }
                    this.tareWeight = tareWtPerArt * this.noArt;
                    this.grossWeight = this.netWeight + this.tareWeight;
                }
            }
        }catch(Exception e){
            System.out.println("Exception e "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }finally{
            try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        return "";
    }
    
	public String checkStkOptRnd(String siteCode,String itemCode,Connection conn) throws ITMException, Exception
	{
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String varValue=null;
		String lsStkOpt = null;
		GenericUtility genericUtility = new GenericUtility();
        ArrayList siteArrList = null;
        String paramSite = "";
    	try{
            siteArrList = new ArrayList();
    		String sql="SELECT VAR_VALUE FROM MFGPARM WHERE PRD_CODE = '999999' AND VAR_NAME = 'RND_NO_STK_SITE' ";
            System.out.println("SQL :"+sql);
    		pstmt = conn.prepareStatement(sql);
    		rs = pstmt.executeQuery();
            if(rs.next()){
                varValue = rs.getString(1);
            }
            if(varValue != null && varValue.trim().length() > 0){
                siteArrList = genericUtility.getTokenList(varValue,",");
            }
            if(siteArrList.size() > 0){
                for(int i = 0;i < siteArrList.size();i++){
                    paramSite = siteArrList.get(i).toString();
                    if(paramSite.equalsIgnoreCase(siteCode)){
                        lsStkOpt = "0";
                        break;
                    }
                }
            }
        }catch(Exception e){
            System.out.println("Exception checkStkOptRnd "+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 t){throw new ITMException(t);}
        }
        System.out.println("Returning Stock Option RND :"+lsStkOpt);
        return lsStkOpt;
	}
	
	//*****************************
    /**
     * checks stock update option
     *
     * @param     itemCode  Item code
     * @param     siteCode Site code
     * @param	  conn   Database connection
     * @return    Error code if fails
     * @exception ITMException 
     */
	public String checkStkOpt(String itemCode,String siteCode,Connection conn) throws ITMException, Exception
	{
		String stkOpt=null;
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String sql=null;
		try{
            sql = "SELECT CASE WHEN STK_OPT IS NULL THEN 'N' ELSE STK_OPT END "
			+" FROM SITEITEM WHERE ITEM_CODE='"+itemCode+"' AND SITE_CODE='"+siteCode+"'";
            System.out.println("SQL :"+sql);
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				stkOpt=rs.getString(1);
			}
			rs.close();
			pstmt.close();
			if(stkOpt == null || stkOpt.equals("") || stkOpt.equalsIgnoreCase("N")){
				sql = "SELECT CASE WHEN STK_OPT IS NULL THEN '0' ELSE STK_OPT END "
					+" FROM ITEM WHERE ITEM_CODE='"+itemCode+"' ";
                System.out.println("SQL :"+sql);
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				if(rs.next()){
					stkOpt=rs.getString(1);
				}
			}
		}catch (Exception e){
			System.out.println("Exception in checkStkOpt "+e);            
            e.printStackTrace();
            throw new ITMException(e);
		}finally{
		    try{
                if(rs != null){rs.close();rs = null;}
                if(pstmt != null){pstmt.close();pstmt = null;}
            }catch(Exception t){throw new ITMException(t);}
        }
        System.out.println("Returning Stock Option : "+stkOpt);
		return stkOpt;
	}	
	
	// added by cpatil on 16/11/12 start adding stock update function
	private HashMap updateLotInfo(HashMap updateStockMap ,Connection conn) throws ITMException, Exception
	{		
       String sql="",errCode="";
       ResultSet rs=null;
       PreparedStatement pstmt=null;
       String shelfLifeType="",lotNo="",itemCode="",grade="",stkOpt="";
       int cnt=0,cnt1=0;
       Timestamp expDate=null , mfgDate=null, retestDate=null;
       //Date today=null;
       double grossrate=0;
       String packCode="",siteCodeMfg="",acctCodeInv="",cctrCodeInv="",batchNo="",suppCodeMfg="",suppCode="";
       double  potencyPerc=0 , grossRate=0 , rate=0 , batchSize=0;
       java.util.Date retestDate3=null,expDate3=null,mfgDate3=null;
       Calendar currentDate = Calendar.getInstance();
       SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
       Timestamp today = Timestamp.valueOf(simpleDateFormat.format(simpleDateFormat.parse(simpleDateFormat.format(currentDate.getTime()))).toString() + " 00:00:00.0");
       System.out.println("Now the date is :=>  " + today);
		
      // today  = new java.sql.Date(System.currentTimeMillis());
       System.out.println("today:::["+today+"]");
       
       
       
       System.out.println("@@@@@ updateLotInfo() method called..............");
       
       System.out.println("Comming Map :::- ["+updateStockMap+"]");

	try{
                    
    		if(updateStockMap.get("item_code")!= null)
    		{
    			this.itemCode = updateStockMap.get("item_code").toString();
    		}
    		if(updateStockMap.get("site_code")!= null)
    		{
    			this.siteCode = updateStockMap.get("site_code").toString();
    		}
    		if(updateStockMap.get("loc_code")!= null)
    		{
    			this.locationCode = updateStockMap.get("loc_code").toString();
    		}
    		if(updateStockMap.get("lot_no")!= null)
    		{
    			this.lotNo = updateStockMap.get("lot_no").toString();
    		}
    		if(updateStockMap.get("lot_sl")!= null)
    		{
    			this.lotSl = updateStockMap.get("lot_sl").toString();
    		}
    		if(updateStockMap.get("quantity")!= null)
    		{
    			this.quantity = Double.parseDouble(updateStockMap.get("quantity").toString());
    		}
    		if(updateStockMap.get("unit") != null)
    		{
    			this.unit = updateStockMap.get("unit").toString();
    		}
    		if(updateStockMap.get("tran_type")!= null)
    		{
    			this.tranType = updateStockMap.get("tran_type").toString();
    		}
    		if(updateStockMap.get("check_expiry")!= null)
    		{
    			this.checkExpiry = updateStockMap.get("check_expiry").toString();
    		}
    		if(updateStockMap.get("tran_date")!= null)
    		{
    			this.tranDate = (Timestamp)updateStockMap.get("tran_date");			
    		}
    		if(updateStockMap.get("tran_ser") != null)
    		{
    			this.tranSer = updateStockMap.get("tran_ser").toString();
    		}
    		if(updateStockMap.get("tran_id")!=null)
    		{
    			this.tranId = updateStockMap.get("tran_id").toString();
    		}
    		if(updateStockMap.get("acct_code__cr")!=null)
    		{
    			this.acctCodeCr = updateStockMap.get("acct_code__cr").toString();
    		}
    		if(updateStockMap.get("acct_code__dr") != null)
    		{
    			this.acctCodeDr = updateStockMap.get("acct_code__dr").toString();
    		}
    		if(updateStockMap.get("cctr_code__cr") != null)
    		{
    			this.cctrCodeCr = 	updateStockMap.get("cctr_code__cr").toString();
    		}
    		if(updateStockMap.get("cctr_code__dr") != null)
    		{
    			this.cctrCodeDr = updateStockMap.get("cctr_code__dr").toString();
    		}
    		if(updateStockMap.get("line_no")!=null)
    		{
    			this.lineNo = updateStockMap.get("line_no").toString();
    		}
    		if(updateStockMap.get("sorder_no")!=null)
    		{
    			this.sorderNo = updateStockMap.get("sorder_no").toString();
    		}
    		if(updateStockMap.get("qty_stduom")!=null)
    		{
    			this.qtyStduom = Double.parseDouble(updateStockMap.get("qty_stduom").toString());
    		}
    		if(updateStockMap.get("rate")!=null)
    		{
    			this.rate = Double.parseDouble(updateStockMap.get("rate").toString());
    		}
    		if(updateStockMap.get("site_code__mfg")!= null)
    		{
    			this.siteCodeMfg = 	updateStockMap.get("site_code__mfg").toString();
    		}
    		if(updateStockMap.get("mfg_date")!=null)
    		{
    			this.mfgDate = 	(Timestamp)updateStockMap.get("mfg_date");
    		}
    		if(updateStockMap.get("potency_perc")!=null)
    		{
    			this.potencyPerc = 	Double.parseDouble(updateStockMap.get("potency_perc").toString());
    		}
    		if(updateStockMap.get("exp_date")!=null)
    		{
    			this.expDate = 	(Timestamp)updateStockMap.get("exp_date");
    		}
    		if(updateStockMap.get("pack_code")!=null)
    		{
    			this.packCode = 	updateStockMap.get("pack_code").toString();
    		}
    		if(updateStockMap.get("item_ser")!=null)
    		{
    			this.itemSer = 	updateStockMap.get("item_ser").toString();
    		}
    		if(updateStockMap.get("reas_code")!=null)
    		{
    			this.reasCode = updateStockMap.get("reas_code").toString();
    		}
    		if(updateStockMap.get("inv_stat")!=null)
    		{
    			this.invStat = 	updateStockMap.get("inv_stat").toString();
    		}
    		if(updateStockMap.get("net_amt")!=null)
    		{
    			this.netAmt = 	Double.parseDouble(updateStockMap.get("net_amt").toString());
    		}
    		if(updateStockMap.get("sundry_type")!=null)
    		{
    			this.sundryType = 	updateStockMap.get("sundry_type").toString();
    		}
    		if(updateStockMap.get("sundry_code")!=null)
    		{
    			this.sundryCode = 	updateStockMap.get("sundry_code").toString();
    		}
    		if(updateStockMap.get("curr_date")!= null)
    		{
    			this.currDate = (Timestamp)	updateStockMap.get("curr_date");
    		}
    		if(updateStockMap.get("gross_weight")!=null)
    		{
    			this.grossWeight = Double.parseDouble(updateStockMap.get("gross_weight").toString());
    		}
    		if(updateStockMap.get("tare_weight")!=null)
    		{
    			this.tareWeight = Double.parseDouble(updateStockMap.get("tare_weight").toString());
    		}
    			if(updateStockMap.get("net_weight")!=null)
    		{
    				this.netWeight = Double.parseDouble(updateStockMap.get("net_weight").toString());
    		}
    		if(updateStockMap.get("pack_instr")!=null)
    		{
    			this.packInstr = updateStockMap.get("pack_instr").toString();
    		}
    		if(updateStockMap.get("dimension")!=null)
    		{
    			this.dimension = 	updateStockMap.get("dimension").toString();
    		}
    		if(updateStockMap.get("acct_code_inv")!=null)
    		{
    			this.acctCodeInv = 	updateStockMap.get("acct_code_inv").toString();
    		}
    		if(updateStockMap.get("cctr_code_inv")!=null)
    		{
    			this.cctrCodeInv = 	updateStockMap.get("cctr_code_inv").toString();
    		}
    		if(updateStockMap.get("rate_oh")!= null)
    		{
    			this.rateOh = 	Double.parseDouble(updateStockMap.get("rate_oh").toString());
    		}
    		if(updateStockMap.get("acct_code_oh")!= null)
    		{
    			this.acctCodeOh = 	updateStockMap.get("acct_code_oh").toString();
    		}
    		if(updateStockMap.get("cctr_code_oh")!=null)
    		{
    			this.cctrCodeOh = 	updateStockMap.get("cctr_code_oh").toString();
    		}
    		if(updateStockMap.get("retest_date")!=null)
    		{
    			this.retestDate = (Timestamp)updateStockMap.get("retest_date");
    		}
    		if(updateStockMap.get("supp_code__mfg")!=null)
    		{
    			this.suppCodeMfg = 	updateStockMap.get("supp_code__mfg").toString();
    		}
    		if(updateStockMap.get("grade")!=null)
    		{
    			this.grade = updateStockMap.get("grade").toString();
    		}
    		if(updateStockMap.get("gross_rate")!=null)
    		{
    			this.grossRate = Double.parseDouble(updateStockMap.get("gross_rate").toString());
    		}
    		if(updateStockMap.get("conv__qty_stduom")!=null)
    		{
    			this.convQtyStduom = Double.parseDouble(updateStockMap.get("conv__qty_stduom").toString());
    		}
    		if(updateStockMap.get("batch_no")!= null)
    		{
    			this.batchNo = 	updateStockMap.get("batch_no").toString();
    		}
    		if(updateStockMap.get("unit__alt")!= null)
    		{
    			this.unitAlt = 	updateStockMap.get("unit__alt").toString();
    		}
    		if(updateStockMap.get("no_art")!=null)
    		{
    			this.noArt = Double.parseDouble(updateStockMap.get("no_art").toString());
    		}
    		if(updateStockMap.get("last_phyc_date")!=null)
    		{
    			this.lastPhycDate = (Timestamp)updateStockMap.get("last_phyc_date");
    		}
    		if(updateStockMap.get("remarks")!=null)
    		{
    			this.remarks = 	updateStockMap.get("remarks").toString();
    		}
    		if(updateStockMap.get("ref_id__for")!=null)
    		{
    			this.refIdFor = updateStockMap.get("ref_id__for").toString();
    		}
    		if(updateStockMap.get("ref_ser__for")!=null)
    		{
    			this.refSerFor = updateStockMap.get("ref_ser__for").toString();
    		}
    		if(updateStockMap.get("actual_rate")!=null)
    		{
    			this.actualRate = Double.parseDouble(updateStockMap.get("actual_rate").toString());
    		}
            if(updateStockMap.get("pack_ref")!= null)
            {
                this.packRef = updateStockMap.get("pack_ref").toString();
            }
            if(updateStockMap.get("consider_allocate")!= null)
            {
                this.considerAllocate = updateStockMap.get("consider_allocate").toString();
            }
            if(updateStockMap.get("partial_used")!= null)
            {
                this.partialUsed = updateStockMap.get("partial_used").toString();
            }
    		if(updateStockMap.get("batch_size")!=null)
    		{
    			this.batchSize = Double.parseDouble(updateStockMap.get("batch_size").toString());
    		}
    		if(updateStockMap.get("supp_code")!=null)
    		{
    			this.suppCode = updateStockMap.get("supp_code").toString();
    		}
    		

    		stkOpt = checkStkOpt(this.itemCode,this.siteCode,conn);
			System.out.println("@@@@@ stkOpt[gfChkStkOpt] :::- ["+stkOpt+"]");
    		
			if( "2".equalsIgnoreCase(stkOpt))
			{	
				
				 sql = " select shelf_life_type from item where item_code = ?  ";
		           pstmt = conn.prepareStatement(sql);
		   		   pstmt.setString(1,this.itemCode);
		   		   rs = pstmt.executeQuery();
		           if(rs.next())
		           {
		        	  shelfLifeType = rs.getString(1);
		           	  System.out.println("@@@@ shelfLifeType:"+shelfLifeType);
		           }
		           rs.close();
		   		   rs = null;
		   		   pstmt.close();
		   		   pstmt = null;
		   		   
		   sql = " select count (*) from item_lot_info where item_code = ? and lot_no = ? ";
           pstmt = conn.prepareStatement(sql);
   		   pstmt.setString(1,this.itemCode);
   		   pstmt.setString(2,this.lotNo);
   		   rs = pstmt.executeQuery();
           if(rs.next())
           {
           	cnt = rs.getInt(1);
           	System.out.println("@@@@ cnt:"+cnt);
           }
           rs.close();
   		   rs = null;
   		   pstmt.close();
   		   pstmt = null;
           
           if( cnt > 0 )
           {
        	     sql = " select item_code,lot_no,grade,exp_date,mfg_date,retest_date,pack_code,potency_perc,site_code__mfg,rate," +
        	     	   " gross_rate,acct_code__inv,cctr_code__inv,batch_no,batch_size,supp_code__mfg,shelf_life_type,supp_code " +
        	    	   " from item_lot_info where item_code = ? and lot_no = ? " ; 
        	    pstmt = conn.prepareStatement(sql);
         		pstmt.setString(1,this.itemCode);
         		pstmt.setString(2,this.lotNo);
         		rs = pstmt.executeQuery();
                if(rs.next())
                 {
                	 itemCode  = rs.getString("item_code");
                	 lotNo 	   =  rs.getString("lot_no");
                	 grade 	   =  rs.getString("grade");
                	 expDate   =  rs.getTimestamp("exp_date");
                	 mfgDate   =  rs.getTimestamp("mfg_date");
                	 retestDate  = rs.getTimestamp("retest_date");
                	 packCode    = rs.getString("pack_code");
                	 potencyPerc = rs.getDouble("potency_perc");
                	 siteCodeMfg = rs.getString("site_code__mfg");
                	 rate  		 = rs.getDouble("rate");
                	 grossRate 	 = rs.getDouble("gross_rate");
                	 acctCodeInv = rs.getString("acct_code__inv");
                	 cctrCodeInv = rs.getString("cctr_code__inv");
                	 batchNo  	 = rs.getString("batch_no");
                	 batchSize 	 = rs.getDouble("batch_size");
                	 suppCodeMfg   = rs.getString("supp_code__mfg");
                	 shelfLifeType = rs.getString("shelf_life_type");
                	 suppCode = rs.getString("supp_code");
                	 
                 }
                rs.close();
        		rs = null;
        		pstmt.close();
        		pstmt = null;
               
                //if isnull(s_updatestock.itemcode) then s_updatestock.itemcode =  ls_item_code
                if(updateStockMap.get("item_code")== null)
        		{
        			updateStockMap.put("item_code", itemCode);
        		}
                //if isnull(s_updatestock.mfg_date) then s_updatestock.mfg_date = ldt_mfgdt
                if(updateStockMap.get("mfg_date")== null)
        		{
        			updateStockMap.put("mfg_date", mfgDate);
        		}
                //if isnull(s_updatestock.potency_perc) then s_updatestock.potency_perc =  lc_potency_perc
                if(updateStockMap.get("potency_perc")== null)
        		{
        			updateStockMap.put("potency_perc", potencyPerc);
        		}
                //if isnull(s_updatestock.exp_date) then s_updatestock.exp_date = mexpdate
                if(updateStockMap.get("exp_date")== null)
        		{
        			updateStockMap.put("exp_date", expDate);
        		}
                //if isnull(s_updatestock.retest_date) then s_updatestock.retest_date =  mretest_date
                if(updateStockMap.get("retest_date")== null)
        		{
        			updateStockMap.put("retest_date", retestDate);
        		}
                //if isnull(s_updatestock.lotno) then s_updatestock.lotno = ls_lot_no
                if(updateStockMap.get("lotno")== null)
        		{
        			updateStockMap.put("lotno", lotNo);
        		}
                //if isnull(s_updatestock.grade) then s_updatestock.grade = ls_grade
                if(updateStockMap.get("grade")== null)
        		{
        			updateStockMap.put("grade", grade);
        		}
                //if isnull(s_updatestock.pack_code) then s_updatestock.pack_code =  ls_pack_code
                if(updateStockMap.get("pack_code")== null)
        		{
        			updateStockMap.put("pack_code", packCode);
        		}
                //if isnull(s_updatestock.sitecode_mfg) then s_updatestock.sitecode_mfg =  ls_site_code__mfg
                if(updateStockMap.get("site_code__mfg")== null)
        		{
        			updateStockMap.put("site_code__mfg", siteCodeMfg);
        		}
                //if isnull(s_updatestock.rate) then s_updatestock.rate = mrate
                if(updateStockMap.get("rate")== null)
        		{
        			updateStockMap.put("rate", rate);
        		}
                //if isnull(s_updatestock.acct_code_inv) then s_updatestock.acct_code_inv  = ls_acct_code__inv
                if(updateStockMap.get("acct_code_inv")== null)
        		{
        			updateStockMap.put("acct_code_inv", acctCodeInv);
        		}
                //if isnull(s_updatestock.cctr_code_inv) then s_updatestock.cctr_code_inv  = ls_ctr_code__inv
                if(updateStockMap.get("cctr_code_inv")== null)
        		{
        			updateStockMap.put("cctr_code_inv", cctrCodeInv);
        		}
                //if isnull(s_updatestock.batch_no) then s_updatestock.batch_no = ls_batch_no
                if(updateStockMap.get("batch_no")== null)
        		{
        			updateStockMap.put("batch_no", batchNo);
        		}
                //if isnull(s_updatestock.gross_rate) then s_updatestock.gross_rate =  ld_grossrate
                if(updateStockMap.get("gross_rate")== null)
        		{
        			updateStockMap.put("gross_rate", grossRate);
        		}
                //if isnull(s_updatestock.batch_size) then s_updatestock.batch_size =  ld_batch_size
                if(updateStockMap.get("batch_size")== null)
        		{
        			updateStockMap.put("batch_size", batchSize);
        		}
                //if isnull(s_updatestock.supp_code__mfg) then  s_updatestock.supp_code__mfg = ls_supp_code__mfg
                if(updateStockMap.get("supp_code__mfg")== null)
        		{
        			updateStockMap.put("supp_code__mfg", suppCodeMfg);
        		}
                if(updateStockMap.get("supp_code")== null)
        		{
        			updateStockMap.put("supp_code", suppCode);
        		}
                
                //int mapsize = updateStockMap.size();
                
               // for(int i=0;i<mapsize;i++)
                {
                	System.out.println("@@@@@ for update map ..........");
                	System.out.println("itemCode::["+updateStockMap.get("item_code")+"]");
             	   	System.out.println("lotNo["+updateStockMap.get("lot_no") +"]");
     				System.out.println("grade["+updateStockMap.get("grade")+"]");
     				System.out.println("retestDate["+updateStockMap.get("retest_date")+"]");
     				System.out.println("expDate["+updateStockMap.get("exp_date")+"]");
     				System.out.println("mfgDate["+updateStockMap.get("mfg_date")+"]");
     				System.out.println("packCode["+updateStockMap.get("pack_code")+"]");
     				System.out.println("potencyPerc["+updateStockMap.get("potency_perc ")+"]");
     				System.out.println("siteCodeMfg["+updateStockMap.get("site_code__mfg")+"]");
     				System.out.println("rate ["+updateStockMap.get("rate")+"]");
     				System.out.println("grossRate["+updateStockMap.get("gross_rate")+"]");
     				System.out.println("acctCodeInv["+updateStockMap.get("acct_code_inv")+"]");
     				System.out.println("cctrCodeInv["+updateStockMap.get("cctr_code_inv")+"]");
     				System.out.println("batchNo["+updateStockMap.get("batch_no")+"]");
     				System.out.println("batchSize["+updateStockMap.get("batch_size")+"]");
     				System.out.println("suppCodeMfg["+updateStockMap.get("supp_code__mfg")+"]");
     				System.out.println("suppCode["+updateStockMap.get("supp_code")+"]");
              /*  	
                
               
                sql = " select count(1) from stock where item_code = ? and lot_no = ?  and site_code= ? and loc_code = ? and lot_sl = ? ";
                pstmt = conn.prepareStatement(sql);
         		pstmt.setString(1,this.itemCode);
         		pstmt.setString(2,this.lotNo);
         		pstmt.setString(3,this.siteCode);
         		pstmt.setString(4,this.lotCode);
         		pstmt.setString(5,this.lotSl);
         		rs = pstmt.executeQuery();
                if(rs.next())
                 {
                	 cnt6  = rs.getInt(1);
                	 
                 }
                rs.close();
        		rs = null;
        		pstmt.close();
        		pstmt = null;
                
                if(cnt6 > 0 )
     			{
     			
     				sql = " update stock set " +
                			" mfg_date = ?,potency_perc = ?,exp_date = ?,retest_date = ?,lot_no = ?,grade = ?,pack_code = ?," +
                			" site_code__mfg = ?,rate = ?,acct_code__inv = ?," +
                            " cctr_code__inv = ? , batch_no = ? ,gross_rate = ? , " +
         					" batch_size = ? , supp_code__mfg = ?  " +
         					" where item_code = ? and lot_no = ?  and site_code= ? and loc_code = ? and lot_sl = ? ";

     				

                	pstmt=conn.prepareStatement(sql);
                	String mfgDate2 = updateStockMap.get("mfg_date")==null?null:updateStockMap.get("mfg_date").toString();
                	if( mfgDate2 != null )
                	{
                		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                        mfgDate3 = simpleDateFormat1.parse(mfgDate2);
                    }
                	pstmt.setDate(1,mfgDate3 ==null?null:new java.sql.Date(mfgDate3.getTime()));
            		System.out.println("@@@@ mfgDate3::"+mfgDate3);
                	
                	pstmt.setString(2,updateStockMap.get("potency_perc")==null?null:updateStockMap.get("potency_perc").toString());
                	
                	String expDate2 = updateStockMap.get("exp_date")==null?null:updateStockMap.get("exp_date").toString();
                	if( expDate2 != null )
                	{
                		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                        expDate3 = simpleDateFormat1.parse(expDate2);
                    }
                	pstmt.setDate(3,expDate3 ==null?null:new java.sql.Date(expDate3.getTime()));
            		System.out.println("@@@@ expDate3::"+expDate3);
            	
                	String retestDate2 = updateStockMap.get("retest_date")==null?null:updateStockMap.get("retest_date").toString();
                	if( retestDate2 != null )
                	{
                		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                        retestDate3 = simpleDateFormat1.parse(retestDate2);
                    }
                	pstmt.setDate(4,retestDate3 ==null?null:new java.sql.Date(retestDate3.getTime()));
            		System.out.println("@@@@ retestDate3::"+retestDate3);
                	
                	pstmt.setString(5,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
 					pstmt.setString(6,updateStockMap.get("grade")==null?null:updateStockMap.get("grade").toString());
 					pstmt.setString(7,updateStockMap.get("pack_pode")==null?null:updateStockMap.get("pack_pode").toString());
 					pstmt.setString(8,updateStockMap.get("site_code__mfg")==null?null:updateStockMap.get("site_code__mfg").toString());
 					pstmt.setString(9,updateStockMap.get("rate")==null?null:updateStockMap.get("rate").toString());
 					pstmt.setString(10,updateStockMap.get("acct_code_inv")==null?null:updateStockMap.get("acct_code_inv").toString());
 					pstmt.setString(11,updateStockMap.get("cctr_code_inv")==null?null:updateStockMap.get("cctr_code_inv").toString());
 					pstmt.setString(12,updateStockMap.get("batch_no")==null?null:updateStockMap.get("batch_no").toString());
 					pstmt.setString(13,updateStockMap.get("gross_rate ")==null?null:updateStockMap.get("gross_rate").toString());
 					pstmt.setString(14,updateStockMap.get("batch_size")==null?null:updateStockMap.get("batch_size").toString());
 					pstmt.setString(15,updateStockMap.get("supp_code__mfg")==null?null:updateStockMap.get("supp_code__mfg").toString());
 					pstmt.setString(16,updateStockMap.get("item_code")==null?null:updateStockMap.get("item_code").toString());
 					pstmt.setString(17,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
 					pstmt.setString(18,updateStockMap.get("site_code")==null?null:updateStockMap.get("site_code").toString());
 					pstmt.setString(19,updateStockMap.get("loc_code")==null?null:updateStockMap.get("loc_code").toString());
 					pstmt.setString(20,updateStockMap.get("lot_sl")==null?null:updateStockMap.get("lot_sl").toString());
 					
     					cnt1 = pstmt.executeUpdate();
    					pstmt.close();
    		     		pstmt = null;
    		     		System.out.println("@@@@ cnt1:::"+cnt1);
    					if ( cnt1 == 0)
    					{
    						 errCode = "VTUPDDET";
    						 errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
    						 return errString;
    					}
    				}  // if cnt6
    			*/
 					}
           }
           else
           {
        	    System.out.println("@@@@@ for insert..........");
        	    System.out.println("itemCode::["+updateStockMap.get("item_code")+"]");
         	    System.out.println("lotNo["+updateStockMap.get("lot_no") +"]");
 				System.out.println("grade["+updateStockMap.get("grade")+"]");
 				System.out.println("retestDate[today]"+today+"]");
 				System.out.println("expDate["+updateStockMap.get("exp_date")+"]");
 				System.out.println("mfgDate["+updateStockMap.get("mfg_date ")+"]");
 				System.out.println("packCode["+updateStockMap.get("pack_code ")+"]");
 				System.out.println("potencyPerc["+updateStockMap.get("potency_perc ")+"]");
 				System.out.println("siteCodeMfg["+updateStockMap.get("site_code__mfg")+"]");
 				System.out.println("rate ["+updateStockMap.get("rate")+"]");
 				System.out.println("grossRate["+updateStockMap.get("gross_rate ")+"]");
 				System.out.println("acctCodeInv["+updateStockMap.get("acct_code_inv ")+"]");
 				System.out.println("cctrCodeInv["+updateStockMap.get("cctr_code_inv")+"]");
 				System.out.println("batchNo["+updateStockMap.get("batch_no")+"]");
 				System.out.println("batchSize["+updateStockMap.get("batch_size")+"]");
 				System.out.println("suppCodeMfg["+updateStockMap.get("supp_code__mfg")+"]");
 				System.out.println("shelfLifeType value ["+shelfLifeType+"]");
 				System.out.println("suppCode ["+suppCode+"]");
 				
        	  sql = " insert into item_lot_info (item_code,lot_no,grade,crea_date,exp_date,mfg_date,retest_date,pack_code," +
        	  		" potency_perc,site_code__mfg,rate,gross_rate,acct_code__inv," +
                    " cctr_code__inv,batch_no,batch_size,supp_code__mfg,shelf_life_type,supp_code) " +
                    " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        	  
              pstmt=conn.prepareStatement(sql);
                
                pstmt.setString(1,updateStockMap.get("item_code")==null?null:updateStockMap.get("item_code").toString());
				pstmt.setString(2,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
				pstmt.setString(3,updateStockMap.get("grade")==null?null:updateStockMap.get("grade").toString());
				pstmt.setTimestamp(4,today);
				
				String expDate2 = updateStockMap.get("exp_date")==null?null:updateStockMap.get("exp_date").toString();
            	if( expDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    expDate3 = simpleDateFormat1.parse(expDate2);
                }
            	pstmt.setDate(5,expDate3 ==null?null:new java.sql.Date(expDate3.getTime()));
        		System.out.println("@@@@ expDate3::"+expDate3);
				
        		String mfgDate2 = updateStockMap.get("mfg_date")==null?null:updateStockMap.get("mfg_date").toString();
            	if( mfgDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                   mfgDate3 = simpleDateFormat1.parse(mfgDate2);
                }
            	pstmt.setDate(6,mfgDate3 ==null?null:new java.sql.Date(mfgDate3.getTime()));
        		System.out.println("@@@@ mfgDate3::"+mfgDate3);
            	
        		String retestDate2 = updateStockMap.get("retest_date")==null?null:updateStockMap.get("retest_date").toString();
            	if( retestDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    retestDate3 = simpleDateFormat1.parse(retestDate2);
                }
            	pstmt.setDate(7,retestDate3 ==null?null:new java.sql.Date(retestDate3.getTime()));
        		System.out.println("@@@@ retestDate3::"+retestDate3);
            	
        		pstmt.setString(8,updateStockMap.get("pack_pode ")==null?null:updateStockMap.get("pack_pode").toString());
				pstmt.setString(9,updateStockMap.get("potency_perc ")==null?null:updateStockMap.get("potency_perc").toString());
				pstmt.setString(10,updateStockMap.get("site_code__mfg")==null?null:updateStockMap.get("site_code__mfg").toString());
				pstmt.setString(11,updateStockMap.get("rate")==null?null:updateStockMap.get("rate").toString());
				pstmt.setString(12,updateStockMap.get("gross_rate ")==null?null:updateStockMap.get("gross_rate").toString());
				pstmt.setString(13,updateStockMap.get("acct_code_inv ")==null?null:updateStockMap.get("acct_code_inv").toString());
				pstmt.setString(14,updateStockMap.get("cctr_code_inv")==null?null:updateStockMap.get("cctr_code_inv").toString());
				pstmt.setString(15,updateStockMap.get("batch_no")==null?null:updateStockMap.get("batch_no").toString());
				pstmt.setString(16,updateStockMap.get("batch_size")==null?null:updateStockMap.get("batch_size").toString());
				pstmt.setString(17,updateStockMap.get("supp_code_mfg")==null?null:updateStockMap.get("supp_code__mfg").toString());
				pstmt.setString(18,shelfLifeType);
				pstmt.setString(19,updateStockMap.get("supp_code")==null?null:updateStockMap.get("supp_code").toString());
				
				cnt1 = pstmt.executeUpdate();
					pstmt.close();
		     		pstmt = null;
				//	if ( cnt1 == 0)
				//	{
				//		 errCode = "VINSERT5";
				//		 errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				//		 return errString;
				//	}
			
           }
			//} //add
		 } 
        }
        catch(Exception e)
        {
            System.out.println("Exception "+e);
            e.printStackTrace();
            throw new ITMException(e);
        }
        return updateStockMap;
	}

/*	
	private String updateStockAmd(HashMap updateStockMap ,Connection conn) throws ITMException, Exception
	{
		String stkOpt="", sql = "",errCode="",errString="";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		java.util.Date retestDate3=null,expDate3=null,mfgDate3=null;
		int cnt1=0,cnt=0;
		
		if ( updateStockMap.get("item_code") != null )
		{
			this.itemCode = updateStockMap.get("item_code").toString();
		}
		if ( updateStockMap.get("lot_no") != null )
		{
			this.lotNo = updateStockMap.get("lot_no").toString();
		}
		if ( updateStockMap.get("lot_no") != null )
		{
			this.siteCode = updateStockMap.get("site_code").toString();
		}
		if ( updateStockMap.get("tran_ser") != null )
		{
			this.tranSer = updateStockMap.get("tran_ser").toString();
		}
		
		stkOpt = checkStkOpt(this.itemCode,this.siteCode,conn);
		System.out.println("@@@@@ stkOpt[gfChkStkOpt] :::- ["+stkOpt+"]");
		
		if( "2".equalsIgnoreCase(stkOpt) )
		{	
			System.out.println("tran_ser::::::::["+this.tranSer+"]");
			if( "STKAMD".equalsIgnoreCase( this.tranSer ))
			{
				sql = " select count(1) from item_lot_info where item_code = ? and lot_no = ?  ";
                pstmt = conn.prepareStatement(sql);
         		pstmt.setString(1,this.itemCode);
         		pstmt.setString(2,this.lotNo);
         		rs = pstmt.executeQuery();
                if(rs.next())
                 {
                	 cnt  = rs.getInt(1);
                 }
                rs.close();
        		rs = null;
        		pstmt.close();
        		pstmt = null;
				
        		if( cnt > 0)
				{
        			sql = " update item_lot_info set " +
					      " mfg_date = ?, exp_date = ? , site_code__mfg = ?, retest_date = ? , potency_perc = ?, " +
            		      " supp_code__mfg = ?  where item_code = ? and lot_no = ? ";

				pstmt=conn.prepareStatement(sql);
            	
				String mfgDate2 = updateStockMap.get("mfg_date")==null?null:updateStockMap.get("mfg_date").toString();
            	if( mfgDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    mfgDate3 = simpleDateFormat1.parse(mfgDate2);
                }
            	pstmt.setDate(1,mfgDate3 ==null?null:new java.sql.Date(mfgDate3.getTime()));
        		System.out.println("@@@@ mfgDate3---::"+mfgDate3);
            	
        		String expDate2 = updateStockMap.get("exp_date")==null?null:updateStockMap.get("exp_date").toString();
            	if( expDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    expDate3 = simpleDateFormat1.parse(expDate2);
                }
            	pstmt.setDate(2,expDate3 ==null?null:new java.sql.Date(expDate3.getTime()));
        		System.out.println("@@@@ expDate3------::"+expDate3);
        	          		
        		pstmt.setString(3,updateStockMap.get("site_code__mfg")==null?null:updateStockMap.get("site_code__mfg").toString());
        		
            	String retestDate2 = updateStockMap.get("retest_date")==null?null:updateStockMap.get("retest_date").toString();
            	if( retestDate2 != null )
            	{
            		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
                    retestDate3 = simpleDateFormat1.parse(retestDate2);
                }
            	pstmt.setDate(4,retestDate3 ==null?null:new java.sql.Date(retestDate3.getTime()));
        		System.out.println("@@@@ retestDate3::"+retestDate3);
            	
        		pstmt.setString(5,updateStockMap.get("potency_perc")==null?null:updateStockMap.get("potency_perc").toString());            		
        		pstmt.setString(6,updateStockMap.get("supp_code__mfg")==null?null:updateStockMap.get("supp_code__mfg").toString());
				pstmt.setString(7,updateStockMap.get("item_code")==null?null:updateStockMap.get("item_code").toString());
				pstmt.setString(8,updateStockMap.get("lot_no")==null?null:updateStockMap.get("lot_no").toString());
					
 				cnt1 = pstmt.executeUpdate();
				pstmt.close();
		     	pstmt = null;
		     	System.out.println("@@@@ cnt1-----:::"+cnt1);
					
		     		if ( cnt1 == 0)
					{
						 errCode = "VTUPDDET";
						 errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
						 return errString;
					}
			    }
        	}
	    }
		return errString;
	}
	*/ //for amm end
	
	// added by cpatil end
	
	
}//END OF CLASS