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)
-- Update date: 15/08/2019 : fixed discount percentage update.
-- Description:      This SP adds and updates customer specific prices in the volume discount module.
-- ===================================================================================================================
CREATE PROCEDURE [dbo].[_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)
			  PRINT 'VD contract created.'
       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)
			  PRINT 'INSERTED for customer ' + COALESCE(@CustomerCode,'') + ' and stock code: ' + COALESCE(@StockCode,'') + ' or group: ' + COALESCE(@StockGroupCode,'') 
       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
			PRINT 'updated [_etblVDLnAR] for customer ' + COALESCE(@CustomerCode,'') + ' and stock code: ' + COALESCE(@StockCode,'') + ' or group: ' + COALESCE(@StockGroupCode,'') + ' VDID: ' + CAST(@VDID as varchar(100))
       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)
						PRINT 'inserted [_etblVDLnLvlAR] for customer ' + COALESCE(@CustomerCode,'') + ' and stock code: ' + COALESCE(@StockCode,'') + ' or group: ' + COALESCE(@StockGroupCode,'') + ' VDID: ' + CAST(@VDID as varchar(100))  + ' iVDLnID: ' + CAST( @VDLnID as varchar(100)) 
       END
       ELSE
       BEGIN
         -- UPDATE
         UPDATE [_etblVDLnLvlAR] SET
              fPriceDisc = @Price@DiscountedPrice
         WHERE
              iVDLnID = @VDLnID
              AND
              iLevel = 1

			      END
 PRINT 'Readyinserted [_etblVDLnLvlAR] for customer ' + COALESCE(@CustomerCode,'') + ' and stock code: ' + COALESCE(@StockCode,'') + ' or group: ' + COALESCE(@StockGroupCode,'') END
GO+ ' VDID: ' + CAST(@VDID as varchar(100))  + ' iVDLnID: ' + CAST( @VDLnID as varchar(100)) 
       END

END