SQL - Imort customer prices on volume discount contracts - now incl of discount percentage options
-----------------------------------------------------------
----------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
SQL - Import customer prices on volume discount contracts
_as_AddUpdateCustomerPrices
-----------------------------------------------------------
----------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),
@Price 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 @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
--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
--END OF CHECK
SELECT @StockID = (SELECT StockLink From StkItem WHERE Code = @StockCode)
--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
--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)
BEGIN
INSERT INTO [_etblVDLnAR] ([iVDID],[iStockID] ,[iStGroupID] ,[iCurrencyID] ,[dEffDate] ,[dExpDate] ,[bUseStockPrc] ,[cEnterInclExcl] ,[bIncremental] ,[bStockAll])
VALUES (@VDID,@StockID,0,0,@StartDate,@EndDate,1,@InclExcl,0,0)
END
ELSE
BEGIN
--UPDATE
UPDATE [_etblVDLnAR] SET
dEffDate = @StartDate
,dExpDate = @EndDate
,[cEnterInclExcl] = @InclExcl
WHERE
iVDID = @VDID
AND
iStockID = @StockID
END
--GET LINE ID OF THE VOLUME DISCOUNT CONTRACT LINE
SELECT @VDLnID = (SELECT IDVDLn FROM _etblVDLnAR WHERE iVDID = @VDID and iStockID = @StockID)
--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,@Price)
END
ELSE
BEGIN
-- UPDATE
UPDATE [_etblVDLnLvlAR] SET
fPriceDisc = @Price
WHERE
iVDLnID = @VDLnID
AND
iLevel = 1
END
END
GO