1 PACKAGE BODY IGS_AS_VAL_ESVS AS
2 /* $Header: IGSAS21B.pls 115.5 2002/11/28 22:44:40 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn
7 -- removed
8 -------------------------------------------------------------------------------------------
9 -- Validate if a IGS_PE_PERSON is an active student.
10
11 --
12 -- Validate exam instance exists for the session and IGS_GR_VENUE.
13 FUNCTION ASSP_VAL_EI_VENUE(
14 p_exam_cal_type IN VARCHAR2 ,
15 p_exam_ci_sequence_number IN NUMBER ,
16 p_dt_alias IN VARCHAR2 ,
17 p_dai_sequence_number IN NUMBER ,
18 p_start_time IN DATE ,
19 p_end_time IN DATE ,
20 p_venue_cd IN VARCHAR2 ,
21 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
22 RETURN BOOLEAN AS
23 BEGIN -- assp_val_ei_venue
24 -- Validate there exists at least one exam instance
25 -- at a IGS_GR_VENUE for which the supervisor is being allocated.
26 DECLARE
27 v_start_time DATE;
28 v_flag VARCHAR2(1) ;
29 v_end_time DATE;
30 v_system_date DATE;
31 CURSOR c_ei IS
32 SELECT '1',ei.start_time,ei.end_time
33 FROM IGS_AS_EXAM_INSTANCE ei
34 WHERE ei.exam_cal_type = p_exam_cal_type AND
35 ei.exam_ci_sequence_number = p_exam_ci_sequence_number AND
36 ei.dt_alias = p_dt_alias AND
37 ei.dai_sequence_number = p_dai_sequence_number AND
38 ei.venue_cd = p_venue_cd;
39 v_x c_ei%ROWTYPE;
40 BEGIN
41 P_MESSAGE_NAME := NULL;
42 v_system_date := SYSDATE;
43 v_flag := 'F' ;
44 v_start_time := Igs_Ge_Gen_003.GENP_SET_TIME(p_start_time);
45 v_end_time := Igs_Ge_Gen_003.GENP_SET_TIME(p_end_time);
46 OPEN c_ei;
47 LOOP
48 FETCH c_ei INTO v_x;
49 EXIT WHEN c_ei%NOTFOUND ;
50 IF Igs_Ge_Gen_003.GENP_SET_TIME(v_x.start_time) = v_start_time AND
51 Igs_Ge_Gen_003.GENP_SET_TIME(v_x.end_time) = v_end_time THEN
52 v_flag := 'T' ;
53 EXIT;
54 END IF;
55 END LOOP;
56 CLOSE c_ei;
57 IF v_flag = 'F' THEN
58 P_MESSAGE_NAME := 'IGS_AS_NOEXAM_INSTANCE_EXIST';
59 RETURN FALSE;
60 ELSE
61 P_MESSAGE_NAME := NULL;
62 RETURN TRUE;
63 END IF;
64 END;
65 EXCEPTION
66 WHEN OTHERS THEN
67 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
68 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESVS.assp_val_ei_venue');
69 IGS_GE_MSG_STACK.ADD;
70 END assp_val_ei_venue;
71 --
72 -- Validate IGS_GR_VENUE is within the supervisor's exam IGS_AD_LOCATIONs
73 FUNCTION ASSP_VAL_ELS_VENUE(
74 p_person_id IN NUMBER ,
75 p_venue_cd IN VARCHAR2 ,
76 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
77 RETURN BOOLEAN AS
78 BEGIN --assp_val_els_venue
79 --Validate if the IGS_PE_PERSON is being allocated to supervise an exam
80 -- instance/IGS_GR_VENUE, then check that the IGS_GR_VENUE is within one of the IGS_PE_PERSONs
81 -- exam_location_supvsr records
82 DECLARE
83 v_els_exists VARCHAR2(1);
84 CURSOR c_els IS
85 SELECT 'X'
86 FROM IGS_AS_EXM_LOC_SPVSR els,
87 IGS_GR_VENUE ve
88 WHERE els.person_id = p_person_id AND
89 els.exam_location_cd = ve.exam_location_cd AND
90 ve.venue_cd = p_venue_cd;
91 BEGIN
92 --Set the default message number
93 P_MESSAGE_NAME := NULL;
94 --Determine if the IGS_GR_VENUE is within a persons supervisory IGS_AD_LOCATIONs
95 OPEN c_els;
96 FETCH c_els INTO v_els_exists;
97 IF (c_els%NOTFOUND) THEN
98 --Return a warning message indicating that the exam IGS_GR_VENUE is not within
99 --a IGS_AD_LOCATION that the IGS_PE_PERSON supervises.
100 CLOSE c_els;
101 P_MESSAGE_NAME := 'IGS_AS_VENUE_NOTWITHIN_SUPVEX';
102 RETURN FALSE;
103 END IF;
104 CLOSE c_els;
105 RETURN TRUE;
106 END;
107 EXCEPTION
108 WHEN OTHERS THEN
109 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
110 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESVS.assp_val_els_venue');
111 IGS_GE_MSG_STACK.ADD;
112 END assp_val_els_venue;
113 --
114 -- Validate if the exam supervisor type is not closed.
115
116 --
117 --
118 -- Validate if supervisor allocated different exam IGS_AD_LOCATION for same day.
119 FUNCTION ASSP_VAL_ESU_ESE_EL(
120 p_person_id IN NUMBER ,
121 p_exam_cal_type IN VARCHAR2 ,
122 p_exam_ci_sequence_number IN NUMBER ,
123 p_dt_alias IN VARCHAR2 ,
124 p_dai_sequence_number IN NUMBER ,
125 p_start_time IN DATE ,
126 p_end_time IN DATE ,
127 p_venue_cd IN VARCHAR2 ,
128 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
129 RETURN BOOLEAN AS
130 BEGIN -- assp_val_esu_ese_el
131 -- Validate the IGS_PE_PERSON is not being allocated to sessions at two different exam
132 -- locations on the same day
133 DECLARE
134 v_dt_alias_val DATE;
135 v_exam_location_cd IGS_GR_VENUE.exam_location_cd%TYPE;
136 v_check CHAR;
137 CURSOR c_venue IS
138 SELECT exam_location_cd
139 FROM IGS_GR_VENUE
140 WHERE venue_cd = p_venue_cd;
141 CURSOR c_eis_ve (
142 cp_dt_alias_val DATE,
143 cp_exam_location_cd IGS_GR_VENUE.exam_location_cd%TYPE) IS
144 SELECT 'x'
145 FROM IGS_AS_EXM_INS_SPVSR eis,
146 IGS_GR_VENUE ve
147 WHERE eis.person_id = p_person_id AND
148 IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
149 eis.dt_alias,
150 eis.dai_sequence_number,
151 eis.exam_cal_type,
152 eis.exam_ci_sequence_number) = cp_dt_alias_val AND
153 eis.venue_cd = ve.venue_cd AND
154 ve.exam_location_cd <> cp_exam_location_cd;
155 CURSOR c_esvs_ve (
156 cp_dt_alias_val DATE,
157 cp_exam_location_cd IGS_GR_VENUE.exam_location_cd%TYPE) IS
158 SELECT 'x'
159 FROM IGS_AS_EXM_SES_VN_SP esvs,
160 IGS_GR_VENUE ve
161 WHERE esvs.person_id = p_person_id AND
162 IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
163 esvs.dt_alias,
164 esvs.dai_sequence_number,
165 esvs.exam_cal_type,
166 esvs.exam_ci_sequence_number) = cp_dt_alias_val AND
167 esvs.venue_cd = ve.venue_cd AND
168 ve.exam_location_cd <> cp_exam_location_cd;
169 BEGIN
170 P_MESSAGE_NAME := NULL;
171 -- Get the date for the date alias instance value.
172 v_dt_alias_val := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
173 p_dt_alias,
174 p_dai_sequence_number,
175 p_exam_cal_type,
176 p_exam_ci_sequence_number);
177 -- Get the exam IGS_AD_LOCATION for the IGS_GR_VENUE.
178 OPEN c_venue;
179 FETCH c_venue INTO v_exam_location_cd;
180 CLOSE c_venue;
181 -- Determine if the IGS_PE_PERSON has been allocated to supervise an exam instance for
182 -- the same day at a different exam locations.
183 OPEN c_eis_ve (
184 v_dt_alias_val,
185 v_exam_location_cd);
186 FETCH c_eis_ve INTO v_check;
187 IF (c_eis_ve%FOUND) THEN
188 -- Return an warning message indicating that the IGS_PE_PERSON has been allocated
189 -- to different exam locations on the same day.
190 CLOSE c_eis_ve;
191 P_MESSAGE_NAME := 'IGS_AS_PRSN_ALLOC_DIFF_EXAM';
192 RETURN FALSE;
193 END IF;
194 CLOSE c_eis_ve;
195 -- Determine if the IGS_PE_PERSON has already been allocated to supervise at a IGS_GR_VENUE
196 -- for the same day at a different exam IGS_AD_LOCATION.
197 OPEN c_esvs_ve(
198 v_dt_alias_val,
199 v_exam_location_cd);
200 FETCH c_esvs_ve INTO v_check;
201 IF (c_esvs_ve%FOUND) THEN
202 -- Return an warning message indicating that the IGS_PE_PERSON has been allocated
203 -- to different exam locations on the same day.
204 CLOSE c_esvs_ve;
205 P_MESSAGE_NAME := 'IGS_AS_PRSN_ALLOC_DIFF_EXAM';
206 RETURN FALSE;
207 END IF;
208 CLOSE c_esvs_ve;
209 RETURN TRUE;
210 END;
211 EXCEPTION
212 WHEN OTHERS THEN
213 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
214 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESVS.assp_val_esu_ese_el');
215 IGS_GE_MSG_STACK.ADD;
216 END assp_val_esu_ese_el;
217 --
218 -- Validate if the supervisor limit exceeded for the session and IGS_GR_VENUE.
219 -- Supervisor cannot be allocated concurrent sessions at different IGS_GR_VENUEs
220 FUNCTION ASSP_VAL_ESU_ESE_VE(
221 p_person_id IN NUMBER ,
222 p_exam_cal_type IN VARCHAR2 ,
223 p_exam_ci_sequence_number IN NUMBER ,
224 p_dt_alias IN VARCHAR2 ,
225 p_dai_sequence_number IN NUMBER ,
226 p_start_time IN DATE ,
227 p_end_time IN DATE ,
228 p_override_start_time IN DATE ,
229 p_override_end_time IN DATE ,
230 p_venue_cd IN VARCHAR2 ,
231 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
232 RETURN BOOLEAN AS
233 gv_other_detail_1 VARCHAR2(255);
234 gv_other_detail_2 VARCHAR2(255);
235 BEGIN --assp_val_esu_ese_ve
236 --Validate the IGS_PE_PERSON cannot be allocated to supervise
237 --concurrent exam sessions at different IGS_GR_VENUEs
238 DECLARE
239 v_eis_exists VARCHAR2(1);
240 v_esvs_exists VARCHAR2(1);
241 v_dt_alias_val DATE;
242 v_start_time DATE;
243 v_end_time DATE;
244 CURSOR c_eis IS
245 SELECT 'X'
246 FROM IGS_AS_EXM_INS_SPVSR eis
247 WHERE person_id = p_person_id AND
248 IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
249 dt_alias,
250 dai_sequence_number,
251 exam_cal_type,
252 exam_ci_sequence_number) = v_dt_alias_val AND
253 ((Igs_Ge_Gen_003.GENP_SET_TIME(NVL(override_start_time, start_time))
254 BETWEEN v_start_time AND v_end_time OR
255 (Igs_Ge_Gen_003.GENP_SET_TIME( NVL(override_end_time, end_time))
256 BETWEEN v_start_time AND v_end_time)) OR
257 (Igs_Ge_Gen_003.GENP_SET_TIME( NVL(override_start_time, start_time)) <= v_start_time AND
258 Igs_Ge_Gen_003.GENP_SET_TIME( NVL(override_end_time, end_time)) >= v_end_time)) AND
259 venue_cd <> p_venue_cd;
260 CURSOR c_esvs IS
261 SELECT 'X'
262 FROM IGS_AS_EXM_SES_VN_SP esvs
263 WHERE person_id = p_person_id AND
264 IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
265 dt_alias,
266 dai_sequence_number,
267 exam_cal_type,
268 exam_ci_sequence_number) = v_dt_alias_val AND
269 ((Igs_Ge_Gen_003.GENP_SET_TIME(NVL(override_start_time, start_time))
270 BETWEEN v_start_time AND v_end_time OR
271 (Igs_Ge_Gen_003.GENP_SET_TIME( NVL(override_end_time, end_time))
272 BETWEEN v_start_time AND v_end_time)) OR
273 (Igs_Ge_Gen_003.GENP_SET_TIME( NVL(override_start_time, start_time)) <= v_start_time AND
274 Igs_Ge_Gen_003.GENP_SET_TIME( NVL(override_end_time, end_time)) >= v_end_time)) AND
275 venue_cd <> p_venue_cd;
276 BEGIN
277 --Set the default message number
278 P_MESSAGE_NAME := NULL;
279 --Get the date for the date alias instance value
280 v_dt_alias_val := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
281 p_dt_alias,
282 p_dai_sequence_number,
283 p_exam_cal_type,
284 p_exam_ci_sequence_number);
285 --Calculate which time parameter to use and set the date component
286 --to be consistent when comparing times.
287 --This is due to the date component varying when entering
288 --a time format within a form
289 v_start_time := Igs_Ge_Gen_003.GENP_SET_TIME(NVL(p_override_start_time, p_start_time));
290 v_end_time := Igs_Ge_Gen_003.GENP_SET_TIME( NVL(p_override_end_time, p_end_time));
291 OPEN c_eis;
292 FETCH c_eis INTO v_eis_exists;
293 IF (c_eis%FOUND) THEN
294 P_MESSAGE_NAME := 'IGS_AS_PRSN_ALLOC_DIFF_VENUE';
295 CLOSE c_eis;
296 RETURN FALSE;
297 END IF;
298 CLOSE c_eis;
299 --Determine if the IGS_PE_PERSON has been allocated to supervise
300 --at a different IGS_GR_VENUE for the same session.
301 OPEN c_esvs;
302 FETCH c_esvs INTO v_esvs_exists;
303 IF (c_esvs%FOUND) THEN
304 --Return an error message indicating that the IGS_PE_PERSON has been
305 --allocated to concurrent sessions at different venues.
306 P_MESSAGE_NAME := 'IGS_AS_PRSN_ALLOC_DIFF_VENUE';
307 CLOSE c_esvs;
308 RETURN FALSE;
309 END IF;
310 CLOSE c_esvs;
311 RETURN TRUE;
312 END;
313 EXCEPTION
314 WHEN OTHERS THEN
315 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
316 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESVS.assp_val_esu_ese_ve');
317 IGS_GE_MSG_STACK.ADD;
318 END assp_val_esu_ese_ve;
319 --
320
321 END Igs_As_Val_Esvs;
[Home] [Help]