iVend Database Database : uspGetItemSpecialPrice Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductKey

In

Reference key of the Product

VarWChar

50

@pSpecialPriceListKey

In

VarWChar

50

@pQuantity

In

Quantity of the product being bought

Numeric

9

@pStoreKey

In

Reference key of the Store

VarWChar

50

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

Boolean

1

@pEffectiveDate

In

Date with reference to which the price has to be computedDate with reference to which the price has to be computed

DBTimeStamp

4

@pUOMKey

In

VarWChar

50

@pIsBaseUOM

In

Boolean

1

@pBaseUOMKey

In

VarWChar

50

@pQuantityPerUOM

In

Numeric

9

@pItemPrice

Out

Item price computed by the procedure

Numeric

9

@pDiscount

Out

Any applicable disount computed by the procedure

Numeric

9

@pDiscountType

Out

Disount type computed by the procedure

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

uspGetItemPrice

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

1

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

2

GetCompanyDateTime

User Defined Function

1

PrcPriceMatrix

Table

Defines the list of products that are associated with each price list. It is also integrated through ERP.

1

PrcSpecialPriceListDate

Table

Defines discounts applicable to products for specified date range.

1

PrcSpecialPriceListMaster

Table

Defines special prices applicable for customer and product combinations.

1

PrcSpecialPriceListQuantity

Table

Defines the volume discount applicable on products. Here we specify the quantity of product on the purchase of which a certain discount would be applied.

1

PrcUOMPriceMatrix

Table

Defines the values for the UOM price matrix

1

/*

declare @pItemPrice  NUMERIC(20,5) ,

@pDiscount  NUMERIC(20,5) ,

@pDiscountType Int,

@pDiscountedPrice NUMERIC(20,5)

Exec uspGetItemPrice 10000000000006, 10000000000004, 1, 10000000000001, 'TRUE', '8/2/2008', @pItemPrice out , @pDiscount out, @pDiscountType out, @pDiscountedPrice out

Select @pItemPrice, @pDiscount, @pDiscountType

*/

CREATE PROCEDURE [dbo].[uspGetItemSpecialPrice]

(

@pProductKey NVARCHAR(50) = '0',

@pSpecialPriceListKey NVARCHAR(50) = '0',

@pQuantity   NUMERIC(20,5) ,

@pStoreKey   NVARCHAR(50) = '0' ,

@pDebug   BIT = 'FALSE',

@pEffectiveDate DATETIME = NULL,

@pUOMKey   NVARCHAR(50) = '0',

@pIsBaseUOM   BIT = 'TRUE',

@pBaseUOMKey NVARCHAR(50),

@pQuantityPerUOM NUMERIC(20,5),

@pItemPrice   NUMERIC(20,5) OUTPUT,

@pDiscount   NUMERIC(20,5) OUTPUT,

@pDiscountType INT OUTPUT

)

AS

BEGIN

SET NOCOUNT ON

