[Home] [Help]
PACKAGE BODY: APPS.DOM_SECURITY_PUB
Source
1 PACKAGE BODY dom_security_pub AS
2 /* $Header: DOMDATASECB.pls 120.15 2006/11/08 14:01:40 ysireesh noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'DOM_SECURITY_PUB' ;
5 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
6 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
7 G_OCS_ROLE VARCHAR2(30) := 'Reviewer';
8 TYPE DYNAMIC_CUR IS REF CURSOR;
9
10 /*
11 -- Test Debug
12 PROCEDURE Write_Debug
13 (
14 p_api_name IN VARCHAR2,
15 p_debug_message IN VARCHAR2
16 )
17 IS
18
19 BEGIN
20
21 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
22 DOM_LOG.LOG_STR(G_PKG_NAME, p_api_name, null, p_debug_message);
23 END IF ;
24
25 EXCEPTION
26 WHEN OTHERS THEN
27 NULL:
28
29 END Write_Debug;
30 */
31
32 PROCEDURE Grant_Document_Role
33 (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 p_object_name IN VARCHAR2,
39 p_pk1_value IN VARCHAR2,
40 p_pk2_value IN VARCHAR2,
41 p_pk3_value IN VARCHAR2,
42 p_pk4_value IN VARCHAR2,
43 p_pk5_value IN VARCHAR2,
44 p_party_ids IN FND_TABLE_OF_NUMBER,
45 p_role_id IN NUMBER,
46 p_start_date IN DATE := SYSDATE,
47 p_end_date IN DATE := NULL,
48 p_api_caller IN VARCHAR2 := NULL,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 x_return_status OUT NOCOPY VARCHAR2
52 )
53 IS
54
55 l_api_name CONSTANT VARCHAR2(50) := 'Grant_Document_Role';
56 l_grant_guid FND_GRANTS.GRANT_GUID%TYPE ;
57 l_grant_exist VARCHAR2(10);
58 l_return_status VARCHAR2(3) ;
59 l_role_name FND_MENUS.MENU_NAME%TYPE;
60 l_pk4_value VARCHAR2(50);
61 BEGIN
62
63 -- Initialize API return status to success
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 SELECT menu_name
67 INTO l_role_name
68 FROM fnd_menus
69 WHERE menu_id = p_role_id;
70
71 FOR lcount in p_party_ids.first .. p_party_ids.last LOOP
72
73 IF p_pk1_value IS NOT NULL THEN
74
75 EGO_SECURITY_PUB.grant_role_guid
76 ( p_api_version => 1.0 ,
77 p_role_name => l_role_name ,
78 p_object_name => p_object_name ,
79 p_instance_type => 'INSTANCE' ,
80 p_instance_set_id => NULL ,
81 p_instance_pk1_value => p_pk1_value ,
82 p_instance_pk2_value => p_pk2_value ,
83 p_instance_pk3_value => '*NULL*' ,
84 p_instance_pk4_value => '*NULL*' ,
85 p_instance_pk5_value => '*NULL*' ,
86 p_party_id => p_party_ids(lcount) ,
87 p_start_date => NVL(p_start_date,SYSDATE) ,
88 p_end_date => p_end_date ,
89 x_return_status => l_return_status ,
90 x_errorcode => x_msg_data ,
91 x_grant_guid => l_grant_guid
92 );
93
94 if(l_grant_guid is not null) THEN
95 if(p_pk4_value is null OR p_pk4_value = '-1') THEN
96 l_pk4_value := '*NULL*';
97 else
98 l_pk4_value := p_pk4_value;
99 end if;
100
101 update FND_GRANTS
102 set parameter1 = p_pk3_value,
103 parameter2 = p_pk4_value
104 where grant_guid = l_grant_guid;
105 end if;
106
107 END IF;
108
109 END LOOP;
110
111 --Grant Access to Files of that document.
112 Grant_Attachments_OCSRole
113 (
114 p_api_version => 1.0,
115 p_init_msg_list => NULL,
116 p_commit => FND_API.G_TRUE,
117 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
118 p_entity_name => p_object_name,
119 p_pk1_value => p_pk1_value,
120 p_pk2_value => p_pk2_value,
121 p_pk3_value => NULL,
122 p_pk4_value => NULL,
123 p_pk5_value => NULL,
124 p_ocs_role => G_OCS_ROLE,
125 p_party_ids => p_party_ids,
126 p_api_caller => NULL,
127 x_msg_count => x_msg_count,
128 x_msg_data => x_msg_data,
129 x_return_status => x_return_status
130 );
131
132 -- Standard check of p_commit.
133 IF FND_API.To_Boolean( p_commit ) THEN
134 COMMIT WORK;
135 END IF;
136
137 --
138 -- returns T if the action is success
139 -- and F on failure
140 --
141 IF ( l_return_status = FND_API.G_TRUE OR l_return_status = FND_API.G_FALSE )
142 THEN
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144 ELSE
145 x_return_status := FND_API.G_RET_STS_ERROR ;
146 END IF ;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
151
152 IF FND_MSG_PUB.Check_Msg_Level
153 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
154 THEN
155 FND_MSG_PUB.Add_Exc_Msg
156 (
157 G_PKG_NAME,
158 l_api_name
159 );
160 END IF;
161
162 END Grant_Document_Role ;
163
164
165 PROCEDURE Revoke_Document_Role
166 (
167 p_api_version IN NUMBER,
168 p_init_msg_list IN VARCHAR2,
169 p_commit IN VARCHAR2 := FND_API.G_FALSE,
170 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
171 p_object_name IN VARCHAR2,
172 p_pk1_value IN VARCHAR2,
173 p_pk2_value IN VARCHAR2,
174 p_pk3_value IN VARCHAR2,
175 p_pk4_value IN VARCHAR2,
176 p_pk5_value IN VARCHAR2,
177 p_party_ids IN FND_TABLE_OF_NUMBER,
178 p_role_id IN NUMBER,
179 p_api_caller IN VARCHAR2 := NULL,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2,
182 x_return_status OUT NOCOPY VARCHAR2
183 )
184 IS
185
186 l_api_name CONSTANT VARCHAR2(50) := 'Revoke_Document_Role';
187 l_return_status VARCHAR2(1) ;
188 l_error_code NUMBER(1) ;
189 l_role_name FND_MENUS.MENU_NAME%TYPE;
190 l_role_ids FND_ARRAY_OF_NUMBER_25;
191 l_object_id NUMBER ;
192 lcount1 NUMBER;
193 l_ocs_role_to_revoke VARCHAR2(30);
194 l_grantee_key fnd_grants.grantee_key%TYPE;
195 l_grantee_type fnd_grants.grantee_type%TYPE;
196
197 CURSOR get_party_type (cp_party_id NUMBER)
198 IS
199 SELECT party_type
200 FROM hz_parties
201 WHERE party_id=cp_party_id;
202
203 BEGIN
204
205 -- Initialize API return status to success
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207
208 SELECT object_id
209 INTO l_object_id
210 FROM fnd_objects
211 WHERE obj_name = p_object_name;
212
213 FOR lcount in p_party_ids.first .. p_party_ids.last LOOP
214
215 -- If Role is not passed, query up all the roles obtained to the user
216 -- for this pks.
217 OPEN get_party_type (cp_party_id => p_party_ids(lcount));
218 FETCH get_party_type INTO l_grantee_type;
219 CLOSE get_party_type;
220
221 IF (l_grantee_type = 'PERSON') THEN
222 l_grantee_type := 'USER';
223 l_grantee_key := 'HZ_PARTY:' || p_party_ids(lcount);
224 ELSIF (l_grantee_type = 'GROUP') THEN
225 l_grantee_type := 'GROUP';
226 l_grantee_key :='HZ_GROUP:' || p_party_ids(lcount);
227 END IF;
228
229 if p_role_id is not null then
230 SELECT menu_name
231 INTO l_role_name
232 FROM fnd_menus
233 WHERE menu_id = p_role_id;
234
235 FND_GRANTS_PKG.delete_grant(
236 p_grantee_type => l_grantee_type,
237 p_grantee_key => l_grantee_key,
238 p_object_name => p_object_name,
239 p_instance_type => 'INSTANCE',
240 p_instance_set_id => NULL,
241 p_instance_pk1_value => p_pk1_value,
242 p_instance_pk2_value => p_pk2_value,
243 p_instance_pk3_value => NULL,
244 p_instance_pk4_value => NULL,
245 p_instance_pk5_value => NULL,
246 p_menu_name => l_role_name,
247 p_program_name => NULL,
248 p_program_tag => NULL,
249 x_success => l_return_status,
250 x_errcode => l_error_code
251 );
252 end if;
253
254 if p_role_id is NULL THEN
255
256 -- Get all User Roles on this entity
257 Get_User_Roles
258 (
259 p_object_id => l_object_id,
260 p_document_id => p_pk1_value,
261 p_revision_id => p_pk2_value,
262 p_change_id => p_pk3_value,
263 p_change_line_id => p_pk4_value,
264 p_party_id => p_party_ids(lcount),
265 x_role_ids => l_role_ids
266 ) ;
267
268 FOR lcount1 in l_role_ids.first .. l_role_ids.last LOOP
269
270 if l_role_ids(lcount1) is not null then
271
272 SELECT menu_name
273 INTO l_role_name
274 FROM fnd_menus
275 WHERE menu_id = l_role_ids(lcount1);
276
277 FND_GRANTS_PKG.delete_grant(
278 p_grantee_type => l_grantee_type,
279 p_grantee_key => l_grantee_key,
280 p_object_name => p_object_name,
281 p_instance_type => 'INSTANCE',
282 p_instance_set_id => NULL,
283 p_instance_pk1_value => p_pk1_value,
284 p_instance_pk2_value => p_pk2_value,
285 p_instance_pk3_value => NULL,
286 p_instance_pk4_value => NULL,
287 p_instance_pk5_value => NULL,
288 p_menu_name => l_role_name,
289 p_program_name => NULL,
290 p_program_tag => NULL,
291 x_success => l_return_status,
292 x_errcode => l_error_code
293 );
294
295 end if;
296 END LOOP;
297
298 end if;
299
300 END LOOP;
301
302 --Revoke Access from the Files of that document.
303 Revoke_Attachments_OCSRole
304 (
305 p_api_version => 1.0,
306 p_init_msg_list => NULL,
307 p_commit => FND_API.G_TRUE,
308 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
309 p_entity_name => p_object_name,
310 p_pk1_value => p_pk1_value,
311 p_pk2_value => p_pk2_value,
312 p_pk3_value => NULL,
313 p_pk4_value => NULL,
314 p_pk5_value => NULL,
315 p_ocs_role => l_ocs_role_to_revoke,
316 p_party_ids => p_party_ids,
317 p_api_caller => NULL,
318 x_msg_count => x_msg_count,
319 x_msg_data => x_msg_data,
320 x_return_status => x_return_status
321 );
322
323
324 -- Standard check of p_commit.
325 IF FND_API.To_Boolean( p_commit ) THEN
326 COMMIT WORK;
327 END IF;
328
329 --
330 -- returns T if the action is success
331 -- and F on failure
332 --
333 IF ( l_return_status = FND_API.G_TRUE OR
334 l_return_status = FND_API.G_FALSE )
335 THEN
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337 ELSE
338 x_return_status := FND_API.G_RET_STS_ERROR ;
339 END IF ;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
345
346 IF FND_MSG_PUB.Check_Msg_Level
347 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
348 THEN
349 FND_MSG_PUB.Add_Exc_Msg
350 (
351 G_PKG_NAME,
352 l_api_name
353 );
354 END IF;
355
356 END Revoke_Document_Role ;
357
358
359 PROCEDURE Grant_Attachments_OCSRole
360 (
361 p_api_version IN NUMBER,
362 p_init_msg_list IN VARCHAR2,
363 p_commit IN VARCHAR2 := FND_API.G_FALSE,
364 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
365 p_entity_name IN VARCHAR2,
366 p_pk1_value IN VARCHAR2,
367 p_pk2_value IN VARCHAR2,
368 p_pk3_value IN VARCHAR2,
369 p_pk4_value IN VARCHAR2,
370 p_pk5_value IN VARCHAR2,
371 p_ocs_role IN VARCHAR2,
372 p_party_ids IN FND_TABLE_OF_NUMBER,
373 p_api_caller IN VARCHAR2 := NULL,
374 x_msg_count OUT NOCOPY NUMBER,
375 x_msg_data OUT NOCOPY VARCHAR2,
376 x_return_status OUT NOCOPY VARCHAR2
377 )
378 IS
379 l_api_name CONSTANT VARCHAR2(50) := 'Grant_Attachments_OCSRole';
380 l_return_status VARCHAR2(1) ;
381 l_error_code NUMBER(1) ;
382 l_role_name FND_MENUS.MENU_NAME%TYPE;
383 l_user_name VARCHAR2(30);
384 l_service_url VARCHAR2(100);
385 l_user_login VARCHAR2(30);
386 l_protocol VARCHAR2(30);
387 l_party_type hz_parties.party_type%TYPE;
388 get_attachments DYNAMIC_CUR;
389 l_dynamic_sql VARCHAR2(32767);
390 l_media_id NUMBER;
391 l_node_id NUMBER;
392 l_created_by NUMBER;
393 l_file_type VARCHAR2(10);
394 l_entity_name VARCHAR2(30);
395 l_rows NUMBER;
396
397 BEGIN
398
399 l_entity_name := p_entity_name;
400
401 l_dynamic_sql := ' SELECT media_id, dm_node, A.created_by, dm_type' ||
402 ' FROM FND_DOCUMENTS D, FND_ATTACHED_DOCUMENTS A' ||
403 ' WHERE A.DOCUMENT_ID = D.DOCUMENT_ID ' ||
404 ' AND A.ENTITY_NAME = :entity_name ' ||
405 ' AND A.PK1_VALUE = :pk1_value ';
406
407 IF (p_entity_name = 'DOM_DOCUMENT_REVISION') THEN
408
409 l_dynamic_sql := l_dynamic_sql || ' AND A.PK2_VALUE = :pk2_value';
410
411 l_entity_name := 'DOM_DOCUMENT_VERSION';
412
413 OPEN get_attachments FOR l_dynamic_sql
414 USING IN l_entity_name,
415 IN p_pk1_value,
416 IN p_pk2_value;
417
418 ELSE
419
420 OPEN get_attachments FOR l_dynamic_sql
421 USING IN l_entity_name,
422 IN p_pk1_value;
423
424 END IF;
425
426 LOOP
427
428 FETCH get_attachments INTO l_media_id, l_node_id, l_created_by, l_file_type;
429 EXIT WHEN get_attachments%NOTFOUND;
430
431 FOR lcount in p_party_ids.first .. p_party_ids.last
432 LOOP
433
434 SELECT service_url, protocol
435 INTO l_service_url, l_protocol
436 FROM DOM_REPOSITORIES WHERE id = l_node_id;
437
438 SELECT user_name INTO l_user_name
439 FROM FND_USER where person_party_id = p_party_ids(lcount);
440
441 SELECT party_type INTO l_party_type
442 FROM hz_parties
443 WHERE party_id = p_party_ids(lcount);
444
445 IF(l_party_type = 'PERSON') THEN
446 l_party_type := 'USER';
447 END IF;
448
449 --Get the Attachment created by user login.
450 -- This is required to make WS connection while trying to grant role to the user.
451 SELECT user_name INTO l_user_login
452 FROM fnd_user
453 WHERE user_id = l_created_by;
454
455 IF (l_protocol = 'WEBSERVICES') THEN
456
457 DOM_WS_INTERFACE_PUB.Grant_Attachments_OCSRole (
458 p_api_version => p_api_version,
459 p_service_url => l_service_url,
460 p_family_id => l_media_id,
461 p_role => p_ocs_role,
462 p_user_name => l_user_name,
463 p_user_login => l_user_login,
464 x_return_status => x_return_status,
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data
467 );
468
469 /*
470 l_rows := Check_For_Duplicate_Grant
471 (
472 p_entity_name => l_entity_name,
473 p_pk1_value => p_pk1_value,
474 p_pk2_value => p_pk2_value,
475 p_pk3_value => p_pk3_value,
476 p_pk4_value => p_pk4_value,
477 p_pk5_value => p_pk5_value,
478 p_file_id => l_media_id,
479 p_repos_id => l_node_id,
480 p_party_id => p_party_ids(lcount)
481 );
482 IF(l_rows = 0) THEN
483 */
484
485 -- Insert to DOM_FOLDER_FILE_MEMBERSHIPS
486 -- Required while revoking roles.
487 INSERT INTO DOM_FOLDER_FILE_MEMBERSHIPS
488 (
489 REPOSITORY_ID,
490 REPOSITORY_ITEM_ID,
491 REPOSITORY_ITEM_TYPE,
492 ENTITY_NAME,
493 PK1_VALUE,
494 PK2_VALUE,
495 PK3_VALUE,
496 PK4_VALUE,
497 PK5_VALUE,
498 PARTY_TYPE,
499 PARTY_ID,
500 OFO_ROLE,
501 CREATED_BY,
502 CREATION_DATE,
503 LAST_UPDATED_BY,
504 LAST_UPDATE_DATE,
505 LAST_UPDATE_LOGIN
506 )
507 VALUES
508 (
509 l_node_id, --REPOSITORY_ID
510 l_media_id, --REPOSITORY_ITEM_ID
511 l_file_type, --REPOSITORY_ITEM_TYPE
512 l_entity_name, --ENTITY_NAME
513 p_pk1_value, --PK1_VALUE
514 p_pk2_value, --PK2_VALUE
515 p_pk3_value, --PK3_VALUE
516 p_pk4_value, --PK4_VALUE
517 p_pk5_value, --PK5_VALUE
518 l_party_type, --PARTY_TYPE
519 p_party_ids(lcount), --PARTY_ID
520 p_ocs_role, --OFO_ROLE
521 NVL(p_api_caller, g_current_user_id), --CREATED_BY
522 SYSDATE, --CREATION_DATE
523 NVL(p_api_caller, g_current_user_id), --LAST_UPDATED_BY
524 SYSDATE, --LAST_UPDATE_DATE
525 g_current_login_id --LAST_UPDATE_LOGIN
526 );
527
528 -- END IF;
529
530 END IF;
531
532 END LOOP;
533
534 END LOOP;
535
536 CLOSE get_attachments;
537
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 -- Standard check of p_commit.
542 IF FND_API.To_Boolean( p_commit ) THEN
543 COMMIT WORK;
544 END IF;
545
546 --
547 -- returns T if the action is success
548 -- and F on failure
549 --
550 IF ( l_return_status = FND_API.G_TRUE OR
551 l_return_status = FND_API.G_FALSE )
552 THEN
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554 ELSE
555 x_return_status := FND_API.G_RET_STS_ERROR ;
556 END IF ;
557
558 -- dbms_output.put_line('value: '||x_return_status);
559
560 EXCEPTION
561
562 WHEN OTHERS THEN
563
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
565
566 IF FND_MSG_PUB.Check_Msg_Level
567 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
568 THEN
569 FND_MSG_PUB.Add_Exc_Msg
570 (
571 G_PKG_NAME,
572 l_api_name
573 );
574 END IF;
575
576 END Grant_Attachments_OCSRole;
577
578 PROCEDURE Grant_Attachment_Access
579 (
580 p_api_version IN NUMBER,
581 p_attached_document_id IN NUMBER := NULL,
582 p_source_media_id IN NUMBER,
583 p_repository_id IN NUMBER,
584 p_ocs_role IN VARCHAR2,
585 p_party_ids IN FND_TABLE_OF_NUMBER,
586 p_submitted_by IN NUMBER,
587 x_msg_count OUT NOCOPY NUMBER,
588 x_msg_data OUT NOCOPY VARCHAR2,
589 x_return_status OUT NOCOPY VARCHAR2
590 )
591 IS
592 l_api_name CONSTANT VARCHAR2(50) := 'Grant_Attachment_Access';
593 l_return_status VARCHAR2(1) ;
594 l_user_name VARCHAR2(30);
595 l_service_url VARCHAR2(100);
596 l_user_login VARCHAR2(30);
597 l_protocol VARCHAR2(30);
598
599 cursor get_user_name(cp_user_id number)
600 IS
601 select user_name
602 from fnd_user
603 where user_id=cp_user_id;
604
605 cursor get_user_name_from_party(cp_party_id number)
606 IS
607 SELECT user_name
608 FROM FND_USER
609 where person_party_id = cp_party_id ;
610
611 BEGIN
612
613 open get_user_name(p_submitted_by);
614 fetch get_user_name into l_user_login;
615 close get_user_name;
616
617 FOR lcount in p_party_ids.first .. p_party_ids.last
618 LOOP
619
620 SELECT service_url, protocol
621 INTO l_service_url, l_protocol
622 FROM DOM_REPOSITORIES WHERE id = p_repository_id;
623
624 FOR rec IN get_user_name_from_party(p_party_ids(lcount))
625 LOOP
626 IF (l_protocol = 'WEBSERVICES') THEN
627
628 DOM_WS_INTERFACE_PUB.Grant_Attachments_OCSRole
629 (
630 p_api_version => p_api_version,
631 p_service_url => l_service_url,
632 p_family_id => p_source_media_id,
633 p_role => p_ocs_role,
634 p_user_name => rec.user_name,
635 p_user_login => l_user_login,
636 x_return_status => x_return_status,
637 x_msg_count => x_msg_count,
638 x_msg_data => x_msg_data
639 );
640 END IF;
641
642 END LOOP;
643
644 END LOOP;
645
646 -- Initialize API return status to success
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648
649
650 --
651 -- returns T if the action is success
652 -- and F on failure
653 --
654 IF ( l_return_status = FND_API.G_TRUE OR
655 l_return_status = FND_API.G_FALSE )
656 THEN
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 ELSE
659 x_return_status := FND_API.G_RET_STS_ERROR ;
660 END IF ;
661
662 -- dbms_output.put_line('value: '||x_return_status);
663
664 EXCEPTION
665
666 WHEN OTHERS THEN
667
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
669
670 IF FND_MSG_PUB.Check_Msg_Level
671 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
672 THEN
673 FND_MSG_PUB.Add_Exc_Msg
674 (
675 G_PKG_NAME,
676 l_api_name
677 );
678 END IF;
679
680 END Grant_Attachment_Access;
681
682
683
684 PROCEDURE Revoke_Attachments_OCSRole
685 (
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2,
688 p_commit IN VARCHAR2 := FND_API.G_FALSE,
689 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
690 p_entity_name IN VARCHAR2,
691 p_pk1_value IN VARCHAR2,
692 p_pk2_value IN VARCHAR2,
693 p_pk3_value IN VARCHAR2,
694 p_pk4_value IN VARCHAR2,
695 p_pk5_value IN VARCHAR2,
696 p_ocs_role IN VARCHAR2,
697 p_party_ids IN FND_TABLE_OF_NUMBER,
698 p_api_caller IN VARCHAR2 := NULL,
699 x_msg_count OUT NOCOPY NUMBER,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 x_return_status OUT NOCOPY VARCHAR2
702 )
703 IS
704
705 l_api_name CONSTANT VARCHAR2(50) := 'Revoke_Attachments_OCSRole';
706 l_return_status VARCHAR2(1) ;
707 l_error_code NUMBER(1) ;
708 l_role_name FND_MENUS.MENU_NAME%TYPE;
709 l_user_name VARCHAR2(30);
710 l_service_url VARCHAR2(100);
711 l_user_login VARCHAR2(30);
712 l_protocol VARCHAR2(30);
713 get_attachments DYNAMIC_CUR;
714 l_dynamic_sql VARCHAR2(32767);
715 l_media_id NUMBER;
716 l_node_id NUMBER;
717 l_created_by NUMBER;
718 l_file_type VARCHAR2(10);
719 l_ocs_role_to_revoke VARCHAR2(30);
720 l_entity_name VARCHAR2(30);
721 l_party_type hz_parties.party_type%TYPE;
722 l_rows NUMBER := 0;
723
724 BEGIN
725
726 l_entity_name := p_entity_name;
727
728 l_dynamic_sql := ' SELECT media_id, dm_node, A.created_by, dm_type' ||
729 ' FROM FND_DOCUMENTS D, FND_ATTACHED_DOCUMENTS A' ||
730 ' WHERE A.DOCUMENT_ID = D.DOCUMENT_ID ' ||
731 ' AND A.ENTITY_NAME = :entity_name ' ||
732 ' AND A.PK1_VALUE = :pk1_value ';
733
734 IF (p_entity_name = 'DOM_DOCUMENT_REVISION') THEN
735
736 l_dynamic_sql := l_dynamic_sql || ' AND A.PK2_VALUE = :pk2_value';
737
738 l_entity_name := 'DOM_DOCUMENT_VERSION';
739
740 OPEN get_attachments FOR l_dynamic_sql
741 USING IN l_entity_name,
742 IN p_pk1_value,
743 IN p_pk2_value;
744
745 ELSE
746
747 OPEN get_attachments FOR l_dynamic_sql
748 USING IN l_entity_name,
749 IN p_pk1_value;
750
751 END IF;
752
753 LOOP
754
755 FETCH get_attachments INTO l_media_id, l_node_id, l_created_by, l_file_type;
756 EXIT WHEN get_attachments%NOTFOUND;
757
758 FOR lcount in p_party_ids.first .. p_party_ids.last
759 LOOP
760
761 l_rows := Check_For_Duplicate_Grant
762 (
763 p_entity_name => l_entity_name,
764 p_pk1_value => p_pk1_value,
765 p_pk2_value => p_pk2_value,
766 p_pk3_value => p_pk3_value,
767 p_pk4_value => p_pk4_value,
768 p_pk5_value => p_pk5_value,
769 p_file_id => l_media_id,
770 p_repos_id => l_node_id,
771 p_party_id => p_party_ids(lcount)
772 );
773
774 if( l_rows <= 1) then
775 l_ocs_role_to_revoke := G_OCS_ROLE;
776 end if;
777
778 SELECT service_url, protocol
779 INTO l_service_url, l_protocol
780 FROM DOM_REPOSITORIES WHERE id = l_node_id ;
781
782 SELECT user_name INTO l_user_name
783 FROM FND_USER where person_party_id = p_party_ids(lcount);
784
785 --Get the Attachment created by user login.
786 -- This is required to make WS connection while trying to grant role to the user.
787 SELECT user_name INTO l_user_login
788 FROM fnd_user
789 WHERE user_id = l_created_by ;
790
791 SELECT party_type INTO l_party_type
792 FROM hz_parties
793 WHERE party_id = p_party_ids(lcount);
794
795 IF(l_party_type = 'PERSON') THEN
796 l_party_type := 'USER';
797 END IF;
798
799 IF(l_ocs_role_to_revoke IS NOT NULL) THEN
800
801 IF (l_protocol = 'WEBSERVICES') THEN
802
803 DOM_WS_INTERFACE_PUB.Remove_Attachments_OCSRole
804 (
805 p_api_version => p_api_version,
806 p_service_url => l_service_url,
807 p_family_id => l_media_id,
808 p_role => l_ocs_role_to_revoke,
809 p_user_name => l_user_name,
810 p_user_login => l_user_login,
811 x_return_status => x_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data
814 );
815
816 END IF;
817
818 END IF;
819
820 -- Delete from
821 DELETE FROM DOM_FOLDER_FILE_MEMBERSHIPS
822 WHERE REPOSITORY_ID = l_node_id
823 AND REPOSITORY_ITEM_ID = l_media_id
824 AND REPOSITORY_ITEM_TYPE = l_file_type
825 AND ENTITY_NAME = l_entity_name
826 AND ( (PK1_VALUE=p_pk1_value ) OR ( (PK1_VALUE IS NULL) AND (p_pk1_value IS NULL)) )
827 AND ( (PK2_VALUE=p_pk2_value ) OR ( (PK2_VALUE IS NULL) AND (p_pk2_value IS NULL)))
828 AND ( (PK3_VALUE=p_pk3_value ) OR ( (PK3_VALUE IS NULL) AND (p_pk3_value IS NULL)) )
829 AND ( (PK4_VALUE=p_pk4_value ) OR ( (PK4_VALUE IS NULL) AND (p_pk4_value IS NULL)))
830 AND ( (PK5_VALUE=p_pk5_value ) OR ( (PK5_VALUE IS NULL) AND (p_pk5_value IS NULL)) )
831 AND PARTY_TYPE = l_party_type
832 AND PARTY_ID = p_party_ids(lcount)
833 AND OFO_ROLE = G_OCS_ROLE ;
834
835 --
836 END LOOP;
837
838
839
840 END LOOP;
841
842 CLOSE get_attachments;
843
844 -- Initialize API return status to success
845 x_return_status := FND_API.G_RET_STS_SUCCESS;
846
847 -- Standard check of p_commit.
848 IF FND_API.To_Boolean( p_commit ) THEN
849 COMMIT WORK;
850 END IF;
851
852 --
853 -- returns T if the action is success
854 -- and F on failure
855 --
856 IF ( l_return_status = FND_API.G_TRUE OR
857 l_return_status = FND_API.G_FALSE )
858 THEN
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860 ELSE
861 x_return_status := FND_API.G_RET_STS_ERROR ;
862 END IF ;
863
864 EXCEPTION
865 WHEN OTHERS THEN
866
867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
868
869 IF FND_MSG_PUB.Check_Msg_Level
870 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
871 THEN
872 FND_MSG_PUB.Add_Exc_Msg
873 (
874 G_PKG_NAME,
875 l_api_name
876 );
877 END IF;
878
879 END Revoke_Attachments_OCSRole;
880
881
882
883 PROCEDURE Get_User_Roles
884 (
885 p_object_id IN NUMBER,
886 p_document_id IN NUMBER,
887 p_revision_id IN NUMBER,
888 p_change_id IN NUMBER,
889 p_change_line_id IN NUMBER,
890 p_party_id IN NUMBER,
891 x_role_ids OUT NOCOPY FND_ARRAY_OF_NUMBER_25
892 )
893 IS
894 l_party_id NUMBER;
895
896 CURSOR get_user_roles (
897 cp_object_id NUMBER,
898 cp_document_id VARCHAR2,
899 cp_revision_id VARCHAR2,
900 cp_change_id VARCHAR2,
901 cp_change_line_id VARCHAR2) IS
902 SELECT grants.menu_id menu_id
903 FROM fnd_grants grants
904 WHERE grants.object_id = cp_object_id
905 AND ((grants.instance_pk1_value=cp_document_id )
906 OR((grants.instance_pk1_value = '*NULL*') AND (cp_document_id IS NULL)))
907 AND ((grants.instance_pk2_value=cp_revision_id )
908 OR((grants.instance_pk2_value = '*NULL*') AND (cp_revision_id IS NULL)))
909 AND ((grants.parameter1=cp_change_id )
910 OR((grants.parameter1 IS NULL) AND (cp_change_id IS NULL)))
911 AND ((grants.parameter2=cp_change_line_id )
912 OR((grants.parameter2 IS NULL) AND (cp_change_line_id IS NULL))
913 );
914
915 l_grantee_type hz_parties.party_type%TYPE;
916 l_grantee_key fnd_grants.grantee_key%TYPE;
917 l_instance_set_id fnd_grants.instance_set_id%TYPE;
918 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
919 l_dummy VARCHAR2(1);
920 l_index INTEGER;
921
922 CURSOR get_party_type (cp_party_id NUMBER)
923 IS
924 SELECT party_type
925 FROM hz_parties
926 WHERE party_id=cp_party_id;
927
928 BEGIN
929
930 l_index := 0;
931
932 OPEN get_party_type (cp_party_id =>p_party_id);
933 FETCH get_party_type INTO l_grantee_type;
934 CLOSE get_party_type;
935
936 x_role_ids := FND_ARRAY_OF_NUMBER_25();
937
938 FOR rec IN get_user_roles
939 (
940 cp_object_id => p_object_id,
941 cp_document_id => p_document_id,
942 cp_revision_id => p_revision_id,
943 cp_change_id => p_change_id,
944 cp_change_line_id => P_change_line_id
945 )
946 LOOP
947 x_role_ids.extend(l_index+1);
948 x_role_ids(l_index+1) := rec.menu_id;
949 l_index := l_index + 1;
950 END LOOP;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 NULL;
955
956 END get_user_roles;
957
958
959 FUNCTION Check_For_Duplicate_Grant
960 (
961 p_entity_name IN VARCHAR2,
962 p_pk1_value IN VARCHAR2,
963 p_pk2_value IN VARCHAR2,
964 p_pk3_value IN VARCHAR2,
965 p_pk4_value IN VARCHAR2,
966 p_pk5_value IN VARCHAR2,
967 p_file_id IN NUMBER,
968 p_repos_id IN NUMBER,
969 p_party_id IN NUMBER
970 )
971 RETURN NUMBER
972 IS
973
974 l_party_type hz_parties.party_type%TYPE;
975 l_count NUMBER := 0;
976
977 CURSOR get_party_type (cp_party_id NUMBER)
978 IS
979 SELECT party_type
980 FROM hz_parties
981 WHERE party_id=cp_party_id;
982
983 BEGIN
984
985 OPEN get_party_type (cp_party_id =>p_party_id);
986 FETCH get_party_type INTO l_party_type;
987 CLOSE get_party_type;
988
989 IF(l_party_type = 'PERSON') THEN
990 l_party_type := 'USER';
991 END IF;
992
993 SELECT count(*) INTO l_count
994 FROM DOM_FOLDER_FILE_MEMBERSHIPS
995 WHERE REPOSITORY_ITEM_ID = p_file_id
996 AND REPOSITORY_ID = p_repos_id
997 AND PARTY_ID = p_party_id
998 AND PARTY_TYPE = l_party_type
999 AND entity_name = p_entity_name;
1000 -- AND ( (pk1_value=p_pk1_value ) OR ( (pk1_value IS NULL) AND (p_pk1_value IS NULL)) )
1001 -- AND ( (pk2_value=p_pk2_value ) OR ( (pk2_value IS NULL) AND (p_pk2_value IS NULL)))
1002 -- AND ( (pk3_value=p_pk3_value ) OR ( (pk3_value IS NULL) AND (p_pk3_value IS NULL)) )
1003 -- AND ( (pk4_value=p_pk4_value ) OR ( (pk4_value IS NULL) AND (p_pk4_value IS NULL)))
1004 -- AND ( (pk5_value=p_pk5_value ) OR ( (pk5_value IS NULL) AND (p_pk5_value IS NULL)) );
1005
1006 RETURN l_count;
1007
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 RETURN l_count;
1011
1012 END Check_For_Duplicate_Grant;
1013
1014
1015 FUNCTION check_user_privilege
1016 (
1017 p_api_version IN NUMBER,
1018 p_privilege IN VARCHAR2,
1019 p_object_name IN VARCHAR2,
1020 p_instance_pk1_value IN VARCHAR2,
1021 p_instance_pk2_value IN VARCHAR2,
1022 p_instance_pk3_value IN VARCHAR2,
1023 p_instance_pk4_value IN VARCHAR2,
1024 p_instance_pk5_value IN VARCHAR2,
1025 p_party_id IN NUMBER
1026 )
1027 RETURN VARCHAR2
1028 IS
1029 -- Start OF comments
1030 -- API name : check_user_privilege
1031 -- TYPE : Public
1032 -- Pre-reqs : None
1033 -- FUNCTION : check a user's privilege on object instance(s)
1034 -- If this operation fails then the check is not
1035 -- done and error code is returned.
1036
1037 -- Version: Current Version 0.1
1038 -- Previous Version : None
1039 -- Notes :
1040 --
1041 -- END OF comments
1042
1043
1044 l_grantee_key fnd_grants.GRANTEE_KEY%TYPE;
1045 l_grantee_type fnd_grants.GRANTEE_TYPE%TYPE;
1046
1047 BEGIN
1048 l_grantee_key := 'HZ_PARTY:'||p_party_id;
1049
1050 RETURN EGO_DATA_SECURITY.check_function
1051 (
1052 p_api_version => p_api_version,
1053 p_function => p_privilege,
1054 p_object_name => p_object_name,
1055 p_instance_pk1_value => p_instance_pk1_value,
1056 p_instance_pk2_value => p_instance_pk2_value,
1057 p_instance_pk3_value => p_instance_pk3_value,
1058 p_instance_pk4_value => p_instance_pk4_value,
1059 p_instance_pk5_value => p_instance_pk5_value,
1060 p_user_name => l_grantee_key
1061 );
1062 END check_user_privilege;
1063
1064
1065 END DOM_SECURITY_PUB;