SQL - Add/Update Pricelist prices

-----------------------------------------------------------
----------DELETE PROCS IF EXIST:
-----------------------------------------------------------
IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_as_AddUpdatePrices') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _as_AddUpdatePrices

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alexander Toufexis
-- Create date: 25/08/2015
-- Update date: 20/11/2015 (updated to allow for incl and excl prices to be inserted
-- Description:	add / update prices to price list table. NOTE: NO WH PRICES!, and no markup costs!
-- =============================================
CREATE PROCEDURE [dbo].[_as_AddUpdatePrices]
	-- Add the parameters for the stored procedure here
	@StockCode as varchar(50),
	@PriceListName as varchar(50),
	@Price as float,
	@InclExcl as char(1) = 'I' --'E' or 'I'
AS
BEGIN
	--USAGE:
	--EXEC _as_AddUpdatePrices 'test', 'Price List 1', 22, 'I'

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--GET IDs:
	DECLARE @StockLink as int
	SELECT @StockLink = COALESCE((SELECT StockLink FROM StkItem WHERE Code = @StockCode),0)
	DECLARE @PriceListLink as int
	SELECT @PriceListLink = COALESCE((SELECT IDPriceListName FROM _etblPriceListName WHERE cName = @PriceListName),0)

	--EARLY OUTS:
	IF (@StockLink = 0)
		BEGIN
			RaisError('Stock Code %s not found!',17,1, @StockCode)
			RETURN -1
		END

	IF (@PriceListLink = 0)
		BEGIN
			RaisError('Price list %s not found!',17,1, @PriceListName)
			RETURN -1
		END

	
	--GET PRICES INCL/EXCL:
	DECLARE @InclPrice float
	DECLARE @ExclPrice float
	IF (@InclExcl = 'E')
		BEGIN
			SELECT @InclPrice = ROUND(@Price * (1+COALESCE((SELECT T.TaxRate FROM TaxRate T WHERE T.Code = (SELECT TTI FROM StkItem WHERE StockLink = @StockLink)),0)/100),2)
			SELECT @ExclPrice = @Price
		END
	ELSE
		BEGIN
			SELECT @InclPrice = @Price
			SELECT @ExclPrice = ROUND(@Price / (1+COALESCE((SELECT T.TaxRate FROM TaxRate T WHERE T.Code = (SELECT TTI FROM StkItem WHERE StockLink = @StockLink)),0)/100),2)
		END


    --CHECK IF RECORD EXISTS:
	DECLARE @idPriceListPrice int
	SELECT @idPriceListPrice = COALESCE((SELECT IDPriceListPrices FROM _etblPriceListPrices WHERE iPriceListNameID = @PriceListLink AND iStockID = @StockLink),0)
	
	PRINT 'About to start insert/update for StockCode: ' + @StockCode + '(' + CAST(@StockLink as varchar(10)) + '), PriceListName: ' + @PriceListName + '(' + CAST(@PriceListLink as varchar(10)) + '), with Excl Price: ' + CAST(@ExclPrice as varchar(10))+ '(' + CAST(@InclPrice as varchar(10)) + ').'

	IF (@idPriceListPrice = 0)
		BEGIN
			--CREATE RECORD!
			PRINT 'Inserting'
			INSERT INTO _etblPriceListPrices (iPriceListNameID, iStockID, iWarehouseID, bUseMarkup, iMarkupOnCost, fMarkupRate, fExclPrice, fInclPrice, dPLPricesTimeStamp) VALUES (@PriceListLink, @StockLink, 0,0,0,0,@ExclPrice, @InclPrice, GETDATE())
		END
	ELSE
		BEGIN
			--UPDATE RECORD!
			PRINT 'Updating'
			UPDATE _etblPriceListPrices SET fExclPrice = @ExclPrice, fInclPrice = @InclPrice WHERE IDPriceListPrices = @idPriceListPrice
		END


END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alexander Toufexis
-- Create date: 07-10-2014
-- Description:	Updates Price List Prices (NOT WH Prices)
-- Usage: EXEC _as_SetItemPrice @StockID, @PriceListName, @PriceExcl
-- =============================================
CREATE PROCEDURE _as_SetItemPrice 
	-- Add the parameters for the stored procedure here
	@StockID bigint, @PriceListName varchar(50), @PriceExcl float
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    --DECLARES:
    DECLARE @PriceID bigint
    DECLARE @PNID bigint
    DECLARE @PriceIncl float
    
    --Tax stuff:
    --Get the Invoice Tax Type
	  declare @TaxInv varchar(4)
	  select @TaxInv=TTI from [dbo].StkItem where StockLink=@StockID
    --Get invoice tax rate for inclusive price
	  declare @TaxRate float
	  select @TaxRate=TaxRate from [dbo].TaxRate where Code=@TaxInv
    --INCLUSIVE PRICE:
    declare @PriceDecs int
	set @PriceDecs = (IsNull((select top 1 Decimals from [dbo].StDfTbl where IsNull(StDfTbl_iBranchID,0)=0), 2))
    set @PriceIncl = round(@PriceExcl * (1 + @TaxRate / 100), @PriceDecs)
    
    
    --See if price exists already:
    SELECT @PNID = COALESCE((SELECT PN2.idPriceListName FROM _etblPriceListName PN2 WHERE PN2.cName = @PriceListName),0)
		--Early out:
		IF (@PNID = 0) 
			BEGIN
				RaisError(N'Price List Name does not exist',11,1)
				RETURN 0
			END
    SELECT @PriceID = (SELECT P2.idPriceListPrices FROM _etblPriceListPrices P2 WHERE P2.iStockID = @StockID AND P2.iPriceListNameID = @PNID)
    
    -- Insert statements for procedure here
	IF (@PriceID != 0)
		BEGIN --update
			UPDATE _etblPriceListPrices
				SET fExclPrice = @PriceExcl, fInclPrice = @PriceIncl
				WHERE idPriceListPrices = @PriceID
		END --IF @PriceID != 0
	ELSE
		BEGIN --insert
			INSERT INTO _etblPriceListPrices (iPriceListNameID, iStockID, iWarehouseID, bUseMarkup, iMarkupOnCost, fMarkupRate, fExclPrice, fInclPrice, _etblPriceListPrices_iBranchID,dPLPricesTimeStamp)
										VALUES(@PNID, @StockID, 0,0,0,0,@PriceExcl,@PriceIncl,0,GETDATE())
		END --IF @PriceID != 0 (else)
	
END