Declare @lUOMPrice     NUMERIC(20, 5),

  @lCurrencyKey   NVARCHAR(50),

  @lUOMPriceExists   BIT,

  @lERPSystemType   INT,

  @lIsAutoUpdate   bit

  IF @pEffectiveDate IS NULL

    SET @pEffectiveDate = dbo.GetCompanyDateTime()

  SET @pDiscount = NULL

  SELECT @lUOMPrice= B.Price

  FROM  PrcSpecialPriceListMaster A With(nolock) LEFT OUTER JOIN PrcUOMPriceMatrix B With(nolock)

  ON A.ProductKey = B.ProductKey

  AND  A.BasePriceListKey = CASE WHEN ISNULL(A.BasePriceListKey,'0') = '0' THEN A.BasePriceListKey ELSE B.PriceListKey END

  AND  A.PriceListKey = CASE WHEN ISNULL(A.PriceListKey,'0') = '0' THEN A.PriceListKey ELSE B.PriceListKey END

  AND B.UOMKey = @pUOMKey

  AND B.IsDeleted = 0

  Where A.SpecialPriceListKey = @pSpecialPriceListKey

  Select @lERPSystemType = ERPSystemType from CfgEnterprise

  IF @lERPSystemType = 0 AND ISNULL(@lUOMPrice,0) = 0

    SET @lUOMPriceExists = 'FALSE'

  ELSE IF @lUOMPrice IS NULL

    SET @lUOMPriceExists = 'FALSE'

  ELSE

    SET @lUOMPriceExists = 'TRUE'

    PRINT @pIsBaseUOM

    PRINT @lUOMPriceExists

    IF @pIsBaseUOM = 'TRUE'

      BEGIN

        SELECT TOP 1

          @pDiscountType = CASE WHEN D.DiscountType IS NOT NULL THEN D.DiscountType ELSE CASE WHEN C.DiscountType IS NOT NULL THEN C.DiscountType ELSE CASE WHEN A.DiscountType IS NOT NULL THEN A.DiscountType ELSE 0 END END END,

          @pDiscount = CASE WHEN D.Discount IS NOT NULL THEN D.Discount ELSE CASE WHEN C.Discount IS NOT NULL THEN C.Discount ELSE CASE WHEN A.Discount IS NOT NULL THEN A.Discount ELSE 0 END END END,

          @lCurrencyKey = CASE a.AutoUpdate WHEN 'TRUE' THEN ISNULL(B.CurrencyKey , '0') ELSE '0' END,

          @pDiscountType = CASE WHEN D.Discount IS NOT NULL THEN D.DiscountType ELSE CASE WHEN C.Discount IS NOT NULL THEN C.DiscountType ELSE CASE WHEN A.Discount IS NOT NULL THEN A.DiscountType ELSE 0 END END END,

          @lIsAutoUpdate = CASE WHEN C.Discount IS NOT NULL THEN C.AutoUpdt ELSE A.AutoUpdate END,--A.AutoUpdate

          @pItemPrice = CASE WHEN @lIsAutoUpdate = 'TRUE' THEN ISNULL(B.Price,A.Price) * @pQuantityPerUOM ELSE CASE WHEN D.DiscountType IS NOT NULL THEN D.Price ELSE CASE WHEN C.DiscountType IS NOT NULL THEN C.Price * @pQuantityPerUOM ELSE CASE WHEN A.DiscountType IS NOT NULL THEN A.Price * @pQuantityPerUOM ELSE 0 END END END END

        FROM  PrcSpecialPriceListMaster A With(nolock)

        LEFT OUTER JOIN PrcPriceMatrix B With(nolock)

        ON  A.ProductKey = B.ProductKey

        AND  A.BasePriceListKey = CASE WHEN ISNULL(A.BasePriceListKey, '0') = '0' THEN A.BasePriceListKey ELSE B.PriceListKey END

        AND  A.PriceListKey = CASE WHEN ISNULL(A.PriceListKey, '0') ='0' THEN A.PriceListKey ELSE B.PriceListKey END

        AND  B.IsDeleted = 'FALSE'

        LEFT OUTER JOIN PrcSpecialPriceListDate C With(nolock)

        ON A.SpecialPriceListKey = C.SpecialPriceListKey

        AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR, C.FromDate, 112) ) <= 0

        AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112),ISNULL(CONVERT(NVARCHAR, C.ToDate, 112) , CONVERT(NVARCHAR, @pEffectiveDate, 112))) >=0

        AND C.IsDeleted = 'FALSE'

        LEFT OUTER JOIN PrcSpecialPriceListQuantity D With(nolock)

        ON C.SpecialPriceListDateKey = D.SpecialPriceListDateKey

        AND D.Quantity <= @pQuantity

        AND D.UOMKey = @pUOMKey

        AND D.IsDeleted = 'FALSE'

        WHERE A.ProductKey = @pProductKey

        AND  A.SpecialPriceListKey = @pSpecialPriceListKey

        AND  A.IsDeleted = 'FALSE'

        ORDER BY D.Quantity DESC

        if (@lIsAutoUpdate = 'false')

        begin

        if (@pDiscountType = 2) --indicates percentage discount

          BEGIN

          set @pItemPrice = (@pItemPrice *100 ) / (100 - @pDiscount)

          END

        if (@pDiscountType = 1) --indicates AMOUNT discount

          BEGIN

          set @pItemPrice = @pItemPrice + @pDiscount

          END

        end

      --INSERT INTO #PricingLogic (Price, UOMPrice, Discount, DiscountType, Area, Stage)

      --SELECT @Price, @Price, @Discount, @DiscountType, 'SPL-CUSTOMER', 1

      END

    IF @pIsBaseUOM = 'FALSE' AND @lUOMPriceExists = 'TRUE'

      BEGIN

      SELECT TOP 1 @pDiscountType = CASE WHEN D.DiscountType IS NOT NULL THEN D.DiscountType ELSE 0 END ,

        @pDiscount = CASE WHEN D.Discount IS NOT NULL THEN D.Discount ELSE 0 END ,

        @pItemPrice = @lUOMPrice ,

        @lCurrencyKey = CASE a.AutoUpdate WHEN 'TRUE' THEN ISNULL(B.CurrencyKey , '0') ELSE '0' END,

        @pDiscountType = CASE WHEN D.Discount IS NOT NULL THEN D.DiscountType ELSE 0 END

      FROM  PrcSpecialPriceListMaster A With(nolock)

      LEFT OUTER JOIN PrcPriceMatrix B With(nolock)

        ON  A.ProductKey = B.ProductKey

        AND  A.BasePriceListKey = CASE WHEN ISNULL(A.BasePriceListKey,'0') = '0' THEN A.BasePriceListKey ELSE B.PriceListKey END

        AND  A.PriceListKey = CASE WHEN ISNULL(A.PriceListKey,'0') = '0' THEN A.PriceListKey ELSE B.PriceListKey END

        AND  B.IsDeleted = 'FALSE'

      LEFT OUTER JOIN PrcSpecialPriceListDate C With(nolock)

        ON A.SpecialPriceListKey = C.SpecialPriceListKey

        AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR, C.FromDate, 112) ) <= 0

        AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112),ISNULL(CONVERT(NVARCHAR, C.ToDate, 112) , CONVERT(NVARCHAR, @pEffectiveDate, 112))) >=0

        AND C.IsDeleted = 'FALSE'

      LEFT OUTER JOIN PrcSpecialPriceListQuantity D With(nolock)

        ON C.SpecialPriceListDateKey = D.SpecialPriceListDateKey

        AND D.Quantity <= @pQuantity

        AND D.UOMKey = @pUOMKey

        AND D.IsDeleted = 'FALSE'

      WHERE A.ProductKey = @pProductKey

      AND  A.SpecialPriceListKey = @pSpecialPriceListKey

      AND  A.IsDeleted = 'FALSE'

      ORDER BY D.Quantity DESC

      --INSERT INTO #PricingLogic (Price, UOMPrice, Discount, DiscountType, Area, Stage)

      --SELECT @Price, @Price, @Discount, @DiscountType, 'SPL-CUSTOMER', 1

      END

    IF @pIsBaseUOM = 'FALSE' AND @lUOMPriceExists = 'FALSE'

      BEGIN

      --CHECK IF RECORD IN VOLUME EXISTS

      SELECT TOP 1 @pDiscountType = D.DiscountType,

        @pDiscount = D.Discount,

        @pItemPrice = ISNULL(B.Price, 0) * @pQuantityPerUOM,

        @lCurrencyKey = CASE a.AutoUpdate WHEN 'TRUE' THEN ISNULL(B.CurrencyKey , '0') ELSE '0' END,

        @pDiscountType = D.DiscountType

      FROM  PrcSpecialPriceListMaster A With(nolock)

      LEFT OUTER JOIN PrcPriceMatrix B With(nolock)

        ON  A.ProductKey = B.ProductKey

        AND  A.BasePriceListKey = CASE WHEN ISNULL(A.BasePriceListKey,'0') = '0' THEN A.BasePriceListKey ELSE B.PriceListKey END

        AND  A.PriceListKey = CASE WHEN ISNULL(A.PriceListKey,'0') = '0' THEN A.PriceListKey ELSE B.PriceListKey END

        AND  B.IsDeleted = 'FALSE'

      INNER JOIN PrcSpecialPriceListDate C With(nolock)

        ON A.SpecialPriceListKey = C.SpecialPriceListKey

        AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR, C.FromDate, 112) ) <= 0

        AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112),ISNULL(CONVERT(NVARCHAR, C.ToDate, 112) , CONVERT(NVARCHAR, @pEffectiveDate, 112))) >=0

        AND C.IsDeleted = 'FALSE'

      INNER JOIN PrcSpecialPriceListQuantity D With(nolock)

        ON C.SpecialPriceListDateKey = D.SpecialPriceListDateKey

        AND D.Quantity <= @pQuantity

        AND D.UOMKey = @pUOMKey

        AND D.IsDeleted = 'FALSE'

      WHERE A.ProductKey = @pProductKey

      AND  A.SpecialPriceListKey = @pSpecialPriceListKey

      AND  A.IsDeleted = 'FALSE'

      ORDER BY D.Quantity DESC

      IF @pDiscount IS NULL

        BEGIN

        SELECT TOP 1 @pDiscountType = CASE WHEN D.DiscountType IS NOT NULL THEN D.DiscountType ELSE CASE WHEN C.DiscountType IS NOT NULL THEN C.DiscountType ELSE CASE WHEN A.DiscountType IS NOT NULL THEN A.DiscountType ELSE 0 END END END,

          @pDiscount = CASE WHEN D.Discount IS NOT NULL THEN D.Discount ELSE CASE WHEN C.Discount IS NOT NULL THEN C.Discount ELSE CASE WHEN A.Discount IS NOT NULL THEN A.Discount ELSE 0 END END END,

          --@pItemPrice = CASE A.AutoUpdate WHEN 'TRUE' THEN ISNULL(B.Price,A.Price) ELSE CASE WHEN D.DiscountType IS NOT NULL THEN D.Price ELSE CASE WHEN C.DiscountType IS NOT NULL THEN C.Price * @pQuantityPerUOM ELSE CASE WHEN A.DiscountType IS NOT NULL THEN A.Price * @pQuantityPerUOM ELSE 0 END END END END,

          @lCurrencyKey = CASE a.AutoUpdate WHEN 'TRUE' THEN ISNULL(B.CurrencyKey , '0') ELSE '0' END,

          @pDiscountType = CASE WHEN D.Discount IS NOT NULL THEN D.DiscountType ELSE CASE WHEN C.Discount IS NOT NULL THEN C.DiscountType ELSE CASE WHEN A.Discount IS NOT NULL THEN A.DiscountType ELSE 0 END END END,

          @lIsAutoUpdate = CASE WHEN C.Discount IS NOT NULL THEN C.AutoUpdt ELSE A.AutoUpdate END,--A.AutoUpdate

          @pItemPrice = CASE WHEN @lIsAutoUpdate = 'TRUE' THEN ISNULL(B.Price,A.Price) * @pQuantityPerUOM ELSE CASE WHEN D.DiscountType IS NOT NULL THEN D.Price ELSE CASE WHEN C.DiscountType IS NOT NULL THEN C.Price * @pQuantityPerUOM ELSE CASE WHEN A.DiscountType IS NOT NULL THEN A.Price * @pQuantityPerUOM ELSE 0 END END END END

        FROM  PrcSpecialPriceListMaster A With(nolock)

        LEFT OUTER JOIN PrcPriceMatrix B With(nolock)

          ON  A.ProductKey = B.ProductKey

          AND  A.BasePriceListKey = CASE WHEN ISNULL(A.BasePriceListKey,'0') = '0' THEN A.BasePriceListKey ELSE B.PriceListKey END

          AND  A.PriceListKey = CASE WHEN ISNULL(A.PriceListKey,'0') = '0' THEN A.PriceListKey ELSE B.PriceListKey END

          AND  B.IsDeleted = 'FALSE'

        LEFT OUTER JOIN PrcSpecialPriceListDate C With(nolock)

          ON A.SpecialPriceListKey = C.SpecialPriceListKey

          AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR, C.FromDate, 112) ) <= 0

          AND datediff(day, CONVERT(NVARCHAR, @pEffectiveDate, 112),ISNULL(CONVERT(NVARCHAR, C.ToDate, 112) , CONVERT(NVARCHAR, @pEffectiveDate, 112))) >=0

          AND C.IsDeleted = 'FALSE'

        LEFT OUTER JOIN PrcSpecialPriceListQuantity D With(nolock)

          ON C.SpecialPriceListDateKey = D.SpecialPriceListDateKey

          AND D.Quantity <= @pQuantity * @pQuantityPerUOM

          AND D.UOMKey = @pBaseUOMKey

          AND D.IsDeleted = 'FALSE'

        WHERE A.ProductKey = @pProductKey

        AND  A.SpecialPriceListKey = @pSpecialPriceListKey

        AND  A.IsDeleted = 'FALSE'

        ORDER BY D.Quantity DESC

        if (@lIsAutoUpdate = 'false')

          begin

          if (@pDiscountType = 2) --indicates percentage discount

            BEGIN

            set @pItemPrice = (@pItemPrice *100 ) / (100 - @pDiscount)

            END

          if (@pDiscountType = 1) --indicates AMOUNT discount

            BEGIN

            set @pItemPrice = @pItemPrice + @pDiscount

            END

          end

        --BEGIN

        -- INSERT INTO #PricingLogic (Price, UOMPrice, Discount, DiscountType, Area, Stage)

        -- SELECT @Price, @Price, @Discount, @DiscountType, 'SPL-CUSTOMER', 1

        --END

        END

      END

END