[Home] [Help]
PACKAGE BODY: APPS.JTF_AUTH_SECURITY_PKG
Source
1 PACKAGE BODY jtf_auth_security_pkg AS
2 /* $Header: JTFSEASB.pls 120.2 2005/10/24 05:03:34 psanyal ship $ */
3 PROCEDURE check_permission (
4 x_flag OUT NOCOPY /* file.sql.39 change */ NUMBER,
5 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6 p_user_name IN VARCHAR2,
7 p_domain_name IN VARCHAR2 DEFAULT 'CRM_DOMAIN',
8 p_permission_name IN VARCHAR2
9 ) IS
10
11 l_cnt NUMBER;
12 l_user_name VARCHAR2(255);
13 l_domain_name VARCHAR2(255);
14 l_permission_name VARCHAR2(255);
15
16 BEGIN
17
18 l_cnt := 0;
19 l_user_name := UPPER(p_user_name);
20 l_domain_name := UPPER(p_domain_name);
21 l_permission_name := p_permission_name;
22
23 SELECT COUNT(*) INTO l_cnt
24 FROM JTF_AUTH_PERMISSIONS_B A,
25 JTF_AUTH_PRINCIPAL_MAPS B,
26 JTF_AUTH_ROLE_PERMS C,
27 JTF_AUTH_PRINCIPALS_B D,
28 JTF_AUTH_PRINCIPALS_B E,
29 JTF_AUTH_DOMAINS_B F
30 WHERE D.PRINCIPAL_NAME = l_user_name
31 AND D.JTF_AUTH_PRINCIPAL_ID =B.JTF_AUTH_PRINCIPAL_ID
32 AND B.JTF_AUTH_PARENT_PRINCIPAL_ID = E.JTF_AUTH_PRINCIPAL_ID
33 AND E.JTF_AUTH_PRINCIPAL_ID = C.JTF_AUTH_PRINCIPAL_ID
34 AND C.JTF_AUTH_PERMISSION_ID = A.JTF_AUTH_PERMISSION_ID
35 AND B.JTF_AUTH_DOMAIN_ID = F.JTF_AUTH_DOMAIN_ID
36 AND F.DOMAIN_NAME = l_domain_name
37 AND A.PERMISSION_NAME = l_permission_name;
38
39 IF (l_cnt>0) THEN
40 x_flag := 1;
41 ELSE
42 x_flag := 0;
43 END IF;
44
45 x_return_status := fnd_api.g_ret_sts_success;
46
47 EXCEPTION
48
49 -- this shouldn't happen but if it does, its safe to return true
50
51 WHEN TOO_MANY_ROWS THEN
52 x_return_status := fnd_api.g_ret_sts_success;
53 x_flag := 1;
54
55 WHEN OTHERS THEN
56 x_return_status := fnd_api.g_ret_sts_unexp_error;
57
58 -- do we need to throw an exception?
59 -- fnd_message.set_name('JTF', '..');
60 -- app_exception.raise_exception;
61
62 END check_permission;
63
64
65
66
67 procedure assign_perm (
68 ROLE_NAME in VARCHAR2,
69 PERM_NAME in VARCHAR2,
70 APP_ID in NUMBER
71 ) is
72
73 cnt NUMBER;
74 U_ROLE_NAME VARCHAR2(255);
75
76 begin
77 U_ROLE_NAME := UPPER(ROLE_NAME);
78
79 select count(*) into cnt
80 from jtf_auth_principals_b
81 where principal_name=U_ROLE_NAME and is_user_flag=0;
82
83 if cnt=0 then
84 fnd_message.set_name('JTF', 'JTF-1061');
85 app_exception.raise_exception;
86 end if;
87
88 select count(*) into cnt
89 from JTF_AUTH_PERMISSIONS_B
90 where PERMISSION_NAME=PERM_NAME;
91
92 if cnt=0 then
93 fnd_message.set_name('JTF', 'JTF-1062');
94 app_exception.raise_exception;
95 end if;
96
97
98 select count(*) into cnt
99 from JTF_AUTH_PRINCIPALS_B A,
100 JTF_AUTH_PERMISSIONS_B B,
101 JTF_AUTH_ROLE_PERMS C
102 where A.JTF_AUTH_PRINCIPAL_ID = C.JTF_AUTH_PRINCIPAL_ID and
103 B.JTF_AUTH_PERMISSION_ID = C.JTF_AUTH_PERMISSION_ID and
104 A.PRINCIPAL_NAME=U_ROLE_NAME and
105 B.PERMISSION_NAME=PERM_NAME and
106 A.IS_USER_FLAG=0;
107
108 if cnt=0 then
109 insert into JTF_AUTH_ROLE_PERMS(JTF_AUTH_ROLE_PERMISSION_ID, JTF_AUTH_PRINCIPAL_ID, JTF_AUTH_PERMISSION_ID,
110 POSITIVE_FLAG, OWNERBASED_FLAG,
111 CREATED_BY, CREATION_DATE,
112 LAST_UPDATED_BY, LAST_UPDATE_DATE,
113 APPLICATION_ID,OBJECT_VERSION_NUMBER)
114 select jtf_auth_s1.nextval, A.JTF_AUTH_PRINCIPAL_ID, B.JTF_AUTH_PERMISSION_ID,
115 1,0, 0, SYSDATE, 0, SYSDATE, APP_ID,1
116 from JTF_AUTH_PRINCIPALS_B A, JTF_AUTH_PERMISSIONS_B B
117 where A.PRINCIPAL_NAME=U_ROLE_NAME and
118 B.PERMISSION_NAME=PERM_NAME and
119 A.IS_USER_FLAG=0;
120
121 end if;
122
123
124 end assign_perm;
125
126
127
128 END jtf_auth_security_pkg;