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