1 PACKAGE BODY IGS_EN_VAL_SCSR AS
2 /* $Header: IGSEN65B.pls 115.5 2002/11/29 00:06:52 nsidana ship $ */
3 --
4 -- Validate the student course special requirement dates.
5 FUNCTION enrp_val_scsr_dates(
6 p_person_id IN NUMBER ,
7 p_course_cd IN VARCHAR2 ,
8 p_special_requirement_cd IN VARCHAR2 ,
9 p_completed_dt IN DATE ,
10 p_expiry_dt IN DATE ,
11 p_message_name OUT NOCOPY VARCHAR2)
12 RETURN BOOLEAN AS
13 BEGIN -- enrp_val_scsr_dates
14 -- * Validate that if the expiry_dt of the current record is NULL that another
15 -- IGS_PS_STDNT_SPL_REQ record does not does not exist for the same
16 -- IGS_EN_STDNT_PS_ATT with the same special_requirement_cd with a NULL
17 -- expiry_dt.
18 -- * Validate that the current record's completed_dt and expiry_dt do not
19 -- overlap with another IGS_PS_STDNT_SPL_REQ record for the same
20 -- IGS_EN_STDNT_PS_ATT with the same special_requirement_cd.
21 -- * Validation will fail if any of the following are true -
22 -- (a) The current completed date is between an existing date range.
23 -- (b) The current expiry date is between an existing date range.
24 -- (c) The current dates overlap an entire existing date range.
25 -- (d) The current dates overlap an existing completed date.
26 -- (e) The current open date range is before an existing completed
27 -- or expiry date.
28 DECLARE
29 v_dummy VARCHAR2(1);
30 v_ret_val BOOLEAN := TRUE;
31 CURSOR c_scsr1 IS
32 SELECT 'X'
33 FROM IGS_PS_STDNT_SPL_REQ scsr1
34 WHERE scsr1.person_id = p_person_id AND
35 scsr1.course_cd = p_course_cd AND
36 scsr1.special_requirement_cd = p_special_requirement_cd AND
37 scsr1.completed_dt <> p_completed_dt AND
38 scsr1.expiry_dt IS NULL;
39 CURSOR c_scsr2 IS
40 SELECT scsr2.completed_dt,
41 scsr2.expiry_dt
42 FROM IGS_PS_STDNT_SPL_REQ scsr2
43 WHERE scsr2.person_id = p_person_id AND
44 scsr2.course_cd = p_course_cd AND
45 scsr2.special_requirement_cd = p_special_requirement_cd AND
46 scsr2.completed_dt <> p_completed_dt;
47 BEGIN
48 -- Set the default message number
49 p_message_name := null;
50 -- 1. Check the passed parameters.
51 IF p_person_id IS NULL OR
52 p_course_cd IS NULL OR
53 p_special_requirement_cd IS NULL OR
54 p_completed_dt IS NULL THEN
55 p_message_name := null;
56 RETURN v_ret_val;
57 END IF;
58 -- 2. Check if p_expiry_dt is NULL and that no other matching records exist
59 -- with a NULL expiry_dt.
60 -- New message created for bug#2283458
61 OPEN c_scsr1;
62 FETCH c_scsr1 INTO v_dummy;
63 IF p_expiry_dt IS NULL THEN
64 IF (c_scsr1%FOUND) THEN
65 CLOSE c_scsr1;
66 p_message_name := 'IGS_EN_COMPLDT_OVERLAPS';
67 v_ret_val := FALSE;
68 RETURN v_ret_val;
69 END IF;
70 END IF;
71 CLOSE c_scsr1;
72 -- 3. Find all of the matching records accept for the record passed in.
73 -- 4. Loop through the matching records checking for records with
74 -- overlapping dates.
75 FOR v_scsr2_rec IN c_scsr2 LOOP
76 IF v_scsr2_rec.expiry_dt IS NOT NULL THEN
77 -- Validate (a),(b),(c),(e)
78 IF p_completed_dt >= v_scsr2_rec.completed_dt AND
79 p_completed_dt <= v_scsr2_rec.expiry_dt THEN
80 p_message_name := 'IGS_EN_COMPLDT_BTWN_STDT_COMD';
81 v_ret_val := FALSE;
82 END IF;
83 IF p_expiry_dt IS NOT NULL THEN
84 -- Validate (b),(c)
85 IF p_expiry_dt >= v_scsr2_rec.completed_dt AND
86 p_expiry_dt <= v_scsr2_rec.expiry_dt THEN
87 p_message_name := 'IGS_EN_EXPDT_BTWN_EXPDT_COMPL';
88 v_ret_val := FALSE;
89 END IF;
90 IF p_completed_dt <= v_scsr2_rec.completed_dt AND
91 p_expiry_dt >= v_scsr2_rec.expiry_dt THEN
92 p_message_name := 'IGS_EN_COMPLDT_EXPDT_ENCOMPAS';
93 v_ret_val := FALSE;
94 END IF;
95 ELSE
96 -- p_expiry_dt IS NULL and Validate (e)
97 IF p_completed_dt <= v_scsr2_rec.completed_dt OR
98 p_completed_dt <= v_scsr2_rec.expiry_dt THEN
99 p_message_name := 'IGS_EN_UNEXP_RECORD_OVERLAPS';
100 v_ret_val := FALSE;
101 END IF;
102 END IF;
103 ELSE
104 -- expiry_dt IS NULL and Validate (d)
105 IF p_completed_dt >= v_scsr2_rec.completed_dt OR
106 p_expiry_dt >= v_scsr2_rec.completed_dt THEN
107 p_message_name := 'IGS_EN_DT_OVERLAP_UNEXPIRED';
108 v_ret_val := FALSE;
109 END IF;
110 END IF;
111 END LOOP; -- c_scsr2
112 RETURN v_ret_val;
113 EXCEPTION
114 WHEN OTHERS THEN
115 IF (c_scsr1%ISOPEN) THEN
116 CLOSE c_scsr1;
117 END IF;
118 IF (c_scsr2%ISOPEN) THEN
119 CLOSE c_scsr2;
120 END IF;
121 RAISE;
122 END;
123 EXCEPTION
124 WHEN OTHERS THEN
125 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
126 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCSR.enrp_val_scsr_dates');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END enrp_val_scsr_dates;
130 --
131 -- Validate the student course special requirement completed date.
132 FUNCTION enrp_val_scsr_cmp_dt(
133 p_completed_dt IN DATE ,
134 p_message_name OUT NOCOPY VARCHAR2)
135 RETURN BOOLEAN AS
136 BEGIN -- enrp_val_scsr_cmp_dt
137 -- Validate thata IGS_PS_STDNT_SPL_REQ.completed_dt is less
138 -- than or equal to today's date/
139 DECLARE
140 CURSOR c_scsr IS
141 SELECT 'x'
142 FROM dual
143 WHERE SYSDATE < p_completed_dt;
144 v_c_scsr_found VARCHAR2(1) DEFAULT NULL;
145 BEGIN
146 -- initialise p_message_name
147 p_message_name := null;
148 -- Check p_completed_dt
149 IF p_completed_dt IS NULL THEN
150 RETURN TRUE;
151 END IF;
152 -- Check if the sysdate is less than or equal to p_completed_dt
153 OPEN c_scsr;
154 FETCH c_scsr INTO v_c_scsr_found;
155 IF (c_scsr%FOUND) THEN
156 CLOSE c_scsr;
157 p_message_name := 'IGS_EN_COMPLT_LE_TODAY_DATE';
158 RETURN FALSE;
159 END IF;
160 CLOSE c_scsr;
161 RETURN TRUE;
162 EXCEPTION
163 WHEN OTHERS THEN
164 IF (c_scsr%ISOPEN) THEN
165 CLOSE c_scsr;
166 END IF;
167 RAISE;
168 END;
169 EXCEPTION
170 WHEN OTHERS THEN
171 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
172 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCSR.enrp_val_scsr_cmp_dt');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END enrp_val_scsr_cmp_dt;
176 --
177 -- Validate the student course special requirement expiry date.
178 FUNCTION enrp_val_scsr_exp_dt(
179 p_completed_dt IN DATE ,
180 p_expiry_dt IN DATE ,
181 p_message_name OUT NOCOPY VARCHAR2)
182 RETURN BOOLEAN AS
183
184 BEGIN -- enrp_val_scsr_exp_dt
185 -- Validate that IGS_PS_STDNT_SPL_REQ.expiry_dt
186 -- if it is not NULL, it must be greater than or equal to the completed_dt
187 DECLARE
188 BEGIN
189 -- Set the default message number
190 p_message_name := null;
191 IF p_completed_dt IS NULL OR
192 p_expiry_dt IS NULL THEN
193 p_message_name := null;
194 RETURN TRUE;
195 END IF;
196 -- 2. Check that the p_expiry_dt is not less than the p_completed_dt.
197 IF p_expiry_dt < p_completed_dt THEN
198 p_message_name := 'IGS_EN_EXPDT_GE_COMPLDT';
199 RETURN FALSE;
200 END IF;
201 RETURN TRUE;
202 END;
203 EXCEPTION
204 WHEN OTHERS THEN
205 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
206 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCSR.enrp_val_scsr_exp_dt');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END enrp_val_scsr_exp_dt;
210 --
211 -- Validate the student course special requirement SCA status.
212 FUNCTION enrp_val_scsr_scas(
213 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
214 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
215 p_message_name OUT NOCOPY VARCHAR2)
216 RETURN BOOLEAN AS
217
218 BEGIN -- enrp_val_scsr_scas
219 -- Validate the IGS_EN_STDNT_PS_ATT.course_attempt_status is
220 -- ENROLLED, COMPLETED, INACTIVE or INTERMIT (not DISCONTIN,
221 -- DELETED, LAPSED or UNCONFIRM) before inserting, updating
222 -- or deleting IGS_PS_STDNT_SPL_REQ records.
223 DECLARE
224 CURSOR c_sca IS
225 SELECT course_attempt_status
226 FROM IGS_EN_STDNT_PS_ATT
227 WHERE person_id = p_person_id AND
228 course_cd = p_course_cd;
229 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
230 BEGIN
231 p_message_name := null;
232 -- Check parameters
233 IF p_person_id IS NULL OR p_course_cd IS NULL THEN
234 RETURN TRUE;
235 END IF;
236 -- Get the course_attempt_status for the specified
237 -- IGS_EN_STDNT_PS_ATT.
238 OPEN c_sca;
239 FETCH c_sca INTO v_course_attempt_status;
240 IF (c_sca%FOUND) THEN
241 IF v_course_attempt_status IN (
242 'DISCONTIN',
243 'DELETED',
244 'LAPSED',
245 'UNCONFIRM') THEN
246 CLOSE c_sca;
247 p_message_name := 'IGS_EN_STUDPRG_SPLREQ_NOTIUD';
248 RETURN FALSE;
249 END IF;
250 END IF;
251 CLOSE c_sca;
252 RETURN TRUE;
253 EXCEPTION
254 WHEN OTHERS THEN
255 IF (c_sca%ISOPEN) THEN
256 CLOSE c_sca;
257 END IF;
258 RAISE;
259 END;
260 EXCEPTION
261 WHEN OTHERS THEN
262 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
263 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCSR.enrp_val_scsr_scas');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END enrp_val_scsr_scas;
267 --
268 -- Validate the special requirement closed indicator.
269 FUNCTION enrp_val_srq_closed(
270 p_special_requirement_cd IN VARCHAR2 ,
271 p_message_name OUT NOCOPY VARCHAR2)
272 RETURN BOOLEAN AS
273 BEGIN -- enrp_val_srq_closed
274 -- Validate that the IGS_GE_SPL_REQ is not closed
275 DECLARE
276 CURSOR c_srq IS
277 SELECT 'x'
278 FROM IGS_GE_SPL_REQ srq
279 WHERE srq.special_requirement_cd = p_special_requirement_cd AND
280 srq.closed_ind = 'Y';
281 v_closed_ind VARCHAR2(1) DEFAULT NULL;
282 BEGIN
283 p_message_name := null;
284 -- Check p_special_requirement_cd
285 IF p_special_requirement_cd IS NULL THEN
286 RETURN TRUE;
287 END IF;
288 -- Check that the closed_ind <> 'Y'
289 OPEN c_srq;
290 FETCH c_srq INTO v_closed_ind;
291 IF (c_srq%FOUND) THEN
292 CLOSE c_srq;
293 p_message_name := 'IGS_EN_SPLREQ_CLOSED';
294 RETURN FALSE;
295 END IF;
296 CLOSE c_srq;
297 RETURN TRUE;
298 EXCEPTION
299 WHEN OTHERS THEN
300 IF (c_srq%ISOPEN) THEN
301 CLOSE c_srq;
302 END IF;
303 RAISE;
304 END;
305 EXCEPTION
306 WHEN OTHERS THEN
307 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
308 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SCSR.enrp_val_srq_closed');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END enrp_val_srq_closed;
312 --
313 END IGS_EN_VAL_SCSR;
[Home] [Help]