Scribd
Upload
2 views1 page

PostgreSQL Function for Currency Exchange Rate

The document defines a PostgreSQL function named 'get_exrate' that retrieves the exchange rate for a specified company and currency on a given date. It checks the base currency of the company and retrieves the maximum document date for the exchange rates before or on the specified date. The function returns the appropriate exchange rate based on whether the selling rate is 'F' or not, defaulting to 1 if the currency matches the base currency.

Uploaded by

premsridev11
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
2 views1 page

PostgreSQL Function for Currency Exchange Rate

The document defines a PostgreSQL function named 'get_exrate' that retrieves the exchange rate for a specified company and currency on a given date. It checks the base currency of the company and retrieves the maximum document date for the exchange rates before or on the specified date. The function returns the appropriate exchange rate based on whether the selling rate is 'F' or not, defaulting to 1 if the currency matches the base currency.

Uploaded by

premsridev11
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE OR REPLACE FUNCTION adamlive.

get_exrate(p_company character varying,


p_currency character varying, p_sellingrate character varying, p_asondate date)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
l_result numeric(15,6);
bcurr varchar(15);
maxdate date;
BEGIN

select basecurrency into bcurr from company where companyname=p_company;


select max(docdate) into maxdate from mg_exrates where currency=p_currency and
docdate<=p_asondate and active='T';

if p_currency=bcurr then
l_result := 1;
end if;
if p_currency<>bcurr then
select case when p_sellingrate='F' then bexrate else sexrate end into
l_result
from mg_exrates
where docdate =maxdate
and currency=p_currency and active='T';
end if;

return l_result;
END;
$function$
;

You might also like