iVend Database Database : uspGetItemPrice Stored Procedure

Creation Date

7/5/2019 6:02 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 computedDate with reference to which the price has to be computed

DBTimeStamp

4

@pUOMGroupDetailKey

In

VarWChar

50

@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

ArrAccountsReceivable

Table

Defines the on Credit details of all Customers

1

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

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

CusCustomerGroup

Table

Defines the groups in which the customers can be catagorised.

1

GetCompanyDateTime

User Defined Function

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

InvUOMGroup

Table

Stores the value for the UOM group

1

InvUOMGroupDetail

Table

Stores the details for the UOM group

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtExchangeRate

Table

Stores the Exchange Rate values for the currencies defined in application

1

PrcPriceList

Table

Defines the price lists which are integrated through ERP system.

1

PrcPriceMatrix

Table

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

1

PrcSpecialPriceDiscountGroup

Table

Defines a list of all products which fall under a common discount group on the basis of belonging to a common Customer group, Product group or Manufacturer.

1

PrcSpecialPriceDiscountGroupDate

Table

Defines a list of all products which fall under a common discount group on the basis of belonging to a common Customer group, Product group or Manufacturer by Date range

1

PrcSpecialPriceListDate

Table

Defines discounts applicable to products for specified date range.

2

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.

2

PrcUOMPriceMatrix

Table

Defines the values for the UOM price matrix

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

RtlStorePriceList

Table

Stores the price list that will be effective for the store on current date

1

uspGetItemSpecialPrice

Stored Procedure

Determines the price of the Product for the customer specified and other parameters

1

CREATE PROCEDURE [dbo].[uspGetItemPrice]

