Hello all. I have identified a problem and engineered a solution. The 'From' price does not calculate the lowest price correctly as it does not allow for negative options. I have rewritten the SQL script which makes this calculation and have included the code below. Unfortunately I cannot attach zip files or sql files to the post.
/****** Object: UserDefinedFunction [dbo].[fnKartrisProduct_GetMinPrice] Script Date: 08/11/2014 14:36:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mohammad
-- Reworked: Craig
-- Create date: 02/10/2008 13:23:42
-- Reworked: 2014/08/11 14:24
-- Description: Calculate minimum price.
-- =============================================
CREATE FUNCTION [dbo].[fnKartrisProduct_GetMinPrice]
(
-- Add the parameters for the function here
@V_ProductID as int
)
RETURNS real
AS
BEGIN
-- Declare the return variable here
DECLARE @Result real;
-- Add the T-SQL statements to compute the return value here
SELECT @Result = Min(V_Price) FROM tblKartrisVersions WHERE V_ProductID = @V_ProductID AND V_Live = 1 AND tblKartrisVersions.V_CustomerGroupID IS NULL;
-- Quantity Discount value
DECLARE @QD_MinPrice as real;
SELECT @QD_MinPrice = Min(QD_Price)
FROM dbo.tblKartrisQuantityDiscounts INNER JOIN tblKartrisVersions
ON tblKartrisQuantityDiscounts.QD_VersionID = tblKartrisVersions.V_ID
WHERE tblKartrisVersions.V_Live = 1 AND tblKartrisVersions.V_ProductID = @V_ProductID AND tblKartrisVersions.V_CustomerGroupID IS NULL;
IF @QD_MinPrice <> 0 AND @QD_MinPrice IS NOT NULL AND @QD_MinPrice < @Result
BEGIN
SET @Result = @QD_MinPrice
END
-- Option Discount value
DECLARE @OD_MinDiscount as real;
SELECT @OD_MinDiscount = MIN(P_OPT_PriceChange)
FROM dbo.tblKartrisProductOptionLink kpol
WHERE kpol.P_OPT_ProductID = @V_ProductID
IF @OD_MinDiscount < 0 AND @OD_MinDiscount IS NOT NULL
BEGIN
-- Case to decimal to work around real calculation errors
SET @Result = CAST(@Result AS DECIMAL(10,5)) + CAST(@OD_MinDiscount AS DECIMAL(10,5))
END
IF @Result IS NULL
BEGIN
SET @Result = 0;
END
-- Return the result of the function
RETURN @Result
END
GO
/****** Object: UserDefinedFunction [dbo].[fnKartrisProduct_GetMinPriceWithCG] Script Date: 08/11/2014 15:28:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mohammad
-- Reworked: Craig
-- Create date: 02/10/2008 13:23:42
-- Reworked: 2014/08/11 14:24
-- Description: Calculate minimum price considering
-- customer group.
-- =============================================
CREATE FUNCTION [dbo].[fnKartrisProduct_GetMinPriceWithCG]
(
-- Add the parameters for the function here
@V_ProductID as int,
@CG_ID as smallint
)
RETURNS real
AS
BEGIN
-- Declare the return variable here
DECLARE @Result real;
IF @CG_ID = 0 OR @CG_ID IS NULL BEGIN
-- No customer group given
SET @Result = dbo.fnKartrisProduct_GetMinPrice(@V_ProductID);
END
ELSE BEGIN
-- Add the T-SQL statements to compute the return value here
SELECT @Result = Min(V_Price) FROM tblKartrisVersions
WHERE V_ProductID = @V_ProductID AND V_Live = 1
AND (tblKartrisVersions.V_CustomerGroupID IS NULL OR tblKartrisVersions.V_CustomerGroupID = @CG_ID);
-- Customer Group Pricing.
DECLARE @CGP_MinPrice as real;
SELECT @CGP_MinPrice = ISNULL(Min([CGP_Price]), @Result)
FROM [dbo].[tblKartrisCustomerGroupPrices]
WHERE [CGP_CustomerGroupID] = @CG_ID
AND [CGP_VersionID] IN (SELECT V_ID FROM tblKartrisVersions WHERE V_ProductID = @V_ProductID);
IF @CGP_MinPrice <> 0 AND @CGP_MinPrice IS NOT NULL AND @CGP_MinPrice < @Result
BEGIN
SET @Result = @CGP_MinPrice
END
-- Quantity Discount Pricing
DECLARE @QD_MinPrice as real;
SELECT @QD_MinPrice = Min(QD_Price)
FROM dbo.tblKartrisQuantityDiscounts INNER JOIN tblKartrisVersions
ON tblKartrisQuantityDiscounts.QD_VersionID = tblKartrisVersions.V_ID
WHERE tblKartrisVersions.V_Live = 1 AND tblKartrisVersions.V_ProductID = @V_ProductID
AND (tblKartrisVersions.V_CustomerGroupID IS NULL OR tblKartrisVersions.V_CustomerGroupID = @CG_ID);
IF @QD_MinPrice <> 0 AND @QD_MinPrice IS NOT NULL AND @QD_MinPrice < @Result
BEGIN
SET @Result = @QD_MinPrice
END
-- Option Pricing
DECLARE @OD_MinDiscount as real;
SELECT @OD_MinDiscount = MIN(P_OPT_PriceChange)
FROM dbo.tblKartrisProductOptionLink kpol
WHERE kpol.P_OPT_ProductID = @V_ProductID
IF @OD_MinDiscount < 0 AND @OD_MinDiscount IS NOT NULL
BEGIN
-- Case to decimal to work around real calculation errors
SET @Result = CAST(@Result AS DECIMAL(10,5)) + CAST(@OD_MinDiscount AS DECIMAL(10,5))
END
END
IF @Result IS NULL
BEGIN
SET @Result = 0;
END
-- Return the result of the function
RETURN @Result
END
GO
We are always willing to help out the community or pitch in to help you fix a problem. However, if you want a complete solution made such as a code module or new feature added you have two options. Either
1) Reach out to the Kartris internal development team at http://www.kartris.com/Contact.aspx.
2) Contact one of the Kartris approved partners at http://www.kartris.com/t-Worldwide-Developers.aspx.
Have fun and good luck coding.