@HeadOfficeID int = 0,
	@GroupID tinyint = 0,
	@CustomerID int = 0,
	@ProjectID smallint = 0,
	@STDPriceList smallint,
	@CurrencyID varchar(3),
	@ProductID varchar(20),
	@Qty decimal(8,2),
	@Price money OUTPUT,
	@PriceListID smallint OUTPUT,
	@ConvRate decimal(18,4) OUTPUT

-- If Group, Customer and Project = 0 then looking up a price for a Country or Special
as
	set nocount on

	if @ProductID = '' or @ProductID is null
	begin
		raiserror('Must specify a Product ID.',11,-1)
		return (1)
	end

	if @Qty <0	--Credit Note <0
	begin
		Set @Price = 0
		Set @PriceListID = 0
		set @ConvRate = 1
		return(1)
	end

	if @Qty <0
		Set @Qty=@Qty*-1
	if @Qty =0
	begin
		raiserror('Must specify a valid Quantity.',11,-1)
		return (1)
	end

	if @GroupID > 0
	begin
		if (Select GroupID From Groups Where GroupID=@GroupID)  is null
		begin
			raiserror('Must specify a valid GroupID ID.',11,-1)
			return (1)
		end
	end

	if @CustomerID > 0
	begin
		if (Select CustomerID From customer Where CustomerID=@CustomerID)  is null
		begin
			raiserror('Must specify a valid Customer ID.',11,-1)
			return (1)
		end
		if (Select PriceListID From Price_Lists Where PriceListID=@STDPriceList)  is null
		begin
			raiserror('Must specify a valid Customer Standard Price List.',11,-1)
			return (1)
		end
	end

	Declare @HOID2 int, @HOID3 int, @HOID4 int, @HOID5 int
	Set @HOID2=0
	Set @HOID3=0
	Set @HOID4=0
	Set @HOID5=0

	if @HeadOfficeID > 0
	begin
		if (Select CustomerID From customer Where CustomerID=@HeadOfficeID)  is null
		begin
			raiserror('Must specify a valid HeadOffice ID.',11,-1)
			return (1)
		end
		Else
		Begin
			Select @HOID2=HeadOfficeID From customer Where CustomerID=@HeadOfficeID
			if @HOID2>0
			Begin
				Select @HOID3=HeadOfficeID From customer Where CustomerID=@HOID2
				if @HOID3>0
				Begin
					Select @HOID4=HeadOfficeID From customer Where CustomerID=@HOID3
					if @HOID4>0
					Begin
						Select @HOID5=HeadOfficeID From customer Where CustomerID=@HOID4
						if @HOID5 > 0
						Begin
							Declare @MySubject varchar(100), @MyMessage varchar(300), @Email varchar(50)
							Select @EMail='itmanager@sinclairefabrics.com'		--Just in case
							Select @MySubject='Head Office over level 5, Customer ' + str(@CustomerID)
							Select @MyMessage='The head office level has more than 5 head offices above it.'
							+ str(@CustomerID) + ', ' + str(@HeadOfficeID) + ', ' + str(@HOID2) + ', ' + str(@HOID3) + ', ' + str(@HOID4) + ', ' + str(@HOID5)
							+ '  Sent from SFsp_GetPrice'
							exec master.dbo.xp_sendmail @EMail, @Subject=@MySubject, @Message=@MyMessage
						End
					End
				End
			End
		End
	end

	if @ProjectID > 0
	begin
		if (Select ProjectID From Projects Where ProjectID=@ProjectID)  is null
		begin
			raiserror('Must specify a valid Project ID.',11,-1)
			return (1)
		end
	end

	if (Select CurrencyID From List_Currencies Where CurrencyID=@CurrencyID)  is null
	begin
		raiserror('Must specify a valid Currency.',11,-1)
		return (1)
	end


