Home » Developer & Programmer » Reports & Discoverer » currency conversions
currency conversions [message #89749] Thu, 15 July 2004 22:52 Go to next message
kris
Messages: 43
Registered: February 2002
Member
Hi All,
Iam facing a problem in converting the currency value.
I have fields curr_code and amount. Curr_code can be USD,INR and so on. I want to generate a report which converts USD amount to INR amount and display the total summary can anyone help.

Thanks in advance
Re: currency conversions [message #89750 is a reply to message #89749] Thu, 15 July 2004 23:18 Go to previous messageGo to next message
Himanshu
Messages: 457
Registered: December 2001
Senior Member
Hi,
Simply write your query with a decode statement as follows:

Select curr_code,decode(curr_code,'USD',nvl(amount,0)*46,amount) amount from MYTable;

Instead of specifying harcoded 46 you may also store the Conversion rates in a DB table & selecte the same in a parameter in before-report-trigger or you may pass the conversion rate from your calling program in a parameter to the report.

HTH
Regards
Himanshu
Re: currency conversions [message #89759 is a reply to message #89750] Mon, 19 July 2004 23:03 Go to previous messageGo to next message
kris
Messages: 43
Registered: February 2002
Member
Hi,
I have to convert the currency based on some condition.
I will give you the data I have.

Table Name : CCTAB

cur_code Amount
INR 10000.00
USD 1000.00

and other currencies and respective amounts.
What I want to do is write a condition and then convert the value of the amount. How should I do that.
Re: currency conversions [message #89760 is a reply to message #89759] Tue, 20 July 2004 00:49 Go to previous message
Himanshu
Messages: 457
Registered: December 2001
Senior Member
Kris,
Add one more Table to your Database to store Conversion rates as Oracle does not know the conversion rates.
You need to have following Columns in your Table:
Create table Curr_conv
(Cur_code Varchar2(50),
Conv_date Date,
Conv_rate Number(10,2));

Add Data into this table as follows:
Cur_code Conv_date Conv_rate
==========================================
USD 01-Jan-2004 45.50
YEN 01-Jan-2004 10.50
MARK 01-Jan-2004 20.02
USD 20-Jul-2004 46.00
YEN 01-Jul-2004 09.20
MARK 01-Jul-2004 22.00

Now make use of following query:
Select a.Cur_code,
decode(a.cur_code,'INR',a.Amount,a.amount*z.Conv_rate)
from CCTAB A,Curr_conv z
Where A.Cur_code=Z.Cur_code
AND Z.Conv_date=(select Max(Conv_date) from
Curr_conv
Where Cur_code=Z.Cur_code);

The reason behind having a Date column in the CURR_CONV table is that you make use of latest rates without losing the old Rates as if you need to refer to Data which was Converted say in Jan 2004 then you would require old rates.

If you have any other queries then do let me know.

HTH
Regards
Himanshu
Previous Topic: How to get reports to read PL/SQL?
Next Topic: I Wish to unsubscribe to Orafaq com
Goto Forum:
  


Current Time: Wed Feb 28 09:55:28 CST 2024