Additional function: update all linked WHs
| Code Block | ||||
|---|---|---|---|---|
| ||||
-- =============================================
-- Author: Alexander Toufexis
-- Create date: 13/05/2022
-- Description: Allow to do all linked WHs at once
-- =============================================
CREATE PROCEDURE [dbo].[_as_AddUpdatePrices_All]
-- 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,/*if -1 then all whs*/
@Markup as bit = 0,
@UpdatePrices bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @StockLink bigint
SELECT @StockLink = COALESCE((SELECT StockLink FROM StkItem S WHERE S.Code = @StockCode),0)
IF (@StockLink = 0)
BEGIN
RaisError('Stock Code %s not found!',17,1, @StockCode)
RETURN -1
END
IF @WHID = -1
BEGIN
DECLARE @WhseLink int
DECLARE WHCursor CURSOR FOR
Select WHWhseID from dbo.WhseStk WS WHERE WS.WHStockLink = @StockLink
--Open WHCursor
PRINT 'Start cursor'
OPEN WHCursor
FETCH NEXT FROM WHCursor INTO @WhseLink
WHILE @@FETCH_STATUS = 0
BEGIN
exec [_as_AddUpdatePrices] @StockCode, @PriceListName, @Price, @InclExcl, @WhseLink, @Markup, @UpdatePrices;
--CURSOR NEXT: WHCursor
FETCH NEXT FROM WHCursor INTO @WhseLink
END --WHILE @@FETCH_STATUS... WHCursor
--Cleaning up cursor:
CLOSE WHCursor
DEALLOCATE WHCursor
END
ELSE
BEGIN
exec [_as_AddUpdatePrices] @StockCode, @PriceListName, @Price, @InclExcl, @WHID, @Markup, @UpdatePrices
END
END --sp[_as_AddUpdatePrices_All]
|
Version 2, the one with markup option which includes some performance improvements
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- =============================================
-- Author: Asamco BV, Alexander Toufexis
-- Create date: 30/4/2020
-- Description: updates markup (uses _as_UpdateMarkup), but allows for WH "all".
-- =============================================
ALTER PROCEDURE [dbo].[_as_UpdateMarkup_allowAll]
@StockCode varchar(100)
,@WHCode varchar(100)
,@MarkupPerc float
,@PriceListID int = 154
AS
BEGIN
DECLARE @WHCode2 varchar(100)
IF @WHCode <> 'all'
BEGIN
exec [_as_UpdateMarkup] @StockCode, @WHCode, @MarkupPerc, @PriceListID, 1;
END
ELSE --@WHCode
BEGIN
-- all linked WHs:
DECLARE MyCursor CURSOR
FORWARD_ONLY
FOR
SELECT W.Code FROM WhseStk WS
LEFT JOIN WhseMst W ON WS.WHWhseID = W.WhseLink
LEFT JOIN StkItem S ON WS.WHStockLink = S.StockLink
WHERE S.Code = @StockCode
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @WHCode2
WHILE @@FETCH_STATUS = 0
BEGIN
exec [_as_UpdateMarkup] @StockCode, @WHCode2, @MarkupPerc, @PriceListID, 0;
FETCH NEXT FROM MyCursor INTO @WHCode2
END
CLOSE MyCursor
DEALLOCATE MyCursor
--1 time updating prices in evo
PRINT 'Updating Evo prices now for all WHs for stock ' + @StockCode;
DECLARE @StockLink int
SELECT @StockLink = StockLink FROM StkItem WHERE Code = @StockCode;
exec _espUpdateSellPrices @StockLink;
END --ELSE @WHCOde <> all..
END |
...