[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_INC_GRD_CPROF_PKG
Source
1 PACKAGE BODY igs_as_inc_grd_cprof_pkg AS
2 /* $Header: IGSDI56B.pls 115.6 2002/11/28 23:24:49 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_inc_grd_cprof%ROWTYPE;
6 new_references igs_as_inc_grd_cprof%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_inc_grd_cprof_id IN NUMBER DEFAULT NULL,
12 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
15 x_incomplete_grade IN VARCHAR2 DEFAULT NULL,
16 x_number_unit_time IN NUMBER DEFAULT NULL,
17 x_type_unit_time IN VARCHAR2 DEFAULT NULL,
18 x_comp_after_dt_alias IN VARCHAR2 DEFAULT NULL,
19 x_default_grade IN VARCHAR2 DEFAULT NULL,
20 x_default_mark IN NUMBER DEFAULT NULL,
21 x_instructor_update_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 : cdcruz
30 || Created On : 25-JUL-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_AS_INC_GRD_CPROF
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.inc_grd_cprof_id := x_inc_grd_cprof_id;
62 new_references.grading_schema_cd := x_grading_schema_cd;
63 new_references.version_number := x_version_number;
64 new_references.org_unit_cd := x_org_unit_cd;
65 new_references.incomplete_grade := x_incomplete_grade;
66 new_references.number_unit_time := x_number_unit_time;
67 new_references.type_unit_time := x_type_unit_time;
68 new_references.comp_after_dt_alias := x_comp_after_dt_alias;
69 new_references.default_grade := x_default_grade;
70 new_references.default_mark := x_default_mark;
71 new_references.instructor_update_ind := x_instructor_update_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 : cdcruz
91 || Created On : 25-JUL-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.version_number,
102 new_references.grading_schema_cd,
103 new_references.org_unit_cd,
104 new_references.incomplete_grade
105 )
106 ) THEN
107 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
108 igs_ge_msg_stack.add;
109 app_exception.raise_exception;
110 END IF;
111
112 END check_uniqueness;
113
114
115 PROCEDURE check_parent_existance AS
116 /*
117 || Created By : cdcruz
118 || Created On : 25-JUL-2001
119 || Purpose : Checks for the existance of Parent records.
120 || Known limitations, enhancements or remarks :
121 || Change History :
122 || Who When What
123 || (reverse chronological order - newest change first)
124 */
125 BEGIN
126
127 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
128 (old_references.version_number = new_references.version_number) AND
129 (old_references.default_grade = new_references.default_grade)) OR
130 ((new_references.grading_schema_cd IS NULL) OR
131 (new_references.version_number IS NULL) OR
132 (new_references.default_grade IS NULL))) THEN
133 NULL;
134 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
135 new_references.grading_schema_cd,
136 new_references.version_number,
137 new_references.default_grade
138 ) THEN
139 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
140 igs_ge_msg_stack.add;
141 app_exception.raise_exception;
142 END IF;
143
144 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
145 (old_references.version_number = new_references.version_number) AND
146 (old_references.incomplete_grade = new_references.incomplete_grade)) OR
147 ((new_references.grading_schema_cd IS NULL) OR
148 (new_references.version_number IS NULL) OR
149 (new_references.incomplete_grade IS NULL))) THEN
150 NULL;
151 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
152 new_references.grading_schema_cd,
153 new_references.version_number,
154 new_references.incomplete_grade
155 ) THEN
156 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
157 igs_ge_msg_stack.add;
158 app_exception.raise_exception;
159 END IF;
160
161 IF (((old_references.comp_after_dt_alias = new_references.comp_after_dt_alias)) OR
162 ((new_references.comp_after_dt_alias IS NULL))) THEN
163 NULL;
164 ELSIF NOT igs_ca_da_pkg.get_pk_for_validation (
165 new_references.comp_after_dt_alias
166 ) THEN
167 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END IF;
171
172 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
173 (old_references.version_number = new_references.version_number)) OR
174 ((new_references.grading_schema_cd IS NULL) OR
175 (new_references.version_number IS NULL))) THEN
176 NULL;
177 ELSIF NOT igs_as_grd_schema_pkg.get_pk_for_validation (
178 new_references.grading_schema_cd,
179 new_references.version_number
180 ) THEN
181 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
182 igs_ge_msg_stack.add;
183 app_exception.raise_exception;
184 END IF;
185
186 END check_parent_existance;
187
188
189 FUNCTION get_pk_for_validation (
190 x_inc_grd_cprof_id IN NUMBER
191 ) RETURN BOOLEAN AS
192 /*
193 || Created By : cdcruz
194 || Created On : 25-JUL-2001
195 || Purpose : Validates the Primary Key of the table.
196 || Known limitations, enhancements or remarks :
197 || Change History :
198 || Who When What
199 || (reverse chronological order - newest change first)
200 */
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM igs_as_inc_grd_cprof
204 WHERE inc_grd_cprof_id = x_inc_grd_cprof_id
205 FOR UPDATE NOWAIT;
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 OPEN cur_rowid;
212 FETCH cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 CLOSE cur_rowid;
215 RETURN(TRUE);
216 ELSE
217 CLOSE cur_rowid;
218 RETURN(FALSE);
219 END IF;
220
221 END get_pk_for_validation;
222
223
224 FUNCTION get_uk_for_validation (
225 x_version_number IN NUMBER,
226 x_grading_schema_cd IN VARCHAR2,
227 x_org_unit_cd IN VARCHAR2,
228 x_incomplete_grade IN VARCHAR2
229 ) RETURN BOOLEAN AS
230 /*
231 || Created By : cdcruz
232 || Created On : 25-JUL-2001
233 || Purpose : Validates the Unique Keys of the table.
234 || Known limitations, enhancements or remarks :
235 || Change History :
236 || Who When What
237 || (reverse chronological order - newest change first)
238 */
239 CURSOR cur_rowid IS
240 SELECT rowid
241 FROM igs_as_inc_grd_cprof
242 WHERE version_number = x_version_number
243 AND grading_schema_cd = x_grading_schema_cd
244 AND org_unit_cd = x_org_unit_cd
245 AND incomplete_grade = x_incomplete_grade
246 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
247
248 lv_rowid cur_rowid%RowType;
249
250 BEGIN
251
252 OPEN cur_rowid;
253 FETCH cur_rowid INTO lv_rowid;
254 IF (cur_rowid%FOUND) THEN
255 CLOSE cur_rowid;
256 RETURN (true);
257 ELSE
258 CLOSE cur_rowid;
259 RETURN(FALSE);
260 END IF;
261
262 END get_uk_for_validation ;
263
264
265 PROCEDURE get_fk_igs_as_grd_sch_grade (
266 x_grading_schema_cd IN VARCHAR2,
267 x_version_number IN NUMBER,
268 x_grade IN VARCHAR2
269 ) AS
270 /*
271 || Created By : cdcruz
272 || Created On : 25-JUL-2001
273 || Purpose : Validates the Foreign Keys for the table.
274 || Known limitations, enhancements or remarks :
275 || Change History :
276 || Who When What
277 || (reverse chronological order - newest change first)
278 */
279 CURSOR cur_rowid IS
280 SELECT rowid
281 FROM igs_as_inc_grd_cprof
282 WHERE ((default_grade = x_grade) AND
283 (grading_schema_cd = x_grading_schema_cd) AND
284 (version_number = x_version_number))
285 OR ((grading_schema_cd = x_grading_schema_cd) AND
286 (incomplete_grade = x_grade) AND
287 (version_number = x_version_number));
288
289 lv_rowid cur_rowid%RowType;
290
291 BEGIN
292
293 OPEN cur_rowid;
294 FETCH cur_rowid INTO lv_rowid;
295 IF (cur_rowid%FOUND) THEN
296 CLOSE cur_rowid;
297 fnd_message.set_name ('IGS', 'IGS_AS_IGCP_CSG_FK');
298 igs_ge_msg_stack.add;
299 app_exception.raise_exception;
300 RETURN;
301 END IF;
302 CLOSE cur_rowid;
303
304 END get_fk_igs_as_grd_sch_grade;
305
306
307 PROCEDURE get_fk_igs_ca_da (
308 x_dt_alias IN VARCHAR2
309 ) AS
310 /*
311 || Created By : cdcruz
312 || Created On : 25-JUL-2001
313 || Purpose : Validates the Foreign Keys for the table.
314 || Known limitations, enhancements or remarks :
315 || Change History :
316 || Who When What
317 || (reverse chronological order - newest change first)
318 */
319 CURSOR cur_rowid IS
320 SELECT rowid
321 FROM igs_as_inc_grd_cprof
322 WHERE ((comp_after_dt_alias = x_dt_alias));
323
324 lv_rowid cur_rowid%RowType;
325
326 BEGIN
327
328 OPEN cur_rowid;
329 FETCH cur_rowid INTO lv_rowid;
330 IF (cur_rowid%FOUND) THEN
331 CLOSE cur_rowid;
332 fnd_message.set_name ('IGS', 'IGS_AS_IGCP_DA_FK');
333 igs_ge_msg_stack.add;
334 app_exception.raise_exception;
335 RETURN;
336 END IF;
337 CLOSE cur_rowid;
338
339 END get_fk_igs_ca_da;
340
341
342 PROCEDURE get_fk_igs_as_grd_schema (
343 x_grading_schema_cd IN VARCHAR2,
344 x_version_number IN NUMBER
345 ) AS
346 /*
347 || Created By : cdcruz
348 || Created On : 25-JUL-2001
349 || Purpose : Validates the Foreign Keys for the table.
350 || Known limitations, enhancements or remarks :
351 || Change History :
352 || Who When What
353 || (reverse chronological order - newest change first)
354 */
355 CURSOR cur_rowid IS
356 SELECT rowid
357 FROM igs_as_inc_grd_cprof
358 WHERE ((grading_schema_cd = x_grading_schema_cd) AND
359 (version_number = x_version_number));
360
361 lv_rowid cur_rowid%RowType;
362
363 BEGIN
364
365 OPEN cur_rowid;
366 FETCH cur_rowid INTO lv_rowid;
367 IF (cur_rowid%FOUND) THEN
368 CLOSE cur_rowid;
369 fnd_message.set_name ('IGS', 'IGS_AS_IGCP_GS_FK');
370 igs_ge_msg_stack.add;
371 app_exception.raise_exception;
372 RETURN;
373 END IF;
374 CLOSE cur_rowid;
375
376 END get_fk_igs_as_grd_schema;
377
378
379 PROCEDURE before_dml (
380 p_action IN VARCHAR2,
381 x_rowid IN VARCHAR2 DEFAULT NULL,
382 x_inc_grd_cprof_id IN NUMBER DEFAULT NULL,
383 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
384 x_version_number IN NUMBER DEFAULT NULL,
385 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
386 x_incomplete_grade IN VARCHAR2 DEFAULT NULL,
387 x_number_unit_time IN NUMBER DEFAULT NULL,
388 x_type_unit_time IN VARCHAR2 DEFAULT NULL,
389 x_comp_after_dt_alias IN VARCHAR2 DEFAULT NULL,
390 x_default_grade IN VARCHAR2 DEFAULT NULL,
391 x_default_mark IN NUMBER DEFAULT NULL,
392 x_instructor_update_ind IN VARCHAR2 DEFAULT NULL,
393 x_creation_date IN DATE DEFAULT NULL,
394 x_created_by IN NUMBER DEFAULT NULL,
395 x_last_update_date IN DATE DEFAULT NULL,
396 x_last_updated_by IN NUMBER DEFAULT NULL,
397 x_last_update_login IN NUMBER DEFAULT NULL
398 ) AS
399 /*
400 || Created By : cdcruz
401 || Created On : 25-JUL-2001
402 || Purpose : Initialises the columns, Checks Constraints, Calls the
403 || Trigger Handlers for the table, before any DML operation.
404 || Known limitations, enhancements or remarks :
405 || Change History :
406 || Who When What
407 || (reverse chronological order - newest change first)
408 */
409 BEGIN
410
411 set_column_values (
412 p_action,
413 x_rowid,
414 x_inc_grd_cprof_id,
415 x_grading_schema_cd,
416 x_version_number,
417 x_org_unit_cd,
418 x_incomplete_grade,
419 x_number_unit_time,
420 x_type_unit_time,
421 x_comp_after_dt_alias,
422 x_default_grade,
423 x_default_mark,
424 x_instructor_update_ind,
425 x_creation_date,
426 x_created_by,
427 x_last_update_date,
428 x_last_updated_by,
429 x_last_update_login
430 );
431
432 IF (p_action = 'INSERT') THEN
433 -- Call all the procedures related to Before Insert.
434 IF ( get_pk_for_validation(
435 new_references.inc_grd_cprof_id
436 )
437 ) THEN
438 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
439 igs_ge_msg_stack.add;
440 app_exception.raise_exception;
441 END IF;
442 check_uniqueness;
443 check_parent_existance;
444 ELSIF (p_action = 'UPDATE') THEN
445 -- Call all the procedures related to Before Update.
446 check_uniqueness;
447 check_parent_existance;
448 ELSIF (p_action = 'VALIDATE_INSERT') THEN
449 -- Call all the procedures related to Before Insert.
450 IF ( get_pk_for_validation (
451 new_references.inc_grd_cprof_id
452 )
453 ) THEN
454 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
455 igs_ge_msg_stack.add;
456 app_exception.raise_exception;
457 END IF;
458 check_uniqueness;
459 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
460 check_uniqueness;
461 END IF;
462
463 END before_dml;
464
465
466 PROCEDURE insert_row (
467 x_rowid IN OUT NOCOPY VARCHAR2,
468 x_inc_grd_cprof_id IN OUT NOCOPY NUMBER,
469 x_grading_schema_cd IN VARCHAR2,
470 x_version_number IN NUMBER,
471 x_org_unit_cd IN VARCHAR2,
472 x_incomplete_grade IN VARCHAR2,
473 x_number_unit_time IN NUMBER,
474 x_type_unit_time IN VARCHAR2,
475 x_comp_after_dt_alias IN VARCHAR2,
476 x_default_grade IN VARCHAR2,
477 x_default_mark IN NUMBER,
478 x_instructor_update_ind IN VARCHAR2,
479 x_mode IN VARCHAR2 DEFAULT 'R'
480 ) AS
481 /*
482 || Created By : cdcruz
483 || Created On : 25-JUL-2001
484 || Purpose : Handles the INSERT DML logic for the table.
485 || Known limitations, enhancements or remarks :
486 || Change History :
487 || Who When What
488 || (reverse chronological order - newest change first)
489 */
490 CURSOR c IS
491 SELECT rowid
492 FROM igs_as_inc_grd_cprof
493 WHERE inc_grd_cprof_id = x_inc_grd_cprof_id;
494
495 x_last_update_date DATE;
496 x_last_updated_by NUMBER;
497 x_last_update_login NUMBER;
498
499 BEGIN
500
501 x_last_update_date := SYSDATE;
502 IF (x_mode = 'I') THEN
503 x_last_updated_by := 1;
504 x_last_update_login := 0;
505 ELSIF (x_mode = 'R') THEN
506 x_last_updated_by := fnd_global.user_id;
507 IF (x_last_updated_by IS NULL) THEN
508 x_last_updated_by := -1;
509 END IF;
510 x_last_update_login := fnd_global.login_id;
511 IF (x_last_update_login IS NULL) THEN
512 x_last_update_login := -1;
513 END IF;
514 ELSE
515 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
516 igs_ge_msg_stack.add;
517 app_exception.raise_exception;
518 END IF;
519
520 SELECT igs_as_inc_grd_cprof_s.NEXTVAL
521 INTO x_inc_grd_cprof_id
522 FROM dual;
523
524 before_dml(
525 p_action => 'INSERT',
526 x_rowid => x_rowid,
527 x_inc_grd_cprof_id => x_inc_grd_cprof_id,
528 x_grading_schema_cd => x_grading_schema_cd,
529 x_version_number => x_version_number,
530 x_org_unit_cd => x_org_unit_cd,
531 x_incomplete_grade => x_incomplete_grade,
532 x_number_unit_time => x_number_unit_time,
533 x_type_unit_time => x_type_unit_time,
534 x_comp_after_dt_alias => x_comp_after_dt_alias,
535 x_default_grade => x_default_grade,
536 x_default_mark => x_default_mark,
537 x_instructor_update_ind => x_instructor_update_ind,
538 x_creation_date => x_last_update_date,
539 x_created_by => x_last_updated_by,
540 x_last_update_date => x_last_update_date,
541 x_last_updated_by => x_last_updated_by,
542 x_last_update_login => x_last_update_login
543 );
544
545 INSERT INTO igs_as_inc_grd_cprof (
546 inc_grd_cprof_id,
547 grading_schema_cd,
548 version_number,
549 org_unit_cd,
550 incomplete_grade,
551 number_unit_time,
552 type_unit_time,
553 comp_after_dt_alias,
554 default_grade,
555 default_mark,
556 instructor_update_ind,
557 creation_date,
558 created_by,
559 last_update_date,
560 last_updated_by,
561 last_update_login
562 ) VALUES (
563 new_references.inc_grd_cprof_id,
564 new_references.grading_schema_cd,
565 new_references.version_number,
566 new_references.org_unit_cd,
567 new_references.incomplete_grade,
568 new_references.number_unit_time,
569 new_references.type_unit_time,
570 new_references.comp_after_dt_alias,
571 new_references.default_grade,
572 new_references.default_mark,
573 new_references.instructor_update_ind,
574 x_last_update_date,
575 x_last_updated_by,
576 x_last_update_date,
577 x_last_updated_by,
578 x_last_update_login
579 );
580
581 OPEN c;
582 FETCH c INTO x_rowid;
583 IF (c%NOTFOUND) THEN
584 CLOSE c;
585 RAISE NO_DATA_FOUND;
586 END IF;
587 CLOSE c;
588
589 END insert_row;
590
591
592 PROCEDURE lock_row (
593 x_rowid IN VARCHAR2,
594 x_inc_grd_cprof_id IN NUMBER,
595 x_grading_schema_cd IN VARCHAR2,
596 x_version_number IN NUMBER,
597 x_org_unit_cd IN VARCHAR2,
598 x_incomplete_grade IN VARCHAR2,
599 x_number_unit_time IN NUMBER,
600 x_type_unit_time IN VARCHAR2,
601 x_comp_after_dt_alias IN VARCHAR2,
602 x_default_grade IN VARCHAR2,
603 x_default_mark IN NUMBER,
604 x_instructor_update_ind IN VARCHAR2
605 ) AS
606 /*
607 || Created By : cdcruz
608 || Created On : 25-JUL-2001
609 || Purpose : Handles the LOCK mechanism for the table.
610 || Known limitations, enhancements or remarks :
611 || Change History :
612 || Who When What
613 || (reverse chronological order - newest change first)
614 */
615 CURSOR c1 IS
616 SELECT
617 grading_schema_cd,
618 version_number,
619 org_unit_cd,
620 incomplete_grade,
621 number_unit_time,
622 type_unit_time,
623 comp_after_dt_alias,
624 default_grade,
625 default_mark,
626 instructor_update_ind
627 FROM igs_as_inc_grd_cprof
628 WHERE rowid = x_rowid
629 FOR UPDATE NOWAIT;
630
631 tlinfo c1%ROWTYPE;
632
633 BEGIN
634
635 OPEN c1;
636 FETCH c1 INTO tlinfo;
637 IF (c1%notfound) THEN
638 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
639 igs_ge_msg_stack.add;
640 CLOSE c1;
641 app_exception.raise_exception;
642 RETURN;
643 END IF;
644 CLOSE c1;
645
646 IF (
647 (tlinfo.grading_schema_cd = x_grading_schema_cd)
648 AND (tlinfo.version_number = x_version_number)
649 AND ((tlinfo.org_unit_cd = x_org_unit_cd) OR ( tlinfo.org_unit_cd IS NULL AND x_org_unit_cd IS NULL ))
650 AND (tlinfo.incomplete_grade = x_incomplete_grade)
651 AND (tlinfo.number_unit_time = x_number_unit_time)
652 AND (tlinfo.type_unit_time = x_type_unit_time)
653 AND (tlinfo.comp_after_dt_alias = x_comp_after_dt_alias)
654 AND ((tlinfo.default_grade = x_default_grade) OR ( tlinfo.default_grade is null and x_default_grade IS NULL ))
655 AND ((tlinfo.default_mark = x_default_mark) OR ( tlinfo.default_mark IS NULL AND x_default_mark IS NULL ))
656 AND (tlinfo.instructor_update_ind = x_instructor_update_ind)
657 ) THEN
658 NULL;
659 ELSE
660 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
661 igs_ge_msg_stack.add;
662 app_exception.raise_exception;
663 END IF;
664
665 RETURN;
666
667 END lock_row;
668
669
670 PROCEDURE update_row (
671 x_rowid IN VARCHAR2,
672 x_inc_grd_cprof_id IN NUMBER,
673 x_grading_schema_cd IN VARCHAR2,
674 x_version_number IN NUMBER,
675 x_org_unit_cd IN VARCHAR2,
676 x_incomplete_grade IN VARCHAR2,
677 x_number_unit_time IN NUMBER,
678 x_type_unit_time IN VARCHAR2,
679 x_comp_after_dt_alias IN VARCHAR2,
680 x_default_grade IN VARCHAR2,
681 x_default_mark IN NUMBER,
682 x_instructor_update_ind IN VARCHAR2,
683 x_mode IN VARCHAR2 DEFAULT 'R'
684 ) AS
685 /*
686 || Created By : cdcruz
687 || Created On : 25-JUL-2001
688 || Purpose : Handles the UPDATE DML logic for the table.
689 || Known limitations, enhancements or remarks :
690 || Change History :
691 || Who When What
692 || (reverse chronological order - newest change first)
693 */
694 x_last_update_date DATE ;
695 x_last_updated_by NUMBER;
696 x_last_update_login NUMBER;
697
698 BEGIN
699
700 x_last_update_date := SYSDATE;
701 IF (X_MODE = 'I') THEN
702 x_last_updated_by := 1;
703 x_last_update_login := 0;
704 ELSIF (x_mode = 'R') THEN
705 x_last_updated_by := fnd_global.user_id;
706 IF x_last_updated_by IS NULL THEN
707 x_last_updated_by := -1;
708 END IF;
709 x_last_update_login := fnd_global.login_id;
710 IF (x_last_update_login IS NULL) THEN
711 x_last_update_login := -1;
712 END IF;
713 ELSE
714 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
715 igs_ge_msg_stack.add;
716 app_exception.raise_exception;
717 END IF;
718
719 before_dml(
720 p_action => 'UPDATE',
721 x_rowid => x_rowid,
722 x_inc_grd_cprof_id => x_inc_grd_cprof_id,
723 x_grading_schema_cd => x_grading_schema_cd,
724 x_version_number => x_version_number,
725 x_org_unit_cd => x_org_unit_cd,
726 x_incomplete_grade => x_incomplete_grade,
727 x_number_unit_time => x_number_unit_time,
728 x_type_unit_time => x_type_unit_time,
729 x_comp_after_dt_alias => x_comp_after_dt_alias,
730 x_default_grade => x_default_grade,
731 x_default_mark => x_default_mark,
732 x_instructor_update_ind => x_instructor_update_ind,
733 x_creation_date => x_last_update_date,
734 x_created_by => x_last_updated_by,
735 x_last_update_date => x_last_update_date,
736 x_last_updated_by => x_last_updated_by,
737 x_last_update_login => x_last_update_login
738 );
739
740 UPDATE igs_as_inc_grd_cprof
741 SET
742 grading_schema_cd = new_references.grading_schema_cd,
743 version_number = new_references.version_number,
744 org_unit_cd = new_references.org_unit_cd,
745 incomplete_grade = new_references.incomplete_grade,
746 number_unit_time = new_references.number_unit_time,
747 type_unit_time = new_references.type_unit_time,
748 comp_after_dt_alias = new_references.comp_after_dt_alias,
749 default_grade = new_references.default_grade,
750 default_mark = new_references.default_mark,
751 instructor_update_ind = new_references.instructor_update_ind,
752 last_update_date = x_last_update_date,
753 last_updated_by = x_last_updated_by,
754 last_update_login = x_last_update_login
755 WHERE rowid = x_rowid;
756
757 IF (SQL%NOTFOUND) THEN
758 RAISE NO_DATA_FOUND;
759 END IF;
760
761 END update_row;
762
763
764 PROCEDURE add_row (
765 x_rowid IN OUT NOCOPY VARCHAR2,
766 x_inc_grd_cprof_id IN OUT NOCOPY NUMBER,
767 x_grading_schema_cd IN VARCHAR2,
768 x_version_number IN NUMBER,
769 x_org_unit_cd IN VARCHAR2,
770 x_incomplete_grade IN VARCHAR2,
771 x_number_unit_time IN NUMBER,
772 x_type_unit_time IN VARCHAR2,
773 x_comp_after_dt_alias IN VARCHAR2,
774 x_default_grade IN VARCHAR2,
775 x_default_mark IN NUMBER,
776 x_instructor_update_ind IN VARCHAR2,
777 x_mode IN VARCHAR2 DEFAULT 'R'
778 ) AS
779 /*
780 || Created By : cdcruz
781 || Created On : 25-JUL-2001
782 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
783 || Known limitations, enhancements or remarks :
784 || Change History :
785 || Who When What
786 || (reverse chronological order - newest change first)
787 */
788 CURSOR c1 IS
789 SELECT rowid
790 FROM igs_as_inc_grd_cprof
791 WHERE inc_grd_cprof_id = x_inc_grd_cprof_id;
792
793 BEGIN
794
795 OPEN c1;
796 FETCH c1 INTO x_rowid;
797 IF (c1%NOTFOUND) THEN
798 CLOSE c1;
799
800 insert_row (
801 x_rowid,
802 x_inc_grd_cprof_id,
803 x_grading_schema_cd,
804 x_version_number,
805 x_org_unit_cd,
806 x_incomplete_grade,
807 x_number_unit_time,
808 x_type_unit_time,
809 x_comp_after_dt_alias,
810 x_default_grade,
811 x_default_mark,
812 x_instructor_update_ind,
813 x_mode
814 );
815 RETURN;
816 END IF;
817 CLOSE c1;
818
819 update_row (
820 x_rowid,
821 x_inc_grd_cprof_id,
822 x_grading_schema_cd,
823 x_version_number,
824 x_org_unit_cd,
825 x_incomplete_grade,
826 x_number_unit_time,
827 x_type_unit_time,
828 x_comp_after_dt_alias,
829 x_default_grade,
830 x_default_mark,
831 x_instructor_update_ind,
832 x_mode
833 );
834
835 END add_row;
836
837
838 PROCEDURE delete_row (
839 x_rowid IN VARCHAR2
840 ) AS
841 /*
842 || Created By : cdcruz
843 || Created On : 25-JUL-2001
844 || Purpose : Handles the DELETE DML logic for the table.
845 || Known limitations, enhancements or remarks :
846 || Change History :
847 || Who When What
848 || (reverse chronological order - newest change first)
849 */
850 BEGIN
851
852 before_dml (
853 p_action => 'DELETE',
854 x_rowid => x_rowid
855 );
856
857 DELETE FROM igs_as_inc_grd_cprof
858 WHERE rowid = x_rowid;
859
860 IF (SQL%NOTFOUND) THEN
861 RAISE NO_DATA_FOUND;
862 END IF;
863
864 END delete_row;
865
866
867 END igs_as_inc_grd_cprof_pkg;