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_HE_AD_DTL_ALL_PKG

Source


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