1 PACKAGE BODY IGS_AS_GEN_003 AS
2 /* $Header: IGSAS03B.pls 120.1 2006/01/31 01:50:14 smaddali noship $ */
3
4 /* Change History
5 who when what
6 smvk 09-Jul-2004 Bug # 3676145. Modified cursors c_usa to use Active (not closed) unit classes.
7 lkaki 20-Aug-2004 Bug # 3842511. Added additional parameter for including the grade and mark
8 for the students whose outcomes are released.
9 smaddali 20-dec-2005 Bug#4666657 : modified procedure assp_get_sua_outcome to loop thru chain transfers
10 */
11 --
12 FUNCTION assp_get_sua_exam_tp(
13 p_person_id IN NUMBER ,
14 p_course_cd IN VARCHAR2 ,
15 p_unit_cd IN VARCHAR2 ,
16 p_cal_type IN VARCHAR2 ,
17 p_ci_sequence_number IN NUMBER ,
18 p_unit_attempt_status IN VARCHAR2 ,
19 -- anilk, 22-Apr-2003, Bug# 2829262
20 p_uoo_id IN NUMBER )
21 RETURN VARCHAR2 IS
22 gv_other_detail VARCHAR2(255);
23 BEGIN -- assp_get_sua_exam_tp
24 -- Get the examination type for a student unit attempt attempt assessment item.
25 -- This routine will ascertain if the student is sitting a NORMAL exam,
26 -- or whether they are doing a SUPPLEMENTARY or SPECIAL examination.
27 -- The supp/special attribute is ascertained from the students grade.
28 -- The grading schema grade table has a field indicating whether the grade
29 -- signifies the granting of a supp/special examination.
30 --ijeddy, Bug 3201661, Grade Book.
31 DECLARE
32 CURSOR c_gsg IS
33 SELECT gsg.s_special_grade_type
34 FROM IGS_AS_SU_STMPTOUT suao,
35 IGS_AS_GRD_SCH_GRADE gsg
36 WHERE suao.person_id = p_person_id AND
37 suao.course_cd = p_course_cd AND
38 -- anilk, 22-Apr-2003, Bug# 2829262
39 suao.uoo_id = p_uoo_id AND
40 suao.finalised_outcome_ind = 'Y' AND
41 suao.grading_schema_cd = gsg.grading_schema_cd AND
42 suao.version_number = gsg.version_number AND
43 suao.grade = gsg.grade AND
44 suao.outcome_dt IN (SELECT MAX(outcome_dt)
45 FROM IGS_AS_SU_STMPTOUT
46 WHERE person_id = suao.person_id AND
47 course_cd = suao.course_cd AND
48 -- anilk, 22-Apr-2003, Bug# 2829262
49 uoo_id = suao.uoo_id);
50
51 cst_normal CONSTANT VARCHAR2(15) := 'NORMAL';
52 cst_special CONSTANT VARCHAR2(15) := 'SPECIAL';
53 cst_supp CONSTANT VARCHAR2(15) := 'SUPP';
54 v_s_special_grade_type IGS_AS_GRD_SCH_GRADE.s_special_grade_type%TYPE;
55 BEGIN
56 -- If the IGS_PS_UNIT attempt status is not completed then it must be a normal
57 -- examination.
58 IF p_unit_attempt_status <> 'COMPLETED' THEN
59 RETURN cst_normal;
60 END IF;
61 -- Select the latest grade from the view
62 OPEN c_gsg;
63 FETCH c_gsg INTO v_s_special_grade_type;
64 IF c_gsg%NOTFOUND THEN
65 CLOSE c_gsg;
66 RETURN cst_normal;
67 END IF;
68 CLOSE c_gsg;
69 IF v_s_special_grade_type = 'SUPP-EXAM' THEN
70 RETURN cst_supp;
71 ELSIF v_s_special_grade_type = 'SPECIAL-EXAM' THEN
72 RETURN cst_special;
73 ELSE
74 RETURN cst_normal;
75 END IF;
76 END;
77 EXCEPTION
78 WHEN OTHERS THEN
79 RAISE;
80 END assp_get_sua_exam_tp;
81 FUNCTION assp_get_sua_exloc(
82 p_person_id IN NUMBER ,
83 p_course_cd IN VARCHAR2 ,
84 p_unit_cd IN VARCHAR2 ,
85 p_cal_type IN VARCHAR2 ,
86 p_ci_sequence_number IN NUMBER ,
87 p_ass_id IN NUMBER ,
88 -- anilk, 22-Apr-2003, Bug# 2829262
89 p_uoo_id IN NUMBER )
90 RETURN VARCHAR2 IS
91 BEGIN
92 -- assp_get_sua_exloc
93 -- Get the applicable examination IGS_AD_LOCATION for a nominated student
94 -- IGS_PS_UNIT attempt record.
95 -- The routine will search for (in order of preference):
96 -- 0. Non-central examination (see below)
97 -- 1. A IGS_EN_SU_ATTEMPT.exam_location_cd value
98 -- 2. A IGS_EN_STDNT_PS_ATT.exam_location_cd value
99 -- 3. The default exam IGS_AD_LOCATION for the enrolled IGS_PS_UNIT attempt
100 -- If the assessment id is passed as a parameter, the routine will determine
101 -- whether the examination is a non-central examination, in which case all
102 -- students are grouped under a single examination IGS_AD_LOCATION. This IGS_AD_LOCATION
103 -- is defined in the IGS_AS_SASSESS_TYPE table.
104 DECLARE
105 v_nonc_exam_loc_cd IGS_AS_SASSESS_TYPE.non_cntrl_exam_loc_cd%TYPE;
106 v_sua_location IGS_AD_LOCATION.location_cd%TYPE;
107 v_sca_location IGS_AD_LOCATION.location_cd%TYPE;
108 CURSOR c_sat IS
109 SELECT sat.non_cntrl_exam_loc_cd
110 FROM IGS_AS_ASSESSMNT_ITM ai,
111 IGS_AS_ASSESSMNT_TYP atyp,
112 IGS_AS_SASSESS_TYPE sat
113 WHERE ai.ass_id = p_ass_id AND
114 atyp.assessment_type = ai.assessment_type AND
115 atyp.s_assessment_type = 'NONCENTRAL' AND
116 sat.s_assessment_type= atyp.s_assessment_type;
117 CURSOR c_sua IS
118 SELECT sua.location_cd,
119 sua.exam_location_cd,
120 um.s_unit_mode
121 FROM IGS_EN_SU_ATTEMPT sua,
122 IGS_AS_UNIT_CLASS ucl,
123 IGS_AS_UNIT_MODE um
124 WHERE sua.person_id = p_person_id AND
125 sua.course_cd = p_course_cd AND
126 -- anilk, 22-Apr-2003, Bug# 2829262
127 sua.uoo_id = p_uoo_id AND
128 ucl.unit_class = sua.unit_class AND
129 um.unit_mode = ucl.unit_mode AND
130 ucl.closed_ind = 'N';
131 v_sua_rec c_sua%ROWTYPE;
132 CURSOR c_sca IS
133 SELECT exam_location_cd,
134 location_cd
135 FROM IGS_EN_STDNT_PS_ATT
136 WHERE person_id = p_person_id AND
137 course_cd = p_course_cd;
138 v_sca_rec c_sca%ROWTYPE;
139 BEGIN
140 -- 0. If assessment ID is set, then check for non-central examination IGS_AD_LOCATION.
141 IF (p_ass_id IS NOT NULL) THEN
142 OPEN c_sat;
143 FETCH c_sat INTO v_nonc_exam_loc_cd;
144 IF (c_sat%FOUND AND
145 v_nonc_exam_loc_cd IS NOT NULL) THEN
146 CLOSE c_sat;
147 RETURN v_nonc_exam_loc_cd;
148 END IF;
149 CLOSE c_sat;
150 END IF;
151 -- 1. If any of the parameters are null return null
152 IF (
153 p_person_id IS NULL OR
154 p_course_cd IS NULL OR
155 p_unit_cd IS NULL OR
156 p_cal_type IS NULL OR
157 p_ci_sequence_number IS NULL OR
158 p_uoo_id IS NULL ) THEN
159 RETURN NULL;
160 END IF;
161 -- 2. Get details from the student IGS_PS_UNIT attempt record
162 OPEN c_sua;
163 FETCH c_sua INTO v_sua_rec;
164 IF (c_sua%NOTFOUND) THEN
165 CLOSE c_sua;
166 RETURN NULL;
167 END IF;
168 CLOSE c_sua;
169 -- 3. If the sua exam IGS_AD_LOCATION is set then return it
170 IF (V_sua_rec.exam_location_cd IS NOT NULL) THEN
171 RETURN v_sua_rec.exam_location_cd;
172 END IF;
173 -- 3.1 If On-Campus IGS_PS_UNIT use the default IGS_AD_LOCATION.
174 IF (v_sua_rec.s_unit_mode = 'ON') THEN
175 v_sua_location := IGS_AS_GEN_002.ASSP_GET_DFLT_EXLOC(v_sua_rec.location_cd);
176 IF (v_sua_location IS NOT NULL) THEN
177 RETURN v_sua_location;
178 END IF;
179 END IF;
180 -- 4. Search for exam IGS_AD_LOCATION code in the student IGS_PS_COURSE attempt record
181 OPEN c_sca;
182 FETCH c_sca INTO v_sca_rec;
183 CLOSE c_sca;
184 IF (v_sca_rec.exam_location_cd IS NOT NULL) THEN
185 RETURN v_sca_rec.exam_location_cd;
186 END IF;
187 -- 5. Search for the default exam IGS_AD_LOCATION for the enrolled IGS_PS_UNIT campus
188 v_sua_location := IGS_AS_GEN_002.ASSP_GET_DFLT_EXLOC(v_sua_rec.location_cd);
189 IF (v_sua_location IS NOT NULL) THEN
190 RETURN v_sua_location;
191 END IF;
192 -- 6. Search for the default exam IGS_AD_LOCATION for the enrolled IGS_PS_COURSE
193 v_sca_location := IGS_AS_GEN_002.ASSP_GET_DFLT_EXLOC(v_sca_rec.location_cd);
194 IF (v_sca_location IS NOT NULL) THEN
195 RETURN v_sca_location;
196 END IF;
197 RETURN NULL;
198 END;
199 END assp_get_sua_exloc;
200 FUNCTION assp_get_sua_grade(
201 p_person_id IN NUMBER ,
202 p_course_cd IN VARCHAR2 ,
203 p_unit_cd IN VARCHAR2 ,
204 p_cal_type IN VARCHAR2 ,
205 p_ci_sequence_number IN NUMBER ,
206 p_unit_attempt_status IN VARCHAR2 ,
207 p_finalised_ind IN VARCHAR2 ,
208 p_grading_schema_cd OUT NOCOPY VARCHAR2 ,
209 p_gs_version_number OUT NOCOPY NUMBER ,
210 p_grade OUT NOCOPY VARCHAR2 ,
211 -- anilk, 22-Apr-2003, Bug# 2829262
212 p_uoo_id IN NUMBER )
213 RETURN VARCHAR2 IS
214 gv_other_detail VARCHAR2(255);
215 BEGIN -- assp_get_sua_grade
216 -- This is an enrolments module.
217 -- It gets the grade of a student IGS_PS_UNIT attempt within a IGS_PS_COURSE code.
218 -- This routine will determine the appropriate grade (and its matching
219 -- result type) and return them. If no grade is found NULL will be
220 -- returned (and output parameters will be NULL).
221 -- IGS_GE_NOTE: This routine handles DUPLICATE IGS_PS_UNIT attempts by searching for
222 -- the 'source' IGS_PS_UNIT attempt and retrieving its grade.
223 -- Note2: If the p_finalised_ind is set then only finalised grades will
224 -- be returned.
225 DECLARE
226 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
227 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
228 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
229 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
230 v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
231 v_finalised_ind VARCHAR2(1);
232 v_sua_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
233 v_gsg_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
234 v_gsg_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
235 v_gsg_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
236 v_gsg_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
237 v_suao_trans_grading_schema_cd
238 IGS_AS_SU_STMPTOUT.translated_grading_schema_cd%TYPE;
239 v_suao_trans_version_number
240 IGS_AS_SU_STMPTOUT.translated_version_number%TYPE;
241 v_suao_trans_grade IGS_AS_SU_STMPTOUT.translated_grade%TYPE;
242 v_gsg2_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
243 CURSOR c_sua (
244 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
245 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
246 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
247 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
248 -- anilk, 22-Apr-2003, Bug# 2829262
249 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
250 ) IS
251 SELECT sut.transfer_course_cd
252 FROM IGS_PS_STDNT_UNT_TRN sut,
253 IGS_EN_SU_ATTEMPT sua
254 WHERE sut.person_id = cp_person_id AND
255 sua.person_id = sut.person_id AND
256 -- anilk, 22-Apr-2003, Bug# 2829262
257 sut.uoo_id = cp_uoo_id AND
258 sua.uoo_id = sut.uoo_id AND
259 sua.course_cd = sut.transfer_course_cd AND
260 sua.unit_attempt_status IN (cst_completed, cst_discontin)
261 ORDER BY sua.unit_attempt_status;
262 CURSOR c_suao_gsg (
263 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
264 c_v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
265 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
266 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
267 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
268 c_v_finalised_ind VARCHAR2,
269 -- anilk, 22-Apr-2003, Bug# 2829262
270 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
271 ) IS
272 SELECT gsg.grading_schema_cd,
273 gsg.version_number,
274 gsg.grade,
275 gsg.s_result_type,
276 suao.translated_grading_schema_cd,
277 suao.translated_version_number,
278 suao.translated_grade
279 FROM IGS_AS_SU_STMPTOUT suao,
280 IGS_AS_GRD_SCH_GRADE gsg
281 WHERE suao.person_id = cp_person_id AND
282 suao.course_cd = c_v_course_cd AND
283 -- anilk, 22-Apr-2003, Bug# 2829262
284 suao.uoo_id = cp_uoo_id AND
285 suao.finalised_outcome_ind
286 LIKE DECODE(c_v_finalised_ind, 'Y', 'Y', '%') AND
287 suao.grading_schema_cd = gsg.grading_schema_cd AND
288 suao.version_number = gsg.version_number AND
289 suao.grade = gsg.grade
290 ORDER BY outcome_dt DESC; -- will put the newest date first.
291 CURSOR c_gsg2 IS
292 SELECT gsg2.s_result_type
293 FROM IGS_AS_GRD_SCH_GRADE gsg2
294 WHERE gsg2.grading_schema_cd = v_suao_trans_grading_schema_cd AND
295 gsg2.version_number = v_suao_trans_version_number AND
296 gsg2.grade = v_suao_trans_grade;
297 BEGIN
298 p_grading_schema_cd := NULL;
299 p_gs_version_number := NULL;
300 p_grade := NULL;
301 -- Depending on the status of the IGS_PS_UNIT attempt, set the grade search criteria.
302 IF (p_unit_attempt_status = cst_duplicate) THEN
303 -- Locate the original IGS_PS_UNIT attempt from which the grade was sourced.
304 -- This will use IGS_PS_STDNT_UNT_TRN details created as a result of
305 -- a IGS_PS_COURSE transfer
306 OPEN c_sua(
307 p_person_id,
308 p_unit_cd,
309 p_cal_type,
310 p_ci_sequence_number,
311 -- anilk, 22-Apr-2003, Bug# 2829262
312 p_uoo_id );
313 FETCH c_sua INTO v_sua_course_cd;
314 IF (c_sua%NOTFOUND) THEN
315 CLOSE c_sua;
316 RETURN NULL;
317 ELSE
318 v_course_cd := v_sua_course_cd;
319 END IF;
320 CLOSE c_sua;
321 ELSIF (p_unit_attempt_status = cst_completed OR
322 p_unit_attempt_status = cst_discontin OR
323 (p_finalised_ind = 'N' and p_unit_attempt_status = cst_enrolled)) THEN
324 -- Use the parameter IGS_PS_COURSE code
325 v_course_cd := p_course_cd;
326 ELSE
327 -- Only COMPLETED or DUPLICATED statuses have grades, so return NULL
328 RETURN NULL;
329 END IF;
330 -- Search for the latest grade against the student IGS_PS_UNIT attempt
331 OPEN c_suao_gsg(
332 p_person_id,
333 v_course_cd,
334 p_unit_cd,
335 p_cal_type,
336 p_ci_sequence_number,
337 p_finalised_ind,
338 -- anilk, 22-Apr-2003, Bug# 2829262
339 p_uoo_id );
340 FETCH c_suao_gsg INTO v_gsg_grading_schema_cd,
341 v_gsg_version_number,
342 v_gsg_grade,
343 v_gsg_s_result_type,
344 v_suao_trans_grading_schema_cd,
345 v_suao_trans_version_number,
346 v_suao_trans_grade;
347 IF (c_suao_gsg%NOTFOUND) THEN
348 CLOSE c_suao_gsg;
349 RETURN NULL;
350 ELSE
351 -- Determine if the translated grade exists and is to be returned.
352 IF v_suao_trans_grading_schema_cd IS NULL OR
353 v_suao_trans_version_number IS NULL OR
354 v_suao_trans_grade IS NULL THEN
355 p_grading_schema_cd := v_gsg_grading_schema_cd;
356 p_gs_version_number := v_gsg_version_number;
357 p_grade := v_gsg_grade;
358 CLOSE c_suao_gsg;
359 RETURN v_gsg_s_result_type;
360 ELSE
361 OPEN c_gsg2;
362 FETCH c_gsg2 INTO v_gsg2_s_result_type;
363 IF c_gsg2%NOTFOUND THEN
364 p_grading_schema_cd := NULL;
365 p_gs_version_number := NULL;
366 p_grade := NULL;
367 CLOSE c_suao_gsg;
368 CLOSE c_gsg2;
369 RETURN NULL;
370 ELSE
371 p_grading_schema_cd := v_suao_trans_grading_schema_cd;
372 p_gs_version_number := v_suao_trans_version_number;
373 p_grade := v_suao_trans_grade;
374 CLOSE c_suao_gsg;
375 CLOSE c_gsg2;
376 RETURN v_gsg2_s_result_type;
377 END IF;
378 END IF;
379 END IF;
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF (c_sua%ISOPEN) THEN
383 CLOSE c_sua;
384 END IF;
385 IF (c_suao_gsg%ISOPEN) THEN
386 CLOSE c_suao_gsg;
387 END IF;
388 IF (c_gsg2%ISOPEN) THEN
389 CLOSE c_gsg2;
390 END IF;
391 RAISE;
392 END;
393 END assp_get_sua_grade;
394 FUNCTION assp_get_sua_gs(
395 p_person_id IN NUMBER ,
396 p_course_cd IN VARCHAR2 ,
397 p_unit_cd IN VARCHAR2 ,
398 p_version_number IN NUMBER ,
399 p_cal_type IN VARCHAR2 ,
400 p_ci_sequence_number IN NUMBER ,
401 p_location_cd IN VARCHAR2 ,
402 p_unit_class IN VARCHAR2 ,
403 p_grading_schema OUT NOCOPY VARCHAR2 ,
404 p_gs_version_number OUT NOCOPY NUMBER
405 ) RETURN boolean IS
406 gv_other_detail VARCHAR2(255);
407 BEGIN -- assp_get_sua_gs
408 -- Get the applicable grading schema for a
409 -- nominated student IGS_PS_UNIT attempt
410 -- Bug 2064285. The fix returns the default grading schema set at Unit Section level if defined or
411 -- returns the default grading schema set at Unit level. -- Kalyan Dande
412 DECLARE
413 CURSOR c_usec_gs IS
414 SELECT gs.grading_schema_code grading_schema_code,
415 gs.grd_schm_version_number grd_schm_version_number
416 FROM igs_ps_usec_grd_schm_v gs,
417 igs_ps_unit_ofr_opt uoo
418 WHERE uoo.unit_cd = p_unit_cd
419 AND uoo.version_number = p_version_number
420 AND uoo.cal_type = p_cal_type
421 AND uoo.ci_sequence_number = p_ci_sequence_number
422 AND uoo.location_cd = p_location_cd
423 AND uoo.unit_class = p_unit_class
424 AND uoo.uoo_id = gs.uoo_id
425 AND gs.default_flag = 'Y';
426 CURSOR c_unit_gs IS
427 SELECT gs.grading_schema_code grading_schema_code,
428 gs.grd_schm_version_number grd_schm_version_number
429 FROM igs_ps_unit_grd_schm_v gs
430 WHERE gs.unit_code = p_unit_cd
431 AND gs.unit_version_number = p_version_number
432 AND gs.default_flag = 'Y';
433 v_grading_schema igs_as_grd_schema.grading_schema_cd%TYPE;
434 v_gs_version_number igs_as_grd_schema.version_number%TYPE;
435 v_ret BOOLEAN DEFAULT FALSE;
436 BEGIN
437 --
438 -- This cursor used in this code was earlier referring to igs_ps_unit_ofr_opt
439 -- which is now changed to igs_ps_usec_grd_schm_v since the concept of having
440 -- multiple grading schemas was introduced by some enhancements.
441 --
442 -- This routine is built to select the grading schema from the link
443 -- to the igs_ps_usec_grd_schm_v table, however in future there will
444 -- also be links to igs_ps_ofr_pat and igs_en_stdnt_ps_att
445 --
446 OPEN c_usec_gs;
447 FETCH c_usec_gs INTO v_grading_schema, v_gs_version_number;
448 IF (c_usec_gs%FOUND) THEN
449 p_grading_schema := v_grading_schema;
450 p_gs_version_number := v_gs_version_number;
451 v_ret := TRUE;
452 ELSE
453 OPEN c_unit_gs;
454 FETCH c_unit_gs INTO v_grading_schema, v_gs_version_number;
455 IF (c_unit_gs%FOUND) THEN
456 p_grading_schema := v_grading_schema;
457 p_gs_version_number := v_gs_version_number;
458 v_ret := TRUE;
459 END IF;
460 CLOSE c_unit_gs;
461 END IF;
462 CLOSE c_usec_gs;
463 RETURN v_ret;
464 END;
465 EXCEPTION
466 WHEN OTHERS THEN
467 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
468 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_003.assp_get_sua_gs');
469 -- IGS_GE_MSG_STACK.ADD;
470 -- App_Exception.Raise_Exception;
471 END assp_get_sua_gs;
472
473 FUNCTION assp_get_sua_outcome(
474 p_person_id IN NUMBER ,
475 p_course_cd IN VARCHAR2 ,
476 p_unit_cd IN VARCHAR2 ,
477 p_cal_type IN VARCHAR2 ,
478 p_ci_sequence_number IN NUMBER ,
479 p_unit_attempt_status IN VARCHAR2 ,
480 p_finalised_ind IN VARCHAR2 ,
481 p_outcome_dt OUT NOCOPY DATE ,
482 p_grading_schema_cd OUT NOCOPY VARCHAR2 ,
483 p_gs_version_number OUT NOCOPY NUMBER ,
484 p_grade OUT NOCOPY VARCHAR2 ,
485 p_mark OUT NOCOPY NUMBER ,
486 p_origin_course_cd OUT NOCOPY VARCHAR2 ,
487 -- anilk, 22-Apr-2003, Bug# 2829262
488 p_uoo_id IN NUMBER,
489 p_use_released_ind IN VARCHAR2)
490 RETURN VARCHAR2 IS
491 gv_other_detail VARCHAR2(255);
492 BEGIN -- assp_get_sua_outcome
493 -- This is an enrolments module.
494 -- It gets the grade of a student IGS_PS_UNIT attempt within a IGS_PS_COURSE code.
495 -- This routine will determine the appropriate grade (and its matching
496 -- result type) and return them. If no grade is found NULL will be
497 -- returned (and output parameters will be NULL).
498 -- IGS_GE_NOTE: This routine handles DUPLICATE IGS_PS_UNIT attempts by searching for
499 -- the 'source' IGS_PS_UNIT attempt and retrieving its grade.
500 -- Note2: If the p_finalised_ind is set then only finalised grades will
501 -- be returned.
502 DECLARE
503 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
504 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
505 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
506 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
507 v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
508 v_finalised_ind VARCHAR2(1);
509 v_sua_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
510 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
511 v_gsg_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
512 v_gsg_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
513 v_gsg_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
514 v_gsg_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
515 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
516 v_suao_trans_grading_schema_cd
517 IGS_AS_SU_STMPTOUT.translated_grading_schema_cd%TYPE;
518 v_suao_trans_version_number
519 IGS_AS_SU_STMPTOUT.translated_version_number%TYPE;
520 v_suao_trans_grade IGS_AS_SU_STMPTOUT.translated_grade%TYPE;
521 v_gsg2_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
522 v_released_date IGS_AS_SU_STMPTOUT.release_date%TYPE;
523 l_course_cd igs_en_su_attempt_all.course_cd%TYPE;
524
525 -- smaddali modified cursor for bug#4666657
526 CURSOR c_sua (cp_course_cd igs_en_su_attempt_all.course_Cd%TYPE ) IS
527 SELECT sut.transfer_course_cd , sua.unit_attempt_status
528 FROM IGS_PS_STDNT_UNT_TRN sut,
529 IGS_EN_SU_ATTEMPT sua
530 WHERE sut.person_id = p_person_id AND
531 sua.person_id = sut.person_id AND
532 -- anilk, 22-Apr-2003, Bug# 2829262
533 sut.uoo_id = p_uoo_id AND
534 sua.uoo_id = sut.uoo_id AND
535 sua.course_cd = sut.transfer_course_cd AND
536 sut.course_cd = cp_course_cd;
537 c_sua_rec c_sua%ROWTYPE;
538
539 CURSOR c_suao_gsg (
540 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
541 cp_finalised_ind VARCHAR2) IS
542 SELECT suao.outcome_dt,
543 suao.mark,
544 gsg.grading_schema_cd,
545 gsg.version_number,
546 gsg.grade,
547 gsg.s_result_type,
548 suao.translated_grading_schema_cd,
549 suao.translated_version_number,
550 suao.translated_grade,
551 suao.release_date
552 FROM IGS_AS_SU_STMPTOUT suao,
553 IGS_AS_GRD_SCH_GRADE gsg
554 WHERE suao.person_id = p_person_id AND
555 suao.course_cd = cp_course_cd AND
556 -- anilk, 22-Apr-2003, Bug# 2829262
557 suao.uoo_id = p_uoo_id AND
558 suao.finalised_outcome_ind LIKE cp_finalised_ind AND
559 suao.grading_schema_cd = gsg.grading_schema_cd AND
560 suao.version_number = gsg.version_number AND
561 suao.grade = gsg.grade
562 ORDER BY suao.outcome_dt DESC; -- will put the newest date first.
563 CURSOR c_gsg2 IS
564 SELECT gsg2.s_result_type
565 FROM IGS_AS_GRD_SCH_GRADE gsg2
566 WHERE gsg2.grading_schema_cd = v_suao_trans_grading_schema_cd AND
567 gsg2.version_number = v_suao_trans_version_number AND
568 gsg2.grade = v_suao_trans_grade;
569 BEGIN
570 p_outcome_dt := NULL;
571 p_grading_schema_cd := NULL;
572 p_gs_version_number := NULL;
573 p_grade := NULL;
574 p_mark := NULL;
575 p_origin_course_cd := NULL;
576 -- Depending on the status of the IGS_PS_UNIT attempt, set the grade search criteria.
577 IF (p_unit_attempt_status = cst_duplicate) THEN
578 -- Locate the original IGS_PS_UNIT attempt from which the grade was sourced.
579 -- This will use IGS_PS_STDNT_UNT_TRN details created as a result of
580 -- a IGS_PS_COURSE transfer
581 -- smaddali modified logic for bug#4666657, to loop thru chain transfers
582 l_course_cd := p_course_cd;
583 LOOP
584 OPEN c_sua ( l_course_cd) ;
585 FETCH c_sua INTO c_sua_rec ;
586 IF (c_sua%NOTFOUND) THEN
587 CLOSE c_sua;
588 RETURN NULL;
589 ELSE
590 IF c_sua_rec.unit_attempt_status IN (cst_completed,cst_discontin) THEN
591 v_course_cd := c_sua_rec.transfer_course_cd;
592 EXIT;
593 ELSE
594 l_course_cd := c_sua_rec.transfer_course_cd;
595 END IF;
596 END IF;
597 CLOSE c_sua;
598 END LOOP;
599
600 ELSIF (p_unit_attempt_status = cst_completed OR
601 p_unit_attempt_status = cst_discontin OR
602 (p_finalised_ind = 'N' and p_unit_attempt_status = cst_enrolled)) THEN
603 -- Use the parameter IGS_PS_COURSE code
604 v_course_cd := p_course_cd;
605 ELSE
606 -- Only COMPLETED or DUPLICATED statuses have grades, so return NULL
607 RETURN NULL;
608 END If;
609 -- Search for the latest grade against the student IGS_PS_UNIT attempt
610 IF p_finalised_ind = 'Y' THEN
611 v_finalised_ind := 'Y';
612 ELSE
613 v_finalised_ind := '%';
614 END IF;
615 OPEN c_suao_gsg(
616 v_course_cd,
617 v_finalised_ind);
618 FETCH c_suao_gsg INTO v_outcome_dt,
619 v_mark,
620 v_gsg_grading_schema_cd,
621 v_gsg_version_number,
622 v_gsg_grade,
623 v_gsg_s_result_type,
624 v_suao_trans_grading_schema_cd,
625 v_suao_trans_version_number,
626 v_suao_trans_grade,
627 v_released_date;
628 IF (c_suao_gsg%FOUND) THEN
629 -- Determine if the translated grade exists and is to be returned.
630 IF v_suao_trans_grading_schema_cd IS NULL OR
631 v_suao_trans_version_number IS NULL OR
632 v_suao_trans_grade IS NULL THEN
633 p_outcome_dt := v_outcome_dt;
634 p_origin_course_cd := v_course_cd;
635 p_grading_schema_cd := v_gsg_grading_schema_cd;
636 p_gs_version_number := v_gsg_version_number;
637 -- IF condition added by LKAKI for bug #3842511
638 IF ((p_use_released_ind IS NULL OR p_use_released_ind = 'N') OR
639 (p_use_released_ind = 'Y' AND v_released_date <= SYSDATE)) THEN
640 p_grade := v_gsg_grade;
641 p_mark := v_mark;
642 END IF;
643
644 CLOSE c_suao_gsg;
645 RETURN v_gsg_s_result_type;
646 ELSE
647 OPEN c_gsg2;
648 FETCH c_gsg2 INTO v_gsg2_s_result_type;
649 IF c_gsg2%NOTFOUND THEN
650 p_outcome_dt := NULL;
651 p_grading_schema_cd := NULL;
652 p_gs_version_number := NULL;
653 p_grade := NULL;
654 p_mark := NULL;
655 p_origin_course_cd := NULL;
656 CLOSE c_suao_gsg;
657 CLOSE c_gsg2;
658 RETURN NULL;
659 ELSE
660 p_outcome_dt := v_outcome_dt;
661 p_grading_schema_cd := v_suao_trans_grading_schema_cd;
662 p_gs_version_number := v_suao_trans_version_number;
663 p_origin_course_cd := v_course_cd;
664 --IF condition added by LKAKI for bug #3842511
665 IF ((p_use_released_ind IS NULL OR p_use_released_ind = 'N') OR
666 (p_use_released_ind = 'Y' AND v_released_date <= SYSDATE)) THEN
667 p_grade := v_suao_trans_grade;
668 p_mark := v_mark;
669 END IF;
670 CLOSE c_suao_gsg;
671 CLOSE c_gsg2;
672 RETURN v_gsg2_s_result_type;
673 END IF;
674 END IF;
675 END IF;
676 CLOSE c_suao_gsg;
677 RETURN NULL;
678 EXCEPTION
679 WHEN OTHERS THEN
680 IF (c_sua%ISOPEN) THEN
681 CLOSE c_sua;
682 END IF;
683 IF (c_suao_gsg%ISOPEN) THEN
684 CLOSE c_suao_gsg;
685 END IF;
686 IF (c_gsg2%ISOPEN) THEN
687 CLOSE c_gsg2;
688 END IF;
689 RAISE;
690 END;
691 END assp_get_sua_outcome;
692 FUNCTION assp_get_supp_cal(
693 p_exam_cal_type IN IGS_CA_INST_ALL.cal_type%TYPE ,
694 p_exam_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
695 p_person_id IN IGS_EN_SU_ATTEMPT_ALL.person_id%TYPE ,
696 p_course_cd IN IGS_EN_SU_ATTEMPT_ALL.course_cd%TYPE ,
697 p_unit_cd IN IGS_EN_SU_ATTEMPT_ALL.unit_cd%TYPE ,
698 p_version_number IN IGS_EN_SU_ATTEMPT_ALL.version_number%TYPE ,
699 p_cal_type IN IGS_EN_SU_ATTEMPT_ALL.cal_type%TYPE ,
700 p_ci_sequence_number IN IGS_EN_SU_ATTEMPT_ALL.ci_sequence_number%TYPE ,
701 p_unit_attempt_status IN IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE ,
702 p_location_cd IN IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE ,
703 p_unit_mode IN IGS_AS_UNIT_CLASS_ALL.unit_mode%TYPE ,
704 p_unit_class IN IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE ,
705 p_ass_id IN IGS_AS_UNITASS_ITEM_ALL.ass_id%TYPE )
706 RETURN VARCHAR2 IS
707 gv_other_detail VARCHAR2(1000);
708 -- anilk, 22-Apr-2003, Bug# 2829262
709 CURSOR cur_uoo_id IS
710 SELECT uoo_id
711 FROM igs_ps_unit_ofr_opt
712 WHERE unit_cd = p_unit_cd
713 AND version_number = p_version_number
714 AND cal_type = p_cal_type
715 AND ci_sequence_number = p_ci_sequence_number
716 AND location_cd = p_location_cd
717 AND unit_class = p_unit_class;
718
719 CURSOR c_ci IS
720 SELECT uai.cal_type
721 FROM IGS_AS_UNITASS_ITEM UAI,
722 IGS_AS_ASSESSMNT_ITM AI,
723 IGS_AS_ASSESSMNT_TYP ATP,
724 IGS_CA_INST CI,
725 IGS_CA_TYPE CAT,
726 IGS_CA_STAT CS
727 WHERE uai.unit_cd = p_unit_cd AND
728 uai.version_number = p_version_number AND
729 uai.cal_type = p_cal_type AND
730 uai.ci_sequence_number = p_ci_sequence_number AND
731 uai.ass_id = p_ass_id AND
732 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
733 p_exam_cal_type,p_exam_ci_sequence_number,
734 ci.cal_type, ci.sequence_number,
735 'N') = 'Y'
736 and UAI.LOGICAL_DELETE_DT IS NULL AND
737 AI.ASS_ID = UAI.ASS_ID AND
738 AI.EXAM_SCHEDULED_IND = 'Y' AND
739 ATP.ASSESSMENT_TYPE = AI.ASSESSMENT_TYPE AND
740 ATP.EXAMINABLE_IND = 'Y' AND
741 CAT.CAL_TYPE = CI.CAL_TYPE AND
742 CAT.S_CAL_CAT = 'EXAM' AND
743 CS.CAL_STATUS = CI.CAL_STATUS AND
744 CS.S_CAL_STATUS = 'ACTIVE' AND
745 (UAI.EXAM_CAL_TYPE IS NULL OR
746 CI.CAL_TYPE = UAI.EXAM_CAL_TYPE) AND
747 (UAI.EXAM_CI_SEQUENCE_NUMBER IS NULL OR
748 CI.SEQUENCE_NUMBER = UAI.EXAM_CI_SEQUENCE_NUMBER) AND
749 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(CI.CAL_TYPE,CI.SEQUENCE_NUMBER,
750 UAI.CAL_TYPE, UAI.CI_SEQUENCE_NUMBER, 'N') = 'Y';
751 v_unit_cd IGS_CA_INST_REL.sup_cal_type%TYPE;
752 CURSOR c_uv IS
753 SELECT supp_exam_permitted_ind
754 FROM IGS_PS_UNIT_VER
755 WHERE unit_cd = p_unit_cd AND
756 version_number = p_version_number;
757 v_supp_exam_permitted_ind IGS_PS_UNIT_VER.supp_exam_permitted_ind%TYPE;
758 rec_uoo_id cur_uoo_id%ROWTYPE;
759 BEGIN
760 -- anilk, 22-Apr-2003, Bug# 2829262
761 OPEN cur_uoo_id;
762 FETCH cur_uoo_id INTO rec_uoo_id;
763 CLOSE cur_uoo_id;
764 -- Call routine to determine whether the student is eligible for a supp/special
765 -- exam.
766 IF ASSP_GET_SUA_EXAM_TP(p_person_id,
767 p_course_cd,
768 p_unit_cd,
769 p_cal_type,
770 p_ci_sequence_number,
771 p_unit_attempt_status,
772 -- anilk, 22-Apr-2003, Bug# 2829262
773 rec_uoo_id.uoo_id) NOT IN ('SUPP','SPECIAL') THEN
774 RETURN 'NA';
775 END IF;
776 -- If supps are not permitted for the IGS_PS_UNIT version then return 'N', indicating
777 -- the exam is not permitted.
778 OPEN c_uv;
779 FETCH c_uv INTO v_supp_exam_permitted_ind;
780 CLOSE c_uv;
781 IF v_supp_exam_permitted_ind = 'N' THEN
782 Return 'N';
783 END IF;
784 -- Determine if the exists a relationship between the exam calendar and the
785 -- original calendar in which the item was examined.
786 OPEN c_ci;
787 FETCH c_ci INTO v_unit_cd;
788 IF c_ci%NOTFOUND THEN
789 CLOSE c_ci;
790 RETURN 'N';
791 ELSE
792 CLOSE c_ci;
793 RETURN 'Y';
794 END IF;
795
796 END;
797 FUNCTION assp_get_trn_sua_out(
798 p_person_id IN NUMBER ,
799 p_course_cd IN VARCHAR2 ,
800 p_unit_cd IN VARCHAR2 ,
801 p_cal_type IN VARCHAR2 ,
802 p_ci_sequence_number IN NUMBER ,
803 p_unit_attempt_status IN VARCHAR2 ,
804 p_final_outcome IN VARCHAR2,
805 -- anilk, 22-Apr-2003, Bug# 2829262
806 p_uoo_id IN NUMBER )
807 RETURN VARCHAR2 IS
808 gv_other_detail VARCHAR2(255);
809 BEGIN -- assp_get_trn_sua_out
810 -- Module which is primarily used by the local function inside
811 -- assp_get_trn_sca_dtl.
812 DECLARE
813 v_ret_val VARCHAR2(10);
814 v_outcome_dt DATE;
815 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
816 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
817 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
818 v_mark IGS_AS_GRD_SCH_GRADE.lower_mark_range%TYPE;
819 v_origin_course_cd IGS_PS_VER.course_cd%TYPE;
820 BEGIN
821 v_ret_val := assp_get_sua_outcome(
822 p_person_id,
823 p_course_cd,
824 p_unit_cd,
825 p_cal_type,
826 p_ci_sequence_number,
827 p_unit_attempt_status,
828 p_final_outcome,
829 v_outcome_dt, -- output
830 v_grading_schema_cd, -- output
831 v_gs_version_number, -- output
832 v_grade, -- output
833 v_mark, -- output
834 v_origin_course_cd,
835 -- anilk, 22-Apr-2003, Bug# 2829262
836 p_uoo_id,
837 'N');
838 RETURN v_ret_val;
839 END;
840 EXCEPTION
841 WHEN OTHERS THEN
842 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
843 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_GEN_003.assp_get_trn_sua_out');
844 IGS_GE_MSG_STACK.ADD;
845 App_Exception.Raise_Exception;
846 END assp_get_trn_sua_out;
847 FUNCTION assp_get_uai_due_dt(
848 p_person_id IN NUMBER ,
849 p_course_cd IN VARCHAR2 ,
850 p_unit_cd IN VARCHAR2 ,
851 p_cal_type IN VARCHAR2 ,
852 p_ci_sequence_number IN NUMBER ,
853 p_ass_id IN NUMBER ,
854 -- anilk, 22-Apr-2003, Bug# 2829262
855 p_uoo_id IN NUMBER )
856 RETURN DATE IS
857 gv_other_detail VARCHAR2(255);
858 BEGIN -- assp_get_uai_due_dt
859 -- This function will return the due date of an assessment item.
860 -- It will use a view that will contain the assessment items that
861 -- apply to the student IGS_PS_UNIT attempt's IGS_AD_LOCATION, class and mode.
862 --
863 -- This function is modified by Nishikant - 08JAN2001 - Enh Bug#2162831.
864 -- Its modified to return the due date for the assessment item if available at
865 -- Unit section level first. If it does not find then it checks at unit offering level.
866 DECLARE
867 v_uai_due_dt IGS_AS_UNITASS_ITEM.due_dt%TYPE;
868
869 -- This cursor selects the due date of the assessment item at unit section level for
870 -- the student where logical date is null.
871 CURSOR c_sus IS
872 SELECT usai.due_dt
873 FROM igs_en_su_attempt sua,
874 igs_ps_unitass_item usai
875 WHERE sua.person_id = p_person_id AND
876 sua.course_cd = p_course_cd AND
877 -- anilk, 22-Apr-2003, Bug# 2829262
878 sua.uoo_id = p_uoo_id AND
879 usai.ass_id = p_ass_id AND
880 usai.logical_delete_dt IS NULL AND
881 sua.uoo_id = usai.uoo_id AND
882 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(usai.ass_id,
883 sua.person_id,
884 sua.course_cd) = 'TRUE';
885
886 -- This cursor selects the due date of the assessment item at unit section level for
887 -- the student where the item is logically deleted ,ie.,logical date is not null.
888 -- In this case it picks up the due date of the assessment item whose logical delete date is
889 -- maximum.
890 CURSOR c_sus_del IS
891 SELECT usai.due_dt
892 FROM igs_en_su_attempt sua,
893 igs_ps_unitass_item usai
894 WHERE sua.person_id = p_person_id AND
895 sua.course_cd = p_course_cd AND
896 -- anilk, 22-Apr-2003, Bug# 2829262
897 sua.uoo_id = p_uoo_id AND
898 usai.ass_id = p_ass_id AND
899 sua.uoo_id = usai.uoo_id AND
900 usai.logical_delete_dt = (
901 SELECT MAX(usai1.logical_delete_dt)
902 FROM igs_ps_unitass_item usai1
903 WHERE usai1.uoo_id = sua.uoo_id and
904 usai1.ass_id = usai.ass_id) AND
905 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(usai.ass_id,
906 sua.person_id,
907 sua.course_cd) = 'TRUE';
908
909 CURSOR c_suv IS
910 SELECT uai.due_dt
911 FROM IGS_EN_SU_ATTEMPT sua,
912 IGS_AS_UNITASS_ITEM uai
913 WHERE sua.person_id = p_person_id AND
914 sua.course_cd = p_course_cd AND
915 -- anilk, 22-Apr-2003, Bug# 2829262
916 sua.uoo_id = p_uoo_id AND
917 uai.ass_id = p_ass_id AND
918 uai.logical_delete_dt IS NULL AND
919 sua.unit_cd = uai.unit_cd AND
920 sua.version_number = uai.version_number AND
921 sua.cal_type = uai.cal_type AND
922 sua.ci_sequence_number = uai.ci_sequence_number AND
923 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(uai.ass_id,
924 sua.person_id,
925 sua.course_cd) = 'TRUE';
926
927 CURSOR c_suv_del IS
928 SELECT uai.due_dt
929 FROM IGS_EN_SU_ATTEMPT sua,
930 IGS_AS_UNITASS_ITEM uai
931 WHERE sua.person_id = p_person_id AND
932 sua.course_cd = p_course_cd AND
933 -- anilk, 22-Apr-2003, Bug# 2829262
934 sua.uoo_id = p_uoo_id AND
935 uai.ass_id = p_ass_id AND
936 uai.logical_delete_dt = (
937 SELECT MAX(uai1.logical_delete_dt)
938 FROM IGS_EN_SU_ATTEMPT sua1,
939 IGS_AS_UNITASS_ITEM uai1
940 WHERE sua1.person_id = sua.person_id AND
941 sua1.course_cd = sua.course_cd AND
942 -- anilk, 22-Apr-2003, Bug# 2829262
943 sua1.uoo_id = sua.uoo_id AND
944 uai1.ass_id = uai.ass_id AND
945 sua.unit_cd = uai.unit_cd AND
946 sua.version_number = uai.version_number AND
947 sua.cal_type = uai.cal_type AND
948 sua.ci_sequence_number = uai.ci_sequence_number AND
949 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(uai.ass_id,
950 sua.person_id,
951 sua.course_cd) = 'TRUE'
952 );
953 BEGIN
954 -- Here it returns the due date of the assessment item for the student which is not
955 -- logically deleted.
956 OPEN c_sus;
957 FETCH c_sus INTO v_uai_due_dt;
958 IF c_sus%FOUND THEN
959 CLOSE c_sus;
960 RETURN v_uai_due_dt;
961 END IF;
962 CLOSE c_sus;
963
964 -- Here it returns the due date of the assessment item for the student which is logically
965 -- deleted but the most recently deleted one.
966 OPEN c_sus_del;
967 FETCH c_sus_del INTO v_uai_due_dt;
968 IF c_sus_del%FOUND THEN
969 CLOSE c_sus_del;
970 RETURN v_uai_due_dt;
971 END IF;
972 CLOSE c_sus_del;
973
974 OPEN c_suv ;
975 FETCH c_suv INTO v_uai_due_dt;
976 IF c_suv%FOUND THEN
977 CLOSE c_suv;
978 RETURN v_uai_due_dt;
979 END IF;
980 CLOSE c_suv;
981
982 OPEN c_suv_del ;
983 FETCH c_suv_del INTO v_uai_due_dt;
984 IF c_suv_del%FOUND THEN
985 CLOSE c_suv_del;
986 RETURN v_uai_due_dt;
987 END IF;
988 CLOSE c_suv_del;
989
990 RETURN NULL;
991 END;
992 END assp_get_uai_due_dt;
993
994 FUNCTION assp_get_uai_ref(
995 p_person_id IN NUMBER ,
996 p_course_cd IN VARCHAR2 ,
997 p_unit_cd IN VARCHAR2 ,
998 p_cal_type IN VARCHAR2 ,
999 p_ci_sequence_number IN NUMBER ,
1000 p_ass_id IN NUMBER ,
1001 -- anilk, 22-Apr-2003, Bug# 2829262
1002 p_uoo_id IN NUMBER )
1003 RETURN VARCHAR2 IS
1004 gv_other_detail VARCHAR2(255);
1005 BEGIN
1006 -- assp_get_uai_ref
1007 -- This function will return the reference of an assessment item.
1008 -- It will use a view that will contain the assessment items that
1009 -- apply to the student IGS_PS_UNIT attempt's IGS_AD_LOCATION, class and mode.
1010 --
1011 -- This function is modified by Nishikant - 08JAN2001 - Enh Bug#2162831.
1012 -- Its modified to return the reference of the assessment item if available at
1013 -- Unit section level first. If it does not find then it checks at unit offering level.
1014 DECLARE
1015 v_uai_reference IGS_AS_UNITASS_ITEM.reference%TYPE;
1016
1017 -- This cursor selects the reference of the assessment item at unit section level for
1018 -- the student where logical date is null.
1019 CURSOR c_sus IS
1020 SELECT usai.reference
1021 FROM igs_en_su_attempt sua,
1022 igs_ps_unitass_item usai
1023 WHERE sua.person_id = p_person_id AND
1024 sua.course_cd = p_course_cd AND
1025 -- anilk, 22-Apr-2003, Bug# 2829262
1026 sua.uoo_id = p_uoo_id AND
1027 usai.ass_id = p_ass_id AND
1028 usai.logical_delete_dt IS NULL AND
1029 sua.uoo_id = usai.uoo_id AND
1030 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(usai.ass_id,
1031 sua.person_id,
1032 sua.course_cd) = 'TRUE';
1033
1034 -- This cursor selects the reference of the assessment item at unit section level for
1035 -- the student where the item is logically deleted ,ie.,logical date is not null.
1036 -- In this case it picks up the reference of the assessment item whose logical delete date is
1037 -- maximum.
1038 CURSOR c_sus_del IS
1039 SELECT usai.reference
1040 FROM igs_en_su_attempt sua,
1041 igs_ps_unitass_item usai
1042 WHERE sua.person_id = p_person_id AND
1043 sua.course_cd = p_course_cd AND
1044 -- anilk, 22-Apr-2003, Bug# 2829262
1045 sua.uoo_id = p_uoo_id AND
1046 usai.ass_id = p_ass_id AND
1047 sua.uoo_id = usai.uoo_id AND
1048 usai.logical_delete_dt = (
1049 SELECT MAX(usai1.logical_delete_dt)
1050 FROM igs_ps_unitass_item usai1
1051 WHERE usai1.uoo_id = sua.uoo_id and
1052 usai1.ass_id = usai.ass_id) AND
1053 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(usai.ass_id,
1054 sua.person_id,
1055 sua.course_cd) = 'TRUE';
1056
1057 CURSOR c_suv IS
1058 SELECT uai.reference
1059 FROM IGS_EN_SU_ATTEMPT sua,
1060 IGS_AS_UNITASS_ITEM uai
1061 WHERE sua.person_id = p_person_id AND
1062 sua.course_cd = p_course_cd AND
1063 -- anilk, 22-Apr-2003, Bug# 2829262
1064 sua.uoo_id = p_uoo_id AND
1065 uai.ass_id = p_ass_id AND
1066 uai.logical_delete_dt IS NULL AND
1067 sua.unit_cd = uai.unit_cd AND
1068 sua.version_number = uai.version_number AND
1069 sua.cal_type = uai.cal_type AND
1070 sua.ci_sequence_number = uai.ci_sequence_number AND
1071 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(uai.ass_id,
1072 sua.person_id,
1073 sua.course_cd) = 'TRUE';
1074
1075
1076 CURSOR c_suv_del IS
1077 SELECT uai.reference
1078 FROM IGS_EN_SU_ATTEMPT sua,
1079 IGS_AS_UNITASS_ITEM uai
1080 WHERE sua.person_id = p_person_id AND
1081 sua.course_cd = p_course_cd AND
1082 -- anilk, 22-Apr-2003, Bug# 2829262
1083 sua.uoo_id = p_uoo_id AND
1084 uai.ass_id = p_ass_id AND
1085 uai.logical_delete_dt = (
1086 SELECT MAX(uai1.logical_delete_dt)
1087 FROM IGS_EN_SU_ATTEMPT sua1,
1088 IGS_AS_UNITASS_ITEM uai1
1089 WHERE sua1.person_id = sua.person_id AND
1090 sua1.course_cd = sua.course_cd AND
1091 -- anilk, 22-Apr-2003, Bug# 2829262
1092 sua1.uoo_id = sua.uoo_id AND
1093 uai1.ass_id = uai.ass_id AND
1094 sua.unit_cd = uai.unit_cd AND
1095 sua.version_number = uai.version_number AND
1096 sua.cal_type = uai.cal_type AND
1097 sua.ci_sequence_number = uai.ci_sequence_number AND
1098 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(uai.ass_id,
1099 sua.person_id,
1100 sua.course_cd) = 'TRUE'
1101 );
1102 BEGIN
1103 -- Here it returns the reference of the assessment item for the student which is not
1104 -- logically deleted.
1105 OPEN c_sus;
1106 FETCH c_sus INTO v_uai_reference;
1107 IF c_sus%FOUND THEN
1108 CLOSE c_sus;
1109 RETURN v_uai_reference;
1110 END IF;
1111 CLOSE c_sus;
1112
1113 -- Here it returns the reference of the assessment item for the student which is logically
1114 -- deleted but the most recently deleted one.
1115 OPEN c_sus_del;
1116 FETCH c_sus_del INTO v_uai_reference;
1117 IF c_sus_del%FOUND THEN
1118 CLOSE c_sus_del;
1119 RETURN v_uai_reference;
1120 END IF;
1121 CLOSE c_sus_del;
1122
1123 OPEN c_suv ;
1124 FETCH c_suv INTO v_uai_reference;
1125 IF c_suv%FOUND THEN
1126 CLOSE c_suv;
1127 RETURN v_uai_reference;
1128 END IF;
1129 CLOSE c_suv;
1130 OPEN c_suv_del ;
1131 FETCH c_suv_del INTO v_uai_reference;
1132 IF c_suv_del%FOUND THEN
1133 CLOSE c_suv_del;
1134 RETURN v_uai_reference;
1135 END IF;
1136 CLOSE c_suv_del;
1137 RETURN NULL;
1138
1139 END;
1140 END assp_get_uai_ref;
1141 FUNCTION assp_get_spcl_needs(
1142 p_person_id IN NUMBER )
1143 RETURN VARCHAR2 IS
1144 gv_other_detail VARCHAR2(255);
1145 v_exists VARCHAR2(1);
1146 BEGIN -- ASSP_GET_SPCL_NEEDS
1147 -- Purpose: Get whether IGS_PE_PERSON is within the special needs group.
1148 -- The type of IGS_PE_PERSON ID group is currently passed in , although this
1149 -- may be replaced by a system table in the future.
1150 DECLARE
1151 CURSOR c_pig_pigm IS
1152 SELECT 'x'
1153 FROM IGS_PE_PERSID_GROUP pig,
1154 IGS_PE_PRSID_GRP_MEM pigm
1155 WHERE pig.group_cd = 'SPCL-NEEDS' AND
1156 pig.closed_ind = 'N' AND
1157 pig.group_id = pigm.group_id AND
1158 pigm.person_id = p_person_id;
1159 BEGIN
1160 -- Cursor handling
1161 OPEN c_pig_pigm;
1162 FETCH c_pig_pigm INTO v_exists;
1163 IF c_pig_pigm%FOUND THEN
1164 CLOSE c_pig_pigm;
1165 RETURN 'Y';
1166 ELSE
1167 CLOSE c_pig_pigm;
1168 RETURN 'N';
1169 END IF;
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 IF c_pig_pigm%ISOPEN THEN
1173 CLOSE c_pig_pigm;
1174 END IF;
1175 RAISE;
1176 END;
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 NULL;
1180 END ASSP_GET_SPCL_NEEDS;
1181
1182 PROCEDURE get_default_grds (
1183 x_unit_cd IN VARCHAR2,
1184 x_version_number IN NUMBER,
1185 x_assessment_type IN VARCHAR2,
1186 x_grading_schema_cd OUT NOCOPY VARCHAR2,
1187 x_gs_version_number OUT NOCOPY NUMBER,
1188 x_description OUT NOCOPY VARCHAR2,
1189 x_approved OUT NOCOPY VARCHAR2
1190 ) AS
1191 /*
1192 || Created By : [email protected]
1193 || Created On : 31-Dec-2001
1194 || Purpose : To get the default Grading Schema for the given
1195 || Unit Code, Version Number and Assessment Type.
1196 || Known limitations, enhancements or remarks :
1197 || Change History :
1198 || Who When What
1199 || (reverse chronological order - newest change first)
1200 */
1201 CURSOR cur_get_def (p_unit_cd VARCHAR2,
1202 p_version_number NUMBER,
1203 p_assessment_type VARCHAR2)IS
1204 SELECT grading_schema_cd,
1205 gs_version_number
1206 FROM igs_as_appr_grd_sch
1207 WHERE unit_cd = p_unit_cd AND
1208 version_number = p_version_number AND
1209 assessment_type = p_assessment_type AND
1210 default_ind = 'Y' AND
1211 closed_ind = 'N' ;
1212 l_cur_get_def cur_get_def%ROWTYPE;
1213
1214 CURSOR cur_get_appr (p_unit_cd VARCHAR2,
1215 p_version_number NUMBER,
1216 p_assessment_type VARCHAR2)IS
1217 SELECT 'X'
1218 FROM igs_as_appr_grd_sch
1219 WHERE unit_cd = p_unit_cd AND
1220 version_number = p_version_number AND
1221 assessment_type = p_assessment_type AND
1222 closed_ind = 'N';
1223 l_cur_get_appr cur_get_appr%ROWTYPE;
1224
1225 CURSOR cur_desc (p_grading_schema_cd VARCHAR2,
1226 p_gs_version_number NUMBER )IS
1227 SELECT description
1228 FROM igs_as_grd_schema
1229 WHERE grading_schema_cd = p_grading_schema_cd AND
1230 version_number = p_gs_version_number;
1231 l_cur_desc cur_desc%ROWTYPE;
1232
1233
1234 BEGIN
1235 OPEN cur_get_def(x_unit_cd, x_version_number, x_assessment_type);
1236 FETCH cur_get_def INTO l_cur_get_def;
1237 IF cur_get_def%FOUND THEN
1238 x_grading_schema_cd := l_cur_get_def.grading_schema_cd;
1239 x_gs_version_number := l_cur_get_def.gs_version_number;
1240 OPEN cur_desc(x_grading_schema_cd, x_gs_version_number);
1241 FETCH cur_desc INTO l_cur_desc;
1242 IF cur_desc%FOUND THEN
1243 x_description := l_cur_desc.description;
1244 END IF;
1245 CLOSE cur_desc;
1246 END IF;
1247 CLOSE cur_get_def;
1248
1249 OPEN cur_get_appr(x_unit_cd, x_version_number, x_assessment_type);
1250 FETCH cur_get_appr INTO l_cur_get_appr;
1251 x_approved := 'N';
1252 IF cur_get_appr%FOUND THEN
1253 x_approved := 'Y';
1254 END IF;
1255 CLOSE cur_get_appr;
1256
1257 END get_default_grds;
1258
1259 PROCEDURE assp_get_suaai_gs(
1260 p_person_id IN NUMBER,
1261 p_course_cd IN VARCHAR2,
1262 p_unit_cd IN VARCHAR2,
1263 p_cal_type IN VARCHAR2,
1264 p_ci_sequence_number IN NUMBER,
1265 p_ass_id IN VARCHAR2,
1266 p_grading_schema_cd OUT NOCOPY VARCHAR2,
1267 p_gs_version_number OUT NOCOPY NUMBER,
1268 -- anilk, 22-Apr-2003, Bug# 2829262
1269 p_uoo_id IN NUMBER ) IS
1270 /*
1271 || Created By : Nishikant
1272 || Created On : 25jan2002
1273 || Purpose : To get the Grading Schema and Version Number
1274 || for an Assessment Item available at unit
1275 || section level or unit offering level
1276 || Known limitations, enhancements or remarks :
1277 || Change History :
1278 || Who When What
1279 || (reverse chronological order - newest change first)
1280 */
1281
1282 CURSOR c_uoo_id IS
1283 SELECT uoo_id, version_number
1284 FROM igs_en_su_attempt
1285 WHERE person_id = p_person_id AND
1286 course_cd = p_course_cd AND
1287 -- anilk, 22-Apr-2003, Bug# 2829262
1288 uoo_id = p_uoo_id;
1289 CURSOR c_us_grad_ver( l_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
1290 SELECT grading_schema_cd, gs_version_number
1291 FROM igs_ps_unitass_item
1292 WHERE uoo_id = l_uoo_id AND
1293 ass_id = p_ass_id AND
1294 logical_delete_dt IS NULL;
1295 CURSOR c_u_grad_ver ( l_version_number igs_en_su_attempt.version_number%TYPE) IS
1296 SELECT grading_schema_cd, gs_version_number
1297 FROM igs_as_unitass_item
1298 WHERE unit_cd = p_unit_cd AND
1299 version_number = l_version_number AND
1300 cal_type = p_cal_type AND
1301 ci_sequence_number = p_ci_sequence_number AND
1302 ass_id = p_ass_id AND
1303 logical_delete_dt IS NULL;
1304 l_c_uoo_id igs_en_su_attempt.uoo_id%TYPE;
1305 l_c_version_number igs_en_su_attempt.version_number%TYPE;
1306
1307 BEGIN
1308
1309 OPEN c_uoo_id;
1310 FETCH c_uoo_id INTO l_c_uoo_id, l_c_version_number;
1311 CLOSE c_uoo_id;
1312 -- Here it checks whether grading schema code and version is avilable
1313 -- for the Assessment item at Unit Offering level.
1314 OPEN c_us_grad_ver(l_c_uoo_id);
1315 FETCH c_us_grad_ver INTO p_grading_schema_cd,p_gs_version_number;
1316 IF c_us_grad_ver%FOUND THEN
1317 CLOSE c_us_grad_ver;
1318 RETURN;
1319 ELSE
1320 -- Here it checks whether grading schema code and version is avilable
1321 -- for the Assessment item at Unit Offering level.
1322 OPEN c_u_grad_ver(l_c_version_number);
1323 FETCH c_u_grad_ver INTO p_grading_schema_cd,p_gs_version_number;
1324 IF c_u_grad_ver%FOUND THEN
1325 CLOSE c_u_grad_ver;
1326 RETURN;
1327 ELSE
1328 CLOSE c_us_grad_ver;
1329 CLOSE c_u_grad_ver;
1330 RETURN;
1331 END IF;
1332 END IF;
1333 END assp_get_suaai_gs;
1334
1335 FUNCTION getStdntCareerPrograms(
1336 P_PERSON_ID IN IGS_EN_STDNT_PS_ATT.person_id%TYPE ,
1337 P_PROGRAM_TYPE IN IGS_PS_VER_ALL.course_type%TYPE ) RETURN VARCHAR2 IS
1338
1339 CURSOR c_stud_careers IS
1340 SELECT a.Course_cd, b.Title
1341 FROM IGS_EN_STDNT_PS_ATT_ALL a, IGS_PS_VER_ALL b
1342 WHERE a.course_cd = b.course_cd AND
1343 a.version_number = b.version_number AND
1344 a.course_attempt_status <> 'UNCONFIRM' AND
1345 b.COURSE_TYPE = P_program_type AND
1346 a.person_id = P_PERSON_ID /* AND
1347 trunc(a.CREATION_DATE) <= (
1348 SELECT min(trunc(innerpsatt.CREATION_DATE))
1349 FROM IGS_EN_STDNT_PS_ATT_ALL innerpsatt, IGS_PS_VER_ALL innerpsver
1350 WHERE innerpsatt.course_cd = innerpsver.course_cd AND
1351 innerpsatt.version_number = innerpsver.version_number AND
1352 innerpsatt.person_id = P_PERSON_ID AND
1353 innerpsatt.course_attempt_status <> 'UNCONFIRM' AND
1354 innerpsver.COURSE_TYPE = P_program_type
1355 ) */
1356 ORDER BY COURSE_TYPE,primary_program_type ;
1357 v_stud_careers c_stud_careers%ROWTYPE;
1358 v_programs VARCHAR2(3000) ;
1359 v_seperator VARCHAR2(3);
1360 BEGIN
1361 FOR v_stud_careers IN c_stud_careers
1362 LOOP
1363 IF v_seperator IS NULL THEN
1364 v_seperator:=' ';
1365 ELSE
1366 v_seperator:=', ';
1367 END IF;
1368 v_programs := v_programs || v_seperator || trim(v_stud_careers.title) ;
1369 end loop;
1370 return(v_programs);
1371 END getStdntCareerPrograms;
1372
1373
1374 FUNCTION getStdntCareerProgsBetween(
1375 P_PERSON_ID IN igs_en_stdnt_ps_att.person_id%TYPE ,
1376 P_COURSE_CD IN igs_en_stdnt_ps_att.course_cd%TYPE ,
1377 P_TERM_START_DATE IN DATE ,
1378 P_TERM_END_DATE IN DATE ) RETURN VARCHAR2 IS
1379
1380 CURSOR c_stud_careers IS
1381 SELECT a.Course_cd, b.Title
1382 FROM IGS_EN_STDNT_PS_ATT_ALL a, IGS_PS_VER_ALL b
1383 WHERE a.course_cd = b.course_cd AND
1384 a.version_number = b.version_number AND
1385 a.course_attempt_status <> 'UNCONFIRM' AND
1386 a.COMMENCEMENT_DT <= p_term_end_date AND
1387 nvl(a.DISCONTINUED_DT , SYSDATE + 100000 ) >= p_term_start_date AND
1388 b.COURSE_TYPE = (SELECT b.course_type
1389 FROM IGS_EN_STDNT_PS_ATT_ALL innerpsatt, IGS_PS_VER_ALL innerpsver
1390 WHERE innerpsatt.course_cd = innerpsver.course_cd AND
1391 innerpsatt.version_number = innerpsver.version_number AND
1392 innerpsatt.person_id = P_PERSON_ID AND
1393 innerpsatt.course_cd = P_COURSE_CD) AND
1394 a.person_id = P_PERSON_ID
1395 ORDER BY primary_program_type ;
1396
1397 v_stud_careers c_stud_careers%rowtype;
1398 v_programs varchar2(3000) ;
1399 v_seperator varchar2(1);
1400 BEGIN
1401 FOR v_stud_careers IN c_stud_careers
1402 LOOP
1403 IF v_seperator IS NULL THEN
1404 v_seperator:=' ';
1405 ELSE
1406 v_seperator:=', ';
1407 END IF;
1408 v_programs := v_programs || v_seperator || v_stud_careers.title ;
1409 END LOOP;
1410 RETURN(v_programs);
1411 END getStdntCareerProgsBetween;
1412
1413 FUNCTION getStdntPrograms(
1414 P_PERSON_ID IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE ,
1415 P_PROGRAM_CD IGS_PS_VER_ALL.COURSE_CD%TYPE ) RETURN VARCHAR2 IS
1416
1417 CURSOR c_stud_programs IS
1418 SELECT a.person_id ,a.Course_cd, c.Title
1419 FROM IGS_PS_STDNT_TRN a, IGS_EN_STDNT_PS_ATT_ALL b , IGS_PS_VER c
1420 WHERE a.COURSE_CD = b.course_cd AND
1421 a.person_id = b.person_id AND
1422 b.course_cd = c.course_cd AND
1423 b.version_number = c.version_number AND
1424 a.TRANSFER_COURSE_CD = P_program_cd AND
1425 a.person_id = P_PERSON_ID;
1426
1427 v_stud_programs c_stud_programs%rowtype;
1428 v_programs varchar2(3000) ;
1429 BEGIN
1430 FOR v_stud_programs IN c_stud_programs
1431 LOOP
1432 v_programs := v_stud_programs.title ;
1433 END LOOP;
1434 return(v_programs);
1435 END getStdntPrograms;
1436
1437
1438
1439 Function getStdntProgsBetween(
1440 P_PERSON_ID igs_en_stdnt_ps_att.person_id%type ,
1441 P_program_cd igs_ps_ver_all.course_type%type ,
1442 p_term_start_date DATE ,
1443 p_term_end_date DATE ) return VARCHAR2 is
1444
1445 CURSOR c_stud_programs IS
1446 SELECT a.course_cd, b.title
1447 FROM igs_ps_stdnt_trn a, igs_ps_ver_all b , IGS_EN_STDNT_PS_ATT_ALL c
1448 WHERE a.TRANSFER_COURSE_CD = c.course_cd
1449 AND a.person_id = c.person_id
1450 AND c.course_cd = b.course_cd
1451 AND c.version_number = b.version_number
1452 AND a.transfer_dt < p_term_end_date
1453 AND a.transfer_dt > p_term_start_date
1454 AND a.course_cd = p_program_cd
1455 AND a.person_id = p_person_id;
1456
1457 v_stud_programs c_stud_programs%rowtype;
1458 v_programs varchar2(3000) ;
1459 v_seperator varchar2(1);
1460 begin
1461 for v_stud_programs in c_stud_programs
1462 loop
1463 IF v_seperator IS NULL THEN
1464 v_seperator:=' ';
1465 ELSE
1466 v_seperator:=',';
1467 END IF;
1468 v_programs := v_programs || v_seperator || v_stud_programs.title ;
1469 end loop;
1470 return(v_programs);
1471 end getStdntProgsBetween;
1472
1473
1474 /******************************************************
1475 * Procedure to be created
1476 * For selecting the current valid term
1477 * Jitendra Handa
1478 * Term Based Location display for VAH
1479 ******************************************************/
1480
1481 PROCEDURE get_current_term (
1482 p_person_id IN NUMBER,
1483 p_course_cd IN VARCHAR2,
1484 p_cal_type OUT NOCOPY VARCHAR2,
1485 p_seq_num OUT NOCOPY NUMBER
1486 )
1487 AS
1488 CURSOR c_terms
1489 IS
1490 SELECT ci.cal_type, ci.sequence_number, ci.start_dt, ci.end_dt,
1491 spa.person_id, spa.course_cd
1492 FROM igs_en_stdnt_ps_att_all spa, igs_ca_inst ci,
1493 igs_ps_ver_all pv
1494 WHERE spa.course_attempt_status <> 'UNCONFIRM'
1495 AND pv.course_cd = spa.course_cd
1496 AND ci.start_dt <= SYSDATE
1497 AND spa.person_id = p_person_id
1498 AND spa.course_cd = p_course_cd
1499 AND ( EXISTS (
1500 SELECT 1
1501 FROM igs_en_su_attempt_all sua,
1502 igs_ca_teach_to_load_v ttl
1503 WHERE sua.person_id = spa.person_id
1504 AND sua.course_cd = spa.course_cd
1505 AND sua.cal_type = ttl.teach_cal_type
1506 AND sua.ci_sequence_number =
1507 ttl.teach_ci_sequence_number
1508 AND ttl.load_cal_type = ci.cal_type
1509 AND ttl.load_ci_sequence_number = ci.sequence_number
1510 AND sua.unit_attempt_status IN
1511 ('ENROLLED',
1512 'COMPLETED',
1513 'DISCONTIN',
1514 'DUPLICATE'
1515 ))
1516 OR EXISTS (
1517 SELECT 1
1518 FROM igs_av_stnd_unit asu
1519 WHERE spa.person_id = asu.person_id
1520 AND spa.course_cd = asu.as_course_cd
1521 AND ci.cal_type = asu.cal_type
1522 AND ci.sequence_number = asu.ci_sequence_number
1523 AND asu.s_adv_stnd_granting_status = 'GRANTED')
1524 OR EXISTS (
1525 SELECT 1
1526 FROM igs_av_stnd_unit_lvl asul
1527 WHERE spa.person_id = asul.person_id
1528 AND spa.course_cd = asul.as_course_cd
1529 AND ci.cal_type = asul.cal_type
1530 AND ci.sequence_number = asul.ci_sequence_number
1531 AND asul.s_adv_stnd_granting_status = 'GRANTED')
1532 )
1533 ORDER BY ci.start_dt DESC;
1534
1535 v_terms c_terms%ROWTYPE;
1536 BEGIN
1537 OPEN c_terms;
1538 FETCH c_terms INTO v_terms;
1539 p_cal_type := v_terms.cal_type;
1540 p_seq_num := v_terms.sequence_number;
1541 CLOSE c_terms;
1542 END get_current_term;
1543
1544 FUNCTION get_spat_att_type_desc (
1545 p_person_id IN NUMBER,
1546 p_program_cd IN VARCHAR2
1547 ) RETURN VARCHAR2 AS
1548
1549 v_term_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
1550 v_term_sequence_NUMBER IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
1551 BEGIN
1552 get_current_term(p_person_id,
1553 p_program_cd,
1554 v_term_cal_type,
1555 v_term_sequence_NUMBER);
1556
1557 return igs_en_spa_terms_api.get_spat_att_type_desc(p_person_id,p_program_cd,v_term_cal_type,v_term_sequence_NUMBER);
1558 END get_spat_att_type_desc;
1559
1560 FUNCTION get_spat_att_mode_desc(
1561 p_person_id IN NUMBER,
1562 p_program_cd IN VARCHAR2
1563 ) RETURN VARCHAR2 AS
1564
1565 v_term_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
1566 v_term_sequence_NUMBER IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
1567 BEGIN
1568
1569 get_current_term(p_person_id,
1570 p_program_cd,
1571 v_term_cal_type,
1572 v_term_sequence_NUMBER);
1573
1574 return igs_en_spa_terms_api.get_spat_att_mode_desc(p_person_id,p_program_cd,v_term_cal_type,v_term_sequence_NUMBER);
1575 END get_spat_att_mode_desc;
1576
1577
1578 FUNCTION get_spat_location_desc(
1579 p_person_id IN NUMBER,
1580 p_program_cd IN VARCHAR2
1581 ) RETURN VARCHAR2 AS
1582 v_term_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
1583 v_term_sequence_NUMBER IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
1584 BEGIN
1585
1586 get_current_term(p_person_id,
1587 p_program_cd,
1588 v_term_cal_type,
1589 v_term_sequence_NUMBER);
1590
1591 return igs_en_spa_terms_api.get_spat_location_desc(p_person_id,p_program_cd,v_term_cal_type,v_term_sequence_NUMBER);
1592 END get_spat_location_desc;
1593 FUNCTION assp_get_sua_rel_grade(
1594 p_person_id IN NUMBER ,
1595 p_course_cd IN VARCHAR2 ,
1596 p_unit_cd IN VARCHAR2 ,
1597 p_cal_type IN VARCHAR2 ,
1598 p_ci_sequence_number IN NUMBER ,
1599 p_unit_attempt_status IN VARCHAR2 ,
1600 p_finalised_ind IN VARCHAR2 ,
1601 p_grading_schema_cd OUT NOCOPY VARCHAR2 ,
1602 p_gs_version_number OUT NOCOPY NUMBER ,
1603 p_grade OUT NOCOPY VARCHAR2 ,
1604 p_uoo_id IN NUMBER )
1605 RETURN VARCHAR2 IS
1606 gv_other_detail VARCHAR2(255);
1607 BEGIN -- assp_get_sua_grade
1608 -- This is an enrolments module.
1609 -- It gets the grade of a student IGS_PS_UNIT attempt within a IGS_PS_COURSE code.
1610 -- This routine will determine the appropriate grade (and its matching
1611 -- result type) and return them. If no grade is found NULL will be
1612 -- returned (and output parameters will be NULL).
1613 -- IGS_GE_NOTE: This routine handles DUPLICATE IGS_PS_UNIT attempts by searching for
1614 -- the 'source' IGS_PS_UNIT attempt and retrieving its grade.
1615 -- Note2: If the p_finalised_ind is set then only finalised grades will
1616 -- be returned.
1617 DECLARE
1618 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1619 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1620 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
1621 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1622 v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
1623 v_finalised_ind VARCHAR2(1);
1624 v_sua_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
1625 v_gsg_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1626 v_gsg_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1627 v_gsg_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1628 v_gsg_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
1629 v_suao_trans_grading_schema_cd
1630 IGS_AS_SU_STMPTOUT.translated_grading_schema_cd%TYPE;
1631 v_suao_trans_version_number
1632 IGS_AS_SU_STMPTOUT.translated_version_number%TYPE;
1633 v_suao_trans_grade IGS_AS_SU_STMPTOUT.translated_grade%TYPE;
1634 v_gsg2_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
1635 CURSOR c_sua (
1636 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1637 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1638 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1639 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1640 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
1641 ) IS
1642 SELECT sut.transfer_course_cd
1643 FROM IGS_PS_STDNT_UNT_TRN sut,
1644 IGS_EN_SU_ATTEMPT sua
1645 WHERE sut.person_id = cp_person_id AND
1646 sua.person_id = sut.person_id AND
1647 sut.uoo_id = cp_uoo_id AND
1648 sua.uoo_id = sut.uoo_id AND
1649 sua.course_cd = sut.transfer_course_cd AND
1650 sua.unit_attempt_status IN (cst_completed, cst_discontin)
1651 ORDER BY sua.unit_attempt_status;
1652 CURSOR c_suao_gsg (
1653 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1654 c_v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1655 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1656 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1657 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1658 c_v_finalised_ind VARCHAR2,
1659 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
1660 ) IS
1661 SELECT gsg.grading_schema_cd,
1662 gsg.version_number,
1663 gsg.grade,
1664 gsg.s_result_type,
1665 suao.translated_grading_schema_cd,
1666 suao.translated_version_number,
1667 suao.translated_grade
1668 FROM IGS_AS_SU_STMPTOUT suao,
1669 IGS_AS_GRD_SCH_GRADE gsg
1670 WHERE suao.person_id = cp_person_id AND
1671 suao.course_cd = c_v_course_cd AND
1672 suao.uoo_id = cp_uoo_id AND
1673 suao.finalised_outcome_ind
1674 LIKE DECODE(c_v_finalised_ind, 'Y', 'Y', '%') AND
1675 suao.grading_schema_cd = gsg.grading_schema_cd AND
1676 suao.version_number = gsg.version_number AND
1677 suao.grade = gsg.grade AND
1678 nvl(suao.RELEASE_DATE ,sysdate+10) <=sysdate
1679 ORDER BY outcome_dt DESC; -- will put the newest date first.
1680 CURSOR c_gsg2 IS
1681 SELECT gsg2.s_result_type
1682 FROM IGS_AS_GRD_SCH_GRADE gsg2
1683 WHERE gsg2.grading_schema_cd = v_suao_trans_grading_schema_cd AND
1684 gsg2.version_number = v_suao_trans_version_number AND
1685 gsg2.grade = v_suao_trans_grade;
1686 BEGIN
1687 p_grading_schema_cd := NULL;
1688 p_gs_version_number := NULL;
1689 p_grade := NULL;
1690 -- Depending on the status of the IGS_PS_UNIT attempt, set the grade search criteria.
1691 IF (p_unit_attempt_status = cst_duplicate) THEN
1692 -- Locate the original IGS_PS_UNIT attempt from which the grade was sourced.
1693 -- This will use IGS_PS_STDNT_UNT_TRN details created as a result of
1694 -- a IGS_PS_COURSE transfer
1695 OPEN c_sua(
1696 p_person_id,
1697 p_unit_cd,
1698 p_cal_type,
1699 p_ci_sequence_number,
1700 -- anilk, 22-Apr-2003, Bug# 2829262
1701 p_uoo_id );
1702 FETCH c_sua INTO v_sua_course_cd;
1703 IF (c_sua%NOTFOUND) THEN
1704 CLOSE c_sua;
1705 RETURN NULL;
1706 ELSE
1707 v_course_cd := v_sua_course_cd;
1708 END IF;
1709 CLOSE c_sua;
1710 ELSIF (p_unit_attempt_status = cst_completed OR
1711 p_unit_attempt_status = cst_discontin OR
1712 (p_finalised_ind = 'N' and p_unit_attempt_status = cst_enrolled)) THEN
1713 -- Use the parameter IGS_PS_COURSE code
1714 v_course_cd := p_course_cd;
1715 ELSE
1716 -- Only COMPLETED or DUPLICATED statuses have grades, so return NULL
1717 RETURN NULL;
1718 END IF;
1719 -- Search for the latest grade against the student IGS_PS_UNIT attempt
1720 OPEN c_suao_gsg(
1721 p_person_id,
1722 v_course_cd,
1723 p_unit_cd,
1724 p_cal_type,
1725 p_ci_sequence_number,
1726 p_finalised_ind,
1727 -- anilk, 22-Apr-2003, Bug# 2829262
1728 p_uoo_id );
1729 FETCH c_suao_gsg INTO v_gsg_grading_schema_cd,
1730 v_gsg_version_number,
1731 v_gsg_grade,
1732 v_gsg_s_result_type,
1733 v_suao_trans_grading_schema_cd,
1734 v_suao_trans_version_number,
1735 v_suao_trans_grade;
1736 IF (c_suao_gsg%NOTFOUND) THEN
1737 CLOSE c_suao_gsg;
1738 RETURN NULL;
1739 ELSE
1740 -- Determine if the translated grade exists and is to be returned.
1741 IF v_suao_trans_grading_schema_cd IS NULL OR
1742 v_suao_trans_version_number IS NULL OR
1743 v_suao_trans_grade IS NULL THEN
1744 p_grading_schema_cd := v_gsg_grading_schema_cd;
1745 p_gs_version_number := v_gsg_version_number;
1746 p_grade := v_gsg_grade;
1747 CLOSE c_suao_gsg;
1748 RETURN v_gsg_s_result_type;
1749 ELSE
1750 OPEN c_gsg2;
1751 FETCH c_gsg2 INTO v_gsg2_s_result_type;
1752 IF c_gsg2%NOTFOUND THEN
1753 p_grading_schema_cd := NULL;
1754 p_gs_version_number := NULL;
1755 p_grade := NULL;
1756 CLOSE c_suao_gsg;
1757 CLOSE c_gsg2;
1758 RETURN NULL;
1759 ELSE
1760 p_grading_schema_cd := v_suao_trans_grading_schema_cd;
1761 p_gs_version_number := v_suao_trans_version_number;
1762 p_grade := v_suao_trans_grade;
1763 CLOSE c_suao_gsg;
1764 CLOSE c_gsg2;
1765 RETURN v_gsg2_s_result_type;
1766 END IF;
1767 END IF;
1768 END IF;
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 IF (c_sua%ISOPEN) THEN
1772 CLOSE c_sua;
1773 END IF;
1774 IF (c_suao_gsg%ISOPEN) THEN
1775 CLOSE c_suao_gsg;
1776 END IF;
1777 IF (c_gsg2%ISOPEN) THEN
1778 CLOSE c_gsg2;
1779 END IF;
1780 RAISE;
1781 END;
1782 END assp_get_sua_rel_grade;
1783
1784
1785 FUNCTION assp_get_sua_rel_marks(
1786 p_person_id IN NUMBER ,
1787 p_course_cd IN VARCHAR2 ,
1788 p_unit_cd IN VARCHAR2 ,
1789 p_cal_type IN VARCHAR2 ,
1790 p_ci_sequence_number IN NUMBER ,
1791 p_unit_attempt_status IN VARCHAR2 ,
1792 p_finalised_ind IN VARCHAR2 ,
1793 p_grading_schema_cd OUT NOCOPY VARCHAR2 ,
1794 p_gs_version_number OUT NOCOPY NUMBER ,
1795 p_grade OUT NOCOPY VARCHAR2 ,
1796 p_uoo_id IN NUMBER )
1797 RETURN NUMBER IS
1798 gv_other_detail VARCHAR2(255);
1799 BEGIN -- assp_get_sua_grade
1800 -- This is an enrolments module.
1801 -- It gets the grade of a student IGS_PS_UNIT attempt within a IGS_PS_COURSE code.
1802 -- This routine will determine the appropriate grade (and its matching
1803 -- result type) and return them. If no grade is found NULL will be
1804 -- returned (and output parameters will be NULL).
1805 -- IGS_GE_NOTE: This routine handles DUPLICATE IGS_PS_UNIT attempts by searching for
1806 -- the 'source' IGS_PS_UNIT attempt and retrieving its grade.
1807 -- Note2: If the p_finalised_ind is set then only finalised grades will
1808 -- be returned.
1809 DECLARE
1810 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1811 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1812 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
1813 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1814 v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
1815 v_finalised_ind VARCHAR2(1);
1816 v_sua_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE;
1817 v_gsg_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1818 v_gsg_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1819 v_gsg_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1820 v_gsg_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
1821 v_suao_trans_grading_schema_cd
1822 IGS_AS_SU_STMPTOUT.translated_grading_schema_cd%TYPE;
1823 v_suao_trans_version_number
1824 IGS_AS_SU_STMPTOUT.translated_version_number%TYPE;
1825 v_suao_trans_grade IGS_AS_SU_STMPTOUT.translated_grade%TYPE;
1826 v_gsg2_s_result_type IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
1827 v_marks IGS_AS_SU_STMPTOUT.mark%TYPE;
1828 CURSOR c_sua (
1829 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1830 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1831 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1832 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1833 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
1834 ) IS
1835 SELECT sut.transfer_course_cd
1836 FROM IGS_PS_STDNT_UNT_TRN sut,
1837 IGS_EN_SU_ATTEMPT sua
1838 WHERE sut.person_id = cp_person_id AND
1839 sua.person_id = sut.person_id AND
1840 sut.uoo_id = cp_uoo_id AND
1841 sua.uoo_id = sut.uoo_id AND
1842 sua.course_cd = sut.transfer_course_cd AND
1843 sua.unit_attempt_status IN (cst_completed, cst_discontin)
1844 ORDER BY sua.unit_attempt_status;
1845 CURSOR c_suao_gsg (
1846 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1847 c_v_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1848 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1849 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1850 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1851 c_v_finalised_ind VARCHAR2,
1852 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE
1853 ) IS
1854 SELECT gsg.grading_schema_cd,
1855 gsg.version_number,
1856 gsg.grade,
1857 gsg.s_result_type,
1858 suao.translated_grading_schema_cd,
1859 suao.translated_version_number,
1860 suao.translated_grade ,
1861 suao.mark
1862 FROM IGS_AS_SU_STMPTOUT suao,
1863 IGS_AS_GRD_SCH_GRADE gsg
1864 WHERE suao.person_id = cp_person_id AND
1865 suao.course_cd = c_v_course_cd AND
1866 suao.uoo_id = cp_uoo_id AND
1867 suao.finalised_outcome_ind
1868 LIKE DECODE(c_v_finalised_ind, 'Y', 'Y', '%') AND
1869 suao.grading_schema_cd = gsg.grading_schema_cd AND
1870 suao.version_number = gsg.version_number AND
1871 suao.grade = gsg.grade AND
1872 nvl(suao.RELEASE_DATE ,sysdate+10) <=sysdate
1873 ORDER BY outcome_dt DESC; -- will put the newest date first.
1874 CURSOR c_gsg2 IS
1875 SELECT gsg2.s_result_type
1876 FROM IGS_AS_GRD_SCH_GRADE gsg2
1877 WHERE gsg2.grading_schema_cd = v_suao_trans_grading_schema_cd AND
1878 gsg2.version_number = v_suao_trans_version_number AND
1879 gsg2.grade = v_suao_trans_grade;
1880 BEGIN
1881 p_grading_schema_cd := NULL;
1882 p_gs_version_number := NULL;
1883 p_grade := NULL;
1884 -- Depending on the status of the IGS_PS_UNIT attempt, set the grade search criteria.
1885 IF (p_unit_attempt_status = cst_duplicate) THEN
1886 -- Locate the original IGS_PS_UNIT attempt from which the grade was sourced.
1887 -- This will use IGS_PS_STDNT_UNT_TRN details created as a result of
1888 -- a IGS_PS_COURSE transfer
1889 OPEN c_sua(
1890 p_person_id,
1891 p_unit_cd,
1892 p_cal_type,
1893 p_ci_sequence_number,
1894 p_uoo_id );
1895 FETCH c_sua INTO v_sua_course_cd;
1896 IF (c_sua%NOTFOUND) THEN
1897 CLOSE c_sua;
1898 RETURN NULL;
1899 ELSE
1900 v_course_cd := v_sua_course_cd;
1901 END IF;
1902 CLOSE c_sua;
1903 ELSIF (p_unit_attempt_status = cst_completed OR
1904 p_unit_attempt_status = cst_discontin OR
1905 (p_finalised_ind = 'N' and p_unit_attempt_status = cst_enrolled)) THEN
1906 -- Use the parameter IGS_PS_COURSE code
1907 v_course_cd := p_course_cd;
1908 END IF;
1909 -- Search for the latest grade against the student IGS_PS_UNIT attempt
1910 OPEN c_suao_gsg(
1911 p_person_id,
1912 v_course_cd,
1913 p_unit_cd,
1914 p_cal_type,
1915 p_ci_sequence_number,
1916 p_finalised_ind,
1917 p_uoo_id );
1918 FETCH c_suao_gsg INTO v_gsg_grading_schema_cd,
1919 v_gsg_version_number,
1920 v_gsg_grade,
1921 v_gsg_s_result_type,
1922 v_suao_trans_grading_schema_cd,
1923 v_suao_trans_version_number,
1924 v_suao_trans_grade,
1925 v_marks;
1926 IF (c_suao_gsg%NOTFOUND) THEN
1927 CLOSE c_suao_gsg;
1928 RETURN NULL;
1929 ELSE
1930 RETURN v_marks;
1931 END IF;
1932 EXCEPTION
1933 WHEN OTHERS THEN
1934 IF (c_sua%ISOPEN) THEN
1935 CLOSE c_sua;
1936 END IF;
1937 IF (c_suao_gsg%ISOPEN) THEN
1938 CLOSE c_suao_gsg;
1939 END IF;
1940 IF (c_gsg2%ISOPEN) THEN
1941 CLOSE c_gsg2;
1942 END IF;
1943 RAISE;
1944 END;
1945 END assp_get_sua_rel_marks;
1946
1947 FUNCTION assp_get_ai_ref(
1948 usaii in igs_ps_unitass_item.unit_section_ass_item_id%type,
1949 uaii in igs_as_unitass_item.unit_ass_item_id%type
1950 )
1951 RETURN VARCHAR2 IS
1952 BEGIN
1953 DECLARE
1954 CURSOR c_ref_ps (usaid igs_ps_unitass_item.unit_section_ass_item_id%TYPE)IS
1955 SELECT reference, release_date
1956 FROM igs_ps_unitass_item
1957 WHERE unit_section_ass_item_id = usaid;
1958 CURSOR c_ref_as (uaid igs_as_unitass_item.unit_ass_item_id%TYPE)IS
1959 SELECT reference, release_date
1960 FROM igs_as_unitass_item
1961 WHERE unit_ass_item_id = uaid;
1962 v_ref c_ref_as%ROWTYPE;
1963
1964 BEGIN
1965 IF usaii IS NOT NULL THEN
1966 OPEN c_ref_ps(usaii);
1967 FETCH c_ref_ps INTO v_ref;
1968 CLOSE c_ref_ps;
1969 ELSIF uaii IS NOT NULL THEN
1970 OPEN c_ref_as(uaii);
1971 FETCH c_ref_as INTO v_ref;
1972 CLOSE c_ref_as;
1973 END IF;
1974 RETURN v_ref.reference;
1975 END;
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 RAISE;
1979 END assp_get_ai_ref;
1980
1981 FUNCTION assp_get_ai_reldate(
1982 usaii in igs_ps_unitass_item.unit_section_ass_item_id%type,
1983 uaii in igs_as_unitass_item.unit_ass_item_id%type
1984 )
1985 RETURN DATE IS
1986 BEGIN
1987 DECLARE
1988 CURSOR c_ref_ps (usaid igs_ps_unitass_item.unit_section_ass_item_id%TYPE)IS
1989 SELECT reference, release_date
1990 FROM igs_ps_unitass_item
1991 WHERE unit_section_ass_item_id = usaid;
1992 CURSOR c_ref_as (uaid igs_as_unitass_item.unit_ass_item_id%TYPE)IS
1993 SELECT reference, release_date
1994 FROM igs_as_unitass_item
1995 WHERE unit_ass_item_id = uaid;
1996 v_ref c_ref_as%ROWTYPE;
1997
1998 BEGIN
1999 IF usaii IS NOT NULL THEN
2000 OPEN c_ref_ps(usaii);
2001 FETCH c_ref_ps INTO v_ref;
2002 CLOSE c_ref_ps;
2003 ELSIF uaii IS NOT NULL THEN
2004 OPEN c_ref_as(uaii);
2005 FETCH c_ref_as INTO v_ref;
2006 CLOSE c_ref_as;
2007 END IF;
2008 RETURN v_ref.release_date;
2009 END;
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 RAISE;
2013 END assp_get_ai_reldate;
2014
2015 END IGS_AS_GEN_003 ;
[Home] [Help]