eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_PR_STU_ACAD_STAT_PKG

Source


1 PACKAGE BODY IGS_PR_STU_ACAD_STAT_PKG AS
2 /* $Header: IGSQI39B.pls 115.3 2003/08/19 05:41:29 smanglm noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_pr_stu_acad_stat%ROWTYPE;
6   new_references igs_pr_stu_acad_stat%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    ,
11     x_person_id                         IN     NUMBER,
12     x_course_cd                         IN     VARCHAR2,
13     x_cal_type                          IN     VARCHAR2,
14     x_ci_sequence_number                IN     NUMBER,
15     x_stat_type                         IN     VARCHAR2,
16     x_timeframe                         IN     VARCHAR2,
17     x_source_type                       IN     VARCHAR2,
18     x_source_reference                  IN     VARCHAR2,
19     x_attempted_credit_points           IN     NUMBER,
20     x_earned_credit_points              IN     NUMBER,
21     x_gpa                               IN     NUMBER,
22     x_gpa_credit_points                 IN     NUMBER,
23     x_gpa_quality_points                IN     NUMBER,
24     x_creation_date                     IN     DATE        ,
25     x_created_by                        IN     NUMBER      ,
26     x_last_update_date                  IN     DATE        ,
27     x_last_updated_by                   IN     NUMBER      ,
28     x_last_update_login                 IN     NUMBER
29   ) AS
30   /*
31   ||  Created By : nmankodi
32   ||  Created On : 04-NOV-2002
33   ||  Purpose : Initialises the Old and New references for the columns of the table.
34   ||  Known limitations, enhancements or remarks :
35   ||  Change History :
36   ||  Who             When            What
37   ||  (reverse chronological order - newest change first)
38 
39   */
40 
41     CURSOR cur_old_ref_values IS
42       SELECT   *
43       FROM     igs_pr_stu_acad_stat
44       WHERE    rowid = x_rowid;
45 
46   BEGIN
47 
48     l_rowid := x_rowid;
49 
50     -- Code for setting the Old and New Reference Values.
51     -- Populate Old Values.
52     OPEN cur_old_ref_values;
53     FETCH cur_old_ref_values INTO old_references;
54     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55       CLOSE cur_old_ref_values;
56       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57       igs_ge_msg_stack.add;
58       app_exception.raise_exception;
59       RETURN;
60     END IF;
61     CLOSE cur_old_ref_values;
62 
63     -- Populate New Values.
64     new_references.person_id                         := x_person_id;
65     new_references.course_cd                         := x_course_cd;
66     new_references.cal_type                          := x_cal_type;
67     new_references.ci_sequence_number                := x_ci_sequence_number;
68     new_references.stat_type                         := x_stat_type;
69     new_references.timeframe                         := x_timeframe;
70     new_references.source_type                       := x_source_type;
71     new_references.source_reference                  := x_source_reference;
72     new_references.attempted_credit_points           := x_attempted_credit_points;
73     new_references.earned_credit_points              := x_earned_credit_points;
74     new_references.gpa                               := x_gpa;
75     new_references.gpa_credit_points                 := x_gpa_credit_points;
76     new_references.gpa_quality_points                := x_gpa_quality_points;
77 
78 
79     IF (p_action = 'UPDATE') THEN
80       new_references.creation_date                   := old_references.creation_date;
81       new_references.created_by                      := old_references.created_by;
82     ELSE
83       new_references.creation_date                   := x_creation_date;
84       new_references.created_by                      := x_created_by;
85     END IF;
86 
87     new_references.last_update_date                  := x_last_update_date;
88     new_references.last_updated_by                   := x_last_updated_by;
89     new_references.last_update_login                 := x_last_update_login;
90 
91   END set_column_values;
92 
93 
94   PROCEDURE check_parent_existance AS
95   /*
96   ||  Created By : nmankodi
97   ||  Created On : 04-NOV-2002
98   ||  Purpose : Checks for the existance of Parent records.
99   ||  Known limitations, enhancements or remarks :
100   ||  Change History :
101   ||  Who             When            What
102   ||  (reverse chronological order - newest change first)
103   */
104   BEGIN
105 
106     IF (((old_references.person_id = new_references.person_id) AND
107          (old_references.course_cd = new_references.course_cd)) OR
108         ((new_references.person_id IS NULL) OR
109          (new_references.course_cd IS NULL))) THEN
110       NULL;
111     ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
112                 new_references.person_id,
113                 new_references.course_cd
114               ) THEN
115       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
116       igs_ge_msg_stack.add;
117       app_exception.raise_exception;
118     END IF;
119 
120     IF (((old_references.cal_type= new_references.cal_type) AND
121          (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
122         ((new_references.cal_type IS NULL) OR
123          (new_references.ci_sequence_number IS NULL))) THEN
124       NULL;
125     ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
126         new_references.cal_type,
127         new_references.ci_sequence_number
128         )	THEN
129 	    Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
130 	    IGS_GE_MSG_STACK.ADD;
131 	    APP_EXCEPTION.RAISE_EXCEPTION;
132     END IF;
133 
134     IF (((old_references.stat_type = new_references.stat_type)) OR
135         ((new_references.stat_type IS NULL))) THEN
136       NULL;
137     ELSIF NOT igs_pr_stat_type_pkg.get_pk_for_validation (
138                 new_references.stat_type
139               ) THEN
140       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
141       igs_ge_msg_stack.add;
142       app_exception.raise_exception;
143     END IF;
144 
145   END check_parent_existance;
146 
147 
148 
149   FUNCTION get_pk_for_validation (
150     x_person_id                         IN     NUMBER,
151     x_course_cd                         IN     VARCHAR2,
152     x_cal_type                          IN     VARCHAR2,
153     x_ci_sequence_number                IN     NUMBER,
154     x_stat_type                         IN     VARCHAR2,
155 		x_timeframe                         IN     VARCHAR2
156   ) RETURN BOOLEAN AS
157   /*
158   ||  Created By : nmankodi
159   ||  Created On : 04-NOV-2002
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_pr_stu_acad_stat
169       WHERE    person_id = x_person_id
170       AND      course_cd = x_course_cd
171       AND      cal_type = x_cal_type
172       AND      ci_sequence_number = x_ci_sequence_number
173       AND      stat_type = x_stat_type
174 			AND      timeframe = x_timeframe
175       FOR UPDATE NOWAIT;
176 
177     lv_rowid cur_rowid%RowType;
178 
179   BEGIN
180 
181     OPEN cur_rowid;
182     FETCH cur_rowid INTO lv_rowid;
183     IF (cur_rowid%FOUND) THEN
184       CLOSE cur_rowid;
185       RETURN(TRUE);
186     ELSE
187       CLOSE cur_rowid;
188       RETURN(FALSE);
189     END IF;
190 
191   END get_pk_for_validation;
192 
193 
194     PROCEDURE get_fk_igs_en_stdnt_ps_att (
195     x_person_id IN NUMBER,
196     x_course_cd IN VARCHAR2
197     ) AS
198       /*
199   ||  Created By : nmankodi
200   ||  Created On : 04-NOV-2002
201   ||  Purpose : Validates the Foreign Keys for the table.
202   ||  Known limitations, enhancements or remarks :
203   ||  Change History :
204   ||  Who             When            What
205   ||  (reverse chronological order - newest change first)
206   */
207     CURSOR cur_rowid IS
208       SELECT   ROWID
209       FROM     igs_pr_stu_acad_stat
210       WHERE    person_id = x_person_id
211       AND      course_cd = x_course_cd ;
212     lv_rowid cur_rowid%ROWTYPE;
213   BEGIN
214     OPEN cur_rowid;
215     FETCH cur_rowid INTO lv_rowid;
216     IF (cur_rowid%FOUND) THEN
217       Fnd_Message.Set_Name ('IGS', 'IGS_PR_SAS_SCA_FK');
218       IGS_GE_MSG_STACK.ADD;
219 	        CLOSE cur_rowid;
220       APP_EXCEPTION.RAISE_EXCEPTION;
221       RETURN;
222     END IF;
223     CLOSE cur_rowid;
224   END get_fk_igs_en_stdnt_ps_att;
225 
226     PROCEDURE get_fk_igs_ca_inst (
227     x_cal_type IN VARCHAR2,
228     x_ci_sequence_number IN NUMBER
229     ) AS
230     CURSOR cur_rowid IS
231       SELECT   ROWID
232       FROM     igs_pr_stu_acad_stat
233       WHERE    cal_type= x_cal_type
234       AND      ci_sequence_number = x_ci_sequence_number;
235 
236     lv_rowid cur_rowid%ROWTYPE;
237   BEGIN
238     OPEN cur_rowid;
239     FETCH cur_rowid INTO lv_rowid;
240     IF (cur_rowid%FOUND) THEN
241       Fnd_Message.Set_Name ('IGS', 'IGS_PR_SAS_CI_FK');
242       IGS_GE_MSG_STACK.ADD;
243 	        CLOSE cur_rowid;
244       APP_EXCEPTION.RAISE_EXCEPTION;
245       RETURN;
246     END IF;
247     CLOSE cur_rowid;
248   END get_fk_igs_ca_inst;
249 
250     PROCEDURE get_fk_igs_pr_stat_type (
251     x_stat_type                         IN     VARCHAR2
252   ) AS
253   /*
254   ||  Created By : nmankodi
255   ||  Created On : 04-NOV-2002
256   ||  Purpose : Validates the Foreign Keys for the table.
257   ||  Known limitations, enhancements or remarks :
258   ||  Change History :
259   ||  Who             When            What
260   ||  (reverse chronological order - newest change first)
261   */
262     CURSOR cur_rowid IS
263       SELECT   rowid
264       FROM     igs_pr_stu_acad_stat
265       WHERE   ((stat_type = x_stat_type));
266 
267     lv_rowid cur_rowid%RowType;
268 
269   BEGIN
270 
271     OPEN cur_rowid;
272     FETCH cur_rowid INTO lv_rowid;
273     IF (cur_rowid%FOUND) THEN
274       CLOSE cur_rowid;
275       fnd_message.set_name ('IGS', 'IGS_PR_SAS_STTY_FK');
276       igs_ge_msg_stack.add;
277       app_exception.raise_exception;
278       RETURN;
279     END IF;
280     CLOSE cur_rowid;
281 
282   END get_fk_igs_pr_stat_type;
283 
284   PROCEDURE before_dml (
285     p_action                            IN     VARCHAR2,
286     x_rowid                             IN     VARCHAR2    ,
287     x_person_id                         IN     NUMBER,
288     x_course_cd                         IN     VARCHAR2,
289     x_cal_type                          IN     VARCHAR2,
290     x_ci_sequence_number                IN     NUMBER,
291     x_stat_type                         IN     VARCHAR2,
292     x_timeframe                         IN     VARCHAR2,
293     x_source_type                       IN     VARCHAR2,
294     x_source_reference                  IN     VARCHAR2,
295     x_attempted_credit_points           IN     NUMBER,
296     x_earned_credit_points              IN     NUMBER,
297     x_gpa                               IN     NUMBER,
298     x_gpa_credit_points                 IN     NUMBER,
299     x_gpa_quality_points                IN     NUMBER,
300     x_creation_date                     IN     DATE        ,
301     x_created_by                        IN     NUMBER      ,
302     x_last_update_date                  IN     DATE        ,
303     x_last_updated_by                   IN     NUMBER      ,
304     x_last_update_login                 IN     NUMBER
305   ) AS
306   /*
307   ||  Created By : nmankodi
308   ||  Created On : 04-NOV-2002
309   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
310   ||            Trigger Handlers for the table, before any DML operation.
311   ||  Known limitations, enhancements or remarks :
312   ||  Change History :
313   ||  Who             When            What
314   ||  (reverse chronological order - newest change first)
315   */
316   BEGIN
317 
318     set_column_values (
319       p_action,
320       x_rowid,
321       x_person_id,
322       x_course_cd,
323       x_cal_type,
324       x_ci_sequence_number,
325       x_stat_type,
326       x_timeframe,
327       x_source_type,
328       x_source_reference,
329       x_attempted_credit_points,
330       x_earned_credit_points,
331       x_gpa,
332       x_gpa_credit_points,
333       x_gpa_quality_points,
334       x_creation_date,
335       x_created_by,
336       x_last_update_date,
337       x_last_updated_by,
338       x_last_update_login
339     );
340 
341     IF (p_action = 'INSERT') THEN
342       -- Call all the procedures related to Before Insert.
343       IF ( get_pk_for_validation(
344              new_references.person_id,
345              new_references.course_cd,
346              new_references.cal_type,
347              new_references.ci_sequence_number,
348              new_references.stat_type,
349              new_references.timeframe)
350          ) THEN
351         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
352         igs_ge_msg_stack.add;
353         app_exception.raise_exception;
354       END IF;
355     ELSIF (p_action = 'UPDATE') THEN
356       -- Call all the procedures related to Before Update.
357       check_parent_existance;
358     ELSIF (p_action = 'VALIDATE_INSERT') THEN
359       -- Call all the procedures related to Before Insert.
360       IF ( get_pk_for_validation(
361              new_references.person_id,
362              new_references.course_cd,
363              new_references.cal_type,
364              new_references.ci_sequence_number,
365              new_references.stat_type,
366              new_references.timeframe
367            )
368          ) THEN
369         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
370         igs_ge_msg_stack.add;
371         app_exception.raise_exception;
372       END IF;
373       ELSIF (p_action = 'VALIDATE_UPDATE') THEN
374       -- Call all the procedures related to Before Update.
375       check_parent_existance;
376       END IF;
377   END before_dml;
378 
379 
380   PROCEDURE insert_row (
381     x_rowid                             IN OUT NOCOPY VARCHAR2,
382     x_person_id                         IN     NUMBER,
383     x_course_cd                         IN     VARCHAR2,
384     x_cal_type                          IN     VARCHAR2,
385     x_ci_sequence_number                IN     NUMBER,
386     x_stat_type                         IN     VARCHAR2,
387     x_timeframe                         IN     VARCHAR2,
388     x_source_type                       IN     VARCHAR2,
389     x_source_reference                  IN     VARCHAR2,
390     x_attempted_credit_points           IN     NUMBER,
391     x_earned_credit_points              IN     NUMBER,
392     x_gpa                               IN     NUMBER,
393     x_gpa_credit_points                 IN     NUMBER,
394     x_gpa_quality_points                IN     NUMBER,
395     x_mode                              IN     VARCHAR2
396   ) AS
397   /*
398   ||  Created By : nmankodi
399   ||  Created On : 04-NOV-2002
400   ||  Purpose : Handles the INSERT DML logic for the table.
401   ||  Known limitations, enhancements or remarks :
402   ||  Change History :
403   ||  Who             When            What
404   ||  (reverse chronological order - newest change first)
405   */
406     CURSOR c IS
407       SELECT   rowid
408       FROM     igs_pr_stu_acad_stat
409       WHERE    person_id = x_person_id
410       AND      course_cd = x_course_cd
411       AND      cal_type = x_cal_type
412       AND      ci_sequence_number = x_ci_sequence_number
413       AND      stat_type = x_stat_type;
414 
415     x_last_update_date           DATE;
416     x_last_updated_by            NUMBER;
417     x_last_update_login          NUMBER;
418 
419   BEGIN
420 
421     x_last_update_date := SYSDATE;
422     IF (x_mode = 'I') THEN
423       x_last_updated_by := 1;
424       x_last_update_login := 0;
425     ELSIF (x_mode = 'R') THEN
426       x_last_updated_by := fnd_global.user_id;
427       IF (x_last_updated_by IS NULL) THEN
428         x_last_updated_by := -1;
429       END IF;
430       x_last_update_login := fnd_global.login_id;
431       IF (x_last_update_login IS NULL) THEN
432         x_last_update_login := -1;
433       END IF;
434     ELSE
435       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
436       igs_ge_msg_stack.add;
437       app_exception.raise_exception;
438     END IF;
439 
440     before_dml(
441       p_action                              => 'INSERT',
442       x_rowid                               => x_rowid,
443       x_person_id                           => x_person_id,
444       x_course_cd                           => x_course_cd ,
445       x_cal_type                            => x_cal_type,
446       x_ci_sequence_number                  => x_ci_sequence_number,
447       x_stat_type                           => x_stat_type,
448       x_timeframe                           => x_timeframe,
449       x_source_type                         => x_source_type,
450       x_source_reference                    => x_source_reference,
451       x_attempted_credit_points             => x_attempted_credit_points,
452       x_earned_credit_points                => x_earned_credit_points,
453       x_gpa                                 => x_gpa,
454       x_gpa_credit_points                   => x_gpa_credit_points,
455       x_gpa_quality_points                  => x_gpa_quality_points,
456       x_creation_date                       => x_last_update_date,
457       x_created_by                          => x_last_updated_by,
458       x_last_update_date                    => x_last_update_date,
459       x_last_updated_by                     => x_last_updated_by,
460       x_last_update_login                   => x_last_update_login
461     );
462 
463     INSERT INTO igs_pr_stu_acad_stat (
464       person_id,
465       course_cd,
466       cal_type,
467       ci_sequence_number,
468       stat_type,
469       timeframe,
470       source_type,
471       source_reference,
472       attempted_credit_points,
473       earned_credit_points,
474       gpa,
475       gpa_credit_points,
476       gpa_quality_points,
477       creation_date,
478       created_by,
479       last_update_date,
480       last_updated_by,
481       last_update_login
482     ) VALUES (
483       new_references.person_id,
484       new_references.course_cd,
485       new_references.cal_type,
486       new_references.ci_sequence_number,
487       new_references.stat_type,
488       new_references.timeframe,
489       new_references.source_type,
490       new_references.source_reference,
491       new_references.attempted_credit_points,
492       new_references.earned_credit_points,
493       new_references.gpa,
494       new_references.gpa_credit_points,
495       new_references.gpa_quality_points,
496       x_last_update_date,
497       x_last_updated_by,
498       x_last_update_date,
499       x_last_updated_by,
500       x_last_update_login
501     );
502 
503     OPEN c;
504     FETCH c INTO x_rowid;
505     IF (c%NOTFOUND) THEN
506       CLOSE c;
507       RAISE NO_DATA_FOUND;
508     END IF;
509     CLOSE c;
510 
511   END insert_row;
512 
513 
514   PROCEDURE lock_row (
515     x_rowid                             IN     VARCHAR2,
516     x_person_id                         IN     NUMBER,
517     x_course_cd                         IN     VARCHAR2,
518     x_cal_type                          IN     VARCHAR2,
519     x_ci_sequence_number                IN     NUMBER,
520     x_stat_type                         IN     VARCHAR2,
521     x_timeframe                         IN     VARCHAR2,
522     x_source_type                       IN     VARCHAR2,
523     x_source_reference                  IN     VARCHAR2,
524     x_attempted_credit_points           IN     NUMBER,
525     x_earned_credit_points              IN     NUMBER,
526     x_gpa                               IN     NUMBER,
527     x_gpa_credit_points                 IN     NUMBER,
528     x_gpa_quality_points                IN     NUMBER
529   ) AS
530   /*
531   ||  Created By : nmankodi
532   ||  Created On : 04-NOV-2002
533   ||  Purpose : Handles the LOCK mechanism for the table.
534   ||  Known limitations, enhancements or remarks :
535   ||  Change History :
536   ||  Who             When            What
537   ||  (reverse chronological order - newest change first)
538   */
539     CURSOR c1 IS
540       SELECT
541       timeframe,
542       source_type,
543       source_reference,
544       attempted_credit_points,
545       earned_credit_points,
546       gpa,
547       gpa_credit_points,
548       gpa_quality_points
549       FROM  igs_pr_stu_acad_stat
550       WHERE rowid = x_rowid
551       FOR UPDATE NOWAIT;
552 
553 
554     tlinfo c1%ROWTYPE;
555 
556   BEGIN
557 
558     OPEN c1;
559     FETCH c1 INTO tlinfo;
560     IF (c1%notfound) THEN
561       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
562       igs_ge_msg_stack.add;
563       CLOSE c1;
564       app_exception.raise_exception;
565       RETURN;
566     END IF;
567     CLOSE c1;
568 
569     IF (
570         (tlinfo.timeframe = x_timeframe)
571         AND (tlinfo.source_type = x_source_type)
572         AND (tlinfo.source_reference = x_source_reference)
573         AND (tlinfo.attempted_credit_points = x_attempted_credit_points )
574         AND (tlinfo.earned_credit_points = x_earned_credit_points )
575         AND (tlinfo.gpa = x_gpa )
576         AND (tlinfo.gpa_credit_points = x_gpa_credit_points )
577         AND (tlinfo.gpa_quality_points = x_gpa_quality_points )
578 
579        ) THEN
580       NULL;
581     ELSE
582       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
583       igs_ge_msg_stack.add;
584       app_exception.raise_exception;
585     END IF;
586 
587     RETURN;
588 
589   END lock_row;
590 
591 
592   PROCEDURE update_row (
593     x_rowid                             IN     VARCHAR2,
594     x_person_id                         IN     NUMBER,
595     x_course_cd                         IN     VARCHAR2,
596     x_cal_type                          IN     VARCHAR2,
597     x_ci_sequence_number                IN     NUMBER,
598     x_stat_type                         IN     VARCHAR2,
599     x_timeframe                         IN     VARCHAR2,
600     x_source_type                       IN     VARCHAR2,
601     x_source_reference                  IN     VARCHAR2,
602     x_attempted_credit_points           IN     NUMBER,
603     x_earned_credit_points              IN     NUMBER,
604     x_gpa                               IN     NUMBER,
605     x_gpa_credit_points                 IN     NUMBER,
606     x_gpa_quality_points                IN     NUMBER,
607     x_mode                              IN     VARCHAR2
608   ) AS
609   /*
610   ||  Created By : nmankodi
611   ||  Created On : 04-NOV-2002
612   ||  Purpose : Handles the UPDATE DML logic for the table.
613   ||  Known limitations, enhancements or remarks :
614   ||  Change History :
615   ||  Who             When            What
616   ||  (reverse chronological order - newest change first)
617   */
618     x_last_update_date           DATE ;
619     x_last_updated_by            NUMBER;
620     x_last_update_login          NUMBER;
621 
622   BEGIN
623 
624     x_last_update_date := SYSDATE;
625     IF (X_MODE = 'I') THEN
626       x_last_updated_by := 1;
627       x_last_update_login := 0;
628     ELSIF (x_mode = 'R') THEN
629       x_last_updated_by := fnd_global.user_id;
630       IF x_last_updated_by IS NULL THEN
631         x_last_updated_by := -1;
632       END IF;
633       x_last_update_login := fnd_global.login_id;
634       IF (x_last_update_login IS NULL) THEN
635         x_last_update_login := -1;
636       END IF;
637     ELSE
638       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
639       igs_ge_msg_stack.add;
640       app_exception.raise_exception;
641     END IF;
642 
643       before_dml(
644       p_action                              => 'UPDATE',
645       x_rowid                               => x_rowid,
646       x_person_id                           => x_person_id,
647       x_course_cd                           => x_course_cd ,
648       x_cal_type                            => x_cal_type,
649       x_ci_sequence_number                  => x_ci_sequence_number,
650       x_stat_type                           => x_stat_type,
651       x_timeframe                           => x_timeframe,
652       x_source_type                         => x_source_type,
653       x_source_reference                    => x_source_reference,
654       x_attempted_credit_points             => x_attempted_credit_points,
655       x_earned_credit_points                => x_earned_credit_points,
656       x_gpa                                 => x_gpa,
657       x_gpa_credit_points                   => x_gpa_credit_points,
658       x_gpa_quality_points                  => x_gpa_quality_points,
659       x_creation_date                       => x_last_update_date,
660       x_created_by                          => x_last_updated_by,
661       x_last_update_date                    => x_last_update_date,
662       x_last_updated_by                     => x_last_updated_by,
663       x_last_update_login                   => x_last_update_login
664     );
665 
666     UPDATE igs_pr_stu_acad_stat
667       SET
668       timeframe                 = new_references.timeframe,
669       source_type               = new_references.source_type,
670       source_reference          = new_references.source_reference,
671       attempted_credit_points   = new_references.attempted_credit_points,
672       earned_credit_points      = new_references.earned_credit_points,
673       gpa                       = new_references.gpa,
674       gpa_credit_points         = new_references.gpa_credit_points,
675       gpa_quality_points        = new_references.gpa_quality_points,
676       last_update_date          = x_last_update_date,
677       last_updated_by           = x_last_updated_by,
678       last_update_login         = x_last_update_login
679       WHERE rowid = x_rowid;
680 
681     IF (SQL%NOTFOUND) THEN
682       RAISE NO_DATA_FOUND;
683     END IF;
684 
685   END update_row;
686 
687 
688   PROCEDURE add_row (
689     x_rowid                             IN OUT NOCOPY VARCHAR2,
690     x_person_id                         IN     NUMBER,
691     x_course_cd                         IN     VARCHAR2,
692     x_cal_type                          IN     VARCHAR2,
693     x_ci_sequence_number                IN     NUMBER,
694     x_stat_type                         IN     VARCHAR2,
695     x_timeframe                         IN     VARCHAR2,
696     x_source_type                       IN     VARCHAR2,
697     x_source_reference                  IN     VARCHAR2,
698     x_attempted_credit_points           IN     NUMBER,
699     x_earned_credit_points              IN     NUMBER,
700     x_gpa                               IN     NUMBER,
701     x_gpa_credit_points                 IN     NUMBER,
702     x_gpa_quality_points                IN     NUMBER,
703     x_mode                              IN     VARCHAR2
704   ) AS
705   /*
706   ||  Created By : nmankodi
707   ||  Created On : 04-NOV-2002
708   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
709   ||  Known limitations, enhancements or remarks :
710   ||  Change History :
711   ||  Who             When            What
712   ||  (reverse chronological order - newest change first)
713   */
714     CURSOR c1 IS
715       SELECT   rowid
716       FROM     igs_pr_stu_acad_stat
717       WHERE    person_id = x_person_id
718       AND      course_cd = x_course_cd
719       AND      cal_type = x_cal_type
720       AND      ci_sequence_number = x_ci_sequence_number
721       AND      stat_type = x_stat_type
722       AND      timeframe = x_timeframe;
723 
724 
725   BEGIN
726 
727     OPEN c1;
728     FETCH c1 INTO x_rowid;
729     IF (c1%NOTFOUND) THEN
730       CLOSE c1;
731 
732       insert_row (
733     x_rowid,
734     x_person_id,
735     x_course_cd,
736     x_cal_type,
737     x_ci_sequence_number,
738     x_stat_type,
739     x_timeframe,
740     x_source_type,
741     x_source_reference,
742     x_attempted_credit_points,
743     x_earned_credit_points,
744     x_gpa,
745     x_gpa_credit_points,
746     x_gpa_quality_points,
747     x_mode
748       );
749       RETURN;
750     END IF;
751     CLOSE c1;
752 
753     update_row (
754     x_rowid,
755     x_person_id,
756     x_course_cd,
757     x_cal_type,
758     x_ci_sequence_number,
759     x_stat_type,
760     x_timeframe,
761     x_source_type,
762     x_source_reference,
763     x_attempted_credit_points,
764     x_earned_credit_points,
765     x_gpa,
766     x_gpa_credit_points,
767     x_gpa_quality_points,
768     x_mode
769     );
770 
771   END add_row;
772 
773 
774   PROCEDURE delete_row (
775     x_rowid IN VARCHAR2
776   ) AS
777   /*
778   ||  Created By : nmankodi
779   ||  Created On : 04-NOV-2002
780   ||  Purpose : Handles the DELETE DML logic for the table.
781   ||  Known limitations, enhancements or remarks :
782   ||  Change History :
783   ||  Who             When            What
784   ||  (reverse chronological order - newest change first)
785   */
786   BEGIN
787 
788     before_dml (
789       p_action => 'DELETE',
790       x_rowid => x_rowid
791     );
792 
793     DELETE FROM igs_pr_stu_acad_stat
794     WHERE rowid = x_rowid;
795 
796     IF (SQL%NOTFOUND) THEN
797       RAISE NO_DATA_FOUND;
798     END IF;
799 
800   END delete_row;
801 
802 
803 END igs_pr_stu_acad_stat_pkg;
804