1 PACKAGE BODY POS_DATA_SECURITY AS
2 /* $Header: POSSECPB.pls 120.1 2011/11/29 00:39:02 dalu noship $ */
3
4 ----------------------------------------------
5 --This is an internal procedure. Not in spec.
6 ----------------------------------------------
7 FUNCTION get_object_id(p_object_name IN VARCHAR2) RETURN NUMBER IS
8 l_object_id NUMBER;
9 BEGIN
10 SELECT object_id
11 INTO l_object_id
12 FROM fnd_objects
13 WHERE obj_name = p_object_name;
14 RETURN l_object_id;
15 EXCEPTION
16 WHEN no_data_found THEN
17 RETURN NULL;
18 END get_object_id;
19
20 ----------------------------------------------
21 --This is an internal procedure. Not in spec.
22 ----------------------------------------------
23 FUNCTION get_group_info (p_party_id IN NUMBER) RETURN VARCHAR2 IS
24
25 CURSOR group_membership_c (cp_orig_system_id IN NUMBER) IS
26 SELECT 'HZ_GROUP:'||group_membership_rel.object_id group_name
27 FROM hz_relationships group_membership_rel
28 WHERE group_membership_rel.RELATIONSHIP_CODE = 'MEMBER_OF'
29 AND group_membership_rel.status= 'A'
30 AND group_membership_rel.start_date <= SYSDATE
31 AND NVL(group_membership_rel.end_date, SYSDATE) >= SYSDATE
32 AND group_membership_rel.subject_id = cp_orig_system_id;
33 l_group_info VARCHAR2(32767);
34 BEGIN
35 l_group_info := '';
36 FOR group_rec IN group_membership_c (p_party_id) LOOP
37 l_group_info := l_group_info ||''''||group_rec.group_name ||''' , ';
38 END LOOP;
39
40 IF( length( l_group_info ) >0) THEN
41 -- strip off the trailing ', '
42 l_group_info := SUBSTR(l_group_info, 1,
43 length(l_group_info) - length(', '));
44 ELSE
45 l_group_info := '''NULL''';
46 END IF;
47 RETURN l_group_info;
48 EXCEPTION
49 WHEN OTHERS THEN
50 RETURN '''NULL''';
51 END get_group_info;
52
53 ----------------------------------------------
54 --This is an internal procedure. Not in spec.
55 ----------------------------------------------
56
57 FUNCTION get_company_info (p_party_id IN NUMBER) RETURN VARCHAR2 IS
58 CURSOR company_membership_c (cp_orig_system_id IN NUMBER) IS
59 SELECT 'HZ_COMPANY:'||group_membership_rel.object_id company_name
60 FROM hz_relationships group_membership_rel
61 WHERE group_membership_rel.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
62 AND group_membership_rel.status = 'A'
63 AND group_membership_rel.start_date <= SYSDATE
64 AND NVL(group_membership_rel.end_date, SYSDATE) >= SYSDATE
65 AND group_membership_rel.subject_id = cp_orig_system_id;
66 l_company_info VARCHAR2(32767);
67 BEGIN
68 l_company_info := '';
69 FOR company_rec IN company_membership_c (p_party_id) LOOP
70 l_company_info := l_company_info||''''||company_rec.company_name||''' , ';
71 END LOOP;
72
73 IF( length( l_company_info ) > 0) THEN
74 -- strip off the trailing ', '
75 l_company_info := SUBSTR(l_company_info, 1,
76 length(l_company_info) - length(', '));
77 ELSE
78 l_company_info := '''NULL''';
79 END IF;
80 RETURN l_company_info;
81 EXCEPTION
82 WHEN OTHERS THEN
83 RETURN '''NULL''';
84 END get_company_info;
85
86 -- Rewrite EGO_SECURITY_PUB.get_party_privileges_d to get privileges in case of prospective suppliers
87 -- Will return x_privileges_string = null if no privileges are assigned to the user and user's default profile option
88 PROCEDURE get_privileges_prosp
89 (
90 p_supp_reg_id IN NUMBER,
91 p_user_id IN NUMBER,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_privileges_string OUT NOCOPY VARCHAR2
94 )
95 IS
96
97 TYPE privileges_csr_type IS REF CURSOR;
98
99 l_api_version CONSTANT NUMBER := 1.0;
100 l_object_name CONSTANT VARCHAR2(10) := 'HZ_PARTIES';
101 l_delimiter CONSTANT VARCHAR2(1) := ',' ;
102
103 l_user_id NUMBER;
104 l_party_id NUMBER;
105 l_role_name VARCHAR2(99);
106 l_user_name FND_GRANTS.GRANTEE_KEY%TYPE;
107
108 l_index NUMBER;
109 l_dynamic_sql VARCHAR2(32767);
110 l_privilege VARCHAR2(480);
111
112 l_group_info VARCHAR2(32767);
113 l_company_info VARCHAR2(32767);
114 l_object_id NUMBER;
115
116 l_privileges_csr privileges_csr_type;
117
118
119 l_prof_privilege_tbl EGO_VARCHAR_TBL_TYPE;
120 l_privilege_tbl EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE;
121
122 BEGIN
123
124 -- get proxy user's user id in case of 'GUEST' user
125 IF (p_user_id = 6) THEN
126 l_user_id := FND_PROFILE.VALUE('POS_SM_PROSPECT_PROXY_LOGIN');
127 ELSE
128 l_user_id := p_user_id;
129 END IF;
130
131 -- Following code is modified from EGO_DATA_SECURITY.get_functions
132
133 -- Step 1:
134 -- get all the privileges set by the profile option
135 l_index := 0;
136
137 IF (p_user_id = 6) THEN -- In case of 'GUEST' user, get default supplier profile option
138 l_role_name := FND_PROFILE.VALUE('POS_SM_DEFAULT_ROLE_SUPP');
139 ELSE
140 l_role_name := FND_PROFILE.VALUE('POS_SM_DEFAULT_ROLE_INTERNAL');
141 END IF;
142
143 EGO_DATA_SECURITY.get_role_functions
144 (p_api_version => l_api_version,
145 p_role_name => l_role_name,
146 x_return_status => x_return_status,
147 x_privilege_tbl => l_prof_privilege_tbl
148 );
149
150 IF (x_return_status = 'T') THEN --- 'T' is defined as success in EGO_DATA_SECURITY
151 IF (l_prof_privilege_tbl.COUNT > 0) THEN
152 FOR i IN l_prof_privilege_tbl.first .. l_prof_privilege_tbl.last LOOP
153 l_privilege_tbl(i) := l_prof_privilege_tbl(i);
154 END LOOP;
155 END IF;
156 l_index := l_prof_privilege_tbl.COUNT;
157 END IF;
158
159 --end of getting privileges from profile option
160
161 -- Step 2:
162 -- get All privileges of a user on a given object
163 -- Skip this step if in 'GUEST' user case, no value is set for the profile option POS_SM_PROSPECT_PROXY_LOGIN
164
165 IF l_user_id IS NOT NULL THEN
166
167 SELECT person_party_id INTO l_party_id
168 FROM fnd_user users
169 WHERE users.user_id = l_user_id;
170
171 l_user_name :='HZ_PARTY:'|| l_party_id;
172
173 l_object_id := get_object_id(p_object_name => l_object_name);
174
175 -- pre-fetch company/group info
176
177 l_group_info := get_group_info(p_party_id => l_party_id);
178 l_company_info := get_company_info(p_party_id => l_party_id);
179
180 l_dynamic_sql :=
181 'SELECT DISTINCT fnd_functions.function_name ' ||
182 ' FROM fnd_grants grants, ' ||
183 ' fnd_form_functions fnd_functions, ' ||
184 ' fnd_menu_entries cmf, '||
185 ' fnd_object_instance_sets sets ' ||
186 ' WHERE grants.object_id = :object_id ' ||
187 ' AND grants.start_date <= SYSDATE ' ||
188 ' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
189 ' AND ( ( grants.grantee_type = ''USER'' AND ' ||
190 ' grants.grantee_key = :user_name ) '||
191 ' OR (grants.grantee_type = ''GROUP'' AND ' ||
192 ' grants.grantee_key in ( '||l_group_info||' )) ' ||
193 ' OR (grants.grantee_type = ''COMPANY'' AND ' ||
194 ' grants.grantee_key in ( '||l_company_info||' )) ' ||
195 ' OR (grants.grantee_type = ''GLOBAL'' AND ' ||
196 ' grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL'') ))' ||
197 ' AND cmf.function_id = fnd_functions.function_id ' ||
198 ' AND cmf.menu_id = grants.menu_id ' ||
199 ' AND grants.instance_set_id = sets.instance_set_id ' ||
200 ' AND grants.instance_type = ''SET'' ' ||
201 ' AND sets.instance_set_name = ''ORGANIZATION'' '; -- Users need to set up it during grant process
202
203 OPEN l_privileges_csr FOR l_dynamic_sql
204 USING IN l_object_id,
205 IN l_user_name;
206
207 LOOP
208 FETCH l_privileges_csr INTO l_privilege;
209 EXIT WHEN l_privileges_csr%NOTFOUND;
210 l_index := l_index+1;
211 l_privilege_tbl(l_index) := l_privilege;
212 END LOOP;
213 CLOSE l_privileges_csr;
214
215 END IF;
216
217 -- Step 3:
218 -- Collect all privileges
219 x_privileges_string := '';
220
221 IF l_privilege_tbl.count > 0 THEN
222 FOR i in l_privilege_tbl.first .. l_privilege_tbl.last LOOP
223 x_privileges_string := x_privileges_string || l_privilege_tbl(i) || l_delimiter;
224 END LOOP;
225
226 x_privileges_string := substr(x_privileges_string, 1,
227 length(x_privileges_string) - length(l_delimiter));
228 END IF;
229
230 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Return Success no matter the privilege list is empty or not
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235
236 END get_privileges_prosp;
237
238 END POS_DATA_SECURITY;
[Home] [Help]