Dynamics GP Multi-Currency Exchange function to convert to USD
July 11 |
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