--Decalre all of the comparative variables (see FP and % price/qty checks below)
Declare @PB1 smallint, @PB1P money, @PB2 smallint, @PB2P money, @PB3 smallint, @PB3P money
Declare @PB4 smallint, @PB4P money, @PB5 smallint, @PB5P money
Declare @PB6 smallint, @PB6P money, @PB7 smallint, @PB7P money, @PB8 smallint, @PB8P money
Declare @PB9 smallint, @PB9P money, @PB10 smallint, @PB10P money
Declare @PriceListID_Cursor smallint, @CurrencyID_Cursor varchar(3)
--Declare @ConvRate decimal(18,4)	--whats the largest currency?? (to 4dp)
declare @Result int

--Set the price so high that any prices selected must be below in any currency
Set @Price = 100000000

--Get Prices
Declare Prices Cursor Local Fast_Forward 
	FOR 	Select Price_Lists.PriceListID, Price_Lists.CurrencyID, Prices.PB1, Prices.PB1P, Prices.PB2, Prices.PB2P, Prices.PB3, Prices.PB3P,
		Prices.PB4, Prices.PB4P, Prices.PB5, Prices.PB5P, Prices.PB6, Prices.PB6P, Prices.PB7, Prices.PB7P, Prices.PB8, Prices.PB8P,
		Prices.PB9, Prices.PB9P, Prices.PB10, Prices.PB10P FROM
		Price_Lists inner join Prices on Price_Lists.PriceListID = Prices.PriceListID
		WHERE (StartDate <= GetDate() and EndDate >= GetDate()) AND (Product = @ProductID) AND (Price_Type='NP' AND (Price_Lists.PriceListID=@STDPriceList or 
		(Type = 'SPO' or (Type = 'GRP' and Type_Client = @GroupID) or (Type = 'CST' and Type_Client = @CustomerID) or (Type = 'CST' and Type_Client = @HeadOfficeID) or 
		(Type = 'CST' and Type_Client = @HOID2) or (Type = 'CST' and Type_Client = @HOID3) or (Type = 'CST' and Type_Client = @HOID4) or 
		(Type = 'CST' and Type_Client = @HOID5) or (Type = 'PRO' and Type_Client = @ProjectID))))
		
Open Prices
Fetch next from Prices Into  @PriceListID_Cursor, @CurrencyID_Cursor, @PB1, @PB1P, @PB2, @PB2P, @PB3, @PB3P, @PB4, @PB4P, @PB5, @PB5P,
								 @PB6, @PB6P, @PB7, @PB7P, @PB8, @PB8P, @PB9, @PB9P, @PB10, @PB10P
