iVend Database Database : GetExchangeRate User Defined Function

Creation Date

4/23/2019 7:06 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pFromCurrency

In

VarWChar

50

@pToCurrency

In

VarWChar

50

@pBusinessDate

In

DBTimeStamp

4

@RETURN_VALUE

Return Value

Numeric

9

Database Object

Object Type

Description

Dep Level

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PmtExchangeRate

Table

Stores the Exchange Rate values for the currencies defined in application

1

-- =============================================

-- Author:  <Author,,Name>

-- Create date: <Create Date, ,>

-- Description: Gets the exchange rate based on business date for a currency

-- =============================================

CREATE FUNCTION [dbo].[GetExchangeRate]

(

@pFromCurrency nvarchar(50),

@pToCurrency nvarchar(50),

--@pBaseCurrency BIGINT,

@pBusinessDate Datetime

)

RETURNS decimal(20,5)

AS

BEGIN

  ---if my to currency is same as base currency then just reverse and send it back

  DECLARE @lBuyRateToCurrency DECIMAL(20,5),

  @lBuyRateFromCurrency AS DECIMAL(20,5),

  @pBaseCurrency as nvarchar(50)

        if (@pFromCurrency = @pToCurrency)

              return 1

  SELECT @pBaseCurrency = CurrencyKey FROM PmtCurrency Where IsBaseCurrency = 1

  Select TOP 1 @lBuyRateFromCurrency = BuyRate from PmtExchangeRate

  Where Convert(char(8), EffectiveDate,112) = Convert(char(8), @pBusinessDate,112)

  AND IsDeleted = 0

  AND CurrencyKey = @pFromCurrency

  ORDER BY EffectiveDate DESC

  Select TOP 1 @lBuyRateToCurrency = BuyRate from PmtExchangeRate

  Where Convert(char(8), EffectiveDate,112) <= Convert(char(8), @pBusinessDate,112)

  AND IsDeleted = 0

  AND CurrencyKey = @pToCurrency

  ORDER BY EffectiveDate DESC

  if ISNULL(@lBuyRateFromCurrency,0) = 0

  SELECT @lBuyRateFromCurrency = 1

  if ISNULL(@lBuyRateToCurrency,0) = 0

  SELECT @lBuyRateToCurrency = 1

  RETURN @lBuyRateToCurrency/@lBuyRateFromCurrency

END