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_AD_APL_REV_PRF_PKG

Source


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