IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'_as_fn_GetCustomerVolumeDiscount') AND type IN ( 'FN' ) )
DROP FUNCTION _as_fn_GetCustomerVolumeDiscount
GO
---Testing
-- SELECT [dbo].[_as_fn_GetCustomerVolumeDiscount] (80 ,11559 ,1, '2018/05/28',1)
----End testing
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_as_fn_GetCustomerVolumeDiscount]
--Parameters:
(@CustomerID INT, --DCLink
@StockID INT,
@Quantity float,
@Date date,
@Exclusive int)
RETURNS FLOAT
WITH EXECUTE AS CALLER
AS
BEGIN
--THIS FUNCTION WILL ONLY GIVE THE UNIT PRICE, REGARDLESS OF TICKED OPTION
--Declares:
DECLARE @DefaultPriceExcl FLOAT
DECLARE @DefaultPriceIncl FLOAT
DECLARE @TaxRate FLOAT
DECLARE @DiscountPercentage FLOAT -- Used for the groups...
DECLARE @Price FLOAT -- Used for the groups..
--SET The declares
SELECT @TaxRate = (SELECT Top 1 TaxRate FROM TAXRATE WHERE idTaxRate = (select iINVTaxTypeID from STDFTBL))
--Check first:
--Check if VD exists for the Customer ID and the Stock ID
IF EXISTS(SELECT * FROM _etblvdar VD
LEFT JOIN _etblvdlnar VDLN ON VD.IDVD = VDLN.iVDID
WHERE VD.iARAPID = @CustomerID
AND VDLN.iStockID = @StockID
AND dEffDate <= @Date
AND dExpDate >= @Date)
BEGIN
RETURN (
SELECT TOP 1
CASE WHEN @Exclusive = 1 --So we want exclusive:
THEN
--Check if we have it Inclusive or Exclusive on the VD:
CASE WHEN cEnterInclExcl = 'I' -- Inclusive:
THEN
(fPriceDisc / (1 + (@TaxRate/100)))
ELSE --Exclusive
fPriceDisc --no need to do anything
END
ELSE --So we want inclusive:
--Check if we have it Inclusive or Exclusive on the VD:
CASE WHEN cEnterInclExcl = 'I' -- Inclusive:
THEN
fPriceDisc --no need to do anything
ELSE --Exclusive
(fPriceDisc * (1 + (@TaxRate/100)))
END
END As fPriceDisc
FROM _etblvdlnlvlar VDLvlln
LEFT JOIN _etblvdlnar VDLvl ON VDLvlln.iVDLnID = VDLvl.IDVDLn
LEFT JOIN _etblvdar VD ON VD.IDVD = VDLvl.iVDID
WHERE VD.iARAPID = @CustomerID AND VDLvl.iStockID = @StockID
AND fQuantity <= @Quantity
AND dEffDate <= @Date
AND dExpDate >= @Date
ORDER BY fQuantity DESC)
END
--Check secondly:
--Get Stock Item Group ID
DECLARE @StockGroupID INT
SET @StockGroupID = (SELECT TOP 1 idGrpTbl FROM GRPTBL
WHERE StGroup IN (SELECT ItemGroup FROM STKITEM WHERE StockLink = @StockID))
--Check if VD exists for the Customer ID and the Stock Group
IF EXISTS(SELECT * FROM _etblvdar VD
LEFT JOIN _etblvdlnar VDLN ON VD.IDVD = VDLN.iVDID
WHERE VD.iARAPID = @CustomerID
AND VDLN.iSTGroupID = @StockGroupID
AND dEffDate <= @Date
AND dExpDate >= @Date)
BEGIN
--RETURN ISNULL((SELECT TOP 1 fPriceDisc FROM _etblvdlnlvlar
--WHERE iVDLnID IN
--(SELECT IDVDLN FROM _etblvdar VD
-- LEFT JOIN _etblvdlnar VDLN ON VD.IDVD = VDLN.iVDID
--WHERE VD.iARAPID = @CustomerID AND VDLN.iSTGroupID = @StockGroupID)
--AND fQuantity <= @Quantity ORDER BY fQuantity DESC),0)
--Now we have the discount percentage:
SELECT @DiscountPercentage =
(SELECT TOP 1 fPriceDisc
FROM _etblvdlnlvlar VDLvlln
LEFT JOIN _etblvdlnar VDLvl ON VDLvlln.iVDLnID = VDLvl.IDVDLn
LEFT JOIN _etblvdar VD ON VD.IDVD = VDLvl.iVDID
WHERE VD.iARAPID = @CustomerID
--Check on the group:
AND VDLvl.iSTGroupID = @StockGroupID
AND fQuantity <= @Quantity
AND dEffDate <= @Date
AND dExpDate >= @Date
ORDER BY fQuantity DESC)
--Now get the default
SELECT @Price = (SELECT TOP 1
CASE WHEN @Exclusive = 1 --So we want exclusive:
THEN
fExclPrice
ELSE --So we want inclusive:
fInclPrice
END As fPriceDisc
FROM Client
LEFT JOIN _etblPriceListPrices PLN ON Client.iARPriceListNameID = PLN.iPriceListNameID
WHERE Client.DCLink = @CustomerID and PLN.iStockID = @StockID)
--Return the price with discount:
RETURN @Price - (@Price * (@DiscountPercentage/100))
END
--Get Customer Group ID
DECLARE @CustomerGroupID INT
SET @CustomerGroupID = (SELECT iClassID FROM CLIENT WHERE DCLink = @CustomerID)
--Check if VD exists for the Customer Group and the Stock ID
IF EXISTS(SELECT * FROM _etblvdar VD
LEFT JOIN _etblvdlnar VDLN ON VD.IDVD = VDLN.iVDID
WHERE VD.iGroupID = @CustomerGroupID
AND VDLN.iStockID = @StockID
AND dEffDate <= @Date
AND dExpDate >= @Date)
BEGIN
RETURN (
SELECT TOP 1
CASE WHEN @Exclusive = 1 --So we want exclusive:
THEN
--Check if we have it Inclusive or Exclusive on the VD:
CASE WHEN cEnterInclExcl = 'I' -- Inclusive:
THEN
(fPriceDisc / (1 + (@TaxRate/100)))
ELSE --Exclusive
fPriceDisc --no need to do anything
END
ELSE --So we want inclusive:
--Check if we have it Inclusive or Exclusive on the VD:
CASE WHEN cEnterInclExcl = 'I' -- Inclusive:
THEN
fPriceDisc --no need to do anything
ELSE --Exclusive
(fPriceDisc * (1 + (@TaxRate/100)))
END
END As fPriceDisc
FROM _etblvdlnlvlar VDLvlln
LEFT JOIN _etblvdlnar VDLvl ON VDLvlln.iVDLnID = VDLvl.IDVDLn
LEFT JOIN _etblvdar VD ON VD.IDVD = VDLvl.iVDID
WHERE VD.iGroupID = @CustomerGroupID --Make sure the Customer group is the same
AND VDLvl.iStockID = @StockID --Make sure the item code is the same
AND fQuantity <= @Quantity
AND dEffDate <= @Date
AND dExpDate >= @Date
ORDER BY fQuantity DESC)
END
--Check if VD exists for the Customer Group and the Stock Group
IF EXISTS(SELECT * FROM _etblvdar VD
LEFT JOIN _etblvdlnar VDLN ON VD.IDVD = VDLN.iVDID
WHERE VD.iGroupID = @CustomerGroupID
AND VDLN.iSTGroupID = @StockGroupID
AND dEffDate <= @Date
AND dExpDate >= @Date)
BEGIN
SELECT @DiscountPercentage =
(SELECT TOP 1 fPriceDisc
FROM _etblvdlnlvlar VDLvlln
LEFT JOIN _etblvdlnar VDLvl ON VDLvlln.iVDLnID = VDLvl.IDVDLn
LEFT JOIN _etblvdar VD ON VD.IDVD = VDLvl.iVDID
WHERE VD.iGroupID = @CustomerGroupID --Make sure the Customer group is the same
--Check on the group:
AND VDLvl.iSTGroupID = @StockGroupID --Make sure the item group is the same
AND fQuantity <= @Quantity
AND dEffDate <= @Date
AND dExpDate >= @Date
ORDER BY fQuantity DESC)
--Now get the default
SELECT @Price = (SELECT TOP 1
CASE WHEN @Exclusive = 1 --So we want exclusive:
THEN
fExclPrice
ELSE --So we want inclusive:
fInclPrice
END As fPriceDisc
FROM Client
LEFT JOIN _etblPriceListPrices PLN ON Client.iARPriceListNameID = PLN.iPriceListNameID
WHERE Client.DCLink = @CustomerID and PLN.iStockID = @StockID)
--Return the price with discount:
RETURN @Price - (@Price * (@DiscountPercentage/100))
END
-- No Volume Discount contract was used. Therefore, first check if the item has a pricelist price.
IF EXISTS(SELECT * FROM Client
LEFT JOIN _etblPriceListPrices PLN ON Client.iARPriceListNameID = PLN.iPriceListNameID
WHERE Client.DCLink = @CustomerID and PLN.iStockID = @StockID)
BEGIN
RETURN
COALESCE((SELECT TOP 1
CASE WHEN @Exclusive = 1 --So we want exclusive:
THEN
fExclPrice
ELSE --So we want inclusive:
fInclPrice
END As fPriceDisc
FROM Client
LEFT JOIN _etblPriceListPrices PLN ON Client.iARPriceListNameID = PLN.iPriceListNameID
WHERE Client.DCLink = @CustomerID and PLN.iStockID = @StockID),0)
END
--If nothing returned any value, return 0
RETURN 0
END
Overview
Content Tools