[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SU_ATMPT_ITM_PKG
Source
1 PACKAGE BODY IGS_AS_SU_ATMPT_ITM_PKG AS
2 /* $Header: IGSDI08B.pls 120.0 2005/07/05 13:08:56 appldev noship $ */
3 -- Bug No. 1956374 Procedure assp_val_suaai_ins reference is changed
4 l_rowid VARCHAR2 (25);
5 l_altered_creation_dt DATE;
6 old_references igs_as_su_atmpt_itm%ROWTYPE;
7 new_references igs_as_su_atmpt_itm%ROWTYPE;
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_attempt_number IN NUMBER DEFAULT NULL,
19 x_outcome_dt IN DATE DEFAULT NULL,
20 x_override_due_dt IN DATE DEFAULT NULL,
21 x_tracking_id IN NUMBER DEFAULT NULL,
22 x_logical_delete_dt IN DATE DEFAULT NULL,
23 x_s_default_ind IN VARCHAR2 DEFAULT NULL,
24 x_ass_pattern_id IN NUMBER DEFAULT NULL,
25 x_creation_date IN DATE DEFAULT NULL,
26 x_created_by IN NUMBER DEFAULT NULL,
27 x_last_update_date IN DATE DEFAULT NULL,
28 x_last_updated_by IN NUMBER DEFAULT NULL,
29 x_last_update_login IN NUMBER DEFAULT NULL,
30 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
31 x_gs_version_number IN NUMBER DEFAULT NULL,
32 x_grade IN VARCHAR2 DEFAULT NULL,
33 x_outcome_comment_code IN VARCHAR2 DEFAULT NULL,
34 x_mark IN NUMBER DEFAULT NULL,
35 x_attribute_category IN VARCHAR2 DEFAULT NULL,
36 x_attribute1 IN VARCHAR2 DEFAULT NULL,
37 x_attribute2 IN VARCHAR2 DEFAULT NULL,
38 x_attribute3 IN VARCHAR2 DEFAULT NULL,
39 x_attribute4 IN VARCHAR2 DEFAULT NULL,
40 x_attribute5 IN VARCHAR2 DEFAULT NULL,
41 x_attribute6 IN VARCHAR2 DEFAULT NULL,
42 x_attribute7 IN VARCHAR2 DEFAULT NULL,
43 x_attribute8 IN VARCHAR2 DEFAULT NULL,
44 x_attribute9 IN VARCHAR2 DEFAULT NULL,
45 x_attribute10 IN VARCHAR2 DEFAULT NULL,
46 x_attribute11 IN VARCHAR2 DEFAULT NULL,
47 x_attribute12 IN VARCHAR2 DEFAULT NULL,
48 x_attribute13 IN VARCHAR2 DEFAULT NULL,
49 x_attribute14 IN VARCHAR2 DEFAULT NULL,
50 x_attribute15 IN VARCHAR2 DEFAULT NULL,
51 x_attribute16 IN VARCHAR2 DEFAULT NULL,
52 x_attribute17 IN VARCHAR2 DEFAULT NULL,
53 x_attribute18 IN VARCHAR2 DEFAULT NULL,
54 x_attribute19 IN VARCHAR2 DEFAULT NULL,
55 x_attribute20 IN VARCHAR2 DEFAULT NULL,
56 x_uoo_id IN NUMBER DEFAULT NULL,
57 x_unit_section_ass_item_id IN NUMBER DEFAULT NULL,
58 x_unit_ass_item_id IN NUMBER DEFAULT NULL,
59 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
60 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
61 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
62 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
63 x_final_weight_qty IN NUMBER DEFAULT NULL,
64 x_submitted_date IN DATE DEFAULT NULL,
65 x_waived_flag IN VARCHAR2 DEFAULT NULL,
66 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
67 ) AS
68 CURSOR cur_old_ref_values IS
69 SELECT *
70 FROM igs_as_su_atmpt_itm
71 WHERE ROWID = x_rowid;
72 BEGIN
73 l_rowid := x_rowid;
74 -- Code for setting the Old and New Reference Values.
75 -- Populate Old Values.
76 OPEN cur_old_ref_values;
77 FETCH cur_old_ref_values INTO old_references;
78 IF (cur_old_ref_values%NOTFOUND)
79 AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
80 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
81 igs_ge_msg_stack.ADD;
82 CLOSE cur_old_ref_values;
83 app_exception.raise_exception;
84 RETURN;
85 END IF;
86 CLOSE cur_old_ref_values;
87 -- Populate New Values.
88 new_references.person_id := x_person_id;
89 new_references.course_cd := x_course_cd;
90 new_references.unit_cd := x_unit_cd;
91 new_references.cal_type := x_cal_type;
92 new_references.ci_sequence_number := x_ci_sequence_number;
93 new_references.ass_id := x_ass_id;
94 new_references.creation_dt := x_creation_dt;
95 new_references.attempt_number := x_attempt_number;
96 new_references.outcome_dt := x_outcome_dt;
97 new_references.override_due_dt := x_override_due_dt;
98 new_references.tracking_id := x_tracking_id;
99 new_references.logical_delete_dt := x_logical_delete_dt;
100 new_references.s_default_ind := x_s_default_ind;
101 new_references.ass_pattern_id := x_ass_pattern_id;
102 new_references.grade := x_grade;
103 new_references.outcome_comment_code := x_outcome_comment_code;
104 new_references.mark := x_mark;
105 new_references.grading_schema_cd := x_grading_schema_cd;
106 new_references.gs_version_number := x_gs_version_number;
107 new_references.attribute_category := x_attribute_category;
108 new_references.attribute1 := x_attribute1;
109 new_references.attribute2 := x_attribute2;
110 new_references.attribute3 := x_attribute3;
111 new_references.attribute4 := x_attribute4;
112 new_references.attribute5 := x_attribute5;
113 new_references.attribute6 := x_attribute6;
114 new_references.attribute7 := x_attribute7;
115 new_references.attribute8 := x_attribute8;
116 new_references.attribute9 := x_attribute9;
117 new_references.attribute10 := x_attribute10;
118 new_references.attribute11 := x_attribute11;
119 new_references.attribute12 := x_attribute12;
120 new_references.attribute13 := x_attribute13;
121 new_references.attribute14 := x_attribute14;
122 new_references.attribute15 := x_attribute15;
123 new_references.attribute16 := x_attribute16;
124 new_references.attribute17 := x_attribute17;
125 new_references.attribute18 := x_attribute18;
126 new_references.attribute19 := x_attribute19;
127 new_references.attribute20 := x_attribute20;
128 new_references.uoo_id := x_uoo_id;
129 new_references.unit_section_ass_item_id := x_unit_section_ass_item_id;
130 new_references.unit_ass_item_id := x_unit_ass_item_id;
131 new_references.sua_ass_item_group_id := x_sua_ass_item_group_id;
132 new_references.midterm_mandatory_type_code := x_midterm_mandatory_type_code;
133 new_references.midterm_weight_qty := x_midterm_weight_qty;
134 new_references.final_mandatory_type_code := x_final_mandatory_type_code;
135 new_references.final_weight_qty := x_final_weight_qty;
136 new_references.submitted_date := x_submitted_date;
137 new_references.waived_flag := x_waived_flag;
138 new_references.penalty_applied_flag := x_penalty_applied_flag;
139 IF (p_action = 'UPDATE') THEN
140 new_references.creation_date := old_references.creation_date;
141 new_references.created_by := old_references.created_by;
142 ELSE
143 new_references.creation_date := x_creation_date;
144 new_references.created_by := x_created_by;
145 END IF;
146 new_references.last_update_date := x_last_update_date;
147 new_references.last_updated_by := x_last_updated_by;
148 new_references.last_update_login := x_last_update_login;
149 END set_column_values;
150
151 PROCEDURE beforerowinsertupdate1 (
152 p_inserting IN BOOLEAN DEFAULT FALSE,
153 p_updating IN BOOLEAN DEFAULT FALSE,
154 p_deleting IN BOOLEAN DEFAULT FALSE
155 ) /***********************************************************************************************************
156 |Change History :
157 |Who When What
158 |
159 |Aiyer 19-Apr-2002 Modified for the bug #2323692
160 | Check that the Outcome comments field should have a comment when grqde or marks field
161 | is changed.
162 *=======================================================================*************************************/
163 AS
164 v_message_name VARCHAR2 (30);
165 v_s_tracking_type igs_tr_type.s_tracking_type%TYPE;
166 v_s_tracking_status igs_tr_status.s_tracking_status%TYPE;
167 CURSOR cp_tri (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
168 SELECT tt.s_tracking_type,
169 trs.s_tracking_status
170 FROM igs_tr_item tri,
171 igs_tr_type tt,
172 igs_tr_status trs
173 WHERE tri.tracking_id = cp_tracking_id
174 AND tri.tracking_type = tt.tracking_type
175 AND tri.tracking_status = trs.tracking_status;
176 cst_assignment CONSTANT VARCHAR2 (10) := 'ASSIGNMENT';
177 cst_assignment_due CONSTANT VARCHAR2 (10) := 'ASSIGN-DUE';
178 BEGIN
179 -- If p_inserting, validate that the assessment item is applicable to the
180 -- student IGS_PS_UNIT attempt and that the IGS_PS_UNIT attempt status is ENROLLED or
181 -- UNCONFIRMED.
182 IF p_inserting THEN
183 IF igs_as_val_scap.assp_val_suaai_ins (
184 new_references.person_id,
185 new_references.course_cd,
186 new_references.unit_cd,
187 new_references.cal_type,
188 new_references.ci_sequence_number,
189 new_references.ass_id,
190 v_message_name,
191 new_references.uoo_id
192 ) = FALSE THEN
193 -- Check message number and return the more appropriate message
194 -- when status is completed.
195 IF v_message_name = 'IGS_CA_AA_CIR_FK' THEN
196 NULL;
197 END IF;
198 fnd_message.set_name ('IGS', v_message_name);
199 igs_ge_msg_stack.ADD;
200 app_exception.raise_exception;
201 END IF;
202 END IF;
203 -- If p_updating the override_due_dt, check if a tracking item exists and update
204 -- the item. Validate that the tracking item is for an assignment.
205 IF p_updating
206 AND (NVL (new_references.override_due_dt, igs_ge_date.igsdate ('1900/01/01')) <>
207 NVL (old_references.override_due_dt, igs_ge_date.igsdate ('1900/01/01'))
208 )
209 AND new_references.tracking_id IS NOT NULL THEN
210 OPEN cp_tri (new_references.tracking_id);
211 FETCH cp_tri INTO v_s_tracking_type,
212 v_s_tracking_status;
213 CLOSE cp_tri;
214 IF v_s_tracking_type = cst_assignment THEN
215 IF v_s_tracking_status <> 'ACTIVE' THEN
216 -- Tracking item is no longer active
217 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
218 igs_ge_msg_stack.ADD;
219 app_exception.raise_exception;
220 ELSIF igs_tr_gen_002.trkp_upd_trst (
221 new_references.tracking_id,
222 NULL,
223 cst_assignment_due,
224 new_references.override_due_dt,
225 NULL,
226 NULL,
227 NULL,
228 NULL,
229 v_message_name
230 ) = FALSE THEN
231 -- Reset the message to be in context of p_updating the override due dt.
232 IF v_message_name = 'IGS_GE_RECORD_ALREADY_EXISTS' THEN
233 fnd_message.set_name ('IGS', 'IGS_AD_TE_TELOC_FK');
234 igs_ge_msg_stack.ADD;
235 app_exception.raise_exception;
236 ELSE
237 fnd_message.set_name ('IGS', 'IGS_AD_TE_TELOC_FK');
238 igs_ge_msg_stack.ADD;
239 app_exception.raise_exception;
240 END IF;
241 END IF;
242 END IF;
243 END IF;
244 -- This code has been added by aiyer for the bug #2323692
245 -- If the user inserts a record or updates a record with a mark or grade for the first time then he does not need to insert a
246 -- Outcome Comment however every subsequent change to any of these two fields require a outcome comment to be provided thereafter
247 IF p_updating THEN
248 IF old_references.grade IS NOT NULL
249 OR old_references.mark IS NOT NULL THEN
250 IF NVL (new_references.grade, 0) <> old_references.grade
251 OR NVL (new_references.mark, 0) <> old_references.mark THEN
252 IF new_references.outcome_comment_code IS NULL THEN
253 fnd_message.set_name ('IGS', 'IGS_AS_OUT_COM_MANDATORY');
254 igs_ge_msg_stack.ADD;
255 app_exception.raise_exception;
256 END IF;
257 END IF;
258 END IF;
259 END IF;
260 END beforerowinsertupdate1;
261 -- Trigger description :-
262 -- "OSS_TST".trg_suaai_ar_iu
263 -- AFTER INSERT OR UPDATE
264 -- ON igs_as_su_atmpt_itm
265 -- FOR EACH ROW
266 PROCEDURE assp_ins_suaai_out_hist AS
267 -- This is the local procedure which inserts a record into history table.
268 l_rowid VARCHAR2 (25);
269 a VARCHAR2 (2000);
270 BEGIN
271 -- begin
272 IF (NVL (new_references.grade, 'NULL') <> NVL (old_references.grade, 'NULL')
273 OR NVL (new_references.grading_schema_cd, 'NULL') <> NVL (old_references.grading_schema_cd, 'NULL')
274 OR NVL (new_references.gs_version_number, 999) <> NVL (old_references.gs_version_number, 999)
275 OR NVL (new_references.mark, 99999) <> NVL (old_references.mark, 99999)
276 OR NVL (new_references.outcome_dt, igs_ge_date.igsdate ('1900/01/01')) <>
277 NVL (old_references.outcome_dt, igs_ge_date.igsdate ('1900/01/01'))
278 OR NVL (new_references.outcome_comment_code, 'NULL') <> NVL (old_references.outcome_comment_code, 'NULL')
279 ) THEN
280 igs_as_suaai_ouhist_pkg.insert_row (
281 x_rowid => l_rowid,
282 x_person_id => old_references.person_id,
283 x_course_cd => old_references.course_cd,
284 x_unit_cd => old_references.unit_cd,
285 x_cal_type => old_references.cal_type,
286 x_ci_sequence_number => old_references.ci_sequence_number,
287 x_ass_id => old_references.ass_id,
288 x_creation_dt => old_references.creation_dt,
289 x_grading_schema_cd => old_references.grading_schema_cd,
290 x_gs_version_number => old_references.gs_version_number,
291 x_grade => old_references.grade,
292 x_outcome_dt => old_references.outcome_dt,
293 x_mark => old_references.mark,
294 x_outcome_comment_code => old_references.outcome_comment_code,
295 x_hist_start_dt => old_references.last_update_date,
296 x_hist_end_dt => new_references.last_update_date,
297 x_hist_who => old_references.last_updated_by,
298 x_mode => 'R',
299 x_uoo_id => old_references.uoo_id,
300 x_sua_ass_item_group_id => old_references.sua_ass_item_group_id,
301 x_midterm_mandatory_type_code => old_references.midterm_mandatory_type_code,
302 x_midterm_weight_qty => old_references.midterm_weight_qty,
303 x_final_mandatory_type_code => old_references.final_mandatory_type_code,
304 x_final_weight_qty => old_references.final_weight_qty,
305 x_submitted_date => old_references.submitted_date,
306 x_waived_flag => old_references.waived_flag,
307 x_penalty_applied_flag => old_references.penalty_applied_flag
308 );
309 END IF;
310 END assp_ins_suaai_out_hist;
311 PROCEDURE afterrowinsertupdate2 (
312 p_inserting IN BOOLEAN DEFAULT FALSE,
313 p_updating IN BOOLEAN DEFAULT FALSE,
314 p_deleting IN BOOLEAN DEFAULT FALSE
315 ) AS
316 v_message_name VARCHAR2 (30);
317 BEGIN
318 IF p_inserting
319 OR (p_updating
320 AND new_references.attempt_number <> old_references.attempt_number
321 ) THEN
322 IF igs_as_val_suaai.assp_val_suaai_atmpt (
323 new_references.person_id,
324 new_references.course_cd,
325 new_references.unit_cd,
326 new_references.cal_type,
327 new_references.ci_sequence_number,
328 new_references.ass_id,
329 new_references.creation_dt,
330 new_references.attempt_number,
331 v_message_name,
332 new_references.uoo_id
333 ) = FALSE THEN
334 fnd_message.set_name ('IGS', v_message_name);
335 igs_ge_msg_stack.ADD;
336 app_exception.raise_exception;
337 END IF;
338 -- Validate the attempt number is unique within the student's assessment item.
339 -- Cannot call assp_val_suaai_atmpt because trigger will be mutating.
340 -- Save the rowid of the current row.
341 -- In case of updation it should save a history record in outcome History Record.
342 END IF;
343 IF p_updating THEN
344 assp_ins_suaai_out_hist;
345 END IF;
346 END afterrowinsertupdate2;
347 -- Trigger description :-
348 -- "OSS_TST".trg_suaai_as_iu
349 -- AFTER INSERT OR UPDATE
350 -- ON igs_as_su_atmpt_itm
351 PROCEDURE check_parent_existance AS
352 BEGIN
353 IF (((old_references.ass_id = new_references.ass_id))
354 OR ((new_references.ass_id IS NULL))
355 ) THEN
356 NULL;
357 ELSIF NOT igs_as_assessmnt_itm_pkg.get_pk_for_validation (new_references.ass_id) THEN
358 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
359 igs_ge_msg_stack.ADD;
360 app_exception.raise_exception;
361 END IF;
362 IF (((old_references.person_id = new_references.sua_ass_item_group_id))
363 OR ((new_references.sua_ass_item_group_id IS NULL))
364 ) THEN
365 NULL;
366 ELSIF NOT igs_as_sua_ai_group_pkg.get_pk_for_validation (
367 new_references.sua_ass_item_group_id
368 ) THEN
369 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
370 igs_ge_msg_stack.ADD;
371 app_exception.raise_exception;
372 END IF;
373 IF (((old_references.tracking_id = new_references.tracking_id))
374 OR ((new_references.tracking_id IS NULL))
375 ) THEN
376 NULL;
377 ELSIF NOT igs_tr_item_pkg.get_pk_for_validation (new_references.tracking_id) THEN
378 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
379 igs_ge_msg_stack.ADD;
380 app_exception.raise_exception;
381 END IF;
382 END check_parent_existance;
383
384 PROCEDURE check_child_existance AS
385 CURSOR cur_igs_as_spl_cons_appl IS
386 SELECT ROWID
387 FROM igs_as_spl_cons_appl
388 WHERE course_cd = old_references.course_cd
389 AND person_id = old_references.person_id
390 AND ass_id = old_references.ass_id
391 AND creation_dt = old_references.creation_dt
392 AND uoo_id = old_references.uoo_id;
393 BEGIN
394 FOR igs_as_spl_cons_appl_rec IN cur_igs_as_spl_cons_appl LOOP
395 igs_as_spl_cons_appl_pkg.delete_row (x_rowid => igs_as_spl_cons_appl_rec.ROWID);
396 END LOOP;
397 igs_as_std_exm_instn_pkg.get_fk_igs_as_su_atmpt_itm (
398 old_references.course_cd,
399 old_references.person_id,
400 old_references.ass_id,
401 old_references.creation_dt,
402 old_references.uoo_id
403 );
404 END check_child_existance;
405
406 -------------------------------------------------------------------------------------------
407 --Change History:
408 --Who When What
409 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
410 -------------------------------------------------------------------------------------------
411 FUNCTION get_pk_for_validation (
412 x_course_cd IN VARCHAR2,
413 x_person_id IN NUMBER,
414 x_ass_id IN NUMBER,
415 x_creation_dt IN DATE,
416 x_uoo_id IN NUMBER
417 )
418 RETURN BOOLEAN AS
419 CURSOR cur_rowid IS
420 SELECT ROWID
421 FROM igs_as_su_atmpt_itm
422 WHERE course_cd = x_course_cd
423 AND person_id = x_person_id
424 AND ass_id = x_ass_id
425 AND creation_dt = x_creation_dt
426 AND uoo_id = x_uoo_id
427 FOR UPDATE NOWAIT;
428 lv_rowid cur_rowid%ROWTYPE;
429 BEGIN
430 OPEN cur_rowid;
431 FETCH cur_rowid INTO lv_rowid;
432 IF (cur_rowid%FOUND) THEN
433 CLOSE cur_rowid;
434 RETURN (TRUE);
435 ELSE
436 CLOSE cur_rowid;
437 RETURN (FALSE);
438 END IF;
439 END get_pk_for_validation;
440
441 PROCEDURE get_fk_igs_as_assessmnt_itm (x_ass_id IN NUMBER) IS
442 CURSOR cur_rowid IS
443 SELECT ROWID
444 FROM igs_as_su_atmpt_itm
445 WHERE ass_id = x_ass_id;
446 lv_rowid cur_rowid%ROWTYPE;
447 BEGIN
448 OPEN cur_rowid;
449 FETCH cur_rowid INTO lv_rowid;
450 IF (cur_rowid%FOUND) THEN
451 fnd_message.set_name ('IGS', 'IGS_AS_SUAAI_AI_FK');
452 igs_ge_msg_stack.ADD;
453 CLOSE cur_rowid;
454 app_exception.raise_exception;
455 RETURN;
456 END IF;
457 CLOSE cur_rowid;
458 END get_fk_igs_as_assessmnt_itm;
459
460 --
461 -- Obsoleted
462 --
463 PROCEDURE get_fk_igs_en_su_attempt (x_person_id IN NUMBER, x_course_cd IN VARCHAR2, x_uoo_id IN NUMBER) AS
464 BEGIN
465 RETURN;
466 END get_fk_igs_en_su_attempt;
467
468 PROCEDURE get_fk_igs_as_sua_ai_group (x_sua_ass_item_group_id IN NUMBER) AS
469 CURSOR cur_rowid IS
470 SELECT ROWID
471 FROM igs_as_su_atmpt_itm
472 WHERE sua_ass_item_group_id = x_sua_ass_item_group_id;
473 lv_rowid cur_rowid%ROWTYPE;
474 BEGIN
475 OPEN cur_rowid;
476 FETCH cur_rowid INTO lv_rowid;
477 IF (cur_rowid%FOUND) THEN
478 fnd_message.set_name ('IGS', 'IGS_AS_SUAAI_SUAIG_FK');
479 igs_ge_msg_stack.ADD;
480 CLOSE cur_rowid;
481 app_exception.raise_exception;
482 RETURN;
483 END IF;
484 CLOSE cur_rowid;
485 END get_fk_igs_as_sua_ai_group;
486
487 PROCEDURE get_fk_igs_tr_item (x_tracking_id IN NUMBER) AS
488 CURSOR cur_rowid IS
489 SELECT ROWID
490 FROM igs_as_su_atmpt_itm
491 WHERE tracking_id = x_tracking_id;
492 lv_rowid cur_rowid%ROWTYPE;
493 BEGIN
494 OPEN cur_rowid;
495 FETCH cur_rowid INTO lv_rowid;
496 IF (cur_rowid%FOUND) THEN
497 fnd_message.set_name ('IGS', 'IGS_AS_SUAAI_TRI_FK');
498 igs_ge_msg_stack.ADD;
499 CLOSE cur_rowid;
500 app_exception.raise_exception;
501 RETURN;
502 END IF;
503 CLOSE cur_rowid;
504 END get_fk_igs_tr_item;
505
506 -- Added by DDEY as a part of enhancement bug # 2162831
507 PROCEDURE get_fk_igs_as_grd_sch_grade (
508 x_grading_schema_cd IN VARCHAR2,
509 x_gs_version_number IN NUMBER,
510 x_grade IN VARCHAR2
511 ) AS
512 CURSOR cur_rowid IS
513 SELECT ROWID
514 FROM igs_as_su_atmpt_itm
515 WHERE grading_schema_cd = x_grading_schema_cd
516 AND gs_version_number = x_gs_version_number
517 AND grade = x_grade;
518 lv_rowid cur_rowid%ROWTYPE;
519 BEGIN
520 OPEN cur_rowid;
521 FETCH cur_rowid INTO lv_rowid;
522 IF (cur_rowid%FOUND) THEN
523 fnd_message.set_name ('IGS', 'IGS_AS_SUAAI_GSG_FK');
524 igs_ge_msg_stack.ADD;
525 CLOSE cur_rowid;
526 app_exception.raise_exception;
527 RETURN;
528 END IF;
529 CLOSE cur_rowid;
530 END get_fk_igs_as_grd_sch_grade;
531
532 -- Added by DDEY as a part of enhancement bug # 2162831
533 PROCEDURE get_fk_igs_lookups_view (x_outcome_comment_code IN VARCHAR2) AS
534 CURSOR cur_rowid IS
535 SELECT ROWID
536 FROM igs_as_su_atmpt_itm
537 WHERE outcome_comment_code = x_outcome_comment_code;
538 lv_rowid cur_rowid%ROWTYPE;
539 BEGIN
540 OPEN cur_rowid;
541 FETCH cur_rowid INTO lv_rowid;
542 IF (cur_rowid%FOUND) THEN
543 fnd_message.set_name ('IGS', 'IGS_AS_SUAAI_LOV_FK');
544 igs_ge_msg_stack.ADD;
545 CLOSE cur_rowid;
546 app_exception.raise_exception;
547 RETURN;
548 END IF;
549 CLOSE cur_rowid;
550 END get_fk_igs_lookups_view;
551
552 PROCEDURE get_ufk_igs_as_untas_pattern (x_ass_pattern_id IN NUMBER) AS
553 BEGIN
554 RETURN;
555 END get_ufk_igs_as_untas_pattern;
556
557 PROCEDURE before_dml (
558 p_action IN VARCHAR2,
559 x_rowid IN VARCHAR2 DEFAULT NULL,
560 x_person_id IN NUMBER DEFAULT NULL,
561 x_course_cd IN VARCHAR2 DEFAULT NULL,
562 x_unit_cd IN VARCHAR2 DEFAULT NULL,
563 x_cal_type IN VARCHAR2 DEFAULT NULL,
564 x_ci_sequence_number IN NUMBER DEFAULT NULL,
565 x_ass_id IN NUMBER DEFAULT NULL,
566 x_creation_dt IN DATE DEFAULT NULL,
567 x_attempt_number IN NUMBER DEFAULT NULL,
568 x_outcome_dt IN DATE DEFAULT NULL,
569 x_override_due_dt IN DATE DEFAULT NULL,
570 x_tracking_id IN NUMBER DEFAULT NULL,
571 x_logical_delete_dt IN DATE DEFAULT NULL,
572 x_s_default_ind IN VARCHAR2 DEFAULT NULL,
573 x_ass_pattern_id IN NUMBER DEFAULT NULL,
574 x_creation_date IN DATE DEFAULT NULL,
575 x_created_by IN NUMBER DEFAULT NULL,
576 x_last_update_date IN DATE DEFAULT NULL,
577 x_last_updated_by IN NUMBER DEFAULT NULL,
578 x_last_update_login IN NUMBER DEFAULT NULL,
579 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
580 x_gs_version_number IN NUMBER DEFAULT NULL,
581 x_grade IN VARCHAR2 DEFAULT NULL,
582 x_outcome_comment_code IN VARCHAR2 DEFAULT NULL,
583 x_mark IN NUMBER DEFAULT NULL,
584 x_attribute_category IN VARCHAR2 DEFAULT NULL,
585 x_attribute1 IN VARCHAR2 DEFAULT NULL,
586 x_attribute2 IN VARCHAR2 DEFAULT NULL,
587 x_attribute3 IN VARCHAR2 DEFAULT NULL,
588 x_attribute4 IN VARCHAR2 DEFAULT NULL,
589 x_attribute5 IN VARCHAR2 DEFAULT NULL,
590 x_attribute6 IN VARCHAR2 DEFAULT NULL,
591 x_attribute7 IN VARCHAR2 DEFAULT NULL,
592 x_attribute8 IN VARCHAR2 DEFAULT NULL,
593 x_attribute9 IN VARCHAR2 DEFAULT NULL,
594 x_attribute10 IN VARCHAR2 DEFAULT NULL,
595 x_attribute11 IN VARCHAR2 DEFAULT NULL,
596 x_attribute12 IN VARCHAR2 DEFAULT NULL,
597 x_attribute13 IN VARCHAR2 DEFAULT NULL,
598 x_attribute14 IN VARCHAR2 DEFAULT NULL,
599 x_attribute15 IN VARCHAR2 DEFAULT NULL,
600 x_attribute16 IN VARCHAR2 DEFAULT NULL,
601 x_attribute17 IN VARCHAR2 DEFAULT NULL,
602 x_attribute18 IN VARCHAR2 DEFAULT NULL,
603 x_attribute19 IN VARCHAR2 DEFAULT NULL,
604 x_attribute20 IN VARCHAR2 DEFAULT NULL,
605 x_uoo_id IN NUMBER DEFAULT NULL,
606 x_unit_section_ass_item_id IN NUMBER DEFAULT NULL,
607 x_unit_ass_item_id IN NUMBER DEFAULT NULL,
608 x_sua_ass_item_group_id IN NUMBER DEFAULT NULL,
609 x_midterm_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
610 x_midterm_weight_qty IN NUMBER DEFAULT NULL,
611 x_final_mandatory_type_code IN VARCHAR2 DEFAULT NULL,
612 x_final_weight_qty IN NUMBER DEFAULT NULL,
613 x_submitted_date IN DATE DEFAULT NULL,
614 x_waived_flag IN VARCHAR2 DEFAULT NULL,
615 x_penalty_applied_flag IN VARCHAR2 DEFAULT NULL
616 ) AS
617 BEGIN
618 set_column_values (
619 p_action,
620 x_rowid,
621 x_person_id,
622 x_course_cd,
623 x_unit_cd,
624 x_cal_type,
625 x_ci_sequence_number,
626 x_ass_id,
627 x_creation_dt,
628 x_attempt_number,
629 x_outcome_dt,
630 x_override_due_dt,
631 x_tracking_id,
632 x_logical_delete_dt,
633 x_s_default_ind,
634 x_ass_pattern_id,
635 x_creation_date,
636 x_created_by,
637 x_last_update_date,
638 x_last_updated_by,
639 x_last_update_login,
640 x_grading_schema_cd,
641 x_gs_version_number,
642 x_grade,
643 x_outcome_comment_code,
644 x_mark,
645 x_attribute_category,
646 x_attribute1,
647 x_attribute2,
648 x_attribute3,
649 x_attribute4,
650 x_attribute5,
651 x_attribute6,
652 x_attribute7,
653 x_attribute8,
654 x_attribute9,
655 x_attribute10,
656 x_attribute11,
657 x_attribute12,
658 x_attribute13,
659 x_attribute14,
660 x_attribute15,
661 x_attribute16,
662 x_attribute17,
663 x_attribute18,
664 x_attribute19,
665 x_attribute20,
666 x_uoo_id,
667 x_unit_section_ass_item_id,
668 x_unit_ass_item_id,
669 x_sua_ass_item_group_id,
670 x_midterm_mandatory_type_code,
671 x_midterm_weight_qty,
672 x_final_mandatory_type_code,
673 x_final_weight_qty,
674 x_submitted_date,
675 x_waived_flag,
676 x_penalty_applied_flag
677 );
678 IF (p_action = 'INSERT') THEN
679 -- Call all the procedures related to Before Insert.
680 beforerowinsertupdate1 (p_inserting => TRUE);
681 IF get_pk_for_validation (
682 new_references.course_cd,
683 new_references.person_id,
684 new_references.ass_id,
685 new_references.creation_dt,
686 new_references.uoo_id
687 ) THEN
688 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
689 igs_ge_msg_stack.ADD;
690 app_exception.raise_exception;
691 END IF;
692 check_constraints;
693 check_parent_existance;
694 ELSIF (p_action = 'UPDATE') THEN
695 -- Call all the procedures related to Before Update.
696 beforerowinsertupdate1 (p_updating => TRUE);
697 check_constraints;
698 check_parent_existance;
699 ELSIF (p_action = 'DELETE') THEN
700 -- Call all the procedures related to Before Delete.
701 NULL;
702 ELSIF (p_action = 'VALIDATE_INSERT') THEN
703 IF get_pk_for_validation (
704 new_references.course_cd,
705 new_references.person_id,
706 new_references.ass_id,
707 new_references.creation_dt,
708 new_references.uoo_id
709 ) THEN
710 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
711 igs_ge_msg_stack.ADD;
712 app_exception.raise_exception;
713 END IF;
714 check_constraints;
715 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
716 check_constraints;
717 ELSIF (p_action = 'VALIDATE_DELETE') THEN
718 check_child_existance;
719 END IF;
720 END before_dml;
721
722 PROCEDURE after_dml (p_action IN VARCHAR2, x_rowid IN VARCHAR2) AS
723 BEGIN
724 l_rowid := x_rowid;
725 IF (p_action = 'INSERT') THEN
726 -- Call all the procedures related to After Insert.
727 afterrowinsertupdate2 (p_inserting => TRUE);
728 ELSIF (p_action = 'UPDATE') THEN
729 -- Call all the procedures related to After Update.
730 afterrowinsertupdate2 (p_updating => TRUE);
731 END IF;
732 END after_dml;
733
734 PROCEDURE insert_row (
735 x_rowid IN OUT NOCOPY VARCHAR2,
736 x_person_id IN NUMBER,
737 x_course_cd IN VARCHAR2,
738 x_unit_cd IN VARCHAR2,
739 x_cal_type IN VARCHAR2,
740 x_ci_sequence_number IN NUMBER,
741 x_ass_id IN NUMBER,
742 x_creation_dt IN DATE,
743 x_attempt_number IN NUMBER,
744 x_outcome_dt IN DATE,
745 x_override_due_dt IN DATE,
746 x_tracking_id IN NUMBER,
747 x_logical_delete_dt IN DATE,
748 x_s_default_ind IN VARCHAR2,
749 x_ass_pattern_id IN NUMBER,
750 x_mode IN VARCHAR2 DEFAULT 'R',
751 x_grading_schema_cd IN VARCHAR2,
752 x_gs_version_number IN NUMBER,
753 x_grade IN VARCHAR2,
754 x_outcome_comment_code IN VARCHAR2,
755 x_mark IN NUMBER,
756 x_attribute_category IN VARCHAR2,
757 x_attribute1 IN VARCHAR2,
758 x_attribute2 IN VARCHAR2,
759 x_attribute3 IN VARCHAR2,
760 x_attribute4 IN VARCHAR2,
761 x_attribute5 IN VARCHAR2,
762 x_attribute6 IN VARCHAR2,
763 x_attribute7 IN VARCHAR2,
764 x_attribute8 IN VARCHAR2,
765 x_attribute9 IN VARCHAR2,
766 x_attribute10 IN VARCHAR2,
767 x_attribute11 IN VARCHAR2,
768 x_attribute12 IN VARCHAR2,
769 x_attribute13 IN VARCHAR2,
770 x_attribute14 IN VARCHAR2,
771 x_attribute15 IN VARCHAR2,
772 x_attribute16 IN VARCHAR2,
773 x_attribute17 IN VARCHAR2,
774 x_attribute18 IN VARCHAR2,
775 x_attribute19 IN VARCHAR2,
776 x_attribute20 IN VARCHAR2,
777 x_uoo_id IN NUMBER,
778 x_unit_section_ass_item_id IN NUMBER,
779 x_unit_ass_item_id IN NUMBER,
780 x_sua_ass_item_group_id IN NUMBER,
781 x_midterm_mandatory_type_code IN VARCHAR2,
782 x_midterm_weight_qty IN NUMBER,
783 x_final_mandatory_type_code IN VARCHAR2,
784 x_final_weight_qty IN NUMBER,
785 x_submitted_date IN DATE,
786 x_waived_flag IN VARCHAR2,
787 x_penalty_applied_flag IN VARCHAR2
788 ) AS
789 CURSOR c IS
790 SELECT ROWID
791 FROM igs_as_su_atmpt_itm
792 WHERE person_id = x_person_id
793 AND course_cd = x_course_cd
794 AND ass_id = x_ass_id
795 AND creation_dt = l_altered_creation_dt
796 AND uoo_id = x_uoo_id;
797 x_last_update_date DATE;
798 x_last_updated_by NUMBER;
799 x_last_update_login NUMBER;
800 x_request_id NUMBER;
801 x_program_id NUMBER;
802 x_program_application_id NUMBER;
803 x_program_update_date DATE;
804 BEGIN
805 IF (((SYSDATE - l_altered_creation_dt) * (24 * 60 * 60)) > 1) THEN
806 l_altered_creation_dt := SYSDATE;
807 ELSE
808 l_altered_creation_dt := NVL (l_altered_creation_dt, SYSDATE) + 1 / (24 * 60 * 60);
809 END IF;
810 x_last_update_date := SYSDATE;
811 IF (x_mode = 'I') THEN
812 x_last_updated_by := 1;
813 x_last_update_login := 0;
814 ELSIF (X_MODE IN ('R', 'S')) THEN
815 x_last_updated_by := fnd_global.user_id;
816 IF x_last_updated_by IS NULL THEN
817 x_last_updated_by := -1;
818 END IF;
819 x_last_update_login := fnd_global.login_id;
820 IF x_last_update_login IS NULL THEN
821 x_last_update_login := -1;
822 END IF;
823 x_request_id := fnd_global.conc_request_id;
824 x_program_id := fnd_global.conc_program_id;
825 x_program_application_id := fnd_global.prog_appl_id;
826 IF (x_request_id = -1) THEN
827 x_request_id := NULL;
828 x_program_id := NULL;
829 x_program_application_id := NULL;
830 x_program_update_date := NULL;
831 ELSE
832 x_program_update_date := SYSDATE;
833 END IF;
834 ELSE
835 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
836 igs_ge_msg_stack.ADD;
837 app_exception.raise_exception;
838 END IF;
839 before_dml (
840 p_action => 'INSERT',
841 x_rowid => x_rowid,
842 x_ass_id => x_ass_id,
843 x_ass_pattern_id => x_ass_pattern_id,
844 x_attempt_number => NVL (x_attempt_number, 1),
845 x_cal_type => x_cal_type,
846 x_ci_sequence_number => x_ci_sequence_number,
847 x_course_cd => x_course_cd,
848 x_creation_dt => l_altered_creation_dt,
849 x_logical_delete_dt => x_logical_delete_dt,
850 x_outcome_dt => x_outcome_dt,
851 x_override_due_dt => x_override_due_dt,
852 x_person_id => x_person_id,
853 x_s_default_ind => NVL (x_s_default_ind, 'N'),
854 x_tracking_id => x_tracking_id,
855 x_unit_cd => x_unit_cd,
856 x_creation_date => x_last_update_date,
857 x_created_by => x_last_updated_by,
858 x_last_update_date => x_last_update_date,
859 x_last_updated_by => x_last_updated_by,
860 x_last_update_login => x_last_update_login,
861 x_grading_schema_cd => x_grading_schema_cd,
862 x_gs_version_number => x_gs_version_number,
863 x_grade => x_grade,
864 x_outcome_comment_code => x_outcome_comment_code,
865 x_mark => x_mark,
866 x_attribute_category => x_attribute_category,
867 x_attribute1 => x_attribute1,
868 x_attribute2 => x_attribute2,
869 x_attribute3 => x_attribute3,
870 x_attribute4 => x_attribute4,
871 x_attribute5 => x_attribute5,
872 x_attribute6 => x_attribute6,
873 x_attribute7 => x_attribute7,
874 x_attribute8 => x_attribute8,
875 x_attribute9 => x_attribute9,
876 x_attribute10 => x_attribute10,
877 x_attribute11 => x_attribute11,
878 x_attribute12 => x_attribute12,
879 x_attribute13 => x_attribute13,
880 x_attribute14 => x_attribute14,
881 x_attribute15 => x_attribute15,
882 x_attribute16 => x_attribute16,
883 x_attribute17 => x_attribute17,
884 x_attribute18 => x_attribute18,
885 x_attribute19 => x_attribute19,
886 x_attribute20 => x_attribute20,
887 x_uoo_id => x_uoo_id,
888 x_unit_section_ass_item_id => x_unit_section_ass_item_id,
889 x_unit_ass_item_id => x_unit_ass_item_id,
890 x_sua_ass_item_group_id => x_sua_ass_item_group_id,
891 x_midterm_mandatory_type_code => x_midterm_mandatory_type_code,
892 x_midterm_weight_qty => x_midterm_weight_qty,
893 x_final_mandatory_type_code => x_final_mandatory_type_code,
894 x_final_weight_qty => x_final_weight_qty,
895 x_submitted_date => x_submitted_date,
896 x_waived_flag => x_waived_flag,
897 x_penalty_applied_flag => x_penalty_applied_flag
898 );
899
900 IF (x_mode = 'S') THEN
901 igs_sc_gen_001.set_ctx('R');
902 END IF;
903 INSERT INTO igs_as_su_atmpt_itm
904 (person_id, course_cd, unit_cd, cal_type,
905 ci_sequence_number, ass_id, creation_dt,
906 attempt_number, outcome_dt, override_due_dt,
907 tracking_id, logical_delete_dt, s_default_ind,
908 ass_pattern_id, creation_date, created_by, last_update_date,
909 last_updated_by, last_update_login, request_id, program_id, program_application_id,
910 program_update_date, grading_schema_cd, gs_version_number,
911 grade, outcome_comment_code, mark,
912 attribute_category, attribute1, attribute2,
913 attribute3, attribute4, attribute5,
914 attribute6, attribute7, attribute8,
915 attribute9, attribute10, attribute11,
916 attribute12, attribute13, attribute14,
917 attribute15, attribute16, attribute17,
918 attribute18, attribute19, attribute20,
919 uoo_id, unit_section_ass_item_id, unit_ass_item_id,
920 sua_ass_item_group_id, midterm_mandatory_type_code,
921 midterm_weight_qty, final_mandatory_type_code,
922 final_weight_qty, submitted_date, waived_flag,
923 penalty_applied_flag)
924 VALUES (new_references.person_id, new_references.course_cd, new_references.unit_cd, new_references.cal_type,
925 new_references.ci_sequence_number, new_references.ass_id, new_references.creation_dt,
926 new_references.attempt_number, new_references.outcome_dt, new_references.override_due_dt,
927 new_references.tracking_id, new_references.logical_delete_dt, new_references.s_default_ind,
928 new_references.ass_pattern_id, x_last_update_date, x_last_updated_by, x_last_update_date,
929 x_last_updated_by, x_last_update_login, x_request_id, x_program_id, x_program_application_id,
930 x_program_update_date, new_references.grading_schema_cd, new_references.gs_version_number,
931 new_references.grade, new_references.outcome_comment_code, new_references.mark,
932 new_references.attribute_category, new_references.attribute1, new_references.attribute2,
933 new_references.attribute3, new_references.attribute4, new_references.attribute5,
934 new_references.attribute6, new_references.attribute7, new_references.attribute8,
935 new_references.attribute9, new_references.attribute10, new_references.attribute11,
936 new_references.attribute12, new_references.attribute13, new_references.attribute14,
937 new_references.attribute15, new_references.attribute16, new_references.attribute17,
938 new_references.attribute18, new_references.attribute19, new_references.attribute20,
939 new_references.uoo_id, new_references.unit_section_ass_item_id, new_references.unit_ass_item_id,
940 new_references.sua_ass_item_group_id, new_references.midterm_mandatory_type_code,
941 new_references.midterm_weight_qty, new_references.final_mandatory_type_code,
942 new_references.final_weight_qty, new_references.submitted_date, new_references.waived_flag,
943 new_references.penalty_applied_flag);
944 IF (x_mode = 'S') THEN
945 igs_sc_gen_001.unset_ctx('R');
946 END IF;
947
948 OPEN c;
949 FETCH c INTO x_rowid;
950 IF (c%NOTFOUND) THEN
951 CLOSE c;
952 RAISE NO_DATA_FOUND;
953 END IF;
954 CLOSE c;
955 after_dml (p_action => 'INSERT', x_rowid => x_rowid);
956
957 EXCEPTION
958 WHEN OTHERS THEN
959 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
960 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
961 fnd_message.set_token ('ERR_CD', SQLCODE);
962 igs_ge_msg_stack.add;
963 igs_sc_gen_001.unset_ctx('R');
964 app_exception.raise_exception;
965 ELSE
966 igs_sc_gen_001.unset_ctx('R');
967 RAISE;
968 END IF;
969 END insert_row;
970
971 PROCEDURE lock_row (
972 x_rowid IN VARCHAR2,
973 x_person_id IN NUMBER,
974 x_course_cd IN VARCHAR2,
975 x_unit_cd IN VARCHAR2,
976 x_cal_type IN VARCHAR2,
977 x_ci_sequence_number IN NUMBER,
978 x_ass_id IN NUMBER,
979 x_creation_dt IN DATE,
980 x_attempt_number IN NUMBER,
981 x_outcome_dt IN DATE,
982 x_override_due_dt IN DATE,
983 x_tracking_id IN NUMBER,
984 x_logical_delete_dt IN DATE,
985 x_s_default_ind IN VARCHAR2,
986 x_ass_pattern_id IN NUMBER,
987 x_grading_schema_cd IN VARCHAR2,
988 x_gs_version_number IN NUMBER,
989 x_grade IN VARCHAR2,
990 x_outcome_comment_code IN VARCHAR2,
991 x_mark IN NUMBER,
992 x_attribute_category IN VARCHAR2,
993 x_attribute1 IN VARCHAR2,
994 x_attribute2 IN VARCHAR2,
995 x_attribute3 IN VARCHAR2,
996 x_attribute4 IN VARCHAR2,
997 x_attribute5 IN VARCHAR2,
998 x_attribute6 IN VARCHAR2,
999 x_attribute7 IN VARCHAR2,
1000 x_attribute8 IN VARCHAR2,
1001 x_attribute9 IN VARCHAR2,
1002 x_attribute10 IN VARCHAR2,
1003 x_attribute11 IN VARCHAR2,
1004 x_attribute12 IN VARCHAR2,
1005 x_attribute13 IN VARCHAR2,
1006 x_attribute14 IN VARCHAR2,
1007 x_attribute15 IN VARCHAR2,
1008 x_attribute16 IN VARCHAR2,
1009 x_attribute17 IN VARCHAR2,
1010 x_attribute18 IN VARCHAR2,
1011 x_attribute19 IN VARCHAR2,
1012 x_attribute20 IN VARCHAR2,
1013 x_uoo_id IN NUMBER,
1014 x_unit_section_ass_item_id IN NUMBER,
1015 x_unit_ass_item_id IN NUMBER,
1016 x_sua_ass_item_group_id IN NUMBER,
1017 x_midterm_mandatory_type_code IN VARCHAR2,
1018 x_midterm_weight_qty IN NUMBER,
1019 x_final_mandatory_type_code IN VARCHAR2,
1020 x_final_weight_qty IN NUMBER,
1021 x_submitted_date IN DATE,
1022 x_waived_flag IN VARCHAR2,
1023 x_penalty_applied_flag IN VARCHAR2
1024 ) AS
1025 CURSOR c1 IS
1026 SELECT attempt_number,
1027 outcome_dt,
1028 override_due_dt,
1029 tracking_id,
1030 logical_delete_dt,
1031 s_default_ind,
1032 ass_pattern_id,
1033 grading_schema_cd,
1034 gs_version_number,
1035 grade,
1036 outcome_comment_code,
1037 mark,
1038 attribute_category,
1039 attribute1,
1040 attribute2,
1041 attribute3,
1042 attribute4,
1043 attribute5,
1044 attribute6,
1045 attribute7,
1046 attribute8,
1047 attribute9,
1048 attribute10,
1049 attribute11,
1050 attribute12,
1051 attribute13,
1052 attribute14,
1053 attribute15,
1054 attribute16,
1055 attribute17,
1056 attribute18,
1057 attribute19,
1058 attribute20,
1059 uoo_id,
1060 unit_section_ass_item_id,
1061 unit_ass_item_id,
1062 sua_ass_item_group_id,
1063 midterm_mandatory_type_code,
1064 midterm_weight_qty,
1065 final_mandatory_type_code,
1066 final_weight_qty,
1067 submitted_date,
1068 waived_flag,
1069 penalty_applied_flag
1070 FROM igs_as_su_atmpt_itm
1071 WHERE ROWID = x_rowid
1072 FOR UPDATE NOWAIT;
1073 tlinfo c1%ROWTYPE;
1074 BEGIN
1075 OPEN c1;
1076 FETCH c1 INTO tlinfo;
1077 IF (c1%NOTFOUND) THEN
1078 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1079 igs_ge_msg_stack.ADD;
1080 app_exception.raise_exception;
1081 CLOSE c1;
1082 RETURN;
1083 END IF;
1084 CLOSE c1;
1085 IF ((tlinfo.attempt_number = x_attempt_number)
1086 AND ((TRUNC (tlinfo.outcome_dt) = TRUNC (x_outcome_dt))
1087 OR ((tlinfo.outcome_dt IS NULL)
1088 AND (x_outcome_dt IS NULL)
1089 )
1090 )
1091 AND ((TRUNC (tlinfo.override_due_dt) = TRUNC (x_override_due_dt))
1092 OR ((tlinfo.override_due_dt IS NULL)
1093 AND (x_override_due_dt IS NULL)
1094 )
1095 )
1096 AND ((tlinfo.tracking_id = x_tracking_id)
1097 OR ((tlinfo.tracking_id IS NULL)
1098 AND (x_tracking_id IS NULL)
1099 )
1100 )
1101 AND ((TRUNC (tlinfo.logical_delete_dt) = TRUNC (x_logical_delete_dt))
1102 OR ((tlinfo.logical_delete_dt IS NULL)
1103 AND (x_logical_delete_dt IS NULL)
1104 )
1105 )
1106 AND (tlinfo.s_default_ind = x_s_default_ind)
1107 AND ((tlinfo.ass_pattern_id = x_ass_pattern_id)
1108 OR ((tlinfo.ass_pattern_id IS NULL)
1109 AND (x_ass_pattern_id IS NULL)
1110 )
1111 )
1112 AND ((tlinfo.grading_schema_cd = x_grading_schema_cd)
1113 OR ((tlinfo.grading_schema_cd IS NULL)
1114 AND (x_grading_schema_cd IS NULL)
1115 )
1116 )
1117 AND ((tlinfo.gs_version_number = x_gs_version_number)
1118 OR ((tlinfo.gs_version_number IS NULL)
1119 AND (x_gs_version_number IS NULL)
1120 )
1121 )
1122 AND ((tlinfo.grade = x_grade)
1123 OR ((tlinfo.grade IS NULL)
1124 AND (x_grade IS NULL)
1125 )
1126 )
1127 AND ((tlinfo.outcome_comment_code = x_outcome_comment_code)
1128 OR ((tlinfo.outcome_comment_code IS NULL)
1129 AND (x_outcome_comment_code IS NULL)
1130 )
1131 )
1132 AND ((tlinfo.mark = x_mark)
1133 OR ((tlinfo.mark IS NULL)
1134 AND (x_mark IS NULL)
1135 )
1136 )
1137 AND ((tlinfo.attribute_category = x_attribute_category)
1138 OR ((tlinfo.attribute_category IS NULL)
1139 AND (x_attribute_category IS NULL)
1140 )
1141 )
1142 AND ((tlinfo.attribute1 = x_attribute1)
1143 OR ((tlinfo.attribute1 IS NULL)
1144 AND (x_attribute1 IS NULL)
1145 )
1146 )
1147 AND ((tlinfo.attribute2 = x_attribute2)
1148 OR ((tlinfo.attribute2 IS NULL)
1149 AND (x_attribute2 IS NULL)
1150 )
1151 )
1152 AND ((tlinfo.attribute3 = x_attribute3)
1153 OR ((tlinfo.attribute3 IS NULL)
1154 AND (x_attribute3 IS NULL)
1155 )
1156 )
1157 AND ((tlinfo.attribute4 = x_attribute4)
1158 OR ((tlinfo.attribute4 IS NULL)
1159 AND (x_attribute4 IS NULL)
1160 )
1161 )
1162 AND ((tlinfo.attribute5 = x_attribute5)
1163 OR ((tlinfo.attribute5 IS NULL)
1164 AND (x_attribute5 IS NULL)
1165 )
1166 )
1167 AND ((tlinfo.attribute6 = x_attribute6)
1168 OR ((tlinfo.attribute6 IS NULL)
1169 AND (x_attribute6 IS NULL)
1170 )
1171 )
1172 AND ((tlinfo.attribute7 = x_attribute7)
1173 OR ((tlinfo.attribute7 IS NULL)
1174 AND (x_attribute7 IS NULL)
1175 )
1176 )
1177 AND ((tlinfo.attribute8 = x_attribute8)
1178 OR ((tlinfo.attribute8 IS NULL)
1179 AND (x_attribute8 IS NULL)
1180 )
1181 )
1182 AND ((tlinfo.attribute9 = x_attribute9)
1183 OR ((tlinfo.attribute9 IS NULL)
1184 AND (x_attribute9 IS NULL)
1185 )
1186 )
1187 AND ((tlinfo.attribute10 = x_attribute10)
1188 OR ((tlinfo.attribute10 IS NULL)
1189 AND (x_attribute10 IS NULL)
1190 )
1191 )
1192 AND ((tlinfo.attribute11 = x_attribute11)
1193 OR ((tlinfo.attribute11 IS NULL)
1194 AND (x_attribute11 IS NULL)
1195 )
1196 )
1197 AND ((tlinfo.attribute12 = x_attribute12)
1198 OR ((tlinfo.attribute12 IS NULL)
1199 AND (x_attribute12 IS NULL)
1200 )
1201 )
1202 AND ((tlinfo.attribute13 = x_attribute13)
1203 OR ((tlinfo.attribute13 IS NULL)
1204 AND (x_attribute13 IS NULL)
1205 )
1206 )
1207 AND ((tlinfo.attribute14 = x_attribute14)
1208 OR ((tlinfo.attribute14 IS NULL)
1209 AND (x_attribute14 IS NULL)
1210 )
1211 )
1212 AND ((tlinfo.attribute15 = x_attribute15)
1213 OR ((tlinfo.attribute15 IS NULL)
1214 AND (x_attribute15 IS NULL)
1215 )
1216 )
1217 AND ((tlinfo.attribute16 = x_attribute16)
1218 OR ((tlinfo.attribute16 IS NULL)
1219 AND (x_attribute16 IS NULL)
1220 )
1221 )
1222 AND ((tlinfo.attribute17 = x_attribute17)
1223 OR ((tlinfo.attribute17 IS NULL)
1224 AND (x_attribute17 IS NULL)
1225 )
1226 )
1227 AND ((tlinfo.attribute18 = x_attribute18)
1228 OR ((tlinfo.attribute18 IS NULL)
1229 AND (x_attribute18 IS NULL)
1230 )
1231 )
1232 AND ((tlinfo.attribute19 = x_attribute19)
1233 OR ((tlinfo.attribute19 IS NULL)
1234 AND (x_attribute19 IS NULL)
1235 )
1236 )
1237 AND ((tlinfo.attribute20 = x_attribute20)
1238 OR ((tlinfo.attribute20 IS NULL)
1239 AND (x_attribute20 IS NULL)
1240 )
1241 )
1242 AND ((tlinfo.attribute19 = x_attribute19)
1243 OR ((tlinfo.attribute19 IS NULL)
1244 AND (x_attribute19 IS NULL)
1245 )
1246 )
1247 AND ((tlinfo.unit_section_ass_item_id = x_unit_section_ass_item_id)
1248 OR ((tlinfo.unit_section_ass_item_id IS NULL)
1249 AND (x_unit_section_ass_item_id IS NULL)
1250 )
1251 )
1252 AND ((tlinfo.unit_ass_item_id = x_unit_ass_item_id)
1253 OR ((tlinfo.unit_ass_item_id IS NULL)
1254 AND (x_unit_ass_item_id IS NULL)
1255 )
1256 )
1257 AND ((tlinfo.sua_ass_item_group_id = x_sua_ass_item_group_id)
1258 OR ((tlinfo.sua_ass_item_group_id IS NULL)
1259 AND (x_sua_ass_item_group_id IS NULL)
1260 )
1261 )
1262 AND ((tlinfo.midterm_mandatory_type_code = x_midterm_mandatory_type_code)
1263 OR ((tlinfo.midterm_mandatory_type_code IS NULL)
1264 AND (x_midterm_mandatory_type_code IS NULL)
1265 )
1266 )
1267 AND ((tlinfo.midterm_weight_qty = x_midterm_weight_qty)
1268 OR ((tlinfo.midterm_weight_qty IS NULL)
1269 AND (x_midterm_weight_qty IS NULL)
1270 )
1271 )
1272 AND ((tlinfo.final_mandatory_type_code = x_final_mandatory_type_code)
1273 OR ((tlinfo.final_mandatory_type_code IS NULL)
1274 AND (x_final_mandatory_type_code IS NULL)
1275 )
1276 )
1277 AND ((tlinfo.final_weight_qty = x_final_weight_qty)
1278 OR ((tlinfo.final_weight_qty IS NULL)
1279 AND (x_final_weight_qty IS NULL)
1280 )
1281 )
1282 AND ((tlinfo.submitted_date = x_submitted_date)
1283 OR ((tlinfo.submitted_date IS NULL)
1284 AND (x_submitted_date IS NULL)
1285 )
1286 )
1287 AND ((tlinfo.waived_flag = x_waived_flag)
1288 OR ((tlinfo.waived_flag IS NULL)
1289 AND (x_waived_flag IS NULL)
1290 )
1291 )
1292 AND ((tlinfo.penalty_applied_flag = x_penalty_applied_flag)
1293 OR ((tlinfo.penalty_applied_flag IS NULL)
1294 AND (x_penalty_applied_flag IS NULL)
1295 )
1296 )
1297 ) THEN
1298 NULL;
1299 ELSE
1300 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
1301 igs_ge_msg_stack.ADD;
1302 app_exception.raise_exception;
1303 END IF;
1304 RETURN;
1305 END lock_row;
1306
1307 PROCEDURE update_row (
1308 x_rowid IN VARCHAR2,
1309 x_person_id IN NUMBER,
1310 x_course_cd IN VARCHAR2,
1311 x_unit_cd IN VARCHAR2,
1312 x_cal_type IN VARCHAR2,
1313 x_ci_sequence_number IN NUMBER,
1314 x_ass_id IN NUMBER,
1315 x_creation_dt IN DATE,
1316 x_attempt_number IN NUMBER,
1317 x_outcome_dt IN DATE,
1318 x_override_due_dt IN DATE,
1319 x_tracking_id IN NUMBER,
1320 x_logical_delete_dt IN DATE,
1321 x_s_default_ind IN VARCHAR2,
1322 x_ass_pattern_id IN NUMBER,
1323 x_mode IN VARCHAR2 DEFAULT 'R',
1324 x_grading_schema_cd IN VARCHAR2,
1325 x_gs_version_number IN NUMBER,
1326 x_grade IN VARCHAR2,
1327 x_outcome_comment_code IN VARCHAR2,
1328 x_mark IN NUMBER,
1329 x_attribute_category IN VARCHAR2,
1330 x_attribute1 IN VARCHAR2,
1331 x_attribute2 IN VARCHAR2,
1332 x_attribute3 IN VARCHAR2,
1333 x_attribute4 IN VARCHAR2,
1334 x_attribute5 IN VARCHAR2,
1335 x_attribute6 IN VARCHAR2,
1336 x_attribute7 IN VARCHAR2,
1337 x_attribute8 IN VARCHAR2,
1338 x_attribute9 IN VARCHAR2,
1339 x_attribute10 IN VARCHAR2,
1340 x_attribute11 IN VARCHAR2,
1341 x_attribute12 IN VARCHAR2,
1342 x_attribute13 IN VARCHAR2,
1343 x_attribute14 IN VARCHAR2,
1344 x_attribute15 IN VARCHAR2,
1345 x_attribute16 IN VARCHAR2,
1346 x_attribute17 IN VARCHAR2,
1347 x_attribute18 IN VARCHAR2,
1348 x_attribute19 IN VARCHAR2,
1349 x_attribute20 IN VARCHAR2,
1350 x_uoo_id IN NUMBER,
1351 x_unit_section_ass_item_id IN NUMBER,
1352 x_unit_ass_item_id IN NUMBER,
1353 x_sua_ass_item_group_id IN NUMBER,
1354 x_midterm_mandatory_type_code IN VARCHAR2,
1355 x_midterm_weight_qty IN NUMBER,
1356 x_final_mandatory_type_code IN VARCHAR2,
1357 x_final_weight_qty IN NUMBER,
1358 x_submitted_date IN DATE,
1359 x_waived_flag IN VARCHAR2,
1360 x_penalty_applied_flag IN VARCHAR2
1361 ) AS
1362 x_last_update_date DATE;
1363 x_last_updated_by NUMBER;
1364 x_last_update_login NUMBER;
1365 x_request_id NUMBER;
1366 x_program_id NUMBER;
1367 x_program_application_id NUMBER;
1368 x_program_update_date DATE;
1369 BEGIN
1370 x_last_update_date := SYSDATE;
1371 IF (x_mode = 'I') THEN
1372 x_last_updated_by := 1;
1373 x_last_update_login := 0;
1374 ELSIF (X_MODE IN ('R', 'S')) THEN
1375 x_last_updated_by := fnd_global.user_id;
1376 IF x_last_updated_by IS NULL THEN
1377 x_last_updated_by := -1;
1378 END IF;
1379 x_last_update_login := fnd_global.login_id;
1380 IF x_last_update_login IS NULL THEN
1381 x_last_update_login := -1;
1382 END IF;
1383 ELSE
1384 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1385 igs_ge_msg_stack.ADD;
1386 app_exception.raise_exception;
1387 END IF;
1388 before_dml (
1389 p_action => 'UPDATE',
1390 x_rowid => x_rowid,
1391 x_ass_id => x_ass_id,
1392 x_ass_pattern_id => x_ass_pattern_id,
1393 x_attempt_number => x_attempt_number,
1394 x_cal_type => x_cal_type,
1395 x_ci_sequence_number => x_ci_sequence_number,
1396 x_course_cd => x_course_cd,
1397 x_creation_dt => x_creation_dt,
1398 x_logical_delete_dt => x_logical_delete_dt,
1399 x_outcome_dt => x_outcome_dt,
1400 x_override_due_dt => x_override_due_dt,
1401 x_person_id => x_person_id,
1402 x_s_default_ind => x_s_default_ind,
1403 x_tracking_id => x_tracking_id,
1404 x_unit_cd => x_unit_cd,
1405 x_creation_date => x_last_update_date,
1406 x_created_by => x_last_updated_by,
1407 x_last_update_date => x_last_update_date,
1408 x_last_updated_by => x_last_updated_by,
1409 x_last_update_login => x_last_update_login,
1410 x_grading_schema_cd => x_grading_schema_cd,
1411 x_gs_version_number => x_gs_version_number,
1412 x_grade => x_grade,
1413 x_outcome_comment_code => x_outcome_comment_code,
1414 x_mark => x_mark,
1415 x_attribute_category => x_attribute_category,
1416 x_attribute1 => x_attribute1,
1417 x_attribute2 => x_attribute2,
1418 x_attribute3 => x_attribute3,
1419 x_attribute4 => x_attribute4,
1420 x_attribute5 => x_attribute5,
1421 x_attribute6 => x_attribute6,
1422 x_attribute7 => x_attribute7,
1423 x_attribute8 => x_attribute8,
1424 x_attribute9 => x_attribute9,
1425 x_attribute10 => x_attribute10,
1426 x_attribute11 => x_attribute11,
1427 x_attribute12 => x_attribute12,
1428 x_attribute13 => x_attribute13,
1429 x_attribute14 => x_attribute14,
1430 x_attribute15 => x_attribute15,
1431 x_attribute16 => x_attribute16,
1432 x_attribute17 => x_attribute17,
1433 x_attribute18 => x_attribute18,
1434 x_attribute19 => x_attribute19,
1435 x_attribute20 => x_attribute20,
1436 x_uoo_id => x_uoo_id,
1437 x_unit_section_ass_item_id => x_unit_section_ass_item_id,
1438 x_unit_ass_item_id => x_unit_ass_item_id,
1439 x_sua_ass_item_group_id => x_sua_ass_item_group_id,
1440 x_midterm_mandatory_type_code => x_midterm_mandatory_type_code,
1441 x_midterm_weight_qty => x_midterm_weight_qty,
1442 x_final_mandatory_type_code => x_final_mandatory_type_code,
1443 x_final_weight_qty => x_final_weight_qty,
1444 x_submitted_date => x_submitted_date,
1445 x_waived_flag => x_waived_flag,
1446 x_penalty_applied_flag => x_penalty_applied_flag
1447 );
1448 IF (X_MODE IN ('R', 'S')) THEN
1449 x_request_id := fnd_global.conc_request_id;
1450 x_program_id := fnd_global.conc_program_id;
1451 x_program_application_id := fnd_global.prog_appl_id;
1452 IF (x_request_id = -1) THEN
1453 x_request_id := old_references.request_id;
1454 x_program_id := old_references.program_id;
1455 x_program_application_id := old_references.program_application_id;
1456 x_program_update_date := old_references.program_update_date;
1457 ELSE
1458 x_program_update_date := SYSDATE;
1459 END IF;
1460 END IF;
1461 IF (x_mode = 'S') THEN
1462 igs_sc_gen_001.set_ctx('R');
1463 END IF;
1464 UPDATE igs_as_su_atmpt_itm
1465 SET attempt_number = new_references.attempt_number,
1466 outcome_dt = new_references.outcome_dt,
1467 override_due_dt = new_references.override_due_dt,
1468 tracking_id = new_references.tracking_id,
1469 logical_delete_dt = new_references.logical_delete_dt,
1470 s_default_ind = new_references.s_default_ind,
1471 ass_pattern_id = new_references.ass_pattern_id,
1472 last_update_date = x_last_update_date,
1473 last_updated_by = x_last_updated_by,
1474 last_update_login = x_last_update_login,
1475 request_id = x_request_id,
1476 program_id = x_program_id,
1477 program_application_id = x_program_application_id,
1478 program_update_date = x_program_update_date,
1479 grading_schema_cd = new_references.grading_schema_cd,
1480 gs_version_number = new_references.gs_version_number,
1481 grade = new_references.grade,
1482 outcome_comment_code = new_references.outcome_comment_code,
1483 mark = new_references.mark,
1484 attribute_category = new_references.attribute_category,
1485 attribute1 = new_references.attribute1,
1486 attribute2 = new_references.attribute2,
1487 attribute3 = new_references.attribute3,
1488 attribute4 = new_references.attribute4,
1489 attribute5 = new_references.attribute5,
1490 attribute6 = new_references.attribute6,
1491 attribute7 = new_references.attribute7,
1492 attribute8 = new_references.attribute8,
1493 attribute9 = new_references.attribute9,
1494 attribute10 = new_references.attribute10,
1495 attribute11 = new_references.attribute11,
1496 attribute12 = new_references.attribute12,
1497 attribute13 = new_references.attribute13,
1498 attribute14 = new_references.attribute14,
1499 attribute15 = new_references.attribute15,
1500 attribute16 = new_references.attribute16,
1501 attribute17 = new_references.attribute17,
1502 attribute18 = new_references.attribute18,
1503 attribute19 = new_references.attribute19,
1504 attribute20 = new_references.attribute20,
1505 unit_section_ass_item_id = new_references.unit_section_ass_item_id,
1506 unit_ass_item_id = new_references.unit_ass_item_id,
1507 sua_ass_item_group_id = new_references.sua_ass_item_group_id,
1508 midterm_mandatory_type_code = new_references.midterm_mandatory_type_code,
1509 midterm_weight_qty = new_references.midterm_weight_qty,
1510 final_mandatory_type_code = new_references.final_mandatory_type_code,
1511 final_weight_qty = new_references.final_weight_qty,
1512 submitted_date = new_references.submitted_date,
1513 waived_flag = new_references.waived_flag,
1514 penalty_applied_flag = new_references.penalty_applied_flag
1515 WHERE ROWID = x_rowid;
1516 IF (SQL%NOTFOUND) THEN
1517 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1518 igs_ge_msg_stack.add;
1519 igs_sc_gen_001.unset_ctx('R');
1520 app_exception.raise_exception;
1521 END IF;
1522 IF (x_mode = 'S') THEN
1523 igs_sc_gen_001.unset_ctx('R');
1524 END IF;
1525
1526 after_dml (p_action => 'UPDATE', x_rowid => x_rowid);
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 IF (SQLCODE = (-28115)) THEN
1531 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1532 fnd_message.set_token ('ERR_CD', SQLCODE);
1533 igs_ge_msg_stack.add;
1534 igs_sc_gen_001.unset_ctx('R');
1535 app_exception.raise_exception;
1536 ELSE
1537 igs_sc_gen_001.unset_ctx('R');
1538 RAISE;
1539 END IF;
1540 END update_row;
1541
1542 PROCEDURE add_row (
1543 x_rowid IN OUT NOCOPY VARCHAR2,
1544 x_person_id IN NUMBER,
1545 x_course_cd IN VARCHAR2,
1546 x_unit_cd IN VARCHAR2,
1547 x_cal_type IN VARCHAR2,
1548 x_ci_sequence_number IN NUMBER,
1549 x_ass_id IN NUMBER,
1550 x_creation_dt IN DATE,
1551 x_attempt_number IN NUMBER,
1552 x_outcome_dt IN DATE,
1553 x_override_due_dt IN DATE,
1554 x_tracking_id IN NUMBER,
1555 x_logical_delete_dt IN DATE,
1556 x_s_default_ind IN VARCHAR2,
1557 x_ass_pattern_id IN NUMBER,
1558 x_mode IN VARCHAR2 DEFAULT 'R',
1559 x_grading_schema_cd IN VARCHAR2,
1560 x_gs_version_number IN NUMBER,
1561 x_grade IN VARCHAR2,
1562 x_outcome_comment_code IN VARCHAR2,
1563 x_mark IN NUMBER,
1564 x_attribute_category IN VARCHAR2,
1565 x_attribute1 IN VARCHAR2,
1566 x_attribute2 IN VARCHAR2,
1567 x_attribute3 IN VARCHAR2,
1568 x_attribute4 IN VARCHAR2,
1569 x_attribute5 IN VARCHAR2,
1570 x_attribute6 IN VARCHAR2,
1571 x_attribute7 IN VARCHAR2,
1572 x_attribute8 IN VARCHAR2,
1573 x_attribute9 IN VARCHAR2,
1574 x_attribute10 IN VARCHAR2,
1575 x_attribute11 IN VARCHAR2,
1576 x_attribute12 IN VARCHAR2,
1577 x_attribute13 IN VARCHAR2,
1578 x_attribute14 IN VARCHAR2,
1579 x_attribute15 IN VARCHAR2,
1580 x_attribute16 IN VARCHAR2,
1581 x_attribute17 IN VARCHAR2,
1582 x_attribute18 IN VARCHAR2,
1583 x_attribute19 IN VARCHAR2,
1584 x_attribute20 IN VARCHAR2,
1585 x_uoo_id IN NUMBER,
1586 x_unit_section_ass_item_id IN NUMBER,
1587 x_unit_ass_item_id IN NUMBER,
1588 x_sua_ass_item_group_id IN NUMBER,
1589 x_midterm_mandatory_type_code IN VARCHAR2,
1590 x_midterm_weight_qty IN NUMBER,
1591 x_final_mandatory_type_code IN VARCHAR2,
1592 x_final_weight_qty IN NUMBER,
1593 x_submitted_date IN DATE,
1594 x_waived_flag IN VARCHAR2,
1595 x_penalty_applied_flag IN VARCHAR2
1596 ) AS
1597 CURSOR c1 IS
1598 SELECT ROWID
1599 FROM igs_as_su_atmpt_itm
1600 WHERE person_id = x_person_id
1601 AND course_cd = x_course_cd
1602 AND ass_id = x_ass_id
1603 AND creation_dt = x_creation_dt
1604 AND uoo_id = x_uoo_id;
1605 BEGIN
1606 OPEN c1;
1607 FETCH c1 INTO x_rowid;
1608 IF (c1%NOTFOUND) THEN
1609 CLOSE c1;
1610 insert_row (
1611 x_rowid,
1612 x_person_id,
1613 x_course_cd,
1614 x_unit_cd,
1615 x_cal_type,
1616 x_ci_sequence_number,
1617 x_ass_id,
1618 x_creation_dt,
1619 x_attempt_number,
1620 x_outcome_dt,
1621 x_override_due_dt,
1622 x_tracking_id,
1623 x_logical_delete_dt,
1624 x_s_default_ind,
1625 x_ass_pattern_id,
1626 x_mode,
1627 x_grading_schema_cd,
1628 x_gs_version_number,
1629 x_grade,
1630 x_outcome_comment_code,
1631 x_mark,
1632 x_attribute_category,
1633 x_attribute1,
1634 x_attribute2,
1635 x_attribute3,
1636 x_attribute4,
1637 x_attribute5,
1638 x_attribute6,
1639 x_attribute7,
1640 x_attribute8,
1641 x_attribute9,
1642 x_attribute10,
1643 x_attribute11,
1644 x_attribute12,
1645 x_attribute13,
1646 x_attribute14,
1647 x_attribute15,
1648 x_attribute16,
1649 x_attribute17,
1650 x_attribute18,
1651 x_attribute19,
1652 x_attribute20,
1653 x_uoo_id,
1654 x_unit_section_ass_item_id,
1655 x_unit_ass_item_id,
1656 x_sua_ass_item_group_id,
1657 x_midterm_mandatory_type_code,
1658 x_midterm_weight_qty,
1659 x_final_mandatory_type_code,
1660 x_final_weight_qty,
1661 x_submitted_date,
1662 x_waived_flag,
1663 x_penalty_applied_flag
1664 );
1665 RETURN;
1666 END IF;
1667 CLOSE c1;
1668 update_row (
1669 x_rowid,
1670 x_person_id,
1671 x_course_cd,
1672 x_unit_cd,
1673 x_cal_type,
1674 x_ci_sequence_number,
1675 x_ass_id,
1676 x_creation_dt,
1677 x_attempt_number,
1678 x_outcome_dt,
1679 x_override_due_dt,
1680 x_tracking_id,
1681 x_logical_delete_dt,
1682 x_s_default_ind,
1683 x_ass_pattern_id,
1684 x_mode,
1685 x_grading_schema_cd,
1686 x_gs_version_number,
1687 x_grade,
1688 x_outcome_comment_code,
1689 x_mark,
1690 x_attribute_category,
1691 x_attribute1,
1692 x_attribute2,
1693 x_attribute3,
1694 x_attribute4,
1695 x_attribute5,
1696 x_attribute6,
1697 x_attribute7,
1698 x_attribute8,
1699 x_attribute9,
1700 x_attribute10,
1701 x_attribute11,
1702 x_attribute12,
1703 x_attribute13,
1704 x_attribute14,
1705 x_attribute15,
1706 x_attribute16,
1707 x_attribute17,
1708 x_attribute18,
1709 x_attribute19,
1710 x_attribute20,
1711 x_uoo_id,
1712 x_unit_section_ass_item_id,
1713 x_unit_ass_item_id,
1714 x_sua_ass_item_group_id,
1715 x_midterm_mandatory_type_code,
1716 x_midterm_weight_qty,
1717 x_final_mandatory_type_code,
1718 x_final_weight_qty,
1719 x_submitted_date,
1720 x_waived_flag,
1721 x_penalty_applied_flag
1722 );
1723 END add_row;
1724
1725 PROCEDURE delete_row (x_rowid IN VARCHAR2,
1726 x_mode IN VARCHAR2) AS
1727 BEGIN
1728 before_dml (p_action => 'DELETE', x_rowid => x_rowid);
1729 IF (x_mode = 'S') THEN
1730 igs_sc_gen_001.set_ctx('R');
1731 END IF;
1732 DELETE FROM igs_as_su_atmpt_itm
1733 WHERE ROWID = x_rowid;
1734 IF (SQL%NOTFOUND) THEN
1735 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1736 igs_ge_msg_stack.add;
1737 igs_sc_gen_001.unset_ctx('R');
1738 app_exception.raise_exception;
1739 END IF;
1740 IF (x_mode = 'S') THEN
1741 igs_sc_gen_001.unset_ctx('R');
1742 END IF;
1743
1744 after_dml (p_action => 'DELETE', x_rowid => x_rowid);
1745 END delete_row;
1746
1747 PROCEDURE check_constraints (column_name IN VARCHAR2 DEFAULT NULL, column_value IN VARCHAR2 DEFAULT NULL) AS
1748 BEGIN
1749 IF column_name IS NULL THEN
1750 NULL;
1751 ELSIF UPPER (column_name) = 'CAL_TYPE' THEN
1752 new_references.cal_type := column_value;
1753 ELSIF UPPER (column_name) = 'COURSE_CD' THEN
1754 new_references.course_cd := column_value;
1755 ELSIF UPPER (column_name) = 'UNIT_CD' THEN
1756 new_references.unit_cd := column_value;
1757 ELSIF UPPER (column_name) = 'S_DEFAULT_IND' THEN
1758 new_references.s_default_ind := column_value;
1759 END IF;
1760 IF UPPER (column_name) = 'CAL_TYPE'
1761 OR column_name IS NULL THEN
1762 IF new_references.cal_type <> UPPER (new_references.cal_type) THEN
1763 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1764 igs_ge_msg_stack.ADD;
1765 app_exception.raise_exception;
1766 END IF;
1767 END IF;
1768 IF UPPER (column_name) = 'COURSE_CD'
1769 OR column_name IS NULL THEN
1770 IF new_references.course_cd <> UPPER (new_references.course_cd) THEN
1771 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1772 igs_ge_msg_stack.ADD;
1773 app_exception.raise_exception;
1774 END IF;
1775 END IF;
1776 IF UPPER (column_name) = 'UNIT_CD'
1777 OR column_name IS NULL THEN
1778 IF new_references.unit_cd <> UPPER (new_references.unit_cd) THEN
1779 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1780 igs_ge_msg_stack.ADD;
1781 app_exception.raise_exception;
1782 END IF;
1783 END IF;
1784 IF UPPER (column_name) = 'S_DEFAULT_IND'
1785 OR column_name IS NULL THEN
1786 IF new_references.s_default_ind NOT IN ('Y', 'N') THEN
1787 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
1788 igs_ge_msg_stack.ADD;
1789 app_exception.raise_exception;
1790 END IF;
1791 END IF;
1792 END check_constraints;
1793 END igs_as_su_atmpt_itm_pkg;