Dynamics GP Multi-Currency Exchange function to convert to USD

By Tracey BrinkmanMicrosoft Dynamics GP, Multi-Currency, With 0 comments

By Ryan McBee

The SQL Script below will create a function that resides on the Dynamics database.  Just open up SQL Server Management Studio and run the script below.  The function will convert any non USD currency into US Dollars.  This can be useful for companies using many different currencies.

The function takes into account the Exchange Expiration Dates and whether or not the exchange is multiply or divide.  These configuration settings for Mufti-Currency are all within the system settings of Dynamics GP.

USE [DYNAMICS]

GO

/****** Object:  UserDefinedFunction [dbo]. [fnsGetExchangeRate]    Script

Date: 07/11/2012 10:36:13 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

/**********************************************************************

* Func Name:    fnsGetExchangeRate

* Author:                             BA

* Date:                                 08/31/2011

* DB(s):                                DYNAMICS

*

* Purpose:                          Returns Multi-Currency Exchange Rate

*

**********************************************************************

* Date – Changed By       – Changes

**********************************************************************

*

*/

CREATE FUNCTION [dbo].[fnsGetExchangeRate]

(

@Amount NUMERIC(19,5)

, @PostDate DATETIME

, @CurrencyID VARCHAR(10)

)

RETURNS NUMERIC(19,7)

 

AS

BEGIN

DECLARE

@Rate NUMERIC(19,7)

, @RateMethod INT

, @Conversion NUMERIC(19,2)

/*

0 = Multiply

1 = Divide

SELECT RTCLCMTD, *

FROM DYNAMICS.dbo.MC40300

WHERE CURNCYID = ‘Z-US$’ AND EXGTBLID = ‘Z-US-AVG’

select * from mc00100

*/

SELECT TOP 1

@RateMethod = RTCLCMTD

FROM

DYNAMICS.dbo.MC40300

WHERE

EXGTBLID like ‘%AVG’

AND CURNCYID = @CurrencyID

 

SELECT TOP 1

@Rate = XCHGRATE

FROM

DYNAMICS.dbo.MC00100

WHERE

EXGTBLID like ‘%AVG’

AND CURNCYID = @CurrencyID

AND @PostDate BETWEEN EXCHDATE AND EXPNDATE

 

/*************************************************/

 

IF @Rate is null

BEGIN

SELECT TOP 1

@Rate = XCHGRATE

FROM

DYNAMICS.dbo.MC00100

WHERE

EXGTBLID like ‘%AVG’

AND CURNCYID = @CurrencyID

AND EXPNDATE < @PostDate

ORDER BY

EXPNDATE

END

 

/*************************************************/

 

IF @Rate is null

BEGIN

SET @Rate = 1.00000

END

 

/*************************************************/

IF @RateMethod = 0

SET @Conversion = @Amount * @Rate;

ELSE

SET @Conversion = @Amount / @Rate;

RETURN @Conversion;

END

 

 

GO

The function requires you to pass through the amount, document date or GL Post Date and Currency ID.  Below is an example of the script being used against the PurchaseLineItems view which is a canned view on the Dynamics GP company databases.

select case when [Currency ID] <>’Z-US$’ then

DYNAMICS.dbo.[fnsGetExchangeRate]([Subtotal], [Document Date], [Currency ID]) else [Subtotal] end as Subtotal

from PurchaseLineItems