1 PACKAGE BODY pa_role_utils AS
2 -- $Header: PARLUTLB.pls 120.2 2006/09/26 00:06:53 rfadia noship $
3
4 --
5 -- PROCEDURE
6 -- Check_Role_Name_Or_Id
7 -- PURPOSE
8 -- This procedure does the following
9 -- If role name is passed converts it to the id
10 -- If id is passed, based on the check_id_flag validates it
11 -- HISTORY
12 -- 23-JUN-2000 R. Krishnamurthy Created
13 --
14
15 ---Procedure Check_Role_Name_Or_Id
16 ------------------------------------------------------
17 procedure Check_Role_Name_Or_Id
18 ( p_role_id IN pa_project_role_types.project_role_id%TYPE
19 ,p_role_name IN pa_project_role_types.meaning%TYPE
20 ,p_check_id_flag IN VARCHAR2 := 'A'
21 ,x_role_id OUT NOCOPY pa_project_role_types.project_role_id%TYPE --File.Sql.39 bug 4440895
22 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
23 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
24
28 WHERE meaning = p_role_name;
25 CURSOR c_ids IS
26 SELECT project_role_id
27 FROM pa_project_role_types_vl
29 l_current_id NUMBER := NULL;
30 l_num_ids NUMBER:= 0;
31 l_id_found_flag VARCHAR(1) := 'N';
32
33 BEGIN
34 pa_debug.init_err_stack ('pa_role_utils.Check_Role_Name_Or_Id');
35 IF p_role_id IS NOT NULL AND p_role_id <> FND_API.G_MISS_NUM THEN
36 IF p_check_id_flag = 'Y' THEN
37 SELECT project_role_id
38 INTO x_role_id
39 FROM pa_project_role_types_b /* Bug#2788815-Changed pa_project_role_types_vl to
40 pa_project_role_types_b */
41 WHERE project_role_id = p_role_id
42 AND TRUNC(SYSDATE) BETWEEN
43 start_date_active AND
44 NVL(end_date_active,TRUNC(SYSDATE));
45 ELSIF (p_check_id_flag = 'N') then
46 x_role_id := p_role_id;
47 ELSIF (p_check_id_flag = 'A') THEN
48 IF p_role_name IS NULL THEN
49 -- return a null since since the name is null
50 x_role_id := NULL;
51 ELSE
52 -- fine the ID which matches the name
53 OPEN c_ids;
54 LOOP
55 FETCH c_ids INTO l_current_id;
56 EXIT WHEN c_ids%notfound;
57 IF (l_current_id = p_role_id) THEN
58 l_id_found_flag := 'Y';
59 x_role_id := p_role_id;
60 END IF;
61 END LOOP;
62 l_num_ids := c_ids%rowcount;
63 CLOSE c_ids;
64
65 IF l_num_ids = 0 THEN
66 -- No IDS for the name
67 RAISE no_data_found;
68 ELSIF(l_num_ids = 1) THEN
69 -- there is only one
70 x_role_id := l_current_id;
71 ELSIF (l_id_found_flag = 'N') THEN
72 -- more than one ID found for the name
73 RAISE too_many_rows;
74 END IF;
75 END IF;
76 END IF;
77 ELSE
78 IF (p_role_name IS NOT NULL) then
79 SELECT project_role_id
80 INTO x_role_id
81 FROM pa_project_role_types_vl
82 WHERE meaning = p_role_name
83 AND TRUNC(SYSDATE) BETWEEN
84 start_date_active AND
85 NVL(end_date_active,TRUNC(SYSDATE));
86 ELSE
87 x_role_id := NULL;
88
89 END IF;
90 END IF;
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 pa_debug.reset_err_stack;
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 x_role_id := null;
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 x_error_message_code := 'PA_ROLE_INVALID_AMBIGOUS';
99
100 WHEN TOO_MANY_ROWS THEN
101 x_role_id := null;
102 x_return_status := FND_API.G_RET_STS_ERROR;
103 x_error_message_code := 'PA_ROLE_INVALID_AMBIGOUS';
104 WHEN OTHERS THEN
105 x_role_id := null;
106 fnd_msg_pub.add_exc_msg
107 (p_pkg_name => 'PA_ROLE_UTILS',
108 p_procedure_name => pa_debug.g_err_stack );
109 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
110 RAISE;
111 END Check_Role_Name_Or_Id;
112
113 procedure Check_Role_RoleList
114 ( p_role_id IN pa_project_role_types.project_role_id%TYPE
115 ,p_role_name IN pa_project_role_types.meaning%TYPE
116 ,p_role_list_id IN pa_role_lists.role_list_id%TYPE := NULL
117 ,p_role_list_name IN pa_role_lists.name%TYPE := null
118 ,p_check_id_flag IN VARCHAR2
119 ,x_role_id OUT NOCOPY pa_project_role_types.project_role_id%TYPE --File.Sql.39 bug 4440895
120 ,x_role_list_id OUT NOCOPY pa_role_lists.role_list_id%TYPE --File.Sql.39 bug 4440895
121 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
122 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
123
124 l_status VARCHAR2(10) ;
125 l_sysdate DATE := TRUNC(sysdate);
126 BEGIN
127 pa_debug.init_err_stack ('pa_role_utils.Check_Role_RoleList');
128
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 begin
132 IF p_role_id IS NOT NULL AND p_role_id <> FND_API.G_MISS_NUM THEN
133 IF p_check_id_flag = 'Y' THEN
134 SELECT project_role_id
135 INTO x_role_id
136 FROM pa_project_role_types_b /* Bug#2788815-Changed pa_project_role_types_vl to
137 pa_project_role_types_b */
138 WHERE project_role_id = p_role_id
139 AND TRUNC(SYSDATE) BETWEEN
140 start_date_active AND
141 NVL(end_date_active,TRUNC(SYSDATE));
142 ELSE
143 x_role_id := p_role_id;
144 END IF;
145 ELSE
146 SELECT project_role_id
147 INTO x_role_id
148 FROM pa_project_role_types_vl
149 WHERE meaning = p_role_name
150 AND TRUNC(SYSDATE) BETWEEN
151 start_date_active AND
152 NVL(end_date_active,TRUNC(SYSDATE));
153 END IF;
154
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 x_error_message_code := 'PA_ROLE_INVALID_AMBIGOUS';
160
161 WHEN TOO_MANY_ROWS THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 x_error_message_code := 'PA_ROLE_INVALID_AMBIGOUS';
164
165 WHEN OTHERS THEN
166 fnd_msg_pub.add_exc_msg
167 (p_pkg_name => 'PA_ROLE_UTILS',
171 END;
168 p_procedure_name => pa_debug.g_err_stack );
169 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
170 RAISE;
172
173 -- if it alread fails, then we return.
174 IF x_return_status <> FND_API.g_ret_sts_success THEN
175 RETURN;
176 END IF;
177
178
179 -- the role_list_id and role_list_name are not passed in
180 -- we will return here too
181 IF p_role_list_id IS NULL and p_role_list_name IS NULL then
182 RETURN;
183 END IF;
184
185
186 BEGIN
187 IF p_role_list_id IS NOT NULL AND p_role_list_id <> FND_API.G_MISS_NUM THEN
188 IF p_check_id_flag <> 'N' THEN
189 SELECT role_list_id
190 INTO x_role_list_id
191 FROM pa_role_lists
192 WHERE role_list_id = p_role_list_id
193 AND TRUNC(start_date_active) <= l_sysdate
194 AND (end_date_active IS NULL OR l_sysdate <= TRUNC(end_date_active));
195 ELSE
196 x_role_list_id := p_role_list_id;
197 END IF;
198 ELSE
199 SELECT role_list_id
200 INTO x_role_list_id
201 FROM pa_role_lists
202 WHERE name = p_role_list_name
203 AND TRUNC(start_date_active) <= l_sysdate
204 AND (end_date_active IS NULL OR l_sysdate <= TRUNC(end_date_active));
205 END IF;
206
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 x_error_message_code := 'PA_ROLE_LIST_INVALID_AMBIGOUS';
212
213 WHEN TOO_MANY_ROWS THEN
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 x_error_message_code := 'PA_ROLE_LIST_INVALID_AMBIGOUS';
216
217 WHEN OTHERS THEN
218 fnd_msg_pub.add_exc_msg
219 (p_pkg_name => 'PA_ROLE_LIST_UTILS',
220 p_procedure_name => pa_debug.g_err_stack );
221 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
222 RAISE;
223
224 END;
225
226
227 -- if it alread fails, then we return.
228 IF x_return_status <> FND_API.g_ret_sts_success THEN
229 RETURN;
230 END IF;
231
232 -- we need to validate that the role actually belongs to the role_type
233 SELECT 'Y' INTO l_status
234 FROM dual
235 WHERE exists
236 (
237 SELECT role_list_id FROM pa_role_list_members
238 WHERE role_list_id = x_role_list_id
239 AND project_role_id = x_role_id
240 );
241
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243 x_error_message_code := NULL;
244 pa_debug.reset_err_stack;
245
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 x_error_message_code := 'PA_ROLE_NOT_IN_ROLELIST';
250 WHEN OTHERS THEN
251 fnd_msg_pub.add_exc_msg
252 (p_pkg_name => 'PA_ROLE_UTILS',
253 p_procedure_name => pa_debug.g_err_stack );
254 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
255 RAISE;
256
257 END Check_Role_RoleList;
258
259 --
260 -- PROCEDURE
261 -- get_role_defaults
262 -- PURPOSE
263 -- This procedure returns the defaults for a role
264 --
265 -- HISTORY
266 PROCEDURE Get_Role_Defaults
267 (p_role_id IN pa_project_role_types.project_role_id%TYPE
268 ,x_meaning OUT NOCOPY pa_project_role_types.meaning%TYPE --File.Sql.39 bug 4440895
269 ,x_default_min_job_level OUT NOCOPY pa_project_role_types.default_min_job_level%TYPE --File.Sql.39 bug 4440895
270 ,x_default_max_job_level OUT NOCOPY pa_project_role_types.default_max_job_level%TYPE --File.Sql.39 bug 4440895
271 ,x_menu_id OUT NOCOPY pa_project_role_types.menu_id%TYPE --File.Sql.39 bug 4440895
272 ,x_schedulable_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
273 ,x_default_job_id OUT NOCOPY pa_project_role_types.default_job_id%TYPE --File.Sql.39 bug 4440895
274 ,x_def_competencies OUT NOCOPY pa_hr_competence_utils.competency_tbl_typ --File.Sql.39 bug 4440895
275 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
276 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
277
278 l_num NUMBER;
279 l_return_status VARCHAR2(30);
280 l_error_message_code VARCHAR2(30);
281 l_competency_tbl pa_hr_competence_utils.competency_tbl_typ;
282 CURSOR l_role_csr IS
283 SELECT meaning,
284 default_min_job_level,
285 default_max_job_level,
286 menu_id,
287 default_job_id
288 FROM pa_project_role_types_vl
289 WHERE project_role_id = p_role_id ;
290 BEGIN
291 pa_debug.init_err_stack ('pa_role_utils.get_role_defaults');
292 OPEN l_role_csr;
293 FETCH l_role_csr INTO
294 x_meaning,
295 x_default_min_job_level,
296 x_default_max_job_level,
297 x_menu_id,
298 x_default_job_id ;
299 IF l_role_csr%NOTFOUND THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 x_error_message_code := 'PA_ROLE_INVALID_AMBIGOUS';
302 CLOSE l_role_csr;
303 pa_debug.reset_err_stack;
304 RETURN;
305 END IF;
306 CLOSE l_role_csr;
307 -- Get the info on whether the role is schedulable here
308 x_schedulable_flag := get_schedulable_flag(p_role_id);
309 -- Get the default competencies for the role
310 pa_hr_competence_utils.get_competencies
311 ( p_object_name => 'PROJECT_ROLE'
312 ,p_object_id => p_role_id
313 ,x_competency_tbl => l_competency_tbl
314 ,x_no_of_competencies => l_num
315 ,x_error_message_code => l_error_message_code
316 ,x_return_status => l_return_status );
320 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
317 -- It is possible the the role does not have competencies
318 -- It is ok to have no competencies;hence we will not raise errors
319 -- if l_num = 0;
321 x_error_message_code := l_error_message_code;
322 x_return_status := l_return_status;
323 pa_debug.reset_err_stack;
324 RETURN ;
325 END IF ;
326 x_def_competencies := l_competency_tbl;
327 x_return_status:= FND_API.G_RET_STS_SUCCESS;
328 pa_debug.reset_err_stack;
329 EXCEPTION
330 WHEN OTHERS THEN
331 fnd_msg_pub.add_exc_msg
332 (p_pkg_name => 'PA_ROLE_UTILS',
333 p_procedure_name => pa_debug.g_err_stack );
334 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
335 RAISE;
336 END Get_Role_Defaults;
337
338
339 -- PROCEDURE
340 -- Validate_Role_Competencies
341 -- PURPOSE
342 -- This procedure validates the competencies for a given role
343 --
344 -- HISTORY
345 PROCEDURE Validate_Role_Competency
346 (p_competence_id IN per_competences.competence_id%TYPE
347 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
348 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
349 l_business_group_id NUMBER := 0;
350
351 CURSOR l_get_bg_csr IS
352 SELECT business_group_id
353 FROM per_competences
354 WHERE competence_id = p_competence_id;
355 BEGIN
356 pa_debug.init_err_stack ('pa_role_utils.validate_role_competency');
357 OPEN l_get_bg_csr;
358 FETCH l_get_bg_csr INTO l_business_group_id;
359 IF l_get_bg_csr%NOTFOUND THEN
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 x_error_message_code := 'PA_COMPETENCY_INVALID_AMBIGOUS';
362 ELSIF (l_business_group_id IS NOT NULL AND
363 pa_cross_business_grp.IsCrossBGProfile = 'Y' ) THEN
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 x_error_message_code := 'PA_ROLE_COMPETENCY_NOT_GLOBAL';
366 ELSIF l_business_group_id is not null THEN
367 IF l_business_group_id <> pa_utils.business_group_id THEN
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 x_error_message_code := 'PA_ROLE_COMPETENCY_BG_INVALID';
370 END IF;
371 ELSE
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373 END IF;
374 CLOSE l_get_bg_csr;
375 pa_debug.reset_err_stack;
376 EXCEPTION
377 WHEN OTHERS THEN
378 fnd_msg_pub.add_exc_msg
379 (p_pkg_name => 'PA_ROLE_UTILS',
380 p_procedure_name => pa_debug.g_err_stack );
381 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
382 RAISE;
383 END Validate_Role_Competency ;
384
385
386 ----PROCEDURE Get_Schedulable_flag
387 ----This procedure returns the schedulable flag for the role
388 --- If the role has the Allow Scheduling control defined,the flag will be 'Y'
389 --- Otherwise the flag will be 'N'
390 --- The return status will be 'S' if the API completes succesfully
391 --- The return status will be 'E' if the API has an error
392 --- The return status will be 'U' if the API has an unexpected error
393 ----x_error_message_code is the fnd message code
394 ----------------------------------------------------------------------------
395 PROCEDURE Get_Schedulable_flag (p_role_id in number
396 ,x_schedulable_flag out NOCOPY varchar2 --File.Sql.39 bug 4440895
397 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
398 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
399 cursor c_allow_schedule is
400 --select '1'
401 --from dual
402 --where exists
403 select 'Y'
404 from pa_role_controls
405 where project_role_id=p_role_id
406 and role_control_code='ALLOW_SCHEDULE'
407 AND ROWNUM = 1;
408 v_dummy varchar2(1);
409 begin
410 open c_allow_schedule;
411 fetch c_allow_schedule into v_dummy;
412 if c_allow_schedule%FOUND then
413 x_schedulable_flag:='Y';
414 x_return_status:=FND_API.G_RET_STS_SUCCESS;
415 else
416 x_schedulable_flag:='N';
417 x_return_status:= FND_API.G_RET_STS_SUCCESS;
418 end if;
419 close c_allow_schedule;
420 exception
421 when others then
422 close c_allow_schedule;
423 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
424 x_error_message_code:=SQLCODE;
425 end;
426
427 ---FUNCTION Get_Schedulable_flag
428 FUNCTION Get_Schedulable_flag (p_role_id in number) return varchar2 is
429 cursor c_allow_schedule is
430 --select '1'
431 --from dual
432 --where exists
433 select 'Y'
434 from pa_role_controls
435 where project_role_id=p_role_id
436 and role_control_code='ALLOW_SCHEDULE'
437 AND ROWNUM =1;
438 v_dummy varchar2(1);
439 begin
440 open c_allow_schedule;
441 fetch c_allow_schedule into v_dummy;
442 if c_allow_schedule%FOUND then
443 close c_allow_schedule;
444 return 'Y';
445 else
446 close c_allow_schedule;
447 return 'N';
448 end if;
449 exception
450 when others then
451 close c_allow_schedule;
452 raise;
453 end;
454
455
456 --- PROCEDURE Check_delete_role_OK
457 ---This procedure will check if the role can be deleted or not.
458 -- Here are the rules:
459 --1.Roles in pa_project_parties table can not be deleted
463 -- the project_role_id. All pre-seeded roles should have project_role_id <1000
460 --2.Roles in pa_project_assignments table can not be deleted
461 --3.Roles in any contracts team table (?) can not be deleted
462 --4 Pre-seeded roles can not be deleted. We decide the pre-seeded roles by
464 --If the x_return_status is 'S', then the role can be deleted
465 --If the x_return_status is 'E', then the role can not be deleted
466 --If the x_return_status is 'U', then unexpected error happen
467 --x_error_message_code is the fnd message code if the x_return_status is 'E'
468 --x_error_message_code is the SQL error code if the x_return_status is 'U'
469 --------------------------------------------------------------------------------
470 PROCEDURE Check_delete_role_OK (p_role_id in number
471 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
472 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
473 cursor c_seed_roles is
474 --select '1'
475 --from dual
476 --where exists
477 select 'Y'
478 from pa_project_role_types_b /* Bug#2788815-Changed pa_project_role_types_vl to
479 pa_project_role_types_b */
480 where project_role_id in (1,2,3,4)
481 and project_role_id=p_role_id
482 AND ROWNUM =1;
483
484 v_dummy varchar2(1);
485 v_dummy1 varchar2(1);
486 Begin
487 open c_seed_roles;
488 fetch c_seed_roles into v_dummy1;
489 if c_seed_roles%FOUND then
490 x_return_status:=FND_API.G_RET_STS_ERROR;
491 x_error_message_code:='PA_COMMON_SEEDED_ROLES';
492 else
493 if is_role_in_use(p_role_id)='N' then
494 x_return_status:=FND_API.G_RET_STS_SUCCESS;
495 else
496 x_return_status:=FND_API.G_RET_STS_ERROR;
497 x_error_message_code:='PA_COMMON_ROLE_IN_USE';
498 end if;
499 end if;
500 close c_seed_roles;
501 exception
502 when others then
503 close c_seed_roles;
504 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
505 x_error_message_code:=SQLCODE;
506 End;
507
508
509 ---- PROCEDURE Check_remove_control_ok
510 -- This procedure will check if the user can remove any control
511 -- for the role
512 ---Business rules:
513 ---The user is not allowed to remove any of the controls if
514 ---the role is in use and the role control matches the object_type in
515 ---pa_project_parties in a certain way
516 --If the x_return_status is 'S', then the role control can not be removed
517 --If the x_return_status is 'E', then the role control can not be removed
518 --If the x_return_status is 'U', then unexpected error happen
519 --x_error_message_code is the fnd message code if the x_return_status is 'E'
520 --x_error_message_code is the SQL error code if the x_return_status is 'U'
521 ------------------------------------------------------------------
522 PROCEDURE Check_remove_control_ok(p_role_id in number
523 ,p_role_control_code in varchar2
524 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
525 ,x_error_message_code out NOCOPY varchar2) is --File.Sql.39 bug 4440895
526 /* Fix for bug 1829383 */
527 cursor c_role_control is
528 select created_by
529 from pa_role_controls
530 where project_role_id=p_role_id
531 and role_control_code =p_role_control_code;
532 /* Fix for bug 1829383 */
533 cursor c_seed_roles is
534 --select '1'
535 --from dual
536 --where exists
537 select 'Y'
538 from pa_project_role_types_b /* Bug#2788815-Changed pa_project_role_types to
539 pa_project_role_types_b */
540 where project_role_id in (1,2,3,4)
541 and project_role_id=p_role_id
542 AND ROWNUM =1;
543 v_dummy varchar2(1);
544 v_cr_by NUMBER;
545
546 Begin
547
548 ------The following logic need be modified later to
549 ------consider the object_type in pa_project_parties
550
551 open c_seed_roles;
552 fetch c_seed_roles into v_dummy;
553 if c_seed_roles%FOUND then
554 /* Fix for bug 1829383
555 To make it possible for a user to remove the user defined controls
556 from the pre-seeded role types */
557 OPEN c_role_control;
558 FETCH c_role_control INTO v_cr_by;
559 IF c_role_control%FOUND THEN
560 IF v_cr_by =1 THEN
561 x_return_status:=FND_API.G_RET_STS_ERROR;
562 x_error_message_code:='PA_COMMON_SEEDED_ROLES';
563 ELSE
564 x_return_status:=FND_API.G_RET_STS_SUCCESS;
565 END IF;
566 -- x_return_status:=FND_API.G_RET_STS_SUCCESS;
567 -- x_return_status:=FND_API.G_RET_STS_ERROR;
568 -- x_error_message_code:='PA_COMMON_SEEDED_ROLES';
569 END IF;
570 CLOSE c_role_control;
571 /* Fix for bug 1829383*/
572 else
573 /* Commented the following line for bug 2375913
574 x_return_status:=FND_API.G_RET_STS_SUCCESS;
575 */
576 /* Uncommented the following block of code for bug 2375913 */
577 /* Added the below if condition for ALLOW_QUERY_LABOR_COST for Bug 2951857 */
578 If p_role_control_code <> 'ALLOW_QUERY_LABOR_COST' then
579 if is_role_in_use(p_role_id)='N' then
580 x_return_status:=FND_API.G_RET_STS_SUCCESS;
581 else
582 x_return_status:=FND_API.G_RET_STS_ERROR;
583 x_error_message_code:='PA_COMMON_ROLE_IN_USE';
584 end if;
585 else
586 x_return_status:=FND_API.G_RET_STS_SUCCESS;
587 end if;
588 /* end of code uncommented for bug 2375913 */
589 end if;
590 CLOSE c_seed_roles;
591 exception
592 when others then
593 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
597
594 x_error_message_code:=SQLCODE;
595 End;
596
598 ------ PROCEDURE Check_delete_role_list_OK
599 -- This procedure will check if the role list can be removed or not
600 -- If a role list has been associated with a project , then it
601 -- can not be removed
602 --If the x_return_status is 'S', then the role list can be deleted
603 --If the x_return_status is 'E', then the role list can not be deleted
604 --If the x_return_status is 'U', then unexpected error happen
605 --x_error_message_code is the fnd message code if the x_return_status is 'E'
606 --x_error_message_code is the SQL error code if the x_return_status is 'U'
607 --------------------------------------------------------------------
608 PROCEDURE Check_delete_role_list_OK(p_role_list_id in number
609 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
610 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
611
612 cursor c_role_list_in_use is
613 --select '1'
614 --from dual
615 --where exists
616 select 'Y'
617 from pa_projects_all
618 where role_list_id=p_role_list_id
619 AND ROWNUM=1;
620 v_dummy varchar2(1);
621
622 begin
623 open c_role_list_in_use;
624 fetch c_role_list_in_use into v_dummy;
625 if c_role_list_in_use%NOTFOUND then
626 x_return_status:=FND_API.G_RET_STS_SUCCESS;
627 else
628 x_return_status:=FND_API.G_RET_STS_ERROR;
629 x_error_message_code:='PA_COMMON_ROLE_LIST_IN_USE';
630 end if;
631 close c_role_list_in_use;
632 exception
633 when others then
634 close c_role_list_in_use;
635 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
636 x_error_message_code:=SQLCODE;
637 End;
638
639
640 ----The following procedure is commented out becuase of the business rules changes
641 ---Based on the discussion on August 25, 2000 with Mary and Selva, we should allow
642 ---the user to change the menu name for pre-seeded roles and roles in use.
643 ---We need to update the menu_id in fnd_grants with changed menu_id .
644 /*---PROCEDURE Check_change_role_menu_OK
645 ---This procedure check if the user can change the menu for the role
646 ---Here, 'change' means changing the menu_id by choosing another menu for the role,
647 --- instead of changing functions for the same menu in the menu form
648 ---If a role has a menu and the role is used in pa_project_parties (fnd_grants)then
649 ---the menu can not be changed in the role
650 PROCEDURE Check_change_role_menu_OK(p_role_id in number
651 ,x_return_status out varchar2
652 ,x_error_message_code out varchar2) IS
653 v_menu_id number;
654 begin
655 select menu_id
656 into v_menu_id
657 from pa_project_role_types
658 where project_role_id=p_role_id;
659
660 if v_menu_id is not null then
661 if is_role_in_use(p_role_id)='N' then
662 x_return_status:=FND_API.G_RET_STS_SUCCESS;
663 else
664 x_return_status:=FND_API.G_RET_STS_ERROR;
665 x_error_message_code:='PA_COMMON_ROLE_IN_USE';
666 end if;
667 else
668 x_return_status:=FND_API.G_RET_STS_ERROR;
669 end if;
670
671 exception
672 when no_data_found then
673 null;
674 when others then
675 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
676 x_error_message_code:=SQLCODE;
677 end; */
678
679
680 ----PROCEDURE update_menu_in_grants
681 ----This procedure updates the menu_id (role_id) in fnd_grants
682 ----in case the user changes the menu id for the role
683 PROCEDURE update_menu_in_grants(p_role_id in number
684 ,p_menu_id in number
685 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
686 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
687 v_return_status varchar2(1);
688 v_msg_count number;
689 v_msg_data varchar2(240);
690 l_count number;
691
692 begin
693 select count(*)
694 into l_count
695 from pa_project_parties
696 where project_role_id = p_role_id;
697
698 IF l_count > 0 THEN
699
700 pa_security_pvt.update_menu(
701 p_commit => FND_API.G_FALSE,
702 -- p_debug_mode => 'N',
703 p_project_role_id => p_role_id,
704 p_menu_id => p_menu_id,
705 x_return_status => v_return_status,
706 x_msg_count => v_msg_count,
707 x_msg_data => v_msg_data
708 );
709
710 if v_return_status=fnd_api.g_ret_sts_success then
711 x_return_status:=fnd_api.g_ret_sts_success;
712 return;
713 else
714 x_return_status:=fnd_api.g_ret_sts_error;
715 return;
716 end if;
717
718 END IF;
719
720 x_return_status:=fnd_api.g_ret_sts_success;
721
722 exception
723 when others then
724 x_return_status:=fnd_api.g_ret_sts_unexp_error;
725 x_error_message_code:=SQLCODE;
726 end ;
727
728 ----PROCEDURE Disable_role_based_sec
729 ----This procedure remove the records from fnd_grants
730 ----in case the user disables role based security from
731 ----role form
732 PROCEDURE disable_role_based_sec(p_role_id in number
733 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
734 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
735 v_return_status varchar2(1);
736 v_msg_count number;
737 v_msg_data varchar2(240);
738 l_count number;
739
743 from pa_project_parties
740 begin
741 select count(*)
742 into l_count
744 where project_role_id = p_role_id;
745
746 IF l_count > 0 THEN
747
748 pa_security_pvt.revoke_role_based_sec(
749 p_commit => FND_API.G_FALSE,
750 -- p_debug_mode =>'N',
751 p_project_role_id => p_role_id,
752 x_return_status => v_return_status,
753 x_msg_count => v_msg_count,
754 x_msg_data => v_msg_data
755 );
756
757 if v_return_status = fnd_api.g_ret_sts_success then
758 x_return_status := fnd_api.g_ret_sts_success;
759 return;
760 else
761 x_return_status := fnd_api.g_ret_sts_error;
762 return;
763 end if;
764 END IF;
765
766 x_return_status:=fnd_api.g_ret_sts_success;
767
768 exception
769 when others then
770 x_return_status:=fnd_api.g_ret_sts_unexp_error;
771 x_error_message_code:=SQLCODE;
772 end ;
773
774 ----PROCEDURE Enable_role_based_sec
775 ----This procedure upgrades existing records in pa_project_parties
776 -----to fnd_grants in case the user enables role based security from
777 ----role form
778 PROCEDURE Enable_role_based_sec(p_role_id in number
779 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
780 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
781
782 v_return_status varchar2(1);
783 v_msg_count number;
784 v_msg_data varchar2(240);
785 l_count number;
786
787 begin
788 select count(*)
789 into l_count
790 from pa_project_parties
791 where project_role_id = p_role_id;
792 -- and parties.grant_id is null;
793
794 -- hr_utility.trace('before call to grant_role_based_sec');
795 IF l_count > 0 THEN
796 pa_security_pvt.grant_role_based_sec(
797 p_commit => FND_API.G_FALSE,
798 -- p_debug_mode => 'N',
799 p_project_role_id => p_role_id,
800 x_return_status => v_return_status,
801 x_msg_count => v_msg_count,
802 x_msg_data => v_msg_data
803 );
804 -- hr_utility.trace('v_return_status is ' || v_return_status);
805
806 -- hr_utility.trace('after call to grant_role_based_sec');
807 if v_return_status = fnd_api.g_ret_sts_success then
808 x_return_status := fnd_api.g_ret_sts_success;
809 return;
810 else
811 x_return_status := fnd_api.g_ret_sts_error;
812 return;
813 end if;
814
815 END IF;
816
817 x_return_status := fnd_api.g_ret_sts_success;
818
819 exception
820 when others then
821 x_return_status := fnd_api.g_ret_sts_unexp_error;
822 x_error_message_code := SQLCODE;
823 end ;
824
825
826 --Function is_role_in_use
827 ------------------------------------------------------------------
828 FUNCTION is_role_in_use(p_role_id in number) return varchar2 IS
829
830 /***********************************************************
831 * Bug - 3575890
832 * Desc - We should also check if the role exists in
833 * pa_rbs_elements and pa_resource_list_members
834 * table.
835 *********************************************************/
836 cursor c_role_in_use is
837 SELECT '1'
838 FROM dual
839 WHERE EXISTS (SELECT 'Y'
840 FROM pa_project_parties ppp
841 WHERE ppp.project_role_id=p_role_id)
842 OR EXISTS
843 (SELECT 'Y'
844 FROM pa_project_assignments ppa
845 WHERE ppa.project_role_id=p_role_id)
846 OR EXISTS
847 (SELECT 'Y'
848 FROM pa_rbs_elements
849 WHERE resource_type_id = 15
850 AND resource_source_id = p_role_id)
851 OR EXISTS
852 (SELECT 'Y'
853 FROM pa_resource_list_members
854 where project_role_id = p_role_id);
855 v_dummy varchar2(1);
856 Begin
857 open c_role_in_use;
858 fetch c_role_in_use into v_dummy;
859 if c_role_in_use%NOTFOUND then
860 close c_role_in_use;
861 return 'N';
862 else
863 close c_role_in_use;
864 return 'Y';
865 end if;
866 exception
867 when others then
868 close c_role_in_use;
869 raise;
870 End;
871
872 ---PROCEDURE Check_dup_role_name
873 ---This procedure check if the role name (meaning) is duplicate
874 ---It will be called in private api before insert into
875 ---a new record into the role table or update an existing record
876 PROCEDURE Check_dup_role_name(p_meaning in varchar2
877 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
878 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
879 cursor c_exist is
880 select 'y'
881 from pa_project_role_types_vl
882 where meaning =p_meaning;
883
884 v_dummy varchar2(1) ;
885 begin
886 open c_exist;
887 fetch c_exist into v_dummy;
888 if c_exist%notfound then
889 x_return_status:=fnd_api.g_ret_sts_success;
890 else
891 x_return_status:=fnd_api.g_ret_sts_error;
892 x_error_message_code:='PA_COMMON_DUP_ROLE_NAME';
893 end if;
894 close c_exist;
895 exception
896 when others then
897 close c_exist;
898 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
899 x_error_message_code:=SQLCODE;
900 end;
901
905 ---a new record into the role list table or update an existing record
902 ---PROCEDURE Check_dup_role_list_name
903 ---This procedure checks if the role list name is duplicate
904 ---It will be called in private api before insert into
906 PROCEDURE Check_dup_role_list_name(p_name in varchar2
907 ,x_return_status out NOCOPY varchar2 --File.Sql.39 bug 4440895
908 ,x_error_message_code out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
909 cursor c_exist is
910 select 'y'
911 from pa_role_lists
912 where name =p_name;
913
914 v_dummy varchar2(1) ;
915 begin
916 open c_exist;
917 fetch c_exist into v_dummy;
918 if c_exist%notfound then
919 x_return_status:=fnd_api.g_ret_sts_success;
920 else
921 x_return_status:=fnd_api.g_ret_sts_error;
922 x_error_message_code:='PA_COMMON_DUP_ROLE_LIST_NAME';
923 end if;
924 close c_exist;
925 exception
926 when others then
927 close c_exist;
928 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
929 x_error_message_code:=SQLCODE;
930 end;
931
932 end PA_ROLE_UTILS ;
[Home] [Help]