Page History

-----------------------------------------------------------
----------DELETE PROCS IF EXIST:
-----------------------------------------------------------
IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_as_AddUpdateCustomerPrices') AND type IN ( N'P', N'PC' ) )
DROP PROCEDURE _as_AddUpdateCustomerPrices
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================================================================
-- Author:           Alexander Toufexis
-- Create date: 25/08/2015 (original: 22/03/2010),
-- Update date: 19/11/2015 (added: supports customer groups now, as well as inclusive/excl support)
-- Description:      This SP adds and updates customer specific prices in the volume discount module.
-- ===================================================================================================================
CREATE PROCEDURE _as_AddUpdateCustomerPrices
       -- Add the parameters for the stored procedure here
@CustomerCode varchar(20),
@GroupCode varchar(20),
@StockCode varchar(255),
@StockGroupCode varchar(255),
@Price float,
@DiscountPerc float,
@StartDate datetime,
@EndDate datetime,
@InclExcl char(1) = 'E'
AS
BEGIN
    --USAGE:
       --on Customer:
       --EXEC _as_AddUpdateCustomerPrices 'CASH', '', 'test', 21, '2015-01-01', '2020-12-31', 'E'
       --on group:
       --EXEC _as_AddUpdateCustomerPrices '', 'GROUP', 'test', 21, '2015-01-01', '2020-12-31', 'E'
       --inclusive vs exclusive: 'I' or 'E'
 
 
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
 
--TO SET IN CODE: @AccountCode, @StockCode, @Price
 
DECLARE @CustID int
DECLARE @GroupID int
DECLARE @StockID int
DECLARE @StockGroupID int
DECLARE @VDID int
DECLARE @VDLnID int
DECLARE @AccountCode varchar(20)
 
SELECT @AccountCode = @CustomerCode
--SELECT * FROM _etblVDAR
 
--DEFINE CustomerID and Group ID.
IF (@CustomerCode <> '')
       BEGIN
       SELECT @CustID = (SELECT DCLink From Client WHERE Account = @AccountCode)
       SELECT @GroupID = 0
       END
ELSE IF (@GroupCode <> '')
       BEGIN
              SELECT @CustID = 0
              SELECT @GroupID = (SELECT IdCliClass FROM CliClass WHERE Code = @GroupCode)
       END
       --END of CustomerID and groupID selection
 
--DEFINE Stock ID and Stock Group ID.
IF (@StockCode <> '')
       BEGIN
       SELECT @StockID = (SELECT StockLink From StkItem WHERE Code = @StockCode)
       SELECT @StockGroupID = 0
       END
ELSE IF (@StockGroupCode <> '')
       BEGIN
              SELECT @StockID = 0
              SELECT @StockGroupID = (SELECT idGrpTbl FROM grptbl WHERE StGroup = @StockGroupCode)
              --select * from grptbl
       END
       --END of Stock ID and groupID selection
 
 
--EARLY OUT CHECK:
IF (COALESCE(@CustID,0) = 0 AND COALESCE(@GroupID,0) = 0)
       BEGIN
              RaisError('Customer %s or Group %s not found!',16,2,@CustomerCode,@GroupCode)
              RETURN -1
       END
IF (COALESCE(@StockID,0) = 0 AND COALESCE(@StockGroupID,0) = 0)
       BEGIN
              RaisError('Stock %s or Item Group %s not found!',16,2,@StockCode,@StockGroupCode)
              RETURN -1
       END
--END OF CHECK
 
--Percentage vs Set price:
DECLARE @bUsePrice bit, @DiscountedPrice float
IF (@Price = 0 AND COALESCE(@DiscountPerc,0) <> 0)
BEGIN
       SELECT @bUsePrice = 0, @InclExcl = '', @DiscountedPrice = @DiscountPerc
END
ELSE
BEGIN
       SELECT @bUsePrice = 1, @DiscountedPrice = @Price
END
--end of Percentage vs Set price:
 
       --CHECK IF DISCOUNT CONTRACT EXISTS FOR THE CUSTOMER. IF NOT, CREATE IT.
       IF NOT EXISTS (SELECT * FROM _etblVDAR WHERE COALESCE(iARAPID,0) = @CustID AND COALESCE(iGroupID,0) = @GroupID AND bIsTemplate = 0 AND bARAPAll = 0)
       BEGIN
       INSERT INTO [_etblVDAR] ([iARAPID],[iGroupID],[iCurrencyID],[cContractName],[bOnHold],[tDescription],[bARAPAll],[bIsTemplate])
              VALUES (@CustID,@GroupID,0,'',0,NULL,0,0)
       END
 
       --delete from [_etblVDLnAR]
       --delete from _etblVDLnLvlAR
 
       --GET ID OF DISCOUNT CONTRACT OF CUSTOMER
       SELECT @VDID = (SELECT IDVD FROM _etblVDAR WHERE COALESCE(iARAPID,0) = @CustID AND COALESCE(iGroupID,0) = @GroupID AND bIsTemplate = 0 AND bARAPAll = 0)
 
       --IF LINES DON'T EXIST FOR THE ITEM, CREATE THEM, OTHERWISE UPDATE THEM.
       IF NOT EXISTS (SELECT * FROM _etblVDLnAR WHERE iVDID = @VDID AND iStockID = @StockID AND iSTGroupID = @StockGroupID)
       BEGIN
         INSERT INTO [_etblVDLnAR] ([iVDID],[iStockID] ,[iStGroupID] ,[iCurrencyID] ,[dEffDate] ,[dExpDate] ,[bUseStockPrc] ,[cEnterInclExcl] ,[bIncremental] ,[bStockAll])
              VALUES (@VDID,@StockID,@StockGroupID,0,@StartDate,@EndDate,@bUsePrice,@InclExcl,0,0)
       END
       ELSE
        BEGIN
         --UPDATE /* select * from [_etblVDLnAR] */
              UPDATE [_etblVDLnAR] SET
                     dEffDate = @StartDate
                     ,dExpDate = @EndDate
                     ,bUseStockPrc = @bUsePrice
                     ,[cEnterInclExcl] = @InclExcl
              WHERE
                     iVDID = @VDID
                     AND
                     iStockID = @StockID AND iSTGroupID = @StockGroupID
       END
       --GET LINE ID OF THE VOLUME DISCOUNT CONTRACT LINE
       SELECT @VDLnID = (SELECT IDVDLn FROM _etblVDLnAR WHERE iVDID = @VDID and iStockID = @StockID AND iSTGroupID = @StockGroupID)
 
       --UPDATE FIRST LEVEL OF VD LINE WITH THE ENTERED PRICE
       IF NOT EXISTS (SELECT * FROM _etblVDLnLvlAR WHERE iVDLnID = @VDLnID AND iLevel = 1)
       BEGIN
         --INSERT
         INSERT INTO [_etblVDLnLvlAR] ([iVDLnID] ,[iLevel] ,[fQuantity] ,[fPriceDisc])
              VALUES
                        (@VDLnID, 1,1,@DiscountedPrice)
       END
       ELSE
       BEGIN
         -- UPDATE
         UPDATE [_etblVDLnLvlAR] SET
              fPriceDisc = @Price
         WHERE
              iVDLnID = @VDLnID
              AND
              iLevel = 1
       END
 
END
GO