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