[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SU_STMPTOUT_PKG
Source
1 PACKAGE BODY igs_as_su_stmptout_pkg AS
2 /* $Header: IGSDI26B.pls 120.3 2006/05/26 11:52:41 ijeddy ship $ */
3 l_rowid VARCHAR2 (25);
4 old_references igs_as_su_stmptout_all%ROWTYPE;
5 new_references igs_as_su_stmptout_all%ROWTYPE;
6 PROCEDURE set_column_values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2,
9 x_org_id IN NUMBER,
10 x_cal_type IN VARCHAR2,
11 x_ci_sequence_number IN NUMBER,
12 x_ci_start_dt IN DATE,
13 x_ci_end_dt IN DATE,
14 x_outcome_dt IN DATE,
15 x_grading_schema_cd IN VARCHAR2,
16 x_version_number IN NUMBER,
17 x_grade IN VARCHAR2,
18 x_s_grade_creation_method_type IN VARCHAR2,
19 x_finalised_outcome_ind IN VARCHAR2,
20 x_mark IN NUMBER,
21 x_translated_grading_schema_cd IN VARCHAR2,
22 x_translated_version_number IN NUMBER,
23 x_translated_grade IN VARCHAR2,
24 x_translated_dt IN DATE,
25 x_number_times_keyed IN NUMBER,
26 x_person_id IN NUMBER,
27 x_course_cd IN VARCHAR2,
28 x_unit_cd IN VARCHAR2,
29 x_creation_date IN DATE,
30 x_created_by IN NUMBER,
31 x_last_update_date IN DATE,
32 x_last_updated_by IN NUMBER,
33 x_last_update_login IN NUMBER,
34 x_grading_period_cd IN VARCHAR2,
35 x_attribute_category IN VARCHAR2,
36 x_attribute1 IN VARCHAR2,
37 x_attribute2 IN VARCHAR2,
38 x_attribute3 IN VARCHAR2,
39 x_attribute4 IN VARCHAR2,
40 x_attribute5 IN VARCHAR2,
41 x_attribute6 IN VARCHAR2,
42 x_attribute7 IN VARCHAR2,
43 x_attribute8 IN VARCHAR2,
44 x_attribute9 IN VARCHAR2,
45 x_attribute10 IN VARCHAR2,
46 x_attribute11 IN VARCHAR2,
47 x_attribute12 IN VARCHAR2,
48 x_attribute13 IN VARCHAR2,
49 x_attribute14 IN VARCHAR2,
50 x_attribute15 IN VARCHAR2,
51 x_attribute16 IN VARCHAR2,
52 x_attribute17 IN VARCHAR2,
53 x_attribute18 IN VARCHAR2,
54 x_attribute19 IN VARCHAR2,
55 x_attribute20 IN VARCHAR2,
56 x_incomp_deadline_date IN DATE,
57 x_incomp_grading_schema_cd IN VARCHAR2,
58 x_incomp_version_number IN NUMBER,
59 x_incomp_default_grade IN VARCHAR2,
60 x_incomp_default_mark IN NUMBER,
61 x_comments IN VARCHAR2,
62 x_uoo_id IN NUMBER,
63 x_mark_capped_flag IN VARCHAR2,
64 x_release_date IN DATE,
65 x_manual_override_flag IN VARCHAR2,
66 x_show_on_academic_histry_flag IN VARCHAR2
67 ) AS
68 CURSOR cur_old_ref_values IS
69 SELECT *
70 FROM igs_as_su_stmptout_all
71 WHERE ROWID = x_rowid;
72
73 CURSOR c_grade_type (l_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
74 l_version_number igs_as_grd_sch_grade.version_number%TYPE,
75 l_grade igs_as_grd_sch_grade.grade%TYPE) IS
76 SELECT 'X'
77 FROM igs_as_grd_sch_grade
78 WHERE grading_schema_cd = l_grading_schema_cd
79 AND version_number = l_version_number
80 and grade = l_grade
81 and s_result_type = 'INCOMP';
82 CURSOR c_org_unit_cd (L_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
83 SELECT owner_org_unit_cd FROM igs_ps_unit_ofr_opt_all WHERE uoo_id = l_uoo_id;
84 v_org_unit_cd igs_ps_unit_ofr_opt_all.owner_org_unit_cd%type;
85
86 CURSOR c_dead_line_with_org(l_incomplete_grade igs_as_inc_grd_cprof.incomplete_grade%TYPE,
87 l_org_unit_cd igs_as_inc_grd_cprof.org_unit_cd%TYPE,
88 l_grading_schema_cd igs_as_inc_grd_cprof.grading_schema_cd%TYPE,
89 l_version_number igs_as_inc_grd_cprof.version_number%TYPE) IS
90 SELECT incomplete_grade, org_unit_cd, comp_after_dt_alias, default_grade,
91 default_mark
92 FROM igs_as_inc_grd_cprof_v
93 WHERE incomplete_grade = l_incomplete_grade
94 AND org_unit_cd = l_org_unit_cd
95 AND grading_schema_cd = l_grading_schema_cd
96 AND version_number = l_version_number;
97
98 CURSOR c_dead_line(l_incomplete_grade igs_as_inc_grd_cprof.incomplete_grade%TYPE,
99 l_grading_schema_cd igs_as_inc_grd_cprof.grading_schema_cd%TYPE,
100 l_version_number igs_as_inc_grd_cprof.version_number%TYPE) IS
101 SELECT incomplete_grade, org_unit_cd, comp_after_dt_alias, default_grade,
102 default_mark
103 FROM igs_as_inc_grd_cprof_v
104 WHERE incomplete_grade = l_incomplete_grade
105 AND grading_schema_cd = l_grading_schema_cd
106 AND version_number = l_version_number;
107
108 CURSOR c_dt_alias_val(l_dt_alias igs_ca_da_inst.dt_alias%TYPE,
109 l_cal_type igs_ca_da_inst.cal_type%TYPE,
110 l_ci_sequence_number igs_ca_da_inst.ci_sequence_number%TYPE) IS
111 SELECT max(IGS_CA_GEN_001.calp_get_alias_val(dt_alias, sequence_number, cal_type, ci_sequence_number))
112 FROM igs_ca_da_inst
113 WHERE dt_alias = l_dt_alias
114 AND cal_type = l_cal_type
115 AND ci_sequence_number = l_ci_sequence_number;
116
117 v_dead_line c_dead_line%ROWTYPE;
118 temp VARCHAR2(1);
119
120 BEGIN
121 l_rowid := x_rowid;
122 -- Code for setting the Old and New Reference Values.
123 -- Populate Old Values.
124 OPEN cur_old_ref_values;
125 FETCH cur_old_ref_values INTO old_references;
126 IF (cur_old_ref_values%NOTFOUND)
127 AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
128 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
129 igs_ge_msg_stack.ADD;
130 CLOSE cur_old_ref_values;
131 app_exception.raise_exception;
132 RETURN;
133 END IF;
134 CLOSE cur_old_ref_values;
135 -- Populate New Values.
136
137
138
139 new_references.org_id := x_org_id;
140 new_references.cal_type := x_cal_type;
141 new_references.ci_sequence_number := x_ci_sequence_number;
142 new_references.ci_start_dt := x_ci_start_dt;
143 new_references.ci_end_dt := x_ci_end_dt;
144 new_references.outcome_dt := x_outcome_dt;
145 new_references.grading_schema_cd := x_grading_schema_cd;
146 new_references.version_number := x_version_number;
147 new_references.grade := x_grade;
148 new_references.s_grade_creation_method_type := x_s_grade_creation_method_type;
149 new_references.finalised_outcome_ind := x_finalised_outcome_ind;
150 new_references.mark := x_mark;
151 new_references.translated_grading_schema_cd := x_translated_grading_schema_cd;
152 new_references.translated_version_number := x_translated_version_number;
153 new_references.translated_grade := x_translated_grade;
154 new_references.translated_dt := x_translated_dt;
155 new_references.number_times_keyed := x_number_times_keyed;
156 new_references.person_id := x_person_id;
157 new_references.course_cd := x_course_cd;
158 new_references.unit_cd := x_unit_cd;
159 new_references.grading_period_cd := x_grading_period_cd;
160 new_references.attribute_category := x_attribute_category;
161 new_references.attribute1 := x_attribute1;
162 new_references.attribute2 := x_attribute2;
163 new_references.attribute3 := x_attribute3;
164 new_references.attribute4 := x_attribute4;
165 new_references.attribute5 := x_attribute5;
166 new_references.attribute6 := x_attribute6;
167 new_references.attribute7 := x_attribute7;
168 new_references.attribute8 := x_attribute8;
169 new_references.attribute9 := x_attribute9;
170 new_references.attribute10 := x_attribute10;
171 new_references.attribute11 := x_attribute11;
172 new_references.attribute12 := x_attribute12;
173 new_references.attribute13 := x_attribute13;
174 new_references.attribute14 := x_attribute14;
175 new_references.attribute15 := x_attribute15;
176 new_references.attribute16 := x_attribute16;
177 new_references.attribute17 := x_attribute17;
178 new_references.attribute18 := x_attribute18;
179 new_references.attribute19 := x_attribute19;
180 new_references.attribute20 := x_attribute20;
181 new_references.comments := x_comments;
182 new_references.uoo_id := x_uoo_id;
183 new_references.mark_capped_flag := x_mark_capped_flag;
184 new_references.release_date := x_release_date;
185 new_references.manual_override_flag := x_manual_override_flag;
186 new_references.show_on_academic_histry_flag := x_show_on_academic_histry_flag;
187
188 OPEN c_grade_type(x_grading_schema_cd,x_version_number,x_grade);
189 FETCH c_grade_type INTO temp;
190 IF c_grade_type%FOUND
191 AND x_incomp_deadline_date IS NULL
192 AND x_incomp_default_grade IS NULL
193 THEN
194 OPEN c_org_unit_cd (x_uoo_id);
195 FETCH c_org_unit_cd INTO v_org_unit_cd;
196 CLOSE c_org_unit_cd;
197 IF v_org_unit_cd IS NULL THEN
198 OPEN c_dead_line(x_grade, x_grading_schema_cd,x_version_number);
199 FETCH c_dead_line INTO v_dead_line;
200 CLOSE c_dead_line;
201 ELSE
202 OPEN c_dead_line_with_org(x_grade, v_org_unit_cd,x_grading_schema_cd,x_version_number);
203 FETCH c_dead_line_with_org INTO v_dead_line;
204 CLOSE c_dead_line_with_org;
205 END IF;
206 OPEN c_dt_alias_val(v_dead_line.comp_after_dt_alias, x_cal_type, x_ci_sequence_number);
207 FETCH c_dt_alias_val INTO new_references.incomp_deadline_date ;
208 CLOSE c_dt_alias_val ;
209 new_references.incomp_grading_schema_cd := x_grading_schema_cd;
210 new_references.incomp_version_number := x_version_number;
211 new_references.incomp_default_grade := v_dead_line.default_grade;
212 new_references.incomp_default_mark := v_dead_line.default_mark;
213 ELSIF c_grade_type%NOTFOUND THEN
214 new_references.incomp_deadline_date := NULL;
215 new_references.incomp_grading_schema_cd := NULL;
216 new_references.incomp_version_number := NULL;
217 new_references.incomp_default_grade := NULL;
218 new_references.incomp_default_mark := NULL;
219 ELSE
220 new_references.incomp_deadline_date := x_incomp_deadline_date;
221 new_references.incomp_grading_schema_cd := x_incomp_grading_schema_cd;
222 new_references.incomp_version_number := x_incomp_version_number;
223 new_references.incomp_default_grade := x_incomp_default_grade;
224 new_references.incomp_default_mark := x_incomp_default_mark;
225 END IF;
226 CLOSE c_grade_type;
227
228 IF (p_action = 'UPDATE') THEN
229 new_references.creation_date := old_references.creation_date;
230 new_references.created_by := old_references.created_by;
231 ELSE
232 new_references.creation_date := x_creation_date;
233 new_references.created_by := x_created_by;
234 END IF;
235 new_references.last_update_date := x_last_update_date;
236 new_references.last_updated_by := x_last_updated_by;
237 new_references.last_update_login := x_last_update_login;
238 END set_column_values;
239
240 PROCEDURE beforerowinsertupdate1 (p_inserting IN BOOLEAN, p_updating IN BOOLEAN, p_deleting IN BOOLEAN) AS
241 v_sequence_number NUMBER;
242 BEGIN
243 -- If a finalised outcome has been altered by either an insert,update or delete
244 -- then flag the student as requiring a IGS_RU_RULE check. IGS_GE_NOTE: Discontinuation
245 -- grades are not processed as they are handled by the SUA trigger.
246 IF (p_inserting
247 AND new_references.finalised_outcome_ind = 'Y'
248 )
249 OR (p_updating
250 AND (old_references.finalised_outcome_ind = 'Y'
251 OR new_references.finalised_outcome_ind = 'Y'
252 )
253 ) THEN
254 IF p_inserting
255 OR p_updating THEN
256 IF new_references.s_grade_creation_method_type <> 'DISCONTIN' THEN
257 v_sequence_number := igs_ge_gen_003.genp_ins_stdnt_todo (new_references.person_id, 'UNIT-RULES', NULL, 'Y');
258 IF p_inserting THEN
259 igs_pr_gen_004.igs_pr_ins_suao_todo (
260 new_references.person_id,
261 new_references.course_cd,
262 NULL, --new_references.version_number,
263 new_references.unit_cd,
264 new_references.cal_type,
265 new_references.ci_sequence_number,
266 new_references.grading_schema_cd,
267 new_references.grading_schema_cd,
268 new_references.version_number,
269 new_references.version_number,
270 new_references.grade,
271 new_references.grade,
272 new_references.mark,
273 new_references.mark,
274 new_references.finalised_outcome_ind,
275 new_references.finalised_outcome_ind,
276 new_references.uoo_id
277 );
278 ELSIF p_updating THEN
279 igs_pr_gen_004.igs_pr_ins_suao_todo (
280 new_references.person_id,
281 new_references.course_cd,
282 NULL, --new_references.version_number,
283 new_references.unit_cd,
284 new_references.cal_type,
285 new_references.ci_sequence_number,
286 old_references.grading_schema_cd,
287 new_references.grading_schema_cd,
288 old_references.version_number,
289 new_references.version_number,
290 old_references.grade,
291 new_references.grade,
292 old_references.mark,
293 new_references.mark,
294 old_references.finalised_outcome_ind,
295 new_references.finalised_outcome_ind,
296 new_references.uoo_id
297 );
298 END IF;
299 END IF;
300 ELSE
301 IF old_references.s_grade_creation_method_type <> 'DISCONTIN' THEN
302 v_sequence_number := igs_ge_gen_003.genp_ins_stdnt_todo (new_references.person_id, 'UNIT-RULES', NULL, 'Y');
303 IF p_inserting THEN
304 igs_pr_gen_004.igs_pr_ins_suao_todo (
305 new_references.person_id,
306 new_references.course_cd,
307 NULL, --new_references.version_number,
308 old_references.unit_cd,
309 old_references.cal_type,
310 old_references.ci_sequence_number,
311 old_references.grading_schema_cd,
312 old_references.grading_schema_cd,
313 old_references.version_number,
314 old_references.version_number,
315 old_references.grade,
316 old_references.grade,
317 old_references.mark,
318 old_references.mark,
319 old_references.finalised_outcome_ind,
320 old_references.finalised_outcome_ind,
321 old_references.uoo_id
322 );
323 END IF;
324 END IF;
325 END IF;
326 END IF;
327 IF p_inserting THEN
328 -- Get the calendar start/end dates.
329 igs_ca_gen_001.calp_get_ci_dates (
330 new_references.cal_type,
331 new_references.ci_sequence_number,
332 new_references.ci_start_dt,
333 new_references.ci_end_dt
334 );
335 END IF;
336 END beforerowinsertupdate1;
337
338 -------------------------------------------------------------------------------------------
339 --Change History:
340 --Who When What
341 --svanukur 29-APR-03 Passed uoo_id to IGS_EN_GEN_007.ENRP_GET_SUA_STATUS , IGS_AS_GEN_005.ASSP_SET_SUAO_TRANS,
342 -- IGS_AS_GEN_007.ASSP_INS_SUAO_HIST, IGS_AS_GEN_005.ASSP_SET_SUAO_TRANS as part of MUS build, # 2829262
343 --rvangala 07-OCT-03 Passing core_indicator_code to IGS_EN_SUA-API.UPDATE_UNIT_ATTEMPT added as part of Prevent Dropping Core Units. Enh Bug# 3052432
344 --IJEDDY 5/26/2006 Bug 5243080, put in a check to retain atleast one finalized outcome for a completed transfered unit.
345 -------------------------------------------------------------------------------------------
346 PROCEDURE afterrowinsertupdate2 (p_inserting IN BOOLEAN, p_updating IN BOOLEAN, p_deleting IN BOOLEAN) AS
347 v_message_name VARCHAR2 (30);
348 v_rowid_saved BOOLEAN := FALSE;
349 v_grade igs_as_su_stmptout_all.grade%TYPE;
350 CURSOR c_sua (
351 cp_person_id igs_en_su_attempt.person_id%TYPE,
352 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
353 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE
354 ) IS
355 SELECT sua.*,
356 sua.ROWID
357 FROM igs_en_su_attempt sua
358 WHERE sua.person_id = cp_person_id
359 AND sua.course_cd = cp_course_cd
360 AND sua.uoo_id = cp_uoo_id;
361 v_sua_rec c_sua%ROWTYPE;
362 v_person_id igs_as_su_stmptout_all.person_id%TYPE;
363 v_course_cd igs_as_su_stmptout_all.course_cd%TYPE;
364 v_unit_cd igs_as_su_stmptout_all.unit_cd%TYPE;
365 v_cal_type igs_as_su_stmptout_all.cal_type%TYPE;
366 v_ci_sequence_number igs_as_su_stmptout_all.ci_sequence_number%TYPE;
367 v_translated_grading_schema_cd igs_as_su_stmptout_all.translated_grading_schema_cd%TYPE;
368 v_translated_version_number igs_as_su_stmptout_all.translated_version_number%TYPE;
369 v_translated_grade igs_as_su_stmptout_all.translated_grade%TYPE;
370 v_translated_dt igs_as_su_stmptout_all.translated_dt%TYPE;
371 v_sua_status igs_en_su_attempt.unit_attempt_status%TYPE;
372
373 --
374 -- Check if there is a transfer for Unit
375 --
376 CURSOR cur_sut (
377 cp_person_id IN NUMBER,
378 cp_course_cd IN VARCHAR2,
379 cp_uoo_id IN VARCHAR2
380 ) IS
381 SELECT 'Y' transfer_exists
382 FROM igs_ps_stdnt_unt_trn sut1
383 WHERE sut1.person_id = cp_person_id
384 AND sut1.transfer_course_cd = cp_course_cd
385 AND sut1.uoo_id = cp_uoo_id
386 AND EXISTS ( SELECT 'X'
387 FROM igs_en_su_attempt_all sua
388 WHERE sua.person_id = sut1.person_id
389 AND sua.course_cd = sut1.transfer_course_cd
390 AND sua.uoo_id = sut1.uoo_id
391 AND sua.unit_attempt_status IN ('COMPLETED', 'DISCONTIN')
392 AND EXISTS (SELECT 'X'
393 FROM igs_en_su_attempt_all sua
394 WHERE sua.person_id = sut1.person_id
395 AND sua.course_cd = sut1.course_cd
396 AND sua.uoo_id = sut1.uoo_id
397 AND sua.unit_attempt_status = 'DUPLICATE'))
398 AND sut1.transfer_dt = (SELECT MAX (sut2.transfer_dt)
399 FROM igs_ps_stdnt_unt_trn sut2
400 WHERE sut2.person_id = sut1.person_id
401 AND sut2.transfer_course_cd = sut1.transfer_course_cd
402 AND sut2.uoo_id = sut1.uoo_id)
403 AND sut1.transfer_dt > (SELECT NVL (MAX (sut3.transfer_dt),( sut1.transfer_dt - 1))
404 FROM igs_ps_stdnt_unt_trn sut3
405 WHERE sut3.person_id = sut1.person_id
406 AND sut3.course_cd = sut1.transfer_course_cd
407 AND sut3.uoo_id = sut1.uoo_id);
408 --
409 rec_sut cur_sut%ROWTYPE;
410
411 BEGIN
412 --
413 -- Update of student Course Attempt after Student Unit Attempt is posted
414 -- to the database
415 --
416 IF v_rowid_saved = FALSE THEN
417 v_grade := new_references.grade;
418 IF p_updating
419 AND (new_references.grade = old_references.grade)
420 AND (new_references.grading_schema_cd = old_references.grading_schema_cd)
421 AND (new_references.version_number = old_references.version_number) THEN
422 --
423 -- Clear the grade to indicate not to perform translation in
424 -- assp_prc_suao_rowids as the grade has not altered.
425 --
426 v_grade := NULL;
427 END IF;
428 IF p_deleting THEN
429 OPEN c_sua (old_references.person_id, old_references.course_cd, old_references.uoo_id);
430 ELSE
431 OPEN c_sua (new_references.person_id, new_references.course_cd, new_references.uoo_id);
432 END IF;
433 FETCH c_sua INTO v_sua_rec;
434 IF (c_sua%NOTFOUND) THEN
435 CLOSE c_sua;
436 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
437 igs_ge_msg_stack.ADD;
438 app_exception.raise_exception;
439 END IF;
440 CLOSE c_sua;
441 --
442 -- Added 'waitlist_dt' as a parameter to call IGS_EN_GEN_007.ENRP_GET_SUA_STATUS.
443 -- This is as per the Bug# 2335455
444 --
445 v_sua_status := igs_en_gen_007.enrp_get_sua_status (
446 v_sua_rec.person_id,
447 v_sua_rec.course_cd,
448 v_sua_rec.unit_cd,
449 v_sua_rec.version_number,
450 v_sua_rec.cal_type,
451 v_sua_rec.ci_sequence_number,
452 v_sua_rec.unit_attempt_status,
453 v_sua_rec.enrolled_dt,
454 v_sua_rec.rule_waived_dt,
455 v_sua_rec.discontinued_dt,
456 v_sua_rec.waitlist_dt,
457 v_sua_rec.uoo_id
458 );
459 --
460 -- Update the Student Unit Attempt status only if it has changed.
461 --
462 IF (v_sua_status <> v_sua_rec.unit_attempt_status) THEN
463 IF v_sua_status <> 'COMPLETED' AND v_sua_rec.unit_attempt_status = 'COMPLETED' THEN
464 OPEN cur_sut (v_sua_rec.person_id,
465 v_sua_rec.course_cd,
466 v_sua_rec.uoo_id);
467 FETCH cur_sut INTO rec_sut;
468 CLOSE cur_sut;
469 IF (NVL (rec_sut.transfer_exists, 'N') = 'Y') THEN
470 -- Error.
471 fnd_message.set_name ('IGS', 'IGS_AS_COMPL_UAO_NOTUPD');
472 igs_ge_msg_stack.ADD;
473 app_exception.raise_exception;
474 END IF;
475 END IF;
476 igs_en_sua_api.update_unit_attempt (
477 x_mode => 'R',
478 x_rowid => v_sua_rec.ROWID,
479 x_person_id => v_sua_rec.person_id,
480 x_course_cd => v_sua_rec.course_cd,
481 x_unit_cd => v_sua_rec.unit_cd,
482 x_cal_type => v_sua_rec.cal_type,
483 x_ci_sequence_number => v_sua_rec.ci_sequence_number,
484 x_version_number => v_sua_rec.version_number,
485 x_location_cd => v_sua_rec.location_cd,
486 x_unit_class => v_sua_rec.unit_class,
487 x_ci_start_dt => v_sua_rec.ci_start_dt,
488 x_ci_end_dt => v_sua_rec.ci_end_dt,
489 x_uoo_id => v_sua_rec.uoo_id,
490 x_enrolled_dt => v_sua_rec.enrolled_dt,
491 x_unit_attempt_status => v_sua_status,
492 x_administrative_unit_status => v_sua_rec.administrative_unit_status,
493 x_administrative_priority => v_sua_rec.administrative_priority,
494 x_discontinued_dt => v_sua_rec.discontinued_dt,
495 x_dcnt_reason_cd => v_sua_rec.dcnt_reason_cd,
496 x_rule_waived_dt => v_sua_rec.rule_waived_dt,
497 x_rule_waived_person_id => v_sua_rec.rule_waived_person_id,
498 x_no_assessment_ind => v_sua_rec.no_assessment_ind,
499 x_sup_unit_cd => v_sua_rec.sup_unit_cd,
500 x_sup_version_number => v_sua_rec.sup_version_number,
501 x_exam_location_cd => v_sua_rec.exam_location_cd,
502 x_alternative_title => v_sua_rec.alternative_title,
503 x_override_enrolled_cp => v_sua_rec.override_enrolled_cp,
504 x_override_eftsu => v_sua_rec.override_eftsu,
505 x_override_achievable_cp => v_sua_rec.override_achievable_cp,
506 x_override_outcome_due_dt => v_sua_rec.override_outcome_due_dt,
507 x_override_credit_reason => v_sua_rec.override_credit_reason,
508 x_waitlist_dt => v_sua_rec.waitlist_dt,
509 x_gs_version_number => v_sua_rec.gs_version_number,
510 x_enr_method_type => v_sua_rec.enr_method_type,
511 x_failed_unit_rule => v_sua_rec.failed_unit_rule,
512 x_cart => v_sua_rec.cart,
513 x_rsv_seat_ext_id => v_sua_rec.rsv_seat_ext_id,
514 x_org_unit_cd => v_sua_rec.org_unit_cd,
515 x_session_id => v_sua_rec.session_id,
516 x_grading_schema_code => v_sua_rec.grading_schema_code,
517 x_deg_aud_detail_id => v_sua_rec.deg_aud_detail_id,
518 x_subtitle => v_sua_rec.subtitle,
519 x_student_career_transcript => v_sua_rec.student_career_transcript,
520 x_student_career_statistics => v_sua_rec.student_career_statistics,
521 x_waitlist_manual_ind => v_sua_rec.waitlist_manual_ind,
522 x_attribute_category => v_sua_rec.attribute_category,
523 x_attribute1 => v_sua_rec.attribute1,
524 x_attribute2 => v_sua_rec.attribute2,
525 x_attribute3 => v_sua_rec.attribute3,
526 x_attribute4 => v_sua_rec.attribute4,
527 x_attribute5 => v_sua_rec.attribute5,
528 x_attribute6 => v_sua_rec.attribute6,
529 x_attribute7 => v_sua_rec.attribute7,
530 x_attribute8 => v_sua_rec.attribute8,
531 x_attribute9 => v_sua_rec.attribute9,
532 x_attribute10 => v_sua_rec.attribute10,
533 x_attribute11 => v_sua_rec.attribute11,
534 x_attribute12 => v_sua_rec.attribute12,
535 x_attribute13 => v_sua_rec.attribute13,
536 x_attribute14 => v_sua_rec.attribute14,
537 x_attribute15 => v_sua_rec.attribute15,
538 x_attribute16 => v_sua_rec.attribute16,
539 x_attribute17 => v_sua_rec.attribute17,
540 x_attribute18 => v_sua_rec.attribute18,
541 x_attribute19 => v_sua_rec.attribute19,
542 x_attribute20 => v_sua_rec.attribute20,
543 x_wlst_priority_weight_num => v_sua_rec.wlst_priority_weight_num,
544 x_wlst_preference_weight_num => v_sua_rec.wlst_preference_weight_num,
545 -- core_indicator_code added by rvangala 07-OCT-2003. Enh Bug# 3052432
546 x_core_indicator_code => v_sua_rec.core_indicator_code
547 );
548 END IF;
549 -- Determine if the translation fields are to be updated.
550 -- If grade has been altered, then the pl/sql table grade will be set.
551 IF v_grade IS NOT NULL THEN
552 v_translated_grading_schema_cd := new_references.translated_grading_schema_cd;
553 v_translated_version_number := new_references.translated_version_number;
554 v_translated_grade := new_references.translated_grade;
555 v_translated_dt := new_references.translated_dt;
556 -- Determine if translation has been done and set the fields accordingly.
557 IF igs_as_gen_005.assp_set_suao_trans (
558 new_references.person_id,
559 new_references.course_cd,
560 new_references.unit_cd,
561 new_references.cal_type,
562 new_references.ci_sequence_number,
563 new_references.outcome_dt,
564 new_references.grade,
565 new_references.grading_schema_cd,
566 new_references.version_number,
567 v_translated_grading_schema_cd,
568 v_translated_version_number,
569 v_translated_grade,
570 v_translated_dt,
571 v_message_name,
572 new_references.uoo_id
573 ) = FALSE THEN
574 -- Error.
575 fnd_message.set_name ('IGS', v_message_name);
576 igs_ge_msg_stack.ADD;
577 app_exception.raise_exception;
578 END IF;
579 -- Not necessary to check for lock as in the process of insert/updating
580 -- anyway, so will already have a lock.
581 UPDATE igs_as_su_stmptout_all
582 SET translated_grading_schema_cd = v_translated_grading_schema_cd,
583 translated_version_number = v_translated_version_number,
584 translated_grade = v_translated_grade,
585 translated_dt = v_translated_dt
586 WHERE person_id = new_references.person_id
587 AND course_cd = new_references.course_cd
588 AND outcome_dt = new_references.outcome_dt
589 AND grading_period_cd = new_references.grading_period_cd
590 AND uoo_id = new_references.uoo_id;
591 END IF;
592 END IF;
593 IF p_updating THEN
594 igs_as_gen_007.assp_ins_suao_hist (
595 old_references.person_id,
596 old_references.course_cd,
597 old_references.unit_cd,
598 old_references.cal_type,
599 old_references.ci_sequence_number,
600 old_references.outcome_dt,
601 new_references.grading_schema_cd,
602 new_references.version_number,
603 new_references.grade,
604 new_references.s_grade_creation_method_type,
605 new_references.finalised_outcome_ind,
606 new_references.mark,
607 new_references.number_times_keyed,
608 new_references.translated_grading_schema_cd,
609 new_references.translated_version_number,
610 new_references.translated_grade,
611 new_references.translated_dt,
612 new_references.last_updated_by,
613 new_references.last_update_date,
614 old_references.grading_schema_cd,
615 old_references.version_number,
616 old_references.grade,
617 old_references.s_grade_creation_method_type,
618 old_references.finalised_outcome_ind,
619 old_references.mark,
620 old_references.number_times_keyed,
621 old_references.translated_grading_schema_cd,
622 old_references.translated_version_number,
623 old_references.translated_grade,
624 old_references.translated_dt,
625 old_references.last_updated_by,
626 old_references.last_update_date,
627 old_references.uoo_id
628 );
629 END IF;
630 END afterrowinsertupdate2;
631 --
632 PROCEDURE check_parent_existance AS
633 BEGIN
634 IF (((old_references.cal_type = new_references.cal_type)
635 AND (old_references.ci_sequence_number = new_references.ci_sequence_number)
636 AND (old_references.ci_start_dt = new_references.ci_start_dt)
637 AND (old_references.ci_end_dt = new_references.ci_end_dt)
638 )
639 OR ((new_references.cal_type IS NULL)
640 OR (new_references.ci_sequence_number IS NULL)
641 OR (new_references.ci_start_dt IS NULL)
642 OR (new_references.ci_end_dt IS NULL)
643 )
644 ) THEN
645 NULL;
646 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (new_references.cal_type, new_references.ci_sequence_number) THEN
647 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
648 igs_ge_msg_stack.ADD;
649 app_exception.raise_exception;
650 END IF;
651 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd)
652 OR (old_references.version_number = new_references.version_number)
653 OR (old_references.grade = new_references.grade)
654 )
655 OR ((new_references.grading_schema_cd IS NULL)
656 OR (new_references.version_number IS NULL)
657 OR (new_references.grade IS NULL)
658 )
659 ) THEN
660 NULL;
661 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
662 new_references.grading_schema_cd,
663 new_references.version_number,
664 new_references.grade
665 ) THEN
666 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
667 igs_ge_msg_stack.ADD;
668 app_exception.raise_exception;
669 END IF;
670 IF (((old_references.s_grade_creation_method_type = new_references.s_grade_creation_method_type))
671 OR ((new_references.s_grade_creation_method_type IS NULL))
672 ) THEN
673 NULL;
674 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
675 'GRADE_CREATION_METHOD_TYPE',
676 new_references.s_grade_creation_method_type
677 ) THEN
678 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
679 igs_ge_msg_stack.ADD;
680 app_exception.raise_exception;
681 END IF;
682 IF (((old_references.person_id = new_references.person_id)
683 OR (old_references.course_cd = new_references.course_cd)
684 OR (old_references.uoo_id = new_references.uoo_id)
685 )
686 OR ((new_references.person_id IS NULL)
687 OR (new_references.course_cd IS NULL)
688 OR (new_references.uoo_id IS NULL)
689 )
690 ) THEN
691 NULL;
692 ELSIF NOT igs_en_su_attempt_pkg.get_pk_for_validation (
693 new_references.person_id,
694 new_references.course_cd,
695 new_references.uoo_id
696 ) THEN
697 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
698 igs_ge_msg_stack.ADD;
699 app_exception.raise_exception;
700 END IF;
701 IF (((old_references.translated_grading_schema_cd = new_references.translated_grading_schema_cd)
702 OR (old_references.translated_version_number = new_references.translated_version_number)
703 OR (old_references.translated_grade = new_references.translated_grade)
704 )
705 OR ((new_references.translated_grading_schema_cd IS NULL)
706 OR (new_references.translated_version_number IS NULL)
707 OR (new_references.translated_grade IS NULL)
708 )
709 ) THEN
710 NULL;
711 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
712 new_references.translated_grading_schema_cd,
713 new_references.translated_version_number,
714 new_references.translated_grade
715 ) THEN
716 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
717 igs_ge_msg_stack.ADD;
718 app_exception.raise_exception;
719 END IF;
720 END check_parent_existance;
721 -------------------------------------------------------------------------------------------
722 --Change History:
723 --Who When What
724 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
725 -------------------------------------------------------------------------------------------
726 FUNCTION get_pk_for_validation (
727 x_person_id IN NUMBER,
728 x_course_cd IN VARCHAR2,
729 x_outcome_dt IN DATE,
730 x_grading_period_cd IN VARCHAR2,
731 x_uoo_id IN NUMBER
732 )
733 RETURN BOOLEAN AS
734 CURSOR cur_rowid IS
735 SELECT ROWID
736 FROM igs_as_su_stmptout_all
737 WHERE person_id = x_person_id
738 AND course_cd = x_course_cd
739 AND outcome_dt = x_outcome_dt
740 AND grading_period_cd = x_grading_period_cd
741 AND uoo_id = x_uoo_id
742 FOR UPDATE NOWAIT;
743 lv_rowid cur_rowid%ROWTYPE;
744 BEGIN
745 OPEN cur_rowid;
746 FETCH cur_rowid INTO lv_rowid;
747 IF (cur_rowid%FOUND) THEN
748 CLOSE cur_rowid;
749 RETURN (TRUE);
750 ELSE
751 CLOSE cur_rowid;
752 RETURN (FALSE);
753 END IF;
754 END get_pk_for_validation;
755
756 PROCEDURE get_ufk_igs_ca_inst (x_cal_type IN VARCHAR2, x_sequence_number IN NUMBER, x_start_dt IN DATE, x_end_dt IN DATE) AS
757 CURSOR cur_rowid IS
758 SELECT ROWID
759 FROM igs_as_su_stmptout_all
760 WHERE cal_type = x_cal_type
761 AND ci_sequence_number = x_sequence_number
762 AND ci_start_dt = x_start_dt
763 AND ci_end_dt = x_end_dt;
764 lv_rowid cur_rowid%ROWTYPE;
765 BEGIN
766 OPEN cur_rowid;
767 FETCH cur_rowid INTO lv_rowid;
768 IF (cur_rowid%FOUND) THEN
769 fnd_message.set_name ('IGS', 'IGS_AS_SUAO_CI_UFK');
770 igs_ge_msg_stack.ADD;
771 CLOSE cur_rowid;
772 app_exception.raise_exception;
773 RETURN;
774 END IF;
775 CLOSE cur_rowid;
776 END get_ufk_igs_ca_inst;
777
778 PROCEDURE get_fk_igs_lookups_view (x_s_grade_creation_method_type IN VARCHAR2) IS
779 CURSOR cur_rowid IS
780 SELECT ROWID
781 FROM igs_as_su_stmptout_all
782 WHERE s_grade_creation_method_type = x_s_grade_creation_method_type;
783 lv_rowid cur_rowid%ROWTYPE;
784 BEGIN
785 OPEN cur_rowid;
786 FETCH cur_rowid INTO lv_rowid;
787 IF (cur_rowid%FOUND) THEN
788 fnd_message.set_name ('IGS', 'IGS_AS_SUAO_SLV_FK');
789 igs_ge_msg_stack.ADD;
790 CLOSE cur_rowid;
791 app_exception.raise_exception;
792 RETURN;
793 END IF;
794 CLOSE cur_rowid;
795 END get_fk_igs_lookups_view;
796
797 PROCEDURE get_fk_igs_en_su_attempt (x_person_id IN NUMBER, x_course_cd IN VARCHAR2, x_uoo_id IN NUMBER) AS
798 CURSOR cur_rowid IS
799 SELECT ROWID
800 FROM igs_as_su_stmptout_all
801 WHERE person_id = x_person_id
802 AND course_cd = x_course_cd
803 AND uoo_id = x_uoo_id;
804 lv_rowid cur_rowid%ROWTYPE;
805 BEGIN
806 OPEN cur_rowid;
807 FETCH cur_rowid INTO lv_rowid;
808 IF (cur_rowid%FOUND) THEN
809 -- Changed '<FORM_RECORD_PRESENT>' to 'IGS_AS_SUAO_SUA_FK',anilk Bug#2413841
810 fnd_message.set_name ('IGS', 'IGS_AS_SUAO_SUA_FK');
811 igs_ge_msg_stack.ADD;
812 CLOSE cur_rowid;
813 app_exception.raise_exception;
814 RETURN;
815 END IF;
816 CLOSE cur_rowid;
817 END get_fk_igs_en_su_attempt;
818
819 PROCEDURE get_fk_igs_as_grd_sch_grade (x_grading_schema_cd IN VARCHAR2, x_version_number IN NUMBER, x_grade IN VARCHAR2) AS
820 CURSOR cur_rowid IS
821 SELECT ROWID
822 FROM igs_as_su_stmptout_all
823 WHERE translated_grading_schema_cd = x_grading_schema_cd
824 AND translated_version_number = x_version_number
825 AND translated_grade = x_grade
826 OR (grading_schema_cd = x_grading_schema_cd
827 AND version_number = x_version_number
828 AND grade = x_grade
829 );
830 lv_rowid cur_rowid%ROWTYPE;
831 BEGIN
832 OPEN cur_rowid;
833 FETCH cur_rowid INTO lv_rowid;
834 IF (cur_rowid%FOUND) THEN
835 fnd_message.set_name ('IGS', 'IGS_AS_SUAO_SUA_FK');
836 igs_ge_msg_stack.ADD;
837 CLOSE cur_rowid;
838 app_exception.raise_exception;
839 RETURN;
840 END IF;
841 CLOSE cur_rowid;
842 END get_fk_igs_as_grd_sch_grade;
843
844 PROCEDURE before_dml (
845 p_action IN VARCHAR2,
846 x_rowid IN VARCHAR2,
847 x_org_id IN NUMBER,
848 x_cal_type IN VARCHAR2,
849 x_ci_sequence_number IN NUMBER,
850 x_ci_start_dt IN DATE,
851 x_ci_end_dt IN DATE,
852 x_outcome_dt IN DATE,
853 x_grading_schema_cd IN VARCHAR2,
854 x_version_number IN NUMBER,
855 x_grade IN VARCHAR2,
856 x_s_grade_creation_method_type IN VARCHAR2,
857 x_finalised_outcome_ind IN VARCHAR2,
858 x_mark IN NUMBER,
859 x_translated_grading_schema_cd IN VARCHAR2,
860 x_translated_version_number IN NUMBER,
861 x_translated_grade IN VARCHAR2,
862 x_translated_dt IN DATE,
863 x_number_times_keyed IN NUMBER,
864 x_person_id IN NUMBER,
865 x_course_cd IN VARCHAR2,
866 x_unit_cd IN VARCHAR2,
867 x_creation_date IN DATE,
868 x_created_by IN NUMBER,
869 x_last_update_date IN DATE,
870 x_last_updated_by IN NUMBER,
871 x_last_update_login IN NUMBER,
872 x_grading_period_cd IN VARCHAR2,
873 x_attribute_category IN VARCHAR2,
874 x_attribute1 IN VARCHAR2,
875 x_attribute2 IN VARCHAR2,
876 x_attribute3 IN VARCHAR2,
877 x_attribute4 IN VARCHAR2,
878 x_attribute5 IN VARCHAR2,
879 x_attribute6 IN VARCHAR2,
880 x_attribute7 IN VARCHAR2,
881 x_attribute8 IN VARCHAR2,
882 x_attribute9 IN VARCHAR2,
883 x_attribute10 IN VARCHAR2,
884 x_attribute11 IN VARCHAR2,
885 x_attribute12 IN VARCHAR2,
886 x_attribute13 IN VARCHAR2,
887 x_attribute14 IN VARCHAR2,
888 x_attribute15 IN VARCHAR2,
889 x_attribute16 IN VARCHAR2,
890 x_attribute17 IN VARCHAR2,
891 x_attribute18 IN VARCHAR2,
892 x_attribute19 IN VARCHAR2,
893 x_attribute20 IN VARCHAR2,
894 x_incomp_deadline_date IN DATE,
895 x_incomp_grading_schema_cd IN VARCHAR2,
896 x_incomp_version_number IN NUMBER,
897 x_incomp_default_grade IN VARCHAR2,
898 x_incomp_default_mark IN NUMBER,
899 x_comments IN VARCHAR2,
900 x_uoo_id IN NUMBER,
901 x_mark_capped_flag IN VARCHAR2,
902 x_release_date IN DATE,
903 x_manual_override_flag IN VARCHAR2,
904 x_show_on_academic_histry_flag IN VARCHAR2
905 ) AS
906 CURSOR c_sua_ass_ind IS
907 SELECT sua.no_assessment_ind
908 FROM igs_en_su_attempt sua
909 WHERE sua.person_id = x_person_id
910 AND sua.course_cd = x_course_cd
911 AND sua.uoo_id = x_uoo_id;
912 l_sua_ass_ind igs_en_su_attempt.no_assessment_ind%TYPE;
913 CURSOR c_gsg_result_type IS
914 SELECT gsg.s_result_type
915 FROM igs_as_grd_sch_grade gsg
916 WHERE gsg.grading_schema_cd = x_grading_schema_cd
917 AND gsg.version_number = x_version_number
918 AND gsg.grade = x_grade;
919 l_gsg_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
920 BEGIN
921 set_column_values (
922 p_action,
923 x_rowid,
924 x_org_id,
925 x_cal_type,
926 x_ci_sequence_number,
927 x_ci_start_dt,
928 x_ci_end_dt,
929 x_outcome_dt,
930 x_grading_schema_cd,
931 x_version_number,
932 x_grade,
933 x_s_grade_creation_method_type,
934 x_finalised_outcome_ind,
935 x_mark,
936 x_translated_grading_schema_cd,
937 x_translated_version_number,
938 x_translated_grade,
939 x_translated_dt,
940 x_number_times_keyed,
941 x_person_id,
942 x_course_cd,
943 x_unit_cd,
944 x_creation_date,
945 x_created_by,
946 x_last_update_date,
947 x_last_updated_by,
948 x_last_update_login,
949 x_grading_period_cd,
950 x_attribute_category,
951 x_attribute1,
952 x_attribute2,
953 x_attribute3,
954 x_attribute4,
955 x_attribute5,
956 x_attribute6,
957 x_attribute7,
958 x_attribute8,
959 x_attribute9,
960 x_attribute10,
961 x_attribute11,
962 x_attribute12,
963 x_attribute13,
964 x_attribute14,
965 x_attribute15,
966 x_attribute16,
967 x_attribute17,
968 x_attribute18,
969 x_attribute19,
970 x_attribute20,
971 x_incomp_deadline_date,
972 x_incomp_grading_schema_cd,
973 x_incomp_version_number,
974 x_incomp_default_grade,
975 x_incomp_default_mark,
976 x_comments,
977 x_uoo_id,
978 x_mark_capped_flag,
979 x_release_date,
980 x_manual_override_flag,
981 x_show_on_academic_histry_flag
982 );
983 IF (p_action = 'INSERT') THEN
984 -- Call all the procedures related to Before Insert.
985 beforerowinsertupdate1 (p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE);
986 IF get_pk_for_validation (
987 new_references.person_id,
988 new_references.course_cd,
989 new_references.outcome_dt,
990 NVL (new_references.grading_period_cd, 'FINAL'),
991 new_references.uoo_id
992 ) THEN
993 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
994 igs_ge_msg_stack.ADD;
995 app_exception.raise_exception;
996 END IF;
997 check_constraints;
998 check_parent_existance;
999 ELSIF (p_action = 'UPDATE') THEN
1000 -- Call all the procedures related to Before Update.
1001 beforerowinsertupdate1 (p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
1002 check_constraints;
1003 check_parent_existance;
1004 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1005 IF get_pk_for_validation (
1006 new_references.person_id,
1007 new_references.course_cd,
1008 new_references.outcome_dt,
1009 NVL (new_references.grading_period_cd, 'FINAL'),
1010 new_references.uoo_id
1011 ) THEN
1012 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1013 igs_ge_msg_stack.ADD;
1014 app_exception.raise_exception;
1015 END IF;
1016 check_constraints;
1017 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1018 check_constraints;
1019 END IF;
1020 --The below code added as part of the bug#2597204
1021 --Opens the cursor to get the no_assessment_ind of a unit Attempt
1022 --The value of no_assesment_ind says whether the unit attempt is Audit or not
1023 OPEN c_sua_ass_ind;
1024 FETCH c_sua_ass_ind INTO l_sua_ass_ind;
1025 CLOSE c_sua_ass_ind;
1026 --Opens the cursor to get the system result type of the Grading Scema Grade
1027 --If the system result type is of AUDIT then the grading schema grade is of audit type
1028 OPEN c_gsg_result_type;
1029 FETCH c_gsg_result_type INTO l_gsg_result_type;
1030 CLOSE c_gsg_result_type;
1031 --If user tries to assign an audit grade to a student who is not taking the unit as an
1032 --audit, an error is thrown
1033 IF l_sua_ass_ind = 'N'
1034 AND l_gsg_result_type = 'AUDIT' THEN
1035 fnd_message.set_name ('IGS', 'IGS_AS_SS_UNT_NT_ADT_GRD');
1036 igs_ge_msg_stack.ADD;
1037 app_exception.raise_exception;
1038 --If user tries to assign a non-audit grade to a student who is taking the unit attempt
1039 --as an audit, an error is thrown
1040 ELSIF l_sua_ass_ind = 'Y'
1041 AND l_gsg_result_type <> 'AUDIT' THEN
1042 fnd_message.set_name ('IGS', 'IGS_AS_SS_UNT_ADT_GRD');
1043 igs_ge_msg_stack.ADD;
1044 app_exception.raise_exception;
1045 END IF;
1046 END before_dml;
1047
1048 PROCEDURE after_dml (p_action IN VARCHAR2, x_rowid IN VARCHAR2) AS
1049 BEGIN
1050 l_rowid := x_rowid;
1051 IF (p_action = 'INSERT') THEN
1052 -- Call all the procedures related to After Insert.
1053 afterrowinsertupdate2 (p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE);
1054 ELSIF (p_action = 'UPDATE') THEN
1055 -- Call all the procedures related to After Update.
1056 afterrowinsertupdate2 (p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
1057 ELSIF (p_action = 'DELETE') THEN
1058 -- Call all the procedures related to After Update.
1059 afterrowinsertupdate2 (p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
1060 END IF;
1061 END after_dml;
1062 --
1063 PROCEDURE insert_row (
1064 x_rowid IN OUT NOCOPY VARCHAR2,
1065 x_org_id IN NUMBER,
1066 x_person_id IN NUMBER,
1067 x_course_cd IN VARCHAR2,
1068 x_unit_cd IN VARCHAR2,
1069 x_cal_type IN VARCHAR2,
1070 x_ci_sequence_number IN NUMBER,
1071 x_outcome_dt IN DATE,
1072 x_ci_start_dt IN DATE,
1073 x_ci_end_dt IN DATE,
1074 x_grading_schema_cd IN VARCHAR2,
1075 x_version_number IN NUMBER,
1076 x_grade IN VARCHAR2,
1077 x_s_grade_creation_method_type IN VARCHAR2,
1078 x_finalised_outcome_ind IN VARCHAR2,
1079 x_mark IN NUMBER,
1080 x_number_times_keyed IN NUMBER,
1081 x_translated_grading_schema_cd IN VARCHAR2,
1082 x_translated_version_number IN NUMBER,
1083 x_translated_grade IN VARCHAR2,
1084 x_translated_dt IN DATE,
1085 x_mode IN VARCHAR2,
1086 x_grading_period_cd IN VARCHAR2,
1087 x_attribute_category IN VARCHAR2,
1088 x_attribute1 IN VARCHAR2,
1089 x_attribute2 IN VARCHAR2,
1090 x_attribute3 IN VARCHAR2,
1091 x_attribute4 IN VARCHAR2,
1092 x_attribute5 IN VARCHAR2,
1093 x_attribute6 IN VARCHAR2,
1094 x_attribute7 IN VARCHAR2,
1095 x_attribute8 IN VARCHAR2,
1096 x_attribute9 IN VARCHAR2,
1097 x_attribute10 IN VARCHAR2,
1098 x_attribute11 IN VARCHAR2,
1099 x_attribute12 IN VARCHAR2,
1100 x_attribute13 IN VARCHAR2,
1101 x_attribute14 IN VARCHAR2,
1102 x_attribute15 IN VARCHAR2,
1103 x_attribute16 IN VARCHAR2,
1104 x_attribute17 IN VARCHAR2,
1105 x_attribute18 IN VARCHAR2,
1106 x_attribute19 IN VARCHAR2,
1107 x_attribute20 IN VARCHAR2,
1108 x_incomp_deadline_date IN DATE,
1109 x_incomp_grading_schema_cd IN VARCHAR2,
1110 x_incomp_version_number IN NUMBER,
1111 x_incomp_default_grade IN VARCHAR2,
1112 x_incomp_default_mark IN NUMBER,
1113 x_comments IN VARCHAR2,
1114 x_uoo_id IN NUMBER,
1115 x_mark_capped_flag IN VARCHAR2,
1116 x_release_date IN DATE,
1117 x_manual_override_flag IN VARCHAR2,
1118 x_show_on_academic_histry_flag IN VARCHAR2
1119 ) AS
1120 CURSOR c IS
1121 SELECT ROWID
1122 FROM igs_as_su_stmptout_all
1123 WHERE person_id = x_person_id
1124 AND course_cd = x_course_cd
1125 AND outcome_dt = x_outcome_dt
1126 AND grading_period_cd = x_grading_period_cd
1127 AND uoo_id = x_uoo_id;
1128 x_last_update_date DATE;
1129 x_last_updated_by NUMBER;
1130 x_last_update_login NUMBER;
1131 x_request_id NUMBER;
1132 x_program_id NUMBER;
1133 x_program_application_id NUMBER;
1134 x_program_update_date DATE;
1135 BEGIN
1136 x_last_update_date := SYSDATE;
1137 IF (x_mode = 'I') THEN
1138 x_last_updated_by := 1;
1139 x_last_update_login := 0;
1140 ELSIF (X_MODE IN ('R', 'S')) THEN
1141 x_last_updated_by := fnd_global.user_id;
1142 IF x_last_updated_by IS NULL THEN
1143 x_last_updated_by := -1;
1144 END IF;
1145 x_last_update_login := fnd_global.login_id;
1146 IF x_last_update_login IS NULL THEN
1147 x_last_update_login := -1;
1148 END IF;
1149 x_request_id := fnd_global.conc_request_id;
1150 x_program_id := fnd_global.conc_program_id;
1151 x_program_application_id := fnd_global.prog_appl_id;
1152 IF (x_request_id = -1) THEN
1153 x_request_id := NULL;
1154 x_program_id := NULL;
1155 x_program_application_id := NULL;
1156 x_program_update_date := NULL;
1157 ELSE
1158 x_program_update_date := SYSDATE;
1159 END IF;
1160 ELSE
1161 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1162 igs_ge_msg_stack.ADD;
1163 app_exception.raise_exception;
1164 END IF;
1165 before_dml (
1166 p_action => 'INSERT',
1167 x_rowid => x_rowid,
1168 x_org_id => igs_ge_gen_003.get_org_id,
1169 x_cal_type => x_cal_type,
1170 x_ci_end_dt => x_ci_end_dt,
1171 x_ci_sequence_number => x_ci_sequence_number,
1172 x_ci_start_dt => x_ci_start_dt,
1173 x_course_cd => x_course_cd,
1174 x_finalised_outcome_ind => NVL (x_finalised_outcome_ind, 'N'),
1175 x_grade => x_grade,
1176 x_grading_schema_cd => x_grading_schema_cd,
1177 x_mark => x_mark,
1178 x_number_times_keyed => x_number_times_keyed,
1179 x_outcome_dt => x_outcome_dt,
1180 x_person_id => x_person_id,
1181 x_s_grade_creation_method_type => x_s_grade_creation_method_type,
1182 x_translated_dt => x_translated_dt,
1183 x_translated_grade => x_translated_grade,
1184 x_translated_grading_schema_cd => x_translated_grading_schema_cd,
1185 x_translated_version_number => x_translated_version_number,
1186 x_unit_cd => x_unit_cd,
1187 x_version_number => x_version_number,
1188 x_creation_date => x_last_update_date,
1189 x_created_by => x_last_updated_by,
1190 x_last_update_date => x_last_update_date,
1191 x_last_updated_by => x_last_updated_by,
1192 x_last_update_login => x_last_update_login,
1193 x_grading_period_cd => x_grading_period_cd,
1194 x_attribute_category => x_attribute_category,
1195 x_attribute1 => x_attribute1,
1196 x_attribute2 => x_attribute2,
1197 x_attribute3 => x_attribute3,
1198 x_attribute4 => x_attribute4,
1199 x_attribute5 => x_attribute5,
1200 x_attribute6 => x_attribute6,
1201 x_attribute7 => x_attribute7,
1202 x_attribute8 => x_attribute8,
1203 x_attribute9 => x_attribute9,
1204 x_attribute10 => x_attribute10,
1205 x_attribute11 => x_attribute11,
1206 x_attribute12 => x_attribute12,
1207 x_attribute13 => x_attribute13,
1208 x_attribute14 => x_attribute14,
1209 x_attribute15 => x_attribute15,
1210 x_attribute16 => x_attribute16,
1211 x_attribute17 => x_attribute17,
1212 x_attribute18 => x_attribute18,
1213 x_attribute19 => x_attribute19,
1214 x_attribute20 => x_attribute20,
1215 x_incomp_deadline_date => x_incomp_deadline_date,
1216 x_incomp_grading_schema_cd => x_incomp_grading_schema_cd,
1217 x_incomp_version_number => x_incomp_version_number,
1218 x_incomp_default_grade => x_incomp_default_grade,
1219 x_incomp_default_mark => x_incomp_default_mark,
1220 x_comments => x_comments,
1221 x_uoo_id => x_uoo_id,
1222 x_mark_capped_flag => x_mark_capped_flag,
1223 x_release_date => x_release_date,
1224 x_manual_override_flag => x_manual_override_flag,
1225 x_show_on_academic_histry_flag => x_show_on_academic_histry_flag
1226 );
1227 IF (x_mode = 'S') THEN
1228 igs_sc_gen_001.set_ctx('R');
1229 END IF;
1230 INSERT INTO igs_as_su_stmptout_all
1231 (org_id, person_id, course_cd, unit_cd,
1232 cal_type, ci_sequence_number, ci_start_dt,
1233 ci_end_dt, outcome_dt, grading_schema_cd,
1234 version_number, grade, s_grade_creation_method_type,
1235 finalised_outcome_ind, mark, number_times_keyed,
1236 translated_grading_schema_cd, translated_version_number,
1237 translated_grade, translated_dt, creation_date, created_by,
1238 last_update_date, last_updated_by, last_update_login, request_id, program_id,
1239 program_application_id, program_update_date, grading_period_cd,
1240 attribute_category, attribute1, attribute2,
1241 attribute3, attribute4, attribute5,
1242 attribute6, attribute7, attribute8,
1243 attribute9, attribute10, attribute11,
1244 attribute12, attribute13, attribute14,
1245 attribute15, attribute16, attribute17,
1246 attribute18, attribute19, attribute20,
1247 incomp_deadline_date, incomp_grading_schema_cd,
1248 incomp_version_number, incomp_default_grade,
1249 incomp_default_mark, comments, uoo_id,
1250 mark_capped_flag, release_date, manual_override_flag,
1251 show_on_academic_histry_flag)
1252 VALUES (new_references.org_id, new_references.person_id, new_references.course_cd, new_references.unit_cd,
1253 new_references.cal_type, new_references.ci_sequence_number, new_references.ci_start_dt,
1254 new_references.ci_end_dt, new_references.outcome_dt, new_references.grading_schema_cd,
1255 new_references.version_number, new_references.grade, new_references.s_grade_creation_method_type,
1256 new_references.finalised_outcome_ind, new_references.mark, new_references.number_times_keyed,
1257 new_references.translated_grading_schema_cd, new_references.translated_version_number,
1258 new_references.translated_grade, new_references.translated_dt, x_last_update_date, x_last_updated_by,
1259 x_last_update_date, x_last_updated_by, x_last_update_login, x_request_id, x_program_id,
1260 x_program_application_id, x_program_update_date, new_references.grading_period_cd,
1261 new_references.attribute_category, new_references.attribute1, new_references.attribute2,
1262 new_references.attribute3, new_references.attribute4, new_references.attribute5,
1263 new_references.attribute6, new_references.attribute7, new_references.attribute8,
1264 new_references.attribute9, new_references.attribute10, new_references.attribute11,
1265 new_references.attribute12, new_references.attribute13, new_references.attribute14,
1266 new_references.attribute15, new_references.attribute16, new_references.attribute17,
1267 new_references.attribute18, new_references.attribute19, new_references.attribute20,
1268 new_references.incomp_deadline_date, new_references.incomp_grading_schema_cd,
1269 new_references.incomp_version_number, new_references.incomp_default_grade,
1270 new_references.incomp_default_mark, new_references.comments, new_references.uoo_id,
1271 new_references.mark_capped_flag, new_references.release_date, new_references.manual_override_flag,
1272 new_references.show_on_academic_histry_flag);
1273 IF (x_mode = 'S') THEN
1274 igs_sc_gen_001.unset_ctx('R');
1275 END IF;
1276
1277 OPEN c;
1278 FETCH c INTO x_rowid;
1279 IF (c%NOTFOUND) THEN
1280 CLOSE c;
1281 RAISE NO_DATA_FOUND;
1282 END IF;
1283 CLOSE c;
1284 after_dml (p_action => 'INSERT', x_rowid => x_rowid);
1285
1286 EXCEPTION
1287 WHEN OTHERS THEN
1288 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1289 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1290 fnd_message.set_token ('ERR_CD', SQLCODE);
1291 igs_ge_msg_stack.add;
1292 igs_sc_gen_001.unset_ctx('R');
1293 app_exception.raise_exception;
1294 ELSE
1295 igs_sc_gen_001.unset_ctx('R');
1296 RAISE;
1297 END IF;
1298 END insert_row;
1299
1300 PROCEDURE lock_row (
1301 x_rowid IN VARCHAR2,
1302 x_person_id IN NUMBER,
1303 x_course_cd IN VARCHAR2,
1304 x_unit_cd IN VARCHAR2,
1305 x_cal_type IN VARCHAR2,
1306 x_ci_sequence_number IN NUMBER,
1307 x_outcome_dt IN DATE,
1308 x_ci_start_dt IN DATE,
1309 x_ci_end_dt IN DATE,
1310 x_grading_schema_cd IN VARCHAR2,
1311 x_version_number IN NUMBER,
1312 x_grade IN VARCHAR2,
1313 x_s_grade_creation_method_type IN VARCHAR2,
1314 x_finalised_outcome_ind IN VARCHAR2,
1315 x_mark IN NUMBER,
1316 x_number_times_keyed IN NUMBER,
1317 x_translated_grading_schema_cd IN VARCHAR2,
1318 x_translated_version_number IN NUMBER,
1319 x_translated_grade IN VARCHAR2,
1320 x_translated_dt IN DATE,
1321 x_grading_period_cd IN VARCHAR2,
1322 x_attribute_category IN VARCHAR2,
1323 x_attribute1 IN VARCHAR2,
1324 x_attribute2 IN VARCHAR2,
1325 x_attribute3 IN VARCHAR2,
1326 x_attribute4 IN VARCHAR2,
1327 x_attribute5 IN VARCHAR2,
1328 x_attribute6 IN VARCHAR2,
1329 x_attribute7 IN VARCHAR2,
1330 x_attribute8 IN VARCHAR2,
1331 x_attribute9 IN VARCHAR2,
1332 x_attribute10 IN VARCHAR2,
1333 x_attribute11 IN VARCHAR2,
1334 x_attribute12 IN VARCHAR2,
1335 x_attribute13 IN VARCHAR2,
1336 x_attribute14 IN VARCHAR2,
1337 x_attribute15 IN VARCHAR2,
1338 x_attribute16 IN VARCHAR2,
1339 x_attribute17 IN VARCHAR2,
1340 x_attribute18 IN VARCHAR2,
1341 x_attribute19 IN VARCHAR2,
1342 x_attribute20 IN VARCHAR2,
1343 x_incomp_deadline_date IN DATE,
1344 x_incomp_grading_schema_cd IN VARCHAR2,
1345 x_incomp_version_number IN NUMBER,
1346 x_incomp_default_grade IN VARCHAR2,
1347 x_incomp_default_mark IN NUMBER,
1348 x_comments IN VARCHAR2,
1349 x_uoo_id IN NUMBER,
1350 x_mark_capped_flag IN VARCHAR2,
1351 x_release_date IN DATE,
1352 x_manual_override_flag IN VARCHAR2,
1353 x_show_on_academic_histry_flag IN VARCHAR2
1354 ) AS
1355 CURSOR c1 IS
1356 SELECT ci_start_dt,
1357 ci_end_dt,
1358 grading_schema_cd,
1359 version_number,
1360 grade,
1361 s_grade_creation_method_type,
1362 finalised_outcome_ind,
1363 mark,
1364 number_times_keyed,
1365 translated_grading_schema_cd,
1366 translated_version_number,
1367 translated_grade,
1368 translated_dt,
1369 grading_period_cd,
1370 attribute_category,
1371 attribute1,
1372 attribute2,
1373 attribute3,
1374 attribute4,
1375 attribute5,
1376 attribute6,
1377 attribute7,
1378 attribute8,
1379 attribute9,
1380 attribute10,
1381 attribute11,
1382 attribute12,
1383 attribute13,
1384 attribute14,
1385 attribute15,
1386 attribute16,
1387 attribute17,
1388 attribute18,
1389 attribute19,
1390 attribute20,
1391 incomp_deadline_date,
1392 incomp_grading_schema_cd,
1393 incomp_version_number,
1394 incomp_default_grade,
1395 incomp_default_mark,
1396 comments,
1397 uoo_id,
1398 mark_capped_flag,
1399 release_date,
1400 manual_override_flag,
1401 show_on_academic_histry_flag
1402 FROM igs_as_su_stmptout_all
1403 WHERE ROWID = x_rowid
1404 FOR UPDATE NOWAIT;
1405 tlinfo c1%ROWTYPE;
1406 BEGIN
1407 OPEN c1;
1408 FETCH c1 INTO tlinfo;
1409 IF (c1%NOTFOUND) THEN
1410 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1411 igs_ge_msg_stack.ADD;
1412 app_exception.raise_exception;
1413 CLOSE c1;
1414 RETURN;
1415 END IF;
1416 CLOSE c1;
1417 IF ((TRUNC (tlinfo.ci_start_dt) = TRUNC (x_ci_start_dt))
1418 AND (TRUNC (tlinfo.ci_end_dt) = TRUNC (x_ci_end_dt))
1419 AND (tlinfo.grading_schema_cd = x_grading_schema_cd)
1420 AND (tlinfo.version_number = x_version_number)
1421 AND (tlinfo.grade = x_grade)
1422 AND (tlinfo.s_grade_creation_method_type = x_s_grade_creation_method_type)
1423 AND (tlinfo.finalised_outcome_ind = x_finalised_outcome_ind)
1424 AND ((tlinfo.mark = x_mark)
1425 OR ((tlinfo.mark IS NULL)
1426 AND (x_mark IS NULL)
1427 )
1428 )
1429 AND ((tlinfo.number_times_keyed = x_number_times_keyed)
1430 OR ((tlinfo.number_times_keyed IS NULL)
1431 AND (x_number_times_keyed IS NULL)
1432 )
1433 )
1434 AND ((tlinfo.translated_grading_schema_cd = x_translated_grading_schema_cd)
1435 OR ((tlinfo.translated_grading_schema_cd IS NULL)
1436 AND (x_translated_grading_schema_cd IS NULL)
1437 )
1438 )
1439 AND ((tlinfo.translated_version_number = x_translated_version_number)
1440 OR ((tlinfo.translated_version_number IS NULL)
1441 AND (x_translated_version_number IS NULL)
1442 )
1443 )
1444 AND ((tlinfo.translated_grade = x_translated_grade)
1445 OR ((tlinfo.translated_grade IS NULL)
1446 AND (x_translated_grade IS NULL)
1447 )
1448 )
1449 AND ((TRUNC (tlinfo.translated_dt) = TRUNC (x_translated_dt)) -- The translated_dt is made truncated to get the correctly. This is done as a part of bug #2310804
1450 OR ((tlinfo.translated_dt IS NULL)
1451 AND (x_translated_dt IS NULL)
1452 )
1453 )
1454 AND (tlinfo.grading_period_cd = x_grading_period_cd)
1455 AND ((tlinfo.attribute_category = x_attribute_category)
1456 OR ((tlinfo.attribute_category IS NULL)
1457 AND (x_attribute_category IS NULL)
1458 )
1459 )
1460 AND ((tlinfo.attribute1 = x_attribute1)
1461 OR ((tlinfo.attribute1 IS NULL)
1462 AND (x_attribute1 IS NULL)
1463 )
1464 )
1465 AND ((tlinfo.attribute2 = x_attribute2)
1466 OR ((tlinfo.attribute2 IS NULL)
1467 AND (x_attribute2 IS NULL)
1468 )
1469 )
1470 AND ((tlinfo.attribute3 = x_attribute3)
1471 OR ((tlinfo.attribute3 IS NULL)
1472 AND (x_attribute3 IS NULL)
1473 )
1474 )
1475 AND ((tlinfo.attribute4 = x_attribute4)
1476 OR ((tlinfo.attribute4 IS NULL)
1477 AND (x_attribute4 IS NULL)
1478 )
1479 )
1480 AND ((tlinfo.attribute5 = x_attribute5)
1481 OR ((tlinfo.attribute5 IS NULL)
1482 AND (x_attribute5 IS NULL)
1483 )
1484 )
1485 AND ((tlinfo.attribute6 = x_attribute6)
1486 OR ((tlinfo.attribute6 IS NULL)
1487 AND (x_attribute6 IS NULL)
1488 )
1489 )
1490 AND ((tlinfo.attribute7 = x_attribute7)
1491 OR ((tlinfo.attribute7 IS NULL)
1492 AND (x_attribute7 IS NULL)
1493 )
1494 )
1495 AND ((tlinfo.attribute8 = x_attribute8)
1496 OR ((tlinfo.attribute8 IS NULL)
1497 AND (x_attribute8 IS NULL)
1498 )
1499 )
1500 AND ((tlinfo.attribute9 = x_attribute9)
1501 OR ((tlinfo.attribute9 IS NULL)
1502 AND (x_attribute9 IS NULL)
1503 )
1504 )
1505 AND ((tlinfo.attribute10 = x_attribute10)
1506 OR ((tlinfo.attribute10 IS NULL)
1507 AND (x_attribute10 IS NULL)
1508 )
1509 )
1510 AND ((tlinfo.attribute11 = x_attribute11)
1511 OR ((tlinfo.attribute11 IS NULL)
1512 AND (x_attribute11 IS NULL)
1513 )
1514 )
1515 AND ((tlinfo.attribute12 = x_attribute12)
1516 OR ((tlinfo.attribute12 IS NULL)
1517 AND (x_attribute12 IS NULL)
1518 )
1519 )
1520 AND ((tlinfo.attribute13 = x_attribute13)
1521 OR ((tlinfo.attribute13 IS NULL)
1522 AND (x_attribute13 IS NULL)
1523 )
1524 )
1525 AND ((tlinfo.attribute14 = x_attribute14)
1526 OR ((tlinfo.attribute14 IS NULL)
1527 AND (x_attribute14 IS NULL)
1528 )
1529 )
1530 AND ((tlinfo.attribute15 = x_attribute15)
1531 OR ((tlinfo.attribute15 IS NULL)
1532 AND (x_attribute15 IS NULL)
1533 )
1534 )
1535 AND ((tlinfo.attribute16 = x_attribute16)
1536 OR ((tlinfo.attribute16 IS NULL)
1537 AND (x_attribute16 IS NULL)
1538 )
1539 )
1540 AND ((tlinfo.attribute17 = x_attribute17)
1541 OR ((tlinfo.attribute17 IS NULL)
1542 AND (x_attribute17 IS NULL)
1543 )
1544 )
1545 AND ((tlinfo.attribute18 = x_attribute18)
1546 OR ((tlinfo.attribute18 IS NULL)
1547 AND (x_attribute18 IS NULL)
1548 )
1549 )
1550 AND ((tlinfo.attribute19 = x_attribute19)
1551 OR ((tlinfo.attribute19 IS NULL)
1552 AND (x_attribute19 IS NULL)
1553 )
1554 )
1555 AND ((tlinfo.attribute20 = x_attribute20)
1556 OR ((tlinfo.attribute20 IS NULL)
1557 AND (x_attribute20 IS NULL)
1558 )
1559 )
1560 AND ((TRUNC (tlinfo.incomp_deadline_date) = TRUNC (x_incomp_deadline_date))
1561 OR ((tlinfo.incomp_deadline_date IS NULL)
1562 AND (x_incomp_deadline_date IS NULL)
1563 )
1564 )
1565 AND ((tlinfo.incomp_grading_schema_cd = x_incomp_grading_schema_cd)
1566 OR ((tlinfo.incomp_grading_schema_cd IS NULL)
1567 AND (x_incomp_grading_schema_cd IS NULL)
1568 )
1569 )
1570 AND ((tlinfo.incomp_version_number = x_incomp_version_number)
1571 OR ((tlinfo.incomp_version_number IS NULL)
1572 AND (x_incomp_version_number IS NULL)
1573 )
1574 )
1575 AND ((tlinfo.incomp_default_grade = x_incomp_default_grade)
1576 OR ((tlinfo.incomp_default_grade IS NULL)
1577 AND (x_incomp_default_grade IS NULL)
1578 )
1579 )
1580 AND ((tlinfo.incomp_default_mark = x_incomp_default_mark)
1581 OR ((tlinfo.incomp_default_mark IS NULL)
1582 AND (x_incomp_default_mark IS NULL)
1583 )
1584 )
1585 AND ((tlinfo.comments = x_comments)
1586 OR ((tlinfo.comments IS NULL)
1587 AND (x_comments IS NULL)
1588 )
1589 )
1590 AND ((tlinfo.mark_capped_flag = x_mark_capped_flag)
1591 OR ((tlinfo.mark_capped_flag IS NULL)
1592 AND (x_mark_capped_flag IS NULL)
1593 )
1594 )
1595 AND ((tlinfo.release_date = x_release_date)
1596 OR ((tlinfo.release_date IS NULL)
1597 AND (x_release_date IS NULL)
1598 )
1599 )
1600 AND ((tlinfo.manual_override_flag = x_manual_override_flag)
1601 OR ((tlinfo.manual_override_flag IS NULL)
1602 AND (x_manual_override_flag IS NULL)
1603 )
1604 )
1605 AND ((tlinfo.show_on_academic_histry_flag = x_show_on_academic_histry_flag)
1606 OR ((tlinfo.show_on_academic_histry_flag IS NULL)
1607 AND (x_show_on_academic_histry_flag IS NULL)
1608 )
1609 )
1610 ) THEN
1611 NULL;
1612 ELSE
1613 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
1614 igs_ge_msg_stack.ADD;
1615 app_exception.raise_exception;
1616 END IF;
1617 RETURN;
1618 END lock_row;
1619
1620 PROCEDURE update_row (
1621 x_rowid IN VARCHAR2,
1622 x_person_id IN NUMBER,
1623 x_course_cd IN VARCHAR2,
1624 x_unit_cd IN VARCHAR2,
1625 x_cal_type IN VARCHAR2,
1626 x_ci_sequence_number IN NUMBER,
1627 x_outcome_dt IN DATE,
1628 x_ci_start_dt IN DATE,
1629 x_ci_end_dt IN DATE,
1630 x_grading_schema_cd IN VARCHAR2,
1631 x_version_number IN NUMBER,
1632 x_grade IN VARCHAR2,
1633 x_s_grade_creation_method_type IN VARCHAR2,
1634 x_finalised_outcome_ind IN VARCHAR2,
1635 x_mark IN NUMBER,
1636 x_number_times_keyed IN NUMBER,
1637 x_translated_grading_schema_cd IN VARCHAR2,
1638 x_translated_version_number IN NUMBER,
1639 x_translated_grade IN VARCHAR2,
1640 x_translated_dt IN DATE,
1641 x_mode IN VARCHAR2,
1642 x_grading_period_cd IN VARCHAR2,
1643 x_attribute_category IN VARCHAR2,
1644 x_attribute1 IN VARCHAR2,
1645 x_attribute2 IN VARCHAR2,
1646 x_attribute3 IN VARCHAR2,
1647 x_attribute4 IN VARCHAR2,
1648 x_attribute5 IN VARCHAR2,
1649 x_attribute6 IN VARCHAR2,
1650 x_attribute7 IN VARCHAR2,
1651 x_attribute8 IN VARCHAR2,
1652 x_attribute9 IN VARCHAR2,
1653 x_attribute10 IN VARCHAR2,
1654 x_attribute11 IN VARCHAR2,
1655 x_attribute12 IN VARCHAR2,
1656 x_attribute13 IN VARCHAR2,
1657 x_attribute14 IN VARCHAR2,
1658 x_attribute15 IN VARCHAR2,
1659 x_attribute16 IN VARCHAR2,
1660 x_attribute17 IN VARCHAR2,
1661 x_attribute18 IN VARCHAR2,
1662 x_attribute19 IN VARCHAR2,
1663 x_attribute20 IN VARCHAR2,
1664 x_incomp_deadline_date IN DATE,
1665 x_incomp_grading_schema_cd IN VARCHAR2,
1666 x_incomp_version_number IN NUMBER,
1667 x_incomp_default_grade IN VARCHAR2,
1668 x_incomp_default_mark IN NUMBER,
1669 x_comments IN VARCHAR2,
1670 x_uoo_id IN NUMBER,
1671 x_mark_capped_flag IN VARCHAR2,
1672 x_release_date IN DATE,
1673 x_manual_override_flag IN VARCHAR2,
1674 x_show_on_academic_histry_flag IN VARCHAR2
1675 ) AS
1676 x_last_update_date DATE;
1677 x_last_updated_by NUMBER;
1678 x_last_update_login NUMBER;
1679 x_request_id NUMBER;
1680 x_program_id NUMBER;
1681 x_program_application_id NUMBER;
1682 x_program_update_date DATE;
1683 BEGIN
1684 x_last_update_date := SYSDATE;
1685 IF (x_mode = 'I') THEN
1686 x_last_updated_by := 1;
1687 x_last_update_login := 0;
1688 ELSIF (X_MODE IN ('R', 'S')) THEN
1689 x_last_updated_by := fnd_global.user_id;
1690 IF x_last_updated_by IS NULL THEN
1691 x_last_updated_by := -1;
1692 END IF;
1693 x_last_update_login := fnd_global.login_id;
1694 IF x_last_update_login IS NULL THEN
1695 x_last_update_login := -1;
1696 END IF;
1697 ELSE
1698 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1699 igs_ge_msg_stack.ADD;
1700 app_exception.raise_exception;
1701 END IF;
1702 before_dml (
1703 p_action => 'UPDATE',
1704 x_rowid => x_rowid,
1705 x_cal_type => x_cal_type,
1706 x_ci_end_dt => x_ci_end_dt,
1707 x_ci_sequence_number => x_ci_sequence_number,
1708 x_ci_start_dt => x_ci_start_dt,
1709 x_course_cd => x_course_cd,
1710 x_finalised_outcome_ind => x_finalised_outcome_ind,
1711 x_grade => x_grade,
1712 x_grading_schema_cd => x_grading_schema_cd,
1713 x_mark => x_mark,
1714 x_number_times_keyed => x_number_times_keyed,
1715 x_outcome_dt => x_outcome_dt,
1716 x_person_id => x_person_id,
1717 x_s_grade_creation_method_type => x_s_grade_creation_method_type,
1718 x_translated_dt => x_translated_dt,
1719 x_translated_grade => x_translated_grade,
1720 x_translated_grading_schema_cd => x_translated_grading_schema_cd,
1721 x_translated_version_number => x_translated_version_number,
1722 x_unit_cd => x_unit_cd,
1723 x_version_number => x_version_number,
1724 x_creation_date => x_last_update_date,
1725 x_created_by => x_last_updated_by,
1726 x_last_update_date => x_last_update_date,
1727 x_last_updated_by => x_last_updated_by,
1728 x_last_update_login => x_last_update_login,
1729 x_grading_period_cd => x_grading_period_cd,
1730 x_attribute_category => x_attribute_category,
1731 x_attribute1 => x_attribute1,
1732 x_attribute2 => x_attribute2,
1733 x_attribute3 => x_attribute3,
1734 x_attribute4 => x_attribute4,
1735 x_attribute5 => x_attribute5,
1736 x_attribute6 => x_attribute6,
1737 x_attribute7 => x_attribute7,
1738 x_attribute8 => x_attribute8,
1739 x_attribute9 => x_attribute9,
1740 x_attribute10 => x_attribute10,
1741 x_attribute11 => x_attribute11,
1742 x_attribute12 => x_attribute12,
1743 x_attribute13 => x_attribute13,
1744 x_attribute14 => x_attribute14,
1745 x_attribute15 => x_attribute15,
1746 x_attribute16 => x_attribute16,
1747 x_attribute17 => x_attribute17,
1748 x_attribute18 => x_attribute18,
1749 x_attribute19 => x_attribute19,
1750 x_attribute20 => x_attribute20,
1751 x_incomp_deadline_date => x_incomp_deadline_date,
1752 x_incomp_grading_schema_cd => x_incomp_grading_schema_cd,
1753 x_incomp_version_number => x_incomp_version_number,
1754 x_incomp_default_grade => x_incomp_default_grade,
1755 x_incomp_default_mark => x_incomp_default_mark,
1756 x_comments => x_comments,
1757 x_uoo_id => x_uoo_id,
1758 x_mark_capped_flag => x_mark_capped_flag,
1759 x_release_date => x_release_date,
1760 x_manual_override_flag => x_manual_override_flag,
1761 x_show_on_academic_histry_flag => x_show_on_academic_histry_flag
1762 );
1763 IF (X_MODE IN ('R', 'S')) THEN
1764 x_request_id := fnd_global.conc_request_id;
1765 x_program_id := fnd_global.conc_program_id;
1766 x_program_application_id := fnd_global.prog_appl_id;
1767 IF (x_request_id = -1) THEN
1768 x_request_id := old_references.request_id;
1769 x_program_id := old_references.program_id;
1770 x_program_application_id := old_references.program_application_id;
1771 x_program_update_date := old_references.program_update_date;
1772 ELSE
1773 x_program_update_date := SYSDATE;
1774 END IF;
1775 END IF;
1776 IF (x_mode = 'S') THEN
1777 igs_sc_gen_001.set_ctx('R');
1778 END IF;
1779 UPDATE igs_as_su_stmptout_all
1780 SET ci_start_dt = new_references.ci_start_dt,
1781 ci_end_dt = new_references.ci_end_dt,
1782 grading_schema_cd = new_references.grading_schema_cd,
1783 version_number = new_references.version_number,
1784 grade = new_references.grade,
1785 s_grade_creation_method_type = new_references.s_grade_creation_method_type,
1786 finalised_outcome_ind = new_references.finalised_outcome_ind,
1787 mark = new_references.mark,
1788 number_times_keyed = new_references.number_times_keyed,
1789 translated_grading_schema_cd = new_references.translated_grading_schema_cd,
1790 translated_version_number = new_references.translated_version_number,
1791 translated_grade = new_references.translated_grade,
1792 translated_dt = new_references.translated_dt,
1793 last_update_date = x_last_update_date,
1794 last_updated_by = x_last_updated_by,
1795 last_update_login = x_last_update_login,
1796 request_id = x_request_id,
1797 program_id = x_program_id,
1798 program_application_id = x_program_application_id,
1799 program_update_date = x_program_update_date,
1800 grading_period_cd = x_grading_period_cd,
1801 attribute_category = new_references.attribute_category,
1802 attribute1 = new_references.attribute1,
1803 attribute2 = new_references.attribute2,
1804 attribute3 = new_references.attribute3,
1805 attribute4 = new_references.attribute4,
1806 attribute5 = new_references.attribute5,
1807 attribute6 = new_references.attribute6,
1808 attribute7 = new_references.attribute7,
1809 attribute8 = new_references.attribute8,
1810 attribute9 = new_references.attribute9,
1811 attribute10 = new_references.attribute10,
1812 attribute11 = new_references.attribute11,
1813 attribute12 = new_references.attribute12,
1814 attribute13 = new_references.attribute13,
1815 attribute14 = new_references.attribute14,
1816 attribute15 = new_references.attribute15,
1817 attribute16 = new_references.attribute16,
1818 attribute17 = new_references.attribute17,
1819 attribute18 = new_references.attribute18,
1820 attribute19 = new_references.attribute19,
1821 attribute20 = new_references.attribute20,
1822 incomp_deadline_date = new_references.incomp_deadline_date,
1823 incomp_grading_schema_cd = new_references.incomp_grading_schema_cd,
1824 incomp_version_number = new_references.incomp_version_number,
1825 incomp_default_grade = new_references.incomp_default_grade,
1826 incomp_default_mark = new_references.incomp_default_mark,
1827 comments = new_references.comments,
1828 mark_capped_flag = new_references.mark_capped_flag,
1829 release_date = new_references.release_date,
1830 manual_override_flag = new_references.manual_override_flag,
1831 show_on_academic_histry_flag = new_references.show_on_academic_histry_flag
1832 WHERE ROWID = x_rowid;
1833 IF (SQL%NOTFOUND) THEN
1834 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1835 igs_ge_msg_stack.add;
1836 igs_sc_gen_001.unset_ctx('R');
1837 app_exception.raise_exception;
1838 END IF;
1839 IF (x_mode = 'S') THEN
1840 igs_sc_gen_001.unset_ctx('R');
1841 END IF;
1842
1843 after_dml (p_action => 'UPDATE', x_rowid => x_rowid);
1844
1845 EXCEPTION
1846 WHEN OTHERS THEN
1847 IF (SQLCODE = (-28115)) THEN
1848 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1849 fnd_message.set_token ('ERR_CD', SQLCODE);
1850 igs_ge_msg_stack.add;
1851 igs_sc_gen_001.unset_ctx('R');
1852 app_exception.raise_exception;
1853 ELSE
1854 igs_sc_gen_001.unset_ctx('R');
1855 RAISE;
1856 END IF;
1857 END update_row;
1858
1859 PROCEDURE add_row (
1860 x_rowid IN OUT NOCOPY VARCHAR2,
1861 x_org_id IN NUMBER,
1862 x_person_id IN NUMBER,
1863 x_course_cd IN VARCHAR2,
1864 x_unit_cd IN VARCHAR2,
1865 x_cal_type IN VARCHAR2,
1866 x_ci_sequence_number IN NUMBER,
1867 x_outcome_dt IN DATE,
1868 x_ci_start_dt IN DATE,
1869 x_ci_end_dt IN DATE,
1870 x_grading_schema_cd IN VARCHAR2,
1871 x_version_number IN NUMBER,
1872 x_grade IN VARCHAR2,
1873 x_s_grade_creation_method_type IN VARCHAR2,
1874 x_finalised_outcome_ind IN VARCHAR2,
1875 x_mark IN NUMBER,
1876 x_number_times_keyed IN NUMBER,
1877 x_translated_grading_schema_cd IN VARCHAR2,
1878 x_translated_version_number IN NUMBER,
1879 x_translated_grade IN VARCHAR2,
1880 x_translated_dt IN DATE,
1881 x_mode IN VARCHAR2,
1882 x_grading_period_cd IN VARCHAR2,
1883 x_attribute_category IN VARCHAR2,
1884 x_attribute1 IN VARCHAR2,
1885 x_attribute2 IN VARCHAR2,
1886 x_attribute3 IN VARCHAR2,
1887 x_attribute4 IN VARCHAR2,
1888 x_attribute5 IN VARCHAR2,
1889 x_attribute6 IN VARCHAR2,
1890 x_attribute7 IN VARCHAR2,
1891 x_attribute8 IN VARCHAR2,
1892 x_attribute9 IN VARCHAR2,
1893 x_attribute10 IN VARCHAR2,
1894 x_attribute11 IN VARCHAR2,
1895 x_attribute12 IN VARCHAR2,
1896 x_attribute13 IN VARCHAR2,
1897 x_attribute14 IN VARCHAR2,
1898 x_attribute15 IN VARCHAR2,
1899 x_attribute16 IN VARCHAR2,
1900 x_attribute17 IN VARCHAR2,
1901 x_attribute18 IN VARCHAR2,
1902 x_attribute19 IN VARCHAR2,
1903 x_attribute20 IN VARCHAR2,
1904 x_incomp_deadline_date IN DATE,
1905 x_incomp_grading_schema_cd IN VARCHAR2,
1906 x_incomp_version_number IN NUMBER,
1907 x_incomp_default_grade IN VARCHAR2,
1908 x_incomp_default_mark IN NUMBER,
1909 x_comments IN VARCHAR2,
1910 x_uoo_id IN NUMBER,
1911 x_mark_capped_flag IN VARCHAR2,
1912 x_release_date IN DATE,
1913 x_manual_override_flag IN VARCHAR2,
1914 x_show_on_academic_histry_flag IN VARCHAR2
1915 ) AS
1916 CURSOR c1 IS
1917 SELECT ROWID
1918 FROM igs_as_su_stmptout_all
1919 WHERE person_id = x_person_id
1920 AND course_cd = x_course_cd
1921 AND outcome_dt = x_outcome_dt
1922 AND grading_period_cd = x_grading_period_cd
1923 AND uoo_id = x_uoo_id;
1924 BEGIN
1925 OPEN c1;
1926 FETCH c1 INTO x_rowid;
1927 IF (c1%NOTFOUND) THEN
1928 CLOSE c1;
1929 insert_row (
1930 x_rowid,
1931 x_org_id,
1932 x_person_id,
1933 x_course_cd,
1934 x_unit_cd,
1935 x_cal_type,
1936 x_ci_sequence_number,
1937 x_outcome_dt,
1938 x_ci_start_dt,
1939 x_ci_end_dt,
1940 x_grading_schema_cd,
1941 x_version_number,
1942 x_grade,
1943 x_s_grade_creation_method_type,
1944 x_finalised_outcome_ind,
1945 x_mark,
1946 x_number_times_keyed,
1947 x_translated_grading_schema_cd,
1948 x_translated_version_number,
1949 x_translated_grade,
1950 x_translated_dt,
1951 x_mode,
1952 x_grading_period_cd,
1953 x_attribute_category,
1954 x_attribute1,
1955 x_attribute2,
1956 x_attribute3,
1957 x_attribute4,
1958 x_attribute5,
1959 x_attribute6,
1960 x_attribute7,
1961 x_attribute8,
1962 x_attribute9,
1963 x_attribute10,
1964 x_attribute11,
1965 x_attribute12,
1966 x_attribute13,
1967 x_attribute14,
1968 x_attribute15,
1969 x_attribute16,
1970 x_attribute17,
1971 x_attribute18,
1972 x_attribute19,
1973 x_attribute20,
1974 x_incomp_deadline_date,
1975 x_incomp_grading_schema_cd,
1976 x_incomp_version_number,
1977 x_incomp_default_grade,
1978 x_incomp_default_mark,
1979 x_comments,
1980 x_uoo_id,
1981 x_mark_capped_flag,
1982 x_release_date,
1983 x_manual_override_flag,
1984 x_show_on_academic_histry_flag
1985 );
1986 RETURN;
1987 END IF;
1988 CLOSE c1;
1989 update_row (
1990 x_rowid,
1991 x_person_id,
1992 x_course_cd,
1993 x_unit_cd,
1994 x_cal_type,
1995 x_ci_sequence_number,
1996 x_outcome_dt,
1997 x_ci_start_dt,
1998 x_ci_end_dt,
1999 x_grading_schema_cd,
2000 x_version_number,
2001 x_grade,
2002 x_s_grade_creation_method_type,
2003 x_finalised_outcome_ind,
2004 x_mark,
2005 x_number_times_keyed,
2006 x_translated_grading_schema_cd,
2007 x_translated_version_number,
2008 x_translated_grade,
2009 x_translated_dt,
2010 x_mode,
2011 x_grading_period_cd,
2012 x_attribute_category,
2013 x_attribute1,
2014 x_attribute2,
2015 x_attribute3,
2016 x_attribute4,
2017 x_attribute5,
2018 x_attribute6,
2019 x_attribute7,
2020 x_attribute8,
2021 x_attribute9,
2022 x_attribute10,
2023 x_attribute11,
2024 x_attribute12,
2025 x_attribute13,
2026 x_attribute14,
2027 x_attribute15,
2028 x_attribute16,
2029 x_attribute17,
2030 x_attribute18,
2031 x_attribute19,
2032 x_attribute20,
2033 x_incomp_deadline_date,
2034 x_incomp_grading_schema_cd,
2035 x_incomp_version_number,
2036 x_incomp_default_grade,
2037 x_incomp_default_mark,
2038 x_comments,
2039 x_uoo_id,
2040 x_mark_capped_flag,
2041 x_release_date,
2042 x_manual_override_flag,
2043 x_show_on_academic_histry_flag
2044 );
2045 END add_row;
2046
2047 PROCEDURE delete_row (x_rowid IN VARCHAR2,
2048 x_mode IN VARCHAR2) AS
2049 BEGIN
2050 before_dml (p_action => 'DELETE', x_rowid => x_rowid);
2051 IF (x_mode = 'S') THEN
2052 igs_sc_gen_001.set_ctx('R');
2053 END IF;
2054 DELETE FROM igs_as_su_stmptout_all
2055 WHERE ROWID = x_rowid;
2056 IF (SQL%NOTFOUND) THEN
2057 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2058 igs_ge_msg_stack.add;
2059 igs_sc_gen_001.unset_ctx('R');
2060 app_exception.raise_exception;
2061 END IF;
2062 IF (x_mode = 'S') THEN
2063 igs_sc_gen_001.unset_ctx('R');
2064 END IF;
2065
2066 after_dml (p_action => 'DELETE', x_rowid => x_rowid);
2067 END delete_row;
2068
2069 PROCEDURE check_constraints (column_name IN VARCHAR2, column_value IN VARCHAR2) AS
2070 BEGIN
2071 IF column_name IS NULL THEN
2072 NULL;
2073 ELSIF UPPER (column_name) = 'PERSON_ID' THEN
2074 new_references.person_id := igs_ge_number.to_num (column_value);
2075 ELSIF UPPER (column_name) = 'VERSION_NUMBER' THEN
2076 new_references.version_number := igs_ge_number.to_num (column_value);
2077 ELSIF UPPER (column_name) = 'MARK' THEN
2078 new_references.mark := igs_ge_number.to_num (column_value);
2079 ELSIF UPPER (column_name) = 'FINALISED_OUTCOME_IND' THEN
2080 new_references.finalised_outcome_ind := column_value;
2081 ELSIF UPPER (column_name) = 'TRANSLATED_VERSION_NUMBER' THEN
2082 new_references.translated_version_number := igs_ge_number.to_num (column_value);
2083 ELSIF UPPER (column_name) = 'NUMBER_TIMES_KEYED' THEN
2084 new_references.number_times_keyed := igs_ge_number.to_num (column_value);
2085 ELSIF UPPER (column_name) = 'CI_SEQUENCE_NUMBER' THEN
2086 new_references.ci_sequence_number := igs_ge_number.to_num (column_value);
2087 ELSIF UPPER (column_name) = 'CAL_TYPE' THEN
2088 new_references.cal_type := column_value;
2089 ELSIF UPPER (column_name) = 'COURSE_CD' THEN
2090 new_references.course_cd := column_value;
2091 ELSIF UPPER (column_name) = 'FINALISED_OUTCOME_IND' THEN
2092 new_references.finalised_outcome_ind := column_value;
2093 ELSIF UPPER (column_name) = 'GRADE' THEN
2094 new_references.grade := column_value;
2095 ELSIF UPPER (column_name) = 'GRADING_SCHEMA_CD' THEN
2096 new_references.grading_schema_cd := column_value;
2097 ELSIF UPPER (column_name) = 'S_GRADE_CREATION_METHOD_TYPE' THEN
2098 new_references.s_grade_creation_method_type := column_value;
2099 ELSIF UPPER (column_name) = 'TRANSLATED_GRADE' THEN
2100 new_references.translated_grade := column_value;
2101 ELSIF UPPER (column_name) = 'TRANSLATED_GRADING_SCHEMA_CD' THEN
2102 new_references.translated_grading_schema_cd := column_value;
2103 ELSIF UPPER (column_name) = 'UNIT_CD' THEN
2104 new_references.unit_cd := column_value;
2105 ELSIF UPPER (column_name) = 'GRADING_PERIOD_CD' THEN
2106 new_references.grading_period_cd := column_value;
2107 END IF;
2108 IF UPPER (column_name) = 'PERSON_ID'
2109 OR column_name IS NULL THEN
2110 IF new_references.person_id < 0
2111 OR new_references.person_id > 9999999999 THEN
2112 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2113 igs_ge_msg_stack.ADD;
2114 app_exception.raise_exception;
2115 END IF;
2116 END IF;
2117 IF UPPER (column_name) = 'VERSION_NUMBER'
2118 OR column_name IS NULL THEN
2119 IF new_references.version_number < 0
2120 OR new_references.version_number > 999 THEN
2121 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2122 igs_ge_msg_stack.ADD;
2123 app_exception.raise_exception;
2124 END IF;
2125 END IF;
2126 IF UPPER (column_name) = 'MARK'
2127 OR column_name IS NULL THEN
2128 IF new_references.mark < 0
2129 OR new_references.mark > 999 THEN
2130 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2131 igs_ge_msg_stack.ADD;
2132 app_exception.raise_exception;
2133 END IF;
2134 END IF;
2135 IF UPPER (column_name) = 'FINALISED_OUTCOME_IND'
2136 OR column_name IS NULL THEN
2137 IF new_references.finalised_outcome_ind NOT IN ('Y', 'N') THEN
2138 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2139 igs_ge_msg_stack.ADD;
2140 app_exception.raise_exception;
2141 END IF;
2142 END IF;
2143 IF UPPER (column_name) = 'TRANSLATED_VERSION_NUMBER'
2144 OR column_name IS NULL THEN
2145 IF new_references.translated_version_number < 0
2146 OR new_references.translated_version_number > 999 THEN
2147 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2148 igs_ge_msg_stack.ADD;
2149 app_exception.raise_exception;
2150 END IF;
2151 END IF;
2152 IF UPPER (column_name) = 'NUMBER_TIMES_KEYED'
2153 OR column_name IS NULL THEN
2154 IF new_references.number_times_keyed < 0
2155 OR new_references.number_times_keyed > 99 THEN
2156 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2157 igs_ge_msg_stack.ADD;
2158 app_exception.raise_exception;
2159 END IF;
2160 END IF;
2161 IF UPPER (column_name) = 'CI_SEQUENCE_NUMBER'
2162 OR column_name IS NULL THEN
2163 IF new_references.ci_sequence_number < 0
2164 OR new_references.ci_sequence_number > 999999 THEN
2165 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2166 igs_ge_msg_stack.ADD;
2167 app_exception.raise_exception;
2168 END IF;
2169 END IF;
2170 IF UPPER (column_name) = 'CAL_TYPE'
2171 OR column_name IS NULL THEN
2172 IF new_references.cal_type <> UPPER (new_references.cal_type) THEN
2173 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2174 igs_ge_msg_stack.ADD;
2175 app_exception.raise_exception;
2176 END IF;
2177 END IF;
2178 IF UPPER (column_name) = 'COURSE_CD'
2179 OR column_name IS NULL THEN
2180 IF new_references.course_cd <> UPPER (new_references.course_cd) THEN
2181 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2182 igs_ge_msg_stack.ADD;
2183 app_exception.raise_exception;
2184 END IF;
2185 END IF;
2186 IF UPPER (column_name) = 'GRADE'
2187 OR column_name IS NULL THEN
2188 IF new_references.grade <> UPPER (new_references.grade) THEN
2189 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2190 igs_ge_msg_stack.ADD;
2191 app_exception.raise_exception;
2192 END IF;
2193 END IF;
2194 IF UPPER (column_name) = 'GRADING_SCHEMA_CD'
2195 OR column_name IS NULL THEN
2196 IF new_references.grading_schema_cd <> UPPER (new_references.grading_schema_cd) THEN
2197 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2198 igs_ge_msg_stack.ADD;
2199 app_exception.raise_exception;
2200 END IF;
2201 END IF;
2202 IF UPPER (column_name) = 'S_GRADE_CREATION_METHOD_TYPE'
2203 OR column_name IS NULL THEN
2204 IF new_references.s_grade_creation_method_type <> UPPER (new_references.s_grade_creation_method_type) THEN
2205 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2206 igs_ge_msg_stack.ADD;
2207 app_exception.raise_exception;
2208 END IF;
2209 END IF;
2210 IF UPPER (column_name) = 'TRANSLATED_GRADE'
2211 OR column_name IS NULL THEN
2212 IF new_references.translated_grade <> UPPER (new_references.translated_grade) THEN
2213 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2214 igs_ge_msg_stack.ADD;
2215 app_exception.raise_exception;
2216 END IF;
2217 END IF;
2218 IF UPPER (column_name) = 'TRANSLATED_GRADING_SCHEMA_CD'
2219 OR column_name IS NULL THEN
2220 IF new_references.translated_grading_schema_cd <> UPPER (new_references.translated_grading_schema_cd) THEN
2221 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2222 igs_ge_msg_stack.ADD;
2223 app_exception.raise_exception;
2224 END IF;
2225 END IF;
2226 IF UPPER (column_name) = 'UNIT_CD'
2227 OR column_name IS NULL THEN
2228 IF new_references.unit_cd <> UPPER (new_references.unit_cd) THEN
2229 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2230 igs_ge_msg_stack.ADD;
2231 app_exception.raise_exception;
2232 END IF;
2233 END IF;
2234 IF UPPER (column_name) = 'GRADING_PERIOD_CD'
2235 OR column_name IS NULL THEN
2236 IF new_references.grading_period_cd <> UPPER (new_references.grading_period_cd) THEN
2237 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
2238 igs_ge_msg_stack.ADD;
2239 app_exception.raise_exception;
2240 END IF;
2241 END IF;
2242 END check_constraints;
2243 END igs_as_su_stmptout_pkg;