SQL - Add/Update Pricelist prices

Version 2, the one with markup option which includes some performance improvements

_as_AddUpdatePrices_V2

/****** Object:  StoredProcedure [dbo].[_as_AddUpdatePrices]    Script Date: 2019/05/09 1:29:36 PM ******/
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
-- Update date: 5/4/2019 (added markup and WH functionality)
-- Description: add / update prices to price list table.
-- =============================================
CREATE PROCEDURE [dbo].[_as_AddUpdatePrices_V2]
    -- Add the parameters for the stored procedure here
    @StockLink as int,
    @PriceListLink as int,
    @Price as float, --note, if markup = 1, then this is the markup percentage.
    @InclExcl as char(1) = 'I', --'E' or 'I'
    @WHID int = 0,
    @Markup as bit = 0,
	@TaxRate as float
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 PRICES INCL/EXCL:
    DECLARE @InclPrice float
    DECLARE @ExclPrice float
    IF (@InclExcl = 'E' AND @Markup = 0)
        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 @InclPrice = ROUND(@Price * @TaxRate,2)
            SELECT @ExclPrice = @Price
        END
    ELSE IF (@InclExcl = 'I' AND @Markup = 0)
        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)
			SELECT @ExclPrice = ROUND(@Price / @TaxRate,2)
        END
     
         
     
  
  
    --CHECK IF RECORD EXISTS:
    DECLARE @idPriceListPrice int
    SELECT @idPriceListPrice = COALESCE((SELECT IDPriceListPrices FROM _etblPriceListPrices WHERE iPriceListNameID = @PriceListLink AND iStockID = @StockLink AND iWarehouseID = @WHID),0)
      
    PRINT 'About to start insert/update for StockLink: (' + CAST(@StockLink as varchar(10)) + '), PriceListLink: (' + 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'
            IF (@Markup = 0)
                BEGIN
                INSERT INTO _etblPriceListPrices (iPriceListNameID, iStockID, iWarehouseID, bUseMarkup, iMarkupOnCost, fMarkupRate, fExclPrice, fInclPrice, dPLPricesTimeStamp) VALUES (@PriceListLink, @StockLink, @WHID,0,0,0,@ExclPrice, @InclPrice, GETDATE())
                END
            ELSE
                BEGIN --Markup = 1
                    INSERT INTO _etblPriceListPrices (iPriceListNameID, iStockID, iWarehouseID, bUseMarkup, iMarkupOnCost, fMarkupRate, fExclPrice, fInclPrice, dPLPricesTimeStamp)
                        (SELECT TOP 1 @PriceListLink, @StockLink, @WHID, @Markup, 0, @Price
                            , ExclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 ELSE W.fAverageCost*(100+@Price)/100 END
                            , InclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 * (1+T.TaxRate/100) ELSE W.fAverageCost*(100+@Price)/100 * (1+T.TaxRate/100) END
                            ,GETDATE()
                            FROM StkItem S
                            LEFT OUTER JOIN WhseStk W ON S.StockLink = W.WHStockLink
                            LEFT JOIN TaxRate T ON S.TTI = T.Code
                            WHERE S.StockLink = @StockLink AND COALESCE(W.WHWHseID,0) = @WHID)
                END
        END
    ELSE
        BEGIN
            --UPDATE RECORD!
            PRINT 'Updating'
            IF (@Markup = 0)
                BEGIN
                    UPDATE _etblPriceListPrices SET bUseMarkup = 0, fMarkupRate = 0, fExclPrice = @ExclPrice, fInclPrice = @InclPrice WHERE IDPriceListPrices = @idPriceListPrice
                END
            ELSE
                BEGIN
                    UPDATE _etblPriceListPrices SET bUseMarkup = @Markup
                        , fMarkupRate = @Price
                        , fExclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 ELSE W.fAverageCost*(100+@Price)/100 END
                        , fInclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 * (1+T.TaxRate/100) ELSE W.fAverageCost*(100+@Price)/100 * (1+T.TaxRate/100) END
                        FROM _etblPriceListPrices
                        LEFT JOIN StkItem S ON _etblPriceListPrices.iStockID = S.StockLink
                        LEFT JOIN WhseStk W ON _etblPriceListPrices.iStockID = W.WHStockLink and _etblPriceListPrices.iWarehouseID = W.WHWhseID
                        LEFT JOIN TaxRate T ON S.TTI = T.Code
                        WHERE IDPriceListPrices = @idPriceListPrice
                END
        END
  
  
