1 PACKAGE BODY IGW_PROP_USER_ROLES_PVT as
2 /* $Header: igwvpurb.pls 115.8 2002/11/18 19:20:06 ashkumar ship $*/
3 PROCEDURE create_prop_user_role (
4 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
6 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
7 ,x_rowid OUT NOCOPY VARCHAR2
8 ,p_proposal_id IN NUMBER
9 ,p_proposal_number IN VARCHAR2
10 ,p_user_id IN NUMBER
11 ,p_user_name IN VARCHAR2
12 ,p_role_id IN NUMBER
13 ,p_role_name IN VARCHAR2
14 ,p_logged_user_id IN NUMBER
15 ,x_return_status OUT NOCOPY VARCHAR2
16 ,x_msg_count OUT NOCOPY NUMBER
17 ,x_msg_data OUT NOCOPY VARCHAR2)
18
19 is
20
21 l_proposal_id NUMBER := p_proposal_id;
22 l_user_id NUMBER := p_user_id;
23 l_role_id NUMBER := p_role_id;
24
25 l_return_status VARCHAR2(1);
26 l_error_msg_code VARCHAR2(250);
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(250);
29 l_data VARCHAR2(250);
30 l_msg_index_out NUMBER;
31
32
33
34 BEGIN
35 -- create savepoint if p_commit is true
36 IF p_commit = FND_API.G_TRUE THEN
37 SAVEPOINT create_user_role;
38 END IF;
39
40 -- initialize message list if p_init_msg_list is set to true
41 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
42 fnd_msg_pub.initialize;
43 end if;
44
45 -- initialize return status to success
46 x_return_status := fnd_api.g_ret_sts_success;
47
48 ------------------------------------- value_id conversion ------------------------------------
49 /*
50 -- if proposal_id is null, then get it
51
52 IF (p_proposal_id is null) THEN
53 IGW_UTILS.GET_PROPOSAL_ID
54 (p_context_field => 'PROPOSAL_ID'
55 ,p_proposal_number => p_proposal_number
56 ,x_proposal_id => l_proposal_id
57 ,x_return_status => x_return_status);
58 END IF;
59
60 -- if user_id is null, then get it
61
62 IF (p_user_id is null) THEN
63 IGW_UTILS.GET_USER_ID
64 (x_user_id => l_user_id
65 ,p_user_name => p_user_name
66 ,x_return_status => x_return_status);
67 END IF;
68 */
69 -- get role_id
70 IF (p_role_name is null) THEN
71 l_role_id := null;
72 ELSE
73 -- IF (p_role_id is null) THEN
74 GET_ROLE_ID (p_role_name => p_role_name
75 ,x_role_id => l_role_id
76 ,x_return_status => x_return_status);
77 -- END IF;
78 END IF;
79
80 check_errors;
81
82 -------------------------------------------- validations -----------------------------------------------------
83 -- validate that the user who has logged on has the rights to modify user roles
84
85 VALIDATE_LOGGED_USER_RIGHTS
86 (p_proposal_id => l_proposal_id
87 ,p_logged_user_id => p_logged_user_id
88 ,x_return_status => x_return_status);
89
90 check_errors;
91
92 -- validate that role is not a seeded role
93
94 CHECK_IF_SEEDED_ROLE
95 (p_role_id => l_role_id
96 ,x_return_status => x_return_status);
97
98
99 check_errors;
100
101 -- call table handler
102 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
103
104 igw_prop_user_roles_tbh.insert_row(
105 x_rowid => x_rowid,
106 p_proposal_id => l_proposal_id,
107 p_user_id => l_user_id,
108 p_role_id => l_role_id,
109 p_mode => 'R',
110 x_return_status => x_return_status);
111
112 end if;
113
114 check_errors;
115
116 -- standard check of p_commit
117 if fnd_api.to_boolean(p_commit) then
118 commit work;
119 end if;
120
121
122 -- standard call to get message count and if count is 1, get message info
123 fnd_msg_pub.count_and_get(p_count => x_msg_count,
124 p_data => x_msg_data);
125
126
127 EXCEPTION
128 WHEN FND_API.G_EXC_ERROR THEN
129 IF p_commit = FND_API.G_TRUE THEN
130 ROLLBACK TO create_user_role;
131 END IF;
132
133 x_return_status := FND_API.G_RET_STS_ERROR;
134
135 fnd_msg_pub.count_and_get(p_count => x_msg_count,
136 p_data => x_msg_data);
137
138 WHEN OTHERS THEN
139 IF p_commit = FND_API.G_TRUE THEN
140 ROLLBACK TO create_user_role;
141 END IF;
142
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144
145 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PVT',
146 p_procedure_name => 'CREATE_USER_ROLE',
147 p_error_text => SUBSTRB(SQLERRM,1,240));
148
149 fnd_msg_pub.count_and_get(p_count => x_msg_count,
150 p_data => x_msg_data);
151
152
153 END create_prop_user_role;
154 --------------------------------------------------------------------------------------------------------------
155
156 Procedure update_prop_user_role (
157 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
158 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
159 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
160 ,x_rowid IN VARCHAR2
161 ,p_proposal_id IN NUMBER
162 ,p_proposal_number IN VARCHAR2
163 ,p_user_id IN NUMBER
164 ,p_user_name IN VARCHAR2
165 ,p_role_id IN NUMBER
166 ,p_role_name IN VARCHAR2
167 ,p_logged_user_id IN NUMBER
168 ,p_record_version_number IN NUMBER
169 ,x_return_status OUT NOCOPY VARCHAR2
170 ,x_msg_count OUT NOCOPY NUMBER
171 ,x_msg_data OUT NOCOPY VARCHAR2) is
172
173
174 l_proposal_id NUMBER;
175 l_proposal_id2 NUMBER := p_proposal_id;
176 l_user_id NUMBER;
177 l_user_id2 NUMBER := p_user_id;
178 l_role_id NUMBER;
179 l_role_id2 NUMBER := p_role_id;
180
181
182 l_return_status VARCHAR2(1);
183 l_error_msg_code VARCHAR2(250);
184 l_msg_count NUMBER;
185 l_data VARCHAR2(250);
186 l_performing_org_id NUMBER;
187 l_msg_data VARCHAR2(250);
188 l_msg_index_out NUMBER;
189
190 BEGIN
191 -- create savepoint if p_commit is true
192 IF p_commit = FND_API.G_TRUE THEN
193 SAVEPOINT update_user_role;
194 END IF;
195
196 -- initialize message list if p_init_msg_list is true
197 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
198 fnd_msg_pub.initialize;
199 end if;
200
201 -- initialize return_status to success
202 x_return_status := fnd_api.g_ret_sts_success;
203
204 -- get proposal_id, user_id, role_id from igw_prop_user_roles using x_rowid and record_version_number
205 -- and also check locking. The columns fetched are the old data, i.e., the data that is being overwritten
206 CHECK_LOCK_GET_PK
207 (x_rowid => x_rowid
208 ,p_record_version_number => p_record_version_number
209 ,x_proposal_id => l_proposal_id
210 ,x_user_id => l_user_id
211 ,x_role_id => l_role_id
212 ,x_return_status => x_return_status);
213
214 check_errors;
215
216 ------------------------------------- value_id conversion (for new data) ------------------------------------
217 -- first validate that the user who has logged on has the rights to modify user roles
218
219 VALIDATE_LOGGED_USER_RIGHTS
220 (p_proposal_id => l_proposal_id
221 ,p_logged_user_id => p_logged_user_id
222 ,x_return_status => x_return_status);
223
224 check_errors;
225 /*
226 -- if proposal_id is null, then get it
227
228 IF (p_proposal_id is null) THEN
229 IGW_UTILS.GET_PROPOSAL_ID
230 (p_context_field => 'PROPOSAL_ID'
231 ,p_proposal_number => p_proposal_number
232 ,x_proposal_id => l_proposal_id2
233 ,x_return_status => x_return_status);
234 END IF;
235
236 -- if user_id is null, then get it
237
238 IF (p_user_id is null) THEN
239 IGW_UTILS.GET_USER_ID
240 (p_user_name => p_user_name
241 ,x_user_id => l_user_id2
242 ,x_return_status => x_return_status);
243 END IF;
244 */
245
246 -- get role_id
247 IF (p_role_name is null) THEN
248 l_role_id := null;
249 ELSE
250 -- IF (p_role_id is null) THEN
251 GET_ROLE_ID (p_role_name => p_role_name
252 ,x_role_id => l_role_id2
253 ,x_return_status => x_return_status);
254 -- END IF;
255 END IF;
256
257 check_errors;
258
259
260 -------------------------------------------- validations -----------------------------------------------------
261 -- now we have both old and new values. Do validations on the old values first, then do on the new
262 -- values if diffent from the new values.
263
264
265 if (l_role_id <> l_role_id2) then
266
267 -- validate that role is not a seeded role
268
269 CHECK_IF_SEEDED_ROLE
270 (p_role_id => l_role_id
271 ,x_return_status => x_return_status);
272
273
274
275 CHECK_IF_SEEDED_ROLE
276 (p_role_id => l_role_id2
277 ,x_return_status => x_return_status);
278
279
280
281 check_errors;
282
283 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
284 igw_prop_user_roles_tbh.update_row (
285 x_rowid => x_rowid,
286 p_proposal_id => l_proposal_id2,
287 p_user_id => l_user_id2,
288 p_role_id => l_role_id2,
289 p_mode => 'R',
290 p_record_version_number => p_record_version_number,
291 x_return_status => x_return_status);
292
293 end if;
294 end if;
295 check_errors;
296
297 -- standard check of p_commit
298 if fnd_api.to_boolean(p_commit) then
299 commit work;
300 end if;
301
302
303 -- standard call to get message count and if count is 1, get message info
304 fnd_msg_pub.count_and_get(p_count => x_msg_count,
305 p_data => x_msg_data);
306
307
308 EXCEPTION
309 WHEN FND_API.G_EXC_ERROR THEN
310 IF p_commit = FND_API.G_TRUE THEN
311 ROLLBACK TO update_user_role;
312 END IF;
313
314 x_return_status := FND_API.G_RET_STS_ERROR;
315
316 fnd_msg_pub.count_and_get(p_count => x_msg_count,
317 p_data => x_msg_data);
318
319
320 WHEN OTHERS THEN
321 IF p_commit = FND_API.G_TRUE THEN
322 ROLLBACK TO update_user_role;
323 END IF;
324
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326
327 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PVT',
328 p_procedure_name => 'UPDATE_USER_ROLE',
329 p_error_text => SUBSTRB(SQLERRM,1,240));
330
331 fnd_msg_pub.count_and_get(p_count => x_msg_count,
332 p_data => x_msg_data);
333
334
335 END update_prop_user_role;
336 --------------------------------------------------------------------------------------------------------
337
338 Procedure delete_prop_user_role (
339 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
340 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
341 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
342 ,x_rowid IN VARCHAR2
343 ,p_logged_user_id IN NUMBER
344 ,p_record_version_number IN NUMBER
345 ,x_return_status OUT NOCOPY VARCHAR2
346 ,x_msg_count OUT NOCOPY NUMBER
347 ,x_msg_data OUT NOCOPY VARCHAR2) is
348
349 l_proposal_id NUMBER;
350 l_user_id NUMBER;
351 l_role_id NUMBER;
352
353
354
355 l_return_status VARCHAR2(1);
356 l_error_msg_code VARCHAR2(250);
357 l_msg_count NUMBER;
358 l_data VARCHAR2(250);
359 l_performing_org_id NUMBER;
360 l_msg_data VARCHAR2(250);
361 l_msg_index_out NUMBER;
362
363 BEGIN
364 -- create savepoint
365 IF p_commit = FND_API.G_TRUE THEN
366 SAVEPOINT delete_user_role;
367 END IF;
368
369 -- initialize message list if p_init_msg_list is set to true
370 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
371 fnd_msg_pub.initialize;
372 end if;
373
374 -- initialize return_status to sucess
375 x_return_status := fnd_api.g_ret_sts_success;
376
377
378 -- get proposal_id, user_id, role_id from igw_prop_user_roles using x_rowid and record_version_number
379 -- and also check locking
380 CHECK_LOCK_GET_PK
381 (x_rowid => x_rowid
382 ,p_record_version_number => p_record_version_number
383 ,x_proposal_id => l_proposal_id
384 ,x_user_id => l_user_id
385 ,x_role_id => l_role_id
386 ,x_return_status => x_return_status);
387
388 check_errors;
389
390 -------------------------------------------- validations -----------------------------------------------------
391
392 -- first validate that the user who has logged on has the rights to modify user roles
393
394 VALIDATE_LOGGED_USER_RIGHTS
395 (p_proposal_id => l_proposal_id
396 ,p_logged_user_id => p_logged_user_id
397 ,x_return_status => x_return_status);
398
399 check_errors;
400 -- validate that role is not a seeded role
401
402 CHECK_IF_SEEDED_ROLE
403 (p_role_id => l_role_id
404 ,x_return_status => x_return_status);
405
406 check_errors;
407
408
409 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
410
411 igw_prop_user_roles_tbh.delete_row(
412 x_rowid => x_rowid,
413 p_record_version_number => p_record_version_number,
414 x_return_status => x_return_status);
415
416 end if;
417
418
419 check_errors;
420
421 -- standard check of p_commit
422 if fnd_api.to_boolean(p_commit) then
423 commit work;
424 end if;
425
426
427 -- standard call to get message count and if count is 1, get message info
428 fnd_msg_pub.count_and_get(p_count => x_msg_count,
429 p_data => x_msg_data);
430
431 EXCEPTION
432 WHEN FND_API.G_EXC_ERROR THEN
433 IF p_commit = FND_API.G_TRUE THEN
434 ROLLBACK TO delete_user_role;
435 END IF;
436
437 x_return_status := FND_API.G_RET_STS_ERROR;
438
439 fnd_msg_pub.count_and_get(p_count => x_msg_count,
440 p_data => x_msg_data);
441
442
443 WHEN OTHERS THEN
444 IF p_commit = FND_API.G_TRUE THEN
445 ROLLBACK TO delete_user_role;
446 END IF;
447
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449
450 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PVT',
451 p_procedure_name => 'DELETE_USER_ROLE',
452 p_error_text => SUBSTRB(SQLERRM,1,240));
453
454 fnd_msg_pub.count_and_get(p_count => x_msg_count,
455 p_data => x_msg_data);
456
457 END delete_prop_user_role;
458 -----------------------------------------------------------------------------------
459
460 PROCEDURE VALIDATE_LOGGED_USER_RIGHTS
461 (p_proposal_id IN NUMBER
462 ,p_logged_user_id IN NUMBER
463 ,x_return_status OUT NOCOPY VARCHAR2) is
464
465 x VARCHAR2(1);
466 y VARCHAR2(1);
467
468 BEGIN
469 x_return_status:= FND_API.G_RET_STS_SUCCESS;
470
471 select x into y
472 from igw_prop_user_roles ppr,
473 igw_prop_users ppu
474 where ppr.proposal_id = p_proposal_id AND
475 ppr.proposal_id = ppu.proposal_id AND
476 ppr.user_id = ppu.user_id AND
477 ppr.role_id in (0,2,3) AND
478 ppr.user_id = p_logged_user_id AND
479 sysdate >= ppu.start_date_active AND
480 sysdate <= nvl(ppu.end_date_active, sysdate);
481
482 EXCEPTION
483
484 WHEN NO_DATA_FOUND THEN
485 x_return_status:= FND_API.G_RET_STS_ERROR;
486 fnd_message.set_name('IGW', 'IGW_NO_RIGHTS');
487 fnd_msg_pub.add;
488
489 WHEN too_many_rows THEN
490 NULL;
491
492 WHEN OTHERS THEN
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PVT',
495 p_procedure_name => 'VALIDATE_LOGGED_USER_RIGHTS',
496 p_error_text => SUBSTRB(SQLERRM,1,240));
497 raise fnd_api.g_exc_unexpected_error;
498 END VALIDATE_LOGGED_USER_RIGHTS;
499
500 ------------------------------------------------------------------------------------------
501 PROCEDURE CHECK_LOCK_GET_PK
502 (x_rowid IN VARCHAR2
503 ,p_record_version_number IN NUMBER
504 ,x_proposal_id OUT NOCOPY NUMBER
505 ,x_user_id OUT NOCOPY NUMBER
506 ,x_role_id OUT NOCOPY NUMBER
507 ,x_return_status OUT NOCOPY VARCHAR2) is
508
509 BEGIN
510 select proposal_id, user_id, role_id
511 into x_proposal_id, x_user_id, x_role_id
512 from igw_prop_user_roles
513 where rowid = x_rowid
514 and record_version_number = p_record_version_number;
515
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
520 FND_MSG_PUB.Add;
521 raise fnd_api.g_exc_error;
522
523 WHEN OTHERS THEN
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PVT',
526 p_procedure_name => 'CHECK_LOCK_GET_PK',
527 p_error_text => SUBSTRB(SQLERRM,1,240));
528 raise fnd_api.g_exc_unexpected_error;
529
530 END CHECK_LOCK_GET_PK;
531
532 ---------------------------------------------------------------------------------------------------------
533 PROCEDURE GET_ROLE_ID
534 (p_role_name IN VARCHAR2
535 ,x_role_id OUT NOCOPY NUMBER
536 ,x_return_status OUT NOCOPY VARCHAR2) is
537
538 BEGIN
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 IF p_role_name IS NOT NULL THEN
542 SELECT role_id
543 INTO x_role_id
544 FROM igw_roles_tl
545 WHERE upper(role_name) = upper(p_role_name)
546 and language = userenv('LANG');
547 END IF;
548
549 EXCEPTION
550 WHEN no_data_found THEN
551 x_return_status:= FND_API.G_RET_STS_ERROR;
552 fnd_message.set_name('IGW', 'IGW_SS_ROLE_INVALID');
553 fnd_msg_pub.add;
554
555 WHEN too_many_rows THEN
556 x_return_status:= FND_API.G_RET_STS_ERROR;
557 fnd_message.set_name('IGW', 'IGW_SS_ROLE_INVALID');
558 fnd_msg_pub.add;
559
560 WHEN OTHERS THEN
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PUB',
563 p_procedure_name => 'GET_ROLE_ID',
564 p_error_text => SUBSTRB(SQLERRM,1,240));
565 raise fnd_api.g_exc_unexpected_error;
566 END GET_ROLE_ID;
567
568 ------------------------------------------------------------------------------------
569 PROCEDURE CHECK_IF_SEEDED_ROLE
570 (p_role_id IN VARCHAR2
571 ,x_return_status OUT NOCOPY VARCHAR2) is
572
573 N NUMBER;
574
575 BEGIN
576 x_return_status:= FND_API.G_RET_STS_SUCCESS;
577
578 select count(*) into N
579 from igw_roles
580 where role_id = p_role_id AND
581 seeded_flag = 'Y';
582
583 if (N <> 0) then
584 x_return_status:= FND_API.G_RET_STS_ERROR;
585 fnd_message.set_name ('IGW', 'IGW_SEEDED_ROLE');
586 fnd_msg_pub.add;
587 end if;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_USER_ROLES_PVT',
593 p_procedure_name => 'CHECK_IF_SEEDED_ROLE',
594 p_error_text => SUBSTRB(SQLERRM,1,240));
595 raise fnd_api.g_exc_unexpected_error;
596 END CHECK_IF_SEEDED_ROLE;
597
598
599 -------------------------------------------------------------------------------------------------------
600 PROCEDURE CHECK_ERRORS is
601 l_msg_count NUMBER;
602 BEGIN
603 l_msg_count := fnd_msg_pub.count_msg;
604 IF (l_msg_count > 0) THEN
605 RAISE FND_API.G_EXC_ERROR;
606 END IF;
607
608 END CHECK_ERRORS;
609
610
611 END IGW_PROP_USER_ROLES_PVT;
[Home] [Help]