1 PACKAGE BODY pa_role_status_menu_utils AS
2 -- $Header: PAXRSMUB.pls 115.6 2003/08/21 07:14:28 nmishra ship $
3
4 --
5 -- PROCEDURE
6 -- check_dup_role_status
7 -- PURPOSE
8 -- This procedure checks to see that the same status
9 -- has not already been mapped to a menu for a given role.
10 -- HISTORY
11 -- 22-May-2003 Ranjana Murthy - Created
12 --
13 -- PROCEDURE check_dup_role_status
14 -- This procedure checks if the role already has a status mapping
15 -- for the specified status - a role can only have one mapping per status
16 -- It will be called from the private api before inserting
17 -- a new record into the role status menu table or before updating
18 -- an existing record
19
20 PROCEDURE check_dup_role_status(p_role_status_menu_id in number
21 ,p_role_id in number
22 ,p_status_code in varchar2
23 ,p_return_status out NOCOPY varchar2
24 ,p_error_message_code out NOCOPY varchar2) IS
25 cursor c_exists is
26 select 'Y'
27 from pa_role_status_menu_map
28 where role_id = p_role_id
29 and status_code = p_status_code
30 and status_type = 'PROJECT'
31 and role_status_menu_id <> nvl(p_role_status_menu_id, -99);
32
33 l_dummy VARCHAR2(1) ;
34
35 BEGIN
36 OPEN c_exists;
37 FETCH c_exists into l_dummy;
38 IF c_exists%NOTFOUND THEN
39 p_return_status := fnd_api.g_ret_sts_success;
40 ELSE
41 p_return_status := fnd_api.g_ret_sts_error;
42 p_error_message_code := 'PA_DUP_ROLE_STATUS_MAPPING';
43 END IF;
44 CLOSE c_exists;
45 EXCEPTION
46 WHEN OTHERS THEN
47 CLOSE c_exists;
48 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
49 p_error_message_code := SQLCODE;
50 END check_dup_role_status;
51
52 --
53 -- PROCEDURE
54 -- check_status_is_in_use
55 -- PURPOSE
56 -- This procedure checks to see that the given status
57 -- exists in pa_role_status_menu_map - if it does, it
58 -- cannot be deleted from the Project Status form.
59 -- Called from the PA_PROJECT_STUS_UTILS.Allow_Status_Deletion
60 -- procedure.
61 -- HISTORY
62 -- 20-June-2003 Ranjana Murthy - Created
63 --
64 PROCEDURE check_status_is_in_use(p_status_code IN VARCHAR2
65 ,p_in_use_flag OUT NOCOPY VARCHAR2
66 ,p_return_status OUT NOCOPY VARCHAR2
67 ,p_error_message_code OUT NOCOPY VARCHAR2) IS
68 cursor c_exists is
69 select 'Y'
70 from pa_role_status_menu_map
71 where status_code = p_status_code
72 and status_type = 'PROJECT';
73
74 BEGIN
75
76 p_in_use_flag := 'N';
77
78 OPEN c_exists;
79 FETCH c_exists into p_in_use_flag;
80 IF c_exists%NOTFOUND THEN
81 p_return_status := fnd_api.g_ret_sts_success;
82 ELSE
83 p_return_status := fnd_api.g_ret_sts_error;
84 p_error_message_code := 'PA_ROLE_STATUS_MAPPING_EXISTS';
85 END IF;
86 CLOSE c_exists;
87 EXCEPTION
88 WHEN OTHERS THEN
89 CLOSE c_exists;
90 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
91 p_error_message_code := SQLCODE;
92 END check_status_is_in_use;
93 --
94 -- PROCEDURE
95 -- get_role_status_menus
96 -- PURPOSE
97 -- This procedure checks is used by the pa_security API
98 -- to get all the status menu mappings for a given role.
99 --
100 -- HISTORY
101 -- 22-May-2003 Ranjana Murthy - Created
102 --
103 -- PROCEDURE get_role_status_menus
104 -- This API will return:
105 -- 1. Status Level and Default Menu Id from PA_PROJECT_ROLE_TYPES_B for
106 -- the given role_id.
107 -- 2. Tables of Status Codes, Status Type, and corresponding Menu Names
108 -- from PA_ROLE_STATUS_MENU_MAP for the given role_id.
109
110 PROCEDURE get_role_status_menus(
111 p_role_id IN NUMBER
112 ,x_role_status_menu_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
113 ,x_status_level OUT NOCOPY VARCHAR2
114 ,x_default_menu_name OUT NOCOPY VARCHAR2
115 ,x_status_type_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type
116 ,x_status_code_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type
117 ,x_menu_name_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type
118 ,x_return_status OUT NOCOPY VARCHAR2
119 ,x_error_message_code OUT NOCOPY VARCHAR2) IS
120
121 cursor csr_get_role_level_info is
122 select nvl(prt.status_level, 'SYSTEM'), fm.menu_name
123 from pa_project_role_types_b prt, fnd_menus fm
124 where prt.project_role_id = p_role_id
125 and prt.menu_id = fm.menu_id(+);
126
127 cursor csr_get_role_status_info is
128 select prsm.role_status_menu_id, prsm.status_type,
129 prsm.status_code, fm.menu_name
130 from pa_role_status_menu_map prsm, fnd_menus fm
131 where prsm.role_id = p_role_id
132 and prsm.menu_id = fm.menu_id;
133
134 l_count NUMBER := 1;
135
136 BEGIN
137
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 OPEN csr_get_role_level_info;
141 FETCH csr_get_role_level_info into x_status_level, x_default_menu_name;
142 IF csr_get_role_level_info%NOTFOUND THEN
143 x_return_status := fnd_api.g_ret_sts_error;
144 x_error_message_code := 'PA_INVALID_PROJECT_ROLE';
145 ELSE
146 IF x_default_menu_name IS NOT NULL THEN
147 OPEN csr_get_role_status_info;
148 FETCH csr_get_role_status_info BULK COLLECT into
149 x_role_status_menu_id_tbl,
150 x_status_type_tbl,
151 x_status_code_tbl,
152 x_menu_name_tbl;
153 CLOSE csr_get_role_status_info;
154 ELSE
155 x_role_status_menu_id_tbl := NULL;
156 x_status_type_tbl := NULL;
157 x_status_code_tbl := NULL;
158 x_menu_name_tbl := NULL;
159 x_status_level := NULL;
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161 END IF;
162 END IF;
163
164 CLOSE csr_get_role_level_info;
165
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 x_error_message_code := SQLCODE;
171
172 END get_role_status_menus;
173
174 end pa_role_status_menu_utils ;
[Home] [Help]