1 PACKAGE BODY IGS_AS_VAL_ESE AS
2 /* $Header: IGSAS19B.pls 115.5 2002/11/28 22:44:11 nsidana ship $ */
3
4
5 --
6 -- To validate the uniqueness of the exam session number
7 FUNCTION ASSP_VAL_ESE_NUM(
8 p_exam_cal_type IN VARCHAR2 ,
9 p_exam_ci_sequence_number IN NUMBER ,
10 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
11 RETURN BOOLEAN AS
12
13 BEGIN -- assp_val_ese_num
14 -- Validate the exam session number for an entire examination.
15 -- The number must be unique across the period
16 DECLARE
17 v_check CHAR;
18 v_ret_val BOOLEAN DEFAULT TRUE;
19 CURSOR c_es IS
20 SELECT dt_alias,
21 dai_sequence_number,
22 start_time,
23 end_time,
24 exam_session_number
25 FROM IGS_AS_EXAM_SESSION es
26 WHERE exam_cal_type = p_exam_cal_type AND
27 exam_ci_sequence_number = p_exam_ci_sequence_number;
28 CURSOR c_es1 (
29 cp_dt_alias IGS_AS_EXAM_SESSION.dt_alias%TYPE,
30 cp_dai_sequence_number IGS_AS_EXAM_SESSION.dai_sequence_number%TYPE,
31 cp_start_time IGS_AS_EXAM_SESSION.start_time%TYPE,
32 cp_end_time IGS_AS_EXAM_SESSION.end_time%TYPE,
33 cp_exam_session_number IGS_AS_EXAM_SESSION.exam_session_number%TYPE) IS
34 SELECT 'x'
35 FROM IGS_AS_EXAM_SESSION es
36 WHERE es.exam_cal_type = p_exam_cal_type AND
37 es.exam_ci_sequence_number = p_exam_ci_sequence_number AND
38 (dt_alias <> cp_dt_alias OR
39 dai_sequence_number <> cp_dai_sequence_number OR
40 start_time <> cp_start_time OR
41 end_time <> cp_end_time) AND
42 exam_session_number = cp_exam_session_number;
43 BEGIN
44 P_MESSAGE_NAME := NULL;
45 FOR v_es_rec IN c_es LOOP
46 OPEN c_es1 (
47 v_es_rec.dt_alias,
48 v_es_rec.dai_sequence_number,
49 v_es_rec.start_time,
50 v_es_rec.end_time,
51 v_es_rec.exam_session_number);
52 FETCH c_es1 INTO v_check;
53 IF (c_es1%FOUND) THEN
54 CLOSE c_es1;
55 P_MESSAGE_NAME := 'IGS_AS_MORE_THAN_ONE_SESSION';
56 v_ret_val := FALSE;
57 EXIT;
58 END IF;
59 CLOSE c_es1;
60 END LOOP;
61 RETURN v_ret_val;
62 END;
63 EXCEPTION
64 WHEN OTHERS THEN
65 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
66 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESE.assp_val_ese_num');
67 IGS_GE_MSG_STACK.ADD;
68 END assp_val_ese_num;
69 --
70 -- To validate for overlap in start/end times of exam sessions
71 FUNCTION ASSP_VAL_ESE_OVRLP(
72 p_exam_cal_type IN VARCHAR2 ,
73 p_exam_ci_sequence_number IN NUMBER ,
74 p_dt_alias IN VARCHAR2 ,
75 p_dai_sequence_number IN NUMBER ,
76 p_start_time IN DATE ,
77 p_end_time IN DATE ,
78 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
79 RETURN BOOLEAN AS
80
81 BEGIN -- assp_val_ese_ovrlp
82 -- Validate whether the examination session overlaps times with another session
83 -- within the same examination calendar.
84 -- This routine only returns warning, as it is seen as possible in unusual
85 -- situations
86 DECLARE
87 v_alias_val IGS_AS_EXAM_SESSION_V.alias_val%TYPE;
88 CURSOR c_daiv(
89 cp_exam_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
90 cp_exam_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
91 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE,
92 cp_dai_sequence_number IGS_CA_DA_INST_V.sequence_number%TYPE) IS
93 SELECT daiv.alias_val
94 FROM IGS_CA_DA_INST_V daiv
95 WHERE daiv.cal_type = cp_exam_cal_type AND
96 daiv.ci_sequence_number = cp_exam_ci_sequence_number AND
97 daiv.dt_alias = cp_dt_alias AND
98 daiv.sequence_number = cp_dai_sequence_number;
99 CURSOR c_esv (
100 cp_exam_cal_type IGS_AS_EXAM_SESSION_V.exam_cal_type%TYPE,
101 cp_exam_ci_sequence_number IGS_AS_EXAM_SESSION_V.exam_ci_sequence_number%TYPE,
102 cp_alias_val IGS_AS_EXAM_SESSION_V.alias_val%TYPE) IS
103 SELECT *
104 FROM IGS_AS_EXAM_SESSION_V esv
105 WHERE esv.exam_cal_type = cp_exam_cal_type AND
106 esv.exam_ci_sequence_number = cp_exam_ci_sequence_number AND
107 esv.alias_val = cp_alias_val;
108 BEGIN
109 P_MESSAGE_NAME := NULL;
110 -- Validate parameters
111 IF(p_exam_cal_type IS NULL OR
112 p_exam_ci_sequence_number IS NULL OR
113 p_dt_alias IS NULL OR
114 p_dai_sequence_number IS NULL OR
115 p_start_time IS NULL OR
116 p_end_time IS NULL) THEN
117 RETURN TRUE;
118 END IF;
119 -- Get the session date of the parameter session by querying
120 -- the date alias instance view
121 OPEN c_daiv(
122 p_exam_cal_type,
123 p_exam_ci_sequence_number,
124 p_dt_alias,
125 p_dai_sequence_number);
126 FETCH c_daiv INTO v_alias_val;
127 IF(c_daiv%NOTFOUND) THEN
128 CLOSE c_daiv;
129 RETURN TRUE;
130 END IF;
131 CLOSE c_daiv;
132 -- Loop through IGS_AS_EXAM_SESSION_V records validating each record for
133 -- time overlaps
134 FOR v_esv_rec IN c_esv(
135 p_exam_cal_type,
136 p_exam_ci_sequence_number,
137 v_alias_val) LOOP
138 IF(v_esv_rec.dt_alias = p_dt_alias AND
139 v_esv_rec.dai_sequence_number = p_dai_sequence_number AND
140 v_esv_rec.start_time = p_start_time AND
141 v_esv_rec.end_time = p_end_time) THEN
142 -- Do not validate against the record passed in
143 NULL;
144 ELSE
145 IF(
146 (p_start_time >= v_esv_rec.start_time AND
147 p_start_time <= v_esv_rec.end_time) OR
148 (p_end_time >= v_esv_rec.start_time AND
149 p_end_time <= v_esv_rec.end_time) OR
150 (p_start_time <= v_esv_rec.start_time AND
151 p_end_time >= v_esv_rec.end_time)) THEN
152 P_MESSAGE_NAME := 'IGS_AS_SESSION_TIMES_OVERLAP';
153 RETURN FALSE;
154 END IF;
155 END IF;
156 END LOOP;
157 RETURN TRUE;
158 END;
159 EXCEPTION
160 WHEN OTHERS THEN
161 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
162 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESE.assp_val_ese_ovrlp');
163 IGS_GE_MSG_STACK.ADD;
164 END assp_val_ese_ovrlp;
165 --
166 -- Validate the IGS_AS_EXAM_SESSION calendar instance
167 FUNCTION ASSP_VAL_ESE_CI(
168 p_cal_type IN VARCHAR2 ,
169 p_sequence_number IN NUMBER ,
170 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
171 RETURN BOOLEAN AS
172
173 BEGIN -- assp_val_ese_ci
174 -- Validate the calendar instance being linked to an IGS_AS_EXAM_SESSION record.
175 -- Check for :
176 -- . Calendar type must have SI_CA_S_CA_CAT of 'EXAM'
177 -- . Calendar instance must have s_cal_status of 'ACTIVE'
178 DECLARE
179 CURSOR c_cat IS
180 SELECT s_cal_cat
181 FROM IGS_CA_TYPE
182 WHERE cal_type = p_cal_type;
183 v_cat_rec c_cat%ROWTYPE;
184 CURSOR c_cs IS
185 SELECT s_cal_status
186 FROM IGS_CA_INST ci,
187 IGS_CA_STAT cs
188 WHERE ci.cal_type = p_cal_type AND
189 ci.sequence_number = p_sequence_number AND
190 cs.cal_status = ci.cal_status;
191 v_cs_rec c_cs%ROWTYPE;
192 BEGIN
193 -- Set the default message number
194 P_MESSAGE_NAME := NULL;
195 -- 1. Check that calendar type is an examination calendar.
196 OPEN c_cat;
197 FETCH c_cat INTO v_cat_rec;
198 IF (c_cat%NOTFOUND) THEN
199 CLOSE c_cat;
200 -- Calendar type is not valid ; the routine is not applicable.
201 P_MESSAGE_NAME := NULL;
202 RETURN TRUE;
203 ELSE
204 CLOSE c_cat;
205 IF (v_cat_rec.s_cal_cat <> 'EXAM') THEN
206 P_MESSAGE_NAME := 'IGS_AS_EXAMSESSIONS_LINKS';
207 RETURN FALSE;
208 END IF;
209 END IF;
210 -- 2. Check that calendar instance is active
211 OPEN c_cs;
212 FETCH c_cs INTO v_cs_rec;
213 IF (c_cs%NOTFOUND) THEN
214 CLOSE c_cs;
215 -- The calendar instance is not valid ; the routine is not applicable.
216 P_MESSAGE_NAME := NULL;
217 RETURN TRUE;
218 ELSE
219 CLOSE c_cs;
220 IF (v_cs_rec.s_cal_status <> 'ACTIVE') THEN
221 P_MESSAGE_NAME := 'IGS_AS_EXAMCAL_NOT_ACTIVE';
222 RETURN FALSE;
223 END IF;
224 END IF;
225 RETURN TRUE;
226 END;
227 EXCEPTION
228 WHEN OTHERS THEN
229 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
230 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESE.assp_val_ese_ci');
231 IGS_GE_MSG_STACK.ADD;
232 END assp_val_ese_ci;
233 --
234 -- Compare time component of two dates and start time is before end time.
235 FUNCTION GENP_VAL_STRT_END_TM(
236 p_start_time IN DATE ,
237 p_end_time IN DATE ,
238 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
239 RETURN BOOLEAN AS
240
241 BEGIN
242 DECLARE
243 v_start_time DATE;
244 v_end_time DATE;
245 BEGIN
246 --- Set the default message number
247 P_MESSAGE_NAME := NULL;
248 --- Make sure the only comparing the time component and that the dates
249 --- are identical. (01/01/1900 is an arbitrary date)
250 v_start_time := IGS_GE_DATE.IGSDATE( '1900/01/01 ' || SUBSTR(IGS_GE_DATE.IGSCHARDT(p_start_time),12));
251 v_end_time := IGS_GE_DATE.IGSDATE( '1900/01/01 ' || SUBSTR(IGS_GE_DATE.IGSCHARDT(p_end_time),12));
252 IF v_end_time <= v_start_time THEN
253 P_MESSAGE_NAME := 'IGS_GE_ST_TIME_LT_END_TIME';
254 RETURN FALSE;
255 ELSE
256 RETURN TRUE;
257 END IF;
258 END;
259 EXCEPTION
260 WHEN OTHERS THEN
261 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
262 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_ESE.GENP_VAL_STRT_END_TM');
263 IGS_GE_MSG_STACK.ADD;
264 END GENP_VAL_STRT_END_TM;
265 END IGS_AS_VAL_ESE;
[Home] [Help]