(

@pProductKey NVARCHAR(50) = '0',

@pCustomerKey   NVARCHAR(50) = '0',

@pQuantity   NUMERIC(20,5) ,

@pStoreKey   NVARCHAR(50) = '0',

@pDebug   BIT = 'FALSE',

@pEffectiveDate DATETIME =NULL,

@pUOMGroupDetailKey NVARCHAR(50) = '0',

@pItemPrice   NUMERIC(20,5) OUTPUT,

@pDiscount   NUMERIC(20,5) OUTPUT,

@pDiscountType INT OUTPUT,

@pDiscountedPrice NUMERIC(20,5) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON

Declare @DiscountType   Int,

  @Discount      NUMERIC(20, 5) ,

  @Expand     Bit,

  @PriceListKey   NVARCHAR(50),

  @Price      NUMERIC(20, 5),

  @lUOMPrice     NUMERIC(20, 5),

  @CustomerPriceListKey   NVARCHAR(50),

  @CustomerGroupKey   NVARCHAR(50),

  @ProductGroupKey   NVARCHAR(50),

  @ManufacturerKey   NVARCHAR(50),

  @IsZeroValue   BIT,

  @IsDiscountable   BIT,

  @WarehouseKey   NVARCHAR(50),

  @lWarehousePrice   NUMERIC(20, 5),

  @lBasicPrice    NUMERIC(20, 5),

  @lCustomerPriceListPrice NUMERIC(20, 5),

  @lWarehousePriceListPrice NUMERIC(20, 5),

  @WarehousePriceListKey NVARCHAR(50),

  @lPricingResolution   INT,

  @ResultKey     NVARCHAR(50),

  @lSpecialPricingApplied BIT,

  @lCurrencyKey   NVARCHAR(50),

  @lCustomerCurrencyKey NVARCHAR(50),

  @lWarehouseCurrencyKey NVARCHAR(50),

  @lUOMKey     NVARCHAR(50),

  @lQuantityPerUOM   DECIMAL(20,5),

  @lBaseUOMKey   NVARCHAR(50),

  @lIsBaseUOM     BIT,

  @lUOMPriceExists   bit,

  @lStoreCurrencyKey   NVARCHAR(50),

  @ERPSystemType   INT, --NAV

  @pSpecialPriceListKey AS NVARCHAR(50),

  @lSubsidiaryKey   AS NVARCHAR(50),

  @lProductDefaultUOMGroupBaseQuantity As Decimal(20,5)

IF @pEffectiveDate IS NULL

SET @pEffectiveDate = dbo.GetCompanyDateTime()

Select @lBaseUOMKey = CASE WHEN ISNULL(A.InventoryUOMKey,'0') = '0' THEN ISNULL(B.BaseUOMKey,'0') else  A.InventoryUOMKey end

from  InvProduct A With(nolock) LEFT OUTER JOIN InvUOMGroup B With(nolock)

ON A.UOMGroupKey = B.UOMGroupKey

Where  A.ProductKey = @pProductKey

Select @lProductDefaultUOMGroupBaseQuantity = A.BaseQty

from  InvUOMGroupDetail A With(nolock) , InvProduct B With(nolock)

Where B.ProductKey = @pProductKey

And B.UOMGroupKey = A.UOMGroupKey

And A.UOMKey = B.InventoryUOMKey

IF @lProductDefaultUOMGroupBaseQuantity IS NULL

SET @lProductDefaultUOMGroupBaseQuantity = 1

Select @lUOMKey = ISNULL(UOMKey,'0'), @lQuantityPerUOM = CASE WHEN B.InventoryUOMKey= ISNULL(UOMKey,'0') THEN 1 ELSE BaseQty / @lProductDefaultUOMGroupBaseQuantity END

from  InvUOMGroupDetail A With(nolock) , InvProduct B With(nolock)

Where B.ProductKey = @pProductKey

And UOMGroupDetailKey = @pUOMGroupDetailKey

IF ISNULL(@lQuantityPerUOM,0) = 0

SET @lQuantityPerUOM = 1

IF @lUOMKey = @lBaseUOMKey

SET @lIsBaseUOM = 1

ELSE

SET @lIsBaseUOM = 0

CREATE TABLE #PricingLogic

(

[InternalKey] [bigint] IDENTITY(1,1) NOT NULL,

[Price] [numeric](20, 5) NULL,

[UOMPrice] [numeric](20, 5) NULL,

[Discount] [numeric](20, 5) NULL,

[DiscountType] [int] NULL,

[AmountAfterDiscount] AS (case when isnull([DiscountType],(0))=(1) OR isnull([DiscountType],(0))=(0) then [Price]-[Discount] else [Price]-([Price]*[Discount])/(100) end),

[AmountAfterUOMDiscount] AS (case when isnull([DiscountType],(0))=(1) OR isnull([DiscountType],(0))=(0) then [UOMPrice]-[Discount] else [UOMPrice]-([UOMPrice]*[Discount])/(100) end),

[Area] [nvarchar](50) NULL,

[Stage] [int] NULL

)

SELECT @DiscountType = 0, @Discount=0, @Price = 0, @lSpecialPricingApplied = 'FALSE'

SELECT @lPricingResolution = PricingResolution, @ERPSystemType = ERPSystemType FROM CfgEnterprise --NAV

SELECT @WarehouseKey = WarehouseKey, @lStoreCurrencyKey = BaseCurrencyKey , @lSubsidiaryKey = SubsidiaryKey

FROM RtlStore With(nolock)

Where StoreKey = @pStoreKey

SELECT @lWarehousePrice = Price * @lQuantityPerUOM

FROM InvInventoryItem With(nolock)

WHERE ProductKey= @pProductKey

AND  WarehouseKey = @WarehouseKey

SELECT @lBasicPrice = BasePrice * @lQuantityPerUOM

FROM InvProduct With(nolock)

WHERE ProductKey= @pProductKey

SELECT @ProductGroupKey = ProductGroupKey,

  @ManufacturerKey = ManufacturerKey,

  @IsZeroValue = IsZeroValue,

  @IsDiscountable = DiscountsAllowed

FROM  InvProduct With(nolock)

WHERE ProductKey =   @pProductKey

IF @pDebug = 'TRUE'

SELECT '@ProductGroupKey' = @ProductGroupKey,

  '@ManufacturerKey' = @ManufacturerKey,

  '@IsZeroValue' = @IsZeroValue,

  '@IsDiscountable' = @IsDiscountable

IF @IsZeroValue = 'TRUE'

BEGIN

  SELECT @pItemPrice = 0, @pDiscount = 0, @pDiscountType = 0, @pDiscountedPrice = 0

  Return

END

--IF THE SYSTEM IS CONFIGURED WITH NAV ERP

IF @ERPSystemType = 4

BEGIN

  IF @pDebug = 'TRUE'

  SELECT @lUOMKey AS UOMKey,

    @lBaseUOMKey AS BaseUOMKey

  EXEC uspGetItemPriceForNAV

    @pProductKey = @pProductKey,

    @pCustomerKey = @pCustomerKey,

    @pQuantity = @pQuantity,

    @pStoreKey = @pStoreKey,

    @pDebug = @pDebug,

    @pEffectiveDate = @pEffectiveDate,

    @pUOMKey = @lUOMKey,

    @pQuantityPerUOM = @lQuantityPerUOM,

    @pItemPrice = @Price out ,

    @pDiscount = @Discount  out ,

    @pDiscountType = @DiscountType out,

    @pDiscountedPrice = @pDiscountedPrice out

    --CALCULATE THE PRICE AFTER THIS

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

    SELECT @Price, @Price, @Discount, @DiscountType, 'NAV PRICING', 0

    SELECT @pItemPrice = Price, @pDiscount= Discount, @pDiscountType = DiscountType, @pDiscountedPrice = AmountAfterDiscount FROM #PricingLogic

    IF @IsDiscountable = 'FALSE'

    BEGIN

    SET @pDiscount = 0 ---INDICATED THAT THE DISCOUNT ON THE ITEM IS 0.

    SET @pDiscountType = 0 ---INDICATES NOT DISCOUNT TYPE IS SPECIFIED.

    END

    IF @pDiscountType = 1 AND @pDiscount > @pItemPrice

    BEGIN

      SET @pDiscount = @pItemPrice

    END

    IF @pDiscount =0

    BEGIN

      SET @pDiscountType = 0

    END

    IF @pDebug = 'TRUE'

    BEGIN

    SELECT '@pItemPrice' = @pItemPrice,

      '@pDiscount' = @pDiscount,

      '@pDiscountType' = @pDiscountType,

      '@IsDiscountable' = @IsDiscountable

    END

    RETURN

END

If @lSubsidiaryKey != '0'

BEGIN

  SELECT @CustomerPriceListKey = T2.PriceListKey ,

    @CustomerGroupKey = T1.CustomerGroupKey

  FROM  CusCustomer T0 With(nolock) Inner Join  CusCustomerGroup T1 With(nolock)

    ON T0.CustomerGroupKey = T1.CustomerGroupKey

    INNER JOIN ArrAccountsReceivable T2

    ON  T0.CustomerKey = T2.CustomerKey

  WHERE T0.CustomerKey = @pCustomerKey

  AND  T2.SubsidiaryKey = @lSubsidiaryKey

END

ELSE

BEGIN

  SELECT @CustomerPriceListKey = T0.PriceListKey ,

    @CustomerGroupKey = T1.CustomerGroupKey

  FROM  CusCustomer T0 With(nolock) Inner Join  CusCustomerGroup T1 With(nolock)

    ON T0.CustomerGroupKey = T1.CustomerGroupKey

  WHERE T0.CustomerKey = @pCustomerKey

END

if @CustomerPriceListKey != '0'

BEGIN

SELECT @lCustomerPriceListPrice = CASE WHEN ISNULL(T2.Price,0) = 0 THEN T1.Price * @lQuantityPerUOM ELSE T2.Price END,

  @lCustomerCurrencyKey = CASE WHEN ISNULL(T2.Price,0) = 0 THEN T1.CurrencyKey ELSE T2.CurrencyKey END

FROM  InvProduct T0 With(nolock)

  LEFT OUTER JOIN PrcPriceMatrix T1 With(nolock)

    ON T0.ProductKey = T1.ProductKey

    And T1.PriceListKey = @CustomerPriceListKey

    and T1.IsDeleted = 0

  LEFT OUTER JOIN PrcUOMPriceMatrix T2 With(nolock)

    ON T0.ProductKey = T2.ProductKey

    AND T2.PriceListKey = @CustomerPriceListKey

    AND T2.UOMKey = @lUOMKey

    AND T2.IsDeleted = 0

  --32256

  JOIN PrcPriceList L With(nolock)

    ON L.PriceListKey = @CustomerPriceListKey

    AND L.IsDeleted = 0

WHERE T0.ProductKey = @pProductKey

END

IF @pDebug = 'TRUE'

SELECT '@CustomerPriceListKey' = @CustomerPriceListKey,

  '@Price' = @Price,

  '@CustomerGroupKey' = @CustomerGroupKey

--IF THE SYSTEM IS CONFIGURED TO USE THE CUSTOMER PRICE LIST THEN ONLY GO IN SPECIAL PRICING BLOCK

IF ISNULL(@CustomerPriceListKey, '0') != '0'

BEGIN

  SELECT @pSpecialPriceListKey = SpecialPriceListKey

    FROM PrcSpecialPriceListMaster With(nolock)

    WHERE ProductKey = @pProductKey

    AND  CustomerKey = @pCustomerKey

    AND  IsDeleted = 'FALSE'

    --Special Price List SAP Changes

    AND  IsActive = 'TRUE'

    AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

    AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

  IF ISNULL(@pSpecialPriceListKey,'0') != '0'

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

  EXEC uspGetItemSpecialPrice

    @pProductKey = @pProductKey,

    @pSpecialPriceListKey = @pSpecialPriceListKey,

    @pQuantity = @pQuantity,

    @pStoreKey = @pStoreKey,

    @pDebug = @pDebug,

    @pEffectiveDate = @pEffectiveDate,

    @pUOMKey = @lUOMKey,

    @pQuantityPerUOM = @lQuantityPerUOM,

    @pIsBaseUOM = @lIsBaseUOM,

    @pBaseUOMKey = @lBaseUOMKey,

    @pItemPrice = @Price out ,

    @pDiscount = @Discount  out ,

    @pDiscountType = @DiscountType out

    --CALCULATE THE PRICE AFTER THIS

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

    SELECT @Price, @Price, @Discount, @DiscountType, 'CUSTOMER SPECIAL PRICING', 1

  END

END --- CHECK END FOR IS DISCOUNTABLE

---IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON PRODUCT GROUP

IF ISNULL(@CustomerPriceListKey, '0') <> '0'

BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount   = Discount

FROM PrcSpecialPriceDiscountGroup With(nolock)

WHERE CustomerKey = @pCustomerKey

AND  ProductGroupKey = @ProductGroupKey

AND  ProductGroupKey <> '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @lCustomerPriceListPrice, @lCustomerPriceListPrice, @Discount, @DiscountType,'CUSTOMER-PRODUCT GROUP'

  END

END

---IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON MANUFACTURER

IF ISNULL(@CustomerPriceListKey, '0') <> '0'

BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount = Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock)

