1 PACKAGE BODY IGS_EN_VAL_SCI AS
2 /* $Header: IGSEN64B.pls 120.0 2005/06/01 17:58:08 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
7 -- smaddali 20-sep-04 EN306- intermission updates build, bug#3889089
8 -------------------------------------------------------------------------------------------
9 -- To validate that SCI is possible with students UA's
10 FUNCTION ENRP_VAL_SCI_UA(
11 p_person_id IN NUMBER ,
12 p_course_cd IN VARCHAR2 ,
13 p_start_dt IN DATE ,
14 p_message_name OUT NOCOPY VARCHAR2)
15 RETURN boolean AS
16 NO_RECORDS_FOUND EXCEPTION;
17 BEGIN
18 DECLARE
19 v_census_date IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE;
20 CURSOR c_census_dt IS
21 SELECT census_dt_alias
22 FROM IGS_GE_S_GEN_CAL_CON
23 WHERE s_control_num = 1;
24 CURSOR c_sua_details(
25 cp_person_id IGS_EN_STDNT_PS_INTM.person_id%TYPE,
26 cp_course_cd IGS_EN_STDNT_PS_INTM.course_cd%TYPE) IS
27 SELECT cal_type,
28 ci_sequence_number
29 FROM IGS_EN_SU_ATTEMPT
30 WHERE person_id = cp_person_id AND
31 course_cd = cp_course_cd AND
32 unit_attempt_status NOT IN ('UNCONFIRM',
33 'INVALID',
34 'DISCONTIN');
35 CURSOR c_daiv_details(
36 cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
37 cp_ci_seq_num IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
38 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
39 SELECT alias_val
40 FROM IGS_CA_DA_INST_V
41 WHERE cal_type = cp_cal_type AND
42 ci_sequence_number = cp_ci_seq_num AND
43 dt_alias = cp_dt_alias
44 ORDER BY alias_val;
45 BEGIN
46 -- This module validates that the intermission is
47 -- in line with the student's unit attempts.
48 -- The rules are that the intermission can't
49 -- pre-date the censusdate of the student's last
50 -- enrolled unit attempts teaching period (ie.
51 -- where the current date is > the same census
52 -- date). This check excludes unit attempts of
53 -- status 'UNCONFIRM', 'INVALID', 'DISCONTIN'.
54 p_message_name := null;
55 OPEN c_census_dt;
56 FETCH c_census_dt INTO v_census_date;
57 IF (c_census_dt%NOTFOUND) THEN
58 CLOSE c_census_dt;
59 RAISE NO_RECORDS_FOUND;
60 END IF;
61 CLOSE c_census_dt;
62 -- selecting IGS_CA_TYPE and ci_sequence_number details
63 FOR v_sua_rec IN c_sua_details(p_person_id,
64 p_course_cd) LOOP
65 -- looping backwards through the
66 -- student's unit attempt records
67 FOR v_daiv_rec IN c_daiv_details(v_sua_rec.cal_type,
68 v_sua_rec.ci_sequence_number,
69 v_census_date) LOOP
70 -- set the message number if the census
71 -- date is after the current date and the
72 -- intermssion start date is on or before
73 -- it, as intermission is not permitted
74 IF (SYSDATE > v_daiv_rec.alias_val AND
75 p_start_dt <= v_daiv_rec.alias_val) THEN
76 p_message_name := 'IGS_EN_INTER_NOTBACK_DATED';
77 RETURN FALSE;
78 END IF;
79 END LOOP;
80 END LOOP;
81 RETURN TRUE;
82 EXCEPTION
83 WHEN NO_RECORDS_FOUND THEN
84 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
85 IGS_GE_MSG_STACK.ADD;
86 App_Exception.Raise_Exception;
87 WHEN OTHERS THEN
88 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
89 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCI.enrp_val_sci_ua');
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception;
92 END;
93 END enrp_val_sci_ua;
94 --
95 -- Validate that intermission is allowed for the student COURSE attempt
96 FUNCTION ENRP_VAL_SCI_ALWD(
97 p_person_id IN NUMBER ,
98 p_course_cd IN VARCHAR2 ,
99 p_message_name OUT NOCOPY VARCHAR2)
100 RETURN boolean AS
101 BEGIN
102 DECLARE
103 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
104 CURSOR c_sca IS
105 SELECT course_attempt_status
106 FROM IGS_EN_STDNT_PS_ATT
107 WHERE person_id = p_person_id AND
108 course_cd = p_course_cd;
109 BEGIN
110 -- This module validates that intermission is allowed
111 -- for the student's COURSE attempt, subject to :
112 -- # the COURSE status of the student's IGS_PS_COURSE
113 -- attempt can't be 'UNCONFIRM', 'DISCONTIN',
114 -- 'LAPSED', 'DELETED' 'COMPLETED'
115 p_message_name := null;
116 OPEN c_sca;
117 FETCH c_sca INTO v_course_attempt_status;
118 -- check if a record has been found
119 IF (c_sca%NOTFOUND) THEN
120 CLOSE c_sca;
121 RETURN TRUE;
122 END IF;
123 CLOSE c_sca;
124 IF (v_course_attempt_status = 'DISCONTIN') THEN
125 p_message_name := 'IGS_EN_INTRM_NA_FOR_DICONT';
126 RETURN FALSE;
127 ELSIF (v_course_attempt_status = 'LAPSED') THEN
128 p_message_name := 'IGS_EN_INTRM_NA_FOR_LAPSED';
129 RETURN FALSE;
130 ELSIF (v_course_attempt_status = 'DELETED') THEN
131 p_message_name := 'IGS_EN_INTRM_NA_FOR_DELETED';
132 RETURN FALSE;
133 ELSIF (v_course_attempt_status = 'COMPLETED') THEN
134 p_message_name := 'IGS_EN_INTER_NOTALLOW_COMPLET';
135 RETURN FALSE;
136 ELSIF (v_course_attempt_status = 'UNCONFIRM') THEN
137 p_message_name := 'IGS_EN_CHG_OPT_NOTALLOW_UNCON';
138 RETURN FALSE;
139 END IF;
140 RETURN TRUE;
141 EXCEPTION
142 WHEN OTHERS THEN
143 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
144 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCI.enrp_val_sci_alwd');
145 IGS_GE_MSG_STACK.ADD;
146 App_Exception.Raise_Exception;
147 END;
148 END enrp_val_sci_alwd;
149 --
150 -- Validate COURSE version of student COURSE intermission.
151 FUNCTION ENRP_VAL_SCI_CV_ALWD(
152 p_person_id IN NUMBER ,
153 p_course_cd IN VARCHAR2 ,
154 p_message_name OUT NOCOPY VARCHAR2)
155 RETURN boolean AS
156 BEGIN
157 DECLARE
158 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
159 v_int_allowed_ind IGS_PS_VER.intrmsn_allowed_ind%TYPE;
160 v_num_units_bf_int IGS_PS_VER.num_of_units_before_intrmsn%TYPE;
161 v_count NUMBER;
162 CURSOR c_sca_details
163 (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
164 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
165 SELECT version_number
166 FROM IGS_EN_STDNT_PS_ATT
167 WHERE person_id = cp_person_id AND
168 course_cd = cp_course_cd;
169 CURSOR c_cv_details
170 (cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
171 cp_version_num IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
172 SELECT intrmsn_allowed_ind,
173 num_of_units_before_intrmsn
174 FROM IGS_PS_VER
175 WHERE course_cd = cp_course_cd AND
176 version_number = cp_version_num;
177 CURSOR c_sca_count
178 (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
179 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
180 SELECT count(*)
181 FROM IGS_EN_SU_ATTEMPT
182 WHERE person_id = cp_person_id AND
183 course_cd = cp_course_cd AND
184 unit_attempt_status = 'COMPLETED';
185 BEGIN
186 -- This module validates that intermission is allowed
187 -- for the student's COURSE version, subject to :
188 -- # the IGS_PS_VER.intrmsn_allowed_ind
189 -- being set (this is only a warning)
190 -- # the number of complete unit attempt must
191 -- exceed the IGS_PS_VER.num_of_units_
192 -- before_intrmsn
193 p_message_name := null;
194 OPEN c_sca_details(p_person_id,
195 p_course_cd);
196 FETCH c_sca_details INTO v_version_number;
197 -- check if a record has been found
198 IF (c_sca_details%NOTFOUND) THEN
199 CLOSE c_sca_details;
200 RETURN TRUE;
201 END IF;
202 CLOSE c_sca_details;
203 OPEN c_cv_details(p_course_cd,
204 v_version_number);
205 FETCH c_cv_details INTO v_int_allowed_ind,
206 v_num_units_bf_int;
207 -- check if the IGS_PS_VER intermission
208 -- allowed indicator
209 IF (v_int_allowed_ind = 'N') THEN
210 p_message_name := 'IGS_EN_INTERMISSION_NOTPERM';
211 RETURN FALSE;
212 END IF;
213 -- check that the student has completed
214 -- enough unit attempts
215 IF (v_num_units_bf_int IS NOT NULL OR
216 v_num_units_bf_int <> 0) THEN
217 OPEN c_sca_count(p_person_id,
218 p_course_cd);
219 FETCH c_sca_count INTO v_count;
220 CLOSE c_sca_count;
221 IF (v_count < v_num_units_bf_int) THEN
222 p_message_name := 'IGS_EN_INCOMPL_STUD_UNITS';
223 RETURN FALSE;
224 END IF;
225 END IF;
226 RETURN TRUE;
227 EXCEPTION
228 WHEN OTHERS THEN
229 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
230 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCI.enrp_val_sci_cv_alwd');
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END;
234 END enrp_val_sci_cv_alwd;
235 --
236 -- Validate whether student COURSE intermission deletion is allowed
237 FUNCTION ENRP_VAL_SCI_DEL(
238 p_start_dt IN DATE ,
239 p_message_name OUT NOCOPY VARCHAR2)
240 RETURN boolean AS
241 BEGIN
242 DECLARE
243 BEGIN
244 --- Set default message number
245 p_message_name := null;
246 IF p_start_dt <= SYSDATE THEN
247 p_message_name := 'IGS_EN_NOTDEL_ITMDET_SET_ENDT';
248 Return FALSE;
249 END IF;
250 --- Return default value
251 RETURN TRUE;
252 END;
253 EXCEPTION
254 WHEN OTHERS THEN
255 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
256 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCI.enrp_val_sci_del');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END enrp_val_sci_del;
260 --
261 -- Validate student COURSE intermission duration
262 FUNCTION ENRP_VAL_SCI_DRTN(
263 p_course_cd IN VARCHAR2 ,
264 p_version_number IN NUMBER ,
265 p_start_dt IN DATE ,
266 p_end_dt IN DATE ,
267 p_message_name OUT NOCOPY VARCHAR2)
268 RETURN boolean AS
269 BEGIN
270 DECLARE
271 CURSOR c_get_mid IS
272 SELECT max_intrmsn_duration
273 FROM IGS_PS_VER
274 WHERE course_cd = p_course_cd AND
275 version_number = p_version_number;
276 v_mid IGS_PS_VER.max_intrmsn_duration%TYPE;
277 BEGIN
278 --- Set default message number
279 p_message_name := null;
280 --- Select the maximum intermission from the students COURSE version
281 OPEN c_get_mid;
282 FETCH c_get_mid INTO v_mid;
283 IF c_get_mid%NOTFOUND OR
284 v_mid IS NULL OR
285 v_mid = 0 THEN
286 CLOSE c_get_mid;
287 RETURN TRUE;
288 END IF;
289 CLOSE c_get_mid;
290 IF MONTHS_BETWEEN( p_end_dt, p_start_dt) > v_mid THEN
291 p_message_name := 'IGS_EN_INTR_PER_EXCEEDS_MAX';
292 RETURN FALSE;
293 END IF;
294 --- Return default value
295 RETURN TRUE;
296 END;
297 EXCEPTION
298 WHEN OTHERS THEN
299 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
300 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCI.enrp_val_sci_drtn');
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303
304 END enrp_val_sci_drtn;
305 --
306 -- Validate for overlap of student COURSE intermission records.
307 FUNCTION ENRP_VAL_SCI_OVRLP(
308 p_person_id IN NUMBER ,
309 p_course_cd IN VARCHAR2 ,
310 p_start_dt IN DATE ,
311 p_end_dt IN DATE ,
312 p_message_name OUT NOCOPY VARCHAR2)
313 RETURN boolean AS
314 BEGIN
315 DECLARE
316 v_sci_rec IGS_EN_STDNT_PS_INTM%ROWTYPE;
317 -- smaddali added the logical detele date check for EN306 - intermission updates build, bug#3889089 ,
318 -- check for other active records overlapping with the current record.
319 CURSOR c_sci(
320 cp_person_id IGS_EN_STDNT_PS_INTM.person_id%TYPE,
321 cp_course_cd IGS_EN_STDNT_PS_INTM.course_cd%TYPE,
322 cp_start_dt IGS_EN_STDNT_PS_INTM.start_dt%TYPE) IS
323 SELECT start_dt,
324 end_dt
325 FROM IGS_EN_STDNT_PS_INTM
326 WHERE person_id = cp_person_id AND
327 course_cd = cp_course_cd AND
328 start_dt <> cp_start_dt AND
329 TRUNC(logical_delete_date) = to_date('31-12-4712','DD-MM-YYYY');
330 BEGIN
331 -- This module validates that the student_course_intermssion
332 -- record being created or updated does not overlap with an
333 -- existing intermission record for the nominated
334 -- IGS_EN_STDNT_PS_ATT
335 p_message_name := null;
336 FOR c_sci_rec IN c_sci(p_person_id,
337 p_course_cd,
338 p_start_dt) LOOP
339 -- Validate the start date is not between an existing date range
340 IF (p_start_dt >= c_sci_rec.start_dt) AND
341 p_start_dt <= c_sci_rec.end_dt THEN
342 p_message_name := 'IGS_EN_IDO_STDT_BTWN_DTRNG';
343 RETURN FALSE;
344 END IF;
345 -- Validate the end date is not between an existing date range
346 IF (p_end_dt >= c_sci_rec.start_dt AND
347 p_end_dt <= c_sci_rec.end_dt) THEN
348 p_message_name := 'IGS_EN_IDO_ENDDT_BTWN_DTRNG';
349 RETURN FALSE;
350 END IF;
351 -- Validate the current dates do not overlap and entire exisitng date range
352 IF (p_start_dt <= c_sci_rec.start_dt AND
353 p_end_dt >= c_sci_rec.end_dt) THEN
354 p_message_name := 'IGS_EN_IDO_DT_OVERLAP_DTRNG';
355 RETURN FALSE;
356 END IF;
357 END LOOP;
358 RETURN TRUE;
359 EXCEPTION
360 WHEN OTHERS THEN
361 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
362 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCI.enrp_val_sci_ovrlp');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 END;
366 END enrp_val_sci_ovrlp;
367 --
368
369 END IGS_EN_VAL_SCI;
[Home] [Help]