1 package PA_SECURITY_PVT AUTHID CURRENT_USER AS
2 /* $Header: PASECPVS.pls 120.6 2011/09/19 22:13:58 skkoppul ship $ */
3
4
5 /* TYPE sec_flag_record IS RECORD (
6 user_id number,
7 object_key number,
8 object_name varchar2(30),
9 sec_resp_flag varchar2(1)) ;
10 TYPE sec_flag_TABTYPE IS TABLE OF sec_flag_record
11 INDEX BY BINARY_INTEGER ;*/
12
13
14 ---This is the generic security API which is used for
15 ---function security check. It applies all functions
16 ---except confirm assignment function
17 -- Procedure Check User Privilege
18 -----------------------------------
19 Procedure check_user_privilege
20 (
21 p_privilege IN VARCHAR2,
22 p_object_name IN VARCHAR2,
23 p_object_key IN NUMBER,
24 x_ret_code out NOCOPY varchar2, --File.Sql.39 bug 4440895
25 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
26 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
27 x_msg_data out NOCOPY varchar2, --File.Sql.39 bug 4440895
28 p_init_msg_list IN VARCHAR2 DEFAULT 'Y') ; -- Added for Bug 4319137
29
30
31 /* ---This API check is role based security is enforced or not
32 ----Procedure check_sec_by_role
33 -----------------------------------------
34 function check_sec_by_role
35 (
36 p_user_id in number,
37 p_object_name in varchar2,
38 p_source_type in varchar2,
39 p_object_key in number ) return varchar2 ;*/
40
41 ---This API check if responsibility based security is enforced or not
42 ----function check_sec_by_resp
43 -----------------------------------------
44 function check_sec_by_resp
45 (
46 p_user_id in number,
47 p_object_name in varchar2,
48 p_source_type in varchar2,
49 p_object_key in number ) return varchar2 ;
50
51 ----This API wrapps all logic for check the
52 ----confirm assignment privilege.
53 ---Procedure check_confirm_asmt
54 --------------------------------------------------
55 procedure check_confirm_asmt
56 (p_project_id in number,
57 p_resource_id in number,
58 p_resource_name in varchar2,
59 p_privilege in varchar2,
60 p_start_date in date DEFAULT SYSDATE,
61 p_init_msg_list IN VARCHAR2 DEFAULT 'T' , -- Added for bug 5130421
62 x_ret_code out NOCOPY varchar2, --File.Sql.39 bug 4440895
63 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
64 x_msg_count out NOCOPY varchar2, --File.Sql.39 bug 4440895
65 x_msg_data out NOCOPY varchar2 ); --File.Sql.39 bug 4440895
66
67 ----This API is for getting the resource organization id
68 ------Procedure get_resource_org_id
69 ------------------------------------------------
70 procedure get_resource_org_id
71 (p_resource_id in number,
72 p_start_date in date,
73 x_resource_org_id out NOCOPY number, --File.Sql.39 bug 4440895
74 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
75 x_error_message_code out NOCOPY varchar2 ); --File.Sql.39 bug 4440895
76
77
78 -----This API is for getting the project owning organization
79 ------Procedure get_project_org_id
80 ------------------------------------------------
81 procedure get_project_org_id
82 (p_project_id in number,
83 x_project_org_id out NOCOPY number, --File.Sql.39 bug 4440895
84 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
85 x_error_message_code out NOCOPY varchar2 ); --File.Sql.39 bug 4440895
86
87 -----This procedure checks if the p_manager_id is a manager
88 -----of p_person_id in HR
89 ------Procedure check_manager_relation
90 ------------------------------------------------
91 procedure check_manager_relation
92 (p_person_id in number,
93 p_manager_id in number,
94 p_start_date in date,
95 x_ret_code out NOCOPY varchar2, --File.Sql.39 bug 4440895
96 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
97 x_error_message_code out NOCOPY varchar2 ); --File.Sql.39 bug 4440895
98
99
100
101 ----Parameters Description for the following APIs
102 ---p_commit DEFAULT FND_API.G_FALSE
103 ---p_debug_mode default 'N'
104 ---p_source_type: 'EMPLOYEE" or 'PARTY'
105 ---p_party_id: employee_id or party_id
106 ---p_role_name: meaning column in pa_project_role_types table
107 ---p_object_key_type: 'INSTANCE' or 'INSTANCE_SET'
108 ---p_object_key: project_id, task_id, org_id, etc.
109 ---For update_role and lock_grant, either pass in grant_id or these parameters:
110 ---p_role_name_old, p_object_name_old, p_object_key_type_old,
111 ---p_object_key_old, p_party_id_old, p_source_type_old and p_start_date_old
112 ---For revoke_grant, either pass in grant_id or these parameters:
113 --p_role_name, p_object_name, p_object_key_type, p_object_key,
114 --p_party_id,p_source_type,p_start_date
115
116 ---Procedure Description:
117 ---Update_role only updates the start date and end date
118 ---Revoke_grant deletes a specific record in fnd_grants, which has the unique combination of
119 ---p_role_name,p_object_name,p_object_key_type,p_object_key,p_party_id,p_source_type,p_start_date
120 ---Revoke_role deletes the records in fnd_grants , which have the combintion of
121 ---p_role_name,p_object_name,,p_object_key_type,p_object_key,p_party_id,p_source_type.
122 ---no matter what the start_date is.
123 PROCEDURE grant_org_authority
124 (
125 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
126 p_debug_mode in varchar2 default 'N',
127 p_project_role_id IN number,
128 p_menu_name in varchar2,
129 p_object_name IN VARCHAR2,
130 p_object_key_type IN VARCHAR2,
131 p_object_key IN NUMBER,
132 p_party_id IN NUMBER,
133 p_source_type in varchar2,
134 p_start_date IN DATE,
135 p_end_date IN DATE,
136 x_grant_guid out NOCOPY raw, --File.Sql.39 bug 4440895
137 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
138 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
139 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
140 );
141
142 PROCEDURE grant_role
143 (
144 p_commit IN VARCHAR2 := FND_API.G_FALSE,
145 p_debug_mode in varchar2 default 'N',
146 p_project_role_id IN number,
147 p_object_name IN VARCHAR2,
148 p_instance_type IN VARCHAR2,
149 p_object_key IN NUMBER,
150 p_party_id IN NUMBER,
151 p_source_type IN varchar2,
152 x_grant_guid OUT NOCOPY raw, --File.Sql.39 bug 4440895
153 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
154 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
155 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
156 );
157
158 PROCEDURE revoke_grant
159 (
160 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
161 p_debug_mode in varchar2 default 'N',
162 p_grant_guid in raw,
163 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
164 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
165 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
166 );
167
168
169 PROCEDURE revoke_role
170 (
171 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
172 p_debug_mode in varchar2 default 'N',
173 p_project_role_id IN number,
174 p_menu_name in varchar2,
175 p_object_name IN VARCHAR2,
176 p_object_key_type IN VARCHAR2,
177 p_object_key IN NUMBER,
178 p_party_id IN NUMBER,
179 p_source_type in varchar2,
180 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
181 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
182 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
183 );
184
185 -- obsolete API
186 PROCEDURE update_role
187 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
188 p_debug_mode in varchar2 default 'N',
189 p_grant_guid in raw,
190 p_project_role_id_old IN number default null,
191 p_object_name_old IN VARCHAR2 default null,
192 p_object_key_type_old IN VARCHAR2 default null,
193 p_object_key_old IN NUMBER default null,
194 p_party_id_old IN NUMBER default null,
195 p_source_type_old in varchar2 default null,
196 p_start_date_old IN DATE default null,
197 p_start_date_new IN DATE default null,
198 p_end_date_new IN DATE,
199 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
200 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
201 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
202 );
203
204 PROCEDURE lock_grant
205 (
206 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
207 p_debug_mode in varchar2 default 'N',
208 p_grant_guid in raw,
209 p_project_role_id_old IN number default null,
210 p_object_name_old IN VARCHAR2 default null,
211 p_object_key_type_old IN VARCHAR2 default null,
212 p_object_key_old IN number default null,
213 p_party_id_old IN NUMBER default null,
214 p_source_type_old in varchar2 default null,
215 p_start_date_old IN DATE default null,
216 p_project_role_id IN number,
217 p_party_id IN NUMBER,
218 p_source_type in varchar2,
219 p_object_name IN VARCHAR2,
220 p_object_key_type IN VARCHAR2,
221 p_object_key IN number,
222 p_start_date IN DATE,
223 p_end_date IN DATE,
224 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
225 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
226 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
227 );
228
229 FUNCTION get_instance_set_id (p_set_name in varchar2) return number;
230
231 ---This function is for pre-seeded roles. The project_role_id
232 ----for pre-seeded roles are fixed
233 FUNCTION get_menu_name (p_project_role_id in number) return varchar2 ;
234
235 ----This function is obsoleted because of translation issue with the pre-seeded roles
236 --FUNCTION get_menu_name (p_project_role_name in varchar2) return varchar2;
237
238 FUNCTION get_menu_id (p_menu_name in varchar2) return number;
239
240 ------This function is only for internal use
241 FUNCTION get_menu_id_for_role(p_project_role_id in number) return number;
242
243 FUNCTION get_proj_role_name(p_project_role_id in number) return varchar2;
244 Function get_party_id return number;
245 FUNCTION is_role_exists ( p_object_name IN FND_OBJECTS.OBJ_NAME%TYPE
246 ,p_object_key_type IN FND_GRANTS.INSTANCE_TYPE%TYPE DEFAULT 'INSTANCE'
247 ,p_role_id IN FND_MENUS.MENU_ID%TYPE
248 ,p_object_key IN FND_GRANTS.INSTANCE_PK1_VALUE%TYPE
249 ,p_party_id IN NUMBER
250 ) RETURN BOOLEAN;
251
252 --------FUNCTION check_user_privilege
253 ---This function will be used in select statement in some of the PRM pages
254 FUNCTION check_user_privilege
255 (p_privilege in varchar2,
256 p_object_name in varchar2,
257 p_object_key in number,
258 p_init_msg_list IN VARCHAR2 DEFAULT 'Y') return varchar2 ;
259 -- p_init_msg_list Added for Bug 4319137
260
261 ---------PROCEDURE check_access_exist
262 --Check where the user has access to any object with the given privilege
263 PROCEDURE check_access_exist(p_privilege IN VARCHAR2,
264 p_object_name IN VARCHAR2,
265 x_ret_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
266 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
267 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
268 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
269
270 FUNCTION get_grantee_key(
271 p_source_type IN VARCHAR2 DEFAULT 'USER',
272 p_source_id IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
273 p_HZ_WF_Synch IN VARCHAR2 DEFAULT 'N') -- Modified default parameter to 'N' for bug 3471913
274 RETURN VARCHAR2;
275
276 FUNCTION get_resource_source_id return NUMBER;
277 FUNCTION get_resource_type_id return NUMBER;
278 FUNCTION get_project_system_status_code return VARCHAR2;
279
280 PROCEDURE check_grant_exists(p_project_role_id in NUMBER,
281 p_instance_type in fnd_grants.INSTANCE_TYPE%TYPE,
282 p_instance_set_name in fnd_object_instance_sets.instance_set_name%TYPE,
283 p_grantee_type in fnd_grants.GRANTEE_TYPE%TYPE,
284 p_grantee_key in fnd_grants.GRANTEE_KEY%TYPE,
285 x_instance_set_id out NOCOPY NUMBER, --File.Sql.39 bug 4440895
286 x_ret_code out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
287 );
288
289 ----------------------------------------------------------------------
290 -- The APIs below will be used by Roles form:
291 -- 1. update_menu
292 -- 2. revoke_role_based_sec
293 -- 3. grant_role_based_sec
294 -- 4. revoke_status_based_sec
295 -- 5. update_status_based_sec
296 ----------------------------------------------------------------------
297
298 -- This API is called when the default Menu is changed
299 -- in Roles form for existing roles which are in use.
300 PROCEDURE update_menu
301 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
302 p_debug_mode in varchar2 default 'N',
303 p_project_role_id IN number,
304 p_menu_id IN number,
305 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
306 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
307 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
308 );
309
310 -- This API is called when Enforce Role-based Security checkbox
311 -- is unchecked in Roles form for existing roles which are in use.
312 PROCEDURE revoke_role_based_sec
313 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
314 p_debug_mode in varchar2 default 'N',
315 p_project_role_id IN number,
316 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
317 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
318 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
319 );
320
321 -- This API is called when Enforce Role-based Security checkbox
322 -- is checked in Roles form for existing roles which are in use.
323 PROCEDURE grant_role_based_sec
324 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
325 p_debug_mode in varchar2 default 'N',
326 p_project_role_id IN number,
327 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
328 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
329 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
330 );
331
332 -- This API is called when Status Level is changed
333 -- in Roles form for existing roles which are in use.
334 PROCEDURE revoke_status_based_sec
335 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
336 p_debug_mode in varchar2 default 'N',
337 p_project_role_id IN number,
338 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
339 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
340 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
341 );
342
343 -- This API is called when status/menu under Project Status is changed
344 -- in Roles form for existing roles which are in use.
345 PROCEDURE update_status_based_sec
346 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
347 p_debug_mode in varchar2 default 'N',
348 p_project_role_id IN number,
349 p_status_level IN pa_project_role_types_b.status_level%TYPE,
350 p_new_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
351 p_new_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
352 p_new_menu_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
353 p_new_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
354 p_mod_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
355 p_mod_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
356 p_mod_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
357 p_mod_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
358 p_del_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
359 p_del_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
360 p_del_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
361 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
362 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
363 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
364 );
365
366 Procedure get_resp_with_access
367 (
368 p_privilege IN VARCHAR2,
369 x_has_access out NOCOPY varchar2,
370 x_resp_key out NOCOPY VARCHAR2,
371 x_appl_short_name out NOCOPY VARCHAR2,
372 x_return_status out NOCOPY varchar2,
373 x_msg_count out NOCOPY number,
374 x_msg_data out NOCOPY varchar2);
375
376 end PA_SECURITY_PVT;
[Home] [Help]