iVend Database Database : uspGetItemPriceForNAV Stored Procedure

Creation Date

7/29/2019 7:18 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductKey

In

Reference key of the Product

VarWChar

50

@pCustomerKey

In

Reference key of the Warehouse

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 computed

DBTimeStamp

4

@pUOMKey

In

VarWChar

50

@pQuantityPerUOM

In

Quantity per Uom

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

@pDiscountedPrice

Out

Final discounted price computed by the procedure

Numeric

9

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

InvProduct

Table

Stores the product related details.

1

InvUOMGroup

Table

Stores the value for the UOM group

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PrcSalesPrice

Table

Stores the Sales price related details. Used in Navision Integration only

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

CREATE PROCEDURE [dbo].[uspGetItemPriceForNAV]

(

@pProductKey NVARCHAR(50) = '0',

@pCustomerKey   NVARCHAR(50) = '0',

@pQuantity   NUMERIC(20,5) ,

@pStoreKey   NVARCHAR(50) = '0',

@pDebug   BIT = 'FALSE',

@pEffectiveDate DATETIME =NULL,

@pUOMKey   NVARCHAR(50) = '0',

@pQuantityPerUOM NUMERIC(20,5),

@pItemPrice   NUMERIC(20,5) OUTPUT,

@pDiscount   NUMERIC(20,5) OUTPUT,

@pDiscountType INT OUTPUT,

@pDiscountedPrice NUMERIC(20,5) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @BasePrice     NUMERIC(20,5)

DECLARE @BaseCurrencyKey   NVARCHAR(50)

DECLARE @CustomerPriceGroupKey NVARCHAR(50)

DECLARE @CustomerDiscountGroupKey NVARCHAR(50)

DECLARE @ProductDiscountGroupKey NVARCHAR(50)

DECLARE @SalesType     BIGINT

--DECLARE @Debug      BIT

DECLARE @IsRecordExistInSalesPrice BIT

DECLARE @BaseUOMKey     NVARCHAR(50)

DECLARE @IsBaseUom     BIT

SET @IsBaseUom     = 0

SET @BasePrice     = 0

SET @BaseCurrencyKey   = 0

SET @CustomerPriceGroupKey = 0

SET @CustomerDiscountGroupKey = 0

SET @ProductDiscountGroupKey = 0

SET @SalesType     = 0

--SET @Debug      = 1

SET @IsRecordExistInSalesPrice = 1

SELECT @BasePrice = ISNULL(BasePrice, 0), @ProductDiscountGroupKey = ProductDiscountGroupKey

FROM InvProduct

WHERE ProductKey = @pProductKey

AND  IsDeleted = 0

SELECT @BaseCurrencyKey = CurrencyKey

FROM PmtCurrency

WHERE IsBaseCurrency = 1

AND  IsDeleted = 0

CREATE TABLE #TempPrice

(

 ID   BIGINT IDENTITY(1, 1),

 Price   NUMERIC(20, 5) NULL,

 QuantityPerUOM DECIMAL(20,5) NULL,

 Discount  NUMERIC(20, 5) NULL,

 SourceType NVARCHAR(100)

)

CREATE TABLE #TempDiscount

(

 ID     BIGINT IDENTITY(1, 1),

 LineDiscountPercent Decimal,

 SourceType   NVARCHAR(100),

 DiscountGroupType NVARCHAR(100)

)

--IF THERE IS NO RECORD IN SALES PRICE TABLE FOR THIS ITEM THEN GET THE BASE PRICE FROM THE PRODUCT

IF NOT EXISTS(SELECT 1 FROM PrcSalesPrice WHERE ProductKey = @pProductKey AND IsDeleted = 0 )

BEGIN

INSERT INTO #TempPrice(Price, SourceType, QuantityPerUOM)

SELECT @BasePrice * @pQuantityPerUOM, 'Base Price', @pQuantityPerUOM

SET @IsRecordExistInSalesPrice = 0

END

/*

 Sales Types For Price

  0 - Customer

  1 - Customer Price Group

  2 - All Customers

 Sales Types For Discount

  0 - Customer

  1 - Customer Discount Group

  2 - All Customers

 DiscountGroupType

  0 - Product

  1 - Product Discount Group

*/

IF @pProductKey <> '0'

BEGIN

SELECT @BaseUOMKey = B.BaseUOMKey FROM InvProduct A INNER JOIN InvUOMGroup B ON A.UOMGroupKey = B.UOMGroupKey

WHERE A.ProductKey = @pProductKey

--IF @BaseUOMKey IS SAME AS @pUOMKey THEN MAKE THE @pUOMKey AS ZERO BEACUASE IN SALES PRICE TABLE BASE UOM KEY IS STORED AS ZERO

IF @pUOMKey = @BaseUOMKey

BEGIN

  SET @pUOMKey = 0

  SET @IsBaseUom = 1

END

--GET THE CustomerPriceGroupKey FROM THE CUSTOMER, IF IT DOES NOT EXIST THEN GET THE ATTCHED CustomerPriceGroupKey FROM STORE

SELECT @CustomerPriceGroupKey = CustomerPriceGroupKey,

  @CustomerDiscountGroupKey = CustomerDiscountGroupKey

FROM CusCustomer

WHERE CustomerKey = @pCustomerKey

AND  IsDeleted = 0

IF @CustomerPriceGroupKey = 0

BEGIN

  SELECT @CustomerPriceGroupKey = CustomerPriceGroupKey FROM RtlStore WHERE StoreKey = @pStoreKey AND IsDeleted = 0

END

IF @pDebug = 1

BEGIN

  SELECT @pCustomerKey AS CustomerKey,

    @CustomerPriceGroupKey AS CustomerPriceGroupKey,

    @CustomerDiscountGroupKey AS CustomerDiscountGroupKey,

    @ProductDiscountGroupKey AS ProductDiscountGroupKey,

    @pProductKey AS ProductKey,

    @pQuantity AS Quantity,

    @pEffectiveDate AS DocDate,

    @pStoreKey AS StoreKey,

    @pUOMKey AS UomKey,

    @BaseCurrencyKey AS BaseCurrencyKey,

    @IsRecordExistInSalesPrice AS IsRecordExistInSalesPrice,

    @pQuantityPerUOM AS QuantityPerUOM,

    @IsBaseUom As IsBaseUom,

    @BasePrice AS BasePrice

END

--*********************************** PRICE *************************************************

--INSERT THE PRICE RECORDS IN TEMP TABLE FOR ALL CUSTOMERS, CUSTOMER AND CUSTOMER PRICE GROUP

--*******************************************************************************************

IF @IsRecordExistInSalesPrice = 1

BEGIN

  --ALL CUSTOMERS

  INSERT INTO #TempPrice(Price, SourceType, QuantityPerUOM)

  SELECT  Price, 'All Customers', @pQuantityPerUOM

  --SELECT  CASE WHEN @IsBaseUom = 0 THEN @pQuantityPerUOM * Price ELSE Price END, 'All Customers', @pQuantityPerUOM

  FROM  PrcSalesPrice

  WHERE  PrcSalesPrice.SalesType = 2

  AND   SalesTypeSourceKey = 0

  AND   ProductKey = @pProductKey

  AND   MinimumQuantity <= @pQuantity

  AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

  AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

  AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

  --AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

  AND   UOMKey = ISNULL(@pUOMKey, 0)

  AND   PrcSalesPrice.IsDeleted = 0

  --CUSTOMER

  INSERT INTO #TempPrice(Price, SourceType, QuantityPerUOM)

  SELECT  Price, 'Customer', @pQuantityPerUOM

  --SELECT  CASE WHEN @IsBaseUom = 0 THEN @pQuantityPerUOM * Price ELSE Price END, 'Customer', @pQuantityPerUOM

  FROM  PrcSalesPrice

  WHERE  SalesTypeSourceKey = @pCustomerKey

  AND   PrcSalesPrice.SalesType = 0

  AND   ProductKey = @pProductKey

  AND   MinimumQuantity <= @pQuantity

  AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

  AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

  AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

  --AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

  AND   UOMKey = ISNULL(@pUOMKey, 0)

  AND   PrcSalesPrice.IsDeleted = 0

  --CUSTOMER PRICE GROUP

  INSERT INTO #TempPrice(Price, SourceType, QuantityPerUOM)

  SELECT  Price, 'Customer Price Group', @pQuantityPerUOM

  --SELECT  CASE WHEN @IsBaseUom = 0 THEN @pQuantityPerUOM * Price ELSE Price END, 'Customer Price Group', @pQuantityPerUOM

  FROM  PrcSalesPrice

  WHERE  SalesTypeSourceKey = @CustomerPriceGroupKey

  AND   PrcSalesPrice.SalesType = 1

  AND   MinimumQuantity <= @pQuantity

  AND   ProductKey = @pProductKey

  AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

  AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

  AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

  --AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

  AND   UOMKey = ISNULL(@pUOMKey, 0)

  AND   IsDeleted = 0

END

--*********************************** ************************* DISCOUNT **********************************************************************

--INSERT THE DISCOUNT RECORDS IN TEMP TABLE FOR ALL CUSTOMERS, CUSTOMER AND CUSTOMER DISCOUNT GROUP IN ITEM AND ITEM DISCOUNT GROUP COMBINATION

--*********************************************************************************************************************************************

--DISCOUNT GROUP TYPE - ITEM AND SALES TYPE - ALL CUSTOMERS

INSERT INTO #TempDiscount(LineDiscountPercent, SourceType, DiscountGroupType)

SELECT  LineDiscountPercent, 'All Customers', 'Product'

FROM  PrcDiscountGroup

WHERE  SalesType = 2

AND   SalesTypeSourceKey = 0

AND   DiscountGroupTypeSourceKey = @pProductKey

AND   DiscountGroupType = 0

AND   MinimumQuantity <= @pQuantity

AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

--AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

AND   UOMKey = ISNULL(@pUOMKey, 0)

AND   IsDeleted = 0

--DISCOUNT GROUP TYPE - ITEM AND SALES TYPE - CUSTOMER

INSERT INTO #TempDiscount(LineDiscountPercent, SourceType, DiscountGroupType)

SELECT  LineDiscountPercent, 'Customer', 'Product'

FROM  PrcDiscountGroup

WHERE  SalesTypeSourceKey = @pCustomerKey

AND   SalesType = 0

AND   DiscountGroupTypeSourceKey = @pProductKey

AND   DiscountGroupType = 0

AND   MinimumQuantity <= @pQuantity

AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

--AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

AND   UOMKey = ISNULL(@pUOMKey, 0)

AND   IsDeleted = 0

--DISCOUNT GROUP TYPE - ITEM AND SALES TYPE - CUSTOMER DISCOUNT GROUP

INSERT INTO #TempDiscount(LineDiscountPercent, SourceType, DiscountGroupType)

SELECT  LineDiscountPercent, 'Customer Discount Group', 'Product'

FROM  PrcDiscountGroup

WHERE  SalesTypeSourceKey = @CustomerDiscountGroupKey

AND   SalesType = 1

AND   DiscountGroupTypeSourceKey = @pProductKey

AND   DiscountGroupType = 0

AND   MinimumQuantity <= @pQuantity

AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

--AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

AND   UOMKey = ISNULL(@pUOMKey, 0)

AND   IsDeleted = 0

--DISCOUNT GROUP TYPE - ITEM DISCOUNT GROUP AND SALES TYPE - ALL CUSTOMERS

INSERT INTO #TempDiscount(LineDiscountPercent, SourceType, DiscountGroupType)

SELECT  LineDiscountPercent, 'All Customers', 'Product Discount Group'

FROM  PrcDiscountGroup

WHERE  SalesType = 2

AND   SalesTypeSourceKey = 0

AND   DiscountGroupTypeSourceKey = @ProductDiscountGroupKey

AND   DiscountGroupType = 1

AND   MinimumQuantity <= @pQuantity

AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

--AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

--AND   UOMKey  = ISNULL(@pUOMKey, 0)

AND   IsDeleted = 0

--DISCOUNT GROUP TYPE - ITEM DISCOUNT GROUP AND SALES TYPE - CUSTOMER

INSERT INTO #TempDiscount(LineDiscountPercent, SourceType, DiscountGroupType)

SELECT  LineDiscountPercent, 'Customer', 'Product Discount Group'

FROM  PrcDiscountGroup

WHERE  SalesTypeSourceKey = @pCustomerKey

AND   SalesType = 0

AND   DiscountGroupTypeSourceKey = @ProductDiscountGroupKey

AND   DiscountGroupType = 1

AND   MinimumQuantity <= @pQuantity

AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

--AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

--AND   UOMKey  = ISNULL(@pUOMKey, 0)

AND   IsDeleted = 0

--DISCOUNT GROUP TYPE - ITEM DISCOUNT GROUP AND SALES TYPE - CUSTOMER DISCOUNT GROUP

INSERT INTO #TempDiscount(LineDiscountPercent, SourceType, DiscountGroupType)

SELECT  LineDiscountPercent, 'Customer Discount Group' , 'Product Discount Group'

FROM  PrcDiscountGroup

WHERE  SalesTypeSourceKey= @CustomerDiscountGroupKey

AND   SalesType = 1

AND   DiscountGroupTypeSourceKey = @ProductDiscountGroupKey

AND   DiscountGroupType = 1

AND   MinimumQuantity <= @pQuantity

AND   CONVERT(NVARCHAR(8), ISNULL(StartDate, @pEffectiveDate), 112) <= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   CONVERT(NVARCHAR(8), ISNULL(EndDate, @pEffectiveDate), 112) >= CONVERT(NVARCHAR(8), @pEffectiveDate, 112)

AND   (CurrencyKey = 0 OR CurrencyKey = @BaseCurrencyKey)

--AND   (UOMKey = 0 OR UOMKey  = @pUOMKey)

--AND   UOMKey  = ISNULL(@pUOMKey, 0)

AND   IsDeleted = 0

END

DECLARE @RecordCount BIGINT

SELECT @RecordCount = COUNT(*) FROM #TempPrice

IF @RecordCount = 0

BEGIN

INSERT INTO #TempPrice(Price, SourceType, QuantityPerUOM)

SELECT @BasePrice * @pQuantityPerUOM, 'Base Price', @pQuantityPerUOM

END

IF @pDebug = 1

BEGIN

SELECT * FROM #TempPrice

SELECT * FROM #TempDiscount

END

--GET THE MINIMUM PRICE OF ALL THE PRICES

SELECT @pItemPrice = ISNULL(Min(Price), 0) FROM #TempPrice

--SELECT @pItemPrice = ISNULL(Min(Price), 0) * @pQuantityPerUOM  FROM #TempPrice

--GET THE MAXIMUM DISCOUNT OF ALL THE DISCOUNTS

SELECT @pDiscount = IsNULL(Max(LineDiscountPercent), 0) FROM #TempDiscount

--DISCOUNTTYPE = 2, INDICATES PERCENTAGE

SELECT @pDiscountType = 2, @pDiscountedPrice = 0

--SELECT @pItemPrice AS Price,

--  @pDiscount AS LineDiscountPercent,

--  @pDiscountType AS DiscountType,

--  @pDiscountedPrice AS DiscountedPrice

DROP TABLE #TempDiscount

DROP TABLE #TempPrice

END