1 PACKAGE BODY PA_ROLE_LIST_UTILS AS
2 /* $Header: PARLSTUB.pls 120.1 2005/08/19 16:55:46 mwasowic noship $ */
3
4 --Public procedure to check role list name and id
5 PROCEDURE check_role_list_name_or_id(
6 p_role_list_id IN NUMBER,
7 p_role_list_name IN VARCHAR2,
8 p_check_id_flag IN VARCHAR2,
9 x_role_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
12 IS
13 l_sysdate DATE := TRUNC(sysdate);
14 BEGIN
15 pa_debug.init_err_stack ('pa_role_list_utils.check_role_list_name_or_id');
16
17 IF p_role_list_id IS NOT NULL AND p_role_list_id <> FND_API.G_MISS_NUM THEN
18 IF p_check_id_flag <> 'N' THEN
19 SELECT role_list_id
20 INTO x_role_list_id
21 FROM pa_role_lists
22 WHERE role_list_id = p_role_list_id
23 AND TRUNC(start_date_active) <= l_sysdate
24 AND (end_date_active IS NULL OR l_sysdate <= TRUNC(end_date_active));
25 ELSE
26 x_role_list_id := p_role_list_id;
27 END IF;
28 ELSE
29 SELECT role_list_id
30 INTO x_role_list_id
31 FROM pa_role_lists
32 WHERE name = p_role_list_name
33 AND TRUNC(start_date_active) <= l_sysdate
34 AND (end_date_active IS NULL OR l_sysdate <= TRUNC(end_date_active));
35 END IF;
36 x_return_status := fnd_api.g_ret_sts_success;
37 x_error_message_code := NULL;
38 pa_debug.reset_err_stack;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 x_return_status := FND_API.G_RET_STS_ERROR;
42 x_error_message_code := 'PA_ROLE_LIST_INVALID_AMBIGOUS';
43 WHEN TOO_MANY_ROWS THEN
44 x_return_status := FND_API.G_RET_STS_ERROR;
45 x_error_message_code := 'PA_ROLE_LIST_INVALID_AMBIGOUS';
46 WHEN OTHERS THEN
47 fnd_msg_pub.add_exc_msg
48 (p_pkg_name => 'PA_ROLE_LIST_UTILS',
49 p_procedure_name => pa_debug.g_err_stack );
50 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
51 RAISE;
52 END check_role_list_name_or_id;
53
54 END PA_ROLE_LIST_UTILS;