DBA Data[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;