package ibase.webitm.ejb.dis;

import ibase.utility.EMail;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.mfg.ExplodeBom;
import ibase.webitm.ejb.mfg.adv.RcpBackflushConfirm;
import ibase.webitm.ejb.sys.*;
import ibase.system.config.*;
import ibase.webitm.utility.ITMException;

import java.text.*;

import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.fin.*;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.dis.*;

import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;

import javax.ejb.*;
import javax.naming.InitialContext;
import javax.ejb.Stateless; 

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
//import org.drools.runtime.pipeline.SmooksTransformerProvider;
import org.w3c.dom.*;

import javax.xml.rpc.ParameterMode;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.parsers.*;

import java.text.SimpleDateFormat;
import ibase.utility.CommonConstants;
import ibase.utility.GenericUtility;
import ibase.webitm.ejb.E12CreateBatchLoadEjb;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.ejb.sys.CreateRCPXML;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

import javax.ejb.Stateless;

import org.w3c.dom.Document;

import com.ibm.db2.jcc.b.SQLException;
@Stateless 
public class PriceListConf extends ActionHandlerEJB implements PriceListConfLocal, PriceListConfRemote 
{
	String userId = "", termId = "",lckGroup="" ;
	ibase.utility.E12GenericUtility genericUtility= new  ibase.utility.E12GenericUtility();
	CommonConstants commonConstants = new CommonConstants();
	ibase.webitm.ejb.sys.UtilMethods utilMethods = ibase.webitm.ejb.sys.UtilMethods.getInstance();
	
	// overloaded method added to call the confirm method from postsave component - 25/11/11 - Gulzar
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		String retString = "";
		boolean isConn= false;
		Connection conn = null;

