1 PACKAGE BODY IGS_AS_VAL_SUAAI AS
2 /* $Header: IGSAS30B.pls 115.11 2003/12/03 08:45:44 ijeddy ship $ */
3
4
5 -- Val IGS_PS_UNIT assess item applies to stud IGS_PS_UNIT IGS_AD_LOCATION, class and mode.
6 FUNCTION ASSP_VAL_UAI_LOC_UC(
7 p_student_location_cd IN VARCHAR2 ,
8 p_student_unit_class IN VARCHAR2 ,
9 p_student_unit_mode IN VARCHAR2 ,
10 p_location_cd IN VARCHAR2 ,
11 p_unit_class IN VARCHAR2 ,
12 p_unit_mode IN VARCHAR2 ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN IS
15
16 BEGIN -- assp_val_uai_loc_uc
17 -- ijeddy, Bug 3201661, Grade Book. Obsoleted
18 RETURN TRUE;
19 END assp_val_uai_loc_uc;
20 --
21 -- Validate that date is not after the assessment variation cutoff date.
22 --
23 -- Validate Assessment Item IGS_PS_COURSE Type restrictions.
24 FUNCTION ASSP_VAL_AI_ACOT(
25 p_ass_id IN NUMBER ,
26 p_course_type IN VARCHAR2 ,
27 p_message_name OUT NOCOPY VARCHAR2 )
28 RETURN BOOLEAN IS
29 gv_other_detail VARCHAR2(255);
30 BEGIN -- assp_val_ai_acot
31 -- Validate that if the assessment item is of an examinable type,
32 -- then validate if there exists IGS_AS_COURSE_TYPE records that restrict
33 -- the assessment item to particular IGS_PS_COURSE types.
34 DECLARE
35 cst_no CONSTANT CHAR := 'N';
36 v_rows_exist BOOLEAN := FALSE;
37 v_valid_item BOOLEAN := FALSE;
38 v_examinable_ind IGS_AS_ASSESSMNT_TYP.examinable_ind%TYPE;
39 v_course_type IGS_AS_COURSE_TYPE.course_type%TYPE;
40 CURSOR c_atyp(
41 cp_ass_id IGS_AS_ASSESSMNT_ITM.ass_id%TYPE) IS
42 SELECT atyp.examinable_ind
43 FROM IGS_AS_ASSESSMNT_TYP atyp,
44 IGS_AS_ASSESSMNT_ITM ai
45 WHERE ai.ass_id = cp_ass_id AND
46 ai.assessment_type = atyp.assessment_type;
47 CURSOR c_act(
48 cp_ass_id IGS_AS_ASSESSMNT_ITM.ass_id%TYPE) IS
49 SELECT course_type
50 FROM IGS_AS_COURSE_TYPE
51 WHERE ass_id = cp_ass_id;
52 BEGIN
53 -- Set the default message number
54 p_message_name := NULL;
55 -- Cursor handling
56 OPEN c_atyp(p_ass_id);
57 FETCH c_atyp INTO v_examinable_ind;
58 IF c_atyp%NOTFOUND THEN
59 CLOSE c_atyp;
60 RAISE NO_DATA_FOUND;
61 END IF;
62 CLOSE c_atyp;
63 IF (v_examinable_ind = cst_no) THEN
64 RETURN TRUE;
65 END IF;
66 FOR v_act_rec IN c_act(p_ass_id)
67 LOOP
68 v_rows_exist := TRUE;
69 IF (v_act_rec.course_type = p_course_type) THEN
70 v_valid_item := TRUE;
71 EXIT;
72 END IF;
73 END LOOP;
74 IF v_rows_exist THEN
75 IF v_valid_item THEN
76 RETURN TRUE;
77 ELSE
78 p_message_name := 'IGS_AS_EXAM_ASSITEM_NA';
79 RETURN FALSE;
80 END IF;
81 ELSE
82 RETURN TRUE;
83 END IF;
84 END;
85
86 END assp_val_ai_acot;
87 --
88 --
89 -- Validate if assessment item completed for discontinued IGS_PS_UNIT.
90 FUNCTION ASSP_VAL_ASS_COUNT(
91 p_unit_attempt_status IN VARCHAR2 ,
92 p_tracking_id IN NUMBER )
93 RETURN VARCHAR2 IS
94 gv_other_detail VARCHAR2(255);
95 BEGIN -- assp_val_ass_count
96 -- This module will check that if the status is discontinued, determine if the
97 -- assignment has been returned by the student, if it has, then include it
98 -- in the count.
99 -- This module is called from a view suaai_extension_v which is used in the
100 -- report "Assignment Due Date Summary Report".
101 DECLARE
102 v_check CHAR;
103 CURSOR c_trst IS
104 SELECT 'x'
105 FROM IGS_TR_STEP trst
106 WHERE trst.tracking_id = p_tracking_id AND
107 trst.s_tracking_step_type = 'ASSIGN-DUE' AND
108 trst.completion_dt IS NOT NULL;
109 BEGIN
110 -- Invalid status so do not count this record.
111 IF (p_unit_attempt_status = 'INVALID') THEN
112 RETURN 'N';
113 -- If status is discontinued, determine if the assignment has been returned by
114 -- the student ready for marking.
115 ELSIF (p_unit_attempt_status = 'DISCONTIN') THEN
116 OPEN c_trst;
117 FETCH c_trst INTO v_check;
118 IF (c_trst%FOUND) THEN
119 -- Assignment returned to include it to be counted.
120 CLOSE c_trst;
121 RETURN 'Y';
122 ELSE
123 -- Assignment not to be counted as student has not returned the assignment.
124 CLOSE c_trst;
125 RETURN 'N';
126 END IF;
127 CLOSE c_trst;
128 -- Valid status (ie. COMPLETED, UNCONFIRMED, ENROLLED), include record
129 -- to be counted.
130 ELSE
131 RETURN 'Y';
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF (c_trst%ISOPEN) THEN
136 CLOSE c_trst;
137 END IF;
138 RAISE;
139 END;
140 EXCEPTION
141 WHEN OTHERS THEN
142 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
143 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SUAAI.assp_val_ass_count');
144 IGS_GE_MSG_STACK.ADD;
145 App_Exception.Raise_Exception;
146 END assp_val_ass_count;
147 --
148 -- Validate the attempt number is unique within the student assigment.
149 FUNCTION assp_val_suaai_atmpt(
150 p_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE ,
151 p_course_cd IGS_AS_SU_ATMPT_ITM.course_cd%TYPE ,
152 p_unit_cd IGS_AS_SU_ATMPT_ITM.unit_cd%TYPE ,
153 p_cal_type IGS_AS_SU_ATMPT_ITM.cal_type%TYPE ,
154 p_ci_sequence_number IGS_AS_SU_ATMPT_ITM.ci_sequence_number%TYPE ,
155 p_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE ,
156 p_creation_dt IN DATE ,
157 p_attempt_number NUMBER ,
158 p_message_name OUT NOCOPY VARCHAR2 ,
159 -- anilk, 22-Apr-2003, Bug# 2829262
160 p_uoo_id IN NUMBER )
161 RETURN BOOLEAN IS
162
163 BEGIN -- assp_val_suaai_atmpt
164 -- Validate that the attempt number for a student's assessment item is unique.
165 DECLARE
166 v_attempt_number IGS_AS_SU_ATMPT_ITM.attempt_number%TYPE;
167 CURSOR c_suaai_atmpt_num IS
168 SELECT suaai.attempt_number
169 FROM IGS_AS_SU_ATMPT_ITM suaai
170 WHERE suaai.person_id = p_person_id AND
171 suaai.course_cd = p_course_cd AND
172 -- anilk, 22-Apr-2003, Bug# 2829262
173 suaai.uoo_id = p_uoo_id AND
174 suaai.ass_id = p_ass_id AND
175 suaai.creation_dt <> p_creation_dt AND
176 suaai.attempt_number = p_attempt_number AND
177 suaai.logical_delete_dt IS NULL;
178 BEGIN
179 -- Set the default message number
180 p_message_name := NULL;
181 -- Determine if the attempt number is unique within
182 -- the assessment item for the student.
183 OPEN c_suaai_atmpt_num;
184 FETCH c_suaai_atmpt_num INTO v_attempt_number;
185 IF c_suaai_atmpt_num%FOUND THEN
186 CLOSE c_suaai_atmpt_num;
187 p_message_name := 'IGS_GE_DUPLICATE_VALUE';
188 RETURN FALSE;
189 END IF;
190 CLOSE c_suaai_atmpt_num;
191 RETURN TRUE;
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF c_suaai_atmpt_num%ISOPEN THEN
195 CLOSE c_suaai_atmpt_num;
196 END IF;
197 RAISE;
198 END;
199 EXCEPTION
200 WHEN OTHERS THEN
201 null;
202 --Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
203 -- App_Exception.Raise_Exception;
204 END assp_val_suaai_atmpt;
205 --
206 -- Validate item still applies to student as a uai or part of a pattern.
207
208 --ijeddy, Bug 3201661, Grade Book.
209 FUNCTION ASSP_VAL_SUAAI_VALID(
210 p_person_id IN NUMBER ,
211 p_unit_cd IN VARCHAR2 ,
212 p_course_cd IN VARCHAR2 ,
213 p_cal_type IN VARCHAR2 ,
214 p_ci_sequence_number IN NUMBER ,
215 p_ass_pattern_id IN NUMBER ,
216 p_ass_id IN NUMBER ,
217 p_suaai_logical_delete_dt IN DATE ,
218 p_message_name OUT NOCOPY VARCHAR2 ,
219 -- anilk, 22-Apr-2003, Bug# 2829262
220 p_uoo_id IN NUMBER DEFAULT NULL )
221 RETURN BOOLEAN IS
222
223 BEGIN -- assp_val_suaai_valid
224 -- This module validate IGS_AD_PS_APLINSTUNT unit version.
225 DECLARE
226 v_logical_del_dt IGS_AS_UNTAS_PATTERN.logical_delete_dt%TYPE;
227 v_uapi_dummy VARCHAR2(1);
228 v_rec_found BOOLEAN := FALSE;
229
230 -- This cursor is changed by DDEY as a part of bug # 2358821
231 -- The Assessment Item can be set at Unit level and the Unit Section Level
232 -- this cursor checks if the Assessmsnt Item is stil valid for the Unit Section
233 -- or at the Unit level.
234
235 CURSOR c_suv IS
236 SELECT uai.logical_delete_dt
237 FROM
238 IGS_EN_SU_ATTEMPT sua,
239 IGS_AS_UNITASS_ITEM uai
240 WHERE sua.person_id = p_person_id AND
241 sua.course_cd = p_course_cd AND
242 -- anilk, 22-Apr-2003, Bug# 2829262
243 sua.uoo_id = p_uoo_id AND
244 uai.ass_id = p_ass_id AND
245 sua.unit_cd = uai.unit_cd AND
246 sua.version_number = uai.version_number AND
247 sua.cal_type = uai.cal_type AND
248 sua.ci_sequence_number = uai.ci_sequence_number AND
249 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(uai.ass_id,
250 sua.person_id,
251 sua.course_cd) = 'TRUE'
252 UNION
253 SELECT psuai.logical_delete_dt
254 FROM
255 IGS_EN_SU_ATTEMPT sua,
256 IGS_PS_UNITASS_ITEM_V psuai
257 WHERE sua.person_id = p_person_id AND
258 sua.course_cd = p_course_cd AND
259 -- anilk, 22-Apr-2003, Bug# 2829262
260 sua.uoo_id = p_uoo_id AND
261 psuai.ass_id = p_ass_id AND
262 sua.uoo_id = psuai.uoo_id AND
263 IGS_AS_VAL_UAI.assp_val_sua_ai_acot(psuai.ass_id,
264 sua.person_id,
265 sua.course_cd) = 'TRUE' ;
266
267
268 BEGIN
269 p_message_name := NULL;
270 -- Check if part of a pattern or not, whether the item is still valid.
271 -- There may have been IGS_PS_COURSE restrictions placed on the item.
272 -- Determine if the item is still valid for the student
273 FOR v_suv_rec IN c_suv LOOP
274 IF v_suv_rec.logical_delete_dt IS NOT NULL AND
275 p_suaai_logical_delete_dt IS NULL THEN
276 -- Item has been logically deleted but the item belonging
277 -- to student has not been logically deleted, hence it is
278 -- invalid. Do nothing at this point as there may still
279 -- be records to process and a valid one is yet to be found.
280 NULL;
281 ELSE
282 IF (v_suv_rec.logical_delete_dt IS NULL AND
283 p_suaai_logical_delete_dt IS NULL) OR
284 (p_suaai_logical_delete_dt IS NOT NULL) THEN
285 -- The record is valid or a valid item has been found
286 -- but the student's record has been deleted.
287 v_rec_found := TRUE;
288 EXIT;
289 END IF;
290 END IF;
291 END LOOP;
292 -- Applicable offering options may have changed or IGS_PS_COURSE restriction placed on
293 -- the item, hence the item is no longer valid for the student.
294 IF v_rec_found = FALSE THEN
295 p_message_name := 'IGS_AS_SUA_ASSITEM_INVALID';
296 RETURN FALSE;
297 END IF;
298 RETURN TRUE;
299 EXCEPTION
300 WHEN OTHERS THEN
301 IF (c_suv%ISOPEN) THEN
302 CLOSE c_suv;
303 END IF;
304 END;
305
306 END assp_val_suaai_valid;
307
308 END IGS_AS_VAL_SUAAI;
[Home] [Help]