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_UC_COM_EBL_SUBJ_PKG

Source


1 PACKAGE BODY igs_uc_com_ebl_subj_pkg AS
2 /* $Header: IGSXI08B.pls 115.8 2003/06/11 10:30:19 smaddali noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_uc_com_ebl_subj%ROWTYPE;
6   new_references igs_uc_com_ebl_subj%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_subject_id                        IN     NUMBER  ,
12     x_year                              IN     NUMBER  ,
13     x_sitting                           IN     VARCHAR2,
14     x_awarding_body                     IN     VARCHAR2,
15     x_external_ref                      IN     VARCHAR2,
16     x_exam_level                        IN     VARCHAR2,
17     x_title                             IN     VARCHAR2,
18     x_subject_code                      IN     VARCHAR2,
19     x_imported                          IN     VARCHAR2,
20     x_creation_date                     IN     DATE    ,
21     x_created_by                        IN     NUMBER  ,
22     x_last_update_date                  IN     DATE    ,
23     x_last_updated_by                   IN     NUMBER  ,
24     x_last_update_login                 IN     NUMBER
25   ) AS
26   /*
27   ||  Created By : rgopalan
28   ||  Created On : 01-OCT-2001
29   ||  Purpose : Initialises the Old and New references for the columns of the table.
30   ||  Known limitations, enhancements or remarks :
31   ||  Change History :
32   ||  Who             When            What
33   ||  smaddali  10-jun-03    obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208 |
34   ||  (reverse chronological order - newest change first)
35   */
36 
37     CURSOR cur_old_ref_values IS
38       SELECT   *
39       FROM     IGS_UC_COM_EBL_SUBJ
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.subject_id                        := x_subject_id;
61     new_references.year                              := x_year;
62     new_references.sitting                           := x_sitting;
63     new_references.awarding_body                     := x_awarding_body;
64     new_references.external_ref                      := x_external_ref;
65     new_references.exam_level                        := x_exam_level;
66     new_references.title                             := x_title;
67     new_references.subject_code                      := x_subject_code;
68     new_references.imported                          := x_imported;
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_parent_existance AS
86   /*
87   ||  Created By : rgopalan
88   ||  Created On : 01-OCT-2001
89   ||  Purpose : Checks for the existance of Parent records.
90   ||  Known limitations, enhancements or remarks :
91   ||  Change History :
92   ||  Who             When            What
93   ||  (reverse chronological order - newest change first)
94   */
95   BEGIN
96 
97     IF (((old_references.awarding_body = new_references.awarding_body) AND
98          (old_references.sitting = new_references.sitting) AND
99          (old_references.year = new_references.year)) OR
100         ((new_references.awarding_body IS NULL) OR
101          (new_references.sitting IS NULL) OR
102          (new_references.year IS NULL))) THEN
103       NULL;
104     ELSIF NOT igs_uc_ref_awrdbdy_pkg.get_pk_for_validation (
105                 new_references.awarding_body,
106                 new_references.sitting,
107                 new_references.year
108               ) THEN
109       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
110       igs_ge_msg_stack.add;
111       app_exception.raise_exception;
112     END IF;
113 
114   END check_parent_existance;
115 
116 
117   PROCEDURE check_child_existance IS
118   /*
119   ||  Created By : rgopalan
120   ||  Created On : 01-OCT-2001
121   ||  Purpose : Checks for the existance of Child records.
122   ||  Known limitations, enhancements or remarks :
123   ||  Change History :
124   ||  Who             When            What
125   ||  (reverse chronological order - newest change first)
126   */
127   BEGIN
128 
129     igs_uc_app_results_pkg.get_fk_igs_uc_com_ebl_subj (
130       old_references.subject_id
131     );
132 
133   END check_child_existance;
134 
135 
136   FUNCTION get_pk_for_validation (
137     x_subject_id                        IN     NUMBER
138   ) RETURN BOOLEAN AS
139   /*
140   ||  Created By : rgopalan
141   ||  Created On : 01-OCT-2001
142   ||  Purpose : Validates the Primary Key of the table.
143   ||  Known limitations, enhancements or remarks :
144   ||  Change History :
145   ||  Who             When            What
146   ||  (reverse chronological order - newest change first)
147   */
148     CURSOR cur_rowid IS
149       SELECT   rowid
150       FROM     igs_uc_com_ebl_subj
151       WHERE    subject_id = x_subject_id ;
152 
153     lv_rowid cur_rowid%RowType;
154 
155   BEGIN
156 
157     OPEN cur_rowid;
158     FETCH cur_rowid INTO lv_rowid;
159     IF (cur_rowid%FOUND) THEN
160       CLOSE cur_rowid;
161       RETURN(TRUE);
162     ELSE
163       CLOSE cur_rowid;
164       RETURN(FALSE);
165     END IF;
166 
167   END get_pk_for_validation;
168 
169 
170   PROCEDURE get_fk_igs_uc_ref_awrdbdy (
171     x_awarding_body                     IN     VARCHAR2,
172     x_sitting                           IN     VARCHAR2,
173     x_year                              IN     NUMBER
174   ) AS
175   /*
176   ||  Created By : rgopalan
177   ||  Created On : 01-OCT-2001
178   ||  Purpose : Validates the Foreign Keys for the table.
179   ||  Known limitations, enhancements or remarks :
180   ||  Change History :
181   ||  Who             When            What
182   ||  (reverse chronological order - newest change first)
183   */
184     CURSOR cur_rowid IS
185       SELECT   rowid
186       FROM     igs_uc_com_ebl_subj
187       WHERE   ((awarding_body = x_awarding_body) AND
188                (sitting = x_sitting) AND
189                (year = x_year));
190 
191     lv_rowid cur_rowid%RowType;
192 
193   BEGIN
194 
195     OPEN cur_rowid;
196     FETCH cur_rowid INTO lv_rowid;
197     IF (cur_rowid%FOUND) THEN
198       CLOSE cur_rowid;
199       fnd_message.set_name ('IGS', 'IGS_UC_UCCOES_UCREAB_FK');
200       igs_ge_msg_stack.add;
201       app_exception.raise_exception;
202       RETURN;
203     END IF;
204     CLOSE cur_rowid;
205 
206   END get_fk_igs_uc_ref_awrdbdy;
207 
208 
209   PROCEDURE before_dml (
210     p_action                            IN     VARCHAR2,
211     x_rowid                             IN     VARCHAR2,
212     x_subject_id                        IN     NUMBER  ,
213     x_year                              IN     NUMBER  ,
214     x_sitting                           IN     VARCHAR2,
215     x_awarding_body                     IN     VARCHAR2,
216     x_external_ref                      IN     VARCHAR2,
217     x_exam_level                        IN     VARCHAR2,
218     x_title                             IN     VARCHAR2,
219     x_subject_code                      IN     VARCHAR2,
220     x_imported                          IN     VARCHAR2,
221     x_creation_date                     IN     DATE    ,
222     x_created_by                        IN     NUMBER  ,
223     x_last_update_date                  IN     DATE    ,
224     x_last_updated_by                   IN     NUMBER  ,
225     x_last_update_login                 IN     NUMBER
226   ) AS
227   /*
228   ||  Created By : rgopalan
229   ||  Created On : 01-OCT-2001
230   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
231   ||            Trigger Handlers for the table, before any DML operation.
232   ||  Known limitations, enhancements or remarks :
233   ||  Change History :
234   ||  Who             When            What
235   ||  smaddali  10-jun-03    obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208 |
236   ||  (reverse chronological order - newest change first)
237   */
238   BEGIN
239 
240     set_column_values (
241       p_action,
242       x_rowid,
243       x_subject_id,
244       x_year,
245       x_sitting,
246       x_awarding_body,
247       x_external_ref,
248       x_exam_level,
249       x_title,
250       x_subject_code,
251       x_imported,
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.subject_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 = 'DELETE') THEN
274       -- Call all the procedures related to Before Delete.
275       check_child_existance;
276     ELSIF (p_action = 'VALIDATE_INSERT') THEN
277       -- Call all the procedures related to Before Insert.
278       IF ( get_pk_for_validation (
279              new_references.subject_id
280            )
281          ) THEN
282         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
283         igs_ge_msg_stack.add;
284         app_exception.raise_exception;
285       END IF;
286     ELSIF (p_action = 'VALIDATE_DELETE') THEN
287       check_child_existance;
288     END IF;
289 
290   END before_dml;
291 
292 
293   PROCEDURE insert_row (
294     x_rowid                             IN OUT NOCOPY VARCHAR2,
295     x_subject_id                        IN OUT NOCOPY NUMBER,
296     x_year                              IN     NUMBER,
297     x_sitting                           IN     VARCHAR2,
298     x_awarding_body                     IN     VARCHAR2,
299     x_external_ref                      IN     VARCHAR2,
300     x_exam_level                        IN     VARCHAR2,
301     x_title                             IN     VARCHAR2,
302     x_subject_code                      IN     VARCHAR2,
303     x_imported                          IN     VARCHAR2,
304     x_mode                              IN     VARCHAR2
305   ) AS
306   /*
307   ||  Created By : rgopalan
308   ||  Created On : 01-OCT-2001
309   ||  Purpose : Handles the INSERT DML logic for the table.
310   ||  Known limitations, enhancements or remarks :
311   ||  Change History :
312   ||  Who             When            What
313   ||  smaddali  10-jun-03    obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208 |
314   ||  (reverse chronological order - newest change first)
315   */
316     CURSOR c IS
317       SELECT   rowid
318       FROM     igs_uc_com_ebl_subj
319       WHERE    subject_id                        = x_subject_id;
320 
321     x_last_update_date           DATE;
322     x_last_updated_by            NUMBER;
323     x_last_update_login          NUMBER;
324 
325   BEGIN
326 
327     x_last_update_date := SYSDATE;
328     IF (x_mode = 'I') THEN
329       x_last_updated_by := 1;
330       x_last_update_login := 0;
331     ELSIF (x_mode = 'R') THEN
332       x_last_updated_by := fnd_global.user_id;
333       IF (x_last_updated_by IS NULL) THEN
334         x_last_updated_by := -1;
335       END IF;
336       x_last_update_login := fnd_global.login_id;
337       IF (x_last_update_login IS NULL) THEN
338         x_last_update_login := -1;
339       END IF;
340     ELSE
341       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
342       igs_ge_msg_stack.add;
343       app_exception.raise_exception;
344     END IF;
345 
346     before_dml(
347       p_action                            => 'INSERT',
348       x_rowid                             => x_rowid,
349       x_subject_id                        => x_subject_id,
350       x_year                              => x_year,
351       x_sitting                           => x_sitting,
352       x_awarding_body                     => x_awarding_body,
353       x_external_ref                      => x_external_ref,
354       x_exam_level                        => x_exam_level,
355       x_title                             => x_title,
356       x_subject_code                      => x_subject_code,
357       x_imported                          => x_imported,
358       x_creation_date                     => x_last_update_date,
359       x_created_by                        => x_last_updated_by,
360       x_last_update_date                  => x_last_update_date,
361       x_last_updated_by                   => x_last_updated_by,
362       x_last_update_login                 => x_last_update_login
363     );
364 
365     INSERT INTO igs_uc_com_ebl_subj (
366       subject_id,
367       year,
368       sitting,
369       awarding_body,
370       external_ref,
371       exam_level,
372       title,
373       subject_code,
374       imported,
375       creation_date,
376       created_by,
377       last_update_date,
378       last_updated_by,
379       last_update_login
380     ) VALUES (
381       new_references.subject_id,
382       new_references.year,
383       new_references.sitting,
384       new_references.awarding_body,
385       new_references.external_ref,
386       new_references.exam_level,
387       new_references.title,
388       new_references.subject_code,
389       new_references.imported,
390       x_last_update_date,
391       x_last_updated_by,
392       x_last_update_date,
393       x_last_updated_by,
394       x_last_update_login
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   END insert_row;
406 
407 
408   PROCEDURE lock_row (
409     x_rowid                             IN     VARCHAR2,
410     x_subject_id                        IN     NUMBER,
411     x_year                              IN     NUMBER,
412     x_sitting                           IN     VARCHAR2,
413     x_awarding_body                     IN     VARCHAR2,
414     x_external_ref                      IN     VARCHAR2,
415     x_exam_level                        IN     VARCHAR2,
416     x_title                             IN     VARCHAR2,
417     x_subject_code                      IN     VARCHAR2,
418     x_imported                          IN     VARCHAR2
419   ) AS
420   /*
421   ||  Created By : rgopalan
422   ||  Created On : 01-OCT-2001
423   ||  Purpose : Handles the LOCK mechanism for the table.
424   ||  Known limitations, enhancements or remarks :
425   ||  Change History :
426   ||  Who             When            What
427   ||  smaddali  10-jun-03    obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208 |
428   ||  (reverse chronological order - newest change first)
429   */
430     CURSOR c1 IS
431       SELECT
432         year,
433         sitting,
434         awarding_body,
435         external_ref,
436         exam_level,
437         title,
438         subject_code,
439         imported
440       FROM  igs_uc_com_ebl_subj
441       WHERE rowid = x_rowid
442       FOR UPDATE NOWAIT;
443 
444     tlinfo c1%ROWTYPE;
445 
446   BEGIN
447 
448     OPEN c1;
449     FETCH c1 INTO tlinfo;
450     IF (c1%notfound) THEN
451       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
452       igs_ge_msg_stack.add;
453       CLOSE c1;
454       app_exception.raise_exception;
455       RETURN;
456     END IF;
457     CLOSE c1;
458 
459     IF (
460         (tlinfo.year = x_year)
461         AND (tlinfo.sitting = x_sitting)
462         AND (tlinfo.awarding_body = x_awarding_body)
463         AND ((tlinfo.external_ref = x_external_ref) OR ((tlinfo.external_ref IS NULL) AND (X_external_ref IS NULL)))
464         AND (tlinfo.exam_level = x_exam_level)
465         AND ((tlinfo.title = x_title) OR ((tlinfo.title IS NULL) AND (X_title IS NULL)))
466         AND (tlinfo.subject_code = x_subject_code)
467         AND (tlinfo.imported = x_imported)
468        ) THEN
469       NULL;
470     ELSE
471       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
472       igs_ge_msg_stack.add;
473       app_exception.raise_exception;
474     END IF;
475 
476     RETURN;
477 
478   END lock_row;
479 
480 
481   PROCEDURE update_row (
482     x_rowid                             IN     VARCHAR2,
483     x_subject_id                        IN     NUMBER,
484     x_year                              IN     NUMBER,
485     x_sitting                           IN     VARCHAR2,
486     x_awarding_body                     IN     VARCHAR2,
487     x_external_ref                      IN     VARCHAR2,
488     x_exam_level                        IN     VARCHAR2,
489     x_title                             IN     VARCHAR2,
490     x_subject_code                      IN     VARCHAR2,
491     x_imported                          IN     VARCHAR2,
492     x_mode                              IN     VARCHAR2
493   ) AS
494   /*
495   ||  Created By : rgopalan
496   ||  Created On : 01-OCT-2001
497   ||  Purpose : Handles the UPDATE DML logic for the table.
498   ||  Known limitations, enhancements or remarks :
499   ||  Change History :
500   ||  Who             When            What
501   ||  smaddali  10-jun-03    obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208 |
502   ||  (reverse chronological order - newest change first)
503   */
504     x_last_update_date           DATE ;
505     x_last_updated_by            NUMBER;
506     x_last_update_login          NUMBER;
507 
508   BEGIN
509 
510     x_last_update_date := SYSDATE;
511     IF (X_MODE = 'I') THEN
512       x_last_updated_by := 1;
513       x_last_update_login := 0;
514     ELSIF (x_mode = 'R') THEN
515       x_last_updated_by := fnd_global.user_id;
516       IF x_last_updated_by IS NULL THEN
517         x_last_updated_by := -1;
518       END IF;
519       x_last_update_login := fnd_global.login_id;
520       IF (x_last_update_login IS NULL) THEN
521         x_last_update_login := -1;
522       END IF;
523     ELSE
524       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
525       igs_ge_msg_stack.add;
526       app_exception.raise_exception;
527     END IF;
528 
529     before_dml(
530       p_action                            => 'UPDATE',
531       x_rowid                             => x_rowid,
532       x_subject_id                        => x_subject_id,
533       x_year                              => x_year,
534       x_sitting                           => x_sitting,
535       x_awarding_body                     => x_awarding_body,
536       x_external_ref                      => x_external_ref,
537       x_exam_level                        => x_exam_level,
538       x_title                             => x_title,
539       x_subject_code                      => x_subject_code,
540       x_imported                          => x_imported,
541       x_creation_date                     => x_last_update_date,
542       x_created_by                        => x_last_updated_by,
543       x_last_update_date                  => x_last_update_date,
544       x_last_updated_by                   => x_last_updated_by,
545       x_last_update_login                 => x_last_update_login
546     );
547 
548     UPDATE igs_uc_com_ebl_subj
549       SET
550         year                              = new_references.year,
551         sitting                           = new_references.sitting,
552         awarding_body                     = new_references.awarding_body,
553         external_ref                      = new_references.external_ref,
554         exam_level                        = new_references.exam_level,
555         title                             = new_references.title,
556         subject_code                      = new_references.subject_code,
557         imported                          = new_references.imported,
558         last_update_date                  = x_last_update_date,
559         last_updated_by                   = x_last_updated_by,
560         last_update_login                 = x_last_update_login
561       WHERE rowid = x_rowid;
562 
563     IF (SQL%NOTFOUND) THEN
564       RAISE NO_DATA_FOUND;
565     END IF;
566 
567   END update_row;
568 
569 
570   PROCEDURE add_row (
571     x_rowid                             IN OUT NOCOPY VARCHAR2,
572     x_subject_id                        IN OUT NOCOPY NUMBER,
573     x_year                              IN     NUMBER,
574     x_sitting                           IN     VARCHAR2,
575     x_awarding_body                     IN     VARCHAR2,
576     x_external_ref                      IN     VARCHAR2,
577     x_exam_level                        IN     VARCHAR2,
578     x_title                             IN     VARCHAR2,
579     x_subject_code                      IN     VARCHAR2,
580     x_imported                          IN     VARCHAR2,
581     x_mode                              IN     VARCHAR2
582   ) AS
583   /*
584   ||  Created By : rgopalan
585   ||  Created On : 01-OCT-2001
586   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
587   ||  Known limitations, enhancements or remarks :
588   ||  Change History :
589   ||  Who             When            What
590   ||  smaddali  10-jun-03    obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208 |
591   ||  (reverse chronological order - newest change first)
592   */
593     CURSOR c1 IS
594       SELECT   rowid
595       FROM     igs_uc_com_ebl_subj
596       WHERE    subject_id                        = x_subject_id;
597 
598   BEGIN
599 
600     OPEN c1;
601     FETCH c1 INTO x_rowid;
602     IF (c1%NOTFOUND) THEN
603       CLOSE c1;
604 
605       insert_row (
606         x_rowid,
607         x_subject_id,
608         x_year,
609         x_sitting,
610         x_awarding_body,
611         x_external_ref,
612         x_exam_level,
613         x_title,
614         x_subject_code,
615         x_imported,
616         x_mode
617       );
618       RETURN;
619     END IF;
620     CLOSE c1;
621 
622     update_row (
623       x_rowid,
624       x_subject_id,
625       x_year,
626       x_sitting,
627       x_awarding_body,
628       x_external_ref,
629       x_exam_level,
630       x_title,
631       x_subject_code,
632       x_imported,
633       x_mode
634     );
635 
636   END add_row;
637 
638 
639   PROCEDURE delete_row (
640     x_rowid IN VARCHAR2
641   ) AS
642   /*
643   ||  Created By : rgopalan
644   ||  Created On : 01-OCT-2001
645   ||  Purpose : Handles the DELETE DML logic for the table.
646   ||  Known limitations, enhancements or remarks :
647   ||  Change History :
648   ||  Who             When            What
649   ||  (reverse chronological order - newest change first)
650   */
651   BEGIN
652 
653     before_dml (
654       p_action => 'DELETE',
655       x_rowid => x_rowid
656     );
657 
658     DELETE FROM igs_uc_com_ebl_subj
659     WHERE rowid = x_rowid;
660 
661     IF (SQL%NOTFOUND) THEN
662       RAISE NO_DATA_FOUND;
663     END IF;
664 
665   END delete_row;
666 
667 
668 END igs_uc_com_ebl_subj_pkg;