1 Package FII_CURRENCY AS
2 /* $Header: FIICACUS.pls 120.7 2005/10/30 05:07:47 appldev noship $ */
3 -- -------------------------------------------------------------------
4 -- Name: get_global_rate_primary
5 -- Parameters: From_Currency
6 -- Exchange_Date
7 -- Desc: Given the from currency and exchange date, this API will
8 -- call the GL_CURRENCY_API.get_closest_rate_sql API to get
9 -- the currency conversion rate to the primary Global
10 -- Currency.
11 -- Output: Conversion rate, data type: NUMBER
12 -- Returns -1 if no rate exists
13 -- Returns -2 if the From Currency is an invalid currency
14 -- Error: If any sql errors occurs, an exception is raised.
15 -- --------------------------------------------------------------------
16 Function get_global_rate_primary(
17 p_from_currency_code VARCHAR2,
18 p_exchange_date DATE)
19 return NUMBER PARALLEL_ENABLE;
20
21 --PRAGMA RESTRICT_REFERENCES(get_global_rate_primary, WNDS,WNPS,RNPS);
22
23 -- -------------------------------------------------------------------
24 -- Name: get_global_rate_secondary
25 -- Parameters: From_Currency
26 -- Exchange_Date
27 -- Desc: Given the from currency and exchange date, this API will
28 -- call the GL_CURRENCY_API.get_closest_rate_sql API to get
29 -- the currency conversion rate to the secondary Global
30 -- Currency.
31 -- Output: Conversion rate, data type: NUMBER
32 -- Returns -1 if no rate exists
33 -- Returns -2 if the From Currency is an invalid currency
34 -- Error: If any sql errors occurs, an exception is raised.
35 -- --------------------------------------------------------------------
36 Function get_global_rate_secondary(
37 p_from_currency_code VARCHAR2,
38 p_exchange_date DATE)
39 return NUMBER PARALLEL_ENABLE;
40
41 --PRAGMA RESTRICT_REFERENCES(get_global_rate_secondary, WNDS,WNPS,RNPS);
42
43 -- -------------------------------------------------------------------
44 -- Name: convert_global_amt_primary
45 -- Parameters: From_Currency
46 -- Amount
47 -- Exchange_Date
48 -- Desc: Given the from currency, amount in from currency, and exchange
49 -- date, this API will call the
50 -- GL_CURRENCY_API.convert_global_amount_sql to convert the amount
51
52 -- into amount in primary Global Currency.
53 -- Output: Amount in primary Global currency, data type: NUMBER
54 -- Returns -1 if no rate exists
55 -- Returns -2 if the From Currency is an invalid currency
56 -- Error: If any sql errors occurs, an exception is raised.
57 -- --------------------------------------------------------------------
58 Function convert_global_amt_primary(
59 p_from_currency_code VARCHAR2,
60 p_from_amount NUMBER,
61 p_exchange_date DATE)
62 return NUMBER PARALLEL_ENABLE;
63
64 --PRAGMA RESTRICT_REFERENCES(convert_global_amt_primary, WNDS,WNPS,RNPS);
65
66 -- -------------------------------------------------------------------
67 -- Name: convert_global_amt_secondary
68 -- Parameters: From_Currency
69 -- Amount
70 -- Exchange_Date
71 -- Desc: Given the from currency, amount in from currency, and exchange
72 -- date, this API will call the
73 -- GL_CURRENCY_API.convert_global_amount_sql to convert the
74 -- amount into amount in secondary Global Currency.
75 -- Output: Amount in secondary Global currency, data type: NUMBER
76 -- Returns -1 if no rate exists
77 -- Returns -2 if the From Currency is an invalid currency
78 -- Error: If any sql errors occurs, an exception is raised.
79 -- --------------------------------------------------------------------
80 Function convert_global_amt_secondary(
81 p_from_currency_code VARCHAR2,
82 p_from_amount NUMBER,
83 p_exchange_date DATE)
84 return NUMBER PARALLEL_ENABLE;
85
86 --PRAGMA RESTRICT_REFERENCES(convert_global_amt_secondary, WNDS,WNPS,RNPS);
87
88 -- --------------------------------------------------------------
89 -- Name: get_mau_primary
90 -- Desc: This function returns minimum accountable unit of the
91 -- primary global warehouse currency. If a currency does not have
92 -- minimum accountable unit, function returns currency precision.
93 -- If there is no precision, function returns default value of 0.01;
94 -- Function result can never be 0.
95 -- This function should be used in combination with get_rate
96 -- function to convert transaction amounts to amounts in primary
97 -- global warehouse currency:
98 -- <trx amount> * get_rate()
99 -- <global amount> = round (-------------------------) * get_mau()
100 -- get_mau()
101 --
102 -- Input : none
103 -- Output: function returns NULL in case of any exceptions.
104 -- --------------------------------------------------------------
105
106 FUNCTION get_mau_primary RETURN NUMBER PARALLEL_ENABLE;
107
108 --PRAGMA RESTRICT_REFERENCES(get_mau_primary, WNDS,WNPS,RNPS);
109
110 -- --------------------------------------------------------------
111 -- Name: get_mau_secondary
112 -- Desc: This function returns minimum accountable unit of the
113 -- secondary global warehouse currency. If a currency does not have
114 -- minimum accountable unit, function returns currency precision.
115 -- If there is no precision, function returns default value of 0.01;
116 -- Function result can never be 0.
117 -- This function should be used in combination with get_rate
118 -- function to convert transaction amounts to amounts in secondary
119 -- global warehouse currency:
120 -- <trx amount> * get_rate()
121 -- <global amount> = round (-------------------------) * get_mau()
122 -- get_mau()
123 --
124 -- Input : none
125 -- Output: function returns NULL in case of any exceptions.
126 -- --------------------------------------------------------------
127
128 FUNCTION get_mau_secondary RETURN NUMBER PARALLEL_ENABLE;
129
130 --PRAGMA RESTRICT_REFERENCES(get_mau_secondary, WNDS,WNPS,RNPS);
131
132 -- -------------------------------------------------------------------
133 -- Name: get_rate
134 -- Parameters: From Currency
135 -- To Currency
136 -- Exchange Date
137 -- Exchange Rate Type
138 -- Desc: Given the from currency, to currency, exchange date and rate type,
139 -- this API will call the GL_CURRENCY_API.get_closest_rate_sql API to
140 -- get the currency conversion rate.
141 -- Output: Conversion rate, data type: NUMBER
142 -- Returns -1 if no rate exists
143 -- Returns -2 if the From Currency is an invalid currency
144 -- Returns -3 when one of the currency is EUR and the
145 -- exchange date is before Jan 1,1999 and no rate exists
146 -- on Jan 1,1999 between the two currencies.
147 -- Returns -4 for other exceptions.
148 -- Error: If any sql errors occurs, an exception is raised.
149 -- --------------------------------------------------------------------
150 Function get_rate(
151 p_from_currency_code VARCHAR2,
152 p_to_currency_code VARCHAR2,
153 p_exchange_date DATE,
154 p_exchange_rate_type VARCHAR2)
155 return NUMBER PARALLEL_ENABLE;
156
157 ----------------------------
158 -- Rate Conversion API
159 ----------------------------
160
161 -- --------------------------------------------------------------------------
162 -- Name : compare_currency_codes
163 -- Type : Function
164 -- Description : Returns 1 if the given currency codes are the same
165 -- else returns 0;
166 -- This function also takes care of fixed currency for Oracle IT.
167 -- If USD is treated as CD , then if one of the parameters is CD
168 -- and the other is USD then the function returns 1.
169 -----------------------------------------------------------------------------
170 FUNCTION compare_currency_codes(
171 p_currency_code1 IN VARCHAR2,
172 p_currency_code2 IN VARCHAR2) RETURN NUMBER PARALLEL_ENABLE;
173
174 -- -----------------------------------------------------------------------
175 -- Name : get_fc_to_pgc_rate
176 -- Type : Function
177 -- Description : Returns rate to convert amounts from functional currency
178 -- to primary global currency. If the transactional currency
179 -- is the same as primary global currency , functional amts
180 -- are not converted and transactional amounts are used.
181 -- Output : Returns 0 if transactional currency and the primary global
182 -- currency is the same.
183 -- Returns 1 if the functional currency is the same as primary
184 -- global currency
185 -- Returns the rate between the functional currency and the
186 -- primary global currency.
187 -- Exceptions : Returns -1 when no rate exists
188 -- Returns -2 when invalid currency
189 -- Returns -3 when one of the currency is EUR and the
190 -- exchange date is before Jan 1,1999 and no rate exists
191 -- from fc to pgc on Jan 1,1999
192 -- Return -4 for any other exception.
193 -- How the exceptions are handled :
194 -- Other exceptions are handled by get_fc_to_pgc_rate
195 -- No Rate and Invalid Currency are handled in gl_currency_api.get_closest_rate_sql
196 -- When one of the currency is EUR and exchange date is before Jan 1,1999
197 -- and no rate exists from fc to pgc on Jan 1,1999 ,-3 is returned from
198 -- FII_CURRENCY.get_rate.
199 ---------------------------------------------------------------------------
200 Function get_fc_to_pgc_rate(p_tc_code IN VARCHAR2,
201 p_fc_code IN VARCHAR2,
202 p_exchange_date IN DATE) RETURN NUMBER PARALLEL_ENABLE;
203
204
205 -- -----------------------------------------------------------------------
206 -- Name : get_fc_to_sgc_rate
207 -- Type : Function
208 -- Description : Returns rate to convert amounts from functional currency
209 -- to secondary global currency. If the transactional currency
210 -- is the same as secondary global currency , functional amts
211 -- are not converted and transactional amounts are used.
212 -- Output : Returns 0 if transactional currency and the secondary global
213 -- currency is the same.
214 -- Returns 1 if the functional currency is the same as secondary
215 -- global currency or secondary currency is not defined.
216 -- Returns the rate between the functional currency and the
217 -- secondary global currency.
218 -- Exceptions : Returns -1 when no rate exists
219 -- Returns -2 when invalid currency
220 -- Returns -3 when one of the currency is EUR and the
221 -- exchange date is before Jan 1 ,1999 and no rate exists
222 -- from fc to pgc on Jan 1,1999
223 -- Return -4 for any other exception.
224 -- How the exceptions are handled :
225 -- Other exceptions are handled by get_fc_to_pgc_rate
226 -- No Rate and Invalid Currency are handled in gl_currency_api.get_closest_rate_sql
227 -- When one of the currency is EUR and exchange date is before Jan 1,1999
228 -- and no rate exists from fc to pgc on Jan 1,1999 ,-3 is returned from
229 -- FII_CURRENCY.get_rate.
230 ---------------------------------------------------------------------------
231 Function get_fc_to_sgc_rate(p_tc_code IN VARCHAR2,
232 p_fc_code IN VARCHAR2,
233 p_exchange_date IN DATE) RETURN NUMBER PARALLEL_ENABLE;
234
235 -- --------------------------------------------------------------------------------
236 -- Name : get_tc_to_pgc_rate
237 -- Type : Function
238 -- Description : This api is to be used for modules not storing functional currency.
239 -- Returns rate to convert amounts from transactional currency to
240 -- primary global currency.
241 -- Output :
242 -- o If transactional currency and primary global currency is the same
243 -- then return 1. user-defined rate is ignored.
244 -- o If user defined rate is given and functional currency and primary
245 -- global currency is the same then return the user defined rate
246 -- else returns the product of the user defined rate and the retrieved
247 -- rate between the functional currency and the primary global currency.
248 -- o In all other cases, it returns the product of rate between transactional
249 -- currency and functional currency and rate between functional currency
250 -- and primary global currency.
251 -- Exceptions : Returns -2 when either of transactional currency and functional currency
252 -- is invalid.
253 -- Returns -3 when transactional or functional currency is EUR and the exchange date
254 -- is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
255 -- Returns -4 for any other exception
256 -- Returns -5 when no rate exists between transactional currency and functional
257 -- currency.
258 -- Returns -6 when no rate exists between functional currency and primary
259 -- global currency.
260 -- Returns -7 when functional or primary global currency is EUR and the
261 -- exchange date is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
262 -- Returns -8 when treasury rate type is null and p_rate is null and exchange
263 -- rate type is null.
264 -- How the exceptions are handled :
265 -- Other exceptions are handled by get_tc_to_pgc_rate
266 -- Invalid Currency (-2) is handled in gl_currency_api.get_closest_rate_sql
267 -- When one of the currency is EUR and exchange date is before Jan 1,1999
268 -- and no rate exists on Jan 1,1999 then -3 is returned from FII_CURRENCY.get_rate
269 -- -5,-6,-7,-8 are handled in get_tc_pgc_rate
270 ----------------------------------------------------------------------------------------------
271
272 FUNCTION get_tc_to_pgc_rate(p_tc_code IN VARCHAR2,
273 p_exchange_date1 IN DATE,
274 p_exchange_rate_type IN VARCHAR2,
275 p_fc_code IN VARCHAR2,
276 p_exchange_date2 IN DATE,
277 p_rate IN NUMBER DEFAULT NULL) RETURN NUMBER PARALLEL_ENABLE;
278
279 -- --------------------------------------------------------------------------------
280 -- Name : get_tc_to_sgc_rate
281 -- Type : Function
285 -- Output :
282 -- Description : This api is to be used for modules not storing functional currency.
283 -- Returns rate to convert amounts from transactional currency to
284 -- secondary global currency.
286 -- o If transactional currency and secondary global currency is the same
287 -- then return 1. user-defined rate is ignored.
288 -- o If global secondary currency is not defined it returns 1.
289 -- o If user defined rate is given and functional currency and secondary
290 -- global currency is the same then return the user defined rate
291 -- else returns the product of the user defined rate and the retrieved
292 -- rate between the functional currency and the secondary global currency.
293 -- o In all other cases, it returns the product of rate between transactional
294 -- currency and functional currency and rate between functional currency
295 -- and secondary global currency.
296 -- Exceptions : Returns -2 when either of transactional currency and functional currency
297 -- is invalid.
298 -- Returns -3 when transactional or functional currency is EUR and the exchange date
299 -- is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
300 -- Returns -4 for any other exception
301 -- Returns -5 when no rate exists between transactional currency and functional
302 -- currency.
303 -- Returns -6 when no rate exists between functional currency and secondary
304 -- global currency.
305 -- Returns -7 when functional or secondary global currency is EUR and the
306 -- exchange date is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
307 -- Returns -8 when treasury rate type is null and p_rate is null and exchange
308 -- rate type is null.
309 -- How the exceptions are handled :
310 -- Other exceptions are handled by get_tc_to_pgc_rate
311 -- Invalid Currency (-2) is handled in gl_currency_api.get_closest_rate_sql
312 -- When one of the currency is EUR and exchange date is before Jan 1,1999
313 -- and no rate exists on Jan 1,1999 then -3 is returned from FII_CURRENCY.get_rate
314 -- -5,-6,-7,-8 are handled in get_tc_sgc_rate
315 ----------------------------------------------------------------------------------------------
316 FUNCTION get_tc_to_sgc_rate(p_tc_code IN VARCHAR2,
317 p_exchange_date1 IN DATE,
318 p_exchange_rate_type IN VARCHAR2,
319 p_fc_code IN VARCHAR2,
320 p_exchange_date2 IN DATE,
321 p_rate IN NUMBER DEFAULT NULL) RETURN NUMBER PARALLEL_ENABLE;
322
323
324 END FII_CURRENCY;