1 PACKAGE BODY ASO_multi_currency_INT as
2 /* $Header: asoimcxb.pls 120.1 2005/06/29 12:33:46 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_multi_currency_INT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_multi_currency_INT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoimcx.pls';
12
13
14 -- Start of Comments
15 --
16 -- API name : convert_amount_daily
17 -- Type :
18 -- Pre-reqs :
19 -- Function :
20 -- This api takes from and to currencies, conversion date, amount and Max Roll
21 --days. It returns the amount converted into the appropriate currency. The
22 --converted amount is calculated in compliance with the triangulation tule.
23 --
24 -- Parameters :
25 -- IN :
26 -- p_from_currency IN VARCHAR2 -- From currency
27 -- p_to_currency IN VARCHAR2 -- To currency
28 -- p_conversion_date IN DATE -- Conversion date
29 -- p_amount IN NUMBER -- Amount to be converted
30 -- p_max_roll_days IN NUMBER -- Maximum days to roll back for a rate
31 --
32 -- Version :
33 -- History :
34 -- 22-Sep-98 J. Shang Created
35 -- Note :
36 -- Using of p_max_roll_days in daily conversion:
37 --When a rate for the conversion date is undefined, p_max_roll_days will be
38 --used to find an alternitive rate to do the conversion.
39 --1. If it is a positive number, the function will look backward from the
40 -- conversion date for the most recent date on which a rate is defined.
41 --2. If it is a negative number, the function will look backward without any
42 -- date limit to find the most recent date on which a rate is defined.
43 --3. If it is zero,the funtion doesn't look backward. This is the default value
44 -- The above definition follows rules defined in GL_CURRENCY_API.
45
46 -- End of Comments
47
48
49 FUNCTION convert_amount_daily (
50 p_from_currency IN VARCHAR2,
51 p_to_currency IN VARCHAR2,
52 p_conversion_date IN DATE,
53 p_amount IN NUMBER,
54 p_max_roll_days IN NUMBER := 0) RETURN NUMBER IS
55
56 l_converted_amount NUMBER;
57 l_conversion_type VARCHAR2(30);
58 BEGIN
59
60 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
61 -- Check if both currencies are identical
62 IF (p_from_currency = p_to_currency) THEN
63 return(p_amount);
64 END IF;
65
66 -- Decide the value of AS_MC_DAILY_CONVERSION_TYPE is valid
67 l_conversion_type := FND_PROFILE.value('ASO_QUOTE_CONVERSION_TYPE');
68 IF (l_conversion_type IS NULL) THEN
69 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
70 fnd_message.set_name('ASO', 'ASO_QTE_MISSING_CONV_TYPE');
71 FND_MSG_PUB.Add;
72 END IF;
73 return(-1); --raise INVALID_DAILY_CONVERSION_TYPE;
74 END IF;
75
76
77 -- Call GL_CURRENCY_API to do conversion
78 l_converted_amount :=
79 gl_currency_api.convert_closest_amount_sql(p_from_currency,
80 p_to_currency,p_conversion_date,l_conversion_type,0,p_amount,
81 p_max_roll_days);
82
83
84 IF l_converted_amount = -1 THEN
85 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
86 fnd_message.set_name('ASO', 'ASO_NO_RATE');
87 fnd_message.set_token('FROM_CURR', p_from_currency);
88 fnd_message.set_token('TO_CURR', p_to_currency);
89 FND_MSG_PUB.Add;
90 END IF;
91 return(-1);
92 ELSIF l_converted_amount = -2 THEN
93 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
94 fnd_message.set_name('ASO', 'ASO_INVALID_CURRENCY');
95 fnd_message.set_token('FROM_CURR', p_from_currency);
96 fnd_message.set_token('TO_CURR', p_to_currency);
97 FND_MSG_PUB.Add;
98 END IF;
99 return(-1); --raise gl_currency_api.INVALID_CURRENCY;
100 END IF;
101
102 return(l_converted_amount);
103
104 END convert_amount_daily;
105
106
107
108
109 -- Start of Comments
110 -- API name : get_euro_info
111 -- Type :
112 -- Pre-reqs :
113 -- Function :
114 -- This api takes currency code and the effective date,
115 -- it will get the currency
116
117 -- Parameters :
118 -- IN :
119 -- p_currency_code IN VARCHAR2 -- Currency to be checked
120 -- p_effective_date IN DATE -- Effective date
121 -- x_currency_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Type of the currency.
122 -- Euro currency, set to 'EURO'
123 -- Emu currency, set to 'EMU'
124 -- Other currencies,set to 'OTHER'
125
126 -- Invalid currency, set to NULL
127 -- x_conversion_rate OUT NOCOPY /* file.sql.39 change */ NUMBER -- Fixed rate for conversion
128 -- Version :
129 --
130 -- History :
131 -- 03-Nov-98 J. Shang Created
132 -- Note :
133 -- This is temporary solution for OSM, and the procedure follows
134 -- the similar privatre procedure defined in GL.
135
136 -- End of Comments
137 --
138
139 PROCEDURE get_euro_info(p_currency_code IN VARCHAR2,
140 p_effective_date IN DATE,
141 x_currency_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
142 x_conversion_rate OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
143
144 CURSOR l_currency_info_csr(l_currency_code VARCHAR2,l_effective_date DATE) IS
145 SELECT decode(derive_type,
146 'EURO', 'EURO',
147 'EMU', decode( sign( trunc(l_effective_date) -
148 trunc(derive_effective)),
149 -1, 'OTHER', 'EMU'), 'OTHER' ),
150 decode(derive_type, 'EURO', 1,
151 'EMU', derive_factor,
152 'OTHER',-1)
153 FROM FND_CURRENCIES
154 WHERE currency_code = l_currency_code;
155
156 BEGIN
157
158 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
159
160 OPEN l_currency_info_csr(p_currency_code,p_effective_date);
161 FETCH l_currency_info_csr INTO x_currency_type,x_conversion_rate;
162
163 IF l_currency_info_csr%NOTFOUND THEN
164 x_currency_type := NULL;
165 x_conversion_rate := -1;
166 END IF;
167
168 CLOSE l_currency_info_csr;
169 END get_euro_info;
170
171 END ASO_multi_currency_INT;