iVend Database Database : GetUOMPriceMatrix Stored Procedure

Creation Date

3/16/2017 5:24 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@PriceListKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvProduct

Table

Stores the product related details.

1

InvUOM

Table

Master table for the Inv UOM

1

InvUOMGroupDetail

Table

Stores the details for the UOM group

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PrcPriceMatrix

Table

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

1

PrcUOMPriceMatrix

Table

Defines the values for the UOM price matrix

1

CREATE Procedure [dbo].[GetUOMPriceMatrix]

(

@PriceListKey NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

SELECT  A.ProductKey,

  CAST(ISNULL(B.PriceListKey, '0') AS NVARCHAR(50)) 'PriceListKey',

  CAST(ISNULL(A.UOMGroupKey, '0') AS NVARCHAR(50)) 'UOMGroupKey',

  CAST(ISNULL(D.UOMKey, '0') AS NVARCHAR(50)) 'UOMKey',

  CAST(ISNULL(D.Id,'') AS nvarchar(20)) 'UOMId',

  CAST(ISNULL(D.Description ,'') AS nvarchar(100)) 'Description',

  CAST(ISNULL(E.Price, B.Price* (ISNULL(C.BaseQty,1)/ISNULL(C.AltQty,1))) AS NUMERIC(20,5)) 'Price',

  CAST(ISNULL(B.PriceMatrixKey, '0') AS NVARCHAR(50)) 'PriceMatrixKey',

  CAST(ISNULL(E.UOMPriceMatrixKey, '0') AS NVARCHAR(50)) 'UOMPriceMatrixKey',

  CAST(ISNULL(C.AltQty,1) AS NUMERIC(20,5)) 'AltQty',

  CAST(ISNULL(C.BaseQty,1) AS NUMERIC(20,5)) 'BaseQty',

  CAST(ISNULL(P.Id, '0') AS nvarchar(20)) 'CurrencyId',

  CAST(B.Price * (ISNULL(C.BaseQty,1)/ISNULL(C.AltQty,1)) AS NUMERIC(20,5)) 'UOMBasePrice'

  INTO #tempUOMPriceMatrix

  FROM InvProduct A INNER JOIN PrcPriceMatrix B ON A.ProductKey = B.ProductKey AND B.PriceListKey = @PriceListKey AND B.IsDeleted = 0 AND A.IsDeleted = 0

  LEFT OUTER JOIN InvUOMGroupDetail C ON A.UOMGroupKey = C.UOMGroupKey AND C.IsDeleted = 0

  LEFT OUTER JOIN InvUOM D ON C.UOMKey = D.UOMKey

  LEFT OUTER JOIN PmtCurrency P ON B.CurrencyKey = P.CurrencyKey

  LEFT OUTER JOIN PrcUOMPriceMatrix E ON A.ProductKey = E.ProductKey AND D.UOMKey = E.UOMKey AND E.PriceListKey = @PriceListKey AND E.IsDeleted = 0

SELECT * FROM #tempUOMPriceMatrix

END