WHERE CustomerKey = @pCustomerKey

AND  ManufacturerKey = @ManufacturerKey

AND  ManufacturerKey <> '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @lCustomerPriceListPrice, @lCustomerPriceListPrice, @Discount, @DiscountType, 'CUSTOMER-MANUFACTURER'

  END

END

---IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON PRODUCT GROUP

IF ISNULL(@CustomerPriceListKey, '0') <> '0'

BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount   = Discount

FROM PrcSpecialPriceDiscountGroup With(nolock)

WHERE CustomerGroupKey = @CustomerGroupKey

AND  ProductGroupKey = @ProductGroupKey

AND  ProductGroupKey <> '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @lCustomerPriceListPrice, @lCustomerPriceListPrice, @Discount, @DiscountType,'CUSTOMER GROUP-PRODUCT GROUP'

  END

END

---IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON MANUFACTURER

IF ISNULL(@CustomerPriceListKey, '0') <> '0'

BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount = Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock)

WHERE CustomerGroupKey = @CustomerGroupKey

AND  ManufacturerKey = @ManufacturerKey

AND  ManufacturerKey <> '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @lCustomerPriceListPrice, @lCustomerPriceListPrice, @Discount, @DiscountType, 'CUSTOMER GROUP-MANUFACTURER'

  END

