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