[Home] [Help]
PACKAGE BODY: APPS.POA_CURRENCY_PKG
Source
1 PACKAGE BODY POA_CURRENCY_PKG as
2 /* $Header: POACURB.pls 120.1 2005/09/09 11:37:44 nnewadka noship $ */
3
4 g_rate_date DATE := to_date('01/01/0039', 'DD/MM/YYYY');
5 g_currency_rate NUMBER;
6 g_rate_type VARCHAR2(150) := '**YYYYYYY';
7 g_currency_code VARCHAR2(150) := '**YYYYYYY';
8 g_rate NUMBER := -99999;
9
10 g_global_currency_code varchar2(3);
11 g_operating_unit varchar2(10);
12 g_functional_currency_code varchar2(3);
13 g_common_functional_currency varchar2(3);
14 g_sec_profile_id varchar2(10);
15
16 g_sglobal_currency_code varchar2(3);
17 g_display_sglobal_currency Boolean;
18
19 -- -------------------------------
20 -- get_global_currency
21 -- -------------------------------
22 FUNCTION get_global_currency RETURN VARCHAR2 IS
23
24 BEGIN
25
26 return bis_common_parameters.get_currency_code;
27
28 END get_global_currency;
29
30
31 -- -------------------------------
32 -- get_secondary_global_currency
33 -- -------------------------------
34 FUNCTION get_secondary_global_currency RETURN VARCHAR2 IS
35
36 BEGIN
37
38 return bis_common_parameters.get_secondary_currency_code;
39
40 END get_secondary_global_currency;
41
42 -- -------------------------------
43 -- display_secondary_currency_yn
44 -- -------------------------------
45 FUNCTION display_secondary_currency_yn RETURN BOOLEAN IS
46 BEGIN
47
48 IF(bis_common_parameters.get_secondary_currency_code IS NOT NULL AND
49 (
50 (bis_common_parameters.get_currency_code <>
51 bis_common_parameters.get_secondary_currency_code)
52 OR
53 ( bis_common_parameters.get_rate_type <>
54 bis_common_parameters.get_secondary_rate_type)
55 )
56 )
57 THEN
58 RETURN TRUE;
59 ELSE
60 RETURN FALSE;
61 END IF;
62 END display_secondary_currency_yn;
63
64 -- -------------------------------
65 -- get_global_rate
66 -- -------------------------------
67 FUNCTION get_global_rate (x_trx_currency_code VARCHAR2,
68 x_exchange_date DATE,
69 x_exchange_rate_type VARCHAR2
70 ) RETURN NUMBER IS
71
72 l_global_currency_code VARCHAR2(30);
73 l_global_rate_type VARCHAR2(15);
74
75 begin
76 l_global_currency_code := fnd_profile.value('POA_CURRENCY_CODE');
77 l_global_rate_type := fnd_profile.value('POA_CURRENCY_RATE_TYPE');
78
79 if x_trx_currency_code = l_global_currency_code then
80 return 1;
81 else
82 return GL_CURRENCY_API.get_rate_sql (
83 x_trx_currency_code,
84 l_global_currency_code,
85 x_exchange_date,
86 l_global_rate_type);
87
88 end if;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 return null;
93
94 END get_global_rate;
95
96 -- -------------------------------
97 -- get_global_currency_rate
98 -- -------------------------------
99 FUNCTION get_global_currency_rate (p_rate_type VARCHAR2,
100 p_currency_code VARCHAR2,
101 p_rate_date DATE,
102 p_rate NUMBER) RETURN NUMBER IS
103 BEGIN
104 if (p_rate_type is NULL) then
105 if (p_rate_date <> g_rate_date OR p_currency_code <> g_currency_code
106 OR g_rate_type is NOT NULL) then
107 g_currency_code := p_currency_code;
108 g_rate_date := p_rate_date;
109 g_rate_type := p_rate_type;
110 g_currency_rate := get_global_rate(p_currency_code,
111 p_rate_date, NULL);
112 end if;
113 elsif (p_rate_type = 'User') then
114 if (p_rate_date <> g_rate_date OR p_currency_code <> g_currency_code OR
115 p_rate_type <> g_rate_type OR g_rate_type is NULL OR
116 p_rate <> g_rate) then
117 g_currency_code := p_currency_code;
118 g_rate_date := p_rate_date;
119 g_rate_type := p_rate_type;
120 g_currency_rate := get_global_rate(p_currency_code,
121 p_rate_date, NULL) * p_rate;
122 end if;
123 else /* p_rate_type is NOT NULL and p_rate_type <> 'User' */
124 if (p_rate_date <> g_rate_date OR p_currency_code <> g_currency_code OR
125 p_rate_type <> g_rate_type OR g_rate_type is NULL) then
126 g_currency_code := p_currency_code;
127 g_rate_date := p_rate_date;
128 g_rate_type := p_rate_type;
129 g_currency_rate := get_global_rate(p_currency_code,
130 p_rate_date, p_rate_type);
131 end if;
132 end if;
133 return g_currency_rate;
134
135 END get_global_currency_rate;
136
137 FUNCTION get_display_currency(p_currency_code IN varchar2,
138 p_selected_operating_unit IN varchar2,
139 p_global_cur_type IN varchar2
140 DEFAULT 'P'
141 ) return varchar2
142 IS
143 l_return_value varchar2(1) := '0';
144
145 l_value NUMBER;
146 l_global_cur_type VARCHAR2(3);
147 BEGIN
148 -- selected currency is the same as the global currency
149
150 BEGIN
151 l_value := to_number(p_global_cur_type);
152 SELECT CASE WHEN BitAnd(l_value , 7) = 7 THEN 'PS'
153 WHEN BitAnd(l_value , 5) = 5 THEN 'S'
154 WHEN BitAnd(l_value , 3) = 3 THEN 'P'
155 END INTO l_global_cur_type
156 FROM DUAL;
157 EXCEPTION
158 WHEN OTHERS THEN
159 l_global_cur_type := p_global_cur_type;
160 END;
161 if(g_global_currency_code is null) then
162 g_global_currency_code := get_global_currency;
163 end if;
164
165 if(g_sglobal_currency_code is null) then
166 g_sglobal_currency_code := get_secondary_global_currency;
167 end if;
168
169 IF(g_display_sglobal_currency IS NULL) THEN
170 g_display_sglobal_currency := display_secondary_currency_yn;
171 END IF;
172
173 -- Check for Annualized currency
174
175 if ((BitAnd(l_value, 8) = 8) AND (p_currency_code = 'FII_GLOBAL3')) THEN
176 return 1; --Show annualized currency
177 elsif ((l_global_cur_type ='P' OR l_global_cur_type ='PS')
178 AND p_currency_code = 'FII_GLOBAL1') then
179 return '1' ; --show the primary global currency
180
181 elsif ((l_global_cur_type ='S' OR l_global_cur_type ='PS')
182 AND p_currency_code = 'FII_GLOBAL2'
183 AND g_display_sglobal_currency) then
184 return '1'; -- show the secondary global currency
185 else
186 -- Currency is not the global currency
187
188 if(nvl(p_selected_operating_unit,'ALL') <> 'ALL') then
189
190 if(p_selected_operating_unit <> g_operating_unit or g_operating_unit is null) then
191 select currency_code
192 into g_functional_currency_code
193 from financials_system_params_all fsp,
194 gl_sets_of_books gsob
195 where fsp.org_id = p_selected_operating_unit
196 and fsp.set_of_books_id = gsob.set_of_books_id;
197 g_operating_unit := p_selected_operating_unit;
198 end if;
199
200 if(p_currency_code = g_functional_currency_code)
201 then
202 --if primary global currency and functional Currency are same then
203 if (g_global_currency_code = g_functional_currency_code)
204 then
205 -- Same as primary global currency
206 return '0';
207 else
208
209 if(l_global_cur_type ='S' OR l_global_cur_type ='PS')
210 then
211 -- product team is implementing Secondary global currency
212 if(g_sglobal_currency_code = g_functional_currency_code)
213 then
214 -- Currency is same as Secondary global currency
215 return '0';
216 else
217 return '1';
218 end if;
219 else
220 -- Product team is not implementing secondary global currency
221 return '1';
222 end if;
223 end if;
224 else
225 -- Currency is not a functional currency
226 return '0';
227 end if;
228 else -- operating unit is 'All'
229
230
231 ---Begin MOAC changes
232 ---Following block is commemnted
233 /*
234 if(g_common_functional_currency is null or nvl(g_sec_profile_id, -1) <> poa_dbi_util_pkg.get_sec_profile) then
235 g_sec_profile_id := poa_dbi_util_pkg.get_sec_profile;
236 select distinct currency_code
237 into g_common_functional_currency
238 from financials_system_params_all fsp,
239 gl_sets_of_books gsob
240 where fsp.set_of_books_id = gsob.set_of_books_id
241 and fsp.org_id in (select organization_id from per_organization_list where security_profile_id = poa_dbi_util_pkg.get_sec_profile);
242 end if;
243 */
244
245 IF g_common_functional_currency IS NULL THEN
246 IF poa_dbi_util_pkg.get_sec_profile <> -1 THEN
247 SELECT DISTINCT currency_code
248 INTO g_common_functional_currency
249 FROM financials_system_params_all fsp,
250 gl_sets_of_books gsob
251 WHERE fsp.set_of_books_id = gsob.set_of_books_id
252 AND EXISTS (SELECT 1
253 FROM per_organization_list org_list
254 WHERE org_list.security_profile_id = poa_dbi_util_pkg.get_sec_profile
255 AND org_list.organization_id = fsp.org_id );
256
257 ELSE
258 SELECT DISTINCT currency_code
259 INTO g_common_functional_currency
260 FROM financials_system_params_all fsp,
261 gl_sets_of_books gsob
262 WHERE fsp.set_of_books_id = gsob.set_of_books_id
263 AND fsp.org_id = poa_dbi_util_pkg.get_ou_org_id ;
264 END IF ; --poa_dbi_util_pkg.get_sec_profile <> -1
265 END IF ; --g_functional_currency IS NULL
266 ---End MOAC changes
267
268 if(p_currency_code = g_common_functional_currency)
269 then
270 --if primary global currency and functional Currency are same then
271 if (g_global_currency_code = g_common_functional_currency)
272 then
273 -- Same as primary global currency
274 return '0';
275 else
276
277 if(l_global_cur_type ='S' OR l_global_cur_type ='PS')
278 then
279 -- product team is is implementing Secondary global currency
280 if(g_sglobal_currency_code = g_common_functional_currency)
281 then
282 -- Currency is same as Secondary global currency
283 return '0';
284 else
285 return '1';
286 end if;
287 else
288 -- Product team is not implementing secondary global currency
289 return '1';
290 end if;
291 end if;
292 else
293 -- Currency is not a functional currency
294 return '0';
295 end if;
296 end if;
297 end if;
298
299 EXCEPTION
300 when too_many_rows then
301 g_common_functional_currency := 'N/A';
302 return '0';
303 when others then
304 return '0';
305 END get_display_currency;
306
307 -- -------------------------------
308 -- get_dbi_global_rate
309 -- -------------------------------
310 FUNCTION get_dbi_global_rate (p_rate_type VARCHAR2,
311 p_currency_code VARCHAR2,
312 p_rate_date DATE,
313 p_txn_cur_code VARCHAR2) RETURN NUMBER
314 IS
315 l_ret NUMBER;
316 l_rate_date DATE := p_rate_date;
317 BEGIN
318 l_ret := fii_currency.get_fc_to_pgc_rate(p_txn_cur_code, p_currency_code, p_rate_date);
319 IF (l_ret < 0) THEN
320 IF (not g_missing_cur) THEN
321 bis_collection_utilities.writemissingrateheader;
322 g_missing_cur := TRUE;
323 END IF;
324 IF (l_ret = -3) THEN
325 l_rate_date := to_date('01/01/1999','MM/DD/RRRR');
326 END IF;
327 bis_collection_utilities.writemissingrate(p_rate_type, p_currency_code, get_global_currency(), l_rate_date);
328 END IF;
329 RETURN l_ret;
330 END get_dbi_global_rate;
331
332 -- -------------------------------
333 -- get_dbi_sglobal_rate
334 -- -------------------------------
335 function get_dbi_sglobal_rate(
336 p_rate_type varchar2,
337 p_currency_code varchar2,
338 p_rate_date date,
339 p_txn_cur_code varchar2
340 ) return number
341 is
342 l_ret number;
343 l_rate_date date := p_rate_date;
344 begin
345 l_ret := fii_currency.get_fc_to_sgc_rate(p_txn_cur_code, p_currency_code, p_rate_date);
346 if (l_ret < 0) then
347 if (not g_missing_cur) then
348 bis_collection_utilities.writemissingrateheader;
349 g_missing_cur := true;
350 end if;
351 if (l_ret = -3) then
352 l_rate_date := to_date('01/01/1999','MM/DD/RRRR');
353 end if;
354 bis_collection_utilities.writemissingrate(p_rate_type, p_currency_code, get_secondary_global_currency(), l_rate_date);
355 end if;
356 return l_ret;
357 end get_dbi_sglobal_rate;
358
359 END POA_CURRENCY_PKG;