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