| currency conversions [message #89749] |
Thu, 15 July 2004 22:52  |
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  |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