		try
		{
			retString = confirm( tranID, xtraParams, forcedFlag, conn, isConn );

			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}
		}
		catch(Exception exception)
		{
			System.out.println("Exception in [InvHoldConfEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}

	public String confirm(String tranId, String xtraParams,String forcedFlag, Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		FinCommon finCommon = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		ValidatorEJB validatorEJB = null;
		System.out.println("tran id = "+tranId);
		//boolean connStatus=false;
		String chgUser="";
		String retString = "";
		String sql = "";
		String conf = "";
		String siteRcp = "";
		String loginEmpCode = "";
		String confirm = "";
		try
		{
			if ( conn == null )
			{
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
				connDriver = null;
				connStatus = true;
			}
			finCommon = new FinCommon();
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			validatorEJB = new ValidatorEJB();
			loginEmpCode =validatorEJB.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			System.out.println("Printing loginEmpCode---1-----"+loginEmpCode+"--");
			
			

			sql = "select confirmed,chg_user from pricelist_hdr where tran_id = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1,tranId);
			rs = pstmtSql.executeQuery();
			if(rs.next())
			{
				confirm = rs.getString("confirmed") == null ?"N":rs.getString("confirmed");
				chgUser = rs.getString("chg_user") == null ?" ":rs.getString("chg_user");
			}
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;
			if(chgUser==null || chgUser.trim().length()==0)
			{
				chgUser="SYSTEM";
			}
			if("null".equalsIgnoreCase(loginEmpCode) || loginEmpCode==null || loginEmpCode.trim().length()==0)
			{
				sql="select emp_code from users where code=?";
				pstmtSql=conn.prepareStatement(sql);
				pstmtSql.setString(1,chgUser);
				
				rs=pstmtSql.executeQuery();
				if(rs.next())
				{
					loginEmpCode=rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;
				System.out.println("Printing loginEmpCode---2-----"+loginEmpCode+"--");
				
			}	
			
			if(confirm != null  && confirm.equalsIgnoreCase("N"))
			{
				retString =confirmPlist(tranId,conn, xtraParams);
				System.out.println("err String  ="+retString);
				if(retString != null && retString.trim().length() > 0)
				{
					return retString;
				}
				if(retString != null && retString.trim().length() > 0)
				{
					return retString;
				}
				
				if(retString == null || retString.trim().length() == 0)
				{
					if("null".equalsIgnoreCase(loginEmpCode) || loginEmpCode==null || loginEmpCode.trim().length()==0)
					{
						loginEmpCode="E03952";
					}
					System.out.println("loginEmpCode--3-"+loginEmpCode);
					sql = "update pricelist_hdr set confirmed = 'Y', conf_date = ?,emp_code__aprv = ? where tran_id = ?";
					pstmtSql = conn.prepareStatement(sql);
					pstmtSql.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
					pstmtSql.setString(2, loginEmpCode);
					pstmtSql.setString(3, tranId);
					int updateCoount = pstmtSql.executeUpdate();
					System.out.println("no of row update = "+updateCoount);
					pstmtSql.close();
					pstmtSql = null;
					if(updateCoount > 0)
					{
						retString = itmDBAccessEJB.getErrorString("","VTCICONF3","","",conn);
					}
				}
				else 
				{
					return retString;
				}
			}
			else
			{
				System.out.println("The Selected transaction is already confirmed");
				retString = itmDBAccessEJB.getErrorString("","VTMCONF1","","",conn);
				return retString;
			}
			System.out.println("115 err String from confirm method.....");
	
		}


		/*try
		{
			if ( conn == null )
			{
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
				connDriver = null;
				connStatus = true;
			}
			
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		    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";
			}
            
			sql = "SELECT CONFIRMED FROM pricelist_hdr WHERE TRAN_ID= ?"; 
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			if ( rs.next() )
			{
				conf = rs.getString("CONFIRMED");
			}
			
			if(pstmtSql != null)
			{
				pstmtSql.close();
				pstmtSql = null;					
			}
			if ( rs != null)
			{
				rs.close();
				rs = null;
			}
			if( conf.equalsIgnoreCase("Y") )
			{
				retString = itmDBAccessLocal.getErrorString("","VTCONF1","");
				return retString;
			}
			else
			{
				retString = confirmPlist(tranId,conn, xtraParams);
				if (retString == null || retString.trim().length() == 0)
				{
					////////////////////// EDI creation
					String ediOption = "";
					String dataStr = "";
					sql = "SELECT EDI_OPTION FROM TRANSETUP WHERE TRAN_WINDOW = 'w_pricelist_tran' ";
					pstmtSql = conn.prepareStatement(sql);
					rs = pstmtSql.executeQuery();
					if ( rs.next() )
					{
						ediOption = rs.getString("EDI_OPTION");
					}
					rs.close();rs = null;
					pstmtSql.close();pstmtSql = null;

					if ( "1".equals(ediOption.trim()) )
					{
						CreateRCPXML createRCPXML = new CreateRCPXML("w_pricelist_tran","tran_id");
						dataStr = createRCPXML.getTranXML( tranId, conn );
						System.out.println( "dataStr =[ "+ dataStr + "]" );
						Document ediDataDom = genericUtility.parseString(dataStr);

						E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
						retString = e12CreateBatchLoad.createBatchLoad( ediDataDom, "w_pricelist_tran", "0", xtraParams, conn );
						createRCPXML = null;
						e12CreateBatchLoad = null;

						if( retString != null && "SUCCESS".equals(retString) )
						{
							System.out.println("retString from batchload = ["+retString+"]");
						}
					}
					/////////////////////

					if( connStatus )//Condition added - 25/11/11 - Gulzar as confirm method is called from post save component
					{
						conn.commit();
						retString = itmDBAccessLocal.getErrorString("","VTMCONF2","");
					}
				}
				else
				{
					conn.rollback();
				}
			}
		}*/
		catch( Exception e)
		{
			if(conn!=null)
			{
				try {
					
					conn.rollback();
				} 
				/*catch (SQLException ex) 
				{

					System.out.println("Exception : "+e);
					e.printStackTrace();
					throw new ITMException(e);

				}*/ catch (java.sql.SQLException e1) {
					e1.printStackTrace();
				}
			}
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			
				try
				{
					
					if(retString != null && retString.trim().length() > 0)
					{
						if( conn != null && !conn.isClosed() && connStatus ) 
						{
							if(retString.indexOf("VTCICONF3") > -1)
							{
								conn.commit();
							}
							else
							{
								conn.rollback();
							}
							conn.close();
							conn = null;
						}
					}
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmtSql != null)
					{
						pstmtSql.close();
						pstmtSql = null;
					}
					//conn.close();
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			
		}
		return retString;
	}
	  private String insertPricelist(String tranId,HashMap PList,ArrayList PricelistGen, Connection conn, String xtraParams) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql ,pstmtInsert= null;
		ResultSet rs = null;
		String dbName = "";
		String sql = "";
		String retString = "";
		String errCode="",AutoExpire="",LotFrBrow="",LotNoToBrow="",LotNoFrom="",OldLotNo="",Set="",NewStr="",OrigStr="",edioption="";
		int DayDiff=0;
		java.sql.Timestamp ValidUpTo=null,EffDate=null;
		int LineNo=0,RowCount=0,Count=0,OldLen=0,New=0;
		double SlabNo=0.0;
		double NewLen=0.0,slabno=0.0,cnt=0.0;
		double MinQtyBrow=0.0;
		double MaxQtyBrow=0.0;
		double minrate=0.0;
		double rate1=0.0;
		double minqty=0.0,maxqty=0.0;
		String ratetype="";
		String refNo1="";
		String refNoold1="";
		String chgref1="";
		double maxrate1=0.0;
		String PriceListParent1="",orderType1="";
		int Ctr=0,countup=0,Len=0,Len1=0;
		java.sql.Timestamp ChgDate=null,efffromdt=null,validupto=null;
		java.sql.Timestamp chgDate = null,validupto1=null;
		String lotnofr="",lotnoto="",currentlotnofr="",currentlotnoto="",nextlotnofr="",LotNoTo="",currentlotnofrtemp="",currentlotnototemp="";
		//String LotNoFrom="",LotNoTo="";
		boolean recordfound;
		//HashMap PlGen=null;
		String plist="",itemcode="",unit="",listtype="";
		String plist1="",refNoold="";
		int cnt1=0,UpdCnt=0;
		String LotNoFroma[] = null, LotNoToa[]=null;
		char left1=0,left2=0,left3 = 0,left4=0;
		int updCnt = 0;
		String mid1="",mid2="",mid3="",mid4="";
		java.text.SimpleDateFormat sdf = null;
		String lotnofrom="",lotnoto1="";
		userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
		termId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "termId" );
		try
		{
			DistCommon distCommon = new DistCommon();
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			dbName = CommonConstants.DB_NAME;
			System.out.println("Insert Price List@@@@@@@@@@@");
		AutoExpire=distCommon.getDisparams("999999","AUTO_EXPIRE_PL", conn);
		System.out.println("Autoexpire Value is:"+AutoExpire);
		
		if (AutoExpire == null)
		{
			AutoExpire = "";
		}
		else if(AutoExpire.equalsIgnoreCase("Y"))
		{
			System.out.println("Insert Autoexpire@@@@@@@@@");
			//PList=new HashMap();
			plist = (String)PList.get("price_list");
			System.out.println("Price list value is"+plist);
			itemcode = (String)PList.get("item_code");
			System.out.println("item code in price list"+itemcode);
			unit = (String)PList.get("unit");
			listtype = (String)PList.get("list_type");
			//rate1=(Double)PList.get("rate");
			//ratetype=(String)PList.get("rate_type");
			//minrate=(Double)PList.get("min_rate");
			//maxrate1=(Double)PList.get("max_rate");
			//refNo1=(String)PList.get("ref_No");
			//refNoold1=(String)PList.get("ref_No_old");
			//PriceListParent1=(String)PList.get("Price_List__Parent");
			//orderType1=(String)PList.get("order_Type");
			//chgref1=(String)PList.get("chg_ref_no");
			PricelistGen.add(PList);
			lotnofrom=(String)PList.get("lot_no__from");
			System.out.println("lot no from "+lotnofrom);
			lotnoto1=(String)PList.get("lot_no__to");
			System.out.println("lot no to "+lotnoto1);
			validupto1=(java.sql.Timestamp)PList.get("valid_upto");
			System.out.println("List Sizeeeeeee"+PricelistGen.size());
			minqty=(Double)PList.get("min_qty");
			System.out.println("min qty in list"+minqty);
			maxqty=(Double)PList.get("max_qty");
			
			
			
			sql="select valid_upto,eff_from,slab_no,lot_no__from,lot_no__to,min_qty,max_qty from pricelist where price_list=? and item_code=? ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1,plist);
			pstmtSql.setString(2,itemcode);
			rs = pstmtSql.executeQuery();
			
			for (Ctr =0; Ctr < PricelistGen.size(); Ctr++)
			{
				
				System.out.println("Insert For loooopppp");
				PList=new HashMap();
				PList = (HashMap)PricelistGen.get(Ctr);
				if(rs.next())
				{
				ValidUpTo=rs.getTimestamp("valid_upto");
				System.out.println("Valid upto in price list"+ValidUpTo);
				EffDate=rs.getTimestamp("eff_from");
				SlabNo=rs.getDouble("slab_no");
				System.out.println("slabbbb_no1111111111111111111111"+SlabNo);
				LotFrBrow=rs.getString("lot_no__from");
				LotNoToBrow=rs.getString("lot_no__to");
				
				MinQtyBrow=rs.getDouble("min_qty");
				MaxQtyBrow=rs.getDouble("max_qty");
				System.out.println("Minimum value is@@@@@@@@@"+MinQtyBrow);
				}
				System.out.println("check if conditionn");
				DayDiff=(int)utilMethods.DaysAfter(ValidUpTo,validupto1);
				System.out.println("check"+DayDiff);
				if(DayDiff<=0 && LotFrBrow.trim()== lotnofrom && LotNoToBrow.trim()==lotnoto1)
			     {
					ValidUpTo=utilMethods.RelativeDate((java.sql.Timestamp)PList.get("eff_from"), -1 );
					if( dbName.equalsIgnoreCase("db2"))
					{
						
						//validupto=new java.sql.Timestamp(System.currentTimeMillis());
						//sdf = new java.text.SimpleDateFormat(genericUtility
							//	.getApplDateFormat());
						//=sdf.format(ValidUpTo);
						//validupto=datetime(ValidUpTo);
						sql="update pricelist set  valid_upto = ? where price_list =? and item_code = ? and unit = ? and list_type = ? and slab_no = ?";
						pstmtSql=conn.prepareStatement(sql);
						pstmtSql.setTimestamp(1, validupto);
						pstmtSql.setString(2, plist);
						pstmtSql.setString(3, itemcode);
						pstmtSql.setString(4, unit);
						pstmtSql.setString(5, listtype);
						pstmtSql.setDouble(6, SlabNo);
						int upcnt1=pstmtSql.executeUpdate();	
						System.out.println("No. of rows updated------>>"+upcnt1);
						if(pstmtSql !=null)
						{
							pstmtSql.close();
							pstmtSql=null;
						}
					}
					else 
					{
						sql="update pricelist set  valid_upto = ? where price_list =? and item_code = ? and unit = ? and list_type = ? and slab_no = ?";
						pstmtSql=conn.prepareStatement(sql);
						pstmtSql.setTimestamp(1, ValidUpTo);
						pstmtSql.setString(2, plist);
						pstmtSql.setString(3, itemcode);
						pstmtSql.setString(4, unit);
						pstmtSql.setString(5, listtype);
						pstmtSql.setDouble(6, SlabNo);
						int upcnt1=pstmtSql.executeUpdate();	
						System.out.println("No. of rows updated------>>"+upcnt1);
						if(pstmtSql !=null)
						{
							pstmtSql.close();
							pstmtSql=null;
						}
						
					}
				 }
					
				
			}// for end
			
		}// end autoexpire if
		///////////////
		// astr_PL.list_type = 'B'
	      listtype = (String)PList.get("list_type");
	      System.out.println("check price listttttttttttttttt type"+listtype);
		  if(listtype.equalsIgnoreCase("B"))
		   {
			LotNoFrom=(String)PList.get("lot_no__from");
			System.out.println("@@@@@lot number from"+LotNoFrom);
			sql="select count(1) from pricelist where price_list = ? and item_code = ? and unit = ? and list_type = ? and lot_no__from <= ? and lot_no__to >= ? and min_qty <= ? and max_qty >= ?";
			pstmtSql=conn.prepareStatement(sql);
			pstmtSql.setString(1,plist);
			pstmtSql.setString(2,itemcode);
			pstmtSql.setString(3,unit);
			pstmtSql.setString(4,listtype);
			pstmtSql.setString(5,lotnofrom);
			pstmtSql.setString(6,lotnoto1);
			pstmtSql.setDouble(7,minqty);
			pstmtSql.setDouble(8,maxqty);
    		rs=pstmtSql.executeQuery();
    		if(rs.next())
    		{
    			Count=rs.getInt(1);
    		}
    		System.out.println("llCount-------->>["+Count+"]");
    		if(rs != null)
			{
				rs.close();
				rs=null;
			}
			if(pstmtSql !=null)
			{
				pstmtSql.close();
				pstmtSql=null;
			}
			
    		if(Count > 0)
    		{
    			System.out.println("insert in count");
    			Len=LotNoFrom.trim().length();
    			Ctr=1;
    			String s2=LotNoFrom.trim();
    			System.out.println("s22222222222222"+s2);
    			String mid="";
    			char left=0;
    			char right= s2.charAt(s2.length()-1);
    			System.out.println("right s22222222222222"+right);
    			if(Character.isDigit(right) )
    			{
    				do
    				{
    					if(Character.isDigit(right))
    					{
    						 left=s2.charAt(0);
    						 System.out.println("left s222222222222"+left);
    							if(left =='0')
    							{
    								if(s2.length()>2)
    								{
    								 mid=s2.substring(2);
    								 System.out.println("middle s2222222222"+mid);
    									Set=Set + '0';
    								}
    							}
    							else
    							{
    							OldLen = LotNoFrom.trim().length();
    							New = OldLen - 1;
    							String new1=Integer.toString(New);
    							NewLen = new1.trim().length();
    							if(OldLen != NewLen);
    							{
    								int diff3=(int) (OldLen - NewLen);
   						    	 for(int s3=0;s3<diff3;s3++)
   						    	 {
   						    		 NewStr=NewStr.concat("0");
   						    		 System.out.println("@@@@@@@@@New String"+NewStr);
   						    	 }
    							}	
    							}
    					}
    					else
    					{
    						 OrigStr = Character.toString(left);
    						    Set = Set + OrigStr;
    							LotNoFrom = (mid + 1);

    					}
    			        Ctr++;
    					
    				}while(Ctr<=Len);
    				if(New==0)
    				{
    					LotNoFrom = Set +NewStr.trim();
    				}
    				else
    				{
    					String a1=Integer.toString(New);
    					LotNoFrom = Set + NewStr.trim() +a1.trim();
    				}
    				sql="update pricelist set lot_no__to = ? where price_list = ? and item_code = ? and unit = ? and list_type = ? and lot_no__from <= ? and lot_no__to >= ? and min_qty <= ? and max_qty >= ?";
    				pstmtSql=conn.prepareStatement(sql);
					pstmtSql.setString(1,LotNoFrom);
					pstmtSql.setString(2, plist);
					pstmtSql.setString(3, itemcode);
					pstmtSql.setString(4, unit);
					pstmtSql.setString(5,listtype);
					pstmtSql.setString(6, lotnofrom);
					pstmtSql.setString(7, lotnoto1);
					pstmtSql.setDouble(8, minqty);
					pstmtSql.setDouble(9, maxqty);
					int upcnt1=pstmtSql.executeUpdate();	
					System.out.println("No. of rows updated@@@@@@@@------>>"+upcnt1);
					if(pstmtSql !=null)
					{
						pstmtSql.close();
						pstmtSql=null;
					}
    			}//end if
    		}
		}
		//to expire the pricelist in case of revise circular of old circular.
		//change the valid upto date of price before the eff from date and batch no less then the 
		//plist1 = (String)PList.get("price_list");
		refNoold=(String)PList.get("ref_no_old");
		 if(listtype.equalsIgnoreCase("B"))
		{
			LotNoFrom=(String)PList.get("lot_no__from");
			System.out.println("@@@@@lot number from"+LotNoFrom);
			if(refNoold !=null && refNoold.trim().length() > 0)
			{
				sql="select count(*) from pricelist  where price_list= ? and item_code = ? and unit = ?  and lot_no__from >= ? and list_type = ?   and min_qty >= ? and max_qty <= ? and ref_no  = ?";
				pstmtSql=conn.prepareStatement(sql);
				pstmtSql.setString(1, plist);
				pstmtSql.setString(2, itemcode);
				pstmtSql.setString(3, unit);
				pstmtSql.setString(4, lotnofrom);
				pstmtSql.setString(5, listtype);
				pstmtSql.setDouble(6, minqty);
				pstmtSql.setDouble(7, maxqty);
				pstmtSql.setString(8, refNoold);
				rs = pstmtSql.executeQuery();
				System.out.println("COUNT"+countup);
				if (rs.next())
				{
					countup = rs.getInt(1);
				}
				
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;
				
			}
			else
			{
				sql="select count(*) from pricelist  where price_list= ? and item_code = ? and unit = ?  and lot_no__from >= ? and list_type = ?   and min_qty >= ? and max_qty <= ? and ref_no is null ";
				pstmtSql=conn.prepareStatement(sql);
				pstmtSql.setString(1, plist);
				pstmtSql.setString(2, itemcode);
				pstmtSql.setString(3, unit);
				pstmtSql.setString(4, lotnofrom);
				pstmtSql.setString(5, listtype);
				pstmtSql.setDouble(6, minqty);
				pstmtSql.setDouble(7, maxqty);
				pstmtSql.setString(8, refNoold);
				rs = pstmtSql.executeQuery();
				System.out.println("COUNT"+countup);
				if (rs.next())
				{
					countup = rs.getInt(1);
				}
				
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;
			}
			if(countup>0)
			{
				if(refNoold !=null && refNoold.trim().length() > 0)
				{
					sql="select slab_no,eff_from ,lot_no__from  from  pricelist where price_list = ? and item_code = ? and unit = ? and lot_no__from >= ? and list_type = ? and min_qty >= ? and max_qty <= ? and ref_no  = ?";
					pstmtSql=conn.prepareStatement(sql);
					pstmtSql.setDouble(1, slabno);
					pstmtSql.setTimestamp(2, efffromdt);
					pstmtSql.setString(3, LotNoFrom);
					rs = pstmtSql.executeQuery();
					if (rs.next())
					{
						plist=rs.getString(1);
						itemcode=rs.getString(2);
						unit=rs.getString(3);
						lotnofrom=rs.getString(4);
						listtype=rs.getString(5);
						minqty=rs.getDouble(6);
						maxqty=rs.getDouble(7);
						refNoold=rs.getString(8);
					}
					rs.close();
					rs = null;
					pstmtSql.close();
					pstmtSql = null;
					}
				else
				{
					sql="select slab_no,eff_from ,lot_no__from  from  pricelist where price_list = ? and item_code = ? and unit = ? and lot_no__from >= ? and list_type = ? and min_qty >= ? and max_qty <= ? and ref_no is null";
					pstmtSql=conn.prepareStatement(sql);
					pstmtSql.setDouble(1, slabno);
					pstmtSql.setTimestamp(2, efffromdt);
					pstmtSql.setString(3, lotnofrom);
					rs = pstmtSql.executeQuery();
					if (rs.next())
					{
						plist=rs.getString(1);
						itemcode=rs.getString(2);
						unit=rs.getString(3);
						LotFrBrow=rs.getString(4);
						listtype=rs.getString(5);
						minqty=rs.getDouble(6);
						maxqty=rs.getDouble(7);
						refNoold=rs.getString(8);
					}
					rs.close();
					rs = null;
					pstmtSql.close();
					pstmtSql = null;
					}
				
			
			ValidUpTo=utilMethods.RelativeDate((java.sql.Timestamp)PList.get("eff_from"), -1 );
			Len=LotNoFrom.trim().length();
			String s=LotNoFrom.trim();
			System.out.println("String s"+s);
			Ctr=1;
		    OrigStr="";
		    Set = "";
			NewStr = "";
			char right1= s.charAt(s.length()-1);
		//	char lotnoform=LotNoFrom .charAt(Len-1);
			//LotNoFrom.sub
			if(Character.isDigit(right1) )
			{
				do
				{
					if(Character.isDigit(right1) )
					{
					 left1=s.charAt(0);
					if(left1=='0')
					{
						if(s.length() > 2)
						{
						 mid1= s.substring(2);
						Set=Set + '0';
						}
						
					}
					else
					{
						OldLen = LotNoFrom.trim().length();
						New = OldLen - 1;
						String new1=Integer.toString(New);
						NewLen = new1.trim().length();
						if(OldLen != NewLen);
						{
							int diff2=(int) (OldLen - NewLen);
					    	 for(int s2=0;s2<diff2;s2++)
					    	 {
					    		 NewStr=NewStr.concat("0");
					    		 System.out.println("@@@@@@@@@New String"+NewStr);
					    	 }
						}	
						
					}
				}
					else
					{
					    OrigStr = Character.toString(left1);
					    Set = Set + OrigStr;
						LotNoFrom = (mid1+ 1);
					}
					Ctr++;
				}while(Ctr <= Len);
				if(New==0)
				{
					LotNoFrom = Set +NewStr.trim();
				}
				else
				{
					String a1=Integer.toString(New);
					LotNoFrom = Set + NewStr.trim() +a1.trim();
				}
				if( dbName.equalsIgnoreCase("db2"))
				{
					//validupto=datetime(ValidUpTo);
					if(refNoold !=null && refNoold.trim().length() > 0)
					{
						sql="update pricelist set valid_upto = ? lot_no__to = ? where price_list = ? and item_code = ? and unit =? and list_type = ? and min_qty >= ? and max_qty <= ? and slab_no = ? and ref_no  =?";
						pstmtSql=conn.prepareStatement(sql);
						pstmtSql.setTimestamp(1, validupto);
						pstmtSql.setString(2, LotNoFrom);
						pstmtSql.setString(3, plist);
						pstmtSql.setString(4, itemcode);
						pstmtSql.setString(5, unit);
						pstmtSql.setString(6, listtype);
						pstmtSql.setDouble(7, minqty);
						pstmtSql.setDouble(8, maxqty);
						pstmtSql.setDouble(9, SlabNo);
						pstmtSql.setString(10, refNoold);
						int upcnt1=pstmtSql.executeUpdate();	
						System.out.println("No. of rows updated11111111111------>>"+upcnt1);
						if(pstmtSql !=null)
						{
							pstmtSql.close();
							pstmtSql=null;
						}
					}
					else
					{
						sql="update pricelist set valid_upto = ? lot_no__to = ? where price_list = ? and item_code = ? and unit =? and list_type = ? and min_qty >= ? and max_qty <= ? and slab_no = ? and ref_no  is null ";
						pstmtSql=conn.prepareStatement(sql);
						pstmtSql.setTimestamp(1, validupto);
						pstmtSql.setString(2, LotNoFrom);
						pstmtSql.setString(3, plist);
						pstmtSql.setString(4, itemcode);
						pstmtSql.setString(5, unit);
						pstmtSql.setString(6, listtype);
						pstmtSql.setDouble(7, minqty);
						pstmtSql.setDouble(8, maxqty);
						pstmtSql.setDouble(9, SlabNo);
						int upcnt1=pstmtSql.executeUpdate();	
						System.out.println("No. of rows updated11111111111------>>"+upcnt1);
						if(pstmtSql !=null)
						{
							pstmtSql.close();
							pstmtSql=null;
						}
					}
					
				}
				else
				{
					if(refNoold !=null && refNoold.trim().length() > 0)
					{
						sql="update pricelist set valid_upto = ? lot_no__to = ? where price_list = ? and item_code = ? and unit =? and list_type = ? and min_qty >= ? and max_qty <= ? and slab_no = ? and ref_no  =?";
						pstmtSql=conn.prepareStatement(sql);
						pstmtSql.setTimestamp(1,ValidUpTo);
						pstmtSql.setString(2, LotNoFrom);
						pstmtSql.setString(3, plist);
						pstmtSql.setString(4, itemcode);
						pstmtSql.setString(5, unit);
						pstmtSql.setString(6, listtype);
						pstmtSql.setDouble(7, minqty);
						pstmtSql.setDouble(8, maxqty);
						pstmtSql.setDouble(9, SlabNo);
						pstmtSql.setString(10, refNoold);
						int upcnt1=pstmtSql.executeUpdate();	
						System.out.println("No. of rows updated11111111111------>>"+upcnt1);
						if(pstmtSql !=null)
						{
							pstmtSql.close();
							pstmtSql=null;
						}
					}
					else
					{
						sql="update pricelist set valid_upto = ? ,lot_no__to = ? where price_list = ? and item_code = ? and unit =? and list_type = ? and min_qty >= ? and max_qty <= ? and slab_no = ? and ref_no  is null ";
						pstmtSql=conn.prepareStatement(sql);
						pstmtSql.setTimestamp(1, ValidUpTo);
						pstmtSql.setString(2, LotNoFrom);
						pstmtSql.setString(3, plist);
						pstmtSql.setString(4, itemcode);
						pstmtSql.setString(5, unit);
						pstmtSql.setString(6, listtype);
						pstmtSql.setDouble(7, minqty);
						pstmtSql.setDouble(8, maxqty);
						pstmtSql.setDouble(9, SlabNo);
						int upcnt1=pstmtSql.executeUpdate();	
						System.out.println("No. of rows updated11111111111------>>"+upcnt1);
						if(pstmtSql !=null)
						{
							pstmtSql.close();
							pstmtSql=null;
						}
					}
				}
			}
			
			}
         
			}
		                 recordfound = false;
		                if(listtype.equalsIgnoreCase("B"))
		                {
		                	LotNoFrom=(String)PList.get("lot_no__from");
		        			System.out.println("@@@@@lot number from2222222222222"+LotNoFrom);
		        			sql="select count(1) from pricelist where price_list = ? and item_code = ? and unit = ? and list_type = ? and lot_no__from <= ? and lot_no__to >= ? and min_qty <= ? and max_qty >= ?";
		        			pstmtSql=conn.prepareStatement(sql);
		        			pstmtSql.setString(1,plist);
		        			pstmtSql.setString(2,itemcode);
		        			pstmtSql.setString(3,unit);
		        			pstmtSql.setString(4,listtype);
		        			pstmtSql.setString(5,LotFrBrow);
		        			pstmtSql.setString(6,LotNoToBrow);
		        			pstmtSql.setDouble(7,minqty);
		        			pstmtSql.setDouble(8,maxqty);
		            		rs=pstmtSql.executeQuery();
		            		if(rs.next())
		            		{
		            			cnt=rs.getInt(1);
		            		}
		            		System.out.println("llCount22222222222-------->>["+cnt+"]");
		            		if(rs != null)
		        			{
		        				rs.close();
		        				rs=null;
		        			}
		        			if(pstmtSql !=null)
		        			{
		        				pstmtSql.close();
		        				pstmtSql=null;
		        			}
		        			
		            		if(cnt > 0)
		            		{
		            			LotNoFrom=(String)PList.get("lot_no__from");
		            		    LotNoTo=(String)PList.get("lot_no__to");
		            		    nextlotnofr=LotNoFrom;
		            		    sql="select lot_no__from, lot_no__to from pricelist where price_list = ? and item_code = ? and unit = ? and list_type = ? and lot_no__from <= ? and lot_no__to >= ? and min_qty <= ? and max_qty >= ?  order by lot_no__from";
		            		    pstmtSql=conn.prepareStatement(sql);
			        			pstmtSql.setString(1,plist);
			        			pstmtSql.setString(2,itemcode);
			        			pstmtSql.setString(3,unit);
			        			pstmtSql.setString(4,listtype);
			        			pstmtSql.setString(5,lotnofrom);
			        			pstmtSql.setString(6,LotNoToBrow);
			        			pstmtSql.setDouble(7,minqty);
			        			pstmtSql.setDouble(8,maxqty);
			            		rs=pstmtSql.executeQuery();
			            		while(rs.next())
			            		{
			            			currentlotnofr=rs.getString(1);
			            			currentlotnoto=rs.getString(2);
			            		}
			            		recordfound = true;
			            		currentlotnofrtemp = currentlotnofr;
			        			currentlotnototemp= currentlotnoto;
			        			
			        			Len=currentlotnofr.trim().length();
			        			Ctr =1;
			        			OrigStr = "";
			        			Set ="";
			        			NewStr ="";
			        			New = 0;
			        			
			        			String x=LotNoFrom.trim();
			        			System.out.println("@@@@@x"+x);
			        			String y=currentlotnofr.trim();
			        			System.out.println("@@@@@x"+y);
			        			if(x.length()<y.length())
			        			{
			        				char right3= y.charAt(y.length()-1);
			        				if(Character.isDigit(right3) )
			        				{
			        					do
			        					{
			        					  if(Character.isDigit(right3) )
			        					  {
			        					    left3=y.charAt(0);
			        					    if(left3=='0')
			        					     {
			        					    	
			        					    	if(y.length()>2)
			        					    	{
			        					    	
			        						   mid3= y.substring(2);
			        						   Set=Set + '0';
			        					    	}
			        					     }
			        					   else
			        					     {
			        						   OldLen = currentlotnofr.trim().length();
			        						   New = OldLen - 1;
			        						   String new1=Integer.toString(New);
			        						   NewLen = new1.trim().length();
			        						   if(OldLen != NewLen);
			        						     {
			        						    	 int diff1=(int) (OldLen - NewLen);
			        						    	 for(int s=0;s<diff1;s++)
			        						    	 {
			        						    		 NewStr=NewStr.concat("0");
			        						    		 System.out.println("@@@@@@@@@New String"+NewStr);
			        						    	 }
			        						     }	
			        						
			        					     }
			        					  }
			        					  else
			        					  {     OrigStr = Character.toString(left3);
			        						    Set = Set + OrigStr;
			        							LotNoFrom = (mid3 + 1); 
			        					  }
			        					  Ctr++;
			        					  }while(Ctr <= Len);
			        				//	lotnoto = Set + trim(ls_NewStr) +string(ll_New)
			        					//ls_lot_no__fr = ls_next_lot_no_fr
			        					String a2=Integer.toString(New);
			        					lotnoto = Set + NewStr.trim() +a2.trim();
			        					lotnofr = nextlotnofr;
		                         }
			        		
			        			
			                   }
                                        Len1=currentlotnoto.trim().length();
			        					Ctr =1;
					        			OrigStr = "";
					        			Set ="";
					        			NewStr ="";
					        			New = 0;
					        			String p=LotNoFrom.trim();
					        			String q=currentlotnofr.trim();
					        			if(p.length()<q.length())
					        			{
					        			char right4= y.charAt(y.length()-1);
					        			if(Character.isDigit(right4) )
					        			{
					        			do
					        			{
					        			if(Character.isDigit(right4) )
					        			 {
					        			            left4 =y.charAt(0);
					        					    if(left4=='0')
					        					     {
					        					    	if(y.length()>2)
					        					    	{
					        						   mid4= y.substring(2);
					        						   Set=Set + '0';
					        					    	}
					        					     }
					        					   else
					        					     {
					        						   OldLen = currentlotnoto.trim().length();
					        						   New = OldLen - 1;
					        						   String new1=Integer.toString(New);
					        						   NewLen = new1.trim().length();
					        						   if(OldLen != NewLen);
					        						     {
					        						    	 int diff=(int) (OldLen - NewLen);
					        						    	 for(int s=0;s<diff;s++)
					        						    	 {
					        						    		 NewStr=NewStr.concat("0");
					        						    		 System.out.println("@@@@@@@@@New String"+NewStr);
					        						    	 }
					        							   //NewStr = fill('0',OldLen - NewLen);
					        						     }	
					        						
					        					     }
					        		     }
					        					  else
					        					  {     OrigStr = Character.toString(left4);
					        						    Set = Set + OrigStr;
					        							LotNoFrom = (mid4+ 1); 
					        					  }
					        					  Ctr++;
					        					  }while(Ctr <= Len);
					        				//	lotnoto = Set + trim(ls_NewStr) +string(ll_New)
					        					//ls_lot_no__fr = ls_next_lot_no_fr
					        					String a2=Integer.toString(New);
					        					lotnofr = Set + NewStr.trim() +a2.trim();
					        					lotnofr = nextlotnofr;
				                          }
					                      }
					        			        int temp=0;
		                                       if(currentlotnofrtemp != LotNoFrom)
		                                       {
                                                    temp =Integer.parseInt(LotNoFrom);
                                                    int temp1=Integer.parseInt(LotNoTo);
		                                    	    nextlotnofr=LotNoFroma[temp]+1;
		                                    	    lotnoto=LotNoToa[temp1]+1;
		                                       }
		                                       //int tempnew=0;
		                                    /*   if(currentlotnototemp <= LotNoTo)
		                                       {
		                                    	   tempnew =Integer.parseInt(LotNoFrom);
                                                   int temp2=Integer.parseInt(LotNoTo);
		                                    	    nextlotnofr=LotNoFroma[temp]+1;
		                                    	    lotnoto=LotNoToa[temp2]+1;
		                                       }*/
		                                       for(int i=1;i<temp;i++)
		                                       {
		                                    	lotnofr = LotNoFroma[i];
		                                    	lotnoto = LotNoToa[i];  
		                                       sql="select  max(slab_no)  from pricelist where price_list = ? AND item_code = ? ";
		                                       pstmtSql = conn.prepareStatement(sql);
		                                       pstmtSql.setString(1, plist);
		                                       pstmtSql.setString(2, itemcode);
		                                       rs = pstmtSql.executeQuery();
		                       				   if ( rs.next() )
		                       				   {
		                       					LineNo = rs.getInt(1);
		                       				    System.out.println("line no@@@@@@@"+LineNo);
		                       				   }
		                       				    rs.close();
		                       				    rs = null;
		                       				    pstmtSql.close();
		                       				    pstmtSql = null;
		                       					if( LineNo == 0)
		                       					{
		                       						System.out.println("Insert line no");
		                       						LineNo++;
		                       						sql="insert into pricelist (price_list,	item_code,unit,list_type,slab_no,eff_from,valid_upto,lot_no__from,lot_no__to,min_qty,max_qty,rate,rate_type,min_rate,chg_date,chg_user,chg_term,max_rate,order_type,	price_list__parent,	chg_ref_no,	ref_no,ref_no_old  )" +
		                       								"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		                       						pstmtInsert = conn.prepareStatement(sql);
		                       						pstmtInsert.setString(1, plist);
		                       						pstmtInsert.setString(2, itemcode);
		                       						pstmtInsert.setString(3, unit);
		                       						pstmtInsert.setString(4, listtype);
		                       						pstmtInsert.setDouble(5, LineNo);
		                       						pstmtInsert.setTimestamp(6, validupto1);
		                       						pstmtInsert.setTimestamp(7, ValidUpTo);
		                       						pstmtInsert.setString(8,lotnofr);
		                       						pstmtInsert.setString(9, lotnoto);
		                       						pstmtInsert.setDouble(10, minqty);
		                       						pstmtInsert.setDouble(11, maxqty);
		                       						pstmtInsert.setDouble(12, rate1);
		                       						pstmtInsert.setString(13, ratetype);
		                       						pstmtInsert.setDouble(14, minrate);
		                       						chgDate = new java.sql.Timestamp(System.currentTimeMillis());
		                       						pstmtInsert.setTimestamp(15, chgDate);
		                       						pstmtInsert.setString(16, userId);
		                       						pstmtInsert.setString(17, termId);
		                       						pstmtInsert.setDouble(18, maxrate1);
		                       						pstmtInsert.setString(19, orderType1);
		                       						pstmtInsert.setString(20, PriceListParent1);
		                       						pstmtInsert.setString(21, chgref1);
		                       						pstmtInsert.setString(22, refNo1);
		                       						pstmtInsert.setString(23, refNoold1);
		                       					    UpdCnt = pstmtInsert.executeUpdate();
		                       						pstmtInsert.close();
		            								pstmtInsert = null;			
		                       						
		                       						
		                       					}
		                       				    
		                                        }
		            		                  }
		                                    }
		                                            if(LineNo == 0)
		                                            {
		                                            	  sql="select  max(slab_no)  from pricelist where price_list = ? AND item_code = ? ";
		   		                                       pstmtSql = conn.prepareStatement(sql);
		   		                                       pstmtSql.setString(1, plist);
		   		                                       pstmtSql.setString(2, itemcode);
		   		                                       rs = pstmtSql.executeQuery();
		   		                       				   if ( rs.next() )
		   		                       				   {
		   		                       					LineNo = rs.getInt(1);
		   		                       				   }
		   		                       				    rs.close();
		   		                       				    rs = null;
		   		                       				    pstmtSql.close();
		   		                       				    pstmtSql = null;
		                                            }
		                                             System.out
															.println("Record found@@@@@@@@@@@@@"+recordfound);
		                                             if(recordfound == false)
		                                             {
		                                            	 LineNo++;
		                                            	 sql="insert into pricelist (price_list,item_code,unit,list_type,slab_no,eff_from,valid_upto,lot_no__from,lot_no__to,min_qty,max_qty,rate,rate_type,min_rate,chg_date,chg_user,chg_term,max_rate,order_type,price_list__parent,	chg_ref_no,	ref_no,ref_no_old  )" +
		                       								"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		                       						pstmtInsert = conn.prepareStatement(sql);
		                       						pstmtInsert.setString(1, plist);
		                       						pstmtInsert.setString(2, itemcode);
		                       						pstmtInsert.setString(3, unit);
		                       						pstmtInsert.setString(4, listtype);
		                       						pstmtInsert.setDouble(5, slabno);
		                       						pstmtInsert.setTimestamp(6, EffDate);
		                       						pstmtInsert.setTimestamp(7, ValidUpTo);
		                       						pstmtInsert.setString(8,lotnofr);
		                       						pstmtInsert.setString(9, lotnoto);
		                       						pstmtInsert.setDouble(10, minqty);
		                       						pstmtInsert.setDouble(11, maxqty);
		                       						pstmtInsert.setDouble(12, rate1);
		                       						pstmtInsert.setString(13, ratetype);
		                       						pstmtInsert.setDouble(14, minrate);
		                       						chgDate = new java.sql.Timestamp(System.currentTimeMillis());
		                       						pstmtInsert.setTimestamp(15, chgDate);
		                       						pstmtInsert.setString(16, userId);
		                       						pstmtInsert.setString(17, termId);
		                       						pstmtInsert.setDouble(18, maxrate1);
		                       						pstmtInsert.setString(19, orderType1);
		                       						pstmtInsert.setString(20, PriceListParent1);
		                       						pstmtInsert.setString(21, chgref1);
		                       						pstmtInsert.setString(22, refNo1);
		                       						pstmtInsert.setString(23, refNoold1);
		                       					    UpdCnt = pstmtInsert.executeUpdate();
		                       						pstmtInsert.close();
		            								pstmtInsert = null;			
		                                            	 
		                                             }
		                                            
		                                            System.out.println("Insert completed"); 
		                                           chgDate = new java.sql.Timestamp(System.currentTimeMillis());
		                                           String sql1="update pricelist set chg_user =?,chg_term = ?,chg_date = ? where price_list =? and  item_code=? and unit = ? and list_type =?";
		                                           pstmtSql = conn.prepareStatement(sql1);
		        								   chgDate = new java.sql.Timestamp(System.currentTimeMillis());
		        								   pstmtSql.setString(1, userId);
		        								   pstmtSql.setString(2, termId);
		        								   pstmtSql.setTimestamp(3, chgDate);
		        								   pstmtSql.setString(4,plist);
		        								   pstmtSql.setString(5,itemcode);
		        								   pstmtSql.setString(6, unit);
		        								   pstmtSql.setString(7, listtype);
		        								   updCnt = pstmtSql.executeUpdate();
		        								   pstmtSql.close();
		        								   pstmtSql = null;	
		                                            
		}//try end
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}	 
         return retString;
	}


	public String confirmPlist(String tranId, Connection conn, String xtraParams) throws RemoteException, ITMException
	{
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		String sql = "";
		String dbName = "";
		String retString = "";
		String errCode="", priceList="",confirmed="",empcode="", itemCode="", lotNoForm="";
		String lotNoTo ="", rateType="", chgref="", listType="", PriceListParent="",PriceList="";
		String orderType="", manageType="", plist1="",plist2="",plist3="",plist4="",plist5="",plist6="",plist7="",plist8="";
		String plist9="",plist10="", plist11="",plist12="",unit="", refNoold="",refNO="",data="",calc="",Str="";
		String calcmeth="",plisttar="";
		java.sql.Timestamp tranDate = null,confDate=null, efffrom=null,validup= null;
		double rate=0.0;
		double minrate=0.0;
		double maxrate=0.0;
		double rate1=0.0;
		double rate2=0.0;
		double rate3=0.0;
		double rate4=0.0;
		double rate5=0.0;
		double rate6=0.0;
		double rate7=0.0;
		double rate8=0.0;
		double rate9=0.0;
		double rate10=0.0;
		double rate11=0.0;
		double rate12=0.0;
		double minqty=0.0;
		double maxqty=0.0;
		int lineno=0;
		String loginEmpCode = "";
		ArrayList PricelistGen = new ArrayList();
		HashMap PList=null;
		try
		{
			DistCommon distCommon = new DistCommon();
			ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
			ValidatorEJB validatorEJB = null;
			System.out.println("Printing loginEmpCode---1-----"+loginEmpCode+"--");
			dbName = CommonConstants.DB_NAME;
			if( dbName.equalsIgnoreCase("db2"))
			{
				sql="select tran_date, price_list,confirmed,conf_date from pricelist_hdr where tran_id =? for update with RS ";
				
			}
			else if ( dbName.equalsIgnoreCase("mysql") )
			{
				sql="select tran_date, price_list,confirmed,conf_date from pricelist_hdr where tran_id =? for update";
			}
			else if(dbName.equalsIgnoreCase("mssql"))
			{
				sql="select tran_date, price_list,confirmed,conf_date from pricelist_hdr where tran_id =?";
			}
			else
			{
				sql="select tran_date, price_list,confirmed,conf_date from pricelist_hdr where tran_id =?  for update nowait";
				
			}
			try
			{
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranId);
				rs = pstmtSql.executeQuery();
				if ( rs.next() )
				{
					tranDate= rs.getTimestamp(1);
					priceList = rs.getString(2);
					confirmed=rs.getString(3);
					confDate=rs.getTimestamp(4);
					pstmtSql.close();
					pstmtSql = null;
					rs.close();
					rs = null;
				}
				else
				{
					pstmtSql.close();
					pstmtSql = null;
					rs.close();
					rs = null;
					retString = itmDBAccessLocal.getErrorString("","VTLCKERR","");
					return retString; 
				}
			}
			catch(Exception e)
			{
				retString = itmDBAccessLocal.getErrorString("","VTLCKERR","");
				throw new ITMException(e);
			}
			sql="select d.item_code,d.lot_no__from,d.lot_no__to,d.rate,d.rate_type,d.min_rate,d.max_rate,d.chg_ref_no,d.eff_from,d.valid_upto,m.price_list,m.list_type ,m.price_list__parent,m.order_type,m.manage_type,d.min_qty ,d.max_qty,d.unit,d.line_no,h.ref_no,ref_no_old,h.calc_basis from pricelist_hdr h, pricelist_det d, pricelist_mst m where h.tran_id = d.tran_id and h.price_list = m.price_list  and h.tran_id = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranId);
			rs = pstmtSql.executeQuery();
			while ( rs.next() )
			{
				PList = new HashMap();
				PList.put("item_code",itemCode=rs.getString("item_code"));
				PList.put("lot_no__from",lotNoForm=rs.getString("lot_no__from"));
				PList.put("lot_no__to",lotNoTo=rs.getString("lot_no__to"));
				PList.put("rate",rate = rs.getDouble("rate"));
				PList.put("rate_type",rateType=rs.getString("rate_type"));
				PList.put("min_rate",minrate=rs.getDouble("min_rate"));
				PList.put("max_rate",maxrate=rs.getDouble("max_rate"));
				PList.put("chg_ref_no",chgref=rs.getString("chg_ref_no"));
				PList.put("eff_from",efffrom=rs.getTimestamp("eff_from"));
				PList.put("valid_upto",validup=rs.getTimestamp("valid_upto"));
				PList.put("price_list",PriceList=rs.getString("price_list"));
				PList.put("list_type",listType=rs.getString("list_type"));
				PList.put("price_list__parent",PriceListParent=rs.getString("price_list__parent"));
				PList.put("order_type",orderType=rs.getString("order_type"));
				PList.put("manage_type",manageType =rs.getString("manage_type"));
				PList.put("min_qty",minqty=rs.getDouble("min_qty"));
				PList.put("max_qty",maxqty=rs.getDouble("max_qty"));
				PList.put("unit",unit=rs.getString("unit"));
				PList.put("line_no",lineno=rs.getInt("line_no"));
				PList.put("ref_no",refNO=rs.getString("ref_no"));
				PList.put("ref_no_old",refNoold=rs.getString("ref_no_old"));
				PList.put("calc_basis",calc=rs.getString("calc_basis"));
			    rs.close();
			    rs = null;
			    pstmtSql.close();
			    pstmtSql = null;
			
			/*PList = new HashMap();
			PList.put(PriceList,"PriceList");
			PList.put(itemCode, "item_code");
			PList.put(unit,"unit");
			PList.put(listType,"list_type");
			PList.put(validup,"valid_upto");
			PList.put(lotNoForm,"lot_no__from");
			PList.put(lotNoTo,"lot_no__to");
			PList.put(minqty,"min_qty");
			PList.put(maxqty,"max_qty");
			PList.put(rate,"rate");
			PList.put(rateType,"rate_type");
			PList.put(minrate,"min_rate");
			PList.put(maxrate,"max_rate");
			PList.put(orderType,"order_type");
			PList.put(PriceListParent,"price_list__parent");
			PList.put(chgref,"chg_ref_no");
			PList.put(refNO,"ref_no");
			PList.put(refNoold,"ref_no_old");
			PList.put(calc,"calc_basis");*/
			PricelistGen.add(PList);
			System.out.println("@@@@@@@@@@@@@Price list@@@@@@@@@@@@@@@@"+PricelistGen);
			retString = insertPricelist(tranId,PList,PricelistGen, conn,xtraParams);
			if ( retString != null && retString.trim().length() > 0)
			{
				return retString;
			}
			sql="select calc_method, price_list__tar , price_list__parent from pricelist_mst_det where price_list = ? order by line_no ";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, PriceList);
			rs = pstmtSql.executeQuery();
			while ( rs.next() )
			{
				calcmeth=rs.getString(1);
				plisttar=rs.getString(2);
				PriceListParent=rs.getString(3);
			
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;
			
			PList.put(plisttar,"price_list__tar");
			PList.put(PriceListParent,"price_list__parentfrom");
		//	PList.put(PriceListParent,"rate");
			
			
			//retString= CalcRate(plisttar,data,calcmeth,errCode);
			if ( retString != null && retString.trim().length() > 0)
			{
				return retString;
			}
			
		  }
		}
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retString;
		
		
		
	}

}
