[Home] [Help]
PACKAGE BODY: APPS.IGS_AZ_ADVISORS_PKG
Source
1 PACKAGE BODY igs_az_advisors_pkg AS
2 /* $Header: IGSHI03B.pls 115.4 2003/06/30 05:38:13 kdande noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_az_advisors%ROWTYPE;
6 new_references igs_az_advisors%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_group_advisor_id IN NUMBER,
12 x_group_name IN VARCHAR2,
13 x_advisor_person_id IN NUMBER,
14 x_start_date IN DATE,
15 x_end_date IN DATE,
16 x_max_students_num IN NUMBER,
17 x_notified_date IN DATE,
18 x_accept_add_flag IN VARCHAR2,
19 x_accept_delete_flag IN VARCHAR2,
20 x_creation_date IN DATE,
21 x_created_by IN NUMBER,
22 x_last_update_date IN DATE,
23 x_last_updated_by IN NUMBER,
24 x_last_update_login IN NUMBER
25 ) AS
26 /*
27 || Created By : [email protected]
28 || Created On : 15-MAY-2003
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM igs_az_advisors
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.group_advisor_id := x_group_advisor_id;
60 new_references.group_name := x_group_name;
61 new_references.advisor_person_id := x_advisor_person_id;
62 new_references.start_date := TRUNC(x_start_date);
63 new_references.end_date := TRUNC(x_end_date);
64 new_references.max_students_num := x_max_students_num;
65 new_references.notified_date := TRUNC(x_notified_date);
66 new_references.accept_add_flag := x_accept_add_flag;
67 new_references.accept_delete_flag := x_accept_delete_flag;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83
84 PROCEDURE check_uniqueness AS
85 /*
86 || Created By : [email protected]
87 || Created On : 15-MAY-2003
88 || Purpose : Handles the Unique Constraint logic defined for the columns.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94 BEGIN
95
96 IF ( get_uk_for_validation (
97 new_references.advisor_person_id,
98 new_references.group_name
99 )
100 ) THEN
101 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 END IF;
105
106 END check_uniqueness;
107
108
109 PROCEDURE check_parent_existance AS
110 /*
111 || Created By : [email protected]
112 || Created On : 15-MAY-2003
113 || Purpose : Checks for the existance of Parent records.
114 || Known limitations, enhancements or remarks :
115 || Change History :
116 || Who When What
117 || (reverse chronological order - newest change first)
118 */
119 BEGIN
120
121 IF (((old_references.group_name = new_references.group_name)) OR
122 ((new_references.group_name IS NULL))) THEN
123 NULL;
124 ELSIF NOT igs_az_groups_pkg.get_pk_for_validation (
125 new_references.group_name
126 ) THEN
127 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
128 igs_ge_msg_stack.add;
129 app_exception.raise_exception;
130 END IF;
131
132 END check_parent_existance;
133
134
135 PROCEDURE check_child_existance AS
136 /*
137 || Created By : [email protected]
138 || Created On : 15-MAY-2003
139 || Purpose : Checks for the existance of Child records.
140 || Known limitations, enhancements or remarks :
141 || Change History :
142 || Who When What
143 || (reverse chronological order - newest change first)
144 */
145 BEGIN
146
147 igs_az_advising_rels_pkg.get_fk_igs_az_advisors (
148 old_references.group_advisor_id
149 );
150
151 END check_child_existance;
152
153
154 FUNCTION get_pk_for_validation (
155 x_group_advisor_id IN NUMBER
156 ) RETURN BOOLEAN AS
157 /*
158 || Created By : [email protected]
159 || Created On : 15-MAY-2003
160 || Purpose : Validates the Primary Key of the table.
161 || Known limitations, enhancements or remarks :
162 || Change History :
163 || Who When What
164 || (reverse chronological order - newest change first)
165 */
166 CURSOR cur_rowid IS
167 SELECT rowid
168 FROM igs_az_advisors
169 WHERE group_advisor_id = x_group_advisor_id;
170
171 lv_rowid cur_rowid%RowType;
172
173 BEGIN
174
175 OPEN cur_rowid;
176 FETCH cur_rowid INTO lv_rowid;
177 IF (cur_rowid%FOUND) THEN
178 CLOSE cur_rowid;
179 RETURN(TRUE);
180 ELSE
181 CLOSE cur_rowid;
182 RETURN(FALSE);
183 END IF;
184
185 END get_pk_for_validation;
186
187
188 FUNCTION get_uk_for_validation (
189 x_advisor_person_id IN NUMBER,
190 x_group_name IN VARCHAR2
191 ) RETURN BOOLEAN AS
192 /*
193 || Created By : [email protected]
194 || Created On : 15-MAY-2003
195 || Purpose : Validates the Unique Keys of the table.
196 || Known limitations, enhancements or remarks :
197 || Change History :
198 || Who When What
199 || (reverse chronological order - newest change first)
200 */
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM igs_az_advisors
204 WHERE advisor_person_id = x_advisor_person_id
205 AND group_name = x_group_name
206 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
207
208 lv_rowid cur_rowid%RowType;
209
210 BEGIN
211
212 OPEN cur_rowid;
213 FETCH cur_rowid INTO lv_rowid;
214 IF (cur_rowid%FOUND) THEN
215 CLOSE cur_rowid;
216 RETURN (TRUE);
217 ELSE
218 CLOSE cur_rowid;
219 RETURN(FALSE);
220 END IF;
221
222 END get_uk_for_validation ;
223
224
225 PROCEDURE get_fk_igs_az_groups (
226 x_group_name IN VARCHAR2
227 ) AS
228 /*
229 || Created By : [email protected]
230 || Created On : 15-MAY-2003
231 || Purpose : Validates the Foreign Keys for the table.
232 || Known limitations, enhancements or remarks :
233 || Change History :
234 || Who When What
235 || (reverse chronological order - newest change first)
236 */
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM igs_az_advisors
240 WHERE ((group_name = x_group_name));
241
242 lv_rowid cur_rowid%RowType;
243
244 BEGIN
245
246 OPEN cur_rowid;
247 FETCH cur_rowid INTO lv_rowid;
248 IF (cur_rowid%FOUND) THEN
249 CLOSE cur_rowid;
250 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
251 igs_ge_msg_stack.add;
252 app_exception.raise_exception;
253 RETURN;
254 END IF;
255 CLOSE cur_rowid;
256
257 END get_fk_igs_az_groups;
258
259
260 PROCEDURE before_dml (
261 p_action IN VARCHAR2,
262 x_rowid IN VARCHAR2,
263 x_group_advisor_id IN NUMBER,
264 x_group_name IN VARCHAR2,
265 x_advisor_person_id IN NUMBER,
266 x_start_date IN DATE,
267 x_end_date IN DATE,
268 x_max_students_num IN NUMBER,
269 x_notified_date IN DATE,
270 x_accept_add_flag IN VARCHAR2,
271 x_accept_delete_flag IN VARCHAR2,
272 x_creation_date IN DATE,
273 x_created_by IN NUMBER,
274 x_last_update_date IN DATE,
275 x_last_updated_by IN NUMBER,
276 x_last_update_login IN NUMBER
277 ) AS
278 /*
279 || Created By : [email protected]
280 || Created On : 15-MAY-2003
281 || Purpose : Initialises the columns, Checks Constraints, Calls the
282 || Trigger Handlers for the table, before any DML operation.
283 || Known limitations, enhancements or remarks :
284 || Change History :
285 || Who When What
286 || (reverse chronological order - newest change first)
287 */
288 BEGIN
289
290 set_column_values (
291 p_action,
292 x_rowid,
293 x_group_advisor_id,
294 x_group_name,
295 x_advisor_person_id,
296 x_start_date,
297 x_end_date,
298 x_max_students_num,
299 x_notified_date,
300 x_accept_add_flag,
301 x_accept_delete_flag,
302 x_creation_date,
303 x_created_by,
304 x_last_update_date,
305 x_last_updated_by,
306 x_last_update_login
307 );
308
309 IF (p_action = 'INSERT') THEN
310 -- Call all the procedures related to Before Insert.
311 IF ( get_pk_for_validation(
312 new_references.group_advisor_id
313 )
314 ) THEN
315 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
316 igs_ge_msg_stack.add;
317 app_exception.raise_exception;
318 END IF;
319 check_uniqueness;
320 check_parent_existance;
321 ELSIF (p_action = 'UPDATE') THEN
322 -- Call all the procedures related to Before Update.
323 check_uniqueness;
324 check_parent_existance;
325 ELSIF (p_action = 'DELETE') THEN
326 -- Call all the procedures related to Before Delete.
327 check_child_existance;
328 ELSIF (p_action = 'VALIDATE_INSERT') THEN
329 -- Call all the procedures related to Before Insert.
330 IF ( get_pk_for_validation (
331 new_references.group_advisor_id
332 )
333 ) THEN
334 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
335 igs_ge_msg_stack.add;
336 app_exception.raise_exception;
337 END IF;
338 check_uniqueness;
339 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
340 check_uniqueness;
341 ELSIF (p_action = 'VALIDATE_DELETE') THEN
342 check_child_existance;
343 END IF;
344
345 END before_dml;
346
347
348 PROCEDURE insert_row (
349 x_rowid IN OUT NOCOPY VARCHAR2,
350 x_group_advisor_id IN OUT NOCOPY NUMBER,
351 x_group_name IN VARCHAR2,
352 x_advisor_person_id IN NUMBER,
353 x_start_date IN DATE,
354 x_end_date IN DATE,
355 x_max_students_num IN NUMBER,
356 x_notified_date IN DATE,
357 x_accept_add_flag IN VARCHAR2,
358 x_accept_delete_flag IN VARCHAR2,
359 x_mode IN VARCHAR2,
360 X_return_status OUT NOCOPY VARCHAR2,
361 X_msg_data OUT NOCOPY VARCHAR2,
362 X_msg_count OUT NOCOPY NUMBER
363 ) AS
364 /*
365 || Created By : [email protected]
366 || Created On : 15-MAY-2003
367 || Purpose : Handles the INSERT DML logic for the table.
368 || Known limitations, enhancements or remarks :
369 || Change History :
370 || Who When What
371 || (reverse chronological order - newest change first)
372 */
373
374 x_last_update_date DATE;
375 x_last_updated_by NUMBER;
376 x_last_update_login NUMBER;
377
378 BEGIN
379
380 FND_MSG_PUB.initialize;
381 x_last_update_date := SYSDATE;
382 IF (x_mode = 'I') THEN
383 x_last_updated_by := 1;
384 x_last_update_login := 0;
385 ELSIF (x_mode = 'R') THEN
386 x_last_updated_by := fnd_global.user_id;
387 IF (x_last_updated_by IS NULL) THEN
388 x_last_updated_by := -1;
389 END IF;
390 x_last_update_login := fnd_global.login_id;
391 IF (x_last_update_login IS NULL) THEN
392 x_last_update_login := -1;
393 END IF;
394 ELSE
395 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
396 fnd_message.set_token ('ROUTINE', 'IGS_AZ_ADVISORS_PKG.INSERT_ROW');
397 igs_ge_msg_stack.add;
398 app_exception.raise_exception;
399 END IF;
400
401 x_group_advisor_id := NULL;
402
403 before_dml(
404 p_action => 'INSERT',
405 x_rowid => x_rowid,
406 x_group_advisor_id => x_group_advisor_id,
407 x_group_name => x_group_name,
408 x_advisor_person_id => x_advisor_person_id,
409 x_start_date => x_start_date,
410 x_end_date => x_end_date,
411 x_max_students_num => x_max_students_num,
412 x_notified_date => x_notified_date,
413 x_accept_add_flag => x_accept_add_flag,
414 x_accept_delete_flag => x_accept_delete_flag,
415 x_creation_date => x_last_update_date,
416 x_created_by => x_last_updated_by,
417 x_last_update_date => x_last_update_date,
418 x_last_updated_by => x_last_updated_by,
419 x_last_update_login => x_last_update_login
420 );
421
422 INSERT INTO igs_az_advisors (
423 group_advisor_id,
424 group_name,
425 advisor_person_id,
426 start_date,
427 end_date,
428 max_students_num,
429 notified_date,
430 accept_add_flag,
431 accept_delete_flag,
432 creation_date,
433 created_by,
434 last_update_date,
435 last_updated_by,
436 last_update_login
437 ) VALUES (
438 igs_az_advisors_s.NEXTVAL,
439 new_references.group_name,
440 new_references.advisor_person_id,
441 new_references.start_date,
442 new_references.end_date,
443 new_references.max_students_num,
444 new_references.notified_date,
445 new_references.accept_add_flag,
446 new_references.accept_delete_flag,
447 x_last_update_date,
448 x_last_updated_by,
449 x_last_update_date,
450 x_last_updated_by,
451 x_last_update_login
452 ) RETURNING ROWID, group_advisor_id INTO x_rowid, x_group_advisor_id;
453
454 -- Initialize API return status to success.
455 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
456 -- Standard call to get message count and if count is 1, get message info.
457 FND_MSG_PUB.Count_And_Get(
458 p_encoded => FND_API.G_FALSE,
459 p_count => x_MSG_COUNT,
460 p_data => X_MSG_DATA);
461 EXCEPTION
462 WHEN FND_API.G_EXC_ERROR THEN
463 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
464 FND_MSG_PUB.Count_And_Get(
465 p_encoded => FND_API.G_FALSE,
466 p_count => x_MSG_COUNT,
467 p_data => X_MSG_DATA);
468 RETURN;
469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
470 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
471 FND_MSG_PUB.Count_And_Get(
472 p_encoded => FND_API.G_FALSE,
473 p_count => x_MSG_COUNT,
474 p_data => X_MSG_DATA);
475 RETURN;
476 WHEN OTHERS THEN
477 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
478 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
479 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
480 FND_MSG_PUB.ADD;
481 FND_MSG_PUB.Count_And_Get(
482 p_encoded => FND_API.G_FALSE,
483 p_count => x_MSG_COUNT,
484 p_data => X_MSG_DATA);
485 RETURN;
486
487 END insert_row;
488
489
490 PROCEDURE lock_row (
491 x_rowid IN VARCHAR2,
492 x_group_advisor_id IN NUMBER,
493 x_group_name IN VARCHAR2,
494 x_advisor_person_id IN NUMBER,
495 x_start_date IN DATE,
496 x_end_date IN DATE,
497 x_max_students_num IN NUMBER,
498 x_notified_date IN DATE,
499 x_accept_add_flag IN VARCHAR2,
500 x_accept_delete_flag IN VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_data OUT NOCOPY VARCHAR2,
503 x_msg_count OUT NOCOPY NUMBER
504 ) AS
505 /*
506 || Created By : [email protected]
507 || Created On : 15-MAY-2003
508 || Purpose : Handles the LOCK mechanism for the table.
509 || Known limitations, enhancements or remarks :
510 || Change History :
511 || Who When What
512 || (reverse chronological order - newest change first)
513 */
514 CURSOR c1 IS
515 SELECT
516 group_name,
517 advisor_person_id,
518 start_date,
519 end_date,
520 max_students_num,
521 notified_date,
522 accept_add_flag,
523 accept_delete_flag
524 FROM igs_az_advisors
525 WHERE rowid = x_rowid
526 FOR UPDATE NOWAIT;
527
528 tlinfo c1%ROWTYPE;
529
530 BEGIN
531
532 FND_MSG_PUB.initialize;
533 OPEN c1;
534 FETCH c1 INTO tlinfo;
535 IF (c1%notfound) THEN
536 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
537 igs_ge_msg_stack.add;
538 CLOSE c1;
539 app_exception.raise_exception;
540 RETURN;
541 END IF;
542 CLOSE c1;
543
544 IF (
545 (tlinfo.group_name = x_group_name)
546 AND (tlinfo.advisor_person_id = x_advisor_person_id)
547 AND ((trunc(tlinfo.start_date) = trunc(x_start_date)) OR ((tlinfo.start_date IS NULL) AND (x_start_date IS NULL)))
548 AND ((trunc(tlinfo.end_date) = trunc(x_end_date)) OR ((tlinfo.end_date IS NULL) AND (x_end_date IS NULL)))
549 AND ((tlinfo.max_students_num = x_max_students_num) OR ((tlinfo.max_students_num IS NULL) AND (x_max_students_num IS NULL)))
550 AND ((trunc(tlinfo.notified_date) = trunc(x_notified_date)) OR ((tlinfo.notified_date IS NULL) AND (x_notified_date IS NULL)))
551 AND ((tlinfo.accept_add_flag = x_accept_add_flag) OR ((tlinfo.accept_add_flag IS NULL) AND (x_accept_add_flag IS NULL)))
552 AND ((tlinfo.accept_delete_flag = x_accept_delete_flag) OR ((tlinfo.accept_delete_flag IS NULL) AND (x_accept_delete_flag IS NULL)))
553 ) THEN
554 NULL;
555 ELSE
556 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
557 igs_ge_msg_stack.add;
558 app_exception.raise_exception;
559 END IF;
560
561 -- Initialize API return status to success.
562 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
563 -- Standard call to get message count and if count is 1, get message info.
564 FND_MSG_PUB.Count_And_Get(
565 p_encoded => FND_API.G_FALSE,
566 p_count => x_MSG_COUNT,
567 p_data => X_MSG_DATA);
568 RETURN;
569
570 EXCEPTION
571 WHEN FND_API.G_EXC_ERROR THEN
572 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
573 FND_MSG_PUB.Count_And_Get(
574 p_encoded => FND_API.G_FALSE,
575 p_count => x_MSG_COUNT,
576 p_data => X_MSG_DATA);
577 RETURN;
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
580 FND_MSG_PUB.Count_And_Get(
581 p_encoded => FND_API.G_FALSE,
582 p_count => x_MSG_COUNT,
583 p_data => X_MSG_DATA);
584 RETURN;
585 WHEN OTHERS THEN
586 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
587 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
588 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
589 FND_MSG_PUB.ADD;
590 FND_MSG_PUB.Count_And_Get(
591 p_encoded => FND_API.G_FALSE,
592 p_count => x_MSG_COUNT,
593 p_data => X_MSG_DATA);
594 RETURN;
595
596 END lock_row;
597
598
599 PROCEDURE update_row (
600 x_rowid IN VARCHAR2,
601 x_group_advisor_id IN NUMBER,
602 x_group_name IN VARCHAR2,
603 x_advisor_person_id IN NUMBER,
604 x_start_date IN DATE,
605 x_end_date IN DATE,
606 x_max_students_num IN NUMBER,
607 x_notified_date IN DATE,
608 x_accept_add_flag IN VARCHAR2,
609 x_accept_delete_flag IN VARCHAR2,
610 x_mode IN VARCHAR2,
611 x_return_status OUT NOCOPY VARCHAR2,
612 x_msg_data OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER
614 ) AS
615 /*
616 || Created By : [email protected]
617 || Created On : 15-MAY-2003
618 || Purpose : Handles the UPDATE DML logic for the table.
619 || Known limitations, enhancements or remarks :
620 || Change History :
621 || Who When What
622 || (reverse chronological order - newest change first)
623 */
624 x_last_update_date DATE ;
625 x_last_updated_by NUMBER;
626 x_last_update_login NUMBER;
627
628 BEGIN
629
630 FND_MSG_PUB.initialize;
631 x_last_update_date := SYSDATE;
632 IF (X_MODE = 'I') THEN
633 x_last_updated_by := 1;
634 x_last_update_login := 0;
635 ELSIF (x_mode = 'R') THEN
636 x_last_updated_by := fnd_global.user_id;
637 IF x_last_updated_by IS NULL THEN
638 x_last_updated_by := -1;
639 END IF;
640 x_last_update_login := fnd_global.login_id;
641 IF (x_last_update_login IS NULL) THEN
642 x_last_update_login := -1;
643 END IF;
644 ELSE
645 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
646 fnd_message.set_token ('ROUTINE', 'IGS_AZ_ADVISORS_PKG.UPDATE_ROW');
647 igs_ge_msg_stack.add;
648 app_exception.raise_exception;
649 END IF;
650
651 before_dml(
652 p_action => 'UPDATE',
653 x_rowid => x_rowid,
654 x_group_advisor_id => x_group_advisor_id,
655 x_group_name => x_group_name,
656 x_advisor_person_id => x_advisor_person_id,
657 x_start_date => x_start_date,
658 x_end_date => x_end_date,
659 x_max_students_num => x_max_students_num,
660 x_notified_date => x_notified_date,
661 x_accept_add_flag => x_accept_add_flag,
662 x_accept_delete_flag => x_accept_delete_flag,
663 x_creation_date => x_last_update_date,
664 x_created_by => x_last_updated_by,
665 x_last_update_date => x_last_update_date,
666 x_last_updated_by => x_last_updated_by,
667 x_last_update_login => x_last_update_login
668 );
669
670 IF ((new_references.end_date IS NULL AND old_references.end_date IS NOT NULL) OR
671 (new_references.end_date IS NOT NULL AND old_references.end_date IS NULL) OR
672 ( NVL(trunc(new_references.end_date),trunc(SYSDATE)) <> NVL(trunc(old_references.end_date),trunc(SYSDATE)) ) ) THEN
673 igs_az_gen_001.end_date_advisor(
674 p_group_name => new_references.group_name,
675 p_advisor_person_id => new_references.advisor_person_id,
676 p_end_date => new_references.end_date,
677 p_calling_mod => 'T' );
678 -- If advisor is being un-end-dated set auto_delete_flag to null
679 IF new_references.end_date IS NULL AND old_references.end_date IS NOT NULL THEN
680 new_references.accept_delete_flag := NULL;
681 END IF;
682 END IF;
683
684 UPDATE igs_az_advisors
685 SET
686 group_name = new_references.group_name,
687 advisor_person_id = new_references.advisor_person_id,
688 start_date = new_references.start_date,
689 end_date = new_references.end_date,
690 max_students_num = new_references.max_students_num,
691 notified_date = new_references.notified_date,
692 accept_add_flag = new_references.accept_add_flag,
693 accept_delete_flag = new_references.accept_delete_flag,
694 last_update_date = x_last_update_date,
695 last_updated_by = x_last_updated_by,
696 last_update_login = x_last_update_login
697 WHERE rowid = x_rowid;
698 IF (SQL%NOTFOUND) THEN
699 RAISE NO_DATA_FOUND;
700 END IF;
701
702 -- Initialize API return status to success.
703 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
704 -- Standard call to get message count and if count is 1, get message info.
705 FND_MSG_PUB.Count_And_Get(
706 p_encoded => FND_API.G_FALSE,
707 p_count => x_MSG_COUNT,
708 p_data => X_MSG_DATA);
709 EXCEPTION
710 WHEN FND_API.G_EXC_ERROR THEN
711 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
712 FND_MSG_PUB.Count_And_Get(
713 p_encoded => FND_API.G_FALSE,
714 p_count => x_MSG_COUNT,
715 p_data => X_MSG_DATA);
716 RETURN;
717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
718 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
719 FND_MSG_PUB.Count_And_Get(
720 p_encoded => FND_API.G_FALSE,
721 p_count => x_MSG_COUNT,
722 p_data => X_MSG_DATA);
723 RETURN;
724 WHEN OTHERS THEN
725 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
726 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
727 FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
728 FND_MSG_PUB.ADD;
729 FND_MSG_PUB.Count_And_Get(
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_MSG_COUNT,
732 p_data => X_MSG_DATA);
733 RETURN;
734
735 END update_row;
736
737
738 PROCEDURE add_row (
739 x_rowid IN OUT NOCOPY VARCHAR2,
740 x_group_advisor_id IN OUT NOCOPY NUMBER,
741 x_group_name IN VARCHAR2,
742 x_advisor_person_id IN NUMBER,
743 x_start_date IN DATE,
744 x_end_date IN DATE,
745 x_max_students_num IN NUMBER,
746 x_notified_date IN DATE,
747 x_accept_add_flag IN VARCHAR2,
748 x_accept_delete_flag IN VARCHAR2,
749 x_mode IN VARCHAR2
750 ) AS
751 /*
752 || Created By : [email protected]
753 || Created On : 15-MAY-2003
754 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
755 || Known limitations, enhancements or remarks :
756 || Change History :
757 || Who When What
758 || (reverse chronological order - newest change first)
759 */
760 CURSOR c1 IS
761 SELECT rowid
762 FROM igs_az_advisors
763 WHERE group_advisor_id = x_group_advisor_id;
764
765 l_return_status VARCHAR2(10);
766 l_msg_data VARCHAR2(2000);
767 l_msg_count NUMBER(10);
768
769 BEGIN
770
771 OPEN c1;
772 FETCH c1 INTO x_rowid;
773 IF (c1%NOTFOUND) THEN
774 CLOSE c1;
775
776 insert_row (
777 x_rowid,
778 x_group_advisor_id,
779 x_group_name,
780 x_advisor_person_id,
781 x_start_date,
782 x_end_date,
783 x_max_students_num,
784 x_notified_date,
785 x_accept_add_flag,
786 x_accept_delete_flag,
787 x_mode,
788 l_return_status,
789 l_msg_data,
790 l_msg_count
791 );
792 RETURN;
793 END IF;
794 CLOSE c1;
795
796 update_row (
797 x_rowid,
798 x_group_advisor_id,
799 x_group_name,
800 x_advisor_person_id,
801 x_start_date,
802 x_end_date,
803 x_max_students_num,
804 x_notified_date,
805 x_accept_add_flag,
806 x_accept_delete_flag,
807 x_mode,
808 l_return_status,
809 l_msg_data,
810 l_msg_count
811 );
812
813 END add_row;
814
815
816 PROCEDURE delete_row (
817 x_rowid IN VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_msg_data OUT NOCOPY VARCHAR2,
820 x_msg_count OUT NOCOPY NUMBER
821 ) AS
822 /*
823 || Created By : [email protected]
824 || Created On : 15-MAY-2003
825 || Purpose : Handles the DELETE DML logic for the table.
826 || Known limitations, enhancements or remarks :
827 || Change History :
828 || Who When What
829 || (reverse chronological order - newest change first)
830 */
831 BEGIN
832
833 FND_MSG_PUB.initialize;
834 before_dml (
835 p_action => 'DELETE',
836 x_rowid => x_rowid
837 );
838
839 DELETE FROM igs_az_advisors
840 WHERE rowid = x_rowid;
841
842 IF (SQL%NOTFOUND) THEN
843 RAISE NO_DATA_FOUND;
844 END IF;
845
846 -- Initialize API return status to success.
847 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
848 -- Standard call to get message count and if count is 1, get message info.
849 FND_MSG_PUB.Count_And_Get(
850 p_encoded => FND_API.G_FALSE,
851 p_count => x_MSG_COUNT,
852 p_data => X_MSG_DATA);
853 EXCEPTION
854 WHEN FND_API.G_EXC_ERROR THEN
855 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
856 FND_MSG_PUB.Count_And_Get(
857 p_encoded => FND_API.G_FALSE,
858 p_count => x_MSG_COUNT,
859 p_data => X_MSG_DATA);
860 RETURN;
861 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
862 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
863 FND_MSG_PUB.Count_And_Get(
864 p_encoded => FND_API.G_FALSE,
865 p_count => x_MSG_COUNT,
866 p_data => X_MSG_DATA);
867 RETURN;
868 WHEN OTHERS THEN
869 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
870 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
871 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
872 FND_MSG_PUB.ADD;
873 FND_MSG_PUB.Count_And_Get(
874 p_encoded => FND_API.G_FALSE,
875 p_count => x_MSG_COUNT,
876 p_data => X_MSG_DATA);
877 RETURN;
878
879 END delete_row;
880
881 END igs_az_advisors_pkg;