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