While (@@Fetch_Status = 0)
begin
	--Obtain exchange rate if not GBP Price list and GBP requirement
	Set @ConvRate = 0
	if @CurrencyID=@CurrencyID_Cursor
		Set @ConvRate = 1
	else
	begin
		Exec @Result = SFsp_Get_Exchange_Rate @StartCurrency=@CurrencyID_Cursor, @EndCurrency=@CurrencyID, @ConvRate=@ConvRate OUTPUT
		IF @Result <> 0
		BEGIN
			raiserror('Error getting exchange rate',11,-1)
			RETURN 1
		END	
	end

	if @@Error = 0
	begin
	  if @Qty >= @PB1
	  begin
		if @Qty >= @PB2 AND @PB2 > 0
		begin
			if @Qty >= @PB3 AND @PB3 > 0
			begin
				if @Qty >= @PB4 AND @PB4 > 0
				begin
					if @Qty >= @PB5 AND @PB5 > 0
					begin
						if @Qty >= @PB6 AND @PB6 > 0
						begin
							if @Qty >= @PB7 AND @PB7 > 0
							begin
								if @Qty >= @PB8 AND @PB8 > 0
								begin
									if @Qty >= @PB9 AND @PB9 > 0
									begin
										if @Qty >= @PB10 AND @PB10 > 0
										begin
											if @PB10P * @ConvRate < @Price
											begin
												Set @Price = @PB10P * @ConvRate
												Set @PriceListID = @PriceListID_Cursor
											end
										end
										else
										begin
											Set @Price = @PB9P * @ConvRate
											Set @PriceListID = @PriceListID_Cursor
										end
									end
									else
									Begin
										if @PB8P * @ConvRate < @Price
										begin
											Set @Price = @PB8P * @ConvRate
											Set @PriceListID = @PriceListID_Cursor
										end
									end
								end
								else
								Begin
									if @PB7P * @ConvRate < @Price
									begin
										Set @Price = @PB7P * @ConvRate
										Set @PriceListID = @PriceListID_Cursor
									end
								end
							end
							else
							Begin
								if @PB6P * @ConvRate < @Price
								begin
									Set @Price = @PB6P * @ConvRate
									Set @PriceListID = @PriceListID_Cursor
								end
							end
						end
						else
						Begin
							if @PB5P * @ConvRate < @Price
							begin
								Set @Price = @PB5P * @ConvRate
								Set @PriceListID = @PriceListID_Cursor
							end
						end
					end
					else
					Begin
						if @PB4P * @ConvRate < @Price
						begin
							Set @Price = @PB4P * @ConvRate
							Set @PriceListID = @PriceListID_Cursor
						end
					end
				end
				else
				Begin
					if @PB3P * @ConvRate < @Price
					begin
						Set @Price = @PB3P * @ConvRate
						Set @PriceListID = @PriceListID_Cursor
					end
				end
			end
			else
			Begin
				if @PB2P * @ConvRate < @Price
				begin
					Set @Price = @PB2P * @ConvRate
					Set @PriceListID = @PriceListID_Cursor
				end
			end
		end
		else
		Begin
			if @PB1P * @ConvRate < @Price
			begin
				Set @Price = @PB1P * @ConvRate
				Set @PriceListID = @PriceListID_Cursor
			end
		end
	  end
	end
	else
	begin
		Set @Price = 100000000
		Set @PriceListID = 0
		set @ConvRate = 0
		raiserror('An error occured while checking for a price therefore a valid price has not been given',11,-1)
		return (1)
	end
	Fetch next from Prices Into  @PriceListID_Cursor, @CurrencyID_Cursor, @PB1, @PB1P, @PB2, @PB2P, @PB3, @PB3P, @PB4, @PB4P, @PB5, @PB5P,
									 @PB6, @PB6P, @PB7, @PB7P, @PB8, @PB8P, @PB9, @PB9P, @PB10, @PB10P
end
Deallocate Prices

--Get Discount Prices
Declare @Disc_PLID smallint, @Disc_PType varchar(3), @Disc_DPLID smallint, @Disc_CurrencyID varchar(3)
Declare Prices Cursor Local Fast_Forward 
	FOR 	Select PriceListID, Price_Type, Disc_PLID, CurrencyID  FROM Price_Lists
		WHERE  (Price_Type='VP' OR Price_Type='PD') AND (PriceListID=@STDPriceList or 
		(Type = 'SPO' or (Type = 'GRP' and Type_Client = @GroupID) or (Type = 'CST' and Type_Client = @CustomerID) or (Type = 'CST' and Type_Client = @HeadOfficeID) or 
		(Type = 'CST' and Type_Client = @HOID2) or (Type = 'CST' and Type_Client = @HOID3) or (Type = 'CST' and Type_Client = @HOID4) or 
		(Type = 'CST' and Type_Client = @HOID5) or (Type = 'PRO' and Type_Client = @ProjectID)))
		