END

--THIS IS DONE SO THAT IN CASE THE PRICING RESOLUTION IS OTHER THAN NONE THE SYSTEM CALCULATES THE DISCOUNTS BASED ON THE PL ATTACHED TO CUSTOMER

IF @lPricingResolution != 0

BEGIN

  SET @lSpecialPricingApplied = 'FALSE'

END

------IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN BASED ON THE CUSTOMER PRICE LIST FIND THE DISCOUNTS ON THE PRICE LIST

IF @lSpecialPricingApplied = 'FALSE' AND ISNULL(@CustomerPriceListKey, '0') != '0'

BEGIN

  SET @pSpecialPriceListKey = NULL

  SELECT @pSpecialPriceListKey = SpecialPriceListKey

  FROM PrcSpecialPriceListMaster With(nolock)

  WHERE ProductKey = @pProductKey

  AND  PriceListKey = @CustomerPriceListKey

  AND  CustomerKey = '0'

  AND  IsDeleted = 'FALSE'

  --Special Price List SAP Changes

  AND  IsActive = 'TRUE'

  AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

  AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

  IF ISNULL(@pSpecialPriceListKey,'0') != '0'

  BEGIN

    EXEC uspGetItemSpecialPrice

    @pProductKey = @pProductKey,

    @pSpecialPriceListKey = @pSpecialPriceListKey,

    @pQuantity = @pQuantity,

    @pStoreKey = @pStoreKey,

    @pDebug = @pDebug,

    @pEffectiveDate = @pEffectiveDate,

    @pUOMKey = @lUOMKey,

    @pQuantityPerUOM = @lQuantityPerUOM,

    @pIsBaseUOM = @lIsBaseUOM,

    @pBaseUOMKey = @lBaseUOMKey,

    @pItemPrice = @Price out ,

    @pDiscount = @Discount  out ,

    @pDiscountType = @DiscountType out

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

    SELECT @Price, @Price, @Discount, @DiscountType,'CUSTOMER PERIOD VOLUME', 3

  END

