/*......................     win_name=w_dist_order(d_dist_order_edit/d_distorderdet_brow)
Name :- BaseInfo Pvt Ltd.
Modification:-
		Reason						Date[Like 05052007 all modified code should contain this so that search easier]

1- if listtype is i then find the site code .lotno from lotno parameter [25052007]
2-

3-					
.............................................*/
package ibase.webitm.ejb.dis;
import java.sql.*;
import java.util.*;
import java.text.NumberFormat;
import java.text.DecimalFormat;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.ejb.fin.FinCommon;
public class DistCommon
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	public String setPlistTaxClassEnv(String siteCodeFrom,String siteCodeTo,String itemCode,String tranType,String itemSer,String caseString,Connection conn)throws ITMException
	{
		//System.out.println("--------------[setPlistTaxClassEnv]---------------------------------");

		//System.out.println("DP[siteCodeto/SiteCodeFrom/itemCode/tranType/itemSer/caseString/conn]");
		//if(siteCodeFrom!=null)
		//System.out.println("Siite Code From:-["+siteCodeFrom+"]");
		//if(siteCodeTo!=null)
		//System.out.println("Site Code To:-["+siteCodeTo+"]");
		//if(itemCode!=null)
		//System.out.println("Item Code:-["+itemCode+"]");
		//if(tranType!=null)
		//System.out.println("TranType:-["+tranType+"]");
		//if(caseString!=null)
		//System.out.println("Case String:-["+caseString+"]");
		//if(itemSer!=null)
		//System.out.println("Item Ser:-["+itemSer+"]");
		//if(conn!=null)
		//System.out.println("Connection Found["+conn+"]");
		//System.out.println("-----------------------------------------------");

		String sql = "";
		String sql2 = "";
		String priceList = "";
		String priceListCLG = "";
		String taxClass = "";
		String taxEnv = "";
		String returnVlaue = "";
		Statement stmt = null;
		Statement stmt2 = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	
		if(itemSer == null)
		{
			itemSer = "";
		}
		if(caseString.equals("PRICE_LIST"))
		{
			int flag=0;
			try
			{
				sql = "SELECT PRICE_LIST FROM ITEM_ACCT_DETR_DIST "
					+ "WHERE SITE_CODE__FROM = ? "
				    + "AND SITE_CODE__TO = ? "
					+ "AND ITEM_SER = ? "
					+ "AND ITEM_CODE = ? "
					+ "AND TRAN_TYPE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,siteCodeFrom);
				pstmt.setString(2,siteCodeTo);
				pstmt.setString(3,itemSer);
				pstmt.setString(4,itemCode);
				pstmt.setString(5,tranType);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					priceList = rs.getString(1);
					//System.out.println("PriceList1 :-["+priceList+"]");
					 flag=1;
				}
				if((flag==0) || (priceList == null))  
				{
					flag=0;
					rs.close();
					priceList = "";
					sql = "SELECT PRICE_LIST FROM ITEM_ACCT_DETR_DIST "
						+ "WHERE SITE_CODE__FROM = ? "
						+ "AND SITE_CODE__TO = ? "
						+ "AND ITEM_SER = '' "
						+ "AND ITEM_CODE = ? "
						+ "AND TRAN_TYPE = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,siteCodeFrom);
					pstmt.setString(2,siteCodeTo);
					pstmt.setString(3,itemCode);
					pstmt.setString(4,tranType);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						priceList = rs.getString(1);
						//System.out.println("PriceList2 :-["+priceList+"]");
						flag=1;
					}
					if((flag==0) || (priceList == null)) //if 2
					{
						rs.close();
						flag=0;
						priceList = "";
						sql = "SELECT PRICE_LIST FROM ITEM_ACCT_DETR_DIST "
							+ "WHERE SITE_CODE__FROM = ? "
							+ "AND SITE_CODE__TO = ? "
							+ "AND ITEM_SER = '' "
							+ "AND ITEM_CODE = ? "
							+ "AND TRAN_TYPE = '' ";
						pstmt = conn.prepareStatement(sql);
			
						pstmt.setString(1,siteCodeFrom);
						pstmt.setString(2,siteCodeTo);
						pstmt.setString(3,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							priceList = rs.getString(1);
							//System.out.println("PriceList3 :-["+priceList+"]");
							flag=1;
						}
						if((flag==0) || (priceList == null)) //if 3
						{
								rs.close();
							priceList = "";
							sql = "SELECT PRICE_LIST FROM ITEM_ACCT_DETR_DIST "
								+ "WHERE SITE_CODE__FROM = ? "
								+ "AND SITE_CODE__TO = ? "
								+ "AND ITEM_SER = ? "
								+ "AND ITEM_CODE = '' "
								+ "AND TRAN_TYPE = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,siteCodeFrom);
							pstmt.setString(2,siteCodeTo);
							pstmt.setString(3,itemSer);
							pstmt.setString(4,tranType);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								priceList = rs.getString(1);
								//System.out.println("PriceList4 :-["+priceList+"]");
								flag=1;
							}
							
							if((flag==0) || (priceList == null)) //if 4
							{
								flag=0;
								rs.close();
								priceList = "";
								sql = "SELECT PRICE_LIST FROM ITEM_ACCT_DETR_DIST "
									+ "WHERE SITE_CODE__FROM = ? "
									+ "AND SITE_CODE__TO = ? "
									+ "AND ITEM_SER = '' "
									+ "AND ITEM_CODE = '' "
									+ "AND TRAN_TYPE = ''";
								pstmt = conn.prepareStatement(sql);
				
								pstmt.setString(1,siteCodeFrom);
								pstmt.setString(2,siteCodeTo);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									priceList = rs.getString(1);
									//System.out.println("PriceList5 :-["+priceList+"]");
								}
								if((flag==0) || (priceList == null)) //if 5
								{
									flag=0;
									rs.close();
									priceList = "";
									sql = "SELECT PRICE_LIST FROM DISTORDER_TYPE "
										+ "WHERE TRAN_TYPE = '"+tranType+"'";
									stmt = conn.createStatement();
									rs = stmt.executeQuery(sql);
									if(rs.next())
									{
										priceList = rs.getString(1);
										//System.out.println("PriceList6 :-["+priceList+"]");
									}//if
									stmt.close();
									stmt = null;
								}//if 5
							}//if 4
						}//if 3
					}//if 2
				}//if 1
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try
			catch (SQLException e)
			{
				System.out.println("SQLException : :" +sql+ e.getMessage() + ":");
			}
			catch(Exception ex)
			{
				System.out.println("Exception []::"+ sql +ex.getMessage());
				ex.printStackTrace();
			}
			if( (priceList == null) || (priceList.trim().length() == 0))
			{
				priceList = getDisparams("999999","DEFAULT_PRICE",conn);
			}
			//System.out.println("Returning the priceList From Dist Common------->");
			//System.out.println("************** ["+priceList+"]**************");
			returnVlaue = priceList;
		}//if(caseString.equals("PRICE_LIST"))
	
		else if(caseString.equals("PRICE_LIST__CLG"))
		{
			int flag =0;
			try
			{
				sql = "SELECT PRICE_LIST__EXCISE FROM ITEM_ACCT_DETR_DIST "
					+ "WHERE SITE_CODE__FROM = ? "
				    + "AND SITE_CODE__TO = ? "
					+ "AND ITEM_SER = ? "
					+ "AND ITEM_CODE = ? "
					+ "AND TRAN_TYPE = ? ";
					
				pstmt = conn.prepareStatement(sql);
		
				pstmt.setString(1,siteCodeFrom);
				pstmt.setString(2,siteCodeTo);
				pstmt.setString(3,itemSer);
				pstmt.setString(4,itemCode);
				pstmt.setString(5,tranType);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					priceListCLG = rs.getString(1);
					//System.out.println("PriceListCLG1:-["+priceListCLG+"]");
					flag=1;
				}
				
				if((flag==0) || (priceListCLG == null))  //if 1
				{
					rs.close();
				
					priceListCLG = "";
					//System.out.println("Inside else if 1 CLG");
					sql = "SELECT PRICE_LIST__EXCISE FROM ITEM_ACCT_DETR_DIST "
						+ "WHERE SITE_CODE__FROM = ? "
						+ "AND SITE_CODE__TO = ? "
						+ "AND ITEM_SER = '' "
						+ "AND ITEM_CODE = ? "
						+ "AND TRAN_TYPE = ? ";
						
					pstmt = conn.prepareStatement(sql);
			
					pstmt.setString(1,siteCodeFrom);
					pstmt.setString(2,siteCodeTo);
					pstmt.setString(3,itemCode);
					pstmt.setString(4,tranType);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						priceListCLG = rs.getString(1);
						//System.out.println("PriceListCLG2:-["+priceListCLG+"]");
						flag=1;
					}
					if((flag==0) || (priceListCLG == null))  //if 2
					{
						rs.close();
						priceListCLG = "";
						//System.out.println("Inside else if 2 CLG");
						sql = "SELECT PRICE_LIST__EXCISE FROM ITEM_ACCT_DETR_DIST "
							+ "WHERE SITE_CODE__FROM = ? "
							+ "AND SITE_CODE__TO = ? "
							+ "AND ITEM_SER = '' "
							+ "AND ITEM_CODE = ? "
							+ "AND TRAN_TYPE = '' ";
							
						pstmt = conn.prepareStatement(sql);
				
						pstmt.setString(1,siteCodeFrom);
						pstmt.setString(2,siteCodeTo);
						pstmt.setString(3,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							priceListCLG = rs.getString(1);
								//System.out.println("PriceListCLG3:-["+priceListCLG+"]");
							flag=1;
						}
						if((flag==0) || (priceListCLG == null))  //if 3
						{
							rs.close();
							priceListCLG = "";
							sql = "SELECT PRICE_LIST__EXCISE FROM ITEM_ACCT_DETR_DIST "
								+ "WHERE SITE_CODE__FROM = ? "
								+ "AND SITE_CODE__TO = ? "
								+ "AND ITEM_SER = ? "
								+ "AND ITEM_CODE = '' "
								+ "AND TRAN_TYPE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,siteCodeFrom);
							pstmt.setString(2,siteCodeTo);
							pstmt.setString(3,itemSer);
							pstmt.setString(4,tranType);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								priceListCLG = rs.getString(1);
								//System.out.println("PriceListCLG4:-["+priceListCLG+"]");
								flag=1;
							}
							if((flag==0) || (priceListCLG == null))  //if 4
							{
								rs.close();
								priceListCLG = "";
								sql = "SELECT PRICE_LIST__EXCISE FROM ITEM_ACCT_DETR_DIST "
									+ "WHERE SITE_CODE__FROM = ? "
									+ "AND SITE_CODE__TO = ? "
									+ "AND ITEM_SER = '' "
									+ "AND ITEM_CODE = '' "
									+ "AND TRAN_TYPE = '' ";
									
								pstmt = conn.prepareStatement(sql);
						
								pstmt.setString(1,siteCodeFrom);
								pstmt.setString(2,siteCodeTo);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									priceListCLG = rs.getString(1);
									//System.out.println("PriceListCLG5:-["+priceListCLG+"]");
									flag=1;
								}
								if((flag==0) || (priceListCLG == null))  //if 5
								{
									rs.close();
									priceListCLG = "";
									sql = "SELECT PRICE_LIST__CLG FROM DISTORDER_TYPE "
										+ "WHERE TRAN_TYPE = '"+tranType+"'";
									stmt = conn.createStatement();
									rs = stmt.executeQuery(sql);
									if(rs.next())
									{
										priceListCLG = rs.getString(1);
										//System.out.println("PriceListCLG6:-["+priceListCLG+"]");
										flag=1;
									}//if
									stmt.close();
									stmt = null;
								}//if 5
							}//if 4
						}//if 3
					}//if 2
				}//if 1
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try
			catch (SQLException e)
			{
				System.out.println("SQLException : :" +sql+ e.getMessage() + ":");
			}
			catch(Exception ex)
			{
				System.out.println("Exception []::"+ sql +ex.getMessage());
				ex.printStackTrace();
				throw new ITMException(ex);
			}
			if((priceListCLG == null) || (priceListCLG.trim().length() == 0))
			{
				priceListCLG = getDisparams("999999","PRICE_LIST__CLG",conn);
			}
			//System.out.println("Returning the priceListCLG From Dist Common------->");
			//System.out.println("************** ["+priceListCLG+"]**************");
			returnVlaue =  priceListCLG;
		}//else if(caseString.equals("PRICE_LIST__CLG"))
	
		else if(caseString.equalsIgnoreCase("TAX_CLASS"))
		{
			int flag =0;
			try
			{//try 1
				sql = "SELECT TAX_CLASS FROM ITEM_ACCT_DETR_DIST "
					+ "WHERE SITE_CODE__FROM = ? "
				    + "AND SITE_CODE__TO = ? "
					+ "AND ITEM_SER = ? "
					+ "AND ITEM_CODE = ? "
					+ "AND TRAN_TYPE = ?";
					
				pstmt = conn.prepareStatement(sql);
		
				pstmt.setString(1,siteCodeFrom);
				pstmt.setString(2,siteCodeTo);
				pstmt.setString(3,itemSer);
				pstmt.setString(4,itemCode);
				pstmt.setString(5,tranType);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					taxClass = rs.getString(1);
					flag = 1;
					//System.out.println("taxClass 1............."+taxClass);
				}
				if((flag ==0) || (taxClass == null))  //if 1
				{
					flag = 0;
					rs.close();
					rs=null;
					taxClass = "";
					//System.out.println("Inside else if 1 Class");
					try
					{//try 2
						sql = "SELECT TAX_CLASS FROM ITEM_ACCT_DETR_DIST "
							 + "WHERE SITE_CODE__FROM = ? "
							 + "AND SITE_CODE__TO = ? "
							 + "AND ITEM_SER = '' "
							 + "AND ITEM_CODE = ? "
							 + "AND TRAN_TYPE = ?";
							
						pstmt = conn.prepareStatement(sql);
				
						pstmt.setString(1,siteCodeFrom);
						pstmt.setString(2,siteCodeTo);
						pstmt.setString(3,itemCode);
						pstmt.setString(4,tranType);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							taxClass = rs.getString(1);
							//System.out.println("taxClass 2............."+taxClass);
							flag =1	;
						}
						if((flag ==0) || (taxClass == null))  //if 2
						{
							flag = 0;
							rs.close();
							rs=null;
						
							taxClass = "";
							//System.out.println("Inside else if 2 Class");
							try
							{//try 3
								taxClass = "";
								sql = "SELECT TAX_CLASS FROM ITEM_ACCT_DETR_DIST "
									 + "WHERE SITE_CODE__FROM = ? "
									 + "AND SITE_CODE__TO = ? "
									 + "AND ITEM_SER = '' "
									 + "AND ITEM_CODE = ? "
									 + "AND TRAN_TYPE = ''";
									
								pstmt = conn.prepareStatement(sql);
						
								pstmt.setString(1,siteCodeFrom);
								pstmt.setString(2,siteCodeTo);
								pstmt.setString(3,itemCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									taxClass = rs.getString(1);
									//System.out.println("taxClass 3............."+taxClass);
									flag =1;
								}
								if((flag ==0) || (taxClass == null))  //if 3
								{
									flag = 0;
									rs.close();
									rs=null;
									//System.out.println("Inside else if 3 Class");
									taxClass = "";
									try
									{//try 4
										try
										{//try 4b
											itemSer = "";
											sql2 = "SELECT ITEM_SER FROM SITEITEM "
											     + "WHERE SITE_CODE = '"+siteCodeFrom+"' AND ITEM_CODE = '"+itemCode+"'";
											stmt2 = conn.createStatement();
											rs2 = stmt2.executeQuery(sql2);   
										    
											if(rs2.next())
											{
												itemSer = rs2.getString(1);
												//System.out.println("itemSer 1............."+itemSer);
											}
											rs2.close();
											if((itemSer == null) || (itemSer.trim().length() == 0))  //if 2 
											{
												//System.out.println("Inside else if ...Class ");
												itemSer = "";
												try
												{
													sql2 = "SELECT ITEM_SER FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
													stmt2 = conn.createStatement();
													rs2 = stmt2.executeQuery(sql2);
													if(rs2.next())
													{
														itemSer = rs2.getString(1);
														//System.out.println("itemSer 2............."+itemSer);
													}
												}
												catch(Exception ex)
												{
													System.out.println("Exception []::"+ sql2 +ex.getMessage());
													ex.printStackTrace();
												}
											}// if 2 
											stmt2.close();
											stmt2 = null;
										}//try 4b
										catch(Exception ex)
										{
											System.out.println("Exception []::"+ sql2 +ex.getMessage());
											ex.printStackTrace();
										}
										sql = "SELECT TAX_CLASS FROM ITEM_ACCT_DETR_DIST "
											+ "WHERE SITE_CODE__FROM = ? "
											+ "AND SITE_CODE__TO = ? "
											+ "AND ITEM_SER = ? "
											+ "AND ITEM_CODE = '' "
											+ "AND TRAN_TYPE = ?";
											
										pstmt = conn.prepareStatement(sql);
								
										pstmt.setString(1,siteCodeFrom);
										pstmt.setString(2,siteCodeTo);
										pstmt.setString(3,itemSer);
										pstmt.setString(4,tranType);
										rs = pstmt.executeQuery();
										if(rs.next())
										{
											taxClass = rs.getString(1);
											//System.out.println("taxClass ............."+taxClass);
										
											flag = 0;
										}
										if((flag == 0) || (taxClass == null))  //if 4
										{
											flag = 0;
											rs.close();
											
											//System.out.println("Inside else if 4 Class");
											taxClass = "";
											try
											{//try 5
												taxClass = "";
												sql = "SELECT TAX_CLASS FROM ITEM_ACCT_DETR_DIST "
													 + "WHERE SITE_CODE__FROM = ? "
													 + "AND SITE_CODE__TO = ? "
													 + "AND ITEM_SER = ? "
													 + "AND ITEM_CODE = '' "
													 + "AND TRAN_TYPE = ''";
													
												pstmt = conn.prepareStatement(sql);
										
												pstmt.setString(1,siteCodeFrom);
												pstmt.setString(2,siteCodeTo);
												pstmt.setString(3,itemSer);
												rs = pstmt.executeQuery();
												if(rs.next())
												{
													taxClass = rs.getString(1);
													//System.out.println("taxClass 4............."+taxClass);
													
													flag =1;
												}
												if((flag == 0) || (taxClass == null))  //if 5
												{
													flag=0;
													rs.close();
													//System.out.println("Inside else if 5 Class");
													taxClass = "";
													try
													{//try 6
													    taxClass = "";
														sql = "SELECT TAX_CLASS FROM ITEM_ACCT_DETR_DIST "
															 + "WHERE SITE_CODE__FROM = ? "
															 + "AND SITE_CODE__TO = ? "
															 + "AND ITEM_SER = '' "
															 + "AND ITEM_CODE = '' "
															 + "AND TRAN_TYPE = ''";
															
														pstmt = conn.prepareStatement(sql);
												
														pstmt.setString(1,siteCodeFrom);
														pstmt.setString(2,siteCodeTo);
														rs = pstmt.executeQuery();
														if(rs.next())
														{
															taxClass = rs.getString(1);
															//System.out.println("taxClass 5............."+taxClass);
															flag = 1;
														}
														if((flag==0) || (taxClass == null))  //if 6
														{
															flag = 0;
															rs.close();
															//System.out.println("Inside else if 6 Class");
															taxClass = "";
															try
															{//try 7
																sql = "SELECT TAX_CLASS FROM DISTORDER_TYPE "
																	 + "WHERE TRAN_TYPE = '"+tranType+"'";
																stmt = conn.createStatement();
																rs = stmt.executeQuery(sql);
																if(rs.next())
																{
																	taxClass = rs.getString(1);
																	//System.out.println("taxClass 6............."+taxClass);
																}//if
																stmt.close();
																stmt = null;
															}//try 7
															catch(Exception ex)
															{
																System.out.println("Exception [1]::"+ sql + ex);
																ex.printStackTrace();
															}
														}//if 6
													}//try 6
													catch(Exception ex)
													{
														System.out.println("Exception [2]::"+ sql +ex.getMessage());
														ex.printStackTrace();
													}
												}//if 5
											}//try 5
											catch(Exception ex)
											{
												System.out.println("Exception [3]::"+ sql +ex.getMessage());
												ex.printStackTrace();
											}
										}//if 4
									}//try 4
									catch(Exception ex)
									{
										System.out.println("Exception [4]::"+ sql +ex.getMessage());
										ex.printStackTrace();
									}
								}//if 3
							}//try 3
							catch(Exception ex)
							{
								System.out.println("Exception [5]::"+ sql +ex.getMessage());
								ex.printStackTrace();
								throw new ITMException(ex);
							}
						}//if 2
					}//try 2
					catch(Exception ex)
					{
						System.out.println("Exception [6]::"+ sql +ex.getMessage());
						ex.printStackTrace();
						throw new ITMException(ex);
					}
				}//if 1
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch (SQLException e)
			{
				System.out.println("SQLException [7]: :" +sql+ e.getMessage() + ":");
				e.printStackTrace();
			}
			catch(Exception ex)
			{
				System.out.println("Exception [8]::" +ex.getMessage());
				ex.printStackTrace();
				throw new ITMException(ex);
			}
			if(taxClass==null)taxClass= " " ;
			//System.out.println("Returning the taxClass From Dist Common------->");
			//System.out.println("************** ["+taxClass+"]**************");
			returnVlaue = taxClass;
		}//else if(caseString.equals("TAX_CLASS"))
	
		else if(caseString.equals("TAX_ENV"))
		{
			int flag =0;
			try
			{
				sql = "SELECT TAX_ENV FROM ITEM_ACCT_DETR_DIST "
					+ "WHERE SITE_CODE__FROM = ? "
				    + "AND SITE_CODE__TO = ? "
					+ "AND ITEM_SER = ? "
					+ "AND ITEM_CODE = ? "
					+ "AND TRAN_TYPE = ?";
					
				pstmt = conn.prepareStatement(sql);
		
				pstmt.setString(1,siteCodeFrom);
				pstmt.setString(2,siteCodeTo);
				pstmt.setString(3,itemSer);
				pstmt.setString(4,itemCode);
				pstmt.setString(5,tranType);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					taxEnv = rs.getString(1);
					//System.out.println("taxEnv ............."+taxEnv);
					flag =1;
				}
				if((flag==0) || (taxEnv == null))  //if 1
				{
					flag= 0;
					rs.close();
					taxEnv = "";
					//System.out.println("Inside else if 1 Env");
					sql = "SELECT TAX_ENV FROM ITEM_ACCT_DETR_DIST "
						+ "WHERE SITE_CODE__FROM = ? "
						+ "AND SITE_CODE__TO = ? "
						+ "AND ITEM_SER = '' "
						+ "AND ITEM_CODE = ? "
						+ "AND TRAN_TYPE = ?";
						
					pstmt = conn.prepareStatement(sql);
			
					pstmt.setString(1,siteCodeFrom);
					pstmt.setString(2,siteCodeTo);
					pstmt.setString(3,itemCode);
					pstmt.setString(4,tranType);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						taxEnv = rs.getString(1);
						//System.out.println("taxEnv ............."+taxEnv);
						flag= 1;
					}
					if((	flag==0) || (taxEnv == null))  //if 2
					{
						flag= 0;
						rs.close();
						taxEnv = "";
						//System.out.println("Inside else if 2 Env");
						sql = "SELECT TAX_ENV FROM ITEM_ACCT_DETR_DIST "
							+ "WHERE SITE_CODE__FROM = ? "
							+ "AND SITE_CODE__TO = ? "
							+ "AND ITEM_SER = '' "
							+ "AND ITEM_CODE = ? "
							+ "AND TRAN_TYPE = ''";
							
						pstmt = conn.prepareStatement(sql);
				
						pstmt.setString(1,siteCodeFrom);
						pstmt.setString(2,siteCodeTo);
						pstmt.setString(3,itemCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							taxEnv = rs.getString(1);
							//System.out.println("taxEnv ............."+taxEnv);
							flag=1;
						}
						if((flag==0) || (taxClass == null))  // if 3
						{
							flag =0;
							rs.close();
							taxEnv = "";
							//System.out.println("Inside else if 3 Env");
							try
							{//try 4b
								itemSer = "";
								sql2 = "SELECT ITEM_SER FROM SITEITEM "
								     + "WHERE SITE_CODE = '"+siteCodeFrom+"' AND ITEM_CODE = '"+itemCode+"'";
								stmt2 = conn.createStatement();
								rs2 = stmt2.executeQuery(sql2);   
								if(rs2.next())
								{
									itemSer = rs2.getString(1);
									//System.out.println("itemSer 1 in env............."+itemSer);
								}
								rs2.close();
								if((itemSer == null) || (itemSer.trim().length() == 0))  //if 2 
								{
									//System.out.println("Inside else if ...Env ");
									itemSer = "";
									try
									{
										sql2 = "SELECT ITEM_SER FROM ITEM WHERE ITEM_CODE = '"+itemCode+"'";
										stmt2 = conn.createStatement();
										rs2 = stmt2.executeQuery(sql2);
										if(rs2.next())
										{
											itemSer = rs2.getString(1);
											//System.out.println("itemSer 2 env............."+itemSer);
										}
										rs2.close();
									}
									catch(Exception ex)
									{
										//System.out.println("Exception []::"+ sql2 +ex.getMessage());
										ex.printStackTrace();
									}
								}// if 2 
								stmt2.close();
								stmt2 = null;
							}//try 4b
							catch(Exception ex)
							{
								//System.out.println("Exception []::"+ sql2 +ex.getMessage());
								ex.printStackTrace();
							}
							sql = "SELECT TAX_ENV FROM ITEM_ACCT_DETR_DIST "
								+ "WHERE SITE_CODE__FROM = ? "
								+ "AND SITE_CODE__TO = ? "
								+ "AND ITEM_SER = ? "
								+ "AND ITEM_CODE = '' "
								+ "AND TRAN_TYPE = ?";
								
							pstmt = conn.prepareStatement(sql);
					
							pstmt.setString(1,siteCodeFrom);
							pstmt.setString(2,siteCodeTo);
							pstmt.setString(3,itemSer);
							pstmt.setString(4,tranType);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								taxEnv = rs.getString(1);
								//System.out.println("taxEnv ............."+taxEnv);
								flag=1;
							}
							if((flag==0) || (taxClass == null))  // if 4
							{
								flag=0;
								rs.close();
								taxEnv = "";
								//System.out.println("Inside else if 4 Env");
								sql = "SELECT TAX_ENV FROM ITEM_ACCT_DETR_DIST "
									+ "WHERE SITE_CODE__FROM = ? "
									+ "AND SITE_CODE__TO = ? "
									+ "AND ITEM_SER = ? "
									+ "AND ITEM_CODE = '' "
									+ "AND TRAN_TYPE = ''";
									
								pstmt = conn.prepareStatement(sql);
						
								pstmt.setString(1,siteCodeFrom);
								pstmt.setString(2,siteCodeTo);
								pstmt.setString(3,itemSer);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									taxEnv = rs.getString(1);
									//System.out.println("taxEnv ............."+taxEnv);
									flag=1;
								}
								if((flag==0)  || (taxEnv == null))  // if 5
								{
										flag=0;
									rs.close();
									taxEnv = "";
									//System.out.println("Inside else if 5 Env");
									sql = "SELECT TAX_ENV FROM ITEM_ACCT_DETR_DIST "
										+ "WHERE SITE_CODE__FROM = ? "
										+ "AND SITE_CODE__TO = ? "
										+ "AND ITEM_SER = '' "
										+ "AND ITEM_CODE = '' "
										+ "AND TRAN_TYPE = ''";
										
									pstmt = conn.prepareStatement(sql);
							
									pstmt.setString(1,siteCodeFrom);
									pstmt.setString(2,siteCodeTo);
									rs = pstmt.executeQuery();
									if(rs.next())
									{
										taxEnv = rs.getString(1);
										//System.out.println("taxEnv ............."+taxEnv);
										flag=1;
									}
									if((flag==0) || (taxEnv == null))  // if 6
									{
											flag=0;
										rs.close();
										taxEnv = "";
										//System.out.println("Inside else if 6 Env");
										sql = "SELECT TAX_ENV FROM DISTORDER_TYPE "
											+ "WHERE TRAN_TYPE = '"+tranType+"'";
										stmt = conn.createStatement();
										rs = stmt.executeQuery(sql);
										if(rs.next())
										{
											taxEnv = rs.getString(1);
											//System.out.println("taxEnv ............."+taxEnv);
										}//if
										rs.close();
										stmt.close();
										stmt = null;
									}// if 6
								}// if 5
							}// if 4
						}// if 3
					}// if 2
				}// if 1
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try
			catch (SQLException e)
			{
				System.out.println("SQLException : :" +sql+ e.getMessage() + ":");
				e.printStackTrace();
			}
			catch(Exception ex)
			{
				System.out.println("Exception []::"+ sql +ex.getMessage());
				ex.printStackTrace();
				throw new ITMException(ex);
			}
			if(taxEnv==null)taxEnv= " " ;
			//System.out.println("Returning the taxEnv From Dist Common------->");
			//System.out.println("************** ["+taxEnv+"]**************");
			returnVlaue = taxEnv;
		}//else if(caseString.equals("TAX_ENV"))
		return returnVlaue;
		
	}// setPlistTaxClassEnv
	//Culculate the trantype value from disparm	
	public String getDisparams(String prdCode,String varName,Connection conn)
	{
		String retVal = null;
		String sql = null;
		ResultSet rs = null;
		Statement stmt = null;
		try
		{
			//System.out.println("Finding the variable value from Disparam for parameters["+prdCode+","+varName+"]");
			stmt = conn.createStatement();
			sql="SELECT VAR_VALUE FROM DISPARM WHERE PRD_CODE ='"+prdCode+"' AND VAR_NAME ='"+varName+"'";
			rs =stmt.executeQuery(sql);
			if(rs.next())
			{
				retVal = (rs.getString(1)==null?" ":rs.getString(1));
			}
			else
			{
				retVal = "NULLFOUND";	
			}
			rs.close();
			stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return(retVal);
	}//End of disparm
	public String getPriceListType(String priceList,Connection conn)throws ITMException
	{
		String listType = "";
		String sql = "";
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			sql = "SELECT LIST_TYPE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"'";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			//System.out.println("LIST_TYPE... sql..."+sql);
			//25052007
			if (rs.next())
			{
				listType = rs.getString(1);
			}
			rs.close();
			stmt.close();
			//System.out.println("listType...................."+listType);
		}
		catch(Exception e)
		{
			System.out.println("Exception...[getPriceListType] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		return(listType);
	}//getPriceListType

	
	public double pickRate(String priceList,String trDate,String itemCode,String aLotNo,String listType,Connection conn)throws ITMException
	{
		//System.out.println("--------------[pickRate]---------------------------------");
		//System.out.println("DP[priceList/trDate/itemCode/tranType/aLotNo/listType/conn]");
		//if(priceList!=null)
		//System.out.println("Price List:-["+priceList+"]");
		//if(trDate!=null)
		//System.out.println("Tran Date:-["+trDate+"]");
		//if(itemCode!=null)
		//System.out.println("Item Code:-["+itemCode+"]");
		//if(aLotNo!=null)
		//System.out.println("Lot No:-["+aLotNo+"]");
		//if(listType!=null)
		//System.out.println("List Type:-["+listType+"]");
		//if(conn!=null)
		//System.out.println("Connection Found["+conn+"]");
		//System.out.println("-----------------------------------------------");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		java.sql.Timestamp tranDate = null;
		String sql = "";
		String priceListParent = "";
		String siteCode = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";
		String type = "";
		double rate = 0.0;
		
		type = getPriceListType(priceList,conn);
		//System.out.println("List Type From Price List:::["+type+"]"); 
		try
		{
			trDate = genericUtility.getValidDateString(trDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			tranDate = java.sql.Timestamp.valueOf(trDate + " 00:00:00");
			//System.out.println("Date in pickRate...."+tranDate);
		//	25052007
			if(type.equalsIgnoreCase("I"))
			{
				if (aLotNo.indexOf("~t") > 0) 
				{
					String MulStr[] = aLotNo.split("~t");
					//System.out.println("First String ["+MulStr[0]+"]");
					//System.out.println("Second String ["+MulStr[1]+"]");
					//System.out.println("Third String ["+MulStr[2]+"]");
					//System.out.println("Four String ["+MulStr[3]+"]");	
				/*	siteCode = getTokenList(aLotNo,"~t");
					locCode = getTokenList(aLotNo,"~t");
					lotNo = getTokenList(aLotNo,"~t");
					lotSl = getTokenList(aLotNo,"~t");*/
					siteCode = MulStr[0];
					locCode = MulStr[1];
					lotNo = MulStr[2];
					lotSl = MulStr[3];
					
					//System.out.println("siteCode..**..*>"+siteCode);
					//System.out.println("locCode..**..*>"+locCode);
					//System.out.println("lotNo..**..*>"+lotNo);
					//System.out.println("lotSl..**..*>"+lotSl);
				}
			}
			else
			{
				lotNo = aLotNo; 
				//System.out.println("lotNo..**..*>"+lotNo);
			}
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[pickRate] "+sql+e.getMessage());
			e.printStackTrace();
		}
		
		if(type.trim().equals("L"))
		{
			rate = 0;
			//System.out.println("Inside type ::-<L>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					//System.out.println("The rate for type (L) is.... "+rate);
				}
				else
				{
				
					try
					{//try 2
						rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
							rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = 'L' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent is ..."+rate);
								}
								else
								{
									return -1;	
								}
								
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("L"))
		if(type.trim().equals("F"))
		{
			//System.out.println("Inside type ::-<F>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'F' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					//System.out.println("The rate for type (F) is.... "+rate);
				}
				else
				{
					
					try
					{//try 2
					rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
						rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = 'F' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent is ..."+rate);
								}
								else
								{
									return -1;	
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("F"))
		
		if(type.trim().equals("D")) //DISPATCH
		{
			rate = 0;
			//System.out.println("Inside type ::-<D>-::");
			//First selecting rate from pricelist for L,if not found 
			//picking up from batch
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();				
				if(rs.next())
				{
					rate = rs.getDouble(1);
					//System.out.println("The rate for type (L) is.... "+rate);
				}
				else
				{//else if 1
				
					try
					{//try 2
						rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
						rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)//1
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = 'L' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent is ..."+rate);
								}
								else
								{//else if 2
								
									try
									{//try 4
									rs2.close();
										sql = "SELECT RATE FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceList+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND LIST_TYPE = 'B' "
											+ "AND LOT_NO__FROM <= '"+lotNo+"' "
						 					+ "AND LOT_NO__TO >= '"+lotNo+"' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										if(rs2.next())
										{
											rate = rs2.getDouble(1);
											//System.out.println("The rate inside priceListParent for <D> is ..."+rate);
										}
										else
										{//else if 3
												rs2.close();
											try
											{//try 5
												rs2.close();
												sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
													+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
													+ "AND LIST_TYPE = 'B'";
												pstmt = conn.prepareStatement(sql);
												rs2 = pstmt.executeQuery();
												while (rs2.next())
												{
													priceListParent = rs2.getString(1);
													//System.out.println("The priceListParent for <D> is .... "+priceListParent);
												}
												rs2.close();
												if((priceListParent == null) || (priceListParent.trim().length() == 0))
												{
													priceListParent = "";
													//System.out.println("The priceListParent if null  .... "+priceListParent);
													return -1;
												}
												if(priceListParent.trim().length() > 0)//2
												{
													try
													{//try 6
														sql = "SELECT RATE FROM PRICELIST "
															+ "WHERE PRICE_LIST = '"+priceListParent+"' "
															+ "AND ITEM_CODE = '"+itemCode+"' "
															+ "AND LIST_TYPE = 'B' "
															+ "AND LOT_NO__FROM <= '"+lotNo+"' "
						 									+ "AND LOT_NO__TO >= '"+lotNo+"' "
															+ "AND EFF_FROM <= ? "
															+ "AND VALID_UPTO >= ?";
														pstmt = conn.prepareStatement(sql);
														pstmt.setTimestamp(1,tranDate);
														pstmt.setTimestamp(2,tranDate);
														rs3 = pstmt.executeQuery();
														//System.out.println("The priceListParent sql .. "+sql);
														if(rs3.next())
														{
															rate = rs3.getDouble(1);
															//System.out.println("The rate inside priceListParent for <D> is ..."+rate);
														}
														else
														{
															return -1;
														}
													}//try 6
													catch(Exception e)
													{
														System.out.println("Exception...[pickRate] "+sql+e.getMessage());
														e.printStackTrace();
														throw new ITMException(e);
													}
												}//if(priceListParent.trim().length() > 0)2
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if 3
										rs2.close();
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//else if 2
								rs2.close();
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if(priceListParent.trim().length() > 0)//1
							
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if 1
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}										
		}//if(type.trim().equals("D"))
		
		if(type.trim().equals("B"))//BATCH PRICE
		{
			rate = 0;
			try
			{//try 1				
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'B' "
					+ "AND LOT_NO__FROM <= '"+lotNo+"' "
					+ "AND LOT_NO__TO >= '"+lotNo+"' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					rate = rs.getDouble(1);
					//System.out.println("The rate inside priceListParent for <B> is ..."+rate);
				}
				else
				{
				
					try
					{//try 2
						rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'B'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent for <B> is .... "+priceListParent);
						}
						
						rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = 'B' "
									+ "AND LOT_NO__FROM <= '"+lotNo+"' "
									+ "AND LOT_NO__TO >= '"+lotNo+"' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent for <B> is ..."+rate);
								}
								else
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else
				rs.close();
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("B"))//BATCH PRICE
		
		type = listType;
		if((type.trim().equals("M")) || (type.trim().equals("N"))) // Discount PRICE
		{
			rate = 0;
			//System.out.println("Inside type ::-<M><N>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = '"+type+"' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					//System.out.println("The rate for type (F) is.... "+rate);
				}
				else
				{
					try
					{//try 2
						rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = '"+type.trim()+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
							rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							type = getPriceListType(priceList,conn);
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = '"+type+"' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent is ..."+rate);
								}
								else
								{
									return -1;	
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if((type.trim().equals("M")) || (type.trim().equals("N"))) 
		
		if(type.trim().equals("I")) //Inventory
		{
			rate = 0;
			if ((lotSl == null) || (lotSl.trim().length() == 0))
			{
				//System.out.println("Inside type ::-<I>-::");
				try
				{//try 1
					sql = "SELECT RATE FROM STOCK "
						+ "WHERE ITEM_CODE = '"+itemCode+"' "
						+ "AND SITE_CODE = '"+siteCode+"' "
						+ "AND LOC_CODE = '"+locCode+"' "
						+ "AND LOT_NO = '"+lotNo+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					while (rs.next())
					{
						rate = rs.getDouble(1);
						//System.out.println("Rate is .*...."+rate);
					}
					rs.close();
				}//try
				catch(Exception e)
				{
					System.out.println("Exception...[pickRate] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//if
			else
			{
				try
				{//try 1
					sql = "SELECT RATE FROM STOCK "
						+ "WHERE ITEM_CODE = '"+itemCode+"' "
						+ "AND SITE_CODE = '"+siteCode+"' "
						+ "AND LOC_CODE = '"+locCode+"' "
						+ "AND LOT_NO = '"+lotNo+"' "
						+ "AND LOT_SL = '"+lotSl+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						rate = rs.getDouble(1);
						//System.out.println("Rate is .*...."+rate);
					}
					rs.close();
				}//try
				catch(Exception e)
				{
					System.out.println("Exception...[pickRate] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//else
		}//if(type.trim().equals("I")) //Inventory
		//System.out.println("Rate From DisCommon***:::::["+rate+"]");
		return(rate);
	}//pickRate()
	
	public String getTokenList(String tokenString, String delimiter) throws ITMException
	{
		String returnList = "";
		if (tokenString != null && tokenString.length() > 0 && tokenString !="")
		{
			if(tokenString.indexOf(delimiter) != -1)
			{
				int endIndex = tokenString.indexOf(delimiter);
				returnList =  delimiter;
				tokenString = tokenString.substring(endIndex + 1, tokenString.length());
			}
			else returnList = tokenString;
		}
		else
		{
			tokenString = "";
			returnList = tokenString;
		}
		return returnList;
	}//getTokenList()
	public double pickRate(String priceList,String trDate,String itemCode,String aLotNo,String listType,double quantity,String aunit,Connection conn)throws ITMException
	{
		//System.out.println("--------------[pickRate]---------------------------------");
		//System.out.println("DP[priceList/trDate/itemCode/tranType/aLotNo/listType/quantity/unit/conn]");
		//if(priceList!=null)
		//System.out.println("Price List:-["+priceList+"]");
		//if(trDate!=null)
		//System.out.println("Tran Date:-["+trDate+"]");
		//if(itemCode!=null)
		//System.out.println("Item Code:-["+itemCode+"]");
		//if(aLotNo!=null)
		//System.out.println("Lot No:-["+aLotNo+"]");
		//if(listType!=null)
		//System.out.println("List Type:-["+listType+"]");
		//System.out.println("Quantity:-["+quantity+"]");
		//if(aunit!=null)
		//System.out.println("Unit:-["+aunit+"]");
		//if(conn!=null)
		//System.out.println("Connection Found["+conn+"]");
		//System.out.println("-----------------------------------------------");
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		ResultSet rs4 = null;
		java.sql.Timestamp tranDate = null;
		String sql = "";
		String priceListParent = "";
		String unit = "";
		String siteCode = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";
		String type = ""; 
		double rate = 0.0;
		double conv = 0;
		int cnt = 0;
		
		type = getPriceListType(priceList,conn);
		try
		{
			trDate = genericUtility.getValidDateString(trDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			tranDate = java.sql.Timestamp.valueOf(trDate + " 00:00:00");
			//System.out.println("Date in pickRate...."+tranDate);
			if (aLotNo.indexOf("~t") > 0) 
			{
				
				String sepStr[] = aLotNo.split("~t");
				
				//System.out.println(sepStr[0]);
				//System.out.println(sepStr[1]);
				//System.out.println(sepStr[2]);
				//System.out.println(sepStr[3]);
				
				siteCode =sepStr[0];
				locCode =sepStr[1];
				lotNo=sepStr[2];
				lotSl=sepStr[3];
	
			/*	siteCode = getTokenList(aLotNo,"~t");S
				locCode = getTokenList(aLotNo,"~t");
				lotNo = getTokenList(aLotNo,"~t");
				lotSl = getTokenList(aLotNo,"~t");*/
				//System.out.println("Separated String :---------------------->");
				
				//System.out.println("siteCode..**..*>"+siteCode);
				//System.out.println("locCode..**..*>"+locCode);
				//System.out.println("lotNo..**..*>"+lotNo);
				//System.out.println("lotSl..**..*>"+lotSl);
			}
			else
			{
				lotNo = aLotNo; 
				//System.out.println("lotNo..**..*>"+lotNo);
			}
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[pickRate] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		if(aunit == null)
		{
			aunit = "";
		}
		try
		{
			sql = "SELECT COUNT(*) AS COUNT FROM UOM WHERE UNIT = '"+unit+"'";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next())
			{
				cnt = rs.getInt("COUNT");
			}
			if(cnt == 0)
			{
				return -1;
			}
			rs.close();
			stmt.close();
			stmt = null;
		}
		catch(Exception e)
		{
			System.out.println("Exception...[pickRate] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		if(type.trim().equals("L")) //LIST PRICE
		{
			rate = 0;
			//System.out.println("Inside type ::-<L>-::");
			try
			{//try 1
				sql = "SELECT RATE,UNIT FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND UNIT = '"+aunit+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					unit = rs.getString(2);
					//System.out.println("The rate for type (L) is.... "+rate);
				}
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT RATE,UNIT FROM PRICELIST "
							+ "WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND ITEM_CODE = '"+itemCode+"' "
							+ "AND LIST_TYPE = 'L' "
							+ "AND MIN_QTY <= "+quantity+" "
							+ "AND MAX_QTY >= "+quantity+" "
							+ "AND EFF_FROM <= ? "
							+ "AND VALID_UPTO >= ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,tranDate);
						pstmt.setTimestamp(2,tranDate);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							rate = rs.getDouble(1);
							unit = rs.getString(2);
							//System.out.println("The rate for type (L) is.... "+rate);
						}
						
						if(!rs.next()) 
						{
							try
							{//try 3
								rs.close();
								sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
									+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
									+ "AND LIST_TYPE = 'L'";
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								while (rs.next())
								{
									priceListParent = rs.getString(1);
									//System.out.println("The priceListParent is .... "+priceListParent);
								}
									rs.close();
								if((priceListParent == null) || (priceListParent.trim().length() == 0))
								{
									priceListParent = "";
									//System.out.println("The priceListParent if null  .... "+priceListParent);
									return -1;
								}
								if(priceListParent.trim().length() > 0)
								{
									try
									{//try 4
										sql = "SELECT RATE,UNIT FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceListParent+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND UNIT = '"+aunit+"' "
											+ "AND MIN_QTY <= "+quantity+" " 
											+ "AND MIN_QTY >= "+quantity+" "
											+ "AND LIST_TYPE = 'L' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										//System.out.println("The priceListParent sql .. "+sql);
										if(rs2.next())
										{
											rate = rs2.getDouble(1);
											unit = rs2.getString(2);
											//System.out.println("The rate inside priceListParent is ..."+rate);
										}
										else 
										{
											try
											{//try 5
												rs2.close();
												sql = "SELECT RATE,UNIT FROM PRICELIST "
													+ "WHERE PRICE_LIST = '"+priceListParent+"' "
													+ "AND ITEM_CODE = '"+itemCode+"' "
													+ "AND MIN_QTY <= "+quantity+" " 
													+ "AND MIN_QTY >= "+quantity+" "
													+ "AND LIST_TYPE = 'L' "
													+ "AND EFF_FROM <= ? "
													+ "AND VALID_UPTO >= ?";
												pstmt = conn.prepareStatement(sql);
												pstmt.setTimestamp(1,tranDate);
												pstmt.setTimestamp(2,tranDate);
												rs2 = pstmt.executeQuery();
												//System.out.println("The priceListParent sql .. "+sql);
												if(rs2.next())
												{
													rate = rs2.getDouble(1);
													unit = rs2.getString(2);
													//System.out.println("The rate inside priceListParent is ..."+rate);
												}
												else
												{
													return -1;
												}
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if
											rs2.close();
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//if
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
					rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("L"))
		
		if(type.trim().equals("F")) //FIXED PRICE ON DATE
		{
			rate = 0;
			//System.out.println("Inside type ::-<F>-::");
			try
			{//try 1
				sql = "SELECT RATE,UNIT FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND UNIT = '"+aunit+"' "
					+ "AND LIST_TYPE = 'F' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					unit = rs.getString(2);
					//System.out.println("The rate for type (L) is.... "+rate);
				}
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT RATE,UNIT FROM PRICELIST "
							+ "WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND ITEM_CODE = '"+itemCode+"' "
							+ "AND LIST_TYPE = 'F' "
							+ "AND MIN_QTY <= "+quantity+" "
							+ "AND MAX_QTY >= "+quantity+" "
							+ "AND EFF_FROM <= ? "
							+ "AND VALID_UPTO >= ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,tranDate);
						pstmt.setTimestamp(2,tranDate);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							rate = rs.getDouble(1);
							unit = rs.getString(2);
							//System.out.println("The rate for type (F) is.... "+rate);
						}
						if(!(rs.next()) || (unit == null))
						{
							
							try
							{//try 3
								rs.close();
								sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
									+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
									+ "AND LIST_TYPE = 'L'";
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								while (rs.next())
								{
									priceListParent = rs.getString(1);
								}
									rs.close();
								if((priceListParent == null) || (priceListParent.trim().length() == 0))
								{
									priceListParent = "";
									//System.out.println("The priceListParent if null  .... "+priceListParent);
									return -1;
								}
								if(priceListParent.trim().length() > 0)
								{
									try
									{//try 4
										sql = "SELECT RATE,UNIT FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceListParent+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND UNIT = '"+aunit+"' "
											+ "AND MIN_QTY <= "+quantity+" " 
											+ "AND MIN_QTY >= "+quantity+" "
											+ "AND LIST_TYPE = 'F' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										//System.out.println("The priceListParent sql .. "+sql);
										if(rs2.next())
										{
											rate = rs2.getDouble(1);
											unit = rs2.getString(2);
											//System.out.println("The rate inside priceListParent is ..."+rate);
										}
										else
										{
											try
											{//try 5
												rs2.close();
												sql = "SELECT RATE,UNIT FROM PRICELIST "
													+ "WHERE PRICE_LIST = '"+priceListParent+"' "
													+ "AND ITEM_CODE = '"+itemCode+"' "
													+ "AND MIN_QTY <= "+quantity+" " 
													+ "AND MIN_QTY >= "+quantity+" "
													+ "AND LIST_TYPE = 'F' "
													+ "AND EFF_FROM <= ? "
													+ "AND VALID_UPTO >= ?";
												pstmt = conn.prepareStatement(sql);
												pstmt.setTimestamp(1,tranDate);
												pstmt.setTimestamp(2,tranDate);
												rs2 = pstmt.executeQuery();
												//System.out.println("The priceListParent sql .. "+sql);
												while(rs2.next())
												{
													rate = rs2.getDouble(1);
													unit = rs2.getString(2);
													//System.out.println("The rate inside priceListParent is ..."+rate);
												}
												if(!rs2.next()) 
												{
													return -1;
												}
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
											}
										}//else if
											rs2.close();
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//if
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
					rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("F"))
		
		if(type.trim().equals("D")) //DESPATCH
		{
			//selecting rate from pricelist  for L, if not found 
			//picking up from batch
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE,UNIT FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND UNIT = '"+aunit+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					unit = rs.getString(2);
					//System.out.println("The rate for type (L) is.... "+rate);
				}
				else
				{
					try
					{//try 2
						rs.close();
						sql = "SELECT RATE,UNIT FROM PRICELIST "
							+ "WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND ITEM_CODE = '"+itemCode+"' "
							+ "AND LIST_TYPE = 'L' "
							+ "AND MIN_QTY <= "+quantity+" "
							+ "AND MAX_QTY >= "+quantity+" "
							+ "AND EFF_FROM <= ? "
							+ "AND VALID_UPTO >= ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,tranDate);
						pstmt.setTimestamp(2,tranDate);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							rate = rs.getDouble(1);
							unit = rs.getString(2);
							//System.out.println("The rate for type (L) is.... "+rate);
						}
						else
						{
							try
							{//try 3
								rs.close();
								sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
									+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
									+ "AND LIST_TYPE = 'L'";
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								while (rs.next())
								{
									priceListParent = rs.getString(1);
									//System.out.println("The priceListParent is .... "+priceListParent);
								}
									rs.close();
								if((priceListParent == null) || (priceListParent.trim().length() == 0))
								{
									priceListParent = "";
									//System.out.println("The priceListParent if null  .... "+priceListParent);
									return -1;
								}
								if(priceListParent.trim().length() > 0)
								{
									try
									{//try 4
										sql = "SELECT RATE,UNIT FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceListParent+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND UNIT = '"+aunit+"' "
											+ "AND MIN_QTY <= "+quantity+" " 
											+ "AND MIN_QTY >= "+quantity+" "
											+ "AND LIST_TYPE = 'L' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										//System.out.println("The priceListParent sql .. "+sql);
										if(rs2.next())
										{
											rate = rs2.getDouble(1);
											unit = rs2.getString(2);
											//System.out.println("The rate inside priceListParent is ..."+rate);
										}
										else
										{
											try
											{//try 5
												rs2.close();
												sql = "SELECT RATE,UNIT FROM PRICELIST "
													+ "WHERE PRICE_LIST = '"+priceListParent+"' "
													+ "AND ITEM_CODE = '"+itemCode+"' "
													+ "AND MIN_QTY <= "+quantity+" " 
													+ "AND MIN_QTY >= "+quantity+" "
													+ "AND LIST_TYPE = 'L' "
													+ "AND EFF_FROM <= ? "
													+ "AND VALID_UPTO >= ?";
												pstmt = conn.prepareStatement(sql);
												pstmt.setTimestamp(1,tranDate);
												pstmt.setTimestamp(2,tranDate);
												rs2 = pstmt.executeQuery();
												//System.out.println("The priceListParent sql .. "+sql);
												if(rs2.next())
												{
													rate = rs2.getDouble(1);
													unit = rs2.getString(2);
													//System.out.println("The rate inside priceListParent is ..."+rate);
												}
												else
												{
													return -1;
												}
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if
											rs2.close();
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//if
								else
								{
									rate = 0;
									try
									{//try 6
										sql = "SELECT RATE,UNIT FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceList+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND UNIT = '"+aunit+"' "
											+ "AND MIN_QTY <= "+quantity+" " 
											+ "AND MIN_QTY >= "+quantity+" "
											+ "AND LIST_TYPE = 'B' "
											+ "AND LOT_NO__FROM <= '"+lotNo+"' "
											+ "AND LOT_NO__TO >= '"+lotNo+"' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs3 = pstmt.executeQuery();
										//System.out.println("Sql .. "+sql);
										if(rs3.next())
										{
											rate = rs3.getDouble(1);
											unit = rs3.getString(2);
											//System.out.println("The rate inside priceListParent is ..."+rate);
										}
										else
										{
											try
											{//try 7
												rs3.close();
												sql = "SELECT RATE,UNIT FROM PRICELIST "
													+ "WHERE PRICE_LIST = '"+priceList+"' "
													+ "AND ITEM_CODE = '"+itemCode+"' "
													+ "AND MIN_QTY <= "+quantity+" " 
													+ "AND MIN_QTY >= "+quantity+" "
													+ "AND LIST_TYPE = 'B' "
													+ "AND LOT_NO__FROM <= '"+lotNo+"' "
													+ "AND LOT_NO__TO >= '"+lotNo+"' "
													+ "AND EFF_FROM <= ? "
													+ "AND VALID_UPTO >= ?";
												pstmt = conn.prepareStatement(sql);
												pstmt.setTimestamp(1,tranDate);
												pstmt.setTimestamp(2,tranDate);
												rs3 = pstmt.executeQuery();
												//System.out.println("Sql .. "+sql);
												if(rs3.next())
												{
													rate = rs3.getDouble(1);
													unit = rs3.getString(2);
													//System.out.println("The rate inside priceListParent is ..."+rate);
												}
												else
												{
													try
													{//try 8
														rs3.close();
														sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
															+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
															+ "AND LIST_TYPE = 'B'";
														pstmt = conn.prepareStatement(sql);
														rs3 = pstmt.executeQuery();
														while (rs3.next())
														{
															priceListParent = rs3.getString(1);
															//System.out.println("The priceListParent is .... "+priceListParent);
														}
														if((priceListParent == null) || (priceListParent.trim().length() == 0))
														{
															priceListParent = "";
															//System.out.println("The priceListParent if null  .... "+priceListParent);
															return -1;
														}
														if(priceListParent.trim().length() > 0)
														{
															try
															{//try 9
																sql = "SELECT RATE,UNIT FROM PRICELIST "
																	+ "WHERE PRICE_LIST = '"+priceListParent+"' "
																	+ "AND ITEM_CODE = '"+itemCode+"' "
																	+ "AND UNIT = '"+aunit+"' "
																	+ "AND MIN_QTY <= "+quantity+" " 
																	+ "AND MIN_QTY >= "+quantity+" "
																	+ "AND LIST_TYPE = 'B' "
																	+ "AND LOT_NO__FROM <= '"+lotNo+"' "
																	+ "AND LOT_NO__TO >= '"+lotNo+"' "
																	+ "AND EFF_FROM <= ? "
																	+ "AND VALID_UPTO >= ?";
																pstmt = conn.prepareStatement(sql);
																pstmt.setTimestamp(1,tranDate);
																pstmt.setTimestamp(2,tranDate);
																rs4 = pstmt.executeQuery();
																//System.out.println("Sql .. "+sql);
																if(rs4.next())
																{
																	rate = rs4.getDouble(1);
																	unit = rs4.getString(2);
																	//System.out.println("The rate inside priceListParent is ..."+rate);
																}
																else
																{
																		rs4.close();
																	try
																	{//try 10
																		sql = "SELECT RATE,UNIT FROM PRICELIST "
																			+ "WHERE PRICE_LIST = '"+priceListParent+"' "
																			+ "AND ITEM_CODE = '"+itemCode+"' "
																			+ "AND MIN_QTY <= "+quantity+" " 
																			+ "AND MIN_QTY >= "+quantity+" "
																			+ "AND LIST_TYPE = 'B' "
																			+ "AND LOT_NO__FROM <= '"+lotNo+"' "
																			+ "AND LOT_NO__TO >= '"+lotNo+"' "
																			+ "AND EFF_FROM <= ? "
																			+ "AND VALID_UPTO >= ? ";
																		pstmt = conn.prepareStatement(sql);
																		pstmt.setTimestamp(1,tranDate);
																		pstmt.setTimestamp(2,tranDate);
																		rs4 = pstmt.executeQuery();
																		//System.out.println("Sql .. "+sql);
																		if(rs4.next())
																		{
																			rate = rs4.getDouble(1);
																			unit = rs4.getString(2);
																			//System.out.println("The rate inside priceListParent is ..."+rate);
																		}
																		else
																		{
																			return -1;
																		}
																	}//try 10
																	catch(Exception e)
																	{
																		System.out.println("Exception...[pickRate] "+sql+e.getMessage());
																		e.printStackTrace();
																		throw new ITMException(e);
																	}
																}//else if
																rs4.close();
															}//try 9
															catch(Exception e)
															{
																System.out.println("Exception...[pickRate] "+sql+e.getMessage());
																e.printStackTrace();
																throw new ITMException(e);
															}
														}// if
													}//try 8
													catch(Exception e)
													{
														System.out.println("Exception...[pickRate] "+sql+e.getMessage());
														e.printStackTrace();
														throw new ITMException(e);
													}
												}//else if
												rs3.close();
											}//try 7
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if
											rs3.close();
									}//try 6
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//else 
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//else
							rs.close();
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
					rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("D"))
		
		if(type.trim().equals("B"))  //BATCH PRICE
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE,UNIT FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND UNIT = '"+aunit+"' "
					+ "AND LIST_TYPE = 'B' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND LOT_NO__FROM <= '"+lotNo+"' "
					+ "AND LOT_NO__TO >= '"+lotNo+"' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
					unit = rs.getString(2);
					//System.out.println("The rate for type (B) is.... "+rate);
				}
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT RATE,UNIT FROM PRICELIST "
							+ "WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND ITEM_CODE = '"+itemCode+"' "
							+ "AND LIST_TYPE = 'B' "
							+ "AND MIN_QTY <= "+quantity+" "
							+ "AND MAX_QTY >= "+quantity+" "
							+ "AND LOT_NO__FROM <= '"+lotNo+"' "
							+ "AND LOT_NO__TO >= '"+lotNo+"' "
							+ "AND EFF_FROM <= ? "
							+ "AND VALID_UPTO >= ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,tranDate);
						pstmt.setTimestamp(2,tranDate);
						rs = pstmt.executeQuery();
						if (rs.next())
						{
							rate = rs.getDouble(1);
							unit = rs.getString(2);
							//System.out.println("The rate for type (L) is.... "+rate);
						}
						else
						{
							try
							{//try 3
							rs.close();
								sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
									+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
									+ "AND LIST_TYPE = 'B'";
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								while (rs.next())
								{
									priceListParent = rs.getString(1);
									//System.out.println("The priceListParent is .... "+priceListParent);
								}
								if((priceListParent == null) || (priceListParent.trim().length() == 0))
								{
									priceListParent = "";
									//System.out.println("The priceListParent if null  .... "+priceListParent);
									return -1;
								}
								if(priceListParent.trim().length() > 0)
								{
									try
									{//try 4
										sql = "SELECT RATE,UNIT FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceListParent+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND UNIT = '"+aunit+"' "
											+ "AND LIST_TYPE = 'B' "
											+ "AND MIN_QTY <= "+quantity+" "
											+ "AND MAX_QTY >= "+quantity+" "
											+ "AND LOT_NO__FROM <= '"+lotNo+"' "
											+ "AND LOT_NO__TO >= '"+lotNo+"' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										//System.out.println("The priceListParent sql .. "+sql);
										if(rs2.next())
										{
											rate = rs2.getDouble(1);
											unit = rs2.getString(2);
											//System.out.println("The rate inside priceListParent is ..."+rate);
										}
										else
										{
											try
											{//try 5
											rs2.close();
												sql = "SELECT RATE,UNIT FROM PRICELIST "
													+ "WHERE PRICE_LIST = '"+priceListParent+"' "
													+ "AND ITEM_CODE = '"+itemCode+"' "
													+ "AND LIST_TYPE = 'B' "
													+ "AND MIN_QTY <= "+quantity+" "
													+ "AND MAX_QTY >= "+quantity+" "
													+ "AND LOT_NO__FROM <= '"+lotNo+"' "
													+ "AND LOT_NO__TO >= '"+lotNo+"' "
													+ "AND EFF_FROM <= ? "
													+ "AND VALID_UPTO >= ?";
												pstmt = conn.prepareStatement(sql);
												pstmt.setTimestamp(1,tranDate);
												pstmt.setTimestamp(2,tranDate);
												rs2 = pstmt.executeQuery();
												//System.out.println("The priceListParent sql .. "+sql);
												if(rs2.next())
												{
													rate = rs2.getDouble(1);
													unit = rs2.getString(2);
													//System.out.println("The rate inside priceListParent is ..."+rate);
												}
												else
												{
													return -1;
												}
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if
										rs.close();
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//if
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("B"))
		if(rate != 0)
		{
			if(!aunit.trim().equals(unit.trim()))
			{
				rate = convQtyFactor(aunit,unit,itemCode,conv,conn);
			}
		}
		//System.out.println("Rate From DisCommon***:::::["+rate+"]");
		return(rate);
	}//pickRate()
	
	public double pickRate(String priceList,String trDate,String itemCode,String aLotNo,String listType,double quantity,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		ResultSet rs4 = null;
		java.sql.Timestamp tranDate = null;
		String sql = "";
		String priceListParent = "";
		String unit = "";
		String siteCode = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";
		String type = "";
		double rate = 0.0;
		double conv = 0;
		int cnt = 0;
		
		type = getPriceListType(priceList,conn);
		try
		{
			trDate = genericUtility.getValidDateString(trDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			tranDate = java.sql.Timestamp.valueOf(trDate + " 00:00:00");
			//System.out.println("Date in pickRate...."+tranDate);
			if (aLotNo.indexOf("~t") > 0) 
			{
				siteCode = getTokenList(aLotNo,"~t");
				locCode = getTokenList(aLotNo,"~t");
				lotNo = getTokenList(aLotNo,"~t");
				lotSl = getTokenList(aLotNo,"~t");
				//System.out.println("siteCode..**..*>"+siteCode);
				//System.out.println("locCode..**..*>"+locCode);
				//System.out.println("lotNo..**..*>"+lotNo);
				//System.out.println("lotSl..**..*>"+lotSl);
			}
			else
			{
				lotNo = aLotNo; 
				//System.out.println("lotNo..**..*>"+lotNo);
			}
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[pickRate] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		if(type.trim().equals("L")) //LIST PRICE
		{
			rate = 0;
			//System.out.println("Inside type ::-<L>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND MIN_QTY <= "+quantity+" " 
									+ "AND MIN_QTY >= "+quantity+" "
									+ "AND LIST_TYPE = 'L' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else 	
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("L"))
		
		if(type.trim().equals("F")) //FIXED PRICE ON DATE
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'F' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
						}
						rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND MIN_QTY <= "+quantity+" " 
									+ "AND MIN_QTY >= "+quantity+" "
									+ "AND LIST_TYPE = 'F' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else 	
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("F"))
		
		if(type.trim().equals("D")) //DESPATCH
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND MIN_QTY <= "+quantity+" " 
									+ "AND MIN_QTY >= "+quantity+" "
									+ "AND LIST_TYPE = 'L' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else
								{
									rate = 0;
									
									try
									{//try 4
									rs2.close();
										sql = "SELECT RATE FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceList+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND LIST_TYPE = 'B' "
											+ "AND MIN_QTY <= "+quantity+" "
											+ "AND MAX_QTY >= "+quantity+" "
											+ "AND LOT_NO__FROM <= '"+lotNo+"' "
											+ "AND LOT_NO__TO >= '"+lotNo+"' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										if (rs2.next())
										{
											rate = rs2.getDouble(1);
										}
										else
										{
											try
											{//try 5
											rs2.close();
												sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
													+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
													+ "AND LIST_TYPE = 'B'";
												pstmt = conn.prepareStatement(sql);
												rs2 = pstmt.executeQuery();
												while (rs2.next())
												{
													priceListParent = rs2.getString(1);
													//System.out.println("The priceListParent is .... "+priceListParent);
												}
												if((priceListParent == null) || (priceListParent.trim().length() == 0))
												{
													priceListParent = "";
													//System.out.println("The priceListParent if null  .... "+priceListParent);
													return -1;
												}
												if(priceListParent.trim().length() > 0)
												{
													try
													{//try 6
														sql = "SELECT RATE FROM PRICELIST "
															+ "WHERE PRICE_LIST = '"+priceListParent+"' "
															+ "AND ITEM_CODE = '"+itemCode+"' "
															+ "AND LIST_TYPE = 'B' "
															+ "AND MIN_QTY <= "+quantity+" "
															+ "AND MAX_QTY >= "+quantity+" "
															+ "AND LOT_NO__FROM <= '"+lotNo+"' "
															+ "AND LOT_NO__TO >= '"+lotNo+"' "
															+ "AND EFF_FROM <= ? "
															+ "AND VALID_UPTO >= ?";
														pstmt = conn.prepareStatement(sql);
														pstmt.setTimestamp(1,tranDate);
														pstmt.setTimestamp(2,tranDate);
														rs3 = pstmt.executeQuery();
														//System.out.println("The priceListParent sql .. "+sql);
														if(rs3.next())
														{
															rate = rs3.getDouble(1);
														}
														else 
														{
															return -1;
														}
													}//try 6
													catch(Exception e)
													{
														System.out.println("Exception...[pickRate] "+sql+e.getMessage());
														e.printStackTrace();
														throw new ITMException(e);
													}
												}//if
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//else if
								rs2.close();
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("D"))
		
		if(type.trim().equals("B")) //BATCH PRICE
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'B' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND LOT_NO__FROM <= '"+lotNo+"' "
					+ "AND LOT_NO__TO >= '"+lotNo+"' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					try
					{//try 2
					rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'B'";
						pstmt = conn.prepareStatement(sql);
						rs2 = pstmt.executeQuery();
						while (rs2.next())
						{
							priceListParent = rs2.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
						rs2.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = 'B' "
									+ "AND MIN_QTY <= "+quantity+" "
									+ "AND MAX_QTY >= "+quantity+" "
									+ "AND LOT_NO__FROM <= '"+lotNo+"' "
									+ "AND LOT_NO__TO >= '"+lotNo+"' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else 
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("B"))	
		
		type = listType;
		if((type.trim().equals("M")) || (type.trim().equals("N"))) // Discount PRICE
		{
			rate = 0;
			//System.out.println("Inside type ::-<M><N>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = '"+type+"' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					type = getPriceListType(priceList,conn);
					try
					{//try 2
					rs.close();
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = '"+type+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
						rs.close();
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							type = getPriceListType(priceList,conn);
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = '"+type+"' "
									+ "AND MIN_QTY <= "+quantity+" "
									+ "AND MAX_QTY >= "+quantity+" "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent is ..."+rate);
								}
								else
								{
									return -1;	
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else
				rs.close();
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if((type.trim().equals("M")) || (type.trim().equals("N"))) 
		
		if(type.trim().equals("I")) //Inventory
		{
			rate = 0;
			if ((lotSl == null) || (lotSl.trim().length() == 0))
			{
				//System.out.println("Inside type ::-<I>-::");
				try
				{//try 1
					sql = "SELECT RATE FROM STOCK "
						+ "WHERE ITEM_CODE = '"+itemCode+"' "
						+ "AND SITE_CODE = '"+siteCode+"' "
						+ "AND LOC_CODE = '"+locCode+"' "
						+ "AND LOT_NO = '"+lotNo+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					while (rs.next())
					{
						rate = rs.getDouble(1);
						//System.out.println("Rate is .*...."+rate);
					}
					rs.close();
				}//try
				catch(Exception e)
				{
					System.out.println("Exception...[pickRate] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//if
			else
			{
				try
				{//try 1
					sql = "SELECT RATE FROM STOCK "
						+ "WHERE ITEM_CODE = '"+itemCode+"' "
						+ "AND SITE_CODE = '"+siteCode+"' "
						+ "AND LOC_CODE = '"+locCode+"' "
						+ "AND LOT_NO = '"+lotNo+"' "
						+ "AND LOT_SL = '"+lotSl+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						rate = rs.getDouble(1);
						System.out.println("Rate is .*...."+rate);
					}
					rs.close();
				}//try
				catch(Exception e)
				{
					System.out.println("Exception...[pickRate] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//else
		}//if(type.trim().equals("I")) //Inventory
		//System.out.println("Rate From DisCommon***:::::["+rate+"]");
		return(rate);
	}//pickRate()
	
	public double pickRateGSM(String priceList,String trDate,String itemCode,String aLotNo,String listType,double quantity,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		ResultSet rs4 = null;
		java.sql.Timestamp tranDate = null;
		String sql = "";
		String priceListParent = "";
		String unit = "";
		String siteCode = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";
		String type = "";
		double rate = 0.0;
		double conv = 0;
		int cnt = 0;
		try
		{
			trDate = genericUtility.getValidDateString(trDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat());
			tranDate = java.sql.Timestamp.valueOf(trDate + " 00:00:00");
			//System.out.println("Date in pickRate...."+tranDate);
			if (aLotNo.indexOf("~t") > 0) 
			{
				siteCode = getTokenList(aLotNo,"~t");
				locCode = getTokenList(aLotNo,"~t");
				lotNo = getTokenList(aLotNo,"~t");
				lotSl = getTokenList(aLotNo,"~t");
				//System.out.println("siteCode..**..*>"+siteCode);
				//System.out.println("locCode..**..*>"+locCode);
				//System.out.println("lotNo..**..*>"+lotNo);
				//System.out.println("lotSl..**..*>"+lotSl);
			}
			else
			{
				lotNo = aLotNo; 
				//System.out.println("lotNo..**..*>"+lotNo);
			}
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[pickRate] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		
		type = getPriceListType(priceList,conn);

		if(type.trim().equals("L")) //LIST PRICE
		{
			rate = 0;
			//System.out.println("Inside type ::-<L>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					try
					{//try 2
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND MIN_QTY <= "+quantity+" " 
									+ "AND MIN_QTY >= "+quantity+" "
									+ "AND LIST_TYPE = 'L' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else 	
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("L"))
		
		if(type.trim().equals("F")) //FIXED PRICE ON DATE
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'F' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				
				else
				{
					try
					{//try 2
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND MIN_QTY <= "+quantity+" " 
									+ "AND MIN_QTY >= "+quantity+" "
									+ "AND LIST_TYPE = 'F' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else 	
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("F"))
		
		if(type.trim().equals("D")) //DESPATCH
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'L' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				
				else
				{
					try
					{//try 2
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'L'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND MIN_QTY <= "+quantity+" " 
									+ "AND MIN_QTY >= "+quantity+" "
									+ "AND LIST_TYPE = 'L' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else
								{
									rate = 0;
									try
									{//try 4
										sql = "SELECT RATE FROM PRICELIST "
											+ "WHERE PRICE_LIST = '"+priceList+"' "
											+ "AND ITEM_CODE = '"+itemCode+"' "
											+ "AND LIST_TYPE = 'B' "
											+ "AND MIN_QTY <= "+quantity+" "
											+ "AND MAX_QTY >= "+quantity+" "
											+ "AND LOT_NO__FROM <= '"+lotNo+"' "
											+ "AND LOT_NO__TO >= '"+lotNo+"' "
											+ "AND EFF_FROM <= ? "
											+ "AND VALID_UPTO >= ?";
										pstmt = conn.prepareStatement(sql);
										pstmt.setTimestamp(1,tranDate);
										pstmt.setTimestamp(2,tranDate);
										rs2 = pstmt.executeQuery();
										if (rs2.next())
										{
											rate = rs2.getDouble(1);
										}
										else
										{
											try
											{//try 5
												sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
													+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
													+ "AND LIST_TYPE = 'B'";
												pstmt = conn.prepareStatement(sql);
												rs2 = pstmt.executeQuery();
												while (rs2.next())
												{
													priceListParent = rs2.getString(1);
													//System.out.println("The priceListParent is .... "+priceListParent);
												}
												if((priceListParent == null) || (priceListParent.trim().length() == 0))
												{
													priceListParent = "";
													//System.out.println("The priceListParent if null  .... "+priceListParent);
													return -1;
												}
												if(priceListParent.trim().length() > 0)
												{
													try
													{//try 6
														sql = "SELECT RATE FROM PRICELIST "
															+ "WHERE PRICE_LIST = '"+priceListParent+"' "
															+ "AND ITEM_CODE = '"+itemCode+"' "
															+ "AND LIST_TYPE = 'B' "
															+ "AND MIN_QTY <= "+quantity+" "
															+ "AND MAX_QTY >= "+quantity+" "
															+ "AND LOT_NO__FROM <= '"+lotNo+"' "
															+ "AND LOT_NO__TO >= '"+lotNo+"' "
															+ "AND EFF_FROM <= ? "
															+ "AND VALID_UPTO >= ?";
														pstmt = conn.prepareStatement(sql);
														pstmt.setTimestamp(1,tranDate);
														pstmt.setTimestamp(2,tranDate);
														rs3 = pstmt.executeQuery();
														//System.out.println("The priceListParent sql .. "+sql);
														if(rs3.next())
														{
															rate = rs3.getDouble(1);
														}
														else 
														{
															return -1;
														}
													}//try 6
													catch(Exception e)
													{
														System.out.println("Exception...[pickRate] "+sql+e.getMessage());
														e.printStackTrace();
														throw new ITMException(e);
													}
												}//if
											}//try 5
											catch(Exception e)
											{
												System.out.println("Exception...[pickRate] "+sql+e.getMessage());
												e.printStackTrace();
												throw new ITMException(e);
											}
										}//else if
									}//try 4
									catch(Exception e)
									{
										System.out.println("Exception...[pickRate] "+sql+e.getMessage());
										e.printStackTrace();
										throw new ITMException(e);
									}
								}//else if
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("D"))
		
		if(type.trim().equals("B")) //BATCH PRICE
		{
			rate = 0;
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = 'B' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND LOT_NO__FROM <= '"+lotNo+"' "
					+ "AND LOT_NO__TO >= '"+lotNo+"' "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					try
					{//try 2
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = 'B'";
						pstmt = conn.prepareStatement(sql);
						rs2 = pstmt.executeQuery();
						while (rs2.next())
						{
							priceListParent = rs2.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = 'B' "
									+ "AND MIN_QTY <= "+quantity+" "
									+ "AND MAX_QTY >= "+quantity+" "
									+ "AND LOT_NO__FROM <= '"+lotNo+"' "
									+ "AND LOT_NO__TO >= '"+lotNo+"' "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
								}
								else 
								{
									return -1;
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else if
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if(type.trim().equals("B"))	
		
		type = listType;
		if((type.trim().equals("M")) || (type.trim().equals("N"))) // Discount PRICE
		{
			rate = 0;
			//System.out.println("Inside type ::-<M><N>-::");
			try
			{//try 1
				sql = "SELECT RATE FROM PRICELIST "
					+ "WHERE PRICE_LIST = '"+priceList+"' "
					+ "AND ITEM_CODE = '"+itemCode+"' "
					+ "AND LIST_TYPE = '"+type+"' "
					+ "AND MIN_QTY <= "+quantity+" "
					+ "AND MAX_QTY >= "+quantity+" "
					+ "AND EFF_FROM <= ? "
					+ "AND VALID_UPTO >= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				//System.out.println("Rate sql ..."+sql);
				if(rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					type = getPriceListType(priceList,conn);
					try
					{//try 2
						sql = "SELECT (CASE WHEN PRICE_LIST__PARENT IS NULL THEN '' ELSE PRICE_LIST__PARENT END) "
							+ "FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' "
							+ "AND LIST_TYPE = '"+type+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						while (rs.next())
						{
							priceListParent = rs.getString(1);
							//System.out.println("The priceListParent is .... "+priceListParent);
						}
						if((priceListParent == null) || (priceListParent.trim().length() == 0))
						{
							priceListParent = "";
							//System.out.println("The priceListParent if null  .... "+priceListParent);
							return -1;
						}
						if(priceListParent.trim().length() > 0)
						{
							type = getPriceListType(priceList,conn);
							try
							{//try 3
								sql = "SELECT RATE FROM PRICELIST "
									+ "WHERE PRICE_LIST = '"+priceListParent+"' "
									+ "AND ITEM_CODE = '"+itemCode+"' "
									+ "AND LIST_TYPE = '"+type+"' "
									+ "AND MIN_QTY <= "+quantity+" "
									+ "AND MAX_QTY >= "+quantity+" "
									+ "AND EFF_FROM <= ? "
									+ "AND VALID_UPTO >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setTimestamp(1,tranDate);
								pstmt.setTimestamp(2,tranDate);
								rs2 = pstmt.executeQuery();
								//System.out.println("The priceListParent sql .. "+sql);
								if(rs2.next())
								{
									rate = rs2.getDouble(1);
									//System.out.println("The rate inside priceListParent is ..."+rate);
								}
								else
								{
									return -1;	
								}
							}//try 3
							catch(Exception e)
							{
								System.out.println("Exception...[pickRate] "+sql+e.getMessage());
								e.printStackTrace();
								throw new ITMException(e);
							}
						}//if
					}//try 2
					catch(Exception e)
					{
						System.out.println("Exception...[pickRate] "+sql+e.getMessage());
						e.printStackTrace();
						throw new ITMException(e);
					}
				}//else
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}//try 1
			catch(Exception e)
			{
				System.out.println("Exception...[pickRate] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//if((type.trim().equals("M")) || (type.trim().equals("N"))) 
		
		if(type.trim().equals("I")) //Inventory
		{
			rate = 0;
			if ((lotSl == null) || (lotSl.trim().length() == 0))
			{
				//System.out.println("Inside type ::-<I>-::");
				try
				{//try 1
					sql = "SELECT RATE FROM STOCK "
						+ "WHERE ITEM_CODE = '"+itemCode+"' "
						+ "AND SITE_CODE = '"+siteCode+"' "
						+ "AND LOC_CODE = '"+locCode+"' "
						+ "AND LOT_NO = '"+lotNo+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					while (rs.next())
					{
						rate = rs.getDouble(1);
						//System.out.println("Rate is .*...."+rate);
					}
				}//try
				catch(Exception e)
				{
					System.out.println("Exception...[pickRate] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//if
			else
			{
				try
				{//try 1
					sql = "SELECT RATE FROM STOCK "
						+ "WHERE ITEM_CODE = '"+itemCode+"' "
						+ "AND SITE_CODE = '"+siteCode+"' "
						+ "AND LOC_CODE = '"+locCode+"' "
						+ "AND LOT_NO = '"+lotNo+"' "
						+ "AND LOT_SL = '"+lotSl+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						rate = rs.getDouble(1);
						//System.out.println("Rate is .*...."+rate);
					}
				}//try
				catch(Exception e)
				{
					System.out.println("Exception...[pickRate] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//else
		}//if(type.trim().equals("I")) //Inventory
		return(rate);
	}//pickRateGSM()

	public double convQtyFactor(String unitFrom,String unitTo,String itemCode,double toConvert,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String round = "";
		double fact = 0.0;
		double roundTo = 0.0;	
		double newQty = 0.0;	
		double errQty = -999999999;
		int count = 0;
		
		if(unitFrom.trim().equals(unitTo.trim()))
		{
			return toConvert;
		}
		try
		{
			sql = "SELECT COUNT(*) AS COUNT FROM UOMCONV "
				+ "WHERE UNIT__FR = ? "
				+ "AND UNIT__TO = ? "
				+ "AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,unitFrom);
			pstmt.setString(2,unitTo);
			pstmt.setString(3,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("COUNT");
				//System.out.println("Count........1 "+count);
			}
			pstmt.close();
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		if(count == 0)
		{
			try
			{
				sql = "SELECT COUNT(*) AS COUNT FROM UOMCONV "
					+ "WHERE UNIT__FR = ? "
					+ "AND UNIT__TO = ? "
					+ "AND ITEM_CODE = 'X'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,unitFrom);
				pstmt.setString(2,unitTo);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt("COUNT");
					//System.out.println("Count........2 "+count);
				}
				pstmt.close();
				pstmt = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
			if(count == 0)
			{
				return errQty;
			}
			else
			{
				try
				{
					sql = "SELECT FACT,ROUND,ROUND_TO FROM UOMCONV "
						+ "WHERE UNIT__FR = ? "
						+ "AND UNIT__TO = ? "
						+ "AND ITEM_CODE = 'X'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,unitFrom);
					pstmt.setString(2,unitTo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						fact = rs.getDouble(1);
						round = rs.getString(2);
						roundTo = rs.getDouble(3);
						//System.out.println("fact........"+fact);
					}
					pstmt.close();
					pstmt = null;
				}
				catch(Exception e)
				{
					System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//else
		}//if
		else
		{
			try
			{
				sql = "SELECT FACT,ROUND,ROUND_TO FROM UOMCONV "
					+ "WHERE UNIT__FR = ? "
					+ "AND UNIT__TO = ? "
					+ "AND ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,unitFrom);
				pstmt.setString(2,unitTo);
				pstmt.setString(3,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					fact = rs.getDouble(1);
					round = rs.getString(2);
					roundTo = rs.getDouble(3);
					//System.out.println("fact........"+fact);
				}
				pstmt.close();
				pstmt = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//else
		newQty = (fact * toConvert);
		round = round.toUpperCase();
		if(round.trim().equals("X"))
		{
			newQty = (newQty - (newQty % roundTo) + roundTo);
		}
		if(round.trim().equals("P"))
		{
			newQty = (newQty - (newQty % roundTo));
		}
		if(round.trim().equals("R"))
		{
			if((newQty % roundTo) < (roundTo / 2))
			{
				newQty = (newQty - (newQty % roundTo));
			}
			else
			{
				newQty = (newQty - (newQty % roundTo) + roundTo);
			}
		}
		return newQty;
	}//convQtyFactor						
	public ArrayList getConvQuantityFact(String frUom, String toUom, String forItem, double toConvert, double fact, Connection connectionObject)
	{
		String  mRound ="";
		String lsOrder = "NOTFOUND";
		double newQty, mConv = 0, mRndTo = 0, errQty = -999999999;
		int mCnt;
		ArrayList returnValue = new ArrayList();
		String errCode="";
		String sql = "";
		Statement stmt;
		ResultSet conRs = null;
		if (frUom.trim().equals(toUom.trim()))
		{
			fact = 1;
			returnValue.add(Double.toString(fact));
			returnValue.add(Double.toString(toConvert));
			return returnValue;
		}
		try
		{
			
			stmt = connectionObject.createStatement();
			//	Check for unit in actual order with item code
			sql =	"SELECT FACT, ROUND, ROUND_TO FROM UOMCONV WHERE UNIT__FR='"+frUom.trim()+"' AND UNIT__TO='"+toUom.trim()+"' AND ITEM_CODE='"+forItem.trim()+"'"; 
			conRs = stmt.executeQuery(sql);
			if (conRs.next())
			{
				mConv=conRs.getDouble(1);
				mRound=conRs.getString(2);
				mRndTo=conRs.getDouble(3);
				lsOrder = "ACTORD";
			}
			else
			{
				sql="SELECT FACT, ROUND, ROUND_TO FROM UOMCONV WHERE UNIT__FR='"+toUom.trim()+"' AND UNIT__TO='"+frUom.trim()+"' AND ITEM_CODE='"+forItem.trim()+"'";
				conRs = null;	
				conRs = stmt.executeQuery(sql);
				if (conRs.next())
				{
					mConv=conRs.getDouble(1);
					mRound=conRs.getString(2);
					mRndTo=conRs.getDouble(3);
					lsOrder = "REVORD";
				}
				else
				{
					
					conRs = null;	
					sql="SELECT FACT, ROUND, ROUND_TO FROM UOMCONV WHERE UNIT__FR='"+frUom.trim()+"' AND UNIT__TO='"+toUom.trim()+"' AND ITEM_CODE='X'";
					conRs = stmt.executeQuery(sql);	
					if(conRs.next())
					{
						mConv=conRs.getDouble(1);
						mRound=conRs.getString(2);
						mRndTo=conRs.getDouble(3);
						lsOrder = "ACTORD";
					}
					
					else
					{
						conRs = null;	
						sql="SELECT FACT, ROUND, ROUND_TO FROM UOMCONV WHERE UNIT__FR='"+toUom.trim()+"' AND UNIT__TO='"+frUom.trim()+"' AND ITEM_CODE='X'";
						conRs = stmt.executeQuery(sql);	
						if(conRs.next())
						{
							mConv=conRs.getDouble(1);
							mRound=conRs.getString(2);
							mRndTo=conRs.getDouble(3);
							lsOrder = "REVORD";
			
						}
						else
						{
							returnValue.add(Double.toString(fact));
							returnValue.add(Double.toString(errQty));
							return returnValue;
						}
					}
				}
			}
			//	Check whether conversion factor need to be assigned
			if (fact == 0)
			{
				if (lsOrder.equals("ACTORD"))
				{
					newQty = mConv * toConvert;
					fact = mConv;
				}
				else
				{
					newQty = 1 / mConv * toConvert;
					fact = 1 / mConv;
				}
			}
			else
			{
				newQty = fact * toConvert;
				
			}

			newQty = getRndamt(newQty, mRound, mRndTo);
			returnValue.add(Double.toString(fact));
			returnValue.add(Double.toString(newQty));
		}//end of try
		catch(Exception e)
		{
			System.out.println("Exception :ITMDBAccessEJB :Conversion:" + e.getMessage() + ":");
		
		}
		return returnValue;
	}

	public double getRndamt(double newQty, String round, double roundTo) 
	{
		double lcMultiply = 1;
		try
		{
			
			round = round.toUpperCase();
			if(newQty < 0)
			{
				lcMultiply = -1;
				newQty = Math.abs(newQty);
			}
			else if (newQty == 0)
			{
				return newQty;
			}
			else if (round.trim().equals("N"))
			{
				return newQty;
			}
			else if(roundTo == 0)
			{
				return newQty;
			}
			if(round.trim().equals("X"))
			{
				newQty = (newQty - (newQty % roundTo) + roundTo);
			}
			if(round.trim().equals("P"))
			{
				newQty = (newQty - (newQty % roundTo));
			}
			if(round.trim().equals("R"))
			{
				if((newQty % roundTo) < (roundTo / 2))
				{
					newQty = (newQty - (newQty % roundTo));
				}
				else
				{
					newQty = (newQty - (newQty % roundTo) + roundTo);
				}
			}
			return newQty;
		}
		catch(Exception e)
		{
			System.out.println("Exception :Conversion Qty ::" + e.getMessage() + ":");
		
		}
		if (roundTo == 1)
		{
			newQty = getRequiredDecimal(newQty, 0);
		}
		else if (roundTo == .1)
		{
			newQty = getRequiredDecimal(newQty, 1);
		}
		else if (roundTo == .01)
		{
			newQty = getRequiredDecimal(newQty, 2);
		}
		else if (roundTo == .001)
		{
			newQty = getRequiredDecimal(newQty, 3);
		}
		else if (roundTo == .0001)
		{
			newQty = getRequiredDecimal(newQty, 4);
		}
		return newQty;
	}

	public double getRequiredDecimal(double actVal, int prec)
	{
		NumberFormat numberFormat = NumberFormat.getIntegerInstance ();
		Double DoubleValue = new Double (actVal);
		numberFormat.setMaximumFractionDigits(3);
		String strValue = numberFormat.format(DoubleValue);
		strValue = strValue.replaceAll(",","");
		double reqVal = Double.parseDouble(strValue);
		return reqVal;
	}

	/*public double convQtyFactor(String unitFrom,String unitTo,String itemCode,double toConvert,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String round = "";
		double fact = 0.0;
		double roundTo = 0.0;	
		double newQty = 0.0;	
		double errQty = -999999999;
		int count = 0;
		
		if(unitFrom.trim().equals(unitTo.trim()))
		{
			return toConvert;
		}
		try
		{
			sql = "SELECT COUNT(*) AS COUNT FROM UOMCONV "
				+ "WHERE UNIT__FR = ? "
				+ "AND UNIT__TO = ? "
				+ "AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,unitFrom);
			pstmt.setString(2,unitTo);
			pstmt.setString(3,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("COUNT");
				System.out.println("Count........1 "+count);
			}
			pstmt.close();
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		if(count == 0)
		{
			try
			{
				sql = "SELECT COUNT(*) AS COUNT FROM UOMCONV "
					+ "WHERE UNIT__FR = ? "
					+ "AND UNIT__TO = ? "
					+ "AND ITEM_CODE = 'X'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,unitFrom);
				pstmt.setString(2,unitTo);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt("COUNT");
					System.out.println("Count........2 "+count);
				}
				pstmt.close();
				pstmt = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
			if(count == 0)
			{
				return errQty;
			}
			else
			{
				try
				{
					sql = "SELECT FACT,ROUND,ROUND_TO FROM UOMCONV "
						+ "WHERE UNIT__FR = ? "
						+ "AND UNIT__TO = ? "
						+ "AND ITEM_CODE = 'X'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,unitFrom);
					pstmt.setString(2,unitTo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						fact = rs.getDouble(1);
						round = rs.getString(2);
						roundTo = rs.getDouble(3);
						System.out.println("fact........"+fact);
					}
					pstmt.close();
					pstmt = null;
				}
				catch(Exception e)
				{
					System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//else
		}//if
		else
		{
			try
			{
				sql = "SELECT FACT,ROUND,ROUND_TO FROM UOMCONV "
					+ "WHERE UNIT__FR = ? "
					+ "AND UNIT__TO = ? "
					+ "AND ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,unitFrom);
				pstmt.setString(2,unitTo);
				pstmt.setString(3,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					fact = rs.getDouble(1);
					round = rs.getString(2);
					roundTo = rs.getDouble(3);
					System.out.println("fact........"+fact);
				}
				pstmt.close();
				pstmt = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//else
		newQty = (fact * toConvert);
		round = round.toUpperCase();
		if(round.trim().equals("X"))
		{
			newQty = (newQty - (newQty % roundTo) + roundTo);
		}
		if(round.trim().equals("P"))
		{
			newQty = (newQty - (newQty % roundTo));
		}
		if(round.trim().equals("R"))
		{
			if((newQty % roundTo) < (roundTo / 2))
			{
				newQty = (newQty - (newQty % roundTo));
			}
			else
			{
				newQty = (newQty - (newQty % roundTo) + roundTo);
			}
		}
		return newQty;
	}//convQtyFactor */

	public ArrayList convQtyFactor(String unitFrom,String unitTo,String itemCode,double toConvert,double fact,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String round = "";
		double roundTo = 0.0;	
		double newQty = 0.0;	
		double errQty = -999999999;
		int count = 0;
		ArrayList retValue = new ArrayList();
		
		if(unitFrom.trim().equals(unitTo.trim()))
		{
			//return toConvert;
			fact = 1;
			retValue.add(new Double(fact));
			retValue.add(new Double(toConvert));
			return retValue;
		}
		try
		{
			sql = "SELECT COUNT(*) AS COUNT FROM UOMCONV "
				+ "WHERE UNIT__FR = ? "
				+ "AND UNIT__TO = ? "
				+ "AND ITEM_CODE = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,unitFrom);
			pstmt.setString(2,unitTo);
			pstmt.setString(3,itemCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("COUNT");
				//System.out.println("Count........1 "+count);
			}
			pstmt.close();
		}//try
		catch(Exception e)
		{
			System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		if(count == 0)
		{
			try
			{
				sql = "SELECT COUNT(*) AS COUNT FROM UOMCONV "
					+ "WHERE UNIT__FR = ? "
					+ "AND UNIT__TO = ? "
					+ "AND ITEM_CODE = 'X'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,unitFrom);
				pstmt.setString(2,unitTo);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt("COUNT");
					//System.out.println("Count........2 "+count);
				}
				pstmt.close();
				pstmt = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
			if(count == 0)
			{
				//return errQty;
				if (fact == 0)
				{
					retValue.add(new Double(fact));
					retValue.add(new Double(errQty));
					return retValue;
				}
				else
				{
					retValue.add(new Double(fact));
					retValue.add(new Double(toConvert * fact));
					return retValue;
				}
			}
			else
			{
				try
				{
					sql = "SELECT FACT,ROUND,ROUND_TO FROM UOMCONV "
						+ "WHERE UNIT__FR = ? "
						+ "AND UNIT__TO = ? "
						+ "AND ITEM_CODE = 'X'";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,unitFrom);
					pstmt.setString(2,unitTo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						fact = rs.getDouble(1);
						round = rs.getString(2);
						roundTo = rs.getDouble(3);
						//System.out.println("fact........"+fact);
					}
					pstmt.close();
					pstmt = null;
				}
				catch(Exception e)
				{
					System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
					e.printStackTrace();
					throw new ITMException(e);
				}
			}//else
		}//if
		else
		{
			try
			{
				sql = "SELECT FACT,ROUND,ROUND_TO FROM UOMCONV "
					+ "WHERE UNIT__FR = ? "
					+ "AND UNIT__TO = ? "
					+ "AND ITEM_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,unitFrom);
				pstmt.setString(2,unitTo);
				pstmt.setString(3,itemCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					fact = rs.getDouble(1);
					round = rs.getString(2);
					roundTo = rs.getDouble(3);
					//System.out.println("fact........"+fact);
				}
				pstmt.close();
				pstmt = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[convQtyFactor] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}//else
		newQty = (fact * toConvert);
		round = round.toUpperCase();
		if(round.trim().equals("X"))
		{
			newQty = (newQty - (newQty % roundTo) + roundTo);
		}
		if(round.trim().equals("P"))
		{
			newQty = (newQty - (newQty % roundTo));
		}
		if(round.trim().equals("R"))
		{
			if((newQty % roundTo) < (roundTo / 2))
			{
				newQty = (newQty - (newQty % roundTo));
			}
			else
			{
				newQty = (newQty - (newQty % roundTo) + roundTo);
			}
		}

		retValue.add(new Double(fact));
		retValue.add(new Double(newQty));
		return retValue;
		
		//return newQty;
	}
	
	public double getIntegralQty(String custCode,String itemCode ,String siteCode,Connection conn)
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		double intQty =0.0 ;
		int cnt =0;	
	
		try
		{
			sql = "select integral_qty  from customeritem "
						+" where cust_code = '"+custCode+"' "
						+ "and item_code = '"+itemCode+"' ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				intQty = rs.getDouble(1);
				//System.out.println("intQty........"+intQty);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if(intQty == 0)
			{
				sql = "select integral_qty  from siteitem "
						+" where site_code = '"+siteCode+"' "
						+" and item_code = '"+itemCode+"' ";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					intQty = rs.getDouble(1);
					
				}
				rs.close();
				rs = null;			
				pstmt.close();
				pstmt = null;
				if(intQty==0)
				{
					sql="select integral_qty  from item "
						+"where item_code = '"+itemCode+"' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						intQty = rs.getDouble(1);
						
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(intQty == 0)
					{
						sql="select count(*)  from item where item_code = '"+itemCode+"' ";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
							
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if(cnt==0)
						{
							sql="select batch_qty  from bom "
									+" where bom_code ='"+itemCode+"' ";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								intQty = rs.getDouble(1);
								
							}
							rs.close();
							rs = null;							
							pstmt.close();
							pstmt = null;
						}
					}					
						
				}
			}			
		
		}		
		catch(Exception e)
		{
			System.out.println("Exception...[getIntegralQty] "+sql+e.getMessage());
			e.printStackTrace();
		}
		return intQty;	
	} //GET
		
	public String getToken(String source,String separator)
	{
		
		int	p =0;
		int lenSource=0;
		int lenSeparator=0;
		int diffstr =0;
		String 	ret = "";
		
		try
		{			
			//p = Pos(source, separator)			
			p = source.indexOf(separator);			
			if(p == -1)						// if no separator, 
			{	ret = source;				// return the whole source string and
				source = "";				// make the original source of zero length
			}	
			else
			{
				ret = source.substring(0,p);				
				lenSource = source.length();
				lenSeparator =separator.length();
				diffstr = lenSource - (p+lenSeparator ) +1 ;
				source = source.substring(diffstr , lenSource);
			}			
		}
		catch(Exception e)
		{
			System.out.println("Exception...[getToken] "+e.getMessage());
			e.printStackTrace();
		}
		 return ret ; 		
	}	
	public String getItemSer(String itemCode,String siteCode,Timestamp tranDate,String sudryCode,String sundryType,Connection conn) //Added by pankaj 01/09/2009  to return Itemser
	{	
		String itemSer = null;	
		String itemSerInv = null;
		String sql = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			sql = "SELECT item_ser FROM siteitem "
						+ "WHERE site_code = '" + siteCode + "'"
						+ "AND ITEM_CODE = '" + itemCode + "'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				itemSer = rs.getString(1);
				//System.out.println("itemSer........"+itemSer);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if( itemSer == null || itemSer.trim().length() == 0 )
			{
				sql = "select item_ser from itemser_change "
							+ " where  ITEM_CODE = '" + itemCode + "'"
							+ " and (( eff_date <= ?"
							+ " and  valid_upto >= ? ) or (valid_upto is Null))";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,tranDate);
				pstmt.setTimestamp(2,tranDate);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					itemSer = rs.getString(1);
					//System.out.println("itemSer........"+itemSer);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				//CHECKING ITEM SERIES AGAIN FROM ITEMSER CHANGE FOR OLD ITEM SER
				if( itemSer == null || itemSer.trim().length() == 0)
				{
					sql = " SELECT item_ser__old FROM itemser_change "
								+ " WHERE ITEM_CODE = '" + itemCode + "'"
								+ " AND (( eff_date >= ?"
								+ " AND  valid_upto >= ?) or (valid_upto is null ))"
								+ " AND eff_date = (select min(eff_date) from itemser_change"
								+ " where item_code = '" + itemCode + "')";
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1,tranDate);
				    pstmt.setTimestamp(2,tranDate);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						itemSer = rs.getString(1);
						//System.out.println("itemSer........"+itemSer);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if( itemSer == null || itemSer.trim().length() == 0)
					{
						sql = " SELECT item_ser__old FROM itemser_change "
									+ " WHERE ITEM_CODE = '" + itemCode + "'"
									+ " AND (( eff_date >= ?"
									+ " AND valid_upto >= ?) or (valid_upto is null))"
									+ " AND eff_date = (select min(eff_date) from itemser_change"
									+ " where item_code = '" + itemCode + "')";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1,tranDate);
						pstmt.setTimestamp(2,tranDate);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							itemSer = rs.getString(1);
							//System.out.println("itemSer........"+itemSer);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						sql = "SELECT item_ser FROM item "
								+ "WHERE ITEM_CODE = '" + itemCode + "'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							itemSer = rs.getString(1);
							//System.out.println("itemSer........"+itemSer);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
					}
				}
			}
			if( sundryType.equalsIgnoreCase("C") )
			{
				sql = "SELECT item_ser__inv FROM customer_series "
							+ "WHERE cust_code = '" + sudryCode + "'"
							+ "AND item_ser = '" + itemSer + "'";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					itemSerInv = rs.getString(1);
					//System.out.println("itemSer........"+itemSer);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if( itemSerInv != null && itemSerInv.trim().length() > 0)
				{
					itemSer = itemSerInv;
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception...[getItemSer] "+sql+e.getMessage());
			e.printStackTrace();
		}
		return itemSer;
	}// end getItemSer
	//start added by alam on 020209	
	public int getNoArt(String siteCode,String custCode,String itemCode , String packCode, double acQty , char  type , double acShipperQty , double acIntegralQty , Connection conn) // Added by Manazir
	{
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			String sql = "";
			double capacity =0.0,reoQty=0.0,shipperQty=0.0,remainder=0.0,lcIntegralQty=0.0;
			double liNoArt1=0,liNoArt2=0,liNoArt=0;
			try
			{
				switch(type)
				{
					case 'S':
						sql="select (case when capacity is null then 0 else capacity end) "
							+"from packing "
							+"where pack_code = '"+packCode+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							capacity = rs.getDouble(1);
							//System.out.println("capacity........"+capacity);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						sql=" select reo_qty   from siteitem  where site_code ='"+siteCode+"'" 
								+" and item_code = '" + itemCode + "'";
						//System.out.println("sql for retro qty:- " + sql);
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							reoQty = rs.getDouble(1);
							//System.out.println("reoQty........"+reoQty);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if(reoQty ==0.0)
						{
							sql="select reo_qty  from item "
								+"where item_code = '" + itemCode + "'";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								reoQty = rs.getDouble(1);
								//System.out.println("reoQty........"+reoQty);
							}
							rs.close();
							rs = null;	
							pstmt.close();
							pstmt = null;
						}
						if(capacity > 0)					
						shipperQty = capacity;					
						else 					
						shipperQty = reoQty;
						
						if( shipperQty > 0 )
						{
							liNoArt =(acQty - acQty%shipperQty)/shipperQty;
						}				
					break;
					
					case 'I' :
						sql="select (case when capacity is null then 0 else capacity end)  from packing "
								+"where pack_code = '"+packCode+"' ";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							capacity = rs.getDouble(1);
							//System.out.println("capacity........"+capacity);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						sql="select reo_qty   from siteitem "
								+"where site_code = '"+siteCode+"' "
								+"and item_code = '"+itemCode+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							reoQty = rs.getDouble(1);
							//System.out.println("reoQty........"+reoQty);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if( reoQty==0 )
						{
							sql="select reo_qty	from item "
								+"where item_code = '"+itemCode+"' ";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								reoQty = rs.getDouble(1);
								//System.out.println("reoQty........"+reoQty);
							}
							rs.close();
							rs = null;						
							pstmt.close();
							pstmt = null;
						}
						if(capacity > 0)					
						shipperQty = capacity;					
						else
						shipperQty = reoQty;
						if( shipperQty > 0 )
						{
							liNoArt =(acQty - acQty%shipperQty)/shipperQty;
							remainder = acQty%shipperQty;
						}
						sql="select integral_qty  from customeritem "
							+"where cust_code = '"+custCode+"' "
							+"and item_code = '"+itemCode+"'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							lcIntegralQty = rs.getDouble(1);
							//System.out.println("lcIntegralQty........"+lcIntegralQty);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if(lcIntegralQty ==0)
						{
							sql="select integral_qty   from siteitem "
								+"where site_code = '"+siteCode+"' "
								+"and item_code = '"+itemCode+"' ";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								lcIntegralQty = rs.getDouble(1);
								//System.out.println("lcIntegralQty........"+lcIntegralQty);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							if(lcIntegralQty==0)
							{
								sql="select (case when integral_qty is null then 0 else integral_qty end)"
									+"from item where item_code = '"+itemCode+"' ";
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									lcIntegralQty = rs.getDouble(1);
									//System.out.println("lcIntegralQty........"+lcIntegralQty);
								}
								rs.close();
								rs = null;						
								pstmt.close();
								pstmt = null;
							}
							if( lcIntegralQty>0 )
							liNoArt2 = (remainder - remainder%lcIntegralQty)/lcIntegralQty ;
							if( liNoArt2 > 0)
							liNoArt2=1;							
							liNoArt = liNoArt1 + liNoArt2;	
						}				
					break ;	
				}	
				
			}
			catch(Exception e)
			{
				System.out.println("Exception...[getNoArt] "+sql+e.getMessage());
				e.printStackTrace();
			}		
			acShipperQty = shipperQty;
			acIntegralQty  = lcIntegralQty;
			return (int)liNoArt;
		
		}	// end getNoart()
	//end added by alam on 020209
public int getNoArt(String siteCode,String custCode,String itemCode , String packCode, int acQty , char  type , double acShipperQty , double acIntegralQty , Connection conn) // Added by Manazir
{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		double capacity =0.0,reoQty=0.0,shipperQty=0.0,remainder=0.0,lcIntegralQty=0.0;
		double liNoArt1=0,liNoArt2=0,liNoArt=0;
		try
		{
			switch(type)
			{
				case 'S':
					sql="select (case when capacity is null then 0 else capacity end) "
						+"from packing "
						+"where pack_code = '"+packCode+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						capacity = rs.getDouble(1);
						//System.out.println("capacity........"+capacity);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql=" select reo_qty   from siteitem  where site_code ='"+siteCode+"'" 
							+" and item_code = '" + itemCode + "'";
					//System.out.println("sql for retro qty:- " + sql);
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						reoQty = rs.getDouble(1);
						//System.out.println("reoQty........"+reoQty);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(reoQty ==0.0)
					{
						sql="select reo_qty  from item "
							+"where item_code = '" + itemCode + "'";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							reoQty = rs.getDouble(1);
							//System.out.println("reoQty........"+reoQty);
						}
						rs.close();
						rs = null;	
						pstmt.close();
						pstmt = null;
					}
					if(capacity > 0)					
					shipperQty = capacity;					
					else 					
					shipperQty = reoQty;
					
					if( shipperQty > 0 )
					{
						liNoArt =(acQty - acQty%shipperQty)/shipperQty;
					}				
				break;
				
				case 'I' :
					sql="select (case when capacity is null then 0 else capacity end)  from packing "
							+"where pack_code = '"+packCode+"' ";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						capacity = rs.getDouble(1);
						//System.out.println("capacity........"+capacity);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					sql="select reo_qty   from siteitem "
							+"where site_code = '"+siteCode+"' "
							+"and item_code = '"+itemCode+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						reoQty = rs.getDouble(1);
						//System.out.println("reoQty........"+reoQty);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if( reoQty==0 )
					{
						sql="select reo_qty	from item "
							+"where item_code = '"+itemCode+"' ";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							reoQty = rs.getDouble(1);
							//System.out.println("reoQty........"+reoQty);
						}
						rs.close();
						rs = null;						
						pstmt.close();
						pstmt = null;
					}
					if(capacity > 0)					
					shipperQty = capacity;					
					else
					shipperQty = reoQty;
					if( shipperQty > 0 )
					{
						liNoArt =(acQty - acQty%shipperQty)/shipperQty;
						remainder = acQty%shipperQty;
					}
					sql="select integral_qty  from customeritem "
						+"where cust_code = '"+custCode+"' "
						+"and item_code = '"+itemCode+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						lcIntegralQty = rs.getDouble(1);
						//System.out.println("lcIntegralQty........"+lcIntegralQty);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					if(lcIntegralQty ==0)
					{
						sql="select integral_qty   from siteitem "
							+"where site_code = '"+siteCode+"' "
							+"and item_code = '"+itemCode+"' ";
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							lcIntegralQty = rs.getDouble(1);
							//System.out.println("lcIntegralQty........"+lcIntegralQty);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if(lcIntegralQty==0)
						{
							sql="select (case when integral_qty is null then 0 else integral_qty end)"
								+"from item where item_code = '"+itemCode+"' ";
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								lcIntegralQty = rs.getDouble(1);
								//System.out.println("lcIntegralQty........"+lcIntegralQty);
							}
							rs.close();
							rs = null;						
							pstmt.close();
							pstmt = null;
						}
						if( lcIntegralQty>0 )
						liNoArt2 = (remainder - remainder%lcIntegralQty)/lcIntegralQty ;
						if( liNoArt2 > 0)
						liNoArt2=1;							
						liNoArt = liNoArt1 + liNoArt2;	
					}				
				break ;	
			}	
			
		}
		catch(Exception e)
		{
			System.out.println("Exception...[getNoArt] "+sql+e.getMessage());
			e.printStackTrace();
		}		
		acShipperQty = shipperQty;
		acIntegralQty  = lcIntegralQty;
		return (int)liNoArt;
	
	}	// end getNoart()
	//added by rajendra on 10/23/2008
	public String stockTransferMulti(String  tran_id, String  invLink,String xtraParams,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;
		String sql = "";
		String siteCode = "",refSerFor="",refIdFor="",reasCode="",mcctr="";
		String itemCode="",locCodeFrom="",locCodeTo="",lotNo="",lotNoTo="",lotSl="",lotSlTo="",acctCodeCr="",acctCodeDr="";
		String cctrCodeDr="",cctrCodeCr="";
		String invstat="",grade="",suppCodeMfg="",siteCodeMfg="",unit="",packCode="",unitAlt="",dimension="";
		String packInstr="",cctrCodeInv="",acctCodeInv="",batchNo="",postOnLine="",finEntity="";
		String itemSer="",overiss="",errString ="",macct ="",currCode="",expLev="";
		String invacct="",trfacct="",errCode ="",invStat="",ls_inv_online="";
		String acctCodeXFRX="",cctrCodeXFRX="",trantype ="",remarks="";
		HashMap stkUpdMap = null,glTraceMap=null;
		int lineNo =0,qccount = 0,noArt,qcCount =0,upd =0;
		double effqty=0,amt=0;
		Timestamp tranDate=null,mfgDate=null,expDate=null,retestDate=null;
		double qtyorg=0,qty=0,rate=0,grossRate=0,grossWeight=0,tareWeight=0,netWeight=0,convQtyStduom=0,potencyPerc=0;				
		try
			{
				stkUpdMap = new HashMap();
				glTraceMap = new HashMap();
				FinCommon finCommon = new FinCommon();
				sql = "SELECT    TRAN_DATE, SITE_CODE,   REF_SER__FOR,REF_ID__FOR, REAS_CODE "
				  +" FROM STOCK_TRANSFER  "
				  +" WHERE  TRAN_ID   = '"+tran_id+"' ";
				//System.out.println("sql......................"+sql); 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					tranDate = rs.getTimestamp("TRAN_DATE");
					siteCode = rs.getString("SITE_CODE");
					refSerFor = rs.getString("REF_SER__FOR");
					refIdFor = rs.getString("REF_ID__FOR");
					reasCode = rs.getString("REAS_CODE");
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				sql = "select   tran_id,line_no,item_code,quantity,loc_code__fr,loc_code__to,lot_no__fr,  "
					+ " lot_no__to,   lot_sl__fr, lot_sl__to,  remarks, acct_code__cr,acct_code__dr, cctr_code__dr, cctr_code__cr "
					+ "from stock_transfer_det  where tran_id = '"+tran_id+"' ";
				//System.out.println("sql............................."+sql); 
				pstmt1 = conn.prepareStatement(sql);
				rs1 = pstmt1.executeQuery();
				while(rs1.next())
				{
					lineNo = rs1.getInt("line_no");
					itemCode =rs1.getString("item_code");
					effqty =rs1.getDouble("quantity");
					//System.out.println("effqty.................."+effqty); 
					locCodeFrom =rs1.getString("loc_code__fr");
					locCodeTo =rs1.getString("loc_code__to");
					lotNo =rs1.getString("lot_no__fr");
					lotNoTo =rs1.getString("lot_no__to");
					lotSl =rs1.getString("lot_sl__fr");
					lotSlTo =rs1.getString("lot_sl__to");
					remarks	   =rs1.getString("remarks");
					acctCodeCr =rs1.getString("acct_code__cr");
					acctCodeDr =rs1.getString("acct_code__dr");
					cctrCodeDr =rs1.getString("cctr_code__dr");
					cctrCodeCr =rs1.getString("cctr_code__cr");
					noArt	= 0;
					if(effqty > 0)
					{
							qcCount = 0;
							sql = "select count(1) from 	 qc_order  where  site_code ='"+siteCode+"'  "
									+ " and item_code ='"+itemCode+"' "
									+ " and lot_no ='"+lotNo+"' "
									+ " and lot_sl ='"+lotSl+"' "
									+ " and status <> 'C' " ;
							//System.out.println("sql........"+sql); 
							pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								qcCount = rs.getInt(1);
							}
							//System.out.println("qcCount.........................."+qcCount); 
							rs.close();
							rs =null;
							pstmt.close();
							pstmt = null;
							if(qcCount > 0)
							{
								sql = "update qc_order 	set loc_code = NULL,lot_sl = NULL,tran_id__xfrx = '"+tran_id+"' "
								     +" where  site_code = '"+siteCode+"' and  loc_code ='"+locCodeFrom+"' "
									 +" and	 item_code ='"+itemCode+"'  and lot_no ='"+lotNo+"'  and lot_sl ='"+lotSl+"' "
									 +" and	 status 	 <> 'C' ";
							   //System.out.println("sql........"+sql); 
							   pstmt = conn.prepareStatement(sql);
							   upd = pstmt.executeUpdate();
							   pstmt.close();
							   pstmt = null;
							}
							else
							{
								sql = "select count(1) from  qc_order  where  site_code ='"+siteCode+"'  "
									+ " and loc_code ='"+locCodeFrom+"'  and item_code ='"+itemCode+"' "
									+ " and lot_no ='"+lotNo+"' "
									+ " and lot_sl is null "
									+ " and status <> 'C' " ;
								//System.out.println("sql........"+sql); 
								pstmt = conn.prepareStatement(sql);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									qcCount = rs.getInt(1);
								}
								rs.close();
								rs =null;
								pstmt.close();
								pstmt = null;
								if(qcCount > 0)
								{
									sql = "update qc_order 	set set loc_code = NULL,tran_id__xfrx = '"+tran_id+"' "
										 +" where  site_code = '"+siteCode+"' and  loc_code ='"+locCodeFrom+"' "
										 +" and	 item_code ='"+itemCode+"'  and lot_no ='"+lotNo+"'  lot_sl is null "
										 +" and	 status 	 <> 'C' ";
								   //System.out.println("sql........"+sql); 
								   pstmt = conn.prepareStatement(sql);
								   upd = pstmt.executeUpdate();
								   pstmt.close();
								   pstmt = null;
								}
							}//else
					}//if(effqty > 0)
				sql ="Select quantity,(quantity - (case when alloc_qty is null then 0 else alloc_qty end)) qty,inv_stat,grade,	mfg_date, 	site_code__mfg,	potency_perc,	pack_code,	supp_code__mfg, unit,	"			 
					 +" rate, 	gross_rate, 	exp_date, 	gross_weight,tare_weight, 	net_weight,	supp_code__mfg, 	retest_date	, "
				     +" conv__qty_stduom,batch_no, acct_code__inv,cctr_code__inv	,pack_instr,dimension, 	unit__alt "
					+" From 		stock Where 	item_code = '"+itemCode+"' and site_code = '"+siteCode+"' 	And loc_code  ='"+locCodeFrom+"' "
					+" and lot_no    = '"+lotNo+"'		And lot_sl    = '"+lotSl+"' " ;
				//System.out.println("sql............................"+sql); 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					qtyorg =rs.getDouble("quantity");
					qty  =rs.getDouble("qty");
					invstat =rs.getString("inv_stat");
					grade =rs.getString("grade");
					rate  =rs.getDouble("rate");
					grossRate  =rs.getDouble("gross_rate");
					mfgDate = rs.getTimestamp("mfg_date");
					expDate = rs.getTimestamp("exp_date");
					grossWeight  =rs.getDouble("gross_weight");
					tareWeight  =rs.getDouble("tare_weight");
					netWeight  =rs.getDouble("net_weight");
					suppCodeMfg =rs.getString("supp_code__mfg");
					retestDate = rs.getTimestamp("retest_date");
					convQtyStduom  =rs.getDouble("conv__qty_stduom");
					siteCodeMfg =rs.getString("site_code__mfg");
					batchNo =rs.getString("batch_no");
					acctCodeInv =rs.getString("acct_code__inv");
					cctrCodeInv =rs.getString("cctr_code__inv");
					packInstr =rs.getString("pack_instr");
					dimension =rs.getString("dimension");
					unitAlt =rs.getString("unit__alt");
					potencyPerc =rs.getDouble("potency_perc");
					packCode =rs.getString("pack_code");
					unit =rs.getString("unit");
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				if (acctCodeDr ==null || acctCodeDr.trim().length() ==0)
				{	
					String acctCodeIN="";
					String cctrCodeIN="";
					String acctDetrType = finCommon.getFromAcctDetr(itemCode,"","IN",conn) ;
					//System.out.println("acctDetrType.....IN........" + acctDetrType);
					if (acctDetrType != null && acctDetrType.trim().length() > 0)
					{
						acctCodeIN = acctDetrType.substring(0,acctDetrType.indexOf(","));
						cctrCodeIN = acctDetrType.substring(acctDetrType.indexOf(",")+1);
					}
					//System.out.println("acctStr.....IN........" + acctCodeIN);
					//System.out.println("acctStr.....IN........" + cctrCodeIN);
				}
				if(effqty == 0 )  continue;
				if(! invLink.equalsIgnoreCase("Y"))
				{
					qtyorg = qty;
				}
				sql ="select overiss from invstat where inv_stat = '"+invstat+"' ";
				//System.out.println("sql........"+sql); 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					overiss = rs.getString("overiss");
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;
				if(effqty > qtyorg && (! overiss.equalsIgnoreCase("Y")))
				{
					//System.out.println("overiss...1111222333......................."+overiss); 
					errCode = "VTOVERISS1";
					return (errCode);
				}
				itemSer =  getItemSer(itemCode, siteCode,tranDate,"","O",conn);
				//System.out.println("itemSer........................."+itemSer);
				if(lotNoTo == null || lotNoTo.trim().length() ==0)
				{
					lotNoTo ="               ";
				}
				if(lotNo == null || lotNo.trim().length() ==0)
				{
					lotNo ="               ";
				}
				if(lotSlTo == null || lotSlTo.trim().length() ==0)
				{
					lotNoTo ="     ";
				}
				if(lotSl == null || lotSl.trim().length() ==0)
				{
					lotNo ="     ";
				}
				stkUpdMap.put("ref_ser__for",refSerFor);
				stkUpdMap.put("ref_id__for",refIdFor);
				stkUpdMap.put("tran_ser","XFRX");
				stkUpdMap.put("tranid",tran_id);
				if(invLink.equalsIgnoreCase("Y"))
				{
					trantype ="ID";
				}
				else
				{
					trantype ="I";
				}
				stkUpdMap.put("tran_type",trantype);
				stkUpdMap.put("item_code",itemCode);
				stkUpdMap.put("site_code",siteCode);
				stkUpdMap.put("ref_ser__for",refSerFor);
				stkUpdMap.put("loc_code",locCodeFrom);
				stkUpdMap.put("lot_no",lotNo);
                stkUpdMap.put("lot_sl",lotSl);
				stkUpdMap.put("unit",unit);
                stkUpdMap.put("quantity",Double.toString(effqty));
				stkUpdMap.put("qty_stduom",Double.toString(effqty));
				stkUpdMap.put("tran_date",tranDate);
				stkUpdMap.put("acct_code__cr",acctCodeCr);
				stkUpdMap.put("cctr_code__cr",cctrCodeCr);
				stkUpdMap.put("remarks",remarks);

				String acctDetrType = finCommon.getAcctDetrTtype(itemCode,itemSer,"XFRX", " ",conn) ;
				//System.out.println("acctDetrType.....XFRX........" + acctDetrType);
				if (acctDetrType != null && acctDetrType.trim().length() > 0)
				{
					acctCodeXFRX = acctDetrType.substring(0,acctDetrType.indexOf(","));
					cctrCodeXFRX = acctDetrType.substring(acctDetrType.indexOf(",")+1);
				}
				//System.out.println("acctStr.....XFRX........" + acctCodeXFRX);
				//System.out.println("acctStr.....XFRX........" + cctrCodeXFRX);
				if(cctrCodeXFRX.trim().length() > 0)
				{
					if ( acctCodeXFRX == null || acctCodeXFRX.trim().length() ==0)
					{
						invacct = finCommon.getFinparams("999999", "INVENTORY_ACCT",conn);
						trfacct = finCommon.getFinparams("999999", "INV_ACCT_TRF",conn);
							if(invacct.equalsIgnoreCase("Y") || trfacct.equalsIgnoreCase("Y"))
							{
								errCode = "VTTRFACT";
								return errCode;
							}
					}
				}
				stkUpdMap.put("acct_code__dr",acctCodeXFRX);
				if (cctrCodeXFRX == null ||cctrCodeXFRX.trim().length() == 0)
				{
					stkUpdMap.put("cctr_code__dr",cctrCodeDr);
				}
				else
				{
					stkUpdMap.put("cctr_code__dr",cctrCodeXFRX);
				}
				stkUpdMap.put("line_no","  1");
				stkUpdMap.put("rate",Double.toString(rate));
				stkUpdMap.put("gross_rate",Double.toString(grossRate));
				stkUpdMap.put("site_code_mfg",siteCodeMfg);
				stkUpdMap.put("potency_perc",Double.toString(potencyPerc));
				stkUpdMap.put("pack_code",packCode);
				stkUpdMap.put("mfg_date",mfgDate);
				stkUpdMap.put("exp_date",expDate);
				stkUpdMap.put("inv_stat",invstat);
				stkUpdMap.put("acct_code_inv",acctCodeInv);
				stkUpdMap.put("cctr_code_inv",cctrCodeInv);
				if (qtyorg != 0)
				{
					grossWeight = (grossWeight / qtyorg * effqty);
					grossWeight =getRequiredDecimal(grossWeight, 3);
					stkUpdMap.put("gross_weight",Double.toString(grossWeight));
					tareWeight = (tareWeight / qtyorg * effqty);
					tareWeight =getRequiredDecimal(tareWeight, 3);
					
					stkUpdMap.put("tare_weight",Double.toString(tareWeight));
					netWeight = (netWeight / qtyorg * effqty);
					netWeight =getRequiredDecimal(netWeight, 3);
					stkUpdMap.put("net_weight",Double.toString(netWeight));
				}
				stkUpdMap.put("supp_code__mfg",suppCodeMfg);
				stkUpdMap.put("retest_date",retestDate);
				stkUpdMap.put("grade",grade);
				stkUpdMap.put("conv__qty_stduom",Double.toString(convQtyStduom));
				stkUpdMap.put("batch_no",batchNo);
				stkUpdMap.put("pack_instr",packInstr);
				stkUpdMap.put("dimension",dimension);
				stkUpdMap.put("unit__alt",unitAlt);
				stkUpdMap.put("no_art",Integer.toString(noArt));
				StockUpdate stkUpd =  new StockUpdate();
				errString = stkUpd.updateStock(stkUpdMap,xtraParams,conn);
				//System.out.println("updateStock................"+errString);
				if (errString != null && errString.trim().length() > 0 )
				{
					//System.out.println("Returning Result "+errString);
					return errString;
				}


				sql ="select inv_stat from location where loc_code = '"+locCodeTo+"' ";
				//System.out.println("sql........"+sql); 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					invStat = rs.getString("inv_stat");
				}
				else
				{
					errCode = "VMLOC1";
					return errCode;
					
				}
				rs.close();
				rs =null;
				pstmt.close();
				pstmt = null;

	// Receipt
				stkUpdMap.put("tran_ser","XFRX");
				stkUpdMap.put("tranid",tran_id);
				stkUpdMap.put("tran_type","R");
				stkUpdMap.put("item_code",itemCode);
				stkUpdMap.put("site_code",siteCode);
				stkUpdMap.put("loc_code",locCodeTo);
				stkUpdMap.put("lot_no",lotNoTo);
                stkUpdMap.put("lot_sl",lotSlTo);
				stkUpdMap.put("inv_stat",invStat);
                stkUpdMap.put("quantity",Double.toString(effqty));
				stkUpdMap.put("qty_stduom",Double.toString(effqty));
				stkUpdMap.put("tran_date",tranDate);
				stkUpdMap.put("acct_code__cr",acctCodeXFRX);
				if (cctrCodeXFRX== null || cctrCodeXFRX.trim().length() == 0)
				{
					stkUpdMap.put("cctr_code__cr",cctrCodeCr);
				}
				stkUpdMap.put("cctr_code__cr",cctrCodeXFRX);
				stkUpdMap.put("acct_code_inv",acctCodeDr);
				stkUpdMap.put("cctr_code_inv",cctrCodeDr);
				stkUpdMap.put("ref_ser__for",refSerFor);
				stkUpdMap.put("ref_id__for",refIdFor);

				errString = stkUpd.updateStock(stkUpdMap,xtraParams,conn);
				//System.out.println("updateStock................"+errString);
				if (errString != null && errString.trim().length() > 0 )
				{
					//System.out.println("Returning Result "+errString);
					return errString;
				}

				if("Y".equalsIgnoreCase(invLink))
					{
						//ls_errcode = lnvo_fin.gbf_stk_transfer_post(lds_data.Describe("datawindow.data"))
			// Receipt side debit entry
						ls_inv_online = finCommon.getFinparams("999999","INV_ACCT_TRF",conn);
						if(ls_inv_online.equalsIgnoreCase("NULLFOUND"))
						{
							//errString = itmDBAccessEJB.getErrorString("","VTFINPARM","","",conn);
							return "VTFINPARM";
						}
						ls_inv_online = postOnLine.substring(0,1);
						if("Y_N".indexOf(ls_inv_online) == -1){
							//errString = itmDBAccessEJB.getErrorString("","VTFINPARM1","","",conn);
							return "VTFINPARM1";
						}
						ls_inv_online = ls_inv_online.trim();
						if(! "Y".equalsIgnoreCase(ls_inv_online))
						{
							return "";
						}
						sql = "select fin_entity from site  where site_code= '"+ siteCode +"'  ";
						//System.out.println("sql........................"+sql); 
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							finEntity = rs.getString("fin_entity");
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt = null;

						sql = "select curr_code from finent where fin_entity= '"+ finEntity +"'  ";
						//System.out.println("sql........"+sql); 
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							currCode = rs.getString("fin_entity");
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt = null;

	// for base currency exchange rate will be 1.
						//lc_exchrate = 1;
						sql = "select rate from stock where item_code= '"+ itemCode +"'  "
							  +" and site_code = '"+siteCode+"' "
							  +" and loc_code = '"+siteCode+"' "
							  +" and lot_no = '"+siteCode+"' "
							  +" and lot_sl = '"+siteCode+"' " ;
						//System.out.println("sql........"+sql); 
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							rate = rs.getDouble("rate");
						}
						rs.close();
						rs =null;
						pstmt.close();
						pstmt = null;
						amt = Math.abs(effqty) * rate;

						glTraceMap.put("tran_date",tranDate);
						glTraceMap.put("eff_date",tranDate);
						glTraceMap.put("fin_entity",finEntity);
						glTraceMap.put("site_code",siteCode);
						glTraceMap.put("sundry_type","O");
						glTraceMap.put("acct_code",acctCodeDr);
						glTraceMap.put("cctr_code",cctrCodeDr);
						glTraceMap.put("curr_code",currCode);
						glTraceMap.put("exch_rate",Double.toString(1));
						glTraceMap.put("dr_amt",Double.toString(amt));
						glTraceMap.put("cr_amt",Double.toString(0));
						glTraceMap.put("ref_type"," ");
						glTraceMap.put("ref_ser","");
						glTraceMap.put("ref_id","");
						glTraceMap.put("remarks",remarks);

						errString = finCommon.glTraceUpdate(glTraceMap,conn);
						if (errString != null && errString.trim().length() > 0 )
						{
							//System.out.println("Returning Result "+errString);
							return errString;
						}
			// Receipt side credit entry
				acctDetrType = finCommon.getAcctDetrTtype(itemCode," ","XFRX", " ",conn) ;
				//System.out.println("acctDetrType.....XFRX........" + acctDetrType);
				if (acctDetrType != null && acctDetrType.trim().length() > 0)
				{
					macct = acctDetrType.substring(0,acctDetrType.indexOf(","));
					mcctr = acctDetrType.substring(acctDetrType.indexOf(",")+1);
				}
				if(mcctr == null || mcctr.trim().length() == 0)
				{
					return "VTSTTRF";
				}
				//System.out.println("macct.....XFRX........" + macct);
				//System.out.println("mcctr.....XFRX........" + mcctr);
					
						glTraceMap.put("tran_date",tranDate);
						glTraceMap.put("eff_date",tranDate);
						glTraceMap.put("fin_entity",finEntity);
						glTraceMap.put("site_code",siteCode);
						glTraceMap.put("sundry_type","O");
						glTraceMap.put("acct_code",macct);
						glTraceMap.put("cctr_code",mcctr);
						glTraceMap.put("curr_code",currCode);
						glTraceMap.put("exch_rate",Double.toString(1));
						glTraceMap.put("dr_amt",Double.toString(0));
						glTraceMap.put("cr_amt",Double.toString(amt));
						glTraceMap.put("ref_type"," ");
						glTraceMap.put("ref_ser","");
						glTraceMap.put("ref_id","");
						glTraceMap.put("remarks",remarks);
						errString = finCommon.glTraceUpdate(glTraceMap,conn);
						if (errString != null && errString.trim().length() > 0 )
						{
							//System.out.println("Returning Result "+errString);
							return errString;
						}

			// Issue side debit entry
						glTraceMap.put("tran_date",tranDate);
						glTraceMap.put("eff_date",tranDate);
						glTraceMap.put("fin_entity",finEntity);
						glTraceMap.put("site_code",siteCode);
						glTraceMap.put("sundry_type","O");
						glTraceMap.put("acct_code",macct);
						if(mcctr == null || mcctr.trim().length() == 0 )
						{
							mcctr = cctrCodeDr;
						}
						glTraceMap.put("cctr_code",mcctr);
						glTraceMap.put("curr_code",currCode);
						glTraceMap.put("exch_rate",Double.toString(1));
						glTraceMap.put("dr_amt",Double.toString(amt));
						glTraceMap.put("cr_amt",Double.toString(0));
						glTraceMap.put("ref_type"," ");
						glTraceMap.put("ref_ser","");
						glTraceMap.put("ref_id","");
						glTraceMap.put("remarks",remarks);
						errString = finCommon.glTraceUpdate(glTraceMap,conn);
						if (errString != null && errString.trim().length() > 0 )
						{
							//System.out.println("Returning Result "+errString);
							return errString;
						}

			// Issue side credit entry
						glTraceMap.put("tran_date",tranDate);
						glTraceMap.put("eff_date",tranDate);
						glTraceMap.put("fin_entity",finEntity);
						glTraceMap.put("site_code",siteCode);
						glTraceMap.put("sundry_type","O");
						glTraceMap.put("acct_code",acctCodeCr);
						glTraceMap.put("cctr_code",cctrCodeCr);
						glTraceMap.put("curr_code",currCode);
						glTraceMap.put("exch_rate",Double.toString(1));
						glTraceMap.put("dr_amt",Double.toString(0));
						glTraceMap.put("cr_amt",Double.toString(amt));
						glTraceMap.put("ref_type"," ");
						glTraceMap.put("ref_ser","");
						glTraceMap.put("ref_id","");
						glTraceMap.put("remarks",remarks);
						errString = finCommon.glTraceUpdate(glTraceMap,conn);
						if (errString != null && errString.trim().length() > 0 )
						{
							//System.out.println("Returning Result "+errString);
							return errString;
						}
						errString = finCommon.checkGlTranDrCr("XFRX","",conn); ///??????????????????

					}
			 }
				rs1.close();
				rs1 =null;
				pstmt1.close();
				pstmt1 = null;
			}
			catch(Exception e)
			{
				System.out.println("Exception...[stockTransferMulti] "+sql+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
			return errString;
	}
	public String gfCheckTaxenvStatus( String asTaxenv, java.sql.Timestamp adDate, Connection conn ) throws SQLException
	{
		String lsErrCode = null, lsStatus = null;
		lsErrCode = "";

		PreparedStatement ps = null;
		java.sql.ResultSet rs = null;
		
		String sql = "select (case when status is null then 'A' else status end) ls_status "
					+" from   taxenv "
					+" where  tax_env      = '" + asTaxenv + "'" 
					+" and    status_date  <= ? "; 

		ps = conn.prepareStatement( sql );
		ps.setDate( 1, java.sql.Date.valueOf( adDate.toString() ) );
		rs = ps.executeQuery();
		
		if( rs.next() )
		{
			lsStatus = rs.getString( "ls_status" );
		}

		if( "C".equalsIgnoreCase( lsStatus ) )
		{
			lsErrCode = "VTTAXENVCL";
		}
		
		return lsErrCode;
	}
	//added by msalam on 210308 
	public String gbfSetIntegralQty( String lsItemCode, String lsTranType, double lcQtyOrder, String lsSiteCode, Connection conn )	
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int llCount = 0;
		
		String lsCheckIntegralQty = null;
		double integralQty = 0.0;
		
		double lcIntQty = 0.0, lcIntQtySite = 0.0;

		if( lsItemCode == null )
		{
			return "";
		}

		try{
			String sql = "Select count(1) cnt From item Where  item_code = '" + lsItemCode.trim() + "'";
			
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();
			
			if( rs.next() )
			{
				llCount = rs.getInt( "cnt" );
			}
								
			if ( llCount == 0 )
			{
				return "";
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
	
	
			sql = "Select Check_integral_qty ls_check_integral_qty From distorder_type Where tran_type = '" + lsTranType.trim() + "'";
			
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();
			
			if( rs.next() )
			{
				lsCheckIntegralQty = rs.getString( "ls_check_integral_qty" );
			}
								
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
					
			if( lsCheckIntegralQty == null ) 
			{
				lsCheckIntegralQty = "Y";
			}
					
			if( "Y".equalsIgnoreCase( lsCheckIntegralQty ) )
			{
				String qtySql = "Select integral_qty lc_int_qty_site From siteitem "
					+" Where site_code = '" + lsSiteCode.trim() + "'"
					+" 	 and item_code = '" + lsItemCode.trim() + "'";
			
			
				pstmt = conn.prepareStatement( qtySql );
				rs = pstmt.executeQuery();
				
				if( rs.next() )
				{
					lcIntQtySite = rs.getDouble( "lc_int_qty_site" );
				}
									
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
	
				String itemSql = "select integral_qty lc_int_qty from item "
				+"	where item_code = '" + lsItemCode.trim() + "'";
				
				pstmt = conn.prepareStatement( itemSql );
				rs = pstmt.executeQuery();
				
				if( rs.next() )
				{
					integralQty = rs.getDouble( "lc_int_qty" );
				}
									
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
						
				if( lcIntQtySite == 0.0 && lcIntQty != 0.0 )
				{	
					lcIntQtySite = lcIntQty;
				}
			
				if( lcIntQtySite > 0.0 )
				{
					if( ( lcQtyOrder % lcIntQtySite ) > 0 )
					{
						return "VTINTQTY";
					}		
				}
			}
		}catch( Exception ex )
		{
			ex.printStackTrace();
			return "SQLERROR";
		}
		finally{
			try{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}catch( Exception e ){}
		}		
				
		return "";
	}	

}//class