[Home] [Help]
PACKAGE BODY: APPS.IBC_DATA_SECURITY_PVT
Source
1 PACKAGE BODY ibc_data_security_pvt AS
2 /* $Header: ibcdsecb.pls 120.2 2006/02/21 15:05:10 sharma noship $ */
3 /*#
4 * This is the private API for OCM Data Security. These methods are
5 * exposed as Java APIs in DataSecurityManager.class
6 * @rep:scope private
7 * @rep:product IBC
8 * @rep:displayname Oracle Content Manager Data Security Private API
9 * @rep:category BUSINESS_ENTITY IBC_DATA_SECURITY
10 */
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBC_DATA_SECURITY_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcdsecb.pls';
14
15 TYPE t_user_id_tbl IS TABLE OF NUMBER
16 INDEX BY BINARY_INTEGER;
17
18 -- Cursor to fetch FND object info definition
19 CURSOR c_object(p_object_id IN NUMBER) IS
20 SELECT object_id,
21 obj_name,
22 database_object_name,
23 pk1_column_name,
24 pk2_column_name,
25 pk3_column_name,
26 pk4_column_name,
27 pk5_column_name,
28 pk1_column_type,
29 pk2_column_type,
30 pk3_column_type,
31 pk4_column_type,
32 pk5_column_type
33 FROM fnd_objects
34 WHERE object_id = p_object_id;
35
36 -- ------------------------------------------------------------
37 -- Internal Function to be used when building a SQL statement
38 -- dynamically.
39 -- ------------------------------------------------------------
40 FUNCTION convert_to_name(p_name IN VARCHAR2,
41 p_type IN VARCHAR2)
42 RETURN VARCHAR2
43 AS
44 BEGIN
45 IF p_type = 'NUMBER' THEN
46 RETURN 'FND_NUMBER.CANONICAL_TO_NUMBER(' || p_name || ')';
47 ELSIF p_type = 'DATE' THEN
48 RETURN 'FND_DATE.CANONICAL_TO_DATE(' || p_name || ')';
49 ELSE
50 RETURN p_name;
51 END IF;
52 END convert_to_name;
53
54 -- ------------------------------------------------------------
55 -- Internal Function to be used when building a SQL statement
56 -- dynamically.
57 -- ------------------------------------------------------------
58 FUNCTION convert_to_value(p_value IN VARCHAR2,
59 p_type IN VARCHAR2)
60 RETURN VARCHAR2
61 AS
62 BEGIN
63 IF p_type = 'NUMBER' THEN
64 RETURN 'FND_NUMBER.CANONICAL_TO_NUMBER(''' || p_value || ''')';
65 ELSIF p_type = 'DATE' THEN
66 RETURN 'FND_DATE.CANONICAL_TO_DATE(''' || p_value || ''')';
67 ELSE
68 RETURN '''' || p_value || '''';
69 END IF;
70 END convert_to_value;
71
72 -- ------------------------------------------------------------
73 -- Internal Function to be used when building a SQL statement
74 -- dynamically.
75 -- ------------------------------------------------------------
76 FUNCTION convert_from_name(p_name IN VARCHAR2,
77 p_type IN VARCHAR2)
78 RETURN VARCHAR2
79 AS
80 BEGIN
81 IF p_type = 'NUMBER' THEN
82 RETURN 'FND_NUMBER.NUMBER_TO_CANONICAL(' || p_name || ')';
83 ELSIF p_type = 'DATE' THEN
84 RETURN 'FND_DATE.DATE_TO_CANONICAL(' || p_name || ')';
85 ELSE
86 RETURN p_name;
87 END IF;
88 END convert_from_name;
89
90 -- ------------------------------------------------------------
91 -- Internal Function to be used when building a SQL statement
92 -- dynamically.
93 -- ------------------------------------------------------------
94 FUNCTION convert_from_value(p_value IN VARCHAR2,
95 p_type IN VARCHAR2)
96 RETURN VARCHAR2
97 AS
98 BEGIN
99 IF p_type = 'NUMBER' THEN
100 RETURN 'FND_NUMBER.NUMBER_TO_CANONICAL(''' || p_value || ''')';
101 ELSIF p_type = 'DATE' THEN
102 RETURN 'FND_DATE.DATE_TO_CANONICAL(''' || p_value || ''')';
103 ELSE
104 RETURN '''' || p_value || '''';
105 END IF;
106 END convert_from_value;
107
108 -- ------------------------------------------------------------
109 -- Internal Function to do conversions to canonical
110 -- ------------------------------------------------------------
111 FUNCTION canonical_from_value(p_value IN VARCHAR2,
112 p_type IN VARCHAR2)
113 RETURN VARCHAR2
114 AS
115 BEGIN
116 IF p_type = 'NUMBER' THEN
117 RETURN FND_NUMBER.NUMBER_TO_CANONICAL(TO_NUMBER(p_value));
118 ELSIF p_type = 'DATE' THEN
119 RETURN FND_DATE.DATE_TO_CANONICAL(TO_DATE(p_value, 'YYYYMMDD HH:MI:SS'));
120 ELSE
121 RETURN p_value;
122 END IF;
123 END canonical_from_value;
124
125 -- ----------------------------------------------------
126 -- FUNCTION: get_object_definition
127 -- DESCRIPTION: Given an object id it returns object
128 -- definition information.
129 -- ----------------------------------------------------
130 PROCEDURE get_object_definition(p_object_id IN NUMBER,
131 x_nbr_pk_cols OUT NOCOPY NUMBER,
132 x_fmt_col_lst OUT NOCOPY VARCHAR2,
133 x_object_def OUT NOCOPY c_object%ROWTYPE) AS
134 BEGIN
135 FOR r_object IN c_object(p_object_id) LOOP
136 x_nbr_pk_cols := 1;
137 IF r_object.pk2_column_name IS NOT NULL THEN
138 IF r_object.pk3_column_name IS NULL THEN
139 x_nbr_pk_cols := 2;
140 ELSIF r_object.pk4_column_name IS NULL THEN
141 x_nbr_pk_cols := 3;
142 ELSIF r_object.pk5_column_name IS NULL THEN
143 x_nbr_pk_cols := 4;
144 ELSE
145 x_nbr_pk_cols := 5;
146 END IF;
147 END IF;
148 x_object_def := r_object;
149 x_fmt_col_lst := r_object.pk1_column_name;
150 IF r_object.pk2_column_name IS NOT NULL THEN
151 x_fmt_col_lst := x_fmt_col_lst || ', ' || r_object.pk2_column_name;
152 IF r_object.pk3_column_name IS NOT NULL THEN
153 x_fmt_col_lst := x_fmt_col_lst || ', ' || r_object.pk3_column_name;
154 IF r_object.pk4_column_name IS NOT NULL THEN
155 x_fmt_col_lst := x_fmt_col_lst || ', ' || r_object.pk4_column_name;
156 IF r_object.pk5_column_name IS NOT NULL THEN
157 x_fmt_col_lst := x_fmt_col_lst || ', ' || r_object.pk5_column_name;
158 END IF;
159 END IF;
160 END IF;
161 END IF;
162 END LOOP;
163 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
164 EXCEPTION
165 WHEN OTHERS THEN
166 RAISE;
167 END get_object_definition;
168
169 -- ----------------------------------------------------
170 -- FUNCTION: get_object_grant_group_info
171 -- DESCRIPTION: Given instance object id and primary
172 -- key returns object's grant group information
173 -- ----------------------------------------------------
174 PROCEDURE get_object_grant_group_info(
175 p_instance_object_id IN NUMBER
176 ,p_instance_pk1_value IN VARCHAR2
177 ,p_instance_pk2_value IN VARCHAR2
178 ,p_instance_pk3_value IN VARCHAR2
179 ,p_instance_pk4_value IN VARCHAR2
180 ,p_instance_pk5_value IN VARCHAR2
181 ,x_rowid OUT NOCOPY ROWID
182 ,x_object_grant_group_id OUT NOCOPY NUMBER
183 ,x_grant_group_id OUT NOCOPY NUMBER
184 ,x_inherited_flag OUT NOCOPY VARCHAR2
185 ,x_inherited_from OUT NOCOPY VARCHAR2
186 ,x_inheritance_type OUT NOCOPY VARCHAR2
187 ) AS
188 TYPE cursorType IS REF CURSOR;
189 c_cursor cursorType;
190 l_statement VARCHAR2(4096);
191 -- Object Definition
192 l_nbr_pk_cols NUMBER;
193 l_fmt_col_lst VARCHAR2(4096);
194 l_object_definition c_object%ROWTYPE;
195 BEGIN
196 IF p_instance_object_id IS NULL OR
197 p_instance_pk1_value IS NULL
198 THEN
199 RETURN;
200 END IF;
201 -- Get Object Definition
202 Get_Object_Definition(
203 p_object_id => p_instance_object_id
204 ,x_nbr_pk_cols => l_nbr_pk_cols
205 ,x_fmt_col_lst => l_fmt_col_lst
206 ,x_object_def => l_object_definition
207 );
208 -- Prepare SQL statement to get Grant Bundle Id
209 l_statement := 'SELECT rowid, object_grant_group_id, grant_group_id, ' ||
210 ' inherited_flag, inherited_from, inheritance_type ' ||
211 ' FROM ibc_object_grant_groups ' ||
212 ' WHERE object_id = :p_instance_object_id ' ||
213 ' AND instance_pk1_value = :p_instance_pk1_value ';
214 IF l_nbr_pk_cols > 1 THEN
215 l_statement := l_statement ||
216 ' AND instance_pk2_value = :p_instance_pk2_value ';
217 IF l_nbr_pk_cols > 2 THEN
218 l_statement := l_statement ||
219 ' AND instance_pk3_value = :p_instance_pk3_value ';
220 IF l_nbr_pk_cols > 3 THEN
221 l_statement := l_statement ||
222 ' AND instance_pk4_value = :p_instance_pk4_value ';
223 IF l_nbr_pk_cols > 4 THEN
224 l_statement := l_statement ||
225 ' AND instance_pk5_value = :p_instance_pk5_value ';
226 END IF;
227 END IF;
228 END IF;
229 END IF;
230 FOR I IN (l_nbr_pk_cols + 1)..5 LOOP
231 l_statement := l_statement ||
232 ' AND instance_pk' || I || '_VALUE IS NULL ';
233 END LOOP;
234
235 IF l_nbr_pk_cols = 5 THEN
236 OPEN c_cursor FOR l_statement
237 USING p_instance_object_id,
238 canonical_from_value(p_instance_pk1_value, l_object_definition.pk1_column_type),
239 canonical_from_value(p_instance_pk2_value, l_object_definition.pk2_column_type),
240 canonical_from_value(p_instance_pk3_value, l_object_definition.pk3_column_type),
241 canonical_from_value(p_instance_pk4_value, l_object_definition.pk4_column_type),
242 canonical_from_value(p_instance_pk5_value, l_object_definition.pk5_column_type);
243 ELSIF l_nbr_pk_cols = 4 THEN
244 OPEN c_cursor FOR l_statement
245 USING p_instance_object_id,
246 canonical_from_value(p_instance_pk1_value, l_object_definition.pk1_column_type),
247 canonical_from_value(p_instance_pk2_value, l_object_definition.pk2_column_type),
248 canonical_from_value(p_instance_pk3_value, l_object_definition.pk3_column_type),
249 canonical_from_value(p_instance_pk4_value, l_object_definition.pk4_column_type);
250 ELSIF l_nbr_pk_cols = 3 THEN
251 OPEN c_cursor FOR l_statement
252 USING p_instance_object_id,
253 canonical_from_value(p_instance_pk1_value, l_object_definition.pk1_column_type),
254 canonical_from_value(p_instance_pk2_value, l_object_definition.pk2_column_type),
255 canonical_from_value(p_instance_pk3_value, l_object_definition.pk3_column_type);
256 ELSIF l_nbr_pk_cols = 2 THEN
257 OPEN c_cursor FOR l_statement
258 USING p_instance_object_id,
259 canonical_from_value(p_instance_pk1_value, l_object_definition.pk1_column_type),
260 canonical_from_value(p_instance_pk2_value, l_object_definition.pk2_column_type);
261 ELSE
262 OPEN c_cursor FOR l_statement
263 USING p_instance_object_id,
264 canonical_from_value(p_instance_pk1_value, l_object_definition.pk1_column_type);
265 END IF;
266
267 -- Fetching Info from IBC_object_grant_groups
268 FETCH c_cursor INTO x_rowid, x_object_grant_group_id, x_grant_group_id,
269 x_inherited_flag, x_inherited_from, x_inheritance_type;
270
271 IF c_cursor%NOTFOUND THEN
272 x_object_grant_group_id := NULL;
273 x_grant_group_id := NULL;
274 x_inherited_flag := NULL;
275 x_inherited_from := NULL;
276 x_inheritance_type := NULL;
277 END IF;
278
279 CLOSE c_cursor;
280
281 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
282 EXCEPTION
283 WHEN OTHERS THEN
284 RAISE;
285
286 END get_object_grant_group_info;
287
288 /*#
289 * Given the object name it returns corrsponding object id
290 * from FND_OBJECTS
291 *
292 * @param p_object_name Object Name in FND_OBJECTS
293 * @return Object Id
294 *
295 * @rep:displayname get_object_id
296 *
297 */
298 FUNCTION get_object_id(
299 p_object_name IN VARCHAR2
300 ) RETURN NUMBER AS
301 l_result NUMBER;
302 CURSOR c_object_id(p_object_name VARCHAR2) IS
303 SELECT object_id
304 FROM fnd_objects
305 WHERE obj_name = p_object_name;
306 BEGIN
307 OPEN c_object_id(p_object_name);
308 FETCH c_object_id INTO l_result;
309 CLOSE c_object_id;
310 RETURN l_result;
311 END get_object_id;
312
313 /*#
314 * Given an object id it returns the lookup type used
315 * to validate especific permissions for the object
316 * instances corresponding to such object id.
317 *
318 * @param p_object_id Object Id
319 * @return permission's lookup type
320 *
321 * @rep:displayname get_perms_lookup_type
322 *
323 */
324 FUNCTION get_perms_lookup_type(
325 p_object_id IN NUMBER
326 ) RETURN VARCHAR2 AS
327 l_result VARCHAR2(30);
328 CURSOR c_lookup_type(p_object_id NUMBER) IS
329 SELECT permissions_lookup_type
330 FROM ibc_object_permissions
331 WHERE object_id = p_object_id;
332 BEGIN
333 OPEN c_lookup_type(p_object_id);
334 FETCH c_lookup_type INTO l_result;
335 CLOSE c_lookup_type;
336 RETURN l_result;
337 END get_perms_lookup_type;
338
339 /*#
340 * It sets inheritance type of an instance already existing in data
341 * security inheritance tree.
342 *
343 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
344 * for this particular instance
345 * @param p_instance_pk1_value value 1 for instance's primary key
346 * @param p_instance_pk2_value value 2 for instance's primary key
347 * @param p_instance_pk3_value value 3 for instance's primary key
348 * @param p_instance_pk4_value value 4 for instance's primary key
349 * @param p_instance_pk5_value value 5 for instance's primary key
350 * @param p_inheritance_type type of inheritance (FOLDER, HIDDEN-FOLDER,
351 * WORKSPACE and WSFOLDER). Currently supported
352 * in OCM only FOLDER and HIDDEN-FOLDER.
353 * @param p_commit Indicates whether to commit or not at the end
354 * of procedure
355 * @param p_api_version standard parm - API Version
356 * @param p_init_msg_list standard parm - Initialize message list
357 * @param x_return_status standard parm - Return Status
358 * @param x_msg_count standard parm - Message Count
359 * @param x_msg_data standard parm - Message Data
360 *
361 * @rep:displayname set_inheritance_type
362 *
363 */
364 PROCEDURE set_inheritance_type(
365 p_instance_object_id IN NUMBER
366 ,p_instance_pk1_value IN VARCHAR2
367 ,p_instance_pk2_value IN VARCHAR2
368 ,p_instance_pk3_value IN VARCHAR2
369 ,p_instance_pk4_value IN VARCHAR2
370 ,p_instance_pk5_value IN VARCHAR2
371 ,p_inheritance_type IN VARCHAR2
372 ,p_commit IN VARCHAR2
373 ,p_api_version IN NUMBER
374 ,p_init_msg_list IN VARCHAR2
375 ,x_return_status OUT NOCOPY VARCHAR2
376 ,x_msg_count OUT NOCOPY NUMBER
377 ,x_msg_data OUT NOCOPY VARCHAR2
378 ) AS
379 l_rowid ROWID;
380 --******** local variable for standards **********
381 l_api_name CONSTANT VARCHAR2(30) := 'set_inheritance_type';
382 l_api_version CONSTANT NUMBER := 1.0;
383 -- IBC_object_grant_groups
384 l_object_grant_group_rowid ROWID;
385 l_object_grant_group_id NUMBER;
386 l_old_grant_group_id NUMBER;
387 l_grant_group_id NUMBER;
388 l_inherited_flag VARCHAR2(2);
389 l_inherited_from NUMBER;
390 l_inheritance_type VARCHAR2(30);
391 l_default_inheritance_type VARCHAR2(30);
392 -- IBC_object_grant_groups
393 l_c_object_grant_group_rowid ROWID;
394 l_c_object_grant_group_id NUMBER;
395 l_c_grant_group_id NUMBER;
396 l_c_inherited_flag VARCHAR2(2);
397 l_c_inherited_from NUMBER;
398 l_c_inheritance_type VARCHAR2(30);
399
400 CURSOR c_ogg(p_object_grant_group_id NUMBER) IS
401 SELECT object_id,
402 instance_pk1_value,
403 instance_pk2_value,
404 instance_pk3_value,
405 instance_pk4_value,
406 instance_pk5_value
407 FROM ibc_object_grant_groups
408 WHERE object_grant_group_id = p_object_grant_group_id;
409 r_ogg c_ogg%ROWTYPE;
410
411 BEGIN
412 SAVEPOINT svpt_set_inheritance_type;
413
414 -- ******* Standard Begins ********
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call (
417 l_api_version,
418 p_api_version,
419 l_api_name,
420 G_PKG_NAME)
421 THEN
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 FND_MSG_PUB.ADD;
424 RAISE FND_API.G_EXC_ERROR;
425 END IF;
426 -- Initialize message list if p_init_msg_list is set to TRUE.
427 IF FND_API.to_Boolean( p_init_msg_list ) THEN
428 FND_MSG_PUB.initialize;
429 END IF;
430
431 -- Initialize API return status to success
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433
434 -- Begin
435
436 -- Fetch object's grant group Info
437 get_object_grant_group_info(
438 p_instance_object_id => p_instance_object_id
439 ,p_instance_pk1_value => p_instance_pk1_value
440 ,p_instance_pk2_value => p_instance_pk2_value
441 ,p_instance_pk3_value => p_instance_pk3_value
442 ,p_instance_pk4_value => p_instance_pk4_value
443 ,p_instance_pk5_value => p_instance_pk5_value
444 ,x_rowid => l_object_grant_group_rowid
445 ,x_object_grant_group_id => l_object_grant_group_id
446 ,x_grant_group_id => l_grant_group_id
447 ,x_inherited_flag => l_inherited_flag
448 ,x_inherited_from => l_inherited_from
449 ,x_inheritance_type => l_inheritance_type
450 );
451
452 OPEN c_ogg(l_inherited_from);
453 FETCH c_ogg INTO r_ogg;
454
455 IF c_ogg%FOUND THEN
456 CLOSE c_ogg;
457 establish_inheritance(
458 p_instance_object_id => p_instance_object_id
459 ,p_instance_pk1_value => p_instance_pk1_value
460 ,p_instance_pk2_value => p_instance_pk2_value
461 ,p_instance_pk3_value => p_instance_pk3_value
462 ,p_instance_pk4_value => p_instance_pk4_value
463 ,p_instance_pk5_value => p_instance_pk5_value
464 ,p_container_object_id => r_ogg.object_id
465 ,p_container_pk1_value => r_ogg.instance_pk1_value
466 ,p_container_pk2_value => r_ogg.instance_pk2_value
467 ,p_container_pk3_value => r_ogg.instance_pk3_value
468 ,p_container_pk4_value => r_ogg.instance_pk4_value
469 ,p_container_pk5_value => r_ogg.instance_pk5_value
470 ,p_inheritance_type => p_inheritance_type
471 ,p_commit => p_commit
472 ,p_api_version => 1.0
473 ,p_init_msg_list => p_init_msg_list
474 ,x_return_status => x_return_status
475 ,x_msg_count => x_msg_count
476 ,x_msg_data => x_msg_data
477 );
478 ELSE
479 CLOSE c_ogg;
480 END IF;
481
482 -- COMMIT?
483 IF (p_commit = FND_API.g_true) THEN
484 COMMIT;
485 END IF;
486
487 -- Standard call to get message count and if count=1, get the message
488 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
489 p_data => x_msg_data);
490 EXCEPTION
491 WHEN FND_API.G_EXC_ERROR THEN
492 ROLLBACK TO svpt_set_inheritance_type;
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
495 p_data => x_msg_data);
496 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
497 P_API_NAME => L_API_NAME
498 ,P_PKG_NAME => G_PKG_NAME
499 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
500 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
501 ,P_SQLCODE => SQLCODE
502 ,P_SQLERRM => SQLERRM
503 ,X_MSG_COUNT => X_MSG_COUNT
504 ,X_MSG_DATA => X_MSG_DATA
505 ,X_RETURN_STATUS => X_RETURN_STATUS
506 );
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508 ROLLBACK TO svpt_set_inheritance_type;
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
511 p_data => x_msg_data);
512 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
513 P_API_NAME => L_API_NAME
514 ,P_PKG_NAME => G_PKG_NAME
515 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
516 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
517 ,P_SQLCODE => SQLCODE
518 ,P_SQLERRM => SQLERRM
519 ,X_MSG_COUNT => X_MSG_COUNT
520 ,X_MSG_DATA => X_MSG_DATA
521 ,X_RETURN_STATUS => X_RETURN_STATUS
522 );
523 WHEN OTHERS THEN
524 ROLLBACK TO svpt_set_inheritance_type;
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
527 THEN
528 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
529 END IF;
530 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
531 p_data => x_msg_data);
532 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
533 P_API_NAME => L_API_NAME
534 ,P_PKG_NAME => G_PKG_NAME
535 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
536 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
537 ,P_SQLCODE => SQLCODE
538 ,P_SQLERRM => SQLERRM
539 ,X_MSG_COUNT => X_MSG_COUNT
540 ,X_MSG_DATA => X_MSG_DATA
541 ,X_RETURN_STATUS => X_RETURN_STATUS
542 );
543 END set_inheritance_type;
544
545
546 /*#
547 * It removes an instance from data security inheritance tree. This procedure
548 * should be called when the directory node gets removed from the system as well,
549 * to keep inheritance information accurate.
550 *
551 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
552 * for this particular instance
553 * @param p_instance_pk1_value value 1 for instance's primary key
554 * @param p_instance_pk2_value value 2 for instance's primary key
555 * @param p_instance_pk3_value value 3 for instance's primary key
556 * @param p_instance_pk4_value value 4 for instance's primary key
557 * @param p_instance_pk5_value value 5 for instance's primary key
558 * @param p_commit Indicates whether to commit or not at the end
559 * of procedure
560 * @param p_api_version standard parm - API Version
561 * @param p_init_msg_list standard parm - Initialize message list
562 * @param x_return_status standard parm - Return Status
563 * @param x_msg_count standard parm - Message Count
564 * @param x_msg_data standard parm - Message Data
565 *
566 * @rep:displayname Remove_Instance
567 *
568 */
569 PROCEDURE Remove_Instance(
570 p_instance_object_id IN NUMBER
571 ,p_instance_pk1_value IN VARCHAR2
572 ,p_instance_pk2_value IN VARCHAR2
573 ,p_instance_pk3_value IN VARCHAR2
574 ,p_instance_pk4_value IN VARCHAR2
575 ,p_instance_pk5_value IN VARCHAR2
576 ,p_commit IN VARCHAR2
577 ,p_api_version IN NUMBER
578 ,p_init_msg_list IN VARCHAR2
579 ,x_return_status OUT NOCOPY VARCHAR2
580 ,x_msg_count OUT NOCOPY NUMBER
581 ,x_msg_data OUT NOCOPY VARCHAR2
582 ) AS
583 l_rowid ROWID;
584 --******** local variable for standards **********
585 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Instance';
586 l_api_version CONSTANT NUMBER := 1.0;
587 -- IBC_object_grant_groups
588 l_object_grant_group_rowid ROWID;
589 l_object_grant_group_id NUMBER;
590 l_old_grant_group_id NUMBER;
591 l_grant_group_id NUMBER;
592 l_inherited_flag VARCHAR2(2);
593 l_inherited_from NUMBER;
594 l_inheritance_type VARCHAR2(30);
595 l_default_inheritance_type VARCHAR2(30);
596
597 CURSOR c_child_ogg(p_ogg_id NUMBER) IS
598 SELECT *
599 FROM ibc_object_grant_groups
600 WHERE inherited_from = p_ogg_id;
601
602 BEGIN
603 SAVEPOINT svpt_remove_instance;
604
605 -- ******* Standard Begins ********
606 -- Standard call to check for call compatibility.
607 IF NOT FND_API.Compatible_API_Call (
608 l_api_version,
609 p_api_version,
610 l_api_name,
611 G_PKG_NAME)
612 THEN
613 x_return_status := FND_API.G_RET_STS_ERROR;
614 FND_MSG_PUB.ADD;
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617 -- Initialize message list if p_init_msg_list is set to TRUE.
618 IF FND_API.to_Boolean( p_init_msg_list ) THEN
619 FND_MSG_PUB.initialize;
620 END IF;
621
622 -- Initialize API return status to success
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624
625 -- Begin
626
627 -- Fetch object's grant group Info
628 get_object_grant_group_info(
629 p_instance_object_id => p_instance_object_id
630 ,p_instance_pk1_value => p_instance_pk1_value
631 ,p_instance_pk2_value => p_instance_pk2_value
632 ,p_instance_pk3_value => p_instance_pk3_value
633 ,p_instance_pk4_value => p_instance_pk4_value
634 ,p_instance_pk5_value => p_instance_pk5_value
635 ,x_rowid => l_object_grant_group_rowid
636 ,x_object_grant_group_id => l_object_grant_group_id
637 ,x_grant_group_id => l_grant_group_id
638 ,x_inherited_flag => l_inherited_flag
639 ,x_inherited_from => l_inherited_from
640 ,x_inheritance_type => l_inheritance_type
641 );
642
643 IF l_object_grant_group_rowid IS NOT NULL THEN
644 FOR r_child_ogg IN c_child_ogg(l_object_grant_group_id) LOOP
645 Remove_Instance(
646 p_instance_object_id => r_child_ogg.object_id
647 ,p_instance_pk1_value => r_child_ogg.instance_pk1_value
648 ,p_instance_pk2_value => r_child_ogg.instance_pk2_value
649 ,p_instance_pk3_value => r_child_ogg.instance_pk3_value
650 ,p_instance_pk4_value => r_child_ogg.instance_pk4_value
651 ,p_instance_pk5_value => r_child_ogg.instance_pk5_value
652 ,p_commit => p_commit
653 ,p_api_version => p_api_version
654 ,p_init_msg_list => p_init_msg_list
655 ,x_return_status => x_return_status
656 ,x_msg_count => x_msg_count
657 ,x_msg_data => x_msg_data
658 );
659 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
660 END LOOP;
661
662 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
663 IF l_inherited_flag = 'N' THEN
664 -- Removing grants and grant group if not inheriting
665 DELETE FROM ibc_grants WHERE grant_group_id = l_grant_group_id;
666 DELETE FROM ibc_grant_groups WHERE grant_group_id = l_grant_group_id;
667 END IF;
668 DELETE FROM ibc_object_grant_groups
669 WHERE ROWID = l_object_grant_group_rowid;
670 END IF;
671
672 END IF;
673
674 -- COMMIT?
675 IF (p_commit = FND_API.g_true AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
676 COMMIT;
677 END IF;
678
679 -- Standard call to get message count and if count=1, get the message
680 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
681 p_data => x_msg_data);
682 EXCEPTION
683 WHEN FND_API.G_EXC_ERROR THEN
684 ROLLBACK TO svpt_remove_instance;
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
687 p_data => x_msg_data);
688 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
689 P_API_NAME => L_API_NAME
690 ,P_PKG_NAME => G_PKG_NAME
691 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
692 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
693 ,P_SQLCODE => SQLCODE
694 ,P_SQLERRM => SQLERRM
695 ,X_MSG_COUNT => X_MSG_COUNT
696 ,X_MSG_DATA => X_MSG_DATA
697 ,X_RETURN_STATUS => X_RETURN_STATUS
698 );
699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
700 ROLLBACK TO svpt_remove_instance;
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
703 p_data => x_msg_data);
704 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
705 P_API_NAME => L_API_NAME
706 ,P_PKG_NAME => G_PKG_NAME
707 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
708 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
709 ,P_SQLCODE => SQLCODE
710 ,P_SQLERRM => SQLERRM
711 ,X_MSG_COUNT => X_MSG_COUNT
712 ,X_MSG_DATA => X_MSG_DATA
713 ,X_RETURN_STATUS => X_RETURN_STATUS
714 );
715 WHEN OTHERS THEN
716 ROLLBACK TO svpt_remove_instance;
717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
718 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
719 THEN
720 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
721 END IF;
722 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
723 p_data => x_msg_data);
724 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
725 P_API_NAME => L_API_NAME
726 ,P_PKG_NAME => G_PKG_NAME
727 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
728 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
729 ,P_SQLCODE => SQLCODE
730 ,P_SQLERRM => SQLERRM
731 ,X_MSG_COUNT => X_MSG_COUNT
732 ,X_MSG_DATA => X_MSG_DATA
733 ,X_RETURN_STATUS => X_RETURN_STATUS
734 );
735 END Remove_Instance;
736
737
738 /*#
739 * This procedure establishes inheritance hierarchy, it must be kept
740 * in sync with directory nodes hierarchy tree. It creates an
741 * inheritance link between an instance (child) and its container (parent).
742 * This procedure must be called for each container (i.e. directory node)
743 * create to define a hierarchy of containment and inheritance
744 *
745 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
746 * for this particular instance
747 * @param p_instance_pk1_value value 1 for instance's primary key
748 * @param p_instance_pk2_value value 2 for instance's primary key
749 * @param p_instance_pk3_value value 3 for instance's primary key
750 * @param p_instance_pk4_value value 4 for instance's primary key
751 * @param p_instance_pk5_value value 5 for instance's primary key
752 * @param p_container_object_id ID for object definition id found in FND_OBJECTS
753 * for the container
754 * @param p_container_pk1_value value 1 for container's primary key
755 * @param p_container_pk2_value value 2 for container's primary key
756 * @param p_container_pk3_value value 3 for container's primary key
757 * @param p_container_pk4_value value 4 for container's primary key
758 * @param p_container_pk5_value value 5 for container's primary key
759 * @param p_inheritance_type type of inheritance (FOLDER, HIDDEN-FOLDER,
760 * WORKSPACE and WSFOLDER). Currently supported
761 * in OCM only FOLDER and HIDDEN-FOLDER.
762 * @param p_commit Indicates whether to commit or not at the end
763 * of procedure
764 * @param p_api_version standard parm - API Version
765 * @param p_init_msg_list standard parm - Initialize message list
766 * @param x_return_status standard parm - Return Status
767 * @param x_msg_count standard parm - Message Count
768 * @param x_msg_data standard parm - Message Data
769 *
770 * @rep:displayname establish_inheritance
771 *
772 */
773 PROCEDURE establish_inheritance(
774 p_instance_object_id IN NUMBER
775 ,p_instance_pk1_value IN VARCHAR2
776 ,p_instance_pk2_value IN VARCHAR2
777 ,p_instance_pk3_value IN VARCHAR2
778 ,p_instance_pk4_value IN VARCHAR2
779 ,p_instance_pk5_value IN VARCHAR2
780 ,p_container_object_id IN NUMBER
781 ,p_container_pk1_value IN VARCHAR2
782 ,p_container_pk2_value IN VARCHAR2
783 ,p_container_pk3_value IN VARCHAR2
784 ,p_container_pk4_value IN VARCHAR2
785 ,p_container_pk5_value IN VARCHAR2
786 ,p_inheritance_type IN VARCHAR2
787 ,p_commit IN VARCHAR2
788 ,p_api_version IN NUMBER
789 ,p_init_msg_list IN VARCHAR2
790 ,x_return_status OUT NOCOPY VARCHAR2
791 ,x_msg_count OUT NOCOPY NUMBER
792 ,x_msg_data OUT NOCOPY VARCHAR2
793 ) AS
794 l_rowid ROWID;
795 --******** local variable for standards **********
796 l_api_name CONSTANT VARCHAR2(30) := 'establish_inheritance';
797 l_api_version CONSTANT NUMBER := 1.0;
798 -- IBC_object_grant_groups
799 l_object_grant_group_rowid ROWID;
800 l_object_grant_group_id NUMBER;
801 l_old_grant_group_id NUMBER;
802 l_grant_group_id NUMBER;
803 l_inherited_flag VARCHAR2(2);
804 l_inherited_from NUMBER;
805 l_inheritance_type VARCHAR2(30);
806 l_default_inheritance_type VARCHAR2(30);
807 -- IBC_object_grant_groups
808 l_c_object_grant_group_rowid ROWID;
809 l_c_object_grant_group_id NUMBER;
810 l_c_grant_group_id NUMBER;
811 l_c_inherited_flag VARCHAR2(2);
812 l_c_inherited_from NUMBER;
813 l_c_inheritance_type VARCHAR2(30);
814 -- Cursor to get all ogg children objects
815 CURSOR c_ogg_children(p_ogg_id IN NUMBER) IS
816 SELECT *
817 FROM ibc_object_grant_groups
818 WHERE inherited_from = p_ogg_id;
819 BEGIN
820 SAVEPOINT svpt_establish_inheritance;
821
822 -- ******* Standard Begins ********
823 -- Standard call to check for call compatibility.
824 IF NOT FND_API.Compatible_API_Call (
825 l_api_version,
826 p_api_version,
827 l_api_name,
828 G_PKG_NAME)
829 THEN
830 x_return_status := FND_API.G_RET_STS_ERROR;
831 FND_MSG_PUB.ADD;
832 RAISE FND_API.G_EXC_ERROR;
833 END IF;
834 -- Initialize message list if p_init_msg_list is set to TRUE.
835 IF FND_API.to_Boolean( p_init_msg_list ) THEN
836 FND_MSG_PUB.initialize;
837 END IF;
838
839 -- Initialize API return status to success
840 x_return_status := FND_API.G_RET_STS_SUCCESS;
841
842 -- Begin
843
844 -- Fetch object's grant group Info
845 get_object_grant_group_info(
846 p_instance_object_id => p_instance_object_id
847 ,p_instance_pk1_value => p_instance_pk1_value
848 ,p_instance_pk2_value => p_instance_pk2_value
849 ,p_instance_pk3_value => p_instance_pk3_value
850 ,p_instance_pk4_value => p_instance_pk4_value
851 ,p_instance_pk5_value => p_instance_pk5_value
852 ,x_rowid => l_object_grant_group_rowid
853 ,x_object_grant_group_id => l_object_grant_group_id
854 ,x_grant_group_id => l_grant_group_id
855 ,x_inherited_flag => l_inherited_flag
856 ,x_inherited_from => l_inherited_from
857 ,x_inheritance_type => l_inheritance_type
858 );
859
860 -- Fetch object's grant group info for Container
861 get_object_grant_group_info(
862 p_instance_object_id => p_container_object_id
863 ,p_instance_pk1_value => p_container_pk1_value
864 ,p_instance_pk2_value => p_container_pk2_value
865 ,p_instance_pk3_value => p_container_pk3_value
866 ,p_instance_pk4_value => p_container_pk4_value
867 ,p_instance_pk5_value => p_container_pk5_value
868 ,x_rowid => l_c_object_grant_group_rowid
869 ,x_object_grant_group_id => l_c_object_grant_group_id
870 ,x_grant_group_id => l_c_grant_group_id
871 ,x_inherited_flag => l_c_inherited_flag
872 ,x_inherited_from => l_c_inherited_from
873 ,x_inheritance_type => l_c_inheritance_type
874 );
875
876 IF l_object_grant_group_rowid IS NULL AND
877 l_c_object_grant_group_rowid IS NULL
878 THEN
879 -- No object's grant group (inheritance) defined for object
880 -- And no object's grant group for container
881 -- It will be treated as initial setup for root dir
882 -- Create Row in IBC_grant_groups
883 SELECT ibc_grant_groups_s1.nextval
884 INTO l_grant_group_id
885 FROM dual;
886 IBC_GRANT_GROUPS_PKG.insert_row(
887 px_rowid => l_rowid
888 ,p_grant_group_id => l_grant_group_id
889 ,p_object_version_number => 1
890 );
891 -- Create Row in IBC_object_grant_groups
892 SELECT ibc_object_grant_groups_s1.nextval
893 INTO l_object_grant_group_id
894 FROM dual;
895 IBC_OBJECT_GRANT_GROUPS_PKG.insert_row(
896 px_rowid => l_rowid
897 ,p_object_grant_group_id => l_object_grant_group_id
898 ,p_object_version_number => 1
899 ,p_grant_group_id => l_grant_group_id
900 ,p_object_id => p_instance_object_id
901 ,p_inherited_flag => 'N'
902 ,p_inherited_from => NULL
903 ,p_instance_pk1_value => p_instance_pk1_value
904 ,p_instance_pk2_value => p_instance_pk2_value
905 ,p_instance_pk3_value => p_instance_pk3_value
906 ,p_instance_pk4_value => p_instance_pk4_value
907 ,p_instance_pk5_value => p_instance_pk5_value
908 ,p_inheritance_type => NVL(p_inheritance_type, 'FOLDER')
909 );
910 ELSIF l_object_grant_group_rowid IS NULL THEN
911 -- No object's grant group (inheritance) defined for object
912 -- but defined for container object.
913 -- Regular inheritance row will be added to object_grant_group
914 -- Create Row in IBC_object_grant_groups
915
916 -- Validation of inheritance type
917 IF (l_c_inheritance_type = 'WORKSPACE' AND
918 p_inheritance_type <> 'WSFOLDER')
919 OR
920 (l_c_inheritance_type = 'HIDDEN-FOLDER' AND
921 p_inheritance_type <> 'HIDDEN-FOLDER')
922 OR
923 (l_c_inheritance_type = 'FOLDER' AND
924 p_inheritance_type NOT IN ('FOLDER', 'HIDDEN-FOLDER', 'WORKSPACE'))
925 OR
926 (l_c_inheritance_type = 'WSFOLDER' AND
927 p_inheritance_type <> 'WSFOLDER')
928 THEN
929 -- Error Inheritance Type not compatible with containers
930 FND_MESSAGE.Set_Name('IBC', 'IBC_INCOMPATIBLE_INHERITANCE_TYPE');
931 FND_MSG_PUB.ADD;
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935 -- Defaulting inheritance type based on container's inheritance type
936 IF p_inheritance_type IS NULL THEN
937 IF l_c_inheritance_type = 'WORKSPACE' THEN
938 l_default_inheritance_type := 'WSFOLDER';
939 ELSE
940 l_default_inheritance_Type := l_c_inheritance_type;
941 END IF;
942 END IF;
943
944 SELECT ibc_object_grant_groups_s1.nextval
945 INTO l_object_grant_group_id
946 FROM dual;
947 IBC_OBJECT_GRANT_GROUPS_PKG.insert_row(
948 px_rowid => l_rowid
949 ,p_object_grant_group_id => l_object_grant_group_id
950 ,p_object_version_number => 1
951 ,p_grant_group_id => l_c_grant_group_id
952 ,p_object_id => p_instance_object_id
953 ,p_inherited_flag => 'Y'
954 ,p_inherited_from => l_c_object_grant_group_id
955 ,p_instance_pk1_value => p_instance_pk1_value
956 ,p_instance_pk2_value => p_instance_pk2_value
957 ,p_instance_pk3_value => p_instance_pk3_value
958 ,p_instance_pk4_value => p_instance_pk4_value
959 ,p_instance_pk5_value => p_instance_pk5_value
960 ,p_inheritance_type => NVL(p_inheritance_type, l_default_inheritance_type)
961 );
962 ELSE
963 -- object's grant group exists for object and container object
964 -- it will be treated as an update.
965 IF l_inherited_from = l_c_object_grant_group_id THEN
966 -- No change in hierarchy tree, only update if inheritance type differs
967 IF l_inheritance_type <> p_inheritance_type THEN
968
969
970 -- Validation of inheritance type according to container
971 IF (l_c_inheritance_type = 'WORKSPACE' AND
972 p_inheritance_type <> 'WSFOLDER')
973 OR
974 (l_c_inheritance_type = 'HIDDEN-FOLDER' AND
975 p_inheritance_type <> 'HIDDEN-FOLDER')
976 OR
977 (l_c_inheritance_type = 'FOLDER' AND
978 p_inheritance_type NOT IN ('FOLDER', 'HIDDEN-FOLDER', 'WORKSPACE'))
979 OR
980 (l_c_inheritance_type = 'WSFOLDER' AND
981 p_inheritance_type <> 'WSFOLDER')
982 THEN
983 -- Error Inheritance Type not compatible with containers
984 FND_MESSAGE.Set_Name('IBC', 'IBC_INCOMPATIBLE_INHERITANCE_TYPE');
985 FND_MSG_PUB.ADD;
986 RAISE FND_API.G_EXC_ERROR;
987 END IF;
988
989 -- Actual UPDATE
990 -- *****
991 Ibc_Object_Grant_Groups_Pkg.UPDATE_ROW (
992 P_OBJECT_GRANT_GROUP_ID => l_object_grant_group_id,
993 p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM,
994 P_GRANT_GROUP_ID => l_grant_group_id,
995 P_OBJECT_ID => p_instance_object_id,
996 P_INHERITED_FLAG => l_inherited_flag,
997 P_INHERITED_FROM => l_inherited_from,
998 P_INSTANCE_PK1_VALUE => p_instance_pk1_value,
999 P_INSTANCE_PK2_VALUE => p_instance_pk2_value,
1000 P_INSTANCE_PK3_VALUE => p_instance_pk3_value,
1001 P_INSTANCE_PK4_VALUE => p_instance_pk4_value,
1002 P_INSTANCE_PK5_VALUE => p_instance_pk5_value,
1003 P_INHERITANCE_TYPE => p_inheritance_type
1004 );
1005
1006 IF p_inheritance_type = 'WSFOLDER' AND l_inherited_flag = FND_API.G_FALSE THEN
1007 -- Removing all permissions for grant group if it's WSFOLDER and it's not inheriting
1008 DELETE FROM ibc_grants
1009 WHERE grant_group_id = l_grant_group_id;
1010 -- Remove grant group
1011 DELETE FROM ibc_grant_groups
1012 WHERE grant_group_id = l_grant_group_id;
1013 -- update inheritance to all the the ones pointing to such grant group to
1014 -- point to the container's, and change inheritance type for all of them to
1015 -- WSFOLDER
1016 UPDATE ibc_object_grant_groups
1017 SET inherited_flag = 'Y',
1018 grant_group_id = l_c_grant_group_id
1019 WHERE object_grant_group_id
1020 IN ( SELECT object_grant_group_id
1021 FROM ibc_object_grant_groups
1022 CONNECT BY PRIOR object_grant_group_id = inherited_from
1023 START WITH inherited_from = l_object_grant_group_id);
1024 END IF;
1025
1026 END IF;
1027
1028 FOR r_ogg IN c_ogg_children(l_object_grant_group_id) LOOP
1029
1030 -- Defaulting inheritance type based on container's inheritance type
1031 IF p_inheritance_type = 'WORKSPACE' THEN
1032 l_default_inheritance_type := 'WSFOLDER';
1033 ELSE
1034 l_default_inheritance_Type := p_inheritance_type;
1035 END IF;
1036
1037 -- **** PENDING.
1038 establish_inheritance(
1039 p_instance_object_id => r_ogg.object_id
1040 ,p_instance_pk1_value => r_ogg.instance_pk1_value
1041 ,p_instance_pk2_value => r_ogg.instance_pk2_value
1042 ,p_instance_pk3_value => r_ogg.instance_pk3_value
1043 ,p_instance_pk4_value => r_ogg.instance_pk4_value
1044 ,p_instance_pk5_value => r_ogg.instance_pk5_value
1045 ,p_container_object_id => p_instance_object_id
1046 ,p_container_pk1_value => p_instance_pk1_value
1047 ,p_container_pk2_value => p_instance_pk2_value
1048 ,p_container_pk3_value => p_instance_pk3_value
1049 ,p_container_pk4_value => p_instance_pk4_value
1050 ,p_container_pk5_value => p_instance_pk5_value
1051 ,p_inheritance_type => l_default_inheritance_type
1052 ,p_commit => FND_API.g_false
1053 ,p_api_version => 1.0
1054 ,p_init_msg_list => FND_API.g_false
1055 ,x_return_status => x_return_status
1056 ,x_msg_count => x_msg_count
1057 ,x_msg_data => x_msg_data
1058 );
1059
1060 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1061 RAISE FND_API.G_EXC_ERROR;
1062 END IF;
1063
1064 END LOOP;
1065 ELSE
1066 -- Moving an instance to a
1067 -- Different container, by default all permissions will be gone.
1068 -- *** STILL PENDING: Not needed in this release ***
1069 NULL;
1070 END IF;
1071 END IF;
1072
1073 -- COMMIT?
1074 IF (p_commit = FND_API.g_true) THEN
1075 COMMIT;
1076 END IF;
1077
1078 -- Standard call to get message count and if count=1, get the message
1079 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1080 p_data => x_msg_data);
1081 EXCEPTION
1082 WHEN FND_API.G_EXC_ERROR THEN
1083 ROLLBACK TO svpt_establish_inheritance;
1084 x_return_status := FND_API.G_RET_STS_ERROR;
1085 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1086 p_data => x_msg_data);
1087 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1088 P_API_NAME => L_API_NAME
1089 ,P_PKG_NAME => G_PKG_NAME
1090 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1091 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1092 ,P_SQLCODE => SQLCODE
1093 ,P_SQLERRM => SQLERRM
1094 ,X_MSG_COUNT => X_MSG_COUNT
1095 ,X_MSG_DATA => X_MSG_DATA
1096 ,X_RETURN_STATUS => X_RETURN_STATUS
1097 );
1098 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1099 ROLLBACK TO svpt_establish_inheritance;
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1102 p_data => x_msg_data);
1103 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1104 P_API_NAME => L_API_NAME
1105 ,P_PKG_NAME => G_PKG_NAME
1106 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1107 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1108 ,P_SQLCODE => SQLCODE
1109 ,P_SQLERRM => SQLERRM
1110 ,X_MSG_COUNT => X_MSG_COUNT
1111 ,X_MSG_DATA => X_MSG_DATA
1112 ,X_RETURN_STATUS => X_RETURN_STATUS
1113 );
1114 WHEN OTHERS THEN
1115 ROLLBACK TO svpt_establish_inheritance;
1116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1117 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1118 THEN
1119 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1120 END IF;
1121 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1122 p_data => x_msg_data);
1123 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1124 P_API_NAME => L_API_NAME
1125 ,P_PKG_NAME => G_PKG_NAME
1126 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1127 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1128 ,P_SQLCODE => SQLCODE
1129 ,P_SQLERRM => SQLERRM
1130 ,X_MSG_COUNT => X_MSG_COUNT
1131 ,X_MSG_DATA => X_MSG_DATA
1132 ,X_RETURN_STATUS => X_RETURN_STATUS
1133 );
1134 END establish_inheritance;
1135
1136
1137 /*#
1138 * This procedure establishes inheritance hierarchy, it must be kept
1139 * in sync with directory nodes hierarchy tree. It creates an
1140 * inheritance link between an instance (child) and its container (parent).
1141 * This procedure must be called for each container (i.e. directory node)
1142 * create to define a hierarchy of containment and inheritance.
1143 * This is overloaded of establish_inheritance without inheritance type parm.
1144 *
1145 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
1146 * for this particular instance
1147 * @param p_instance_pk1_value value 1 for instance's primary key
1148 * @param p_instance_pk2_value value 2 for instance's primary key
1149 * @param p_instance_pk3_value value 3 for instance's primary key
1150 * @param p_instance_pk4_value value 4 for instance's primary key
1151 * @param p_instance_pk5_value value 5 for instance's primary key
1152 * @param p_container_object_id ID for object definition id found in FND_OBJECTS
1153 * for the container
1154 * @param p_container_pk1_value value 1 for container's primary key
1155 * @param p_container_pk2_value value 2 for container's primary key
1156 * @param p_container_pk3_value value 3 for container's primary key
1157 * @param p_container_pk4_value value 4 for container's primary key
1158 * @param p_container_pk5_value value 5 for container's primary key
1159 * @param p_commit Indicates whether to commit or not at the end
1160 * of procedure
1161 * @param p_api_version standard parm - API Version
1162 * @param p_init_msg_list standard parm - Initialize message list
1163 * @param x_return_status standard parm - Return Status
1164 * @param x_msg_count standard parm - Message Count
1165 * @param x_msg_data standard parm - Message Data
1166 *
1167 * @rep:displayname establish_inheritance
1168 *
1169 */
1170 PROCEDURE establish_inheritance(
1171 p_instance_object_id IN NUMBER
1172 ,p_instance_pk1_value IN VARCHAR2
1173 ,p_instance_pk2_value IN VARCHAR2
1174 ,p_instance_pk3_value IN VARCHAR2
1175 ,p_instance_pk4_value IN VARCHAR2
1176 ,p_instance_pk5_value IN VARCHAR2
1177 ,p_container_object_id IN NUMBER
1178 ,p_container_pk1_value IN VARCHAR2
1179 ,p_container_pk2_value IN VARCHAR2
1180 ,p_container_pk3_value IN VARCHAR2
1181 ,p_container_pk4_value IN VARCHAR2
1182 ,p_container_pk5_value IN VARCHAR2
1183 ,p_commit IN VARCHAR2
1184 ,p_api_version IN NUMBER
1185 ,p_init_msg_list IN VARCHAR2
1186 ,x_return_status OUT NOCOPY VARCHAR2
1187 ,x_msg_count OUT NOCOPY NUMBER
1188 ,x_msg_data OUT NOCOPY VARCHAR2
1189 ) AS
1190 BEGIN
1191 establish_inheritance(
1192 p_instance_object_id => p_instance_object_id
1193 ,p_instance_pk1_value => p_instance_pk1_value
1194 ,p_instance_pk2_value => p_instance_pk2_value
1195 ,p_instance_pk3_value => p_instance_pk3_value
1196 ,p_instance_pk4_value => p_instance_pk4_value
1197 ,p_instance_pk5_value => p_instance_pk5_value
1198 ,p_container_object_id => p_container_object_id
1199 ,p_container_pk1_value => p_container_pk1_value
1200 ,p_container_pk2_value => p_container_pk2_value
1201 ,p_container_pk3_value => p_container_pk3_value
1202 ,p_container_pk4_value => p_container_pk4_value
1203 ,p_container_pk5_value => p_container_pk5_value
1204 ,p_inheritance_type => NULL
1205 ,p_commit => p_commit
1206 ,p_api_version => p_api_Version
1207 ,p_init_msg_list => p_init_msg_list
1208 ,x_return_status => x_return_status
1209 ,x_msg_count => x_msg_count
1210 ,x_msg_data => x_msg_data
1211 );
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 RAISE;
1215 END establish_inheritance;
1216
1217 /*#
1218 * It Resets all permissions, and makes the instance to inherit
1219 * all permissions from parent. This procedure gets called when
1220 * in the UI the user selects "Inherit"
1221 *
1222 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
1223 * for this particular instance
1224 * @param p_instance_pk1_value value 1 for instance's primary key
1225 * @param p_instance_pk2_value value 2 for instance's primary key
1226 * @param p_instance_pk3_value value 3 for instance's primary key
1227 * @param p_instance_pk4_value value 4 for instance's primary key
1228 * @param p_instance_pk5_value value 5 for instance's primary key
1229 * @param p_commit Indicates whether to commit or not at the end
1230 * of procedure
1231 * @param p_api_version standard parm - API Version
1232 * @param p_init_msg_list standard parm - Initialize message list
1233 * @param x_return_status standard parm - Return Status
1234 * @param x_msg_count standard parm - Message Count
1235 * @param x_msg_data standard parm - Message Data
1236 *
1237 * @rep:displayname reset_permissions
1238 *
1239 */
1240 PROCEDURE reset_permissions(
1241 p_instance_object_id IN NUMBER
1242 ,p_instance_pk1_value IN VARCHAR2
1243 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
1244 ,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
1245 ,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
1246 ,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
1247 ,p_commit IN VARCHAR2 DEFAULT FND_API.g_false
1248 ,p_api_version IN NUMBER DEFAULT 1.0
1249 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
1250 ,x_return_status OUT NOCOPY VARCHAR2
1251 ,x_msg_count OUT NOCOPY NUMBER
1252 ,x_msg_data OUT NOCOPY VARCHAR2
1253 ) AS
1254 l_rowid ROWID;
1255 --******** local variable for standards **********
1256 l_api_name CONSTANT VARCHAR2(30) := 'reset_permissions';
1257 l_api_version CONSTANT NUMBER := 1.0;
1258 -- IBC_object_grant_groups
1259 l_object_grant_group_rowid ROWID;
1260 l_object_grant_group_id NUMBER;
1261 l_old_grant_group_id NUMBER;
1262 l_grant_group_id NUMBER;
1263 l_inherited_flag VARCHAR2(2);
1264 l_inherited_from NUMBER;
1265 l_inheritance_type VARCHAR2(30);
1266 l_default_inheritance_type VARCHAR2(30);
1267 BEGIN
1268 SAVEPOINT svpt_reset_permissions;
1269
1270 -- ******* Standard Begins ********
1271 -- Standard call to check for call compatibility.
1272 IF NOT FND_API.Compatible_API_Call (
1273 l_api_version,
1274 p_api_version,
1275 l_api_name,
1276 G_PKG_NAME)
1277 THEN
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279 FND_MSG_PUB.ADD;
1280 RAISE FND_API.G_EXC_ERROR;
1281 END IF;
1282 -- Initialize message list if p_init_msg_list is set to TRUE.
1283 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1284 FND_MSG_PUB.initialize;
1285 END IF;
1286
1287 -- Initialize API return status to success
1288 x_return_status := FND_API.G_RET_STS_SUCCESS;
1289
1290 -- Begin
1291
1292 IF IBC_DEBUG_PVT.debug_enabled THEN
1293 IBC_DEBUG_PVT.start_process(
1294 p_proc_type => 'PROCEDURE',
1295 p_proc_name => 'Reset_Permissions',
1296 p_parms => IBC_DEBUG_PVT.make_parameter_list(
1297 p_tag => 'PARAMETERS',
1298 p_parms => JTF_VARCHAR2_TABLE_4000(
1299 'p_instance_object_id', p_instance_object_id,
1300 'p_instance_pk1_value', p_instance_pk1_value,
1301 'p_instance_pk2_value', p_instance_pk2_value,
1302 'p_instance_pk3_value', p_instance_pk3_value,
1303 'p_instance_pk4_value', p_instance_pk4_value,
1304 'p_instance_pk5_value', p_instance_pk5_value,
1305 'p_commit', p_commit,
1306 'p_api_version', p_api_version,
1307 'p_init_msg_list', p_init_msg_list
1308 )
1309 )
1310 );
1311 END IF;
1312
1313
1314 -- Fetch object's grant group Info
1315 get_object_grant_group_info(
1316 p_instance_object_id => p_instance_object_id
1317 ,p_instance_pk1_value => p_instance_pk1_value
1318 ,p_instance_pk2_value => p_instance_pk2_value
1319 ,p_instance_pk3_value => p_instance_pk3_value
1320 ,p_instance_pk4_value => p_instance_pk4_value
1321 ,p_instance_pk5_value => p_instance_pk5_value
1322 ,x_rowid => l_object_grant_group_rowid
1323 ,x_object_grant_group_id => l_object_grant_group_id
1324 ,x_grant_group_id => l_grant_group_id
1325 ,x_inherited_flag => l_inherited_flag
1326 ,x_inherited_from => l_inherited_from
1327 ,x_inheritance_type => l_inheritance_type
1328 );
1329
1330 -- Only update if not currently inheriting and it is inheriting from
1331 -- a container
1332 IF l_inherited_flag = 'N' and l_inherited_from IS NOT NULL THEN
1333 -- Removing all permissions for grant group
1334 DELETE FROM ibc_grants
1335 WHERE grant_group_id = l_grant_group_id;
1336 -- Remove grant group
1337 DELETE FROM ibc_grant_groups
1338 WHERE grant_group_id = l_grant_group_id;
1339 -- Sets inherited_flag to Y and points to grant group from container
1340 UPDATE ibc_object_grant_groups
1341 SET inherited_flag = 'Y',
1342 grant_group_id = (SELECT grant_group_id
1343 FROM ibc_object_grant_groups
1344 WHERE object_grant_group_id = l_inherited_from)
1345 WHERE object_grant_group_id
1346 IN ( SELECT object_grant_group_id
1347 FROM ibc_object_grant_groups
1348 WHERE grant_group_id = l_grant_group_id);
1349 END IF;
1350
1351 -- COMMIT?
1352 IF (p_commit = FND_API.g_true) THEN
1353 COMMIT;
1354 END IF;
1355
1356 -- Standard call to get message count and if count=1, get the message
1357 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1358 p_data => x_msg_data);
1359
1360 IF IBC_DEBUG_PVT.debug_enabled THEN
1361 IBC_DEBUG_PVT.end_process(
1362 IBC_DEBUG_PVT.make_parameter_list(
1363 p_tag => 'OUTPUT',
1364 p_parms => JTF_VARCHAR2_TABLE_4000(
1365 'x_return_status', x_return_status,
1366 'x_msg_count', x_msg_count,
1367 'x_msg_data', x_msg_data
1368 )
1369 )
1370 );
1371 END IF;
1372
1373 EXCEPTION
1374 WHEN FND_API.G_EXC_ERROR THEN
1375 ROLLBACK TO svpt_reset_permissions;
1376 x_return_status := FND_API.G_RET_STS_ERROR;
1377 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1378 p_data => x_msg_data);
1379 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1380 P_API_NAME => L_API_NAME
1381 ,P_PKG_NAME => G_PKG_NAME
1382 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1383 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1384 ,P_SQLCODE => SQLCODE
1385 ,P_SQLERRM => SQLERRM
1386 ,X_MSG_COUNT => X_MSG_COUNT
1387 ,X_MSG_DATA => X_MSG_DATA
1388 ,X_RETURN_STATUS => X_RETURN_STATUS
1389 );
1390 IF IBC_DEBUG_PVT.debug_enabled THEN
1391 IBC_DEBUG_PVT.end_process(
1392 IBC_DEBUG_PVT.make_parameter_list(
1393 p_tag => 'OUTPUT',
1394 p_parms => JTF_VARCHAR2_TABLE_4000(
1395 'x_return_status', x_return_status,
1396 'x_msg_count', x_msg_count,
1397 'x_msg_data', x_msg_data
1398 )
1399 )
1400 );
1401 END IF;
1402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1403 ROLLBACK TO svpt_reset_permissions;
1404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1406 p_data => x_msg_data);
1407 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1408 P_API_NAME => L_API_NAME
1409 ,P_PKG_NAME => G_PKG_NAME
1410 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1411 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1412 ,P_SQLCODE => SQLCODE
1413 ,P_SQLERRM => SQLERRM
1414 ,X_MSG_COUNT => X_MSG_COUNT
1415 ,X_MSG_DATA => X_MSG_DATA
1416 ,X_RETURN_STATUS => X_RETURN_STATUS
1417 );
1418 IF IBC_DEBUG_PVT.debug_enabled THEN
1419 IBC_DEBUG_PVT.end_process(
1420 IBC_DEBUG_PVT.make_parameter_list(
1421 p_tag => 'OUTPUT',
1422 p_parms => JTF_VARCHAR2_TABLE_4000(
1423 'x_return_status', x_return_status,
1424 'x_msg_count', x_msg_count,
1425 'x_msg_data', x_msg_data
1426 )
1427 )
1428 );
1429 END IF;
1430 WHEN OTHERS THEN
1431 ROLLBACK TO svpt_reset_permissions;
1432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1433 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1434 THEN
1435 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1436 END IF;
1437 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1438 p_data => x_msg_data);
1439 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1440 P_API_NAME => L_API_NAME
1441 ,P_PKG_NAME => G_PKG_NAME
1442 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1443 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1444 ,P_SQLCODE => SQLCODE
1445 ,P_SQLERRM => SQLERRM
1446 ,X_MSG_COUNT => X_MSG_COUNT
1447 ,X_MSG_DATA => X_MSG_DATA
1448 ,X_RETURN_STATUS => X_RETURN_STATUS
1449 );
1450 IF IBC_DEBUG_PVT.debug_enabled THEN
1451 IBC_DEBUG_PVT.end_process(
1452 IBC_DEBUG_PVT.make_parameter_list(
1453 p_tag => 'OUTPUT',
1454 p_parms => JTF_VARCHAR2_TABLE_4000(
1455 'x_return_status', x_return_status,
1456 'x_msg_count', x_msg_count,
1457 'x_msg_data', x_msg_data
1458 )
1459 )
1460 );
1461 END IF;
1462 END reset_permissions;
1463
1464 /*#
1465 * It breaks inheritance of an instance form its parent, and copies
1466 * all permissions from container with the intention of "isolating"
1467 * instance's permissions from any modification to its container's
1468 * permissions. This procedure gets called from UI when User clicks
1469 * on "Override", and it is useful so even though the user doesn't
1470 * make any other modification, the inheritance is already broken
1471 * and can be saved as such.
1472 *
1473 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
1474 * for this particular instance
1475 * @param p_instance_pk1_value value 1 for instance's primary key
1476 * @param p_instance_pk2_value value 2 for instance's primary key
1477 * @param p_instance_pk3_value value 3 for instance's primary key
1478 * @param p_instance_pk4_value value 4 for instance's primary key
1479 * @param p_instance_pk5_value value 5 for instance's primary key
1480 * @param p_commit Indicates whether to commit or not at the end
1481 * of procedure
1482 * @param p_api_version standard parm - API Version
1483 * @param p_init_msg_list standard parm - Initialize message list
1484 * @param x_return_status standard parm - Return Status
1485 * @param x_msg_count standard parm - Message Count
1486 * @param x_msg_data standard parm - Message Data
1487 *
1488 * @rep:displayname override_permissions
1489 *
1490 */
1491 PROCEDURE override_permissions(
1492 p_instance_object_id IN NUMBER
1493 ,p_instance_pk1_value IN VARCHAR2
1494 ,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
1495 ,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
1496 ,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
1497 ,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
1498 ,p_commit IN VARCHAR2 DEFAULT FND_API.g_false
1499 ,p_api_version IN NUMBER DEFAULT 1.0
1500 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
1501 ,x_return_status OUT NOCOPY VARCHAR2
1502 ,x_msg_count OUT NOCOPY NUMBER
1503 ,x_msg_data OUT NOCOPY VARCHAR2
1504 ) AS
1505 l_rowid ROWID;
1506 --******** local variable for standards **********
1507 l_api_name CONSTANT VARCHAR2(30) := 'override_permissions';
1508 l_api_version CONSTANT NUMBER := 1.0;
1509 -- IBC_object_grant_groups
1510 l_object_grant_group_rowid ROWID;
1511 l_object_grant_group_id NUMBER;
1512 l_grant_group_id NUMBER;
1513 l_inherited_flag VARCHAR2(2);
1514 l_inherited_from NUMBER;
1515 l_inheritance_type VARCHAR2(30);
1516 l_old_grant_group_id NUMBER;
1517 -- IBC_object_grant_groups
1518 l_c_object_grant_group_rowid ROWID;
1519 l_c_object_grant_group_id NUMBER;
1520 l_c_grant_group_id NUMBER;
1521 l_c_inherited_flag VARCHAR2(2);
1522 l_c_inherited_from NUMBER;
1523 l_c_inheritance_type VARCHAR2(30);
1524
1525 CURSOR c_ogg(p_object_grant_group_id NUMBER) IS
1526 SELECT object_id,
1527 instance_pk1_value,
1528 instance_pk2_value,
1529 instance_pk3_value,
1530 instance_pk4_value,
1531 instance_pk5_value
1532 FROM ibc_object_grant_groups
1533 WHERE object_grant_group_id = p_object_grant_group_id;
1534
1535 -- Cursor to apply/propagate changes for ObjectGrantGroups with same old grant group id
1536 CURSOR c_ogg_tree_update (p_object_grant_group_id NUMBER,
1537 p_grant_group_id NUMBER) IS
1538 SELECT ogg.*
1539 FROM ibc_object_grant_groups ogg
1540 WHERE grant_group_id = p_grant_group_id
1541 AND inherited_flag = 'Y'
1542 CONNECT BY PRIOR object_grant_group_id = inherited_from
1543 START WITH inherited_from = p_object_grant_group_id;
1544
1545 r_ogg c_ogg%ROWTYPE;
1546
1547 BEGIN
1548 SAVEPOINT svpt_override_permissions;
1549
1550 -- ******* Standard Begins ********
1551 -- Standard call to check for call compatibility.
1552 IF NOT FND_API.Compatible_API_Call (
1553 l_api_version,
1554 p_api_version,
1555 l_api_name,
1556 G_PKG_NAME)
1557 THEN
1558 x_return_status := FND_API.G_RET_STS_ERROR;
1559 FND_MSG_PUB.ADD;
1560 RAISE FND_API.G_EXC_ERROR;
1561 END IF;
1562 -- Initialize message list if p_init_msg_list is set to TRUE.
1563 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1564 FND_MSG_PUB.initialize;
1565 END IF;
1566
1567 -- Initialize API return status to success
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569
1570 -- Begin
1571
1572 IF IBC_DEBUG_PVT.debug_enabled THEN
1573 IBC_DEBUG_PVT.start_process(
1574 p_proc_type => 'PROCEDURE',
1575 p_proc_name => 'Override_Permissions',
1576 p_parms => IBC_DEBUG_PVT.make_parameter_list(
1577 p_tag => 'PARAMETERS',
1578 p_parms => JTF_VARCHAR2_TABLE_4000(
1579 'p_instance_object_id', p_instance_object_id,
1580 'p_instance_pk1_value', p_instance_pk1_value,
1581 'p_instance_pk2_value', p_instance_pk2_value,
1582 'p_instance_pk3_value', p_instance_pk3_value,
1583 'p_instance_pk4_value', p_instance_pk4_value,
1584 'p_instance_pk5_value', p_instance_pk5_value,
1585 'p_commit', p_commit,
1586 'p_api_version', p_api_version,
1587 'p_init_msg_list', p_init_msg_list
1588 )
1589 )
1590 );
1591 END IF;
1592
1593 -- Fetch object's grant group Info
1594 get_object_grant_group_info(
1595 p_instance_object_id => p_instance_object_id
1596 ,p_instance_pk1_value => p_instance_pk1_value
1597 ,p_instance_pk2_value => p_instance_pk2_value
1598 ,p_instance_pk3_value => p_instance_pk3_value
1599 ,p_instance_pk4_value => p_instance_pk4_value
1600 ,p_instance_pk5_value => p_instance_pk5_value
1601 ,x_rowid => l_object_grant_group_rowid
1602 ,x_object_grant_group_id => l_object_grant_group_id
1603 ,x_grant_group_id => l_grant_group_id
1604 ,x_inherited_flag => l_inherited_flag
1605 ,x_inherited_from => l_inherited_from
1606 ,x_inheritance_type => l_inheritance_type
1607 );
1608
1609 IF l_inheritance_type = 'FOLDER' AND
1610 l_inherited_flag = 'Y'
1611 THEN
1612
1613 OPEN c_ogg(l_inherited_from);
1614 FETCH c_ogg INTO r_ogg;
1615
1616 IF c_ogg%FOUND THEN
1617
1618 l_old_grant_group_id := l_grant_group_id;
1619
1620 -- Create Row in IBC_grant_groups
1621 SELECT ibc_grant_groups_s1.nextval
1622 INTO l_grant_group_id
1623 FROM dual;
1624 IBC_GRANT_GROUPS_PKG.insert_row(
1625 px_rowid => l_rowid
1626 ,p_grant_group_id => l_grant_group_id
1627 ,p_object_version_number => 1
1628 );
1629
1630 -- Update Row in IBC_object_grant_groups
1631 FOR r_data IN (SELECT object_grant_group_id,
1632 object_version_number,
1633 object_id,
1634 inherited_from,
1635 instance_pk1_value,
1636 instance_pk2_value,
1637 instance_pk3_value,
1638 instance_pk4_value,
1639 instance_pk5_value,
1640 inheritance_type
1641 FROM ibc_object_grant_groups
1642 WHERE ROWID = l_object_grant_group_rowid)
1643 LOOP
1644 IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
1645 p_object_grant_group_id => r_data.object_grant_group_id
1646 ,p_object_version_number => r_data.object_version_number
1647 ,p_grant_group_id => l_grant_group_id
1648 ,p_object_id => r_data.object_id
1649 ,p_inherited_flag => 'N'
1650 ,p_inherited_from => r_data.inherited_from
1651 ,p_instance_pk1_value => r_data.instance_pk1_value
1652 ,p_instance_pk2_value => r_data.instance_pk2_value
1653 ,p_instance_pk3_value => r_data.instance_pk3_value
1654 ,p_instance_pk4_value => r_data.instance_pk4_value
1655 ,p_instance_pk5_value => r_data.instance_pk5_value
1656 ,p_inheritance_type => r_data.inheritance_type
1657 );
1658 END LOOP;
1659
1660 IBC_DEBUG_PVT.debug_message('** l_object_grant_group_id:' || l_object_grant_group_id ||
1661 ' l_old_grant_group_id: ' || l_old_grant_group_id);
1662
1663 FOR r_tree_ogg IN c_ogg_tree_update (l_object_grant_group_id,
1664 l_old_grant_group_id)
1665 LOOP
1666 IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
1667 p_object_grant_group_id => r_tree_ogg.object_grant_group_id
1668 ,p_object_version_number => r_tree_ogg.object_version_number
1669 ,p_grant_group_id => l_grant_group_id
1670 ,p_object_id => r_tree_ogg.object_id
1671 ,p_inherited_flag => r_tree_ogg.inherited_flag
1672 ,p_inherited_from => r_tree_ogg.inherited_from
1673 ,p_instance_pk1_value => r_tree_ogg.instance_pk1_value
1674 ,p_instance_pk2_value => r_tree_ogg.instance_pk2_value
1675 ,p_instance_pk3_value => r_tree_ogg.instance_pk3_value
1676 ,p_instance_pk4_value => r_tree_ogg.instance_pk4_value
1677 ,p_instance_pk5_value => r_tree_ogg.instance_pk5_value
1678 ,p_inheritance_type => r_tree_ogg.inheritance_type
1679 );
1680 END LOOP;
1681
1682 -- Copy all rows From inherited from IBC_object_grant_groups
1683 FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
1684 object_id, permission_code, grantee_user_id,
1685 grantee_resource_id, grantee_resource_type,
1686 l_grant_group_id grant_group_id,
1687 action, grant_level + 1 grant_level, cascade_flag
1688 FROM ibc_grants
1689 WHERE grant_group_id = l_old_grant_group_id
1690 AND cascade_flag = IBC_UTILITIES_PVT.g_true)
1691 LOOP
1692 IBC_GRANTS_PKG.insert_row(
1693 PX_ROWID => l_rowid
1694 ,P_GRANT_ID => r_data.grant_id
1695 ,P_PERMISSION_CODE => r_data.permission_code
1696 ,P_GRANTEE_USER_ID => r_data.grantee_user_id
1697 ,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
1698 ,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
1699 ,P_GRANT_GROUP_ID => r_data.grant_group_id
1700 ,P_ACTION => r_data.action
1701 ,P_GRANT_LEVEL => r_data.grant_level
1702 ,P_CASCADE_FLAG => r_data.cascade_flag
1703 ,P_OBJECT_VERSION_NUMBER => 1
1704 ,P_OBJECT_ID => r_data.object_id
1705 );
1706 END LOOP;
1707
1708 END IF;
1709 CLOSE c_ogg;
1710
1711 END IF;
1712
1713 -- COMMIT?
1714 IF (p_commit = FND_API.g_true) THEN
1715 COMMIT;
1716 END IF;
1717
1718 -- Standard call to get message count and if count=1, get the message
1719 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1720 p_data => x_msg_data);
1721
1722 IF IBC_DEBUG_PVT.debug_enabled THEN
1723 IBC_DEBUG_PVT.end_process(
1724 IBC_DEBUG_PVT.make_parameter_list(
1725 p_tag => 'OUTPUT',
1726 p_parms => JTF_VARCHAR2_TABLE_4000(
1727 'x_return_status', x_return_status,
1728 'x_msg_count', x_msg_count,
1729 'x_msg_data', x_msg_data
1730 )
1731 )
1732 );
1733 END IF;
1734
1735 EXCEPTION
1736 WHEN FND_API.G_EXC_ERROR THEN
1737 ROLLBACK TO svpt_override_permissions;
1738 x_return_status := FND_API.G_RET_STS_ERROR;
1739 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1740 p_data => x_msg_data);
1741 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1742 P_API_NAME => L_API_NAME
1743 ,P_PKG_NAME => G_PKG_NAME
1744 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1745 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1746 ,P_SQLCODE => SQLCODE
1747 ,P_SQLERRM => SQLERRM
1748 ,X_MSG_COUNT => X_MSG_COUNT
1749 ,X_MSG_DATA => X_MSG_DATA
1750 ,X_RETURN_STATUS => X_RETURN_STATUS
1751 );
1752 IF IBC_DEBUG_PVT.debug_enabled THEN
1753 IBC_DEBUG_PVT.end_process(
1754 IBC_DEBUG_PVT.make_parameter_list(
1755 p_tag => 'OUTPUT',
1756 p_parms => JTF_VARCHAR2_TABLE_4000(
1757 'x_return_status', x_return_status,
1758 'x_msg_count', x_msg_count,
1759 'x_msg_data', x_msg_data
1760 )
1761 )
1762 );
1763 END IF;
1764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1765 ROLLBACK TO svpt_override_permissions;
1766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1767 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1768 p_data => x_msg_data);
1769 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1770 P_API_NAME => L_API_NAME
1771 ,P_PKG_NAME => G_PKG_NAME
1772 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1773 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1774 ,P_SQLCODE => SQLCODE
1775 ,P_SQLERRM => SQLERRM
1776 ,X_MSG_COUNT => X_MSG_COUNT
1777 ,X_MSG_DATA => X_MSG_DATA
1778 ,X_RETURN_STATUS => X_RETURN_STATUS
1779 );
1780 IF IBC_DEBUG_PVT.debug_enabled THEN
1781 IBC_DEBUG_PVT.end_process(
1782 IBC_DEBUG_PVT.make_parameter_list(
1783 p_tag => 'OUTPUT',
1784 p_parms => JTF_VARCHAR2_TABLE_4000(
1785 'x_return_status', x_return_status,
1786 'x_msg_count', x_msg_count,
1787 'x_msg_data', x_msg_data
1788 )
1789 )
1790 );
1791 END IF;
1792 WHEN OTHERS THEN
1793 ROLLBACK TO svpt_override_permissions;
1794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1795 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1796 THEN
1797 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1798 END IF;
1799 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1800 p_data => x_msg_data);
1801 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1802 P_API_NAME => L_API_NAME
1803 ,P_PKG_NAME => G_PKG_NAME
1804 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1805 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1806 ,P_SQLCODE => SQLCODE
1807 ,P_SQLERRM => SQLERRM
1808 ,X_MSG_COUNT => X_MSG_COUNT
1809 ,X_MSG_DATA => X_MSG_DATA
1810 ,X_RETURN_STATUS => X_RETURN_STATUS
1811 );
1812 IF IBC_DEBUG_PVT.debug_enabled THEN
1813 IBC_DEBUG_PVT.end_process(
1814 IBC_DEBUG_PVT.make_parameter_list(
1815 p_tag => 'OUTPUT',
1816 p_parms => JTF_VARCHAR2_TABLE_4000(
1817 'x_return_status', x_return_status,
1818 'x_msg_count', x_msg_count,
1819 'x_msg_data', x_msg_data
1820 )
1821 )
1822 );
1823 END IF;
1824 END override_permissions;
1825
1826 /*#
1827 * Grants a permission on a particular object instance (or contained objects)
1828 * to a user.
1829 *
1830 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
1831 * for this particular instance
1832 * @param p_instance_pk1_value value 1 for instance's primary key
1833 * @param p_instance_pk2_value value 2 for instance's primary key
1834 * @param p_instance_pk3_value value 3 for instance's primary key
1835 * @param p_instance_pk4_value value 4 for instance's primary key
1836 * @param p_instance_pk5_value value 5 for instance's primary key
1837 * @param p_action either ALLOW(permissions) or
1838 * RESTRICT (exclusions)
1839 * @param p_permission_object_id Object ID of object which permission is
1840 * being granted
1841 * @param p_permission_code Permission being granted
1842 * @param p_grantee_user_id User receiving permission, If not especified it
1843 * means ANYBODY
1844 * @param p_grantee_resource_id Resource Id
1845 * @param p_grantee_resource_type Resource Type. Resource receiving permission
1846 * if not especified it means ANYBODY
1847 * @param p_container_object_id ID for object definition id found in FND_OBJECTS
1848 * for the container
1849 * @param p_container_pk1_value value 1 for container's primary key
1850 * @param p_container_pk2_value value 2 for container's primary key
1851 * @param p_container_pk3_value value 3 for container's primary key
1852 * @param p_container_pk4_value value 4 for container's primary key
1853 * @param p_container_pk5_value value 5 for container's primary key
1854 * @param p_cascade_flag Indicates if permission should be carried over
1855 * to contained objects
1856 * @param p_commit Indicates whether to commit or not at the end
1857 * of procedure
1858 * @param p_api_version standard parm - API Version
1859 * @param p_init_msg_list standard parm - Initialize message list
1860 * @param x_return_status standard parm - Return Status
1861 * @param x_msg_count standard parm - Message Count
1862 * @param x_msg_data standard parm - Message Data
1863 *
1864 * @rep:displayname grant_permission
1865 *
1866 */
1867 PROCEDURE grant_permission(
1868 p_instance_object_id IN NUMBER
1869 ,p_instance_pk1_value IN VARCHAR2
1870 ,p_instance_pk2_value IN VARCHAR2
1871 ,p_instance_pk3_value IN VARCHAR2
1872 ,p_instance_pk4_value IN VARCHAR2
1873 ,p_instance_pk5_value IN VARCHAR2
1874 ,p_action IN VARCHAR2
1875 ,p_permission_object_id IN NUMBER
1876 ,p_permission_code IN VARCHAR2
1877 ,p_grantee_user_id IN NUMBER
1878 ,p_grantee_resource_id IN NUMBER
1879 ,p_grantee_resource_type IN VARCHAR2
1880 ,p_container_object_id IN NUMBER
1881 ,p_container_pk1_value IN VARCHAR2
1882 ,p_container_pk2_value IN VARCHAR2
1883 ,p_container_pk3_value IN VARCHAR2
1884 ,p_container_pk4_value IN VARCHAR2
1885 ,p_container_pk5_value IN VARCHAR2
1886 ,p_cascade_flag IN VARCHAR2
1887 ,p_commit IN VARCHAR2
1888 ,p_api_version IN NUMBER
1889 ,p_init_msg_list IN VARCHAR2
1890 ,x_return_status OUT NOCOPY VARCHAR2
1891 ,x_msg_count OUT NOCOPY NUMBER
1892 ,x_msg_data OUT NOCOPY VARCHAR2
1893 ) AS
1894 TYPE cursorType IS REF CURSOR;
1895 c_object_grant_group cursorType;
1896 c_chk_data cursorType;
1897 l_statement VARCHAR2(4096);
1898 l_rowid ROWID;
1899 l_grant_id NUMBER;
1900 l_dummy VARCHAR2(30);
1901 --******** local variable for standards **********
1902 l_api_name CONSTANT VARCHAR2(30) := 'establish_inheritance';
1903 l_api_version CONSTANT NUMBER := 1.0;
1904 -- Object Definition
1905 l_nbr_pk_cols NUMBER;
1906 l_fmt_col_lst VARCHAR2(4096);
1907 l_object_definition c_object%ROWTYPE;
1908 -- IBC_object_grant_groups
1909 l_object_grant_group_rowid ROWID;
1910 l_object_grant_group_id NUMBER;
1911 l_old_grant_group_id NUMBER;
1912 l_grant_group_id NUMBER;
1913 l_inherited_flag VARCHAR2(2);
1914 l_inherited_from NUMBER;
1915 l_inheritance_type VARCHAR2(30);
1916 -- IBC_object_grant_groups
1917 l_c_object_grant_group_rowid ROWID;
1918 l_c_object_grant_group_id NUMBER;
1919 l_c_grant_group_id NUMBER;
1920 l_c_inherited_flag VARCHAR2(2);
1921 l_c_inherited_from NUMBER;
1922 l_c_inheritance_type VARCHAR2(30);
1923
1924 -- Cursor to apply/propagate changes
1925 CURSOR c_object_grant_group_tree (p_object_grant_group_id NUMBER) IS
1926 SELECT LEVEL - 1 grant_level, ogg.*
1927 FROM ibc_object_grant_groups ogg
1928 CONNECT BY PRIOR object_grant_group_id = inherited_from
1929 AND p_cascade_flag = IBC_UTILITIES_PVT.g_true
1930 START WITH object_grant_group_id = p_object_grant_group_id;
1931
1932 -- Cursor to apply/propagate changes for ObjectGrantGroups with same old grant group id
1933 CURSOR c_ogg_tree_update (p_object_grant_group_id NUMBER,
1934 p_grant_group_id NUMBER) IS
1935 SELECT ogg.*
1936 FROM ibc_object_grant_groups ogg
1937 WHERE grant_group_id = p_grant_group_id
1938 AND inherited_flag = 'Y'
1939 CONNECT BY PRIOR object_grant_group_id = inherited_from
1940 AND p_cascade_flag = IBC_UTILITIES_PVT.g_true
1941 START WITH inherited_from = p_object_grant_group_id;
1942
1943
1944 -- Cursor to check if permission code belongs to permission_object_id
1945 CURSOR c_chk_permission_code(p_lookup_type VARCHAR2,
1946 p_permission_code VARCHAR2) IS
1947 SELECT 'X'
1948 FROM fnd_lookup_values
1949 WHERE lookup_type = p_lookup_type
1950 AND lookup_code = p_permission_code
1951 AND enabled_flag = 'Y'
1952 AND language = USERENV('lang')
1953 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
1954 AND NVL(end_date_active, SYSDATE);
1955
1956 BEGIN
1957 SAVEPOINT svpt_grant_permission;
1958 -- ******* Standard Begins ********
1959 -- Standard call to check for call compatibility.
1960 IF NOT FND_API.Compatible_API_Call (
1961 l_api_version,
1962 p_api_version,
1963 l_api_name,
1964 G_PKG_NAME)
1965 THEN
1966 x_return_status := FND_API.G_RET_STS_ERROR;
1967 FND_MSG_PUB.ADD;
1968 RAISE FND_API.G_EXC_ERROR;
1969 END IF;
1970 -- Initialize message list if p_init_msg_list is set to TRUE.
1971 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1972 FND_MSG_PUB.initialize;
1973 END IF;
1974
1975 -- Initialize API return status to success
1976 x_return_status := FND_API.G_RET_STS_SUCCESS;
1977
1978 -- Begin
1979
1980
1981 IF IBC_DEBUG_PVT.debug_enabled THEN
1982 IBC_DEBUG_PVT.start_process(
1983 p_proc_type => 'PROCEDURE',
1984 p_proc_name => 'Grant_Permission',
1985 p_parms => IBC_DEBUG_PVT.make_parameter_list(
1986 p_tag => 'PARAMETERS',
1987 p_parms => JTF_VARCHAR2_TABLE_4000(
1988 'p_instance_object_id', p_instance_object_id,
1989 'p_instance_pk1_value', p_instance_pk1_value,
1990 'p_instance_pk2_value', p_instance_pk2_value,
1991 'p_instance_pk3_value', p_instance_pk3_value,
1992 'p_instance_pk4_value', p_instance_pk4_value,
1993 'p_instance_pk5_value', p_instance_pk5_value,
1994 'p_action', p_action,
1995 'p_permission_object_id', p_permission_object_id,
1996 'p_permission_code', p_permission_code,
1997 'p_grantee_user_id', p_grantee_user_id,
1998 'p_grantee_resource_id', p_grantee_resource_id,
1999 'p_grantee_resource_type', p_grantee_resource_type,
2000 'p_container_object_id', p_container_object_id,
2001 'p_container_pk1_value', p_container_pk1_value,
2002 'p_container_pk2_value', p_container_pk2_value,
2003 'p_container_pk3_value', p_container_pk3_value,
2004 'p_container_pk4_value', p_container_pk4_value,
2005 'p_container_pk5_value', p_container_pk5_value,
2006 'p_cascade_flag', p_cascade_flag,
2007 'p_commit', p_commit,
2008 'p_api_version', p_api_version,
2009 'p_init_msg_list', p_init_msg_list
2010 )
2011 )
2012 );
2013
2014 END IF;
2015
2016
2017 -- Validate action
2018 IF p_action NOT IN ('ALLOW', 'RESTRICT') THEN
2019 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2020 FND_MESSAGE.Set_Name('IBC', 'INVALID_PERMISSION_ACTION');
2021 FND_MSG_PUB.ADD;
2022 END IF;
2023 RAISE FND_API.G_EXC_ERROR;
2024 END IF;
2025
2026 -- Validate permission code for a particular object
2027 OPEN c_chk_permission_code(get_perms_lookup_type(p_permission_object_id),
2028 p_permission_code);
2029 FETCH c_chk_permission_code INTO l_dummy;
2030 IF c_chk_permission_code%NOTFOUND THEN
2031 CLOSE c_chk_permission_code;
2032 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2033 FND_MESSAGE.Set_Name('IBC', 'INVALID_PERMISSION_FOR_OBJECT');
2034 FND_MESSAGE.Set_token('PERMISSION_CODE', p_permission_code);
2035 FND_MESSAGE.Set_token('PERMISSION_OBJECT_ID', p_permission_object_id);
2036 FND_MSG_PUB.ADD;
2037 END IF;
2038 RAISE FND_API.G_EXC_ERROR;
2039 END IF;
2040 CLOSE c_chk_permission_code;
2041
2042 -- Fetch object's grant group Info
2043 get_object_grant_group_info(
2044 p_instance_object_id => p_instance_object_id
2045 ,p_instance_pk1_value => p_instance_pk1_value
2046 ,p_instance_pk2_value => p_instance_pk2_value
2047 ,p_instance_pk3_value => p_instance_pk3_value
2048 ,p_instance_pk4_value => p_instance_pk4_value
2049 ,p_instance_pk5_value => p_instance_pk5_value
2050 ,x_rowid => l_object_grant_group_rowid
2051 ,x_object_grant_group_id => l_object_grant_group_id
2052 ,x_grant_group_id => l_grant_group_id
2053 ,x_inherited_flag => l_inherited_flag
2054 ,x_inherited_from => l_inherited_from
2055 ,x_inheritance_type => l_inheritance_type
2056 );
2057
2058 -- IF inheritance type is WSFOLDER then a grant is not allowed for this object
2059 -- it needs to be done at the container WORKSPACE
2060 IF l_inheritance_type = 'WSFOLDER' THEN
2061 FND_MESSAGE.Set_Name('IBC', 'IBC_WSFOLDER_NO_GRANT_ALLOWED');
2062 FND_MSG_PUB.ADD;
2063 RAISE FND_API.G_EXC_ERROR;
2064 END IF;
2065
2066 IF l_object_grant_group_id IS NULL OR
2067 l_inherited_flag = 'Y'
2068 THEN
2069 l_old_grant_group_id := l_grant_group_id;
2070 -- Create Row in IBC_grant_groups
2071 SELECT ibc_grant_groups_s1.nextval
2072 INTO l_grant_group_id
2073 FROM dual;
2074 IBC_GRANT_GROUPS_PKG.insert_row(
2075 px_rowid => l_rowid
2076 ,p_grant_group_id => l_grant_group_id
2077 ,p_object_version_number => 1
2078 );
2079 IF l_object_grant_group_id IS NULL THEN
2080 SELECT ibc_object_grant_groups_s1.nextval
2081 INTO l_object_grant_group_id
2082 FROM dual;
2083 IF p_container_object_id IS NOT NULL THEN
2084 -- Fetch object's grant group info for Container
2085 get_object_grant_group_info(
2086 p_instance_object_id => p_container_object_id
2087 ,p_instance_pk1_value => p_container_pk1_value
2088 ,p_instance_pk2_value => p_container_pk2_value
2089 ,p_instance_pk3_value => p_container_pk3_value
2090 ,p_instance_pk4_value => p_container_pk4_value
2091 ,p_instance_pk5_value => p_container_pk5_value
2092 ,x_rowid => l_c_object_grant_group_rowid
2093 ,x_object_grant_group_id => l_c_object_grant_group_id
2094 ,x_grant_group_id => l_c_grant_group_id
2095 ,x_inherited_flag => l_c_inherited_flag
2096 ,x_inherited_from => l_c_inherited_from
2097 ,x_inheritance_type => l_c_inheritance_type
2098 );
2099
2100 -- IF inheritance type is WSFOLDER then a grant is not allowed for this object
2101 -- it needs to be done at the container WORKSPACE
2102 IF l_c_inheritance_type IN ('WORKSPACE', 'WSFOLDER') THEN
2103 FND_MESSAGE.Set_Name('IBC', 'IBC_WSFOLDER_NO_GRANT_ALLOWED');
2104 FND_MSG_PUB.ADD;
2105 RAISE FND_API.G_EXC_ERROR;
2106 END IF;
2107
2108 -- Copy all rows from container object
2109 -- It doesn't check for cascading at this point
2110 FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
2111 object_id, permission_code, grantee_user_id,
2112 grantee_resource_id, grantee_resource_type,
2113 l_grant_group_id grant_group_id,
2114 action, grant_level + 1 grant_level, cascade_flag
2115 FROM ibc_grants
2116 WHERE grant_group_id = l_c_grant_group_id)
2117 LOOP
2118 IBC_GRANTS_PKG.insert_row(
2119 PX_ROWID => l_rowid
2120 ,P_GRANT_ID => r_data.grant_id
2121 ,P_PERMISSION_CODE => r_data.permission_code
2122 ,P_GRANTEE_USER_ID => r_data.grantee_user_id
2123 ,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
2124 ,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
2125 ,P_GRANT_GROUP_ID => r_data.grant_group_id
2126 ,P_ACTION => r_data.action
2127 ,P_GRANT_LEVEL => r_data.grant_level
2128 ,P_CASCADE_FLAG => r_data.cascade_flag
2129 ,P_OBJECT_VERSION_NUMBER => 1
2130 ,P_OBJECT_ID => r_data.object_id
2131 );
2132 END LOOP;
2133 END IF;
2134 -- Create Row in IBC_object_grant_groups
2135 IBC_OBJECT_GRANT_GROUPS_PKG.insert_row(
2136 px_rowid => l_rowid
2137 ,p_object_grant_group_id => l_object_grant_group_id
2138 ,p_object_version_number => 1
2139 ,p_grant_group_id => l_grant_group_id
2140 ,p_object_id => p_instance_object_id
2141 ,p_inherited_flag => 'N'
2142 ,p_inherited_from => l_c_object_grant_group_id
2143 ,p_instance_pk1_value => p_instance_pk1_value
2144 ,p_instance_pk2_value => p_instance_pk2_value
2145 ,p_instance_pk3_value => p_instance_pk3_value
2146 ,p_instance_pk4_value => p_instance_pk4_value
2147 ,p_instance_pk5_value => p_instance_pk5_value
2148 ,p_inheritance_type => l_c_inheritance_type
2149 );
2150
2151 ELSE
2152 -- Update Row in IBC_object_grant_groups
2153 FOR r_data IN (SELECT object_grant_group_id,
2154 object_version_number,
2155 object_id,
2156 inherited_from,
2157 instance_pk1_value,
2158 instance_pk2_value,
2159 instance_pk3_value,
2160 instance_pk4_value,
2161 instance_pk5_value,
2162 inheritance_type
2163 FROM ibc_object_grant_groups
2164 WHERE ROWID = l_object_grant_group_rowid)
2165 LOOP
2166 IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
2167 p_object_grant_group_id => r_data.object_grant_group_id
2168 ,p_object_version_number => r_data.object_version_number
2169 ,p_grant_group_id => l_grant_group_id
2170 ,p_object_id => r_data.object_id
2171 ,p_inherited_flag => 'N'
2172 ,p_inherited_from => r_data.inherited_from
2173 ,p_instance_pk1_value => r_data.instance_pk1_value
2174 ,p_instance_pk2_value => r_data.instance_pk2_value
2175 ,p_instance_pk3_value => r_data.instance_pk3_value
2176 ,p_instance_pk4_value => r_data.instance_pk4_value
2177 ,p_instance_pk5_value => r_data.instance_pk5_value
2178 ,p_inheritance_type => r_data.inheritance_type
2179 );
2180 END LOOP;
2181
2182 IBC_DEBUG_PVT.debug_message('** l_object_grant_group_id:' || l_object_grant_group_id ||
2183 ' l_old_grant_group_id: ' || l_old_grant_group_id);
2184
2185 FOR r_ogg IN c_ogg_tree_update (l_object_grant_group_id,
2186 l_old_grant_group_id)
2187 LOOP
2188 IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
2189 p_object_grant_group_id => r_ogg.object_grant_group_id
2190 ,p_object_version_number => r_ogg.object_version_number
2191 ,p_grant_group_id => l_grant_group_id
2192 ,p_object_id => r_ogg.object_id
2193 ,p_inherited_flag => r_ogg.inherited_flag
2194 ,p_inherited_from => r_ogg.inherited_from
2195 ,p_instance_pk1_value => r_ogg.instance_pk1_value
2196 ,p_instance_pk2_value => r_ogg.instance_pk2_value
2197 ,p_instance_pk3_value => r_ogg.instance_pk3_value
2198 ,p_instance_pk4_value => r_ogg.instance_pk4_value
2199 ,p_instance_pk5_value => r_ogg.instance_pk5_value
2200 ,p_inheritance_type => r_ogg.inheritance_type
2201 );
2202 END LOOP;
2203
2204 END IF;
2205 IF l_inherited_flag = 'Y' THEN
2206 -- Copy all rows From inherited from IBC_object_grant_groups
2207 FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
2208 object_id, permission_code, grantee_user_id,
2209 grantee_resource_id, grantee_resource_type,
2210 l_grant_group_id grant_group_id,
2211 action, grant_level + 1 grant_level, cascade_flag
2212 FROM ibc_grants
2213 WHERE grant_group_id = l_old_grant_group_id
2214 AND cascade_flag = IBC_UTILITIES_PVT.g_true)
2215 LOOP
2216 IBC_GRANTS_PKG.insert_row(
2217 PX_ROWID => l_rowid
2218 ,P_GRANT_ID => r_data.grant_id
2219 ,P_PERMISSION_CODE => r_data.permission_code
2220 ,P_GRANTEE_USER_ID => r_data.grantee_user_id
2221 ,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
2222 ,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
2223 ,P_GRANT_GROUP_ID => r_data.grant_group_id
2224 ,P_ACTION => r_data.action
2225 ,P_GRANT_LEVEL => r_data.grant_level
2226 ,P_CASCADE_FLAG => r_data.cascade_flag
2227 ,P_OBJECT_VERSION_NUMBER => 1
2228 ,P_OBJECT_ID => r_data.object_id
2229 );
2230 END LOOP;
2231 END IF;
2232 END IF;
2233
2234 -- Check if there is a row already with the same information at current level
2235 l_statement := ' SELECT ''X'' ' ||
2236 ' FROM ibc_grants ' ||
2237 ' WHERE object_id = :p_permission_object_id ' ||
2238 ' AND permission_code = :p_permission_code ' ||
2239 ' AND grant_group_id = :p_grant_group_id ' ||
2240 ' AND action = :p_action ' ||
2241 ' AND grant_level = 0 ';
2242 IF p_grantee_user_id IS NOT NULL THEN
2243 l_statement := l_statement ||
2244 ' AND grantee_user_id = :p_grantee_user_id ' ||
2245 ' AND grantee_resource_id IS NULL ' ||
2246 ' AND grantee_resource_type IS NULL ';
2247 OPEN c_chk_data FOR l_statement
2248 USING p_permission_object_id, p_permission_code,
2249 l_grant_group_id, p_action, p_grantee_user_id;
2250 ELSIF p_grantee_resource_id IS NOT NULL THEN
2251 l_statement := l_statement ||
2252 ' AND grantee_resource_id = :p_grantee_user_id ' ||
2253 ' AND grantee_resource_type = :p_grantee_resource_type ' ||
2254 ' AND grantee_user_id IS NULL ';
2255 OPEN c_chk_data FOR l_statement
2256 USING p_permission_object_id, p_permission_code,
2257 l_grant_group_id, p_action, p_grantee_resourcE_id, p_grantee_resource_type;
2258 ELSE
2259 l_statement := l_statement ||
2260 ' AND grantee_resource_id IS NULL ' ||
2261 ' AND grantee_resource_type IS NULL ' ||
2262 ' AND grantee_user_id IS NULL';
2263 OPEN c_chk_data FOR l_statement
2264 USING p_permission_object_id, p_permission_code,
2265 l_grant_group_id, p_action;
2266 END IF;
2267
2268 FETCH c_chk_data INTO l_dummy;
2269
2270 IF c_chk_data%NOTFOUND THEN
2271 -- Create and Propagate Row in IBC_GRANTS.
2272 FOR r_object_grant_group IN c_object_grant_group_tree(l_object_grant_group_id) LOOP
2273 --DBMS_OUTPUT.put_line(' inherited_flag:[' || r_object_grant_group.inherited_flag || ']' ||
2274 -- ' grant_level:['|| r_object_grant_group.grant_level || ']' ||
2275 -- ' inheritance_type:[' || r_object_grant_group.inheritance_type || ']');
2276 IF r_object_grant_group.inherited_flag = 'N' AND
2277 (r_object_grant_group.grant_level = 0 OR
2278 r_object_grant_group.inheritance_type = 'FULL')
2279 THEN
2280 SELECT ibc_grants_s1.nextval
2281 INTO l_grant_id
2282 FROM DUAL;
2283 IBC_GRANTS_PKG.insert_row(
2284 PX_ROWID => l_rowid
2285 ,P_GRANT_ID => l_grant_id
2286 ,P_PERMISSION_CODE => p_permission_code
2287 ,P_GRANTEE_USER_ID => p_grantee_user_id
2288 ,P_GRANTEE_RESOURCE_ID => p_grantee_resource_id
2289 ,P_GRANTEE_RESOURCE_TYPE => p_grantee_resource_type
2290 ,P_GRANT_GROUP_ID => r_object_grant_group.grant_group_id
2291 ,P_ACTION => p_action
2292 ,P_GRANT_LEVEL => r_object_grant_group.grant_level
2293 ,P_CASCADE_FLAG => p_cascade_flag
2294 ,P_OBJECT_VERSION_NUMBER => 1
2295 ,P_OBJECT_ID => p_permission_object_id
2296 );
2297 END IF;
2298 END LOOP;
2299 END IF;
2300
2301 CLOSE c_chk_data;
2302
2303 -- COMMIT?
2304 IF (p_commit = FND_API.g_true) THEN
2305 COMMIT;
2306 END IF;
2307
2308 -- Standard call to get message count and if count=1, get the message
2309 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2310 p_data => x_msg_data);
2311
2312 IF IBC_DEBUG_PVT.debug_enabled THEN
2313 IBC_DEBUG_PVT.end_process(
2314 IBC_DEBUG_PVT.make_parameter_list(
2315 p_tag => 'OUTPUT',
2316 p_parms => JTF_VARCHAR2_TABLE_4000(
2317 'x_return_status', x_return_status,
2318 'x_msg_count', x_msg_count,
2319 'x_msg_data', x_msg_data
2320 )
2321 )
2322 );
2323 END IF;
2324
2325 EXCEPTION
2326 WHEN FND_API.G_EXC_ERROR THEN
2327 ROLLBACK TO svpt_grant_permission;
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2330 p_data => x_msg_data);
2331 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2332 P_API_NAME => L_API_NAME
2333 ,P_PKG_NAME => G_PKG_NAME
2334 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2335 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2336 ,P_SQLCODE => SQLCODE
2337 ,P_SQLERRM => SQLERRM
2338 ,X_MSG_COUNT => X_MSG_COUNT
2339 ,X_MSG_DATA => X_MSG_DATA
2340 ,X_RETURN_STATUS => X_RETURN_STATUS
2341 );
2342 IF IBC_DEBUG_PVT.debug_enabled THEN
2343 IBC_DEBUG_PVT.end_process(
2344 IBC_DEBUG_PVT.make_parameter_list(
2345 p_tag => 'OUTPUT',
2346 p_parms => JTF_VARCHAR2_TABLE_4000(
2347 'x_return_status', x_return_status,
2348 'x_msg_count', x_msg_count,
2349 'x_msg_data', x_msg_data
2350 )
2351 )
2352 );
2353 END IF;
2354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2355 ROLLBACK TO svpt_grant_permission;
2356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2357 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2358 p_data => x_msg_data);
2359 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2360 P_API_NAME => L_API_NAME
2361 ,P_PKG_NAME => G_PKG_NAME
2362 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2363 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2364 ,P_SQLCODE => SQLCODE
2365 ,P_SQLERRM => SQLERRM
2366 ,X_MSG_COUNT => X_MSG_COUNT
2367 ,X_MSG_DATA => X_MSG_DATA
2368 ,X_RETURN_STATUS => X_RETURN_STATUS
2369 );
2370 IF IBC_DEBUG_PVT.debug_enabled THEN
2371 IBC_DEBUG_PVT.end_process(
2372 IBC_DEBUG_PVT.make_parameter_list(
2373 p_tag => 'OUTPUT',
2374 p_parms => JTF_VARCHAR2_TABLE_4000(
2375 'x_return_status', x_return_status,
2376 'x_msg_count', x_msg_count,
2377 'x_msg_data', x_msg_data
2378 )
2379 )
2380 );
2381 END IF;
2382 WHEN OTHERS THEN
2383 ROLLBACK TO svpt_grant_permission;
2384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2385 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2386 THEN
2387 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2388 END IF;
2389 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2390 p_data => x_msg_data);
2391 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2392 P_API_NAME => L_API_NAME
2393 ,P_PKG_NAME => G_PKG_NAME
2394 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
2395 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2396 ,P_SQLCODE => SQLCODE
2397 ,P_SQLERRM => SQLERRM
2398 ,X_MSG_COUNT => X_MSG_COUNT
2399 ,X_MSG_DATA => X_MSG_DATA
2400 ,X_RETURN_STATUS => X_RETURN_STATUS
2401 );
2402 IF IBC_DEBUG_PVT.debug_enabled THEN
2403 IBC_DEBUG_PVT.end_process(
2404 IBC_DEBUG_PVT.make_parameter_list(
2405 p_tag => 'OUTPUT',
2406 p_parms => JTF_VARCHAR2_TABLE_4000(
2407 'x_return_status', x_return_status,
2408 'x_msg_count', x_msg_count,
2409 'x_msg_data', x_msg_data
2410 )
2411 )
2412 );
2413 END IF;
2414 END grant_permission;
2415
2416 /*#
2417 * Grants a permission on a particular object instance (or contained objects)
2418 * to a user.
2419 *
2420 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
2421 * for this particular instance
2422 * @param p_instance_pk1_value value 1 for instance's primary key
2423 * @param p_instance_pk2_value value 2 for instance's primary key
2424 * @param p_instance_pk3_value value 3 for instance's primary key
2425 * @param p_instance_pk4_value value 4 for instance's primary key
2426 * @param p_instance_pk5_value value 5 for instance's primary key
2427 * @param p_action either ALLOW(permissions) or
2428 * RESTRICT (exclusions)
2429 * @param p_permission_object_id Object ID of object which permission is
2430 * being granted
2431 * @param p_permission_code Permission being granted
2432 * @param p_grantee_user_id User receiving permission, If not especified it
2433 * means ANYBODY
2434 * @param p_container_object_id ID for object definition id found in FND_OBJECTS
2435 * for the container
2436 * @param p_container_pk1_value value 1 for container's primary key
2437 * @param p_container_pk2_value value 2 for container's primary key
2438 * @param p_container_pk3_value value 3 for container's primary key
2439 * @param p_container_pk4_value value 4 for container's primary key
2440 * @param p_container_pk5_value value 5 for container's primary key
2441 * @param p_cascade_flag Indicates if permission should be carried over
2442 * to contained objects
2443 * @param p_commit Indicates whether to commit or not at the end
2444 * of procedure
2445 * @param p_api_version standard parm - API Version
2446 * @param p_init_msg_list standard parm - Initialize message list
2447 * @param x_return_status standard parm - Return Status
2448 * @param x_msg_count standard parm - Message Count
2449 * @param x_msg_data standard parm - Message Data
2450 *
2451 * @rep:displayname grant_permission
2452 *
2453 */
2454 PROCEDURE grant_permission(
2455 p_instance_object_id IN NUMBER
2456 ,p_instance_pk1_value IN VARCHAR2
2457 ,p_instance_pk2_value IN VARCHAR2
2458 ,p_instance_pk3_value IN VARCHAR2
2459 ,p_instance_pk4_value IN VARCHAR2
2460 ,p_instance_pk5_value IN VARCHAR2
2461 ,p_action IN VARCHAR2
2462 ,p_permission_object_id IN NUMBER
2463 ,p_permission_code IN VARCHAR2
2464 ,p_grantee_user_id IN NUMBER
2465 ,p_container_object_id IN NUMBER
2466 ,p_container_pk1_value IN VARCHAR2
2467 ,p_container_pk2_value IN VARCHAR2
2468 ,p_container_pk3_value IN VARCHAR2
2469 ,p_container_pk4_value IN VARCHAR2
2470 ,p_container_pk5_value IN VARCHAR2
2471 ,p_cascade_flag IN VARCHAR2
2472 ,p_commit IN VARCHAR2
2473 ,p_api_version IN NUMBER
2474 ,p_init_msg_list IN VARCHAR2
2475 ,x_return_status OUT NOCOPY VARCHAR2
2476 ,x_msg_count OUT NOCOPY NUMBER
2477 ,x_msg_data OUT NOCOPY VARCHAR2
2478 ) AS
2479 BEGIN
2480 grant_permission(
2481 p_instance_object_id => p_instance_object_id
2482 ,p_instance_pk1_value => p_instance_pk1_value
2483 ,p_instance_pk2_value => p_instance_pk2_value
2484 ,p_instance_pk3_value => p_instance_pk3_value
2485 ,p_instance_pk4_value => p_instance_pk4_value
2486 ,p_instance_pk5_value => p_instance_pk5_value
2487 ,p_action => p_action
2488 ,p_permission_object_id => p_permission_object_id
2489 ,p_permission_code => p_permission_code
2490 ,p_grantee_user_id => p_grantee_user_id
2491 ,p_grantee_resource_id => NULL
2492 ,p_grantee_resource_type => NULL
2493 ,p_container_object_id => p_container_object_id
2494 ,p_container_pk1_value => p_container_pk1_value
2495 ,p_container_pk2_value => p_container_pk2_value
2496 ,p_container_pk3_value => p_container_pk3_value
2497 ,p_container_pk4_value => p_container_pk4_value
2498 ,p_container_pk5_value => p_container_pk5_value
2499 ,p_cascade_flag => p_cascade_flag
2500 ,p_commit => p_commit
2501 ,p_api_version => p_api_version
2502 ,p_init_msg_list => p_init_msg_list
2503 ,x_return_status => x_return_status
2504 ,x_msg_count => x_msg_count
2505 ,x_msg_data => x_msg_data
2506 );
2507 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 RAISE;
2511 END grant_permission;
2512
2513 /*#
2514 * Grants a permission on a particular object instance
2515 * (or contained objects) to ANYBODY (if p_grantee_resource_id and
2516 * type are not passed) or a particular resource.
2517 *
2518 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
2519 * for this particular instance
2520 * @param p_instance_pk1_value value 1 for instance's primary key
2521 * @param p_instance_pk2_value value 2 for instance's primary key
2522 * @param p_instance_pk3_value value 3 for instance's primary key
2523 * @param p_instance_pk4_value value 4 for instance's primary key
2524 * @param p_instance_pk5_value value 5 for instance's primary key
2525 * @param p_action either ALLOW(permissions) or
2526 * RESTRICT (exclusions)
2527 * @param p_permission_object_id Object ID of object which permission is
2528 * being granted
2529 * @param p_permission_code Permission being granted
2530 * @param p_grantee_resource_id Resource Id
2531 * @param p_grantee_resource_type Resource Type. Resource receiving permission
2532 * if not especified it means ANYBODY
2533 * @param p_container_object_id ID for object definition id found in FND_OBJECTS
2534 * for the container
2535 * @param p_container_pk1_value value 1 for container's primary key
2536 * @param p_container_pk2_value value 2 for container's primary key
2537 * @param p_container_pk3_value value 3 for container's primary key
2538 * @param p_container_pk4_value value 4 for container's primary key
2539 * @param p_container_pk5_value value 5 for container's primary key
2540 * @param p_cascade_flag Indicates if permission should be carried over
2541 * to contained objects
2542 * @param p_commit Indicates whether to commit or not at the end
2543 * of procedure
2544 * @param p_api_version standard parm - API Version
2545 * @param p_init_msg_list standard parm - Initialize message list
2546 * @param x_return_status standard parm - Return Status
2547 * @param x_msg_count standard parm - Message Count
2548 * @param x_msg_data standard parm - Message Data
2549 *
2550 * @rep:displayname grant_permission
2551 *
2552 */
2553 PROCEDURE grant_permission(
2554 p_instance_object_id IN NUMBER
2555 ,p_instance_pk1_value IN VARCHAR2
2556 ,p_instance_pk2_value IN VARCHAR2
2557 ,p_instance_pk3_value IN VARCHAR2
2558 ,p_instance_pk4_value IN VARCHAR2
2559 ,p_instance_pk5_value IN VARCHAR2
2560 ,p_action IN VARCHAR2
2561 ,p_permission_object_id IN NUMBER
2562 ,p_permission_code IN VARCHAR2
2563 ,p_grantee_resource_id IN NUMBER
2564 ,p_grantee_resource_type IN VARCHAR2
2565 ,p_container_object_id IN NUMBER
2566 ,p_container_pk1_value IN VARCHAR2
2567 ,p_container_pk2_value IN VARCHAR2
2568 ,p_container_pk3_value IN VARCHAR2
2569 ,p_container_pk4_value IN VARCHAR2
2570 ,p_container_pk5_value IN VARCHAR2
2571 ,p_cascade_flag IN VARCHAR2
2572 ,p_commit IN VARCHAR2
2573 ,p_api_version IN NUMBER
2574 ,p_init_msg_list IN VARCHAR2
2575 ,x_return_status OUT NOCOPY VARCHAR2
2576 ,x_msg_count OUT NOCOPY NUMBER
2577 ,x_msg_data OUT NOCOPY VARCHAR2
2578 ) AS
2579 BEGIN
2580 grant_permission(
2581 p_instance_object_id => p_instance_object_id
2582 ,p_instance_pk1_value => p_instance_pk1_value
2583 ,p_instance_pk2_value => p_instance_pk2_value
2584 ,p_instance_pk3_value => p_instance_pk3_value
2585 ,p_instance_pk4_value => p_instance_pk4_value
2586 ,p_instance_pk5_value => p_instance_pk5_value
2587 ,p_action => p_action
2588 ,p_permission_object_id => p_permission_object_id
2589 ,p_permission_code => p_permission_code
2590 ,p_grantee_user_id => NULL
2591 ,p_grantee_resource_id => p_grantee_resource_id
2592 ,p_grantee_resource_type => p_grantee_resource_type
2593 ,p_container_object_id => p_container_object_id
2594 ,p_container_pk1_value => p_container_pk1_value
2595 ,p_container_pk2_value => p_container_pk2_value
2596 ,p_container_pk3_value => p_container_pk3_value
2597 ,p_container_pk4_value => p_container_pk4_value
2598 ,p_container_pk5_value => p_container_pk5_value
2599 ,p_cascade_flag => p_cascade_flag
2600 ,p_commit => p_commit
2601 ,p_api_version => p_api_version
2602 ,p_init_msg_list => p_init_msg_list
2603 ,x_return_status => x_return_status
2604 ,x_msg_count => x_msg_count
2605 ,x_msg_data => x_msg_data
2606 );
2607 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
2608 EXCEPTION
2609 WHEN OTHERS THEN
2610 RAISE;
2611 END grant_permission;
2612
2613 /*#
2614 * Revokes a especific permission already given, do not confuse this
2615 * with a grant to RESTRICT a permission.
2616 *
2617 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
2618 * for this particular instance
2619 * @param p_instance_pk1_value value 1 for instance's primary key
2620 * @param p_instance_pk2_value value 2 for instance's primary key
2621 * @param p_instance_pk3_value value 3 for instance's primary key
2622 * @param p_instance_pk4_value value 4 for instance's primary key
2623 * @param p_instance_pk5_value value 5 for instance's primary key
2624 * @param p_action either ALLOW(permissions) or
2625 * RESTRICT (exclusions)
2626 * @param p_permission_object_id Object ID of object to which permission was granted
2627 * @param p_permission_code Permission code
2628 * @param p_grantee_user_id User to which permission was originally granted,
2629 * if not especified it means ANYBODY
2630 * @param p_grantee_resource_id Resource to which permission was originally
2631 * granted, if not especified it means ANYBODY
2632 * @param p_grantee_resource_type Resource Type
2633 * @param p_commit Indicates whether to commit or not at the end
2634 * of procedure
2635 * @param p_api_version standard parm - API Version
2636 * @param p_init_msg_list standard parm - Initialize message list
2637 * @param x_return_status standard parm - Return Status
2638 * @param x_msg_count standard parm - Message Count
2639 * @param x_msg_data standard parm - Message Data
2640 *
2641 * @rep:displayname revoke_permission
2642 *
2643 */
2644 PROCEDURE revoke_permission(
2645 p_instance_object_id IN NUMBER
2646 ,p_instance_pk1_value IN VARCHAR2
2647 ,p_instance_pk2_value IN VARCHAR2
2648 ,p_instance_pk3_value IN VARCHAR2
2649 ,p_instance_pk4_value IN VARCHAR2
2650 ,p_instance_pk5_value IN VARCHAR2
2651 ,p_action IN VARCHAR2
2652 ,p_permission_object_id IN NUMBER
2653 ,p_permission_code IN VARCHAR2
2654 ,p_grantee_user_id IN NUMBER
2655 ,p_grantee_resource_id IN NUMBER
2656 ,p_grantee_resource_type IN VARCHAR2
2657 ,p_commit IN VARCHAR2
2658 ,p_api_version IN NUMBER
2659 ,p_init_msg_list IN VARCHAR2
2660 ,x_return_status OUT NOCOPY VARCHAR2
2661 ,x_msg_count OUT NOCOPY NUMBER
2662 ,x_msg_data OUT NOCOPY VARCHAR2
2663 ) AS
2664 l_statement VARCHAR2(4096);
2665 l_curr_statement VARCHAR2(4096);
2666 l_chk_statement VARCHAR2(4096);
2667 l_dummy VARCHAR2(2);
2668 l_count NUMBER;
2669 l_rowid ROWID;
2670 TYPE cursorType IS REF CURSOR;
2671 l_cursor cursorType;
2672 l_grant_id NUMBER;
2673 -- IBC_object_grant_groups
2674 l_object_grant_group_rowid ROWID;
2675 l_object_grant_group_id NUMBER;
2676 l_grant_group_id NUMBER;
2677 l_new_grant_group_id NUMBER;
2678 l_inherited_flag VARCHAR2(2);
2679 l_inherited_from NUMBER;
2680 l_inheritance_type VARCHAR2(30);
2681 --******** local variable for standards **********
2682 l_api_name CONSTANT VARCHAR2(30) := 'revoke_permission';
2683 l_api_version CONSTANT NUMBER := 1.0;
2684 -- Cursor to apply/propagate changes
2685 CURSOR c_object_grant_group (p_object_grant_group_id NUMBER) IS
2686 SELECT LEVEL - 1 grant_level, ogg.*
2687 FROM ibc_object_grant_groups ogg
2688 CONNECT BY PRIOR object_grant_group_id = inherited_from
2689 START WITH object_grant_group_id = p_object_grant_group_id
2690 ORDER BY 1 asc;
2691 -- Cursor to fetch a specific object's grant group
2692 CURSOR c_object_grant_group_by_id(p_object_grant_group_id NUMBER) IS
2693 SELECT *
2694 FROM ibc_object_grant_groups
2695 WHERE object_grant_group_id = p_object_grant_group_id;
2696 r_object_grant_group_by_id c_object_grant_group_by_id%ROWTYPE;
2697 -- Cursor to Check if object's grant group is still associated to a grant bundle id
2698 CURSOR c_object_grant_groups(p_grant_group_id NUMBER) IS
2699 SELECT 'X'
2700 FROM ibc_object_grant_groups
2701 WHERE grant_group_id = p_grant_group_id;
2702 -- Cursor for grants to check if there's need for branch of grants
2703 CURSOR c_grants(p_grant_group_id NUMBER, p_inheritance_type VARCHAR2) IS
2704 SELECT *
2705 FROM ibc_grants
2706 WHERE grant_group_id = p_grant_group_id
2707 AND (grant_level = 0 OR p_inheritance_type <> 'FULL');
2708 r_grant c_grants%ROWTYPE;
2709
2710 BEGIN
2711 SAVEPOINT svpt_revoke_permission;
2712 -- ******* Standard Begins ********
2713 -- Standard call to check for call compatibility.
2714 IF NOT FND_API.Compatible_API_Call (
2715 l_api_version,
2716 p_api_version,
2717 l_api_name,
2718 G_PKG_NAME)
2719 THEN
2720 x_return_status := FND_API.G_RET_STS_ERROR;
2721 FND_MSG_PUB.ADD;
2722 RAISE FND_API.G_EXC_ERROR;
2723 END IF;
2724 -- Initialize message list if p_init_msg_list is set to TRUE.
2725 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2726 FND_MSG_PUB.initialize;
2727 END IF;
2728
2729 -- Initialize API return status to success
2730 x_return_status := FND_API.G_RET_STS_SUCCESS;
2731
2732 -- Begin
2733
2734
2735 IF IBC_DEBUG_PVT.debug_enabled THEN
2736 IBC_DEBUG_PVT.start_process(
2737 p_proc_type => 'PROCEDURE',
2738 p_proc_name => 'Revoke_Permission',
2739 p_parms => IBC_DEBUG_PVT.make_parameter_list(
2740 p_tag => 'PARAMETERS',
2741 p_parms => JTF_VARCHAR2_TABLE_4000(
2742 'p_instance_object_id', p_instance_object_id,
2743 'p_instance_pk1_value', p_instance_pk1_value,
2744 'p_instance_pk2_value', p_instance_pk2_value,
2745 'p_instance_pk3_value', p_instance_pk3_value,
2746 'p_instance_pk4_value', p_instance_pk4_value,
2747 'p_instance_pk5_value', p_instance_pk5_value,
2748 'p_action', p_action,
2749 'p_permission_object_id', p_permission_object_id,
2750 'p_permission_code', p_permission_code,
2751 'p_grantee_user_id', p_grantee_user_id,
2752 'p_grantee_resource_id', p_grantee_resource_id,
2753 'p_grantee_resource_type', p_grantee_resource_type,
2754 'p_commit', p_commit,
2755 'p_api_version', p_api_version,
2756 'p_init_msg_list', p_init_msg_list
2757 )
2758 )
2759 );
2760 END IF;
2761
2762 -- Fetch object's grant group Info
2763 get_object_grant_group_info(
2764 p_instance_object_id => p_instance_object_id
2765 ,p_instance_pk1_value => p_instance_pk1_value
2766 ,p_instance_pk2_value => p_instance_pk2_value
2767 ,p_instance_pk3_value => p_instance_pk3_value
2768 ,p_instance_pk4_value => p_instance_pk4_value
2769 ,p_instance_pk5_value => p_instance_pk5_value
2770 ,x_rowid => l_object_grant_group_rowid
2771 ,x_object_grant_group_id => l_object_grant_group_id
2772 ,x_grant_group_id => l_grant_group_id
2773 ,x_inherited_flag => l_inherited_flag
2774 ,x_inherited_from => l_inherited_from
2775 ,x_inheritance_type => l_inheritance_type
2776 );
2777 IBC_DEBUG_PVT.debug_message('GRANT_GROUP_ROWID=' || l_object_grant_group_rowid);
2778
2779 IF l_object_grant_group_rowid IS NOT NULL THEN
2780
2781
2782 l_statement := ' SELECT grant_id ' ||
2783 ' FROM ibc_grants ' ||
2784 ' WHERE object_id = :p_permission_object_id ' ||
2785 ' AND permission_code = :p_permission_code ' ||
2786 ' AND action = :p_action ';
2787 IF p_grantee_user_id IS NOT NULL THEN
2788 l_statement := l_statement ||
2789 ' AND grantee_user_id = :p_grantee_user_id ' ||
2790 ' AND grantee_resource_id IS NULL ' ||
2791 ' AND grantee_resource_type IS NULL ';
2792 ELSIF p_grantee_resource_id IS NOT NULL THEN
2793 l_statement := l_statement ||
2794 ' AND grantee_resource_id = :p_grantee_user_id ' ||
2795 ' AND grantee_resource_type = :p_grantee_resource_type ' ||
2796 ' AND grantee_user_id IS NULL ';
2797 ELSE
2798 l_statement := l_statement ||
2799 ' AND grantee_resource_id IS NULL ' ||
2800 ' AND grantee_resource_type IS NULL ' ||
2801 ' AND grantee_user_id IS NULL ';
2802 END IF;
2803
2804 -- If inheriting Copy permissions from parent
2805 IF l_inherited_flag = 'Y' THEN
2806 l_chk_statement := l_statement ||
2807 ' AND grant_group_id = :p_grant_group_id ';
2808
2809 IF p_grantee_user_id IS NOT NULL THEN
2810 OPEN l_cursor FOR l_chk_statement
2811 USING p_permission_object_id, p_permission_code, p_action,
2812 p_grantee_user_id, l_grant_group_id;
2813 ELSIF p_grantee_resource_id IS NOT NULL THEN
2814 OPEN l_cursor FOR l_chk_statement
2815 USING p_permission_object_id, p_permission_code, p_action,
2816 p_grantee_resource_id, p_grantee_resource_type,
2817 l_grant_group_id;
2818 ELSE
2819 OPEN l_cursor FOR l_chk_statement
2820 USING p_permission_object_id, p_permission_code, p_action,
2821 l_grant_group_id;
2822 END IF;
2823
2824 FETCH l_cursor INTO l_grant_id;
2825 IF l_cursor%FOUND THEN
2826
2827 -- Create Row in IBC_grant_groups
2828 SELECT ibc_grant_groups_s1.nextval
2829 INTO l_new_grant_group_id
2830 FROM dual;
2831 IBC_GRANT_GROUPS_PKG.insert_row(
2832 px_rowid => l_rowid
2833 ,p_grant_group_id => l_new_grant_group_id
2834 ,p_object_version_number => 1
2835 );
2836
2837 -- Copy all rows From inherited from IBC_object_grant_groups
2838 FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
2839 object_id, permission_code, grantee_user_id,
2840 grantee_resource_id, grantee_resource_type,
2841 l_new_grant_group_id grant_group_id,
2842 action, grant_level + 1 grant_level, cascade_flag
2843 FROM ibc_grants
2844 WHERE grant_group_id = l_grant_group_id)
2845 LOOP
2846 IBC_GRANTS_PKG.insert_row(
2847 PX_ROWID => l_rowid
2848 ,P_GRANT_ID => r_data.grant_id
2849 ,P_PERMISSION_CODE => r_data.permission_code
2850 ,P_GRANTEE_USER_ID => r_data.grantee_user_id
2851 ,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
2852 ,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
2853 ,P_GRANT_GROUP_ID => r_data.grant_group_id
2854 ,P_ACTION => r_data.action
2855 ,P_GRANT_LEVEL => r_data.grant_level
2856 ,P_CASCADE_FLAG => r_data.cascade_flag
2857 ,P_OBJECT_VERSION_NUMBER => 1
2858 ,P_OBJECT_ID => r_data.object_id
2859 );
2860 END LOOP;
2861
2862 -- Update Row in IBC_object_grant_groups
2863 FOR r_data IN (SELECT object_grant_group_id,
2864 object_version_number,
2865 object_id,
2866 inherited_from,
2867 instance_pk1_value,
2868 instance_pk2_value,
2869 instance_pk3_value,
2870 instance_pk4_value,
2871 instance_pk5_value,
2872 inheritance_type
2873 FROM ibc_object_grant_groups
2874 WHERE ROWID = l_object_grant_group_rowid)
2875
2876 LOOP
2877 IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
2878 p_object_grant_group_id => r_data.object_grant_group_id
2879 ,p_object_version_number => r_data.object_version_number
2880 ,p_grant_group_id => l_new_grant_group_id
2881 ,p_object_id => r_data.object_id
2882 ,p_inherited_flag => 'N'
2883 ,p_inherited_from => r_data.inherited_from
2884 ,p_instance_pk1_value => r_data.instance_pk1_value
2885 ,p_instance_pk2_value => r_data.instance_pk2_value
2886 ,p_instance_pk3_value => r_data.instance_pk3_value
2887 ,p_instance_pk4_value => r_data.instance_pk4_value
2888 ,p_instance_pk5_value => r_data.instance_pk5_value
2889 ,p_inheritance_type => r_data.inheritance_type
2890 );
2891 END LOOP;
2892
2893 l_inherited_flag := 'N';
2894 l_grant_group_id := l_new_grant_group_id;
2895
2896 END IF;
2897 CLOSE l_cursor;
2898 END IF;
2899 -- Actual removal of grants
2900 FOR r_object_grant_group in c_object_grant_group(l_object_grant_group_id) LOOP
2901
2902 l_curr_statement := l_statement ||
2903 ' AND grant_group_id = :p_grant_group_id ';
2904 IF r_object_grant_group.inheritance_type = 'FULL' THEN
2905 l_curr_statement := l_statement ||
2906 ' AND grant_level = :p_grant_level';
2907 ELSIF r_object_grant_group.grant_level > 0 THEN
2908 EXIT;
2909 END IF;
2910 IBC_DEBUG_PVT.debug_message(l_curr_statement);
2911 l_count := 0;
2912
2913 IF p_grantee_user_id IS NOT NULL THEN
2914 IF r_object_grant_group.inheritance_type = 'FULL' THEN
2915 OPEN l_cursor FOR l_curr_statement
2916 USING p_permission_object_id, p_permission_code, p_action,
2917 p_grantee_user_id, r_object_grant_group.grant_group_id,
2918 r_object_grant_group.grant_level;
2919 ELSE
2920 OPEN l_cursor FOR l_curr_statement
2921 USING p_permission_object_id, p_permission_code, p_action,
2922 p_grantee_user_id, r_object_grant_group.grant_group_id;
2923 END IF;
2924 ELSIF p_grantee_resource_id IS NOT NULL THEN
2925 IF r_object_grant_group.inheritance_type = 'FULL' THEN
2926 OPEN l_cursor FOR l_curr_statement
2927 USING p_permission_object_id, p_permission_code, p_action,
2928 p_grantee_resource_id, p_grantee_resource_type,
2929 r_object_grant_group.grant_group_id,
2930 r_object_grant_group.grant_level;
2931 ELSE
2932 OPEN l_cursor FOR l_curr_statement
2933 USING p_permission_object_id, p_permission_code, p_action,
2934 p_grantee_resource_id, p_grantee_resource_type,
2935 r_object_grant_group.grant_group_id;
2936 END IF;
2937 ELSE
2938 IF r_object_grant_group.inheritance_type = 'FULL' THEN
2939 OPEN l_cursor FOR l_curr_statement
2940 USING p_permission_object_id, p_permission_code, p_action,
2941 r_object_grant_group.grant_group_id,
2942 r_object_grant_group.grant_level;
2943 ELSE
2944 OPEN l_cursor FOR l_curr_statement
2945 USING p_permission_object_id, p_permission_code, p_action,
2946 r_object_grant_group.grant_group_id;
2947 END IF;
2948 END IF;
2949
2950 LOOP
2951 IBC_DEBUG_PVT.debug_message('LOOP');
2952 FETCH l_cursor INTO l_grant_id;
2953 EXIT WHEN l_cursor%NOTFOUND;
2954 l_count := l_count + 1;
2955 IBC_GRANTS_PKG.delete_row(l_grant_id);
2956 END LOOP;
2957 CLOSE l_cursor;
2958 IF l_count > 0 THEN
2959 OPEN c_grants(r_object_grant_group.grant_group_id, r_object_grant_group.inheritance_type);
2960 FETCH c_grants into r_grant;
2961 IF c_grants%NOTFOUND THEN
2962 -- Remove grants if not grants at this level in case of FULL inheritance type
2963 FOR r_data IN (SELECT grant_id
2964 FROM ibc_grants
2965 WHERE grant_group_id = r_object_grant_group.grant_group_id)
2966 LOOP
2967 IBC_GRANTS_PKG.delete_row(r_data.grant_id);
2968 END LOOP;
2969 -- Fetch Parent
2970 OPEN c_object_grant_group_by_id(r_object_grant_group.inherited_from);
2971 FETCH c_object_grant_group_by_id INTO r_object_grant_group_by_id;
2972 CLOSE c_object_grant_group_by_id;
2973 -- Remove grant object's grant group if parent object_id
2974 -- it's not the same as current object_id
2975 -- it means no danger to break the inheritance
2976 IF r_object_grant_group_by_id.object_id <> r_object_grant_group.object_id THEN
2977 IBC_OBJECT_GRANT_GROUPS_PKG.delete_row(r_object_grant_group.object_grant_group_id);
2978 ELSIF r_object_grant_group.inherited_from IS NOT NULL AND
2979 r_object_grant_group.inheritance_type = 'FULL' -- Added to fix bug# 3392944
2980 THEN
2981 -- Update grant object's grant group to inherit from container
2982 SELECT grant_group_id
2983 INTO l_grant_group_id
2984 FROM ibc_object_grant_groups
2985 WHERE object_grant_group_id = r_object_grant_group.inherited_from;
2986 IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
2987 p_object_grant_group_id => r_object_grant_group.object_grant_group_id
2988 ,p_object_version_number => r_object_grant_group.object_version_number
2989 ,p_grant_group_id => l_grant_group_id
2990 ,p_object_id => r_object_grant_group.object_id
2991 ,p_inherited_flag => 'Y'
2992 ,p_inherited_from => r_object_grant_group.inherited_from
2993 ,p_instance_pk1_value => r_object_grant_group.instance_pk1_value
2994 ,p_instance_pk2_value => r_object_grant_group.instance_pk2_value
2995 ,p_instance_pk3_value => r_object_grant_group.instance_pk3_value
2996 ,p_instance_pk4_value => r_object_grant_group.instance_pk4_value
2997 ,p_instance_pk5_value => r_object_grant_group.instance_pk5_value
2998 ,p_inheritance_type => r_object_grant_group.inheritance_type
2999 );
3000 END IF;
3001 -- Remove Grant Bundle if not in use anymore
3002 OPEN c_object_grant_groups(r_object_grant_group.grant_group_id);
3003 FETCH c_object_grant_groups INTO l_dummy;
3004 IF c_object_grant_groups%NOTFOUND THEN
3005 IBC_GRANT_GROUPS_PKG.delete_row(r_object_grant_group.grant_group_id);
3006 END IF;
3007 CLOSE c_object_grant_groups;
3008 END IF;
3009 CLOSE c_grants;
3010 END IF;
3011 END LOOP;
3012 END IF;
3013 -- COMMIT?
3014 IF (p_commit = FND_API.g_true) THEN
3015 COMMIT;
3016 END IF;
3017
3018 -- Standard call to get message count and if count=1, get the message
3019 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3020 p_data => x_msg_data);
3021
3022 IF IBC_DEBUG_PVT.debug_enabled THEN
3023 IBC_DEBUG_PVT.end_process(
3024 IBC_DEBUG_PVT.make_parameter_list(
3025 p_tag => 'OUTPUT',
3026 p_parms => JTF_VARCHAR2_TABLE_4000(
3027 'x_return_status', x_return_status,
3028 'x_msg_count', x_msg_count,
3029 'x_msg_data', x_msg_data
3030 )
3031 )
3032 );
3033 END IF;
3034
3035 EXCEPTION
3036 WHEN FND_API.G_EXC_ERROR THEN
3037 ROLLBACK TO svpt_revoke_permission;
3038 x_return_status := FND_API.G_RET_STS_ERROR;
3039 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3040 p_data => x_msg_data);
3041 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3042 P_API_NAME => L_API_NAME
3043 ,P_PKG_NAME => G_PKG_NAME
3044 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3045 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3046 ,P_SQLCODE => SQLCODE
3047 ,P_SQLERRM => SQLERRM
3048 ,X_MSG_COUNT => X_MSG_COUNT
3049 ,X_MSG_DATA => X_MSG_DATA
3050 ,X_RETURN_STATUS => X_RETURN_STATUS
3051 );
3052 IF IBC_DEBUG_PVT.debug_enabled THEN
3053 IBC_DEBUG_PVT.end_process(
3054 IBC_DEBUG_PVT.make_parameter_list(
3055 p_tag => 'OUTPUT',
3056 p_parms => JTF_VARCHAR2_TABLE_4000(
3057 'x_return_status', x_return_status,
3058 'x_msg_count', x_msg_count,
3059 'x_msg_data', x_msg_data
3060 )
3061 )
3062 );
3063 END IF;
3064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3065 ROLLBACK TO svpt_revoke_permission;
3066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3067 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3068 p_data => x_msg_data);
3069 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3070 P_API_NAME => L_API_NAME
3071 ,P_PKG_NAME => G_PKG_NAME
3072 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3073 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3074 ,P_SQLCODE => SQLCODE
3075 ,P_SQLERRM => SQLERRM
3076 ,X_MSG_COUNT => X_MSG_COUNT
3077 ,X_MSG_DATA => X_MSG_DATA
3078 ,X_RETURN_STATUS => X_RETURN_STATUS
3079 );
3080 IF IBC_DEBUG_PVT.debug_enabled THEN
3081 IBC_DEBUG_PVT.end_process(
3082 IBC_DEBUG_PVT.make_parameter_list(
3083 p_tag => 'OUTPUT',
3084 p_parms => JTF_VARCHAR2_TABLE_4000(
3085 'x_return_status', x_return_status,
3086 'x_msg_count', x_msg_count,
3087 'x_msg_data', x_msg_data
3088 )
3089 )
3090 );
3091 END IF;
3092 WHEN OTHERS THEN
3093 ROLLBACK TO svpt_revoke_permission;
3094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3095 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3096 THEN
3097 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3098 END IF;
3099 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3100 p_data => x_msg_data);
3101 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3102 P_API_NAME => L_API_NAME
3103 ,P_PKG_NAME => G_PKG_NAME
3104 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
3105 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3106 ,P_SQLCODE => SQLCODE
3107 ,P_SQLERRM => SQLERRM
3108 ,X_MSG_COUNT => X_MSG_COUNT
3109 ,X_MSG_DATA => X_MSG_DATA
3110 ,X_RETURN_STATUS => X_RETURN_STATUS
3111 );
3112 IF IBC_DEBUG_PVT.debug_enabled THEN
3113 IBC_DEBUG_PVT.end_process(
3114 IBC_DEBUG_PVT.make_parameter_list(
3115 p_tag => 'OUTPUT',
3116 p_parms => JTF_VARCHAR2_TABLE_4000(
3117 'x_return_status', x_return_status,
3118 'x_msg_count', x_msg_count,
3119 'x_msg_data', x_msg_data
3120 )
3121 )
3122 );
3123 END IF;
3124 END revoke_permission;
3125
3126 /*#
3127 * Revokes a especific permission already given, do not confuse this
3128 * with a grant to RESTRICT a permission.
3129 *
3130 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
3131 * for this particular instance
3132 * @param p_instance_pk1_value value 1 for instance's primary key
3133 * @param p_instance_pk2_value value 2 for instance's primary key
3134 * @param p_instance_pk3_value value 3 for instance's primary key
3135 * @param p_instance_pk4_value value 4 for instance's primary key
3136 * @param p_instance_pk5_value value 5 for instance's primary key
3137 * @param p_action either ALLOW(permissions) or
3138 * RESTRICT (exclusions)
3139 * @param p_permission_object_id Object ID of object to which permission was granted
3140 * @param p_permission_code Permission code
3141 * @param p_grantee_user_id User to which permission was originally granted,
3142 * if not especified it means ANYBODY
3143 * @param p_commit Indicates whether to commit or not at the end
3144 * of procedure
3145 * @param p_api_version standard parm - API Version
3146 * @param p_init_msg_list standard parm - Initialize message list
3147 * @param x_return_status standard parm - Return Status
3148 * @param x_msg_count standard parm - Message Count
3149 * @param x_msg_data standard parm - Message Data
3150 *
3151 * @rep:displayname revoke_permission
3152 *
3153 */
3154 PROCEDURE revoke_permission(
3155 p_instance_object_id IN NUMBER
3156 ,p_instance_pk1_value IN VARCHAR2
3157 ,p_instance_pk2_value IN VARCHAR2
3158 ,p_instance_pk3_value IN VARCHAR2
3159 ,p_instance_pk4_value IN VARCHAR2
3160 ,p_instance_pk5_value IN VARCHAR2
3161 ,p_action IN VARCHAR2
3162 ,p_permission_object_id IN NUMBER
3163 ,p_permission_code IN VARCHAR2
3164 ,p_grantee_user_id IN NUMBER
3165 ,p_commit IN VARCHAR2
3166 ,p_api_version IN NUMBER
3167 ,p_init_msg_list IN VARCHAR2
3168 ,x_return_status OUT NOCOPY VARCHAR2
3169 ,x_msg_count OUT NOCOPY NUMBER
3170 ,x_msg_data OUT NOCOPY VARCHAR2
3171 ) AS
3172 BEGIN
3173 revoke_permission(
3174 p_instance_object_id => p_instance_object_id
3175 ,p_instance_pk1_value => p_instance_pk1_value
3176 ,p_instance_pk2_value => p_instance_pk2_value
3177 ,p_instance_pk3_value => p_instance_pk3_value
3178 ,p_instance_pk4_value => p_instance_pk4_value
3179 ,p_instance_pk5_value => p_instance_pk5_value
3180 ,p_action => p_action
3181 ,p_permission_object_id => p_permission_object_id
3182 ,p_permission_code => p_permission_code
3183 ,p_grantee_user_id => p_grantee_user_id
3184 ,p_grantee_resource_id => NULL
3185 ,p_grantee_resource_type => NULL
3186 ,p_commit => p_commit
3187 ,p_api_version => p_api_version
3188 ,p_init_msg_list => p_init_msg_list
3189 ,x_return_status => x_return_status
3190 ,x_msg_count => x_msg_count
3191 ,x_msg_data => x_msg_data
3192 );
3193 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
3194 EXCEPTION
3195 WHEN OTHERS THEN
3196 RAISE;
3197 END revoke_permission;
3198
3199 /*#
3200 * Revokes a especific permission already given, do not confuse this
3201 * with a grant to RESTRICT a permission.
3202 *
3203 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
3204 * for this particular instance
3205 * @param p_instance_pk1_value value 1 for instance's primary key
3206 * @param p_instance_pk2_value value 2 for instance's primary key
3207 * @param p_instance_pk3_value value 3 for instance's primary key
3208 * @param p_instance_pk4_value value 4 for instance's primary key
3209 * @param p_instance_pk5_value value 5 for instance's primary key
3210 * @param p_action either ALLOW(permissions) or
3211 * RESTRICT (exclusions)
3212 * @param p_permission_object_id Object ID of object to which permission was granted
3213 * @param p_permission_code Permission code
3214 * @param p_grantee_resource_id Resource to which permission was originally
3215 * granted, if not especified it means ANYBODY
3216 * @param p_grantee_resource_type Resource Type
3217 * @param p_commit Indicates whether to commit or not at the end
3218 * of procedure
3219 * @param p_api_version standard parm - API Version
3220 * @param p_init_msg_list standard parm - Initialize message list
3221 * @param x_return_status standard parm - Return Status
3222 * @param x_msg_count standard parm - Message Count
3223 * @param x_msg_data standard parm - Message Data
3224 *
3225 * @rep:displayname revoke_permission
3226 *
3227 */
3228 PROCEDURE revoke_permission(
3229 p_instance_object_id IN NUMBER
3230 ,p_instance_pk1_value IN VARCHAR2
3231 ,p_instance_pk2_value IN VARCHAR2
3232 ,p_instance_pk3_value IN VARCHAR2
3233 ,p_instance_pk4_value IN VARCHAR2
3234 ,p_instance_pk5_value IN VARCHAR2
3235 ,p_action IN VARCHAR2
3236 ,p_permission_object_id IN NUMBER
3237 ,p_permission_code IN VARCHAR2
3238 ,p_grantee_resource_id IN NUMBER
3239 ,p_grantee_resource_type IN VARCHAR2
3240 ,p_commit IN VARCHAR2
3241 ,p_api_version IN NUMBER
3242 ,p_init_msg_list IN VARCHAR2
3243 ,x_return_status OUT NOCOPY VARCHAR2
3244 ,x_msg_count OUT NOCOPY NUMBER
3245 ,x_msg_data OUT NOCOPY VARCHAR2
3246 ) AS
3247 BEGIN
3248 revoke_permission(
3249 p_instance_object_id => p_instance_object_id
3250 ,p_instance_pk1_value => p_instance_pk1_value
3251 ,p_instance_pk2_value => p_instance_pk2_value
3252 ,p_instance_pk3_value => p_instance_pk3_value
3253 ,p_instance_pk4_value => p_instance_pk4_value
3254 ,p_instance_pk5_value => p_instance_pk5_value
3255 ,p_action => p_action
3256 ,p_permission_object_id => p_permission_object_id
3257 ,p_permission_code => p_permission_code
3258 ,p_grantee_user_id => NULL
3259 ,p_grantee_resource_id => p_grantee_resource_id
3260 ,p_grantee_resource_type => p_grantee_resource_type
3261 ,p_commit => p_commit
3262 ,p_api_version => p_api_version
3263 ,p_init_msg_list => p_init_msg_list
3264 ,x_return_status => x_return_status
3265 ,x_msg_count => x_msg_count
3266 ,x_msg_data => x_msg_data
3267 );
3268 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
3269 EXCEPTION
3270 WHEN OTHERS THEN
3271 RAISE;
3272 END revoke_permission;
3273
3274 /*#
3275 * Checks whether an user has a particular permission on an
3276 * object instance
3277 *
3278 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
3279 * for this particular instance
3280 * @param p_instance_pk1_value value 1 for instance's primary key
3281 * @param p_instance_pk2_value value 2 for instance's primary key
3282 * @param p_instance_pk3_value value 3 for instance's primary key
3283 * @param p_instance_pk4_value value 4 for instance's primary key
3284 * @param p_instance_pk5_value value 5 for instance's primary key
3285 * @param p_permission_code Permission Code
3286 * @param p_container_pk1_value value 1 for container's primary key
3287 * @param p_container_pk2_value value 2 for container's primary key
3288 * @param p_container_pk3_value value 3 for container's primary key
3289 * @param p_container_pk4_value value 4 for container's primary key
3290 * @param p_container_pk5_value value 5 for container's primary key
3291 * @param p_current_user_id Current User Id
3292 * @return Whether user has (FND_API.g_true) or not (FND_API.g_false) such
3293 * permission
3294 *
3295 * @rep:displayname has_permission
3296 *
3297 */
3298 FUNCTION has_permission(
3299 p_instance_object_id IN NUMBER
3300 ,p_instance_pk1_value IN VARCHAR2
3301 ,p_instance_pk2_value IN VARCHAR2
3302 ,p_instance_pk3_value IN VARCHAR2
3303 ,p_instance_pk4_value IN VARCHAR2
3304 ,p_instance_pk5_value IN VARCHAR2
3305 ,p_permission_code IN VARCHAR2
3306 ,p_container_object_id IN NUMBER
3307 ,p_container_pk1_value IN VARCHAR2
3308 ,p_container_pk2_value IN VARCHAR2
3309 ,p_container_pk3_value IN VARCHAR2
3310 ,p_container_pk4_value IN VARCHAR2
3311 ,p_container_pk5_value IN VARCHAR2
3312 ,p_current_user_id IN NUMBER
3313 ) RETURN VARCHAR2 AS
3314 l_result VARCHAR2(30);
3315 -- Permission variables
3316 l_action VARCHAR2(30);
3317 -- IBC_object_grant_groups
3318 l_object_grant_group_rowid ROWID;
3319 l_object_grant_group_id NUMBER;
3320 l_grant_group_id NUMBER;
3321 l_inherited_flag VARCHAR2(2);
3322 l_inherited_from NUMBER;
3323 l_inheritance_type VARCHAR2(30);
3324 -- Cursor for a specific permission
3325 CURSOR c_permission(p_grant_group_id NUMBER
3326 ,p_inherited_flag VARCHAR2
3327 ,p_inheritance_type VARCHAR2)
3328 IS
3329 SELECT action
3330 FROM ibc_grants
3331 WHERE object_id = p_instance_object_id
3332 AND permission_code = p_permission_code
3333 AND grant_group_id = p_grant_group_id
3334 AND (p_inherited_flag = 'N'
3335 OR cascade_flag = IBC_UTILITIES_PVT.g_true
3336 )
3337 AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
3338 (IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
3339 grantee_resource_id, grantee_resource_type,
3340 p_current_user_id) = 'TRUE')
3341 )
3342 ORDER BY DECODE(p_inheritance_type, 'FOLDER', 0, 'HIDDEN-FOLDER', 0, grant_level) asc,
3343 DECODE(grantee_resource_type,
3344 'RESPONSIBILITY', 2,
3345 'RS_GROUP', 2,
3346 'GROUP', 2,
3347 DECODE(grantee_user_id, NULL, 3, 1)),
3348 action;
3349
3350 BEGIN
3351
3352 IF IBC_DEBUG_PVT.debug_enabled THEN
3353 IBC_DEBUG_PVT.start_process(
3354 p_proc_type => 'PROCEDURE',
3355 p_proc_name => 'has_permission',
3356 p_parms => IBC_DEBUG_PVT.make_parameter_list(
3357 p_tag => 'PARAMETERS',
3358 p_parms => JTF_VARCHAR2_TABLE_4000(
3359 'p_instance_object_id', p_instance_object_id,
3360 'p_instance_pk1_value', p_instance_pk1_value,
3361 'p_instance_pk2_value', p_instance_pk2_value,
3362 'p_instance_pk3_value', p_instance_pk3_value,
3363 'p_instance_pk4_value', p_instance_pk4_value,
3364 'p_instance_pk5_value', p_instance_pk5_value,
3365 'p_permission_code', p_permission_code,
3366 'p_container_object_id', p_container_object_id,
3367 'p_container_pk1_value', p_container_pk1_value,
3368 'p_container_pk2_value', p_container_pk2_value,
3369 'p_container_pk3_value', p_container_pk3_value,
3370 'p_container_pk4_value', p_container_pk4_value,
3371 'p_container_pk5_value', p_container_pk5_value,
3372 'p_current_user_id', p_current_user_id
3373 )
3374 )
3375 );
3376 END IF;
3377
3378
3379 IF Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999) = 'Y' THEN
3380 l_result := FND_API.g_false;
3381 -- Fetch object's grant group Info
3382 get_object_grant_group_info(
3383 p_instance_object_id => p_instance_object_id
3384 ,p_instance_pk1_value => p_instance_pk1_value
3385 ,p_instance_pk2_value => p_instance_pk2_value
3386 ,p_instance_pk3_value => p_instance_pk3_value
3387 ,p_instance_pk4_value => p_instance_pk4_value
3388 ,p_instance_pk5_value => p_instance_pk5_value
3389 ,x_rowid => l_object_grant_group_rowid
3390 ,x_object_grant_group_id => l_object_grant_group_id
3391 ,x_grant_group_id => l_grant_group_id
3392 ,x_inherited_flag => l_inherited_flag
3393 ,x_inherited_from => l_inherited_from
3394 ,x_inheritance_type => l_inheritance_type
3395 );
3396 IF l_object_grant_group_rowid IS NULL AND
3397 NVL(l_inheritance_type, 'FOLDER') <> 'HIDDEN-FOLDER'
3398 THEN
3399 -- Fetch object's grant group Info for container object
3400 get_object_grant_group_info(
3401 p_instance_object_id => p_container_object_id
3402 ,p_instance_pk1_value => p_container_pk1_value
3403 ,p_instance_pk2_value => p_container_pk2_value
3404 ,p_instance_pk3_value => p_container_pk3_value
3405 ,p_instance_pk4_value => p_container_pk4_value
3406 ,p_instance_pk5_value => p_container_pk5_value
3407 ,x_rowid => l_object_grant_group_rowid
3408 ,x_object_grant_group_id => l_object_grant_group_id
3409 ,x_grant_group_id => l_grant_group_id
3410 ,x_inherited_flag => l_inherited_flag
3411 ,x_inherited_from => l_inherited_from
3412 ,x_inheritance_type => l_inheritance_type
3413 );
3414 END IF;
3415 IF l_inheritance_type = 'HIDDEN-FOLDER' THEN
3416 l_result := FND_API.g_true;
3417 ELSE
3418 IF l_object_grant_group_rowid IS NOT NULL THEN
3419 OPEN c_permission(l_grant_group_id, l_inherited_flag, l_inheritance_type);
3420 FETCH c_permission INTO l_action;
3421 IF c_permission%FOUND AND l_action = 'ALLOW' THEN
3422 l_result := FND_API.g_true;
3423 END IF;
3424 CLOSE c_permission;
3425 END IF;
3426 END IF;
3427 ELSE
3428 -- Returns TRUE because IBC_USE_ACCESS_CONTROL profile is either
3429 -- not set or not set to 'Y'
3430 l_result := FND_API.g_true;
3431 END IF;
3432
3433 IF IBC_DEBUG_PVT.debug_enabled THEN
3434 IBC_DEBUG_PVT.end_process(
3435 IBC_DEBUG_PVT.make_parameter_list(
3436 p_tag => 'OUTPUT',
3437 p_parms => JTF_VARCHAR2_TABLE_4000(
3438 'RESULT', l_result
3439 )
3440 )
3441 );
3442 END IF;
3443
3444 RETURN l_result;
3445
3446 EXCEPTION
3447 WHEN OTHERS THEN
3448 l_result := FND_API.g_false;
3449
3450 IF IBC_DEBUG_PVT.debug_enabled THEN
3451 IBC_DEBUG_PVT.end_process(
3452 IBC_DEBUG_PVT.make_parameter_list(
3453 p_tag => 'OUTPUT',
3454 p_parms => JTF_VARCHAR2_TABLE_4000(
3455 'RESULT', l_result
3456 )
3457 )
3458 );
3459 END IF;
3460
3461 RETURN l_result;
3462 END has_permission;
3463
3464 /*#
3465 * Returns the list of permissions a user has on an object instance
3466 * as a string (comma separated and bracket delimited)
3467 *
3468 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
3469 * for this particular instance
3470 * @param p_instance_pk1_value value 1 for instance's primary key
3471 * @param p_instance_pk2_value value 2 for instance's primary key
3472 * @param p_instance_pk3_value value 3 for instance's primary key
3473 * @param p_instance_pk4_value value 4 for instance's primary key
3474 * @param p_instance_pk5_value value 5 for instance's primary key
3475 * @param p_container_object_id ID for container. Found in FND_OBJECTS
3476 * @param p_container_pk1_value value 1 for container's primary key
3477 * @param p_container_pk2_value value 2 for container's primary key
3478 * @param p_container_pk3_value value 3 for container's primary key
3479 * @param p_container_pk4_value value 4 for container's primary key
3480 * @param p_container_pk5_value value 5 for container's primary key
3481 * @param p_current_user_id Current User Id
3482 *
3483 * @rep:displayname get_permissions_as_string
3484 *
3485 */
3486 FUNCTION get_permissions_as_string(
3487 p_instance_object_id IN NUMBER
3488 ,p_instance_pk1_value IN VARCHAR2
3489 ,p_instance_pk2_value IN VARCHAR2
3490 ,p_instance_pk3_value IN VARCHAR2
3491 ,p_instance_pk4_value IN VARCHAR2
3492 ,p_instance_pk5_value IN VARCHAR2
3493 ,p_container_object_id IN NUMBER
3494 ,p_container_pk1_value IN VARCHAR2
3495 ,p_container_pk2_value IN VARCHAR2
3496 ,p_container_pk3_value IN VARCHAR2
3497 ,p_container_pk4_value IN VARCHAR2
3498 ,p_container_pk5_value IN VARCHAR2
3499 ,p_current_user_id IN NUMBER
3500 ) RETURN VARCHAR2 AS
3501 l_result VARCHAR2(4096);
3502 l_perms_lookup_type VARCHAR2(30);
3503 -- Permission variables
3504 l_action VARCHAR2(30);
3505 -- IBC_object_grant_groups
3506 l_object_grant_group_rowid ROWID;
3507 l_object_grant_group_id NUMBER;
3508 l_grant_group_id NUMBER;
3509 l_inherited_flag VARCHAR2(2);
3510 l_inherited_from NUMBER;
3511 l_inheritance_type VARCHAR2(30);
3512 -- Cursor for "ALLOW"'s permission
3513 CURSOR c_permission(p_grant_group_id NUMBER
3514 ,p_inherited_flag VARCHAR2)
3515 IS
3516 SELECT permission_code
3517 FROM ibc_grants a0
3518 WHERE object_id = p_instance_object_id
3519 AND grant_group_id = p_grant_group_id
3520 AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
3521 (IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
3522 grantee_resource_id, grantee_resource_type,
3523 p_current_user_id) = 'TRUE')
3524 )
3525 AND action = 'ALLOW'
3526 AND (p_inherited_flag = 'N'
3527 OR cascade_flag = IBC_UTILITIES_PVT.g_true
3528 )
3529 AND NOT EXISTS (
3530 SELECT 'x'
3531 FROM ibc_grants a1
3532 WHERE a1.grant_group_id = a0.grant_group_id
3533 AND a1.object_id = a0.object_id
3534 AND a1.permission_code = a0.permission_code
3535 AND a1.action = 'RESTRICT'
3536 -- Precedence User:1 Resp/Group:2 Global:3
3537 -- Lowest takes precedence
3538 AND DECODE(a1.grantee_resource_type,
3539 'RESPONSIBILITY', 2,
3540 'RS_GROUP', 2,
3541 'GROUP', 2,
3542 DECODE(a1.grantee_user_id, NULL, 3, 1))
3543 <
3544 DECODE(a0.grantee_resource_type,
3545 'RESPONSIBILITY', 2,
3546 'RS_GROUP', 2,
3547 'GROUP', 2,
3548 DECODE(a0.grantee_user_id, NULL, 3, 1))
3549 AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
3550 (IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
3551 grantee_resource_id, grantee_resource_type,
3552 p_current_user_id) = 'TRUE')
3553 )
3554 )
3555 ORDER BY grant_level asc, grantee_resource_id asc;
3556 BEGIN
3557
3558 IF IBC_DEBUG_PVT.debug_enabled THEN
3559 IBC_DEBUG_PVT.start_process(
3560 p_proc_type => 'PROCEDURE',
3561 p_proc_name => 'Get_Permissions_As_String',
3562 p_parms => IBC_DEBUG_PVT.make_parameter_list(
3563 p_tag => 'PARAMETERS',
3564 p_parms => JTF_VARCHAR2_TABLE_4000(
3565 'p_instance_object_id', p_instance_object_id,
3566 'p_instance_pk1_value', p_instance_pk1_value,
3567 'p_instance_pk2_value', p_instance_pk2_value,
3568 'p_instance_pk3_value', p_instance_pk3_value,
3569 'p_instance_pk4_value', p_instance_pk4_value,
3570 'p_instance_pk5_value', p_instance_pk5_value,
3571 'p_container_object_id', p_container_object_id,
3572 'p_container_pk1_value', p_container_pk1_value,
3573 'p_container_pk2_value', p_container_pk2_value,
3574 'p_container_pk3_value', p_container_pk3_value,
3575 'p_container_pk4_value', p_container_pk4_value,
3576 'p_container_pk5_value', p_container_pk5_value,
3577 'p_current_user_id', p_current_user_id
3578 )
3579 )
3580 );
3581 END IF;
3582
3583 l_result := NULL;
3584
3585 -- Fetch object's grant group Info
3586 get_object_grant_group_info(
3587 p_instance_object_id => p_instance_object_id
3588 ,p_instance_pk1_value => p_instance_pk1_value
3589 ,p_instance_pk2_value => p_instance_pk2_value
3590 ,p_instance_pk3_value => p_instance_pk3_value
3591 ,p_instance_pk4_value => p_instance_pk4_value
3592 ,p_instance_pk5_value => p_instance_pk5_value
3593 ,x_rowid => l_object_grant_group_rowid
3594 ,x_object_grant_group_id => l_object_grant_group_id
3595 ,x_grant_group_id => l_grant_group_id
3596 ,x_inherited_flag => l_inherited_flag
3597 ,x_inherited_from => l_inherited_from
3598 ,x_inheritance_type => l_inheritance_type
3599 );
3600
3601 IF Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999) = 'Y' AND
3602 NVL(l_inheritance_type, 'FOLDER') <> 'HIDDEN-FOLDER'
3603 THEN
3604 IF l_object_grant_group_rowid IS NULL THEN
3605 -- Fetch object's grant group Info for container object
3606 get_object_grant_group_info(
3607 p_instance_object_id => p_container_object_id
3608 ,p_instance_pk1_value => p_container_pk1_value
3609 ,p_instance_pk2_value => p_container_pk2_value
3610 ,p_instance_pk3_value => p_container_pk3_value
3611 ,p_instance_pk4_value => p_container_pk4_value
3612 ,p_instance_pk5_value => p_container_pk5_value
3613 ,x_rowid => l_object_grant_group_rowid
3614 ,x_object_grant_group_id => l_object_grant_group_id
3615 ,x_grant_group_id => l_grant_group_id
3616 ,x_inherited_flag => l_inherited_flag
3617 ,x_inherited_from => l_inherited_from
3618 ,x_inheritance_type => l_inheritance_type
3619 );
3620 END IF;
3621 IF l_object_grant_group_rowid IS NOT NULL THEN
3622 FOR r_permission IN c_permission(l_grant_group_id, l_inherited_flag) LOOP
3623 IF l_result IS NULL OR
3624 INSTR(l_result, '[' || r_permission.permission_code || ']') = 0
3625 THEN
3626 l_result := l_result || '[' || r_permission.permission_code || ']';
3627 END IF;
3628 END LOOP;
3629 END IF;
3630 ELSE
3631 -- No Profile set (or set to N) for Using Data security
3632 -- Returning the whole list of permissions for specific object
3633 l_perms_lookup_type := get_perms_lookup_type(p_instance_object_id);
3634 FOR r_permission IN (SELECT lookup_code
3635 FROM fnd_lookup_values
3636 WHERE lookup_type = l_perms_lookup_type
3637 AND enabled_flag = 'Y'
3638 AND language = USERENV('lang')
3639 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
3640 AND NVL(end_date_active, SYSDATE))
3641 LOOP
3642 IF l_result IS NULL OR
3643 INSTR(l_result, '[' || r_permission.lookup_code || ']') = 0
3644 THEN
3645 l_result := l_result || '[' || r_permission.lookup_code || ']';
3646 END IF;
3647 END LOOP;
3648 END IF;
3649
3650 IF IBC_DEBUG_PVT.debug_enabled THEN
3651 IBC_DEBUG_PVT.end_process(
3652 IBC_DEBUG_PVT.make_parameter_list(
3653 p_tag => 'OUTPUT',
3654 p_parms => JTF_VARCHAR2_TABLE_4000(
3655 'RESULT', l_result
3656 )
3657 )
3658 );
3659 END IF;
3660
3661
3662 RETURN l_result;
3663 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
3664 EXCEPTION
3665 WHEN OTHERS THEN
3666 IF IBC_DEBUG_PVT.debug_enabled THEN
3667 IBC_DEBUG_PVT.end_process(
3668 IBC_DEBUG_PVT.make_parameter_list(
3669 p_tag => 'OUTPUT',
3670 p_parms => JTF_VARCHAR2_TABLE_4000(
3671 'RESULT', l_result,
3672 'EXCEPTION', '****EXCEPTION:' || SQLERRM
3673 )
3674 )
3675 );
3676 END IF;
3677 RAISE;
3678 END get_permissions_as_string;
3679
3680 /*#
3681 * Returns the list of permissions a user has on an object instance
3682 *
3683 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
3684 * for this particular instance
3685 * @param p_instance_pk1_value value 1 for instance's primary key
3686 * @param p_instance_pk2_value value 2 for instance's primary key
3687 * @param p_instance_pk3_value value 3 for instance's primary key
3688 * @param p_instance_pk4_value value 4 for instance's primary key
3689 * @param p_instance_pk5_value value 5 for instance's primary key
3690 * @param p_container_object_id ID for container. Found in FND_OBJECTS
3691 * @param p_container_pk1_value value 1 for container's primary key
3692 * @param p_container_pk2_value value 2 for container's primary key
3693 * @param p_container_pk3_value value 3 for container's primary key
3694 * @param p_container_pk4_value value 4 for container's primary key
3695 * @param p_container_pk5_value value 5 for container's primary key
3696 * @param p_current_user_id Current User Id
3697 * @param x_permission_tbl Output pl/sql table containing all
3698 * different permission codes.
3699 * @param p_api_version standard parm - API Version
3700 * @param p_init_msg_list standard parm - Initialize message list
3701 * @param x_return_status standard parm - Return Status
3702 * @param x_msg_count standard parm - Message Count
3703 * @param x_msg_data standard parm - Message Data
3704 *
3705 * @rep:displayname get_permissions
3706 *
3707 */
3708 PROCEDURE get_permissions(
3709 p_instance_object_id IN NUMBER
3710 ,p_instance_pk1_value IN VARCHAR2
3711 ,p_instance_pk2_value IN VARCHAR2
3712 ,p_instance_pk3_value IN VARCHAR2
3713 ,p_instance_pk4_value IN VARCHAR2
3714 ,p_instance_pk5_value IN VARCHAR2
3715 ,p_container_object_id IN NUMBER
3716 ,p_container_pk1_value IN VARCHAR2
3717 ,p_container_pk2_value IN VARCHAR2
3718 ,p_container_pk3_value IN VARCHAR2
3719 ,p_container_pk4_value IN VARCHAR2
3720 ,p_container_pk5_value IN VARCHAR2
3721 ,p_current_user_id IN NUMBER
3722 ,x_permission_tbl OUT NOCOPY jtf_varchar2_table_100
3723 ,p_api_version IN NUMBER
3724 ,p_init_msg_list IN VARCHAR2
3725 ,x_return_status OUT NOCOPY VARCHAR2
3726 ,x_msg_count OUT NOCOPY NUMBER
3727 ,x_msg_data OUT NOCOPY VARCHAR2
3728 ) AS
3729 TYPE t_vc100_tbl IS TABLE OF VARCHAR2(100)
3730 INDEX BY BINARY_INTEGER;
3731 l_result t_vc100_tbl;
3732 l_count NUMBER;
3733 l_perms_lookup_type VARCHAR2(30);
3734 --******** local variable for standards **********
3735 l_api_name CONSTANT VARCHAR2(30) := 'get_permissions';
3736 l_api_version CONSTANT NUMBER := 1.0;
3737 -- Permission variables
3738 l_action VARCHAR2(30);
3739 l_add BOOLEAN;
3740 -- IBC_object_grant_groups
3741 l_object_grant_group_rowid ROWID;
3742 l_object_grant_group_id NUMBER;
3743 l_grant_group_id NUMBER;
3744 l_inherited_flag VARCHAR2(2);
3745 l_inherited_from NUMBER;
3746 l_inheritance_type VARCHAR2(30);
3747 -- Cursor for "ALLOW"'s permission
3748 CURSOR c_permission(p_grant_group_id NUMBER
3749 ,p_inherited_flag VARCHAR2)
3750 IS
3751 SELECT permission_code
3752 FROM ibc_grants a0
3753 WHERE object_id = p_instance_object_id
3754 AND grant_group_id = p_grant_group_id
3755 AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
3756 (IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
3757 grantee_resource_id, grantee_resource_type,
3758 p_current_user_id) = 'TRUE')
3759 )
3760 AND action = 'ALLOW'
3761 AND (p_inherited_flag = 'N'
3762 OR cascade_flag = IBC_UTILITIES_PVT.g_true
3763 )
3764 AND NOT EXISTS (
3765 SELECT 'x'
3766 FROM ibc_grants a1
3767 WHERE a1.grant_group_id = a0.grant_group_id
3768 AND a1.object_id = a0.object_id
3769 AND a1.permission_code = a0.permission_code
3770 AND a1.action = 'RESTRICT'
3771 -- Precedence User:1 Resp/Group:2 Global:3
3772 -- Lowest takes precedence
3773 AND DECODE(a1.grantee_resource_type,
3774 'RESPONSIBILITY', 2,
3775 'RS_GROUP', 2,
3776 'GROUP', 2,
3777 DECODE(a1.grantee_user_id, NULL, 3, 1))
3778 <
3779 DECODE(a0.grantee_resource_type,
3780 'RESPONSIBILITY', 2,
3781 'RS_GROUP', 2,
3782 'GROUP', 2,
3783 DECODE(a0.grantee_user_id, NULL, 3, 1))
3784 AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
3785 (IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
3786 grantee_resource_id, grantee_resource_type,
3787 p_current_user_id) = 'TRUE')
3788 )
3789 )
3790 ORDER BY grant_level asc, grantee_resource_id asc;
3791 BEGIN
3792 -- ******* Standard Begins ********
3793
3794 -- Standard call to check for call compatibility.
3795 IF NOT FND_API.Compatible_API_Call (
3796 l_api_version,
3797 p_api_version,
3798 l_api_name,
3799 G_PKG_NAME)
3800 THEN
3801 x_return_status := FND_API.G_RET_STS_ERROR;
3802 FND_MSG_PUB.ADD;
3803 RAISE FND_API.G_EXC_ERROR;
3804 END IF;
3805 -- Initialize message list if p_init_msg_list is set to TRUE.
3806 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3807 FND_MSG_PUB.initialize;
3808 END IF;
3809
3810 -- Initialize API return status to success
3811 x_return_status := FND_API.G_RET_STS_SUCCESS;
3812
3813 -- Begin
3814
3815 l_count := 0;
3816
3817 -- Fetch object's grant group Info
3818 get_object_grant_group_info(
3819 p_instance_object_id => p_instance_object_id
3820 ,p_instance_pk1_value => p_instance_pk1_value
3821 ,p_instance_pk2_value => p_instance_pk2_value
3822 ,p_instance_pk3_value => p_instance_pk3_value
3823 ,p_instance_pk4_value => p_instance_pk4_value
3824 ,p_instance_pk5_value => p_instance_pk5_value
3825 ,x_rowid => l_object_grant_group_rowid
3826 ,x_object_grant_group_id => l_object_grant_group_id
3827 ,x_grant_group_id => l_grant_group_id
3828 ,x_inherited_flag => l_inherited_flag
3829 ,x_inherited_from => l_inherited_from
3830 ,x_inheritance_type => l_inheritance_type
3831 );
3832
3833 IF Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999) = 'Y' AND
3834 NVL(l_inheritance_type, 'FOLDER') <> 'HIDDEN-FOLDER'
3835 THEN
3836 IF l_object_grant_group_rowid IS NULL THEN
3837 -- Fetch object's grant group Info for container object
3838 get_object_grant_group_info(
3839 p_instance_object_id => p_container_object_id
3840 ,p_instance_pk1_value => p_container_pk1_value
3841 ,p_instance_pk2_value => p_container_pk2_value
3842 ,p_instance_pk3_value => p_container_pk3_value
3843 ,p_instance_pk4_value => p_container_pk4_value
3844 ,p_instance_pk5_value => p_container_pk5_value
3845 ,x_rowid => l_object_grant_group_rowid
3846 ,x_object_grant_group_id => l_object_grant_group_id
3847 ,x_grant_group_id => l_grant_group_id
3848 ,x_inherited_flag => l_inherited_flag
3849 ,x_inherited_from => l_inherited_from
3850 ,x_inheritance_type => l_inheritance_type
3851 );
3852 END IF;
3853 IF l_object_grant_group_rowid IS NOT NULL THEN
3854 FOR r_permission IN c_permission(l_grant_group_id, l_inherited_flag) LOOP
3855 l_add := true;
3856 FOR ind IN 1..l_result.COUNT LOOP
3857 IF l_result(ind) = r_permission.permission_code THEN
3858 l_add := false;
3859 EXIT;
3860 END IF;
3861 END LOOP;
3862 IF l_add THEN
3863 l_count := l_count + 1;
3864 l_result(l_count) := r_permission.permission_code;
3865 END IF;
3866 END LOOP;
3867 END IF;
3868 ELSE
3869 -- No Profile set (or set to N) for Using Data security
3870 -- Returning the whole list of permissions for specific object
3871 l_perms_lookup_type := get_perms_lookup_type(p_instance_object_id);
3872 FOR r_permission IN (SELECT lookup_code
3873 FROM fnd_lookup_values
3874 WHERE lookup_type = l_perms_lookup_type
3875 AND enabled_flag = 'Y'
3876 AND language = USERENV('lang')
3877 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
3878 AND NVL(end_date_active, SYSDATE))
3879 LOOP
3880 l_count := l_count + 1;
3881 l_result(l_count) := r_permission.lookup_code;
3882 END LOOP;
3883 END IF;
3884
3885 IF l_count > 0 THEN
3886 x_permission_tbl := JTF_VARCHAR2_TABLE_100();
3887 x_permission_tbl.extend(l_count);
3888 FOR I IN 1..l_count LOOP
3889 x_permission_tbl(I) := l_result(I);
3890 END LOOP;
3891 END IF;
3892
3893 -- Standard call to get message count and if count=1, get the message
3894 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3895 p_data => x_msg_data);
3896 EXCEPTION
3897 WHEN FND_API.G_EXC_ERROR THEN
3898 x_return_status := FND_API.G_RET_STS_ERROR;
3899 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3900 p_data => x_msg_data);
3901 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3902 P_API_NAME => L_API_NAME
3903 ,P_PKG_NAME => G_PKG_NAME
3904 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3905 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3906 ,P_SQLCODE => SQLCODE
3907 ,P_SQLERRM => SQLERRM
3908 ,X_MSG_COUNT => X_MSG_COUNT
3909 ,X_MSG_DATA => X_MSG_DATA
3910 ,X_RETURN_STATUS => X_RETURN_STATUS
3911 );
3912 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3914 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3915 p_data => x_msg_data);
3916 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3917 P_API_NAME => L_API_NAME
3918 ,P_PKG_NAME => G_PKG_NAME
3919 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3920 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3921 ,P_SQLCODE => SQLCODE
3922 ,P_SQLERRM => SQLERRM
3923 ,X_MSG_COUNT => X_MSG_COUNT
3924 ,X_MSG_DATA => X_MSG_DATA
3925 ,X_RETURN_STATUS => X_RETURN_STATUS
3926 );
3927 WHEN OTHERS THEN
3928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3929 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3930 THEN
3931 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3932 END IF;
3933 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3934 p_data => x_msg_data);
3935 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3936 P_API_NAME => L_API_NAME
3937 ,P_PKG_NAME => G_PKG_NAME
3938 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
3939 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3940 ,P_SQLCODE => SQLCODE
3941 ,P_SQLERRM => SQLERRM
3942 ,X_MSG_COUNT => X_MSG_COUNT
3943 ,X_MSG_DATA => X_MSG_DATA
3944 ,X_RETURN_STATUS => X_RETURN_STATUS
3945 );
3946 END get_permissions;
3947
3948 -- Utility proc to add an entry in a user table
3949 -- ignoring duplicate entries.
3950 PROCEDURE Add_to_user_table(
3951 p_user_id IN NUMBER
3952 ,p_user_id_tbl IN OUT NOCOPY t_user_id_tbl
3953 ) IS
3954 l_add BOOLEAN;
3955 BEGIN
3956 l_add := TRUE;
3957 FOR I IN 1..p_user_id_tbl.COUNT LOOP
3958 IF p_user_id_tbl(I) = p_user_id THEN
3959 l_add := FALSE;
3960 EXIT;
3961 END IF;
3962 END LOOP;
3963 IF l_add THEN
3964 p_user_id_tbl(p_user_id_tbl.COUNT + 1) := p_user_id;
3965 END IF;
3966 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
3967 EXCEPTION
3968 WHEN OTHERS THEN
3969 RAISE;
3970 END Add_to_user_table;
3971
3972 -- Utility proc populate a user table with userids from
3973 -- a resource.
3974 PROCEDURE Create_User_Table(
3975 p_resource_id IN NUMBER
3976 ,p_resource_type IN VARCHAR2
3977 ,p_user_id_tbl IN OUT NOCOPY t_user_id_tbl
3978 ) IS
3979
3980 l_resource_type VARCHAR2(30);
3981 l_user_id NUMBER;
3982
3983 CURSOR c_resource(p_resource_id NUMBER) IS
3984 SELECT resource_type
3985 FROM jtf_rs_all_resources_vl
3986 WHERE resource_id = p_resource_id;
3987
3988 CURSOR c_grp_members(p_resource_id NUMBER) IS
3989 SELECT group_id group_id, resource_id group_resource_id, 'INDIVIDUAL' resource_type
3990 FROM jtf_rs_group_members
3991 WHERE group_id = p_resource_id
3992 AND delete_flag = 'N'
3993 UNION
3994 SELECT rgm.group_id group_id, rgr.group_id group_resource_id, 'GROUP' resource_type
3995 FROM jtf_rs_group_members rgm, jtf_rs_grp_relations rgr
3996 WHERE rgm.group_id = rgr.related_group_id
3997 AND rgm.group_id = p_resource_id
3998 AND rgm.delete_flag = 'N'
3999 AND rgr.delete_flag = 'N';
4000
4001 CURSOR c_resp_users(p_resp_id IN NUMBER) IS
4002 SELECT user_id
4003 FROM fnd_user_resp_groups
4004 WHERE responsibility_id = p_resp_id;
4005
4006 CURSOR c_user_id(p_resource_id IN NUMBER) IS
4007 SELECT user_id
4008 FROM jtf_rs_resource_extns
4009 WHERE resource_id = p_resource_id;
4010
4011 BEGIN
4012 IF p_resource_type IS NULL THEN
4013 OPEN c_resource(p_resource_id);
4014 FETCH c_resource INTO l_resource_type;
4015 CLOSE c_resource;
4016 ELSE
4017 l_resource_type := RTRIM(p_resource_type);
4018 END IF;
4019 IF l_resource_type IN ('GROUP', 'RS_GROUP') THEN
4020 FOR rec_member IN c_grp_members(p_resource_id) LOOP
4021 Create_User_Table(p_resource_id => rec_member.group_resource_id,
4022 p_resource_type => rec_member.resource_type,
4023 p_user_id_tbl => p_user_id_tbl);
4024 END LOOP;
4025 ELSIF l_resource_type = 'RESPONSIBILITY' THEN
4026 FOR r_resp_user IN c_resp_users(p_resource_id) LOOP
4027 Add_to_user_table(r_resp_user.user_id, p_user_id_tbl);
4028 END LOOP;
4029 ELSE
4030 OPEN c_user_id(p_resource_id);
4031 FETCH c_user_id INTO l_user_id;
4032 IF c_user_id%FOUND AND l_user_id IS NOT NULL THEN
4033 Add_to_user_table(l_user_id, p_user_id_tbl);
4034 END IF;
4035 CLOSE c_user_id;
4036 END IF;
4037 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
4038 EXCEPTION
4039 WHEN OTHERS THEN
4040 RAISE;
4041 END Create_User_Table;
4042
4043 /*#
4044 * Procedure to obtain a list of users which has a particular
4045 * permission on a object's instance. The result is returned comma
4046 * separated.
4047 *
4048 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
4049 * for this particular instance
4050 * @param p_instance_pk1_value value 1 for instance's primary key
4051 * @param p_instance_pk2_value value 2 for instance's primary key
4052 * @param p_instance_pk3_value value 3 for instance's primary key
4053 * @param p_instance_pk4_value value 4 for instance's primary key
4054 * @param p_instance_pk5_value value 5 for instance's primary key
4055 * @param p_permission_code Permission Code
4056 * @param p_container_pk1_value value 1 for container's primary key
4057 * @param p_container_pk2_value value 2 for container's primary key
4058 * @param p_container_pk3_value value 3 for container's primary key
4059 * @param p_container_pk4_value value 4 for container's primary key
4060 * @param p_container_pk5_value value 5 for container's primary key
4061 * @param p_include_global whether to include "global" user in the list
4062 * @param p_global_value Value to be used as "global" user, by default
4063 * it is 'All'.
4064 * @param x_usernames Output string containing all users with
4065 * permission on object's instance
4066 * @param p_api_version standard parm - API Version
4067 * @param p_init_msg_list standard parm - Initialize message list
4068 * @param x_return_status standard parm - Return Status
4069 * @param x_msg_count standard parm - Message Count
4070 * @param x_msg_data standard parm - Message Data
4071 *
4072 * @rep:displayname get_grantee_usernames
4073 *
4074 */
4075 PROCEDURE get_grantee_usernames(
4076 p_instance_object_id IN NUMBER
4077 ,p_instance_pk1_value IN VARCHAR2
4078 ,p_instance_pk2_value IN VARCHAR2
4079 ,p_instance_pk3_value IN VARCHAR2
4080 ,p_instance_pk4_value IN VARCHAR2
4081 ,p_instance_pk5_value IN VARCHAR2
4082 ,p_permission_code IN VARCHAR2
4083 ,p_container_object_id IN NUMBER
4084 ,p_container_pk1_value IN VARCHAR2
4085 ,p_container_pk2_value IN VARCHAR2
4086 ,p_container_pk3_value IN VARCHAR2
4087 ,p_container_pk4_value IN VARCHAR2
4088 ,p_container_pk5_value IN VARCHAR2
4089 ,p_include_global IN VARCHAR2
4090 ,p_global_value IN VARCHAR2
4091 ,x_usernames OUT NOCOPY VARCHAR2
4092 ,p_api_version IN NUMBER
4093 ,p_init_msg_list IN VARCHAR2
4094 ,x_return_status OUT NOCOPY VARCHAR2
4095 ,x_msg_count OUT NOCOPY NUMBER
4096 ,x_msg_data OUT NOCOPY VARCHAR2
4097 ) IS
4098
4099 l_user_id_tbl t_user_id_tbl;
4100
4101 l_user_name VARCHAR2(30);
4102
4103 l_result VARCHAR2(4096);
4104
4105 -- Variable to know if a grant ALLOW was given to everybody
4106 -- not used for the moment, but could be used in the future.
4107 l_granted_to_all VARCHAR2(1) := FND_API.g_false;
4108
4109 -- IBC_object_grant_groups
4110 l_object_grant_group_rowid ROWID;
4111 l_object_grant_group_id NUMBER;
4112 l_grant_group_id NUMBER;
4113 l_inherited_flag VARCHAR2(2);
4114 l_inherited_from NUMBER;
4115 l_inheritance_type VARCHAR2(30);
4116
4117 --******** local variable for standards **********
4118 l_api_name CONSTANT VARCHAR2(30) := 'get_grantee_usernames';
4119 l_api_version CONSTANT NUMBER := 1.0;
4120
4121 CURSOR c_user_name(p_user_id NUMBER)
4122 IS SELECT user_name
4123 FROM fnd_user
4124 WHERE user_id = p_user_id;
4125
4126 CURSOR c_base_grants(p_object_id NUMBER,
4127 p_grant_group_id NUMBER,
4128 p_permission_code VARCHAR2)
4129 IS SELECT action,
4130 permission_code,
4131 grant_level,
4132 grant_group_id,
4133 grantee_user_id,
4134 grantee_resource_id,
4135 grantee_resource_type
4136 FROM ibc_grants a0
4137 WHERE object_id = p_object_id
4138 AND grant_group_id = p_grant_group_id
4139 AND permission_code = p_permission_code
4140 AND action = 'ALLOW'
4141 ORDER BY grant_level;
4142
4143
4144 BEGIN
4145 -- ******* Standard Begins ********
4146
4147 -- Standard call to check for call compatibility.
4148 IF NOT FND_API.Compatible_API_Call (
4149 l_api_version,
4150 p_api_version,
4151 l_api_name,
4152 G_PKG_NAME)
4153 THEN
4154 x_return_status := FND_API.G_RET_STS_ERROR;
4155 FND_MSG_PUB.ADD;
4156 RAISE FND_API.G_EXC_ERROR;
4157 END IF;
4158 -- Initialize message list if p_init_msg_list is set to TRUE.
4159 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4160 FND_MSG_PUB.initialize;
4161 END IF;
4162
4163 -- Initialize API return status to success
4164 x_return_status := FND_API.G_RET_STS_SUCCESS;
4165
4166 -- Begin
4167
4168 IF IBC_DEBUG_PVT.debug_enabled THEN
4169 IBC_DEBUG_PVT.start_process(
4170 p_proc_type => 'PROCEDURE',
4171 p_proc_name => l_api_name,
4172 p_parms => IBC_DEBUG_PVT.make_parameter_list(
4173 p_tag => 'PARAMETERS',
4174 p_parms => JTF_VARCHAR2_TABLE_4000(
4175 'p_instance_object_id', p_instance_object_id,
4176 'p_instance_pk1_value', p_instance_pk1_value,
4177 'p_instance_pk2_value', p_instance_pk2_value,
4178 'p_instance_pk3_value', p_instance_pk3_value,
4179 'p_instance_pk4_value', p_instance_pk4_value,
4180 'p_instance_pk5_value', p_instance_pk5_value,
4181 'p_permission_code', p_permission_code,
4182 'p_container_object_id', p_container_object_id,
4183 'p_container_pk1_value', p_container_pk1_value,
4184 'p_container_pk2_value', p_container_pk2_value,
4185 'p_container_pk3_value', p_container_pk3_value,
4186 'p_container_pk4_value', p_container_pk4_value,
4187 'p_container_pk5_value', p_container_pk5_value
4188 )
4189 )
4190 );
4191 END IF;
4192
4193
4194 l_result := NULL;
4195 l_granted_to_all := FND_API.g_false;
4196
4197 get_object_grant_group_info(
4198 p_instance_object_id => p_instance_object_id
4199 ,p_instance_pk1_value => p_instance_pk1_value
4200 ,p_instance_pk2_value => p_instance_pk2_value
4201 ,p_instance_pk3_value => p_instance_pk3_value
4202 ,p_instance_pk4_value => p_instance_pk4_value
4203 ,p_instance_pk5_value => p_instance_pk5_value
4204 ,x_rowid => l_object_grant_group_rowid
4205 ,x_object_grant_group_id => l_object_grant_group_id
4206 ,x_grant_group_id => l_grant_group_id
4207 ,x_inherited_flag => l_inherited_flag
4208 ,x_inherited_from => l_inherited_from
4209 ,x_inheritance_type => l_inheritance_type
4210 );
4211 IF l_object_grant_group_rowid IS NULL THEN
4212 -- Fetch object's grant group Info for container object
4213 get_object_grant_group_info(
4214 p_instance_object_id => p_container_object_id
4215 ,p_instance_pk1_value => p_container_pk1_value
4216 ,p_instance_pk2_value => p_container_pk2_value
4217 ,p_instance_pk3_value => p_container_pk3_value
4218 ,p_instance_pk4_value => p_container_pk4_value
4219 ,p_instance_pk5_value => p_container_pk5_value
4220 ,x_rowid => l_object_grant_group_rowid
4221 ,x_object_grant_group_id => l_object_grant_group_id
4222 ,x_grant_group_id => l_grant_group_id
4223 ,x_inherited_flag => l_inherited_flag
4224 ,x_inherited_from => l_inherited_from
4225 ,x_inheritance_type => l_inheritance_type
4226 );
4227 END IF;
4228
4229 FOR r_base_grants IN c_base_grants(p_instance_object_id, l_grant_group_id, p_permission_code) LOOP
4230 IF r_base_grants.grantee_user_id IS NULL AND
4231 r_base_grants.grantee_resource_id IS NOT NULL
4232 THEN
4233 Create_User_Table(p_resource_id => r_base_grants.grantee_resource_id,
4234 p_resource_type => r_base_grants.grantee_resource_type,
4235 p_user_id_tbl => l_user_id_tbl);
4236 ELSIF r_base_grants.grantee_user_id IS NOT NULL THEN
4237 Add_to_user_table(r_base_grants.grantee_user_id, l_user_id_tbl);
4238 ELSIF r_base_grants.grantee_resource_id IS NULL THEN
4239 l_granted_to_all := FND_API.g_true;
4240 END IF;
4241 END LOOP;
4242 -- Set output result
4243 FOR I IN 1..l_user_id_tbl.COUNT LOOP
4244 -- Check permission under a specific user
4245 -- checking permission is commented out as now user list contains mix of all i.e. group,
4246 -- responsibility users and individual users, checking permission will filter out
4247 -- users coming via group and responsibility causing not notification received.
4248 -- so commenting if and end if for same
4249 /*
4250 IF IBC_DATA_SECURITY_PVT.has_permission(p_instance_object_id => p_instance_object_id,
4251 p_instance_pk1_value => p_instance_pk1_value,
4252 p_instance_pk2_value => p_instance_pk2_value,
4253 p_instance_pk3_value => p_instance_pk3_value,
4254 p_instance_pk4_value => p_instance_pk4_value,
4255 p_instance_pk5_value => p_instance_pk5_value,
4256 p_permission_code => p_permission_code,
4257 p_container_object_id => p_container_object_id,
4258 p_container_pk1_value => p_container_pk1_value,
4259 p_container_pk2_value => p_container_pk2_value,
4260 p_container_pk3_value => p_container_pk3_value,
4261 p_container_pk4_value => p_container_pk4_value,
4262 p_container_pk5_value => p_container_pk5_value,
4263 p_current_user_id => l_user_id_tbl(I)) = FND_API.g_true
4264 THEN
4265 */
4266 OPEN c_user_name(l_user_id_tbl(I));
4267 FETCH c_user_name INTO l_user_name;
4268 CLOSE c_user_name;
4269 IF l_result IS NULL THEN
4270 l_result := l_user_name;
4271 ELSE
4272 l_result := l_result || ',' || l_user_name;
4273 END IF;
4274 --END IF;
4275 END LOOP;
4276
4277 IF l_granted_to_all = FND_API.g_true AND p_include_global = FND_API.g_true THEN
4278 IF l_result IS NULL THEN
4279 l_result := NVL(p_global_value, 'All');
4280 ELSE
4281 l_result := l_result || ',' || NVL(p_global_value, 'All');
4282 END IF;
4283 END IF;
4284
4285 x_usernames := l_result;
4286
4287 -- Standard call to get message count and if count=1, get the message
4288 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4289 p_data => x_msg_data);
4290
4291 IF IBC_DEBUG_PVT.debug_enabled THEN
4292 IBC_DEBUG_PVT.end_process(
4293 IBC_DEBUG_PVT.make_parameter_list(
4294 p_tag => 'OUTPUT',
4295 p_parms => JTF_VARCHAR2_TABLE_4000(
4296 'x_usernames', x_usernames,
4297 'x_return_status', x_return_status,
4298 'x_msg_count', x_msg_count,
4299 'x_msg_data', x_msg_data
4300 )
4301 )
4302 );
4303 END IF;
4304
4305 EXCEPTION
4306 WHEN FND_API.G_EXC_ERROR THEN
4307 x_return_status := FND_API.G_RET_STS_ERROR;
4308 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4309 p_data => x_msg_data);
4310 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4311 P_API_NAME => L_API_NAME
4312 ,P_PKG_NAME => G_PKG_NAME
4313 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4314 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4315 ,P_SQLCODE => SQLCODE
4316 ,P_SQLERRM => SQLERRM
4317 ,X_MSG_COUNT => X_MSG_COUNT
4318 ,X_MSG_DATA => X_MSG_DATA
4319 ,X_RETURN_STATUS => X_RETURN_STATUS
4320 );
4321 IF IBC_DEBUG_PVT.debug_enabled THEN
4322 IBC_DEBUG_PVT.end_process(
4323 IBC_DEBUG_PVT.make_parameter_list(
4324 p_tag => 'OUTPUT',
4325 p_parms => JTF_VARCHAR2_TABLE_4000(
4326 'x_return_status', x_return_status,
4327 'x_msg_count', x_msg_count,
4328 'x_msg_data', x_msg_data
4329 )
4330 )
4331 );
4332 END IF;
4333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4335 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4336 p_data => x_msg_data);
4337 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4338 P_API_NAME => L_API_NAME
4339 ,P_PKG_NAME => G_PKG_NAME
4340 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4341 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4342 ,P_SQLCODE => SQLCODE
4343 ,P_SQLERRM => SQLERRM
4344 ,X_MSG_COUNT => X_MSG_COUNT
4345 ,X_MSG_DATA => X_MSG_DATA
4346 ,X_RETURN_STATUS => X_RETURN_STATUS
4347 );
4348 IF IBC_DEBUG_PVT.debug_enabled THEN
4349 IBC_DEBUG_PVT.end_process(
4350 IBC_DEBUG_PVT.make_parameter_list(
4351 p_tag => 'OUTPUT',
4352 p_parms => JTF_VARCHAR2_TABLE_4000(
4353 'x_return_status', x_return_status,
4354 'x_msg_count', x_msg_count,
4355 'x_msg_data', x_msg_data
4356 )
4357 )
4358 );
4359 END IF;
4360 WHEN OTHERS THEN
4361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4362 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4363 THEN
4364 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4365 END IF;
4366 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4367 p_data => x_msg_data);
4368 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4369 P_API_NAME => L_API_NAME
4370 ,P_PKG_NAME => G_PKG_NAME
4371 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
4372 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4373 ,P_SQLCODE => SQLCODE
4374 ,P_SQLERRM => SQLERRM
4375 ,X_MSG_COUNT => X_MSG_COUNT
4376 ,X_MSG_DATA => X_MSG_DATA
4377 ,X_RETURN_STATUS => X_RETURN_STATUS
4378 );
4379 IF IBC_DEBUG_PVT.debug_enabled THEN
4380 IBC_DEBUG_PVT.end_process(
4381 IBC_DEBUG_PVT.make_parameter_list(
4382 p_tag => 'OUTPUT',
4383 p_parms => JTF_VARCHAR2_TABLE_4000(
4384 'x_return_status', x_return_status,
4385 'x_msg_count', x_msg_count,
4386 'x_msg_data', x_msg_data,
4387 'EXCEPTION', SQLERRM
4388 )
4389 )
4390 );
4391 END IF;
4392 END get_grantee_usernames;
4393
4394 /*#
4395 * returns the list of grantee user ids who have a specific permission
4396 * on a given object instance. This doesn't include permissions given
4397 * to everybody (no grantee in particular) nor "RESTRICT" grants.
4398 *
4399 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
4400 * for this particular instance
4401 * @param p_instance_pk1_value value 1 for instance's primary key
4402 * @param p_instance_pk2_value value 2 for instance's primary key
4403 * @param p_instance_pk3_value value 3 for instance's primary key
4404 * @param p_instance_pk4_value value 4 for instance's primary key
4405 * @param p_instance_pk5_value value 5 for instance's primary key
4406 * @param p_permission_code Permission Code
4407 * @param p_container_pk1_value value 1 for container's primary key
4408 * @param p_container_pk2_value value 2 for container's primary key
4409 * @param p_container_pk3_value value 3 for container's primary key
4410 * @param p_container_pk4_value value 4 for container's primary key
4411 * @param p_container_pk5_value value 5 for container's primary key
4412 * @param x_userids Output table containing all users with
4413 * permission on object's instance
4414 * @param p_api_version standard parm - API Version
4415 * @param p_init_msg_list standard parm - Initialize message list
4416 * @param x_return_status standard parm - Return Status
4417 * @param x_msg_count standard parm - Message Count
4418 * @param x_msg_data standard parm - Message Data
4419 *
4420 * @rep:displayname get_grantee_userids
4421 *
4422 */
4423 PROCEDURE get_grantee_userids(
4424 p_instance_object_id IN NUMBER
4425 ,p_instance_pk1_value IN VARCHAR2
4426 ,p_instance_pk2_value IN VARCHAR2
4427 ,p_instance_pk3_value IN VARCHAR2
4428 ,p_instance_pk4_value IN VARCHAR2
4429 ,p_instance_pk5_value IN VARCHAR2
4430 ,p_permission_code IN VARCHAR2
4431 ,p_container_object_id IN NUMBER
4432 ,p_container_pk1_value IN VARCHAR2
4433 ,p_container_pk2_value IN VARCHAR2
4434 ,p_container_pk3_value IN VARCHAR2
4435 ,p_container_pk4_value IN VARCHAR2
4436 ,p_container_pk5_value IN VARCHAR2
4437 ,x_userids OUT NOCOPY JTF_NUMBER_TABLE
4438 ,p_api_version IN NUMBER
4439 ,p_init_msg_list IN VARCHAR2
4440 ,x_return_status OUT NOCOPY VARCHAR2
4441 ,x_msg_count OUT NOCOPY NUMBER
4442 ,x_msg_data OUT NOCOPY VARCHAR2
4443 ) IS
4444
4445 l_user_id_tbl t_user_id_tbl;
4446
4447 l_user_name VARCHAR2(30);
4448
4449 l_count NUMBER;
4450 l_result t_user_id_tbl;
4451
4452 -- Variable to know if a grant ALLOW was given to everybody
4453 -- not used for the moment, but could be used in the future.
4454 l_granted_to_all VARCHAR2(1) := FND_API.g_false;
4455
4456 -- IBC_object_grant_groups
4457 l_object_grant_group_rowid ROWID;
4458 l_object_grant_group_id NUMBER;
4459 l_grant_group_id NUMBER;
4460 l_inherited_flag VARCHAR2(2);
4461 l_inherited_from NUMBER;
4462 l_inheritance_type VARCHAR2(30);
4463
4464 --******** local variable for standards **********
4465 l_api_name CONSTANT VARCHAR2(30) := 'get_grantee_usernames';
4466 l_api_version CONSTANT NUMBER := 1.0;
4467
4468 CURSOR c_user_name(p_user_id NUMBER)
4469 IS SELECT user_name
4470 FROM fnd_user
4471 WHERE user_id = p_user_id;
4472
4473 CURSOR c_base_grants(p_object_id NUMBER,
4474 p_grant_group_id NUMBER,
4475 p_permission_code VARCHAR2)
4476 IS SELECT action,
4477 permission_code,
4478 grant_level,
4479 grant_group_id,
4480 grantee_user_id,
4481 grantee_resource_id, grantee_resource_type
4482 FROM ibc_grants a0
4483 WHERE object_id = p_object_id
4484 AND grant_group_id = p_grant_group_id
4485 AND permission_code = p_permission_code
4486 AND action = 'ALLOW'
4487 ORDER BY grant_level;
4488
4489 BEGIN
4490 -- ******* Standard Begins ********
4491
4492 -- Standard call to check for call compatibility.
4493 IF NOT FND_API.Compatible_API_Call (
4494 l_api_version,
4495 p_api_version,
4496 l_api_name,
4497 G_PKG_NAME)
4498 THEN
4499 x_return_status := FND_API.G_RET_STS_ERROR;
4500 FND_MSG_PUB.ADD;
4501 RAISE FND_API.G_EXC_ERROR;
4502 END IF;
4503 -- Initialize message list if p_init_msg_list is set to TRUE.
4504 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4505 FND_MSG_PUB.initialize;
4506 END IF;
4507
4508 -- Initialize API return status to success
4509 x_return_status := FND_API.G_RET_STS_SUCCESS;
4510
4511 -- Begin
4512
4513 l_granted_to_all := FND_API.g_false;
4514
4515 get_object_grant_group_info(
4516 p_instance_object_id => p_instance_object_id
4517 ,p_instance_pk1_value => p_instance_pk1_value
4518 ,p_instance_pk2_value => p_instance_pk2_value
4519 ,p_instance_pk3_value => p_instance_pk3_value
4520 ,p_instance_pk4_value => p_instance_pk4_value
4521 ,p_instance_pk5_value => p_instance_pk5_value
4522 ,x_rowid => l_object_grant_group_rowid
4523 ,x_object_grant_group_id => l_object_grant_group_id
4524 ,x_grant_group_id => l_grant_group_id
4525 ,x_inherited_flag => l_inherited_flag
4526 ,x_inherited_from => l_inherited_from
4527 ,x_inheritance_type => l_inheritance_type
4528 );
4529 IF l_object_grant_group_rowid IS NULL THEN
4530 -- Fetch object's grant group Info for container object
4531 get_object_grant_group_info(
4532 p_instance_object_id => p_container_object_id
4533 ,p_instance_pk1_value => p_container_pk1_value
4534 ,p_instance_pk2_value => p_container_pk2_value
4535 ,p_instance_pk3_value => p_container_pk3_value
4536 ,p_instance_pk4_value => p_container_pk4_value
4537 ,p_instance_pk5_value => p_container_pk5_value
4538 ,x_rowid => l_object_grant_group_rowid
4539 ,x_object_grant_group_id => l_object_grant_group_id
4540 ,x_grant_group_id => l_grant_group_id
4541 ,x_inherited_flag => l_inherited_flag
4542 ,x_inherited_from => l_inherited_from
4543 ,x_inheritance_type => l_inheritance_type
4544 );
4545 END IF;
4546
4547 FOR r_base_grants IN c_base_grants(p_instance_object_id, l_grant_group_id, p_permission_code) LOOP
4548 IF r_base_grants.grantee_user_id IS NULL AND
4549 r_base_grants.grantee_resource_id IS NOT NULL
4550 THEN
4551 Create_User_Table(p_resource_id => r_base_grants.grantee_resource_id,
4552 p_resource_type => r_base_grants.grantee_resource_type,
4553 p_user_id_tbl => l_user_id_tbl);
4554 ELSIF r_base_grants.grantee_user_id IS NOT NULL THEN
4555 Add_to_user_table(r_base_grants.grantee_user_id, l_user_id_tbl);
4556 ELSIF r_base_grants.grantee_resource_id IS NULL THEN
4557 l_granted_to_all := FND_API.g_true;
4558 END IF;
4559 END LOOP;
4560 -- Set output result
4561 l_count := 0;
4562 FOR I IN 1..l_user_id_tbl.COUNT LOOP
4563 -- Check permission under a specific user
4564 IF IBC_DATA_SECURITY_PVT.has_permission(p_instance_object_id => p_instance_object_id,
4565 p_instance_pk1_value => p_instance_pk1_value,
4566 p_instance_pk2_value => p_instance_pk2_value,
4567 p_instance_pk3_value => p_instance_pk3_value,
4568 p_instance_pk4_value => p_instance_pk4_value,
4569 p_instance_pk5_value => p_instance_pk5_value,
4570 p_permission_code => p_permission_code,
4571 p_container_object_id => p_container_object_id,
4572 p_container_pk1_value => p_container_pk1_value,
4573 p_container_pk2_value => p_container_pk2_value,
4574 p_container_pk3_value => p_container_pk3_value,
4575 p_container_pk4_value => p_container_pk4_value,
4576 p_container_pk5_value => p_container_pk5_value,
4577 p_current_user_id => l_user_id_tbl(I)) = FND_API.g_true
4578 THEN
4579 l_count := l_count + 1;
4580 l_result(l_count) := l_user_id_tbl(I);
4581 END IF;
4582 END LOOP;
4583
4584 -- Set actual result table (JTF_NUMBER_TABLE)
4585 IF l_count > 0 THEN
4586 x_userids := JTF_NUMBER_TABLE();
4587 x_userids.extend(l_count);
4588 FOR I IN 1..l_result.COUNT LOOP
4589 x_userids(I) := l_result(I);
4590 END LOOP;
4591 END IF;
4592
4593 -- Standard call to get message count and if count=1, get the message
4594 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4595 p_data => x_msg_data);
4596 EXCEPTION
4597 WHEN FND_API.G_EXC_ERROR THEN
4598 x_return_status := FND_API.G_RET_STS_ERROR;
4599 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4600 p_data => x_msg_data);
4601 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4602 P_API_NAME => L_API_NAME
4603 ,P_PKG_NAME => G_PKG_NAME
4604 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4605 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4606 ,P_SQLCODE => SQLCODE
4607 ,P_SQLERRM => SQLERRM
4608 ,X_MSG_COUNT => X_MSG_COUNT
4609 ,X_MSG_DATA => X_MSG_DATA
4610 ,X_RETURN_STATUS => X_RETURN_STATUS
4611 );
4612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4614 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4615 p_data => x_msg_data);
4616 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4617 P_API_NAME => L_API_NAME
4618 ,P_PKG_NAME => G_PKG_NAME
4619 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4620 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4621 ,P_SQLCODE => SQLCODE
4622 ,P_SQLERRM => SQLERRM
4623 ,X_MSG_COUNT => X_MSG_COUNT
4624 ,X_MSG_DATA => X_MSG_DATA
4625 ,X_RETURN_STATUS => X_RETURN_STATUS
4626 );
4627 WHEN OTHERS THEN
4628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4629 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4630 THEN
4631 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4632 END IF;
4633 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4634 p_data => x_msg_data);
4635 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4636 P_API_NAME => L_API_NAME
4637 ,P_PKG_NAME => G_PKG_NAME
4638 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
4639 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4640 ,P_SQLCODE => SQLCODE
4641 ,P_SQLERRM => SQLERRM
4642 ,X_MSG_COUNT => X_MSG_COUNT
4643 ,X_MSG_DATA => X_MSG_DATA
4644 ,X_RETURN_STATUS => X_RETURN_STATUS
4645 );
4646 END get_grantee_userids;
4647
4648 /*#
4649 * Returns information about inheritance, particularly the type of
4650 * inheritance, and if in fact this instance has its own permissions
4651 * or is still inheriting from parent container.
4652 *
4653 * @param p_instance_object_id ID for object definition id found in FND_OBJECTS
4654 * for this particular instance
4655 * @param p_instance_pk1_value value 1 for instance's primary key
4656 * @param p_instance_pk2_value value 2 for instance's primary key
4657 * @param p_instance_pk3_value value 3 for instance's primary key
4658 * @param p_instance_pk4_value value 4 for instance's primary key
4659 * @param p_instance_pk5_value value 5 for instance's primary key
4660 * @param p_container_object_id ID for container. Found in FND_OBJECTS
4661 * @param p_container_pk1_value value 1 for container's primary key
4662 * @param p_container_pk2_value value 2 for container's primary key
4663 * @param p_container_pk3_value value 3 for container's primary key
4664 * @param p_container_pk4_value value 4 for container's primary key
4665 * @param p_container_pk5_value value 5 for container's primary key
4666 * @param p_api_version standard parm - API Version
4667 * @param p_init_msg_list standard parm - Initialize message list
4668 * @param x_inherited_flag Whether instance is inheriting (T) or Not (F)
4669 * @param x_inheritance_type Inheritance Type
4670 * @param x_return_status standard parm - Return Status
4671 * @param x_msg_count standard parm - Message Count
4672 * @param x_msg_data standard parm - Message Data
4673 *
4674 * @rep:displayname get_inheritance_info
4675 *
4676 */
4677 PROCEDURE get_inheritance_info (
4678 p_instance_object_id IN NUMBER
4679 ,p_instance_pk1_value IN VARCHAR2
4680 ,p_instance_pk2_value IN VARCHAR2
4681 ,p_instance_pk3_value IN VARCHAR2
4682 ,p_instance_pk4_value IN VARCHAR2
4683 ,p_instance_pk5_value IN VARCHAR2
4684 ,p_container_object_id IN NUMBER
4685 ,p_container_pk1_value IN VARCHAR2
4686 ,p_container_pk2_value IN VARCHAR2
4687 ,p_container_pk3_value IN VARCHAR2
4688 ,p_container_pk4_value IN VARCHAR2
4689 ,p_container_pk5_value IN VARCHAR2
4690 ,p_api_version IN NUMBER
4691 ,p_init_msg_list IN VARCHAR2
4692 ,x_inherited_flag OUT NOCOPY VARCHAR2
4693 ,x_inheritance_type OUT NOCOPY VARCHAR2
4694 ,x_return_status OUT NOCOPY VARCHAR2
4695 ,x_msg_count OUT NOCOPY NUMBER
4696 ,x_msg_data OUT NOCOPY VARCHAR2
4697 ) IS
4698
4699 -- IBC_object_grant_groups
4700 l_object_grant_group_rowid ROWID;
4701 l_object_grant_group_id NUMBER;
4702 l_grant_group_id NUMBER;
4703 l_inherited_flag VARCHAR2(2);
4704 l_inherited_from NUMBER;
4705 l_inheritance_type VARCHAR2(30);
4706
4707 --******** local variable for standards **********
4708 l_api_name CONSTANT VARCHAR2(30) := 'get_inheritance_info';
4709 l_api_version CONSTANT NUMBER := 1.0;
4710
4711 BEGIN
4712
4713 -- ******* Standard Begins ********
4714
4715 -- Standard call to check for call compatibility.
4716 IF NOT FND_API.Compatible_API_Call (
4717 l_api_version,
4718 p_api_version,
4719 l_api_name,
4720 G_PKG_NAME)
4721 THEN
4722 x_return_status := FND_API.G_RET_STS_ERROR;
4723 FND_MSG_PUB.ADD;
4724 RAISE FND_API.G_EXC_ERROR;
4725 END IF;
4726 -- Initialize message list if p_init_msg_list is set to TRUE.
4727 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4728 FND_MSG_PUB.initialize;
4729 END IF;
4730
4731 -- Initialize API return status to success
4732 x_return_status := FND_API.G_RET_STS_SUCCESS;
4733
4734 -- Begin
4735
4736 get_object_grant_group_info(
4737 p_instance_object_id => p_instance_object_id
4738 ,p_instance_pk1_value => p_instance_pk1_value
4739 ,p_instance_pk2_value => p_instance_pk2_value
4740 ,p_instance_pk3_value => p_instance_pk3_value
4741 ,p_instance_pk4_value => p_instance_pk4_value
4742 ,p_instance_pk5_value => p_instance_pk5_value
4743 ,x_rowid => l_object_grant_group_rowid
4744 ,x_object_grant_group_id => l_object_grant_group_id
4745 ,x_grant_group_id => l_grant_group_id
4746 ,x_inherited_flag => l_inherited_flag
4747 ,x_inherited_from => l_inherited_from
4748 ,x_inheritance_type => l_inheritance_type
4749 );
4750 IF l_object_grant_group_rowid IS NULL THEN
4751 -- Fetch object's grant group Info for container object
4752 get_object_grant_group_info(
4753 p_instance_object_id => p_container_object_id
4754 ,p_instance_pk1_value => p_container_pk1_value
4755 ,p_instance_pk2_value => p_container_pk2_value
4756 ,p_instance_pk3_value => p_container_pk3_value
4757 ,p_instance_pk4_value => p_container_pk4_value
4758 ,p_instance_pk5_value => p_container_pk5_value
4759 ,x_rowid => l_object_grant_group_rowid
4760 ,x_object_grant_group_id => l_object_grant_group_id
4761 ,x_grant_group_id => l_grant_group_id
4762 ,x_inherited_flag => l_inherited_flag
4763 ,x_inherited_from => l_inherited_from
4764 ,x_inheritance_type => l_inheritance_type
4765 );
4766 END IF;
4767
4768 -- Setting values for OUT parameters.
4769 x_inherited_flag := l_inherited_flag;
4770 x_inheritance_type := l_inheritance_type;
4771
4772 -- Standard call to get message count and if count=1, get the message
4773 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4774 p_data => x_msg_data);
4775 EXCEPTION
4776 WHEN FND_API.G_EXC_ERROR THEN
4777 x_return_status := FND_API.G_RET_STS_ERROR;
4778 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4779 p_data => x_msg_data);
4780 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4781 P_API_NAME => L_API_NAME
4782 ,P_PKG_NAME => G_PKG_NAME
4783 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4784 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4785 ,P_SQLCODE => SQLCODE
4786 ,P_SQLERRM => SQLERRM
4787 ,X_MSG_COUNT => X_MSG_COUNT
4788 ,X_MSG_DATA => X_MSG_DATA
4789 ,X_RETURN_STATUS => X_RETURN_STATUS
4790 );
4791 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4793 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4794 p_data => x_msg_data);
4795 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4796 P_API_NAME => L_API_NAME
4797 ,P_PKG_NAME => G_PKG_NAME
4798 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4799 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4800 ,P_SQLCODE => SQLCODE
4801 ,P_SQLERRM => SQLERRM
4802 ,X_MSG_COUNT => X_MSG_COUNT
4803 ,X_MSG_DATA => X_MSG_DATA
4804 ,X_RETURN_STATUS => X_RETURN_STATUS
4805 );
4806 WHEN OTHERS THEN
4807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4808 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4809 THEN
4810 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4811 END IF;
4812 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4813 p_data => x_msg_data);
4814 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4815 P_API_NAME => L_API_NAME
4816 ,P_PKG_NAME => G_PKG_NAME
4817 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
4818 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
4819 ,P_SQLCODE => SQLCODE
4820 ,P_SQLERRM => SQLERRM
4821 ,X_MSG_COUNT => X_MSG_COUNT
4822 ,X_MSG_DATA => X_MSG_DATA
4823 ,X_RETURN_STATUS => X_RETURN_STATUS
4824 );
4825 END get_inheritance_info;
4826
4827 END;