[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CURRENCY_PKG
Source
1 PACKAGE BODY ISC_DBI_CURRENCY_PKG AS
2 /* $Header: ISCCURRB.pls 120.0.12000000.2 2007/01/25 07:10:18 abhdixi ship $ */
3
4 g_global_currency_code varchar2(100);
5 g_global_rate_type varchar2(100);
6 g_sec_currency_code varchar2(100);
7 g_sec_rate_type varchar2(100);
8 g_ou_is_cached BOOLEAN := FALSE;
9 g_w_is_cached BOOLEAN := FALSE;
10 g_s_is_cached BOOLEAN := FALSE;
11 g_common_functional_currency varchar2(15);
12
13
14 function is_sec_curr_defined return varchar2 is
15
16 begin
17 if not g_s_is_cached then
18 g_sec_currency_code := bis_common_parameters.get_secondary_currency_code;
19 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
20 g_s_is_cached := true;
21 end if;
22
23 if (g_sec_currency_code is null and g_sec_rate_type is null) then
24 return 'N';
25 elsif (g_sec_currency_code is not null and g_sec_rate_type is not null) then
26 return 'Y';
27 else
28 return 'E';
29 end if;
30
31 end is_sec_curr_defined;
32
33
34 -- -------
35 -- get_ou
36 -- -------
37
38 function get_ou(p_selected_org IN varchar2) return number is
39
40 l_ou number;
41
42 begin
43
44 select decode(fpg.multi_org_flag, 'Y', to_number(hoi2.org_information3), to_number(null))
45 into l_ou
46 from hr_organization_information hoi2,
47 fnd_product_groups fpg
48 where (hoi2.org_information_context || '') = 'Accounting Information'
49 and organization_id = p_selected_org;
50
51 return l_ou;
52
53 exception
54 when others then
55 return (-1);
56
57 end get_ou;
58
59 -- ---------------------
60 -- get_display_currency
61 -- ----------------------
62
63 function get_display_currency( p_org_type IN varchar2,
64 p_currency_code IN varchar2,
65 p_selected_org IN varchar2) return varchar2 is
66
67 l_f_currency_code varchar2(15);
68 l_return_value varchar2(1);
69 l_ou number;
70 l_sec varchar2(1);
71 l_failure exception;
72 l_fnd_prod_grp_flag varchar2(1);
73 l_fnd_prod_grp_filter varchar2(20);
74 l_stmt varchar2(2000);
75
76 begin
77
78 l_return_value := '0';
79
80 if (upper(p_org_type) = 'O') then -- for operating unit page parameter
81
82 if not g_ou_is_cached then
83 g_global_currency_code := bis_common_parameters.get_currency_code;
84 g_ou_is_cached := true;
85 if (g_global_currency_code is null) then
86 raise l_failure;
87 end if;
88 end if;
89
90 if (p_currency_code = 'FII_GLOBAL1') then
91 return '1';
92
93 else -- Currency is not the global currency
94
95 if (p_selected_org <> 'ALL') then
96
97 select gsob.currency_code
98 into l_f_currency_code
99 from ar_system_parameters_all asp,
100 gl_sets_of_books gsob
101 where asp.org_id = p_selected_org
102 and asp.set_of_books_id = gsob.set_of_books_id;
103
104 if (p_currency_code = l_f_currency_code) then
105 if (l_f_currency_code = g_global_currency_code or l_f_currency_code = g_sec_currency_code) then
106 return '0';
107 else
108 return '1';
109 end if;
110 else
111 return '0';
112 end if;
113
114 else -- if org ='ALL' and all OUs have same function curreny which is different from global currency, show functional curr.
115 if(g_common_functional_currency is null) then
116
117 select distinct gsob.currency_code
118 into g_common_functional_currency
119 from ar_system_parameters_all asp,
120 gl_sets_of_books gsob
121 where asp.set_of_books_id = gsob.set_of_books_id
122 and asp.org_id in (select organization_id from per_organization_list where security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'));
123 end if;
124
125 if (p_currency_code = g_common_functional_currency) then
126 if (g_common_functional_currency = g_global_currency_code or g_common_functional_currency = g_sec_currency_code) then
127 return '0';
128 else
129 return '1';
130 end if;
131 else
132 return '0';
133 end if;
134
135
136 end if;
137 end if;
138
139 elsif (upper(p_org_type) = 'W') then -- for inventory org page parameter
140
141 if not g_w_is_cached then
142 g_global_currency_code := bis_common_parameters.get_currency_code;
143 g_global_rate_type := bis_common_parameters.get_rate_type;
144 g_w_is_cached := true;
145 if (g_global_currency_code is null) then
146 raise l_failure;
147 end if;
148 end if;
149
150 if (p_currency_code = 'FII_GLOBAL1') then
151 return '1';
152
153 elsif (p_currency_code = 'FII_GLOBAL2') then
154
155 l_sec := is_sec_curr_defined;
156 if (l_sec = 'Y') then
157 if (g_global_currency_code = g_sec_currency_code and g_global_rate_type = g_sec_rate_type) then
158 return '0';
159 else
160 return '1';
161 end if;
162 else
163 return '0';
164 end if;
165
166
167 else -- Currency is not the global currency
168
169 if (p_selected_org <> 'ALL') then
170
171 l_ou := get_ou(p_selected_org);
172
173 if (l_ou = -1) then
174 raise l_failure;
175 end if;
176
177 select gsob.currency_code
178 into l_f_currency_code
179 from ar_system_parameters_all asp,
180 gl_sets_of_books gsob
181 where asp.org_id = l_ou
182 and asp.set_of_books_id = gsob.set_of_books_id;
183
184 if (p_currency_code = l_f_currency_code) then
185 if (l_f_currency_code = g_global_currency_code or l_f_currency_code = g_sec_currency_code) then
186 return '0';
187 else
188 return '1';
189 end if;
190 else
191 return '0';
192 end if;
193
194 else -- p_selected_org = 'ALL' and functional currency
195 if(g_common_functional_currency is null) then
196
197 -- <Bug 4913384>
198
199 select multi_org_flag
200 into l_fnd_prod_grp_flag
201 from fnd_product_groups;
202
203 if(SQL%ROWCOUNT = 1)
204 then
205 if(l_fnd_prod_grp_flag = 'Y')
206 then
207 l_fnd_prod_grp_filter := ' to_number(hoi2.org_information3) ';
208 else
209 l_fnd_prod_grp_filter := ' to_number(null) ';
210 end if;
211
212 l_stmt := ' select distinct gsob.currency_code
213 into g_common_functional_currency -- if not all orgs have same func currency, will error out, return 0
214 from (select o.organization_id
215 from org_access o
216 where o.responsibility_id = fnd_global.resp_id
217 and o.resp_application_id = fnd_global.resp_appl_id
218 union
219 select org.organization_id
220 from mtl_parameters org
221 where not exists (select 1
222 from org_access ora
223 where org.organization_id = ora.organization_id))
224 org,
225 ar_system_parameters_all asp,
226 gl_sets_of_books gsob,
227 hr_organization_information hoi2
228 where '|| l_fnd_prod_grp_filter || ' = asp.org_id
229 and asp.set_of_books_id = gsob.set_of_books_id
230 and (hoi2.org_information_context || '') = ''Accounting Information''
231 and hoi2.organization_id = org.organization_id' ;
232
233 execute immediate l_stmt;
234
235 else
236 raise l_failure;
237 end if;
238
239 -- </Bug 4913384>
240
241 end if;
242
243 if (p_currency_code = g_common_functional_currency) then
244 if (g_common_functional_currency = g_global_currency_code or g_common_functional_currency = g_sec_currency_code) then
245 return '0';
246 else
247 return '1';
248 end if;
249 else
250 return '0';
251 end if;
252 end if; -- end if p_selected_org<>'ALL'
253 end if; -- end if p_currency_code = 'FII_GLOBAL1' or (p_currency_code = 'FII_GLOBAL2')
254 end if;
255
256 exception
257 when too_many_rows then
258 g_common_functional_currency := 'N/A';
259 return '0';
260 when l_failure then
261 return '0';
262 when others then
263 return '0';
264
265 end get_display_currency;
266
267 FUNCTION get_func_display_currency(p_org_type IN varchar2,
268 p_currency_code IN varchar2,
269 p_selected_org IN varchar2) return varchar2 is
270
271 l_f_currency_code varchar2(15) := null;
272 l_ou number;
273 l_sec varchar2(1);
274 l_failure exception;
275
276 begin
277 /*if (p_currency_code = 'FII_GLOBAL1' or p_currency_code = 'FII_GLOBAL2') then
278 return '0';
279 end if; */
280
281 if not g_w_is_cached then
282 g_global_currency_code := bis_common_parameters.get_currency_code;
283 g_sec_currency_code := bis_common_parameters.get_secondary_currency_code;
284 g_global_rate_type := bis_common_parameters.get_rate_type;
285 g_w_is_cached := true;
286 end if;
287
288 if(upper(p_org_type) = 'W') then
289 if(upper(p_selected_org) <> 'ALL') then
290 l_ou := get_ou(p_selected_org);
291 if (l_ou = -1) then
292 return '0';
293 end if;
294 select gsob.currency_code
295 into l_f_currency_code
296 from ar_system_parameters_all asp,
297 gl_sets_of_books gsob
298 where asp.org_id = l_ou
299 and asp.set_of_books_id = gsob.set_of_books_id;
300
301 if (l_f_currency_code = g_global_currency_code and
302 p_currency_code = 'FII_GLOBAL1') then
303 return '1';
304 else
305 if (l_f_currency_code = g_sec_currency_code and
306 p_currency_code = 'FII_GLOBAL2') then
307 return '1';
308 else
309 if (p_currency_code = 'FII_GLOBAL1' or p_currency_code = 'FII_GLOBAL2') then
310 return '0';
311 else
312 if (l_f_currency_code = p_currency_code and
313 l_f_currency_code <> g_global_currency_code and
314 l_f_currency_code <> g_sec_currency_code) then
315 return '1';
316 else
317 return '0';
318 end if;
319 end if;
320 end if;
321 end if;
322 else
323 return '0';
324 end if;
325 else
326 return '0';
327 end if;
328
329 end get_func_display_currency;
330
331 FUNCTION get_cpm_display_currency( p_currency_code IN varchar2) return varchar2 is
332
333 BEGIN
334
335 if (p_currency_code = 'FII_GLOBAL1' or p_currency_code = 'FII_GLOBAL2') then
336 return get_display_currency(p_org_type => 'W',
337 p_currency_code => p_currency_code,
338 p_selected_org => 'ALL');
339 else
340 return '0';
341 end if;
342
343 end get_cpm_display_currency ;
344
345 END ISC_DBI_CURRENCY_PKG;