END

New version, includes markup option

markup option

-----------------------------------------------------------
----------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
-- Update date: 5/4/2019 (added markup and WH functionality)
-- Description: add / update prices to price list table.
-- =============================================
CREATE PROCEDURE [dbo].[_as_AddUpdatePrices]
    -- Add the parameters for the stored procedure here
    @StockCode as varchar(50),
    @PriceListName as varchar(50),
    @Price as float, --note, if markup = 1, then this is the markup percentage.
    @InclExcl as char(1) = 'I', --'E' or 'I'
	@WHID int = 0,
	@Markup as bit = 0
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
	IF NOT EXISTS (SELECT * FROM WhseMst WHERE WhseLink = @WHID)
		BEGIN
			RaisError('WH with ID %d not found!',17,1, @WHID)
            RETURN -1
		END
     
    --GET PRICES INCL/EXCL:
    DECLARE @InclPrice float
    DECLARE @ExclPrice float
    IF (@InclExcl = 'E' AND @Markup = 0)
        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 IF (@InclExcl = 'I' AND @Markup = 0)
        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 AND iWarehouseID = @WHID),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'
			IF (@Markup = 0)
				BEGIN
				INSERT INTO _etblPriceListPrices (iPriceListNameID, iStockID, iWarehouseID, bUseMarkup, iMarkupOnCost, fMarkupRate, fExclPrice, fInclPrice, dPLPricesTimeStamp) VALUES (@PriceListLink, @StockLink, @WHID,0,0,0,@ExclPrice, @InclPrice, GETDATE())
				END
			ELSE
				BEGIN --Markup = 1
					INSERT INTO _etblPriceListPrices (iPriceListNameID, iStockID, iWarehouseID, bUseMarkup, iMarkupOnCost, fMarkupRate, fExclPrice, fInclPrice, dPLPricesTimeStamp)
						(SELECT TOP 1 @PriceListLink, @StockLink, @WHID, @Markup, 0, @Price
							, ExclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 ELSE W.fAverageCost*(100+@Price)/100 END
							, InclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 * (1+T.TaxRate/100) ELSE W.fAverageCost*(100+@Price)/100 * (1+T.TaxRate/100) END
							,GETDATE()
							FROM StkItem S 
							LEFT OUTER JOIN WhseStk W ON S.StockLink = W.WHStockLink
							LEFT JOIN TaxRate T ON S.TTI = T.Code
							WHERE S.StockLink = @StockLink AND COALESCE(W.WHWHseID,0) = @WHID)
				END
        END
    ELSE
        BEGIN
            --UPDATE RECORD!
            PRINT 'Updating'
			IF (@Markup = 0)
				BEGIN
					UPDATE _etblPriceListPrices SET bUseMarkup = 0, fMarkupRate = 0, fExclPrice = @ExclPrice, fInclPrice = @InclPrice WHERE IDPriceListPrices = @idPriceListPrice
				END
			ELSE
				BEGIN
					UPDATE _etblPriceListPrices SET bUseMarkup = @Markup
						, fMarkupRate = @Price
						, fExclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 ELSE W.fAverageCost*(100+@Price)/100 END
						, fInclPrice = CASE WHEN @WHID = 0 THEN S.AveUCst * (100+@Price)/100 * (1+T.TaxRate/100) ELSE W.fAverageCost*(100+@Price)/100 * (1+T.TaxRate/100) END
						FROM _etblPriceListPrices
						LEFT JOIN StkItem S ON _etblPriceListPrices.iStockID = S.StockLink
						LEFT JOIN WhseStk W ON _etblPriceListPrices.iStockID = W.WHStockLink and _etblPriceListPrices.iWarehouseID = W.WHWhseID 
						LEFT JOIN TaxRate T ON S.TTI = T.Code
						WHERE IDPriceListPrices = @idPriceListPrice
				END
        END
 
 
END

Old version (excl and incl prices):

Stored proc - incl and excl 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

Old version (excl prices only):

Add update prices (old)

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

Space shortcuts

Page tree

Version 2, the one with markup option which includes some performance improvements

New version, includes markup option

Old version (excl and incl prices):

Old version (excl prices only):