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_CRSE_KEYWRDS_PKG

Source


1 PACKAGE BODY igs_uc_crse_keywrds_pkg AS
2 /* $Header: IGSXI15B.pls 120.1 2005/09/27 19:34:33 appldev ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_uc_crse_keywrds%ROWTYPE;
6   new_references igs_uc_crse_keywrds%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    ,
11     x_ucas_program_code                 IN     VARCHAR2    ,
12     x_institute                         IN     VARCHAR2    ,
13     x_ucas_campus                       IN     VARCHAR2    ,
14     x_option_code                       IN     VARCHAR2    ,
15     x_preference                        IN     NUMBER      ,
16     x_keyword                           IN     VARCHAR2    ,
17     x_updater                           IN     VARCHAR2    ,
18     x_active                            IN     VARCHAR2    ,
19     x_deleted                           IN     VARCHAR2    ,
20     x_sent_to_ucas                      IN     VARCHAR2    ,
21     x_system_code                       IN     VARCHAR2,
22     x_crse_keyword_id                   IN     NUMBER,
23     x_creation_date                     IN     DATE        ,
24     x_created_by                        IN     NUMBER      ,
25     x_last_update_date                  IN     DATE        ,
26     x_last_updated_by                   IN     NUMBER      ,
27     x_last_update_login                 IN     NUMBER
28   ) AS
29   /*
30   ||  Created By : rgopalan
31   ||  Created On : 23-OCT-2001
32   ||  Purpose : Initialises the Old and New references for the columns of the table.
33   ||  Known limitations, enhancements or remarks :
34   ||  Change History :
35   ||  Who             When            What
36   || smaddali 10-jun-03  Obsoleting datetimestamp column for ucfd203 -multiple cycled build ,bug#2669208
37   ||  (reverse chronological order - newest change first)
38   */
39 
40     CURSOR cur_old_ref_values IS
41       SELECT   *
42       FROM     IGS_UC_CRSE_KEYWRDS
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.ucas_program_code                 := x_ucas_program_code;
64     new_references.institute                         := x_institute;
65     new_references.ucas_campus                       := x_ucas_campus;
66     new_references.option_code                       := x_option_code;
67     new_references.preference                        := x_preference;
68     new_references.keyword                           := x_keyword;
69     new_references.updater                           := x_updater;
70     new_references.active                            := x_active;
71     new_references.deleted                           := x_deleted;
72     new_references.sent_to_ucas                      := x_sent_to_ucas;
73     new_references.system_code                       := x_system_code;
74     new_references.crse_keyword_id                   := x_crse_keyword_id;
75 
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 : rgopalan
95   ||  Created On : 23-OCT-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.keyword = new_references.keyword)) OR
105         ((new_references.keyword IS NULL))) THEN
106       NULL;
107     ELSIF NOT igs_uc_ref_keywords_pkg.get_pk_for_validation (
108                 new_references.keyword
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.system_code = new_references.system_code) AND
116          (old_references.institute = new_references.institute) AND
117          (old_references.ucas_campus = new_references.ucas_campus)) OR
118         ((new_references.ucas_program_code IS NULL) OR
119          (new_references.institute IS NULL) OR
120          (new_references.system_code IS NULL) OR
121          (new_references.ucas_campus IS NULL))) THEN
122       NULL;
123     ELSIF NOT igs_uc_crse_dets_pkg.get_pk_for_validation (
124                 new_references.ucas_program_code,
125                 new_references.institute,
126                 new_references.ucas_campus ,
127                 new_references.system_code
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   END check_parent_existance;
135 
136   PROCEDURE check_uniqueness AS
137   /*
138   ||  Created By : [email protected]
139   ||  Created On : 17-SEP-2002
140   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
141   ||  Known limitations, enhancements or remarks :
142   ||  Change History :
143   ||  Who             When            What
144   ||  rgangara       16-APR-04     Bug#3496874. Passing Preference instead of Keyword
145   ||                               for get_uk_for_validation.
146   ||  (reverse chronological order - newest change first)
147   */
148   BEGIN
149 
150     IF ( get_uk_for_validation (
151            new_references.ucas_program_code  ,
152            new_references.institute,
153            new_references.ucas_campus  ,
154            new_references.option_code  ,
155            new_references.system_code  ,
156            new_references.keyword )
157        ) THEN
158       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
159       igs_ge_msg_stack.add;
160       app_exception.raise_exception;
161     END IF;
162 
163   END check_uniqueness;
164 
165 
166 
167 
168   FUNCTION get_uk_for_validation (
169     x_ucas_program_code                 IN     VARCHAR2,
170     x_institute                         IN     VARCHAR2,
171     x_ucas_campus                       IN     VARCHAR2,
172     x_option_code                       IN     VARCHAR2,
173     x_system_code                       IN     VARCHAR2,
174     x_keyword                           IN     VARCHAR2
175   ) RETURN BOOLEAN AS
176   /*
177   ||  Created By : rgopalan
178   ||  Created On : 23-OCT-2001
179   ||  Purpose : Validates the Primary Key of the table.
180   ||  Known limitations, enhancements or remarks :
181   ||  Change History :
182   ||  Who             When            What
183   ||  rgangara       16-APR-04     Bug#3496874. Def changed to get Preference instead of Keyword
184   ||                               for get_uk_for_validation.
185   ||  (reverse chronological order - newest change first)
186   */
187     CURSOR cur_rowid IS
188       SELECT   rowid
189       FROM     igs_uc_crse_keywrds
190       WHERE    ucas_program_code = x_ucas_program_code
191       AND      institute = x_institute
192       AND         ucas_campus = x_ucas_campus
193       AND  ( (  x_option_code IS NOT NULL AND  option_code = x_option_code ) OR
194              (   x_option_code IS NULL) )
195       AND      system_code = x_system_code
196       AND      keyword = x_keyword
197       AND     ((l_rowid IS NULL) OR (rowid <> l_rowid));
198 
199     lv_rowid cur_rowid%RowType;
200 
201   BEGIN
202 
203     OPEN cur_rowid;
204     FETCH cur_rowid INTO lv_rowid;
205     IF (cur_rowid%FOUND) THEN
206       CLOSE cur_rowid;
207       RETURN(TRUE);
208     ELSE
209       CLOSE cur_rowid;
210       RETURN(FALSE);
211     END IF;
212 
213   END get_uk_for_validation;
214 
215 
216 
217   FUNCTION get_pk_for_validation (
218     x_crse_keyword_id  IN NUMBER
219   ) RETURN BOOLEAN AS
220   /*
221   ||  Created By :bayadav
222   ||  Created On : 23-OCT-2001
223   ||  Purpose : Validates the Primary Key of the table.
224   ||  Known limitations, enhancements or remarks :
225   ||  Change History :
226   ||  Who             When            What
227   ||  (reverse chronological order - newest change first)
228   */
229     CURSOR cur_rowid IS
230       SELECT   rowid
231       FROM     igs_uc_crse_keywrds
232       WHERE    crse_keyword_id = x_crse_keyword_id ;
233 
234     lv_rowid cur_rowid%RowType;
235 
236   BEGIN
237 
238     OPEN cur_rowid;
239     FETCH cur_rowid INTO lv_rowid;
240     IF (cur_rowid%FOUND) THEN
241       CLOSE cur_rowid;
242       RETURN(TRUE);
243     ELSE
244       CLOSE cur_rowid;
245       RETURN(FALSE);
246     END IF;
247 
248   END get_pk_for_validation;
249 
250 
251 
252 
253   PROCEDURE get_fk_igs_uc_ref_keywords (
254     x_keyword                           IN     VARCHAR2
255   ) AS
256   /*
257   ||  Created By : rgopalan
258   ||  Created On : 23-OCT-2001
259   ||  Purpose : Validates the Foreign Keys for the table.
260   ||  Known limitations, enhancements or remarks :
261   ||  Change History :
262   ||  Who             When            What
263   ||  (reverse chronological order - newest change first)
264   */
265     CURSOR cur_rowid IS
266       SELECT   rowid
267       FROM     igs_uc_crse_keywrds
268       WHERE   ((keyword = x_keyword));
269 
270     lv_rowid cur_rowid%RowType;
271 
272   BEGIN
273 
274     OPEN cur_rowid;
275     FETCH cur_rowid INTO lv_rowid;
276     IF (cur_rowid%FOUND) THEN
277       CLOSE cur_rowid;
278       fnd_message.set_name ('IGS', 'IGS_UC_UCCSKW_UCREKW_FK');
279       igs_ge_msg_stack.add;
280       app_exception.raise_exception;
281       RETURN;
282     END IF;
283     CLOSE cur_rowid;
284 
285   END get_fk_igs_uc_ref_keywords;
286 
287 
288   PROCEDURE get_fk_igs_uc_crse_dets (
289     x_ucas_program_code                 IN     VARCHAR2,
290     x_institute                         IN     VARCHAR2,
291     x_ucas_campus                       IN     VARCHAR2,
292     x_system_code                       IN     VARCHAR2
293   ) AS
294   /*
295   ||  Created By : rgopalan
296   ||  Created On : 23-OCT-2001
297   ||  Purpose : Validates the Foreign Keys for the table.
298   ||  Known limitations, enhancements or remarks :
299   ||  Change History :
300   ||  Who             When            What
301   ||  (reverse chronological order - newest change first)
302   */
303     CURSOR cur_rowid IS
304       SELECT   rowid
305       FROM     igs_uc_crse_keywrds
306       WHERE   ((institute = x_institute) AND
307                (ucas_campus = x_ucas_campus) AND
308                (ucas_program_code = x_ucas_program_code)  AND
309                (system_code = x_system_code));
310 
311     lv_rowid cur_rowid%RowType;
312 
313   BEGIN
314 
315     OPEN cur_rowid;
316     FETCH cur_rowid INTO lv_rowid;
317     IF (cur_rowid%FOUND) THEN
318       CLOSE cur_rowid;
319       fnd_message.set_name ('IGS', 'IGS_UC_UCCSKW_UCCSDE_FK');
320       igs_ge_msg_stack.add;
321       app_exception.raise_exception;
322       RETURN;
323     END IF;
324     CLOSE cur_rowid;
325 
326   END get_fk_igs_uc_crse_dets;
327 
328 
329   PROCEDURE before_dml (
330     p_action                            IN     VARCHAR2,
331     x_rowid                             IN     VARCHAR2    ,
332     x_ucas_program_code                 IN     VARCHAR2    ,
333     x_institute                         IN     VARCHAR2    ,
334     x_ucas_campus                       IN     VARCHAR2    ,
335     x_option_code                       IN     VARCHAR2    ,
336     x_preference                        IN     NUMBER      ,
337     x_keyword                           IN     VARCHAR2    ,
338     x_updater                           IN     VARCHAR2    ,
339     x_active                            IN     VARCHAR2    ,
340     x_deleted                           IN     VARCHAR2    ,
341     x_sent_to_ucas                      IN     VARCHAR2    ,
342     x_system_code                       IN     VARCHAR2,
343     x_crse_keyword_id                   IN     NUMBER,
344     x_creation_date                     IN     DATE        ,
345     x_created_by                        IN     NUMBER      ,
346     x_last_update_date                  IN     DATE        ,
347     x_last_updated_by                   IN     NUMBER      ,
348     x_last_update_login                 IN     NUMBER
349   ) AS
350   /*
351   ||  Created By : rgopalan
352   ||  Created On : 23-OCT-2001
353   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
354   ||            Trigger Handlers for the table, before any DML operation.
355   ||  Known limitations, enhancements or remarks :
356   ||  Change History :
357   ||  Who             When            What
358   || smaddali 10-jun-03  Obsoleting datetimestamp column for ucfd203 -multiple cycled build ,bug#2669208
359   ||  (reverse chronological order - newest change first)
360   */
361   BEGIN
362 
363     set_column_values (
364       p_action,
365       x_rowid,
366       x_ucas_program_code,
367       x_institute,
368       x_ucas_campus,
369       x_option_code,
370       x_preference,
371       x_keyword,
372       x_updater,
373       x_active,
374       x_deleted,
375       x_sent_to_ucas,
376       x_system_code,
377       x_crse_keyword_id ,
378       x_creation_date,
379       x_created_by,
380       x_last_update_date,
381       x_last_updated_by,
382       x_last_update_login
383     );
384 
385     IF (p_action = 'INSERT') THEN
386       -- Call all the procedures related to Before Insert.
387       IF ( get_pk_for_validation(
388              new_references.crse_keyword_id
389            )
390          ) THEN
391         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
392         igs_ge_msg_stack.add;
393         app_exception.raise_exception;
394       END IF;
395       check_parent_existance;
396        check_uniqueness ;
397     ELSIF (p_action = 'UPDATE') THEN
398       -- Call all the procedures related to Before Update.
399       check_parent_existance;
400        check_uniqueness ;
401     ELSIF (p_action = 'VALIDATE_INSERT') THEN
402       -- Call all the procedures related to Before Insert.
403       IF ( get_pk_for_validation (
404               new_references.crse_keyword_id
405               )
406          ) THEN
407         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
408         igs_ge_msg_stack.add;
409         app_exception.raise_exception;
410       END IF;
411     END IF;
412 
413   END before_dml;
414 
415 
416   PROCEDURE insert_row (
417     x_rowid                             IN OUT NOCOPY VARCHAR2,
418     x_ucas_program_code                 IN     VARCHAR2,
419     x_institute                         IN     VARCHAR2,
420     x_ucas_campus                       IN     VARCHAR2,
421     x_option_code                       IN     VARCHAR2,
422     x_preference                        IN     NUMBER,
423     x_keyword                           IN     VARCHAR2,
424     x_updater                           IN     VARCHAR2,
425     x_active                            IN     VARCHAR2,
426     x_deleted                           IN     VARCHAR2,
427     x_sent_to_ucas                      IN     VARCHAR2,
428     x_system_code                       IN     VARCHAR2,
429     x_crse_keyword_id                   IN   OUT NOCOPY  NUMBER,
430     x_mode                              IN     VARCHAR2
431   ) AS
432   /*
433   ||  Created By : rgopalan
434   ||  Created On : 23-OCT-2001
435   ||  Purpose : Handles the INSERT DML logic for the table.
436   ||  Known limitations, enhancements or remarks :
437   ||  Change History :
438   ||  Who             When            What
439   || smaddali 10-jun-03  Obsoleting datetimestamp column for ucfd203 -multiple cycled build ,bug#2669208
440   ||  (reverse chronological order - newest change first)
441   */
442     CURSOR c IS
443       SELECT   rowid
444       FROM     igs_uc_crse_keywrds
445       WHERE    crse_keyword_id                = x_crse_keyword_id;
446 
447     CURSOR c_keyword IS
448     SELECT    igs_uc_crse_keywrds_s.NEXTVAL
449     FROM      dual;
450 
451     x_last_update_date           DATE;
452     x_last_updated_by            NUMBER;
453     x_last_update_login          NUMBER;
454 
455 
456 
457   BEGIN
458 
459     x_last_update_date := SYSDATE;
460     IF (x_mode = 'I') THEN
461       x_last_updated_by := 1;
462       x_last_update_login := 0;
463     ELSIF (x_mode = 'R') THEN
464       x_last_updated_by := fnd_global.user_id;
465       IF (x_last_updated_by IS NULL) THEN
466         x_last_updated_by := -1;
467       END IF;
468       x_last_update_login := fnd_global.login_id;
469       IF (x_last_update_login IS NULL) THEN
470         x_last_update_login := -1;
471       END IF;
472     ELSE
473       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
474       igs_ge_msg_stack.add;
475       app_exception.raise_exception;
476     END IF;
477 
478      OPEN c_keyword;
479      FETCH  c_keyword INTO x_crse_keyword_id;
480      CLOSE  c_keyword;
481 
482 
483 
484 
485     before_dml(
486       p_action                            => 'INSERT',
487       x_rowid                             => x_rowid,
488       x_ucas_program_code                 => x_ucas_program_code,
489       x_institute                         => x_institute,
490       x_ucas_campus                       => x_ucas_campus,
491       x_option_code                       => x_option_code,
492       x_preference                        => x_preference,
493       x_keyword                           => x_keyword,
494       x_updater                           => x_updater,
495       x_active                            => x_active,
496       x_deleted                           => NVL (x_deleted,'N' ),
497       x_sent_to_ucas                      => x_sent_to_ucas,
498       x_system_code                       =>  x_system_code,
499       x_crse_keyword_id                   =>  x_crse_keyword_id,
500       x_creation_date                     => x_last_update_date,
501       x_created_by                        => x_last_updated_by,
502       x_last_update_date                  => x_last_update_date,
503       x_last_updated_by                   => x_last_updated_by,
504       x_last_update_login                 => x_last_update_login
505     );
506 
507     INSERT INTO igs_uc_crse_keywrds (
508       ucas_program_code,
509       institute,
510       ucas_campus,
511       option_code,
512       preference,
513       keyword,
514       updater,
515       active,
516       deleted,
517       sent_to_ucas,
518       system_code ,
519       crse_keyword_id,
520       creation_date,
521       created_by,
522       last_update_date,
523       last_updated_by,
524       last_update_login
525     ) VALUES (
526       new_references.ucas_program_code,
527       new_references.institute,
528       new_references.ucas_campus,
529       new_references.option_code,
530       new_references.preference,
531       new_references.keyword,
532       new_references.updater,
533       new_references.active,
534       new_references.deleted,
535       new_references.sent_to_ucas,
536       new_references.system_code  ,
537       new_references.crse_keyword_id ,
538       x_last_update_date,
539       x_last_updated_by,
540       x_last_update_date,
541       x_last_updated_by,
542       x_last_update_login
543     );
544 
545     OPEN c;
546     FETCH c INTO x_rowid;
547     IF (c%NOTFOUND) THEN
548       CLOSE c;
549       RAISE NO_DATA_FOUND;
550     END IF;
551     CLOSE c;
552 
553   END insert_row;
554 
555 
556   PROCEDURE lock_row (
557     x_rowid                             IN     VARCHAR2,
558     x_ucas_program_code                 IN     VARCHAR2,
559     x_institute                         IN     VARCHAR2,
560     x_ucas_campus                       IN     VARCHAR2,
561     x_option_code                       IN     VARCHAR2,
562     x_preference                        IN     NUMBER,
563     x_keyword                           IN     VARCHAR2,
564     x_updater                           IN     VARCHAR2,
565     x_active                            IN     VARCHAR2,
566     x_deleted                           IN     VARCHAR2,
567     x_sent_to_ucas                      IN     VARCHAR2,
568     x_system_code                       IN     VARCHAR2,
569     x_crse_keyword_id                   IN     NUMBER
570   ) AS
571   /*
572   ||  Created By : rgopalan
573   ||  Created On : 23-OCT-2001
574   ||  Purpose : Handles the LOCK mechanism for the table.
575   ||  Known limitations, enhancements or remarks :
576   ||  Change History :
577   ||  Who             When            What
578   || smaddali 10-jun-03  Obsoleting datetimestamp column for ucfd203 -multiple cycled build ,bug#2669208
579   ||  (reverse chronological order - newest change first)
580   */
581     CURSOR c1 IS
582       SELECT
583         preference,
584         option_code,
585         updater,
586         active,
587         deleted,
588         sent_to_ucas,
589         system_code,
590         ucas_program_code,
591         institute,
592         ucas_campus,
593         keyword
594       FROM  igs_uc_crse_keywrds
595       WHERE rowid = x_rowid
596       FOR UPDATE NOWAIT;
597 
598     tlinfo c1%ROWTYPE;
599 
600   BEGIN
601 
602     OPEN c1;
603     FETCH c1 INTO tlinfo;
604     IF (c1%notfound) THEN
605       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
606       igs_ge_msg_stack.add;
607       CLOSE c1;
608       app_exception.raise_exception;
609       RETURN;
610     END IF;
611     CLOSE c1;
612 
613     IF (
614             ((tlinfo.preference = x_preference) OR ((tlinfo.preference IS NULL) AND (x_preference IS NULL)))
615         AND ((tlinfo.system_code = x_system_code) )
616         AND ((tlinfo.ucas_program_code = x_ucas_program_code) )
617         AND ((tlinfo.institute = x_institute) )
618         AND ((tlinfo.ucas_campus = x_ucas_campus) )
619         AND ((tlinfo.keyword = x_keyword) )
620         AND ((tlinfo.option_code = x_option_code) )
621         AND (tlinfo.updater = x_updater)
622         AND (tlinfo.active = x_active)
623         AND (tlinfo.deleted = x_deleted)
624         AND (tlinfo.sent_to_ucas = x_sent_to_ucas)
625        ) THEN
626       NULL;
627     ELSE
628       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
629       igs_ge_msg_stack.add;
630       app_exception.raise_exception;
631     END IF;
632 
633     RETURN;
634 
635   END lock_row;
636 
637 
638   PROCEDURE update_row (
639     x_rowid                             IN     VARCHAR2,
640     x_ucas_program_code                 IN     VARCHAR2,
641     x_institute                         IN     VARCHAR2,
642     x_ucas_campus                       IN     VARCHAR2,
643     x_option_code                       IN     VARCHAR2,
644     x_preference                        IN     NUMBER,
645     x_keyword                           IN     VARCHAR2,
646     x_updater                           IN     VARCHAR2,
647     x_active                            IN     VARCHAR2,
648     x_deleted                           IN     VARCHAR2,
649     x_sent_to_ucas                      IN     VARCHAR2,
650     x_system_code                       IN     VARCHAR2,
651     x_crse_keyword_id                   IN     NUMBER,
652     x_mode                              IN     VARCHAR2
653   ) AS
654   /*
655   ||  Created By : rgopalan
656   ||  Created On : 23-OCT-2001
657   ||  Purpose : Handles the UPDATE DML logic for the table.
658   ||  Known limitations, enhancements or remarks :
659   ||  Change History :
660   ||  Who             When            What
661   || smaddali 10-jun-03  Obsoleting datetimestamp column for ucfd203 -multiple cycled build ,bug#2669208
662   ||  (reverse chronological order - newest change first)
663   */
664     x_last_update_date           DATE ;
665     x_last_updated_by            NUMBER;
666     x_last_update_login          NUMBER;
667 
668   BEGIN
669 
670     x_last_update_date := SYSDATE;
671     IF (X_MODE = 'I') THEN
672       x_last_updated_by := 1;
673       x_last_update_login := 0;
674     ELSIF (x_mode = 'R') THEN
675       x_last_updated_by := fnd_global.user_id;
676       IF x_last_updated_by IS NULL THEN
677         x_last_updated_by := -1;
678       END IF;
679       x_last_update_login := fnd_global.login_id;
680       IF (x_last_update_login IS NULL) THEN
681         x_last_update_login := -1;
682       END IF;
683     ELSE
684       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
685       igs_ge_msg_stack.add;
686       app_exception.raise_exception;
687     END IF;
688 
689     before_dml(
690       p_action                            => 'UPDATE',
691       x_rowid                             => x_rowid,
692       x_ucas_program_code                 => x_ucas_program_code,
693       x_institute                         => x_institute,
694       x_ucas_campus                       => x_ucas_campus,
695       x_option_code                       => x_option_code,
696       x_preference                        => x_preference,
697       x_keyword                           => x_keyword,
698       x_updater                           => x_updater,
699       x_active                            => x_active,
700       x_deleted                           => NVL (x_deleted,'N' ),
701       x_sent_to_ucas                      => x_sent_to_ucas,
702       x_system_code                       => x_system_code ,
703       x_crse_keyword_id                   => x_crse_keyword_id,
704       x_creation_date                     => x_last_update_date,
705       x_created_by                        => x_last_updated_by,
706       x_last_update_date                  => x_last_update_date,
707       x_last_updated_by                   => x_last_updated_by,
708       x_last_update_login                 => x_last_update_login
709     );
710 
711     UPDATE igs_uc_crse_keywrds
712       SET
713         preference                        = new_references.preference,
714         updater                           = new_references.updater,
715         active                            = new_references.active,
716         deleted                           = new_references.deleted,
717         sent_to_ucas                      = new_references.sent_to_ucas,
718         system_code                       = new_references.system_code,
719         ucas_program_code                 = new_references.ucas_program_code,
720         institute                         = new_references.institute,
721         option_code                       = new_references.option_code,
722         ucas_campus                       = new_references.ucas_campus ,
723         keyword                           = new_references.keyword ,
724         last_update_date                  = x_last_update_date,
725         last_updated_by                   = x_last_updated_by,
726         last_update_login                 = x_last_update_login
727       WHERE rowid = x_rowid;
728 
729     IF (SQL%NOTFOUND) THEN
730       RAISE NO_DATA_FOUND;
731     END IF;
732 
733   END update_row;
734 
735 
736   PROCEDURE add_row (
737     x_rowid                             IN OUT NOCOPY VARCHAR2,
738     x_ucas_program_code                 IN     VARCHAR2,
739     x_institute                         IN     VARCHAR2,
740     x_ucas_campus                       IN     VARCHAR2,
741     x_option_code                       IN     VARCHAR2,
742     x_preference                        IN     NUMBER,
743     x_keyword                           IN     VARCHAR2,
744     x_updater                           IN     VARCHAR2,
745     x_active                            IN     VARCHAR2,
746     x_deleted                           IN     VARCHAR2,
747     x_sent_to_ucas                      IN     VARCHAR2,
748     x_system_code                       IN     VARCHAR2,
749     x_crse_keyword_id                   IN OUT NOCOPY    NUMBER,
750     x_mode                              IN     VARCHAR2
751   ) AS
752   /*
753   ||  Created By : rgopalan
754   ||  Created On : 23-OCT-2001
755   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
756   ||  Known limitations, enhancements or remarks :
757   ||  Change History :
758   ||  Who             When            What
759   || smaddali 10-jun-03  Obsoleting datetimestamp column for ucfd203 -multiple cycled build ,bug#2669208
760   ||  (reverse chronological order - newest change first)
761   */
762     CURSOR c1 IS
763       SELECT   rowid
764       FROM     igs_uc_crse_keywrds
765       WHERE    crse_keyword_id                  = x_crse_keyword_id;
766 
767   BEGIN
768 
769     OPEN c1;
770     FETCH c1 INTO x_rowid;
771     IF (c1%NOTFOUND) THEN
772       CLOSE c1;
773 
774       insert_row (
775         x_rowid,
776         x_ucas_program_code,
777         x_institute,
778         x_ucas_campus,
779         x_option_code,
780         x_preference,
781         x_keyword,
782         x_updater,
783         x_active,
784         x_deleted,
785         x_sent_to_ucas,
786         x_system_code  ,
787         x_crse_keyword_id    ,
788         x_mode
789       );
790       RETURN;
791     END IF;
792     CLOSE c1;
793 
794     update_row (
795       x_rowid,
796       x_ucas_program_code,
797       x_institute,
798       x_ucas_campus,
799       x_option_code,
800       x_preference,
801       x_keyword,
802       x_updater,
803       x_active,
804       x_deleted,
805       x_sent_to_ucas,
806       x_system_code  ,
807       x_crse_keyword_id,
808       x_mode
809     );
810 
811   END add_row;
812 
813 
814   PROCEDURE delete_row (
815     x_rowid IN VARCHAR2
816   ) AS
817   /*
818   ||  Created By : rgopalan
819   ||  Created On : 23-OCT-2001
820   ||  Purpose : Handles the DELETE DML logic for the table.
821   ||  Known limitations, enhancements or remarks :
822   ||  Change History :
823   ||  Who             When            What
824   ||  (reverse chronological order - newest change first)
825   */
826   BEGIN
827 
828     before_dml (
829       p_action => 'DELETE',
830       x_rowid => x_rowid
831     );
832 
833     DELETE FROM igs_uc_crse_keywrds
834     WHERE rowid = x_rowid;
835 
836     IF (SQL%NOTFOUND) THEN
837       RAISE NO_DATA_FOUND;
838     END IF;
839 
840   END delete_row;
841 
842 
843 END igs_uc_crse_keywrds_pkg;