1 package FND_GRANTS_PKG AUTHID CURRENT_USER as
2 /* $Header: AFSCGNTS.pls 115.23 2003/12/16 02:49:37 tmorrow ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_GRANT_GUID in RAW,
7 X_GRANTEE_TYPE in VARCHAR2,
8 X_GRANTEE_KEY in VARCHAR2,
9 X_MENU_ID in NUMBER,
10 X_START_DATE in DATE,
11 X_END_DATE in DATE,
12 X_OBJECT_ID in NUMBER,
13 X_INSTANCE_TYPE in VARCHAR2,
14 X_INSTANCE_SET_ID in NUMBER,
15 X_INSTANCE_PK1_VALUE in VARCHAR2,
16 X_INSTANCE_PK2_VALUE in VARCHAR2,
17 X_INSTANCE_PK3_VALUE in VARCHAR2,
18 X_INSTANCE_PK4_VALUE in VARCHAR2,
19 X_INSTANCE_PK5_VALUE in VARCHAR2,
20 X_PROGRAM_NAME in VARCHAR2,
21 X_PROGRAM_TAG in VARCHAR2,
22 X_CREATION_DATE in DATE,
23 X_CREATED_BY in NUMBER,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATED_BY in NUMBER,
26 X_LAST_UPDATE_LOGIN in NUMBER,
27 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
28 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
29 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
30 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
31 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
32 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
33 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
34 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
35 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
36 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
37 X_CTX_SECGRP_ID in NUMBER default -1,
38 X_CTX_RESP_ID in NUMBER default -1,
39 X_CTX_RESP_APPL_ID in NUMBER default -1,
40 X_CTX_ORG_ID in NUMBER default -1,
41 X_NAME IN VARCHAR2 DEFAULT NULL,
42 X_DESCRIPTION IN VARCHAR2 DEFAULT NULL
43 );
44
45 procedure LOCK_ROW (
46 X_GRANT_GUID in RAW,
47 X_GRANTEE_TYPE in VARCHAR2,
48 X_GRANTEE_KEY in VARCHAR2,
49 X_MENU_ID in NUMBER,
50 X_START_DATE in DATE,
51 X_END_DATE in DATE,
52 X_OBJECT_ID in NUMBER,
53 X_INSTANCE_TYPE in VARCHAR2,
54 X_INSTANCE_SET_ID in NUMBER,
55 X_INSTANCE_PK1_VALUE in VARCHAR2,
56 X_INSTANCE_PK2_VALUE in VARCHAR2,
57 X_INSTANCE_PK3_VALUE in VARCHAR2,
58 X_INSTANCE_PK4_VALUE in VARCHAR2,
59 X_INSTANCE_PK5_VALUE in VARCHAR2,
60 X_PROGRAM_NAME in VARCHAR2,
61 X_PROGRAM_TAG in VARCHAR2,
62 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
63 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
64 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
65 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
66 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
67 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
68 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
69 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
70 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
71 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
72 X_CTX_SECGRP_ID in NUMBER default -1,
73 X_CTX_RESP_ID in NUMBER default -1,
74 X_CTX_RESP_APPL_ID in NUMBER default -1,
75 X_CTX_ORG_ID in NUMBER default -1,
76 X_NAME IN VARCHAR2 DEFAULT NULL,
77 X_DESCRIPTION IN VARCHAR2 DEFAULT NULL
78 );
79
80 procedure UPDATE_ROW (
81 X_GRANT_GUID in RAW,
82 X_GRANTEE_TYPE in VARCHAR2,
83 X_GRANTEE_KEY in VARCHAR2,
84 X_MENU_ID in NUMBER,
85 X_START_DATE in DATE,
86 X_END_DATE in DATE,
87 X_OBJECT_ID in NUMBER,
88 X_INSTANCE_TYPE in VARCHAR2,
89 X_INSTANCE_SET_ID in NUMBER,
90 X_INSTANCE_PK1_VALUE in VARCHAR2,
91 X_INSTANCE_PK2_VALUE in VARCHAR2,
92 X_INSTANCE_PK3_VALUE in VARCHAR2,
93 X_INSTANCE_PK4_VALUE in VARCHAR2,
94 X_INSTANCE_PK5_VALUE in VARCHAR2,
95 X_PROGRAM_NAME in VARCHAR2,
96 X_PROGRAM_TAG in VARCHAR2,
97 X_LAST_UPDATE_DATE in DATE,
98 X_LAST_UPDATED_BY in NUMBER,
99 X_LAST_UPDATE_LOGIN in NUMBER,
100 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
101 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
102 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
103 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
104 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
105 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
106 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
107 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
108 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
109 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
110 X_CTX_SECGRP_ID in NUMBER default -1,
111 X_CTX_RESP_ID in NUMBER default -1,
112 X_CTX_RESP_APPL_ID in NUMBER default -1,
113 X_CTX_ORG_ID in NUMBER default -1,
114 X_NAME IN VARCHAR2 DEFAULT '*NOTPASSED*', /* If you pass name, */
115 X_DESCRIPTION IN VARCHAR2 DEFAULT '*NOTPASSED*'/* must pass description*/
116
117 );
118
119 /* This version is obsolete. Use overloaded version below */
120 procedure LOAD_ROW (
121 X_GRANT_GUID in VARCHAR2,
122 X_GRANTEE_TYPE in VARCHAR2,
123 X_GRANTEE_KEY in VARCHAR2,
124 X_MENU_NAME in VARCHAR2,
125 X_START_DATE in VARCHAR2,
126 X_END_DATE in VARCHAR2,
127 X_OBJ_NAME in VARCHAR2,
128 X_INSTANCE_TYPE in VARCHAR2,
129 X_INSTANCE_SET_NAME in VARCHAR2,
130 X_INSTANCE_PK1_VALUE in VARCHAR2,
131 X_INSTANCE_PK2_VALUE in VARCHAR2,
132 X_INSTANCE_PK3_VALUE in VARCHAR2,
133 X_INSTANCE_PK4_VALUE in VARCHAR2,
134 X_INSTANCE_PK5_VALUE in VARCHAR2,
135 X_PROGRAM_NAME in VARCHAR2,
136 X_PROGRAM_TAG in VARCHAR2,
137 X_OWNER in VARCHAR2,
138 X_CUSTOM_MODE in VARCHAR2
139 );
140
141 procedure DELETE_ROW (
142 X_GRANT_GUID in RAW
143 );
144
145
146 PROCEDURE grant_function
147 (
148 p_api_version IN NUMBER,
149 p_menu_name IN VARCHAR2,
150 p_object_name IN VARCHAR2,
151 p_instance_type IN VARCHAR2,
152 p_instance_set_id IN NUMBER DEFAULT NULL,
153 p_instance_pk1_value IN VARCHAR2 DEFAULT NULL,
154 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
155 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
156 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
157 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
158 p_grantee_type IN VARCHAR2 DEFAULT 'USER',
159 p_grantee_key IN VARCHAR2,
160 p_start_date IN DATE,
161 p_end_date IN DATE,
162 p_program_name IN VARCHAR2 DEFAULT NULL,
163 p_program_tag IN VARCHAR2 DEFAULT NULL,
164 x_grant_guid OUT NOCOPY RAW,
165 x_success OUT NOCOPY VARCHAR, /* Boolean */
166 x_errorcode OUT NOCOPY NUMBER,
167 p_parameter1 IN VARCHAR2 DEFAULT NULL,
168 p_parameter2 IN VARCHAR2 DEFAULT NULL,
169 p_parameter3 IN VARCHAR2 DEFAULT NULL,
170 p_parameter4 IN VARCHAR2 DEFAULT NULL,
171 p_parameter5 IN VARCHAR2 DEFAULT NULL,
172 p_parameter6 IN VARCHAR2 DEFAULT NULL,
173 p_parameter7 IN VARCHAR2 DEFAULT NULL,
174 p_parameter8 IN VARCHAR2 DEFAULT NULL,
175 p_parameter9 IN VARCHAR2 DEFAULT NULL,
176 p_parameter10 IN VARCHAR2 DEFAULT NULL,
177 p_ctx_secgrp_id IN NUMBER default -1,
178 p_ctx_resp_id IN NUMBER default -1,
179 p_ctx_resp_appl_id IN NUMBER default -1,
180 p_ctx_org_id IN NUMBER default -1,
181 p_name IN VARCHAR2 DEFAULT NULL,
182 p_description IN VARCHAR2 DEFAULT NULL
183
184 );
185 -- Start OF comments
186 -- API name : Grant
187 -- TYPE : Public
188 -- Pre-reqs : None
189 -- FUNCTION : Grant a Menu of functions on object instances to a Party.
190 -- If this operation fails then the grant is not
191 -- done and error code is returned. Nothing ever commits.
192 -- Note: the name of this routine should probably be
193 -- grant_menu() but it is left as is for legacy reasons.
194 --
195 -- For a more thorough discussion of the grants table and what the
196 -- various columns mean, see the internal oracle document
197 -- http://www-apps.us.oracle.com/atg/plans/r115x/datasec.txt
198 --
199 -- Parameters:
200 -- IN : p_api_version IN NUMBER (required)
201 -- API Version of this procedure (currently 1.0)
202 --
203 -- p_menu_name IN VARCHAR2 (required)
204 -- menu to be granted
205 --
206 -- p_object_name IN VARCHAR2 (required)
207 -- object on which the menu is granted
208 -- from fnd_objects table.
209 --
210 -- p_instance_type IN VARCHAR2 (required)
211 -- instance type in grants table-
212 -- 'INSTANCE' or 'SET'
213 --
214 -- p_instance_set_id IN VARCHAR2 (optional)
215 -- This must be filled in if p_instance_type='SET'.
216 -- NULL should be passed if p_instance_type='INSTANCE'.
217 -- This is a FK to the FND_OBJECT_INSTANCE_SETS table
218 -- and indicates which instance set is granted.
219 --
220 -- p_instance_pk[1..5]_value IN NUMBER (optional)
221 -- Must be filled in if p_instance_type = 'INSTANCE'
222 -- NULL should be passed if p_instance_type='SET'.
223 -- These are the primary keys of the object instance
224 -- granted. The order of the PKs must match the order
225 -- they were defined in FND_OBJECTS. NULLs should
226 -- be passed for the higher numbered args with no PKs.
227 --
228 -- p_grantee_type IN VARCHAR2 (optional)
229 -- grantee type for which the menu is granted.
230 -- 'USER', 'GROUP', 'GLOBAL'
231 --
232 -- p_grantee_key IN VARCHAR2 (required)
233 -- User or group that gets the grant,
234 -- from FND_USER or another table that the view
235 -- WF_ROLES is based on.
236 --
237 -- p_program_name IN VARCHAR2 (optional)
238 -- name of the program that handles grant. This is used
239 -- So that each product that has UIs over grants will
240 -- know which grants it is responsible for.
241 --
242 -- p_program_tag IN VARCHAR2 (optional)
243 -- tag used by the program that handles grant. This can
244 -- be used at the discretion of the program that creates
245 -- the grant.
246 --
247 -- p_parameter[1-10] IN NUMBER (optional)
248 -- Should be filled in if p_instance_type = 'SET'
249 -- and the predicate of the instance set pointed to by
250 -- p_instance_set_name references the parameter as
251 -- G.PARAMETER[1-10].
252 -- NULL should be passed otherwise.
253 -- These are parameters for the instance set. For
254 -- example you might have an instance set
255 -- "Profiles by Category" whose predicate is
256 -- "X.CATEGORY = G.PARAMETER1". Then if you put "ADMIN"
257 -- in to P_PARAMETER1, the instance set would cover
258 -- profiles whose CATEGORY is "ADMIN".
259 --
260 -- p_ctx_secgrp_id IN NUMBER (optional)
261 -- if grant applies to all security groups, pass -1
262 -- if grant applies to only a particular secgrp, pass id.
263 --
264 -- p_ctx_resp_id IN NUMBER (optional)
265 -- if grant applies to all responsibilities, pass -1
266 -- if grant applies to only a particular resp, pass id.
267 --
268 -- p_ctx_resp_appl_id IN NUMBER (optional)
269 -- if grant applies to all responsibilities, pass -1
270 -- if grant applies to only a particular resp, pass
271 -- application id of that resp.
272 --
273 -- p_ctx_org_id IN NUMBER (optional)
274 -- if grant applies to all organizations, pass -1
275 -- if grant applies to only a particular org, pass org id.
276 --
277 -- p_name in varchar2 (optional)
278 -- User-friendly name of grant. To be used in UIs.
279 --
280 -- p_description in varchar2 (optional)
281 -- User-friendly description of grant. To be used in UIs.
282 --
283 -- OUT : x_grant_guid OUT RAW
284 -- returns the guid of the grant that was created (or found)
285 --
286 -- X_success OUT VARCHAR(1)
287 -- 'T' if everything worked correctly
288 -- 'F' if there was a failure. If 'F'
289 -- is returned, there will either be an error
290 -- message on the FND_MESSAGE stack which
291 -- can be retrieved with FND_MESSAGE.GET_ENCODED(),
292 -- or there will be a PL/SQL exception raised.
293 --
294 -- X_ErrorCode OUT NUMBER
295 -- RETURN value OF the errorcode
296 -- check only if x_success = F.
297 -- Positive error codes are "expected"
298 -- Negative error codes are "unexpected".
299 --
300 --
301 -- Version: Current Version 1.0
302 -- Previous Version : None
303 -- Notes :
304 --
305 -- END OF comments
306 ---------------------------------------------------------------
307
308 ---------------------------------------------------------------
309 PROCEDURE revoke_grant
310 (
311 p_api_version IN NUMBER,
312 p_grant_guid IN raw,
313 x_success OUT NOCOPY VARCHAR2, /* Boolean */
314 x_errorcode OUT NOCOPY NUMBER
315 );
316
317 -- Start OF comments
318 -- API name : Revoke_Grant
319 -- TYPE : Public
320 -- Pre-reqs : None
321 -- FUNCTION : Revoke a Party's function on object instances.
322 -- If this operation fails then the revoke is not
323 -- done and error code is returned.
324 --
325 -- Parameters:
326 -- IN : p_api_version IN NUMBER (required)
327 -- API Version of this procedure (currently 1.0)
328 --
329 -- p_grant_guid IN RAW
330 --
331 --
332 -- OUT :
333 -- X_success OUT VARCHAR(1)
334 -- 'T' if everything worked correctly
335 -- 'F' if there was a failure. If FALSE
336 -- is returned, there will either be an error
337 -- message on the FND_MESSAGE stack which
338 -- can be retrieved with FND_MESSAGE.GET_ENCODED(),
339 -- or there will be a PL/SQL exception raised.
340 --
341 -- X_ErrorCode OUT NUMBER
342 -- RETURN value OF the errorcode
343 -- check only if x_success = F.
344 -- Positive error codes are "expected"
345 -- Negative error codes are "unexpected".
346 --
347 --
348 -- Version: Current Version 1.0
349 -- Previous Version : None
350 -- Notes :
351 --
352 -- END OF comments
353
354 ----------------------------------------------------------------
355
356
360 (
357 /* This version of update_grant is obsoleted. */
358 /* Please use overloaded update_grant below which takes name and desc.*/
359 PROCEDURE update_grant /* ***OBSOLETED for backward compatibility only */
361 p_api_version IN NUMBER,
362 p_grant_guid IN raw,
363 p_start_date IN DATE,
364 p_end_date IN DATE,
365 x_success OUT NOCOPY VARCHAR2
366 );
367
368
369 /* This is the overloaded version of update_grant that should be used in */
370 /* new code. */
371 PROCEDURE update_grant
372 (
373 p_api_version IN NUMBER,
374 p_grant_guid IN raw,
375 p_start_date IN DATE,
376 p_end_date IN DATE,
377 p_name IN VARCHAR2,
378 p_description IN VARCHAR2,
379 x_success OUT NOCOPY VARCHAR2
380 );
381 -- Start OF comments
382 -- API name : UPDATE_GRANT
383 -- TYPE : Public
384 -- Pre-reqs : None
385 -- FUNCTION :
386 --
387 -- Parameters:
388 -- IN : p_api_version IN NUMBER (required)
389 -- API Version of this procedure (currently 1.0)
390 --
391 -- p_grant_guid IN RAW (required)
392 -- grant guid
393 --
394 -- p_start_date IN DATE (required)
395 -- start date for the grant identified by grant id.
396 --
397 -- p_end_date IN DATE (required)
398 -- end date for the grant identified by grant id.
399 --
400 -- p_name in varchar2 (optional)
401 -- User-friendly name of grant. To be used in UIs.
402 --
403 -- p_description in varchar2 (optional)
404 -- User-friendly description of grant. To be used in UIs.
405 --
406 -- OUT :
407 -- X_success OUT VARCHAR(1)
408 -- 'T' if everything worked correctly
409 -- 'F' if there was a failure. If FALSE
410 -- is returned, there will either be an error
411 -- message on the FND_MESSAGE stack which
412 -- can be retrieved with FND_MESSAGE.GET_ENCODED()
413 -- or there will be a PL/SQL exception raised.
414 --
415 --
416 -- Version: Current Version 1.0
417 -- Previous Version : None
418 -- Notes :
419 --
420 -- END OF comments
421
422 ------------------------------------------------------------------
423 PROCEDURE lock_grant
424 (
425 p_grant_guid IN raw,
426 p_menu_id IN NUMBER,
427 p_object_id IN number,
428 p_instance_type IN varchar2,
429 p_instance_set_id in number,
430 p_instance_pk1_value IN VARCHAR2,
431 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
432 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
433 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
434 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
435 p_grantee_type in varchar2 default 'USER',
436 p_grantee_key IN varchar2,
437 p_start_date IN DATE,
438 p_end_date IN DATE,
439 p_program_name IN VARCHAR2,
440 p_program_tag IN VARCHAR2,
441 p_parameter1 IN VARCHAR2 DEFAULT NULL,
442 p_parameter2 IN VARCHAR2 DEFAULT NULL,
443 p_parameter3 IN VARCHAR2 DEFAULT NULL,
444 p_parameter4 IN VARCHAR2 DEFAULT NULL,
445 p_parameter5 IN VARCHAR2 DEFAULT NULL,
446 p_parameter6 IN VARCHAR2 DEFAULT NULL,
447 p_parameter7 IN VARCHAR2 DEFAULT NULL,
448 p_parameter8 IN VARCHAR2 DEFAULT NULL,
449 p_parameter9 IN VARCHAR2 DEFAULT NULL,
450 p_parameter10 IN VARCHAR2 DEFAULT NULL,
451 p_ctx_secgrp_id IN NUMBER default -1,
452 p_ctx_resp_id IN NUMBER default -1,
453 p_ctx_resp_appl_id IN NUMBER default -1,
454 p_ctx_org_id IN NUMBER default -1,
455 p_name IN VARCHAR2 default NULL,
456 p_description IN VARCHAR2 default NULL
457 ) ;
458 -- Start OF comments
459 -- API name : LOCK_GRANT
460 -- TYPE : Public
461 -- Pre-reqs : None
462 -- FUNCTION :
463 --
464 -- Parameters:
465 -- IN : p_api_version IN NUMBER (required)
466 -- API Version of this procedure (currently 1.0)
467 --
468 -- p_grant_guid IN RAW (required)
469 -- grant guid
470 -- p_menu_id IN NUMBER (required)
471 -- menu id
472 -- p_object_id IN NUMBER(required)
473 -- object id
474 -- p_instance_type IN VARCHAR2 (required)
475 -- instance type in grants table- 'INSTANCE' or 'SET'
476 -- p_instance_set_id IN VARCHAR2 (required)
477 -- instance set if instance_type='SET'
478 -- p_instance_pk[1..5]_value IN VARCHAR(required)
479 -- key columns of grant to lock, if instance_type='INSTANCE'
480 -- p_grantee_type IN VARCHAR2 (optional)
481 -- grantee type: 'GROUP', 'USER', 'GLOBAL'
482 -- p_grantee_key IN NUMBER (required)
483 -- grantee key (FK to wf_roles.name).
484 -- p_start_date IN DATE (required)
485 -- start date for the grant
486 -- p_end_date IN DATE (required)
490 -- p_program_tag IN VARCHAR2 (required)
487 -- end date for the grant
488 -- p_program_name IN VARCHAR2 (required)
489 -- name of the program that handles grant
491 -- tag used by the program that handles grant
492 -- p_ctx_... columns IN VARCHAR2 (optional)
493 -- see ctx column descriptions in GRANT_FUNCTION.
494 -- p_name IN VARCHAR2 (optional)
495 -- user friendly name
496 -- p_description IN VARCHAR2 (optional)
497 -- user friendly description
498 ------------------------------------------------------------------
499
500 -- fill_in_orig_columns
501 -- This routine is mostly for AOL internal use by this loader itself;
502 -- it fills in the columns grantee_orig_system and
503 -- grantee_orig_system_id from the grantee_key.
504 procedure fill_in_orig_columns(p_grant_guid IN raw);
505
506 -- fill_in_missing_orig_columns
507 -- This routine is mostly for AOL internal use by this loader itself;
508 -- it fills in the columns grantee_orig_system and
509 -- grantee_orig_system_id from the grantee_key for all grants that
510 -- are missing them.
511 procedure fill_in_missing_orig_columns;
512
513 /* This is the newest version. Use instead of overloaded version above */
514 procedure LOAD_ROW (
515 X_GRANT_GUID in VARCHAR2, /* For new guid: select sys_guid() from dual */
516 X_GRANTEE_TYPE in VARCHAR2,
517 X_GRANTEE_KEY in VARCHAR2,
518 X_MENU_NAME in VARCHAR2,
519 X_START_DATE in VARCHAR2,
520 X_END_DATE in VARCHAR2,
521 X_OBJ_NAME in VARCHAR2,
522 X_INSTANCE_TYPE in VARCHAR2,
523 X_INSTANCE_SET_NAME in VARCHAR2,
524 X_INSTANCE_PK1_VALUE in VARCHAR2,
525 X_INSTANCE_PK2_VALUE in VARCHAR2,
526 X_INSTANCE_PK3_VALUE in VARCHAR2,
527 X_INSTANCE_PK4_VALUE in VARCHAR2,
528 X_INSTANCE_PK5_VALUE in VARCHAR2,
529 X_PROGRAM_NAME in VARCHAR2,
530 X_PROGRAM_TAG in VARCHAR2,
531 X_OWNER in VARCHAR2,
532 X_CUSTOM_MODE in VARCHAR2,
533 X_LAST_UPDATE_DATE in VARCHAR2,
534 X_PARAMETER1 IN VARCHAR2 DEFAULT NULL,
535 X_PARAMETER2 IN VARCHAR2 DEFAULT NULL,
536 X_PARAMETER3 IN VARCHAR2 DEFAULT NULL,
537 X_PARAMETER4 IN VARCHAR2 DEFAULT NULL,
538 X_PARAMETER5 IN VARCHAR2 DEFAULT NULL,
539 X_PARAMETER6 IN VARCHAR2 DEFAULT NULL,
540 X_PARAMETER7 IN VARCHAR2 DEFAULT NULL,
541 X_PARAMETER8 IN VARCHAR2 DEFAULT NULL,
542 X_PARAMETER9 IN VARCHAR2 DEFAULT NULL,
543 X_PARAMETER10 IN VARCHAR2 DEFAULT NULL,
544 X_CTX_SECURITY_GROUP_KEY in VARCHAR2 default '*GLOBAL*',
545 X_CTX_RESP_KEY in VARCHAR2 default '*GLOBAL*',
546 X_CTX_RESP_APP_SHORT_NAME in VARCHAR2 default '*GLOBAL*',
547 X_CTX_ORGANIZATION in VARCHAR2 default '*GLOBAL*',
548 X_NAME IN VARCHAR2 DEFAULT '*NOTPASSED*',
549 X_DESCRIPTION IN VARCHAR2 DEFAULT '*NOTPASSED*'
550 );
551
552 PROCEDURE delete_grant(
553 p_grantee_type IN VARCHAR2 DEFAULT NULL,
554 p_grantee_key IN VARCHAR2 DEFAULT NULL,
555 p_object_name IN VARCHAR2 DEFAULT NULL,
556 p_instance_type IN VARCHAR2 DEFAULT NULL,
557 p_instance_set_id IN NUMBER DEFAULT NULL,
558 p_instance_pk1_value IN VARCHAR2 DEFAULT NULL,
559 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
560 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
561 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
562 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
563 p_menu_name IN VARCHAR2 DEFAULT NULL,
564 p_program_name IN VARCHAR2 DEFAULT NULL,
565 p_program_tag IN VARCHAR2 DEFAULT NULL,
566 x_success OUT NOCOPY VARCHAR,
567 x_errcode OUT NOCOPY NUMBER);
568 -- Start of comments:
569 -- API Name : Delete_Grant
570 -- Type: Public
571 -- Pre-reqs: None
572 -- Function: Delete the grants on object instances based on the parameters
573 -- passed in.
574 -- Parameters:
575 -- Passing NULL to any parameter means "all":
576 -- "don't consider this value when deciding which rows to delete"
577 -- Passing a specific value means you want to delete only rows
578 -- with that specific value.
579 -- Passing '*NULL*' means you want to delete rows where the value
580 -- is actually NULL.
581 --
582 -- If the caller passes NULL for all values, to delete all
583 -- the records in FND_GRANTS, this procedure will raise an exception,
584 -- not delete anything, and pass out a failure status.
585 --
586 -- p_grantee_type IN VARCHAR2 (optional)
587 -- The grantee type, either 'USER', 'GROUP', or 'GLOBAL'
588 -- See above for meaning of NULL and '*NULL*'
589 --
590 -- p_grantee_key IN VARCHAR2 (optional)
591 -- User or group that is granted to.
592 -- Pass NULL (meaning all) if grantee_key is 'GLOBAL'
593 -- See above for meaning of NULL and '*NULL*'
594 -- This is required (NULL not allowed except for GLOBAL)
595 -- if p_grantee_type is passed.
596 --
600 -- Required (not NULL or '*NULL*') if p_instance_type is passed.
597 -- p_object_name IN VARCHAR2 (optional)
598 -- Object of the grant.
599 -- See above for meaning of NULL and '*NULL*'
601 --
602 -- p_instance_type IN VARCHAR2 (optional)
603 -- Instance type, either 'INSTANCE' or 'SET'
604 -- See above for meaning of NULL and '*NULL*'
605 --
606 -- p_instance_set_id IN NUMBER (optional)
607 -- A FK to FND_OBJECT_INSTANCE_SETS which indicates the instance
608 -- set to be deleted.
609 -- This value is required (NULL or '*NULL*' not allowed)
610 -- if p_instance_type is 'SET'.
611 -- See above for meaning of NULL and '*NULL*'
612 --
616 -- See above for meaning of NULL and '*NULL*'
613 -- p_instance_pk[1...5]_value IN VARCHAR2 (optional)
614 -- If p_instance_type is 'INSTANCE' then
615 -- these values indicate the instance PK values to match.
617 --
618 -- p_menu_name IN VARCHAR2 (optional)
619 -- Menu to be deleted.
620 -- See above for meaning of NULL and '*NULL*'
621 --
622 -- p_program_name IN VARCHAR2 (optional)
623 -- Name of the program.
624 -- See above for meaning of NULL and '*NULL*'
625 --
626 -- p_program_tag IN VARCHAR2 (optional)
627 -- Tag used by the program that processes the grant.
628 -- See above for meaning of NULL and '*NULL*'
629 --
630 -- x_success OUT VARCHAR (required)
631 -- Returns 'T' if successful, else 'F'.
632 -- If FALSE
633 -- is returned, there will either be an error
634 -- message on the FND_MESSAGE stack which
635 -- can be retrieved with FND_MESSAGE.GET_ENCODED(),
636 -- or there will be a PL/SQL exception raised.
637 --
638 -- X_ErrorCode OUT NUMBER
639 -- check only if x_success = F.
640 -- Positive error codes are "expected"
641 -- Negative error codes are "unexpected".
642 --
643 -- x_errcode OUT VARCHAR (required)
644 --
645 -- Examples:
646 --
647 -- 1. Delete all grants for a specific responsibility.
648 --
649 -- FND_GRANTS_DELETE_PKG.delete_grant(
650 -- p_grantee_type => 'GROUP',
651 -- p_grantee_key => 'FND_RESP101:50234',
652 -- x_success => l_success,
653 -- x_errcode => l_errcode
654 -- );
655 --
656 -- 2. Delete all grants for a specific responsibility and menu function for
657 -- all object instances having only 2 primary key values, one value being
658 -- '1' and the other NULL.
659 --
660 -- FND_GRANTS_DELETE_PKG.delete_grant(
661 -- p_grantee_type => 'GROUP',
662 -- p_grantee_key => 'FND_RESP101:50234',
663 -- p_object_name => 'FSG_ROW_SET',
664 -- p_instance_type => 'INSTANCE',
665 -- p_instance_pk1_value => '1',
666 -- p_instance_pk2_value => '*NULL*',
667 -- p_menu_name => 'FSG_ROW_SET_V',
668 -- p_program_name => NULL,
669 -- p_program_tag => NULL,
670 -- x_success => l_success,
671 -- x_errcode => l_errcode
672 -- );
673 --
674 --
675 -- This routine is created and maintained by GL.
676 -- Created 07/09/02 by [email protected].
677 -- GL Contacts: [email protected], [email protected]
678 -- Reviewed by Tom Morrow.
679 --
680 --
681
682 /* CONVERT_NULLS- For install time use only, not a */
683 /* runtime routine. This routine will convert NULL to '*NULL*' in the */
684 /* columns INSTANCE_PKX_VALUE in the table FND_GRANTS. */
685 /* The reason for this routine is that we decided to have those columns be */
686 /* non-NULL in order to speed up queries that go against different numbers */
687 /* of pk columns. This should be run once at patch application time and */
688 /* should never need to be run again. This will be included in the ATG */
689 /* data security patch. */
690 /* Returns the number of rows converted. */
691 function CONVERT_NULLS return NUMBER;
692
693
694 end FND_GRANTS_PKG;