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_AS_ANON_ID_PS_PKG

Source


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