[Home] [Help]
PACKAGE BODY: APPS.EDW_CURRENCY
Source
1 PACKAGE BODY EDW_CURRENCY as
2 /* $Header: FIICACRB.pls 120.1 2002/10/22 21:52:39 djanaswa ship $ */
3
4 -- --------------------------------------------------------------
5 -- Name: convert_global_amount
6 -- --------------------------------------------------------------
7 FUNCTION convert_global_amount (
8 x_trx_amount NUMBER,
9 x_base_amount NUMBER DEFAULT NULL,
10 x_trx_currency_code VARCHAR2,
11 x_base_currency_code VARCHAR2 DEFAULT NULL,
12 x_exchange_date DATE,
13 x_exchange_rate_type VARCHAR2 DEFAULT NULL
14 ) RETURN NUMBER IS
15
16 l_converted_amount NUMBER := -1;
17 l_rate_type VARCHAR2(30);
18 l_global_currency_code VARCHAR2(30);
19 l_global_rate_type VARCHAR2(15);
20
21 BEGIN
22 Select /*+ FULL(SP) CACHE(SP) */
23 warehouse_currency_code, rate_type
24 into l_global_currency_code, l_global_rate_type
25 From EDW_LOCAL_SYSTEM_PARAMETERS SP;
26
27 IF (x_base_amount is not NULL) and
28 (x_base_currency_code = l_global_currency_code) then
29 l_converted_amount := x_base_amount;
30 ELSIF (x_trx_amount is not NULL) and
31 (x_trx_currency_code = l_global_currency_code) then
32 l_converted_amount := x_trx_amount;
33 ELSIF (x_base_amount IS NULL AND x_trx_amount IS NULL ) then
34 l_converted_amount := to_number(NULL);
35 ELSIF (x_exchange_date IS NULL) THEN
36 l_converted_amount := -1;
37 ELSE
38
39 IF (x_base_currency_code is not NULL) and
40 (x_base_amount is not NULL) then
41 l_converted_amount := GL_CURRENCY_API.convert_amount_sql (
42 x_base_currency_code,
43 l_global_currency_code,
44 x_exchange_date,
45 l_global_rate_type,
46 x_base_amount);
47 END IF;
48
49 IF (l_converted_amount = -1 AND
50 x_trx_currency_code is not NULL AND
51 x_trx_amount is not NULL) then
52 l_converted_amount := GL_CURRENCY_API.convert_amount_sql (
53 x_trx_currency_code,
54 l_global_currency_code,
55 x_exchange_date,
56 l_global_rate_type,
57 x_trx_amount);
58 END IF;
59
60 END IF;
61
62 return (l_converted_amount);
63
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 raise_application_error(-20000,
67 'No data found, trx_amount='||x_trx_amount||','||
68 'base_amount='||x_base_amount||','||
69 'trx_curr='||x_trx_currency_code||','||
70 'base_curr='||x_base_currency_code||','||
71 'date='||to_char(x_exchange_date)||','||
72 'rate_type='||x_exchange_rate_type);
73 WHEN OTHERS THEN
74 raise_application_error(-20000,
75 'Other error, trx_amount='||x_trx_amount||','||
76 'base_amount='||x_base_amount||','||
77 'trx_curr='||x_trx_currency_code||','||
78 'base_curr='||x_base_currency_code||','||
79 'date='||to_char(x_exchange_date)||','||
80 'rate_type='||x_exchange_rate_type);
81
82 END convert_global_amount;
83
84
85
86 -- --------------------------------------------------------------
87 -- Name: convert_global_amount
88 -- Performance: Worse case, 9 buffer gets per call
89 -- --------------------------------------------------------------
90 FUNCTION convert_global_amount (
91 x_trx_amount NUMBER,
92 x_base_amount NUMBER DEFAULT NULL,
93 x_trx_currency_code VARCHAR2,
94 x_set_of_books_id NUMBER,
95 x_exchange_date DATE,
96 x_exchange_rate_type VARCHAR2 DEFAULT NULL
97 ) RETURN NUMBER IS
98 l_base_currency_code VARCHAR2(15) := NULL;
99 l_converted_amount NUMBER;
100 cursor sob_cur is
101 Select currency_code
102 From GL_SETS_OF_BOOKS
103 Where set_of_books_id = x_set_of_books_id;
104 BEGIN
105
106 if (x_base_amount is not NULL) then
107 open sob_cur;
108 fetch sob_cur into l_base_currency_code;
109 close sob_cur;
110 end if;
111
112 l_converted_amount := convert_global_amount(
113 x_trx_amount,
114 x_base_amount,
115 x_trx_currency_code,
116 l_base_currency_code,
117 x_exchange_date,
118 x_exchange_rate_type
119 );
120
121 return (l_converted_amount);
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 raise_application_error(-20000,
126 'No data found, trx_amount='||x_trx_amount||','||
127 'base_amount='||x_base_amount||','||
128 'trx_curr='||x_trx_currency_code||','||
129 'sob_id='||x_set_of_books_id||','||
130 'date='||to_char(x_exchange_date)||','||
131 'rate_type='||x_exchange_rate_type);
132 WHEN OTHERS THEN
133 if sob_cur%ISOPEN then
134 close sob_cur;
135 end if;
136 raise_application_error(-20000,
137 'Other error, trx_amount='||x_trx_amount||','||
138 'base_amount='||x_base_amount||','||
139 'trx_curr='||x_trx_currency_code||','||
140 'sob_id='||x_set_of_books_id||','||
141 'date='||to_char(x_exchange_date)||','||
142 'rate_type='||x_exchange_rate_type);
143
144
145 END convert_global_amount;
146
147
148
149 -- -------------------------------
150 -- get_rate
151 -- -------------------------------
152
153 FUNCTION get_rate (
154 x_trx_currency_code VARCHAR2,
155 x_exchange_date DATE,
156 x_exchange_rate_type VARCHAR2 DEFAULT NULL
157 ) RETURN NUMBER IS
158
159 l_warehouse_currency_code VARCHAR2(15);
160 l_warehouse_rate_type VARCHAR2(30);
161 l_global_currency_code VARCHAR2(30);
162 l_global_rate_type VARCHAR2(15);
163
164 begin
165
166 Select /*+ FULL(SP) CACHE(SP) */
167 warehouse_currency_code, rate_type
168 into l_global_currency_code, l_global_rate_type
169 From EDW_LOCAL_SYSTEM_PARAMETERS SP;
170
171 if x_trx_currency_code = l_global_currency_code then
172 return 1;
173 else
174 return GL_CURRENCY_API.get_rate_sql (
175 x_trx_currency_code,
176 l_global_currency_code,
177 x_exchange_date,
178 l_global_rate_type);
179
180 end if;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 return null;
185
186 END get_rate;
187
188
189 -- -------------------------------
190 -- get_mau
191 -- -------------------------------
192
193 FUNCTION get_mau RETURN NUMBER IS
194 l_mau NUMBER;
195 BEGIN
196
197 select nvl( curr.minimum_accountable_unit, power( 10, (-1 * curr.precision)))
198 into l_mau
199 from edw_local_system_parameters lsp,
200 gl_currencies curr
201 where lsp.warehouse_currency_code = curr.currency_code;
202
203 if l_mau is null then
204 l_mau := 0.01; -- assign default value if null;
205 elsif l_mau = 0 then
206 l_mau := 1;
207 end if;
208
209 return l_mau;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 return null;
214
215 END get_mau;
216
217
218 END EDW_CURRENCY;