END

---------------------------------------------------------------------------------------------------------------------------------------------

---IF NO PRICE LIST IS ATTCHED TO CUSTOMER THEN USE THE WAREHOUSE PRICELIST FOR THE CUSTOMER

SELECT @WarehousePriceListKey = B.PriceListKey,

  @lWarehousePriceListPrice = CASE WHEN F.Price IS NOT NULL THEN F.Price ELSE C.Price * @lQuantityPerUOM END ,

  @lWarehouseCurrencyKey = ISNULL(C.CurrencyKey , 0)

From RtlStore A With(nolock)

INNER JOIN InvWarehouse B With(nolock)

ON A.WarehouseKey = B.WarehouseKey

LEFT OUTER JOIN  PrcPriceMatrix C With(nolock)

ON B.PriceListKey = C.PriceListKey

AND C.ProductKey = @pProductKey

AND C.IsDeleted = 'FALSE'

LEFT OUTER JOIN PrcUOMPriceMatrix F With(nolock)

ON C.ProductKey = F.ProductKey

AND A.PriceListKey = F.PriceListKey

AND F.UOMKey = @lUOMKey

AND F.IsDeleted = 0

--32256

  JOIN PrcPriceList L With(nolock)

ON L.PriceListKey = B.PriceListKey

AND L.IsDeleted = 0

WHERE StoreKey = @pStoreKey

--- NIX - Get the price list and price from effective price list----------------------------------

DECLARE @StorePriceListKey NVARCHAR(50),

  @PricingStoreGroupKey NVARCHAR(50)

SELECT @StorePriceListKey = PriceListKey, @PricingStoreGroupKey = PricingStoreGroupKey FROM RtlStore With(nolock)

WHERE StoreKey = @pStoreKey

IF @StorePriceListKey != '0'

BEGIN

SELECT TOP 1 @WarehousePriceListKey = A.PriceListKey,

  @lWarehousePriceListPrice = CASE WHEN C.Price IS NOT NULL THEN C.Price ELSE B.Price * @lQuantityPerUOM END ,

  @lWarehouseCurrencyKey = ISNULL(B.CurrencyKey , 0)

FROM RtlStorePriceList A With(nolock)

LEFT OUTER JOIN PrcPriceMatrix B With(nolock) ON A.PriceListKey = B.PriceListKey AND B.ProductKey = @pProductKey AND B.IsDeleted = 'FALSE'

LEFT OUTER JOIN PrcUOMPriceMatrix C With(nolock) ON B.ProductKey = C.ProductKey AND A.PriceListKey = C.PriceListKey AND C.UOMKey = @lUOMKey AND C.IsDeleted = 0

WHERE A.SourceType = 0 AND A.SourceKey = @pStoreKey AND A.IsActive = 'TRUE' AND A.IsDeleted = 'FALSE'

AND CONVERT(NVARCHAR, A.FromDate, 112) <= CONVERT(NVARCHAR, @pEffectiveDate, 112)

ORDER BY A.FromDate DESC

END

ELSE IF @PricingStoreGroupKey != '0'

BEGIN

SELECT TOP 1 @WarehousePriceListKey = A.PriceListKey,

  @lWarehousePriceListPrice = CASE WHEN C.Price IS NOT NULL THEN C.Price ELSE B.Price * @lQuantityPerUOM END ,

  @lWarehouseCurrencyKey = ISNULL(B.CurrencyKey , 0)

FROM RtlStorePriceList A With(nolock)

LEFT OUTER JOIN PrcPriceMatrix B With(nolock) ON A.PriceListKey = B.PriceListKey AND B.ProductKey = @pProductKey AND B.IsDeleted = 'FALSE'

LEFT OUTER JOIN PrcUOMPriceMatrix C With(nolock) ON B.ProductKey = C.ProductKey AND A.PriceListKey = C.PriceListKey AND C.UOMKey = @lUOMKey AND C.IsDeleted = 0

WHERE A.SourceType = 1 AND A.SourceKey = @PricingStoreGroupKey AND A.IsActive = 'TRUE' AND A.IsDeleted = 'FALSE'

