1 PACKAGE BODY igs_as_gen_002 AS
2 /* $Header: IGSAS02B.pls 115.10 2003/12/12 15:48:33 kdande ship $ */
3
4 FUNCTION assp_get_atyp_exmnbl (
5 p_assessment_type IN igs_as_assessmnt_itm_all.assessment_type%TYPE
6 ) RETURN VARCHAR2 IS
7 gv_other_detail VARCHAR2 (255);
8 BEGIN -- assp_get_atyp_exmnbl
9 -- This module fetches the value for the examinable_ind
10 -- for an assessment type from the IGS_AS_ASSESSMNT_TYP table.
11 DECLARE
12 CURSOR c_atyp IS
13 SELECT examinable_ind
14 FROM igs_as_assessmnt_typ
15 WHERE assessment_type = p_assessment_type;
16 v_atyp_rec c_atyp%ROWTYPE;
17 BEGIN
18 -- Fetch the examinable indicator
19 OPEN c_atyp;
20 FETCH c_atyp INTO v_atyp_rec;
21 IF c_atyp%NOTFOUND THEN
22 CLOSE c_atyp;
23 RETURN NULL;
24 END IF;
25 CLOSE c_atyp;
26 RETURN v_atyp_rec.examinable_ind;
27 EXCEPTION
28 WHEN OTHERS THEN
29 IF c_atyp%ISOPEN THEN
30 CLOSE c_atyp;
31 END IF;
32 RAISE;
33 END;
34 EXCEPTION
35 WHEN OTHERS THEN
36 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
37 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_atyp_exmnbl');
38 igs_ge_msg_stack.ADD;
39 app_exception.raise_exception;
40 END assp_get_atyp_exmnbl;
41
42 FUNCTION assp_get_dflt_exloc (p_location_cd IN VARCHAR2)
43 RETURN VARCHAR2 IS
44 gv_other_detail VARCHAR2 (255);
45 BEGIN -- assp_get_dflt_exloc
46 -- This routine returns the default examination IGS_AD_LOCATION
47 -- for a nominated campus.
48 -- The default IGS_AD_LOCATION is signified by the dflt_ind being set in the
49 -- IGS_AD_LOCATION_REL table between the specified campus and an exam
50 -- IGS_AD_LOCATION.
51 DECLARE
52 CURSOR c_lr IS
53 SELECT lr.sub_location_cd
54 FROM igs_ad_location_rel lr,
55 igs_ad_location loc,
56 igs_ad_location_type lt
57 WHERE lr.location_cd = p_location_cd
58 AND NVL (lr.dflt_ind, 'N') = 'Y'
59 AND NVL (loc.closed_ind, 'N') = 'N'
60 AND lt.s_location_type = 'EXAM_CTR'
61 AND loc.location_cd = lr.location_cd
62 AND loc.location_type = lt.location_type;
63 v_sub_location_cd igs_ad_location_rel.location_cd%TYPE DEFAULT NULL;
64 BEGIN
65 -- Search for the default exam IGS_AD_LOCATION
66 OPEN c_lr;
67 FETCH c_lr INTO v_sub_location_cd;
68 IF c_lr%NOTFOUND THEN
69 CLOSE c_lr;
70 RETURN NULL;
71 END IF;
72 CLOSE c_lr;
73 RETURN v_sub_location_cd;
74 END;
75 EXCEPTION
76 WHEN OTHERS THEN
77 NULL;
78 END assp_get_dflt_exloc;
79
80 FUNCTION assp_get_dflt_finls (
81 p_person_id IN NUMBER,
82 p_course_cd IN VARCHAR2,
83 p_unit_cd IN VARCHAR2,
84 p_cal_type IN VARCHAR2,
85 p_ci_sequence_number IN NUMBER,
86 -- anilk, 22-Apr-2003, Bug# 2829262
87 p_uoo_id IN NUMBER
88 )
89 RETURN VARCHAR2 IS
90 gv_other_detail VARCHAR2 (255);
91 BEGIN
92 -- assp_get_dflt_finls
93 -- Get the default finalised outcome indicator for a IGS_PE_PERSON.
94 -- The default is based on the last outcome against the SUA:
95 -- if finalised, the default is finalised; if no finalised,
96 -- or there is no prior outcome, the default is unfinalised.
97 DECLARE
98 v_finalised_outcome_ind igs_as_su_stmptout_all.finalised_outcome_ind%TYPE;
99 CURSOR c_suao IS
100 SELECT suao.finalised_outcome_ind
101 FROM igs_as_su_stmptout suao
102 WHERE suao.person_id = p_person_id
103 AND suao.course_cd = p_course_cd
104 AND suao.uoo_id = p_uoo_id
105 ORDER BY suao.outcome_dt DESC;
106 BEGIN
107 OPEN c_suao;
108 FETCH c_suao INTO v_finalised_outcome_ind;
109 IF (c_suao%NOTFOUND) THEN
110 v_finalised_outcome_ind := NULL;
111 END IF;
112 CLOSE c_suao;
113 RETURN v_finalised_outcome_ind;
114 END;
115 EXCEPTION
116 WHEN OTHERS THEN
117 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
118 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_dflt_finls');
119 igs_ge_msg_stack.ADD;
120 app_exception.raise_exception;
121 END assp_get_dflt_finls;
122
123 FUNCTION assp_get_dflt_grade (p_mark IN NUMBER, p_grading_schema_cd IN VARCHAR2, p_gs_version_number IN NUMBER)
124 RETURN VARCHAR2 IS
125 gv_other_detail VARCHAR2 (255);
126 BEGIN -- assp_get_dflt_grade
127 -- Routine to get the default grade within a nominated grading schema
128 -- which applies to a nominated mark.
129 DECLARE
130 v_rec_found BOOLEAN DEFAULT FALSE;
131 v_ret_val igs_as_grd_sch_grade.grade%TYPE DEFAULT NULL;
132
133 CURSOR c_gsg IS
134 SELECT grade
135 FROM igs_as_grd_sch_grade
136 WHERE grading_schema_cd = p_grading_schema_cd
137 AND version_number = p_gs_version_number
138 AND (lower_mark_range IS NOT NULL
139 OR upper_mark_range IS NOT NULL
140 )
141 AND NVL (lower_mark_range, 0) <= FLOOR (p_mark)
142 AND NVL (upper_mark_range, 1000) >= FLOOR (p_mark)
143 AND NVL (closed_ind, 'N') = 'N';
144 BEGIN
145 -- If parameters are null then return null grade
146 IF (p_mark IS NULL
147 OR p_grading_schema_cd IS NULL
148 OR p_gs_version_number IS NULL
149 ) THEN
150 RETURN NULL;
151 END IF;
152 FOR v_gsg_rec IN c_gsg LOOP
153 IF (v_rec_found = TRUE) THEN
154 -- multiple records found;
155 v_ret_val := NULL;
156 EXIT;
157 END IF;
158 v_rec_found := TRUE;
159 v_ret_val := v_gsg_rec.grade;
160 END LOOP;
161 RETURN v_ret_val;
162 END;
163 EXCEPTION
164 WHEN OTHERS THEN
165 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
166 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_dflt_grade');
167 igs_ge_msg_stack.ADD;
168 app_exception.raise_exception;
169 END assp_get_dflt_grade;
170
171 FUNCTION assp_get_exam_view (
172 p_uai_exam_cal_type igs_as_unitass_item_all.exam_cal_type%TYPE,
173 p_uai_exam_ci_seq igs_as_unitass_item_all.exam_ci_sequence_number%TYPE,
174 p_ueciv_exam_cal_type igs_as_unitass_item_all.exam_cal_type%TYPE,
175 p_ueciv_exam_ci_seq igs_as_unitass_item_all.exam_ci_sequence_number%TYPE,
176 p_sua_person_id igs_en_su_attempt_all.person_id%TYPE,
177 p_sua_course_cd igs_en_su_attempt_all.course_cd%TYPE,
178 p_sua_unit_cd igs_en_su_attempt_all.unit_cd%TYPE,
179 p_sua_version_number igs_en_su_attempt_all.version_number%TYPE,
180 p_sua_cal_type igs_en_su_attempt_all.cal_type%TYPE,
181 p_sua_ci_seq igs_en_su_attempt_all.ci_sequence_number%TYPE,
182 p_sua_unit_attempt_status igs_en_su_attempt_all.unit_attempt_status%TYPE,
183 p_sua_location_cd igs_en_su_attempt_all.location_cd%TYPE,
184 p_ucl_unit_mode igs_as_unit_class_all.unit_mode%TYPE,
185 p_sua_unit_class igs_en_su_attempt_all.unit_class%TYPE,
186 p_ueciv_ass_id igs_as_unitass_item_all.ass_id%TYPE
187 )
188 RETURN VARCHAR2 IS
189 BEGIN
190 DECLARE
191 v_return_val VARCHAR2 (10);
192 BEGIN
193 v_return_val := igs_as_gen_003.assp_get_supp_cal (
194 p_ueciv_exam_cal_type,
195 p_ueciv_exam_ci_seq,
196 p_sua_person_id,
197 p_sua_course_cd,
198 p_sua_unit_cd,
199 p_sua_version_number,
200 p_sua_cal_type,
201 p_sua_ci_seq,
202 p_sua_unit_attempt_status,
203 p_sua_location_cd,
204 p_ucl_unit_mode,
205 p_sua_unit_class,
206 p_ueciv_ass_id
207 );
208 IF v_return_val = 'Y'
209 OR v_return_val = 'N' THEN
210 RETURN v_return_val;
211 ELSE
212 IF ((p_uai_exam_cal_type IS NULL
213 OR p_ueciv_exam_cal_type = p_uai_exam_cal_type
214 )
215 AND (p_uai_exam_ci_seq IS NULL
216 OR p_ueciv_exam_ci_seq = p_uai_exam_ci_seq
217 )
218 ) THEN
219 RETURN 'Y';
220 ELSE
221 RETURN 'N';
222 END IF;
223 END IF;
224 END;
225 END assp_get_exam_view;
226
227 FUNCTION assp_get_gsg_cncd (p_grading_schema_cd IN VARCHAR2, p_version_number IN NUMBER, p_grade IN VARCHAR2)
228 RETURN VARCHAR2 IS
229 gv_other_detail VARCHAR2 (255);
230 BEGIN -- assp_get_gsg_cncd
231 -- Get whether a nominated grade is a 'conceded' pass grade, as indicated
232 -- by the value in the special grade type in the IGS_AS_GRD_SCH_GRADE table.
233 DECLARE
234 cst_conceded_pass CONSTANT VARCHAR2 (15) := 'CONCEDED-PASS';
235 v_s_special_grade_type igs_as_grd_sch_grade.s_special_grade_type%TYPE;
236 CURSOR c_gsg IS
237 SELECT gsg.s_special_grade_type
238 FROM igs_as_grd_sch_grade gsg
239 WHERE gsg.grading_schema_cd = p_grading_schema_cd
240 AND gsg.version_number = p_version_number
241 AND gsg.grade = p_grade;
242 BEGIN
243 OPEN c_gsg;
244 FETCH c_gsg INTO v_s_special_grade_type;
245 IF c_gsg%NOTFOUND THEN
246 CLOSE c_gsg;
247 RETURN 'N';
248 ELSE
249 CLOSE c_gsg;
250 IF v_s_special_grade_type = cst_conceded_pass THEN
251 RETURN 'Y';
252 ELSE
253 RETURN 'N';
254 END IF;
255 END IF;
256 EXCEPTION
257 WHEN OTHERS THEN
258 IF c_gsg%ISOPEN THEN
259 CLOSE c_gsg;
260 END IF;
261 RAISE;
262 END;
263 EXCEPTION
264 WHEN OTHERS THEN
265 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
266 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_gsg_cncd');
267 igs_ge_msg_stack.ADD;
268 app_exception.raise_exception;
269 END assp_get_gsg_cncd;
270
271 FUNCTION assp_get_gsg_rank (p_grading_schema_cd IN VARCHAR2, p_version_number IN NUMBER, p_grade IN VARCHAR2)
272 RETURN NUMBER IS
273 gv_other_detail VARCHAR2 (255);
274 BEGIN -- assp_get_gsg_rank
275 -- This module fetches the value for the rank for a grade within
276 -- a grading schema from the IGS_AS_GRD_SCH_GRADE table.
277 DECLARE
278 v_gsg_rank igs_as_grd_sch_grade.RANK%TYPE;
279 CURSOR c_gsg IS
280 SELECT gsg.RANK
281 FROM igs_as_grd_sch_grade gsg
282 WHERE gsg.grading_schema_cd = p_grading_schema_cd
283 AND gsg.version_number = p_version_number
284 AND gsg.grade = p_grade;
285 BEGIN
286 -- Fetch the rank
287 OPEN c_gsg;
288 FETCH c_gsg INTO v_gsg_rank;
289 IF c_gsg%NOTFOUND THEN
290 CLOSE c_gsg;
291 RETURN NULL;
292 END IF;
293 CLOSE c_gsg;
294 RETURN v_gsg_rank;
295 EXCEPTION
296 WHEN OTHERS THEN
297 IF c_gsg%ISOPEN THEN
298 CLOSE c_gsg;
299 END IF;
300 RAISE;
301 END;
302 EXCEPTION
303 WHEN OTHERS THEN
304 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
305 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_gsg_rank');
306 igs_ge_msg_stack.ADD;
307 app_exception.raise_exception;
308 END assp_get_gsg_rank;
309
310 FUNCTION assp_get_gsg_result (p_grading_schema_cd IN VARCHAR2, p_version_number IN NUMBER, p_grade IN VARCHAR2)
311 RETURN VARCHAR2 IS
312 gv_other_detail VARCHAR2 (255);
313 BEGIN -- assp_get_gsg_result
314 DECLARE
315 v_gsg_s_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
316 CURSOR c_gsg IS
317 SELECT gsg.s_result_type
318 FROM igs_as_grd_sch_grade gsg
319 WHERE gsg.grading_schema_cd = p_grading_schema_cd
320 AND gsg.version_number = p_version_number
321 AND gsg.grade = p_grade;
322 BEGIN
323 OPEN c_gsg;
324 FETCH c_gsg INTO v_gsg_s_result_type;
325 IF c_gsg%FOUND THEN
326 CLOSE c_gsg;
327 RETURN v_gsg_s_result_type;
328 ELSE
329 CLOSE c_gsg;
330 RETURN NULL;
331 END IF;
332 EXCEPTION
333 WHEN OTHERS THEN
334 IF c_gsg%ISOPEN THEN
335 CLOSE c_gsg;
336 END IF;
337 RAISE;
338 END;
339 EXCEPTION
340 WHEN OTHERS THEN
341 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
342 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_gsg_result');
343 igs_ge_msg_stack.ADD;
344 app_exception.raise_exception;
345 END assp_get_gsg_result;
346
347 FUNCTION assp_get_mark_mndtry (p_grading_schema_cd IN VARCHAR2, p_version_number IN NUMBER, p_grade IN VARCHAR2)
348 RETURN VARCHAR2 IS
349 gv_other_detail VARCHAR2 (200);
350 BEGIN
351 DECLARE
352 CURSOR c_gsg IS
353 SELECT lower_mark_range,
354 upper_mark_range
355 FROM igs_as_grd_sch_grade
356 WHERE grading_schema_cd = p_grading_schema_cd
357 AND version_number = p_version_number
358 AND grade = p_grade;
359 v_gsg_rec c_gsg%ROWTYPE;
360 BEGIN
361 OPEN c_gsg;
362 FETCH c_gsg INTO v_gsg_rec;
363 IF c_gsg%NOTFOUND THEN
364 CLOSE c_gsg;
365 RETURN 'N';
366 ELSE
367 IF (v_gsg_rec.lower_mark_range IS NOT NULL
368 AND v_gsg_rec.lower_mark_range <> 0
369 )
370 OR (v_gsg_rec.upper_mark_range IS NOT NULL
371 AND v_gsg_rec.upper_mark_range <> 0
372 ) THEN
373 CLOSE c_gsg;
374 RETURN 'Y';
375 ELSE
376 CLOSE c_gsg;
377 RETURN 'N';
378 END IF;
379 END IF;
380 END;
381 EXCEPTION
382 WHEN OTHERS THEN
383 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
384 fnd_message.set_token ('NAME', 'IGS_AS_GEN_002.assp_get_mark_mndtry');
385 igs_ge_msg_stack.ADD;
386 app_exception.raise_exception;
387 END assp_get_mark_mndtry;
388
389 FUNCTION assp_get_ai_s_type (p_ass_id IN NUMBER)
390 RETURN VARCHAR2 IS
391 gv_other_detail VARCHAR2 (255);
392 BEGIN -- assp_get_ai_s_type
393 -- Return the system type of an assessment item.
394 DECLARE
395 CURSOR c_atyp (cp_ass_id igs_as_assessmnt_itm.ass_id%TYPE) IS
396 SELECT s_assessment_type
397 FROM igs_as_assessmnt_typ atyp,
398 igs_as_assessmnt_itm ai
399 WHERE ai.ass_id = cp_ass_id
400 AND ai.assessment_type = atyp.assessment_type;
401 v_atyp_rec c_atyp%ROWTYPE;
402 BEGIN
403 OPEN c_atyp (p_ass_id);
404 FETCH c_atyp INTO v_atyp_rec;
405 IF c_atyp%NOTFOUND THEN
406 CLOSE c_atyp;
407 RAISE NO_DATA_FOUND;
408 END IF;
409 CLOSE c_atyp;
410 RETURN v_atyp_rec.s_assessment_type;
411 EXCEPTION
412 WHEN OTHERS THEN
413 gv_other_detail := 'Parm: p_ass_id - ' || TO_CHAR (p_ass_id);
414 RAISE;
415 END;
416 END assp_get_ai_s_type;
417 END igs_as_gen_002;
[Home] [Help]