1 PACKAGE BODY igs_as_suaai_ouhist_pkg AS
2 /* $Header: IGSDI59B.pls 115.7 2003/12/03 09:03:06 ijeddy noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_suaai_ouhist%ROWTYPE;
6 new_references igs_as_suaai_ouhist%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_ass_id IN NUMBER DEFAULT NULL,
17 x_creation_dt IN DATE DEFAULT NULL,
18 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
19 x_gs_version_number IN NUMBER DEFAULT NULL,
20 x_grade IN VARCHAR2 DEFAULT NULL,
21 x_outcome_dt IN DATE DEFAULT NULL,
22 x_mark IN NUMBER DEFAULT NULL,
23 x_outcome_comment_code IN VARCHAR2 DEFAULT NULL,
24 x_hist_start_dt IN DATE DEFAULT NULL,
25 x_hist_end_dt IN DATE DEFAULT NULL,
26 x_hist_who IN NUMBER DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL,
32 x_uoo_id IN NUMBER DEFAULT NULL,
33 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
34 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
35 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
36 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
37 x_final_weight_qty IN NUMBER DEFAULT NULL,
38 x_submitted_date IN DATE DEFAULT NULL,
39 x_waived_flag IN VARCHAR2 DEFAULT NULL,
40 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
41
42 ) AS
43 /*
44 || Created By : [email protected]
45 || Created On : 28-DEC-2001
46 || Purpose : Initialises the Old and New references for the columns of the table.
47 || Known limitations, enhancements or remarks :
48 || Change History :
49 || Who When What
50 || svanukur 29-APR-03 Set the value of uoo_id as part of MUS build
51 || (reverse chronological order - newest change first)
52 */
53
54 CURSOR cur_old_ref_values IS
55 SELECT *
56 FROM igs_as_suaai_ouhist
57 WHERE rowid = x_rowid;
58
59 BEGIN
60
61 l_rowid := x_rowid;
62
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 OPEN cur_old_ref_values;
66 FETCH cur_old_ref_values INTO old_references;
67 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
68 CLOSE cur_old_ref_values;
69 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
70 igs_ge_msg_stack.add;
71 app_exception.raise_exception;
72 RETURN;
73 END IF;
74 CLOSE cur_old_ref_values;
75
76 -- Populate New Values.
77 new_references.person_id := x_person_id;
78 new_references.course_cd := x_course_cd;
79 new_references.unit_cd := x_unit_cd;
80 new_references.cal_type := x_cal_type;
81 new_references.ci_sequence_number := x_ci_sequence_number;
82 new_references.ass_id := x_ass_id;
83 new_references.creation_dt := x_creation_dt;
84 new_references.grading_schema_cd := x_grading_schema_cd;
85 new_references.gs_version_number := x_gs_version_number;
86 new_references.grade := x_grade;
87 new_references.outcome_dt := x_outcome_dt;
88 new_references.mark := x_mark;
89 new_references.outcome_comment_code := x_outcome_comment_code;
90 new_references.hist_start_dt := x_hist_start_dt;
91 new_references.hist_end_dt := x_hist_end_dt;
92 new_references.hist_who := x_hist_who;
93 new_references.uoo_id := x_uoo_id;
94
95 new_references.sua_ass_item_group_id := x_sua_ass_item_group_id;
96 new_references.midterm_mandatory_type_code := x_midterm_mandatory_type_code;
97 new_references.midterm_weight_qty := x_midterm_weight_qty;
98 new_references.final_mandatory_type_code := x_final_mandatory_type_code;
99 new_references.final_weight_qty := x_final_weight_qty;
100 new_references.submitted_date := x_submitted_date;
101 new_references.waived_flag := x_waived_flag;
102 new_references.penalty_applied_flag := x_penalty_applied_flag;
103
104 IF (p_action = 'UPDATE') THEN
105 new_references.creation_date := old_references.creation_date;
106 new_references.created_by := old_references.created_by;
107 ELSE
108 new_references.creation_date := x_creation_date;
109 new_references.created_by := x_created_by;
110 END IF;
111
112 new_references.last_update_date := x_last_update_date;
113 new_references.last_updated_by := x_last_updated_by;
114 new_references.last_update_login := x_last_update_login;
115
116 END set_column_values;
117
118
119 PROCEDURE check_parent_existance AS
120 /*
121 || Created By : [email protected]
122 || Created On : 28-DEC-2001
123 || Purpose : Checks for the existance of Parent records.
124 || Known limitations, enhancements or remarks :
125 || Change History :
126 || Who When What
127 || (reverse chronological order - newest change first)
128 || Who when What
129 || Aiyer 17-Apr-2002 Modified the parameter list for the call to the function
130 || igs_as_su_atmpt_itm_pkg.get_pk_For_validation for the code fix
131 || of the bug 2323692.Initially unit_cd field was being passes as parameter
132 || to the x_person_id field and vice versa. As person_id is a number and unit cd is a character, hence
133 || assignment of unit cd to person_id used to give a character to numeric conversion error.This has been rectified
134 */
135 BEGIN
136
137 IF (((old_references.person_id = new_references.person_id) AND
138 (old_references.course_cd = new_references.course_cd) AND
139 (old_references.ass_id = new_references.ass_id) AND
140 (old_references.creation_dt = new_references.creation_dt) AND
141 (old_references.uoo_id = new_references.uoo_id)) OR
142 ((new_references.person_id IS NULL) OR
143 (new_references.course_cd IS NULL) OR
144 (new_references.ass_id IS NULL) OR
145 (new_references.creation_dt IS NULL) OR
146 (new_references.uoo_id IS NULL))) THEN
147 NULL;
148 ELSIF NOT igs_as_su_atmpt_itm_pkg.get_pk_For_validation (
149 x_course_cd => new_references.course_cd,
150 x_person_id => new_references.person_id,
151 x_ass_id => new_references.ass_id,
152 x_creation_dt => new_references.creation_dt,
153 x_uoo_id => new_references.uoo_id
154 ) THEN
155
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.sua_ass_item_group_id = new_references.sua_ass_item_group_id
162 OR new_references.sua_ass_item_group_id IS NULL) THEN
163 NULL;
164 ELSIF NOT igs_as_sua_ai_group_pkg.get_pk_For_validation (
165 x_sua_ass_item_group_id => new_references.sua_ass_item_group_id) THEN
166 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
167 igs_ge_msg_stack.add;
168 app_exception.raise_exception;
169 END IF;
170
171 END check_parent_existance;
172
173
174 FUNCTION get_pk_for_validation (
175 x_ass_id IN NUMBER,
176 x_course_cd IN VARCHAR2,
177 x_creation_dt IN DATE,
178 x_hist_start_dt IN DATE,
179 x_person_id IN NUMBER,
180 x_uoo_id IN NUMBER
181 ) RETURN BOOLEAN AS
182 /*
183 || Created By : [email protected]
184 || Created On : 28-DEC-2001
185 || Purpose : Validates the Primary Key of the table.
186 || Known limitations, enhancements or remarks :
187 || Change History :
188 || Who When What
189 || svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
190 || (reverse chronological order - newest change first)
191 */
192 CURSOR cur_rowid IS
193 SELECT rowid
194 FROM igs_as_suaai_ouhist
195 WHERE ass_id = x_ass_id
196 AND course_cd = x_course_cd
197 AND creation_dt = x_creation_dt
198 AND hist_start_dt = x_hist_start_dt
199 AND person_id = x_person_id
200 AND uoo_id = x_uoo_id
201 FOR UPDATE NOWAIT;
202
203 lv_rowid cur_rowid%RowType;
204
205 BEGIN
206
207 OPEN cur_rowid;
208 FETCH cur_rowid INTO lv_rowid;
209 IF (cur_rowid%FOUND) THEN
210 CLOSE cur_rowid;
211 RETURN(TRUE);
212 ELSE
213 CLOSE cur_rowid;
214 RETURN(FALSE);
215 END IF;
216
217 END get_pk_for_validation;
218
219
220 PROCEDURE get_fk_igs_as_su_atmpt_itm (
221 x_person_id IN NUMBER,
222 x_course_cd IN VARCHAR2,
223 x_ass_id IN NUMBER,
224 x_creation_dt IN DATE,
225 x_uoo_id IN NUMBER
226 ) AS
227 /*
228 || Created By : [email protected]
229 || Created On : 28-DEC-2001
230 || Purpose : Validates the Foreign Keys for the table.
231 || Known limitations, enhancements or remarks :
232 || Change History :
233 || Who When What
234 || svanukur 29-APR-03 changed where clause as part of MUS build, # 2829262
235 || (reverse chronological order - newest change first)
236 */
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM igs_as_suaai_ouhist
240 WHERE ((ass_id = x_ass_id) AND
241 (course_cd = x_course_cd) AND
242 (creation_dt = x_creation_dt) AND
243 (person_id = x_person_id) AND
244 (uoo_id = x_uoo_id));
245
246 lv_rowid cur_rowid%RowType;
247
248 BEGIN
249
250 OPEN cur_rowid;
251 FETCH cur_rowid INTO lv_rowid;
252 IF (cur_rowid%FOUND) THEN
253 CLOSE cur_rowid;
254 fnd_message.set_name ('IGS', 'IGS_AS_ASHO_SUAAI_FK');
255 igs_ge_msg_stack.add;
256 app_exception.raise_exception;
257 RETURN;
258 END IF;
259 CLOSE cur_rowid;
260
261 END get_fk_igs_as_su_atmpt_itm;
262
263 PROCEDURE get_fk_igs_as_sua_ai_group (
264 x_sua_ass_item_group_id IN NUMBER
265 ) AS
266 /*
267 || Created By : [email protected]
268 || Created On : 02-Dec-2003
269 || Purpose : Validates the Foreign Keys for the table.
270 || Known limitations, enhancements or remarks :
271 || Change History :
272 || Who When What
273 || (reverse chronological order - newest change first)
274 */
275 CURSOR cur_rowid IS
276 SELECT rowid
277 FROM igs_as_suaai_ouhist
278 WHERE ((sua_ass_item_group_id = x_sua_ass_item_group_id));
279
280 lv_rowid cur_rowid%RowType;
281
282 BEGIN
283
284 OPEN cur_rowid;
285 FETCH cur_rowid INTO lv_rowid;
286 IF (cur_rowid%FOUND) THEN
287 CLOSE cur_rowid;
288 fnd_message.set_name ('IGS', 'IGS_AS_ASHO_SUAAIG_FK');
289 igs_ge_msg_stack.add;
290 app_exception.raise_exception;
291 RETURN;
292 END IF;
293 CLOSE cur_rowid;
294
295 END get_fk_igs_as_sua_ai_group;
296
297 PROCEDURE before_dml (
298 p_action IN VARCHAR2,
299 x_rowid IN VARCHAR2 DEFAULT NULL,
300 x_person_id IN NUMBER DEFAULT NULL,
301 x_course_cd IN VARCHAR2 DEFAULT NULL,
302 x_unit_cd IN VARCHAR2 DEFAULT NULL,
303 x_cal_type IN VARCHAR2 DEFAULT NULL,
304 x_ci_sequence_number IN NUMBER DEFAULT NULL,
305 x_ass_id IN NUMBER DEFAULT NULL,
306 x_creation_dt IN DATE DEFAULT NULL,
307 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
308 x_gs_version_number IN NUMBER DEFAULT NULL,
309 x_grade IN VARCHAR2 DEFAULT NULL,
310 x_outcome_dt IN DATE DEFAULT NULL,
311 x_mark IN NUMBER DEFAULT NULL,
312 x_outcome_comment_code IN VARCHAR2 DEFAULT NULL,
313 x_hist_start_dt IN DATE DEFAULT NULL,
314 x_hist_end_dt IN DATE DEFAULT NULL,
315 x_hist_who IN NUMBER DEFAULT NULL,
316 x_creation_date IN DATE DEFAULT NULL,
317 x_created_by IN NUMBER DEFAULT NULL,
318 x_last_update_date IN DATE DEFAULT NULL,
319 x_last_updated_by IN NUMBER DEFAULT NULL,
320 x_last_update_login IN NUMBER DEFAULT NULL,
321 x_uoo_id IN NUMBER DEFAULT NULL,
322 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
323 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
324 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
325 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
326 x_final_weight_qty IN NUMBER DEFAULT NULL,
327 x_submitted_date IN DATE DEFAULT NULL,
328 x_waived_flag IN VARCHAR2 DEFAULT NULL,
329 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
330 ) AS
331 /*
332 || Created By : [email protected]
333 || Created On : 28-DEC-2001
334 || Purpose : Initialises the columns, Checks Constraints, Calls the
335 || Trigger Handlers for the table, before any DML operation.
336 || Known limitations, enhancements or remarks :
337 || Change History :
338 || Who When What
339 ||svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
340 || (reverse chronological order - newest change first)
341 */
342 BEGIN
343
344 set_column_values (
345 p_action,
346 x_rowid,
347 x_person_id,
348 x_course_cd,
349 x_unit_cd,
350 x_cal_type,
351 x_ci_sequence_number,
352 x_ass_id,
353 x_creation_dt,
354 x_grading_schema_cd,
355 x_gs_version_number,
356 x_grade,
357 x_outcome_dt,
358 x_mark,
359 x_outcome_comment_code,
360 x_hist_start_dt,
361 x_hist_end_dt,
362 x_hist_who,
363 x_creation_date,
364 x_created_by,
365 x_last_update_date,
366 x_last_updated_by,
367 x_last_update_login,
368 x_uoo_id,
369 x_sua_ass_item_group_id,
370 x_midterm_mandatory_type_code,
371 x_midterm_weight_qty,
372 x_final_mandatory_type_code,
373 x_final_weight_qty,
374 x_submitted_date,
375 x_waived_flag,
376 x_penalty_applied_flag
377 );
378
379 IF (p_action = 'INSERT') THEN
380 -- Call all the procedures related to Before Insert.
381 IF ( get_pk_for_validation(
382 new_references.ass_id,
383 new_references.course_cd,
384 new_references.creation_dt,
385 new_references.hist_start_dt,
386 new_references.person_id,
387 new_references.uoo_id
388 )
389 ) THEN
390 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
391 igs_ge_msg_stack.add;
392 app_exception.raise_exception;
393 END IF;
394 check_parent_existance;
395 ELSIF (p_action = 'UPDATE') THEN
396 -- Call all the procedures related to Before Update.
397 check_parent_existance;
398 ELSIF (p_action = 'VALIDATE_INSERT') THEN
399 -- Call all the procedures related to Before Insert.
400 IF ( get_pk_for_validation (
401 new_references.ass_id,
402 new_references.course_cd,
403 new_references.creation_dt,
404 new_references.hist_start_dt,
405 new_references.person_id,
406 new_references.uoo_id
407 )
408 ) THEN
409 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
410 igs_ge_msg_stack.add;
411 app_exception.raise_exception;
412 END IF;
413 END IF;
414
415 END before_dml;
416
417
418 PROCEDURE insert_row (
419 x_rowid IN OUT NOCOPY VARCHAR2,
420 x_person_id IN NUMBER,
421 x_course_cd IN VARCHAR2,
422 x_unit_cd IN VARCHAR2,
423 x_cal_type IN VARCHAR2,
424 x_ci_sequence_number IN NUMBER,
425 x_ass_id IN NUMBER,
426 x_creation_dt IN DATE,
427 x_grading_schema_cd IN VARCHAR2,
428 x_gs_version_number IN NUMBER,
429 x_grade IN VARCHAR2,
430 x_outcome_dt IN DATE,
431 x_mark IN NUMBER,
432 x_outcome_comment_code IN VARCHAR2,
433 x_hist_start_dt IN DATE,
434 x_hist_end_dt IN DATE,
435 x_hist_who IN NUMBER,
436 x_mode IN VARCHAR2 DEFAULT 'R' ,
437 x_uoo_id IN NUMBER,
438 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
439 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
440 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
441 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
442 x_final_weight_qty IN NUMBER DEFAULT NULL,
443 x_submitted_date IN DATE DEFAULT NULL,
444 x_waived_flag IN VARCHAR2 DEFAULT NULL,
445 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
446 ) AS
447 /*
448 || Created By : [email protected]
449 || Created On : 28-DEC-2001
450 || Purpose : Handles the INSERT DML logic for the table.
451 || Known limitations, enhancements or remarks :
452 || Change History :
453 || Who When What
454 ||svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
455 || (reverse chronological order - newest change first)
456 */
457 CURSOR c IS
458 SELECT rowid
459 FROM igs_as_suaai_ouhist
460 WHERE ass_id = x_ass_id
461 AND course_cd = x_course_cd
462 AND creation_dt = x_creation_dt
463 AND hist_start_dt = x_hist_start_dt
464 AND person_id = x_person_id
465 AND uoo_id = x_uoo_id;
466
467 x_last_update_date DATE;
468 x_last_updated_by NUMBER;
469 x_last_update_login NUMBER;
470
471 BEGIN
472
473 x_last_update_date := SYSDATE;
474 IF (x_mode = 'I') THEN
475 x_last_updated_by := 1;
476 x_last_update_login := 0;
477 ELSIF (x_mode = 'R') THEN
478 x_last_updated_by := fnd_global.user_id;
479 IF (x_last_updated_by IS NULL) THEN
480 x_last_updated_by := -1;
481 END IF;
482 x_last_update_login := fnd_global.login_id;
483 IF (x_last_update_login IS NULL) THEN
484 x_last_update_login := -1;
485 END IF;
486 ELSE
487 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
488 igs_ge_msg_stack.add;
489 app_exception.raise_exception;
490 END IF;
491
492 before_dml(
493 p_action => 'INSERT',
494 x_rowid => x_rowid,
495 x_person_id => x_person_id,
496 x_course_cd => x_course_cd,
497 x_unit_cd => x_unit_cd,
498 x_cal_type => x_cal_type,
499 x_ci_sequence_number => x_ci_sequence_number,
500 x_ass_id => x_ass_id,
501 x_creation_dt => x_creation_dt,
502 x_grading_schema_cd => x_grading_schema_cd,
503 x_gs_version_number => x_gs_version_number,
504 x_grade => x_grade,
505 x_outcome_dt => x_outcome_dt,
506 x_mark => x_mark,
507 x_outcome_comment_code => x_outcome_comment_code,
508 x_hist_start_dt => x_hist_start_dt,
509 x_hist_end_dt => x_hist_end_dt,
510 x_hist_who => x_hist_who,
511 x_creation_date => x_last_update_date,
512 x_created_by => x_last_updated_by,
513 x_last_update_date => x_last_update_date,
514 x_last_updated_by => x_last_updated_by,
515 x_last_update_login => x_last_update_login,
516 x_uoo_id => x_uoo_id,
517 x_sua_ass_item_group_id => x_sua_ass_item_group_id,
518 x_midterm_mandatory_type_code => x_midterm_mandatory_type_code,
519 x_midterm_weight_qty => x_midterm_weight_qty,
520 x_final_mandatory_type_code => x_final_mandatory_type_code,
521 x_final_weight_qty => x_final_weight_qty,
522 x_submitted_date => x_submitted_date,
523 x_waived_flag => x_waived_flag,
524 x_penalty_applied_flag => x_penalty_applied_flag
525 );
526
527 INSERT INTO igs_as_suaai_ouhist (
528 person_id,
529 course_cd,
530 unit_cd,
531 cal_type,
532 ci_sequence_number,
533 ass_id,
534 creation_dt,
535 grading_schema_cd,
536 gs_version_number,
537 grade,
538 outcome_dt,
539 mark,
540 outcome_comment_code,
541 hist_start_dt,
542 hist_end_dt,
543 hist_who,
544 creation_date,
545 created_by,
546 last_update_date,
547 last_updated_by,
548 last_update_login,
549 uoo_id,
550 sua_ass_item_group_id,
551 midterm_mandatory_type_code,
552 midterm_weight_qty,
553 final_mandatory_type_code,
554 final_weight_qty,
555 submitted_date,
556 waived_flag,
557 penalty_applied_flag
558 ) VALUES (
559 new_references.person_id,
560 new_references.course_cd,
561 new_references.unit_cd,
562 new_references.cal_type,
563 new_references.ci_sequence_number,
564 new_references.ass_id,
565 new_references.creation_dt,
566 new_references.grading_schema_cd,
567 new_references.gs_version_number,
568 new_references.grade,
569 new_references.outcome_dt,
570 new_references.mark,
571 new_references.outcome_comment_code,
572 new_references.hist_start_dt,
573 new_references.hist_end_dt,
574 new_references.hist_who,
575 x_last_update_date,
576 x_last_updated_by,
577 x_last_update_date,
578 x_last_updated_by,
579 x_last_update_login,
580 new_references.uoo_id,
581 new_references.sua_ass_item_group_id,
582 new_references.midterm_mandatory_type_code,
583 new_references.midterm_weight_qty,
584 new_references.final_mandatory_type_code,
585 new_references.final_weight_qty,
586 new_references.submitted_date,
587 new_references.waived_flag,
588 new_references.penalty_applied_flag
589 );
590
591 OPEN c;
592 FETCH c INTO x_rowid;
593 IF (c%NOTFOUND) THEN
594 CLOSE c;
595 RAISE NO_DATA_FOUND;
596 END IF;
597 CLOSE c;
598
599 END insert_row;
600
601
602 PROCEDURE lock_row (
603 x_rowid IN VARCHAR2,
604 x_person_id IN NUMBER,
605 x_course_cd IN VARCHAR2,
606 x_unit_cd IN VARCHAR2,
607 x_cal_type IN VARCHAR2,
608 x_ci_sequence_number IN NUMBER,
609 x_ass_id IN NUMBER,
610 x_creation_dt IN DATE,
611 x_grading_schema_cd IN VARCHAR2,
612 x_gs_version_number IN NUMBER,
613 x_grade IN VARCHAR2,
614 x_outcome_dt IN DATE,
615 x_mark IN NUMBER,
616 x_outcome_comment_code IN VARCHAR2,
617 x_hist_start_dt IN DATE,
618 x_hist_end_dt IN DATE,
619 x_hist_who IN NUMBER,
620 x_uoo_id IN NUMBER,
621 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
622 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
623 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
624 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
625 x_final_weight_qty IN NUMBER DEFAULT NULL,
626 x_submitted_date IN DATE DEFAULT NULL,
627 x_waived_flag IN VARCHAR2 DEFAULT NULL,
628 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
629 ) AS
630 /*
631 || Created By : [email protected]
632 || Created On : 28-DEC-2001
633 || Purpose : Handles the LOCK mechanism for the table.
634 || Known limitations, enhancements or remarks :
635 || Change History :
636 || Who When What
637 || (reverse chronological order - newest change first)
638 ||svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
639 */
640 CURSOR c1 IS
641 SELECT
642 grading_schema_cd,
643 gs_version_number,
644 grade,
645 outcome_dt,
646 mark,
647 outcome_comment_code,
648 hist_end_dt,
649 hist_who,
650 sua_ass_item_group_id,
651 midterm_mandatory_type_code,
652 midterm_weight_qty,
653 final_mandatory_type_code,
654 final_weight_qty,
655 submitted_date,
656 waived_flag,
657 penalty_applied_flag
658 FROM igs_as_suaai_ouhist
659 WHERE rowid = x_rowid
660 FOR UPDATE NOWAIT;
661
662 tlinfo c1%ROWTYPE;
663
664 BEGIN
665
666 OPEN c1;
667 FETCH c1 INTO tlinfo;
668 IF (c1%notfound) THEN
669 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
670 igs_ge_msg_stack.add;
671 CLOSE c1;
672 app_exception.raise_exception;
673 RETURN;
674 END IF;
675 CLOSE c1;
676
677 IF (
678 ((tlinfo.grading_schema_cd = x_grading_schema_cd) OR ((tlinfo.grading_schema_cd IS NULL) AND (X_grading_schema_cd IS NULL)))
679 AND ((tlinfo.gs_version_number = x_gs_version_number) OR ((tlinfo.gs_version_number IS NULL) AND (X_gs_version_number IS NULL)))
680 AND ((tlinfo.grade = x_grade) OR ((tlinfo.grade IS NULL) AND (X_grade IS NULL)))
681 AND (tlinfo.outcome_dt = x_outcome_dt)
682 AND ((tlinfo.mark = x_mark) OR ((tlinfo.mark IS NULL) AND (X_mark IS NULL)))
683 AND ((tlinfo.outcome_comment_code = x_outcome_comment_code) OR ((tlinfo.outcome_comment_code IS NULL) AND (X_outcome_comment_code IS NULL)))
684 AND (tlinfo.hist_end_dt = x_hist_end_dt)
685 AND (tlinfo.hist_who = x_hist_who)
686 AND ((tlinfo.sua_ass_item_group_id = x_sua_ass_item_group_id ) OR ((tlinfo.sua_ass_item_group_id IS NULL) AND (x_sua_ass_item_group_id IS NULL)))
687 AND ((tlinfo.midterm_mandatory_type_code = x_midterm_mandatory_type_code) OR ((tlinfo.midterm_mandatory_type_code IS NULL) AND (x_midterm_mandatory_type_code IS NULL)))
688 AND ((tlinfo.midterm_weight_qty = x_midterm_weight_qty ) OR ((tlinfo.midterm_weight_qty IS NULL) AND (x_midterm_weight_qty IS NULL)))
689 AND ((tlinfo.final_mandatory_type_code = x_final_mandatory_type_code ) OR ((tlinfo.final_mandatory_type_code IS NULL) AND (x_final_mandatory_type_code IS NULL)))
690 AND ((tlinfo.final_weight_qty = x_final_weight_qty ) OR ((tlinfo.final_weight_qty IS NULL) AND (x_final_weight_qty IS NULL)))
691 AND ((tlinfo.submitted_date = x_submitted_date ) OR ((tlinfo.submitted_date IS NULL) AND (x_submitted_date IS NULL)))
692 AND ((tlinfo.waived_flag = x_waived_flag ) OR ((tlinfo.waived_flag IS NULL) AND (x_waived_flag IS NULL)))
693 AND ((tlinfo.penalty_applied_flag = x_penalty_applied_flag ) OR ((tlinfo.penalty_applied_flag IS NULL) AND (x_penalty_applied_flag IS NULL)))
694
695 ) THEN
696 NULL;
697 ELSE
698 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
699 igs_ge_msg_stack.add;
700 app_exception.raise_exception;
701 END IF;
702
703 RETURN;
704
705 END lock_row;
706
707
708 PROCEDURE update_row (
709 x_rowid IN VARCHAR2,
710 x_person_id IN NUMBER,
711 x_course_cd IN VARCHAR2,
712 x_unit_cd IN VARCHAR2,
713 x_cal_type IN VARCHAR2,
714 x_ci_sequence_number IN NUMBER,
715 x_ass_id IN NUMBER,
716 x_creation_dt IN DATE,
717 x_grading_schema_cd IN VARCHAR2,
718 x_gs_version_number IN NUMBER,
719 x_grade IN VARCHAR2,
720 x_outcome_dt IN DATE,
721 x_mark IN NUMBER,
722 x_outcome_comment_code IN VARCHAR2,
723 x_hist_start_dt IN DATE,
724 x_hist_end_dt IN DATE,
725 x_hist_who IN NUMBER,
726 x_mode IN VARCHAR2 DEFAULT 'R' ,
727 x_uoo_id IN NUMBER,
728 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
729 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
730 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
731 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
732 x_final_weight_qty IN NUMBER DEFAULT NULL,
733 x_submitted_date IN DATE DEFAULT NULL,
734 x_waived_flag IN VARCHAR2 DEFAULT NULL,
735 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
736
737 ) AS
738 /*
739 || Created By : [email protected]
740 || Created On : 28-DEC-2001
741 || Purpose : Handles the UPDATE DML logic for the table.
742 || Known limitations, enhancements or remarks :
743 || Change History :
744 || Who When What
745 || (reverse chronological order - newest change first)
746 ||svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
747 */
748 x_last_update_date DATE ;
749 x_last_updated_by NUMBER;
750 x_last_update_login NUMBER;
751
752 BEGIN
753
754 x_last_update_date := SYSDATE;
755 IF (X_MODE = 'I') THEN
756 x_last_updated_by := 1;
757 x_last_update_login := 0;
758 ELSIF (x_mode = 'R') THEN
759 x_last_updated_by := fnd_global.user_id;
760 IF x_last_updated_by IS NULL THEN
761 x_last_updated_by := -1;
762 END IF;
763 x_last_update_login := fnd_global.login_id;
764 IF (x_last_update_login IS NULL) THEN
765 x_last_update_login := -1;
766 END IF;
767 ELSE
768 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
769 igs_ge_msg_stack.add;
770 app_exception.raise_exception;
771 END IF;
772
773 before_dml(
774 p_action => 'UPDATE',
775 x_rowid => x_rowid,
776 x_person_id => x_person_id,
777 x_course_cd => x_course_cd,
778 x_unit_cd => x_unit_cd,
779 x_cal_type => x_cal_type,
780 x_ci_sequence_number => x_ci_sequence_number,
781 x_ass_id => x_ass_id,
782 x_creation_dt => x_creation_dt,
783 x_grading_schema_cd => x_grading_schema_cd,
784 x_gs_version_number => x_gs_version_number,
785 x_grade => x_grade,
786 x_outcome_dt => x_outcome_dt,
787 x_mark => x_mark,
788 x_outcome_comment_code => x_outcome_comment_code,
789 x_hist_start_dt => x_hist_start_dt,
790 x_hist_end_dt => x_hist_end_dt,
791 x_hist_who => x_hist_who,
792 x_creation_date => x_last_update_date,
793 x_created_by => x_last_updated_by,
794 x_last_update_date => x_last_update_date,
795 x_last_updated_by => x_last_updated_by,
796 x_last_update_login => x_last_update_login,
797 x_uoo_id => x_uoo_id,
798 x_sua_ass_item_group_id => x_sua_ass_item_group_id,
799 x_midterm_mandatory_type_code => x_midterm_mandatory_type_code,
800 x_midterm_weight_qty => x_midterm_weight_qty,
801 x_final_mandatory_type_code => x_final_mandatory_type_code,
802 x_final_weight_qty => x_final_weight_qty,
803 x_submitted_date => x_submitted_date,
804 x_waived_flag => x_waived_flag,
805 x_penalty_applied_flag => x_penalty_applied_flag
806 );
807
808 UPDATE igs_as_suaai_ouhist
809 SET
810 grading_schema_cd = new_references.grading_schema_cd,
811 gs_version_number = new_references.gs_version_number,
812 grade = new_references.grade,
813 outcome_dt = new_references.outcome_dt,
814 mark = new_references.mark,
815 outcome_comment_code = new_references.outcome_comment_code,
816 hist_end_dt = new_references.hist_end_dt,
817 hist_who = new_references.hist_who,
818 last_update_date = x_last_update_date,
819 last_updated_by = x_last_updated_by,
820 last_update_login = x_last_update_login,
821 sua_ass_item_group_id = x_sua_ass_item_group_id,
822 midterm_mandatory_type_code = x_midterm_mandatory_type_code,
823 midterm_weight_qty = x_midterm_weight_qty,
824 final_mandatory_type_code = x_final_mandatory_type_code,
825 final_weight_qty = x_final_weight_qty,
826 submitted_date = x_submitted_date,
827 waived_flag = x_waived_flag,
828 penalty_applied_flag = x_penalty_applied_flag
829 WHERE rowid = x_rowid;
830
831 IF (SQL%NOTFOUND) THEN
832 RAISE NO_DATA_FOUND;
833 END IF;
834
835 END update_row;
836
837
838 PROCEDURE add_row (
839 x_rowid IN OUT NOCOPY VARCHAR2,
840 x_person_id IN NUMBER,
841 x_course_cd IN VARCHAR2,
842 x_unit_cd IN VARCHAR2,
843 x_cal_type IN VARCHAR2,
844 x_ci_sequence_number IN NUMBER,
845 x_ass_id IN NUMBER,
846 x_creation_dt IN DATE,
847 x_grading_schema_cd IN VARCHAR2,
848 x_gs_version_number IN NUMBER,
849 x_grade IN VARCHAR2,
850 x_outcome_dt IN DATE,
851 x_mark IN NUMBER,
852 x_outcome_comment_code IN VARCHAR2,
853 x_hist_start_dt IN DATE,
854 x_hist_end_dt IN DATE,
855 x_hist_who IN NUMBER,
856 x_mode IN VARCHAR2 DEFAULT 'R' ,
857 x_uoo_id IN NUMBER,
858 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
859 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
860 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
861 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
862 x_final_weight_qty IN NUMBER DEFAULT NULL,
863 x_submitted_date IN DATE DEFAULT NULL,
864 x_waived_flag IN VARCHAR2 DEFAULT NULL,
865 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
866 ) AS
867 /*
868 || Created By : [email protected]
869 || Created On : 28-DEC-2001
870 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
871 || Known limitations, enhancements or remarks :
872 || Change History :
873 || Who When What
874 || (reverse chronological order - newest change first)
875 ||svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
876 */
877 CURSOR c1 IS
878 SELECT rowid
879 FROM igs_as_suaai_ouhist
880 WHERE ass_id = x_ass_id
881 AND course_cd = x_course_cd
882 AND creation_dt = x_creation_dt
883 AND hist_start_dt = x_hist_start_dt
884 AND person_id = x_person_id
885 AND uoo_id = x_uoo_id;
886
887 BEGIN
888
889 OPEN c1;
890 FETCH c1 INTO x_rowid;
891 IF (c1%NOTFOUND) THEN
892 CLOSE c1;
893
894 insert_row (
895 x_rowid,
896 x_person_id,
897 x_course_cd,
898 x_unit_cd,
899 x_cal_type,
900 x_ci_sequence_number,
901 x_ass_id,
902 x_creation_dt,
903 x_grading_schema_cd,
904 x_gs_version_number,
905 x_grade,
906 x_outcome_dt,
907 x_mark,
908 x_outcome_comment_code,
909 x_hist_start_dt,
910 x_hist_end_dt,
911 x_hist_who,
912 x_mode,
913 x_uoo_id,
914 x_sua_ass_item_group_id,
915 x_midterm_mandatory_type_code,
916 x_midterm_weight_qty,
917 x_final_mandatory_type_code,
918 x_final_weight_qty,
919 x_submitted_date,
920 x_waived_flag,
921 x_penalty_applied_flag
922 );
923 RETURN;
924 END IF;
925 CLOSE c1;
926
927 update_row (
928 x_rowid,
929 x_person_id,
930 x_course_cd,
931 x_unit_cd,
932 x_cal_type,
933 x_ci_sequence_number,
934 x_ass_id,
935 x_creation_dt,
936 x_grading_schema_cd,
937 x_gs_version_number,
938 x_grade,
939 x_outcome_dt,
940 x_mark,
941 x_outcome_comment_code,
942 x_hist_start_dt,
943 x_hist_end_dt,
944 x_hist_who,
945 x_mode,
946 x_uoo_id,
947 x_sua_ass_item_group_id,
948 x_midterm_mandatory_type_code,
949 x_midterm_weight_qty,
950 x_final_mandatory_type_code,
951 x_final_weight_qty,
952 x_submitted_date,
953 x_waived_flag,
954 x_penalty_applied_flag
955 );
956
957 END add_row;
958
959
960 PROCEDURE delete_row (
961 x_rowid IN VARCHAR2
962 ) AS
963 /*
964 || Created By : [email protected]
965 || Created On : 28-DEC-2001
966 || Purpose : Handles the DELETE DML logic for the table.
967 || Known limitations, enhancements or remarks :
968 || Change History :
969 || Who When What
970 || (reverse chronological order - newest change first)
971 ||svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
972 */
973 BEGIN
974
975 before_dml (
976 p_action => 'DELETE',
977 x_rowid => x_rowid
978 );
979
980 DELETE FROM igs_as_suaai_ouhist
981 WHERE rowid = x_rowid;
982
983 IF (SQL%NOTFOUND) THEN
984 RAISE NO_DATA_FOUND;
985 END IF;
986
987 END delete_row;
988
989
990 END igs_as_suaai_ouhist_pkg;
[Home] [Help]