1 PACKAGE BODY IGS_AS_VAL_SUAO AS
2 /* $Header: IGSAS32B.pls 115.10 2004/02/05 07:41:07 kdande ship $ */
3 -- To validate update of IGS_AS_SU_STMPTOUT record
4 FUNCTION ASSP_VAL_SUAO_UPD(
5 p_person_id IN NUMBER ,
6 p_course_cd IN VARCHAR2 ,
7 p_unit_cd IN VARCHAR2 ,
8 p_cal_type IN VARCHAR2 ,
9 p_ci_sequence_number IN NUMBER ,
10 p_outcome_dt IN DATE ,
11 p_new_finalised_outcome_ind IN VARCHAR2 ,
12 p_new_s_grade_creation_mthd_tp IN VARCHAR2 ,
13 p_new_mark IN NUMBER ,
14 p_new_grading_schema_cd IN VARCHAR2 ,
15 p_new_version_number IN NUMBER ,
16 p_new_grade IN VARCHAR2 ,
17 p_old_finalised_outcome_ind IN VARCHAR2 ,
18 p_old_s_grade_creation_mthd_tp IN VARCHAR2 ,
19 p_old_mark IN NUMBER ,
20 p_old_grading_schema_cd IN VARCHAR2 ,
21 p_old_version_number IN NUMBER ,
22 p_old_grade IN VARCHAR2 ,
23 p_message_name OUT NOCOPY VARCHAR2 ,
24 -- anilk, 22-Apr-2003, Bug# 2829262
25 p_uoo_id IN NUMBER )
26 RETURN boolean IS
27 gv_other_detail VARCHAR2(255);
28 BEGIN -- assp_val_suao_upd
29 -- Validate the update of a IGS_AS_SU_STMPTOUT record. Routine checks:
30 -- ? Cannot update a finalised outcome where
31 -- s_grade_creation_method_type <> ?DISCONTIN?
32 -- ? Cannot update s_grade_creation_method_type
33 -- ? Cannot unfinalise an outcome where
34 -- s_grade_creation_method_type = ?DISCONTIN?
35 -- IGS_GE_NOTE: This routine is designed to be used through both forms and a database
36 -- trigger. This is achieved by passing the ?old? values as optional
37 -- parameters. The database trigger can pass the :old values, whereas forms
38 -- can pass NULL, in which case the routine will load the values from the
39 -- database (since mutation is not a problem through forms).
40 DECLARE
41 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
42 cst_yes CONSTANT VARCHAR2(1) := 'Y';
43 cst_no CONSTANT VARCHAR2(1) := 'N';
44 cst_x CONSTANT VARCHAR2(1) := 'x';
45 v_finalised_outcome_ind IGS_AS_SU_STMPTOUT.finalised_outcome_ind%TYPE;
46 v_s_grade_creation_mthd_tp
47 IGS_AS_SU_STMPTOUT.s_grade_creation_method_type%TYPE;
48 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
49 v_grading_schema_cd IGS_AS_SU_STMPTOUT.grading_schema_cd%TYPE;
50 v_version_number IGS_AS_SU_STMPTOUT.version_number%TYPE;
51 v_grade IGS_AS_SU_STMPTOUT.grade%TYPE;
52 CURSOR c_suao (
53 cp_person_id IGS_AS_SU_STMPTOUT.person_id%TYPE,
54 cp_course_cd IGS_AS_SU_STMPTOUT.course_cd%TYPE,
55 cp_unit_cd IGS_AS_SU_STMPTOUT.unit_cd%TYPE,
56 cp_cal_type IGS_AS_SU_STMPTOUT.cal_type%TYPE,
57 cp_ci_sequence_number IGS_AS_SU_STMPTOUT.ci_sequence_number%TYPE,
58 cp_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE,
59 cp_uoo_id IGS_AS_SU_STMPTOUT.uoo_id%TYPE) IS
60 SELECT mark,
61 grading_schema_cd,
62 version_number,
63 grade,
64 finalised_outcome_ind,
65 s_grade_creation_method_type
66 FROM IGS_AS_SU_STMPTOUT
67 WHERE person_id = cp_person_id AND
68 course_cd = cp_course_cd AND
69 -- anilk, 22-Apr-2003, Bug# 2829262
70 uoo_id = cp_uoo_id AND
71 outcome_dt = cp_outcome_dt;
72 v_suao_rec c_suao%ROWTYPE;
73 BEGIN
74 -- Set the default message number
75 p_message_name := NULL;
76 -- initialise the variables to the old values
77 v_finalised_outcome_ind := p_old_finalised_outcome_ind ;
78 v_s_grade_creation_mthd_tp := p_old_s_grade_creation_mthd_tp ;
79 v_mark := p_old_mark ;
80 v_grading_schema_cd := p_old_grading_schema_cd ;
81 v_version_number := p_old_version_number ;
82 v_grade := p_old_grade ;
83 -- If the ?old? parameters are not set then load from the database
84 -- (assuming commit has not occurred).
85 IF p_old_finalised_outcome_ind IS NULL OR
86 p_old_s_grade_creation_mthd_tp IS NULL THEN
87 OPEN c_suao(
88 p_person_id,
89 p_course_cd,
90 p_unit_cd,
91 p_cal_type,
92 p_ci_sequence_number,
93 p_outcome_dt,
94 -- anilk, 22-Apr-2003, Bug# 2829262
95 p_uoo_id
96 );
97 FETCH c_suao INTO v_suao_rec;
98 IF c_suao%NOTFOUND THEN
99 CLOSE c_suao;
100 -- Internal error, will come out NOCOPY in the db trigger
101 RETURN TRUE;
102 END IF;
103 CLOSE c_suao;
104 v_finalised_outcome_ind := v_suao_rec.finalised_outcome_ind ;
105 v_s_grade_creation_mthd_tp := v_suao_rec.s_grade_creation_method_type ;
106 v_mark := v_suao_rec.mark ;
107 v_grading_schema_cd := v_suao_rec.grading_schema_cd ;
108 v_version_number := v_suao_rec.version_number ;
109 v_grade := v_suao_rec.grade ;
110 END IF;
111 IF NVL(v_s_grade_creation_mthd_tp, cst_x) <>
112 NVL(p_new_s_grade_creation_mthd_tp, cst_x) THEN
113 p_message_name := 'IGS_AS_CANNOT_CHG_GRD_CREATE';
114 RETURN FALSE;
115 END IF;
116 IF NVL(v_s_grade_creation_mthd_tp, cst_x) = cst_discontin AND
117 NVL(p_new_finalised_outcome_ind, cst_x) = cst_no THEN
118 p_message_name := 'IGS_AS_CANNOT_UNFINALISE_SYS';
119 RETURN FALSE;
120 END IF;
121 IF NVL(v_finalised_outcome_ind, cst_x) = cst_yes AND
122 NVL(v_s_grade_creation_mthd_tp, cst_x) <> cst_discontin THEN
123 IF NVL(v_mark, 0) <> NVL(p_new_mark, 0) OR
124 NVL(v_grading_schema_cd, cst_x) <> NVL(p_new_grading_schema_cd, cst_x) OR
125 NVL(v_version_number, 0) <> NVL(p_new_version_number, 0) OR
126 NVL(v_grade, cst_x) <> NVL(p_new_grade, cst_x) THEN
127 p_message_name := 'IGS_AS_CANNOT_ALTER_MARK_GRD';
128 RETURN FALSE;
129 END IF;
130 END IF;
131 -- Return the default value
132 RETURN TRUE;
133 END;
134 EXCEPTION
135 WHEN OTHERS THEN
136 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
137 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_SUAO.ASSP_VAL_SUAO_UPD');
138 Igs_Ge_Msg_Stack.Add;
139 App_Exception.Raise_Exception;
140
141
142 END assp_val_suao_upd;
143 --
144 -- Validate the insert of a IGS_AS_SU_STMPTOUT record
145 FUNCTION ASSP_VAL_SUAO_INS(
146 p_person_id IN NUMBER ,
147 p_course_cd IN VARCHAR2 ,
148 p_unit_cd IN VARCHAR2 ,
149 p_cal_type IN VARCHAR2 ,
150 p_ci_sequence_number IN NUMBER ,
151 p_outcome_dt IN DATE ,
152 p_s_grade_creation_method_type IN VARCHAR2 ,
153 p_unit_attempt_status IN VARCHAR2 ,
154 p_message_name OUT NOCOPY VARCHAR2 ,
155 -- anilk, 22-Apr-2003, Bug# 2829262
156 p_uoo_id IN NUMBER )
157 RETURN boolean IS
158 gv_other_detail VARCHAR2(255);
159 BEGIN -- assp_val_suao_ins
160 -- Validate the insert of a IGS_AS_SU_STMPTOUT record.
161 DECLARE
162 cst_unconfirm CONSTANT VARCHAR2(255) := 'UNCOMFIRM';
163 cst_enrolled CONSTANT VARCHAR2(255) := 'ENROLLED';
164 cst_complete CONSTANT VARCHAR2(255) := 'COMPLETED';
165 cst_discontin CONSTANT VARCHAR2(255) := 'DISCONTIN';
166 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
167 v_check CHAR;
168 CURSOR c_uas IS
169 SELECT unit_attempt_status
170 FROM IGS_EN_SU_ATTEMPT
171 WHERE person_id = p_person_id AND
172 course_cd = p_course_cd AND
173 -- anilk, 22-Apr-2003, Bug# 2829262
174 uoo_id = p_uoo_id;
175 CURSOR c_chk_suao IS
176 SELECT 'x'
177 FROM IGS_AS_SU_STMPTOUT
178 WHERE person_id = p_person_id and
179 course_cd = p_course_cd and
180 -- anilk, 22-Apr-2003, Bug# 2829262
181 uoo_id = p_uoo_id and
182 outcome_dt <> p_outcome_dt and
183 s_grade_creation_method_type = cst_discontin;
184 BEGIN
185 p_message_name := NULL;
186 -- 1. Validate the IGS_PS_UNIT attempt status
187 IF (p_unit_attempt_status IS NULL) THEN
188 -- Load IGS_PS_UNIT attempt status
189 OPEN c_uas;
190 FETCH c_uas INTO v_unit_attempt_status;
191 IF (c_uas%NOTFOUND) THEN
192 v_unit_attempt_status := cst_unconfirm;
193 ELSE
194 -- v_unit_attempt_status := selected value;
195 NULL;
196 END IF;
197 CLOSE c_uas;
198 ELSE
199 v_unit_attempt_status := p_unit_attempt_status;
200 END IF;
201 IF (v_unit_attempt_status NOT IN ( cst_enrolled,
202 cst_complete,
203 cst_discontin)) THEN
204 p_message_name := 'IGS_AS_ONLY_ADD_AGAINST_ENR';
205 RETURN FALSE;
206 END IF;
207 -- 2. Validate the grade creation method type against the IGS_PS_UNIT attempt status
208 IF ( p_s_grade_creation_method_type <> cst_discontin AND
209 v_unit_attempt_status = cst_discontin) THEN
210 p_message_name := 'IGS_AS_GRD_ONLY_ADD_DISCONT';
211 RETURN FALSE;
212 END IF;
213 IF ( p_s_grade_creation_method_type = cst_discontin AND
214 v_unit_attempt_status <> cst_discontin) THEN
215 p_message_name := 'IGS_AS_CANNOT_ADD_DISCONT_GRD';
216 RETURN FALSE;
217 END IF;
218 -- 3. if not a discontinuation grade, then ensure than no discontinuation
219 -- grade exists.
220 IF (p_s_grade_creation_method_type <> cst_discontin) THEN
221 OPEN c_chk_suao;
222 FETCH c_chk_suao INTO v_check;
223 IF (c_chk_suao%FOUND) THEN
224 CLOSE c_chk_suao;
225 p_message_name := 'IGS_AS_CANNOT_ADD_NONDISCONT';
226 RETURN FALSE;
227 END IF;
228 CLOSE c_chk_suao;
229 END IF;
230 -- 4. Validate for closed grade creation method type
231 IF (IGS_AS_VAL_SUAO.assp_val_sgcmt_clsd(
232 p_s_grade_creation_method_type,
233 p_message_name ) = FALSE) THEN
234 RETURN FALSE;
235 END IF;
236 RETURN TRUE;
237 END;
238 EXCEPTION
239 WHEN OTHERS THEN
240 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
241 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_SUAO.ASSP_VAL_SUAO_INS');
242 Igs_Ge_Msg_Stack.Add;
243 App_Exception.Raise_Exception;
244
245
246 END assp_val_suao_ins;
247 --
248 -- Validate IGS_AS_SU_STMPTOUT outcome_dt field
249 FUNCTION ASSP_VAL_SUAO_DT(
250 p_person_id IN NUMBER ,
251 p_course_cd IN VARCHAR2 ,
252 p_unit_cd VARCHAR2 ,
253 p_cal_type IN VARCHAR2 ,
254 p_ci_sequence_number IN NUMBER ,
255 p_outcome_dt IN DATE ,
256 p_message_name OUT NOCOPY VARCHAR2 ,
257 -- anilk, 22-Apr-2003, Bug# 2829262
258 p_uoo_id IN NUMBER )
259 RETURN boolean IS
260 gv_other_detail VARCHAR2(255);
261 BEGIN -- assp_val_suao_dt
262 -- Validate the outcome date of a IGS_AS_SU_STMPTOUT record being
263 -- inserted
264 DECLARE
265 v_check CHAR;
266 v_ret_val BOOLEAN DEFAULT TRUE;
267 CURSOR c_suao IS
268 SELECT 'x'
269 FROM IGS_AS_SU_STMPTOUT
270 WHERE person_id = p_person_id AND
271 course_cd = p_course_cd AND
272 -- anilk, 22-Apr-2003, Bug# 2829262
273 uoo_id = p_uoo_id AND
274 outcome_dt > p_outcome_dt;
275 BEGIN
276 p_message_name := NULL;
277 -- Date cannot be prior to any existing outcome record for the IGS_PS_UNIT attempt
278 OPEN c_suao;
279 FETCH c_suao INTO v_check;
280 IF (c_suao%FOUND) THEN
281 v_ret_val := FALSE;
282 p_message_name := 'IGS_AS_OUTCOME_DT_NOT_PRIOR';
283 END IF;
284 CLOSE c_suao;
285 RETURN v_ret_val;
286 END;
287 EXCEPTION
288 WHEN OTHERS THEN
289 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
290 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_SUAO.ASSP_VAL_SUAO_DT');
291 Igs_Ge_Msg_Stack.Add;
292 App_Exception.Raise_Exception;
293
294
295 END assp_val_suao_dt;
296 --
297 -- Validate s_grade_creation_method_type closed indicator
298 FUNCTION ASSP_VAL_SGCMT_CLSD(
299 p_s_grade_creation_method_type IN VARCHAR2 ,
300 p_message_name OUT NOCOPY VARCHAR2 )
301 RETURN boolean IS
302 gv_other_detail VARCHAR2(255);
303 BEGIN -- assp_val_sgcmt_clsd
304 -- Validate the System Grade Creation Method Type closed indicator.
305 DECLARE
306 v_closed_ind IGS_LOOKUPS_VIEW.closed_ind%TYPE;
307 v_ret_val BOOLEAN DEFAULT TRUE;
308 CURSOR c_sgcmt IS
309 SELECT closed_ind
310 FROM IGS_LOOKUPS_VIEW
311 WHERE LOOKUP_TYPE = p_s_grade_creation_method_type;
312 BEGIN
313 p_message_name := NULL;
314 OPEN c_sgcmt;
315 FETCH c_sgcmt INTO v_closed_ind;
316 IF (c_sgcmt%FOUND) THEN
317 IF (v_closed_ind = 'Y') THEN
318 p_message_name := 'IGS_AS_CANNOT_ADD_OUTCOME';
319 v_ret_val := FALSE;
320 END IF;
321 END IF;
322 CLOSE c_sgcmt;
323 RETURN v_ret_val;
324 END;
325 EXCEPTION
326 WHEN OTHERS THEN
327 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
328 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_SUAO.ASSP_VAL_SGCMT_CLSD');
329 Igs_Ge_Msg_Stack.Add;
330 App_Exception.Raise_Exception;
331 END assp_val_sgcmt_clsd;
332 --
333 -- To validate an assessment mark against a grade
334 -- Return TRUE if the validation passes; Else return FALSE
335 --
336 FUNCTION assp_val_mark_grade (
337 p_mark IN NUMBER,
338 p_grade IN VARCHAR2,
339 p_grading_schema_cd IN VARCHAR2,
340 p_version_number IN NUMBER,
341 p_message_name OUT NOCOPY VARCHAR2
342 ) RETURN BOOLEAN IS
343 --
344 gv_other_detail VARCHAR2 (255);
345 v_lower_mark_range igs_as_grd_sch_grade.lower_mark_range%TYPE;
346 v_upper_mark_range igs_as_grd_sch_grade.upper_mark_range%TYPE;
347 --
348 -- Get the Lower and Upper Mark limits of the Grade as setup in the
349 -- Grading Schema
350 --
351 CURSOR c_gsg IS
352 SELECT gsg.lower_mark_range,
353 gsg.upper_mark_range
354 FROM igs_as_grd_sch_grade gsg
355 WHERE gsg.grading_schema_cd = p_grading_schema_cd
356 AND gsg.version_number = p_version_number
357 AND gsg.grade = p_grade;
358 --
359 -- Get the Minimum of the Lower and Maximum of the Upper Mark limits
360 -- as setup in the Grading Schema
361 --
362 CURSOR c_gsg_min_max IS
363 SELECT NVL (MIN (gsg.lower_mark_range), 0) min_lower_mark_range,
364 NVL (MAX (gsg.upper_mark_range), 1000) max_upper_mark_range
365 FROM igs_as_grd_sch_grade gsg
366 WHERE gsg.grading_schema_cd = p_grading_schema_cd
367 AND gsg.version_number = p_version_number;
368 rec_gsg_min_max c_gsg_min_max%ROWTYPE;
369 BEGIN
370 -- Validate a mark against a grade within a specified grading schema version
371 p_message_name := NULL;
372 --
373 IF (p_mark IS NULL)
374 OR (p_grade IS NULL) THEN
375 p_message_name := NULL;
376 RETURN TRUE;
377 END IF;
378 --
379 OPEN c_gsg_min_max;
380 FETCH c_gsg_min_max INTO rec_gsg_min_max;
381 CLOSE c_gsg_min_max;
382 IF ((p_mark < rec_gsg_min_max.min_lower_mark_range) OR
383 (p_mark > rec_gsg_min_max.max_upper_mark_range)) THEN
384 p_message_name := 'IGS_AS_MARK_INVALID';
385 RETURN FALSE;
386 END IF;
387 --
388 OPEN c_gsg;
389 FETCH c_gsg INTO v_lower_mark_range,
390 v_upper_mark_range;
391 --
392 IF (c_gsg%NOTFOUND) THEN
393 p_message_name := NULL;
394 CLOSE c_gsg;
395 RETURN TRUE;
396 END IF;
397 --
398 CLOSE c_gsg;
399 --
400 IF (v_lower_mark_range IS NOT NULL)
401 AND (p_mark < v_lower_mark_range) THEN
402 p_message_name := 'IGS_AS_MARK_BELOW_SPECF_RANGE';
403 RETURN FALSE;
404 END IF;
405 --
406 IF (v_upper_mark_range IS NOT NULL)
407 AND (p_mark > v_upper_mark_range) THEN
408 p_message_name := 'IGS_AS_MARK_ABOVE_SPECF_RANGE';
409 RETURN FALSE;
410 END IF;
411 --
412 p_message_name := NULL;
413 RETURN TRUE;
414 EXCEPTION
415 WHEN OTHERS THEN
416 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
417 fnd_message.set_token ('NAME', 'IGS_AS_VAL_SUAO.ASSP_VAL_MARK_GRADE');
418 igs_ge_msg_stack.ADD;
419 app_exception.raise_exception;
420 END assp_val_mark_grade;
421 --
422 --
423 --
424 PROCEDURE assp_val_mark_grade_ss (
425 p_mark IN NUMBER,
426 p_grade IN VARCHAR2,
427 p_grading_schema_cd IN VARCHAR2,
428 p_version_number IN NUMBER,
429 p_message_name OUT NOCOPY VARCHAR2,
430 p_boolean OUT NOCOPY VARCHAR2
431 ) IS
432 lv_message_name VARCHAR2 (2000);
433 -- Validate a mark against a grade within a specified grading schema version
434 -- This is a wrapper procedure on the assp_val_mark_grade function for SS Pages.
435 -- Used in GradeAMImpl.java
436 BEGIN
437 IF igs_as_val_suao.assp_val_mark_grade (
438 p_mark,
439 p_grade,
440 p_grading_schema_cd,
441 p_version_number,
442 lv_message_name) THEN
443 p_boolean := 'TRUE';
444 p_message_name := lv_message_name;
445 ELSE
446 p_boolean := 'FALSE';
447 p_message_name := lv_message_name;
448 END IF;
449 END assp_val_mark_grade_ss;
450
451 END igs_as_val_suao;
[Home] [Help]