1 PACKAGE BODY cn_user_access_pvt AS
2 --$Header: cnvurasb.pls 115.10 2003/12/30 22:36:42 sbadami noship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CN_USER_ACCESS_PVT';
5
6 -- Start of comments
7 -- API name : Create_User_Access
8 -- Pre-reqs : None.
9 -- IN : standard params
10 -- p_rec of table rec type
11 -- OUT : standard params
12 -- x_user_access_id
13 -- Version : 1.0
14 --
15 -- End of comments
16
17 PROCEDURE Create_User_Access
18 (p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
20 p_commit IN VARCHAR2 := FND_API.G_FALSE,
21 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
22 p_rec IN user_access_rec_type,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26 x_user_access_id OUT NOCOPY NUMBER) IS
27
28 l_api_name CONSTANT VARCHAR2(30) := 'Create_User_Access';
29 l_api_version CONSTANT NUMBER := 1.0;
30
31 l_newrec user_access_rec_type := p_rec;
32 l_count NUMBER;
33 BEGIN
34 -- Standard Start of API savepoint
35 SAVEPOINT Create_User_Access;
36
37 -- Standard call to check for call compatibility.
38 IF NOT FND_API.Compatible_API_Call
39 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
40 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
41 END IF;
42
43 -- Initialize message list if p_init_msg_list is set to TRUE.
44 IF FND_API.to_Boolean( p_init_msg_list ) THEN
45 FND_MSG_PUB.initialize;
46 END IF;
47
48 -- Initialize API return status to success
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 -- make sure the access level for the current comp group and org
52 -- isn't already assigned
53 select count(user_access_id) into l_count
54 from cn_user_accesses
55 where user_id = l_newrec.user_id
56 and comp_group_id = l_newrec.comp_group_id
57 and org_code = l_newrec.org_code;
58 IF l_count > 0 THEN
59 FND_MESSAGE.SET_NAME ('CN' , 'CN_ACCESS_DUP_ERR');
60 FND_MSG_PUB.Add;
61 RAISE FND_API.G_EXC_ERROR;
62 END IF;
63
64 cn_user_access_pkg.insert_row(l_newrec);
65
66 -- Standard check of p_commit.
67 IF FND_API.To_Boolean( p_commit ) THEN
68 COMMIT WORK;
69 END IF;
70
71 FND_MSG_PUB.Count_And_Get
72 (p_count => x_msg_count,
73 p_data => x_msg_data,
74 p_encoded => FND_API.G_FALSE);
75
76 EXCEPTION
77 WHEN FND_API.G_EXC_ERROR THEN
78 ROLLBACK TO Create_User_Access;
79 x_return_status := FND_API.G_RET_STS_ERROR;
80 FND_MSG_PUB.Count_And_Get
81 (p_count => x_msg_count,
82 p_data => x_msg_data,
83 p_encoded => FND_API.G_FALSE);
84 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
85 ROLLBACK TO Create_User_Access;
86 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
87 FND_MSG_PUB.Count_And_Get
88 (p_count => x_msg_count,
89 p_data => x_msg_data,
90 p_encoded => FND_API.G_FALSE);
91 WHEN OTHERS THEN
92 ROLLBACK TO Create_User_Access;
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
95 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
96 END IF;
97 FND_MSG_PUB.Count_And_Get
98 (p_count => x_msg_count,
99 p_data => x_msg_data,
100 p_encoded => FND_API.G_FALSE);
101 END Create_User_Access;
102
103 -- Start of comments
104 -- API name : Update_User_Access
105 -- Pre-reqs : None.
106 -- IN : standard params
107 -- p_rec of table rec type
108 -- OUT : standard params
109 -- Version : 1.0
110 --
111 -- End of comments
112
113 PROCEDURE Update_User_Access
114 (p_api_version IN NUMBER,
115 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
116 p_commit IN VARCHAR2 := FND_API.G_FALSE,
117 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
118 p_rec IN user_access_rec_type,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2) IS
122
123 l_api_name CONSTANT VARCHAR2(30) := 'Update_User_Access';
124 l_api_version CONSTANT NUMBER := 1.0;
125 l_newrec user_access_rec_type := p_rec;
126 l_count NUMBER;
127 BEGIN
128 -- Standard Start of API savepoint
129 SAVEPOINT Update_User_Access;
130
131 -- Standard call to check for call compatibility.
132 IF NOT FND_API.Compatible_API_Call
133 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137 -- Initialize message list if p_init_msg_list is set to TRUE.
138 IF FND_API.to_Boolean( p_init_msg_list ) THEN
139 FND_MSG_PUB.initialize;
140 END IF;
141
142 -- Initialize API return status to success
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 -- make sure record to be updated exists.
146 select count(*) into l_count
147 from cn_user_accesses
148 where user_access_id = l_newrec.user_access_id;
149
150 if l_count = 0 then
151 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_UPDATE_REC');
152 FND_MSG_PUB.ADD;
153 RAISE FND_API.G_EXC_ERROR;
154 end if;
155
156 -- make sure the access level for the current comp group and org
157 -- isn't already assigned
158 select count(user_access_id) into l_count
159 from cn_user_accesses
160 where user_id = l_newrec.user_id
161 and comp_group_id = l_newrec.comp_group_id
162 and org_code = l_newrec.org_code
163 and user_access_id <> l_newrec.user_access_id;
164 IF l_count > 0 THEN
165 FND_MESSAGE.SET_NAME ('CN' , 'CN_ACCESS_DUP_ERR');
166 FND_MSG_PUB.Add;
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169
170 -- make sure the object version number hasn't changed in the meantime
171 cn_user_access_pkg.lock_row(l_newrec.user_access_id,
172 l_newrec.object_version_number);
173
174 cn_user_access_pkg.update_row(l_newrec);
175
176 -- Standard check of p_commit.
177 IF FND_API.To_Boolean( p_commit ) THEN
178 COMMIT WORK;
179 END IF;
180
181 FND_MSG_PUB.Count_And_Get
182 (p_count => x_msg_count,
183 p_data => x_msg_data,
184 p_encoded => FND_API.G_FALSE);
185 EXCEPTION
186 WHEN FND_API.G_EXC_ERROR THEN
187 ROLLBACK TO Update_User_Access;
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 FND_MSG_PUB.Count_And_Get
190 (p_count => x_msg_count,
191 p_data => x_msg_data,
192 p_encoded => FND_API.G_FALSE);
193 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
194 ROLLBACK TO Update_User_Access;
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 FND_MSG_PUB.Count_And_Get
197 (p_count => x_msg_count,
198 p_data => x_msg_data,
199 p_encoded => FND_API.G_FALSE);
200 WHEN OTHERS THEN
201 ROLLBACK TO Update_User_Access;
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
204 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
205 END IF;
206 FND_MSG_PUB.Count_And_Get
207 (p_count => x_msg_count,
208 p_data => x_msg_data,
209 p_encoded => FND_API.G_FALSE);
210 END Update_User_Access;
211
212 -- Start of comments
213 -- API name : Delete_User_Access
214 -- Pre-reqs : None.
215 -- IN : standard params
216 -- p_user_access_id
217 -- OUT : standard params
218 -- Version : 1.0
219 --
220 -- End of comments
221
222 PROCEDURE Delete_User_Access
223 (p_api_version IN NUMBER,
224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
225 p_commit IN VARCHAR2 := FND_API.G_FALSE,
226 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
227 p_user_access_id IN NUMBER,
228 p_object_version_number IN NUMBER,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2) IS
232
233 l_api_name CONSTANT VARCHAR2(30) := 'Delete_User_Access';
234 l_api_version CONSTANT NUMBER := 1.0;
235 l_count NUMBER;
236
237 BEGIN
238 -- Standard Start of API savepoint
239 SAVEPOINT Delete_User_Access;
240
241 -- Standard call to check for call compatibility.
242 IF NOT FND_API.Compatible_API_Call
243 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247 -- Initialize message list if p_init_msg_list is set to TRUE.
248 IF FND_API.to_Boolean( p_init_msg_list ) THEN
249 FND_MSG_PUB.initialize;
250 END IF;
251
252 -- Initialize API return status to success
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 -- make sure record to be deleted exists.
256 select count(*) into l_count
257 from cn_user_accesses
258 where user_access_id = p_user_access_id;
259
260 if l_count = 0 then
261 FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_DEL_REC');
262 FND_MSG_PUB.ADD;
263 RAISE FND_API.G_EXC_ERROR;
264 end if;
265
266 -- make sure the object version number hasn't changed in the meantime
267 cn_user_access_pkg.lock_row(p_user_access_id,
268 p_object_version_number);
269
270 cn_user_access_pkg.delete_row(p_user_access_id);
271
272 -- Standard check of p_commit.
273 IF FND_API.To_Boolean( p_commit ) THEN
274 COMMIT WORK;
275 END IF;
276
277 FND_MSG_PUB.Count_And_Get
278 (p_count => x_msg_count,
279 p_data => x_msg_data,
280 p_encoded => FND_API.G_FALSE);
281 EXCEPTION
282 WHEN FND_API.G_EXC_ERROR THEN
283 ROLLBACK TO Delete_User_Access;
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 FND_MSG_PUB.Count_And_Get
286 (p_count => x_msg_count,
287 p_data => x_msg_data,
288 p_encoded => FND_API.G_FALSE);
289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
290 ROLLBACK TO Delete_User_Access;
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
292 FND_MSG_PUB.Count_And_Get
293 (p_count => x_msg_count,
294 p_data => x_msg_data,
295 p_encoded => FND_API.G_FALSE);
296 WHEN OTHERS THEN
297 ROLLBACK TO Delete_Job_Role;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
300 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
301 END IF;
302 FND_MSG_PUB.Count_And_Get
303 (p_count => x_msg_count,
304 p_data => x_msg_data,
305 p_encoded => FND_API.G_FALSE);
306 END Delete_User_Access;
307
308 -- Start of comments
309 -- API name : Get_Accesses - Private
310 -- Pre-reqs : None.
311 -- IN : range params
312 -- OUT : x_result_tbl
313 -- Version : Current version 1.0
314 --
315 -- End of comments
316
317 PROCEDURE Get_Accesses
318 (p_range_low IN NUMBER,
319 p_range_high IN NUMBER,
320 x_total_rows OUT NOCOPY NUMBER,
321 x_result_tbl OUT NOCOPY user_access_sum_tbl_type) IS
322
323 rec user_access_sum_rec_type;
324 l_index NUMBER := 0;
325
326 l_org_id VARCHAR2(30);
327
328 cursor get_data (c_fm_resp_key IN VARCHAR2) is
329 SELECT DISTINCT f.user_id, p.full_name, f.user_name
330 FROM fnd_user_resp_groups fr, fnd_user f,
331 fnd_responsibility_vl r, per_all_people_f p
332 WHERE f.user_id = fr.user_id
333 AND fr.responsibility_id = r.responsibility_id
334 AND r.application_id = 283
335 AND r.responsibility_key = c_fm_resp_key
336 AND f.employee_id = p.person_id(+)
337 AND trunc(sysdate) BETWEEN p.effective_start_date(+) AND
338 nvl(p.effective_end_date(+), trunc(sysdate))
339 ORDER BY 3,2;
340
341 cursor get_resp IS
342 SELECT responsibility_id, responsibility_key
343 FROM fnd_responsibility
344 WHERE application_id = 283
345 AND sysdate BETWEEN start_date AND nvl(end_date, sysdate)
346 ;
347
348 l_fm_resp_key VARCHAR2(30) := NULL;
349
350 BEGIN
351 x_total_rows := 0;
352
353 l_org_id := fnd_profile.value('ORG_ID');
354
355 FOR resp_rec IN get_resp LOOP
356 IF fnd_profile.value_specific(name => 'CN_SFP_RESP_GROUP', responsibility_id => resp_rec.responsibility_id) = 'CN_SF_FINANCE_MGR' THEN
357 IF NVL(fnd_profile.value_specific(name => 'ORG_ID', responsibility_id => resp_rec.responsibility_id),-9999) = nvl(l_org_id,-9999) THEN
358 l_fm_resp_key := resp_rec.responsibility_key;
359 END IF;
360 END IF;
361 END LOOP;
362
363 /* Add this check
364 IF l_fm_resp_key IS NULL THEN
365 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
366 FND_MESSAGE.SET_NAME('CN', 'CN_QM_NO_FM');
367 FND_MSG_PUB.Add;
368 END IF;
369 RAISE FND_API.G_EXC_error;
370 END IF;
371 */
372
373
374
375 open get_data(l_fm_resp_key);
376 loop
377 fetch get_data into rec;
378 exit when get_data%notfound;
379
380 x_total_rows := x_total_rows + 1;
381 if (x_total_rows between p_range_low and p_range_high) OR
382 (p_range_high = -1) then
383 l_index := l_index + 1;
384 x_result_tbl(l_index) := rec;
385 end if;
386 end loop;
387 close get_data;
388
389 END Get_Accesses;
390
391 -- Start of comments
392 -- API name : Get_Access_Details - Private
393 -- Pre-reqs : None.
394 -- IN : p_user_id
395 -- OUT : x_result_tbl
396 -- Version : Current version 1.0
397 --
398 -- End of comments
399
400 PROCEDURE Get_Access_Details
401 (p_user_id IN NUMBER,
402 x_result_tbl OUT NOCOPY user_access_tbl_type) IS
403
404 l_count NUMBER := 1;
405
406 cursor get_data is
407 SELECT user_access_id, user_id, comp_group_id, org_code, access_code,
408 object_version_number
409 FROM cn_user_accesses
410 WHERE user_id = p_user_id
411 ORDER BY 3,5;
412
413 BEGIN
414 for c in get_data loop
415 x_result_tbl(l_count).user_access_id := c.user_access_id;
416 x_result_tbl(l_count).user_id := c.user_id;
417 x_result_tbl(l_count).comp_group_id := c.comp_group_id;
418 x_result_tbl(l_count).org_code := c.org_code;
419 x_result_tbl(l_count).access_code := c.access_code;
420 x_result_tbl(l_count).object_version_number := c.object_version_number;
421 l_count := l_count + 1;
422 end loop;
423 END Get_Access_Details;
424
425 END CN_USER_ACCESS_PVT;
[Home] [Help]