1 PACKAGE BODY FND_DATA_SECURITY AS
2 /* $Header: AFSCDSCB.pls 120.18 2006/11/15 07:26:42 stadepal noship $ */
3
4 C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_DATA_SECURITY';
5 C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_DATA_SECURITY.';
6 C_TYPE_SET CONSTANT VARCHAR2(30) := 'SET';
7 C_TYPE_GLOBAL CONSTANT VARCHAR2(30) := 'GLOBAL';
8 C_TYPE_INSTANCE CONSTANT VARCHAR2(30) := 'INSTANCE';
9 C_TYPE_UNIVERSAL CONSTANT VARCHAR2(30) := 'UNIVERSAL';
10
11 C_NULL_STR CONSTANT VARCHAR2(30) := '*NULL*';
12
13
14 C_AMP_STR CONSTANT VARCHAR2(30) := '&';
15 C_GRANT_ALIAS_TOK CONSTANT VARCHAR2(30) := C_AMP_STR || 'GRANT_ALIAS.';
16 C_TABLE_ALIAS_TOK CONSTANT VARCHAR2(30) := C_AMP_STR || 'TABLE_ALIAS.';
17
18
19 c_pred_buf_size CONSTANT NUMBER := 32767;
20
21 /* This is the VPD size limit of predicates in the database. */
22 /* In 8.1.7 databases the limit is 4k, and in 8.2+ it will be 32k. */
23 /* Set by the code to 4k for 8.1.7- or ~32k if db version is 8.2+ */
24 g_vpd_buf_limit NUMBER := -1; /* 0 means needs to be initialized */
25
26 /* One level cache for get_object_id() */
27 g_obj_id_cache NUMBER := NULL;
28 g_obj_name_cache VARCHAR2(30) := NULL;
29
30 /* One level cache for get_function_id() */
31 g_func_id_cache NUMBER := NULL;
32 -- modified for bug#5395351
33 g_func_name_cache fnd_form_functions.function_name%type := NULL;
34
35 /* One level cache for get_security_predicate */
36 -- modified for bug#5395351
37 g_gsp_function fnd_form_functions.function_name%type := '*EMPTY*';
38 g_gsp_object_name VARCHAR2(30) := '*EMPTY*';
39 g_gsp_grant_instance_type VARCHAR2(30) := '*EMPTY*';
40 g_gsp_user_name VARCHAR2(255) := '*EMPTY*';
41 g_gsp_statement_type VARCHAR2(30) := '*EMPTY*';
42 g_gsp_predicate VARCHAR2(32767):= '*EMPTY*';
43 g_gsp_return_status VARCHAR2(30) := '*EMPTY*';
44 g_gsp_table_alias VARCHAR2(255) := '*EMPTY*';
45 g_gsp_bind_order VARCHAR2(255) := '*EMPTY*';
46 g_gsp_with_binds VARCHAR2(30) := '*EMPTY*';
47 g_gsp_context_user_id NUMBER := -11111;
48 g_gsp_context_secgrpid NUMBER := -11111;
49 g_gsp_context_resp_id NUMBER := -11111;
50 g_gsp_context_resp_appl_id NUMBER := -11111;
51 g_gsp_context_org_id NUMBER := -11111;
52 g_gsp_object_id NUMBER := -11111;
53 g_gsp_function_id NUMBER := -11111;
54
55 /* One level cache for CHECK_USER_ROLE() */
56 g_ck_user_role_result VARCHAR2(1) := NULL;
57 g_ck_user_role_name VARCHAR2(255) := NULL;
58
59
60 /* Returning this value indicates date conversion failed. */
61 g_bad_date DATE := fnd_date.canonical_to_date('1970/11/11');
62
63 /* Define the exception that will be used for reraising exception */
64 /* concerning a call to deprecated APIs */
65 FND_MESSAGE_RAISED_ERR EXCEPTION;
66 pragma exception_init(FND_MESSAGE_RAISED_ERR, -20001);
67
68
69 ---This is an internal procedure. Not for general use.
70 -- Initializes the max vpd predicate size depending on the database version.
71 -- In 8.1.7 databases the limit is 4k, and in 8.2 it will be 32k.
72 -----------------------------------------------
73 function self_init_pred_size return number is
74 limit_up_to_8_1_7 number := 4*1024; /* 4k */
75 /* Keep the limit smaller than c_pred_buf_size so that we can detect when */
76 /* we have filled a predicate buffer past the limit. */
77 limit_after_8_1_7 number := c_pred_buf_size-1; /* approx 32k */
78 l_pos1 pls_integer;
79 l_pos2 pls_integer;
80 l_version_string varchar2(80);
81 l_version_major varchar2(80);
82 l_version_minor varchar2(80);
83 begin
84
85 /* Version will be something like '9.2.0.5.0' meaning major 9, minor 2*/
86 SELECT version
87 INTO l_version_string
88 FROM v$instance;
89
90 l_pos1 := instr(l_version_string, '.');
91 if((l_pos1) = 0) then
92 /* Cant parse version. Should never happen. Assume the worst */
93 return limit_up_to_8_1_7;
94 end if;
95
96 l_version_major := to_number(substr(l_version_string,
97 1,
98 l_pos1 - 1));
99
100 if(l_version_major > 8) then
101 /* Db version is higher, so use higher limit */
102 return limit_after_8_1_7;
103 end if;
104
105 if(l_version_major < 8) then
106 /* Db version is lower, so use lower limit */
107 return limit_up_to_8_1_7;
108 end if;
109
110 /* If we got here then the major version is 8, so check minor version*/
111
112 l_pos2 := instr(l_version_string, '.', 1, 2);
113 if((l_pos2) = 0) then
114 /* Cant parse version. Should never happen. Assume the worst */
115 return limit_up_to_8_1_7;
116 end if;
117
118 l_version_minor := to_number(substr(l_version_string,
119 l_pos1 + 1,
120 l_pos2 - l_pos1 - 1));
121
122 if(l_version_minor >= 2) then
123 /* Db version is higher, so use higher limit */
124 return limit_after_8_1_7;
125 else
126 /* Db version is lower, so use lower limit */
127 return limit_up_to_8_1_7;
128 end if;
129
130 end;
131
132 ---This is an internal procedure. Not for general use.
133 -- Gets returns a result indicating whether the user has a role.
134 -----------------------------------------------
135 function CHECK_USER_ROLE(P_USER_NAME in varchar2)
136 return varchar2 /* T/F */
137 is
138 l_dummy number := 0;
139 colon pls_integer;
140
141 begin
142
143 if( (g_ck_user_role_name is not NULL)
144 and (g_ck_user_role_name = p_user_name)) then
145 return g_ck_user_role_result;
146 end if;
147
148
149 --Changes for Bug#3867925
150 -- Fix Non Backward change made for universal person support
151 colon := instr(p_user_name, 'PER:');
152 if (colon <> 0) then
153 -- Fix for bug 4308825: This code was preventing global grantee type
154 -- grants from working in the PER case, so it's commented out.
155 -- select 1
156 -- into l_dummy
157 -- from fnd_grants
158 -- where rownum = 1
159 -- and grantee_type = 'USER'
160 -- and grantee_key = p_user_name;
161 null;
162 else
163 select 1
164 into l_dummy
165 from wf_user_roles
166 where user_name = p_user_name
167 and rownum = 1;
168 end if;
169
170 g_ck_user_role_result := 'T';
171 g_ck_user_role_name := p_user_name;
172 return g_ck_user_role_result;
173
174 exception when no_data_found then
175 g_ck_user_role_result := 'F';
176 g_ck_user_role_name := p_user_name;
177 return g_ck_user_role_result;
178
179 end CHECK_USER_ROLE;
180
181 ---This is an internal procedure. Not for general use.
182 -- Gets the user_name bind value, e.g.
183 -- SYS_CONTEXT('FND','USER_NAME')
184 -- This will return references to the sys_context rather
185 -- than literal values if it can, so that a statement
186 -- can be reused without parsing.
187 -----------------------------------------------
188 procedure get_name_bind(p_user_name in VARCHAR2,
189 x_user_name_bind out NOCOPY varchar2) is
190 l_api_name CONSTANT VARCHAR2(30) := 'GET_NAME_BIND';
191 colon pls_integer;
192 l_unfound BOOLEAN;
193 x_user_id number;
194 x_is_per_person number;
195 begin
196
197 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
198 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
199 c_log_head || l_api_name || '.begin',
200 c_pkg_name || '.' ||l_api_name|| '(' ||
201 'p_user_name =>'|| p_user_name ||');');
202 end if;
203
204 if ((p_user_name is NULL) or (p_user_name = 'GLOBAL')) then
205 x_user_name_bind := ''''|| replace(p_user_name, '''','''''')||'''';
206 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
207 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
208 c_log_head || l_api_name || '.end_quick',
209 'returning NULLs for user_name bind.');
210 end if;
211 return;
212 end if;
213
214 if (p_user_name = SYS_CONTEXT('FND','USER_NAME')) then
215 x_user_name_bind := 'SYS_CONTEXT(''FND'',''USER_NAME'')';
216 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
217 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
218 c_log_head || l_api_name || '.end_global_user_name',
219 'returning x_user_name_bind:' || x_user_name_bind);
220 end if;
221 return;
222 else
223 x_user_name_bind := ''''||replace(p_user_name, '''', '''''')||'''';
224 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
225 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
226 c_log_head || l_api_name || '.end_literal',
227 'returning x_user_name_bind:' || x_user_name_bind);
228 end if;
229 return;
230 end if;
231
232 /* This line should never be reached. */
233 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
234 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
235 c_log_head || l_api_name || '.end_interr',
236 'Internal Error. This line should not be executed.');
237 end if;
238 x_user_name_bind := 'ERROR_IN_GET_NAME_BIND';
239 return;
240
241 end;
242
243
244
245 ---This is an internal procedure. Not for general use.
246 -- Gets the orig_system_id and orig_system from wf_roles,
247 -- given the user_name.
248 -- This is around mostly for backward compatibility with our
249 -- grants loader, but we may eliminate even that use and this
250 -- routine may disappear entirely, so outside code should
251 -- not call it or their code will break in the future.
252 -----------------------------------------------
253 -- DEPRECATED DEPRECATED DEPRECATED DEPRECATED DEPRECATED
254 procedure get_orig_key(p_user_name in VARCHAR2,
255 x_orig_system out NOCOPY varchar2,
256 x_orig_system_id out NOCOPY NUMBER)
257 is
258 l_api_name CONSTANT VARCHAR2(30) := 'GET_ORIG_KEY';
259 colon pls_integer;
260 begin
261 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
262 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
263 c_log_head || l_api_name || '.begin',
264 c_pkg_name || '.' ||l_api_name|| '(' ||
265 'p_user_name =>'|| p_user_name ||');');
266 end if;
267
268 if (fnd_data_security.DISALLOW_DEPRECATED = 'Y') then
269 /* In R12 this routine is deprecated, because it effectively */
270 /* does a blind query, potentially returning zillions of */
271 /* records, which is unsupportable from a performance */
272 /* perspective. */
273 /* So we raise a runtime exception to help people to know */
274 /* they need to change their code. */
275 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
276 fnd_message.set_token('ROUTINE',
277 c_pkg_name || '.'|| l_api_name);
278 fnd_message.set_token('REASON',
279 'Invalid API call. API '
280 ||c_pkg_name || '.'|| l_api_name ||
281 ' is desupported and should not be called in R12.'||
282 ' Any product team that calls it '||
283 'must correct their code because it does not work '||
284 'correctly. Please see the deprecated API document at '||
285 'http://files.oraclecorp.com/content/AllPublic/'||
286 'SharedFolders/ATG%20Requirements-Public/R12/'||
287 'Requirements%20Definition%20Document/'||
288 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
289 'Oracle employees who encounter this error should log '||
290 'a bug against the product that owns the call to this '||
291 'routine');
292 if (fnd_log.LEVEL_EXCEPTION >=
293 fnd_log.g_current_runtime_level) then
294 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
295 c_log_head || l_api_name || '.end_unsupported',
296 FALSE);
297 end if;
298 fnd_message.raise_error;
299 end if;
300
301 x_orig_system := NULL;
302 x_orig_system_id := NULL;
303
304 /* Note that this logic is written to accomodate VGEORGE's case where*/
305 /* the grantee_type is 'GLOBAL' but the grantee_key is something */
306 /* parsable. */
307 if ((p_user_name is NULL) or (p_user_name = 'GLOBAL')) then
308 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
309 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
310 c_log_head || l_api_name || '.end_quick',
311 'returning NULLs for x_orig_system and x_orig_system_id');
312 end if;
313 return;
314 end if;
315
316
317 /* This routine may not perform as well as the old implementation of*/
318 /* our get_orig_key() but since it's only called when uploading grants*/
322 Orig_System => x_orig_system,
319 /* now this should suffice. */
320 wf_directory.GetRoleOrigSysInfo(
321 Role => p_user_name,
323 Orig_System_Id => x_orig_system_id);
324
325 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
326 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
327 c_log_head || l_api_name || '.end',
328 'returning x_orig_system:' || x_orig_system ||
329 'x_orig_system_id:'||to_char(x_orig_system_id));
330 end if;
331 end;
332
333
334
335 ---This is an internal function. Not in spec
336 ---Function get_object_id
337 ------------------------------
338 Function get_object_id(p_object_name in varchar2
339 ) return number is
340 v_object_id number;
341 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_ID';
342 Begin
343 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
344 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
345 c_log_head || l_api_name || '.begin',
346 c_pkg_name || '.' ||l_api_name|| '(' ||
347 'p_object_name =>'|| p_object_name ||');');
348 end if;
349 if (p_object_name = g_obj_name_cache) then
350 v_object_id := g_obj_id_cache; /* If we have it cached, use value */
351 else /* not cached, hit db */
352 select object_id
353 into v_object_id
354 from fnd_objects
355 where obj_name=p_object_name;
356
357 /* Store in cache */
358 g_obj_id_cache := v_object_id;
359 g_obj_name_cache := p_object_name;
360 end if;
361
362 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
363 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
364 c_log_head || l_api_name || '.end',
365 'returning v_object_id:' || v_object_id);
366 end if;
367 return v_object_id;
368 exception
369 when no_data_found then
370 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
371 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
372 c_log_head || l_api_name || '.end_null',
373 'returning null');
374 end if;
375 return null;
376 end;
377
378 ---This is an internal function. Not in spec
379 ---Function get_function_id
380 ------------------------------
381 Function get_function_id(p_function_name in varchar2
382 ) return number is
383 v_function_id number;
384 l_api_name CONSTANT VARCHAR2(30) := 'GET_FUNCTION_ID';
385 Begin
386 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
387 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
388 c_log_head || l_api_name || '.begin',
389 c_pkg_name || '.' ||l_api_name|| '(' ||
390 'p_function_name =>'|| p_function_name ||');');
391 end if;
392
393 if (p_function_name = g_func_name_cache) then
394 v_function_id := g_func_id_cache; /* If we have it cached, use value */
395 else /* not cached, hit db */
396 select function_id
397 into v_function_id
398 from fnd_form_functions
399 where function_name=p_function_name;
400
401 /* Store in cache */
402 g_func_id_cache := v_function_id;
403 g_func_name_cache := p_function_name;
404 end if;
405
406
407 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
408 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
409 c_log_head || l_api_name || '.end',
410 'returning v_function_id:' || v_function_id);
411 end if;
412 return v_function_id;
413 exception
414 when no_data_found then
415 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
416 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
417 c_log_head || l_api_name || '.end_null',
418 'returning null');
419 end if;
420 return null;
421 end;
422
423 -- get_to_char-
424 -- This is an internal procedure not in spec.
425 ------------------------------
426 function get_to_char(x_column_name in varchar2,
427 x_column_type in varchar2) return varchar2 is
428 retval varchar2(255);
429 begin
430 /* INTEGER type... no format mask needed. NUMBER is an obsolete type */
431 /* that we support like INTEGER for backward compatibility. */
432 if (x_column_type = 'INTEGER') or (x_column_type = 'NUMBER') then
433 retval := 'TO_CHAR('||x_column_name||')';
434 elsif (x_column_type = 'FLOAT') then
435 retval := 'TO_CHAR('||x_column_name||
436 ', ''FM999999999999999999999.99999999999999999999'')';
437 elsif (x_column_type = 'DATE') then
438 retval := 'TO_CHAR('||x_column_name||', ''YYYY/MM/DD HH24:MI:SS'')';
439 else
440 retval := '/* ERROR_UNK_TYPE:'||x_column_type
441 ||' */ TO_CHAR('||x_column_name||')';
442 end if;
443 return retval;
444 end;
445
446 ---This is an internal procedure. Not in spec.
447 -- Procedure get_pk_information
448 -- x_pk_column returns the aliased list of columns without
449 -- type conv.
450 -- x_ik_clause returns the clause of the where statement for
454 -- (TO_NUMBER(gnt.instance_pk1_value) = objtab.pk_id)
451 -- instance grants, which looks something like this:
452 -- ((gnt.instance_type = 'INSTANCE')
453 -- AND
455 -- AND
456 -- (TO_NUMBER(gnt.instance_pk2_value) = objtab.pk_app_id)
457 -- )
458 -- x_orig_pk_column returns the list of columns
459 -- without the table aliases (X.)
460 -- returns 'T' for success or 'U' for unexpected error.
461 -----------------------------------------------
462 function get_pk_information(p_object_name in VARCHAR2,
463 x_pk1_column_name out NOCOPY varchar2,
464 x_pk2_column_name out NOCOPY varchar2,
465 x_pk3_column_name out NOCOPY varchar2,
466 x_pk4_column_name out NOCOPY varchar2,
467 x_pk5_column_name out NOCOPY varchar2,
468 x_pk_column out NOCOPY varchar2,
469 x_ik_clause out NOCOPY varchar2,
470 x_exact_clause out NOCOPY varchar2,
471 x_orig_pk_column out NOCOPY varchar2,
472 x_database_object_name out NOCOPY varchar2,
473 x_table_alias in varchar2,
474 x_grant_alias in varchar2)
475 return VARCHAR2 IS
476 l_api_name CONSTANT VARCHAR2(30) := 'GET_PK_INFORMATION';
477 x_pk1_column_type varchar2(8);
478 x_pk2_column_type varchar2(8);
479 x_pk3_column_type varchar2(8);
480 x_pk4_column_type varchar2(8);
481 x_pk5_column_type varchar2(8);
482 l_table_alias varchar2(255);
483 l_grant_alias varchar2(255);
484 cursor c_pk is
485 SELECT pk1_column_name
486 ,pk2_column_name
487 ,pk3_column_name
488 ,pk4_column_name
489 ,pk5_column_name
490 ,pk1_column_type
491 ,pk2_column_type
492 ,pk3_column_type
493 ,pk4_column_type
494 ,pk5_column_type
495 , database_object_name
496 FROM fnd_objects
497 WHERE obj_name=p_object_name ;
498 begin
499 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
500 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
501 c_log_head || l_api_name || '.begin',
502 c_pkg_name || '.' ||l_api_name|| '(' ||
503 'p_object_name=>'|| p_object_name ||
504 'x_table_alias=>'|| x_table_alias ||
505 'x_grant_alias=>'|| x_grant_alias
506 ||');');
507 end if;
508
509 if(x_table_alias is NULL) then
510 l_table_alias := NULL;
511 else
512 l_table_alias := x_table_alias || '.';
513 end if;
514
515 if(x_grant_alias is NULL) then
516 l_grant_alias := NULL;
517 else
518 l_grant_alias := x_grant_alias || '.';
519 end if;
520
521 open c_pk;
522 fetch c_pk into
523 x_pk1_column_name ,
524 x_pk2_column_name ,
525 x_pk3_column_name ,
526 x_pk4_column_name ,
527 x_pk5_column_name ,
528 x_pk1_column_type ,
529 x_pk2_column_type ,
530 x_pk3_column_type ,
531 x_pk4_column_type ,
532 x_pk5_column_type ,
533 x_database_object_name;
534
535 IF(c_pk%NOTFOUND) THEN
536 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
537 fnd_message.set_token('ROUTINE',
538 c_pkg_name || '.'|| l_api_name);
539 fnd_message.set_token('REASON',
540 'FND_OBJECTS does not have column obj_name with value:'||
541 p_object_name);
542 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
543 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
544 c_log_head || l_api_name || '.bad_objname',
545 FALSE);
546 end if;
547 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
548 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
549 c_log_head || l_api_name || '.end_bad_objname',
550 'returning: ' ||'U');
551 end if;
552 return 'U';
553 end if;
554
555 CLOSE c_pk;
556
557 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
558 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
559 c_log_head || l_api_name || '.after_fetch',
560 ' x_pk1_column_name: '|| x_pk1_column_name||
561 ' x_pk2_column_name: '|| x_pk2_column_name||
562 ' x_pk3_column_name: '|| x_pk3_column_name||
563 ' x_pk4_column_name: '|| x_pk4_column_name||
564 ' x_pk5_column_name: '|| x_pk5_column_name||
565 ' x_pk1_column_type: '|| x_pk1_column_type||
566 ' x_pk2_column_type: '|| x_pk2_column_type||
567 ' x_pk3_column_type: '|| x_pk3_column_type||
568 ' x_pk4_column_type: '|| x_pk4_column_type||
569 ' x_pk5_column_type: '|| x_pk5_column_type||
570 ' x_database_object_name: '|| x_database_object_name);
571 end if;
572
573
574 -- Build up the list of column names without 'X.' (table alias)
575 x_orig_pk_column := NULL;
576 if( (x_pk1_column_name is not NULL)
577 AND (x_pk1_column_name <> C_NULL_STR)) then
578 x_orig_pk_column := x_orig_pk_column ||x_pk1_column_name;
579 end if;
583 end if;
580 if( (x_pk2_column_name is not NULL)
581 AND (x_pk2_column_name <> C_NULL_STR)) then
582 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk2_column_name;
584 if( (x_pk3_column_name is not NULL)
585 AND (x_pk3_column_name <> C_NULL_STR)) then
586 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk3_column_name;
587 end if;
588 if( (x_pk4_column_name is not NULL)
589 AND (x_pk4_column_name <> C_NULL_STR)) then
590 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk4_column_name;
591 end if;
592 if( (x_pk5_column_name is not NULL)
593 AND (x_pk5_column_name <> C_NULL_STR)) then
594 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk5_column_name;
595 end if;
596
597
598
599 -- Build up the x_pk_column and x_ik_clause lists
600 -- by adding values for each column name.
601 x_ik_clause := '(('||l_grant_alias||'INSTANCE_TYPE = ''INSTANCE'')';
602 x_exact_clause := '(';
603
604 if ( (x_pk1_column_name is not null)
605 AND (x_pk1_column_name <> C_NULL_STR))then
606 x_pk_column := x_pk_column||l_table_alias||x_pk1_column_name;
607 x_ik_clause := x_ik_clause||' AND ('||l_grant_alias
608 ||'INSTANCE_PK1_VALUE' ||
609 ' = '||get_to_char(l_table_alias|| x_pk1_column_name,
610 x_pk1_column_type)
611 || ')';
612 x_exact_clause := x_exact_clause||
613 ' ( :pk1 = '||l_table_alias|| x_pk1_column_name || ')';
614 if ( (x_pk2_COLUMN_name is not null)
615 AND (x_pk2_column_name <> C_NULL_STR)) then
616 x_pk_column:=
617 x_pk_column||', '||l_table_alias||x_pk2_COLUMN_name;
618 x_ik_clause := x_ik_clause||' AND ('||
619 l_grant_alias ||'INSTANCE_PK2_VALUE'||
620 ' = '||get_to_char(l_table_alias
621 || x_pk2_column_name, x_pk2_column_type)
622 || ')';
623 x_exact_clause := x_exact_clause||
624 ' AND ( :pk2 = '||l_table_alias
625 || x_pk2_column_name || ')';
626 if ( (x_pk3_COLUMN_name is not null)
627 AND (x_pk3_column_name <> C_NULL_STR)) then
628 x_pk_column :=
629 x_pk_column||', '||l_table_alias||x_pk3_COLUMN_name;
630 x_ik_clause := x_ik_clause||' AND ('||l_grant_alias
631 ||'INSTANCE_PK3_VALUE'||
632 ' = '||get_to_char(l_table_alias||
633 x_pk3_column_name,
634 x_pk3_column_type)
635 || ')';
636 x_exact_clause := x_exact_clause||
637 ' AND ( :pk3 = '||l_table_alias
638 || x_pk3_column_name || ')';
639 if ( (x_pk4_COLUMN_name is not null)
640 AND (x_pk4_column_name <> C_NULL_STR)) then
641 x_pk_column:=
642 x_pk_column||', '||l_table_alias||x_pk4_COLUMN_name;
643 x_ik_clause := x_ik_clause||' AND ('||
644 l_grant_alias
645 ||'INSTANCE_PK4_VALUE'||
646 ' = '||get_to_char(l_table_alias||
647 x_pk4_column_name,
648 x_pk4_column_type)
649 || ')';
650 x_exact_clause := x_exact_clause||
651 ' AND ( :pk4 = '||l_table_alias
652 || x_pk4_column_name || ')';
653 if ( (x_pk5_COLUMN_name is not null)
654 AND (x_pk5_column_name <> C_NULL_STR)) then
655 x_pk_column:=
656 x_pk_column||', '||l_table_alias||x_pk5_COLUMN_name;
657 x_ik_clause := x_ik_clause||' AND ('|| l_grant_alias
658 ||'INSTANCE_PK5_VALUE'||
659 ' = '||get_to_char(l_table_alias|| x_pk5_column_name,
660 x_pk5_column_type)
661 || ')';
662 x_exact_clause := x_exact_clause||
663 ' AND ( :pk5 = '||l_table_alias
664 || x_pk5_column_name || ')';
665 end if;
666 end if;
667 end if;
668 end if;
669 end if;
670
671 x_ik_clause := x_ik_clause||' )';
672 x_exact_clause := x_exact_clause||' )';
673
674 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
675 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
676 c_log_head || l_api_name || '.end',
677 'returning: ' ||
678 ' p_object_name=>'|| p_object_name ||','||
679 ' x_pk1_column_name=>'|| x_pk1_column_name ||','||
680 ' x_pk2_column_name=>'|| x_pk2_column_name ||','||
681 ' x_pk3_column_name=>'|| x_pk3_column_name ||','||
682 ' x_pk4_column_name=>'|| x_pk4_column_name ||','||
683 ' x_pk5_column_name=>'|| x_pk5_column_name ||','||
684 ' x_pk_column=>'|| x_pk_column ||','||
685 ' x_ik_clause=>'|| x_ik_clause ||','||
686 ' x_exact_clause=>'|| x_exact_clause ||','||
690 return 'T';
687 ' x_orig_pk_column=>'|| x_orig_pk_column ||','||
688 ' x_database_object_name=>'|| x_database_object_name);
689 end if;
691 end;
692
693 ------Function check_global_object_grant-------------
694 -- Is a particular function granted globally to all objects or a
695 -- particular object in the current context?
696 -- passing 'GLOBAL' for object_name means check global object type grants
697 FUNCTION check_global_object_grant
698 (
699 p_api_version IN NUMBER,
700 p_function IN VARCHAR2,
701 p_user_name in varchar2,
702 p_object_name in varchar2
703 )
704 RETURN VARCHAR2 IS
705
706 l_api_version CONSTANT NUMBER := 1.0;
707 l_api_name CONSTANT VARCHAR2(30) :='CHECK_GLOBAL_OBJECT_GRANT';
708 l_sysdate DATE := Sysdate;
709 dummy_item_id NUMBER;
710 l_result VARCHAR2(1);
711 l_return_status varchar2(1);
712 result varchar2(30);
713 l_nrows pls_integer;
714 l_object_id NUMBER;
715 colon pls_integer;
716 l_function_id number;
717 l_user_name varchar2(80);
718
719 -- Performance bug 5080621. Flipped the order of 'union all' to do
720 -- select 'GLOBAL' from dual first.
721 -- Done similar changes in the following cursors also.
722 -- instance_set_grants_c (in get_security_predicate_helper api)
723 -- isg_grp_glob_fn_c (in get_security_predicate_intrnl api)
724 -- isg_grp_glob_nofn_c (in get_security_predicate_intrnl api)
725
726 CURSOR global_grants_c( cp_user_name varchar2,
727 cp_function_id NUMBER,
728 cp_sysdate DATE,
729 cp_object_id in NUMBER
730 ) IS
731 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
732 FROM
733 ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
734 union all
735 select role_name
736 from wf_user_roles wur,
737 (
738 select cp_user_name name from dual
739 union all
740 select incr1.name name
741 from wf_local_roles incr1, fnd_user u1
742 where 'HZ_PARTY' = incr1.orig_system
743 and u1.user_name = cp_user_name
744 and u1.person_party_id = incr1.orig_system_id
745 and incr1.partition_id = 9 /* HZ_PARTY */
746 ) incr2
747 where wur.user_name = incr2.name
748 ) u2,
749 fnd_grants g
750 WHERE rownum = 1
751 AND g.grantee_key = u2.role_name
752 AND g.menu_id in
753 (select cmf.menu_id
754 from fnd_compiled_menu_functions cmf
755 where cmf.function_id = cp_function_id)
756 AND( g.ctx_secgrp_id = -1
757 OR g.ctx_secgrp_id = SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
758 AND( g.ctx_resp_id = -1
759 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
760 AND( g.ctx_resp_appl_id = -1
761 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
762 AND( g.ctx_org_id = -1
763 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
764 AND
765 ( g.end_date IS NULL
766 OR g.end_date >= cp_sysdate )
767 AND
768 g.start_date <= cp_sysdate
769 AND
770 ( (g.instance_type = 'GLOBAL')
771 AND (g.object_id = cp_object_id))
772 ;
773
774 --Changes for Bug#3867925
775 -- Fix Non Backward change made for universal person support
776 --
777 -- Performance note: This statement has not received the optimizations
778 -- to the WF User portion of the SQL because the separation of the
779 -- USER and GROUP clauses prevent that. Since this is only used for
780 -- deprecated code that is okay.
781 CURSOR global_grants_bkwd_c ( cp_user_name varchar2,
782 cp_function_id NUMBER,
783 cp_sysdate DATE,
784 cp_object_id in NUMBER
785 )
786 IS
787 SELECT 'X'
788 FROM fnd_grants g
789 WHERE rownum = 1
790 AND(
791 ( g.grantee_type = 'USER'
792 and g.grantee_key = cp_user_name)
793 OR (g.grantee_type = 'GROUP'
794 and (g.grantee_key in
795 (select role_name
796 from wf_user_roles wur
797 where wur.user_name in
798 ( (select cp_user_name from dual)
799 union all
800 (select incrns.name from wf_local_roles incrns, fnd_user f
801 where 'HZ_PARTY' = incrns.orig_system
802 and f.user_name = cp_user_name
803 and f.person_party_id = incrns.orig_system_id)))))
804 OR (g.grantee_type = 'GLOBAL'))
805 AND g.menu_id in
806 (select cmf.menu_id
807 from fnd_compiled_menu_functions cmf
811 AND( g.ctx_resp_id = -1
808 where cmf.function_id = cp_function_id)
809 AND( g.ctx_secgrp_id = -1
810 OR g.ctx_secgrp_id = SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
812 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
813 AND( g.ctx_resp_appl_id = -1
814 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
815 AND( g.ctx_org_id = -1
816 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
817 AND
818 ( g.end_date IS NULL
819 OR g.end_date >= cp_sysdate )
820 AND
821 g.start_date <= cp_sysdate
822 AND
823 ( (g.instance_type = 'GLOBAL')
824 AND (g.object_id = cp_object_id));
825
826
827 -- This cursor is the same as the global_grants_c above except the
828 -- clause for GLOBAL grantee_key is removed, because the guest
829 -- user should not have access to global grants.
830 CURSOR global_grants_guest_c ( cp_user_name varchar2,
831 cp_function_id NUMBER,
832 cp_sysdate DATE,
833 cp_object_id in NUMBER
834 )
835 IS
836 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
837 FROM
838 ( select /*+ NO_MERGE */ role_name
839 from wf_user_roles wur,
840 (
841 select cp_user_name name from dual
842 union all
843 select incr1.name name
844 from wf_local_roles incr1, fnd_user u1
845 where 'HZ_PARTY' = incr1.orig_system
846 and u1.user_name = cp_user_name
847 and u1.person_party_id = incr1.orig_system_id
848 and incr1.partition_id = 9 /* HZ_PARTY */
849 ) incr2
850 where wur.user_name = incr2.name
851 ) u2,
852 fnd_grants g
853 WHERE rownum = 1
854 AND g.grantee_key = u2.role_name
855 AND g.menu_id in
856 (select cmf.menu_id
857 from fnd_compiled_menu_functions cmf
858 where cmf.function_id = cp_function_id)
859 AND( g.ctx_secgrp_id = -1
860 OR g.ctx_secgrp_id = SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
861 AND( g.ctx_resp_id = -1
862 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
863 AND( g.ctx_resp_appl_id = -1
864 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
865 AND( g.ctx_org_id = -1
866 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
867 AND
868 ( g.end_date IS NULL
869 OR g.end_date >= cp_sysdate )
870 AND
871 g.start_date <= cp_sysdate
872 AND
873 ( (g.instance_type = 'GLOBAL')
874 AND (g.object_id = cp_object_id))
875 ;
876
877 BEGIN
878 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
879 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
880 c_log_head || l_api_name || '.begin',
881 c_pkg_name || '.' ||l_api_name|| '(' ||
882 ' p_api_version=>'|| to_char(p_api_version) ||','||
883 ' p_function=>'|| p_function ||','||
884 ' p_user_name=>'|| p_user_name ||');');
885 end if;
886
887 -- check for call compatibility.
888 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
889 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
890 fnd_message.set_token('ROUTINE',
891 c_pkg_name || '.'|| l_api_name);
892 fnd_message.set_token('REASON',
893 'Unsupported version '|| to_char(p_api_version)||
894 ' passed to API; expecting version '||
895 to_char(l_api_version));
896 if (fnd_log.LEVEL_EXCEPTION >=
897 fnd_log.g_current_runtime_level) then
898 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
899 c_log_head || l_api_name || '.end_bad_api_ver',
900 FALSE);
901 end if;
902 return 'U';
903 END IF;
904
905 -- Check for null arguments.
906 if (p_function is NULL) THEN
907 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
908 fnd_message.set_token('ROUTINE',
909 c_pkg_name || '.'|| l_api_name);
910 fnd_message.set_token('REASON',
911 'NULL value passed for p_function:'||p_function);
912
913 if (fnd_log.LEVEL_EXCEPTION >=
914 fnd_log.g_current_runtime_level) then
915 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
916 c_log_head || l_api_name || '.end_bad_param',
917 FALSE);
918 end if;
919 return 'U';
920 END IF;
921
922 /* Make sure that the FND_COMPILED_MENU_FUNCTIONS table is compiled */
923 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
924 FND_FUNCTION.FAST_COMPILE;
925 end if;
926
927 /* Convert object name to id */
931 l_object_id := get_object_id(p_object_name);
928 if (p_object_name = 'GLOBAL') then
929 l_object_id := -1;
930 else
932 if (l_object_id is NULL) THEN
933 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
934 fnd_message.set_token('ROUTINE',
935 c_pkg_name || '.'|| l_api_name);
936 fnd_message.set_token('REASON',
937 'The parameter value p_object_name is not a valid object.'||
938 ' p_object_name:'||p_object_name);
939 if (fnd_log.LEVEL_EXCEPTION >=
940 fnd_log.g_current_runtime_level) then
941 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
942 c_log_head || l_api_name || '.end_bad_obj',
943 FALSE);
944 end if;
945 return 'U';
946 END IF;
947 end if;
948
949 -- Default the user name if not passed in.
950 if(p_user_name is NULL) then
951 l_user_name := SYS_CONTEXT('FND','USER_NAME');
952 else
953 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
954 and (substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) <>
955 'GET_MNUIDS_NBVCXDS')
956 and ( (p_user_name <> SYS_CONTEXT('FND','USER_NAME'))
957 or ( (p_user_name is not null)
958 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
959 /* In R12 we do not allow passing values other than */
960 /* the current user name (which is the default), */
961 /* so we raise a runtime exception if that deprecated */
962 /* kind of call is made to this routine. */
963 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
964 fnd_message.set_token('ROUTINE',
965 c_pkg_name || '.'|| l_api_name);
966 fnd_message.set_token('REASON',
967 'Invalid API call. Parameter p_user_name: '||p_user_name||
968 ' was passed to API '||c_pkg_name || '.'|| l_api_name ||
969 '. p_object_name: '||p_object_name||'. '||
970 ' In Release 12 and beyond the p_user_name parameter '||
971 'is unsupported, and any product team that passes it '||
972 'must correct their code because it does not work '||
973 'correctly. Please see the deprecated API document at '||
974 'http://files.oraclecorp.com/content/AllPublic/'||
975 'SharedFolders/ATG%20Requirements-Public/R12/'||
976 'Requirements%20Definition%20Document/'||
977 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
978 'Oracle employees who encounter this error should log '||
979 'a bug against the product that owns the call to this '||
980 'routine, which is likely the owner of the object that '||
981 'was passed to this routine: '||
982 p_object_name);
983 if (fnd_log.LEVEL_EXCEPTION >=
984 fnd_log.g_current_runtime_level) then
985 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
986 c_log_head || l_api_name || '.end_depr_param',
987 FALSE);
988 end if;
989 fnd_message.raise_error;
990 end if;
991 l_user_name := p_user_name;
992 end if;
993
994 -- look up function id from function name
995 l_function_id:=get_function_id(p_function);
996 if (l_function_id is NULL) THEN
997 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
998 fnd_message.set_token('ROUTINE',
999 c_pkg_name || '.'|| l_api_name);
1000 fnd_message.set_token('REASON',
1001 'The parameter value p_function is not a valid function name.'||
1002 ' p_function:'||p_function);
1003 if (fnd_log.LEVEL_EXCEPTION >=
1004 fnd_log.g_current_runtime_level) then
1005 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1006 c_log_head || l_api_name || '.end_bad_func',
1007 FALSE);
1008 end if;
1009 return 'U';
1010 END IF;
1011
1012 IF (l_user_name <> 'GUEST') THEN
1013 --Changes for Bug#3867925
1014 -- Fix Non Backward change made for universal person support
1015 colon := instr(p_user_name, 'PER:');
1016 if (colon <> 0) then
1017 OPEN global_grants_bkwd_c (cp_user_name => l_user_name,
1018 cp_function_id => l_function_id,
1019 cp_sysdate => l_sysdate,
1020 cp_object_id => l_object_id);
1021
1022 if (fnd_log.LEVEL_STATEMENT >=
1023 fnd_log.g_current_runtime_level) then
1024 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1025 c_log_head||l_api_name||
1026 '.open_grants_bkwd_cursor',
1027 ' cp_user_name: '|| l_user_name||
1028 ' cp_function_id: '|| l_function_id||
1029 ' l_sysdate: '|| to_char(l_sysdate)||
1033 FETCH global_grants_bkwd_c INTO l_result;
1030 ' cp_object_id: '|| l_object_id);
1031 end if;
1032
1034 CLOSE global_grants_bkwd_c;
1035 else
1036 OPEN global_grants_c (cp_user_name => l_user_name,
1037 cp_function_id => l_function_id,
1038 cp_sysdate => l_sysdate,
1039 cp_object_id => l_object_id);
1040 if (fnd_log.LEVEL_STATEMENT >=
1041 fnd_log.g_current_runtime_level) then
1042 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1043 c_log_head || l_api_name || '.open_grants_cursor',
1044 ' cp_user_name: '|| l_user_name||
1045 ' cp_function_id: '|| l_function_id||
1046 ' l_sysdate: '|| to_char(l_sysdate)||
1047 ' cp_object_id: '|| l_object_id);
1048 end if;
1049
1050 FETCH global_grants_c INTO l_result;
1051 CLOSE global_grants_c;
1052 end if;
1053 ELSE
1054 OPEN global_grants_guest_c (cp_user_name => l_user_name,
1055 cp_function_id => l_function_id,
1056 cp_sysdate => l_sysdate,
1057 cp_object_id => l_object_id);
1058 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1059 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1060 c_log_head || l_api_name || '.open_grants_guest_cursor',
1061 ' cp_user_name: '|| l_user_name||
1062 ' cp_function_id: '|| l_function_id||
1063 ' l_sysdate: '|| to_char(l_sysdate)||
1064 ' cp_object_id: '|| l_object_id);
1065 end if;
1066
1067 FETCH global_grants_guest_c INTO l_result;
1068 CLOSE global_grants_guest_c;
1069
1070 END IF;
1071
1072 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1073 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1074 c_log_head || l_api_name || '.fetch_instance_grants',
1075 ' l_result:'||l_result);
1076 end if;
1077 IF (l_result = 'X') THEN
1078 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1079 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1080 c_log_head || l_api_name || '.end_inst_grant',
1081 'T');
1082 end if;
1083 RETURN 'T';
1084 ELSE
1085
1086 if (fnd_log.LEVEL_PROCEDURE >=
1087 fnd_log.g_current_runtime_level) then
1088 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1089 c_log_head || l_api_name || '.end_no_inst','F');
1090 end if;
1091 RETURN 'F';
1092 END IF;
1093
1094
1095 EXCEPTION
1096 /* If API called with deprecated p_user_name arg, */
1097 /* propagate that up so the caller gets exception */
1098 WHEN FND_MESSAGE_RAISED_ERR THEN
1099 /* Re raise the error for the caller */
1100 fnd_message.raise_error;
1101 return 'U'; /* This line should never be executed */
1102
1103 WHEN OTHERS THEN
1104 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1105 fnd_message.set_token('ROUTINE',
1106 c_pkg_name||','||l_api_name);
1107 fnd_message.set_token('ERRNO', SQLCODE);
1108 fnd_message.set_token('REASON', SQLERRM);
1109
1110 if (fnd_log.LEVEL_EXCEPTION >=
1111 fnd_log.g_current_runtime_level) then
1112 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1113 c_log_head || l_api_name || '.other_err',
1114 FALSE);
1115 end if;
1116 if (fnd_log.LEVEL_PROCEDURE >=
1117 fnd_log.g_current_runtime_level) then
1118 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1119 c_log_head || l_api_name || '.end_after_other',
1120 'U' );
1121 end if;
1122 RETURN 'U';
1123 END check_global_object_grant;
1124 -----------------------------------------------------------------
1125
1126 ------Function check_global_object_type_grant-------------
1127 -- Is a particular function granted globally to all objects in
1128 -- the current context?
1129 FUNCTION check_global_object_type_grant
1130 (
1131 p_api_version IN NUMBER,
1132 p_function IN VARCHAR2,
1133 p_user_name in varchar2
1134 )
1135 RETURN VARCHAR2 IS
1136
1137 l_api_version CONSTANT NUMBER := 1.0;
1138 l_api_name CONSTANT VARCHAR2(30) :=
1139 'CHECK_GLOBAL_OBJECT_TYPE_GRANT';
1140 l_result varchar2(30);
1141 BEGIN
1142 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1143 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1144 c_log_head || l_api_name || '.begin',
1145 c_pkg_name || '.' ||l_api_name|| '(' ||
1146 ' p_api_version=>'|| to_char(p_api_version) ||','||
1147 ' p_function=>'|| p_function ||','||
1148 ' p_user_name=>'|| p_user_name ||');');
1149 end if;
1150
1151 -- check for call compatibility.
1155 c_pkg_name || '.'|| l_api_name);
1152 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
1153 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1154 fnd_message.set_token('ROUTINE',
1156 fnd_message.set_token('REASON',
1157 'Unsupported version '|| to_char(p_api_version)||
1158 ' passed to API; expecting version '||
1159 to_char(l_api_version));
1160 if (fnd_log.LEVEL_EXCEPTION >=
1161 fnd_log.g_current_runtime_level) then
1162 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1163 c_log_head || l_api_name || '.end_bad_api_ver',
1164 FALSE);
1165 end if;
1166 return 'U';
1167 END IF;
1168
1169 l_result := check_global_object_grant
1170 ( 1.0,
1171 p_function,
1172 p_user_name,
1173 'GLOBAL');
1174
1175 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1176 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1177 c_log_head || l_api_name || '.end',
1178 l_result);
1179 end if;
1180
1181 RETURN l_result;
1182 END check_global_object_type_grant;
1183 -----------------------------------------------------------------
1184
1185 ------Function check_function-------------
1186 FUNCTION check_function
1187 (
1188 p_api_version IN NUMBER,
1189 p_function IN VARCHAR2,
1190 p_object_name IN VARCHAR2,
1191 p_instance_pk1_value IN VARCHAR2,
1192 p_instance_pk2_value IN VARCHAR2,
1193 p_instance_pk3_value IN VARCHAR2,
1194 p_instance_pk4_value IN VARCHAR2,
1195 p_instance_pk5_value IN VARCHAR2,
1196 p_user_name in varchar2
1197 )
1198 RETURN VARCHAR2 IS
1199
1200 l_api_version CONSTANT NUMBER := 1.0;
1201 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_FUNCTION';
1202 l_sysdate DATE := Sysdate;
1203 l_predicate VARCHAR2(32767);
1204 dummy_item_id NUMBER;
1205 dynamic_sql VARCHAR2(32767);
1206 l_db_object_name varchar2(30);
1207 l_db_pk1_column varchar2(256);
1208 l_db_pk2_column varchar2(256);
1209 l_db_pk3_column varchar2(256);
1210 l_db_pk4_column varchar2(256);
1211 l_db_pk5_column varchar2(256);
1212 l_pk_column_names varchar2(512);
1213 l_pk_orig_column_names varchar2(512);
1214 l_ik_clause varchar2(2048);
1215 l_exact_clause varchar2(2048);
1216 l_result VARCHAR2(1);
1217 l_return_status varchar2(1);
1218 result varchar2(30);
1219 l_nrows pls_integer;
1220 l_instance_pk1_value varchar2(256);
1221 l_instance_pk2_value varchar2(256);
1222 l_instance_pk3_value varchar2(256);
1223 l_instance_pk4_value varchar2(256);
1224 l_instance_pk5_value varchar2(256);
1225 l_bind_pk1 boolean := FALSE;
1226 l_bind_pk2 boolean := FALSE;
1227 l_bind_pk3 boolean := FALSE;
1228 l_bind_pk4 boolean := FALSE;
1229 l_bind_pk5 boolean := FALSE;
1230
1231 l_object_id number := NULL;
1232 l_function_id number := NULL;
1233 l_user_name varchar2(80);
1234 l_cursor int;
1235 l_rows_processed number;
1236 i number;
1237 l_bind_order varchar2(256);
1238 BEGIN
1239
1240 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1241 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1242 c_log_head || l_api_name || '.begin',
1243 c_pkg_name || '.' ||l_api_name|| '(' ||
1244 ' p_api_version=>'|| to_char(p_api_version) ||','||
1245 ' p_function=>'|| p_function ||','||
1246 ' p_object_name=>'|| p_object_name ||','||
1247 ' p_instance_pk1_value=>'|| p_instance_pk1_value ||','||
1248 ' p_instance_pk2_value=>'|| p_instance_pk2_value ||','||
1249 ' p_instance_pk3_value=>'|| p_instance_pk3_value ||','||
1250 ' p_instance_pk4_value=>'|| p_instance_pk4_value ||','||
1251 ' p_instance_pk5_value=>'|| p_instance_pk5_value ||','||
1252 ' p_user_name=>'|| p_user_name ||');');
1253 end if;
1254
1255 -- check for call compatibility.
1256 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
1257 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1258 fnd_message.set_token('ROUTINE',
1259 c_pkg_name || '.'|| l_api_name);
1260 fnd_message.set_token('REASON',
1261 'Unsupported version '|| to_char(p_api_version)||
1262 ' passed to API; expecting version '||
1263 to_char(l_api_version));
1264
1265 if (fnd_log.LEVEL_EXCEPTION >=
1266 fnd_log.g_current_runtime_level) then
1267 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1268 c_log_head || l_api_name || '.end_bad_api_ver',
1269 FALSE);
1270 end if;
1271 return 'U';
1272 END IF;
1273
1274 -- Check for null arguments.
1278 c_pkg_name || '.'|| l_api_name);
1275 if ((p_function is NULL) or (p_object_name is NULL)) THEN
1276 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1277 fnd_message.set_token('ROUTINE',
1279 fnd_message.set_token('REASON',
1280 'NULL value passed for p_function:'||p_function||
1281 ' or for p_object_name:'||p_object_name);
1282 if (fnd_log.LEVEL_EXCEPTION >=
1283 fnd_log.g_current_runtime_level) then
1284 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1285 c_log_head || l_api_name || '.end_bad_param',
1286 FALSE);
1287 end if;
1288 return 'U';
1289 END IF;
1290
1291
1292 -- Default the user name if not passed in.
1293 if(p_user_name is NULL) then
1294 l_user_name := SYS_CONTEXT('FND','USER_NAME');
1295 else
1296 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
1297 and (substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) <>
1298 'GET_MNUIDS_NBVCXDS')
1299 and ( (p_user_name <> SYS_CONTEXT('FND','USER_NAME'))
1300 or ( (p_user_name is not null)
1301 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
1302 /* In R12 we do not allow passing values other than */
1303 /* the current user name (which is the default), */
1304 /* so we raise a runtime exception if that deprecated */
1305 /* kind of call is made to this routine. */
1306 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1307 fnd_message.set_token('ROUTINE',
1308 c_pkg_name || '.'|| l_api_name);
1309 fnd_message.set_token('REASON',
1310 'Invalid API call. Parameter p_user_name: '||p_user_name||
1311 ' was passed to API '||c_pkg_name || '.'|| l_api_name ||
1312 '. p_object_name: '||p_object_name||'. '||
1313 ' In Release 12 and beyond the p_user_name parameter '||
1314 'is unsupported, and any product team that passes it '||
1315 'must correct their code because it does not work '||
1316 'correctly. Please see the deprecated API document at '||
1317 'http://files.oraclecorp.com/content/AllPublic/'||
1318 'SharedFolders/ATG%20Requirements-Public/R12/'||
1319 'Requirements%20Definition%20Document/'||
1320 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
1321 'Oracle employees who encounter this error should log '||
1322 'a bug against the product that owns the call to this '||
1323 'routine, which is likely the owner of the object that '||
1324 'was passed to this routine: '||
1325 p_object_name);
1326 if (fnd_log.LEVEL_EXCEPTION >=
1327 fnd_log.g_current_runtime_level) then
1328 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1329 c_log_head || l_api_name || '.end_depr_param',
1330 FALSE);
1331 end if;
1332 fnd_message.raise_error;
1333 end if;
1334 l_user_name := p_user_name;
1335 end if;
1336
1337 /* Make sure that the FND_COMPILED_MENU_FUNCTIONS table is compiled */
1338 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
1339 FND_FUNCTION.FAST_COMPILE;
1340 end if;
1341
1342 l_instance_pk1_value := p_instance_pk1_value;
1343 l_instance_pk2_value := p_instance_pk2_value;
1344 l_instance_pk3_value := p_instance_pk3_value;
1345 l_instance_pk4_value := p_instance_pk4_value;
1346 l_instance_pk5_value := p_instance_pk5_value;
1347
1348 /* As a special accomodation for bug 2082465, we won't compile */
1349 /* the menu_funcs if the caller guarantees they've already compiled */
1350 /* them by passing the value 'FCMF_GUARANTEED_COMPILED' for */
1351 /* p_instance_pk5_value. This is not for public use, just a */
1352 /* hackish solution to a specific need. Actually this is no longer */
1353 /* necessary since we aren't compiling inline anymore anyway, */
1354 /* but it is left in place for backward compatibility. */
1355 if(l_instance_pk5_value = 'FCMF_GUARANTEED_COMPILED') then
1356 l_instance_pk5_value := C_NULL_STR;
1357 end if;
1358
1359 /* As a special temporary accomodation for bug 2766313, */
1360 /* If the pk values passed in are all NULL, assume that the caller*/
1361 /* wants to check global object instance grants for their context. */
1362 /* This is not a feature that anyone should rely on going forward; */
1363 /* This code will be removed in 11ir2. */
1364 /* See the following document for more details: */
1365 /* http://www-apps.us.oracle.com/atg/plans/r1159/nulldatapk.txt */
1366 if ( p_instance_pk1_value is NULL
1367 AND p_instance_pk2_value is NULL
1368 AND p_instance_pk3_value is NULL
1369 AND p_instance_pk4_value is NULL
1370 AND p_instance_pk5_value is NULL) then
1371 l_result := check_global_object_grant
1372 (1.0,
1373 p_function,
1377 /* If we found a global object grant, we're done. */
1374 l_user_name,
1375 p_object_name);
1376
1378 if (l_result = 'T') then
1379 if (fnd_log.LEVEL_PROCEDURE >=
1380 fnd_log.g_current_runtime_level) then
1381 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1382 c_log_head || l_api_name || '.end_shortckt',
1383 'T');
1384 end if;
1385 return 'T';
1386 elsif (l_result <> 'F') then
1387 if (fnd_log.LEVEL_PROCEDURE >=
1388 fnd_log.g_current_runtime_level) then
1389 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1390 c_log_head || l_api_name || '.end_sckterr',
1391 l_result);
1392 end if;
1393 return l_result;
1394 end if;
1395 end if;
1396 /* End special accomodation for bug 2766313*/
1397
1398
1399 -- Get PK information
1400 result := get_pk_information(p_object_name ,
1401 l_db_pk1_column ,
1402 l_db_pk2_column ,
1403 l_db_pk3_column ,
1404 l_db_pk4_column ,
1405 l_db_pk5_column ,
1406 l_pk_column_names ,
1407 l_ik_clause ,
1408 l_exact_clause,
1409 l_pk_orig_column_names,
1410 l_db_object_name,
1411 'OBJTAB', 'GNT' );
1412 if (result <> 'T') then
1413 if (fnd_log.LEVEL_PROCEDURE >=
1414 fnd_log.g_current_runtime_level) then
1415 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1416 c_log_head || l_api_name || '.end_pk_info_err',
1417 'returning status: '|| result);
1418 end if;
1419 /* There will be a message on the msg dict stack. */
1420 return result;
1421 end if;
1422
1423 get_security_predicate_w_binds(p_api_version=>1.0,
1424 p_function =>p_function,
1425 p_object_name =>p_object_name,
1426 p_grant_instance_type =>C_TYPE_UNIVERSAL,
1427 p_user_name =>l_user_name,
1428 p_table_alias => 'CKALIAS',
1429 x_predicate=>l_predicate,
1430 x_return_status=>l_return_status,
1431 x_object_id=>l_object_id,
1432 x_function_id=>l_function_id,
1433 x_bind_order=>l_bind_order
1434 );
1435
1436 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1437 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1438 c_log_head || l_api_name || '.after_gsp',
1439 'l_predicate:'||l_predicate||
1440 'l_return_status:'||l_return_status||
1441 'l_object_id:'||l_object_id||
1442 'l_function_id:'||l_function_id);
1443 end if;
1444
1445 IF( l_return_status <> 'T' AND l_return_status <> 'F') then
1446 /* There will be a message on the stack from gsp */
1447 if (fnd_log.LEVEL_EXCEPTION >=
1448 fnd_log.g_current_runtime_level) then
1449 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1450 c_log_head || l_api_name || '.end_gsp_fail',
1451 FALSE);
1452 end if;
1453 if (l_return_status = 'L') then
1454 return 'U';
1455 else
1456 /* Else return E, or U status code, whatever gsp returned. */
1457 return l_return_status;
1458 end if;
1459 end if;
1460
1461 IF( l_return_status = 'F') then /* If there weren't enough grants */
1462 /* to make a predicate, we are done. */
1463 if (fnd_log.LEVEL_PROCEDURE >=
1464 fnd_log.g_current_runtime_level) then
1465 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1466 c_log_head || l_api_name || '.end_gsp_nopred',
1467 'F');
1468 end if;
1469 return 'F';
1470 end if;
1471
1472 IF( length(l_predicate ) >1) THEN
1473 dynamic_sql :=
1474 'SELECT '|| '1' ||
1475 ' FROM '|| l_db_object_name || ' CKALIAS'||
1476 ' WHERE ROWNUM =1 AND ';
1477 if ( (l_db_pk1_column is not NULL)
1478 AND (l_db_pk1_column <> C_NULL_STR)) then
1479 if (l_instance_pk1_value is NULL) then
1480 dynamic_sql := dynamic_sql ||
1481 ' (CKALIAS.'||l_db_pk1_column||' is NULL) ';
1482 else
1483 dynamic_sql := dynamic_sql ||
1484 ' (CKALIAS.'||l_db_pk1_column||' = :pk1_val) ';
1485 l_bind_pk1 := TRUE;
1486 end if;
1487 end if;
1488 if ( (l_db_pk2_column is not NULL)
1492 ' AND (CKALIAS.'||l_db_pk2_column||' is NULL) ';
1489 AND (l_db_pk2_column <> C_NULL_STR)) then
1490 if (l_instance_pk2_value is NULL) then
1491 dynamic_sql := dynamic_sql ||
1493 else
1494 dynamic_sql := dynamic_sql ||
1495 ' AND (CKALIAS.'||l_db_pk2_column||' = :pk2_val) ';
1496 l_bind_pk2 := TRUE;
1497 end if;
1498 end if;
1499 if ( (l_db_pk3_column is not NULL)
1500 AND (l_db_pk3_column <> C_NULL_STR)) then
1501 if (l_instance_pk3_value is NULL) then
1502 dynamic_sql := dynamic_sql ||
1503 ' AND (CKALIAS.'||l_db_pk3_column||' is NULL) ';
1504 else
1505 dynamic_sql := dynamic_sql ||
1506 ' AND (CKALIAS.'||l_db_pk3_column||' = :pk3_val) ';
1507 l_bind_pk3 := TRUE;
1508 end if;
1509 end if;
1510 if ( (l_db_pk4_column is not NULL)
1511 AND (l_db_pk4_column <> C_NULL_STR)) then
1512 if (l_instance_pk4_value is NULL) then
1513 dynamic_sql := dynamic_sql ||
1514 ' AND (CKALIAS.'||l_db_pk4_column||' is NULL) ';
1515 else
1516 dynamic_sql := dynamic_sql ||
1517 ' AND (CKALIAS.'||l_db_pk4_column||' = :pk4_val) ';
1518 l_bind_pk4 := TRUE;
1519 end if;
1520 end if;
1521 if ( (l_db_pk5_column is not NULL)
1522 AND (l_db_pk5_column <> C_NULL_STR)) then
1523 if (l_instance_pk5_value is NULL) then
1524 dynamic_sql := dynamic_sql ||
1525 ' AND (CKALIAS.'||l_db_pk5_column||' is NULL) ';
1526 else
1527 dynamic_sql := dynamic_sql ||
1528 ' AND (CKALIAS.'||l_db_pk5_column||' = :pk5_val) ';
1529 l_bind_pk5 := TRUE;
1530 end if;
1531 end if;
1532 dynamic_sql := dynamic_sql ||
1533 ' AND ('||l_predicate||') ';
1534
1535 if (fnd_log.LEVEL_STATEMENT >=
1536 fnd_log.g_current_runtime_level) then
1537 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1538 c_log_head || l_api_name || '.create_dy_sql',
1539 'dynamic_sql:'||dynamic_sql);
1540 end if;
1541
1542 l_cursor := dbms_sql.open_cursor;
1543 dbms_sql.parse(l_cursor, dynamic_sql, dbms_sql.native);
1544
1545 if l_bind_pk1 then
1546 dbms_sql.bind_variable(
1547 l_cursor, 'pk1_val', l_instance_pk1_value);
1548 if (fnd_log.LEVEL_STATEMENT >=
1549 fnd_log.g_current_runtime_level) then
1550 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1551 c_log_head || l_api_name || '.open1bind',
1552 ' l_instance_pk1_value:'||l_instance_pk1_value);
1553 end if;
1554 end if;
1555
1556 if l_bind_pk2 then
1557 dbms_sql.bind_variable(
1558 l_cursor, 'pk2_val', l_instance_pk2_value);
1559 if (fnd_log.LEVEL_STATEMENT >=
1560 fnd_log.g_current_runtime_level) then
1561 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1562 c_log_head || l_api_name || '.open2bind',
1563 ' l_instance_pk2_value:'||l_instance_pk2_value);
1564 end if;
1565 end if;
1566
1567 if l_bind_pk3 then
1568 dbms_sql.bind_variable(
1569 l_cursor, 'pk3_val', l_instance_pk3_value);
1570 if (fnd_log.LEVEL_STATEMENT >=
1571 fnd_log.g_current_runtime_level) then
1572 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1573 c_log_head || l_api_name || '.open3bind',
1574 ' l_instance_pk3_value:'||l_instance_pk3_value);
1575 end if;
1576 end if;
1577
1578 if l_bind_pk4 then
1579 dbms_sql.bind_variable(
1580 l_cursor, 'pk4_val', l_instance_pk4_value);
1581 if (fnd_log.LEVEL_STATEMENT >=
1582 fnd_log.g_current_runtime_level) then
1583 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1584 c_log_head || l_api_name || '.open4bind',
1585 ' l_instance_pk4_value:'||l_instance_pk4_value);
1586 end if;
1587 end if;
1588
1589 if l_bind_pk5 then
1590 dbms_sql.bind_variable(
1591 l_cursor, 'pk5_val', l_instance_pk5_value);
1592 if (fnd_log.LEVEL_STATEMENT >=
1593 fnd_log.g_current_runtime_level) then
1594 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1595 c_log_head || l_api_name || '.open5bind',
1596 ' l_instance_pk5_value:'||l_instance_pk5_value);
1597 end if;
1598 end if;
1599
1600
1601 if(l_function_id is not NULL) then
1605 c_log_head || l_api_name || '.fnidbind',
1602 if (fnd_log.LEVEL_STATEMENT >=
1603 fnd_log.g_current_runtime_level) then
1604 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1606 ' l_function_id:'||l_function_id);
1607 end if;
1608 dbms_sql.bind_variable(
1609 l_cursor, 'FUNCTION_ID_BIND',l_function_id);
1610 end if;
1611
1612 if(l_object_id is not NULL) then
1613 if (fnd_log.LEVEL_STATEMENT >=
1614 fnd_log.g_current_runtime_level) then
1615 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1616 c_log_head || l_api_name || '.objidbind',
1617 ' l_object_id:'||l_object_id);
1618 end if;
1619 dbms_sql.bind_variable(
1620 l_cursor, 'OBJECT_ID_BIND',l_object_id);
1621 end if;
1622
1623 dbms_sql.define_column(l_cursor, 1, dummy_item_id);
1624 l_rows_processed := dbms_sql.execute(l_cursor);
1625
1626 IF( dbms_sql.fetch_rows(l_cursor) > 0 ) THEN
1627 dbms_sql.close_cursor(l_cursor); -- close cursor
1628 if (fnd_log.LEVEL_PROCEDURE >=
1629 fnd_log.g_current_runtime_level) then
1630 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1631 c_log_head || l_api_name || '.end_found',
1632 'T');
1633 end if;
1634 RETURN 'T';
1635 ELSE
1636 dbms_sql.close_cursor(l_cursor); -- close cursor
1637 if (fnd_log.LEVEL_PROCEDURE >=
1638 fnd_log.g_current_runtime_level) then
1639 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1640 c_log_head || l_api_name || '.end_notfnd',
1641 'F');
1642 end if;
1643 RETURN 'F';
1644 END IF;
1645
1646 ELSE
1647 -- No predicate
1648 if (fnd_log.LEVEL_PROCEDURE >=
1649 fnd_log.g_current_runtime_level) then
1650 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1651 c_log_head || l_api_name || '.end_nopred',
1652 'F');
1653 end if;
1654 RETURN 'F';
1655 END IF; -- End of l_predicate checking */
1656
1657
1658
1659 EXCEPTION
1660 /* If API called with deprecated p_user_name arg, */
1661 /* propagate that up so the caller gets exception */
1662 WHEN FND_MESSAGE_RAISED_ERR THEN
1663 /* Re raise the error for the caller */
1664 fnd_message.raise_error;
1665 return 'U'; /* This line should never be executed */
1666
1667 WHEN OTHERS THEN
1668
1669 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1670 fnd_message.set_token('ROUTINE',
1671 c_pkg_name||','||l_api_name);
1672 fnd_message.set_token('ERRNO', SQLCODE);
1673 fnd_message.set_token('REASON', SQLERRM);
1674
1675 if (fnd_log.LEVEL_EXCEPTION >=
1676 fnd_log.g_current_runtime_level) then
1677 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1678 c_log_head || l_api_name || '.other_err',
1679 FALSE);
1680 end if;
1681 if (fnd_log.LEVEL_PROCEDURE >=
1682 fnd_log.g_current_runtime_level) then
1683 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1684 c_log_head || l_api_name || '.end_after_other',
1685 'U' );
1686 end if;
1687 RETURN 'U';
1688 END check_function;
1689 -----------------------------------------------------------------
1690
1691
1692
1693
1694 ------Function get_functions-------------
1695 /* Note that this routine has a special not publicly documented feature used*/
1696 /* by the ATG java code where it will return a list of the menuids rather than*/
1697 /* functions, if the special magic cookie */
1698 /* is passed for the p_user_name */
1699 PROCEDURE get_functions(
1700 p_api_version IN NUMBER,
1701 p_object_name IN VARCHAR2,
1702 p_instance_pk1_value IN VARCHAR2,
1703 p_instance_pk2_value IN VARCHAR2,
1704 p_instance_pk3_value IN VARCHAR2,
1705 p_instance_pk4_value IN VARCHAR2,
1706 p_instance_pk5_value IN VARCHAR2,
1707 p_user_name IN VARCHAR2,
1708 x_return_status OUT NOCOPY VARCHAR2,
1709 x_privilege_tbl OUT NOCOPY FND_PRIVILEGE_NAME_TABLE_TYPE
1710 ) IS
1711 l_api_version CONSTANT NUMBER := 1.0;
1712 l_api_name CONSTANT VARCHAR2(30) := 'GET_FUNCTIONS';
1713 l_sysdate DATE := Sysdate;
1714 l_predicate VARCHAR2(32767);
1715 -- modified for bug#5395351
1716 function_name fnd_form_functions.function_name%type;
1717 dynamic_sql VARCHAR2(32767);
1718 l_return_status varchar2(1);
1719 result varchar2(30);
1720 l_nrows pls_integer;
1721 l_index NUMBER;
1722 l_grant_inst_type varchar2(30);
1723
1724
1725 TYPE DYNAMIC_CUR IS REF CURSOR;
1729 l_user_name varchar2(80);
1726 instance_sets_cur DYNAMIC_CUR;
1727
1728 l_object_id number;
1730 BEGIN
1731 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1732 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1733 c_log_head || l_api_name || '.begin',
1734 c_pkg_name || '.' ||l_api_name|| '(' ||
1735 ' p_api_version=>'|| to_char(p_api_version) ||','||
1736 ' p_object_name=>'|| p_object_name ||','||
1737 ' p_instance_pk1_value=>'|| p_instance_pk1_value ||','||
1738 ' p_instance_pk2_value=>'|| p_instance_pk2_value ||','||
1739 ' p_instance_pk3_value=>'|| p_instance_pk3_value ||','||
1740 ' p_instance_pk4_value=>'|| p_instance_pk4_value ||','||
1741 ' p_instance_pk5_value=>'|| p_instance_pk5_value ||','||
1742 ' p_user_name=>'|| p_user_name ||
1743 ');');
1744 end if;
1745
1746 -- check for call compatibility.
1747 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
1748 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1749 fnd_message.set_token('ROUTINE',
1750 c_pkg_name || '.'|| l_api_name);
1751 fnd_message.set_token('REASON',
1752 'Unsupported version '|| to_char(p_api_version)||
1753 ' passed to API; expecting version '||
1754 to_char(l_api_version));
1755
1756 if (fnd_log.LEVEL_EXCEPTION >=
1757 fnd_log.g_current_runtime_level) then
1758 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1759 c_log_head || l_api_name || '.end_bad_api_ver',
1760 FALSE);
1761 end if;
1762 x_return_status := 'U';
1763 return;
1764 END IF;
1765
1766 -- Check for null arguments.
1767 if (p_object_name is NULL) THEN
1768 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1769 fnd_message.set_token('ROUTINE',
1770 c_pkg_name || '.'|| l_api_name);
1771 fnd_message.set_token('REASON',
1772 'NULL value passed for p_object_name:'|| p_object_name);
1773
1774 if (fnd_log.LEVEL_EXCEPTION >=
1775 fnd_log.g_current_runtime_level) then
1776 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1777 c_log_head || l_api_name || '.end_bad_param',
1778 FALSE);
1779 end if;
1780 x_return_status := 'U';
1781 return;
1782 END IF;
1783
1784 /* Make sure that the FND_COMPILED_MENU_FUNCTIONS table is compiled */
1785 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
1786 FND_FUNCTION.FAST_COMPILE;
1787 end if;
1788
1789 -- Default the user name if not passed in.
1790 if(p_user_name is NULL) then
1791 l_user_name := SYS_CONTEXT('FND','USER_NAME');
1792 else
1793 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
1794 and (substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) <>
1795 'GET_MNUIDS_NBVCXDS')
1796 and ( (p_user_name <> SYS_CONTEXT('FND','USER_NAME'))
1797 or ( (p_user_name is not null)
1798 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
1799 /* In R12 we do not allow passing values other than */
1800 /* the current user name (which is the default), */
1801 /* so we raise a runtime exception if that deprecated */
1802 /* kind of call is made to this routine. */
1803 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1804 fnd_message.set_token('ROUTINE',
1805 c_pkg_name || '.'|| l_api_name);
1806 fnd_message.set_token('REASON',
1807 'Invalid API call. Parameter p_user_name: '||p_user_name||
1808 ' was passed to API '||c_pkg_name || '.'|| l_api_name ||
1809 '. p_object_name: '||p_object_name||'. '||
1810 ' In Release 12 and beyond the p_user_name parameter '||
1811 'is unsupported, and any product team that passes it '||
1812 'must correct their code because it does not work '||
1813 'correctly. Please see the deprecated API document at '||
1814 'http://files.oraclecorp.com/content/AllPublic/'||
1815 'SharedFolders/ATG%20Requirements-Public/R12/'||
1816 'Requirements%20Definition%20Document/'||
1817 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
1818 'Oracle employees who encounter this error should log '||
1819 'a bug against the product that owns the call to this '||
1820 'routine, which is likely the owner of the object that '||
1821 'was passed to this routine: '||
1822 p_object_name);
1823 if (fnd_log.LEVEL_EXCEPTION >=
1824 fnd_log.g_current_runtime_level) then
1825 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1826 c_log_head || l_api_name || '.end_depr_param',
1827 FALSE);
1828 end if;
1829 fnd_message.raise_error;
1833
1830 end if;
1831 l_user_name := p_user_name;
1832 end if;
1834 if (p_instance_pk1_value is not NULL) then
1835 l_grant_inst_type := 'FUNCLIST';
1836 else
1837 l_grant_inst_type := 'FUNCLIST_NOINST';
1838 end if;
1839
1840 get_security_predicate(p_api_version=>1.0,
1841 p_function =>NULL,
1842 p_object_name =>p_object_name,
1843 p_grant_instance_type =>l_grant_inst_type,
1844 p_user_name =>l_user_name,
1845 x_predicate=>l_predicate,
1846 x_return_status=>l_return_status
1847 );
1848 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1849 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1850 c_log_head || l_api_name || '.after_gsp',
1851 'l_predicate:'||l_predicate);
1852 end if;
1853
1854 IF( l_return_status <> 'T' AND l_return_status <> 'F') then
1855 /* There will be a message on the stack from gsp */
1856 if (fnd_log.LEVEL_EXCEPTION >=
1857 fnd_log.g_current_runtime_level) then
1858 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1859 c_log_head || l_api_name || '.end_gsp_fail',
1860 FALSE);
1861 end if;
1862 if (l_return_status = 'L') then
1863 x_return_status := 'U';
1864 else
1865 /* Else return E, U, status code, whatever gsp returned. */
1866 x_return_status := l_return_status;
1867 end if;
1868 return;
1869 end if;
1870
1871 IF( l_return_status = 'F') then /* If there weren't enough grants */
1872 /* to make a predicate, we are done. */
1873 if (fnd_log.LEVEL_PROCEDURE >=
1874 fnd_log.g_current_runtime_level) then
1875 fnd_log.message(FND_LOG.LEVEL_PROCEDURE,
1876 c_log_head || l_api_name || '.end_gsp_nopred',
1877 FALSE);
1878 end if;
1879 x_return_status := 'F';
1880 return;
1881 end if;
1882
1883 IF( length(l_predicate ) >1) THEN
1884 dynamic_sql := l_predicate;
1885
1886 if (fnd_log.LEVEL_STATEMENT >=
1887 fnd_log.g_current_runtime_level) then
1888 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1889 c_log_head || l_api_name || '.create_dy_sql',
1890 'dynamic_sql:'||dynamic_sql);
1891 end if;
1892
1893 if(p_instance_pk5_value is not NULL) then
1894 OPEN instance_sets_cur FOR dynamic_sql USING
1895 p_instance_pk1_value,
1896 p_instance_pk2_value,
1897 p_instance_pk3_value,
1898 p_instance_pk4_value,
1899 p_instance_pk5_value;
1900 elsif (p_instance_pk4_value is not NULL) then
1901 OPEN instance_sets_cur FOR dynamic_sql USING
1902 p_instance_pk1_value,
1903 p_instance_pk2_value,
1904 p_instance_pk3_value,
1905 p_instance_pk4_value;
1906 elsif (p_instance_pk3_value is not NULL) then
1907 OPEN instance_sets_cur FOR dynamic_sql USING
1908 p_instance_pk1_value,
1909 p_instance_pk2_value,
1910 p_instance_pk3_value;
1911 elsif (p_instance_pk2_value is not NULL) then
1912 OPEN instance_sets_cur FOR dynamic_sql USING
1913 p_instance_pk1_value,
1914 p_instance_pk2_value;
1915 elsif (p_instance_pk1_value is not NULL) then
1916 OPEN instance_sets_cur FOR dynamic_sql USING
1917 p_instance_pk1_value;
1918 else
1919 OPEN instance_sets_cur FOR dynamic_sql;
1920 end if;
1921 l_index := 0;
1922 LOOP
1923 FETCH instance_sets_cur INTO function_name ;
1924 if (fnd_log.LEVEL_STATEMENT >=
1925 fnd_log.g_current_runtime_level) then
1926 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1927 c_log_head || l_api_name || '.got_priv',
1928 'function_name:' || function_name);
1929 end if;
1930 EXIT WHEN instance_sets_cur%NOTFOUND;
1931 x_privilege_tbl (l_index):=function_name;
1932 l_index:=l_index+1;
1933 END LOOP;
1934 CLOSE instance_sets_cur;
1935 if(l_index > 0) then
1936 x_return_status := 'T'; /* Success */
1937 else
1938 x_return_status := 'F'; /* No functions */
1939 end if;
1940 if (fnd_log.LEVEL_PROCEDURE >=
1941 fnd_log.g_current_runtime_level) then
1942 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1943 c_log_head || l_api_name || '.end',
1944 'returning '|| x_return_status );
1945 end if;
1946 ELSE
1947 -- No predicate
1948 if (fnd_log.LEVEL_PROCEDURE >=
1949 fnd_log.g_current_runtime_level) then
1953 end if;
1950 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1951 c_log_head || l_api_name || '.end_nopred',
1952 'F');
1954 x_return_status := 'F';
1955 RETURN;
1956 END IF; -- End of l_predicate checking */
1957
1958 return;
1959
1960 EXCEPTION
1961 /* If API called with deprecated p_user_name arg, */
1962 /* propagate that up so the caller gets exception */
1963 WHEN FND_MESSAGE_RAISED_ERR THEN
1964 /* Re raise the error for the caller */
1965 fnd_message.raise_error;
1966
1967 x_return_status := 'U'; /* This line should never be executed */
1968 return;
1969
1970 WHEN OTHERS THEN
1971 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1972 fnd_message.set_token('ROUTINE',
1973 c_pkg_name || '.'|| l_api_name);
1974 fnd_message.set_token('ERRNO', SQLCODE);
1975 fnd_message.set_token('REASON', SQLERRM);
1976
1977 if (fnd_log.LEVEL_EXCEPTION >=
1978 fnd_log.g_current_runtime_level) then
1979 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1980 c_log_head || l_api_name || '.other_err',
1981 FALSE);
1982 end if;
1983 if (fnd_log.LEVEL_PROCEDURE >=
1984 fnd_log.g_current_runtime_level) then
1985 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1986 c_log_head || l_api_name || '.end_after_other',
1987 'returning '|| 'U' );
1988 end if;
1989 x_return_status := 'U';
1990 return;
1991 END get_functions;
1992 -----------------------------------------------------------------
1993
1994
1995 ------Procedure GET_MENUS-------------
1996 /* INTERNAL ATG USE ONLY. NOT FOR PUBLIC USE. This is primarily used */
1997 /* by the ATG java code where it will return a list of the menuids. */
1998 /* Currently the user_name argument is ignored and the current user */
1999 /* is used. */
2000 PROCEDURE get_menus
2001 (
2002 p_api_version IN NUMBER,
2003 p_object_name IN VARCHAR2,
2004 p_instance_pk1_value IN VARCHAR2,
2005 p_instance_pk2_value IN VARCHAR2,
2006 p_instance_pk3_value IN VARCHAR2,
2007 p_instance_pk4_value IN VARCHAR2,
2008 p_instance_pk5_value IN VARCHAR2,
2009 p_user_name IN VARCHAR2,
2010 x_return_status OUT NOCOPY VARCHAR2,
2011 x_menu_tbl OUT NOCOPY FND_TABLE_OF_NUMBER
2012 ) IS
2013 l_api_version CONSTANT NUMBER := 1.0;
2014 l_api_name CONSTANT VARCHAR2(30) := 'GET_MENUS';
2015 l_menu_tbl FND_PRIVILEGE_NAME_TABLE_TYPE;
2016 l_index NUMBER;
2017 l_return_status VARCHAR2(30);
2018 l_menu_id NUMBER;
2019 l_out_menu_tbl FND_TABLE_OF_NUMBER;
2020 l_user_id_str VARCHAR2(255);
2021 begin
2022 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2023 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2024 c_log_head || l_api_name || '.begin',
2025 c_pkg_name || '.' ||l_api_name|| '(' ||
2026 ' p_api_version=>'|| to_char(p_api_version) ||','||
2027 ' p_object_name=>'|| p_object_name ||','||
2028 ' p_instance_pk1_value=>'|| p_instance_pk1_value ||','||
2029 ' p_instance_pk2_value=>'|| p_instance_pk2_value ||','||
2030 ' p_instance_pk3_value=>'|| p_instance_pk3_value ||','||
2031 ' p_instance_pk4_value=>'|| p_instance_pk4_value ||','||
2032 ' p_instance_pk5_value=>'|| p_instance_pk5_value ||','||
2033 ' p_user_name=>'|| p_user_name ||');');
2034 end if;
2035
2036 -- check for call compatibility.
2037 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
2038 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2039 fnd_message.set_token('ROUTINE',
2040 c_pkg_name || '.'|| l_api_name);
2041 fnd_message.set_token('REASON',
2042 'Unsupported version '|| to_char(p_api_version)||
2043 ' passed to API; expecting version '||
2044 to_char(l_api_version));
2045 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2046 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2047 c_log_head || l_api_name || '.end_bad_api_ver',
2048 FALSE);
2049 end if;
2050 x_return_status := 'U';
2051 return;
2052 END IF;
2053
2054 if(p_user_name is NULL) then
2055 l_user_id_str := 'GET_MNUIDS_NBVCXDS';
2056 else
2057 l_user_id_str := 'GET_MNUIDS_NBVCXDS:'||p_user_name;
2058 end if;
2059
2060 fnd_data_security.get_functions
2061 (
2062 p_api_version => 1.0,
2063 p_object_name => p_object_name,
2064 p_instance_pk1_value => p_instance_pk1_value,
2065 p_instance_pk2_value => p_instance_pk2_value,
2066 p_instance_pk3_value => p_instance_pk3_value,
2067 p_instance_pk4_value => p_instance_pk4_value,
2068 p_instance_pk5_value => p_instance_pk5_value,
2069 p_user_name => l_user_id_str,
2070 x_return_status => x_return_status,
2071 x_privilege_tbl => l_menu_tbl );
2072
2073 l_index := 0;
2074 l_out_menu_tbl := FND_TABLE_OF_NUMBER();
2075
2079 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2076 LOOP
2077 begin
2078 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2080 c_log_head || l_api_name || '.got_menu',
2081 'menu_id:' || l_menu_tbl(l_index));
2082 end if;
2083 l_menu_id := to_number(l_menu_tbl(l_index));
2084 l_out_menu_tbl.EXTEND;
2085 l_out_menu_tbl(l_index+1):= l_menu_id;
2086 l_index:=l_index+1;
2087 exception
2088 when no_data_found then
2089 exit;
2090 end;
2091 END LOOP;
2092
2093 if (l_out_menu_tbl is not NULL) then
2094 x_menu_tbl := l_out_menu_tbl;
2095 else
2096 x_menu_tbl := NULL;
2097 x_return_status := 'F';
2098 end if;
2099
2100 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2101 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2102 c_log_head || l_api_name || '.end',
2103 'returning status'|| x_return_status );
2104 end if;
2105
2106 end GET_MENUS;
2107
2108
2109 ----- get_security_predicate_helper- this is not a public API
2110 ----- This is the OLD version of the get_security_predicate code, used
2111 ----- only for certain rare modes.
2112 ----- Get the predicate. This handles the modes (p_grant_instance_type)
2113 ----- FUNCLIST, FUNCLIST_NOINST, and GRANTS_ONLY.
2114 ----- Undocumented unsupported feature for internal use only:
2115 ----- passing 'FUNCLIST' for p_grant_instance_type will yield pred
2116 ----- for use in get_functions.
2117 --------------------------------------------
2118 PROCEDURE get_security_predicate_helper
2119 (
2120 p_function IN VARCHAR2,
2121 p_object_name IN VARCHAR2,
2122 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
2123 /* Undocumented value: FUNCLIST, FUNCLIST_NOINST */
2124 /* Documented value: GRANTS_ONLY */
2125 p_user_name IN VARCHAR2,
2126 /* stmnt_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= for checking existence. */
2127 p_statement_type IN VARCHAR2,
2128 x_predicate out NOCOPY varchar2,
2129 x_return_status out NOCOPY varchar2,
2130 p_table_alias IN VARCHAR2
2131 ) IS
2132 l_api_name CONSTANT VARCHAR2(30):= 'GET_SECURITY_PREDICATE_HELPER';
2133
2134 l_sysdate DATE := Sysdate;
2135 l_aggregate_predicate VARCHAR2(32767); /* Must match c_pred_buf_size*/
2136 l_instance_flag BOOLEAN := TRUE;
2137 l_instance_set_flag BOOLEAN := TRUE;
2138 l_grants_only_flag BOOLEAN := FALSE;
2139 l_funclist_flag BOOLEAN := FALSE;
2140 l_menulist_flag BOOLEAN := FALSE;
2141 l_set_predicates VARCHAR2(32767); /* Must match c_pred_buf_size*/
2142 l_db_object_name varchar2(30);
2143 l_db_pk1_column varchar2(256);
2144 l_db_pk2_column varchar2(256);
2145 l_db_pk3_column varchar2(256);
2146 l_db_pk4_column varchar2(256);
2147 l_db_pk5_column varchar2(256);
2148 l_pk_column_names varchar2(512);
2149 l_pk_orig_column_names varchar2(512);
2150 l_ik_clause varchar2(2048);
2151 l_exact_clause varchar2(2048);
2152 l_user_name varchar2(80);
2153 l_nrows pls_integer;
2154 l_table_alias varchar2(256);
2155 l_pred varchar2(4000);
2156 colon pls_integer;
2157
2158 CURSOR instance_set_grants_c (cp_user_name varchar2,
2159 cp_function_id NUMBER,
2160 cp_object_id VARCHAR2)
2161 IS
2162 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
2163 UNIQUE
2164 instance_sets.predicate, instance_sets.instance_set_id
2165 FROM
2166 ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
2167 union all
2168 select role_name
2169 from wf_user_roles wur,
2170 (
2171 select cp_user_name name from dual
2172 union all
2173 select incr1.name name
2174 from wf_local_roles incr1, fnd_user u1
2175 where 'HZ_PARTY' = incr1.orig_system
2176 and u1.user_name = cp_user_name
2177 and u1.person_party_id = incr1.orig_system_id
2178 and incr1.partition_id = 9 /* HZ_PARTY */
2179 ) incr2
2180 where wur.user_name = incr2.name
2181 ) u2,
2182 fnd_grants g,
2183 fnd_object_instance_sets instance_sets
2184 WHERE g.instance_type = 'SET'
2185 AND g.grantee_key = u2.role_name
2186 AND g.object_id = cp_object_id
2187 AND ( (cp_function_id = -1)
2188 OR (g.menu_id in
2189 (select cmf.menu_id
2190 from fnd_compiled_menu_functions cmf
2191 where cmf.function_id = cp_function_id)))
2192 AND g.instance_set_id = instance_sets.instance_set_id
2193 AND ( g.ctx_secgrp_id = -1
2194 OR g.ctx_secgrp_id =
2195 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
2196 AND ( g.ctx_resp_id = -1
2197 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
2198 AND ( g.ctx_resp_appl_id = -1
2202 AND g.start_date <= SYSDATE
2199 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
2200 AND ( g.ctx_org_id = -1
2201 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
2203 AND ( g.end_date IS NULL
2204 OR g.end_date >= SYSDATE );
2205
2206 --Changes for Bug#3867925
2207 -- Fix Non Backward change made for universal person support
2208 --
2209 -- Performance note: This statement has not received the optimizations
2210 -- to the WF User portion of the SQL because the separation of the
2211 -- USER and GROUP clauses prevent that. Since this is only used for
2212 -- deprecated code that is okay.
2213 CURSOR instance_set_grants_bkwd_c (cp_user_name varchar2,
2214 cp_function_id NUMBER,
2215 cp_object_id VARCHAR2)
2216 IS
2217 SELECT UNIQUE
2218 instance_sets.predicate, instance_sets.instance_set_id
2219 FROM fnd_grants g,
2220 fnd_object_instance_sets instance_sets
2221 WHERE g.instance_type = 'SET'
2222 AND (( g.grantee_type = 'USER'
2223 AND g.grantee_key = cp_user_name)
2224 OR ( g.grantee_type = 'GROUP'
2225 AND (g.grantee_key in
2226 (select role_name
2227 from wf_user_roles wur
2228 where wur.user_name in
2229 ( (select cp_user_name from dual)
2230 union all
2231 (select incrns.name from wf_local_roles incrns, fnd_user f
2232 where 'HZ_PARTY' = incrns.orig_system
2233 and f.user_name = cp_user_name
2234 and f.person_party_id = incrns.orig_system_id)))))
2235 OR (g.grantee_type = 'GLOBAL'))
2236 AND g.object_id = cp_object_id
2237 AND ( (cp_function_id = -1)
2238 OR (g.menu_id in
2239 (select cmf.menu_id
2240 from fnd_compiled_menu_functions cmf
2241 where cmf.function_id = cp_function_id)))
2242 AND g.instance_set_id = instance_sets.instance_set_id
2243 AND ( g.ctx_secgrp_id = -1
2244 OR g.ctx_secgrp_id =
2245 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
2246 AND ( g.ctx_resp_id = -1
2247 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
2248 AND ( g.ctx_resp_appl_id = -1
2249 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
2250 AND ( g.ctx_org_id = -1
2251 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
2252 AND g.start_date <= SYSDATE
2253 AND ( g.end_date IS NULL
2254 OR g.end_date >= SYSDATE );
2255
2256 CURSOR instance_set_grants_guest_c (cp_user_name varchar2,
2257 cp_function_id NUMBER,
2258 cp_object_id VARCHAR2)
2259 IS
2260 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
2261 UNIQUE
2262 instance_sets.predicate, instance_sets.instance_set_id
2263 FROM
2264 ( select /*+ NO_MERGE */ role_name
2265 from wf_user_roles wur,
2266 (
2267 select cp_user_name name from dual
2268 union all
2269 select incr1.name name
2270 from wf_local_roles incr1, fnd_user u1
2271 where 'HZ_PARTY' = incr1.orig_system
2272 and u1.user_name = cp_user_name
2273 and u1.person_party_id = incr1.orig_system_id
2274 and incr1.partition_id = 9 /* HZ_PARTY */
2275 ) incr2
2276 where wur.user_name = incr2.name
2277 ) u2,
2278 fnd_grants g,
2279 fnd_object_instance_sets instance_sets
2280 WHERE g.instance_type = 'SET'
2281 AND g.grantee_key = u2.role_name
2282 AND g.object_id = cp_object_id
2283 AND ( (cp_function_id = -1)
2284 OR (g.menu_id in
2285 (select cmf.menu_id
2286 from fnd_compiled_menu_functions cmf
2287 where cmf.function_id = cp_function_id)))
2288 AND g.instance_set_id = instance_sets.instance_set_id
2289 AND ( g.ctx_secgrp_id = -1
2290 OR g.ctx_secgrp_id =
2291 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
2292 AND ( g.ctx_resp_id = -1
2293 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
2294 AND ( g.ctx_resp_appl_id = -1
2295 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
2296 AND ( g.ctx_org_id = -1
2297 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
2298 AND g.start_date <= SYSDATE
2299 AND ( g.end_date IS NULL
2300 OR g.end_date >= SYSDATE );
2301
2302 l_object_id number;
2303 l_function_id number;
2304 BEGIN
2305
2306 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
2307 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2308 c_log_head || l_api_name || '.begin',
2312 ', p_grant_instance_type=>'|| p_grant_instance_type ||
2309 c_pkg_name || '.' ||l_api_name|| '(' ||
2310 ', p_function=>'|| p_function ||
2311 ', p_object_name=>'|| p_object_name ||
2313 ', p_user_name=>'|| p_user_name ||
2314 ', p_table_alias=>'|| p_table_alias
2315 ||');');
2316 end if;
2317
2318 x_return_status := 'T'; /* Assume Success */
2319
2320 /* Make sure that the FND_COMPILED_MENU_FUNCTIONS table is compiled */
2321 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
2322 FND_FUNCTION.FAST_COMPILE;
2323 end if;
2324
2325 -- Default the user name if not passed in
2326 if(substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) =
2327 'GET_MNUIDS_NBVCXDS') then
2328 l_menulist_flag := TRUE; /* For a special mode called from java */
2329 if(substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS:')) =
2330 'GET_MNUIDS_NBVCXDS:') then
2331 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
2332 and (substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) <>
2333 'GET_MNUIDS_NBVCXDS')
2334 and ( (p_user_name <> SYS_CONTEXT('FND','USER_NAME'))
2335 or ( (p_user_name is not null)
2336 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
2337 /* In R12 we do not allow passing values other than */
2338 /* the current user name (which is the default), */
2339 /* so we raise a runtime exception if that deprecated */
2340 /* kind of call is made to this routine. */
2341 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2342 fnd_message.set_token('ROUTINE',
2343 c_pkg_name || '.'|| l_api_name);
2344 fnd_message.set_token('REASON',
2345 'Invalid API call. Parameter p_user_name: '||p_user_name||
2346 ' was passed to API '||c_pkg_name || '.'|| l_api_name ||
2347 '. p_object_name: '||p_object_name||'. '||
2348 ' In Release 12 and beyond the p_user_name parameter '||
2349 'is unsupported, and any product team that passes it '||
2350 'must correct their code because it does not work '||
2351 'correctly. Please see the deprecated API document at '||
2352 'http://files.oraclecorp.com/content/AllPublic/'||
2353 'SharedFolders/ATG%20Requirements-Public/R12/'||
2354 'Requirements%20Definition%20Document/'||
2355 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
2356 'Oracle employees who encounter this error should log '||
2357 'a bug against the product that owns the call to this '||
2358 'routine, which is likely the owner of the object that '||
2359 'was passed to this routine: '||
2360 p_object_name);
2361 if (fnd_log.LEVEL_EXCEPTION >=
2362 fnd_log.g_current_runtime_level) then
2363 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2364 c_log_head || l_api_name || '.end_depr_param',
2365 FALSE);
2366 end if;
2367 fnd_message.raise_error;
2368 end if;
2369 l_user_name := SUBSTR(p_user_name,
2370 LENGTH('GET_MNUIDS_NBVCXDS:')+1);
2371 else
2372 l_user_name := SYS_CONTEXT('FND','USER_NAME');
2373 end if;
2374 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2375 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2376 c_log_head || l_api_name || '.get_mnuid',
2377 'l_user_name= '||l_user_name);
2378 end if;
2379 elsif (p_user_name is NULL) then
2380 l_user_name := SYS_CONTEXT('FND','USER_NAME');
2381 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2382 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2383 c_log_head || l_api_name || '.null_username',
2384 'l_user_name= '||l_user_name);
2385 end if;
2386 else
2387 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
2388 and (substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) <>
2389 'GET_MNUIDS_NBVCXDS')
2390 and ( (p_user_name <> SYS_CONTEXT('FND','USER_NAME'))
2391 or ( (p_user_name is not null)
2392 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
2393 /* In R12 we do not allow passing values other than */
2394 /* the current user name (which is the default), */
2395 /* so we raise a runtime exception if that deprecated */
2396 /* kind of call is made to this routine. */
2397 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2398 fnd_message.set_token('ROUTINE',
2399 c_pkg_name || '.'|| l_api_name);
2400 fnd_message.set_token('REASON',
2401 'Invalid API call. Parameter p_user_name: '||p_user_name||
2402 ' was passed to API '||c_pkg_name || '.'|| l_api_name ||
2403 '. p_object_name: '||p_object_name||'. '||
2404 ' In Release 12 and beyond the p_user_name parameter '||
2405 'is unsupported, and any product team that passes it '||
2409 'SharedFolders/ATG%20Requirements-Public/R12/'||
2406 'must correct their code because it does not work '||
2407 'correctly. Please see the deprecated API document at '||
2408 'http://files.oraclecorp.com/content/AllPublic/'||
2410 'Requirements%20Definition%20Document/'||
2411 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
2412 'Oracle employees who encounter this error should log '||
2413 'a bug against the product that owns the call to this '||
2414 'routine, which is likely the owner of the object that '||
2415 'was passed to this routine: '||
2416 p_object_name);
2417 if (fnd_log.LEVEL_EXCEPTION >=
2418 fnd_log.g_current_runtime_level) then
2419 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2420 c_log_head || l_api_name || '.end_depr_param',
2421 FALSE);
2422 end if;
2423 fnd_message.raise_error;
2424 end if;
2425 l_user_name := p_user_name;
2426 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2427 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2428 c_log_head || l_api_name || '.passed_uname',
2429 'l_user_name= '||l_user_name);
2430 end if;
2431 end if;
2432
2433
2434
2435 -- Check to make sure a valid role is passed or defaulted for user_name
2436 if (check_user_role(l_user_name) = 'F') then
2437 -- If we got here then the grantee will never be found because
2438 -- it isn't even a role, so we know there won't be a matching grant.
2439 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2440 fnd_message.set_token('ROUTINE',
2441 c_pkg_name || '.'|| l_api_name);
2442 fnd_message.set_token('REASON',
2443 'The user_name passed or defaulted is not a valid user_name '||
2444 'in wf_user_roles. '||
2445 'Invalid user_name: '||l_user_name ||
2446 ' Passed p_user_name: '||p_user_name);
2447 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2448 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2449 c_log_head || l_api_name || '.end_no_wf_user_role',
2450 FALSE);
2451 end if;
2452 l_aggregate_predicate := '1=2';
2453 x_return_status := 'E'; /* Error condition */
2454 return;
2455 end if;
2456
2457
2458 -- Step 1.
2459
2460 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2461 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2462 c_log_head || l_api_name || '.step1start',
2463 'p_grant_instance_type:'||p_grant_instance_type);
2464 end if;
2465 IF (p_grant_instance_type = C_TYPE_INSTANCE) THEN
2466 if (fnd_log.LEVEL_STATEMENT >=
2467 fnd_log.g_current_runtime_level) then
2468 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2469 c_log_head || l_api_name || '.chk_instance',
2470 ' l_instance_set_flag := FALSE ');
2471 end if;
2472 l_instance_set_flag:= FALSE;
2473 ELSIF (p_grant_instance_type = C_TYPE_SET) THEN
2474 if (fnd_log.LEVEL_STATEMENT >=
2475 fnd_log.g_current_runtime_level) then
2476 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2477 c_log_head || l_api_name || '.chk_set',
2478 ' l_instance_flag := FALSE ');
2479 end if;
2480 l_instance_flag:= FALSE;
2481 ELSIF (p_grant_instance_type = 'FUNCLIST') THEN
2482 if (fnd_log.LEVEL_STATEMENT >=
2483 fnd_log.g_current_runtime_level) then
2484 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2485 c_log_head || l_api_name || '.chk_funclist',
2486 ' l_funclist_flag := TRUE ');
2487 end if;
2488 l_funclist_flag:= TRUE;
2489 ELSIF (p_grant_instance_type = 'FUNCLIST_NOINST') THEN
2490 if (fnd_log.LEVEL_STATEMENT >=
2491 fnd_log.g_current_runtime_level) then
2492 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2493 c_log_head || l_api_name || '.chk_funclist_noinst',
2494 ' l_funclist_flag := TRUE ');
2495 end if;
2496 l_funclist_flag:= TRUE;
2497 l_instance_flag:= FALSE;
2498 l_instance_set_flag:= FALSE;
2499 ELSIF (p_grant_instance_type = 'GRANTS_ONLY') THEN
2500 if (fnd_log.LEVEL_STATEMENT >=
2501 fnd_log.g_current_runtime_level) then
2502 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2503 c_log_head || l_api_name || '.chk_grants_only',
2504 ' l_funclist_flag := FALSE ');
2505 end if;
2506 l_grants_only_flag:= TRUE;
2507 l_funclist_flag:= FALSE;
2508 l_instance_flag:= FALSE;
2509 l_instance_set_flag:= FALSE;
2510 ELSIF (p_grant_instance_type = 'UNIVERSAL') THEN
2511 if (fnd_log.LEVEL_STATEMENT >=
2512 fnd_log.g_current_runtime_level) then
2513 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2514 c_log_head || l_api_name || '.chk_universal',
2515 ' ');
2516 end if;
2517 END IF;
2521 fnd_message.set_token('ROUTINE',
2518
2519 if (p_object_name is NULL) THEN
2520 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2522 c_pkg_name || '.'|| l_api_name);
2523 fnd_message.set_token('REASON',
2524 'The parameter p_object_name can not be NULL.');
2525 if (fnd_log.LEVEL_EXCEPTION >=
2526 fnd_log.g_current_runtime_level) then
2527 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2528 c_log_head || l_api_name || '.end_null_obj',
2529 FALSE);
2530 end if;
2531 x_return_status := 'U';
2532 return;
2533 END IF;
2534
2535 if(p_object_name = 'GLOBAL') then
2536 /* As a special hack to allow Raymond to retrieve the */
2537 /* global grants by calling the internal get_menus routine, */
2538 /* we allow 'GLOBAL' only for that particular case. */
2539
2540 /* Bug 5580650.
2541 * Don't support any special hacking.
2542 * Don't support object_name=GLOBAL even for 'l_menulist_flag=TRUE'
2543 * case. With the following code though it appears object_name=GLOBAL
2544 * is allowed for 'l_menulist_flag=TRUE' case, it is failing with
2545 * SQL parse error ORA-00942 at the end while executing the
2546 * dynamically generated sql predicate. So the below code is commented
2547 * so that instead of deferring the error to SQL parse stage,
2548 * now it throws the error as soon as object_name=GLOBAL case is
2549 * identified.
2550 */
2551 /******
2552 if (l_menulist_flag = FALSE) then
2553 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2554 fnd_message.set_token('ROUTINE',
2555 c_pkg_name || '.'|| l_api_name);
2556 fnd_message.set_token('REASON',
2557 'The parameter p_object_name can not be ''GLOBAL''. ');
2558 if (fnd_log.LEVEL_EXCEPTION >=
2559 fnd_log.g_current_runtime_level) then
2560 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2561 c_log_head || l_api_name || '.end_glob_obj',
2562 FALSE);
2563 end if;
2564 x_return_status := 'U';
2565 return;
2566 else
2567 l_object_id := -1;
2568 end if;
2569 ********/
2570 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2571 fnd_message.set_token('ROUTINE',
2572 c_pkg_name || '.'|| l_api_name);
2573 fnd_message.set_token('REASON',
2574 'The parameter p_object_name can not be ''GLOBAL''. ');
2575 if (fnd_log.LEVEL_EXCEPTION >=
2576 fnd_log.g_current_runtime_level) then
2577 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2578 c_log_head || l_api_name || '.end_glob_obj',
2579 FALSE);
2580 end if;
2581 x_return_status := 'U';
2582 return;
2583 else /* Normal case */
2584 /* Get the primary key lists and info for this object */
2585 x_return_status := get_pk_information(p_object_name,
2586 l_db_pk1_column ,
2587 l_db_pk2_column ,
2588 l_db_pk3_column ,
2589 l_db_pk4_column ,
2590 l_db_pk5_column ,
2591 l_pk_column_names ,
2592 l_ik_clause,
2593 l_exact_clause,
2594 l_pk_orig_column_names,
2595 l_db_object_name,
2596 'OBJTAB', 'GNT');
2597 if (x_return_status <> 'T') then
2598 if (fnd_log.LEVEL_PROCEDURE >=
2599 fnd_log.g_current_runtime_level) then
2600 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2601 c_log_head || l_api_name || '.end_pk_info_err',
2602 'returning status: '|| x_return_status);
2603 end if;
2604 /* There will be a message on the msg dict stack. */
2605 return; /* We will return the x_return_status as out param */
2606 end if;
2607
2608 l_object_id :=get_object_id(p_object_name );
2609 if (l_object_id is NULL) THEN
2610 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2611 fnd_message.set_token('ROUTINE',
2612 c_pkg_name || '.'|| l_api_name);
2613 fnd_message.set_token('REASON',
2614 'The parameter value p_object_name is not a valid object.'||
2615 ' p_object_name:'||p_object_name);
2616 if (fnd_log.LEVEL_EXCEPTION >=
2617 fnd_log.g_current_runtime_level) then
2618 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2619 c_log_head || l_api_name || '.end_bad_obj',
2620 FALSE);
2621 end if;
2622 x_return_status := 'U';
2623 return;
2624 END IF;
2625 end if;
2626
2627 if(p_function is NULL) then
2628 l_function_id := -1;
2629 else
2630 l_function_id := get_function_id(p_function);
2631 if (l_function_id is NULL) THEN
2635 fnd_message.set_token('REASON',
2632 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
2633 fnd_message.set_token('ROUTINE',
2634 c_pkg_name || '.'|| l_api_name);
2636 'The parameter value p_function is not a valid function name.'||
2637 ' p_function:'||p_function);
2638 if (fnd_log.LEVEL_EXCEPTION >=
2639 fnd_log.g_current_runtime_level) then
2640 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2641 c_log_head || l_api_name || '.end_bad_func',
2642 FALSE);
2643 end if;
2644 x_return_status := 'U';
2645 return;
2646 END IF;
2647 end if;
2648
2649 l_aggregate_predicate := '';
2650
2651
2652 if (p_table_alias is not NULL) then
2653 l_table_alias := p_table_alias || '.';
2654 l_pk_orig_column_names := l_table_alias ||
2655 replace (l_pk_orig_column_names, ', ',
2656 ', '||l_table_alias);
2657 else
2658 l_table_alias := NULL;
2659 end if;
2660
2661 -- Step 2.
2662 if (l_funclist_flag = TRUE) then
2663 l_aggregate_predicate := '';
2664 elsif (p_statement_type = 'EXISTS') then
2665 l_aggregate_predicate := 'EXISTS (';
2666 elsif (l_grants_only_flag = TRUE) then
2667 l_aggregate_predicate := '';
2668 else /* This is the normal case */
2669 l_aggregate_predicate := '('||l_pk_orig_column_names || ') IN (';
2670 end if;
2671
2672 IF (l_menulist_flag = TRUE) then
2673 l_aggregate_predicate := l_aggregate_predicate ||
2674 'SELECT unique to_char(GNT.MENU_ID) '||
2675 ' FROM fnd_grants GNT, ' ||
2676 l_db_object_name||' OBJTAB'||
2677 ' WHERE ';
2678 ELSIF(l_funclist_flag = TRUE) THEN
2679 l_aggregate_predicate := l_aggregate_predicate ||
2680 'SELECT unique FF.FUNCTION_NAME '||
2681 ' FROM fnd_grants GNT, ' ||
2682 l_db_object_name||' OBJTAB, '||
2683 'fnd_compiled_menu_functions CMF, '||
2684 'fnd_form_functions FF '||
2685 ' WHERE ';
2686 ELSIF (l_grants_only_flag = TRUE) then
2687 NULL;
2688 ELSE
2689 l_aggregate_predicate := l_aggregate_predicate ||
2690 ' SELECT '|| l_pk_column_names ||
2691 ' FROM fnd_grants GNT';
2692 IF ((l_instance_flag = TRUE) OR (l_instance_set_flag = TRUE)) then
2693 l_aggregate_predicate := l_aggregate_predicate ||
2694 ', '||l_db_object_name||' OBJTAB';
2695 END IF;
2696 l_aggregate_predicate := l_aggregate_predicate || ' WHERE ';
2697 END IF;
2698
2699 --Changes for Bug#3867925
2700 -- Fix Non Backward change made for universal person support
2701 --
2702 -- Performance note: This statement has not received the optimizations
2703 -- to the WF User portion of the SQL because the separation of the
2704 -- USER and GROUP clauses prevent that. Since this is only used for
2705 -- deprecated code that is okay.
2706 colon := instr(p_user_name, 'PER:');
2707 if (colon <> 0) then
2708 l_aggregate_predicate :=
2709 l_aggregate_predicate ||
2710 ' GNT.object_id = ' || l_object_id ||
2711 ' AND ((GNT.grantee_type = ''USER'' ' ||
2712 ' AND GNT.grantee_key = '''||
2713 replace(l_user_name,'''','''''')||''')'||
2714 ' OR (GNT.grantee_type = ''GROUP'' '||
2715 ' AND GNT.grantee_key in ';
2716 l_aggregate_predicate := l_aggregate_predicate ||
2717 ' (select role_name '||
2718 ' from wf_user_roles wur '||
2719 ' where wur.user_name in '||
2720 ' ( (select '||
2721 ' '''||replace(l_user_name,'''','''''')||''' '||
2722 ' from dual) '||
2723 ' union all '||
2724 ' (select incrns.name from wf_local_roles incrns, '||
2725 ' fnd_user f '||
2726 ' where ''HZ_PARTY'' = incrns.orig_system '||
2727 ' and f.user_name = '||
2728 ' '''||replace(l_user_name,'''','''''')||''' '||
2729 ' and f.person_party_id = incrns.orig_system_id)))) '||
2730 ' OR (GNT.grantee_type = ''GLOBAL''))';
2731 else
2732 l_aggregate_predicate :=
2733 l_aggregate_predicate ||
2734 ' GNT.object_id = ' || l_object_id ||
2735 ' AND (GNT.grantee_key in '||
2736 ' (select role_name '||
2737 ' from wf_user_roles wur, '||
2738 ' ( select '||
2739 ' '''||replace(l_user_name,'''','''''')||''' '||
2740 ' name from dual '||
2741 ' union all '||
2742 ' (select incrns.name from wf_local_roles incrns, '||
2746 ' '''||replace(l_user_name,'''','''''')||''' '||
2743 ' fnd_user f '||
2744 ' where ''HZ_PARTY'' = incrns.orig_system '||
2745 ' and f.user_name = '||
2747 ' and f.person_party_id = incrns.orig_system_id '||
2748 ' and incrns.partition_id = 9 ) '||
2749 ' ) incr2 '||
2750 ' where wur.user_name = incr2.name '||
2751 ' union all '||
2752 ' select ''GLOBAL'' from dual))';
2753 end if;
2754 if (l_function_id <> -1) then
2755 l_aggregate_predicate := l_aggregate_predicate ||
2756 ' AND GNT.menu_id in'||
2757 ' (select cmf.menu_id'||
2758 ' from fnd_compiled_menu_functions cmf'||
2759 ' where cmf.function_id = '||l_function_id||')';
2760 end if;
2761 l_aggregate_predicate := l_aggregate_predicate ||
2762 ' AND( GNT.ctx_secgrp_id = -1'||
2763 ' OR GNT.ctx_secgrp_id = '||
2764 ' SYS_CONTEXT(''FND'',''SECURITY_GROUP_ID''))'||
2765 ' AND( GNT.ctx_resp_id = -1'||
2766 ' OR GNT.ctx_resp_id = '||
2767 ' SYS_CONTEXT(''FND'',''RESP_ID''))'||
2768 ' AND( GNT.ctx_resp_appl_id = -1'||
2769 ' OR GNT.ctx_resp_appl_id ='||
2770 ' SYS_CONTEXT(''FND'',''RESP_APPL_ID''))'||
2771 ' AND( GNT.ctx_org_id = -1'||
2772 ' OR GNT.ctx_org_id ='||
2773 ' SYS_CONTEXT(''FND'', ''ORG_ID''))'||
2774 ' AND GNT.start_date <= sysdate ' ||
2775 ' AND ( GNT.end_date IS NULL ' ||
2776 ' OR GNT.end_date >= sysdate ) ';
2777 IF(l_funclist_flag = TRUE) THEN
2778 if (l_menulist_flag = FALSE) then /* usual case */
2779 l_aggregate_predicate := l_aggregate_predicate ||
2780 ' AND CMF.MENU_ID = GNT.MENU_ID '||
2781 ' AND CMF.FUNCTION_ID = FF.FUNCTION_ID ';
2782 end if;
2783 /* Add on the clause that */
2784 /* gives bind vars for an exact pk match */
2785 IF ((l_instance_flag = TRUE) OR (l_instance_set_flag = TRUE)) then
2786 l_aggregate_predicate := l_aggregate_predicate ||
2787 ' AND '||l_exact_clause;
2788 END IF;
2789 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2790 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2791 c_log_head || l_api_name || '.step2astart','step2astart');
2792 end if;
2793 END IF;
2794 if(l_grants_only_flag = FALSE) then
2795 l_aggregate_predicate := l_aggregate_predicate ||
2796 ' AND(';
2797 end if;
2798 IF(l_instance_flag = TRUE) THEN
2799 /* Add on the clause for INSTANCE_TYPE = 'INSTANCE' */
2800 l_aggregate_predicate := l_aggregate_predicate || l_ik_clause;
2801 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2802 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2803 c_log_head || l_api_name || '.step2start','step2start');
2804 end if;
2805 END IF;
2806 IF(l_instance_set_flag = TRUE) THEN
2807 /* Add on the clause for INSTANCE_TYPE = 'SET' */
2808 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2809 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2810 c_log_head || l_api_name || '.step3start',
2811 ' user_name: '|| l_user_name ||
2812 ' function: '|| p_function ||
2813 ' l_object_id: '|| l_object_id);
2814 end if;
2815 l_set_predicates:='';
2816
2817
2818 if (l_user_name <> 'GUEST') then
2819
2820 --Changes for Bug#3867925
2821 -- Fix Non Backward change made for universal person support
2822 if (colon <> 0) then
2823 FOR instance_set_grants_bkwd_rec IN
2824 instance_set_grants_bkwd_c (l_user_name,
2825 l_function_id,
2826 l_object_id)
2827 LOOP
2828 /* Upgrade and substitute predicate */
2829 l_pred := upgrade_predicate(
2830 instance_set_grants_bkwd_rec.predicate);
2831
2832 if (fnd_log.LEVEL_STATEMENT >=
2833 fnd_log.g_current_runtime_level) then
2834 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2835 c_log_head || l_api_name || '.upgd_pred',
2836 'l_pred:'||l_pred);
2837 end if;
2838
2839 /* in funclist mode, alias is 'OBJTAB'*/
2840 if (l_funclist_flag OR l_menulist_flag) then
2841 l_pred := substitute_predicate(
2842 l_pred,
2843 'OBJTAB');
2844 else
2845 l_pred := substitute_predicate(
2846 l_pred,
2847 p_table_alias);
2848 end if;
2849 if (fnd_log.LEVEL_STATEMENT >=
2850 fnd_log.g_current_runtime_level) then
2851 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2855
2852 c_log_head || l_api_name || '.subbed_pred',
2853 'l_pred:'||l_pred);
2854 end if;
2856 l_set_predicates := substrb( l_set_predicates ||
2857 ' ( (gnt.instance_set_id = '||
2858 instance_set_grants_bkwd_rec.instance_set_id ||
2859 ' ) AND ('||
2860 l_pred ||
2861 ' )) OR ', 1, c_pred_buf_size);
2862 if (fnd_log.LEVEL_STATEMENT >=
2863 fnd_log.g_current_runtime_level) then
2864 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2865 c_log_head || l_api_name || '.step3loop',
2866 ' l_set_predicates: ' || l_set_predicates);
2867 end if;
2868 END LOOP;
2869 else
2870 FOR instance_set_grants_rec
2871 IN instance_set_grants_c (l_user_name,
2872 l_function_id,
2873 l_object_id)
2874 LOOP
2875 /* Upgrade and substitute predicate */
2876 l_pred := upgrade_predicate(
2877 instance_set_grants_rec.predicate);
2878
2879 if (fnd_log.LEVEL_STATEMENT >=
2880 fnd_log.g_current_runtime_level) then
2881 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2882 c_log_head || l_api_name || '.upgd_pred',
2883 'l_pred:'||l_pred);
2884 end if;
2885
2886 /* in funclist mode, alias is 'OBJTAB'*/
2887 if (l_funclist_flag OR l_menulist_flag) then
2888 l_pred := substitute_predicate(
2889 l_pred,
2890 'OBJTAB');
2891 else
2892 l_pred := substitute_predicate(
2893 l_pred,
2894 p_table_alias);
2895 end if;
2896 if (fnd_log.LEVEL_STATEMENT >=
2897 fnd_log.g_current_runtime_level) then
2898 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2899 c_log_head || l_api_name || '.subbed_pred',
2900 'l_pred:'||l_pred);
2901 end if;
2902
2903 l_set_predicates := substrb( l_set_predicates ||
2904 ' ( (gnt.instance_set_id = '||
2905 instance_set_grants_rec.instance_set_id ||
2906 ' ) AND ('||
2907 l_pred ||
2908 ' )) OR ', 1, c_pred_buf_size);
2909 if (fnd_log.LEVEL_STATEMENT >=
2910 fnd_log.g_current_runtime_level) then
2911 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2912 c_log_head || l_api_name || '.step3loop',
2913 ' l_set_predicates: ' || l_set_predicates);
2914 end if;
2915 END LOOP;
2916 end if;
2917 else
2918
2919 -- Handle for user GUEST
2920 FOR instance_set_grants_guest_rec IN
2921 instance_set_grants_guest_c (l_user_name,
2922 l_function_id,
2923 l_object_id)
2924 LOOP
2925 /* Upgrade and substitute predicate */
2926 l_pred := upgrade_predicate(
2927 instance_set_grants_guest_rec.predicate);
2928
2929 if (fnd_log.LEVEL_STATEMENT >=
2930 fnd_log.g_current_runtime_level) then
2931 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2932 c_log_head || l_api_name || '.upgd_pred',
2933 'l_pred:'||l_pred);
2934 end if;
2935
2936 /* in funclist mode, alias is 'OBJTAB'*/
2937 if (l_funclist_flag OR l_menulist_flag) then
2938 l_pred := substitute_predicate(
2939 l_pred,
2940 'OBJTAB');
2941 else
2942 l_pred := substitute_predicate(
2943 l_pred,
2944 p_table_alias);
2945 end if;
2946 if (fnd_log.LEVEL_STATEMENT >=
2947 fnd_log.g_current_runtime_level) then
2948 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2949 c_log_head || l_api_name || '.subbed_pred',
2950 'l_pred:'||l_pred);
2951 end if;
2952
2953 l_set_predicates := substrb( l_set_predicates ||
2954 ' ( (gnt.instance_set_id = '||
2955 instance_set_grants_guest_rec.instance_set_id ||
2956 ' ) AND ('||
2957 l_pred ||
2958 ' )) OR ', 1, c_pred_buf_size);
2959 if (fnd_log.LEVEL_STATEMENT >=
2960 fnd_log.g_current_runtime_level) then
2961 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2965 END LOOP;
2962 c_log_head || l_api_name || '.step3loop',
2963 ' l_set_predicates: ' || l_set_predicates);
2964 end if;
2966 end if;
2967
2968 IF( length(l_set_predicates ) >0) THEN
2969 -- strip off the trailing 'OR '
2970 l_set_predicates := substr(l_set_predicates, 1,
2971 length(l_set_predicates) - length('OR '));
2972
2973 if (l_instance_flag = TRUE) then /* If necc, add OR on front*/
2974 l_aggregate_predicate := substrb(
2975 l_aggregate_predicate || ' OR',
2976 1, c_pred_buf_size);
2977 end if;
2978 l_aggregate_predicate := substrb(
2979 l_aggregate_predicate ||
2980 ' ( (gnt.instance_type = ''SET'') AND ( ' ||
2981 l_set_predicates ||'))',
2982 1, c_pred_buf_size);
2983 if (fnd_log.LEVEL_STATEMENT >=
2984 fnd_log.g_current_runtime_level) then
2985 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
2986 c_log_head || l_api_name || '.setpreds',
2987 ' l_aggregate_predicate: ' || l_aggregate_predicate);
2988 end if;
2989 ELSE
2990 /* If there weren't any instance sets and not instance mode,
2991 /* predicate will not return any rows, so just return '1=2' */
2992 if (l_instance_flag = FALSE) then
2993 x_predicate := '(1=2)';
2994 if (fnd_log.LEVEL_PROCEDURE >=
2995 fnd_log.g_current_runtime_level) then
2996 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2997 c_log_head || l_api_name || '.end_no_row_pred',
2998 'x_predicate: '|| x_predicate ||
2999 ', x_return_status:'||x_return_status);
3000 end if;
3001 x_return_status := 'T';
3002 return;
3003 end if;
3004 END IF;
3005 END IF;
3006
3007 /* Add the clause for the global */
3008 IF ((l_instance_flag = TRUE) OR (l_instance_set_flag = TRUE)) then
3009 l_aggregate_predicate := substrb(
3010 l_aggregate_predicate || ' OR',
3011 1, c_pred_buf_size);
3012 END IF;
3013 IF (l_grants_only_flag = FALSE) then
3014 l_aggregate_predicate := substrb( l_aggregate_predicate ||
3015 ' ( (gnt.instance_type = ''GLOBAL'')'||
3016 /* The object_id here isn't functionally necessary */
3017 /* since it appears elsewhere in the SQL but putting */
3018 /* it here helps the CBO choose a good plan; removing*/
3019 /* made the whole stmnt several times slower. */
3020 ' AND (gnt.object_id = '||l_object_id||'))',
3021 1, c_pred_buf_size);
3022 end if;
3023
3024 IF (l_grants_only_flag = FALSE) then
3025 /* Close off parenthesis 'AND (' that started instance/set clauses*/
3026 l_aggregate_predicate := substrb( l_aggregate_predicate||')',
3027 1, c_pred_buf_size);
3028 if (l_funclist_flag = FALSE) then /* Close off subselect parens */
3029
3030 l_aggregate_predicate := substrb( l_aggregate_predicate||')',
3031 1, c_pred_buf_size);
3032 end if;
3033 end if;
3034
3035
3036 /* Put parentheses around the statement in order to make it */
3037 /* amenable to ANDing with another statement */
3038 if( (p_statement_type <> 'EXISTS')
3039 AND (l_funclist_flag = FALSE)
3040 AND (l_grants_only_flag = FALSE)) then
3041 /* tmorrow- for bug 4592098 added substr to prevent buf overflows*/
3042 x_predicate := substrb(
3043 '('||l_aggregate_predicate||')',
3044 1, c_pred_buf_size);
3045 else
3046 x_predicate :=l_aggregate_predicate;
3047 end if;
3048
3049 if (g_vpd_buf_limit = -1) then /* If not initialized */
3050 g_vpd_buf_limit := self_init_pred_size(); /* init from db version */
3051 end if;
3052
3053 /* tmorrow- for bug 4592098 check x_predicate rather than l_aggreg..*/
3054 if ( (lengthb(x_predicate) > g_vpd_buf_limit)
3055 AND ( (p_statement_type = 'VPD')
3056 OR (p_statement_type = 'BASE'/* deprecated */)))then
3057 FND_MESSAGE.SET_NAME('FND', 'GENERIC-INTERNAL ERROR');
3058 FND_MESSAGE.SET_TOKEN('ROUTINE',
3059 c_pkg_name || '.'|| l_api_name);
3060 FND_MESSAGE.SET_TOKEN('REASON',
3061 'The predicate was longer than the database VPD limit of '||
3062 to_char(g_vpd_buf_limit)||' bytes for the predicate. ');
3063
3064 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
3065 fnd_log.message(FND_LOG.LEVEL_PROCEDURE,
3066 c_log_head || l_api_name || '.end',
3067 FALSE);
3068 end if;
3069 x_return_status := 'L'; /* Indicate Error */
3070
3071 end if;
3072
3073 EXCEPTION
3077 /* Re raise the error for the caller */
3074 /* If API called with deprecated p_user_name arg, */
3075 /* propagate that up so the caller gets exception */
3076 WHEN FND_MESSAGE_RAISED_ERR THEN
3078 fnd_message.raise_error;
3079
3080 x_return_status := 'U'; /* This line should never be executed */
3081 return;
3082
3083 WHEN OTHERS THEN
3084 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
3085 fnd_message.set_token('ROUTINE',
3086 c_pkg_name || '.'|| l_api_name);
3087 fnd_message.set_token('ERRNO', SQLCODE);
3088 fnd_message.set_token('REASON', SQLERRM);
3089
3090 if (fnd_log.LEVEL_EXCEPTION >=
3091 fnd_log.g_current_runtime_level) then
3092 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3093 c_log_head || l_api_name || '.other_err',
3094 FALSE);
3095 end if;
3096 x_return_status := 'U';
3097 if (fnd_log.LEVEL_PROCEDURE >=
3098 fnd_log.g_current_runtime_level) then
3099 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
3100 c_log_head || l_api_name || '.end_after_other',
3101 'x_predicate: '|| x_predicate ||
3102 ', x_return_status:'||x_return_status);
3103 end if;
3104 return;
3105 END get_security_predicate_helper;
3106 -------------------------------------------------------------------------------
3107
3108 ----- Not for external use.
3109 ----- Internal routine which actually implements get_security_predicate.
3110 ----- This is the routine called by get_security_predicate[_w_binds]
3111 --------------------------------------------
3112 PROCEDURE get_security_predicate_intrnl(
3113 p_api_version IN NUMBER,
3114 p_function IN VARCHAR2,
3115 p_object_name IN VARCHAR2,
3116 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
3117 /* Undocumented value: FUNCLIST, FUNCLIST_NOINST */
3118 /* Documented value: GRANTS_ONLY */
3119 p_user_name IN VARCHAR2,
3120 /* stmnt_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= for checking existence. */
3121 p_statement_type IN VARCHAR2,
3122 p_table_alias IN VARCHAR2,
3123 p_with_binds IN VARCHAR2,
3124 x_predicate out NOCOPY varchar2,
3125 x_return_status out NOCOPY varchar2,
3126 x_function_id out NOCOPY NUMBER,
3127 x_object_id out NOCOPY NUMBER,
3128 x_bind_order out NOCOPY VARCHAR2
3129 ) IS
3130
3131 l_api_name CONSTANT VARCHAR2(30) := 'GET_SECURITY_PREDICATE';
3132
3133 -- On addition of any Required parameters the major version needs
3134 -- to change i.e. for eg. 1.X to 2.X.
3135 -- On addition of any Optional parameters the minor version needs
3136 -- to change i.e. for eg. X.6 to X.7.
3137 l_api_version CONSTANT NUMBER := 1.0;
3138 l_sysdate DATE := Sysdate;
3139 l_aggregate_predicate VARCHAR2(32767); /* Must match c_pred_buf_size*/
3140 l_instance_predicate VARCHAR2(32767); /* Must match c_pred_buf_size*/
3141 l_instance_flag BOOLEAN := TRUE;
3142 l_instance_set_flag BOOLEAN := TRUE;
3143 l_inst_group_grantee_type BOOLEAN := FALSE;
3144 l_inst_global_grantee_type BOOLEAN := FALSE;
3145 l_set_group_grantee_type BOOLEAN := FALSE;
3146 l_set_global_grantee_type BOOLEAN := FALSE;
3147 l_inst_instance_type BOOLEAN := FALSE;
3148 l_set_instance_type BOOLEAN := FALSE;
3149 l_global_instance_type BOOLEAN := FALSE;
3150 l_db_object_name varchar2(30);
3151 l_db_pk1_column varchar2(256);
3152 l_db_pk2_column varchar2(256);
3153 l_db_pk3_column varchar2(256);
3154 l_db_pk4_column varchar2(256);
3155 l_db_pk5_column varchar2(256);
3156 l_pk_column_names varchar2(512);
3157 l_pk_orig_column_names varchar2(512);
3158 l_ik_clause varchar2(2048);
3159 l_exact_clause varchar2(2048);
3160 l_user_name_bind varchar2(255);
3161 l_user_name varchar2(80);
3162 l_nrows pls_integer;
3163 l_table_alias varchar2(256);
3164 l_last_instance_set_id NUMBER;
3165 l_last_pred varchar2(4000);
3166 l_need_to_close_pred BOOLEAN;
3167 l_refers_to_grants BOOLEAN;
3168 l_last_was_hextoraw BOOLEAN;
3169 l_pred varchar2(4000);
3170 l_uses_params BOOLEAN;
3171 d_predicate VARCHAR2(4000);
3172 d_instance_set_id number;
3173 d_grant_guid RAW(16);
3174 l_grp_glob_fn BOOLEAN;
3175 l_grp_glob_nofn BOOLEAN;
3176 l_glob_fn BOOLEAN;
3177 l_glob_nofn BOOLEAN;
3178 l_grp_fn BOOLEAN;
3179 l_grp_nofn BOOLEAN;
3180 l_cursor_is_open BOOLEAN;
3181 l_dummy NUMBER;
3182 colon PLS_INTEGER;
3183 l_pop_message BOOLEAN;
3184
3185 /* This cursor determines if there are any grants to GLOBAL grantee, */
3186 /* for a particular instance type */
3187 CURSOR grant_types_global_c (cp_user_name varchar2,
3188 cp_function_id NUMBER,
3192 select 1 from
3189 cp_object_id VARCHAR2,
3190 cp_instance_type VARCHAR2)
3191 IS
3193 dual
3194 where exists
3195 (
3196 SELECT 1
3197 FROM fnd_grants g
3198 WHERE (g.grantee_type = 'GLOBAL')
3199 AND g.object_id = cp_object_id
3200 AND ( (cp_function_id = -1)
3201 OR (g.menu_id in
3202 (select cmf.menu_id
3203 from fnd_compiled_menu_functions cmf
3204 where cmf.function_id = cp_function_id)))
3205 AND ( g.ctx_secgrp_id = -1
3206 OR g.ctx_secgrp_id =
3207 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3208 AND ( g.ctx_resp_id = -1
3209 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3210 AND ( g.ctx_resp_appl_id = -1
3211 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3212 AND ( g.ctx_org_id = -1
3213 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3214 AND g.start_date <= SYSDATE
3215 AND ( g.end_date IS NULL
3216 OR g.end_date >= SYSDATE )
3217 AND g.instance_type = cp_instance_type
3218 );
3219
3220
3221 --Changes for Bug#3867925
3222 -- Fix Non Backward change made for universal person support
3223 --
3224 -- Performance note: This statement has not received the optimizations
3225 -- to the WF User portion of the SQL because the separation of the
3226 -- USER and GROUP clauses prevent that. Since this is only used for
3227 -- deprecated code that is okay.
3228 CURSOR grant_types_group_bkwd_c(cp_user_name varchar2,
3229 cp_function_id NUMBER,
3230 cp_object_id VARCHAR2,
3231 cp_instance_type VARCHAR2)
3232 IS
3233 select 1
3234 from dual
3235 where exists
3236 (
3237 select 1
3238 from fnd_grants g
3239 where (( g.grantee_type = 'USER'
3240 AND g.grantee_key = cp_user_name)
3241 OR (g.grantee_type = 'GROUP'
3242 AND (g.grantee_key in
3243 (select role_name
3244 from wf_user_roles wur
3245 where wur.user_name in
3246 ( (select cp_user_name from dual)
3247 union all
3248 (select incrns.name from wf_local_roles incrns, fnd_user f
3249 where 'HZ_PARTY' = incrns.orig_system
3250 and f.user_name = cp_user_name
3251 and f.person_party_id = incrns.orig_system_id))))))
3252 and g.object_id = cp_object_id
3253 and ((cp_function_id = -1)
3254 or (g.menu_id in
3255 (select cmf.menu_id
3256 from fnd_compiled_menu_functions cmf
3257 where cmf.function_id = cp_function_id)))
3258 and ( g.ctx_secgrp_id = -1
3259 or g.ctx_secgrp_id =
3260 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3261 and ( g.ctx_resp_id = -1
3262 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3263 and ( g.ctx_resp_appl_id = -1
3264 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3265 and ( g.ctx_org_id = -1
3266 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3267 and g.start_date <= SYSDATE
3268 and ( g.end_date IS NULL
3269 OR g.end_date >= SYSDATE )
3270 and g.instance_type = cp_instance_type
3271 );
3272
3273
3274 /* This cursor determines if there are any grants to USER or GROUP */
3275 /* grantee, for a particular instance type */
3276 CURSOR grant_types_group_c (cp_user_name varchar2,
3277 cp_function_id NUMBER,
3278 cp_object_id VARCHAR2,
3279 cp_instance_type VARCHAR2)
3280 IS
3281 select 1 from
3282 dual
3283 where exists
3284 (
3285 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
3286 FROM
3287 ( select /*+ NO_MERGE */ role_name
3288 from wf_user_roles wur,
3289 (
3290 select cp_user_name name from dual
3291 union all
3292 select incr1.name name
3293 from wf_local_roles incr1, fnd_user u1
3294 where 'HZ_PARTY' = incr1.orig_system
3295 and u1.user_name = cp_user_name
3296 and u1.person_party_id = incr1.orig_system_id
3297 and incr1.partition_id = 9 /* HZ_PARTY */
3298 ) incr2
3299 where wur.user_name = incr2.name
3300 ) u2,
3301 fnd_grants g
3302 WHERE rownum = 1
3303 AND g.grantee_key = u2.role_name
3304 and g.object_id = cp_object_id
3308 from fnd_compiled_menu_functions cmf
3305 and ((cp_function_id = -1)
3306 or (g.menu_id in
3307 (select cmf.menu_id
3309 where cmf.function_id = cp_function_id)))
3310 and ( g.ctx_secgrp_id = -1
3311 or g.ctx_secgrp_id =
3312 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3313 and ( g.ctx_resp_id = -1
3314 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3315 and ( g.ctx_resp_appl_id = -1
3316 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3317 and ( g.ctx_org_id = -1
3318 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3319 and g.start_date <= SYSDATE
3320 and ( g.end_date IS NULL
3321 OR g.end_date >= SYSDATE )
3322 and g.instance_type = cp_instance_type
3323 );
3324
3325 /*
3326 ** Note: following are six different cursors that leave out or
3327 ** include different combinations of the following 3 things:
3328 ** Group grantee type (includes User grantee type)
3329 ** Global grantee type
3330 ** function_id (left out if function_id = -1 meaning all functions)
3331 ** Besides those three clauses, the cursors are the same and should
3332 ** all be maintained with whatever changes are made to any one.
3333 */
3334
3335 /* Which instance sets are granted to specific function? */
3336 CURSOR isg_grp_glob_fn_c (cp_user_name varchar2,
3337 cp_function_id NUMBER,
3338 cp_object_id VARCHAR2)
3339 IS
3340 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3341 instance_sets.predicate, instance_sets.instance_set_id,
3342 g.grant_guid
3343 FROM
3344 ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
3345 union all
3346 select role_name
3347 from wf_user_roles wur,
3348 (
3349 select cp_user_name name from dual
3350 union all
3351 select incr1.name name
3352 from wf_local_roles incr1, fnd_user u1
3353 where 'HZ_PARTY' = incr1.orig_system
3354 and u1.user_name = cp_user_name
3355 and u1.person_party_id = incr1.orig_system_id
3356 and incr1.partition_id = 9 /* HZ_PARTY */
3357 ) incr2
3358 where wur.user_name = incr2.name
3359 ) u2,
3360 fnd_grants g,
3361 fnd_object_instance_sets instance_sets
3362 WHERE g.grantee_key = u2.role_name
3363 AND g.instance_type = 'SET'
3364 AND g.object_id = cp_object_id
3365 AND (g.menu_id in
3366 (select cmf.menu_id
3367 from fnd_compiled_menu_functions cmf
3368 where cmf.function_id = cp_function_id))
3369 AND g.instance_set_id = instance_sets.instance_set_id
3370 AND ( g.ctx_secgrp_id = -1
3371 OR g.ctx_secgrp_id =
3372 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3373 AND ( g.ctx_resp_id = -1
3374 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3375 AND ( g.ctx_resp_appl_id = -1
3376 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3377 AND ( g.ctx_org_id = -1
3378 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3379 AND g.start_date <= SYSDATE
3380 AND ( g.end_date IS NULL
3381 OR g.end_date >= SYSDATE )
3382 ORDER BY instance_sets.predicate,
3383 instance_sets.instance_set_id desc;
3384
3385 /* Which instance sets are granted for any function? */
3386 CURSOR isg_grp_glob_nofn_c (cp_user_name varchar2,
3387 cp_object_id VARCHAR2)
3388 IS
3389 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3390 instance_sets.predicate, instance_sets.instance_set_id,
3391 g.grant_guid
3392 FROM
3393 ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
3394 union all
3395 select role_name
3396 from wf_user_roles wur,
3397 (
3398 select cp_user_name name from dual
3399 union all
3400 select incr1.name name
3401 from wf_local_roles incr1, fnd_user u1
3402 where 'HZ_PARTY' = incr1.orig_system
3403 and u1.user_name = cp_user_name
3404 and u1.person_party_id = incr1.orig_system_id
3405 and incr1.partition_id = 9 /* HZ_PARTY */
3406 ) incr2
3407 where wur.user_name = incr2.name
3408 ) u2,
3409 fnd_grants g,
3410 fnd_object_instance_sets instance_sets
3411 WHERE g.grantee_key = u2.role_name
3412 AND g.object_id = cp_object_id
3413 AND g.instance_set_id = instance_sets.instance_set_id
3414 AND ( g.ctx_secgrp_id = -1
3415 OR g.ctx_secgrp_id =
3419 AND ( g.ctx_resp_appl_id = -1
3416 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3417 AND ( g.ctx_resp_id = -1
3418 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3420 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3421 AND ( g.ctx_org_id = -1
3422 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3423 AND g.start_date <= SYSDATE
3424 AND ( g.end_date IS NULL
3425 OR g.end_date >= SYSDATE )
3426 ORDER BY instance_sets.predicate,
3427 instance_sets.instance_set_id desc;
3428
3429 --Changes for Bug#3867925
3430 -- Fix Non Backward change made for universal person support
3431 --
3432 -- Performance note: This statement has not received the optimizations
3433 -- to the WF User portion of the SQL because the separation of the
3434 -- USER and GROUP clauses prevent that. Since this is only used for
3435 -- deprecated code that is okay.
3436 CURSOR isg_grp_glob_nofn_bkwd_c (cp_user_name varchar2,
3437 cp_object_id VARCHAR2)
3438 IS
3439 SELECT instance_sets.predicate, instance_sets.instance_set_id,
3440 g.grant_guid
3441 FROM fnd_grants g,
3442 fnd_object_instance_sets instance_sets
3443 WHERE g.instance_type = 'SET'
3444 AND( (g.grantee_type = 'USER'
3445 AND g.grantee_key = cp_user_name)
3446 OR (g.grantee_type = 'GROUP'
3447 AND (g.grantee_key in
3448 (select role_name
3449 from wf_user_roles wur
3450 where wur.user_name in
3451 ( (select cp_user_name from dual)
3452 union all
3453 (select incrns.name from wf_local_roles incrns, fnd_user f
3454 where 'HZ_PARTY' = incrns.orig_system
3455 and f.user_name = cp_user_name
3456 and f.person_party_id = incrns.orig_system_id)))))
3457 OR (g.grantee_type = 'GLOBAL'))
3458 AND g.object_id = cp_object_id
3459 AND g.instance_set_id = instance_sets.instance_set_id
3460 AND ( g.ctx_secgrp_id = -1
3461 OR g.ctx_secgrp_id =
3462 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3463 AND ( g.ctx_resp_id = -1
3464 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3465 AND ( g.ctx_resp_appl_id = -1
3466 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3467 AND ( g.ctx_org_id = -1
3468 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3469 AND g.start_date <= SYSDATE
3470 AND ( g.end_date IS NULL
3471 OR g.end_date >= SYSDATE )
3472 ORDER BY instance_sets.predicate,
3473 instance_sets.instance_set_id desc;
3474
3475 /* Which instance sets are granted to specific function? */
3476 CURSOR isg_grp_fn_c (cp_user_name varchar2,
3477 cp_function_id NUMBER,
3478 cp_object_id VARCHAR2)
3479 IS
3480 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3481 instance_sets.predicate, instance_sets.instance_set_id,
3482 g.grant_guid
3483 FROM
3484 ( select /*+ NO_MERGE */ role_name
3485 from wf_user_roles wur,
3486 (
3487 select cp_user_name name from dual
3488 union all
3489 select incr1.name name
3490 from wf_local_roles incr1, fnd_user u1
3491 where 'HZ_PARTY' = incr1.orig_system
3492 and u1.user_name = cp_user_name
3493 and u1.person_party_id = incr1.orig_system_id
3494 and incr1.partition_id = 9 /* HZ_PARTY */
3495 ) incr2
3496 where wur.user_name = incr2.name
3497 ) u2,
3498 fnd_grants g,
3499 fnd_object_instance_sets instance_sets
3500 WHERE g.grantee_key = u2.role_name
3501 AND g.object_id = cp_object_id
3502 AND (g.menu_id in
3503 (select cmf.menu_id
3504 from fnd_compiled_menu_functions cmf
3505 where cmf.function_id = cp_function_id))
3506 AND g.instance_set_id = instance_sets.instance_set_id
3507 AND ( g.ctx_secgrp_id = -1
3508 OR g.ctx_secgrp_id =
3509 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3510 AND ( g.ctx_resp_id = -1
3511 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3512 AND ( g.ctx_resp_appl_id = -1
3513 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3514 AND ( g.ctx_org_id = -1
3515 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3516 AND g.start_date <= SYSDATE
3517 AND ( g.end_date IS NULL
3518 OR g.end_date >= SYSDATE )
3519 ORDER BY instance_sets.predicate,
3520 instance_sets.instance_set_id desc;
3521
3522 --Changes for Bug#3867925
3526 -- to the WF User portion of the SQL because the separation of the
3523 -- Fix Non Backward change made for universal person support
3524 --
3525 -- Performance note: This statement has not received the optimizations
3527 -- USER and GROUP clauses prevent that. Since this is only used for
3528 -- deprecated code that is okay.
3529 CURSOR isg_grp_fn_bkwd_c (cp_user_name varchar2,
3530 cp_function_id NUMBER,
3531 cp_object_id VARCHAR2)
3532 IS
3533 SELECT instance_sets.predicate, instance_sets.instance_set_id,
3534 g.grant_guid
3535 FROM fnd_grants g,
3536 fnd_object_instance_sets instance_sets
3537 WHERE g.instance_type = 'SET'
3538 AND ( (g.grantee_type = 'USER'
3539 AND g.grantee_key = cp_user_name)
3540 OR ( g.grantee_type = 'GROUP'
3541 AND (g.grantee_key in
3542 (select role_name
3543 from wf_user_roles wur
3544 where wur.user_name in
3545 ( (select cp_user_name from dual)
3546 union all
3547 (select incrns.name from wf_local_roles incrns, fnd_user f
3548 where 'HZ_PARTY' = incrns.orig_system
3549 and f.user_name = cp_user_name
3550 and f.person_party_id = incrns.orig_system_id))))))
3551 AND g.object_id = cp_object_id
3552 AND (g.menu_id in
3553 (select cmf.menu_id
3554 from fnd_compiled_menu_functions cmf
3555 where cmf.function_id = cp_function_id))
3556 AND g.instance_set_id = instance_sets.instance_set_id
3557 AND ( g.ctx_secgrp_id = -1
3558 OR g.ctx_secgrp_id =
3559 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3560 AND ( g.ctx_resp_id = -1
3561 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3562 AND ( g.ctx_resp_appl_id = -1
3563 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3564 AND ( g.ctx_org_id = -1
3565 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3566 AND g.start_date <= SYSDATE
3567 AND ( g.end_date IS NULL
3568 OR g.end_date >= SYSDATE )
3569 ORDER BY instance_sets.predicate,
3570 instance_sets.instance_set_id desc;
3571
3572
3573 /* Which instance sets are granted for any function? */
3574 CURSOR isg_grp_nofn_c (cp_user_name varchar2,
3575 cp_object_id VARCHAR2)
3576 IS
3577 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3578 instance_sets.predicate, instance_sets.instance_set_id,
3579 g.grant_guid
3580 FROM
3581 ( select /*+ NO_MERGE */ role_name
3582 from wf_user_roles wur,
3583 (
3584 select cp_user_name name from dual
3585 union all
3586 select incr1.name name
3587 from wf_local_roles incr1, fnd_user u1
3588 where 'HZ_PARTY' = incr1.orig_system
3589 and u1.user_name = cp_user_name
3590 and u1.person_party_id = incr1.orig_system_id
3591 and incr1.partition_id = 9 /* HZ_PARTY */
3592 ) incr2
3593 where wur.user_name = incr2.name
3594 ) u2,
3595 fnd_grants g,
3596 fnd_object_instance_sets instance_sets
3597 WHERE g.grantee_key = u2.role_name
3598 AND g.object_id = cp_object_id
3599 AND g.instance_set_id = instance_sets.instance_set_id
3600 AND ( g.ctx_secgrp_id = -1
3601 OR g.ctx_secgrp_id =
3602 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3603 AND ( g.ctx_resp_id = -1
3604 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3605 AND ( g.ctx_resp_appl_id = -1
3606 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3607 AND ( g.ctx_org_id = -1
3608 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3609 AND g.start_date <= SYSDATE
3610 AND ( g.end_date IS NULL
3611 OR g.end_date >= SYSDATE )
3612 ORDER BY instance_sets.predicate,
3613 instance_sets.instance_set_id desc;
3614
3615 --Changes for Bug#3867925
3616 -- Fix Non Backward change made for universal person support
3617 -- Performance note: This statement has not received the optimizations
3618 -- to the WF User portion of the SQL because the separation of the
3619 -- USER and GROUP clauses prevent that. Since this is only used for
3620 -- deprecated code that is okay.
3621 CURSOR isg_grp_nofn_bkwd_c (cp_user_name varchar2,
3622 cp_object_id VARCHAR2)
3623 IS
3624 SELECT instance_sets.predicate, instance_sets.instance_set_id,
3625 g.grant_guid
3626 FROM fnd_grants g, fnd_object_instance_sets instance_sets
3627 WHERE g.instance_type = 'SET'
3628 AND ( (g.grantee_type = 'USER'
3632 (select role_name
3629 AND g.grantee_key = cp_user_name)
3630 OR (g.grantee_type = 'GROUP'
3631 AND (g.grantee_key in
3633 from wf_user_roles wur
3634 where wur.user_name in
3635 ( (select cp_user_name from dual)
3636 union all
3637 (select incrns.name from wf_local_roles incrns, fnd_user f
3638 where 'HZ_PARTY' = incrns.orig_system
3639 and f.user_name = cp_user_name
3640 and f.person_party_id = incrns.orig_system_id))))))
3641 AND g.object_id = cp_object_id
3642 AND g.instance_set_id = instance_sets.instance_set_id
3643 AND ( g.ctx_secgrp_id = -1
3644 OR g.ctx_secgrp_id =
3645 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3646 AND ( g.ctx_resp_id = -1
3647 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3648 AND ( g.ctx_resp_appl_id = -1
3649 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3650 AND ( g.ctx_org_id = -1
3651 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3652 AND g.start_date <= SYSDATE
3653 AND ( g.end_date IS NULL
3654 OR g.end_date >= SYSDATE )
3655 ORDER BY instance_sets.predicate,
3656 instance_sets.instance_set_id desc;
3657
3658
3659 /* Which instance sets are granted to specific function? */
3660 CURSOR isg_glob_fn_c (cp_user_name varchar2,
3661 cp_function_id NUMBER,
3662 cp_object_id VARCHAR2)
3663 IS
3664 SELECT instance_sets.predicate, instance_sets.instance_set_id,
3665 g.grant_guid
3666 FROM fnd_grants g,
3667 fnd_object_instance_sets instance_sets
3668 WHERE g.instance_type = 'SET'
3669 AND (g.grantee_type = 'GLOBAL')
3670 AND g.object_id = cp_object_id
3671 AND (g.menu_id in
3672 (select cmf.menu_id
3673 from fnd_compiled_menu_functions cmf
3674 where cmf.function_id = cp_function_id))
3675 AND g.instance_set_id = instance_sets.instance_set_id
3676 AND ( g.ctx_secgrp_id = -1
3677 OR g.ctx_secgrp_id =
3678 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3679 AND ( g.ctx_resp_id = -1
3680 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3681 AND ( g.ctx_resp_appl_id = -1
3682 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3683 AND ( g.ctx_org_id = -1
3684 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3685 AND g.start_date <= SYSDATE
3686 AND ( g.end_date IS NULL
3687 OR g.end_date >= SYSDATE )
3688 ORDER BY instance_sets.predicate,
3689 instance_sets.instance_set_id desc;
3690
3691 /* Which instance sets are granted for any function? */
3692 CURSOR isg_glob_nofn_c (cp_user_name varchar2,
3693 cp_object_id VARCHAR2)
3694 IS
3695 SELECT instance_sets.predicate, instance_sets.instance_set_id,
3696 g.grant_guid
3697 FROM fnd_grants g,
3698 fnd_object_instance_sets instance_sets
3699 WHERE g.instance_type = 'SET'
3700 AND (g.grantee_type = 'GLOBAL')
3701 AND g.object_id = cp_object_id
3702 AND g.instance_set_id = instance_sets.instance_set_id
3703 AND ( g.ctx_secgrp_id = -1
3704 OR g.ctx_secgrp_id =
3705 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3706 AND ( g.ctx_resp_id = -1
3707 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3708 AND ( g.ctx_resp_appl_id = -1
3709 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
3710 AND ( g.ctx_org_id = -1
3711 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3712 AND g.start_date <= SYSDATE
3713 AND ( g.end_date IS NULL
3714 OR g.end_date >= SYSDATE )
3715 ORDER BY instance_sets.predicate,
3716 instance_sets.instance_set_id desc;
3717
3718
3719 l_object_id number := -2;
3720 l_function_id number := -2;
3721
3722 BEGIN
3723
3724 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
3725 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
3726 c_log_head || l_api_name || '.begin',
3727 c_pkg_name || '.' ||l_api_name|| '(' ||
3728 'p_api_version=>'|| to_char(p_api_version) ||
3729 ', p_function=>'|| p_function ||
3730 ', p_object_name=>'|| p_object_name ||
3731 ', p_grant_instance_type=>'|| p_grant_instance_type ||
3732 ', p_user_name=>'|| p_user_name ||');');
3733 end if;
3734
3735 x_function_id := NULL;
3736 x_object_id := NULL;
3737 x_bind_order := NULL;
3738 x_predicate := NULL;
3739 x_return_status := 'T'; /* Assume Success */
3740
3741 -- For sneeruga fix for bug#4238074- check_function returns 'E'
3745 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
3742 colon := instr(p_user_name, 'PER:');
3743
3744 -- check for call compatibility.
3746 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
3747 fnd_message.set_token('ROUTINE',
3748 c_pkg_name || '.'|| l_api_name);
3749 fnd_message.set_token('REASON',
3750 'Unsupported version '|| to_char(p_api_version)||
3751 ' passed to API; expecting version '||
3752 to_char(l_api_version));
3753 if (fnd_log.LEVEL_EXCEPTION >=
3754 fnd_log.g_current_runtime_level) then
3755 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3756 c_log_head || l_api_name || '.end_bad_api_ver',
3757 FALSE);
3758 end if;
3759 x_return_status := 'U'; /* Unexpected Error */
3760 return;
3761 END IF;
3762
3763 /* default the username if necessary. */
3764 if (p_user_name is NULL) then
3765 l_user_name := SYS_CONTEXT('FND','USER_NAME');
3766 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3767 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3768 c_log_head || l_api_name || '.null_username',
3769 'l_user_name= '||l_user_name);
3770 end if;
3771 else
3772 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3773 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3774 c_log_head || l_api_name || '.passed_uname',
3775 'l_user_name= '||l_user_name);
3776 end if;
3777
3778 if ( (fnd_data_security.DISALLOW_DEPRECATED = 'Y')
3779 and (substr(p_user_name, 1, LENGTH('GET_MNUIDS_NBVCXDS')) <>
3780 'GET_MNUIDS_NBVCXDS')
3781 and ( (p_user_name <> SYS_CONTEXT('FND','USER_NAME'))
3782 or ( (p_user_name is not null)
3783 and (SYS_CONTEXT('FND','USER_NAME') is null)))) then
3784 /* In R12 we do not allow passing values other than */
3785 /* the current user name (which is the default), */
3786 /* so we raise a runtime exception if that deprecated */
3787 /* kind of call is made to this routine. */
3788 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
3789 fnd_message.set_token('ROUTINE',
3790 c_pkg_name || '.'|| l_api_name);
3791 fnd_message.set_token('REASON',
3792 'Invalid API call. Parameter p_user_name: '||p_user_name||
3793 ' was passed to API '||c_pkg_name || '.'|| l_api_name ||
3794 '. p_object_name: '||p_object_name||'. '||
3795 ' In Release 12 and beyond the p_user_name parameter '||
3796 'is unsupported, and any product team that passes it '||
3797 'must correct their code because it does not work '||
3798 'correctly. Please see the deprecated API document at '||
3799 'http://files.oraclecorp.com/content/AllPublic/'||
3800 'SharedFolders/ATG%20Requirements-Public/R12/'||
3801 'Requirements%20Definition%20Document/'||
3802 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
3803 'Oracle employees who encounter this error should log '||
3804 'a bug against the product that owns the call to this '||
3805 'routine, which is likely the owner of the object that '||
3806 'was passed to this routine: '||
3807 p_object_name);
3808 if (fnd_log.LEVEL_EXCEPTION >=
3809 fnd_log.g_current_runtime_level) then
3810 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3811 c_log_head || l_api_name || '.end_depr_param',
3812 FALSE);
3813 end if;
3814 fnd_message.raise_error;
3815 end if;
3816 l_user_name := p_user_name;
3817 end if;
3818
3819
3820 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3821 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3822 c_log_head || l_api_name || '.b4cachechk',
3823 ' g_gsp_function: '|| g_gsp_function ||
3824 ' p_function: '|| p_function ||
3825 ' g_gsp_object_name: '|| g_gsp_object_name ||
3826 ' p_object_name: '|| p_object_name ||
3827 ' g_gsp_grant_instance_type: '|| p_grant_instance_type ||
3828 ' p_grant_instance_type: '|| p_grant_instance_type ||
3829 ' g_gsp_user_name: '|| g_gsp_user_name ||
3830 ' l_user_name: '|| l_user_name ||
3831 ' g_gsp_statement_type: '|| g_gsp_statement_type ||
3832 ' p_statement_type: '|| p_statement_type ||
3833 ' g_gsp_table_alias: '|| g_gsp_table_alias ||
3834 ' p_table_alias: '|| p_table_alias ||
3835 ' g_gsp_grant_instance_type: '||g_gsp_grant_instance_type||
3836 ' p_grant_instance_type: '|| p_grant_instance_type ||
3837 ' g_gsp_with_binds: '|| g_gsp_with_binds ||
3838 ' p_with_binds: '|| p_with_binds ||
3839 ' g_gsp_context_user_id: '||g_gsp_context_user_id ||
3840 ' SYS_CONTEXT(''FND'',''USER_ID''): '||
3844 SYS_CONTEXT('FND','RESP_ID') ||
3841 SYS_CONTEXT('FND','USER_ID') ||
3842 ' g_gsp_context_resp_id: '||g_gsp_context_resp_id ||
3843 ' SYS_CONTEXT(''FND'',''RESP_ID''): '||
3845 ' g_gsp_context_secgrpid: '|| g_gsp_context_secgrpid ||
3846 ' SYS_CONTEXT(''FND'',''SECURITY_GROUP_ID''): '||
3847 SYS_CONTEXT('FND','SECURITY_GROUP_ID') ||
3848 ' g_gsp_context_resp_appl_id: '||
3849 g_gsp_context_resp_appl_id ||
3850 ' SYS_CONTEXT(''FND'',''RESP_APPL_ID''): '||
3851 SYS_CONTEXT('FND','RESP_APPL_ID') ||
3852 ' g_gsp_context_org_id: '||g_gsp_context_org_id||
3853 ' SYS_CONTEXT(''FND'', ''ORG_ID''): '||
3854 SYS_CONTEXT('FND', 'ORG_ID')
3855 );
3856 end if;
3857
3858
3859 /* Check one level cache to see if we have this value cached already*/
3860 if ( (g_gsp_function = p_function
3861 or (g_gsp_function is NULL and p_function is NULL))
3862 AND (g_gsp_object_name = p_object_name
3863 or (g_gsp_object_name is NULL and p_object_name is NULL))
3864 AND (g_gsp_grant_instance_type = p_grant_instance_type)
3865 AND (g_gsp_user_name = l_user_name
3866 or (g_gsp_user_name is NULL and l_user_name is NULL))
3867 AND (g_gsp_statement_type = p_statement_type)
3868 AND ( g_gsp_table_alias = p_table_alias
3869 or (g_gsp_table_alias is NULL and p_table_alias is NULL))
3870 AND (g_gsp_with_binds = p_with_binds)
3871 AND (g_gsp_context_user_id = SYS_CONTEXT('FND','USER_ID'))
3872 AND (g_gsp_context_resp_id = SYS_CONTEXT('FND','RESP_ID'))
3873 AND (g_gsp_context_secgrpid =
3874 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
3875 AND (g_gsp_context_resp_appl_id =
3876 SYS_CONTEXT('FND','RESP_APPL_ID'))
3877 AND ( (g_gsp_context_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
3878 or ( g_gsp_context_org_id is NULL
3879 and SYS_CONTEXT('FND', 'ORG_ID') is NULL))) then
3880 x_predicate := g_gsp_predicate;
3881 x_return_status := g_gsp_return_status;
3882 x_object_id := g_gsp_object_id;
3883 x_function_id := g_gsp_function_id;
3884 x_bind_order := g_gsp_bind_order;
3885 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
3886 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
3887 c_log_head || l_api_name || '.end_cachehit',
3888 'x_predicate: '|| x_predicate ||
3889 ', x_return_status:'||x_return_status||
3890 ', x_object_id:'||x_object_id||
3891 ', x_function_id:'||x_function_id||
3892 ', x_bind_order:'||x_bind_order
3893 );
3894 end if;
3895 return;
3896 end if;
3897
3898 -- Check to make sure we're not using unsupported statement_type
3899 if ( (p_statement_type <> 'VPD')
3900 AND (p_statement_type <> 'BASE' /* Deprecated, same as VPD */)
3901 AND (p_statement_type <> 'OTHER')
3902 AND (p_statement_type <> 'EXISTS')) then
3903 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
3904 fnd_message.set_token('ROUTINE',
3905 c_pkg_name || '.'|| l_api_name);
3906 fnd_message.set_token('REASON',
3907 'Unsupported p_statement_type: '|| p_statement_type);
3908 if (fnd_log.LEVEL_EXCEPTION >=
3909 fnd_log.g_current_runtime_level) then
3910 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3911 c_log_head || l_api_name || '.end_bad_stm_typ',
3912 FALSE);
3913 end if;
3914 x_return_status := 'U'; /* Unexpected Error */
3915 return;
3916 end if;
3917
3918
3919 /* Make sure that the FND_COMPILED_MENU_FUNCTIONS table is compiled */
3920 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
3921 FND_FUNCTION.FAST_COMPILE;
3922 end if;
3923
3924 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3925 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3926 c_log_head || l_api_name || '.step1start',
3927 'p_grant_instance_type:'||p_grant_instance_type);
3928 end if;
3929
3930 /* check if we need to call through to old routine */
3931 IF ( (p_grant_instance_type = 'FUNCLIST')
3932 OR (p_grant_instance_type = 'FUNCLIST_NOINST')
3933 OR (p_grant_instance_type = 'GRANTS_ONLY'))THEN
3934 /* If this is one of the modes that require the old-style */
3935 /* statement, just call the old code for that. */
3936 get_security_predicate_helper(
3937 p_function,
3938 p_object_name,
3939 p_grant_instance_type,
3940 p_user_name,
3941 p_statement_type,
3942 x_predicate,
3943 x_return_status,
3944 p_table_alias);
3945 return;
3946 end if;
3947
3948 -- Check to make sure a valid role is passed or defaulted for user_name
3949 if (check_user_role (l_user_name) = 'F') then
3953 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
3950
3951 -- If we got here then the grantee will never be found because
3952 -- it isn't even a role, so we know there won't be a matching grant.
3954 fnd_message.set_token('ROUTINE',
3955 c_pkg_name || '.'|| l_api_name);
3956 fnd_message.set_token('REASON',
3957 'The user_name passed or defaulted is not a valid user_name '||
3958 'in wf_user_roles. '||
3959 'Invalid user_name: '||l_user_name ||
3960 ' Passed p_user_name: '||p_user_name);
3961 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
3962 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
3963 c_log_head || l_api_name || '.end_no_wf_user_role',
3964 FALSE);
3965 end if;
3966
3967 l_aggregate_predicate := '1=2';
3968 x_return_status := 'E'; /* Error condition */
3969 goto return_and_cache;
3970 end if;
3971
3972 /* Set up flags depending on which mode we are running in. */
3973 IF (p_grant_instance_type = C_TYPE_INSTANCE) THEN
3974 if (fnd_log.LEVEL_STATEMENT >=
3975 fnd_log.g_current_runtime_level) then
3976 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3977 c_log_head || l_api_name || '.chk_instance',
3978 ' l_instance_set_flag := FALSE ');
3979 end if;
3980 l_instance_set_flag:= FALSE;
3981 ELSIF (p_grant_instance_type = C_TYPE_SET) THEN
3982 if (fnd_log.LEVEL_STATEMENT >=
3983 fnd_log.g_current_runtime_level) then
3984 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3985 c_log_head || l_api_name || '.chk_set',
3986 ' l_instance_flag := FALSE ');
3987 end if;
3988 l_instance_flag:= FALSE;
3989 ELSIF (p_grant_instance_type = 'UNIVERSAL') THEN
3990 if (fnd_log.LEVEL_STATEMENT >=
3991 fnd_log.g_current_runtime_level) then
3992 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
3993 c_log_head || l_api_name || '.chk_universal',
3994 ' ');
3995 end if;
3996 END IF;
3997
3998
3999 -- Get the key columns from the user name
4000 -- We are not checking for NULL returns (meaning user not in wf_roles)
4001 -- because right now we allow checking of grants to users not in
4002 -- wf_roles.
4003 get_name_bind(l_user_name,
4004 l_user_name_bind);
4005
4006
4007 if (p_object_name is NULL) THEN
4008 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4009 fnd_message.set_token('ROUTINE',
4010 c_pkg_name || '.'|| l_api_name);
4011 fnd_message.set_token('REASON',
4012 'The parameter p_object_name can not be NULL.');
4013 if (fnd_log.LEVEL_EXCEPTION >=
4014 fnd_log.g_current_runtime_level) then
4015 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4016 c_log_head || l_api_name || '.end_null_obj',
4017 FALSE);
4018 end if;
4019 x_return_status := 'U';
4020 return;
4021 END IF;
4022
4023 if(p_object_name = 'GLOBAL') then
4024 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4025 fnd_message.set_token('ROUTINE',
4026 c_pkg_name || '.'|| l_api_name);
4027 fnd_message.set_token('REASON',
4028 'The parameter p_object_name can not be ''GLOBAL''. ');
4029 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
4030 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4031 c_log_head || l_api_name || '.end_glob_obj',
4032 FALSE);
4033 end if;
4034 x_return_status := 'U';
4035 return;
4036 else /* Normal case */
4037 /* Get the primary key lists and info for this object */
4038 x_return_status := get_pk_information(p_object_name,
4039 l_db_pk1_column ,
4040 l_db_pk2_column ,
4041 l_db_pk3_column ,
4042 l_db_pk4_column ,
4043 l_db_pk5_column ,
4044 l_pk_column_names ,
4045 l_ik_clause,
4046 l_exact_clause,
4047 l_pk_orig_column_names,
4048 l_db_object_name,
4049 p_table_alias,
4050 'GNT');
4051 if (x_return_status <> 'T') then
4052 if (fnd_log.LEVEL_PROCEDURE >=
4053 fnd_log.g_current_runtime_level) then
4054 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
4055 c_log_head || l_api_name || '.end_pk_info_err',
4056 'returning status: '|| x_return_status);
4057 end if;
4058 /* There will be a message on the msg dict stack. */
4059 return; /* We will return the x_return_status as out param */
4060 end if;
4061
4062 if (p_table_alias is not NULL) then
4063 l_table_alias := p_table_alias || '.';
4067 else
4064 l_pk_orig_column_names := l_table_alias ||
4065 replace (l_pk_orig_column_names, ', ',
4066 ', '||l_table_alias);
4068 l_table_alias := NULL;
4069 end if;
4070
4071 l_object_id :=get_object_id(p_object_name );
4072 if (l_object_id is NULL) THEN
4073 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4074 fnd_message.set_token('ROUTINE',
4075 c_pkg_name || '.'|| l_api_name);
4076 fnd_message.set_token('REASON',
4077 'The parameter value p_object_name is not a valid object.'||
4078 ' p_object_name:'||p_object_name);
4079 if (fnd_log.LEVEL_EXCEPTION >=
4080 fnd_log.g_current_runtime_level) then
4081 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4082 c_log_head || l_api_name || '.end_bad_obj',
4083 FALSE);
4084 end if;
4085 x_return_status := 'U';
4086 return;
4087 END IF;
4088 end if;
4089
4090 if(p_function is NULL) then
4091 l_function_id := -1;
4092 else
4093 l_function_id := get_function_id(p_function);
4094 if (l_function_id is NULL) THEN
4095 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4096 fnd_message.set_token('ROUTINE',
4097 c_pkg_name || '.'|| l_api_name);
4098 fnd_message.set_token('REASON',
4099 'The parameter value p_function is not a valid function name.'||
4100 ' p_function:'||p_function);
4101 if (fnd_log.LEVEL_EXCEPTION >=
4102 fnd_log.g_current_runtime_level) then
4103 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4104 c_log_head || l_api_name || '.end_bad_func',
4105 FALSE);
4106 end if;
4107 x_return_status := 'U';
4108 return;
4109 END IF;
4110 end if;
4111
4112 -- Performance note: we are doing up to six SQL statements in order
4113 -- to determine whether there exists all the possible combinations
4114 -- of grantee_type either GROUP (including USER) or GLOBAL
4115 -- and instance_type GLOBAL, INSTANCE, or SET.
4116 -- We had originally tried to check all these possibilities with
4117 -- a single statement, but it didn't perform adequately due to
4118 -- the ORs and sorting necessary, so this is the best solution.
4119 -- Perhaps if we get the WF folks to put GLOBAL (all) users into the
4120 -- a group in WF_USER_ROLES then we could go down to only 3 SQL
4121 -- or even one.
4122
4123 --
4124 -- Do not check for GLOBAL grants when the user is GUEST
4125 --
4126 if (l_user_name <> 'GUEST') then
4127 --
4128 -- check for 'GLOBAL' instance type
4129 --
4130 /* See if there are any grants with */
4131 /* grantee_type='GLOBAL' and instance_type = 'GLOBAL' that apply*/
4132 l_dummy := -1;
4133 open grant_types_global_c (l_user_name,
4134 l_function_id,
4135 l_object_id,
4136 'GLOBAL');
4137 fetch grant_types_global_c into l_dummy;
4138 IF(grant_types_global_c%NOTFOUND) THEN
4139 NULL;
4140 else
4141 if(l_dummy = 1) then
4142 l_global_instance_type := TRUE;
4143 close grant_types_global_c;
4144 goto global_inst_type;
4145 end if;
4146 end if;
4147 close grant_types_global_c;
4148 end if;
4149
4150 /* See if there are any grants with */
4151 /* grantee_type='GROUP' and instance_type = 'GLOBAL' that apply*/
4152 l_dummy := -1;
4153
4154 --Changes for Bug#3867925
4155 -- Fix Non Backward change made for universal person support
4156 /*open grant_types_group_c (l_user_name,
4157 l_function_id,
4158 l_object_id,
4159 'GLOBAL');
4160 fetch grant_types_group_c into l_dummy;
4161 IF(grant_types_group_c%NOTFOUND) THEN
4162 NULL;
4163 else
4164 if(l_dummy = 1) then
4165 l_global_instance_type := TRUE;
4166 close grant_types_group_c;
4167 goto global_inst_type;
4168 end if;
4169 end if;
4170 close grant_types_group_c;*/
4171
4172 if (colon <> 0) then
4173 open grant_types_group_bkwd_c (l_user_name,
4174 l_function_id,
4175 l_object_id,
4176 'GLOBAL');
4177 fetch grant_types_group_bkwd_c into l_dummy;
4178 IF(grant_types_group_bkwd_c%NOTFOUND) THEN
4179 NULL;
4180 else
4181 if(l_dummy = 1) then
4182 l_global_instance_type := TRUE;
4183 close grant_types_group_bkwd_c;
4184 goto global_inst_type;
4185 end if;
4189 open grant_types_group_c (l_user_name,
4186 end if;
4187 close grant_types_group_bkwd_c;
4188 else
4190 l_function_id,
4191 l_object_id,
4192 'GLOBAL');
4193 fetch grant_types_group_c into l_dummy;
4194 IF(grant_types_group_c%NOTFOUND) THEN
4195 NULL;
4196 else
4197 if(l_dummy = 1) then
4198 l_global_instance_type := TRUE;
4199 close grant_types_group_c;
4200 goto global_inst_type;
4201 end if;
4202 end if;
4203 close grant_types_group_c;
4204 end if;
4205
4206 --
4207 -- check for 'SET' instance type
4208 --
4209 if l_instance_set_flag then
4210 /* See if there are any grants with */
4211 --
4212 -- Do not check for GLOBAL grants when the user is GUEST
4213 --
4214 if (l_user_name <> 'GUEST') then
4215 /* grantee_type='GLOBAL' and instance_type = 'SET' that apply*/
4216 l_dummy := -1;
4217 open grant_types_global_c (l_user_name,
4218 l_function_id,
4219 l_object_id,
4220 'SET');
4221 fetch grant_types_global_c into l_dummy;
4222 IF(grant_types_global_c%NOTFOUND) THEN
4223 NULL;
4224 else
4225 if(l_dummy = 1) then
4226 l_set_instance_type := TRUE;
4227 l_set_global_grantee_type := TRUE;
4228 end if;
4229 end if;
4230 close grant_types_global_c;
4231 end if;
4232
4233
4234 /* See if there are any grants with */
4235 /* grantee_type='GROUP' and instance_type = 'SET' that apply*/
4236
4237 l_dummy := -1;
4238
4239 --Changes for Bug#3867925
4240 -- Fix Non Backward change made for universal person support
4241 /*open grant_types_group_c (l_user_name,
4242 l_function_id,
4243 l_object_id,
4244 'SET');
4245 fetch grant_types_group_c into l_dummy;
4246 IF(grant_types_group_c%NOTFOUND) THEN
4247 NULL;
4248 else
4249 if(l_dummy = 1) then
4250 l_set_instance_type := TRUE;
4251 l_set_group_grantee_type := TRUE;
4252 end if;
4253 end if;
4254 close grant_types_group_c;*/
4255
4256 if (colon <> 0) then
4257 open grant_types_group_bkwd_c (l_user_name,
4258 l_function_id,
4259 l_object_id,
4260 'SET');
4261 fetch grant_types_group_bkwd_c into l_dummy;
4262 IF(grant_types_group_bkwd_c%NOTFOUND) THEN
4263 NULL;
4264 else
4265 if(l_dummy = 1) then
4266 l_set_instance_type := TRUE;
4267 l_set_group_grantee_type := TRUE;
4268 end if;
4269 end if;
4270 close grant_types_group_bkwd_c;
4271 else
4272 open grant_types_group_c (l_user_name,
4273 l_function_id,
4274 l_object_id,
4275 'SET');
4276 fetch grant_types_group_c into l_dummy;
4277 IF(grant_types_group_c%NOTFOUND) THEN
4278 NULL;
4279 else
4280 if(l_dummy = 1) then
4281 l_set_instance_type := TRUE;
4282 l_set_group_grantee_type := TRUE;
4283 end if;
4284 end if;
4285 close grant_types_group_c;
4286 end if;
4287
4288 end if; /* l_instance_set_flag */
4289
4290 --
4291 -- check for 'INSTANCE' instance type
4292 --
4293 if l_instance_flag then
4294 /* See if there are any grants with */
4295 --
4296 -- Do not check for GLOBAL grants when the user is GUEST
4297 --
4298 if (l_user_name <> 'GUEST') then
4299
4300 /* grantee_type='GLOBAL' and instance_type = 'INSTANCE' that apply*/
4301 l_dummy := -1;
4302 open grant_types_global_c (l_user_name,
4303 l_function_id,
4304 l_object_id,
4305 'INSTANCE');
4306 fetch grant_types_global_c into l_dummy;
4307 IF(grant_types_global_c%NOTFOUND) THEN
4308 NULL;
4309 else
4310 if(l_dummy = 1) then
4311 l_inst_instance_type := TRUE;
4312 l_inst_global_grantee_type := TRUE;
4313 end if;
4314 end if;
4315 close grant_types_global_c;
4316 end if;
4317
4318 /* See if there are any grants with */
4322 --Changes for Bug#3867925
4319 /* grantee_type='GROUP' and instance_type = 'INSTANCE' that apply*/
4320 l_dummy := -1;
4321
4323 -- Fix Non Backward change made for universal person support
4324 /*open grant_types_group_c (l_user_name,
4325 l_function_id,
4326 l_object_id,
4327 'INSTANCE');
4328 fetch grant_types_group_c into l_dummy;
4329 IF(grant_types_group_c%NOTFOUND) THEN
4330 NULL;
4331 else
4332 if(l_dummy = 1) then
4333 l_inst_instance_type := TRUE;
4334 l_inst_group_grantee_type := TRUE;
4335 end if;
4336 end if;
4337 close grant_types_group_c;*/
4338
4339 if (colon <> 0) then
4340 open grant_types_group_bkwd_c (l_user_name,
4341 l_function_id,
4342 l_object_id,
4343 'INSTANCE');
4344 fetch grant_types_group_bkwd_c into l_dummy;
4345 IF(grant_types_group_bkwd_c%NOTFOUND) THEN
4346 NULL;
4347 else
4348 if(l_dummy = 1) then
4349 l_inst_instance_type := TRUE;
4350 l_inst_group_grantee_type := TRUE;
4351 end if;
4352 end if;
4353 close grant_types_group_bkwd_c;
4354 else
4355 open grant_types_group_c (l_user_name,
4356 l_function_id,
4357 l_object_id,
4358 'INSTANCE');
4359 fetch grant_types_group_c into l_dummy;
4360 IF(grant_types_group_c%NOTFOUND) THEN
4361 NULL;
4362 else
4363 if(l_dummy = 1) then
4364 l_inst_instance_type := TRUE;
4365 l_inst_group_grantee_type := TRUE;
4366 end if;
4367 end if;
4368 close grant_types_group_c;
4369 end if;
4370
4371 end if; /* l_instance_flag */
4372
4373 <<global_inst_type>>
4374
4375 /* If we have a global instance type grant, then all rows are */
4376 /* in scope, so just return 1=1 */
4377 if(l_global_instance_type = TRUE) then
4378 l_aggregate_predicate := '1=1';
4379 x_return_status := 'T';
4380 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
4381 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4382 c_log_head || l_api_name || '.11global_inst',
4383 'l_aggregate_predicate: '|| l_aggregate_predicate ||
4384 ', x_return_status:'||x_return_status);
4385 end if;
4386 goto return_and_cache;
4387 end if;
4388
4389 /* If there are no instance sets and we aren't looking for */
4390 /* instances, then there won't be any rows returned by the */
4391 /* predicate so return (1=2) */
4392 if(l_global_instance_type = FALSE and
4393 l_inst_instance_type = FALSE and
4394 l_set_instance_type = FALSE) then
4395 l_aggregate_predicate := '1=2';
4396 x_return_status := 'T';
4397 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
4398 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4399 c_log_head || l_api_name || '.12no_inst',
4400 'l_aggregate_predicate: '|| l_aggregate_predicate ||
4401 ', x_return_status:'||x_return_status);
4402 end if;
4403 goto return_and_cache;
4404 end if;
4405
4406 /* If we have an instance type grant, but no recognized grantee, */
4407 /* that is a data error, so signal that error */
4408 if(l_inst_instance_type = TRUE and
4409 l_inst_group_grantee_type = FALSE and
4410 l_inst_global_grantee_type = FALSE) then
4411 l_set_instance_type := TRUE;
4412 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
4413 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4414 c_log_head || l_api_name || '.err_inst_no_gnt_typ',
4415 'x_predicate: '|| x_predicate);
4416 end if;
4417 end if;
4418
4419 /* Build up the instance set part of the predicate */
4420 l_last_pred := '*NO_PRED*';
4421 if(l_set_instance_type = TRUE) then
4422 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
4423 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4424 c_log_head || l_api_name || '.in_set_instyp',
4425 '.');
4426 end if;
4427
4428 l_last_instance_set_id := -11162202;
4429 l_need_to_close_pred := FALSE;
4430 l_refers_to_grants := FALSE;
4431
4432 l_grp_glob_fn := FALSE;
4433 l_grp_glob_nofn := FALSE;
4434 l_grp_fn := FALSE;
4435 l_grp_nofn := FALSE;
4436 l_glob_fn := FALSE;
4437 l_glob_nofn := FALSE;
4438
4439 /* Open one of six different cursors */
4440 if ( l_set_group_grantee_type
4444 l_function_id,
4441 AND l_set_global_grantee_type
4442 AND l_function_id <> -1) then
4443 OPEN isg_grp_glob_fn_c ( l_user_name,
4445 l_object_id);
4446 l_grp_glob_fn := TRUE;
4447 if (fnd_log.LEVEL_STATEMENT >=
4448 fnd_log.g_current_runtime_level) then
4449 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4450 c_log_head || l_api_name || '.open_grp_glob_fn_cursor',
4451 ' open');
4452 end if;
4453 elsif( l_set_group_grantee_type
4454 AND l_set_global_grantee_type
4455 AND l_function_id = -1) then
4456 --Changes for Bug#3867925
4457 --- Fix Non Backward change made for universal person support
4458 /*OPEN isg_grp_glob_nofn_c ( l_user_name,
4459 l_object_id);
4460 l_grp_glob_nofn := TRUE;
4461 if (fnd_log.LEVEL_STATEMENT >=
4462 fnd_log.g_current_runtime_level) then
4463 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4464 c_log_head || l_api_name || '.open_grp_glob_nofn_cursor',
4465 ' open');
4466 end if;*/
4467 if (colon <> 0) then
4468 OPEN isg_grp_glob_nofn_bkwd_c ( l_user_name,
4469 l_object_id);
4470 l_grp_glob_nofn := TRUE;
4471 if (fnd_log.LEVEL_STATEMENT >=
4472 fnd_log.g_current_runtime_level) then
4473 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4474 c_log_head || l_api_name ||
4475 '.open_grp_glob_nofn_bkwd_cursor',
4476 ' open');
4477 end if;
4478 else
4479 OPEN isg_grp_glob_nofn_c ( l_user_name,
4480 l_object_id);
4481 l_grp_glob_nofn := TRUE;
4482 if (fnd_log.LEVEL_STATEMENT >=
4483 fnd_log.g_current_runtime_level) then
4484 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4485 c_log_head || l_api_name || '.open_grp_glob_nofn_cursor',
4486 ' open');
4487 end if;
4488 end if;
4489 elsif( l_set_group_grantee_type
4490 AND (NOT l_set_global_grantee_type)
4491 AND l_function_id <> -1) then
4492 /*OPEN isg_grp_fn_c ( l_user_name,
4493 l_function_id,
4494 l_object_id);
4495 l_grp_fn := TRUE;
4496 if (fnd_log.LEVEL_STATEMENT >=
4497 fnd_log.g_current_runtime_level) then
4498 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4499 c_log_head || l_api_name || '.open_grp_fn_cursor',
4500 ' open');
4501 end if;*/
4502
4503 if (colon <> 0 ) then
4504 OPEN isg_grp_fn_bkwd_c (l_user_name,
4505 l_function_id,
4506 l_object_id);
4507 l_grp_fn := TRUE;
4508 if (fnd_log.LEVEL_STATEMENT >=
4509 fnd_log.g_current_runtime_level) then
4510 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4511 c_log_head || l_api_name || '.open_grp_fn_bkwd_cursor',
4512 ' open');
4513 end if;
4514 else
4515 OPEN isg_grp_fn_c (l_user_name,
4516 l_function_id,
4517 l_object_id);
4518 l_grp_fn := TRUE;
4519 if (fnd_log.LEVEL_STATEMENT >=
4520 fnd_log.g_current_runtime_level) then
4521 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4522 c_log_head || l_api_name || '.open_grp_fn_cursor',
4523 ' open');
4524 end if;
4525 end if;
4526 elsif( l_set_group_grantee_type
4527 AND NOT l_set_global_grantee_type
4528 AND l_function_id = -1) then
4529 --Changes for Bug#3867925
4530 -- Fix Non Backward change made for universal person support
4531 /*OPEN isg_grp_nofn_c ( l_user_name,
4532 l_object_id);
4533 l_grp_nofn := TRUE;
4534 if (fnd_log.LEVEL_STATEMENT >=
4535 fnd_log.g_current_runtime_level) then
4536 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4537 c_log_head || l_api_name || '.open_grp_nofn_cursor',
4538 ' open');
4539 end if;*/
4540 if (colon <> 0) then
4541 OPEN isg_grp_nofn_bkwd_c (l_user_name,
4542 l_object_id);
4543 l_grp_nofn := TRUE;
4544 if (fnd_log.LEVEL_STATEMENT >=
4545 fnd_log.g_current_runtime_level) then
4546 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4547 c_log_head || l_api_name || '.open_grp_nofn_bkwd_cursor',
4548 ' open');
4549 end if;
4550 else
4554 if (fnd_log.LEVEL_STATEMENT >=
4551 OPEN isg_grp_nofn_c (l_user_name,
4552 l_object_id);
4553 l_grp_nofn := TRUE;
4555 fnd_log.g_current_runtime_level) then
4556 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4557 c_log_head || l_api_name || '.open_grp_nofn_cursor',
4558 ' open');
4559 end if;
4560 end if;
4561 elsif(NOT l_set_group_grantee_type
4562 AND l_set_global_grantee_type
4563 AND l_function_id <> -1) then
4564 OPEN isg_glob_fn_c (l_user_name,
4565 l_function_id,
4566 l_object_id);
4567 l_glob_fn := TRUE;
4568 if (fnd_log.LEVEL_STATEMENT >=
4569 fnd_log.g_current_runtime_level) then
4570 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4571 c_log_head || l_api_name || '.open_glob_fn_cursor',
4572 ' open');
4573 end if;
4574 elsif(NOT l_set_group_grantee_type
4575 AND l_set_global_grantee_type
4576 AND l_function_id = -1) then
4577 OPEN isg_glob_nofn_c (l_user_name,
4578 l_object_id);
4579 l_glob_nofn := TRUE;
4580 if (fnd_log.LEVEL_STATEMENT >=
4581 fnd_log.g_current_runtime_level) then
4582 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4583 c_log_head || l_api_name || '.open_glob_nofn_cursor',
4584 ' open');
4585 end if;
4586 else
4587 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4588 fnd_message.set_token('ROUTINE',
4589 c_pkg_name || '.'|| l_api_name);
4590 fnd_message.set_token('REASON',
4591 ' Fell through where we shouldnt have (1)');
4592 if (fnd_log.LEVEL_EXCEPTION >=
4593 fnd_log.g_current_runtime_level) then
4594 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4595 c_log_head || l_api_name || '.end_fallthru1',
4596 FALSE);
4597 end if;
4598 x_return_status := 'U';
4599 return;
4600 end if;
4601
4602 l_cursor_is_open := TRUE;
4603 LOOP
4604 if (l_grp_glob_fn) then
4605 if (fnd_log.LEVEL_STATEMENT >=
4606 fnd_log.g_current_runtime_level) then
4607 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4608 c_log_head || l_api_name || '.fetch_grp_glob_fn_cursor',
4609 ' fetch');
4610 end if;
4611 FETCH isg_grp_glob_fn_c INTO d_predicate, d_instance_set_id,
4612 d_grant_guid;
4613 if (isg_grp_glob_fn_c%notfound) then
4614 close isg_grp_glob_fn_c;
4615 l_cursor_is_open := FALSE;
4616 exit; -- exit loop
4617 end if;
4618 elsif (l_grp_glob_nofn) then
4619 --Changes for Bug#3867925
4620 -- Fix Non Backward change made for universal person support
4621 /*if (fnd_log.LEVEL_STATEMENT >=
4622 fnd_log.g_current_runtime_level) then
4623 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4624 c_log_head || l_api_name || '.fetch_grp_glob_nofn_cursor',
4625 ' fetch');
4626 end if;
4627 FETCH isg_grp_glob_nofn_c INTO d_predicate, d_instance_set_id,
4628 d_grant_guid;
4629 if (isg_grp_glob_nofn_c%notfound) then
4630 close isg_grp_glob_nofn_c;
4631 l_cursor_is_open := FALSE;
4632 exit; -- exit loop
4633 end if;*/
4634
4635 if (colon <> 0) then
4636 if (fnd_log.LEVEL_STATEMENT >=
4637 fnd_log.g_current_runtime_level) then
4638 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4639 c_log_head || l_api_name ||
4640 '.fetch_grp_glob_nofn_bkwd_cursor',
4641 ' fetch');
4642 end if;
4643 FETCH isg_grp_glob_nofn_bkwd_c
4644 INTO d_predicate, d_instance_set_id, d_grant_guid;
4645 if (isg_grp_glob_nofn_bkwd_c%notfound) then
4646 close isg_grp_glob_nofn_bkwd_c;
4647 l_cursor_is_open := FALSE;
4648 exit; -- exit loop
4649 end if;
4650 else
4651 if (fnd_log.LEVEL_STATEMENT >=
4652 fnd_log.g_current_runtime_level) then
4653 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4654 c_log_head || l_api_name||'.fetch_grp_glob_nofn_cursor',
4655 ' fetch');
4656 end if;
4657 FETCH isg_grp_glob_nofn_c
4658 INTO d_predicate, d_instance_set_id,
4659 d_grant_guid;
4663 exit; -- exit loop
4660 if (isg_grp_glob_nofn_c%notfound) then
4661 close isg_grp_glob_nofn_c;
4662 l_cursor_is_open := FALSE;
4664 end if;
4665 end if;
4666 elsif (l_grp_fn) then
4667 --Changes for Bug#3867925
4668 -- Fix Non Backward change made for universal person support
4669 /*if (fnd_log.LEVEL_STATEMENT >=
4670 fnd_log.g_current_runtime_level) then
4671 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4672 c_log_head || l_api_name || '.fetch_grp_fn_cursor',
4673 ' fetch');
4674 end if;
4675 FETCH isg_grp_fn_c INTO d_predicate, d_instance_set_id,
4676 d_grant_guid;
4677 if (isg_grp_fn_c%notfound) then
4678 close isg_grp_fn_c;
4679 l_cursor_is_open := FALSE;
4680 exit; -- exit loop
4681 end if;*/
4682
4683 if (colon <> 0) then
4684 if (fnd_log.LEVEL_STATEMENT >=
4685 fnd_log.g_current_runtime_level) then
4686 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4687 c_log_head || l_api_name||'.fetch_grp_fn_bkwd_cursor',
4688 ' fetch');
4689 end if;
4690 FETCH isg_grp_fn_bkwd_c INTO
4691 d_predicate, d_instance_set_id,
4692 d_grant_guid;
4693 if (isg_grp_fn_bkwd_c%notfound) then
4694 close isg_grp_fn_bkwd_c;
4695 l_cursor_is_open := FALSE;
4696 exit; -- exit loop
4697 end if;
4698 else
4699 if (fnd_log.LEVEL_STATEMENT >=
4700 fnd_log.g_current_runtime_level) then
4701 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4702 c_log_head || l_api_name || '.fetch_grp_fn_cursor',
4703 ' fetch');
4704 end if;
4705 FETCH isg_grp_fn_c INTO d_predicate, d_instance_set_id,
4706 d_grant_guid;
4707 if (isg_grp_fn_c%notfound) then
4708 close isg_grp_fn_c;
4709 l_cursor_is_open := FALSE;
4710 exit; -- exit loop
4711 end if;
4712 end if;
4713 elsif (l_grp_nofn) then
4714 --Changes for Bug#3867925
4715 -- Fix Non Backward change made for universal person support
4716 /*if (fnd_log.LEVEL_STATEMENT >=
4717 fnd_log.g_current_runtime_level) then
4718 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4719 c_log_head || l_api_name || '.fetch_grp_nofn_cursor',
4720 ' fetch');
4721 end if;
4722 FETCH isg_grp_nofn_c INTO d_predicate, d_instance_set_id,
4723 d_grant_guid;
4724 if (isg_grp_fn_c%notfound) then
4725 close isg_grp_fn_c;
4726 l_cursor_is_open := FALSE;
4727 exit; -- exit loop
4728 end if; */
4729
4730 if (colon <> 0) then
4731 if (fnd_log.LEVEL_STATEMENT >=
4732 fnd_log.g_current_runtime_level) then
4733 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4734 c_log_head || l_api_name ||'.fetch_grp_nofn_bkwd_cursor',
4735 ' fetch');
4736 end if;
4737 FETCH isg_grp_nofn_bkwd_c
4738 INTO d_predicate, d_instance_set_id,
4739 d_grant_guid;
4740 if (isg_grp_nofn_bkwd_c%notfound) then
4741 close isg_grp_nofn_bkwd_c;
4742 l_cursor_is_open := FALSE;
4743 exit; -- exit loop
4744 end if;
4745 else
4746 if (fnd_log.LEVEL_STATEMENT >=
4747 fnd_log.g_current_runtime_level) then
4748 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4749 c_log_head || l_api_name || '.fetch_grp_nofn_cursor',
4750 ' fetch');
4751 end if;
4752 FETCH isg_grp_nofn_c INTO d_predicate, d_instance_set_id,
4753 d_grant_guid;
4754 if (isg_grp_nofn_c%notfound) then
4755 close isg_grp_nofn_c;
4756 l_cursor_is_open := FALSE;
4757 exit; -- exit loop
4758 end if;
4759 end if;
4760 elsif (l_glob_fn) then
4761 if (fnd_log.LEVEL_STATEMENT >=
4762 fnd_log.g_current_runtime_level) then
4763 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4764 c_log_head || l_api_name || '.fetch_glob_fn_cursor',
4765 ' fetch');
4766 end if;
4767 FETCH isg_glob_fn_c INTO d_predicate, d_instance_set_id,
4771 l_cursor_is_open := FALSE;
4768 d_grant_guid;
4769 if (isg_glob_fn_c%notfound) then
4770 close isg_glob_fn_c;
4772 exit; -- exit loop
4773 end if;
4774 elsif (l_glob_nofn) then
4775 if (fnd_log.LEVEL_STATEMENT >=
4776 fnd_log.g_current_runtime_level) then
4777 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4778 c_log_head || l_api_name || '.fetch_glob_nofn_cursor',
4779 ' fetch');
4780 end if;
4781 FETCH isg_glob_nofn_c INTO d_predicate, d_instance_set_id,
4782 d_grant_guid;
4783 if (isg_glob_nofn_c%notfound) then
4784 close isg_glob_nofn_c;
4785 l_cursor_is_open := FALSE;
4786 exit; -- exit loop
4787 end if;
4788 else
4789 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4790 fnd_message.set_token('ROUTINE',
4791 c_pkg_name || '.'|| l_api_name);
4792 fnd_message.set_token('REASON',
4793 ' Fell through where we shouldnt have (2)');
4794 if (fnd_log.LEVEL_EXCEPTION >=
4795 fnd_log.g_current_runtime_level) then
4796 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4797 c_log_head || l_api_name || '.end_fallthru2',
4798 FALSE);
4799 end if;
4800 x_return_status := 'U';
4801 return;
4802 end if;
4803
4804 if (fnd_log.LEVEL_STATEMENT >=
4805 fnd_log.g_current_runtime_level) then
4806 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4807 c_log_head || l_api_name || '.loop_isg',
4808 ' d_instance_set_id: '||
4809 d_instance_set_id ||
4810 ' d_predicate: '||
4811 d_predicate ||
4812 ' d_grant_guid: '||
4813 d_grant_guid);
4814 end if;
4815
4816 /* If we are coming upon a new instance set */
4817 if (d_instance_set_id <>
4818 l_last_instance_set_id) then
4819 if (l_need_to_close_pred) then /* Close off the last pred */
4820 l_aggregate_predicate := substrb( l_aggregate_predicate ||
4821 ') AND '|| l_pred ||')', 1, c_pred_buf_size);
4822 l_need_to_close_pred := FALSE;
4823 l_last_was_hextoraw := FALSE;
4824 if (fnd_log.LEVEL_STATEMENT >=
4825 fnd_log.g_current_runtime_level) then
4826 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4827 c_log_head || l_api_name || '.close_pred',
4828 'l_pred:'||l_pred);
4829 end if;
4830 end if;
4831
4832 /* If we need to add an OR, do so. */
4833 if (l_last_pred <> '*NO_PRED*') then
4834 l_aggregate_predicate := substrb( l_aggregate_predicate ||
4835 ' OR ', 1, c_pred_buf_size);
4836 end if;
4837
4838 /* Upgrade and substitute predicate */
4839 l_pred := upgrade_predicate(
4840 d_predicate);
4841
4842 if (fnd_log.LEVEL_STATEMENT >=
4843 fnd_log.g_current_runtime_level) then
4844 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4845 c_log_head || l_api_name || '.upgd_pred',
4846 'l_pred:'||l_pred);
4847 end if;
4848
4849 /* If this is the simple form of predicate that does not */
4850 /* refer to parameters in the grant table */
4851 if (instr(l_pred, C_GRANT_ALIAS_TOK) <> 0) then
4852 l_uses_params := TRUE;
4853 else
4854 l_uses_params := FALSE;
4855 end if;
4856
4857 l_pred := substitute_predicate(
4858 l_pred,
4859 p_table_alias);
4860
4861 if (fnd_log.LEVEL_STATEMENT >=
4862 fnd_log.g_current_runtime_level) then
4863 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4864 c_log_head || l_api_name || '.subbed_pred',
4865 'l_pred:'||l_pred);
4866 end if;
4867
4868 /* If this is the simple form of predicate that does not */
4869 /* refer to parameters in the grant table */
4870 if ( NOT l_uses_params) then
4871 l_aggregate_predicate :=
4872 substrb( l_aggregate_predicate ||
4873 '('|| l_pred ||')', 1, c_pred_buf_size);
4874 l_need_to_close_pred := FALSE;
4875 l_refers_to_grants := FALSE;
4876 if (fnd_log.LEVEL_STATEMENT >=
4877 fnd_log.g_current_runtime_level) then
4878 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4879 c_log_head || l_api_name || '.simple_pred',
4883 /* against the grants table */
4880 'l_pred:'||l_pred);
4881 end if;
4882 else /* Has references to grant table so we subselect */
4884 l_aggregate_predicate :=
4885 substrb( l_aggregate_predicate ||
4886 ' exists (select null'||
4887 ' from fnd_grants gnt'||
4888 ' where gnt.grant_guid in (',
4889 1, c_pred_buf_size);
4890 l_need_to_close_pred := TRUE;
4891 l_refers_to_grants := TRUE;
4892 if (fnd_log.LEVEL_STATEMENT >=
4893 fnd_log.g_current_runtime_level) then
4894 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4895 c_log_head || l_api_name || '.compl_pred',
4896 'l_pred:'||l_pred);
4897 end if;
4898 end if;
4899 end if;
4900
4901 l_last_instance_set_id := d_instance_set_id;
4902 l_last_pred := d_predicate;
4903
4904 /* Add this grant_guid to the predicate */
4905 if (l_refers_to_grants) then
4906 if (l_last_was_hextoraw) then /* Add a comma if necessary */
4907 l_aggregate_predicate :=
4908 substrb(l_aggregate_predicate ||
4909 ', ', 1, c_pred_buf_size);
4910 end if;
4911 l_aggregate_predicate :=
4912 substrb( l_aggregate_predicate ||
4913 'hextoraw('''|| d_grant_guid
4914 ||''')', 1, c_pred_buf_size);
4915 l_last_was_hextoraw := TRUE;
4916 else
4917 l_last_was_hextoraw := FALSE;
4918 end if;
4919 if (fnd_log.LEVEL_STATEMENT >=
4920 fnd_log.g_current_runtime_level) then
4921 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4922 c_log_head || l_api_name || '.instsetloop',
4923 ' l_aggregate_predicate: ' || l_aggregate_predicate);
4924 end if;
4925 END LOOP;
4926
4927 /* Close the cursor */
4928 if (l_cursor_is_open) then
4929 if (l_grp_glob_fn) then
4930 close isg_grp_glob_fn_c;
4931 elsif (l_grp_glob_nofn) then
4932 close isg_grp_glob_nofn_c;
4933 close isg_grp_glob_nofn_bkwd_c;
4934 elsif (l_grp_fn) then
4935 close isg_grp_fn_c;
4936 elsif (l_grp_nofn) then
4937 close isg_grp_nofn_c;
4938 close isg_grp_nofn_bkwd_c;
4939 elsif (l_glob_fn) then
4940 close isg_glob_fn_c;
4941 elsif (l_glob_nofn) then
4942 close isg_glob_nofn_c;
4943 else
4944 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
4945 fnd_message.set_token('ROUTINE',
4946 c_pkg_name || '.'|| l_api_name);
4947 fnd_message.set_token('REASON',
4948 ' Fell through where we shouldnt have (3)');
4949 if (fnd_log.LEVEL_EXCEPTION >=
4950 fnd_log.g_current_runtime_level) then
4951 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
4952 c_log_head || l_api_name || '.end_fallthru3',
4953 FALSE);
4954 end if;
4955 x_return_status := 'U';
4956 return;
4957 end if;
4958 end if;
4959
4960 if (l_need_to_close_pred) then /* Close off the last pred */
4961 l_aggregate_predicate := substrb( l_aggregate_predicate ||
4962 ') AND '|| l_pred ||')', 1, c_pred_buf_size);
4963 l_need_to_close_pred := FALSE;
4964 l_last_was_hextoraw := FALSE;
4965 end if;
4966
4967 end if;
4968
4969 /* If there were no predicates found */
4970 if (l_last_pred = '*NO_PRED*') then
4971 l_set_instance_type := FALSE;
4972 end if;
4973
4974 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
4975 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4976 c_log_head || l_api_name || '.afterinstset',
4977 ' l_aggregate_predicate: ' || l_aggregate_predicate);
4978 end if;
4979
4980 /* ---------- Instance part */
4981 if (l_inst_instance_type) then
4982 l_instance_predicate :=
4983 l_instance_predicate ||
4984 ' exists (select null'||
4985 ' from fnd_grants gnt';
4986 if (p_with_binds = 'Y') then
4987 l_instance_predicate :=
4988 l_instance_predicate ||
4989 ' where (GNT.object_id = :OBJECT_ID_BIND'||
4990 ' AND (';
4991 x_object_id := l_object_id;
4992 if(x_bind_order is not NULL) then
4993 x_bind_order := x_bind_order || 'O';
4994 else
4995 x_bind_order := 'O';
4996 end if;
4997 else
4998 l_instance_predicate :=
5002 end if;
4999 l_instance_predicate ||
5000 ' where (GNT.object_id = ' || l_object_id ||
5001 ' AND (';
5003
5004 if (l_inst_group_grantee_type) then
5005
5006 --Changes for Bug#3867925
5007 -- Fix Non Backward change made for universal person support
5008
5009 if (colon <> 0) then
5010
5011 l_instance_predicate := l_instance_predicate ||
5012 ' ( ( GNT.grantee_type = ''USER'' '||
5013 ' AND GNT.grantee_key = '|| l_user_name_bind||') '||
5014 ' OR ( GNT.grantee_type = ''GROUP'' '||
5015 ' AND GNT.grantee_key in ' ||
5016 ' (select role_name '||
5017 ' from wf_user_roles wur '||
5018 ' where wur.user_name in '||
5019 ' ( (select '||l_user_name_bind ||' from dual) '||
5020 ' union all '||
5021 ' (select incrns.name from wf_local_roles incrns, '||
5022 ' fnd_user f '||
5023 ' where ''HZ_PARTY'' = incrns.orig_system '||
5024 ' and f.user_name = '||l_user_name_bind ||
5025 ' and f.person_party_id = incrns.orig_system_id)))))';
5026
5027 if (l_inst_global_grantee_type) then
5028 l_instance_predicate := l_instance_predicate ||
5029 ' OR' ||
5030 ' (GNT.grantee_type = ''GLOBAL'')';
5031 end if;
5032
5033 else /* colon <> 0 */
5034
5035 l_instance_predicate := l_instance_predicate ||
5036 ' (GNT.grantee_key in '||
5037 ' (select role_name '||
5038 ' from wf_user_roles wur, '||
5039 ' ( select '||l_user_name_bind ||' name from dual '||
5040 ' union all '||
5041 ' (select incrns.name from wf_local_roles incrns, '||
5042 ' fnd_user f '||
5043 ' where ''HZ_PARTY'' = incrns.orig_system '||
5044 ' and f.user_name = '||l_user_name_bind ||
5045 ' and f.person_party_id = incrns.orig_system_id '||
5046 ' and incrns.partition_id = 9 ) '||
5047 ' ) incr2 '||
5048 ' where wur.user_name = incr2.name ';
5049
5050 if (l_inst_global_grantee_type) then
5051 l_instance_predicate := l_instance_predicate ||
5052 ' union all '||
5053 ' select ''GLOBAL'' from dual ';
5054 end if;
5055
5056 l_instance_predicate := l_instance_predicate ||
5057 ' ) ) ';
5058
5059 end if; /* colon <> 0 */
5060
5061 else /* (l_inst_group_grantee_type) */
5062
5063 if (l_inst_global_grantee_type) then
5064 l_instance_predicate := l_instance_predicate ||
5065 ' (GNT.grantee_type = ''GLOBAL'')';
5066 end if;
5067
5068 end if;
5069
5070 /* Close off the grantee part */
5071 l_instance_predicate := l_instance_predicate ||
5072 ' )';
5073 if (p_with_binds = 'Y') then /* If returning a stmnt w/ binds*/
5074 if (l_function_id <> -1) then
5075 l_instance_predicate := l_instance_predicate ||
5076 ' AND GNT.menu_id in'||
5077 ' (select cmf.menu_id'||
5078 ' from fnd_compiled_menu_functions cmf'||
5079 ' where cmf.function_id = :FUNCTION_ID_BIND )';
5080 x_function_id := l_function_id;
5081 if(x_bind_order is not NULL) then
5082 x_bind_order := x_bind_order || 'F';
5083 else
5084 x_bind_order := 'F';
5085 end if;
5086 end if;
5087 else
5088 if (l_function_id <> -1) then
5089 l_instance_predicate := l_instance_predicate ||
5090 ' AND GNT.menu_id in'||
5091 ' (select cmf.menu_id'||
5092 ' from fnd_compiled_menu_functions cmf'||
5093 ' where cmf.function_id = '||l_function_id||')';
5094 end if;
5095 end if;
5096 l_instance_predicate := l_instance_predicate ||
5097 ' AND( GNT.ctx_secgrp_id = -1'||
5098 ' OR GNT.ctx_secgrp_id = '||
5099 ' SYS_CONTEXT(''FND'',''SECURITY_GROUP_ID''))'||
5100 ' AND( GNT.ctx_resp_id = -1'||
5101 ' OR GNT.ctx_resp_id = '||
5102 ' SYS_CONTEXT(''FND'',''RESP_ID''))'||
5103 ' AND( GNT.ctx_resp_appl_id = -1'||
5104 ' OR GNT.ctx_resp_appl_id ='||
5105 ' SYS_CONTEXT(''FND'',''RESP_APPL_ID''))'||
5106 ' AND( GNT.ctx_org_id = -1'||
5107 ' OR GNT.ctx_org_id ='||
5108 ' SYS_CONTEXT(''FND'', ''ORG_ID''))'||
5109 ' AND GNT.start_date <= sysdate ' ||
5110 ' AND ( GNT.end_date IS NULL ' ||
5111 ' OR GNT.end_date >= sysdate ) ';
5112
5113 /* Add on the clause for INSTANCE_TYPE = 'INSTANCE' */
5114 l_instance_predicate := l_instance_predicate ||
5115 ' AND'|| l_ik_clause||'))';
5119 end if;
5116 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
5117 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
5118 c_log_head || l_api_name || '.instpred','instpred');
5120 end if;
5121
5122 /* Add the instance predicate on to the end */
5123 if (l_set_instance_type and l_inst_instance_type) then
5124 l_aggregate_predicate := substrb(l_aggregate_predicate ||
5125 ' OR', 1, c_pred_buf_size);
5126 end if;
5127 if(l_inst_instance_type) then
5128 l_aggregate_predicate := substrb(l_aggregate_predicate ||
5129 l_instance_predicate, 1, c_pred_buf_size);
5130 end if;
5131
5132 /* If we have no predicate, then return 1=2. This is for robustness*/
5133 /* but probably isn't needed in practice. */
5134 if( l_aggregate_predicate is NULL
5135 or l_aggregate_predicate = '*NO_PRED*') then
5136 l_aggregate_predicate := '1=2';
5137 end if;
5138
5139 <<return_and_cache>>
5140
5141
5142
5143 /* Put parentheses around the statement in order to make it */
5144 /* amenable to ANDing with another statement */
5145 if(p_statement_type = 'EXISTS')then
5146 /* tmorrow- for bug 4592098 added substr to prevent buf overflows*/
5147 l_aggregate_predicate :=substrb(
5148 'ROWNUM=1 and ('||l_aggregate_predicate||')',
5149 1, c_pred_buf_size);
5150 else
5151 /* tmorrow- for bug 4592098 added substr to prevent buf overflows*/
5152 l_aggregate_predicate :=substrb('('||l_aggregate_predicate||')',
5153 1, c_pred_buf_size);
5154 end if;
5155
5156 if (g_vpd_buf_limit = -1) then /* If not initialized */
5157 g_vpd_buf_limit := self_init_pred_size(); /* init from db version */
5158 end if;
5159
5160 if ( (lengthb(l_aggregate_predicate) > g_vpd_buf_limit)
5161 AND ( (p_statement_type = 'BASE') /* deprecated, same as VPD*/
5162 or (p_statement_type = 'VPD')))then
5163 FND_MESSAGE.SET_NAME('FND', 'GENERIC-INTERNAL ERROR');
5164 FND_MESSAGE.SET_TOKEN('ROUTINE',
5165 c_pkg_name || '.'|| l_api_name);
5166 FND_MESSAGE.SET_TOKEN('REASON',
5167 'The predicate was longer than the database VPD limit of '||
5168 to_char(g_vpd_buf_limit)||' bytes for the predicate. ');
5169
5170 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5171 fnd_log.message(FND_LOG.LEVEL_PROCEDURE,
5172 c_log_head || l_api_name || '.end',
5173 FALSE);
5174 end if;
5175 x_return_status := 'L'; /* Indicate Error */
5176 end if;
5177
5178 x_predicate := l_aggregate_predicate;
5179
5180 /* For VPD, null predicate is logically equivalent to and performs */
5181 /* similarly to (1=1) so return that. */
5182 if ( (x_predicate = '(1=1)')
5183 AND ( (p_statement_type = 'BASE') /* deprecated, same as VPD*/
5184 or (p_statement_type = 'VPD'))) then
5185 x_predicate := NULL;
5186 end if;
5187
5188 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5189 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5190 c_log_head || l_api_name || '.end',
5191 'x_predicate: '|| x_predicate ||
5192 ', x_return_status:'||x_return_status);
5193 end if;
5194
5195 /* Set params and results into 1-level cache for next time */
5196 g_gsp_function := p_function;
5197 g_gsp_object_name := p_object_name;
5198 g_gsp_grant_instance_type := p_grant_instance_type;
5199 g_gsp_user_name := l_user_name;
5200 g_gsp_statement_type := p_statement_type;
5201 g_gsp_table_alias := p_table_alias;
5202 g_gsp_with_binds := p_with_binds;
5203 g_gsp_context_user_id := SYS_CONTEXT('FND','USER_ID');
5204 g_gsp_context_resp_id := SYS_CONTEXT('FND','RESP_ID');
5205 g_gsp_context_secgrpid := SYS_CONTEXT('FND','SECURITY_GROUP_ID');
5206 g_gsp_context_resp_appl_id := SYS_CONTEXT('FND','RESP_APPL_ID');
5207 g_gsp_context_org_id := SYS_CONTEXT('FND', 'ORG_ID');
5208 g_gsp_predicate := x_predicate;
5209 g_gsp_return_status := x_return_status;
5210 g_gsp_object_id := x_object_id;
5211 g_gsp_function_id := x_function_id;
5212 g_gsp_bind_order := x_bind_order;
5213
5214 EXCEPTION
5215 /* If API called with deprecated p_user_name arg, */
5216 /* propagate that up so the caller gets exception */
5217 WHEN FND_MESSAGE_RAISED_ERR THEN
5218 /* Re raise the error for the caller */
5219 fnd_message.raise_error;
5220
5221 x_return_status := 'U'; /* This line should never be executed */
5222 return;
5223
5224 WHEN OTHERS THEN
5225 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
5226 fnd_message.set_token('ROUTINE',
5227 c_pkg_name || '.'|| l_api_name);
5228 fnd_message.set_token('ERRNO', SQLCODE);
5232 fnd_log.g_current_runtime_level) then
5229 fnd_message.set_token('REASON', SQLERRM);
5230
5231 if (fnd_log.LEVEL_EXCEPTION >=
5233 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5234 c_log_head || l_api_name || '.other_err',
5235 FALSE);
5236 end if;
5237 x_return_status := 'U';
5238 if (fnd_log.LEVEL_PROCEDURE >=
5239 fnd_log.g_current_runtime_level) then
5240 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5241 c_log_head || l_api_name || '.end_after_other',
5242 'x_predicate: '|| x_predicate ||
5243 ', x_return_status:'||x_return_status);
5244 end if;
5245 return;
5246 END get_security_predicate_intrnl;
5247 ------------------------------------------------------------------------------------
5248
5249 PROCEDURE get_security_predicate_w_binds
5250 (
5251 p_api_version IN NUMBER,
5252 p_function IN VARCHAR2,
5253 p_object_name IN VARCHAR2,
5254 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
5255 p_user_name IN VARCHAR2,
5256 /* statement_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= to check existence*/
5257 p_statement_type IN VARCHAR2,
5258 p_table_alias IN VARCHAR2,
5259 x_predicate out NOCOPY varchar2,
5260 x_return_status out NOCOPY varchar2,
5261 x_function_id out NOCOPY NUMBER,
5262 x_object_id out NOCOPY NUMBER,
5263 x_bind_order out NOCOPY varchar2
5264 )
5265 IS
5266 l_api_name CONSTANT VARCHAR2(30) := 'GET_SECURITY_PREDICATE_W_BINDS';
5267 l_api_version CONSTANT NUMBER := 1.0;
5268 BEGIN
5269
5270 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5271 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5272 c_log_head || l_api_name || '.begin',
5273 c_pkg_name || '.' ||l_api_name|| '(' ||
5274 'p_api_version=>'|| to_char(p_api_version) ||
5275 ', p_function=>'|| p_function ||
5276 ', p_object_name=>'|| p_object_name ||
5277 ', p_grant_instance_type=>'|| p_grant_instance_type ||
5278 ', p_user_name=>'|| p_user_name ||
5279 ', p_statement_type=>'|| p_statement_type ||
5280 ', p_table_alias=>'|| p_table_alias ||');');
5281 end if;
5282
5283
5284 -- check for call compatibility.
5285 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
5286 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
5287 fnd_message.set_token('ROUTINE',
5288 c_pkg_name || '.'|| l_api_name);
5289 fnd_message.set_token('REASON',
5290 'Unsupported version '|| to_char(p_api_version)||
5291 ' passed to API; expecting version '||
5292 to_char(l_api_version));
5293 if (fnd_log.LEVEL_EXCEPTION >=
5294 fnd_log.g_current_runtime_level) then
5295 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5296 c_log_head || l_api_name || '.end_bad_api_ver',
5297 FALSE);
5298 end if;
5299 x_return_status := 'U'; /* Unexpected Error */
5300 return;
5301 END IF;
5302
5303 fnd_data_security.get_security_predicate_intrnl(
5304 p_api_version, p_function, p_object_name, p_grant_instance_type,
5305 p_user_name, p_statement_type, p_table_alias,'Y',
5306 x_predicate, x_return_status,
5307 x_function_id, x_object_id, x_bind_order);
5308
5309 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5310 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5311 c_log_head || l_api_name || '.end',
5312 ' x_predicate: '|| x_predicate ||
5313 ' x_return_status:'||x_return_status||
5314 ' x_function_id: '|| x_function_id ||
5315 ' x_object_id: '|| x_object_id ||
5316 ' x_bind_order:'|| x_bind_order );
5317 end if;
5318 END;
5319
5320
5321 ----- THIS IS THE RIGHT VERSION OF GET_SECURITY_PREDICATE TO USE.
5322 ----- Get the list of predicates Strings
5323 ----- Undocumented unsupported feature for internal use only:
5324 ----- passing 'FUNCLIST' for p_grant_instance_type will yield pred
5325 ----- for use in get_functions.
5326 --------------------------------------------
5327 PROCEDURE get_security_predicate(
5328 p_api_version IN NUMBER,
5329 p_function IN VARCHAR2,
5330 p_object_name IN VARCHAR2,
5331 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
5332 /* Undocumented value: FUNCLIST, FUNCLIST_NOINST */
5333 /* Documented value: GRANTS_ONLY */
5334 p_user_name IN VARCHAR2,
5335 /* stmnt_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= for checking existence. */
5336 p_statement_type IN VARCHAR2,
5337 x_predicate out NOCOPY varchar2,
5338 x_return_status out NOCOPY varchar2,
5339 p_table_alias IN VARCHAR2
5340 ) IS
5341 l_api_name CONSTANT VARCHAR2(30) := 'GET_SECURITY_PREDICATE';
5342 l_api_version CONSTANT NUMBER := 1.0;
5343 x_function_id NUMBER;
5344 x_object_id NUMBER;
5345 x_bind_order varchar2(256);
5346 BEGIN
5350 c_log_head || l_api_name || '.begin',
5347
5348 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5349 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5351 c_pkg_name || '.' ||l_api_name|| '(' ||
5352 'p_api_version=>'|| to_char(p_api_version) ||
5353 ', p_function=>'|| p_function ||
5354 ', p_object_name=>'|| p_object_name ||
5355 ', p_grant_instance_type=>'|| p_grant_instance_type ||
5356 ', p_user_name=>'|| p_user_name ||
5357 ', p_statement_type=>'|| p_statement_type ||
5358 ', p_table_alias=>'|| p_table_alias ||');');
5359 end if;
5360
5361 -- check for call compatibility.
5362 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
5363 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
5364 fnd_message.set_token('ROUTINE',
5365 c_pkg_name || '.'|| l_api_name);
5366 fnd_message.set_token('REASON',
5367 'Unsupported version '|| to_char(p_api_version)||
5368 ' passed to API; expecting version '||
5369 to_char(l_api_version));
5370 if (fnd_log.LEVEL_EXCEPTION >=
5371 fnd_log.g_current_runtime_level) then
5372 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5373 c_log_head || l_api_name || '.end_bad_api_ver',
5374 FALSE);
5375 end if;
5376 x_return_status := 'U'; /* Unexpected Error */
5377 return;
5378 END IF;
5379
5380 fnd_data_security.get_security_predicate_intrnl(
5381 p_api_version, p_function, p_object_name, p_grant_instance_type,
5382 p_user_name, p_statement_type, p_table_alias, 'N',
5383 x_predicate, x_return_status,
5384 x_function_id, x_object_id, x_bind_order);
5385
5386 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5387 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5388 c_log_head || l_api_name || '.end',
5389 ' x_predicate: '|| x_predicate ||
5390 ' x_return_status:'||x_return_status||
5391 ' x_function_id: '|| x_function_id ||
5392 ' x_object_id: '|| x_object_id ||
5393 ' x_bind_order:'|| x_bind_order);
5394 end if;
5395 END;
5396
5397
5398 -- DEPRECATED. DO NOT CALL THIS. USE THE OTHER OVERLOADED PROCEDURE.
5399 -- This version of get_security_predicate is no longer supported because
5400 -- the pk aliases that it takes in the params do not work in our new
5401 -- SQL which now puts the object name in the SQL for parameterized
5402 -- instance sets. It is being left in the API simply for patching
5403 -- reasons but should NEVER be called from new code. The pk aliases
5404 -- will be ignored. In some upcoming release this may be dropped
5405 -- from the API.
5406 -- New code should call the overloaded get_security_predicate without
5407 -- the pk aliases.
5408 PROCEDURE get_security_predicate
5409 (
5410 p_api_version IN NUMBER,
5411 p_function IN VARCHAR2,
5412 p_object_name IN VARCHAR2,
5413 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
5414 p_user_name IN VARCHAR2,
5415 /* stmnt_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= for checking existence. */
5416 p_statement_type IN VARCHAR2,
5417 p_pk1_alias IN VARCHAR2,
5418 p_pk2_alias IN VARCHAR2,
5419 p_pk3_alias IN VARCHAR2,
5420 p_pk4_alias IN VARCHAR2,
5421 p_pk5_alias IN VARCHAR2,
5422 x_predicate out NOCOPY varchar2,
5423 x_return_status out NOCOPY varchar2
5424 ) IS
5425 l_api_name CONSTANT VARCHAR2(30) := 'GET_SECURITY_PREDICATE';
5426 begin
5427
5428
5429 -- Check to make sure we're not using unsupported modes
5430 if ( ( (p_statement_type = 'BASE')/* deprecated, same as VPD */
5431 or (p_statement_type = 'VPD')
5432 /* or (p_grant_instance_type = 'SET')*/
5433 OR(p_pk1_alias is not NULL)
5434 OR (p_pk2_alias is not NULL)
5435 OR (p_pk3_alias is not NULL)
5436 OR (p_pk4_alias is not NULL)
5437 OR (p_pk5_alias is not NULL))) then
5438
5439 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
5440 fnd_message.set_token('ROUTINE',
5441 c_pkg_name || '.'|| l_api_name);
5442 fnd_message.set_token('REASON',
5443 'Unsupported mode arguments: '||
5444 'p_statement_type = BASE|VPD,'||
5445 ' or p_pkX_alias values passed.');
5446 if (fnd_log.LEVEL_EXCEPTION >=
5447 fnd_log.g_current_runtime_level) then
5448 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5449 c_log_head || l_api_name || '.end_bad_mode',
5450 FALSE);
5451 end if;
5452 x_return_status := 'U'; /* Unexpected Error */
5453 return;
5454 end if;
5455
5456 if (fnd_data_security.DISALLOW_DEPRECATED = 'Y') then
5457 /* In R12 this routine is deprecated */
5458 /* So we raise a runtime exception to help people to know */
5459 /* they need to change their code. */
5460 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
5464 'Invalid API call. API '
5461 fnd_message.set_token('ROUTINE',
5462 c_pkg_name || '.'|| l_api_name);
5463 fnd_message.set_token('REASON',
5465 ||c_pkg_name || '.'|| l_api_name ||
5466 ' is desupported and should not be called in R12.'||
5467 ' Any product team that calls it '||
5468 'must correct their code because it does not work '||
5469 'correctly. Please see the deprecated API document at '||
5470 'http://files.oraclecorp.com/content/AllPublic/'||
5471 'SharedFolders/ATG%20Requirements-Public/R12/'||
5472 'Requirements%20Definition%20Document/'||
5473 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
5474 'Oracle employees who encounter this error should log '||
5475 'a bug against the product that owns the call to this '||
5476 'routine, which is likely the owner of the object that '||
5477 'was passed to this routine: '||
5478 p_object_name);
5479 if (fnd_log.LEVEL_EXCEPTION >=
5480 fnd_log.g_current_runtime_level) then
5481 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5482 c_log_head || l_api_name || '.end_unsupported',
5483 FALSE);
5484 end if;
5485 fnd_message.raise_error;
5486 end if;
5487
5488
5489 fnd_data_security.get_security_predicate(
5490 p_api_version, p_function, p_object_name, p_grant_instance_type,
5491 p_user_name, p_statement_type, x_predicate, x_return_status);
5492 end;
5493
5494 /* THIS ROUTINE IS DEPRECATED AND MAY BE DESUPPORTED. DO NOT CALL IT. */
5495 PROCEDURE get_instances
5496 (
5497 p_api_version IN NUMBER,
5498 p_function IN VARCHAR2,
5499 p_object_name IN VARCHAR2,
5500 p_user_name IN VARCHAR2,
5501 x_return_status OUT NOCOPY VARCHAR2,
5502 x_object_key_tbl OUT NOCOPY FND_INSTANCE_TABLE_TYPE
5503 ) is
5504 l_api_name CONSTANT VARCHAR2(30) := 'GET_INSTANCES';
5505 l_predicate VARCHAR2(32767);
5506 l_dynamic_sql VARCHAR2(32767);
5507 l_db_object_name varchar2(30);
5508 l_db_pk1_column varchar2(256);
5509 l_db_pk2_column varchar2(256);
5510 l_db_pk3_column varchar2(256);
5511 l_db_pk4_column varchar2(256);
5512 l_db_pk5_column varchar2(256);
5513 l_pk_column_names varchar2(512);
5514 l_pk_orig_column_names varchar2(512);
5515 l_ik_clause varchar2(2048);
5516 l_exact_clause varchar2(2048);
5517 l_pk1_val varchar2(512);
5518 l_pk2_val varchar2(512);
5519 l_pk3_val varchar2(512);
5520 l_pk4_val varchar2(512);
5521 l_pk5_val varchar2(512);
5522 l_index number;
5523
5524 TYPE DYNAMIC_CUR IS REF CURSOR;
5525 instances_cur DYNAMIC_CUR;
5526 begin
5527 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5528 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5529 c_log_head || l_api_name || '.begin',
5530 c_pkg_name || '.' ||l_api_name|| '(' ||
5531 'p_api_version=>'|| to_char(p_api_version) ||
5532 ', p_function=>'|| p_function ||
5533 ', p_object_name=>'|| p_object_name ||
5534 ', p_user_name=>'|| p_user_name ||
5535 ')');
5536 end if;
5537
5538 if (fnd_data_security.DISALLOW_DEPRECATED = 'Y') then
5539 /* In R12 this routine is deprecated, because it effectively */
5540 /* does a blind query, potentially returning zillions of */
5541 /* records, which is unsupportable from a performance */
5542 /* perspective. */
5543 /* So we raise a runtime exception to help people to know */
5544 /* they need to change their code. */
5545 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
5546 fnd_message.set_token('ROUTINE',
5547 c_pkg_name || '.'|| l_api_name);
5548 fnd_message.set_token('REASON',
5549 'Invalid API call. API '
5550 ||c_pkg_name || '.'|| l_api_name ||
5551 ' is desupported and should not be called in R12.'||
5552 ' Any product team that calls it '||
5553 'must correct their code because it does not work '||
5554 'correctly. Please see the deprecated API document at '||
5555 'http://files.oraclecorp.com/content/AllPublic/'||
5556 'SharedFolders/ATG%20Requirements-Public/R12/'||
5557 'Requirements%20Definition%20Document/'||
5558 'Application%20Object%20Library/DeprecatedApiRDD.doc '||
5559 'Oracle employees who encounter this error should log '||
5560 'a bug against the product that owns the call to this '||
5561 'routine, which is likely the owner of the object that '||
5562 'was passed to this routine: '||
5563 p_object_name);
5564 if (fnd_log.LEVEL_EXCEPTION >=
5568 FALSE);
5565 fnd_log.g_current_runtime_level) then
5566 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5567 c_log_head || l_api_name || '.end_unsupported',
5569 end if;
5570 fnd_message.raise_error;
5571 end if;
5572
5573 get_security_predicate(p_api_version=>1.0,
5574 p_function =>p_function,
5575 p_object_name =>p_object_name,
5576 p_grant_instance_type =>C_TYPE_UNIVERSAL,
5577 p_user_name =>p_user_name,
5578 x_predicate=>l_predicate,
5579 x_return_status=>x_return_status);
5580 if((x_return_status <> 'T') AND (x_return_status <> 'F'))then
5581 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5582 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5583 c_log_head || l_api_name || '.end_gsp_err',
5584 'returning status: '|| x_return_status);
5585 end if;
5586 /* There will be a message on the msg dict stack. */
5587 return; /* We will return the x_return_status as an out param */
5588 end if;
5589
5590 -- Get names and list of primary keys for this object.
5591 x_return_status := get_pk_information(p_object_name ,
5592 l_db_pk1_column ,
5593 l_db_pk2_column ,
5594 l_db_pk3_column ,
5595 l_db_pk4_column ,
5596 l_db_pk5_column ,
5597 l_pk_column_names ,
5598 l_ik_clause ,
5599 l_exact_clause,
5600 l_pk_orig_column_names,
5601 l_db_object_name,
5602 'OBJTAB', 'GNT');
5603 if (x_return_status <> 'T') then
5604 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5605 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5606 c_log_head || l_api_name || '.end_pk_info_err',
5607 'returning status: '|| x_return_status);
5608 end if;
5609 /* There will be a message on the msg dict stack. */
5610 return; /* We will return the x_return_status as out param */
5611 end if;
5612
5613
5614 if (l_predicate is not NULL) then
5615 l_dynamic_sql :=
5616 'SELECT '|| l_pk_orig_column_names ||
5617 ' FROM '|| l_db_object_name ||
5618 ' WHERE '||l_predicate||' ';
5619 else
5620 x_return_status := 'F';
5621 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5622 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5623 c_log_head || l_api_name || '.end_nopred',
5624 'returning '|| x_return_status );
5625 end if;
5626 return;
5627 end if;
5628
5629 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
5630 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
5631 c_log_head || l_api_name || '.create_dy_sql',
5632 'dynamic_sql:'||l_dynamic_sql);
5633 end if;
5634
5635 l_index:=0;
5636
5637 -- Run the statement,
5638 OPEN instances_cur FOR l_dynamic_sql;
5639 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
5640 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
5641 c_log_head || l_api_name || '.startloop',
5642 ' startloop');
5643 end if;
5644 LOOP
5645 if( (l_db_pk5_column is NOT NULL)
5646 AND (l_db_pk5_column <> C_NULL_STR)) then
5647 FETCH instances_cur INTO l_pk1_val,
5648 l_pk2_val,
5649 l_pk3_val,
5650 l_pk4_val,
5651 l_pk5_val;
5652 elsif( (l_db_pk4_column is NOT NULL)
5653 AND (l_db_pk4_column <> C_NULL_STR)) then
5654 FETCH instances_cur INTO l_pk1_val,
5655 l_pk2_val,
5656 l_pk3_val,
5657 l_pk4_val;
5658 elsif( (l_db_pk3_column is NOT NULL)
5659 AND (l_db_pk3_column <> C_NULL_STR)) then
5660 FETCH instances_cur INTO l_pk1_val,
5661 l_pk2_val,
5662 l_pk3_val;
5663 elsif( (l_db_pk2_column is NOT NULL)
5664 AND (l_db_pk2_column <> C_NULL_STR)) then
5665 FETCH instances_cur INTO l_pk1_val,
5666 l_pk2_val;
5667 elsif( (l_db_pk1_column is NOT NULL)
5668 AND (l_db_pk1_column <> C_NULL_STR)) then
5669 FETCH instances_cur INTO l_pk1_val;
5670 else
5671 x_return_status := 'U';
5672 return; /* This will never happen since pk1 is reqd*/
5673 end if;
5674
5675
5676 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
5677 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
5678 c_log_head || l_api_name || '.did_fetch',
5679 ' l_pk1_val: ' || l_pk1_val ||
5680 ' l_pk2_val: ' || l_pk2_val ||
5681 ' l_pk3_val: ' || l_pk3_val ||
5685 EXIT WHEN instances_cur%NOTFOUND;
5682 ' l_pk4_val: ' || l_pk4_val ||
5683 ' l_pk5_val: ' || l_pk5_val );
5684 end if;
5686 x_object_key_tbl(l_index).pk1_value := l_pk1_val;
5687 x_object_key_tbl(l_index).pk2_value := l_pk2_val;
5688 x_object_key_tbl(l_index).pk3_value := l_pk3_val;
5689 x_object_key_tbl(l_index).pk4_value := l_pk4_val;
5690 x_object_key_tbl(l_index).pk5_value := l_pk5_val;
5691 l_index:=l_index+1;
5692 END LOOP;
5693 CLOSE instances_cur;
5694 if(l_index > 0) then
5695 x_return_status := 'T'; /* Success */
5696 else
5697 x_return_status := 'F'; /* No instances */
5698 end if;
5699
5700 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5701 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5702 c_log_head || l_api_name || '.end',
5703 'returning '|| x_return_status );
5704 end if;
5705 return;
5706
5707 EXCEPTION
5708 /* If API called where it is unsupported, */
5709 /* propagate that up so the caller gets exception */
5710 WHEN FND_MESSAGE_RAISED_ERR THEN
5711 /* Re raise the error for the caller */
5712 fnd_message.raise_error;
5713 x_return_status := 'U'; /* This line should never be executed */
5714 return;
5715
5716 WHEN OTHERS THEN
5717 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
5718 fnd_message.set_token('ROUTINE',
5719 c_pkg_name || '.'|| l_api_name);
5720 fnd_message.set_token('ERRNO', SQLCODE);
5721 fnd_message.set_token('REASON', SQLERRM);
5722
5723 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
5724 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5725 c_log_head || l_api_name || '.other_err',
5726 FALSE);
5727 end if;
5728 x_return_status := 'U';
5729 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5730 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5731 c_log_head || l_api_name || '.end_after_other',
5732 ', x_return_status:'||x_return_status);
5733 end if;
5734 RETURN ;
5735 end get_instances;
5736
5737 /* THIS FUNCTION IS DESUPPORTED. DO NOT CALL THIS FUNCTION. */
5738 /* FUNCTIONALITY HAS BEEN STRIPPED OUT. THIS WON'T DO ANYTHING */
5739 /* This nonfunctional stub is left in the API just to prevent compilation */
5740 /* problems with old code from old patches. */
5741 FUNCTION check_instance_in_set
5742 (
5743 p_api_version IN NUMBER,
5744 p_instance_set_name IN VARCHAR2,
5745 p_instance_pk1_value IN VARCHAR2,
5746 p_instance_pk2_value IN VARCHAR2,
5747 p_instance_pk3_value IN VARCHAR2,
5748 p_instance_pk4_value IN VARCHAR2,
5749 p_instance_pk5_value IN VARCHAR2
5750 ) return VARCHAR2 is
5751 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_INSTANCE_IN_SET';
5752
5753 begin
5754 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
5755 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
5756 c_log_head || l_api_name || '.begin',
5757 c_pkg_name || '.' ||l_api_name|| '(' ||
5758 'p_api_version=>'|| to_char(p_api_version) ||
5759 ', p_instance_set_name=>'|| p_instance_set_name ||
5760 ', p_instance_pk1_value=>'|| p_instance_pk1_value ||
5761 ', p_instance_pk2_value=>'|| p_instance_pk2_value ||
5762 ', p_instance_pk3_value=>'|| p_instance_pk3_value ||
5763 ', p_instance_pk4_value=>'|| p_instance_pk4_value ||
5764 ', p_instance_pk5_value=>'|| p_instance_pk5_value ||')');
5765 end if;
5766
5767 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
5768 fnd_message.set_token('ROUTINE', c_pkg_name || '.'|| l_api_name);
5769 fnd_message.set_token('REASON',
5770 'Desupported API called. This routine is no longer '||
5771 'supported because it is incompatible with '||
5772 'parameterized instance sets which were introduced '||
5773 'in 4/2002. ');
5774 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
5775 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
5776 c_log_head || l_api_name || '.end_desupported',
5777 FALSE);
5778 end if;
5779
5780 fnd_message.raise_error;
5781 return 'u';/* unexpected error */
5782
5783 end check_instance_in_set;
5784
5785 /*
5786 ** replace_str- internal user only. not in api.
5787 ** replace a character string if it doesn't occur in the middle of an
5788 ** alphanumeric string.
5789 */
5790 function replace_str(in_pred in varchar2, /* must be uppercased */
5791 from_str in varchar2,
5792 to_str in varchar2) return varchar2 is
5793 punctuation varchar2(255);
5794 justspaces varchar2(255);
5795 compare_pred varchar2(4000);
5796 out_pred varchar2(4000);
5797 pred_frag varchar2(4000);
5798 out_offset number; /* difference in sizes as we are replacing smaller */
5799 /* strings with bigger strings */
5800 xoldlen number;
5801 xnewlen number;
5802 i number;
5803 j number;
5804 maxlen number := 4000; /* maximum length of predicate */
5808 /* convert punctuation in predicate to spaces for comparison */
5805 before_char varchar2(30);
5806 match boolean;
5807 begin
5809 punctuation := fnd_global.newline||'`~!@#$%^*()-=+|][{}\";:,<>/?''.';
5810 justspaces := ' ';
5811 compare_pred := upper(in_pred);
5812 out_pred := in_pred;
5813 xoldlen := LENGTH(from_str);
5814 xnewlen := LENGTH(to_str);
5815
5816 i:= 99999;
5817 j:= 1;
5818 out_offset := 0;
5819 while (i<>0) loop
5820 i := instr(compare_pred, from_str, 1, j);
5821 if i=0 then
5822 exit;
5823 end if;
5824
5825 if i<>1 then
5826 /* Make sure the character before the X isnt alphanumeric */
5827 /* or underscore, which would mean this is not a match */
5828 before_char := substr(compare_pred, i-1, 1);
5829 before_char := translate(before_char, punctuation, justspaces);
5830 if before_char = ' ' then
5831 match := TRUE;
5832 else
5833 match := FALSE;
5834 end if;
5835 else
5836 match := TRUE;
5837 end if;
5838
5839 if (match) then
5840 /* Replace the string in the output
5841 ** predicate. Clip the string to the max byte size allowed.
5842 */
5843 out_pred := substrb( substr(out_pred, 1, i + out_offset - 1)
5844 || to_str
5845 || substr(out_pred, i + out_offset + xoldlen),
5846 1, maxlen);
5847 out_offset := out_offset + xnewlen - xoldlen ;
5848 end if;
5849
5850 j := j + 1;
5851 end loop;
5852
5853 return out_pred;
5854 end;
5855
5856 /*
5857 ** upgrade_predicate-
5858 ** an internal-only routine that upgrades the predicate
5859 ** from the 11.5.8- style predicate "X.column_name = G.parameter1"
5860 ** format to the new "[Amp]TABLE_ALIAS.column_name =
5861 ** [Amp]GRANT_ALIAS.parameter1"
5862 ** format where [Amp] represents an ampersand.
5863 **
5864 */
5865 FUNCTION upgrade_predicate(in_pred in varchar2) return VARCHAR2 is
5866 xpos number;
5867 gpos number;
5868 compare_pred varchar2(4000);
5869 out_pred varchar2(4000);
5870 xoldval varchar2(255) := 'X.';
5871 goldval varchar2(255) := 'G.PARAMETER';
5872 xnewval varchar2(255) := C_TABLE_ALIAS_TOK;
5873 gnewval varchar2(255) := C_GRANT_ALIAS_TOK||'PARAMETER';
5874 begin
5875 /* upper case the predicate for comparison */
5876 compare_pred := UPPER(in_pred);
5877 xpos := INSTR(compare_pred, xoldval);
5878 gpos := INSTR(compare_pred, goldval);
5879 if (xpos = 0 and gpos = 0) then
5880 return in_pred; /* Short circuit return if no upgrade candidates */
5881 end if;
5882
5883 out_pred := in_pred;
5884 if(xpos <> 0) then
5885 out_pred := replace_str(out_pred, xoldval, xnewval);
5886 end if;
5887 if(gpos <> 0) then
5888 out_pred := replace_str(out_pred, goldval, gnewval);
5889 end if;
5890 return out_pred;
5891 end upgrade_predicate;
5892
5893
5894
5895 /*
5896 ** upgrade_column_type-
5897 ** an internal-only routine that upgrades the FND_OBJECT column types
5898 ** from the obsolete NUMBER type to INTEGER type (leaving other types
5899 ** alone)
5900 **
5901 */
5902 FUNCTION upgrade_column_type(in_col_type in varchar2) return VARCHAR2 is
5903 begin
5904 if (in_col_type = 'NUMBER') then
5905 return 'INTEGER';
5906 else
5907 return in_col_type;
5908 end if;
5909 end upgrade_column_type;
5910
5911
5912 /*
5913 ** upgrade_grantee_key-
5914 ** an internal-only routine that upgrades the GRANTEE_KEY to 'GLOBAL'
5915 ** in any case where the GRANTEE_TYPE is 'GLOBAL'. This will go in 11.5.10.
5916 **
5917 */
5918 FUNCTION upgrade_grantee_key(in_grantee_type in varchar2,
5919 in_grantee_key in varchar2) return VARCHAR2 is
5920 begin
5921 if (in_grantee_type = 'GLOBAL') then
5922 return 'GLOBAL';
5923 else
5924 return in_grantee_key;
5925 end if;
5926 end upgrade_grantee_key;
5927
5928
5929 /*
5930 ** substitute_pred-
5931 **
5932 ** an internal-only routine that substitutes in the object table alias
5933 ** and the grant table alias in the fnd_grants table.
5934 **
5935 **
5936 */
5937 FUNCTION substitute_predicate(in_pred in varchar2,
5938 in_table_alias in varchar2) return VARCHAR2 is
5939 out_pred varchar2(4000);
5940 maxlen number := 4000;
5941 gsubval varchar2(255) := 'GNT.';
5942 l_table_alias varchar2(255);
5943 begin
5944 if (in_table_alias is not NULL) then
5945 l_table_alias := in_table_alias || '.';
5946 else
5947 l_table_alias := NULL;
5948 end if;
5949
5950 out_pred := in_pred;
5951 out_pred := substrb(replace(out_pred, C_TABLE_ALIAS_TOK,
5952 l_table_alias), 1, maxlen);
5953
5954 out_pred := substrb(replace(out_pred, C_GRANT_ALIAS_TOK,
5955 gsubval), 1, maxlen);
5956
5957 return out_pred;
5958 end substitute_predicate;
5959
5960 /*
5961 ** to_int-
5962 ** Convert an integer (no decimal) canonical format VARCHAR2 into NUMBER.
5966 ** this is being called on non-numerical data, just returns -11111.
5963 ** This should be used with id type numbers that don't have decimals
5964 ** because it performs better than to_decimal().
5965 ** If due to the SQL statement being evaluated in an unanticipated order,
5967 ** The reason that it is essential that this is called instead of to_number()
5968 ** on grant parameters is that this routine will not cause an exception if
5969 ** the generated predicate ends up being evaluated such that the grant
5970 ** rows are not filtered before going through the fnd_data_security.to_int()
5971 ** routine. Some grant rows may have non-numeric data if they are for other
5972 ** object instance sets. We need to make sure that the data security
5973 ** clause will not generate an exception no matter what order the database
5974 ** decides to evaluate the statement in.
5975 */
5976 FUNCTION to_int(inval in varchar2) return NUMBER is
5977 outval NUMBER;
5978 begin
5979 if (inval is NULL) then
5980 return NULL;
5981 end if;
5982
5983 begin
5984 outval := to_number(inval);
5985 exception
5986 when value_error then
5987 outval := -11111;
5988 end;
5989 return outval;
5990 end;
5991
5992 /*
5993 ** to_decimal-
5994 ** Convert a canonical format VARCHAR2 with a decimal into a NUMBER.
5995 ** This must be used rather than to_int() whenever the data has a decimal
5996 ** character.
5997 ** If due to the SQL statement being evaluated in an unanticipated order,
5998 ** this is being called on non-numerical data, just return -11111.
5999 */
6000 FUNCTION to_decimal(inval in varchar2) return NUMBER is
6001 outval NUMBER;
6002 begin
6003 if (inval is NULL) then
6004 return NULL;
6005 end if;
6006
6007 begin
6008 outval := fnd_number.canonical_to_number(inval);
6009 exception
6010 when value_error then
6011 outval := -11111;
6012 end;
6013 return outval;
6014 end;
6015
6016 /*
6017 ** to_date-
6018 ** Convert a canonical format date VARCHAR2 into a DATE.
6019 ** If due to the SQL statement being evaluated in an unanticipated order,
6020 ** this is being called on non-date data, returns 1970/11/11.
6021 */
6022 FUNCTION to_date(inval in varchar2/* format 'YYYY/MM/DD' */) return DATE is
6023 outval DATE;
6024 /* GSSC note: the above line may errantly cause File.Date.5 but this file */
6025 /* AFSCDSCB.pls is grandfathered in so it will still build */
6026 begin
6027 if (inval is NULL) then
6028 return NULL;
6029 end if;
6030
6031 begin
6032 outval := fnd_date.canonical_to_date(inval);
6033 exception
6034 when others then
6035 outval := g_bad_date;
6036 end;
6037 return outval;
6038 end;
6039
6040 END FND_DATA_SECURITY;
[Home] [Help]