[Home] [Help]
PACKAGE BODY: APPS.ZPB_SECURITY_UTIL_PVT
Source
1 PACKAGE BODY ZPB_SECURITY_UTIL_PVT AS
2 /* $Header: ZPBVSCUB.pls 120.4 2007/12/04 14:40:22 mbhat noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZPB_SECURITY_UTIL_PVT';
6
7 -------------------------------------------------------------------------------
8
9 /* This procedure validates that the given user is an EPB user and whether read access has been assigned */
10
11 PROCEDURE validate_user(p_user_id IN NUMBER,
12 p_business_area_id IN NUMBER,
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE,
16 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
17 x_user_account_state OUT NOCOPY varchar2,
18 x_return_status OUT NOCOPY varchar2,
19 x_msg_count OUT NOCOPY number,
20 x_msg_data OUT NOCOPY varchar2)
21
22
23 IS
24
25 l_api_name CONSTANT VARCHAR2(32) := 'validate_user';
26 l_api_version CONSTANT NUMBER := 1.0;
27
28 l_invalid_user CONSTANT VARCHAR2(12) := 'INVALID_USER';
29 l_has_read_acc CONSTANT VARCHAR2(12) := 'HAS_READ_ACC';
30 l_no_read_acc CONSTANT VARCHAR2(11) := 'NO_READ_ACC';
31
32 l_schema VARCHAR2(64);
33 l_shared_aw VARCHAR2(128);
34 l_code_aw VARCHAR2(128);
35 l_status VARCHAR2(1);
36 l_user_id VARCHAR2(16);
37 l_count NUMBER;
38 l_data VARCHAR2(4000);
39 l_secAdminId NUMBER;
40 l_readAcc VARCHAR2(3);
41
42
43 BEGIN
44
45 -- Standard Start of API savepoint
46 SAVEPOINT zpb_excp_pvt_populate_results;
47 -- Standard call to check for call compatibility.
48 IF NOT FND_API.Compatible_API_Call( l_api_version,
49 p_api_version,
50 l_api_name,
51 G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55 -- Initialize message list if p_init_msg_list is set to TRUE.
56 IF FND_API.to_Boolean(p_init_msg_list) THEN
57 FND_MSG_PUB.initialize;
58 END IF;
59 -- Initialize API return status to success
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 -- API body
63
64 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Validating user ' || p_user_id || '...');
65
66 l_user_id := to_char(p_user_id);
67
68 l_readAcc := ZPB_AW.INTERP('shw sc.has.read.acc(''' || p_user_id || ''')');
69 if l_readAcc = 'YES' then
70 x_user_account_state := l_has_read_acc;
71 else
72 x_user_account_state := l_no_read_acc;
73 end if;
74
75 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'User validation complete.');
76
77 -- End of API body.
78
79 -- Standard check of p_commit.
80 IF FND_API.To_Boolean( p_commit ) THEN
81 COMMIT WORK;
82 END IF;
83 -- Standard call to get message count and if count is 1, get message info.
84 FND_MSG_PUB.Count_And_Get(
85 p_count => x_msg_count, p_data => x_msg_data );
86
87 EXCEPTION
88 WHEN FND_API.G_EXC_ERROR THEN
89 ROLLBACK TO zpb_excp_pvt_populate_results;
90 x_return_status := FND_API.G_RET_STS_ERROR;
91 FND_MSG_PUB.Count_And_Get(
92 p_count => x_msg_count,
93 p_data => x_msg_data
94 );
95 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
96 ROLLBACK TO zpb_excp_pvt_populate_results;
97 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
98 FND_MSG_PUB.Count_And_Get(
99 p_count => x_msg_count,
100 p_data => x_msg_data
101 );
102 WHEN OTHERS THEN
103 ROLLBACK TO zpb_excp_pvt_populate_results;
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
106 FND_MSG_PUB.Add_Exc_Msg(
107 G_PKG_NAME,
108 l_api_name
109 );
110 END IF;
111 FND_MSG_PUB.Count_And_Get(
112 p_count => x_msg_count,
113 p_data => x_msg_data
114 );
115
116
117 END validate_user;
118
119 PROCEDURE has_read_access (p_user_id IN NUMBER,
120 p_business_area_id IN NUMBER,
121 p_api_version IN NUMBER,
122 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
123 p_commit IN VARCHAR2 := FND_API.G_FALSE,
124 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
125 x_user_read_access OUT NOCOPY varchar2,
126 x_return_status OUT NOCOPY varchar2,
127 x_msg_count OUT NOCOPY number,
128 x_msg_data OUT NOCOPY varchar2)
129
130 IS
131
132 l_api_name CONSTANT VARCHAR2(32) := 'validate_user';
133 l_api_version CONSTANT NUMBER := 1.0;
134
135 l_has_read_acc CONSTANT VARCHAR2(12) := 'HAS_READ_ACC';
136 l_no_read_acc CONSTANT VARCHAR2(11) := 'NO_READ_ACC';
137
138 l_user_name VARCHAR2(128);
139 l_read_acc NUMBER;
140 l_data VARCHAR2(4000);
141 l_secAdminId NUMBER;
142
143
144 BEGIN
145
146 -- Standard Start of API savepoint
147 SAVEPOINT zpb_excp_pvt_populate_results;
148 -- Standard call to check for call compatibility.
149 IF NOT FND_API.Compatible_API_Call( l_api_version,
150 p_api_version,
151 l_api_name,
152 G_PKG_NAME)
153 THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157 -- Initialize message list if p_init_msg_list is set to TRUE.
158 IF FND_API.to_Boolean(p_init_msg_list) THEN
159 FND_MSG_PUB.initialize;
160 END IF;
161
162 -- Initialize API return status to success
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 -- API body
166
167 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Check for read access assigned to user ' || p_user_id || '...');
168
169 select max(has_read_access) into l_read_acc
170 from zpb_account_states
171 where user_id = p_user_id
172 and business_area_id = p_business_area_id;
173
174 if l_read_acc = 1 then
175 x_user_read_access := l_has_read_acc;
176 else
177 x_user_read_access := l_no_read_acc;
178 end if;
179
180 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Read access check complete.');
181
182 -- End of API body.
183
184 -- Standard check of p_commit.
185 IF FND_API.To_Boolean( p_commit ) THEN
186 COMMIT WORK;
187 END IF;
188 -- Standard call to get message count and if count is 1, get message info.
189 FND_MSG_PUB.Count_And_Get(
190 p_count => x_msg_count, p_data => x_msg_data );
191
192 EXCEPTION
193 WHEN FND_API.G_EXC_ERROR THEN
194 ROLLBACK TO zpb_excp_pvt_populate_results;
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 FND_MSG_PUB.Count_And_Get(
197 p_count => x_msg_count,
198 p_data => x_msg_data
199 );
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 ROLLBACK TO zpb_excp_pvt_populate_results;
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 FND_MSG_PUB.Count_And_Get(
204 p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207 WHEN OTHERS THEN
208 ROLLBACK TO zpb_excp_pvt_populate_results;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
211 FND_MSG_PUB.Add_Exc_Msg(
212 G_PKG_NAME,
213 l_api_name
214 );
215 END IF;
216 FND_MSG_PUB.Count_And_Get(
217 p_count => x_msg_count,
218 p_data => x_msg_data
219 );
220
221
222 END has_read_access;
223
224
225 /* This procedure sets the read access state in zpb_account_states */
226
227 PROCEDURE set_user_access_state
228 (p_user_id IN NUMBER,
229 p_read_access_state IN varchar2,
230 p_api_version IN NUMBER,
231 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
232 p_commit IN VARCHAR2 := FND_API.G_FALSE,
233 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
234 x_read_access_state OUT NOCOPY varchar2,
235 x_return_status OUT NOCOPY varchar2,
236 x_msg_count OUT NOCOPY number,
237 x_msg_data OUT NOCOPY varchar2)
238
239
240 IS
241
242 l_api_name CONSTANT VARCHAR2(32) := 'set_user_access_state';
243 l_api_version CONSTANT NUMBER := 1.0;
244
245 l_invalid_user CONSTANT VARCHAR2(12) := 'INVALID_USER';
246 l_has_read_acc CONSTANT VARCHAR2(12) := 'HAS_READ_ACC';
247 l_no_read_acc CONSTANT VARCHAR2(11) := 'NO_READ_ACC';
248
249 l_schema VARCHAR2(64);
250 l_shared_aw VARCHAR2(128);
251 l_code_aw VARCHAR2(128);
252 l_status VARCHAR2(1);
253 l_user_id VARCHAR2(16);
254 l_count NUMBER;
255 l_data VARCHAR2(4000);
256 l_secAdminId NUMBER;
257 l_readAcc NUMBER(1);
258
259
260 BEGIN
261
262 -- Standard Start of API savepoint
263 SAVEPOINT zpb_excp_pvt_populate_results;
264 -- Standard call to check for call compatibility.
265 IF NOT FND_API.Compatible_API_Call( l_api_version,
266 p_api_version,
267 l_api_name,
268 G_PKG_NAME)
269 THEN
270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
271 END IF;
272 -- Initialize message list if p_init_msg_list is set to TRUE.
273 IF FND_API.to_Boolean(p_init_msg_list) THEN
274 FND_MSG_PUB.initialize;
275 END IF;
276 -- Initialize API return status to success
277 x_return_status := FND_API.G_RET_STS_SUCCESS;
278
279 -- API body
280
281 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'Setting read access state for user ' || p_user_id || ' ...');
282
283 l_user_id := to_char(p_user_id);
284
285 if p_read_access_state = l_has_read_acc
286 then
287 l_readAcc := 1;
288 else
289 l_readAcc := 0;
290 end if;
291
292 update ZPB_ACCOUNT_STATES
293 set has_read_access = l_readAcc
294 where user_id = p_user_id;
295
296 ZPB_LOG.WRITE_STATEMENT(G_PKG_NAME || '.' || l_api_name, 'User access state has been set to ' || l_readAcc || '.');
297
298 -- End of API body.
299
300 -- Standard check of p_commit.
301 IF FND_API.To_Boolean( p_commit ) THEN
302 COMMIT WORK;
303 END IF;
304 -- Standard call to get message count and if count is 1, get message info.
305 FND_MSG_PUB.Count_And_Get(
306 p_count => x_msg_count, p_data => x_msg_data );
307
308 EXCEPTION
309 WHEN FND_API.G_EXC_ERROR THEN
310 ROLLBACK TO zpb_excp_pvt_populate_results;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 FND_MSG_PUB.Count_And_Get(
313 p_count => x_msg_count,
314 p_data => x_msg_data
315 );
316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
317 ROLLBACK TO zpb_excp_pvt_populate_results;
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
319 FND_MSG_PUB.Count_And_Get(
320 p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323 WHEN OTHERS THEN
324 ROLLBACK TO zpb_excp_pvt_populate_results;
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
327 FND_MSG_PUB.Add_Exc_Msg(
328 G_PKG_NAME,
329 l_api_name
330 );
331 END IF;
332 FND_MSG_PUB.Count_And_Get(
333 p_count => x_msg_count,
334 p_data => x_msg_data
335 );
336
337
338 END set_user_access_state;
339
340 END ZPB_SECURITY_UTIL_PVT;
341