iVend Database Database : uspGetPurchasePriceForNAV Stored Procedure

Creation Date

9/12/2019 6:40 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductKey

In

Reference key of the ProductReference key of the Product

VarWChar

50

@pVendorKey

In

Reference key of the Vendor

VarWChar

50

@pDocumentCurrencyKey

In

VarWChar

50

@pQuantity

In

Quantity of the product being bought

Numeric

9

@pExchangeRate

In

exchange rate

Numeric

9

@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

reference key of the Uom

VarWChar

50

@pItemPrice

Out

Item price computed by the procedure

Numeric

9

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

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

PrcPurchasePrice

Table

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

1

CREATE PROCEDURE [dbo].[uspGetPurchasePriceForNAV]

(

@pProductKey     NVARCHAR(50) = '0',

@pVendorKey       NVARCHAR(50) = '0',

@pDocumentCurrencyKey   NVARCHAR(50) = '0',

@pQuantity       NUMERIC(20,5),

@pExchangeRate      NUMERIC(20,5),

@pDebug       BIT = 'FALSE',

@pEffectiveDate     DATETIME =NULL,

@pUOMKey       NVARCHAR(50) = '0',

@pItemPrice       NUMERIC(20,5) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @BaseUOMKey     NVARCHAR(50)

DECLARE @BaseCurrencyKey   NVARCHAR(50)

--DECLARE @Debug      BIT

DECLARE @IsDocumentInBaseCurrency BIT

SET @pItemPrice     = 0

SET @BaseCurrencyKey   = 0

--SET @Debug      = 1

SET @IsDocumentInBaseCurrency = 1

SELECT @BaseCurrencyKey = CurrencyKey

FROM PmtCurrency

WHERE IsBaseCurrency = 1

AND  IsDeleted = 0

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 PURCHASE PRICE TABLE BASE UOM KEY IS STORED AS ZERO

IF @pUOMKey = @BaseUOMKey

BEGIN

SET @pUOMKey = 0

END

IF @pDocumentCurrencyKey <> @BaseCurrencyKey

SET @IsDocumentInBaseCurrency = 0

IF @pDebug = 1

BEGIN

SELECT @pProductKey AS ProductKey,

  @pVendorKey AS pVendorKey,

  @pDocumentCurrencyKey AS DocumentCurrencyKey,

  @pQuantity AS Quantity,

  @pEffectiveDate AS DocDate,

  @pUOMKey AS UomKey,

  @pDocumentCurrencyKey AS DocumentCurrencyKey,

  @pExchangeRate As ExchangeRate

END

IF @IsDocumentInBaseCurrency = 0 --If PO is not in BaseCurrency

BEGIN

SELECT @pItemPrice = ISNULL(MIN(DirectUnitCost),0)

FROM PrcPurchasePrice

WHERE 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 = @pDocumentCurrencyKey

AND  UOMKey = ISNULL(@pUOMKey, 0)

AND  IsDeleted = 0

AND  VendorKey = @pVendorKey

END

IF @pItemPrice = 0 OR @IsDocumentInBaseCurrency = 1     --If ItemPrice = 0 OR PO is in BaseCurrency

BEGIN

SELECT @pItemPrice = ISNULL(MIN(DirectUnitCost),0)

FROM PrcPurchasePrice

WHERE 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

AND  UOMKey = ISNULL(@pUOMKey, 0)

AND  IsDeleted = 0

AND  VendorKey = @pVendorKey

IF @IsDocumentInBaseCurrency = 0 --If PO is not in BaseCurrency

BEGIN

  SET @pItemPrice = @pItemPrice / @pExchangeRate

END

END

END