1 PACKAGE hr_currency_pkg AUTHID CURRENT_USER AS
2 /* $Header: pyemucnv.pkh 120.1.12010000.1 2008/07/27 22:31:44 appldev ship $ */
3
4 --
5 -- Package
6 -- hr_currency_pkg
7 --
8 -- Purpose
9 --
10 -- This package will provide a cover for the gl_currency_API for the
11 -- following purposes:
12 -- o Determine exchange rate based on any two currencies, currency_type,
13 -- and conversion date information
14 -- o Convert an amount to a different currency based on any two currencies,
15 -- currency Type, and conversion date information
16 --
17 -- History
18 -- 02-Jun-98 wkerr Created
19 --
20
21 --
22 -- Exceptions
23 --
24 -- User defined exceptions for hr_currency_api:
25 -- o INVALID_CURRENCY - One of the two currencies is invalid.
26 -- o NO_RATE - No rate exists between the two currencies for the
27 -- given date and payroll id.
28 -- o NO_DERIVE_TYPE - No derive type found for the specified currency
29 -- during the specified period.
30 --
31 INVALID_CURRENCY exception;
32 -- -------------------------------------------------------------------------
33 -- |-----------------------< check_rate_type >-----------------------------|
34 -- -------------------------------------------------------------------------
35 --
36 -- Function
37 -- check_rate_type
38 --
39 -- checks that rate type exists in gl_daily_conversion_types
40 -- This function is used within a Fast Formula to validate a
41 -- conversion rate type
42 --
43 -- returns -1 if error or 1 if record exists
44 --
45 -- History
46 -- 02/02/99 wkerr.uk created
47 --
48 -- Arguments
49 -- p_rate_type The rate type to check
50 --
51 Function check_rate_type(
52 p_rate_type VARCHAR2) RETURN NUMBER;
53 --
54 -- -------------------------------------------------------------------------
55 -- |-----------------------< get_rate_type >-------------------------------|
56 -- -------------------------------------------------------------------------
57 -- Function
58 -- get_rate_type
59 --
60 --
61 -- Purpose
62 --
63 -- Returns the rate type given the business group, effective date and
64 -- processing type
65 --
66 -- Returns NULL if no type found
67 --
68 -- Current processing types are:-
69 -- P - Payroll Processing
70 -- R - General HRMS reporting
71 -- I - Business Intelligence System
72 --
73 -- History
74 -- 22/01/99 wkerr.uk Created
75 --
76 -- Argumnents
77 -- p_business_group_id The business group
78 -- p_conversion_date The date for which to return the rate type
79 -- p_processing_type The processing type of which to return the rate
80 --
81 FUNCTION get_rate_type (
82 p_business_group_id NUMBER,
83 p_conversion_date DATE,
84 p_processing_type VARCHAR2 ) RETURN VARCHAR2;
85 --
86 -- -------------------------------------------------------------------------
87 -- |-----------------------< get_rate >------------------------------------|
88 -- -------------------------------------------------------------------------
89 -- Function
90 -- get_rate
91 --
92 -- Purpose
93 -- Returns the rate between the two currencies for a given conversion
94 -- date and rate type.
95 --
96 -- History
97 -- 22-Apr-98 wkerr Created
98 --
99 -- Arguments
100 -- p_from_currency From currency
101 -- p_to_currency To currency
102 -- p_conversion_date Conversion date
103 -- p_rate_type Rate Type
104 --
105 FUNCTION get_rate (
106 p_from_currency VARCHAR2,
107 p_to_currency VARCHAR2,
108 p_conversion_date DATE,
109 p_rate_type VARCHAR2) RETURN NUMBER;
110 -- -------------------------------------------------------------------------
111 -- |-----------------------< get_rate_sql >--------------------------------|
112 -- -------------------------------------------------------------------------
113 --
114 -- Purpose
115 -- Returns the rate between the two currencies for a given conversion
116 -- date and rate type by calling get_rate().
117 --
118 -- Return -1 if the NO_RATE exception is raised in get_rate().
119 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
120 --
121 -- History
122 -- 22-Apr-98 wkerr Created
123 --
124 -- Arguments
125 -- p_from_currency From currency
126 -- p_to_currency To currency
127 -- p_conversion_date Conversion date
128 -- p_rate_type Rate Type
129 --
130 FUNCTION get_rate_sql (
131 p_from_currency VARCHAR2,
132 p_to_currency VARCHAR2,
133 p_conversion_date DATE,
134 p_rate_type VARCHAR2) RETURN NUMBER;
135 -- -------------------------------------------------------------------------
136 -- |------------------------< convert_amount >-----------------------------|
137 -- -------------------------------------------------------------------------
138 --
139 -- Purpose
140 -- Returns the amount converted from the from currency into the
141 -- to currency for a given conversion date and rate type.
142 -- The amount returned is rounded to the precision and minimum
143 -- account unit of the to currency.
144 --
145 -- History
146 -- 22-Apr-98 wkerr Created
147 --
148 -- Arguments
149 -- p_from_currency From currency
150 -- p_to_currency To currency
151 -- p_conversion_date Conversion date
152 -- p_amount Amount to be converted from the from currency
153 -- into the to currency
154 -- p_rate_type Rate Type
155 -- p_round Rounding decimal places
156 --
157 FUNCTION convert_amount (
158 p_from_currency VARCHAR2,
159 p_to_currency VARCHAR2,
160 p_conversion_date DATE,
161 p_amount NUMBER,
162 p_rate_type VARCHAR2 DEFAULT NULL,
163 p_round NUMBER DEFAULT NULL) RETURN NUMBER;
164 -- -------------------------------------------------------------------------
165 -- |-----------------------< convert_amount_sql >--------------------------|
166 -- -------------------------------------------------------------------------
167 --
168 -- Purpose
169 -- Returns the amount converted from the from currency into the
170 -- to currency for a given conversion date and rate type by
171 -- calling convert_amount().
172 -- The amount returned is rounded to the precision and minimum
173 -- account unit of the to currency.
174 --
175 -- Return -1 if the NO_RATE exception is raised in convert_amount().
176 -- -2 if the INVALID_CURRENCY exception is raised in
177 -- convert_amount().
178 --
179 -- History
180 -- 22-Apr-98 wkerr Created
181 --
182 -- Arguments
183 -- p_from_currency From currency
184 -- p_to_currency To currency
185 -- p_conversion_date Conversion date
186 -- p_amount Amount to be converted from the from currency
187 -- into the to currency
188 -- p_rate_type Rate Type
189 -- p_round Round decimal places
190 FUNCTION convert_amount_sql (
191 p_from_currency VARCHAR2,
192 p_to_currency VARCHAR2,
193 p_conversion_date DATE,
194 p_amount NUMBER,
195 p_rate_type VARCHAR2 DEFAULT NULL,
196 p_round NUMBER DEFAULT NULL) RETURN NUMBER;
197 -- -------------------------------------------------------------------------
198 -- |-----------------------< is_ncu_currency >-----------------------------|
199 -- -------------------------------------------------------------------------
200 --
201 -- Purpose
202 -- Returns EMU if currency is a valid NCU code
203 --
204 -- Arguments
205 -- p_currency Currency code to check
206 -- p_date Date that we are checking this currency
207 --
208 -- ----------------------------------------------------------------------------
209 FUNCTION is_ncu_currency
210 (p_currency VARCHAR2
211 ,p_date DATE) RETURN varchar2;
212 --
213 -- -------------------------------------------------------------------------
214 -- |-----------------------< is_ncu_currency_sql >-------------------------|
215 -- -------------------------------------------------------------------------
216 --
217 -- Purpose
218 -- Returns EMU if currency is a valid NCU code
219 --
220 -- Arguments
221 -- p_currency currency
222 -- p_date date to check
223 --
224 -- ----------------------------------------------------------------------------
225 FUNCTION is_ncu_currency_sql
226 (p_currency VARCHAR2
227 ,p_date DATE) RETURN varchar2;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |-----------------------< efc_convert_number_amount >----------------------|
231 -- ----------------------------------------------------------------------------
232 -- {Start Of Comments}
233 --
234 -- Description:
235 -- EFC conversion process money amount conversion function. Used to convert
236 -- NCU currency amounts to the EUR currency. Created for performance reasons.
237 --
238 -- Prerequisites:
239 -- This function should only be used as part of the EFC conversion process.
240 -- The p_round parameter should only be specified when the default number
241 -- of decimal places for the EUR currency should not be used.
242 -- The p_amount parameter should be set with a number datatype.
243 --
244 -- In Parameters:
245 -- Name Reqd Type Description
246 -- p_from_currency Yes varchar2 Currency code which matches
247 -- the p_amount value.
248 -- p_amount Yes number Money value to be converted.
249 -- p_round No number Number of decimal places for
250 -- the converted value.
251 --
252 -- Post Success:
253 -- If the p_from_currency value is not an NCU currency as of sysdate or
254 -- the p_from_currency is already EUR then p_amount will be returned. The
255 -- value for p_round will be ignored.
256 --
257 -- If the p_from_currency is an NCU currency as of sysdate the p_amount
258 -- value will be converted to the EUR currency, using the derived_factor
259 -- defined in the FND_CURRENCIES table. If p_round is undefined or null then
260 -- the returned value will be specified to the number of decimal places for
261 -- the EUR currency as defined in the FND_CURRENCIES table. Otherwise the
262 -- number of decimal places specified by p_round will be used.
263 --
264 -- Post Failure:
265 -- The INVALID_CURRENCY exception will be raised if the p_from_currency
266 -- or 'EUR' currency does not exist in the FND_CURRENCIES table.
267 --
268 -- Access Status:
269 -- Internal Development Use Only.
270 --
271 -- {End Of Comments}
272 --
273 function efc_convert_number_amount
274 (p_from_currency in varchar2
275 ,p_amount in number
276 ,p_round in number default null
277 ) return number;
278 PRAGMA RESTRICT_REFERENCES(efc_convert_number_amount, WNDS);
279 -- ----------------------------------------------------------------------------
280 -- |----------------------< efc_get_derived_factor >--------------------------|
281 -- ----------------------------------------------------------------------------
282 --
283 function efc_get_derived_factor
284 (p_from_currency in varchar2
285 ) return number;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |----------------------< efc_convert_varchar2_amount >---------------------|
289 -- ----------------------------------------------------------------------------
290 -- {Start Of Comments}
291 --
292 -- Description:
293 -- EFC conversion process money amount conversion function. Used to convert
294 -- NCU currency amounts held in varchar2 columns to the EUR currency.
295 -- Created for performance reasons.
296 --
297 -- Prerequisites:
298 -- This function should only be used as part of the EFC conversion process.
299 -- The p_round parameter should only be specified when the default number
300 -- of decimal places for the EUR currency should not be used.
301 -- The p_amount parameter must be set with a value in checkformat's varchar2
302 -- 'MONEY' format.
303 --
304 -- In Parameters:
305 -- Name Reqd Type Description
306 -- p_from_currency Yes varchar2 Currency code which matches
307 -- the p_amount value.
308 -- p_amount Yes varchar2 Money value to be converted.
309 -- p_round No number Number of decimal places for
310 -- the converted value.
311 --
312 -- Post Success:
313 -- Provides the same behaviour as the efc_convert_number_amount function,
314 -- except returns the amount in checkformat's varchar2 'MONEY' format.
315 --
316 -- Post Failure:
317 -- The INVALID_CURRENCY exception will be raised if the p_from_currency
318 -- or 'EUR' currency does not exist in the FND_CURRENCIES table.
319 --
320 -- Access Status:
321 -- Internal Development Use Only.
322 --
323 -- {End Of Comments}
324 --
325 function efc_convert_varchar2_amount
326 (p_from_currency in varchar2
327 ,p_amount in varchar2
328 ,p_round in number default null
329 ) return varchar2;
330 PRAGMA RESTRICT_REFERENCES(efc_convert_varchar2_amount, WNDS);
331 --
332 --
333 -- ----------------------------------------------------------------------------
334 -- |---------------------------< efc_is_ncu_currency >------------------------|
335 -- ----------------------------------------------------------------------------
336 -- {Start Of Comments}
337 --
338 -- Description:
339 -- EFC conversion process function to indicate if a currency is an NCU
340 -- (National Currency Unit) as of sysdate. Where possible this version
341 -- should be used, as it makes use of a cache for performance reasons.
342 --
343 -- The is_ncu_currency function which does not make use of a cache should
344 -- only be used when an WNPS pragma is required or the NCU status needs
345 -- to be detected using a different date to sysdate.
346 --
347 -- Prerequisites:
348 -- This function should only be used as part of the EFC conversion process.
349 --
350 -- In Parameters:
351 -- Name Reqd Type Description
352 -- p_currency Yes varchar2 Currency code to test for
353 -- NCU status.
354 --
355 -- Post Success:
356 -- Returns TRUE if p_currency is an NCU as of sysdate. Otherwise FALSE is
357 -- returned.
358 --
359 -- Post Failure:
360 -- The INVALID_CURRENCY exception will be raised if the p_currency
361 -- currency does not exist in the FND_CURRENCIES table.
362 --
363 -- Access Status:
364 -- Internal Development Use Only.
365 --
366 -- {End Of Comments}
367 --
368 FUNCTION efc_is_ncu_currency
369 (p_currency in varchar2
370 ) return boolean;
371 PRAGMA RESTRICT_REFERENCES(efc_is_ncu_currency,WNDS);
372
373 END hr_currency_pkg;