[Home] [Help]
PACKAGE BODY: APPS.FPA_SECURITY_PVT
Source
1 package body FPA_SECURITY_PVT as
2 /* $Header: FPAVSECB.pls 120.4 2005/08/18 11:50:33 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'FPA_SECURITY_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := FPA_UTILITIES_PVT.G_APP_NAME;
6 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
7 L_API_NAME CONSTANT VARCHAR2(35) := 'SECURITY_PVT';
8
9 /* ***************************************************************
10 Desc: Verify if a grant exists for a given portfolio and a role
11 parameters:
12 ***************************************************************** */
13
14 PROCEDURE Get_Grant(p_project_role_id IN NUMBER,
15 p_instance_type IN FND_GRANTS.INSTANCE_TYPE%TYPE,
16 p_instance_set_name IN FND_OBJECT_INSTANCE_SETS.INSTANCE_SET_NAME%TYPE,
17 p_grantee_type IN FND_GRANTS.GRANTEE_TYPE%TYPE,
18 p_grantee_key IN FND_GRANTS.GRANTEE_KEY%TYPE,
19 x_instance_set_id OUT NOCOPY NUMBER,
20 x_grant_id OUT NOCOPY FND_GRANTS.GRANT_GUID%TYPE,
21 x_ret_code OUT NOCOPY VARCHAR2) IS
22
23 cursor grants_csr (p_menu_id IN NUMBER,
24 p_instance_type IN FND_GRANTS.INSTANCE_TYPE%TYPE,
25 p_instance_set_id IN NUMBER,
26 p_instance_set_name IN FND_OBJECT_INSTANCE_SETS.INSTANCE_SET_NAME%TYPE,
27 p_grantee_type IN FND_GRANTS.GRANTEE_TYPE%TYPE,
28 p_grantee_key IN FND_GRANTS.GRANTEE_KEY%TYPE) IS
29
30 select 'T', grant_guid
31 from fnd_grants
32 where grantee_key = p_grantee_key
33 and grantee_type = 'USER'
34 and instance_set_id = p_instance_set_id
35 and grantee_type = p_grantee_type
36 and instance_type = p_instance_type
37 and menu_id = p_menu_id;
38
39 l_instance_set_id NUMBER := null;
40 l_menu_id NUMBER := null;
41 l_grant_exists VARCHAR2(1);
42 l_grant_id FND_GRANTS.GRANT_GUID%TYPE := null;
43
44 BEGIN
45
46 l_grant_exists := FND_API.G_FALSE;
47
48 l_instance_set_id := PA_SECURITY_PVT.Get_Instance_Set_Id(p_instance_set_name);
49 x_instance_set_id := l_instance_set_id;
50 l_menu_id := PA_SECURITY_PVT.get_menu_id_for_role(p_project_role_id);
51
52 open grants_csr(l_menu_id,
53 p_instance_type,
54 l_instance_set_id,
55 p_instance_set_name,
56 p_grantee_type,
57 p_grantee_key);
58
59 fetch grants_csr into l_grant_exists, l_grant_id;
60 close grants_csr;
61
62 x_ret_code := l_grant_exists;
63 x_grant_id := l_grant_id;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 if grants_csr%ISOPEN then
68 close grants_csr;
69 end if;
70 x_instance_set_id := l_instance_set_id;
71 x_ret_code := l_grant_exists;
72 x_grant_id := null;
73 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
74 FND_LOG.String(
75 FND_LOG.LEVEL_PROCEDURE,
76 'FPA_SECURITY_PVT.Get_Grant',
77 x_instance_set_id||','||x_ret_code||','||x_ret_code);
78 raise;
79 end if;
80 END Get_Grant;
81
82
83 FUNCTION Check_User_Previlege(
84 p_privilege IN VARCHAR2,
85 p_object_name IN VARCHAR2,
86 p_object_id IN NUMBER,
87 p_person_id IN NUMBER) RETURN VARCHAR2 IS
88
89 g_key FND_GRANTS.GRANTEE_KEY%TYPE;
90 l_ret_code VARCHAR2(1) := null;
91
92 BEGIN
93 /* Changes for ATG mandate for deprecated parameter
94 if(p_person_id is null) then
95 g_key := PA_SECURITY_PVT.Get_Grantee_Key('USER');
96 else
97 g_key := PA_SECURITY_PVT.Get_Grantee_Key('PERSON',p_person_id, 'Y');
98 end if;
99 */
100 /*
101 if (not fnd_function.test(p_privilege)) then
102 return FND_API.G_FALSE;
103 end if;
104 */
105
106
107
108 l_ret_code := FND_DATA_SECURITY.Check_Function(
109 p_api_version => 1.0,
110 p_function => p_privilege,
111 p_object_name => p_object_name,
112 p_instance_pk1_value => p_object_id,
113 p_instance_pk2_value => NULL,
114 p_instance_pk3_value => NULL,
115 p_instance_pk4_value => NULL,
116 p_instance_pk5_value => NULL);
117 -- Changes for ATG mandate for deprecated parameter
118 --,
119 --p_user_name => g_key);
120
121 return l_ret_code;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
126 FND_LOG.String(
127 FND_LOG.LEVEL_PROCEDURE,
128 'FPA_SECURITY_PVT.Check_User_Previlege',
129 p_privilege||','||p_object_name||','||p_object_id);
130 end if;
131 return 'U';
132 END Check_User_Previlege;
133
134
135 FUNCTION Check_Privilege(
136 p_privilege IN VARCHAR2,
137 p_object_name IN VARCHAR2,
138 p_object_id IN NUMBER,
139 p_person_id IN NUMBER) RETURN VARCHAR2 IS
140
141 g_key FND_GRANTS.GRANTEE_KEY%TYPE;
142 l_ret_code VARCHAR2(1) := null;
143
144 BEGIN
145 /* Changes for ATG mandate for deprecated parameter
146 if(p_person_id is null) then
147 g_key := PA_SECURITY_PVT.Get_Grantee_Key('USER');
148 else
149 g_key := PA_SECURITY_PVT.Get_Grantee_Key('PERSON',p_person_id, 'Y');
150 end if;
151 */
152 /*
153 if (not fnd_function.test(p_privilege)) then
154 return FND_API.G_FALSE;
155 end if;
156 */
157
158
159
160 l_ret_code := FND_DATA_SECURITY.Check_Function(
161 p_api_version => 1.0,
162 p_function => p_privilege,
163 p_object_name => p_object_name,
164 p_instance_pk1_value => p_object_id,
165 p_instance_pk2_value => NULL,
166 p_instance_pk3_value => NULL,
167 p_instance_pk4_value => NULL,
168 p_instance_pk5_value => NULL);
169 -- Changes for ATG mandate for deprecated parameter
170 -- ,
171 -- p_user_name => g_key);
172
173 if(l_ret_code = 'T') then
174 return 'X';
175 else
176 return null;
177 end if;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
182 FND_LOG.String(
183 FND_LOG.LEVEL_PROCEDURE,
184 'FPA_SECURITY_PVT.Check_User_Previlege',
185 p_privilege||','||p_object_name||','||p_object_id);
186 end if;
187 return 'U';
188 END Check_Privilege;
189
190
191
192 FUNCTION Get_Owner(
193 p_portfolio_id IN NUMBER) RETURN NUMBER IS
194
195 l_person_id NUMBER := null;
196
197 BEGIN
198
199 select
200 pp.resource_source_id into l_person_id
201 from pa_project_parties pp, pa_project_role_types_b rlt
202 where pp.object_type = 'PJP_PORTFOLIO'
203 and pp.object_id = p_portfolio_id
204 and PP.project_role_id = rlt.project_role_id
205 and rlt.project_role_type = G_OWNER;
206
207 return l_person_id;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 return null;
212 END Get_Owner;
213
214
215
216 FUNCTION Get_Role(
217 p_project_role_id IN PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_ID%TYPE)
218 RETURN VARCHAR2 IS
219
220 l_role VARCHAR2(200) := null;
221 BEGIN
222 select
223 project_role_type into l_role
224 from pa_project_role_types_b
225 where project_role_id = p_project_role_id;
226
227 return l_role;
228 EXCEPTION
229 WHEN OTHERS THEN
230 return null;
231 END Get_Role;
232
233 FUNCTION Get_Role_Id(
234 p_project_role IN PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_TYPE%TYPE)
235 RETURN PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_ID%TYPE IS
236
237 l_role_id PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_ID%TYPE := null;
238
239 BEGIN
240 select
241 project_role_id into l_role_id
242 from pa_project_role_types_b
243 where project_role_type = p_project_role;
244
245 return l_role_id;
246 EXCEPTION
247 WHEN OTHERS THEN
248 return null;
249 END Get_Role_Id;
250
251
252 PROCEDURE Grant_Role
253 (
254 p_api_version IN NUMBER,
255 p_init_msg_list IN VARCHAR2,
256 p_project_role_id IN NUMBER,
257 p_object_name IN VARCHAR2,
258 p_object_set IN VARCHAR2,
259 p_party_id IN NUMBER,
260 p_source_type IN VARCHAR2,
261 x_grant_guid OUT NOCOPY RAW,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2) IS
265
266 -- standard parameters
267 l_return_status VARCHAR2(1);
268 l_api_name CONSTANT VARCHAR2(30) := 'Grant_Role';
269 l_api_version CONSTANT NUMBER := 1.0;
270 l_msg_log VARCHAR2(2000) := null;
271 ----------------------------------------------------------------------------
272
273 l_exists VARCHAR2(1);
274 l_grant_id RAW(16);
275 l_instance_set_id NUMBER;
276 l_grantee_key FND_GRANTS.GRANTEE_KEY%TYPE;
277 l_secured_role_menu FND_MENUS.MENU_NAME%TYPE;
278 l_success VARCHAR2(1);
279 l_error_code NUMBER;
280 l_role VARCHAR2(100);
281
282 BEGIN
283
284 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
285 l_exists := 'F';
286
287 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
288 -- call START_ACTIVITY to create savepoint, check compatibility
289 -- and initialize message list
290 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
291 p_api_name => l_api_name,
292 p_pkg_name => G_PKG_NAME,
293 p_init_msg_list => p_init_msg_list,
294 l_api_version => l_api_version,
295 p_api_version => p_api_version,
296 p_api_type => G_API_TYPE,
297 p_msg_log => 'Entering fpa_security_pvt.grant_role',
298 x_return_status => x_return_status);
299
300 -- check if activity started successfully
301 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
302 l_msg_log := 'start_activity';
303 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
304 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
305 l_msg_log := 'start_activity';
306 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
307 end if;
308
309 l_grantee_key:= PA_SECURITY_PVT.Get_Grantee_Key(
310 p_source_type,
311 p_party_id,
312 'Y');
313
314 Get_Grant(p_project_role_id => p_project_role_id,
315 p_instance_type => 'SET',
316 p_instance_set_name => p_object_set,
317 p_grantee_type => 'USER',
318 p_grantee_key => l_grantee_key,
319 x_instance_set_id => l_instance_set_id,
320 x_grant_id => l_grant_id,
321 x_ret_code => l_exists);
322
323 if(l_exists = FND_API.G_TRUE) then
324
325 FPA_UTILITIES_PVT.END_ACTIVITY(
326 p_api_name => l_api_name,
327 p_pkg_name => G_PKG_NAME,
328 p_msg_log => null,
329 x_msg_count => x_msg_count,
330 x_msg_data => x_msg_data);
331
332 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
333
334 return;
335
336 end if;
337
338 l_secured_role_menu := PA_SECURITY_PVT.Get_Menu_Name(p_project_role_id);
339
340 if l_secured_role_menu is null then
341 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
342 FPA_UTILITIES_PVT.SET_MESSAGE(
343 p_app_name => g_app_name
344 , p_msg_name => 'PA_INVALID_PROJECT_ROLE');
345 -- l_msg_log := p_portfolio_party_id;
346 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
347 end if;
348 l_instance_set_id := PA_SECURITY_PVT.Get_Instance_Set_Id(p_object_set);
349
350
351 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
352
353 FND_GRANTS_PKG.Grant_Function(
354 p_api_version => l_api_version,
355 p_menu_name => l_secured_role_menu,
356 p_object_name => p_object_name,
357 p_instance_type => 'SET',
358 p_instance_set_id => l_instance_set_id,
359 p_grantee_type => 'USER',
360 p_grantee_key => l_grantee_key,
361 p_parameter1 => p_project_role_id,
362 p_parameter2 => p_party_id,
363 p_start_date => sysdate,
364 p_end_date => null,
365 x_grant_guid => l_grant_id,
366 x_success => l_success,
367 x_errorcode => l_error_code);
368
369
370 if l_success <> FND_API.G_TRUE then
371 if l_error_code > 0 then
372 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
373 else
374 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR;
375 end if;
376 end if;
377
378 -- check if activity started successfully
379 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
380 l_msg_log := l_secured_role_menu||','||to_char(l_grant_id);
381 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
382 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
383 l_msg_log := l_secured_role_menu||','||l_grant_id;
384 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
385 end if;
386
387 FPA_UTILITIES_PVT.END_ACTIVITY(
388 p_api_name => l_api_name,
389 p_pkg_name => G_PKG_NAME,
390 p_msg_log => null,
391 x_msg_count => x_msg_count,
392 x_msg_data => x_msg_data);
393
394 EXCEPTION
395 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
396 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
397 p_api_name => l_api_name,
398 p_pkg_name => G_PKG_NAME,
399 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
400 p_msg_log => l_msg_log,
401 x_msg_count => x_msg_count,
402 x_msg_data => x_msg_data,
403 p_api_type => G_API_TYPE);
404
405 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
406 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
407 p_api_name => l_api_name,
408 p_pkg_name => G_PKG_NAME,
409 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
410 p_msg_log => l_msg_log,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data,
413 p_api_type => G_API_TYPE);
414
415 when OTHERS then
416 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
417 p_api_name => l_api_name,
418 p_pkg_name => G_PKG_NAME,
419 p_exc_name => 'OTHERS',
420 p_msg_log => l_msg_log||SQLERRM,
421 x_msg_count => x_msg_count,
422 x_msg_data => x_msg_data,
423 p_api_type => G_API_TYPE);
424
425 END Grant_Role;
426
427
428 FUNCTION Get_Party_Name(p_person_id IN NUMBER)
429 RETURN VARCHAR2 IS
430 l_party_name VARCHAR2(200) := null;
431 begin
432 SELECT FULL_NAME
433 INTO l_party_name
434 FROM PER_ALL_PEOPLE_F PPF
435 WHERE PERSON_ID = p_person_id;
436 return l_party_name;
437 EXCEPTION
438 WHEN OTHERS THEN
439 return null;
440 END Get_Party_Name;
441
442
443 /* ***************************************************************
444 Desc: Call to revoke role when deleting a portfolio party
445 parameters:
446 ***************************************************************** */
447
448 PROCEDURE Revoke_Role(
449 p_api_version IN NUMBER,
450 p_init_msg_list IN VARCHAR2,
451 p_project_role_id IN NUMBER,
452 -- p_project_party_id IN NUMBER,
453 p_object_name IN VARCHAR2,
454 -- p_object_key_type IN VARCHAR2,
455 p_object_key IN NUMBER,
456 p_party_id IN NUMBER,
457 p_source_type IN VARCHAR2,
458 -- x_revoked OUT NOCOPY VARCHAR2,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2) IS
462
463 cursor parties_csr (p_project_role_id IN NUMBER,
464 p_party_id IN NUMBER) IS
465 select 'T'
466 from pa_project_parties
467 where project_role_id = p_project_role_id
468 and object_type = 'PJP_PORTFOLIO'
469 and resource_type_id = 101
470 and resource_source_id = p_party_id
471 and rownum=1;
472
473
474 -- standard parameters
475 l_return_status VARCHAR2(1);
476 l_api_name CONSTANT VARCHAR2(30) := 'Revoke_Role';
477 l_api_version CONSTANT NUMBER := 1.0;
478 l_msg_log VARCHAR2(2000) := null;
479 ----------------------------------------------------------------------------
480
481 l_object_id NUMBER;
482 l_object_key_type VARCHAR2(8);
483 l_grant_id FND_GRANTS.GRANT_GUID%TYPE;
484 l_grantee_key FND_GRANTS.GRANTEE_KEY%TYPE;
485 l_user VARCHAR2(200);
486 l_success VARCHAR2(1);
487 l_error_code NUMBER;
488 l_instance_set_id NUMBER;
489 l_exists VARCHAR2(1);
490
491 BEGIN
492 -- l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
493
494 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
495 -- call START_ACTIVITY to create savepoint, check compatibility
496 -- and initialize message list
497 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
498 p_api_name => l_api_name,
499 p_pkg_name => G_PKG_NAME,
500 p_init_msg_list => p_init_msg_list,
501 l_api_version => l_api_version,
502 p_api_version => p_api_version,
503 p_api_type => G_API_TYPE,
504 p_msg_log => 'Entering fpa_security_pvt.grant_role',
505 x_return_status => x_return_status);
506
507 -- check if activity started successfully
508 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
509 l_msg_log := 'start_activity';
510 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
511 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
512 l_msg_log := 'start_activity';
513 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
514 end if;
515
516 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
517
518 open parties_csr(p_project_role_id,
519 p_party_id);
520
521 fetch parties_csr into l_exists;
522 close parties_csr;
523
524 if(l_exists = FND_API.G_TRUE) then
525
526 FPA_UTILITIES_PVT.END_ACTIVITY(
527 p_api_name => l_api_name,
528 p_pkg_name => G_PKG_NAME,
529 p_msg_log => null,
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data);
532
533 return;
534
535 end if;
536
537 l_grantee_key:= PA_SECURITY_PVT.Get_Grantee_Key(
538 p_source_type,
539 p_party_id,
540 'Y');
541
542 l_exists := null;
543
544 Get_Grant(p_project_role_id => p_project_role_id,
545 p_instance_type => 'SET',
546 p_instance_set_name => p_object_name,
547 p_grantee_type => 'USER',
548 p_grantee_key => l_grantee_key,
549 x_instance_set_id => l_instance_set_id,
550 x_grant_id => l_grant_id,
551 x_ret_code => l_exists);
552
553 if(l_exists = FND_API.G_FALSE) then
554 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
555 l_user := Get_party_Name(p_party_id);
556 FPA_UTILITIES_PVT.SET_MESSAGE(
557 p_app_name => g_app_name
558 , p_msg_name => 'FPA_SEC_NO_GRANT'
559 , p_token1 => 'USER'
560 ,p_token1_value => l_user);
561
562 l_msg_log := 'FPA_SEC_NO_GRANT '||l_grantee_key||','||p_project_role_id;
563 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
564 end if;
565
566
567 FND_GRANTS_PKG.Revoke_Grant(
568 p_api_version => p_api_version,
569 p_grant_guid => l_grant_id,
570 x_success => l_success,
571 x_errorcode => l_error_code);
572
573 if l_success <> FND_API.G_TRUE then
574 -- x_revoked := FND_API.G_FALSE;
575 if l_error_code > 0 then
576 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
577 else
578 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR;
579 end if;
580 end if;
581
582 -- check if activity started successfully
583 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
584 l_msg_log := 'fpa_security_pvt.revoke_grant '||l_grant_id;
585 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
586 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
587 l_msg_log := 'fpa_security_pvt.revoke_grant '||l_grant_id;
588 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
589 end if;
590
591 --x_revoked := FND_API.G_TRUE;
592
593 FPA_UTILITIES_PVT.END_ACTIVITY(
594 p_api_name => l_api_name,
595 p_pkg_name => G_PKG_NAME,
596 p_msg_log => null,
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data);
599
600
601 EXCEPTION
602 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
603 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
604 p_api_name => l_api_name,
605 p_pkg_name => G_PKG_NAME,
606 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
607 p_msg_log => l_msg_log,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data,
610 p_api_type => G_API_TYPE);
611
612 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
613 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
614 p_api_name => l_api_name,
615 p_pkg_name => G_PKG_NAME,
616 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
617 p_msg_log => l_msg_log,
618 x_msg_count => x_msg_count,
619 x_msg_data => x_msg_data,
620 p_api_type => G_API_TYPE);
621
622 when OTHERS then
623 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
624 p_api_name => l_api_name,
625 p_pkg_name => G_PKG_NAME,
626 p_exc_name => 'OTHERS',
627 p_msg_log => l_msg_log||SQLERRM,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630 p_api_type => G_API_TYPE);
631 END Revoke_Role;
632
633
634 FUNCTION check_user(p_person_id IN NUMBER,
635 p_portfolio_id IN NUMBER)
636 RETURN BOOLEAN IS
637 l_flag varchar2(1) := FND_API.G_FALSE;
638 cursor PORTFOLIO_USER_CSR(P_PERSON_ID IN VARCHAR2,
639 P_PORTFOLIO_ID IN NUMBER) is
640 SELECT 'T'
641 FROM PA_PROJECT_PARTIES
642 WHERE OBJECT_TYPE = 'PJP_PORTFOLIO'
643 AND OBJECT_ID = P_PORTFOLIO_ID
644 AND RESOURCE_SOURCE_ID = P_PERSON_ID;
645
646 BEGIN
647 open PORTFOLIO_USER_CSR(p_person_id, p_portfolio_id);
648 fetch PORTFOLIO_USER_CSR into l_flag;
649 close PORTFOLIO_USER_CSR;
650 if(l_flag = FND_API.G_TRUE) then
651 return true;
652 else
653 return false;
654 end if;
655 END check_user;
656
657 PROCEDURE Create_Portfolio_User(
658 p_api_version IN NUMBER,
659 p_init_msg_list IN VARCHAR2,
660 p_object_id IN PA_PROJECT_PARTIES.OBJECT_ID%TYPE,
661 p_instance_set_name IN VARCHAR2,
662 p_project_role_id IN PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_ID%TYPE,
663 p_party_id IN NUMBER,
664 p_start_date_active IN DATE,
665 p_end_date_active IN DATE,
666 x_portfolio_party_id OUT NOCOPY PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_msg_count OUT NOCOPY NUMBER,
669 x_msg_data OUT NOCOPY VARCHAR2) IS
670
671 l_project_party_id NUMBER;
672 l_resource_id NUMBER;
673 l_start_date_active DATE;
674 l_end_date_active DATE;
675 l_wf_item_type VARCHAR2(30);
676 l_wf_type VARCHAR2(30);
677 l_wf_party_process VARCHAR2(30);
678 l_assignment_id NUMBER;
679 l_grant_id RAW(16);
680 l_user VARCHAR2(200);
681 l_role VARCHAR2(200);
682 -- standard parameters
683 l_return_status VARCHAR2(1);
684 l_api_name CONSTANT VARCHAR2(30) := 'Create_Portfolio_User';
685 l_api_version CONSTANT NUMBER := 1.0;
686 l_msg_log VARCHAR2(2000) := null;
687 ----------------------------------------------------------------------------
688
689 BEGIN
690
691 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
692 l_end_date_active := p_end_date_active;
693
694 if p_start_date_active is null then
695 l_start_date_active := sysdate;
696 else
697 l_start_date_active := p_start_date_active;
698 end if;
699
700
701 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
702 -- call START_ACTIVITY to create savepoint, check compatibility
703 -- and initialize message list
704
705 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
706 p_api_name => l_api_name,
707 p_pkg_name => G_PKG_NAME,
708 p_init_msg_list => p_init_msg_list,
709 l_api_version => l_api_version,
710 p_api_version => p_api_version,
711 p_api_type => G_API_TYPE,
712 p_msg_log => 'Entering fpa_security_pvt.create_portfolio_user',
713 x_return_status => x_return_status);
714
715 -- check if activity started successfully
716 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
717 l_msg_log := 'start_activity';
718 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
719 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
720 l_msg_log := 'start_activity';
721 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
722 end if;
723 /*
724 if (check_user(p_party_id, p_object_id)) then
725 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
726 l_user := Get_party_Name(p_party_id);
727 FPA_UTILITIES_PVT.SET_MESSAGE(
728 p_app_name => g_app_name
729 , p_msg_name => 'FPA_SEC_USER_EXISTS'
730 , p_token1 => 'USER'
731 ,p_token1_value => l_user);
732 l_msg_log := 'FPA_SEC_USER_EXISTS'||p_party_id||','||p_object_id;
733 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
734 end if;
735 */
736 if (Get_Role(p_project_role_id) = G_OWNER AND Get_Owner(p_object_id) is not null) then
737 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
738 l_role := PA_SECURITY_PVT.get_proj_role_name(p_project_role_id);
739 FPA_UTILITIES_PVT.SET_MESSAGE(
740 p_app_name => g_app_name
741 , p_msg_name => 'FPA_SEC_OWNER_ROLE_EXISTS'
742 , p_token1 => 'ROLE'
743 ,p_token1_value => l_role);
744 l_msg_log := 'FPA_SEC_OWNER_ROLE_EXISTS'||p_project_role_id||','||p_object_id;
745 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
746 end if;
747
748 PA_PROJECT_PARTIES_PVT.Create_Project_Party(
749 p_commit => FND_API.G_FALSE,
750 p_validate_only => FND_API.G_FALSE,
751 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
752 p_debug_Mode => 'N',
753 p_object_ID => p_object_id,
754 p_object_Type => G_PORTFOLIO,
755 p_resource_Type_ID => 101,
756 p_project_Role_ID => p_project_role_id,
757 p_resource_Source_ID => p_party_id,
758 p_start_Date_Active => l_start_date_active,
759 p_scheduled_Flag => 'N',
760 p_calling_Module => 'FORM',
761 p_project_ID => NULL,
762 p_project_End_Date => NULL,
763 p_end_Date_Active => l_end_date_active,
764 x_project_Party_ID => x_portfolio_party_id,
765 x_resource_ID => l_resource_id,
766 x_assignment_ID => l_assignment_id,
767 x_WF_Type => l_wf_type,
768 x_WF_Item_Type => l_wf_item_type,
769 x_WF_Process => l_wf_party_process,
770 x_Return_Status => x_return_status,
771 x_Msg_Count => x_msg_count,
772 x_Msg_Data => x_msg_data);
773
774 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
775 l_msg_log := p_object_id||','||p_project_role_id||','||p_party_id;
776 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
777 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
778 l_msg_log := p_object_id||','||p_project_role_id||','||p_party_id;
779 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
780 end if;
781
782 Grant_Role(p_api_version => p_api_version,
783 p_init_msg_list => p_init_msg_list,
784 p_project_role_id => p_project_role_id,
785 p_object_name => G_PORTFOLIO,
786 p_object_set => p_instance_set_name,
787 p_party_id => p_party_id,
788 p_source_type => 'PERSON',
789 x_grant_guid => l_grant_id,
790 x_return_status => x_return_status,
791 x_msg_count => x_msg_count,
792 x_msg_data => x_msg_data);
793
794
795 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
796 l_msg_log := p_object_id||','||p_project_role_id||','||p_party_id||','||l_grant_id;
797 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
798 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
799 l_msg_log := p_object_id||','||p_project_role_id||','||p_party_id||','||l_grant_id;
800 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
801 end if;
802
803 FPA_UTILITIES_PVT.END_ACTIVITY(
804 p_api_name => l_api_name,
805 p_pkg_name => G_PKG_NAME,
806 p_msg_log => null,
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data);
809
810 EXCEPTION
811 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
812 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
813 p_api_name => l_api_name,
814 p_pkg_name => G_PKG_NAME,
815 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
816 p_msg_log => l_msg_log,
817 x_msg_count => x_msg_count,
818 x_msg_data => x_msg_data,
819 p_api_type => G_API_TYPE);
820
821 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
822 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
823 p_api_name => l_api_name,
824 p_pkg_name => G_PKG_NAME,
825 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
826 p_msg_log => l_msg_log,
827 x_msg_count => x_msg_count,
828 x_msg_data => x_msg_data,
829 p_api_type => G_API_TYPE);
830
831 when OTHERS then
832 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
833 p_api_name => l_api_name,
834 p_pkg_name => G_PKG_NAME,
835 p_exc_name => 'OTHERS',
836 p_msg_log => l_msg_log||SQLERRM,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data,
839 p_api_type => G_API_TYPE);
840
841 END Create_Portfolio_User;
842
843
844
845 PROCEDURE Update_Portfolio_User
846 (
847 p_api_version IN NUMBER,
848 p_init_msg_list IN VARCHAR2,
849 p_portfolio_party_id IN PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE,
850 p_project_role_id IN PA_PROJECT_ROLE_TYPES.PROJECT_ROLE_ID%TYPE,
851 p_start_date_active IN DATE,
852 p_end_date_active IN DATE,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2) IS
856
857 -- standard parameters
858 l_return_status VARCHAR2(1);
859 l_api_name CONSTANT VARCHAR2(30) := 'Update_Portfolio_User';
860 l_api_version CONSTANT NUMBER := 1.0;
861 l_msg_log VARCHAR2(2000) := null;
862 ----------------------------------------------------------------------------
863
864 CURSOR update_rec_csr (p_project_party_id IN NUMBER) IS
865 select
866 object_id,
867 object_type,
868 project_id,
869 resource_id,
870 resource_type_id,
871 resource_source_id,
872 project_role_id,
873 start_date_active,
874 end_date_active,
875 scheduled_flag,
876 record_version_number,
877 grant_id
878 from pa_project_parties
879 where project_party_id = p_project_party_id;
880
881 l_wf_type VARCHAR2(250);
882 l_wf_item_type VARCHAR2(250);
883 l_wf_process VARCHAR2(250);
884 l_assignment_id NUMBER;
885
886 l_object_id NUMBER;
887 l_object_type VARCHAR2(30);
888 l_project_id NUMBER;
889 l_resource_id NUMBER;
890 l_resource_type_id NUMBER;
891 l_resource_source_id NUMBER;
892 l_project_role_id NUMBER;
893 l_revoke_role_id NUMBER;
894 l_start_date_active DATE;
895 l_end_date_active DATE;
896 l_scheduled_flag VARCHAR2(1);
897 l_record_version_number NUMBER;
898 l_grant_id RAW(16);
899 l_revoked VARCHAR2(1);
900 l_user VARCHAR2(200);
901
902 l_project_party_id NUMBER := p_portfolio_party_id;
903 x_call_overlap VARCHAR2(1) := 'Y';
904 x_assignment_action VARCHAR2(20) := 'NOACTION';
905
906 BEGIN
907
908 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
909 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
910
911 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
912 p_api_name => l_api_name,
913 p_pkg_name => G_PKG_NAME,
914 p_init_msg_list => p_init_msg_list,
915 l_api_version => l_api_version,
916 p_api_version => p_api_version,
917 p_api_type => G_API_TYPE,
918 p_msg_log => 'Entering fpa_security_pvt.update_portfolio_user',
919 x_return_status => x_return_status);
920
921 -- check if activity started successfully
922 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
923 l_msg_log := 'start_activity';
924 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
925 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
926 l_msg_log := 'start_activity';
927 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
928 end if;
929
930
931 open update_rec_csr(p_portfolio_party_id);
932 fetch update_rec_csr into
933 l_object_id,
934 l_object_type,
935 l_project_id,
936 l_resource_id,
937 l_resource_type_id,
938 l_resource_source_id,
939 l_project_role_id,
940 l_start_date_active,
941 l_end_date_active,
942 l_scheduled_flag,
943 l_record_version_number,
944 l_grant_id;
945 close update_rec_csr;
946
947 if(p_project_role_id <> l_project_role_id) then
948 if (Get_Role(p_project_role_id) = G_OWNER AND Get_Owner(l_object_id) is not null) then
949 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
950 l_user := Get_party_Name(p_project_role_id);
951 FPA_UTILITIES_PVT.SET_MESSAGE(
952 p_app_name => g_app_name
953 , p_msg_name => 'FPA_SEC_OWNER_ROLE_EXISTS'
954 , p_token1 => 'USER'
955 ,p_token1_value => l_user);
956 l_msg_log := 'FPA_SEC_OWNER_ROLE_EXISTS'||p_project_role_id||','||l_object_id;
957 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
958 end if;
959 end if;
960
961 -- l_start_date_active := p_start_date_active;
962 if p_start_date_active is null then
963 l_start_date_active := sysdate;
964 else
965 l_start_date_active := p_start_date_active;
966 end if;
967
968 l_end_date_active := p_end_date_active;
969
970 /*
971 NOT ABLE TO UPDATE USING BELOW CALL WITH VALIDATIONS AS ROLE ID IS IGNORED FOR UPDATE
972 WHEN "PA_INSTALL.IS_PRM_LICENSED()" IS 'Y'. NEED TO FIND OUT DETAILS ON THE
973 PROFILE OPTION IF BELOW CALL WITH VALIDATIONS CAN BE USED.
974 */
975
976 /*
977 PA_PROJECT_PARTIES_PVT.UPDATE_PROJECT_PARTY(
978 p_commit => FND_API.G_FALSE,
979 p_validate_only => FND_API.G_FALSE,
980 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
981 p_debug_mode => 'N',
982 p_object_id => l_object_id,
983 p_object_type => l_object_type,
984 p_project_role_id => p_project_role_id,
985 p_resource_type_id => l_resource_type_id,
986 p_resource_source_id => l_resource_source_id,
987 p_resource_id => l_resource_id,
988 p_start_date_active => l_start_date_active,
989 p_scheduled_flag => l_scheduled_flag,
990 p_record_version_number => l_record_version_number,
991 p_calling_module => 'FORM',
992 p_project_id => null,
993 p_project_end_date => l_end_date_active,
994 p_project_party_id => p_portfolio_party_id,
995 p_assignment_id => 0,
996 p_assign_record_version_number => null,
997 p_end_date_active => l_end_date_active,
998 x_assignment_id => l_assignment_id,
999 x_wf_type => l_wf_type,
1000 x_wf_item_type => l_wf_item_type,
1001 x_wf_process => l_wf_process,
1002 x_return_status => l_return_status,
1003 x_msg_count => x_msg_count,
1004 x_msg_data => x_msg_data);
1005
1006 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1007 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1008 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1009 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1010 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1011 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1012 end if;
1013 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1014 */
1015
1016 pa_project_parties_utils.validate_project_party(
1017 FND_API.G_VALID_LEVEL_FULL,
1018 'N',
1019 l_object_id,
1020 l_object_type,
1021 p_project_role_id,
1022 l_resource_type_id,
1023 l_resource_source_id,
1024 l_start_date_active,
1025 NVL(l_scheduled_flag, 'N'),
1026 l_record_version_number,
1027 'FORM',
1028 'UPDATE',
1029 l_object_id,
1030 l_end_date_active,
1031 l_end_date_active,
1032 l_project_party_id,
1033 x_call_overlap,
1034 x_assignment_action,
1035 x_return_status);
1036
1037 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1038 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1039 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1040 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1041 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1042 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1043 end if;
1044
1045 UPDATE pa_project_parties SET
1046 project_role_id = p_project_role_id,
1047 start_date_active = l_start_date_active,
1048 end_date_active = l_end_date_active,
1049 last_update_date = sysdate,
1050 last_updated_by = fnd_global.user_id,
1051 last_update_login = fnd_global.login_id
1052 WHERE project_party_id = p_portfolio_party_id;
1053
1054 Revoke_Role(p_api_version => l_api_version,
1055 p_init_msg_list => p_init_msg_list,
1056 p_project_role_id => l_project_role_id,
1057 p_object_name => G_PORTFOLIO_SET_ALL,
1058 p_object_key => l_object_id,
1059 p_party_id => l_resource_source_id,
1060 p_source_type => 'PERSON',
1061 x_return_status => x_return_status,
1062 x_msg_count => x_msg_count,
1063 x_msg_data => x_msg_data);
1064
1065 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1066 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1067 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1068 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1069 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1070 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1071 end if;
1072
1073
1074 Grant_Role(p_api_version => p_api_version,
1075 p_init_msg_list => p_init_msg_list,
1076 p_project_role_id => p_project_role_id,
1077 p_object_name => G_PORTFOLIO,
1078 p_object_set => G_PORTFOLIO_SET_ALL,
1079 p_party_id => l_resource_source_id,
1080 p_source_type => 'PERSON',
1081 x_grant_guid => l_grant_id,
1082 x_return_status => x_return_status,
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data);
1085
1086 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1087 l_msg_log := p_portfolio_party_id||','||l_resource_source_id||','||l_project_role_id;
1088 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1089 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1090 l_msg_log := p_portfolio_party_id||','||l_resource_source_id||','||l_project_role_id;
1091 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1092 end if;
1093
1094 -- end if;
1095
1096 FPA_UTILITIES_PVT.END_ACTIVITY(
1097 p_api_name => l_api_name,
1098 p_pkg_name => G_PKG_NAME,
1099 p_msg_log => l_msg_log,
1100 x_msg_count => x_msg_count,
1101 x_msg_data => x_msg_data);
1102
1103 EXCEPTION
1104 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
1105 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1106 p_api_name => l_api_name,
1107 p_pkg_name => G_PKG_NAME,
1108 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1109 p_msg_log => l_msg_log,
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data,
1112 p_api_type => G_API_TYPE);
1113
1114 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1115 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1116 p_api_name => l_api_name,
1117 p_pkg_name => G_PKG_NAME,
1118 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1119 p_msg_log => l_msg_log,
1120 x_msg_count => x_msg_count,
1121 x_msg_data => x_msg_data,
1122 p_api_type => G_API_TYPE);
1123
1124 when OTHERS then
1125 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1126 p_api_name => l_api_name,
1127 p_pkg_name => G_PKG_NAME,
1128 p_exc_name => 'OTHERS',
1129 p_msg_log => l_msg_log||SQLERRM,
1130 x_msg_count => x_msg_count,
1131 x_msg_data => x_msg_data,
1132 p_api_type => G_API_TYPE);
1133
1134 END Update_Portfolio_User;
1135
1136
1137 PROCEDURE Update_Portfolio_Owner
1138 (
1139 p_api_version IN NUMBER,
1140 p_init_msg_list IN VARCHAR2,
1141 p_portfolio_id IN NUMBER,
1142 p_person_id IN NUMBER,
1143 x_return_status OUT NOCOPY VARCHAR2,
1144 x_msg_count OUT NOCOPY NUMBER,
1145 x_msg_data OUT NOCOPY VARCHAR2) IS
1146
1147 -- standard parameters
1148 l_return_status VARCHAR2(1);
1149 l_api_name CONSTANT VARCHAR2(30) := 'Update_Portfolio_Owner';
1150 l_api_version CONSTANT NUMBER := 1.0;
1151 l_msg_log VARCHAR2(2000) := null;
1152 ----------------------------------------------------------------------------
1153 CURSOR owner_rec_csr (p_portfolio_id IN NUMBER) IS
1154
1155 select
1156 pp.project_party_id,
1157 pp.resource_source_id,
1158 pp.project_role_id
1159 from pa_project_parties pp, pa_project_role_types_b rlt
1160 where pp.object_type = 'PJP_PORTFOLIO'
1161 and pp.object_id = p_portfolio_id
1162 and PP.project_role_id = rlt.project_role_id
1163 and rlt.project_role_type = G_OWNER;
1164
1165
1166 l_wf_type VARCHAR2(250);
1167 l_wf_item_type VARCHAR2(250);
1168 l_wf_process VARCHAR2(250);
1169 l_assignment_id NUMBER;
1170
1171 l_project_party_id NUMBER;
1172 l_resource_source_id NUMBER;
1173 l_project_role_id NUMBER;
1174 l_grant_id RAW(16);
1175 l_user VARCHAR2(200);
1176
1177 BEGIN
1178
1179 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1180 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1181
1182 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
1183 p_api_name => l_api_name,
1184 p_pkg_name => G_PKG_NAME,
1185 p_init_msg_list => p_init_msg_list,
1186 l_api_version => l_api_version,
1187 p_api_version => p_api_version,
1188 p_api_type => G_API_TYPE,
1189 p_msg_log => 'Entering fpa_security_pvt.update_portfolio_owner',
1190 x_return_status => x_return_status);
1191
1192 -- check if activity started successfully
1193 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1194 l_msg_log := 'start_activity';
1195 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1196 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1197 l_msg_log := 'start_activity';
1198 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1199 end if;
1200
1201
1202 open owner_rec_csr(p_portfolio_id);
1203 fetch owner_rec_csr into
1204 l_project_party_id,
1205 l_resource_source_id,
1206 l_project_role_id;
1207 close owner_rec_csr;
1208
1209
1210 if(p_person_id = l_resource_source_id) then
1211 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1212 FPA_UTILITIES_PVT.END_ACTIVITY(
1213 p_api_name => l_api_name,
1214 p_pkg_name => G_PKG_NAME,
1215 p_msg_log => 'Exiting fpa_security_pvt.update_portfolio_owner',
1216 x_msg_count => x_msg_count,
1217 x_msg_data => x_msg_data);
1218 end if;
1219
1220
1221 /*
1222 if(p_person_id <> l_resource_source_id) then
1223 if (check_user(p_person_id, p_portfolio_id)) then
1224 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
1225 l_user := Get_party_Name(p_person_id);
1226 FPA_UTILITIES_PVT.SET_MESSAGE(
1227 p_app_name => g_app_name
1228 , p_msg_name => 'FPA_SEC_USER_EXISTS'
1229 , p_token1 => 'USER'
1230 ,p_token1_value => l_user);
1231 l_msg_log := 'FPA_SEC_USER_EXISTS'||p_person_id||','||p_portfolio_id;
1232 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1233 end if;
1234 end if;
1235 */
1236
1237 /*
1238
1239 PA_PROJECT_PARTIES_PVT.UPDATE_PROJECT_PARTY(
1240 p_commit => FND_API.G_FALSE,
1241 p_validate_only => FND_API.G_FALSE,
1242 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1243 p_debug_mode => 'N',
1244 p_object_id => l_object_id,
1245 p_object_type => l_object_type,
1246 p_project_role_id => p_project_role_id,
1247 p_resource_type_id => l_resource_type_id,
1248 p_resource_source_id => l_resource_source_id,
1249 p_resource_id => l_resource_id,
1250 p_start_date_active => l_start_date_active,
1251 p_scheduled_flag => l_scheduled_flag,
1252 p_record_version_number => l_record_version_number,
1253 p_calling_module => 'FORM',
1254 p_project_id => null,
1255 p_project_end_date => l_end_date_active,
1256 p_project_party_id => p_portfolio_party_id,
1257 p_assignment_id => 0,
1258 p_assign_record_version_number => null,
1259 p_end_date_active => l_end_date_active,
1260 x_assignment_id => l_assignment_id,
1261 x_wf_type => l_wf_type,
1262 x_wf_item_type => l_wf_item_type,
1263 x_wf_process => l_wf_process,
1264 x_return_status => l_return_status,
1265 x_msg_count => x_msg_count,
1266 x_msg_data => x_msg_data);
1267
1268 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1269 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1270 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1271 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1272 l_msg_log := p_portfolio_party_id||','||l_project_role_id;
1273 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1274 end if;
1275 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1276
1277 */
1278
1279 /*
1280 NOT ABLE TO UPDATE USING ABOVE CALL WITH VALIDATIONS AS ROLE ID IS IGNORED FOR UPDATE
1281 WHEN "PA_INSTALL.IS_PRM_LICENSED()" IS 'Y'. NEED TO FIND OUT DETAILS ON THE
1282 PROFILE OPTION IF ABOVE CALL WITH VALIDATIONS CAN BE USED.
1283 */
1284
1285 UPDATE pa_project_parties SET
1286 resource_source_id = p_person_id
1287 WHERE project_party_id = l_project_party_id;
1288
1289 Revoke_Role(p_api_version => l_api_version,
1290 p_init_msg_list => p_init_msg_list,
1291 p_project_role_id => l_project_role_id,
1292 p_object_name => G_PORTFOLIO_SET_ALL,
1293 p_object_key => p_portfolio_id,
1294 p_party_id => l_resource_source_id,
1295 p_source_type => 'PERSON',
1296 x_return_status => x_return_status,
1297 x_msg_count => x_msg_count,
1298 x_msg_data => x_msg_data);
1299
1300 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1301 l_msg_log := l_project_party_id||','||l_resource_source_id;
1302 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1303 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1304 l_msg_log := l_project_party_id||','||l_resource_source_id;
1305 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1306 end if;
1307
1308
1309 Grant_Role(p_api_version => p_api_version,
1310 p_init_msg_list => p_init_msg_list,
1311 p_project_role_id => l_project_role_id,
1312 p_object_name => G_PORTFOLIO,
1313 p_object_set => G_PORTFOLIO_SET_ALL,
1314 p_party_id => p_person_id,
1315 p_source_type => 'PERSON',
1316 x_grant_guid => l_grant_id,
1317 x_return_status => x_return_status,
1318 x_msg_count => x_msg_count,
1319 x_msg_data => x_msg_data);
1320
1321 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1322 l_msg_log := l_project_role_id||','||p_person_id;
1323 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1324 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1325 l_msg_log := l_project_role_id||','||p_person_id;
1326 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1327 end if;
1328
1329 -- end if;
1330
1331 FPA_UTILITIES_PVT.END_ACTIVITY(
1332 p_api_name => l_api_name,
1333 p_pkg_name => G_PKG_NAME,
1334 p_msg_log => l_msg_log,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data);
1337
1338 EXCEPTION
1339 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
1340 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1341 p_api_name => l_api_name,
1342 p_pkg_name => G_PKG_NAME,
1343 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1344 p_msg_log => l_msg_log,
1345 x_msg_count => x_msg_count,
1346 x_msg_data => x_msg_data,
1347 p_api_type => G_API_TYPE);
1348
1349 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1350 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1351 p_api_name => l_api_name,
1352 p_pkg_name => G_PKG_NAME,
1353 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1354 p_msg_log => l_msg_log,
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_api_type => G_API_TYPE);
1358
1359 when OTHERS then
1360 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1361 p_api_name => l_api_name,
1362 p_pkg_name => G_PKG_NAME,
1363 p_exc_name => 'OTHERS',
1364 p_msg_log => l_msg_log||SQLERRM,
1365 x_msg_count => x_msg_count,
1366 x_msg_data => x_msg_data,
1367 p_api_type => G_API_TYPE);
1368
1369 END Update_Portfolio_Owner;
1370
1371
1372
1373 /* ***************************************************************
1374 Desc: Call to delete portfolio user and the the grant for the role.
1375 parameters:
1376 p_portfolio_party_id -> pa_project_parties.project_party_id.
1377 ***************************************************************** */
1378
1379 PROCEDURE Delete_Portfolio_User
1380 (
1381 p_api_version IN NUMBER,
1382 p_init_msg_list IN VARCHAR2,
1383 p_portfolio_party_id IN PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE,
1384 p_instance_set_name IN VARCHAR2,
1385 x_return_status OUT NOCOPY VARCHAR2,
1386 x_msg_count OUT NOCOPY NUMBER,
1387 x_msg_data OUT NOCOPY VARCHAR2) IS
1388
1389 CURSOR project_parties_csr (p_portfolio_party_id in number) IS
1390 SELECT project_role_id, resource_source_id, object_id
1391 FROM pa_project_parties
1392 WHERE project_party_id = p_portfolio_party_id;
1393
1394 CURSOR verify_delete_csr (p_portfolio_party_id in number) IS
1395 SELECT 'T'
1396 FROM pa_project_parties
1397 WHERE project_party_id = p_portfolio_party_id;
1398
1399
1400 l_role_id number;
1401 l_object_id number;
1402 l_party_id number;
1403 l_instance_set_id number;
1404 l_exists varchar2(1);
1405 l_revoked varchar2(1);
1406 l_grant_id raw(16);
1407 l_user varchar2(200);
1408
1409 -- standard parameters
1410 l_return_status VARCHAR2(1);
1411 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Portfolio_User';
1412 l_api_version CONSTANT NUMBER := 1.0;
1413 l_msg_log VARCHAR2(2000) := null;
1414 ----------------------------------------------------------------------------
1415 BEGIN
1416 l_exists := FND_API.G_FALSE;
1417
1418 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1419 -- call START_ACTIVITY to create savepoint, check compatibility
1420 -- and initialize message list
1421 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
1422 p_api_name => l_api_name,
1423 p_pkg_name => G_PKG_NAME,
1424 p_init_msg_list => p_init_msg_list,
1425 l_api_version => l_api_version,
1426 p_api_version => p_api_version,
1427 p_api_type => G_API_TYPE,
1428 p_msg_log => 'Entering fpa_security_pvt.delete_portfolio_user',
1429 x_return_status => x_return_status);
1430
1431 -- check if activity started successfully
1432 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1433 l_msg_log := 'start_activity';
1434 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1435 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1436 l_msg_log := 'start_activity';
1437 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1438 end if;
1439
1440 open project_parties_csr(p_portfolio_party_id => p_portfolio_party_id);
1441 fetch project_parties_csr into l_role_id, l_party_id, l_object_id;
1442 if project_parties_csr%NOTFOUND then
1443 close project_parties_csr;
1444 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
1445 l_user := Get_party_Name(l_party_id);
1446 FPA_UTILITIES_PVT.SET_MESSAGE(
1447 p_app_name => g_app_name
1448 , p_msg_name => 'FPA_SEC_DELETE_FAILED'
1449 , p_token1 => 'USER'
1450 ,p_token1_value => l_user);
1451 l_msg_log := 'FPA_SEC_DELETE_FAILED'||p_portfolio_party_id;
1452 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1453 end if;
1454 close project_parties_csr;
1455 -- l_grant_id := pa_project_parties_utils.get_grant_id(
1456 -- p_project_party_id => p_portfolio_party_id);
1457
1458 PA_PROJECT_PARTIES_PKG.Delete_Row(x_project_id => null,
1459 x_project_party_id => p_portfolio_party_id,
1460 x_record_version_number => null);
1461
1462 -- no return status verifying delete ?
1463
1464 open verify_delete_csr(p_portfolio_party_id);
1465 fetch verify_delete_csr into l_exists;
1466 close verify_delete_csr;
1467
1468
1469 if(l_exists = FND_API.G_TRUE) then
1470 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
1471 l_user := Get_party_Name(l_party_id);
1472 FPA_UTILITIES_PVT.SET_MESSAGE(
1473 p_app_name => g_app_name
1474 , p_msg_name => 'FPA_SEC_DELETE_FAILED'
1475 , p_token1 => 'USER'
1476 ,p_token1_value => l_user);
1477 l_msg_log := 'FPA_SEC_DELETE_FAILED '||p_portfolio_party_id;
1478 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1479 end if;
1480
1481 l_instance_set_id := pa_security_pvt.get_instance_set_id(p_instance_set_name);
1482
1483 Revoke_Role(p_api_version => l_api_version,
1484 p_init_msg_list => p_init_msg_list,
1485 p_project_role_id => l_role_id,
1486 p_object_name => G_PORTFOLIO_SET_ALL,
1487 p_object_key => l_object_id,
1488 p_party_id => l_party_id,
1489 p_source_type => 'PERSON',
1490 x_return_status => x_return_status,
1491 x_msg_count => x_msg_count,
1492 x_msg_data => x_msg_data);
1493
1494 -- check if activity started successfully
1495 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1496 l_msg_log := p_portfolio_party_id||','||l_role_id||','||l_instance_set_id||','||l_party_id;
1497 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1498 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1499 l_msg_log := p_portfolio_party_id||','||l_role_id||','||l_instance_set_id||','||l_party_id;
1500 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1501 end if;
1502
1503 FPA_UTILITIES_PVT.END_ACTIVITY(
1504 p_api_name => l_api_name,
1505 p_pkg_name => G_PKG_NAME,
1506 p_msg_log => 'end fpa_security_pvt.Delete_Portfolio_User',
1507 x_msg_count => x_msg_count,
1508 x_msg_data => x_msg_data);
1509
1510
1511 EXCEPTION
1512 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
1513 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1514 p_api_name => l_api_name,
1515 p_pkg_name => G_PKG_NAME,
1516 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1517 p_msg_log => l_msg_log,
1518 x_msg_count => x_msg_count,
1519 x_msg_data => x_msg_data,
1520 p_api_type => G_API_TYPE);
1521
1522 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1523 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1524 p_api_name => l_api_name,
1525 p_pkg_name => G_PKG_NAME,
1526 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1527 p_msg_log => l_msg_log,
1528 x_msg_count => x_msg_count,
1529 x_msg_data => x_msg_data,
1530 p_api_type => G_API_TYPE);
1531
1532 when OTHERS then
1533 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1534 p_api_name => l_api_name,
1535 p_pkg_name => G_PKG_NAME,
1536 p_exc_name => 'OTHERS',
1537 p_msg_log => l_msg_log||SQLCODE||SQLERRM,
1538 x_msg_count => x_msg_count,
1539 x_msg_data => x_msg_data,
1540 p_api_type => G_API_TYPE);
1541
1542 END Delete_Portfolio_User;
1543
1544 end FPA_SECURITY_PVT;