1 PACKAGE BODY IGS_AS_VAL_AI AS
2 /* $Header: IGSAS11B.pls 115.5 2002/11/28 22:42:00 nsidana ship $ */
3 --
4 -- Validate the appropriate assessment item details set and are not set
5 FUNCTION assp_val_ai_details(
6 p_assessment_type IN IGS_AS_ASSESSMNT_ITM_ALL.assessment_type%TYPE ,
7 p_exam_scheduled_ind IN IGS_AS_ASSESSMNT_ITM_ALL.exam_scheduled_ind%TYPE ,
8 p_exam_working_time IN IGS_AS_ASSESSMNT_ITM_ALL.exam_working_time%TYPE ,
9 p_exam_announcements IN IGS_AS_ASSESSMNT_ITM_ALL.exam_announcements%TYPE ,
10 p_exam_short_paper_name IN IGS_AS_ASSESSMNT_ITM_ALL.exam_short_paper_name%TYPE ,
11 p_exam_paper_name IN IGS_AS_ASSESSMNT_ITM_ALL.exam_paper_name%TYPE ,
12 p_exam_perusal_time IN IGS_AS_ASSESSMNT_ITM_ALL.exam_perusal_time%TYPE ,
13 p_exam_supervisor_instrctn IN IGS_AS_ASSESSMNT_ITM_ALL.exam_supervisor_instrctn%TYPE ,
14 p_exam_allowable_instrctn IN IGS_AS_ASSESSMNT_ITM_ALL.exam_allowable_instrctn%TYPE ,
15 p_exam_non_allowed_instrctn IN IGS_AS_ASSESSMNT_ITM_ALL.exam_non_allowed_instrctn%TYPE ,
16 p_exam_supplied_instrctn IN IGS_AS_ASSESSMNT_ITM_ALL.exam_supplied_instrctn%TYPE ,
17 p_question_or_title IN IGS_AS_ASSESSMNT_ITM_ALL.question_or_title%TYPE ,
18 p_ass_length_or_duration IN IGS_AS_ASSESSMNT_ITM_ALL.ass_length_or_duration%TYPE ,
19 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
20 RETURN BOOLEAN AS
21 V_MESSAGE_NAME VARCHAR2(30);
22 BEGIN
23 DECLARE
24 CURSOR c_atyp(
25 cp_assessment_type IGS_AS_ASSESSMNT_ITM.assessment_type%TYPE) IS
26 SELECT examinable_ind
27 FROM IGS_AS_ASSESSMNT_TYP
28 WHERE assessment_type = cp_assessment_type;
29 v_atyp_rec c_atyp%ROWTYPE;
30 cst_yes CONSTANT CHAR := 'Y';
31 BEGIN
32 -- Validate the apropriate assessment item details are set/not set
33 -- for the respective assessment type's examinable indicator settings.
34 -- Set the default message number
35 P_MESSAGE_NAME := NULL;
36 -- Cursor handling
37 OPEN c_atyp(
38 p_assessment_type);
39 FETCH c_atyp INTO v_atyp_rec;
40 IF c_atyp%NOTFOUND THEN
41 CLOSE c_atyp;
42 RETURN TRUE;
43 END IF;
44 CLOSE c_atyp;
45 IF (v_atyp_rec.examinable_ind = cst_yes) THEN
46 -- Check if any non-exam related fields are set
47 IF (p_question_or_title IS NOT NULL OR
48 p_ass_length_or_duration IS NOT NULL) THEN
49 P_MESSAGE_NAME := 'IGS_AS_ASS_ITEM_DET_CONFLICT';
50 RETURN FALSE;
51 END IF;
52 ELSE
53 -- Check if any exam related fields are set
54 IF (p_exam_working_time IS NOT NULL OR
55 p_exam_announcements IS NOT NULL OR
56 p_exam_short_paper_name IS NOT NULL OR
57 p_exam_paper_name IS NOT NULL OR
58 p_exam_perusal_time IS NOT NULL OR
59 p_exam_supervisor_instrctn IS NOT NULL OR
60 p_exam_allowable_instrctn IS NOT NULL OR
61 p_exam_non_allowed_instrctn IS NOT NULL OR
62 p_exam_supplied_instrctn IS NOT NULL OR
63 p_exam_scheduled_ind = cst_yes) THEN
64 P_MESSAGE_NAME := 'IGS_AS_ASS_ITEM_DET_CONFLICT';
65 RETURN FALSE;
66 END IF;
67 END IF;
68 -- Return the default value
69 RETURN TRUE;
70 END;
71 EXCEPTION
72 WHEN OTHERS THEN
73 FND_MESSAGE.SET_NAME('IGS',v_message_name);
74 IGS_GE_MSG_STACK.ADD;
75 --APP_EXCEPTION.RAISE_EXCEPTION;
76 END assp_val_ai_details;
77 --
78 -- Validate exam times
79 FUNCTION assp_val_ai_ex_times(
80 p_assessment_type IN IGS_AS_ASSESSMNT_ITM_ALL.assessment_type%TYPE ,
81 p_exam_working_time IN IGS_AS_ASSESSMNT_ITM_ALL.exam_working_time%TYPE ,
82 p_exam_perusal_time IN IGS_AS_ASSESSMNT_ITM_ALL.exam_perusal_time%TYPE ,
83 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
84 RETURN BOOLEAN AS
85 V_MESSAGE_NAME VARCHAR2(30);
86
87 BEGIN
88 DECLARE
89 CURSOR c_atyp(
90 cp_assessment_type IGS_AS_ASSESSMNT_ITM.assessment_type%TYPE) IS
91 SELECT examinable_ind
92 FROM IGS_AS_ASSESSMNT_TYP
93 WHERE assessment_type = cp_assessment_type;
94 v_atyp_rec c_atyp%ROWTYPE;
95 cst_yes CONSTANT CHAR := 'Y';
96 v_exam_working_time VARCHAR2(10);
97 v_exam_perusal_time VARCHAR2(10);
98 BEGIN
99 -- Validate the exam times if they have been entered.
100 -- Only validate when both times are set.
101 -- Check whether the perusal time is greater than the
102 -- working time and set off an appropriate message.
103 -- Set the default message number
104 P_MESSAGE_NAME := 'NULL';
105 -- Cursor handling
106 OPEN c_atyp(
107 p_assessment_type);
108 FETCH c_atyp INTO v_atyp_rec;
109 IF c_atyp%NOTFOUND THEN
110 CLOSE c_atyp;
111 RETURN TRUE;
112 END IF;
113 CLOSE c_atyp;
114 IF (v_atyp_rec.examinable_ind = cst_yes) THEN
115 IF (p_exam_working_time IS NOT NULL AND
116 p_exam_perusal_time IS NOT NULL) THEN
117 v_exam_perusal_time := SUBSTR(IGS_GE_DATE.IGSCHARDT(p_exam_perusal_time), 12, 5);
118 v_exam_working_time := SUBSTR(IGS_GE_DATE.IGSCHARDT(p_exam_working_time), 12, 5);
119 IF (v_exam_perusal_time >= v_exam_working_time) THEN
120 P_MESSAGE_NAME := 'IGS_AS_PERUSALTIME_LT_WORKTIM';
121 RETURN FALSE;
122 END IF;
123 END IF;
124 END IF;
125 -- Return the default value
126 RETURN TRUE;
127 END;
128 EXCEPTION
129 WHEN OTHERS THEN
130 FND_MESSAGE.SET_NAME('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 --APP_EXCEPTION.RAISE_EXCEPTION;
133 END assp_val_ai_ex_times;
134 --
135 -- Validate assessment type closed indicator.
136 FUNCTION assp_val_atyp_closed(
137 p_assessment_type IN IGS_AS_ASSESSMNT_TYP.assessment_type%TYPE ,
138 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
139 RETURN BOOLEAN AS
140 V_MESSAGE_NAME VARCHAR2(30);
141 BEGIN -- assp_val_atyp_closed
142 -- Validate the assessemnt type closed indicator
143 DECLARE
144 CURSOR c_atyp(
145 cp_assessment_type IGS_AS_ASSESSMNT_TYP.assessment_type%TYPE) IS
146 SELECT closed_ind
147 FROM IGS_AS_ASSESSMNT_TYP
148 WHERE assessment_type = cp_assessment_type;
149 v_atyp_rec c_atyp%ROWTYPE;
150 cst_yes CONSTANT CHAR := 'Y';
151 BEGIN
152 -- Set the default message number
153 P_MESSAGE_NAME := NULL;
154 -- Cursor handling
155 OPEN c_atyp(
156 p_assessment_type);
157 FETCH c_atyp INTO v_atyp_rec;
158 IF c_atyp%NOTFOUND THEN
159 CLOSE c_atyp;
160 RETURN TRUE;
161 END IF;
162 CLOSE c_atyp;
163 IF (v_atyp_rec.closed_ind = cst_yes) THEN
164 P_MESSAGE_NAME := 'IGS_AS_ASSTYPE_CLOSED';
165 RETURN FALSE;
166 END IF;
167 -- Return the default value
168 RETURN TRUE;
169 END;
170 EXCEPTION
171 WHEN OTHERS THEN
172 FND_MESSAGE.SET_NAME('IGS',v_message_name);
173 IGS_GE_MSG_STACK.ADD;
174 --APP_EXCEPTION.RAISE_EXCEPTION;
175 END assp_val_atyp_closed;
176 --
177 -- Validate updating ass type, does not cause non-unique uai.reference
178 FUNCTION ASSP_VAL_AI_TYPE(
179 p_ass_id IN NUMBER ,
180 p_assessment_type IN VARCHAR2 ,
181 p_old_assessment_type IN VARCHAR2 ,
182 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
183 RETURN BOOLEAN AS
184 V_MESSAGE_NAME VARCHAR2(30);
185 BEGIN -- ASSP_VAL_AI_TYPE
186 -- This module will be called when the assessment type is being altered for an
187 -- existing assessment item. It will check that when altering the assessment
188 -- type, that no IGS_AS_UNITASS_ITEM records exist within a unitoffering
189 -- pattern that will have the same reference and type.
190 DECLARE
191 v_dummy VARCHAR2(1);
192 v_atyp_s_ass_type IGS_AS_ASSESSMNT_TYP.s_assessment_type%TYPE;
193 v_atyp_examinable_ind IGS_AS_ASSESSMNT_TYP.examinable_ind%TYPE;
194 cst_assignment CONSTANT VARCHAR2(10) := 'ASSIGNMENT';
195 CURSOR c_uai IS
196 SELECT 'x'
197 FROM IGS_AS_UNITASS_ITEM uai
198 WHERE ass_id = p_ass_id AND
199 EXISTS (
200 SELECT 'x'
201 FROM IGS_AS_UNITASS_ITEM uai2
202 WHERE uai2.unit_cd = uai.unit_cd AND
203 uai2.version_number = uai.version_number AND
204 uai2.cal_type = uai.cal_type AND
205 uai2.ci_sequence_number = uai.ci_sequence_number AND
206 uai2.sequence_number <> uai.sequence_number AND
207 uai2.reference = uai.reference AND
208 uai2.logical_delete_dt IS NULL AND
209 EXISTS (
210 SELECT 'x'
211 FROM IGS_AS_ASSESSMNT_ITM ai
212 WHERE ai.ass_id = uai2.ass_id AND
213 ai.assessment_type = p_assessment_type)) AND
214 uai.logical_delete_dt IS NULL;
215 CURSOR c_atyp ( cp_assessment_type IGS_AS_ASSESSMNT_TYP.assessment_type%TYPE) IS
216 SELECT atyp.s_assessment_type,
217 atyp.examinable_ind
218 FROM IGS_AS_ASSESSMNT_TYP atyp
219 WHERE atyp.assessment_type = cp_assessment_type;
220 CURSOR c_suaai IS
221 SELECT 'x'
222 FROM IGS_AS_SU_ATMPT_ITM suaai
223 WHERE ass_id = p_ass_id AND
224 suaai.logical_delete_dt IS NULL AND
225 suaai.tracking_id IS NOT NULL;
226 -- If an examinable item, then reference must be unique across all examinable
227 -- items within the IGS_PS_UNIT offering pattern.
228 CURSOR c_uai2 IS
229 SELECT 'x'
230 FROM IGS_AS_UNITASS_ITEM uai
231 WHERE ass_id = p_ass_id AND
232 EXISTS (
233 SELECT 'x'
234 FROM IGS_AS_UNITASS_ITEM uai2,
235 IGS_AS_ASSESSMNT_ITM ai,
236 IGS_AS_ASSESSMNT_TYP atyp
237 WHERE uai2.unit_cd = uai.unit_cd AND
238 uai2.version_number = uai.version_number AND
239 uai2.cal_type = uai.cal_type AND
240 uai2.ci_sequence_number = uai.ci_sequence_number AND
241 uai2.sequence_number <> uai.sequence_number AND
242 uai2.reference = uai.reference AND
243 uai2.logical_delete_dt IS NULL AND
244 uai2.ass_id = ai.ass_id AND
245 ai.assessment_type = atyp.assessment_type AND
246 atyp.examinable_ind = 'Y');
247 BEGIN
248 P_MESSAGE_NAME := NULL;
249 OPEN c_atyp (p_assessment_type);
250 FETCH c_atyp INTO v_atyp_s_ass_type,
251 v_atyp_examinable_ind;
252 -- This should never happen!
253 IF c_atyp%NOTFOUND THEN
254 CLOSE c_atyp;
255 RAISE NO_DATA_FOUND;
256 END IF;
257 CLOSE c_atyp;
258 IF v_atyp_examinable_ind = 'N' THEN
259 -- If not an examinable item, then check if reference is unique within the
260 -- type.
261 OPEN c_uai;
262 FETCH c_uai INTO v_dummy;
263 IF c_uai%FOUND THEN
264 CLOSE c_uai;
265 P_MESSAGE_NAME := 'IGS_AS_REF_NOT_UNIQUE';
266 RETURN FALSE;
267 END IF;
268 CLOSE c_uai;
269 ELSE
270 -- If an examinable item, then reference must be unique across all examinable
271 -- items within the UNIT offering pattern.
272 OPEN c_uai2;
273 FETCH c_uai2 INTO v_dummy;
274 IF c_uai2%FOUND THEN
275 CLOSE c_uai2;
276 P_MESSAGE_NAME := 'IGS_AS_REF_NOT_UNIQUE';
277 RETURN FALSE;
278 END IF;
279 CLOSE c_uai2;
280 END IF;
281 -- Verify that if the item is being altered from an assignment type,
282 -- check if tracking exists against the item.
283 OPEN c_atyp (p_old_assessment_type);
284 FETCH c_atyp INTO v_atyp_s_ass_type,
285 v_atyp_examinable_ind;
286 -- This should never happen!
287 IF c_atyp%NOTFOUND THEN
288 CLOSE c_atyp;
289 RAISE NO_DATA_FOUND;
290 END IF;
291 CLOSE c_atyp;
292 IF NVL(v_atyp_s_ass_type, 'NULL') = cst_assignment THEN
293 -- Determine if a tracking item exists against the item.
294 OPEN c_suaai;
295 FETCH c_suaai INTO v_dummy;
296 IF c_suaai%FOUND THEN
297 CLOSE c_suaai;
298 P_MESSAGE_NAME := 'IGS_AS_CANALT_ASSTYPE';
299 RETURN FALSE;
300 END IF;
301 CLOSE c_suaai;
302 END IF;
303 RETURN TRUE;
304 EXCEPTION
305 WHEN OTHERS THEN
306 IF c_uai%ISOPEN THEN
307 CLOSE c_uai;
308 END IF;
309 IF c_atyp%ISOPEN THEN
310 CLOSE c_atyp;
311 END IF;
312 IF c_suaai%ISOPEN THEN
313 CLOSE c_suaai;
314 END IF;
315 IF c_uai2%ISOPEN THEN
316 CLOSE c_uai2;
317 END IF;
318 RAISE;
319 END;
320 EXCEPTION
321 WHEN OTHERS THEN
322 FND_MESSAGE.SET_NAME('IGS',v_message_name);
323 IGS_GE_MSG_STACK.ADD;
324 --APP_EXCEPTION.RAISE_EXCEPTION;
325 END assp_val_ai_type;
326 --
327 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
328 --
329 -- Routine to save ai records in a PL/SQL RECORD for current commit.
330 END IGS_AS_VAL_AI;
[Home] [Help]