Open Prices
Fetch next from Prices Into  @Disc_PLID, @Disc_PType, @Disc_DPLID, @Disc_CurrencyID
While (@@Fetch_Status = 0)
begin
	--Obtain exchange rate if not GBP Price list and GBP requirement
	Set @ConvRate = 0
	if @CurrencyID=@Disc_CurrencyID
		Set @ConvRate = 1
	else
	begin
		Exec @Result = SFsp_Get_Exchange_Rate @StartCurrency=@Disc_CurrencyID, @EndCurrency=@CurrencyID, @ConvRate=@ConvRate OUTPUT
		 IF @Result <> 0
		BEGIN
			raiserror('Error getting exchange rate',11,-1)
			RETURN 1
		END
	end

	--Calculate all of the discounts from the Fixed Price List
	Declare @Disc_NextPLID smallint, @Disc_LastPLID smallint, @Disc_LastPT varchar(3)
	set @Disc_LastPLID = @Disc_PLID
	set @Disc_LastPT = @Disc_PType
	Declare @Level tinyint	--maximum of 256 discount recursions
	CREATE TABLE #PLstack (PLID smallint, PType varchar(3), PLlevel tinyint)
	set @Level =0
	while @Disc_LastPT <> 'NP'
	begin
		Select @Disc_LastPT = Price_Type, @Disc_NextPLID = Disc_PLID from Price_Lists Where PriceListID = @Disc_LastPLID
		set @Level = @Level +1
		Insert into #PLstack Values(@Disc_LastPLID, @Disc_LastPT, @Level)
		if @Disc_LastPT <> 'NP' set @Disc_LastPLID = @Disc_NextPLID
		if @Level>254
		begin
			raiserror('There is a circular reference in the price lists, i.e. a price list eventually refers to itself as the discounted list',11,-1)
			return (1)
		end
	end
	--Now @Disc_LastPLID is the Fixed Price List
	select @PB1= PB1, @PB1P = PB1P, @PB2= PB2, @PB2P = PB2P, @PB3= PB3, @PB3P = PB3P, @PB4= PB4, @PB4P = PB4P, @PB5= PB5, @PB5P = PB5P, @PB6= PB6, @PB6P = PB6P,
		@PB7= PB7, @PB7P = PB7P, @PB8= PB8, @PB8P = PB8P, @PB9= PB9, @PB9P = PB9P, @PB10= PB10, @PB10P = PB10P
		FROM Prices WHERE PriceListID = @Disc_LastPLID AND Product = @ProductID
	
	--Work back through the Price Lists
	set @level = @level -1 -- last one is Fixed Price List therefore take one step back
	while @Level > 0
	begin
		Select @Disc_LastPT = PType, @Disc_NextPLID = PLID From #PLstack where PLlevel = @Level
		if @Disc_LastPT = 'VP'
			select @PB1P = @PB1P - PB1P, @PB2P = @PB2P - PB2P, @PB3P = @PB3P - PB3P, @PB4P = @PB4P - PB4P, @PB5P = @PB5P - PB5P,
				@PB6P = @PB6P - PB6P, @PB7P = @PB7P - PB7P, @PB8P = @PB8P - PB8P, @PB9P = @PB9P - PB9P, @PB10P = @PB10P - PB10P
				FROM Prices WHERE PriceListID = @Disc_NextPLID AND Product = @ProductID
		else
			select @PB1P = @PB1P * ((100-PB1P)/100), @PB2P = @PB2P * ((100-PB2P)/100), @PB3P = @PB3P * ((100-PB3P)/100),
				@PB4P = @PB4P * ((100-PB4P)/100), @PB5P = @PB5P * ((100-PB5P)/100),
				@PB6P = @PB6P * ((100-PB6P)/100), @PB7P = @PB7P * ((100-PB7P)/100),
				@PB8P = @PB8P * ((100-PB8P)/100), @PB9P = @PB9P * ((100-PB9P)/100), @PB10P = @PB10P * ((100-PB10P)/100)
				FROM Prices WHERE PriceListID = @Disc_NextPLID AND Product = @ProductID
		set @Level = @Level -1
	end

	Drop Table #PLstack

	if @@Error = 0
	begin
	  if @Qty >= @PB1
	  begin
		if @Qty >= @PB2 AND @PB2 > 0
		begin
			if @Qty >= @PB3 AND @PB3 > 0
			begin
				if @Qty >= @PB4 AND @PB4 > 0
				begin
					if @Qty >= @PB5 AND @PB5 > 0
					begin
						if @Qty >= @PB6 AND @PB6 > 0
						begin
							if @Qty >= @PB7 AND @PB7 > 0
							begin
								if @Qty >= @PB8 AND @PB8 > 0
								begin
									if @Qty >= @PB9 AND @PB9 > 0
									begin
										if @Qty >= @PB10 AND @PB10 > 0
										begin
											if @PB10P * @ConvRate < @Price
											begin
												Set @Price = @PB10P * @ConvRate
												Set @PriceListID = @Disc_PLID
											end
										end
										else
										begin
											Set @Price = @PB9P * @ConvRate
											Set @PriceListID = @Disc_PLID
										end
									end
									else

									Begin
										if @PB8P * @ConvRate < @Price

										begin
											Set @Price = @PB8P * @ConvRate
											Set @PriceListID = @Disc_PLID
										end
									end
								end
								else
								Begin
									if @PB7P * @ConvRate < @Price
									begin
										Set @Price = @PB7P * @ConvRate
										Set @PriceListID = @Disc_PLID
									end
								end
							end
							else
							Begin
								if @PB6P * @ConvRate < @Price
								begin
									Set @Price = @PB6P * @ConvRate
									Set @PriceListID = @Disc_PLID
								end
							end
						end
						else
						Begin
							if @PB5P * @ConvRate < @Price
							begin
								Set @Price = @PB5P * @ConvRate
								Set @PriceListID = @Disc_PLID
							end
						end
					end
					else
					Begin
						if @PB4P * @ConvRate < @Price
						begin
							Set @Price = @PB4P * @ConvRate
							Set @PriceListID = @Disc_PLID
						end

					end
				end
				else
				Begin
					if @PB3P * @ConvRate < @Price
					begin
						Set @Price = @PB3P * @ConvRate
						Set @PriceListID = @Disc_PLID
					end
				end
			end
			else
			Begin
				if @PB2P * @ConvRate < @Price
				begin
					Set @Price = @PB2P * @ConvRate
					Set @PriceListID = @Disc_PLID
				end
			end
		end
		else
		Begin
			if @PB1P * @ConvRate < @Price
			begin
				Set @Price = @PB1P * @ConvRate
				Set @PriceListID = @Disc_PLID
			end
		end
	  end
	end
	else
	begin
		Set @Price = 100000000
		Set @Disc_PLID = 0
		set @ConvRate = 0
		raiserror('An error occured while checking for a price therefore a valid price has not been given',11,-1)
		return (1)
	end
	Fetch next from Prices Into  @Disc_PLID, @Disc_PType, @Disc_DPLID, @Disc_CurrencyID
end
Deallocate Prices

--If the Price is still 100000000 or -ve flag error
declare @ErrorString varchar(500)
	if @Price = 100000000
	begin
		if right(@ProductID,2)='SA' or right(@ProductID,2)='BK' or right(@ProductID,2)='CD'-- if no price is found for a sample assume 0
		Begin
			Set @Price =0
			Set @PriceListID = 1
			set @ConvRate = 1
		end
		Else
		Begin
			select @ErrorString = 'No Price was found for ' + @ProductID + ' in any Price Lists valid for - (Group(' + ltrim(str(@GroupID)) +
			 '), Customer(' + ltrim(str(@CustomerID)) + '), Project(' + ltrim(str(@ProjectID)) + '), STDPL(' + ltrim(str(@STDPriceList)) + '))'
			raiserror(@ErrorString,11,-1)
			return (1)
		end	--Non Samples
	end
	if @Price <0
	begin
		select @ErrorString = 'A negative Price was found for ' + @ProductID + ' in Price Lists ' + ltrim(str(@PriceListID))
		raiserror(@ErrorString,11,-1)
		Set @Price = 100000000
		Set @PriceListID = 0
		set @ConvRate = 0
		return (1)
	end