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_ASS_PKG

Source


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