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_EN_SPL_PERM_H_PKG

Source


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