1 PACKAGE AS_MC_PKG AUTHID CURRENT_USER AS
2 /* $Header: asxmcmcs.pls 115.8 2002/12/13 10:58:09 nkamble ship $ */
3 -- Package
4 -- AS_MC_PKG
5 --
6 -- PURPOSE
7 -- Creates package specification for multi-currency api
8 -- HISTORY
9 -- 14-Sep-1998 J. Shang Created
10 -- 13-Nov-1998 J. Shang Add three new functions: get_euro_info
11 -- format_amount
12 -- unformat_amount
13 --
14 -- 02-Dec-1999 CHSIN Change G_NEG_MAX from -9.99E125 to -9.99E120 for Prj. Mona Lisa
15 -- solve UI calling get_opportunity NumberBytesToBigDecimal exception problem
16 --
17 -- Exceptions
18 --
19 -- User defined exceptions for as_mc_pkg:
20 --
21 -- INVALID_DAILY_CONVERSION_TYPE - the profile AS_MC_DAILY_CONVERSION_TYPE is not set
22 -- INVALID_FORECAST_CALENDAR - the profile AS_MC_FORECAST_CALENDAR is not set
23 -- INVALID_PERIOD - the period is invalid
24
25 INVALID_DAILY_CONVERSION_TYPE EXCEPTION;
26 INVALID_FORECAST_CALENDAR EXCEPTION;
27 INVALID_PERIOD EXCEPTION;
28
29 G_NEG_MAX CONSTANT NUMBER := -9.99E120;
30
31 -- Start of Comments
32 --
33 -- API name : convert_amount_daily
34 -- Type :
35 -- Pre-reqs :
36 -- Function :
37 -- This api takes from and to currencies, conversion date, amount and Max Roll
38 -- days. It returns the amount converted into the appropriate currency. The
39 -- converted amount is calculated in compliance with the triangulation tule.
40 --
41 -- Parameters :
42 -- IN :
43 -- p_from_currency IN VARCHAR2 Required -- From currency
44 -- p_to_currency IN VARCHAR2 Required -- To currency
45 -- p_conversion_date IN DATE Required -- Conversion date
46 -- p_amount IN NUMBER Required -- Amount to be converted
47 -- p_max_roll_days IN NUMBER Optional -- Maximum days to roll back for a rate
48 --
49 -- Version :
50 --
51 -- HISTORY
52 -- 14-Sep-1998 J. Shang Created
53 -- Note :
54 -- In this function, a cursor is defined to search a closest rate. The query
55 -- follows the searching rule defined in GL_CURRENCY_API. So, if the rule is
56 -- changed in GL_CURRENCY_API, this cursor has to be changed also.
57 --End of Comments
58
59 FUNCTION convert_amount_daily (
60 p_from_currency IN VARCHAR2,
61 p_to_currency IN VARCHAR2,
62 p_conversion_date IN DATE,
63 p_amount IN NUMBER,
64 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER;
65
66 -- Start of Comments
67 --
68 -- API name : convert_amount_daily_sql
69 -- Type :
70 -- Pre-reqs :
71 -- Function :
72 -- This api takes from and to currencies, conversion date, amount and Max Roll
73 -- days. It returns the amount converted into the appropriate currency. The
74 -- converted amount is calculated in compliance with the triangulation tule.
75 -- This api has the same function as the above api but is used in SQL statement.
76 --
77 -- Parameters :
78 -- IN :
79 -- p_from_currency IN VARCHAR2 Required -- From currency
80 -- p_to_currency IN VARCHAR2 Required -- To currency
81 -- p_conversion_date IN DATE Required -- Conversion date
82 -- p_amount IN NUMBER Required -- Amount to be converted
83 -- p_max_roll_days IN NUMBER Optional -- Maximum days to roll back for a rate
84 --
85 -- Version :
86 --
87 -- HISTORY
88 -- 14-Sep-1998 J. Shang Created
89 -- Note :
90 -- In this function, a cursor is defined to search a closest rate. The query
91 -- follows the searching rule defined in GL_CURRENCY_API. So, if the rule is
92 -- changed in GL_CURRENCY_API, this cursor has to be changed also.
93 --
94 -- End of Comments
95
96 FUNCTION convert_amount_daily_sql (
97 p_from_currency IN VARCHAR2,
98 p_to_currency IN VARCHAR2,
99 p_conversion_date IN DATE,
100 p_amount IN NUMBER,
101 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER;
102 PRAGMA RESTRICT_REFERENCES(convert_amount_daily_sql, WNDS,WNPS);
103
104 -- Start of Comments
105 --
106 -- API name : convert_amount_daily_sql
107 -- Type :
108 -- Pre-reqs :
109 -- Function :
110 -- This api takes from and to currencies, conversion date, amount, Max Roll
111 -- days and conversion type. It returns the amount converted into the appropriate
112 -- currency. The converted amount is calculated in compliance with the triangulation
113 -- tule.This api has the same function as the above api but is used in SQL statement.
114 --
115 -- Parameters :
116 -- IN :
117 -- p_from_currency IN VARCHAR2 Required -- From currency
118 -- p_to_currency IN VARCHAR2 Required -- To currency
119 -- p_conversion_date IN DATE Required -- Conversion date
120 -- p_amount IN NUMBER Required -- Amount to be converted
121 -- p_max_roll_days IN NUMBER Optional -- Maximum days to roll back for a rate
122 -- p_conversion_type IN VARCHAR2 Required -- Conversion Type
123 --
124 -- Version :
125 --
126 -- HISTORY
127 -- 14-MAR-00 XDING Created
128 -- Note :
129 -- In this function, a cursor is defined to search a closest rate. The query
130 -- follows the searching rule defined in GL_CURRENCY_API. So, if the rule is
131 -- changed in GL_CURRENCY_API, this cursor has to be changed also.
132 --
133 -- End of Comments
134
135 FUNCTION convert_amount_daily_sql (
136 p_from_currency IN VARCHAR2,
137 p_to_currency IN VARCHAR2,
138 p_conversion_date IN DATE,
139 p_amount IN NUMBER,
140 p_max_roll_days IN NUMBER DEFAULT 0,
141 p_conversion_type IN VARCHAR2 ) RETURN NUMBER;
142 PRAGMA RESTRICT_REFERENCES(convert_amount_daily_sql, WNDS,WNPS);
143
144 -- Start of Comments
145 --
146 -- API name : convert_amount_period
147 -- Type :
148 -- Pre-reqs :
149 -- Function :
150 -- This api takes from and to currencies, conversion period and amount
151 -- It returns the amount converted into the appropriate currency based on
152 -- pseudo period. The converted amount is calculated in compliance with
153 -- the triangulation tule.
154 --
155 -- Parameters :
156 -- IN :
157 -- p_from_currency IN VARCHAR2 Required -- From currency
158 -- p_to_currency IN VARCHAR2 Required -- To currency
159 -- p_conversion_period IN VARCHAR2 Required -- Conversion period
160 -- p_amount IN NUMBER Required -- Amount to be converted
161 --
162 -- Version :
163 --
164 -- HISTORY
165 -- 14-Sep-1998 J. Shang Created
166 --
167 -- End of Comments
168
169 FUNCTION convert_amount_period (
170 p_from_currency IN VARCHAR2,
171 p_to_currency IN VARCHAR2,
172 p_conversion_period IN VARCHAR2,
173 p_amount IN NUMBER) RETURN NUMBER;
174
175 -- Start of Comments
176 --
177 -- API name : convert_amount_period_sql
178 -- Type :
179 -- Pre-reqs :
180 -- Function :
181 -- This api takes from and to currencies, conversion period and amount
182 -- It returns the amount converted into the appropriate currency based on
183 -- pseudo period. The converted amount is calculated in compliance with
184 -- the triangulation tule. This api has the same function as the above
185 -- api but is used in SQL statement.
186 --
187 -- Parameters :
188 -- IN :
189 -- p_from_currency IN VARCHAR2 Required -- From currency
190 -- p_to_currency IN VARCHAR2 Required -- To currency
191 -- p_conversion_period IN VARCHAR2 Required -- Conversion period
192 -- p_amount IN NUMBER Required -- Amount to be converted
193 --
194 -- Version :
195 --
196 -- HISTORY
197 -- 14-Sep-1998 J. Shang Created
198 --
199 -- End of Comments
200
201 FUNCTION convert_amount_period_sql (
202 p_from_currency IN VARCHAR2,
203 p_to_currency IN VARCHAR2,
204 p_conversion_period IN VARCHAR2,
205 p_amount IN NUMBER) RETURN NUMBER;
206 PRAGMA RESTRICT_REFERENCES(convert_amount_period_sql, WNDS, WNPS);
207
208 -- Start of Comments
209 --
210 -- API name : convert_amount_period_sql
211 -- Type :
212 -- Pre-reqs :
213 -- Function :
214 -- This api takes from and to currencies, conversion date, type and amount
215 -- It returns the amount converted into the appropriate currency based on
216 -- pseudo period. The converted amount is calculated in compliance with
217 -- the triangulation tule. This api has the same function as the above
218 -- api but is used in SQL statement.
219 --
220 -- Parameters :
221 -- IN :
222 -- p_from_currency IN VARCHAR2 Required -- From currency
223 -- p_to_currency IN VARCHAR2 Required -- To currency
224 -- p_conversion_date IN DATE Required -- Conversion date
225 -- p_conversion_type IN VARCHAR2 Required -- Conversion type
226 -- p_amount IN NUMBER Required -- Amount to be converted
227 --
228 -- Version :
229 --
230 -- HISTORY
231 -- 14-MAR-00 XDING Created
232 --
233 -- End of Comments
234
235 FUNCTION convert_amount_period_sql (
236 p_from_currency IN VARCHAR2,
237 p_to_currency IN VARCHAR2,
238 p_conversion_date IN DATE,
239 p_conversion_type IN VARCHAR2,
240 p_amount IN NUMBER) RETURN NUMBER;
241 PRAGMA RESTRICT_REFERENCES(convert_amount_period_sql, WNDS, WNPS);
242
243 -- Start of Comments
244 --
245 -- API name : get_daily_rate
246 -- Type :
247 -- Pre-reqs :
248 -- Function :
249 -- This api takes from and to currencies, conversion date, and Max Roll
250 -- days. It returns the rate from the from currency to the to currency.
251 --
252 -- Parameters :
253 -- IN :
254 -- p_from_currency IN VARCHAR2 Required -- From currency
255 -- p_to_currency IN VARCHAR2 Required -- To currency
256 -- p_conversion_date IN DATE Required -- Conversion date
257 -- p_max_roll_days IN NUMBER Optional -- Maximum days to roll back for a rate
258 --
259 -- Version :
260 --
261 -- HISTORY
262 -- 14-Sep-1998 J. Shang Created
263 -- Note :
264 -- In this function, a cursor is defined to search a closest rate. The query
265 -- follows the searching rule defined in GL_CURRENCY_API. So, if the rule is
266 -- changed in GL_CURRENCY_API, this cursor has to be changed also.
267 --
268 -- End of Comments
269
270 FUNCTION get_daily_rate (
271 p_from_currency IN VARCHAR2,
272 p_to_currency IN VARCHAR2,
273 p_conversion_date IN DATE,
274 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER;
275
276 -- Start of Comments
277 --
278 -- API name : get_daily_rate_sql
279 -- Type :
280 -- Pre-reqs :
281 -- Function :
282 -- This api takes from and to currencies, conversion date, and Max Roll
283 -- days. It returns the rate from the from currency to the to currency.
284 -- This api has the same function as the above api but is used in SQL
285 -- statement.
286 --
287 -- Parameters :
288 -- IN :
289 -- p_from_currency IN VARCHAR2 Required -- From currency
290 -- p_to_currency IN VARCHAR2 Required -- To currency
291 -- p_conversion_date IN DATE Required --Conversion date
292 -- p_max_roll_days IN NUMBER Optional --Maximum days to roll back for a rate
293 -- Version :
294 --
295 -- HISTORY
296 -- 14-Sep-1998 J. Shang Created
297 -- Note :
298 -- In this function, a cursor is defined to search a closest rate. The query
299 -- follows the searching rule defined in GL_CURRENCY_API. So, if the rule is
300 -- changed in GL_CURRENCY_API, this cursor has to be changed also.
301 --
302 -- End of Comments
303
304 FUNCTION get_daily_rate_sql (
305 p_from_currency IN VARCHAR2,
306 p_to_currency IN VARCHAR2,
307 p_conversion_date IN DATE,
308 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER;
309 PRAGMA RESTRICT_REFERENCES(get_daily_rate_sql, WNDS);
310
311 -- Start of Comments
312 --
313 -- API name : get_period_rate
314 -- Type :
315 -- Pre-reqs :
316 -- Function :
317 -- This api takes from and to currencies, conversion period. It returns the
318 -- rate from the from currency to the to currency based on pseudo period rate.
319 --
320 -- Parameters :
321 -- IN :
322 -- p_from_currency IN VARCHAR2 Required -- From currency
323 -- p_to_currency IN VARCHAR2 Required -- To currency
324 -- p_conversion_period IN VARCHAR2 Required -- Conversion period
325 -- Version :
326 --
327 -- HISTORY
328 -- 14-Sep-1998 J. Shang Created
329 --
330 -- End of Comments
331
332 FUNCTION get_period_rate (
333 p_from_currency IN VARCHAR2,
334 p_to_currency IN VARCHAR2,
335 p_conversion_period IN VARCHAR2) RETURN NUMBER;
336
337 -- Start of Comments
338 --
339 -- API name : get_period_rate_sql
340 -- Type :
341 -- Pre-reqs :
342 -- Function :
343 -- This api takes from and to currencies, conversion period. It returns the
344 -- rate from the from currency to the to currency based on pseudo period rate.
345 -- This api has the same function as the above api but is used in SQL
346 -- statement.
347 -- Parameters :
348 -- IN :
349 -- p_from_currency IN VARCHAR2 Required -- From currency
350 -- p_to_currency IN VARCHAR2 Required -- To currency
351 -- p_conversion_period IN VARCHAR2 Required -- Conversion period
352 -- Version :
353 --
354 -- HISTORY
355 -- 14-Sep-1998 J. Shang Created
359 FUNCTION get_period_rate_sql (
356 --
357 -- End of Comments
358
360 p_from_currency IN VARCHAR2,
361 p_to_currency IN VARCHAR2,
362 p_conversion_period IN VARCHAR2) RETURN NUMBER;
363 PRAGMA RESTRICT_REFERENCES(get_period_rate_sql, WNDS);
364
365 FUNCTION daily_rate_exists (
366 p_from_currency IN VARCHAR2,
367 p_to_currency IN VARCHAR2,
368 p_conversion_date IN DATE,
369 p_max_roll_days IN NUMBER DEFAULT 0,
370 x_rate_date OUT NOCOPY DATE) RETURN VARCHAR2;
371
372 -- Start of Comments
373 --
374 -- API name : period_rate_exists
375 -- Type :
376 -- Pre-reqs :
377 -- Function :
378 -- This api takes from and to currencies, conversion period. It checks if there
379 -- is a rate from the from currency to the to currency based on pseudo period rate.
380 --
381 -- Parameters :
382 -- IN :
383 -- p_from_currency IN VARCHAR2 Required -- From currency
384 -- p_to_currency IN VARCHAR2 Required -- To currency
385 -- p_conversion_period IN VARCHAR2 Required -- Conversion period
386 -- Version :
387 --
388 -- HISTORY
389 -- 14-Sep-1998 J. Shang Created
390 --
391 -- End of Comments
392
393 FUNCTION period_rate_exists (
394 p_from_currency IN VARCHAR2,
395 p_to_currency IN VARCHAR2,
396 p_conversion_period IN VARCHAR2) RETURN VARCHAR2;
397 PRAGMA RESTRICT_REFERENCES(period_rate_exists, WNDS);
398
399 -- Start of Comments
400 --
401 -- API name : get_period_info
402 -- Type :
403 -- Pre-reqs :
404 -- Function :
405 -- This api takes conversion period and returns the information of this period,
406 -- such as, period type, start date and end date.
407 --
408 -- Parameters :
409 -- IN :
410 -- p_period IN VARCHAR2 -- Conversion period
411 -- OUT
412 -- x_period_type OUT VARCHAR2 -- Conversion period type
413 -- x_period_date OUT DATE -- Converion date
414 -- Version :
415 --
416 -- History :
417 -- 22-Sep-98 J. Shang Created
418 -- End of Comments
419 --
420 PROCEDURE get_period_info(
421 p_period IN VARCHAR2,
422 x_period_type OUT NOCOPY VARCHAR2,
423 x_period_date OUT NOCOPY DATE);
424 PRAGMA RESTRICT_REFERENCES(get_period_info,WNDS,WNPS);
425
426 -- Start of Comments
427 --
428 -- API name : get_conversion_type
429 -- Type :
430 -- Pre-reqs :
431 -- Function :
432 -- This api takes conversion period type and returns the conversion type for
433 -- this period type.
434 --
435 -- Parameters :
436 -- IN :
437 -- p_period_type IN VARCHAR2 -- Conversion period type
438 -- Version :
439 --
440 -- History :
441 -- 22-Sep-98 J. Shang Created
442 -- 22-Oct-98 J. Shang Change name of the rate mapping table
443 -- End of Comments
444 --
445 FUNCTION get_conversion_type(
446 p_period_type IN VARCHAR2 ) RETURN VARCHAR2;
447 PRAGMA RESTRICT_REFERENCES(get_conversion_type,WNDS,WNPS);
448
449 -- Start of Comments
450 --
451 -- API name : get_euro_info
452 -- Type :
453 -- Pre-reqs :
454 -- Function :
455 -- This api takes currency code and the effective date, it will get the currency
456 -- type information about the given currency
457 --
458 -- Parameters :
459 -- IN :
460 -- p_currency_code IN VARCHAR2 -- Currency to be checked
461 -- p_effective_date IN DATE -- Effective date
462 -- x_currency_type OUT VARCHAR2 -- Type of the currency. Euro currency, set to 'EURO'
463 -- Emu currency, set to 'EMU'
464 -- Other currencies,set to 'OTHER'
465 -- Invalid currency, set to NULL
466 -- x_conversion_rate OUT NUMBER -- Fixed rate for conversion
467 -- Version :
468 --
469 -- History :
470 -- 03-Nov-98 J. Shang Created
471 -- Note :
472 -- This is temporary solution for OSM, and the procedure follows the similar privatre
473 -- procedure, get_info, defined in GL.
474 -- End of Comments
475 --
476 PROCEDURE get_euro_info(p_currency_code IN VARCHAR2,
477 p_effective_date IN DATE,
478 x_currency_type OUT NOCOPY VARCHAR2,
479 x_conversion_rate OUT NOCOPY NUMBER);
480
481 -- Start of Comments
482 --
483 -- API name : format_amount
484 -- Type :
485 -- Pre-reqs :
486 -- Function :
487 -- This api takes currency code and amount to be formatted. It returns the amount
488 -- converted into the appropriate format. The format mask is get from foundation
489 -- library.
490 --
491 -- Parameters :
492 -- IN :
493 -- p_currency_code IN VARCHAR2 -- Currency code to get format mask
494 -- p_amount IN NUMBER -- Amount to be formatted
495 -- p_length IN NUMBER -- The maximum number of characters available to hold
496 -- the formatted value
497 --
498 -- Version :
499 --
500 -- History :
501 -- 05-Nov-98 J. Shang Created
502 -- Note :
503 --
504 -- End of Comments
505 --
506 FUNCTION format_amount(p_currency_code IN VARCHAR2,
507 p_amount IN NUMBER,
508 p_length IN NUMBER) RETURN VARCHAR2;
509
510 -- Start of Comments
511 --
515 -- Function :
512 -- API name : unformat_amount
513 -- Type :
514 -- Pre-reqs :
516 -- This api takes currency code and amount to be unformatted. It returns the amount
517 -- converted into a number. The format mask is get from foundation library.
518 --
519 -- Parameters :
520 -- IN :
521 -- p_currency_code IN VARCHAR2 -- Currency code to get format mask
522 -- p_amount IN NUMBER -- Amount to be formatted
523 --
524 -- Version :
525 --
526 -- History :
527 -- 12-Nov-98 J. Shang Created
528 -- Note :
529 -- Conversion rules :
530 -- 1. For pass-in amount without format, such as, 12345.23, 58.6, this function will
531 -- convert the amount using format mask and then do conversion again to get the amount
532 -- in NUMBER type.
533 -- 2. For pass-in amount with exact format, the function will do conversion using the format
534 -- mask and return the amount in NUMBER type.
535 -- 3. For other situation, like an amount in invalid format, the fuction will return NEG_MAX_NUM
536 -- define in this package.
537 -- End of Comments
538 --
539 FUNCTION unformat_amount(p_currency_code IN VARCHAR2,
540 p_amount IN VARCHAR2) RETURN NUMBER;
541
542 -- Start of Comments
543 --
544 -- API name : convert_group_amounts_daily
545 -- Type :
546 -- Pre-reqs :
547 -- Function :
548 -- This api takes from currency, to currency, conversion date and a group of amount
549 -- to be converted. It returns the converted amount. This procedure is mainly to save
550 -- communication time between form and database
551 -- Parameters :
552 -- IN :
553 -- p_from_currency IN VARCHAR2
554 -- p_to_currency IN VARCHAR2
555 -- p_conversion_date IN DATE
556 -- p_amount1 IN NUMBER
557 -- p_amount2 IN NUMBER
558 -- p_amount3 IN NUMBER
559 -- p_amount4 IN NUMBER
560 -- p_amount5 IN NUMBER
561 -- OUT
562 -- p_out_amount1 OUT NUMBER
563 -- p_out_amount2 OUT NUMBER
564 -- p_out_amount3 OUT NUMBER
565 -- p_out_amount4 OUT NUMBER
566 -- p_out_amount5 OUT NUMBER
567 --
568 -- Version :
569 --
570 -- History :
571 -- 11-Dec-98 J. Shang Created
572 -- Note :
573 -- The possible exceptions generated from this procedure are
574 -- gl_currency_api.NO_RATE
575 -- gl_currency_api.INVALID_CURRENCY
576 -- as_mc_pkg.INVALID_DAILY_CONVERSION_TYPE
577 -- End of Comments
578 --
579 PROCEDURE convert_group_amounts_daily (
580 p_from_currency IN VARCHAR2,
581 p_to_currency IN VARCHAR2,
582 p_conversion_date IN DATE,
583 p_amount1 IN NUMBER,
584 p_amount2 IN NUMBER,
585 p_amount3 IN NUMBER,
586 p_amount4 IN NUMBER,
587 p_amount5 IN NUMBER,
588 p_out_amount1 OUT NOCOPY NUMBER,
589 p_out_amount2 OUT NOCOPY NUMBER,
590 p_out_amount3 OUT NOCOPY NUMBER,
591 p_out_amount4 OUT NOCOPY NUMBER,
592 p_out_amount5 OUT NOCOPY NUMBER);
593
594 END AS_MC_PKG;
[Home] [Help]