AND CONVERT(NVARCHAR, A.FromDate, 112) <= CONVERT(NVARCHAR, @pEffectiveDate, 112)

ORDER BY A.FromDate DESC

END

---- NIX -----------------------------------------------------------------------------------------

IF @lPricingResolution !=0 OR ISNULL(@CustomerPriceListKey, '0') = '0'

BEGIN

  SET @pSpecialPriceListKey = NULL

  SELECT @pSpecialPriceListKey = SpecialPriceListKey

  FROM PrcSpecialPriceListMaster With(nolock)

  WHERE ProductKey = @pProductKey

  AND  PriceListKey = @WarehousePriceListKey

  AND  CustomerKey = '0'

  AND  IsDeleted = 'FALSE'

  --Special Price List SAP Changes

  AND  IsActive = 'TRUE'

  AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

  AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

  IF ISNULL(@pSpecialPriceListKey,'0') != '0'

  BEGIN

    EXEC uspGetItemSpecialPrice

      @pProductKey = @pProductKey,

      @pSpecialPriceListKey = @pSpecialPriceListKey,

      @pQuantity = @pQuantity,

      @pStoreKey = @pStoreKey,

      @pDebug = @pDebug,

      @pEffectiveDate = @pEffectiveDate,

      @pUOMKey = @lUOMKey,

      @pQuantityPerUOM = @lQuantityPerUOM,

      @pIsBaseUOM = @lIsBaseUOM,

      @pBaseUOMKey = @lBaseUOMKey,

      @pItemPrice = @Price out ,

      @pDiscount = @Discount  out ,

      @pDiscountType = @DiscountType out

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

    SELECT @Price, @Price, @Discount, @DiscountType,'WAREHOUSE PERIOD VOLUME', 2

  END

END

---THIS WILL CALCULATE THE PRICE FOR THE FOLLOWING CODE

IF ISNULL(@CustomerPriceListKey,'0') != '0'

BEGIN

  SET @Price = ISNULL(@lCustomerPriceListPrice, 0)

  SET @lCurrencyKey = ISNULL(@lCustomerCurrencyKey, '0')

END

ELSE

BEGIN

  SET @Price = ISNULL(@lWarehousePriceListPrice, 0)

  SET @lCurrencyKey = ISNULL(@lWarehouseCurrencyKey, '0')

END

IF ISNULL(@Price,0) = 0

BEGIN

  SET @Price = @lWarehousePrice

END

IF ISNULL(@Price,0) = 0

BEGIN

  SET @Price = @lBasicPrice

END

---IF THE SYSTEM IS NOT CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON PRODUCT GROUP

--IF ISNULL(@CustomerPriceListKey, 0) = 0

--BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount   = Discount

FROM PrcSpecialPriceDiscountGroup With(nolock)

WHERE CustomerKey = '-1'

AND  ProductGroupKey = @ProductGroupKey

AND  ProductGroupKey != '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @Price, @Price, @Discount, @DiscountType,'CUSTOMER-ALL-PRODUCT GROUP'

  END

--END

---IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON MANUFACTURER

--IF ISNULL(@CustomerPriceListKey, 0) = 0

--BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount = Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock)

WHERE CustomerKey = '-1'

AND  ManufacturerKey = @ManufacturerKey

AND  ManufacturerKey != '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @Price, @Price, @Discount, @DiscountType, 'CUSTOMER ALL-MANUFACTURER'

  END

--END

---IF THE SYSTEM IS NOT CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON PRODUCT GROUP

--IF ISNULL(@CustomerPriceListKey, 0) = 0

--BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount   = Discount

FROM PrcSpecialPriceDiscountGroup With(nolock)

WHERE CustomerGroupKey = '-1'

AND  ProductGroupKey = @ProductGroupKey

AND  ProductGroupKey <> '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @Price, @Price, @Discount, @DiscountType,'CUSTOMER GROUP ALL-PRODUCT GROUP'

  END

--END

---IF THE SYSTEM IS CONFIGURED FOR THE CUSTOMER PRICE LIST THEN SEARCH FOR DISOCUNT BASED ON MANUFACTURER

--IF ISNULL(@CustomerPriceListKey, 0) = 0

--BEGIN

SELECT @DiscountType =  DiscountType,

  @Discount = Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock)

WHERE CustomerGroupKey = '-1'

AND  ManufacturerKey = @ManufacturerKey

AND  ManufacturerKey <> '0'

AND  IsDeleted = 'FALSE'

AND  IsActive = 'TRUE'

