[Home] [Help]
PACKAGE BODY: APPS.FND_GRANTS_PKG
Source
1 package body FND_GRANTS_PKG as
2 /* $Header: AFSCGNTB.pls 120.6 2006/04/29 02:28:43 stadepal ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'FND_GRANTS_PKG';
5 G_LOG_HEAD CONSTANT VARCHAR2(30):= 'fnd.plsql.FND_GRANTS_PKG.';
6
7 procedure INSERT_ROW (
8 X_ROWID in out nocopy VARCHAR2,
9 X_GRANT_GUID in RAW,
10 X_GRANTEE_TYPE in VARCHAR2,
11 X_GRANTEE_KEY in VARCHAR2,
12 X_MENU_ID in NUMBER,
13 X_START_DATE in DATE,
14 X_END_DATE in DATE,
15 X_OBJECT_ID in NUMBER,
16 X_INSTANCE_TYPE in VARCHAR2,
17 X_INSTANCE_SET_ID in NUMBER,
18 X_INSTANCE_PK1_VALUE in VARCHAR2,
19 X_INSTANCE_PK2_VALUE in VARCHAR2,
20 X_INSTANCE_PK3_VALUE in VARCHAR2,
21 X_INSTANCE_PK4_VALUE in VARCHAR2,
22 X_INSTANCE_PK5_VALUE in VARCHAR2,
23 X_PROGRAM_NAME in VARCHAR2,
24 X_PROGRAM_TAG in VARCHAR2,
25 X_CREATION_DATE in DATE,
26 X_CREATED_BY in NUMBER,
27 X_LAST_UPDATE_DATE in DATE,
28 X_LAST_UPDATED_BY in NUMBER,
29 X_LAST_UPDATE_LOGIN in NUMBER,
30 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
31 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
32 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
33 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
34 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
35 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
36 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
37 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
38 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
39 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
40 X_CTX_SECGRP_ID in NUMBER default -1,
41 X_CTX_RESP_ID in NUMBER default -1,
42 X_CTX_RESP_APPL_ID in NUMBER default -1,
43 X_CTX_ORG_ID in NUMBER default -1,
44 X_NAME in VARCHAR2 default null,
45 X_DESCRIPTION in varchar2 default null
46 ) is
47 cursor C is select ROWID from FND_GRANTS
48 where GRANT_GUID = HEXTORAW(X_GRANT_GUID)
49 ;
50 -- Bug 5059644. Added an explicit HEXTORAW to improve the performance.
51 -- HEXTORAW is added for all grant_guid bindvariables used in FND_GRANTS
52 -- SQL statement.
53
54 l_orig_system varchar2(48) := NULL;
55 l_orig_system_id number := NULL;
56 l_instance_pk1_value varchar2(256);
57 l_instance_pk2_value varchar2(256);
58 l_instance_pk3_value varchar2(256);
59 l_instance_pk4_value varchar2(256);
60 l_instance_pk5_value varchar2(256);
61 l_grantee_key varchar2(240);
62 resp_id number := NULL;
63 app_id number := NULL;
64
65 begin
66
67 /* Figure out how to populate the orig_... columns from the grantee_key*/
68 wf_directory.GetRoleOrigSysInfo(
69 Role => x_grantee_key,
70 Orig_System => l_orig_system,
71 Orig_System_Id => l_orig_system_id);
72
73
74 /* only allowed grantee_key is 'GLOBAL' for grantee_type 'GLOBAL'*/
75 if(x_grantee_type = 'GLOBAL') then
76 l_grantee_key := 'GLOBAL';
77 else
78 l_grantee_key := x_grantee_key;
79 end if;
80
81 if(x_instance_pk1_value is NULL) then
82 l_instance_pk1_value := '*NULL*';
83 else
84 l_instance_pk1_value := x_instance_pk1_value;
85 end if;
86
87 if(x_instance_pk2_value is NULL) then
88 l_instance_pk2_value := '*NULL*';
89 else
90 l_instance_pk2_value := x_instance_pk2_value;
91 end if;
92
93 if(x_instance_pk3_value is NULL) then
94 l_instance_pk3_value := '*NULL*';
95 else
96 l_instance_pk3_value := x_instance_pk3_value;
97 end if;
98
99 if(x_instance_pk4_value is NULL) then
100 l_instance_pk4_value := '*NULL*';
101 else
102 l_instance_pk4_value := x_instance_pk4_value;
103 end if;
104
105 if(x_instance_pk5_value is NULL) then
106 l_instance_pk5_value := '*NULL*';
107 else
108 l_instance_pk5_value := x_instance_pk5_value;
109 end if;
110
111 insert into FND_GRANTS (
112 GRANT_GUID,
113 GRANTEE_TYPE,
114 GRANTEE_KEY,
115 MENU_ID,
116 START_DATE,
117 END_DATE,
118 OBJECT_ID,
119 INSTANCE_TYPE,
120 INSTANCE_SET_ID,
121 INSTANCE_PK1_VALUE,
122 INSTANCE_PK2_VALUE,
123 INSTANCE_PK3_VALUE,
124 INSTANCE_PK4_VALUE,
125 INSTANCE_PK5_VALUE,
126 PROGRAM_NAME,
127 PROGRAM_TAG,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN,
133 PARAMETER1,
134 PARAMETER2,
135 PARAMETER3,
136 PARAMETER4,
137 PARAMETER5,
138 PARAMETER6,
139 PARAMETER7,
140 PARAMETER8,
141 PARAMETER9,
142 PARAMETER10,
143 CTX_SECGRP_ID,
144 CTX_RESP_ID,
145 CTX_RESP_APPL_ID,
146 CTX_ORG_ID,
147 GRANTEE_ORIG_SYSTEM,
148 GRANTEE_ORIG_SYSTEM_ID,
149 NAME,
150 DESCRIPTION
151 ) values (
152 X_GRANT_GUID,
153 X_GRANTEE_TYPE,
154 L_GRANTEE_KEY,
155 X_MENU_ID,
156 X_START_DATE,
157 X_END_DATE,
158 X_OBJECT_ID,
159 X_INSTANCE_TYPE,
160 X_INSTANCE_SET_ID,
161 L_INSTANCE_PK1_VALUE,
162 L_INSTANCE_PK2_VALUE,
163 L_INSTANCE_PK3_VALUE,
164 L_INSTANCE_PK4_VALUE,
165 L_INSTANCE_PK5_VALUE,
166 X_PROGRAM_NAME,
167 X_PROGRAM_TAG,
168 X_CREATION_DATE,
169 X_CREATED_BY,
170 X_LAST_UPDATE_DATE,
171 X_LAST_UPDATED_BY,
172 X_LAST_UPDATE_LOGIN,
173 X_PARAMETER1,
174 X_PARAMETER2,
175 X_PARAMETER3,
176 X_PARAMETER4,
177 X_PARAMETER5,
178 X_PARAMETER6,
179 X_PARAMETER7,
180 X_PARAMETER8,
181 X_PARAMETER9,
182 X_PARAMETER10,
183 X_CTX_SECGRP_ID,
184 X_CTX_RESP_ID,
185 X_CTX_RESP_APPL_ID,
186 X_CTX_ORG_ID,
187 l_orig_system,
188 l_orig_system_id,
189 X_NAME,
190 X_DESCRIPTION
191 );
192
193 -- Added for Function Security Cache Invalidation Project
194 -- bug 3554601 - Only raise the event if it is Function Security not for
195 -- Data Security events.
196 if ( X_OBJECT_ID = -1 ) then
197 fnd_function_security_cache.insert_grant(X_GRANT_GUID, X_GRANTEE_TYPE, L_GRANTEE_KEY);
198 end if;
199
200 open c;
201 fetch c into X_ROWID;
202 if (c%notfound) then
203 close c;
204 raise no_data_found;
205 end if;
206 close c;
207
208 end INSERT_ROW;
209
210 procedure LOCK_ROW (
211 X_GRANT_GUID in RAW,
212 X_GRANTEE_TYPE in VARCHAR2,
213 X_GRANTEE_KEY in VARCHAR2,
214 X_MENU_ID in NUMBER,
215 X_START_DATE in DATE,
216 X_END_DATE in DATE,
217 X_OBJECT_ID in NUMBER,
218 X_INSTANCE_TYPE in VARCHAR2,
219 X_INSTANCE_SET_ID in NUMBER,
220 X_INSTANCE_PK1_VALUE in VARCHAR2,
221 X_INSTANCE_PK2_VALUE in VARCHAR2,
222 X_INSTANCE_PK3_VALUE in VARCHAR2,
223 X_INSTANCE_PK4_VALUE in VARCHAR2,
224 X_INSTANCE_PK5_VALUE in VARCHAR2,
225 X_PROGRAM_NAME in VARCHAR2,
226 X_PROGRAM_TAG in VARCHAR2,
227 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
228 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
229 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
230 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
231 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
232 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
233 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
234 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
235 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
236 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
237 X_CTX_SECGRP_ID in NUMBER default -1,
238 X_CTX_RESP_ID in NUMBER default -1,
239 X_CTX_RESP_APPL_ID in NUMBER default -1,
240 X_CTX_ORG_ID in NUMBER default -1,
241 X_NAME in VARCHAR2 default null,
242 X_DESCRIPTION in VARCHAR2 default null
243 ) is
244 cursor c is select
245 GRANTEE_TYPE,
246 GRANTEE_KEY,
247 MENU_ID,
248 START_DATE,
249 END_DATE,
250 OBJECT_ID,
251 INSTANCE_TYPE,
252 INSTANCE_SET_ID,
253 INSTANCE_PK1_VALUE,
254 INSTANCE_PK2_VALUE,
255 INSTANCE_PK3_VALUE,
256 INSTANCE_PK4_VALUE,
257 INSTANCE_PK5_VALUE,
258 PARAMETER1,
259 PARAMETER2,
260 PARAMETER3,
261 PARAMETER4,
262 PARAMETER5,
263 PARAMETER6,
264 PARAMETER7,
265 PARAMETER8,
266 PARAMETER9,
267 PARAMETER10,
268 CTX_SECGRP_ID,
269 CTX_RESP_ID,
270 CTX_RESP_APPL_ID,
271 CTX_ORG_ID,
272 PROGRAM_NAME,
273 PROGRAM_TAG,
274 NAME,
275 DESCRIPTION
276 from FND_GRANTS
277 where GRANT_GUID = hextoraw(X_GRANT_GUID)
278 for update of GRANT_GUID nowait;
279 recinfo c%rowtype;
280
281 l_instance_pk1_value varchar2(256);
282 l_instance_pk2_value varchar2(256);
283 l_instance_pk3_value varchar2(256);
284 l_instance_pk4_value varchar2(256);
285 l_instance_pk5_value varchar2(256);
286 l_grantee_key varchar2(240);
287 resp_id number := NULL;
288 app_id number := NULL;
289
290 begin
291 open c;
292 fetch c into recinfo;
293 if (c%notfound) then
294 close c;
295 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
296 app_exception.raise_exception;
297 end if;
298 close c;
299
300 /* only allowed grantee_key is 'GLOBAL' for grantee_type 'GLOBAL'*/
301 if(x_grantee_type = 'GLOBAL') then
302 l_grantee_key := 'GLOBAL';
303 else
304 l_grantee_key := x_grantee_key;
305 end if;
306
307 if(x_instance_pk1_value is NULL) then
308 l_instance_pk1_value := '*NULL*';
309 else
310 l_instance_pk1_value := x_instance_pk1_value;
311 end if;
312
313 if(x_instance_pk2_value is NULL) then
314 l_instance_pk2_value := '*NULL*';
315 else
316 l_instance_pk2_value := x_instance_pk2_value;
317 end if;
318
319 if(x_instance_pk3_value is NULL) then
320 l_instance_pk3_value := '*NULL*';
321 else
322 l_instance_pk3_value := x_instance_pk3_value;
323 end if;
324
325 if(x_instance_pk4_value is NULL) then
326 l_instance_pk4_value := '*NULL*';
327 else
328 l_instance_pk4_value := x_instance_pk4_value;
329 end if;
330
331 if(x_instance_pk5_value is NULL) then
332 l_instance_pk5_value := '*NULL*';
333 else
334 l_instance_pk5_value := x_instance_pk5_value;
335 end if;
336
337
338 if ( (recinfo.GRANTEE_TYPE = X_GRANTEE_TYPE)
339 AND ( (recinfo.GRANTEE_KEY = X_GRANTEE_KEY)
340 OR (recinfo.GRANTEE_KEY = L_GRANTEE_KEY))
341 AND (recinfo.MENU_ID = X_MENU_ID)
342 AND (recinfo.START_DATE = X_START_DATE)
343 AND ((recinfo.END_DATE = X_END_DATE)
344 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
345 AND (recinfo.OBJECT_ID = X_OBJECT_ID)
346 AND (recinfo.INSTANCE_TYPE = X_INSTANCE_TYPE)
347 AND ((recinfo.INSTANCE_SET_ID = X_INSTANCE_SET_ID)
348 OR ((recinfo.INSTANCE_SET_ID is null)
349 AND (X_INSTANCE_SET_ID is null)))
350 AND ((recinfo.INSTANCE_PK1_VALUE = L_INSTANCE_PK1_VALUE)
351 OR ((recinfo.INSTANCE_PK1_VALUE is null)
352 AND (X_INSTANCE_PK1_VALUE is null)))
353 AND ((recinfo.INSTANCE_PK2_VALUE = L_INSTANCE_PK2_VALUE)
354 OR ((recinfo.INSTANCE_PK2_VALUE is null)
355 AND (X_INSTANCE_PK2_VALUE is null)))
356 AND ((recinfo.INSTANCE_PK3_VALUE = L_INSTANCE_PK3_VALUE)
357 OR ((recinfo.INSTANCE_PK3_VALUE is null)
358 AND (X_INSTANCE_PK3_VALUE is null)))
359 AND ((recinfo.INSTANCE_PK4_VALUE = L_INSTANCE_PK4_VALUE)
360 OR ((recinfo.INSTANCE_PK4_VALUE is null)
361 AND (X_INSTANCE_PK4_VALUE is null)))
362 AND ((recinfo.INSTANCE_PK5_VALUE = L_INSTANCE_PK5_VALUE)
363 OR ((recinfo.INSTANCE_PK5_VALUE is null)
364 AND (X_INSTANCE_PK5_VALUE is null)))
365 AND ((recinfo.PROGRAM_NAME = X_PROGRAM_NAME)
366 OR ((recinfo.PROGRAM_NAME is null) AND (X_PROGRAM_NAME is null)))
367 AND ((recinfo.PROGRAM_TAG = X_PROGRAM_TAG)
368 OR ((recinfo.PROGRAM_TAG is null) AND (X_PROGRAM_TAG is null)))
369 AND ((recinfo.PARAMETER1 = X_PARAMETER1)
370 OR ((recinfo.PARAMETER1 is null) AND (X_PARAMETER1 is null)))
371 AND ((recinfo.PARAMETER2 = X_PARAMETER2)
372 OR ((recinfo.PARAMETER2 is null) AND (X_PARAMETER2 is null)))
373 AND ((recinfo.PARAMETER3 = X_PARAMETER3)
374 OR ((recinfo.PARAMETER3 is null) AND (X_PARAMETER3 is null)))
375 AND ((recinfo.PARAMETER4 = X_PARAMETER4)
376 OR ((recinfo.PARAMETER4 is null) AND (X_PARAMETER4 is null)))
377 AND ((recinfo.PARAMETER5 = X_PARAMETER5)
378 OR ((recinfo.PARAMETER5 is null) AND (X_PARAMETER5 is null)))
379 AND ((recinfo.PARAMETER6 = X_PARAMETER6)
380 OR ((recinfo.PARAMETER6 is null) AND (X_PARAMETER6 is null)))
381 AND ((recinfo.PARAMETER7 = X_PARAMETER7)
382 OR ((recinfo.PARAMETER7 is null) AND (X_PARAMETER7 is null)))
383 AND ((recinfo.PARAMETER8 = X_PARAMETER8)
384 OR ((recinfo.PARAMETER8 is null) AND (X_PARAMETER8 is null)))
385 AND ((recinfo.PARAMETER9 = X_PARAMETER9)
386 OR ((recinfo.PARAMETER9 is null) AND (X_PARAMETER9 is null)))
387 AND ((recinfo.PARAMETER10 = X_PARAMETER10)
388 OR ((recinfo.PARAMETER10 is null) AND (X_PARAMETER10 is null)))
389 AND ((recinfo.CTX_SECGRP_ID = X_CTX_SECGRP_ID)
390 OR ((recinfo.CTX_SECGRP_ID is null) AND (X_CTX_SECGRP_ID is null)))
391 AND ((recinfo.CTX_RESP_ID = X_CTX_RESP_ID)
392 OR ((recinfo.CTX_RESP_ID is null) AND (X_CTX_RESP_ID is null)))
393 AND ((recinfo.CTX_RESP_APPL_ID = X_CTX_RESP_APPL_ID)
394 OR ((recinfo.CTX_RESP_APPL_ID is null)
395 AND (X_CTX_RESP_APPL_ID is null)))
396 AND ((recinfo.CTX_ORG_ID = X_CTX_ORG_ID)
397 OR ((recinfo.CTX_ORG_ID is null) AND (X_CTX_ORG_ID is null)))
398 AND ((recinfo.NAME = X_NAME)
399 OR ((recinfo.NAME is null) AND (X_NAME is null)))
400 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
401 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
402 ) then
403 null;
404 else
405 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
406 app_exception.raise_exception;
407 end if;
408
409 return;
410 end LOCK_ROW;
411
412 procedure UPDATE_ROW (
413 X_GRANT_GUID in RAW,
414 X_GRANTEE_TYPE in VARCHAR2,
415 X_GRANTEE_KEY in VARCHAR2,
416 X_MENU_ID in NUMBER,
417 X_START_DATE in DATE,
418 X_END_DATE in DATE,
419 X_OBJECT_ID in NUMBER,
420 X_INSTANCE_TYPE in VARCHAR2,
421 X_INSTANCE_SET_ID in NUMBER,
422 X_INSTANCE_PK1_VALUE in VARCHAR2,
423 X_INSTANCE_PK2_VALUE in VARCHAR2,
424 X_INSTANCE_PK3_VALUE in VARCHAR2,
425 X_INSTANCE_PK4_VALUE in VARCHAR2,
426 X_INSTANCE_PK5_VALUE in VARCHAR2,
427 X_PROGRAM_NAME in VARCHAR2,
428 X_PROGRAM_TAG in VARCHAR2,
429 X_LAST_UPDATE_DATE in DATE,
430 X_LAST_UPDATED_BY in NUMBER,
431 X_LAST_UPDATE_LOGIN in NUMBER,
432 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
433 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
434 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
435 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
436 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
437 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
438 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
439 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
440 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
441 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
442 X_CTX_SECGRP_ID in NUMBER default -1,
443 X_CTX_RESP_ID in NUMBER default -1,
444 X_CTX_RESP_APPL_ID in NUMBER default -1,
445 X_CTX_ORG_ID in NUMBER default -1,
446 X_NAME in VARCHAR2 default '*NOTPASSED*',
447 X_DESCRIPTION in VARCHAR2 default '*NOTPASSED*'
448 ) is
449
450 l_orig_system varchar2(48) := NULL;
451 l_orig_system_id number := NULL;
452 l_instance_pk1_value varchar2(256);
453 l_instance_pk2_value varchar2(256);
454 l_instance_pk3_value varchar2(256);
455 l_instance_pk4_value varchar2(256);
456 l_instance_pk5_value varchar2(256);
457 l_grantee_key varchar2(240);
458 resp_id number := NULL;
459 app_id number := NULL;
460
461 begin
462
463 /* Figure out how to populate the orig_... columns from the grantee_key*/
464 wf_directory.GetRoleOrigSysInfo(
465 Role => x_grantee_key,
466 Orig_System => l_orig_system,
467 Orig_System_Id => l_orig_system_id);
468
469
470 /* only allowed grantee_key is 'GLOBAL' for grantee_type 'GLOBAL'*/
471 if(x_grantee_type = 'GLOBAL') then
472 l_grantee_key := 'GLOBAL';
473 else
474 l_grantee_key := x_grantee_key;
475 end if;
476
477 if(x_instance_pk1_value is NULL) then
478 l_instance_pk1_value := '*NULL*';
479 else
480 l_instance_pk1_value := x_instance_pk1_value;
481 end if;
482
483 if(x_instance_pk2_value is NULL) then
484 l_instance_pk2_value := '*NULL*';
485 else
486 l_instance_pk2_value := x_instance_pk2_value;
487 end if;
488
489 if(x_instance_pk3_value is NULL) then
490 l_instance_pk3_value := '*NULL*';
491 else
492 l_instance_pk3_value := x_instance_pk3_value;
493 end if;
494
495 if(x_instance_pk4_value is NULL) then
496 l_instance_pk4_value := '*NULL*';
497 else
498 l_instance_pk4_value := x_instance_pk4_value;
499 end if;
500
501 if(x_instance_pk5_value is NULL) then
502 l_instance_pk5_value := '*NULL*';
503 else
504 l_instance_pk5_value := x_instance_pk5_value;
505 end if;
506
507 if((x_name = '*NOTPASSED*') or (x_description = '*NOTPASSED*')) then
508
509 if ((x_name is NULL) or (x_description is NULL)) then
510 /* Mixing NULL with *NOTPASSED* not allowed. */
511 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
512 fnd_message.set_token('REASON',
513 'UPDATE_ROW caller mixed NULL with *NOTPASSED* on grant guid:'
514 ||X_GRANT_GUID);
515 app_exception.raise_exception;
516 end if;
517
518 /* First version of update does not include name, description */
519 update FND_GRANTS set
520 GRANTEE_TYPE = X_GRANTEE_TYPE,
521 GRANTEE_KEY = L_GRANTEE_KEY,
522 MENU_ID = X_MENU_ID,
523 START_DATE = X_START_DATE,
524 END_DATE = X_END_DATE,
525 OBJECT_ID = X_OBJECT_ID,
526 INSTANCE_TYPE = X_INSTANCE_TYPE,
527 INSTANCE_SET_ID = X_INSTANCE_SET_ID,
528 INSTANCE_PK1_VALUE = L_INSTANCE_PK1_VALUE,
529 INSTANCE_PK2_VALUE = L_INSTANCE_PK2_VALUE,
530 INSTANCE_PK3_VALUE = L_INSTANCE_PK3_VALUE,
531 INSTANCE_PK4_VALUE = L_INSTANCE_PK4_VALUE,
532 INSTANCE_PK5_VALUE = L_INSTANCE_PK5_VALUE,
533 PARAMETER1 = X_PARAMETER1,
534 PARAMETER2 = X_PARAMETER2,
535 PARAMETER3 = X_PARAMETER3,
536 PARAMETER4 = X_PARAMETER4,
537 PARAMETER5 = X_PARAMETER5,
538 PARAMETER6 = X_PARAMETER6,
539 PARAMETER7 = X_PARAMETER7,
540 PARAMETER8 = X_PARAMETER8,
541 PARAMETER9 = X_PARAMETER9,
542 PARAMETER10 = X_PARAMETER10,
543 CTX_SECGRP_ID = X_CTX_SECGRP_ID,
544 CTX_RESP_ID = X_CTX_RESP_ID,
545 CTX_RESP_APPL_ID = X_CTX_RESP_APPL_ID,
546 CTX_ORG_ID = X_CTX_ORG_ID,
547 PROGRAM_NAME = X_PROGRAM_NAME,
548 PROGRAM_TAG = X_PROGRAM_TAG,
549 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
550 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
551 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
552 GRANTEE_ORIG_SYSTEM = l_orig_system,
553 GRANTEE_ORIG_SYSTEM_ID = l_orig_system_id
554 where GRANT_GUID = hextoraw(X_GRANT_GUID);
555 else
556 /* Second version of update includes name, description */
557 update FND_GRANTS set
558 GRANTEE_TYPE = X_GRANTEE_TYPE,
559 GRANTEE_KEY = L_GRANTEE_KEY,
560 MENU_ID = X_MENU_ID,
561 START_DATE = X_START_DATE,
562 END_DATE = X_END_DATE,
563 OBJECT_ID = X_OBJECT_ID,
564 INSTANCE_TYPE = X_INSTANCE_TYPE,
565 INSTANCE_SET_ID = X_INSTANCE_SET_ID,
566 INSTANCE_PK1_VALUE = L_INSTANCE_PK1_VALUE,
567 INSTANCE_PK2_VALUE = L_INSTANCE_PK2_VALUE,
568 INSTANCE_PK3_VALUE = L_INSTANCE_PK3_VALUE,
569 INSTANCE_PK4_VALUE = L_INSTANCE_PK4_VALUE,
570 INSTANCE_PK5_VALUE = L_INSTANCE_PK5_VALUE,
571 PARAMETER1 = X_PARAMETER1,
572 PARAMETER2 = X_PARAMETER2,
573 PARAMETER3 = X_PARAMETER3,
574 PARAMETER4 = X_PARAMETER4,
575 PARAMETER5 = X_PARAMETER5,
576 PARAMETER6 = X_PARAMETER6,
577 PARAMETER7 = X_PARAMETER7,
578 PARAMETER8 = X_PARAMETER8,
579 PARAMETER9 = X_PARAMETER9,
580 PARAMETER10 = X_PARAMETER10,
581 CTX_SECGRP_ID = X_CTX_SECGRP_ID,
582 CTX_RESP_ID = X_CTX_RESP_ID,
583 CTX_RESP_APPL_ID = X_CTX_RESP_APPL_ID,
584 CTX_ORG_ID = X_CTX_ORG_ID,
585 PROGRAM_NAME = X_PROGRAM_NAME,
586 PROGRAM_TAG = X_PROGRAM_TAG,
587 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
588 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
589 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
590 GRANTEE_ORIG_SYSTEM = l_orig_system,
591 GRANTEE_ORIG_SYSTEM_ID = l_orig_system_id,
592 NAME = x_name,
593 DESCRIPTION = x_description
594 where GRANT_GUID = hextoraw(X_GRANT_GUID);
595 end if;
596
597 if (sql%notfound) then
598 raise no_data_found;
599 else
600 -- Added for Function Security Cache Invalidation Project
601 -- bug 3554601 - Only raise the event if it is Function Security not for
602 -- Data Security events.
603 if ( X_OBJECT_ID = -1 ) then
604 fnd_function_security_cache.update_grant(X_GRANT_GUID, X_GRANTEE_TYPE, L_GRANTEE_KEY);
605 end if;
606 end if;
607
608 end UPDATE_ROW;
609
610 /* Overloaded version below. Use that; this is the obsolete form. */
611 procedure LOAD_ROW (
612 X_GRANT_GUID in VARCHAR2,
613 X_GRANTEE_TYPE in VARCHAR2,
614 X_GRANTEE_KEY in VARCHAR2,
615 X_MENU_NAME in VARCHAR2,
616 X_START_DATE in VARCHAR2,
617 X_END_DATE in VARCHAR2,
618 X_OBJ_NAME in VARCHAR2,
619 X_INSTANCE_TYPE in VARCHAR2,
620 X_INSTANCE_SET_NAME in VARCHAR2,
621 X_INSTANCE_PK1_VALUE in VARCHAR2,
622 X_INSTANCE_PK2_VALUE in VARCHAR2,
623 X_INSTANCE_PK3_VALUE in VARCHAR2,
624 X_INSTANCE_PK4_VALUE in VARCHAR2,
625 X_INSTANCE_PK5_VALUE in VARCHAR2,
626 X_PROGRAM_NAME in VARCHAR2,
627 X_PROGRAM_TAG in VARCHAR2,
628 X_OWNER in VARCHAR2,
629 X_CUSTOM_MODE in VARCHAR2
630 ) is
631 begin
632
633 fnd_grants_pkg.LOAD_ROW (
634 X_GRANT_GUID => X_GRANT_GUID,
635 X_GRANTEE_TYPE => X_GRANTEE_TYPE,
636 X_GRANTEE_KEY => X_GRANTEE_KEY,
637 X_MENU_NAME => X_MENU_NAME,
638 X_START_DATE => X_START_DATE,
639 X_END_DATE => X_END_DATE,
640 X_OBJ_NAME => X_OBJ_NAME,
641 X_INSTANCE_TYPE => X_INSTANCE_TYPE,
642 X_INSTANCE_SET_NAME => X_INSTANCE_SET_NAME,
643 X_INSTANCE_PK1_VALUE => X_INSTANCE_PK1_VALUE,
644 X_INSTANCE_PK2_VALUE => X_INSTANCE_PK2_VALUE,
645 X_INSTANCE_PK3_VALUE => X_INSTANCE_PK3_VALUE,
646 X_INSTANCE_PK4_VALUE => X_INSTANCE_PK4_VALUE,
647 X_INSTANCE_PK5_VALUE => X_INSTANCE_PK5_VALUE,
648 X_PROGRAM_NAME => X_PROGRAM_NAME,
649 X_PROGRAM_TAG => X_PROGRAM_TAG,
650 X_OWNER => X_OWNER,
651 X_CUSTOM_MODE => X_CUSTOM_MODE,
652 X_LAST_UPDATE_DATE => null
653 );
654 end LOAD_ROW;
655
656 /* Overloaded version above. This is the new version. Use this. */
657 procedure LOAD_ROW (
658 X_GRANT_GUID in VARCHAR2,
659 X_GRANTEE_TYPE in VARCHAR2,
660 X_GRANTEE_KEY in VARCHAR2,
661 X_MENU_NAME in VARCHAR2,
662 X_START_DATE in VARCHAR2,
663 X_END_DATE in VARCHAR2,
664 X_OBJ_NAME in VARCHAR2,
665 X_INSTANCE_TYPE in VARCHAR2,
666 X_INSTANCE_SET_NAME in VARCHAR2,
667 X_INSTANCE_PK1_VALUE in VARCHAR2,
668 X_INSTANCE_PK2_VALUE in VARCHAR2,
669 X_INSTANCE_PK3_VALUE in VARCHAR2,
670 X_INSTANCE_PK4_VALUE in VARCHAR2,
671 X_INSTANCE_PK5_VALUE in VARCHAR2,
672 X_PROGRAM_NAME in VARCHAR2,
673 X_PROGRAM_TAG in VARCHAR2,
674 X_OWNER in VARCHAR2,
675 X_CUSTOM_MODE in VARCHAR2,
676 X_LAST_UPDATE_DATE in VARCHAR2,
677 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
678 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
679 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
680 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
681 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
682 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
683 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
684 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
685 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
686 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
687 X_CTX_SECURITY_GROUP_KEY in VARCHAR2 default '*GLOBAL*',
688 X_CTX_RESP_KEY in VARCHAR2 default '*GLOBAL*',
689 X_CTX_RESP_APP_SHORT_NAME in VARCHAR2 default '*GLOBAL*',
690 X_CTX_ORGANIZATION in VARCHAR2 default '*GLOBAL*',
691 X_NAME in VARCHAR2 default '*NOTPASSED*',
692 X_DESCRIPTION in VARCHAR2 default '*NOTPASSED*'
693 ) is
694 obj_id number := NULL;
695 mnu_id number := NULL;
696 ins_set_id number := NULL;
697 row_id varchar2(64);
698 f_luby number; -- entity owner in file
699 f_ludate date; -- entity update date in file
700 db_luby number; -- entity owner in db
701 db_ludate date; -- entity update date in db
702 secgrp_id number := -1;
703 resp_id number := -1;
704 respl_id number := -1;
705 resp_appl_id number := -1;
706 org_id number := -1;
707 l_instance_pk1_value varchar2(256);
708 l_instance_pk2_value varchar2(256);
709 l_instance_pk3_value varchar2(256);
710 l_instance_pk4_value varchar2(256);
711 l_instance_pk5_value varchar2(256);
712 l_grantee_key varchar2(240);
713 l_name varchar2(80);
714 l_description varchar2(320);
715 app_id number := NULL;
716
717 begin
718
719 -- Translate owner to file_last_updated_by
720 f_luby := fnd_load_util.owner_id(x_owner);
721
722 -- Translate char last_update_date to date
723 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
724
725 -- object_name and menu_name have reference command in afsload.lct
726 -- So we don't have to worry about whether it is valid or not because
727
728 if (X_OBJ_NAME is not NULL) then
729 if (X_OBJ_NAME = 'GLOBAL') then
730 obj_id := -1;
731 else
732 begin
733 select object_id
734 into obj_id
735 from fnd_objects
736 where obj_name = X_OBJ_NAME;
737 exception
738 when no_data_found then
739 obj_id := NULL;
740 end;
741 end if;
742 if (obj_id is NULL) then
743 FND_MESSAGE.SET_NAME('FND', 'SQL_NO_DATA_FOUND');
744 FND_MESSAGE.SET_TOKEN('VALUE', X_OBJ_NAME);
745 FND_MESSAGE.SET_TOKEN('COLUMN', 'OBJ_NAME');
746 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_OBJECTS');
747 app_exception.raise_exception;
748 end if;
749 else
750 obj_id := NULL;
751 end if;
752
753 if (X_MENU_NAME is not NULL) then
754 begin
755 select menu_id
756 into mnu_id
757 from fnd_menus
758 where menu_name = X_MENU_NAME;
759 exception
760 when no_data_found then
761 mnu_id := NULL;
762 end;
763 if (mnu_id is NULL) then
764 FND_MESSAGE.SET_NAME('FND', 'SQL_NO_DATA_FOUND');
765 FND_MESSAGE.SET_TOKEN('VALUE', X_MENU_NAME);
766 FND_MESSAGE.SET_TOKEN('COLUMN', 'MENU_NAME');
767 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_MENUS');
768 app_exception.raise_exception;
769 end if;
770 else
771 mnu_id := NULL;
772 end if;
773
774
775 if (X_INSTANCE_SET_NAME is not NULL) then
776 begin
777 select instance_set_id
778 into ins_set_id
779 from fnd_object_instance_sets
780 where instance_set_name = X_INSTANCE_SET_NAME;
781 exception
782 when no_data_found then
783 ins_set_id := NULL;
784 end;
785 if (ins_set_id is NULL) then
786 FND_MESSAGE.SET_NAME('FND', 'SQL_NO_DATA_FOUND');
787 FND_MESSAGE.SET_TOKEN('VALUE', X_INSTANCE_SET_NAME);
788 FND_MESSAGE.SET_TOKEN('COLUMN', 'INSTANCE_SET_NAME');
789 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_OBJECT_INSTANCE_SETS');
790 app_exception.raise_exception;
791 end if;
792 else
793 ins_set_id := NULL;
794 end if;
795
796
797 if((X_CTX_SECURITY_GROUP_KEY is not NULL)
798 AND (X_CTX_SECURITY_GROUP_KEY <> '*GLOBAL*'))then
799 begin
800 select security_group_id into secgrp_id
801 from fnd_security_groups
802 where security_group_key = X_CTX_SECURITY_GROUP_KEY;
803 exception
804 when no_data_found then
805 secgrp_id := -1;
806 end;
807 if (secgrp_id = -1) then
808 FND_MESSAGE.SET_NAME('FND', 'SQL_NO_DATA_FOUND');
809 FND_MESSAGE.SET_TOKEN('VALUE', X_CTX_SECURITY_GROUP_KEY);
810 FND_MESSAGE.SET_TOKEN('COLUMN', 'SECURITY_GROUP_KEY');
811 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_SECURITY_GROUPS');
812 app_exception.raise_exception;
813 end if;
814 else
815 secgrp_id := -1;
816 end if;
817
818 if((X_CTX_RESP_APP_SHORT_NAME is not NULL)
819 AND (X_CTX_RESP_APP_SHORT_NAME <> '*GLOBAL*'))then
820 begin
821 select application_id into resp_appl_id
822 from fnd_application
823 where application_short_name = X_CTX_RESP_APP_SHORT_NAME;
824 exception
825 when no_data_found then
826 resp_appl_id := -1;
827 end;
828 if (resp_appl_id = -1) then
829 FND_MESSAGE.SET_NAME('FND', 'SQL_NO_DATA_FOUND');
830 FND_MESSAGE.SET_TOKEN('VALUE', X_CTX_RESP_APP_SHORT_NAME);
831 FND_MESSAGE.SET_TOKEN('COLUMN', 'APPLICATION_SHORT_NAME');
832 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_APPLICATION');
833 app_exception.raise_exception;
834 end if;
835 else
836 resp_appl_id := -1;
837 end if;
838
839 if((X_CTX_RESP_KEY is not NULL)
840 AND (X_CTX_RESP_KEY <> '*GLOBAL*'))then
841 begin
842 select responsibility_id into resp_id
843 from fnd_responsibility
844 where responsibility_key = X_CTX_RESP_KEY
845 and application_id = resp_appl_id;
846 exception
847 when no_data_found then
848 resp_id := -1;
849 end;
850 if (resp_id = -1) then
851 FND_MESSAGE.SET_NAME('FND', 'SQL_NO_DATA_FOUND');
852 FND_MESSAGE.SET_TOKEN('VALUE', X_CTX_RESP_KEY);
853 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESPONSIBILITY_KEY');
854 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_RESPONSIBILITY');
855 app_exception.raise_exception;
856 end if;
857 else
858 resp_id := -1;
859 end if;
860
861 if((X_CTX_ORGANIZATION is not NULL)
862 AND (X_CTX_ORGANIZATION <> '*GLOBAL*'))then
863 org_id := to_number(X_CTX_ORGANIZATION);
864 else
865 org_id := -1;
866 end if;
867
868 /* only allowed grantee_key is 'GLOBAL' for grantee_type 'GLOBAL'*/
869 if(x_grantee_type = 'GLOBAL') then
870 l_grantee_key := 'GLOBAL';
871 else
872 if ((instr(x_grantee_key, 'FND_RESP')= 1) and (instr(x_grantee_key,'FND_RESP|')=0))
873 then
874 app_id := to_number(substr(x_grantee_key, 9, instr(x_grantee_key, ':')-9));
875 respl_id := to_number(substr(x_grantee_key, instr(x_grantee_key, ':')+1));
876 l_grantee_key := fnd_user_resp_groups_api.upgrade_resp_role(respl_id, app_id);
877 else
878 l_grantee_key := x_grantee_key;
879 end if;
880 end if;
881
882 if(x_instance_pk1_value is NULL) then
883 l_instance_pk1_value := '*NULL*';
884 else
885 l_instance_pk1_value := x_instance_pk1_value;
886 end if;
887
888 if(x_instance_pk2_value is NULL) then
889 l_instance_pk2_value := '*NULL*';
890 else
891 l_instance_pk2_value := x_instance_pk2_value;
892 end if;
893
894 if(x_instance_pk3_value is NULL) then
895 l_instance_pk3_value := '*NULL*';
896 else
897 l_instance_pk3_value := x_instance_pk3_value;
898 end if;
899
900 if(x_instance_pk4_value is NULL) then
901 l_instance_pk4_value := '*NULL*';
902 else
903 l_instance_pk4_value := x_instance_pk4_value;
904 end if;
905
906 if(x_instance_pk5_value is NULL) then
907 l_instance_pk5_value := '*NULL*';
908 else
909 l_instance_pk5_value := x_instance_pk5_value;
910 end if;
911
912
913 /* If there isn't yet any row, this will raise a no_data_found*/
914 /* exception and a new row will get inserted. */
915 select last_updated_by, last_update_date
916 into db_luby, db_ludate
917 from fnd_grants
918 where grant_guid = hextoraw(X_GRANT_GUID);
919
920 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
921 db_ludate, X_CUSTOM_MODE)) then
922 FND_GRANTS_PKG.UPDATE_ROW(
923 X_GRANT_GUID => hextoraw(X_GRANT_GUID),
924 X_GRANTEE_TYPE => X_GRANTEE_TYPE,
925 X_GRANTEE_KEY => L_GRANTEE_KEY,
926 X_MENU_ID => mnu_id,
927 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
928 X_END_DATE => to_date(X_END_DATE, 'YYYY/MM/DD'),
929 X_OBJECT_ID => obj_id,
930 X_INSTANCE_TYPE => X_INSTANCE_TYPE,
931 X_INSTANCE_SET_ID => ins_set_id,
932 X_INSTANCE_PK1_VALUE => L_INSTANCE_PK1_VALUE,
933 X_INSTANCE_PK2_VALUE => L_INSTANCE_PK2_VALUE,
934 X_INSTANCE_PK3_VALUE => L_INSTANCE_PK3_VALUE,
935 X_INSTANCE_PK4_VALUE => L_INSTANCE_PK4_VALUE,
936 X_INSTANCE_PK5_VALUE => L_INSTANCE_PK5_VALUE,
937 X_PARAMETER1 => X_PARAMETER1,
938 X_PARAMETER2 => X_PARAMETER2,
939 X_PARAMETER3 => X_PARAMETER3,
940 X_PARAMETER4 => X_PARAMETER4,
941 X_PARAMETER5 => X_PARAMETER5,
942 X_PARAMETER6 => X_PARAMETER6,
943 X_PARAMETER7 => X_PARAMETER7,
944 X_PARAMETER8 => X_PARAMETER8,
945 X_PARAMETER9 => X_PARAMETER9,
946 X_PARAMETER10 => X_PARAMETER10,
947 X_CTX_SECGRP_ID => secgrp_id,
948 X_CTX_RESP_ID => resp_id,
949 X_CTX_RESP_APPL_ID => resp_appl_id,
950 X_CTX_ORG_ID => org_id,
951 X_PROGRAM_NAME => X_PROGRAM_NAME,
952 X_PROGRAM_TAG => X_PROGRAM_TAG,
953 X_LAST_UPDATE_DATE => f_ludate,
954 X_LAST_UPDATED_BY => f_luby,
955 X_LAST_UPDATE_LOGIN => 0,
956 X_NAME => x_name,
957 X_DESCRIPTION => x_description);
958 end if;
959
960 exception
961 when NO_DATA_FOUND then
962 if (obj_id is not null and
963 mnu_id is not null) then
964
965 if(x_name = '*NOTPASSED*') then
966 l_name := NULL;
967 else
968 l_name := x_name;
969 end if;
970
971 if(x_description = '*NOTPASSED*') then
972 l_description := NULL;
973 else
974 l_description := x_description;
975 end if;
976
977
978 FND_GRANTS_PKG.INSERT_ROW(
979 X_ROWID => row_id,
980 X_GRANT_GUID => hextoraw(X_GRANT_GUID),
981 X_GRANTEE_TYPE => X_GRANTEE_TYPE,
982 X_GRANTEE_KEY => L_GRANTEE_KEY,
983 X_MENU_ID => mnu_id,
984 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
985 X_END_DATE => to_date(X_END_DATE, 'YYYY/MM/DD'),
986 X_OBJECT_ID => obj_id,
987 X_INSTANCE_TYPE => X_INSTANCE_TYPE,
988 X_INSTANCE_SET_ID => ins_set_id,
989 X_INSTANCE_PK1_VALUE => L_INSTANCE_PK1_VALUE,
990 X_INSTANCE_PK2_VALUE => L_INSTANCE_PK2_VALUE,
991 X_INSTANCE_PK3_VALUE => L_INSTANCE_PK3_VALUE,
992 X_INSTANCE_PK4_VALUE => L_INSTANCE_PK4_VALUE,
993 X_INSTANCE_PK5_VALUE => L_INSTANCE_PK5_VALUE,
994 X_PARAMETER1 => X_PARAMETER1,
995 X_PARAMETER2 => X_PARAMETER2,
996 X_PARAMETER3 => X_PARAMETER3,
997 X_PARAMETER4 => X_PARAMETER4,
998 X_PARAMETER5 => X_PARAMETER5,
999 X_PARAMETER6 => X_PARAMETER6,
1000 X_PARAMETER7 => X_PARAMETER7,
1001 X_PARAMETER8 => X_PARAMETER8,
1002 X_PARAMETER9 => X_PARAMETER9,
1003 X_PARAMETER10 => X_PARAMETER10,
1004 X_CTX_SECGRP_ID => secgrp_id,
1005 X_CTX_RESP_ID => resp_id,
1006 X_CTX_RESP_APPL_ID => resp_appl_id,
1007 X_CTX_ORG_ID => org_id,
1008 X_PROGRAM_NAME => X_PROGRAM_NAME,
1009 X_PROGRAM_TAG => X_PROGRAM_TAG,
1010 X_CREATION_DATE => f_ludate,
1011 X_CREATED_BY => f_luby,
1012 X_LAST_UPDATE_DATE => f_ludate,
1013 X_LAST_UPDATED_BY => f_luby,
1014 X_LAST_UPDATE_LOGIN => 0,
1015 X_NAME => l_name,
1016 X_DESCRIPTION =>l_description
1017 );
1018 else
1019 -- Data corruption. Bad menu or bad instance set or bad object.
1020 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
1021 fnd_message.set_token('REASON',
1022 'Error- data corruption for GRANT:'||X_GRANT_GUID);
1023 app_exception.raise_exception;
1024
1025 end if;
1026 end LOAD_ROW;
1027
1028 PROCEDURE grant_function
1029 (
1030 p_api_version IN NUMBER,
1031 p_menu_name IN VARCHAR2,
1032 p_object_name IN VARCHAR2,
1033 p_instance_type IN VARCHAR2,
1034 p_instance_set_id IN NUMBER DEFAULT NULL,
1035 p_instance_pk1_value IN VARCHAR2 DEFAULT NULL,
1036 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
1037 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
1038 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
1039 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
1040 p_grantee_type IN VARCHAR2 DEFAULT 'USER',
1041 p_grantee_key IN VARCHAR2,
1042 p_start_date IN DATE,
1043 p_end_date IN DATE,
1044 p_program_name IN VARCHAR2 DEFAULT NULL,
1045 p_program_tag IN VARCHAR2 DEFAULT NULL,
1046 x_grant_guid OUT NOCOPY RAW,
1047 x_success OUT NOCOPY VARCHAR, /* Boolean */
1048 x_errorcode OUT NOCOPY NUMBER,
1049 p_parameter1 IN VARCHAR2 DEFAULT NULL,
1050 p_parameter2 IN VARCHAR2 DEFAULT NULL,
1051 p_parameter3 IN VARCHAR2 DEFAULT NULL,
1052 p_parameter4 IN VARCHAR2 DEFAULT NULL,
1053 p_parameter5 IN VARCHAR2 DEFAULT NULL,
1054 p_parameter6 IN VARCHAR2 DEFAULT NULL,
1055 p_parameter7 IN VARCHAR2 DEFAULT NULL,
1056 p_parameter8 IN VARCHAR2 DEFAULT NULL,
1057 p_parameter9 IN VARCHAR2 DEFAULT NULL,
1058 p_parameter10 IN VARCHAR2 DEFAULT NULL,
1059 p_ctx_secgrp_id IN NUMBER default -1,
1060 p_ctx_resp_id IN NUMBER default -1,
1061 p_ctx_resp_appl_id IN NUMBER default -1,
1062 p_ctx_org_id IN NUMBER default -1,
1063 p_name in VARCHAR2 default null,
1064 p_description in VARCHAR2 default null
1065 ) is
1066
1067 l_api_name CONSTANT VARCHAR2(30):= 'GRANT_FUNCTION';
1068
1069 -- On addition of any Required parameters the major version needs
1070 -- to change i.e. for eg. 1.X to 2.X.
1071 -- On addition of any Optional parameters the minor version needs
1072 -- to change i.e. for eg. X.6 to X.7.
1073 l_api_version CONSTANT NUMBER := 1.0;
1074 l_menu_id fnd_grants.menu_id%TYPE;
1075 l_sys_date DATE := Sysdate;
1076 l_user_id number:=fnd_global.user_id;
1077 l_grant_guid raw(16);
1078 l_row_id varchar2(18) ;
1079 l_object_id number;
1080 l_grantee_key varchar2(240);
1081 resp_id number := NULL;
1082 app_id number := NULL;
1083
1084 CURSOR get_menu_id(p_menu_name VARCHAR2) IS
1085 SELECT menu_id
1086 FROM fnd_menus
1087 WHERE menu_name =p_menu_name;
1088
1089
1090 CURSOR get_object_id (p_object_name varchar2) is
1091 select object_id
1092 from fnd_objects
1093 where obj_name=p_object_name;
1094 BEGIN
1095 x_grant_guid := NULL;
1096 x_success := FND_API.G_FALSE ;
1097 x_errorcode:=-1;
1098
1099 IF NOT FND_API.Compatible_API_Call (l_api_version,
1100 p_api_version ,
1101 l_api_name ,
1102 G_PKG_NAME)
1103 THEN
1104 if (fnd_log.LEVEL_EXCEPTION >=
1105 fnd_log.g_current_runtime_level) then
1106
1107 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1108 fnd_message.set_token('ROUTINE',
1109 G_PKG_NAME||'.'||l_api_name);
1110 fnd_message.set_token('REASON',
1111 'Unsupported version '|| to_char(p_api_version)||
1112 ' passed to API; expecting version '||
1113 to_char(l_api_version));
1114 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1115 G_log_head || l_api_name || '.end_bad_api_ver',
1116 FALSE);
1117 end if;
1118 x_success := FND_API.G_FALSE;
1119 x_errorcode:=-1;
1120 return;
1121 END IF;
1122
1123
1124 -- Initialize API return status to success
1125 x_success := FND_API.G_TRUE;
1126
1127
1128 -- Step 2
1129 -- get role id for from FND_MENUS
1130 OPEN get_menu_id(p_menu_name);
1131 FETCH get_menu_id INTO l_menu_id;
1132 IF (get_menu_id%NOTFOUND) THEN
1133 CLOSE get_menu_id;
1134 fnd_message.set_name('FND','FND_INVALID_MENU_NAME');
1135 fnd_msg_pub.ADD; /* Add for backward compatibility because in */
1136 /* the past this API put messages on fnd_msg_pub */
1137 /* stack. That's obsolete. FND_MESSAGE is now */
1138 /* used. */
1139 fnd_message.set_name('FND','FND_INVALID_MENU_NAME');
1140 x_success := FND_API.G_FALSE ;
1141 x_errorcode:=1;
1142 return;
1143 END IF;
1144 CLOSE get_menu_id;
1145
1146 ---Step 3.
1147 --- Get object_id from the fnd_objects
1148 if (p_object_name = 'GLOBAL') then
1149 l_object_id := -1;
1150 else
1151 open get_object_id(p_object_name);
1152 fetch get_object_id into l_object_id;
1153 if (get_object_id%NOTFOUND) then
1154 close get_object_id;
1155 fnd_message.set_name('FND','FND_INVALID_OBJECT_NAME');
1156 fnd_msg_pub.ADD; /* Add for backward compatibility because in */
1157 /* the past this API put messages on fnd_msg_pub */
1158 /* stack. That's obsolete. FND_MESSAGE is now */
1159 /* used. */
1160 fnd_message.set_name('FND','FND_INVALID_OBJECT_NAME');
1161 x_success := FND_API.G_FALSE ;
1162 x_errorcode:=1;
1163 return;
1164 end if ;
1165 close get_object_id;
1166 end if;
1167
1168
1169 -- Step 4.
1170 -- Insert a row
1171 select sys_guid()
1172 into l_grant_guid
1173 from dual;
1174
1175
1176
1177 INSERT_ROW (
1178 X_ROWID =>l_row_id,
1179 X_GRANT_GUID =>l_grant_guid,
1180 X_GRANTEE_TYPE=>p_grantee_type,
1181 X_GRANTEE_KEY =>p_grantee_key,
1182 X_menu_id =>l_menu_id,
1183 X_START_DATE =>p_start_date,
1184 X_END_DATE =>p_end_date,
1185 X_OBJECT_ID =>l_object_id,
1186 X_INSTANCE_TYPE =>p_instance_type,
1187 x_instance_set_id =>p_instance_set_id,
1188 X_INSTANCE_PK1_VALUE =>p_instance_PK1_value,
1189 X_INSTANCE_PK2_VALUE =>p_instance_PK2_value,
1190 X_INSTANCE_PK3_VALUE =>p_instance_PK3_value,
1191 X_INSTANCE_PK4_VALUE =>p_instance_PK4_value,
1192 X_INSTANCE_PK5_VALUE =>p_instance_PK5_value,
1193 X_PARAMETER1 => P_PARAMETER1,
1194 X_PARAMETER2 => P_PARAMETER2,
1195 X_PARAMETER3 => P_PARAMETER3,
1196 X_PARAMETER4 => P_PARAMETER4,
1197 X_PARAMETER5 => P_PARAMETER5,
1198 X_PARAMETER6 => P_PARAMETER6,
1199 X_PARAMETER7 => P_PARAMETER7,
1200 X_PARAMETER8 => P_PARAMETER8,
1201 X_PARAMETER9 => P_PARAMETER9,
1202 X_PARAMETER10 => P_PARAMETER10,
1203 X_CTX_SECGRP_ID => P_CTX_SECGRP_ID,
1204 X_CTX_RESP_ID => P_CTX_RESP_ID,
1205 X_CTX_RESP_APPL_ID => P_CTX_RESP_APPL_ID,
1206 X_CTX_ORG_ID => P_CTX_ORG_ID,
1207 X_PROGRAM_NAME =>p_program_name,
1208 X_PROGRAM_TAG =>p_program_tag,
1209 X_CREATION_DATE =>l_sys_date,
1210 X_CREATED_BY =>l_user_id,
1211 X_LAST_UPDATE_DATE =>l_sys_date,
1212 X_LAST_UPDATED_BY =>l_user_id,
1213 X_LAST_UPDATE_LOGIN =>l_user_id,
1214 X_NAME => p_name,
1215 X_DESCRIPTION => p_description
1216 ) ;
1217
1218 x_grant_guid:=l_grant_guid;
1219 x_success := FND_API.G_TRUE;
1220 x_errorcode := NULL;
1221
1222 END grant_function;
1223
1224
1225
1226 PROCEDURE revoke_grant
1227 (
1228 p_api_version IN NUMBER,
1229 p_grant_guid IN raw,
1230 x_success OUT NOCOPY VARCHAR2, /* Boolean */
1231 x_errorcode OUT NOCOPY NUMBER
1232 ) is
1233
1234 l_api_name CONSTANT VARCHAR2(30) := 'revoke_grant';
1235
1236 -- On addition of any Required parameters the major version needs
1237 -- to change i.e. for eg. 1.X to 2.X.
1238 -- On addition of any Optional parameters the minor version needs
1239 -- to change i.e. for eg. X.6 to X.7.
1240 l_api_version CONSTANT NUMBER := 1.0;
1241
1242
1243 BEGIN
1244 x_success := FND_API.G_FALSE ;
1245 x_errorcode:=-1;
1246
1247 -- Standard call to check for call compatibility.
1248 IF NOT FND_API.Compatible_API_Call (l_api_version,
1249 p_api_version ,
1250 l_api_name ,
1251 G_PKG_NAME)
1252 THEN
1253 if (fnd_log.LEVEL_EXCEPTION >=
1254 fnd_log.g_current_runtime_level) then
1255 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1256 fnd_message.set_token('ROUTINE',
1257 G_PKG_NAME||'.'||l_api_name);
1258 fnd_message.set_token('REASON',
1259 'Unsupported version '|| to_char(p_api_version)||
1260 ' passed to API; expecting version '||
1261 to_char(l_api_version));
1262 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1263 G_log_head || l_api_name || '.end_bad_api_ver',
1264 FALSE);
1265 end if;
1266 x_success := FND_API.G_FALSE ;
1267 x_errorcode:=-1;
1268 return;
1269 END IF;
1270
1271 DELETE_ROW ( X_GRANT_GUID=> p_grant_guid);
1272 x_success := FND_API.G_TRUE;
1273 x_errorcode:=NULL;
1274
1275 END revoke_grant;
1276 ----------------------------------------------------------------------------
1277
1278
1279 /* Please call overloaded update_grant below. This version is obsolete */
1280 PROCEDURE update_grant
1281 (
1282 p_api_version IN NUMBER,
1283 p_grant_guid IN raw,
1284 p_start_date IN DATE,
1285 p_end_date IN DATE,
1286 x_success OUT NOCOPY VARCHAR2
1287 ) is
1288 begin
1289 update_grant(p_api_version => p_api_version,
1290 p_grant_guid => p_grant_guid,
1291 p_start_date => p_start_date,
1292 p_end_date => p_end_date,
1293 p_name => '*NOTPASSED*',
1294 p_description => '*NOTPASSED*',
1295 x_success => x_success);
1296
1297 END update_grant;
1298
1299
1300
1301 /* This is the new version of update_grant for new code to use */
1302 PROCEDURE update_grant
1303 (
1304 p_api_version IN NUMBER,
1305 p_grant_guid IN raw,
1306 p_start_date IN DATE,
1307 p_end_date IN DATE,
1308 p_name IN VARCHAR2,
1309 p_description IN VARCHAR2,
1310 x_success OUT NOCOPY VARCHAR2
1311 ) is
1312
1313 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GRANT';
1314
1315 -- On addition of any Required parameters the major version needs
1316 -- to change i.e. for eg. 1.X to 2.X.
1317 -- On addition of any Optional parameters the minor version needs
1318 -- to change i.e. for eg. X.6 to X.7.
1319 l_api_version CONSTANT NUMBER := 1.0;
1320 l_grantee_type VARCHAR2(8);
1321 l_grantee_key VARCHAR2(240);
1322 l_object_id NUMBER;
1323
1324 BEGIN
1325 x_success := FND_API.G_FALSE ;
1326
1327 -- Standard call to check for call compatibility.
1328 IF NOT FND_API.Compatible_API_Call (l_api_version,
1329 p_api_version ,
1330 l_api_name ,
1331 G_PKG_NAME)
1332 THEN
1333 if (fnd_log.LEVEL_EXCEPTION >=
1334 fnd_log.g_current_runtime_level) then
1335 fnd_message.set_name('FND', 'GENERIC-INTERNAL ERROR');
1336 fnd_message.set_token('ROUTINE',
1337 G_PKG_NAME||'.'||l_api_name);
1338 fnd_message.set_token('REASON',
1339 'Unsupported version '|| to_char(p_api_version)||
1340 ' passed to API; expecting version '||
1341 to_char(l_api_version));
1342 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
1343 G_log_head || l_api_name || '.end_bad_api_ver',
1344 FALSE);
1345 end if;
1346 x_success := FND_API.G_FALSE ;
1347 return;
1348 END IF;
1349
1350 if((p_name = '*NOTPASSED*') or (p_description = '*NOTPASSED*')) then
1351
1352 if ((p_name is NULL) or (p_description is NULL)) then
1353 /* Mixing NULL with *NOTPASSED* not allowed. */
1354 fnd_message.set_name('FND','GENERIC-INTERNAL ERROR');
1355 fnd_message.set_token('REASON',
1356 'UPDATE_GRANT caller mixed NULL with *NOTPASSED* on grant guid:'
1357 ||P_GRANT_GUID);
1358 app_exception.raise_exception;
1359 end if;
1360 /* Don't update name and description */
1361 UPDATE fnd_grants
1362 SET start_date=p_start_date,
1363 end_date=p_end_date
1364 WHERE grant_guid= hextoraw(p_grant_guid);
1365 else
1366 /* This version updates name and description */
1367 UPDATE fnd_grants
1368 SET start_date=p_start_date,
1369 end_date=p_end_date,
1370 name = p_name,
1371 description = p_description
1372 WHERE grant_guid= hextoraw(p_grant_guid);
1373 end if;
1374
1375 -- Added for Function Security Cache Invalidation Project
1376 -- bug 3554601 added object_id to verify Function Security
1377 select grantee_type, grantee_key, object_id
1378 into l_grantee_type, l_grantee_key, l_object_id
1379 from fnd_grants
1380 where grant_guid= hextoraw(p_grant_guid);
1381
1382 -- bug 3554601 - Only raise the event if it is Function Security not for
1383 -- Data Security events.
1384 if ( l_object_id = -1 ) then
1385 fnd_function_security_cache.update_grant(p_grant_guid, l_grantee_type, l_grantee_key);
1386 end if;
1387
1388 x_success:=fnd_api.g_true;
1389
1390 END update_grant;
1391
1392
1393 ----------------------------------------------------------------------------
1394 PROCEDURE lock_grant
1395 (
1396 p_grant_guid IN raw,
1397 p_menu_id IN NUMBER,
1398 p_object_id IN number,
1399 p_instance_type IN varchar2,
1400 p_instance_set_id in number,
1401 p_instance_pk1_value IN VARCHAR2,
1402 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
1403 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
1404 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
1405 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
1406 p_grantee_type in varchar2 default 'USER',
1407 p_grantee_key IN varchar2,
1408 p_start_date IN DATE,
1409 p_end_date IN DATE,
1410 p_program_name IN VARCHAR2,
1411 p_program_tag IN VARCHAR2,
1412 p_parameter1 IN VARCHAR2 DEFAULT NULL,
1413 p_parameter2 IN VARCHAR2 DEFAULT NULL,
1414 p_parameter3 IN VARCHAR2 DEFAULT NULL,
1415 p_parameter4 IN VARCHAR2 DEFAULT NULL,
1416 p_parameter5 IN VARCHAR2 DEFAULT NULL,
1417 p_parameter6 IN VARCHAR2 DEFAULT NULL,
1418 p_parameter7 IN VARCHAR2 DEFAULT NULL,
1419 p_parameter8 IN VARCHAR2 DEFAULT NULL,
1420 p_parameter9 IN VARCHAR2 DEFAULT NULL,
1421 p_parameter10 IN VARCHAR2 DEFAULT NULL,
1422 p_ctx_secgrp_id IN NUMBER default -1,
1423 p_ctx_resp_id IN NUMBER default -1,
1424 p_ctx_resp_appl_id IN NUMBER default -1,
1425 p_ctx_org_id IN NUMBER default -1,
1426 p_name IN VARCHAR2 default null,
1427 p_description IN VARCHAR2 default null
1428 ) is
1429 -- l_object_id number;
1430 BEGIN
1431
1432 LOCK_ROW (
1433 X_GRANT_GUID =>p_grant_guid,
1434 X_GRANTEE_TYPE =>p_grantee_type,
1435 X_GRANTEE_key =>p_grantee_key,
1436 X_menu_ID =>p_menu_id,
1437 X_START_DATE =>p_start_date,
1438 X_END_DATE =>p_end_date,
1439 X_OBJECT_ID =>p_object_id,
1440 X_INSTANCE_TYPE =>p_instance_type,
1441 X_INSTANCE_SET_ID =>p_instance_set_id,
1442 X_INSTANCE_PK1_VALUE =>p_instance_pk1_value,
1443 X_INSTANCE_PK2_VALUE =>p_instance_pk2_value,
1444 X_INSTANCE_PK3_VALUE =>p_instance_pk3_value,
1445 X_INSTANCE_PK4_VALUE =>p_instance_pk4_value,
1446 X_INSTANCE_PK5_VALUE =>p_instance_pk5_value,
1447 X_PROGRAM_NAME =>p_program_name,
1448 X_PROGRAM_TAG =>p_program_tag,
1449 X_PARAMETER1 => P_PARAMETER1,
1450 X_PARAMETER2 => P_PARAMETER2,
1451 X_PARAMETER3 => P_PARAMETER3,
1452 X_PARAMETER4 => P_PARAMETER4,
1453 X_PARAMETER5 => P_PARAMETER5,
1454 X_PARAMETER6 => P_PARAMETER6,
1455 X_PARAMETER7 => P_PARAMETER7,
1456 X_PARAMETER8 => P_PARAMETER8,
1457 X_PARAMETER9 => P_PARAMETER9,
1458 X_PARAMETER10 => P_PARAMETER10,
1459 X_CTX_SECGRP_ID => P_CTX_SECGRP_ID,
1460 X_CTX_RESP_ID => P_CTX_RESP_ID,
1461 X_CTX_RESP_APPL_ID => P_CTX_RESP_APPL_ID,
1462 X_CTX_ORG_ID => P_CTX_ORG_ID,
1463 X_NAME => P_NAME,
1464 X_DESCRIPTION => P_DESCRIPTION
1465 );
1466
1467 END lock_grant;
1468
1469 procedure DELETE_ROW (
1470 X_GRANT_GUID in RAW
1471 ) is
1472
1473 l_grantee_type VARCHAR2(8);
1474 l_grantee_key VARCHAR2(240);
1475 l_object_id NUMBER;
1476
1477 begin
1478 -- Pick out the row first, before it gets deleted.
1479 -- bug 3554601 added object_id to verify Function Security
1480 select grantee_type, grantee_key, object_id
1481 into l_grantee_type, l_grantee_key, l_object_id
1482 from fnd_grants
1483 where grant_guid= hextoraw(X_GRANT_GUID);
1484
1485 delete from FND_GRANTS
1486 where GRANT_GUID = hextoraw(X_GRANT_GUID);
1487
1488 if (sql%notfound) then
1489 raise no_data_found;
1490 else
1491 -- Added for Function Security Cache Invalidation Project
1492 -- bug 3554601 - Only raise the event if it is Function Security not for
1493 -- Data Security events.
1494 if ( l_object_id = -1 ) then
1495 fnd_function_security_cache.delete_grant(X_GRANT_GUID, l_grantee_type, l_grantee_key);
1496 end if;
1497 end if;
1498 end DELETE_ROW;
1499
1500
1501 PROCEDURE delete_grant(
1502 p_grantee_type IN VARCHAR2 DEFAULT NULL,
1503 p_grantee_key IN VARCHAR2 DEFAULT NULL,
1504 p_object_name IN VARCHAR2 DEFAULT NULL,
1505 p_instance_type IN VARCHAR2 DEFAULT NULL,
1506 p_instance_set_id IN NUMBER DEFAULT NULL,
1507 p_instance_pk1_value IN VARCHAR2 DEFAULT NULL,
1508 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
1509 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
1510 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
1511 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
1512 p_menu_name IN VARCHAR2 DEFAULT NULL,
1513 p_program_name IN VARCHAR2 DEFAULT NULL,
1514 p_program_tag IN VARCHAR2 DEFAULT NULL,
1515 x_success OUT NOCOPY VARCHAR,
1516 x_errcode OUT NOCOPY NUMBER)IS
1517
1518 type sql_curs_type is REF CURSOR; -- bug3625804 Reference cursor type
1519 del_sql_stmt VARCHAR2(5000); -- bug3625804 delete statement
1520 sel_sql_stmt VARCHAR2(5000); -- bug3625804 select statement
1521 sel_sql_curs sql_curs_type; -- bug3625804 sel SQL cursor
1522 where_clause VARCHAR2(5000); -- bug3625804 where clause
1523 grantee_stmt VARCHAR2(500);
1524 object_stmt VARCHAR2(500);
1525 l_object_id NUMBER;
1526 menu_stmt VARCHAR2(500);
1527 l_menu_id NUMBER;
1528 program_stmt VARCHAR2(500);
1529 l_grant_guid RAW(16);
1530 invalid_args EXCEPTION;
1531
1532 CURSOR get_menu_id(p_menu_name VARCHAR2) IS
1533 SELECT menu_id
1534 FROM FND_MENUS
1535 WHERE menu_name = p_menu_name;
1536
1537 CURSOR get_object_id(p_object_name VARCHAR2) IS
1538 SELECT object_id
1539 FROM fnd_objects
1540 WHERE obj_name = p_object_name;
1541
1542 BEGIN
1543
1544 IF(p_grantee_type is NULL AND p_object_name is NULL AND p_menu_name IS NULL
1545 AND p_program_name is NULL) THEN
1546 fnd_message.set_name('FND','FND_ROUTINE_INVALID_ARGS');
1547 fnd_message.set_token('ROUTINE',
1548 'FND_GRANTS_DELETE_PKG.delete_grant()');
1549 fnd_msg_pub.ADD; /* Add for backward compatibility because in */
1550 /* the past this API put messages on fnd_msg_pub */
1551 /* stack. That's obsolete. FND_MESSAGE is now */
1552 /* used. */
1553 fnd_message.set_name('FND','FND_ROUTINE_INVALID_ARGS');
1554 fnd_message.set_token('ROUTINE',
1555 'FND_GRANTS_DELETE_PKG.delete_grant()');
1556 x_success := 'F';
1557 x_errcode := -1;
1558 return;
1559 END IF;
1560
1561 -- bug3625804 initialize the delete and select statement
1562
1563 del_sql_stmt := 'DELETE FROM FND_GRANTS';
1564 sel_sql_stmt := 'SELECT GRANT_GUID FROM FND_GRANTS';
1565 where_clause := fnd_global.newline||'WHERE 1=1 ';
1566
1567 grantee_stmt := fnd_global.newline||'AND grantee_type = '''||
1568 replace(p_grantee_type, '''', '''''')||''' AND grantee_key = '''||
1569 replace(p_grantee_key, '''', '''''')||'''';
1570
1571 IF(p_object_name is NOT NULL ) THEN
1572 if (p_object_name = 'GLOBAL') then
1573 l_object_id := -1;
1574 else
1575 OPEN get_object_id(p_object_name);
1576 FETCH get_object_id INTO l_object_id;
1577 IF(get_object_id%NOTFOUND) THEN
1578 CLOSE get_object_id;
1579 fnd_message.set_name('FND','FND_INVALID_OBJECT_NAME');
1580 fnd_msg_pub.ADD; /* Add for backward compatibility because in */
1581 /* the past this API put messages on fnd_msg_pub */
1582 /* stack. That's obsolete. FND_MESSAGE is now */
1583 /* used. */
1584 fnd_message.set_name('FND','FND_INVALID_OBJECT_NAME');
1585 x_success := 'F';
1586 x_errcode := 1;
1587 return;
1588 END IF;
1589 CLOSE get_object_id; -- bug5122727 moved to inside of if block
1590 end if;
1591 object_stmt := fnd_global.newline||'AND object_id = '||l_object_id||' AND instance_type = '''||replace(p_instance_type, '''', '''''')||'''';
1592
1593 IF(p_instance_type = 'SET') THEN
1594 IF(p_instance_set_id IS NOT NULL) THEN
1595 object_stmt := object_stmt||
1596 ' AND instance_set_id = '||p_instance_set_id;
1597 END IF;
1598 ELSIF(p_instance_type = 'INSTANCE') THEN
1599 IF(p_instance_pk1_value IS NOT NULL) THEN
1600 object_stmt := object_stmt||' AND instance_pk1_value = '''||
1601 replace(p_instance_pk1_value, '''', '''''')||'''';
1602 END IF;
1603
1604 IF(p_instance_pk2_value IS NOT NULL) THEN
1605 object_stmt := object_stmt||' AND instance_pk2_value = '''||
1606 replace(p_instance_pk2_value, '''', '''''')||'''';
1607 END IF;
1608
1609 IF(p_instance_pk3_value IS NOT NULL) THEN
1610 object_stmt := object_stmt||' AND instance_pk3_value = '''||
1611 replace(p_instance_pk3_value, '''', '''''')||'''';
1612 END IF;
1613
1614 IF(p_instance_pk4_value IS NOT NULL) THEN
1615 object_stmt := object_stmt||' AND instance_pk4_value = '''||
1616 replace(p_instance_pk4_value, '''', '''''')||'''';
1617 END IF;
1618
1619 IF(p_instance_pk5_value IS NOT NULL) THEN
1620 object_stmt := object_stmt||' AND instance_pk5_value = '''||
1621 replace(p_instance_pk5_value, '''', '''''')||'''';
1622 END IF;
1623 END IF;
1624 END IF;
1625 IF(p_menu_name IS NOT NULL) THEN
1626 OPEN get_menu_id(p_menu_name);
1627 FETCH get_menu_id INTO l_menu_id;
1628 IF(get_menu_id%NOTFOUND) THEN
1629 CLOSE get_menu_id;
1630 fnd_message.set_name('FND','FND_INVALID_MENU_NAME');
1631 fnd_msg_pub.ADD; /* Add for backward compatibility because in */
1632 /* the past this API put messages on fnd_msg_pub */
1633 /* stack. That's obsolete. FND_MESSAGE is now */
1634 /* used. */
1635 fnd_message.set_name('FND','FND_INVALID_MENU_NAME');
1636 x_success := 'F';
1637 x_errcode := 1;
1638 return;
1639 END IF;
1640 CLOSE get_menu_id;
1641 menu_stmt := fnd_global.newline||'AND menu_id = '||l_menu_id;
1642 END IF;
1643
1644 program_stmt := fnd_global.newline||'AND program_name = '''||replace(p_program_name, '''', '''''')||'''';
1645
1646 IF(p_program_tag IS NOT NULL ) THEN
1647 program_stmt := program_stmt||' AND program_tag = '''||
1648 replace(p_program_tag, '''', '''''')||'''';
1649 END IF;
1650
1651 --bug3625804 - Modified code to build a where clause instead of
1652 -- the delete SQL as it was before.
1653
1654 IF(p_grantee_type IS NOT NULL) THEN
1655 where_clause := where_clause||grantee_stmt;
1656 END IF;
1657
1658 IF(p_object_name IS NOT NULL) THEN
1659 IF(p_grantee_type IS NOT NULL)THEN
1660 where_clause := where_clause||object_stmt;
1661 END IF;
1662 END IF;
1663
1664 IF(p_menu_name IS NOT NULL) THEN
1665 IF(p_grantee_type IS NOT NULL or p_object_name IS NOT NULL) THEN
1666 where_clause := where_clause||menu_stmt;
1667 END IF;
1668 END IF;
1669
1670 IF(p_program_name IS NOT NULL) THEN
1671 IF(p_grantee_type IS NOT NULL or p_object_name IS NOT NULL
1672 or p_menu_name IS NOT NULL) THEN
1673 where_clause := where_clause||program_stmt;
1674 END IF;
1675 END IF;
1676
1677 -- bug3625804 append the built where clause
1678
1679 del_sql_stmt := del_sql_stmt||where_clause;
1680
1681 EXECUTE IMMEDIATE del_sql_stmt;
1682
1683 -- bug3625804 check if function security then create cursor
1684 -- to select object_id and grant_guid in order
1685 -- to raise the delete_grant event.
1686
1687 IF ((p_object_name is NULL ) or (l_object_id = -1)) THEN
1688
1689 sel_sql_stmt := sel_sql_stmt||where_clause||' and object_id = -1';
1690
1691 OPEN sel_sql_curs FOR sel_sql_stmt;
1692
1693 LOOP
1694 FETCH sel_sql_curs INTO l_grant_guid;
1695 EXIT when sel_sql_curs%notfound;
1696
1697 -- Added for Function Security Cache Invalidation Project
1698 -- bug 3554601 - Only raise the event if it is Function Security not for
1699 -- Data Security events.
1700 fnd_function_security_cache.delete_grant(l_grant_guid, p_grantee_type, p_grantee_key);
1701 END LOOP;
1702 END IF;
1703 x_success := 'T';
1704 x_errcode := NULL;
1705 END delete_grant;
1706
1707
1708 -- fill_in_orig_columns
1709 -- This routine is mostly for AOL internal use by this loader itself;
1710 -- it fills in the columns grantee_orig_system and
1711 -- grantee_orig_system_id from the grantee_key.
1712 procedure fill_in_orig_columns(p_grant_guid IN raw) is
1713
1714 l_grantee_type varchar2(8);
1715 l_grantee_key varchar2(240);
1716 l_orig_system varchar2(48) := NULL;
1717 l_orig_system_id number := NULL;
1718 l_object_id NUMBER;
1719 begin
1720 -- bug 3554601 added object_id to verify Function Security
1721 select grantee_type, grantee_key, object_id
1722 into l_grantee_type, l_grantee_key, l_object_id
1723 from fnd_grants
1724 where grant_guid = hextoraw(p_grant_guid);
1725
1726 wf_directory.GetRoleOrigSysInfo(
1727 Role => l_grantee_key,
1728 Orig_System => l_orig_system,
1729 Orig_System_Id => l_orig_system_id);
1730
1731 UPDATE fnd_grants
1732 SET grantee_orig_system = l_orig_system,
1733 grantee_orig_system_id = l_orig_system_id
1734 WHERE grant_guid= hextoraw(p_grant_guid);
1735
1736 -- Added for Function Security Cache Invalidation Project
1737 -- bug 3554601 - Only raise the event if it is Function Security not for
1738 -- Data Security events.
1739 if ( l_object_id = -1 ) then
1740 fnd_function_security_cache.update_grant(p_grant_guid, l_grantee_type, l_grantee_key);
1741 end if;
1742
1743 end fill_in_orig_columns;
1744
1745 -- fill_in_missing_orig_columns
1746 -- This routine is mostly for AOL internal use at upgrade time;
1747 -- it fills in the columns grantee_orig_system and
1748 -- grantee_orig_system_id from the grantee_key for all grants that
1749 -- are missing them.
1750 -- This should not be called at runtime because every time it runs
1751 -- it will revisit some rows.
1752 -- Runtime code should call this package to manipulate the data so
1753 -- it will automatically keep the orig_ columns in sync without
1754 -- the need to call this routine.
1755 procedure fill_in_missing_orig_columns is
1756 cursor find_missing_cols_c is
1757 select grant_guid
1758 from fnd_grants
1759 where grantee_key is not NULL
1760 and grantee_orig_system is NULL;
1761 begin
1762 for c1 in find_missing_cols_c loop
1763 fill_in_orig_columns(c1.grant_guid);
1764 end loop;
1765 end fill_in_missing_orig_columns;
1766
1767 /* CONVERT_NULLS- For install time use only, not a */
1768 /* runtime routine. This routine will convert NULL to '*NULL*' in the */
1769 /* columns INSTANCE_PKX_VALUE in the table FND_GRANTS. */
1770 /* The reason for this routine is that we decided to have those columns be */
1771 /* non-NULL in order to speed up queries that go against different numbers */
1772 /* of pk columns. This should be run once at patch application time and */
1773 /* should never need to be run again. This will be included in the ATG */
1774 /* data security patch. */
1775 /* returns number of rows converted. */
1776 function CONVERT_NULLS return NUMBER is
1777
1778 l_grantee_type varchar2(8);
1779 l_grantee_key varchar2(240);
1780
1781 cursor c1 is
1782 select grant_guid,
1783 instance_pk1_value,
1784 instance_pk2_value,
1785 instance_pk3_value,
1786 instance_pk4_value,
1787 instance_pk5_value
1788 from fnd_grants
1789 where (instance_pk1_value is NULL)
1790 or (instance_pk2_value is NULL)
1791 or (instance_pk3_value is NULL)
1792 or (instance_pk4_value is NULL)
1793 or (instance_pk5_value is NULL);
1794 l_pk1 varchar2(256);
1795 l_pk2 varchar2(256);
1796 l_pk3 varchar2(256);
1797 l_pk4 varchar2(256);
1798 l_pk5 varchar2(256);
1799 l_guid RAW(16);
1800 i INTEGER;
1801 l_object_id NUMBER;
1802 begin
1803 i := 0;
1804 for c1rec in c1 loop
1805 l_pk1 := c1rec.instance_pk1_value;
1806 l_pk2 := c1rec.instance_pk2_value;
1807 l_pk3 := c1rec.instance_pk3_value;
1808 l_pk4 := c1rec.instance_pk4_value;
1809 l_pk5 := c1rec.instance_pk5_value;
1810 l_guid := c1rec.grant_guid;
1811
1812 if(l_pk1 is NULL) then
1813 l_pk1 := '*NULL*';
1814 end if;
1815 if(l_pk2 is NULL) then
1816 l_pk2 := '*NULL*';
1817 end if;
1818 if(l_pk3 is NULL) then
1819 l_pk3 := '*NULL*';
1820 end if;
1821 if(l_pk4 is NULL) then
1822 l_pk4 := '*NULL*';
1823 end if;
1824 if(l_pk5 is NULL) then
1825 l_pk5 := '*NULL*';
1826 end if;
1827
1828 -- bug 3554601 added object_id to verify Function Security
1829 select grantee_type, grantee_key, object_id
1830 into l_grantee_type, l_grantee_key, l_object_id
1831 from fnd_grants
1832 where grant_guid = hextoraw(l_guid);
1833
1834 update fnd_grants set
1835 instance_pk1_value = l_pk1,
1836 instance_pk2_value = l_pk2,
1837 instance_pk3_value = l_pk3,
1838 instance_pk4_value = l_pk4,
1839 instance_pk5_value = l_pk5
1840 where
1841 grant_guid = hextoraw(l_guid);
1842
1843 -- Added for Function Security Cache Invalidation Project
1844 -- bug 3554601 - Only raise the event if it is Function Security not for
1845 -- Data Security events.
1846 if ( l_object_id = -1 ) then
1847 fnd_function_security_cache.update_grant(l_guid, l_grantee_type, l_grantee_key);
1848 end if;
1849
1850 if (mod(i, 100) = 0) then
1851 commit;
1852 end if;
1853
1854 i := i+1;
1855 end loop;
1856 commit;
1857 return i;
1858 end;
1859
1860 end FND_GRANTS_PKG;