1 package body fnd_client_info as
2 /* $Header: AFCINFOB.pls 120.2.12000000.6 2007/12/06 01:59:49 pdeluna ship $ */
3
4
5 --
6 -- Private Functions and Procedures
7 --
8 procedure generic_error(routine in varchar2,
9 errcode in number,
10 errmsg in varchar2) is
11 begin
12 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
13 fnd_message.set_token('ROUTINE', routine);
14 fnd_message.set_token('ERRNO', errcode);
15 fnd_message.set_token('REASON', dbms_utility.format_error_stack);
16 -- dbms_output.put_line(fnd_message.get);
17 fnd_message.raise_error;
18 end;
19
20 --
21 -- Public Functions and Procedures
22 --
23
24 --
25 -- Name
26 -- setup_client_info
27 -- Purpose
28 -- Sets up the operating unit context and the Multi-Currency context
29 -- in the client info area based on the current application,
30 -- responsibility, user, security_group and organization.
31 --
32 -- Arguments
33 -- application_id
34 -- responsibility_id
35 -- user_id
36 -- security_group_id
37 -- org_id
38 --
39 procedure setup_client_info(application_id in number,
40 responsibility_id in number,
41 user_id in number,
42 security_group_id in number,
43 org_id in number) is
44 org_id_char varchar2(240);
45 org_id_defined boolean;
46 sp_id_char varchar2(240);
47 sp_id_defined boolean;
48 is_multi_org varchar2(1);
49 no_morg_profile_value exception;
50 reporting_sob_id_char varchar2(240);
51 reporting_sob_id_defined boolean;
52 is_multi_currency varchar2(1);
53 no_mcur_profile_value exception;
54 security_profile_id_char varchar2(240);
55 -- security_profile_id_defined boolean;
56 l_security_profile_id NUMBER;
57 l_morg_profile_name varchar2(240);
58 begin
59
60 /* Bug 5646892: APPSPERFR12:FND:EXTRA FND_PROFILE.GET_SPECIFIC CALL IN
61 FND_CLIENT_INFO.SETUP_CLIENT_INFO
62 FND_GLOBAL will now pass in the org_id context, i.e. FND_GLOBAL.org_id.
63 There is no need for setup_client_info to get the ORG_ID value again.
64 */
65
66 --
67 -- Set MultiOrg Context
68 --
69 -- Check if org_id is NULL or -1. It is likely that org_id = -1, not NULL since
70 -- fnd_global is passing it in. When fnd_global calls fnd_profile to get the
71 -- org_id value and the value returned is NULL, fnd_global does not set org_id
72 -- to NULL. It just leaves the default value of -1. So, org_id = -1 means there
73 -- was no value returned by fnd_profile. A benefit of calling fnd_profile in
74 -- setup_client_info was that it did not check the value, but rather whether a
75 -- value was defined.
76 if (org_id is NULL) or (org_id = -1) then
77 -- If not R12, then check if the instance is multiorg-enabled.
78 if fnd_release.major_version < 12 then
79 -- Check FND_PRODUCT_GROUPS for multi-org/currency flags
80 select nvl(multi_org_flag, 'N'), nvl(multi_currency_flag, 'N')
81 into is_multi_org, is_multi_currency
82 from fnd_product_groups;
83 -- If multiorg-enabled, raise an error since org_id should not be NULL.
84 -- FND_GLOBAL.org_id should not be NULL if instance is multiorg-enabled.
85 if is_multi_org = 'Y' then
86 /* Bug 6637377: This fnd_profile.get_specific call is a LAST CHECK to
87 make sure that org_id is, indeed, not set. Need to make sure before
88 the error is raised. This should not undo the fix for 5646892
89 completely and is needed.
90 */
91 fnd_profile.get_specific('ORG_ID', user_id, responsibility_id,
92 application_id, org_id_char, org_id_defined);
93
94 -- If org_id is really not defined, then raise the exception, as
95 -- planned.
96 if (not org_id_defined) then
97
98 select user_profile_option_name
99 into l_morg_profile_name
100 from fnd_profile_options_vl
101 where profile_option_name = 'ORG_ID';
102
103 raise no_morg_profile_value;
104 end if;
105 end if;
106 else
107 -- Bug 2852842: Due to MOAC re-architecture for R12, a default org_id
108 -- is no longer required. If ORG_ID is not set, then default the org
109 -- client_info area to null. No need to raise an error in R12.
110 org_id_char := '';
111 end if;
112 else
113 -- If org_id is NOT NULL, convert to string.
114 org_id_char := to_char(org_id);
115 end if;
116
117 fnd_client_info.set_org_context(org_id_char);
118
119 --
120 -- Set MultiCurrency Context.
121 -- This applies to releases before R12.
122 --
123 if fnd_release.major_version < 12 then
124 if is_multi_currency = 'Y' then
125 --
126 -- Get MRC_REPORTING_SOB_ID profile option value
127 --
128 fnd_profile.get_specific('MRC_REPORTING_SOB_ID',
129 user_id, responsibility_id, application_id,
130 reporting_sob_id_char, reporting_sob_id_defined);
131 --
132 -- If MRC_REPORTING_SOB_ID profile option defined for this responsibility,
133 -- set the currency context = MRC_REPORTING_SOB_ID for this resp
134 --
135 if reporting_sob_id_defined then
136 fnd_client_info.set_currency_context(reporting_sob_id_char);
137 else
138 raise no_mcur_profile_value;
139 end if;
140
141 end if;
142 end if;
143
144 --
145 -- Set Security Group Context
146 --
147 fnd_client_info.set_security_group_context(to_char(security_group_id));
148
149 exception
150 when no_morg_profile_value then
151 fnd_message.set_name('FND', 'FND-ORG_ID PROFILE CANNOT READ');
152 fnd_message.set_token('OPTION', l_morg_profile_name);
153 -- dbms_output.put_line(fnd_message.get);
154 fnd_message.raise_error;
155 -- generic_error('FND_CLIENT_INFO.SETUP_CLIENT_INFO', -20000,
156 -- 'MultiOrg enabled but ORG_ID profile not defined');
157 when no_mcur_profile_value then
158 generic_error('FND_CLIENT_INFO.SETUP_CLIENT_INFO', -20000,
159 'MultiCurrency enabled but MRC_REPORTING_SOB_ID profile not defined');
160 when others then
161 generic_error('FND_CLIENT_INFO.SETUP_CLIENT_INFO', sqlcode, sqlerrm);
162
163 end setup_client_info;
164
165 --
166 -- Name
167 -- setup_client_info
168 -- Purpose
169 -- Sets up the operating unit context and the Multi-Currency context
170 -- in the client info area based on the current application,
171 -- responsibility, user, and security_group.
172 -- This is an overloaded version for backwards compatibility.
173 --
174 -- Arguments
175 -- application_id
176 -- responsibility_id
177 -- user_id
178 -- security_group_id
179 --
180 procedure setup_client_info(application_id in number,
181 responsibility_id in number,
182 user_id in number,
183 security_group_id in number) is
184 begin
185
186 -- Call setup_client_info and pass in fnd_global.org_id for
187 -- org argument.
188 setup_client_info(application_id, responsibility_id, user_id,
189 security_group_id, fnd_global.org_id);
190
191 end setup_client_info;
192
193 --
194 -- Name
195 -- set_org_context
196 -- Purpose
197 -- Sets up the operating unit context in the client info area
198 --
199 -- Arguments
200 -- context - org_id for the operating unit; can be up to 10
201 -- bytes long
202 --
203 procedure set_org_context (context in varchar2) is
204 context_area varchar2(64);
205 context_too_long exception;
206 bad_characters exception;
207 local_context varchar2(30);
208 begin
209
210 -- check for multibyte characters
211
212 if length(context) <> lengthb(context) then
213 raise bad_characters;
214 end if;
215
216 -- check for input string too long
217
218 if lengthb(context) > 10 then
219 raise context_too_long;
220 end if;
221
222 -- set local_context to first ten chars of context
223 -- set to a single space if context was null
224
225 local_context := substrb(nvl(context,' '),1,10);
226
227 -- pad local_context on the right with blanks to exactly 10 bytes
228 -- Do not use RPAD(), because it may not work as expected with a
229 -- MultiByte character set
230
231 while lengthb(local_context) < 10 loop
232 local_context := local_context || ' ';
233 end loop;
234
235 -- Get current CLIENT_INFO value in context_area variable
236
237 dbms_application_info.read_client_info(context_area);
238
239 -- pad context_area on the right with blanks to exactly 64 bytes
240 -- Do not use RPAD(), because it may not work as expected with a
241 -- MultiByte character set
242
243 context_area := nvl(context_area,' ');
244
245 while lengthb(context_area) < 64 loop
246 context_area := context_area || ' ';
247 end loop;
248
249 -- load new value into context_area
250
251 context_area := local_context ||
252 substrb(context_area,11,54);
253
254 -- save context_area variable to CLIENT_INFO
255
256 dbms_application_info.set_client_info(context_area);
257
258 exception
259 when context_too_long then
260 fnd_message.set_name('FND', 'CLIENT_INFO_ARG_TOO_LONG');
261 fnd_message.set_token('ROUTINE', 'SET_ORG_CONTEXT');
262 fnd_message.set_token('BAD_ARG', context);
263 -- dbms_output.put_line(fnd_message.get);
264 fnd_message.raise_error;
265 when bad_characters then
266 generic_error('FND_CLIENT_INFO.SET_ORG_CONTEXT', -20000,
267 'Only single-byte characters are valid input');
268 when others then
269 generic_error('FND_CLIENT_INFO.SET_ORG_CONTEXT', sqlcode, sqlerrm);
270
271 end set_org_context;
272
273 --
274 -- Name
275 -- set_currency_context
276 -- Purpose
277 -- Sets up the client info area for Multi-Currency reporting
278 --
279 -- Arguments
280 -- context - context information up to 10 bytes
281 --
282 procedure set_currency_context (context in varchar2) is
283 context_area varchar2(64);
284 context_too_long exception;
285 bad_characters exception;
286 local_context varchar2(30);
287 begin
288
289 -- check for multibyte characters
290
291 if length(context) <> lengthb(context) then
292 raise bad_characters;
293 end if;
294
295 -- check for input string too long
296
297 if lengthb(context) > 10 then
298 raise context_too_long;
299 end if;
300
301 -- set local_context to first ten chars of context
302 -- set to a single space if context was null
303
304 local_context := substrb(nvl(context,' '),1,10);
305
306 -- pad local_context on the right with blanks to exactly 10 bytes
307 -- Do not use RPAD(), because it may not work as expected with a
308 -- MultiByte character set
309
310 while lengthb(local_context) < 10 loop
311 local_context := local_context || ' ';
312 end loop;
313
314 -- Get current CLIENT_INFO value in context_area variable
315
316 dbms_application_info.read_client_info(context_area);
317
318 -- pad context_area on the right with blanks to exactly 64 bytes
319 -- Do not use RPAD(), because it may not work as expected with a
320 -- MultiByte character set
321
322 context_area := nvl(context_area,' ');
323
324 while lengthb(context_area) < 64 loop
325 context_area := context_area || ' ';
326 end loop;
327
328 -- load new value into context_area
329
330 context_area := substrb(context_area,1,44) ||
331 local_context ||
332 substrb(context_area,55,10);
333
334 -- save context_area variable to CLIENT_INFO
335
336 dbms_application_info.set_client_info(context_area);
337
338 exception
339 when context_too_long then
340 fnd_message.set_name('FND', 'CLIENT_INFO_ARG_TOO_LONG');
341 fnd_message.set_token('ROUTINE', 'SET_CURRENCY_CONTEXT');
342 fnd_message.set_token('BAD_ARG', context);
343 -- dbms_output.put_line(fnd_message.get);
344 fnd_message.raise_error;
345 when bad_characters then
346 generic_error('FND_CLIENT_INFO.SET_CURRENCY_CONTEXT', -20000,
347 'Only single-byte characters are valid input');
348 when others then
349 generic_error('FND_CLIENT_INFO.SET_CURRENCY_CONTEXT', sqlcode, sqlerrm);
350
351 end set_currency_context;
352
353 --
354 -- Name
355 -- set_security_group_context
356 -- Purpose
357 -- Sets up the the security group context in the client info area
358 --
359 -- Arguments
360 -- context - security_group_id; can be up to 10 bytes long
361 --
362 procedure set_security_group_context (context in varchar2) is
363 context_area varchar2(64);
364 context_too_long exception;
365 bad_characters exception;
366 local_context varchar2(30);
367 begin
368
369 -- check for multibyte characters
370
371 if length(context) <> lengthb(context) then
372 raise bad_characters;
373 end if;
374
375 -- check for input string too long
376
377 if lengthb(context) > 10 then
378 raise context_too_long;
379 end if;
380
381 -- set local_context to first ten chars of context
382 -- set to a single space if context was null
383
384 local_context := substrb(nvl(context,' '),1,10);
385
386 -- pad local_context on the right with blanks to exactly 10 bytes
387 -- Do not use RPAD(), because it may not work as expected with a
388 -- MultiByte character set
389
393
390 while lengthb(local_context) < 10 loop
391 local_context := local_context || ' ';
392 end loop;
394 -- Get current CLIENT_INFO value in context_area variable
395
396 dbms_application_info.read_client_info(context_area);
397
398 -- pad context_area on the right with blanks to exactly 64 bytes
399 -- Do not use RPAD(), because it may not work as expected with a
400 -- MultiByte character set
401
402 context_area := nvl(context_area,' ');
403
404 while lengthb(context_area) < 64 loop
405 context_area := context_area || ' ';
406 end loop;
407
408 -- load new value into context_area
409 context_area := substrb(context_area,1,54) ||
410 local_context;
411
412 -- save context_area variable to CLIENT_INFO
413
414 dbms_application_info.set_client_info(context_area);
415
416 exception
417 when context_too_long then
418 fnd_message.set_name('FND', 'CLIENT_INFO_ARG_TOO_LONG');
419 fnd_message.set_token('ROUTINE', 'SET_SECURITY_GROUP_CONTEXT');
420 fnd_message.set_token('BAD_ARG', context);
421 -- dbms_output.put_line(fnd_message.get);
422 fnd_message.raise_error;
423 when bad_characters then
424 generic_error('FND_CLIENT_INFO.SET_SECURITY_GROUP_CONTEXT', -20000,
425 'Only single-byte characters are valid input');
426 when others then
427 generic_error('FND_CLIENT_INFO.SET_SECURITY_GROUP_CONTEXT',
428 sqlcode, sqlerrm);
429 end set_security_group_context;
430
431 --
432 -- Name
433 -- org_security
434 -- Purpose
435 -- Called by oracle server during parsing sql statment
436 --
437 -- Arguments
438 -- obj_schema - schema of the object
439 -- obj_name - name of the object
440 --
441 FUNCTION org_security(
442 obj_schema VARCHAR2
443 , obj_name VARCHAR2
444 )
445 RETURN VARCHAR2
446 IS
447
448 -- AOL suggested that all product-specific logic should be moved to
449 -- product-specific packages. Hence, the org_security logic is moved
450 -- to the new package MO_GLOBAL.
451 --
452 -- However, FND_CLIENT_INFO.org_security is referenced in many CRM
453 -- views. Removing it from this package is out of the question since
454 -- all CRM code would break.
455 --
456 -- So, only option is to keep it as a wrapper function for
457 -- MO_GLOBAL.org_security
458
459 l_sql_stmt VARCHAR2(1000);
460 l_predicate VARCHAR2(2000) := '';
461
462
463 BEGIN
464
465 -- For backward compatible purpose
466 l_sql_stmt := 'select mo_global.org_security(:1, :2) from dual';
467 EXECUTE IMMEDIATE l_sql_stmt INTO l_predicate USING
468 IN obj_schema,
469 IN obj_name;
470
471 RETURN l_predicate;
472
473 -- Alternatively, we could have avoided the dynamic SQL by simply
474 -- returning MO_GLOBAL.org_security(obj_schema, obj_name) but we don't
475 -- want this package to have dependencies on other packages during
476 -- compilation.
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
481 fnd_message.set_token('ERRNO', to_char(sqlcode));
482 fnd_message.set_token('REASON', dbms_utility.format_error_stack);
483 fnd_message.set_token('ROUTINE', 'ORG_SECURITY');
484 app_exception.raise_exception;
485 END org_security;
486
487 end fnd_client_info;