AND datediff(day, convert(NVARCHAR, @pEffectiveDate, 112), CONVERT(NVARCHAR,ISNULL(ValidFrom,@pEffectiveDate), 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), CONVERT(NVARCHAR, ISNULL(ValidTo, dbo.GetCompanyDateTime()), 112)) >=0

IF @@RowCount > 0

  BEGIN

  SET @lSpecialPricingApplied = 'TRUE'

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

  SELECT @Price, @Price, @Discount, @DiscountType, 'CUSTOMER GROUP-ALL-MANUFACTURER'

  END

--END

----------------FOR DISCOUNT BASED ON CUSTOMER GROUP-----------------------

SELECT TOP 1 @DiscountType =  B.DiscountType,

  @Discount = B.Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock) , PrcSpecialPriceDiscountGroupDate B With(nolock)

WHERE A.PriceDiscountGroupKey = B.PriceDiscountGroupKey

AND  A.ProductGroupKey = '0'

AND  A.CustomerKey = '0'

AND  A.CustomerGroupKey = @CustomerGroupKey

AND  A.ManufacturerKey = '0'

AND  A.IsDeleted = 'FALSE'

AND  B.IsDeleted = 'FALSE'

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), convert(nvarchar, B.FromDate, 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112),ISNULL(convert(nvarchar, ISNULL(B.ToDate, dbo.GetCompanyDateTime()), 112) , convert(nvarchar, @pEffectiveDate, 112))) >=0

And ISNULL(B.SubsidiaryKey,'0') = @lSubsidiaryKey

And  (ISNULL(B.WarehouseKey,'0') = '0' OR ISNULL(B.WarehouseKey,'0') = @WarehouseKey)

ORDER BY B.WarehouseKey Desc

IF @@RowCount > 0

BEGIN

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

  SELECT @Price, @Price, @Discount, @DiscountType, 'CUSTOMER GROUP'

END

---------------------------------------------------------------------------

----------------FOR DISCOUNT BASED ON PRODUCT GROUP------------------------

SELECT TOP 1 @DiscountType =  B.DiscountType,

  @Discount = B.Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock) , PrcSpecialPriceDiscountGroupDate B With(nolock)

WHERE A.PriceDiscountGroupKey = B.PriceDiscountGroupKey

AND  A.ProductGroupKey = @ProductGroupKey

AND  A.CustomerKey = '0'

AND  A.CustomerGroupKey = '0'

AND  A.ManufacturerKey = '0'

AND  A.IsDeleted = 'FALSE'

AND  B.IsDeleted = 'FALSE'

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), convert(nvarchar, B.FromDate, 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112),ISNULL(convert(nvarchar, ISNULL(B.ToDate, dbo.GetCompanyDateTime()), 112) , convert(nvarchar, @pEffectiveDate, 112))) >=0

And ISNULL(B.SubsidiaryKey,'0') = @lSubsidiaryKey

And  (ISNULL(B.WarehouseKey,'0') = '0' OR ISNULL(B.WarehouseKey,'0') = @WarehouseKey)

ORDER BY B.WarehouseKey Desc

IF @@RowCount > 0

BEGIN

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

  SELECT @Price, @Price, @Discount, @DiscountType, 'PRODUCT GROUP'

END

---------------------------------------------------------------------------------------

--------------------------GROUP BASED ON MANUFACTURER-----------------------------------

SELECT TOP 1 @DiscountType =  B.DiscountType,

    @Discount = B.Discount

FROM PrcSpecialPriceDiscountGroup A With(nolock) , PrcSpecialPriceDiscountGroupDate B With(nolock)

WHERE A.PriceDiscountGroupKey = B.PriceDiscountGroupKey

AND  A.ProductGroupKey = '0'

AND  A.CustomerKey = '0'

AND  A.CustomerGroupKey = '0'

AND  A.ManufacturerKey = @ManufacturerKey

AND  A.IsDeleted = 'FALSE'

AND  B.IsDeleted = 'FALSE'

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112), convert(nvarchar, B.FromDate, 112) ) <= 0

AND datediff(day, convert(nvarchar, @pEffectiveDate, 112),ISNULL(convert(nvarchar, ISNULL(B.ToDate, dbo.GetCompanyDateTime()), 112) , convert(nvarchar, @pEffectiveDate, 112))) >=0

And ISNULL(B.SubsidiaryKey,'0') = @lSubsidiaryKey

And  (ISNULL(B.WarehouseKey,'0') = '0' OR ISNULL(B.WarehouseKey,'0') = @WarehouseKey)

