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