iVend Database Database : GetPriceListForPriceManager Stored Procedure

Creation Date

9/18/2015 10:23 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@PriceListKey

In

VarWChar

50

@WarehouseKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[GetPriceListForPriceManager]

(

@PriceListKey nvarchar(50),

@WarehouseKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #TempPriceListProductPriceManager

(

 PriceListKey nvarchar(50),

 PriceMatrixKey nvarchar(50),

 UOMPriceMatrixKey nvarchar(50),

 ProductKey nvarchar(50),

 ProductId NVARCHAR(20),

 Description NVARCHAR(200),

 ProductGroupId NVARCHAR(50),

 CurrentCost DECIMAL(20,5),

 CurrentPrice DECIMAL(20,5),

 SuggestedPrice DECIMAL(20,5),

 UOMKey nvarchar(50),

 UOMId NVARCHAR(50),

)

INSERT INTO #TempPriceListProductPriceManager(PriceListKey,PriceMatrixKey,UOMPriceMatrixKey,ProductKey,ProductId,

  Description,ProductGroupId,CurrentCost,CurrentPrice,SuggestedPrice,UOMKey,UOMId)

SELECT DISTINCT PrcPriceList.PriceListKey,PrcPriceMatrix.PriceMatrixKey,'0' AS UOMPriceMatrixKey,PrcPriceMatrix.ProductKey,

    InvProduct.Id, InvProduct.Description,InvProductGroup.Id,0,PrcPriceMatrix.Price As CurrentCost,

    0 As CurrentPrice,ISNULL(InvUOM.UOMKey, '0'),ISNULL(InvUOM.Id,'')

FROM PrcPriceList

INNER JOIN PrcPriceMatrix ON PrcPriceMatrix.PriceListKey = PrcPriceList.PriceListKey AND PrcPriceMatrix.IsDeleted = 0

INNER JOIN InvProduct ON InvProduct.ProductKey = PrcPriceMatrix.ProductKey AND InvProduct.IsDeleted = 0

INNER JOIN InvProductGroup ON InvProductGroup.ProductGroupKey = InvProduct.ProductGroupKey

LEFT OUTER JOIN InvUOMGroupDetail ON InvProduct.UOMGroupKey = InvUOMGroupDetail.UOMGroupKey AND InvUOMGroupDetail.IsDeleted = 0

  LEFT OUTER JOIN InvUOM ON InvUOMGroupDetail.BaseUOMKey = InvUOM.UOMKey

LEFT OUTER JOIN InvProduct AS ParentProduct ON ParentProduct.ProductKey = InvProduct.ParentMartixProductKey

WHERE PrcPriceList.PriceListKey = @PriceListKey AND InvProduct.IsGiftCertificate = 0 AND InvProduct.IsMatrixItem = 0

UPDATE A

SET A.CurrentPrice = B.Price

FROM #TempPriceListProductPriceManager A

INNER JOIN PrcUOMPriceMatrix B ON A.ProductKey = B.ProductKey AND A.UOMKey = B.UOMKey AND B.PriceListKey = A.PriceListKey AND B.IsDeleted = 0

UPDATE A

SET A.CurrentCost = CASE B.CostingMethod WHEN 1 THEN ISNULL((

    SELECT TOP 1 AverageCost

    FROM InvInventoryItem WITH (NOLOCK)

    WHERE ProductKey = B.ProductKey

    AND WarehouseKey = @WarehouseKey--C.WarehouseKey

And ISNULL(AverageCost,0) != 0

),0)

WHEN 2 THEN ISNULL((

    SELECT TOP 1 Cost

    FROM InvProductCost WITH (NOLOCK)

    WHERE ProductKey = B.ProductKey

    AND WarehouseKey IN (0,@WarehouseKey)

    AND IsDeleted = 0

    AND CONVERT(VARCHAR,EffectiveDate,112) <= dbo.GetCompanyDateTime()

    ORDER BY EffectiveDate DESC

),0)

ELSE 0 END

FROM #TempPriceListProductPriceManager A

INNER JOIN InvProduct B ON A.ProductKey = B.ProductKey

SELECT * FROM #TempPriceListProductPriceManager ORDER BY ProductId

END