1 PACKAGE BODY IGS_AS_VAL_EIS AS
2 /* $Header: IGSAS17B.pls 115.5 2002/11/28 22:43:44 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
7 -------------------------------------------------------------------------------------------
8 --
9 -- Validate if a person is an active student.
10 FUNCTION ASSP_VAL_ACTV_STDNT(
11 p_person_id IN NUMBER ,
12 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
13 RETURN BOOLEAN AS
14
15 BEGIN -- assp_val_actv_stdnt
16 -- Validates if the person is an active student
17 DECLARE
18 cst_lapsed CONSTANT VARCHAR2(6) := 'LAPSED';
19 cst_enrolled CONSTANT VARCHAR2(8) := 'ENROLLED';
20 cst_intermit CONSTANT VARCHAR2(8) := 'INTERMIT';
21 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
22 v_rec_found BOOLEAN DEFAULT FALSE;
23 CURSOR c_sca (
24 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE) IS
25 SELECT 'x'
26 FROM IGS_EN_STDNT_PS_ATT sca
27 WHERE sca.person_id = cp_person_id AND
28 sca.course_attempt_status IN (cst_enrolled, cst_intermit,
29 cst_lapsed, cst_inactive);
30 BEGIN
31 P_MESSAGE_NAME := NULL;
32 FOR v_sca_rec IN c_sca(
33 p_person_id) LOOP
34 v_rec_found := TRUE;
35 END LOOP;
36 IF(v_rec_found = TRUE) THEN
37 P_MESSAGE_NAME := 'IGS_AS_PRSN_AN_ACTIVE_STUD';
38 RETURN FALSE;
39 END IF;
40 RETURN TRUE;
41 END;
42 EXCEPTION
43 WHEN OTHERS THEN
44 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
45 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_EIS.ASSP_VAL_ACTV_STDNT');
46 IGS_GE_MSG_STACK.ADD;
47 END assp_val_actv_stdnt;
48 -- Validate if a person is an active student.
49
50 --
51 -- Validate venue is within the supervisor's exam exam locations
52
53 -- Validate if more than one person incharge at a session and venue.
54 FUNCTION ASSP_VAL_ESE_INCHRG(
55 p_person_id IN NUMBER ,
56 p_exam_cal_type IN VARCHAR2 ,
57 p_exam_ci_sequence_number IN NUMBER ,
58 p_dt_alias IN VARCHAR2 ,
59 p_dai_sequence_number IN NUMBER ,
60 p_start_time IN DATE ,
61 p_end_time IN DATE ,
62 p_venue_cd IN VARCHAR2 ,
63 p_exam_supervisor_type IN VARCHAR2 ,
64 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
65 RETURN BOOLEAN AS
66
67 BEGIN -- assp_val_ese_inchrg
68 -- Validate that not more than one person has been allocated to be in-charge
69 -- for a particular session at a venue.
70 DECLARE
71 v_start_time DATE;
72 v_end_time DATE;
73 v_exam_inst_count NUMBER(5);
74 v_venue_count NUMBER(5);
75 CURSOR c_check_not_charge IS
76 SELECT 'x'
77 FROM IGS_AS_EXM_SPRVSRTYP
78 WHERE exam_supervisor_type = p_exam_supervisor_type AND
79 in_charge_ind = 'N';
80 v_not_charge_exist VARCHAR2(1);
81 CURSOR c_exam_inst_count IS
82 SELECT count(distinct eis.person_id)
83 FROM IGS_AS_EXM_INS_SPVSR eis,
84 IGS_AS_EXM_SUPRVISOR esu,
85 IGS_AS_EXM_SPRVSRTYP est
86 WHERE eis.person_id <> p_person_id AND
87 eis.exam_cal_type = p_exam_cal_type AND
88 eis.exam_ci_sequence_number = p_exam_ci_sequence_number AND
89 eis.dt_alias = p_dt_alias AND
90 eis.dai_sequence_number = p_dai_sequence_number AND
91 IGS_GE_GEN_003.GENP_SET_TIME(eis.start_time) = v_start_time AND
92 IGS_GE_GEN_003.GENP_SET_TIME(eis.end_time) = v_end_time AND
93 eis.venue_cd = p_venue_cd AND
94 eis.person_id = esu.person_id AND
95 esu.exam_supervisor_type = est.exam_supervisor_type AND
96 est.in_charge_ind = 'Y';
97 CURSOR c_venue_count IS
98 SELECT count(esvs.person_id)
99 FROM IGS_AS_EXM_SES_VN_SP esvs,
100 IGS_AS_EXM_SUPRVISOR esu,
101 IGS_AS_EXM_SPRVSRTYP est
102 WHERE esvs.person_id <> p_person_id AND
103 esvs.exam_cal_type = p_exam_cal_type AND
104 esvs.exam_ci_sequence_number = p_exam_ci_sequence_number AND
105 esvs.dt_alias = p_dt_alias AND
106 esvs.dai_sequence_number = p_dai_sequence_number AND
107 IGS_GE_GEN_003.GENP_SET_TIME(esvs.start_time) = v_start_time AND
108 IGS_GE_GEN_003.GENP_SET_TIME(esvs.end_time) = v_end_time AND
109 esvs.venue_cd = p_venue_cd AND
110 esvs.person_id = esu.person_id AND
111 esu.exam_supervisor_type = est.exam_supervisor_type AND
112 est.in_charge_ind = 'Y';
113 BEGIN
114 -- Set the default message number
115 P_MESSAGE_NAME := NULL;
116 -- Check if the person being allocated to the session/venue is to be
117 -- in-charge. If person not being allocated as in-charge, then no need
118 -- to validate further, then return successfully.
119 OPEN c_check_not_charge;
120 FETCH c_check_not_charge INTO v_not_charge_exist;
121 IF c_check_not_charge%FOUND THEN
122 CLOSE c_check_not_charge;
123 RETURN TRUE;
124 END IF;
125 CLOSE c_check_not_charge;
126 -- Check if more than one supervisor to a session and venue has been allocated
127 -- as being 'in-charge'.
128 v_start_time := IGS_GE_GEN_003.GENP_SET_TIME(p_start_time);
129 v_end_time := IGS_GE_GEN_003.GENP_SET_TIME(p_end_time);
130 -- Determine the distinct number of supervisors already allocated to the
131 -- session/venu within the exam instance that are 'in-charge'
132 OPEN c_exam_inst_count;
133 FETCH c_exam_inst_count INTO v_exam_inst_count;
134 CLOSE c_exam_inst_count;
135 -- Check if person already allocated as being incharge.
136 IF NVL(v_exam_inst_count, 0) > 0 THEN
137 P_MESSAGE_NAME := 'IGS_AS_SUPV_EXISTS_NOMINATED';
138 RETURN FALSE;
139 END IF;
140 -- Determine the number of supervisors already allocated to the venue for the
141 -- session that are 'in-charge'.
142 OPEN c_venue_count;
143 FETCH c_venue_count INTO v_venue_count;
144 CLOSE c_venue_count;
145 IF NVL(v_venue_count, 0) > 0 THEN
146 P_MESSAGE_NAME := 'IGS_AS_SUPV_EXISTS_NOMINATED';
147 RETURN FALSE;
148 END IF;
149 RETURN TRUE;
150 END;
151 EXCEPTION
152 WHEN OTHERS THEN
153 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
154 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_EIS.ASSP_VAL_ESE_INCHRG');
155 IGS_GE_MSG_STACK.ADD;
156
157 END assp_val_ese_inchrg;
158 --
159 -- Validate if the exam supervisor type is not closed.
160
161 --
162 -- Validate if person allocated as incharge when not normally incharge.
163 FUNCTION ASSP_VAL_EST_INCHRG(
164 p_person_id IN NUMBER ,
165 p_exam_supervisor_type IN VARCHAR2 ,
166 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
167 RETURN BOOLEAN AS
168
169 BEGIN --assp_val_est_inchrg
170 --Validate the Exam Supervisor Type in-charge indicator. This routine will be
171 -- called when overiding the exam supervisor type for an
172 -- IGS_AS_EXM_INS_SPVSR or IGS_AS_EXM_SES_VN_SP. If the person is being
173 -- made an in-charge supervisor when they are normally are not in-charge,
174 -- then return a warning message.
175 DECLARE
176 v_est_in_charge_ind IGS_AS_EXM_SPRVSRTYP.in_charge_ind%TYPE;
177 v_in_charge_ind IGS_AS_EXM_SPRVSRTYP.in_charge_ind%TYPE;
178 CURSOR c_est IS
179 SELECT est.in_charge_ind
180 FROM IGS_AS_EXM_SUPRVISOR esu,
181 IGS_AS_EXM_SPRVSRTYP est
182 WHERE esu.person_id = p_person_id AND
183 esu.exam_supervisor_type = est.exam_supervisor_type;
184 CURSOR c_est2 IS
185 SELECT est.in_charge_ind
186 FROM IGS_AS_EXM_SPRVSRTYP est
187 WHERE exam_supervisor_type = p_exam_supervisor_type;
188 BEGIN
189 --Set the default message number
190 P_MESSAGE_NAME := NULL;
191 --Select the persons current type and in_charge indicator
192 OPEN c_est;
193 FETCH c_est INTO v_est_in_charge_ind;
194 CLOSE c_est;
195 --Select the in-charge indicator of the exam_supervisor_type that will be
196 -- used to overide the persons current type
197 OPEN c_est2;
198 FETCH c_est2 INTO v_in_charge_ind;
199 --This situation should never happen it is really validating RI constraints.
200 --As it is only a warning, return successfully.
201 IF (c_est2%NOTFOUND) THEN
202 CLOSE c_est2;
203 RETURN TRUE;
204 END IF;
205 CLOSE c_est2;
206 --If the person normally isnt in_charge and is being given a supervisory
207 --position as in-charge, then return a warning message.
208 IF (v_in_charge_ind = 'Y' AND
209 v_est_in_charge_ind = 'N') THEN
210 P_MESSAGE_NAME := 'IGS_AS_PRSN_NOTHAVE_SUPVTYPE';
211 RETURN FALSE;
212 END IF;
213 RETURN TRUE;
214 END;
215 EXCEPTION
216 WHEN OTHERS THEN
217 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
218 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_EIS.assp_val_est_inchrg');
219 IGS_GE_MSG_STACK.ADD;
220 END assp_val_est_inchrg;
221 --
222 --
223 -- Validate if the supervisor limit exceeded for the session and venue.
224 FUNCTION ASSP_VAL_ESU_ESE_LMT(
225 p_person_id IN NUMBER ,
226 p_exam_cal_type IN VARCHAR2 ,
227 p_exam_ci_sequence_number IN NUMBER ,
228 p_dt_alias IN VARCHAR2 ,
229 p_dai_sequence_number IN NUMBER ,
230 p_start_time IN DATE ,
231 p_end_time IN DATE ,
232 p_venue_cd IN VARCHAR2 ,
233 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
234 RETURN BOOLEAN AS
235
236 BEGIN -- assp_val_esu_ese_lmt
237 -- Validate that adding the person to a session at a venue will cause
238 -- the venue.supervisor_lit to be exceeded.
239 DECLARE
240 v_start_time IGS_AS_EXAM_SESSION.start_time%TYPE;
241 v_end_time IGS_AS_EXAM_SESSION.end_time%TYPE;
242 v_supervisor_count NUMBER(5);
243 v_venue_count NUMBER(5);
244 v_supervisor_limit IGS_GR_VENUE.supervisor_limit%TYPE;
245 CURSOR c_sprvsr_cnt (
246 cp_start_time IGS_AS_EXAM_SESSION.start_time%TYPE,
247 cp_end_time IGS_AS_EXAM_SESSION.end_time%TYPE) IS
248 SELECT COUNT(DISTINCT person_id)
249 FROM IGS_AS_EXM_INS_SPVSR
250 WHERE exam_cal_type = p_exam_cal_type AND
251 exam_ci_sequence_number = p_exam_ci_sequence_number AND
252 dt_alias = p_dt_alias AND
253 dai_sequence_number = p_dai_sequence_number AND
254 IGS_GE_GEN_003.GENP_SET_TIME(start_time) = cp_start_time AND
255 IGS_GE_GEN_003.GENP_SET_TIME(end_time) = cp_end_time AND
256 venue_cd = p_venue_cd;
257 CURSOR c_venue_cnt (
258 cp_start_time IGS_AS_EXAM_SESSION.start_time%TYPE,
259 cp_end_time IGS_AS_EXAM_SESSION.end_time%TYPE) IS
260 SELECT COUNT(person_id) -- v_venue_count
261 FROM IGS_AS_EXM_SES_VN_SP
262 WHERE exam_cal_type = p_exam_cal_type AND
263 exam_ci_sequence_number = p_exam_ci_sequence_number AND
264 dt_alias = p_dt_alias AND
265 dai_sequence_number = p_dai_sequence_number AND
266 IGS_GE_GEN_003.GENP_SET_TIME(start_time) = cp_start_time AND
267 IGS_GE_GEN_003.GENP_SET_TIME(end_time) = cp_end_time AND
268 venue_cd = p_venue_cd;
269 CURSOR c_venue IS
270 SELECT supervisor_limit
271 FROM IGS_GR_VENUE
272 WHERE venue_cd = p_venue_cd;
273 BEGIN
274 P_MESSAGE_NAME := NULL;
275 -- Check if the allocation of a supervisor to a session and venue will cause
276 -- the venue.supervisor_limit to be exceeded.
277 v_start_time := IGS_GE_GEN_003.GENP_SET_TIME(
278 p_start_time);
279 v_end_time := IGS_GE_GEN_003.GENP_SET_TIME(
280 p_end_time);
281 -- Determine the distinct number of supervisors already allocated to the
282 -- session/venu within the exam instance
283 OPEN c_sprvsr_cnt(
284 v_start_time,
285 v_end_time);
286 FETCH c_sprvsr_cnt INTO v_supervisor_count;
287 CLOSE c_sprvsr_cnt;
288 -- Determine the number of supervisors already allocated to the venue for the
289 -- session.
290 OPEN c_venue_cnt(
291 v_start_time,
292 v_end_time);
293 FETCH c_venue_cnt INTO v_venue_count;
294 CLOSE c_venue_cnt;
295 -- Add the count to the total and increment by 1 to include the person about to
296 -- be added.
297 v_supervisor_count := v_supervisor_count + v_venue_count + 1;
298 -- Determine the supervisor limit for the venue.
299 OPEN c_venue;
300 FETCH c_venue INTO v_supervisor_limit;
301 CLOSE c_venue;
302 -- Check if the limit has been exceeded.
303 -- Note: If the selected limit is NULL then no limit exists.
304 IF v_supervisor_count > NVL(v_supervisor_limit, v_supervisor_count) THEN
305 P_MESSAGE_NAME := 'IGS_AS_SUPV_LIMIT_EXCEEDS';
306 RETURN FALSE;
307 END IF;
308 Return TRUE;
309 END;
310 EXCEPTION
311 WHEN OTHERS THEN
312 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
313 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_EIS.assp_val_esu_ese_lmt');
314 IGS_GE_MSG_STACK.ADD;
315 END assp_val_esu_ese_lmt;
316 --
317 -- Supervisor cannot be allocated concurrent sessions at different venues
318 --
319
320 END IGS_AS_VAL_EIS;
[Home] [Help]