ORDER BY B.WarehouseKey Desc

IF @@RowCount > 0

BEGIN

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

  SELECT @Price, @Price, @Discount, @DiscountType, 'MANUFACTURER'

END

-------------------------------------------------------------------------

IF ISNULL(@lCustomerPriceListPrice, 0) != 0

BEGIN

  --CALCULATE THE PRICE AFTER THIS BASED ON THE WAREHOUSE PRICE

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

  SELECT @lCustomerPriceListPrice, @lCustomerPriceListPrice , 0, 0, 'CUSTOMER BASED PL'

END

IF ISNULL(@lCustomerPriceListPrice, 0) = 0 AND @lWarehousePriceListPrice IS NOT NULL

BEGIN

  --CALCULATE THE PRICE AFTER THIS BASED ON THE WAREHOUSE PRICE

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

  SELECT @lWarehousePriceListPrice, @lWarehousePriceListPrice, 0, 0, 'WAREHOUSE BASED PL'

END

IF ISNULL(@lCustomerPriceListPrice, 0) =0 AND @lWarehousePriceListPrice IS NULL AND ISNULL(@lWarehousePrice, 0) != 0

BEGIN

  --CALCULATE THE PRICE AFTER THIS BASED ON THE WAREHOUSE PRICE

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

  SELECT @lWarehousePrice, @lWarehousePrice , 0, 0, 'WAREHOUSE'

END

--CALCULATE THE PRICE AFTER THIS BASED ON THE BASE PRIVE OF AN ITEM

IF ISNULL(@lCustomerPriceListPrice,0) =0 AND ISNULL(@lWarehousePriceListPrice,0) = 0 AND ISNULL(@lWarehousePrice, 0) = 0

BEGIN

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

  SELECT @lBasicPrice, @lBasicPrice , 0, 0, 'BASIC PRICE'

END

IF @lPricingResolution = 0

SELECT TOP 1 @ResultKey = InternalKey

FROM #PricingLogic

WHERE  DiscountType IS NOT NULL

ELSE IF @lPricingResolution = 1

SELECT TOP 1 @ResultKey = InternalKey

FROM #PricingLogic

ORDER BY AmountAfterDiscount ASC

ELSE IF @lPricingResolution = 2

BEGIN

  If Exists (SELECT 1 From #PricingLogic Where Area = 'CUSTOMER SPECIAL PRICING')

  begin

    DELETE FROM #PricingLogic

    Where Area = 'CUSTOMER BASED PL'

  end

  SELECT TOP 1 @ResultKey = InternalKey

  FROM #PricingLogic

  ORDER BY AmountAfterDiscount DESC

END

--GET THE EXCHANGE RATE BASED ON THE CURRENCY

DECLARE @lExchangeRate decimal(20,5)

SET @lExchangeRate = 1

IF @lStoreCurrencyKey ! = @lCurrencyKey

BEGIN

SELECT @lExchangeRate = ISNULL(BuyRate,1)

FROM PmtExchangeRate With(nolock)

WHERE CurrencyKey = @lCurrencyKey

AND Convert(nvarchar,  EffectiveDate,112) = CONVERT(nvarchar, @pEffectiveDate, 112)

END

SELECT @pItemPrice = CONVERT(NUMERIC(20,5), ISNULL(UOMPrice, 0)) * @lExchangeRate,

  --@pItemPrice = CONVERT(NUMERIC(20,5), ISNULL(Price, 0)) * @lExchangeRate,

  @pDiscount = CONVERT(NUMERIC(20,5), ISNULL(Discount, 0)),

  @pDiscountType = IsNull(DiscountType, 0),

  @pDiscountedPrice = AmountAfterDiscount * @lExchangeRate

FROM #PricingLogic

WHERE  InternalKey = @ResultKey

IF @IsDiscountable = 'FALSE'

BEGIN

  SET @pDiscount = 0 ---INDICATED THAT THE DISCOUNT ON THE ITEM IS 0.

  SET @pDiscountType = 0 ---INDICATES NOT DISCOUNT TYPE IS SPECIFIED.

END

IF @pDiscountType = 1 AND @pDiscount > @pItemPrice

BEGIN

  SET @pDiscount = @pItemPrice

END

IF @pDiscount =0

BEGIN

  SET @pDiscountType = 0

END

IF @pDebug = 'TRUE'

BEGIN

SELECT '@pItemPrice' = @pItemPrice,

  '@pDiscount' = @pDiscount,

  '@pDiscountType' = @pDiscountType

SELECT * FROM #PricingLogic

END

END