[Home] [Help]
PACKAGE BODY: APPS.PA_SECURITY_PVT
Source
1 package body PA_SECURITY_PVT AS
2 /* $Header: PASECPVB.pls 120.17 2011/09/19 22:12:02 skkoppul ship $ */
3 G_PKG_NAME varchar2(30) :='PA_SECURITY_PVT';
4 G_responsibility_id NUMBER :=FND_GLOBAL.RESP_ID;
5 G_user_id NUMBER:=FND_GLOBAL.USER_ID;
6 G_source_type VARCHAR2(30) := '';
7 G_source_id NUMBER;
8 G_grantee_key VARCHAR2(240) := '';
9 G_debug_flag varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
10 G_source_type_id NUMBER;
11 G_project_system_status_code VARCHAR2(30) := '';
12 G_project_roles_ins_set_id NUMBER;
13 G_project_roles_ins_set_name FND_OBJECT_INSTANCE_SETS.instance_set_name%TYPE := 'PA_PROJECT_ROLES';
14
15 l_api_version number :=1.0;
16 l_errorcode number;
17
18 /*** ----------------------------------------------- */
19 procedure Init_global
20 is
21 l_emp_id NUMBER;
22 l_cust_id NUMBER;
23 BEGIN
24 G_responsibility_id := FND_GLOBAL.RESP_ID;
25 G_user_id := FND_GLOBAL.USER_ID;
26 G_debug_flag := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
27 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
28
29 SELECT employee_id, person_party_id -- Bug 4527617. Replaced customer_id with person_party_id.
30 INTO l_emp_id, l_cust_id
31 FROM fnd_user
32 WHERE user_id=G_user_id;
33
34 IF l_emp_id IS NOT NULL AND l_emp_id > 0 THEN
35 G_source_type := 'PERSON';
36 G_source_id := l_emp_id;
37 G_grantee_key := get_grantee_key('PERSON',l_emp_id,'Y'); -- Added 'Y' parameter for bug 3471913
38 G_source_type_id := 101;
39 ELSIF l_cust_id IS NOT NULL AND l_cust_id > 0 THEN
40 G_source_type := 'HZ_PARTY';
41 G_source_id := l_cust_id;
42 G_grantee_key := get_grantee_key('HZ_PARTY',l_cust_id,'Y'); -- Added 'Y' parameter for bug 3471913
43 G_source_type_id := 112;
44 END IF;
45 END;
46
47 FUNCTION get_resource_source_id RETURN NUMBER
48 IS
49 BEGIN
50 --Bug#11778245 - Addition starts
51 IF G_source_id is null OR (G_user_id is not null and G_user_id<>FND_GLOBAL.USER_ID) THEN
52 Init_global;
53 END IF;
54 --Bug#11778245 - Addition end
55 RETURN G_source_id;
56 END get_resource_source_id;
57
58 FUNCTION get_resource_type_id RETURN NUMBER
59 IS
60 BEGIN
61 --Bug#11778245 - Addition starts
62 IF G_source_type_id is null OR (G_user_id is not null and G_user_id<>FND_GLOBAL.USER_ID) THEN
63 Init_global;
64 END IF;
65 --Bug#11778245 - Addition end
66 RETURN G_source_type_id;
67 END get_resource_type_id;
68
69 FUNCTION get_project_system_status_code RETURN VARCHAR2
70 IS
71 BEGIN
72 RETURN G_project_system_status_code;
73 END get_project_system_status_code;
74
75 FUNCTION get_grantee_key(
76 p_source_type IN VARCHAR2 DEFAULT 'USER',
77 p_source_id IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
78 p_HZ_WF_Synch IN VARCHAR2 DEFAULT 'N') -- Modified default value to 'N' for bug 3471913
79 RETURN VARCHAR2 IS
80 l_emp_id NUMBER;
81 l_cust_id NUMBER;
82 l_grantee_key VARCHAR2(240) := '';
83
84 /* Bug 3484332 - Reverted the outer join fix for performance */
85
86 cursor get_role_for_employee(c_emp_id NUMBER) is
87 select /*+ leading(PER) index(PER PER_PEOPLE_F_PK) */ wfr.name, per.party_id
88 from per_all_people_f per,
89 wf_roles wfr
90 where per.person_id = c_emp_id
91 and per.party_id = wfr.orig_system_id /* Added outer join for bug 3417803 */
92 and wfr.orig_system = 'HZ_PARTY' /* Added outer join for bug 3417803 */
93 and rownum = 1;
94
95 cursor get_role_for_customer(c_cust_id NUMBER) is
96 select name
97 from wf_roles
98 where orig_system_id = c_cust_id
99 and orig_system = 'HZ_PARTY'
100 and rownum = 1;
101
102 /* Added for bug 3484332 */
103 CURSOR get_party_id(c_emp_id NUMBER) IS
104 SELECT per.party_id
105 FROM per_all_people_f per
106 WHERE per.person_id = c_emp_id;
107
108 BEGIN
109 IF p_source_type='USER' THEN
110 SELECT employee_id, person_party_id -- Bug 4527617. Replaced customer_id with person_party_id.
111 INTO l_emp_id, l_cust_id
112 FROM fnd_user
113 WHERE user_id=p_source_id;
114
115 IF l_emp_id IS NOT NULL AND l_emp_id > 0 THEN
116 OPEN get_role_for_employee(l_emp_id);
117 FETCH get_role_for_employee into l_grantee_key, l_cust_id;
118 CLOSE get_role_for_employee;
119
120 ELSIF l_cust_id IS NOT NULL AND l_cust_id > 0 THEN
121 OPEN get_role_for_customer(l_cust_id);
122 FETCH get_role_for_customer into l_grantee_key;
123 CLOSE get_role_for_customer;
124
125 END IF;
126
127 ELSIF p_source_type='PERSON' THEN
128 OPEN get_role_for_employee(p_source_id);
129 FETCH get_role_for_employee into l_grantee_key, l_cust_id;
130 CLOSE get_role_for_employee;
131 l_emp_id := p_source_id; -- Added for bug 3484332
132
133 ELSIF p_source_type='HZ_PARTY' THEN
134 OPEN get_role_for_customer(p_source_id);
135 FETCH get_role_for_customer into l_grantee_key;
136 CLOSE get_role_for_customer;
137 l_cust_id := p_source_id;
138 END IF;
139
140 -- Invoke TCA API to create WF_ROLES if it does not exists
141 /* Bug 3417803 - Modified condition l_grantee_key = '' to l_grantee_key is null */
142 IF (l_grantee_key is null OR l_grantee_key = '')
143 AND p_HZ_WF_Synch = 'Y' THEN
144 /* Added get_party_id code and if condition for bug 3484332 */
145 IF l_cust_id is null THEN
146 OPEN get_party_id(l_emp_id);
147 FETCH get_party_id into l_cust_id;
148 CLOSE get_party_id;
149 END IF;
150 HZ_WF_Synch.SynchPersonWFRole(partyid => l_cust_id);
151 OPEN get_role_for_customer(l_cust_id);
152 FETCH get_role_for_customer into l_grantee_key;
153 CLOSE get_role_for_customer;
154
155 END IF;
156
157 RETURN l_grantee_key;
158 END get_grantee_key;
159
160 /*** ----------------------------------------------- */
161
162 ---This is the generic security API which is used for
163 ---function security check. It applies all functions
164 ---except confirm assignment function
165 --- Procedure Check User Privilege
166 ----------------------------------------------------
167 Procedure check_user_privilege
168 (
169 p_privilege IN VARCHAR2,
170 p_object_name IN VARCHAR2,
171 p_object_key IN NUMBER,
172 x_ret_code out NOCOPY varchar2, --File.Sql.39 bug 4440895
173 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
174 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
175 x_msg_data out NOCOPY varchar2, --File.Sql.39 bug 4440895
176 p_init_msg_list IN VARCHAR2 DEFAULT 'Y') is
177
178 -- secure_role_flag varchar2(1);
179 secure_resp_flag varchar2(1);
180 v_fnd_api_ret_code varchar2(1);
181 i BINARY_INTEGER;
182 l_exist_flag varchar2(1):='F';
183
184 Begin
185 Init_global ;
186
187 --Clear the global PL/SQL message table
188 IF p_init_msg_list = 'Y' THEN
189 FND_MSG_PUB.initialize;
190 END IF;
191
192 pa_debug.Init_err_stack ( 'Check User Privilege');
193 x_msg_count :=0;
194 x_msg_data:= null;
195 x_return_status:=fnd_api.g_ret_sts_success;
196 x_ret_code:=fnd_api.g_true;
197
198 ------- Check for License
199 IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
200 x_ret_code:= fnd_api.g_false;
201 x_return_status:=fnd_api.g_ret_sts_success;
202 RETURN;
203 END IF;
204 ------- End check for License
205
206 If nvl(p_object_key,-999) =-999 then
207 -----Not in object instance context. Check responbility level
208 ------function security
209 pa_debug.G_err_stage := 'check responsibility level security: not in object instance context';
210 IF G_debug_flag = 'Y' THEN
211 pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
212 END IF;
213 if fnd_function.test(p_privilege) then
214 x_ret_code:=fnd_api.g_true;
215 x_return_status:=fnd_api.g_ret_sts_success;
216 else
217 x_ret_code:=fnd_api.g_false;
218 x_return_status:=fnd_api.g_ret_sts_success;
219 end if;
220 return;
221 end if;
222
223 --------check role based security ------------
224 pa_debug.G_err_stage := 'check role based security';
225 -- store project system status code in global variable for instance set
226 IF p_object_name = 'PA_PROJECTS' THEN
227 select project_system_status_code into G_project_system_status_code
228 from pa_projects_all ppa,
229 pa_project_statuses pps
230 where ppa.project_status_code = pps.project_status_code
231 and ppa.project_id = p_object_key;
232 END IF;
233
234 IF G_debug_flag = 'Y' THEN
235 pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
236 END IF;
237 --dbms_output.put_line('before: calling fnd_data_sec');
238 --dbms_output.put_line('get_grantee_key:'||get_grantee_key);
239 --dbms_output.put_line('p_privilege:'||p_privilege);
240 --dbms_output.put_line('p_object_name:'||p_object_name);
241 --dbms_output.put_line('p_object_key:'||p_object_key);
242
243
244 v_fnd_api_ret_code:=fnd_data_security.check_function(
245 p_api_version =>l_api_version,
246 p_function =>p_privilege,
247 p_object_name => p_object_name,
248 p_instance_pk1_value=>p_object_key,
249 p_instance_pk2_value => NULL,
250 p_instance_pk3_value => NULL,
251 p_instance_pk4_value => NULL,
252 p_instance_pk5_value => NULL );
253 -- p_user_name => get_grantee_key ); Commented for Bug 4498436.
254
255 --dbms_output.put_line('after calling fnd_data_sec');
256 --dbms_output.put_line('v_fnd_api_ret_code:'||v_fnd_api_ret_code);
257 if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
258 OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
259 x_ret_code:=fnd_api.g_false;
260 x_return_status:=fnd_api.g_ret_sts_unexp_error;
261 FND_MSG_PUB.Count_And_Get
262 (p_count => x_msg_count ,
263 p_data => x_msg_data
264 );
265 return;
266 end if;
267
268 if v_fnd_api_ret_code=fnd_api.g_true then
269 x_ret_code:= fnd_api.g_true;
270 x_return_status:=fnd_api.g_ret_sts_success;
271 return;
272 end if;
273
274 if v_fnd_api_ret_code=fnd_api.g_false then
275 x_ret_code:= fnd_api.g_false;
276 x_return_status:=fnd_api.g_ret_sts_success;
277 ----not return from here. need to continue to do
278 ----responsibility level check
279 end if;
280 -----------End of check role based security-----------
281
282 pa_debug.G_err_stage := 'get secure_resp_flag';
283 IF G_debug_flag = 'Y' THEN
284 pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
285 END IF;
286
287 -----check if responsibility based security is enforced
288 ---(check if any of the roles the user plays on the object
289 ----does not have a menu_id or the user does not play any role on the object)
290 IF p_object_name='PA_PROJECTS' THEN
291 -----------fixing bug 1484710-------------------
292 PA_SECURITY.Initialize(X_user_id =>G_user_id ,
293 X_calling_module => 'PAXPREPR');
294 ----------end of fixing bug 1484710------------
295 END IF;
296
297 secure_resp_flag:=check_sec_by_resp
298 (
299 G_user_id ,
300 p_object_name ,
301 G_source_type ,
302 p_object_key );
303
304
305 if secure_resp_flag =FND_API.G_RET_STS_UNEXP_ERROR then
306 x_ret_code:=fnd_api.g_false;
307 x_return_status:=fnd_api.g_ret_sts_unexp_error;
308
309 FND_MSG_PUB.Count_And_Get
310 (p_count => x_msg_count ,
311 p_data => x_msg_data
312 );
313 return;
314 end if;
315
316 -----Check responsibility level function security in these cases:
317 -----1. The user plays unsecured roles (roles without menu) on the object
318 -----2. The user doesn't play any roles (instance assignment) on the object
319
320 if secure_resp_flag=fnd_api.g_true then
321 pa_debug.G_err_stage := 'check responsibility level security: in object context';
322 IF G_debug_flag = 'Y' THEN
323 pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
324 END IF;
325 if p_object_name='PA_PROJECTS' then
326 if pa_security.allow_update (p_object_key)<>'Y' then
327 x_ret_code:=fnd_api.g_false;
328 x_return_status:=fnd_api.g_ret_sts_success;
329 return;
330 end if;
331 end if;
332
333 if fnd_function.test(p_privilege) then
334 x_ret_code:=fnd_api.g_true;
335 x_return_status:=fnd_api.g_ret_sts_success;
336 return;
337 end if;
338
339 x_ret_code:=fnd_api.g_false;
340 x_return_status:=fnd_api.g_ret_sts_success;
341
342 ---The following code is for testing the error page
343 /* fnd_message.set_name('PA','PA_SEC_NO_ACCESS');
344 fnd_msg_pub.ADD;
345 FND_MSG_PUB.Count_And_Get
346 (p_count => x_msg_count ,
347 p_data => x_msg_data
348 ); */
349
350 return;
351 end if ;
352 Exception
353 when others then
354 pa_debug.G_err_stage := 'exceptions raised';
355 IF G_debug_flag = 'Y' THEN
356 pa_debug.write_file('check_user_privilege: ' || 'LOG', pa_debug.G_err_stage);
357 END IF;
358 fnd_msg_pub.add_exc_msg
359 (p_pkg_name => G_PKG_NAME,
360 p_procedure_name =>'CHECK_USER_PRIVILEGE' );
361 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
362 x_ret_code :=fnd_api.g_false;
363 FND_MSG_PUB.Count_And_Get
364 (p_count => x_msg_count ,
365 p_data => x_msg_data
366 );
367 end check_user_privilege;
368
369
370 -----The following code is commented out because of instance sets and
371 -----resource roles like project authority and resource authority are not
372 -----in pa_project_parties
373 -----there is no perfect way to check if role based security is enforced or not
374
375 /*---This API check if role based security is enforced or not
376 ----function check_sec_by_role
377 -----------------------------------------
378 function check_sec_by_role
379 (
380 p_user_id in number,
381 p_object_name in varchar2,
382 p_source_type in varchar2,
383 p_object_key in number ) return varchar2 is
384
385 cursor c_role_sec_enabled is
386 select 'Y'
387 from fnd_user users,
388 pa_project_parties ppp,
389 pa_project_role_types roletypes
390 where decode (p_source_type, 'PERSON', users.employee_id,
391 'HZ_PARTY', users.customer_id)
392 = ppp.resource_source_id
393 and ppp.resource_type_id= decode(p_source_type, 'PERSON', 101,
394 'HZ_PARTY', 112,
395 111)
396 and ppp.project_role_id=roletypes.project_role_id
397 and users.user_id =p_user_id
398 and ppp.object_id=p_object_key
399 and ppp.object_type=p_object_name
400 and roletypes.menu_id is not null
401 and ROWNUM=1;
402
403 v_dummy varchar2(1);
404
405 Begin
406 open c_role_sec_enabled;
407 fetch c_role_sec_enabled into v_dummy;
408 if c_role_sec_enabled%found then
409 close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
410 return fnd_api.g_true;
411 else
412 close c_role_sec_enabled; -- Bug #2994870: closing the cursor.
413 return fnd_api.g_false;
414 end if;
415 Exception
416 when others then
417 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
418 fnd_msg_pub.add_exc_msg
419 (p_pkg_name => G_PKG_NAME,
420 p_procedure_name => 'check_sec_by_role');
421 end if;
422 return fnd_api.g_ret_sts_unexp_error;
423 end;*/
424
425
426 ---This API check if responsibility based security is enforced or not
427 ----funcrion check_sec_by_resp
428 -----------------------------------------
429 Function check_sec_by_resp
430 (
431 p_user_id in number,
432 p_object_name in varchar2,
433 p_source_type in varchar2,
434 p_object_key in number ) return varchar2 is
435
436 cursor c_unsecured_role is
437 select 'Y'
438 from fnd_user users,
439 pa_project_parties ppp,
440 --pa_project_role_types roletypes --bug 4004821
441 pa_project_role_types_b roletypes
442 where decode (p_source_type, 'PERSON', users.employee_id,
443 'HZ_PARTY', users.person_party_id) -- Bug 4527617. Replaced customer_id with person_party_id.
444 = ppp.resource_source_id
445 and ppp.resource_type_id= decode(p_source_type,'PERSON',101,
446 'HZ_PARTY', 112,
447 111)
448 and ppp.project_role_id=roletypes.project_role_id
449 and users.user_id =p_user_id
450 and ppp.object_id=p_object_key
451 and ppp.object_type=p_object_name
452 and roletypes.menu_id is null
453 and roletypes.role_party_class = 'PERSON' --bug 4004821
454 and ROWNUM=1;
455
456 cursor c_any_role is
457 select 'Y'
458 from fnd_user users,
459 pa_project_parties ppp
460 where decode (p_source_type, 'PERSON', users.employee_id,
461 'HZ_PARTY', users.person_party_id) -- Bug 4527617. Replaced customer_id with person_party_id.
462 = ppp.resource_source_id
463 and ppp.resource_type_id= decode(p_source_type,'PERSON',101,
464 'HZ_PARTY', 112,
465 111)
466 and users.user_id =p_user_id
467 and ppp.object_id=p_object_key
468 and ppp.object_type=p_object_name
469 and ROWNUM=1;
470
471 ------There might be an issue for pre-seeded resource roles,like resource authority
472 ------or project authority. The assignments of these roles are not in pa_project_parties.
473 ---- check_sec_by_resp will always return true for these assignments.
474
475 /* select 'Y'
476 from dual
477 where not exists
478 (select 'Y'
479 from fnd_grants fg,
480 fnd_objects obj
481 where fg.object_id=obj.object_id
482 and obj.obj_name=p_object_name
483 and fg.grantee_type='USER'
484 and fg.grantee_key='PER:'||to_char(get_party_id)
485 and fg.instance_type='INSTANCE'
486 and fg.instance_pk1_value=p_object_key);
487 ---we have not considered the instance sets in fnd_grants
488 -- We may need to enhance this api to consider the instance sets
489 ---(right now we only have instance sets for project authority and
490 ----resource authority)*/
491
492 v_dummy varchar2(1);
493
494 Begin
495 OPEN c_unsecured_role;
496 FETCH c_unsecured_role INTO v_dummy;
497 IF c_unsecured_role%found THEN
498 --Check responsibility if an unsecured role is found
499 CLOSE c_unsecured_role;
500 RETURN fnd_api.g_true;
501 END IF;
502 CLOSE c_unsecured_role;
503
504 OPEN c_any_role;
505 FETCH c_any_role INTO v_dummy;
506 IF c_any_role%found THEN
507 /*nisinha bug#8541727 */
508 --Check responsibility if no roles are found
509 CLOSE c_any_role;
510 RETURN fnd_api.g_true;
511 END IF;
512 CLOSE c_any_role;
513
514 IF p_object_name IN ('PA_PROJECTS')
515 AND pa_security.g_cross_project_user='Y' THEN
516 --profile option override this check
517 RETURN fnd_api.g_true;
518 END IF;
519
520 RETURN fnd_api.g_false;
521
522 Exception
523 when others then
524 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
525 fnd_msg_pub.add_exc_msg
526 (p_pkg_name => G_PKG_NAME,
527 p_procedure_name => 'check_sec_by_resp');
528 end if;
529 return fnd_api.g_ret_sts_unexp_error;
530 end;
531
532
533 --function get_resource_person_id
534 --This function will return person_id given a resource_id
535 FUNCTION get_resource_person_id(p_resource_id NUMBER) RETURN NUMBER IS
536 ret NUMBER;
537 BEGIN
538 SELECT person_id INTO ret
539 FROM pa_resource_txn_attributes
540 WHERE resource_id = p_resource_id
541 AND person_id IS NOT NULL;
542
543 RETURN ret;
544 END;
545
546 ------------------------------------------------------------------
547 --This API is currently owned by PJR team (Alex.Yang)
548 ------------------------------------------------------------------
549 ----This API wraps all logic for check the
550 ----confirm assignment privilege.
551 ---procedure check_confirm_asmt
552 ---p_resource_id or p_resource_name: only one of them is necessary
553 --------------------------------------------------
554 procedure check_confirm_asmt
555 (p_project_id in number,
556 p_resource_id in number,
557 p_resource_name in varchar2,
558 p_privilege in varchar2,
559 p_start_date in date DEFAULT SYSDATE,
560 p_init_msg_list IN VARCHAR2 DEFAULT 'T', -- Added for bug 5130421
561 x_ret_code out NOCOPY varchar2, --File.Sql.39 bug 4440895
562 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
563 x_msg_count out NOCOPY varchar2, --File.Sql.39 bug 4440895
564 x_msg_data out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
565
566 v_resource_id number ;
567 v_resource_type_id number;
568 v_ret_code varchar2(1);
569 v_return_status varchar2(1);
570 v_error_message_code varchar2(30);
571 v_resource_org_id number;
572 v_resource_emp_id number;
573 v_project_org_id number;
574 v_fnd_api_ret_code varchar2(1);
575 v_resource_super_user varchar2(1) := 'N';
576 l_fnd_function_test boolean:=false; -- Added for Bug2970209
577 begin
578
579 Init_global ;
580
581 --Clear the global PL/SQL message table : Changed for bug 5130421
582 IF p_init_msg_list = 'T' THEN
583 FND_MSG_PUB.initialize;
584 END IF;
585
586 pa_debug.Init_err_stack ( 'check_confirm_asmt');
587 x_msg_count :=0;
588 x_msg_data:= null;
589 x_return_status:=fnd_api.g_ret_sts_success;
590 x_ret_code:=fnd_api.g_true;
591
592 --Return false if login user is not an employee
593 IF G_source_type<>'PERSON' THEN
594 x_ret_code:=FND_API.G_FALSE;
595 RETURN;
596 END IF;
597
598 -----Initialization: get resource id, resource org, project org
599 pa_debug.G_err_stage := 'Initialization: get resource id, resource org, project org';
600 -- Bug 4359282 : Added check before calling pa_debug.write_file
601 IF G_debug_flag = 'Y' THEN
602 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
603 END IF;
604
605 ------- Check for License
606 IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
607 x_ret_code:= fnd_api.g_false;
608 RETURN;
609 END IF;
610 ------- End check for License
611
612
613 v_resource_id:=p_resource_id;
614 If p_resource_id is null then
615 pa_resource_utils.Check_ResourceName_Or_Id(
616 P_RESOURCE_ID=>p_resource_id ,
617 P_RESOURCE_NAME=>p_resource_name ,
618 P_CHECK_ID_FLAG=>'Y' ,
619 X_RESOURCE_ID=>v_resource_id ,
620 X_RESOURCE_TYPE_ID=>v_resource_type_id ,
621 X_RETURN_STATUS=>v_return_status ,
622 X_ERROR_MESSAGE_CODE=>v_error_message_code );
623 if v_return_status <>fnd_api.g_ret_sts_success then
624 x_msg_count := x_msg_count+1;
625 x_msg_data := v_error_message_code;
626 x_return_status := fnd_api.g_ret_sts_error;
627 x_ret_code:=fnd_api.g_false;
628 FND_MSG_PUB.add_exc_msg
629 (p_pkg_name =>'pa_resource_utils',
630 p_procedure_name => 'Check_ResourceName_Or_Id',
631 p_error_text => v_error_message_code);
632 RETURN;
633 end IF;
634 end if;
635
636 get_resource_org_id
637 (v_resource_id ,
638 p_start_date ,
639 v_resource_org_id ,
640 v_return_status ,
641 v_error_message_code ) ;
642 if v_return_status <>fnd_api.g_ret_sts_success then
643 x_msg_count := x_msg_count+1;
644 x_msg_data := v_error_message_code;
645 x_return_status := v_return_status;
646 x_ret_code:=fnd_api.g_false;
647 --Bug# 6134740 Fix start
648 /*FND_MSG_PUB.add_exc_msg
649 (p_pkg_name =>G_PKG_NAME,
650 p_procedure_name => 'GET_RESOURCE_ORG_ID',
651 p_error_text => v_error_message_code);*/
652 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
653 ,p_msg_name => v_error_message_code);
654 --Bug# 6134740 Fix end
655 RETURN;
656 end IF;
657
658 /* Bug 2970209 -- Commented the code to call check_user_privilege. Now new
659 code is added in the last to check for resource autority */
660 /*------- Check if the user has resource authority on the resource
661 pa_debug.G_err_stage := 'Check if the user has resource authority on the resource';
662 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
663
664 check_user_privilege(p_privilege => p_privilege,
665 p_object_name => 'ORGANIZATION',
666 p_object_key => v_resource_org_id,
667 x_ret_code => x_ret_code,
668 x_return_status => x_return_status,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data);
671
672 IF x_return_status<>fnd_api.g_ret_sts_success THEN
673 RETURN;
674 END IF;
675
676 if x_ret_code=fnd_api.g_true then
677 return;
678 end if;
679 ---------End of check if the user has resource authority------
680 */
681
682 -- Bug 2970209 : Getting the value from fnd_function.test, which will be used at other places in this code
683 l_fnd_function_test:=fnd_function.test(p_privilege);
684
685 -- Bug 2991490 Added the following condition.
686 -- If the logged in user is the resource on which the action is being performed, then grant privilege to the user.
687
688 IF (p_resource_id = pa_resource_utils.get_resource_id(G_source_id) and l_fnd_function_test = true) THEN
689 x_ret_code:=fnd_api.g_true;
690 x_return_status:=fnd_api.g_ret_sts_success;
691 return;
692 END IF;
693
694
695 -----------Check if user is Resource Super User--------------
696 pa_debug.G_err_stage := 'Check if the user is a resource super user';
697 IF G_debug_flag = 'Y' THEN
698 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
699 END IF;
700
701 v_resource_super_user := fnd_profile.value_specific('PA_SUPER_RESOURCE',
702 G_USER_ID,
703 G_RESPONSIBILITY_ID ,
704 fnd_global.resp_appl_id);
705 IF v_resource_super_user = 'Y' THEN
706
707 pa_debug.G_err_stage := 'check FND function security';
708
709 IF G_debug_flag = 'Y' THEN
710 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
711 END IF;
712
713 if l_fnd_function_test then
714 x_ret_code:=fnd_api.g_true;
715 x_return_status:=fnd_api.g_ret_sts_success;
716 return;
717 else
718 x_ret_code:=fnd_api.g_false;
719 x_return_status:=fnd_api.g_ret_sts_success;
720 end if;
721
722 END IF;
723
724 -----------End Resource Super User Check-------------------
725
726 ---------Check if the user is the manager of the resource in HR hierarchy
727 pa_debug.G_err_stage := 'Check if the user is the manager of the resource in HR hierarchy';
728 IF G_debug_flag = 'Y' THEN
729 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
730 END IF;
731
732 -- Bug 2970209 The following condition is not needed and it is unambiguoys, because we are defaulting x_ret_code as true
733 -- so this check will not be fired if v_resource_super_user <> Y
734 -- if x_ret_code =fnd_api.g_false then
735 v_resource_emp_id := get_resource_person_id(v_resource_id);
736 check_manager_relation
737 (v_resource_emp_id,
738 -- G_user_emp_id,
739 G_source_id,
740 p_start_date,
741 v_ret_code,
742 v_return_status,
743 v_error_message_code);
744
745 if v_return_status <>fnd_api.g_ret_sts_success then
746 x_msg_count := x_msg_count+1;
747 x_msg_data := v_error_message_code;
748 x_return_status := v_return_status;
749 x_ret_code:=fnd_api.g_false;
750 FND_MSG_PUB.add_exc_msg
751 (p_pkg_name =>G_PKG_NAME,
752 p_procedure_name => 'CHECK_MANAGER_RELATION',
753 p_error_text => v_error_message_code);
754 RETURN;
755 end if;
756
757 if (v_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
758 x_ret_code:=v_ret_code;
759 x_return_status:=v_return_status;
760 return;
761 else
762 x_ret_code:=fnd_api.g_false;
763 x_return_status:=v_return_status;
764 end if;
765
766 -- end if;
767 -------------End of check HR hierarchy---------------------
768 -- Bug 2970209 Added role based security here
769 --------check role based security ------------
770 pa_debug.G_err_stage := 'check role based security';
771 IF G_debug_flag = 'Y' THEN
772 pa_debug.write_file('check_confirm_asmt: ' || 'LOG', pa_debug.G_err_stage);
773 END IF;
774
775 --dbms_output.put_line('p_privilege IS : ' || p_privilege);
776 --dbms_output.put_line('v_resource_org_id IS : ' || v_resource_org_id);
777 --dbms_output.put_line('get_grantee_key IS : ' || get_grantee_key);
778
779 v_fnd_api_ret_code:=fnd_data_security.check_function(
780 p_api_version =>l_api_version,
781 p_function => p_privilege,
782 p_object_name => 'ORGANIZATION',
783 p_instance_pk1_value=>v_resource_org_id,
784 p_instance_pk2_value => NULL,
785 p_instance_pk3_value => NULL,
786 p_instance_pk4_value => NULL,
787 p_instance_pk5_value => NULL );
788 -- p_user_name => get_grantee_key); Commented for Bug 4498436.
789 --dbms_output.put_line('fter fnd_data_security.check_function');
790 --dbms_output.put_line('v_fnd_api_ret_code IS : ' || v_fnd_api_ret_code);
791
792 if v_fnd_api_ret_code=fnd_api.g_ret_sts_error
793 OR v_fnd_api_ret_code=fnd_api.g_ret_sts_unexp_error then
794 x_ret_code:=fnd_api.g_false;
795 x_return_status:=fnd_api.g_ret_sts_unexp_error;
796 FND_MSG_PUB.Count_And_Get
797 (p_count => x_msg_count ,
798 p_data => x_msg_data
799 );
800 return;
801 end if;
802
803 -- Bug 4099469 changes - If the person has organization authority
804 -- over the resource organization and the call to FND's security
805 -- returns TRUE, then give access. At this time do not check if the
806 -- function is in the responsibility.
807 -- Hence commenting out check of l_fnd_function_test
808
809 --if (v_fnd_api_ret_code=fnd_api.g_true and l_fnd_function_test = true)then
810 if (v_fnd_api_ret_code=fnd_api.g_true) then
811 x_ret_code:= fnd_api.g_true;
812 x_return_status:=fnd_api.g_ret_sts_success;
813 return;
814 end if;
815
816
817 x_ret_code:= fnd_api.g_false;
818 x_return_status:=fnd_api.g_ret_sts_success;
819 return;
820
821 -----------End of check role based security-----------
822
823
824 Exception
825 when others then
826 pa_debug.G_err_stage := SUBSTR(SQLERRM, 1, 200);
827 IF G_debug_flag = 'Y' THEN
828 pa_debug.write_file( 'LOG', pa_debug.G_err_stage);
829 END IF;
830 fnd_msg_pub.add_exc_msg
831 (p_pkg_name => G_PKG_NAME,
832 p_procedure_name =>'CHECK_CONFIRM_ASMT' );
833 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
834 x_msg_count:=x_msg_count+1;
835 x_ret_code :=fnd_api.g_false;
836 raise;
837 end;
838
839
840
841 ----This API is for getting the resource organization id
842 ------Procedure get_resource_org_id
843 ------------------------------------------------
844 PROCEDURE get_resource_org_id
845 (p_resource_id IN NUMBER,
846 p_start_date IN DATE,
847 x_resource_org_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
848 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
849 x_error_message_code OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
850 v_person_id NUMBER;
851 v_org_id NUMBER;
852 l_future_term_wf_flag pa_resources.future_term_wf_flag%TYPE := NULL ; --Added for Bug 6056112
853
854 --If p_start_date is null, the cursor will select org_id from
855 --the first assignment available from sysdate
856 -- Bug 2911451 - Included condition for Assignment_type ='E'
857 CURSOR c_org_id IS
858 SELECT organization_id
859 FROM per_all_assignments_f -- Bug 4359282: Changed from per_assignments_f to all
860 WHERE person_id=v_person_id
861 AND TRUNC(effective_start_date)<=TRUNC(NVL(p_start_date, effective_start_date))
862 AND TRUNC(NVL(p_start_date,SYSDATE))<=TRUNC(effective_end_date)
863 AND primary_flag='Y'
864 AND Assignment_type in ('E', 'C')
865 ORDER BY effective_start_date;
866
867 /* Added for Bug 6056112 */
868 CURSOR c_fut_term_org_id IS
869 SELECT RESOURCE_ORGANIZATION_ID
870 FROM pa_resources_denorm
871 WHERE person_id = v_person_id
872 AND TRUNC(resource_effective_start_date)<=TRUNC(NVL(p_start_date, resource_effective_start_date))
873 AND TRUNC(NVL(p_start_date,SYSDATE))<=TRUNC(resource_effective_end_date);
874
875 BEGIN
876 x_return_status:=fnd_api.g_ret_sts_success;
877 v_person_id:=get_resource_person_id(p_resource_id);
878
879 /* Added for Bug 6056112 */
880 SELECT nvl(future_term_wf_flag,'N')
881 INTO l_future_term_wf_flag
882 FROM pa_resources
883 WHERE resource_id = p_resource_id;
884
885 OPEN c_org_id;
886 FETCH c_org_id INTO x_resource_org_id;
887 IF c_org_id%NOTFOUND THEN
888 /* Start of Changes for Bug 6056112 */
889 IF (nvl(l_future_term_wf_flag,'N') = 'Y') THEN
890 OPEN c_fut_term_org_id;
891 FETCH c_fut_term_org_id INTO x_resource_org_id;
892 IF c_fut_term_org_id%NOTFOUND THEN
893 x_return_status := FND_API.G_RET_STS_ERROR;
894 x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
895 END IF ;
896 CLOSE c_fut_term_org_id;
897 ELSE
898 x_return_status := FND_API.G_RET_STS_ERROR;
899 x_error_message_code := 'PA_RESOURCE_ORG_AMBIGUOUS';
900 END IF ;
901 /* End of Changes for Bug 6056112 */
902 END IF;
903 CLOSE c_org_id; /* Bug #2994870: Closing the cursor. */
904 END get_resource_org_id;
905
906 -----This API is for getting the project owning organization
907 ------Procedure get_project_org_id
908 ------------------------------------------------
909 procedure get_project_org_id
910 (p_project_id in number,
911 x_project_org_id out NOCOPY number, --File.Sql.39 bug 4440895
912 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
913 x_error_message_code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
914 begin
915 x_return_status:=fnd_api.g_ret_sts_success;
916 select CARRYING_OUT_ORGANIZATION_ID
917 into x_project_org_id
918 from pa_projects_all
919 where project_id=p_project_id;
920 exception
921 WHEN NO_DATA_FOUND THEN
922 x_return_status := FND_API.G_RET_STS_ERROR;
923 x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
924 WHEN TOO_MANY_ROWS THEN
925 x_return_status := FND_API.G_RET_STS_ERROR;
926 x_error_message_code := 'PA_PROJ_ORG_AMBIGUOUS';
927 WHEN OTHERS THEN
928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
929 end;
930
931
932 --This procedure checks if the p_manager_id is a HR manager
933 --of p_person_id. It checks the HR supervisor hierarchy
934 --for all managers of the given person p_person_id
935 --Procedure check_manager_relation
936 --------------------------------------------------------
937 PROCEDURE check_manager_relation
938 (p_person_id in number,
939 p_manager_id in number,
940 p_start_date in date,
941 x_ret_code out NOCOPY varchar2, --File.Sql.39 bug 4440895
942 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
943 x_error_message_code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
944 l_is_manager VARCHAR2(1) := 'N';
945
946 --Added for bug 5916094
947 l_person_id NUMBER;
948 l_mgr_id NUMBER;
949 l_mgr_str VARCHAR2(4000);
950 l_mgr_str1 VARCHAR2(20);
951
952 BEGIN
953 x_return_status:=fnd_api.g_ret_sts_success;
954 x_ret_code:=fnd_api.g_false;
955
956 BEGIN
957 /* Commented for bug 5916094
958 There can exists overlapping RM for a resource for same 2 resources for a same duration
959 Like for a duration A is manager of B and for same duration B is manager of A
960
961 SELECT 'Y'
962 INTO l_is_manager
963 FROM dual
964 WHERE p_manager_id IN ( SELECT Manager_id
965 FROM pa_resources_denorm
966 WHERE nvl(p_start_date, trunc(sysdate)) BETWEEN resource_effective_start_date
967 AND resource_effective_end_date
968 AND manager_id is not null
969 START WITH person_id = p_person_id
970 CONNECT BY PRIOR manager_id = person_id
971 AND manager_id <> prior person_id
972 AND nvl(p_start_date, trunc(sysdate)) BETWEEN resource_effective_start_date
973 AND resource_effective_end_date);
974
975 */
976
977 --Added below code for bug 5916094
978
979 l_person_id := p_person_id;
980 l_mgr_str := ',' || p_manager_id || ',';
981
982 LOOP
983 BEGIN
984 SELECT manager_id INTO l_mgr_id
985 FROM pa_resources_denorm WHERE
986 person_id = l_person_id
987 AND nvl(p_start_date, trunc(sysdate))
988 BETWEEN resource_effective_start_date AND resource_effective_end_date;
989
990 IF l_mgr_id IS NULL THEN
991 l_is_manager := 'N';
992 EXIT;
993 ELSIF l_mgr_id = p_manager_id THEN
994 l_is_manager := 'Y';
995 EXIT;
996 ELSE
997 l_person_id := l_mgr_id;
998 l_mgr_str1 := ',' || l_mgr_id || ',';
999 END IF;
1000
1001 IF instr(l_mgr_str, l_mgr_str1) <> 0 THEN
1002 l_is_manager := 'N';
1003 EXIT;
1004 ELSE
1005 l_mgr_str := l_mgr_str || l_mgr_str1;
1006 END IF;
1007
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 EXIT;
1011 END;
1012
1013 END LOOP;
1014
1015 IF l_is_manager = 'Y' THEN
1016 x_ret_code:=fnd_api.g_true;
1017 END IF;
1018
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 x_ret_code:=fnd_api.g_false;
1022 END;
1023
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1027 x_ret_code:=fnd_api.g_false;
1028 raise;
1029 END;
1030
1031
1032 -- This API is called when a person is assigned to a new role
1033 -- from create_project_party
1034 PROCEDURE grant_role
1035 (
1036 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1037 p_debug_mode in varchar2 default 'N',
1038 p_project_role_id IN number,
1039 p_object_name IN VARCHAR2,
1040 p_instance_type IN VARCHAR2,
1041 p_object_key IN NUMBER,
1042 p_party_id IN NUMBER,
1043 p_source_type IN varchar2,
1044 x_grant_guid OUT NOCOPY raw, --File.Sql.39 bug 4440895
1045 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1046 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1047 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1048 ) IS
1049
1050 l_success varchar2(1);
1051 l_error_code number;
1052 l_instance_set_id number;
1053 l_grantee_key fnd_grants.grantee_key%TYPE;
1054 l_grant_exists VARCHAR2(1);
1055 l_status_level VARCHAR2(30);
1056 l_default_menu_name fnd_menus.menu_name%TYPE := null;
1057 l_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
1058 l_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
1059 l_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
1060 l_exp_error varchar2(1) := 'F';
1061 l_unexp_error varchar2(1) := 'F';
1062 l_parameter2 fnd_grants.parameter2%TYPE;
1063 l_role_status_menu_id_tbl SYSTEM.pa_num_tbl_type := null;
1064 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1065 l_error_message_code VARCHAR2(30);
1066
1067 begin
1068
1069 --dbms_output.put_line('pa_security_pvt.grant_role');
1070
1071 x_return_status:=fnd_api.g_ret_sts_success;
1072 x_msg_count:=0;
1073 x_msg_data:=null;
1074
1075 l_grantee_key:=get_grantee_key(p_source_type, p_party_id, 'Y'); -- Added 'Y' parameter for bug 3471913
1076
1077 -- for role-based security, check to see the this person already has a FND_GRANTS
1078 -- record for this given role. Only grant if person does not have such records.
1079 IF p_project_role_id IS NOT NULL AND p_object_name = 'PA_PROJECTS' THEN
1080 pa_security_pvt.check_grant_exists(p_project_role_id => p_project_role_id,
1081 p_instance_type => 'SET',
1082 p_instance_set_name => G_project_roles_ins_set_name,
1083 p_grantee_type => 'USER',
1084 p_grantee_key => l_grantee_key,
1085 x_instance_set_id => l_instance_set_id,
1086 x_ret_code => l_grant_exists
1087 );
1088
1089 --dbms_output.put_line('grant_exist: '||l_grant_exists);
1090 --dbms_output.put_line('instance_set_id: '||l_instance_set_id);
1091
1092 IF l_grant_exists = 'F' THEN
1093
1094 -- returns all menus-statuses associated with this role
1095 pa_role_status_menu_utils.Get_Role_Status_Menus(
1096 p_role_id => p_project_role_id
1097 ,x_status_level => l_status_level
1098 ,x_default_menu_name => l_default_menu_name
1099 ,x_status_type_tbl => l_status_type_tbl
1100 ,x_status_code_tbl => l_status_code_tbl
1101 ,x_menu_name_tbl => l_menu_name_tbl
1102 ,x_role_status_menu_id_tbl => l_role_status_menu_id_tbl
1103 ,x_return_status => l_return_status
1104 ,x_error_message_code => l_error_message_code);
1105
1106 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1107 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1108 ,p_msg_name => l_error_message_code );
1109
1110 ELSE
1111
1112 --dbms_output.put_line('default menu name: '||l_default_menu_name);
1113
1114 IF l_default_menu_name is not null AND l_return_status = fnd_api.g_ret_sts_success THEN
1115
1116 IF l_status_code_tbl IS NULL OR l_status_code_tbl.COUNT = 0 THEN
1117 l_parameter2 := 'NON_STATUS_BASED';
1118 ELSE
1119 l_parameter2 := 'DEFAULT';
1120 END IF;
1121
1122 --dbms_output.put_line('before calling grant_function');
1123 --dbms_output.put_line('l_grantee_key: '||l_grantee_key);
1124
1125 fnd_grants_pkg.grant_function
1126 (
1127 p_api_version => l_api_version,
1128 p_menu_name => l_default_menu_name,
1129 p_object_name => 'PA_PROJECTS',
1130 p_instance_type => 'SET',
1131 p_instance_set_id => l_instance_set_id,
1132 p_instance_pk1_value => null,
1133 p_instance_pk2_value => null,
1134 p_instance_pk3_value => null,
1135 p_instance_pk4_value => null,
1136 p_instance_pk5_value => null,
1137 p_grantee_type => 'USER',
1138 p_grantee_key => l_grantee_key,
1139 p_parameter1 => p_project_role_id,
1140 p_parameter2 => l_parameter2,
1141 p_parameter3 => null,
1142 p_start_date => sysdate,
1143 p_end_date => null,
1144 x_grant_guid =>x_grant_guid,
1145 x_success =>l_success,
1146 x_errorcode =>l_error_code
1147 );
1148
1149 --dbms_output.put_line('grant_function: '||l_success);
1150
1151 if l_success <> fnd_api.g_true then
1152 if l_error_code >0 then
1153 l_exp_error := 'T';
1154 else
1155 l_unexp_error := 'T';
1156 end if;
1157 end if;
1158
1159 IF l_status_code_tbl IS NOT NULL AND l_status_code_tbl.COUNT > 0 THEN
1160
1161 FOR i IN l_status_code_tbl.FIRST .. l_status_code_tbl.LAST LOOP
1162
1163 --dbms_output.put_line('status menu name: '||l_menu_name_tbl(i));
1164
1165 fnd_grants_pkg.grant_function
1166 (
1167 p_api_version => l_api_version,
1168 p_menu_name => l_menu_name_tbl(i),
1169 p_object_name => 'PA_PROJECTS',
1170 p_instance_type => 'SET',
1171 p_instance_set_id => l_instance_set_id,
1172 p_instance_pk1_value => null,
1173 p_instance_pk2_value => null,
1174 p_instance_pk3_value => null,
1175 p_instance_pk4_value => null,
1176 p_instance_pk5_value => null,
1177 p_grantee_type => 'USER',
1178 p_grantee_key => l_grantee_key,
1179 p_parameter1 => p_project_role_id,
1180 p_parameter2 => l_status_level,
1181 p_parameter3 => l_status_code_tbl(i),
1182 p_parameter4 => l_role_status_menu_id_tbl(i),
1183 p_start_date => sysdate,
1184 p_end_date => null,
1185 x_grant_guid => x_grant_guid,
1186 x_success => l_success,
1187 x_errorcode => l_error_code
1188 );
1189
1190 --dbms_output.put_line('grant_function: '||l_success);
1191
1192
1193 if l_success <> fnd_api.g_true then
1194 if l_error_code >0 then
1195 l_exp_error := 'T';
1196 else
1197 l_unexp_error := 'T';
1198 end if;
1199 end if;
1200
1201 END LOOP;
1202 END IF; -- IF l_status_code_tbl IS NOT NULL OR l_status_code_tbl.COUNT > 0 THEN
1203
1204 if l_exp_error = 'F' and l_unexp_error = 'F' then
1205 l_return_status:=fnd_api.g_ret_sts_success;
1206 else
1207 if l_unexp_error = 'T' then
1208 l_return_status:=fnd_api.g_ret_sts_unexp_error;
1209 else
1210 l_return_status:=fnd_api.g_ret_sts_error;
1211 end if;
1212 end if;
1213
1214 END IF; -- IF l_default_menu_id is not null THEN
1215
1216 END IF; -- IF pa_role_status_menu_utils.Get_Role_Status_Menus errors out;
1217 END IF; -- IF l_grant_exists = 'F'
1218
1219 END IF; --IF p_project_role_id IS NOT NULL AND p_object_name = 'PA_PROJECTS' THEN
1220
1221 --dbms_output.put_line('l_exp_error: '||l_exp_error||' l_unexp_error: '||l_unexp_error);
1222 x_return_status := l_return_status;
1223
1224 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1225 FND_MSG_PUB.Count_And_Get
1226 (p_count => x_msg_count ,
1227 p_data => x_msg_data
1228 );
1229 END IF;
1230
1231 exception
1232 when others then
1233 raise;
1234 end grant_role;
1235
1236
1237 -- This API is called when Organization Authority is granted
1238 -- to specified resources.
1239 PROCEDURE grant_org_authority
1240 (
1241 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1242 p_debug_mode in varchar2 default 'N',
1243 p_project_role_id IN number,
1244 p_menu_name in varchar2,
1245 p_object_name IN VARCHAR2,
1246 p_object_key_type IN VARCHAR2,
1247 p_object_key IN NUMBER,
1248 p_party_id IN NUMBER,
1249 p_source_type IN varchar2,
1250 p_start_date IN DATE,
1251 p_end_date IN DATE,
1252 x_grant_guid OUT NOCOPY raw, --File.Sql.39 bug 4440895
1253 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1254 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1255 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1256 ) IS
1257 v_menu_name varchar2(30);
1258 l_success varchar2(1);
1259 l_error_code number;
1260 l_instance_pk1_value number;
1261 l_instance_set_id number;
1262 l_grantee_key varchar2(240);
1263 l_object_key_type varchar2(8);
1264
1265 begin
1266
1267 --Clear the global PL/SQL message table
1268 -- FND_MSG_PUB.initialize; commented the call for Bug 2887390
1269
1270 --dbms_output.put_line('inside grant role');
1271 x_return_status:=fnd_api.g_ret_sts_success;
1272 x_msg_count:=0;
1273 x_msg_data:=null;
1274 if p_menu_name is null then
1275 v_menu_name:=get_menu_name(p_project_role_id);
1276 else
1277 v_menu_name:=p_menu_name;
1278 end if;
1279 -- l_grantee_key:='PER:'||to_char(p_party_id);
1280 l_grantee_key:=get_grantee_key(p_source_type, p_party_id, 'Y'); -- Added 'Y' parameter for bug 3471913
1281 if v_menu_name is not null then
1282 if p_object_key_type='INSTANCE' then
1283 l_instance_pk1_value:=p_object_key;
1284 l_instance_set_id:=null;
1285 l_object_key_type:='INSTANCE';
1286 else
1287 l_instance_set_id:=p_object_key;
1288 l_instance_pk1_value:=null;
1289 l_object_key_type:='SET';
1290 end if;
1291
1292 fnd_grants_pkg.grant_function
1293 (
1294 p_api_version =>l_api_version,
1295 p_menu_name =>v_menu_name,
1296 p_object_name =>p_object_name,
1297 p_instance_type =>l_object_key_type,
1298 p_instance_set_id =>l_instance_set_id,
1299 p_instance_pk1_value =>l_instance_pk1_value,
1300 p_instance_pk2_value =>null,
1301 p_instance_pk3_value =>null,
1302 p_instance_pk4_value =>null,
1303 p_instance_pk5_value =>null,
1304 p_grantee_type => 'USER',
1305 p_grantee_key =>l_grantee_key,
1306 p_start_date =>p_start_date,
1307 p_end_date =>p_end_date,
1308 x_grant_guid =>x_grant_guid,
1309 x_success =>l_success,
1310 x_errorcode =>l_error_code
1311 ) ;
1312 if l_success=fnd_api.g_true then
1313 x_return_status:=fnd_api.g_ret_sts_success;
1314 else
1315 if l_error_code >0 then
1316 x_return_status:=fnd_api.g_ret_sts_error;
1317 else
1318 x_return_status:=fnd_api.g_ret_sts_unexp_error;
1319 end if;
1320 FND_MSG_PUB.Count_And_Get
1321 (p_count => x_msg_count ,
1322 p_data => x_msg_data
1323 );
1324 end if;
1325 end if;
1326 exception
1327 when others then
1328 raise;
1329 end;
1330
1331 PROCEDURE revoke_grant
1332 (
1333 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1334 p_debug_mode in varchar2 default 'N',
1335 p_grant_guid in raw,
1336 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1337 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1338 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1339 ) IS
1340 l_success varchar2(1);
1341 l_error_code number;
1342
1343 begin
1344
1345 --Clear the global PL/SQL message table
1346 FND_MSG_PUB.initialize;
1347
1348 x_return_status :=fnd_api.g_ret_sts_success;
1349 x_msg_count:=0;
1350 x_msg_data :=null;
1351 --dbms_output.put_line('inside pa revoke_grants');
1352 fnd_grants_pkg.revoke_grant
1353 (
1354 p_api_version =>l_api_version,
1355 p_grant_guid =>p_grant_guid,
1356 x_success =>l_success,
1357 x_errorcode =>l_error_code
1358 );
1359
1360 if l_success=fnd_api.g_true then
1361 x_return_status:=fnd_api.g_ret_sts_success;
1362 else
1363 if l_error_code >0 then
1364 x_return_status:=fnd_api.g_ret_sts_error;
1365 else
1366 x_return_status:=fnd_api.g_ret_sts_unexp_error;
1367 end if;
1368 FND_MSG_PUB.Count_And_Get
1369 (p_count => x_msg_count ,
1370 p_data => x_msg_data
1371 );
1372 end if;
1373 exception
1374 when others then
1375 raise;
1376 end;
1377
1378
1379 PROCEDURE revoke_role
1380 (
1381 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1382 p_debug_mode in varchar2 default 'N',
1383 p_project_role_id IN number,
1384 p_menu_name in varchar2,
1385 p_object_name IN VARCHAR2,
1386 p_object_key_type IN VARCHAR2,
1387 p_object_key IN NUMBER,
1388 p_party_id IN NUMBER,
1389 p_source_type in varchar2,
1390 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1391 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1392 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1393 ) IS
1394 l_menu_id NUMBER ;
1395 l_object_id NUMBER;
1396 l_object_key_type VARCHAR2(8);
1397 l_grant_guid RAW(16);
1398 l_success VARCHAR2(1);
1399 l_error_code NUMBER;
1400
1401 begin
1402 --Clear the global PL/SQL message table
1403 FND_MSG_PUB.initialize;
1404
1405 x_msg_count:=0;
1406 x_msg_data:=null;
1407
1408 select object_id
1409 into l_object_id
1410 from fnd_objects
1411 where obj_name=p_object_name;
1412 if p_project_role_id is not null then
1413 l_menu_id:=get_menu_id_for_role(p_project_role_id);
1414 else
1415 l_menu_id:=get_menu_id(p_menu_name);
1416 end if;
1417
1418 if p_object_key_type='INSTANCE' then
1419 l_object_key_type:='INSTANCE';
1420 else
1421 l_object_key_type:='SET';
1422 end if;
1423
1424 --Standard Start of API savepoint
1425 SAVEPOINT revoke_role_PUB;
1426
1427 SELECT grant_guid
1428 INTO l_grant_guid
1429 FROM fnd_grants
1430 WHERE grantee_type='USER' AND
1431 grantee_key=get_grantee_key(p_source_type, p_party_id) AND
1432 menu_id=l_menu_id AND
1433 object_id=l_object_id AND
1434 instance_type=l_object_key_type AND
1435 ((l_object_key_type='INSTANCE' AND
1436 instance_pk1_value=TO_CHAR(p_object_key)) OR
1437 (l_object_key_type='SET' AND
1438 instance_set_id=p_object_key));
1439
1440 fnd_grants_pkg.revoke_grant(
1441 p_api_version => l_api_version,
1442 p_grant_guid => l_grant_guid,
1443 x_success => l_success,
1444 x_errorcode => l_error_code);
1445
1446 IF l_success=fnd_api.g_true THEN
1447 x_return_status:=fnd_api.g_ret_sts_success;
1448 ELSE
1449 IF l_error_code>0 THEN
1450 x_return_status:=fnd_api.g_ret_sts_error;
1451 ELSE
1452 x_return_status:=fnd_api.g_ret_sts_unexp_error;
1453 END IF;
1454 FND_MSG_PUB.Count_And_Get
1455 (p_count => x_msg_count ,
1456 p_data => x_msg_data
1457 );
1458 RETURN;
1459 END IF;
1460
1461 IF FND_API.To_Boolean( p_commit ) THEN
1462 COMMIT WORK;
1463 END IF;
1464
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 ROLLBACK TO revoke_role_PUB;
1468 x_return_status := FND_API.g_ret_sts_unexp_error ;
1469 IF FND_MSG_PUB.Check_Msg_Level
1470 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1471 THEN
1472 FND_MSG_PUB.Add_Exc_Msg
1473 ( G_PKG_NAME ,
1474 'revoke_role'
1475 );
1476 END IF;
1477 FND_MSG_PUB.Count_And_Get
1478 (p_count => x_msg_count ,
1479 p_data => x_msg_data
1480 );
1481 end;
1482
1483 -- obsoleted API
1484 PROCEDURE update_role
1485 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1486 p_debug_mode in varchar2 default 'N',
1487 p_grant_guid in raw,
1488 p_project_role_id_old IN number default null,
1489 p_object_name_old IN VARCHAR2 default null,
1490 p_object_key_type_old IN VARCHAR2 default null,
1491 p_object_key_old IN NUMBER default null,
1492 p_party_id_old IN NUMBER default null,
1493 p_source_type_old in varchar2 default null,
1494 p_start_date_old IN DATE default null,
1495 p_start_date_new IN DATE default null,
1496 p_end_date_new IN DATE,
1497 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1498 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1499 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1500 ) is
1501 l_success varchar2(1);
1502
1503 begin
1504 --Clear the global PL/SQL message table
1505 FND_MSG_PUB.initialize;
1506
1507 x_return_status:=fnd_api.g_ret_sts_success;
1508 x_msg_count:=0;
1509 x_msg_data:=null;
1510 fnd_grants_pkg.update_grant
1511 (
1512 p_api_version =>l_api_version,
1513 p_grant_guid =>p_grant_guid,
1514 p_start_date =>p_start_date_new,
1515 p_end_date =>p_end_date_new,
1516 x_success =>l_success
1517 ) ;
1518 if l_success=fnd_api.g_true then
1519 x_return_status:=fnd_api.g_ret_sts_success;
1520 else
1521 x_return_status:=fnd_api.g_ret_sts_unexp_error;
1522 FND_MSG_PUB.Count_And_Get
1523 (p_count => x_msg_count ,
1524 p_data => x_msg_data
1525 );
1526 end if;
1527 exception
1528 when others then
1529 raise;
1530
1531 end;
1532
1533
1534 PROCEDURE lock_grant
1535 (
1536 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1537 p_debug_mode in varchar2 default 'N',
1538 p_grant_guid in raw,
1539 p_project_role_id_old IN number default null,
1540 p_object_name_old IN VARCHAR2 default null,
1541 p_object_key_type_old IN VARCHAR2 default null,
1542 p_object_key_old IN number default null,
1543 p_party_id_old IN NUMBER default null,
1544 p_source_type_old in varchar2 default null,
1545 p_start_date_old IN DATE default null,
1546 p_project_role_id IN number,
1547 p_party_id IN NUMBER,
1548 p_source_type in varchar2,
1549 p_object_name IN VARCHAR2,
1550 p_object_key_type IN VARCHAR2,
1551 p_object_key IN number,
1552 p_start_date IN DATE,
1553 p_end_date IN DATE,
1554 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1555 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1556 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1557 ) is
1558 v_menu_id number;
1559 l_instance_set_id number;
1560 l_instance_pk1_value number;
1561 l_object_key_type varchar2(8);
1562 l_object_id number;
1563
1564 begin
1565 --Clear the global PL/SQL message table
1566 FND_MSG_PUB.initialize;
1567
1568 x_msg_count:=0;
1569 x_msg_data:=null;
1570
1571 v_menu_id:=get_menu_id_for_role(p_project_role_id);
1572 if p_object_key_type='INSTANCE' then
1573 l_instance_pk1_value:=p_object_key;
1574 l_instance_set_id:=null;
1575 l_object_key_type:='INSTANCE';
1576 else
1577 l_instance_set_id:=p_object_key;
1578 l_instance_pk1_value:=null;
1579 l_object_key_type:='SET';
1580 end if;
1581
1582 select object_id
1583 into l_object_id
1584 from fnd_objects
1585 where obj_name=p_object_name;
1586
1587 fnd_grants_pkg.lock_grant
1588 (
1589 p_grant_guid =>p_grant_guid,
1590 p_menu_id =>v_menu_id,
1591 -- p_grantee_key =>'PER:'||to_char(p_party_id),
1592 p_grantee_key =>get_grantee_key(p_source_type, p_party_id),
1593 p_grantee_type =>'USER',
1594 p_object_id =>l_object_id,
1595 p_instance_type =>l_object_key_type,
1596 p_instance_set_id =>l_instance_set_id,
1597 p_instance_pk1_value =>l_instance_pk1_value,
1598 p_instance_pk2_value =>null,
1599 p_instance_pk3_value =>null,
1600 p_instance_pk4_value =>null,
1601 p_instance_pk5_value =>null,
1602 p_start_date => p_start_date,
1603 p_end_date => p_end_date,
1604 p_program_name =>null,
1605 p_program_tag =>null
1606 ) ;
1607 x_return_status:='S';
1608 exception
1609 when others then
1610 x_return_status:=fnd_api.g_ret_sts_unexp_error;
1611 raise;
1612 end;
1613
1614
1615 FUNCTION get_instance_set_id (p_set_name in varchar2) return number IS
1616 l_api_name varchar2(30):='get_instance_set_id';
1617 l_set_id number;
1618 begin
1619 select instance_set_id into l_set_id
1620 from fnd_object_instance_sets
1621 where instance_set_name=p_set_name;
1622 return l_set_id;
1623 exception
1624 when no_data_found then
1625 fnd_message.set_name('PA','PA_COMMON_NO_INS_SET');
1626 fnd_msg_pub.ADD;
1627 return null;
1628 when others then
1629 IF FND_MSG_PUB.Check_Msg_Level
1630 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1631 THEN
1632 FND_MSG_PUB.Add_Exc_Msg
1633 ( G_PKG_NAME ,
1634 l_api_name
1635 );
1636 END IF;
1637 return null;
1638 end;
1639
1640
1641 FUNCTION get_menu_name (p_project_role_id in number) return varchar2 is
1642 v_menu_name varchar2(30);
1643 l_api_name varchar2(30):='get_menu_name';
1644 begin
1645 select menu.menu_name
1646 into v_menu_name
1647 from fnd_menus menu,
1648 --pa_project_role_types role
1649 pa_project_role_types_b role --Bug 4867700
1650 where menu.menu_id=role.menu_id
1651 and role.project_role_id= p_project_role_id;
1652 return v_menu_name;
1653 exception
1654 when no_data_found then
1655 --fnd_message.set_name('PA','PA_COMMON_NO_ROLE_MENU');
1656 --fnd_msg_pub.ADD;
1657 return null;
1658 when others then
1659 IF FND_MSG_PUB.Check_Msg_Level
1660 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1661 THEN
1662 FND_MSG_PUB.Add_Exc_Msg
1663 ( G_PKG_NAME ,
1664 l_api_name
1665 );
1666 END IF;
1667 return null;
1668 end;
1669
1670 FUNCTION get_menu_name_from_id (p_menu_id in number) return varchar2 is
1671 v_menu_name varchar2(30);
1672 begin
1673 select menu.menu_name
1674 into v_menu_name
1675 from fnd_menus menu
1676 where menu.menu_id=p_menu_id;
1677 return v_menu_name;
1678 exception
1679 when others then
1680 return null;
1681 end;
1682
1683 -----The following function is obsoleted because of translation issue for pre-seeded
1684 -----roles
1685 /*FUNCTION get_menu_name (p_project_role_name in varchar2) return varchar2 is
1686 v_menu_name varchar2(30);
1687 l_api_name varchar2(30):='get_menu_name';
1688 begin
1689 select menu.menu_name
1690 into v_menu_name
1691 from fnd_menus menu,
1692 pa_project_role_types role
1693 where menu.menu_id=role.menu_id
1694 and role.meaning= p_project_role_name;
1695 return v_menu_name;
1696 exception
1697 when no_data_found then
1698 --fnd_message.set_name('PA','PA_COMMON_NO_ROLE_MENU');
1699 --fnd_msg_pub.ADD;
1700 return null;
1701 when others then
1702 IF FND_MSG_PUB.Check_Msg_Level
1703 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1704 THEN
1705 FND_MSG_PUB.Add_Exc_Msg
1706 ( G_PKG_NAME ,
1707 l_api_name
1708 );
1709 END IF;
1710 return null;
1711 end;*/
1712
1713
1714 FUNCTION get_menu_id (p_menu_name in varchar2) return number is
1715 v_menu_id number;
1716 l_api_name varchar2(30):='get_menu_id';
1717 begin
1718 select menu_id into v_menu_id
1719 from fnd_menus
1720 where menu_name =p_menu_name;
1721 return v_menu_id;
1722 exception
1723 when no_data_found then
1724 -- fnd_message.set_name('PA','PA_COMMON_NO_ROLE_MENU');
1725 -- fnd_msg_pub.ADD;
1726 return null;
1727 when others then
1728 IF FND_MSG_PUB.Check_Msg_Level
1729 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1730 THEN
1731 FND_MSG_PUB.Add_Exc_Msg
1732 ( G_PKG_NAME ,
1733 l_api_name
1734 );
1735 END IF;
1736 return null;
1737 end;
1738
1739 FUNCTION get_menu_id_for_role (p_project_role_id in number) return number is
1740 v_menu_id number;
1741 l_api_name varchar2(30):='get_menu_id_for_role';
1742 begin
1743 select menu_id into v_menu_id
1744 --from pa_project_role_types --bug 4004821
1745 from pa_project_role_types_b
1746 where project_role_id =p_project_role_id;
1747 return v_menu_id;
1748 exception
1749 when no_data_found then
1750 -- fnd_message.set_name('PA','PA_COMMON_NO_ROLE_MENU');
1751 -- fnd_msg_pub.ADD;
1752 return null;
1753 when others then
1754 IF FND_MSG_PUB.Check_Msg_Level
1755 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1756 THEN
1757 FND_MSG_PUB.Add_Exc_Msg
1758 ( G_PKG_NAME ,
1759 l_api_name
1760 );
1761 END IF;
1762 return null;
1763 end;
1764
1765 FUNCTION get_proj_role_name(p_project_role_id in number) return varchar2 is
1766 v_role_name varchar2(80);
1767 begin
1768 select meaning
1769 into v_role_name
1770 from pa_project_role_types
1771 where project_role_id =p_project_role_id;
1772 return v_role_name;
1773 exception
1774 when others then
1775 raise;
1776 end;
1777
1778 FUNCTION get_party_id RETURN NUMBER IS
1779 BEGIN
1780 RETURN G_source_id;
1781 END;
1782
1783
1784
1785 -----FUNCTION is_role_exists-------------
1786 ----This FUnction is created by Hari. It is used in resource model
1787 --------------------------------------------------------------------------
1788 FUNCTION is_role_exists ( p_object_name IN FND_OBJECTS.OBJ_NAME%TYPE
1789 ,p_object_key_type IN FND_GRANTS.INSTANCE_TYPE%TYPE DEFAULT 'INSTANCE'
1790 ,p_role_id IN FND_MENUS.MENU_ID%TYPE
1791 ,p_object_key IN FND_GRANTS.INSTANCE_PK1_VALUE%TYPE
1792 ,p_party_id IN NUMBER
1793 ) RETURN BOOLEAN IS
1794
1795 --l_yes CONSTANT BOOLEAN := TRUE;
1796 --l_exists_flag BOOLEAN DEFAULT FALSE;
1797 l_dummy VARCHAR2(1);
1798 l_object_key_type varchar2(8);
1799 l_grantee_key fnd_grants.grantee_key%TYPE := '';
1800
1801 BEGIN
1802 if p_object_key_type='INSTANCE' then
1803 l_object_key_type:='INSTANCE';
1804 else
1805 l_object_key_type:='SET';
1806 end if;
1807 ----------------------------------------------------------------------
1808 --object_name object_key_type role_id object_key party_id
1809 -- (menu_id) (org_id)
1810 -- (set_id)
1811 ------------------------------------------------------------------------
1812 --ORGANIZATION INSTANCE 1 1274 53
1813 --PEOPLE SET 1 999 53
1814 ------------------------------------------------------------------------
1815
1816 --
1817 -- The person_id can be NULL, when the caller tries to check
1818 -- whether there is anybody else holding a particular authority in
1819 -- that organizaton.
1820 --
1821 --
1822 -- There'll be a INSTANCE record - corresponding to each (person_id, role_id) combination
1823 -- To check whether there are *any* INSTANCE records for a particular (person_id, role_id) combination,
1824 -- the caller would send the object_key ( org_id ) as NULL.
1825 --
1826
1827
1828 BEGIN
1829
1830 IF p_party_id IS NOT NULL THEN
1831 l_grantee_key := get_grantee_key('PERSON', p_party_id);
1832 END IF;
1833
1834 SELECT DISTINCT 'Y'
1835 INTO l_dummy
1836 FROM fnd_grants fg ,
1837 fnd_objects fo
1838 WHERE fg.object_id=fo.object_id
1839 AND fo.obj_name = p_object_name
1840 AND fg.INSTANCE_type = l_object_key_type
1841 AND fg.menu_id = p_role_id
1842 AND fg.grantee_type='USER'
1843 AND (p_party_id IS NULL OR fg.grantee_key=l_grantee_key)
1844 AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
1845 and trunc(NVL(fg.END_DATE, SYSDATE+1))
1846 AND ((l_object_key_type='INSTANCE' AND
1847 instance_pk1_value=NVL(p_object_key, instance_pk1_value)) OR
1848 (l_object_key_type='SET' AND
1849 fg.instance_set_id=TO_NUMBER(p_object_key)));
1850
1851 EXCEPTION
1852 WHEN NO_DATA_FOUND THEN
1853 l_dummy := 'N';
1854 END;
1855 IF l_dummy='Y' THEN
1856 RETURN TRUE;
1857 ELSE
1858 RETURN FALSE;
1859 END IF;
1860 END is_role_exists;
1861
1862
1863
1864 --------FUNCTION check_user_privilege
1865 ---This function will be used in select statement in some of the PRM pages
1866 FUNCTION check_user_privilege
1867 (p_privilege in varchar2,
1868 p_object_name in varchar2,
1869 p_object_key in number,
1870 p_init_msg_list IN VARCHAR2 DEFAULT 'Y') return varchar2 is
1871 x_ret_code varchar2(1);
1872 x_return_status varchar2(1);
1873 x_msg_count number;
1874 x_msg_data varchar2(240);
1875
1876 begin
1877 check_user_privilege(p_privilege,
1878 p_object_name,
1879 p_object_key,
1880 x_ret_code,
1881 x_return_status,
1882 x_msg_count,
1883 x_msg_data,
1884 p_init_msg_list);
1885 return x_ret_code;
1886 exception
1887 when others then
1888 raise;
1889 end;
1890
1891 ---------PROCEDURE get_pk_information
1892 --Getting the database information about an object
1893 Procedure get_pk_information(p_object_name in VARCHAR2,
1894 x_pk1_column_name out NOCOPY varchar2, --File.Sql.39 bug 4440895
1895 x_pk2_column_name out NOCOPY varchar2, --File.Sql.39 bug 4440895
1896 x_pk3_column_name out NOCOPY varchar2, --File.Sql.39 bug 4440895
1897 x_pk4_column_name out NOCOPY varchar2, --File.Sql.39 bug 4440895
1898 x_pk5_column_name out NOCOPY varchar2, --File.Sql.39 bug 4440895
1899 x_aliased_pk_column out NOCOPY varchar2, --File.Sql.39 bug 4440895
1900 x_aliased_ik_column out NOCOPY varchar2, --File.Sql.39 bug 4440895
1901 x_database_object_name out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
1902 cursor c_pk is
1903 SELECT pk1_column_name
1904 ,pk2_column_name
1905 ,pk3_column_name
1906 ,pk4_column_name
1907 ,pk5_column_name
1908 , database_object_name
1909 FROM fnd_objects
1910 WHERE obj_name=p_object_name ;
1911 begin
1912 open c_pk;
1913 fetch c_pk into
1914 x_pk1_column_name ,
1915 x_pk2_column_name ,
1916 x_pk3_column_name ,
1917 x_pk4_column_name ,
1918 x_pk5_column_name ,
1919 x_database_object_name;
1920 close c_pk; /* Bug #2994870: closing the cursor. */
1921
1922 x_aliased_pk_column:=x_pk1_column_name;
1923 x_aliased_ik_column:='INSTANCE_PK1_VALUE';
1924 if x_pk2_COLUMN_name is not null then
1925 x_aliased_pk_column:=x_aliased_pk_column||','||x_pk2_COLUMN_name;
1926 x_aliased_ik_column:=x_aliased_ik_column||','||'INSTANCE_PK2_VALUE';
1927 if x_pk3_COLUMN_name is not null then
1928 x_aliased_pk_column :=x_aliased_pk_column||','||x_pk3_COLUMN_name;
1929 x_aliased_ik_column:=x_aliased_ik_column||','||'INSTANCE_PK3_VALUE';
1930 if x_pk4_COLUMN_name is not null then
1931 x_aliased_pk_column:=x_aliased_pk_column||','||x_pk4_COLUMN_name;
1932 x_aliased_ik_column:=x_aliased_ik_column||','||'INSTANCE_PK4_VALUE';
1933 if x_pk5_COLUMN_name is not null then
1934 x_aliased_pk_column:=x_aliased_pk_column||','||x_pk5_COLUMN_name;
1935 x_aliased_ik_column:=x_aliased_ik_column||','||'INSTANCE_PK5_VALUE';
1936 end if;
1937 end if;
1938 end if;
1939 end if;
1940 exception
1941 when others then
1942 raise;
1943 end;
1944
1945 ---------PROCEDURE check_access_exist
1946 --Check where the user has access to any object with the given privilege
1947 PROCEDURE check_access_exist(p_privilege IN VARCHAR2,
1948 p_object_name IN VARCHAR2,
1949 x_ret_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1950 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1951 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1952 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1953 IS
1954 l_db_object_name fnd_objects.database_object_name%TYPE;
1955 l_db_pk1_column fnd_objects.pk1_column_name%TYPE;
1956 l_db_pk2_column fnd_objects.pk2_column_name%TYPE;
1957 l_db_pk3_column fnd_objects.pk3_column_name%TYPE;
1958 l_db_pk4_column fnd_objects.pk4_column_name%TYPE;
1959 l_db_pk5_column fnd_objects.pk5_column_name%TYPE;
1960 l_aliased_pk_column VARCHAR2(180);
1961 l_aliased_ik_column VARCHAR2(180);
1962
1963 TYPE DYNAMIC_CUR IS REF CURSOR;
1964 l_cur DYNAMIC_CUR;
1965 l_dummy NUMBER;
1966 l_sql VARCHAR2(32767);
1967 l_predicate VARCHAR2(32767);
1968 BEGIN
1969 --Clear the global PL/SQL message table
1970 FND_MSG_PUB.initialize;
1971
1972 pa_debug.Init_err_stack ( 'Check_Access_Exist');
1973
1974 Init_global;
1975 x_ret_code := FND_API.G_TRUE;
1976 x_return_status := FND_API.G_RET_STS_SUCCESS;
1977 x_msg_count := 0;
1978 x_msg_data := NULL;
1979
1980 ------- Check for License
1981 IF pa_product_install_utils.check_function_licensed(p_privilege) <> 'Y' THEN
1982 x_ret_code:= fnd_api.g_false;
1983 x_return_status:=fnd_api.g_ret_sts_success;
1984 RETURN;
1985 END IF;
1986 ------- End check for License
1987
1988 pa_debug.G_err_stage := 'get objects primary keys information';
1989 IF G_debug_flag = 'Y' THEN
1990 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
1991 END IF;
1992 get_pk_information(p_object_name ,
1993 l_db_pk1_column ,
1994 l_db_pk2_column ,
1995 l_db_pk3_column ,
1996 l_db_pk4_column ,
1997 l_db_pk5_column ,
1998 l_aliased_pk_column ,
1999 l_aliased_ik_column ,
2000 l_db_object_name);
2001
2002 pa_debug.G_err_stage := 'check access from responsibility level';
2003 IF G_debug_flag = 'Y' THEN
2004 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2005 END IF;
2006 IF fnd_function.test(p_privilege) THEN
2007 --Adding the below code for the bug 3137696
2008 x_ret_code := FND_API.G_TRUE;
2009 x_return_status := FND_API.G_RET_STS_SUCCESS;
2010 RETURN;
2011 --Commented the below code for the bug 3137696
2012 /*IF p_object_name='PA_PROJECTS' THEN
2013 -- Bug 1571014, faulty dynamic sql
2014 l_sql := 'SELECT 1 FROM '||l_db_object_name||
2015 ' WHERE pa_security_pvt.check_sec_by_resp('||
2016 g_user_id||',''PA_PROJECTS'','''||
2017 g_source_type||''','||l_db_pk1_column||')=''T'''||
2018 ' AND pa_security.allow_update('||l_db_pk1_column||')=''Y'''||
2019 ' AND ROWNUM=1';
2020
2021 pa_debug.G_err_stage := 'checking allow_update in case of PA_PROJECTS';
2022 IF G_debug_flag = 'Y' THEN
2023 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2024 END IF;
2025
2026 PA_SECURITY.Initialize(X_user_id =>G_user_id ,
2027 X_calling_module => 'PAXPREPR');
2028
2029 OPEN l_cur FOR l_sql;
2030 FETCH l_cur INTO l_dummy;
2031 IF l_cur%FOUND THEN
2032 CLOSE l_cur; -- Bug #2994870: closing the cursor.
2033 RETURN;
2034 END IF;
2035 CLOSE l_cur; -- Bug #2994870: closing the cursor.
2036
2037 ELSE
2038 -- Bug 1571014, faulty dynamic sql
2039 l_sql := 'SELECT 1 FROM '||l_db_object_name||
2040 ' WHERE pa_security_pvt.check_sec_by_resp('||
2041 g_user_id||','''||p_object_name||''','''||
2042 g_source_type||''','||l_db_pk1_column||')=''T'''||
2043 ' AND ROWNUM=1';
2044
2045 pa_debug.G_err_stage := 'checking check_sec_by_resp';
2046 IF G_debug_flag = 'Y' THEN
2047 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2048 END IF;
2049
2050 PA_SECURITY.Initialize(X_user_id =>G_user_id ,
2051 X_calling_module => 'PAXPREPR');
2052
2053 OPEN l_cur FOR l_sql;
2054 FETCH l_cur INTO l_dummy;
2055 IF l_cur%FOUND THEN
2056 CLOSE l_cur; -- Bug #2994870: closing the cursor.
2057 RETURN;
2058 END IF;
2059 CLOSE l_cur; -- Bug #2994870: closing the cursor.
2060 END IF;*/
2061 END IF;
2062
2063
2064 pa_debug.G_err_stage := 'get predicate from fnd_data_security';
2065 IF G_debug_flag = 'Y' THEN
2066 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2067 END IF;
2068 fnd_data_security.get_security_predicate(
2069 p_api_version => l_api_version,
2070 p_function => p_privilege,
2071 p_object_name => p_object_name,
2072 -- p_user_name => 'PER:'||to_char(G_user_emp_id),
2073 -- p_user_name => get_grantee_key, Commented for Bug 4498436.
2074 p_statement_type => 'EXISTS',
2075 x_predicate => l_predicate,
2076 x_return_status => x_return_status);
2077
2078 -- IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2079 IF x_return_status<>'T' AND x_return_status<>'F' THEN
2080 x_ret_code := FND_API.G_FALSE;
2081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082 FND_MSG_PUB.Count_And_Get
2083 (p_count => x_msg_count ,
2084 p_data => x_msg_data);
2085 RETURN;
2086 ELSIF x_return_status='F' THEN
2087 x_ret_code := FND_API.G_FALSE;
2088 x_return_status := FND_API.G_RET_STS_SUCCESS;
2089 x_msg_count := 0;
2090 x_msg_data := '';
2091 RETURN;
2092 END IF;
2093
2094 -- l_predicate := LTRIM(l_predicate, l_aliased_pk_column || ' IN ');
2095 -- l_predicate := 'SELECT 1 FROM DUAL WHERE EXISTS '||l_predicate;
2096 --Bug 2603255, selecting from db_object table instead of dual
2097 -- l_predicate := 'SELECT 1 FROM DUAL WHERE '||l_predicate;
2098 l_predicate := 'SELECT 1 FROM '||l_db_object_name||' WHERE '||l_predicate;
2099
2100 pa_debug.G_err_stage := 'open cursor for dynamic sql';
2101 IF G_debug_flag = 'Y' THEN
2102 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2103 END IF;
2104 OPEN l_cur FOR l_predicate;
2105 FETCH l_cur INTO l_dummy;
2106 IF l_cur%NOTFOUND THEN
2107 x_ret_code := FND_API.G_FALSE;
2108 x_return_status := FND_API.G_RET_STS_SUCCESS;
2109 x_msg_count := 0;
2110 x_msg_data := '';
2111 CLOSE l_cur; -- Bug #2994870: closing the cursor.
2112 RETURN;
2113 END IF;
2114 CLOSE l_cur; -- Bug #2994870: closing the cursor.
2115
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118 pa_debug.G_err_stage := 'exceptions raised';
2119 IF G_debug_flag = 'Y' THEN
2120 pa_debug.write_file('check_access_exist: ' || 'LOG', pa_debug.G_err_stage);
2121 END IF;
2122 x_ret_code := FND_API.G_FALSE;
2123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2124 FND_MSG_PUB.Count_And_Get
2125 (p_count => x_msg_count ,
2126 p_data => x_msg_data);
2127
2128 END check_access_exist;
2129
2130 PROCEDURE check_grant_exists(p_project_role_id in NUMBER,
2131 p_instance_type in fnd_grants.INSTANCE_TYPE%TYPE,
2132 p_instance_set_name in fnd_object_instance_sets.instance_set_name%TYPE,
2133 p_grantee_type in fnd_grants.GRANTEE_TYPE%TYPE,
2134 p_grantee_key in fnd_grants.GRANTEE_KEY%TYPE,
2135 x_instance_set_id out NOCOPY NUMBER, --File.Sql.39 bug 4440895
2136 x_ret_code out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2137 ) IS
2138
2139 l_instance_set_id NUMBER := null;
2140 l_grant_exists VARCHAR2(1) := 'F';
2141
2142 BEGIN
2143
2144
2145
2146 IF p_instance_set_name = G_project_roles_ins_set_name THEN
2147 IF G_project_roles_ins_set_id IS NULL THEN
2148 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
2149 END IF;
2150 l_instance_set_id := G_project_roles_ins_set_id;
2151 x_instance_set_id := G_project_roles_ins_set_id;
2152 END IF;
2153
2154 select 'T' into l_grant_exists
2155 from fnd_grants
2156 where grantee_key = p_grantee_key
2157 and grantee_type = 'USER'
2158 and instance_set_id = l_instance_set_id
2159 and grantee_type = p_grantee_type
2160 and instance_type = p_instance_type
2161 and parameter1 = to_char(p_project_role_id)
2162 and rownum=1;
2163
2164 x_ret_code := l_grant_exists;
2165
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 x_instance_set_id := l_instance_set_id;
2169 x_ret_code := l_grant_exists;
2170
2171 END check_grant_exists;
2172
2173 ----------------------------------------------------------------------
2174 -- The APIs below will be used by Roles form:
2175 -- 1. update_menu
2176 -- 2. revoke_role_based_sec
2177 -- 3. grant_role_based_sec
2178 -- 4. revoke_status_based_sec
2179 -- 5. update_status_based_sec
2180 ----------------------------------------------------------------------
2181
2182 -- This API is called when the default Menu is changed
2183 -- in Roles form for existing roles which are in use.
2184 PROCEDURE update_menu
2185 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2186 p_debug_mode in varchar2 default 'N',
2187 p_project_role_id IN number,
2188 p_menu_id IN number,
2189 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2190 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2191 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2192 ) is
2193
2194 BEGIN
2195
2196 IF G_project_roles_ins_set_id IS NULL THEN
2197 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
2198 END IF;
2199
2200 -- update menu_id in FND_GRANTS
2201 UPDATE fnd_grants
2202 SET menu_id = p_menu_id
2203 WHERE parameter1 = to_char(p_project_role_id)
2204 AND (parameter2 = 'NON_STATUS_BASED' OR parameter2 = 'DEFAULT')
2205 AND instance_type = 'SET'
2206 AND instance_set_id = G_project_roles_ins_set_id;
2207
2208 x_return_status:=fnd_api.g_ret_sts_success;
2209
2210 EXCEPTION
2211 when others then
2212 x_return_status:=fnd_api.g_ret_sts_unexp_error;
2213 fnd_msg_pub.add_exc_msg
2214 (p_pkg_name => 'PA_SECURITY_PVT',
2215 p_procedure_name => 'UPDATE_MENU',
2216 p_error_text => SQLCODE);
2217 x_msg_count := 1;
2218
2219 END update_menu;
2220
2221
2222 -- This API is called when Enforce Role-based Security checkbox
2223 -- is unchecked in Roles form for existing roles which are in use.
2224 PROCEDURE revoke_role_based_sec
2225 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2226 p_debug_mode in varchar2 default 'N',
2227 p_project_role_id IN number,
2228 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2229 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2230 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2231 ) is
2232
2233 BEGIN
2234
2235 IF G_project_roles_ins_set_id IS NULL THEN
2236 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
2237 END IF;
2238
2239 -- delete from FND_GRANTS
2240 DELETE FROM fnd_grants
2241 WHERE parameter1 = to_char(p_project_role_id)
2242 AND instance_type = 'SET'
2243 AND instance_set_id = G_project_roles_ins_set_id;
2244
2245 x_return_status:=fnd_api.g_ret_sts_success;
2246
2247 EXCEPTION
2248 when others then
2249 x_return_status:=fnd_api.g_ret_sts_unexp_error;
2250 fnd_msg_pub.add_exc_msg
2251 (p_pkg_name => 'PA_SECURITY_PVT',
2252 p_procedure_name => 'REVOKE_ROLE_BASED_SEC',
2253 p_error_text => SQLCODE);
2254 x_msg_count := 1;
2255 END revoke_role_based_sec;
2256
2257
2258 -- This API is called when Enforce Role-based Security checkbox
2259 -- is checked in Roles form for existing roles which are in use.
2260 PROCEDURE grant_role_based_sec
2261 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2262 p_debug_mode in varchar2 default 'N',
2263 p_project_role_id IN number,
2264 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2265 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2266 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2267 ) is
2268
2269 l_success varchar2(1);
2270 l_error_code number;
2271 l_instance_set_id number;
2272 l_grantee_key fnd_grants.grantee_key%TYPE;
2273 l_grant_exists VARCHAR2(1);
2274 l_status_level VARCHAR2(30);
2275 l_default_menu_name fnd_menus.menu_name%TYPE := null;
2276 l_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
2277 l_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
2278 l_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := null;
2279 l_exp_error varchar2(1) := 'F';
2280 l_unexp_error varchar2(1) := 'F';
2281 l_parameter2 fnd_grants.parameter2%TYPE;
2282 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2283 l_error_message_code VARCHAR2(30);
2284 l_grant_guid fnd_grants.grant_guid%TYPE;
2285 l_role_status_menu_id_tbl SYSTEM.pa_num_tbl_type := null;
2286
2287 cursor get_resources_on_role is
2288 select distinct ppp.resource_type_id,ppp.resource_source_id
2289 -- , wfr.name grantee_key
2290 from pa_project_parties ppp
2291 -- wf_roles wfr
2292 where ppp.project_role_id = p_project_role_id;
2293 -- and ppp.resource_type_id = 112
2294 -- and ppp.resource_source_id = wfr.orig_system_id
2295 -- and wfr.orig_system = 'HZ_PARTY'
2296 /*
2297 UNION ALL
2298 select distinct ppp.resource_type_id,ppp.resource_source_id, wfr.name grantee_key
2299 from pa_project_parties ppp,
2300 per_all_people_f per,
2301 wf_roles wfr
2302 where ppp.project_role_id = p_project_role_id
2303 and ppp.resource_type_id = 101
2304 and ppp.resource_source_id = per.person_id
2305 and per.party_id = wfr.orig_system_id
2306 and wfr.orig_system = 'HZ_PARTY';
2307
2308 */
2309 BEGIN
2310
2311 IF G_project_roles_ins_set_id IS NULL THEN
2312 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
2313 END IF;
2314
2315 -- get all menus-statuses associated with this role
2316 pa_role_status_menu_utils.Get_Role_Status_Menus(
2317 p_role_id => p_project_role_id
2318 ,x_status_level => l_status_level
2319 ,x_default_menu_name => l_default_menu_name
2320 ,x_status_type_tbl => l_status_type_tbl
2321 ,x_status_code_tbl => l_status_code_tbl
2322 ,x_menu_name_tbl => l_menu_name_tbl
2323 ,x_role_status_menu_id_tbl => l_role_status_menu_id_tbl
2324 ,x_return_status => l_return_status
2325 ,x_error_message_code => l_error_message_code);
2326
2327 --dbms_output.put_line('Get_Role_Status_Menus:'||l_return_status);
2328
2329 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2330 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2331 ,p_msg_name => l_error_message_code );
2332 ELSE
2333
2334 IF l_status_code_tbl IS NULL OR l_status_code_tbl.COUNT = 0 THEN
2335 l_parameter2 := 'NON_STATUS_BASED';
2336 ELSE
2337 l_parameter2 := 'DEFAULT';
2338 END IF;
2339
2340 -- insert records back to fnd_Grants
2341 FOR res in get_resources_on_role LOOP
2342
2343 IF res.resource_type_id = 112 THEN
2344 SELECT wfr.name grantee_key
2345 INTO l_grantee_key
2346 FROM wf_roles wfr
2347 WHERE wfr.orig_system_id = res.resource_source_id
2348 AND wfr.orig_system = 'HZ_PARTY'
2349 AND rownum = 1;
2350 ELSIF res.resource_type_id = 101 THEN
2351 SELECT wfr.name grantee_key
2352 INTO l_grantee_key
2353 FROM per_all_people_f per,
2354 wf_roles wfr
2355 WHERE res.resource_source_id = per.person_id
2356 AND per.party_id = wfr.orig_system_id
2357 AND wfr.orig_system = 'HZ_PARTY'
2358 AND rownum = 1;
2359 END IF;
2360
2361 -- l_grantee_key := res.grantee_key;
2362
2363 --dbms_output.put_line('------------------------------');
2364 --dbms_output.put_line('grantee_key: ' || l_grantee_key);
2365
2366 -- insert new records into FND_GRANTS
2367 fnd_grants_pkg.grant_function
2368 (
2369 p_api_version => l_api_version, -- Modified the parameter from '1.0' to l_api_version: Bug #3983570
2370 p_menu_name => l_default_menu_name,
2371 p_object_name => 'PA_PROJECTS',
2372 p_instance_type => 'SET',
2373 p_instance_set_id => G_project_roles_ins_set_id,
2374 p_instance_pk1_value => null,
2375 p_instance_pk2_value => null,
2376 p_instance_pk3_value => null,
2377 p_instance_pk4_value => null,
2378 p_instance_pk5_value => null,
2379 p_grantee_type => 'USER',
2380 p_grantee_key => l_grantee_key,
2381 p_parameter1 => p_project_role_id,
2382 p_parameter2 => l_parameter2,
2383 p_parameter3 => null,
2384 p_start_date => sysdate,
2385 p_end_date => null,
2386 x_grant_guid => l_grant_guid,
2387 x_success => l_success,
2388 x_errorcode => l_error_code
2389 );
2390
2391 if l_success <> fnd_api.g_true then
2392 if l_error_code >0 then
2393 l_exp_error := 'T';
2394 else
2395 l_unexp_error := 'T';
2396 end if;
2397 end if;
2398
2399 IF l_status_code_tbl IS NOT NULL AND l_status_code_tbl.COUNT > 0 THEN
2400
2401 FOR i IN l_status_code_tbl.FIRST..l_status_code_tbl.LAST LOOP
2402
2403 fnd_grants_pkg.grant_function
2404 (
2405 p_api_version => l_api_version, -- Modified the parameter from '1.0' to l_api_version: Bug #3983570
2406 p_menu_name => l_menu_name_tbl(i),
2407 p_object_name => 'PA_PROJECTS',
2408 p_instance_type => 'SET',
2409 p_instance_set_id => G_project_roles_ins_set_id,
2410 p_instance_pk1_value => null,
2411 p_instance_pk2_value => null,
2412 p_instance_pk3_value => null,
2413 p_instance_pk4_value => null,
2414 p_instance_pk5_value => null,
2415 p_grantee_type => 'USER',
2416 p_grantee_key => l_grantee_key,
2417 p_parameter1 => p_project_role_id,
2418 p_parameter2 => l_status_level,
2419 p_parameter3 => l_status_code_tbl(i),
2420 p_parameter4 => l_role_status_menu_id_tbl(i),
2421 p_start_date => sysdate,
2422 p_end_date => null,
2423 x_grant_guid => l_grant_guid,
2424 x_success => l_success,
2425 x_errorcode => l_error_code
2426 ) ;
2427
2428 if l_success <> fnd_api.g_true then
2429 if l_error_code >0 then
2430 l_exp_error := 'T';
2431 else
2432 l_unexp_error := 'T';
2433 end if;
2434 end if;
2435
2436 END LOOP; -- l_status_code_tbl.FIRST..l_status_code_tbl.LAST
2437
2438 END IF; -- IF l_status_code_tbl.COUNT > 0 THEN
2439
2440 END LOOP; -- FOR res in get_resources_on_role LOOP
2441
2442 if l_exp_error = 'T' then
2443 l_return_status:=fnd_api.g_ret_sts_error;
2444 elsif l_unexp_error = 'T' then
2445 l_return_status:=fnd_api.g_ret_sts_unexp_error;
2446 else
2447 l_return_status:=fnd_api.g_ret_sts_success;
2448
2449 END IF;
2450
2451 END IF; -- l_return_status = 'S'
2452
2453 x_return_status:=l_return_status;
2454
2455 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2456 FND_MSG_PUB.Count_And_Get
2457 (p_count => x_msg_count ,
2458 p_data => x_msg_data
2459 );
2460 END IF;
2461
2462 EXCEPTION
2463 when others then
2464 x_return_status:=fnd_api.g_ret_sts_unexp_error;
2465 fnd_msg_pub.add_exc_msg
2466 (p_pkg_name => 'PA_SECURITY_PVT',
2467 p_procedure_name => 'GRANT_ROLE_BASED_SEC',
2468 p_error_text => SQLCODE);
2469 FND_MSG_PUB.Count_And_Get
2470 (p_count => x_msg_count ,
2471 p_data => x_msg_data
2472 );
2473 END grant_role_based_sec;
2474
2475
2476 -- This API is called when Status Level is changed
2477 -- in Roles form for existing roles which are in use.
2478 PROCEDURE revoke_status_based_sec
2479 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2480 p_debug_mode in varchar2 default 'N',
2481 p_project_role_id IN number,
2482 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2483 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2484 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2485 ) is
2486
2487 BEGIN
2488
2489 IF G_project_roles_ins_set_id IS NULL THEN
2490 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
2491 END IF;
2492
2493 -- delete all but default from fnd_Grants
2494 DELETE FROM fnd_grants
2495 WHERE parameter1 = to_char(p_project_role_id)
2496 AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
2497 AND instance_type = 'SET'
2498 AND instance_set_id = G_project_roles_ins_set_id;
2499
2500 x_return_status:=fnd_api.g_ret_sts_success;
2501
2502 EXCEPTION
2503 when others then
2504 x_return_status:=fnd_api.g_ret_sts_unexp_error;
2505 fnd_msg_pub.add_exc_msg
2506 (p_pkg_name => 'PA_SECURITY_PVT',
2507 p_procedure_name => 'REVOKE_STATUS_BASED_SEC',
2508 p_error_text => SQLCODE);
2509 x_msg_count := 1;
2510 END revoke_status_based_sec;
2511
2512 -- This API is called when status/menu under Project Status is changed
2513 -- in Roles form for existing roles which are in use.
2514 PROCEDURE update_status_based_sec
2515 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2516 p_debug_mode in varchar2 default 'N',
2517 p_project_role_id IN number,
2518 p_status_level IN pa_project_role_types_b.status_level%TYPE,
2519 p_new_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2520 p_new_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2521 p_new_menu_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2522 p_new_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
2523 p_mod_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2524 p_mod_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2525 p_mod_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
2526 p_mod_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
2527 p_del_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2528 p_del_status_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := null,
2529 p_del_role_sts_menu_id_tbl IN SYSTEM.pa_num_tbl_type := null,
2530 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2531 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2532 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2533 ) is
2534
2535 cursor get_resources_on_role is
2536 select distinct ppp.resource_type_id,ppp.resource_source_id, wfr.name grantee_key
2537 from pa_project_parties ppp,
2538 wf_roles wfr
2539 where ppp.project_role_id = p_project_role_id
2540 and ppp.resource_type_id = 112
2541 and ppp.resource_source_id = wfr.orig_system_id
2542 and wfr.orig_system = 'HZ_PARTY'
2543 UNION ALL
2544 select distinct ppp.resource_type_id,ppp.resource_source_id, wfr.name grantee_key
2545 from pa_project_parties ppp,
2546 per_all_people_f per,
2547 wf_roles wfr
2548 where ppp.project_role_id = p_project_role_id
2549 and ppp.resource_type_id = 101
2550 and ppp.resource_source_id = per.person_id
2551 and per.party_id = wfr.orig_system_id
2552 and wfr.orig_system = 'HZ_PARTY';
2553
2554
2555 l_grantee_key fnd_grants.grantee_key%TYPE;
2556 l_status_menu_count NUMBER := 0;
2557 l_parameter2 fnd_grants.parameter2%TYPE;
2558 l_opp_param2 fnd_grants.parameter2%TYPE;
2559 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2560 l_grant_guid fnd_grants.grant_guid%TYPE;
2561 l_success varchar2(1);
2562 l_exp_error varchar2(1);
2563 l_unexp_error varchar2(1);
2564 l_error_code number;
2565
2566 BEGIN
2567
2568 IF G_project_roles_ins_set_id IS NULL THEN
2569 G_project_roles_ins_set_id := get_instance_set_id(G_project_roles_ins_set_name);
2570 END IF;
2571
2572 -- 1. NEW: insert new records into FND_GRANTS
2573 IF p_new_status_code_tbl IS NOT NULL AND p_new_status_code_tbl.COUNT > 0 THEN
2574
2575 --dbms_output.put_line('insert new record');
2576
2577 FOR res in get_resources_on_role LOOP
2578
2579 l_grantee_key := res.grantee_key;
2580
2581 FOR i IN p_new_status_code_tbl.FIRST..p_new_status_code_tbl.LAST LOOP
2582
2583 fnd_grants_pkg.grant_function
2584 (
2585 p_api_version => l_api_version, -- Modified the parameter from '1.0' to l_api_version: Bug #3983570
2586 p_menu_name => p_new_menu_name_tbl(i),
2587 p_object_name => 'PA_PROJECTS',
2588 p_instance_type => 'SET',
2589 p_instance_set_id => G_project_roles_ins_set_id,
2590 p_instance_pk1_value => null,
2591 p_instance_pk2_value => null,
2592 p_instance_pk3_value => null,
2593 p_instance_pk4_value => null,
2594 p_instance_pk5_value => null,
2595 p_grantee_type => 'USER',
2596 p_grantee_key => l_grantee_key,
2597 p_parameter1 => p_project_role_id,
2598 p_parameter2 => p_status_level,
2599 p_parameter3 => p_new_status_code_tbl(i),
2600 p_parameter4 => p_new_role_sts_menu_id_tbl(i),
2601 p_start_date => sysdate,
2602 p_end_date => null,
2603 x_grant_guid => l_grant_guid,
2604 x_success => l_success,
2605 x_errorcode => l_error_code
2606 ) ;
2607
2608 if l_success <> fnd_api.g_true then
2609 if l_error_code >0 then
2610 l_exp_error := 'T';
2611 else
2612 l_unexp_error := 'T';
2613 end if;
2614 end if;
2615
2616 END LOOP; -- p_new_status_code_tbl
2617 END LOOP; -- get_resources_on_role
2618
2619 if l_exp_error = 'T' then
2620 l_return_status:=fnd_api.g_ret_sts_error;
2621 elsif l_unexp_error = 'T' then
2622 l_return_status:=fnd_api.g_ret_sts_unexp_error;
2623 else
2624 l_return_status:=fnd_api.g_ret_sts_success;
2625 end if;
2626
2627 END IF; -- p_new_status_code_tbl.COUNT > 0 THEN
2628
2629 IF l_return_status = fnd_api.g_ret_sts_success THEN
2630
2631 -- 2. delete obsolete records from FND_GRANTS
2632 IF p_del_status_code_tbl IS NOT NULL AND p_del_status_code_tbl.COUNT > 0 THEN
2633
2634 --dbms_output.put_line('delete records');
2635
2636 FORALL j in p_del_status_code_tbl.FIRST..p_del_status_code_tbl.LAST
2637 DELETE FROM fnd_grants
2638 WHERE parameter1 = to_char(p_project_role_id)
2639 AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
2640 AND parameter3 = p_del_status_code_tbl(j)
2641 AND parameter4 = p_del_role_sts_menu_id_tbl(j)
2642 AND instance_type = 'SET'
2643 AND instance_set_id = G_project_roles_ins_set_id;
2644
2645 END IF;
2646
2647 -- 3. update existing records in FND_GRANTS
2648 IF p_mod_status_code_tbl IS NOT NULL AND p_mod_status_code_tbl.COUNT > 0 THEN
2649
2650 --dbms_output.put_line('update records');
2651
2652 FORALL k in p_mod_status_code_tbl.FIRST..p_mod_status_code_tbl.LAST
2653 UPDATE fnd_grants
2654 SET parameter2 = p_status_level,
2655 parameter3 = p_mod_status_code_tbl(k),
2656 menu_id = p_mod_menu_id_tbl(k)
2657 WHERE parameter1 = to_char(p_project_role_id)
2658 AND (parameter2 = 'USER' OR parameter2 = 'SYSTEM')
2659 AND parameter4 = p_mod_role_sts_menu_id_tbl(k)
2660 AND instance_type = 'SET'
2661 AND instance_set_id = G_project_roles_ins_set_id;
2662 END IF;
2663
2664 -- 4. if there is no status-menu record for the role,
2665 -- set the fnd_grants records from DEFAULT to NON_STATUS_BASED
2666 select count(role_status_menu_id) into l_status_menu_count
2667 from pa_role_status_menu_map
2668 where role_id = p_project_role_id
2669 and rownum=1;
2670
2671 IF l_status_menu_count = 0 THEN
2672 l_parameter2 := 'NON_STATUS_BASED';
2673 l_opp_param2 := 'DEFAULT';
2674 ELSE
2675 l_parameter2 := 'DEFAULT';
2676 l_opp_param2 := 'NON_STATUS_BASED';
2677 END IF;
2678
2679 UPDATE fnd_grants
2680 SET parameter2 = l_parameter2
2681 WHERE parameter1 = to_char(p_project_role_id)
2682 AND parameter2 = l_opp_param2
2683 AND instance_type = 'SET'
2684 AND instance_set_id = G_project_roles_ins_set_id;
2685
2686 END IF; -- l_return_status = fnd_api.g_ret_sts_success THEN
2687
2688 x_return_status := l_return_status;
2689
2690 EXCEPTION
2691 when others then
2692 x_return_status:=fnd_api.g_ret_sts_unexp_error;
2693 fnd_msg_pub.add_exc_msg
2694 (p_pkg_name => 'PA_SECURITY_PVT',
2695 p_procedure_name => 'UPDATE_STATUS_BASED_SEC',
2696 p_error_text => SQLCODE);
2697 FND_MSG_PUB.Count_And_Get
2698 (p_count => x_msg_count ,
2699 p_data => x_msg_data
2700 );
2701 END update_status_based_sec;
2702
2703 -- This procedure loops through all the responsibilities that the user is
2704 -- assigned with and check if he has access to a page. If he does, return
2705 -- resp_key and appl_short_name
2706 PROCEDURE GET_RESP_WITH_ACCESS
2707 (
2708 p_privilege IN VARCHAR2,
2709 x_has_access out NOCOPY varchar2,
2710 x_resp_key out NOCOPY VARCHAR2,
2711 x_appl_short_name out NOCOPY VARCHAR2,
2712 x_return_status out NOCOPY varchar2,
2713 x_msg_count out NOCOPY number,
2714 x_msg_data out NOCOPY varchar2) is
2715
2716
2717 --Get all the responsibilities for the user
2718 CURSOR user_resp_csr(p_user_id NUMBER) is
2719 select a.responsibility_id, c.application_id,
2720 b.responsibility_key, c.application_short_name
2721 from fnd_user_resp_groups_all a,
2722 fnd_responsibility_vl b,
2723 fnd_application c
2724 where a.user_id = p_user_id
2725 and sysdate between NVL(a.start_date,sysdate) and NVL(a.end_date,sysdate)
2726 and a.responsibility_id = b.responsibility_id
2727 and a.responsibility_application_id = b.application_id
2728 and b.application_id = c.application_id;
2729
2730 l_user_id NUMBER;
2731 l_old_resp_id NUMBER;
2732 l_old_resp_appl_id NUMBER;
2733 l_ret_code VARCHAR2(1) := 'F';
2734 l_resp_key VARCHAR2(30);
2735 l_appl_short_name VARCHAR2(50);
2736
2737 Begin
2738
2739 pa_debug.Init_err_stack ( 'GET_RESP_WITH_ACCESS');
2740
2741 x_msg_count := 0;
2742 x_msg_data := null;
2743 x_return_status := fnd_api.g_ret_sts_success;
2744 x_has_access := 'N';
2745
2746 l_old_resp_id := FND_GLOBAL.RESP_ID;
2747 l_old_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
2748 l_user_id := FND_GLOBAL.USER_ID;
2749
2750 IF G_debug_flag = 'Y' THEN
2751 pa_debug.write_file('check_access_exist: ' || 'LOG',' p_privilege '||p_privilege||' l_user_id '||l_user_id);
2752 END IF;
2753 --Get the responsibility that has access to view budget.
2754 FOR user_resp_rec IN user_resp_csr(l_user_id) LOOP
2755
2756 FND_GLOBAL.Apps_Initialize
2757 ( user_id => l_user_id
2758 , resp_id => user_resp_rec.responsibility_id
2759 , resp_appl_id => user_resp_rec.application_id
2760 );
2761
2762 PA_SECURITY_PVT.check_user_privilege
2763 ( p_privilege => p_privilege
2764 ,p_object_name => NULL
2765 ,p_object_key => to_number(NULL)
2766 ,x_ret_code => l_ret_code
2767 ,x_return_status => x_return_status
2768 ,x_msg_count => x_msg_count
2769 ,x_msg_data => x_msg_data
2770 );
2771 IF G_debug_flag = 'Y' THEN
2772 pa_debug.write_file('check_access_exist: ' || 'LOG',' resp_id '||user_resp_rec.responsibility_id||' l_ret_code '||l_ret_code);
2773 END IF;
2774 IF l_ret_code = 'T' THEN
2775 x_resp_key := user_resp_rec.responsibility_key;
2776 x_appl_short_name := user_resp_rec.application_short_name;
2777 x_has_access := 'Y';
2778 EXIT;
2779 END IF;
2780
2781 END LOOP;
2782
2783 IF l_ret_code <> 'T' THEN
2784 x_has_access := 'N';
2785 x_resp_key := '';
2786 x_appl_short_name := '';
2787 END IF;
2788
2789 IF G_debug_flag = 'Y' THEN
2790 pa_debug.write_file('check_access_exist: ' || 'LOG',' x_has_access '||x_has_access);
2791 pa_debug.write_file('check_access_exist: ' || 'LOG',' x_resp_key '||x_resp_key||' x_appl_short_name '||x_appl_short_name);
2792 END IF;
2793
2794 FND_GLOBAL.Apps_Initialize
2795 ( user_id => l_user_id
2796 , resp_id => l_old_resp_id
2797 , resp_appl_id => l_old_resp_appl_id
2798 );
2799
2800 EXCEPTION
2801 WHEN OTHERS THEN
2802 fnd_msg_pub.add_exc_msg
2803 (p_pkg_name => G_PKG_NAME,
2804 p_procedure_name =>'get_resp_with_access' );
2805
2806 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2807 x_has_access := 'N';
2808 x_resp_key := '';
2809 x_appl_short_name := '';
2810
2811 FND_MSG_PUB.Count_And_Get
2812 (p_count => x_msg_count ,
2813 p_data => x_msg_data
2814 );
2815 END GET_RESP_WITH_ACCESS;
2816
2817 end PA_SECURITY_PVT;