1 PACKAGE BODY IGS_EN_VAL_PCE AS
2 /* $Header: IGSEN50B.pls 115.5 2002/11/29 00:02:08 nsidana ship $ */
3 --
4 -- bug id : 1956374
5 -- sjadhav , 28-aug-2001
6 -- modified procedure ENRP_VAL_PEE_TABLE
7 -- added code from IGSEN60B
8 --
9 -- Validate that IGS_PE_PERSON doesn't already have an open crs exclusion.
10 FUNCTION enrp_val_pce_open(
11 p_person_id IN NUMBER ,
12 p_encumbrance_type IN VARCHAR2 ,
13 p_pen_start_dt IN DATE ,
14 p_s_encmb_effect_type IN VARCHAR2 ,
15 p_pee_start_dt IN DATE ,
16 p_course_cd IN VARCHAR2 ,
17 p_pce_start_dt IN DATE ,
18 p_message_name OUT NOCOPY VARCHAR2)
19 RETURN BOOLEAN AS
20
21 BEGIN -- enrp_val_pce_open
22 -- Validate that there are no other "open ended" pce records
23 -- for the nominated encumbrance effect type
24 DECLARE
25 v_check VARCHAR2(1);
26 v_ret_val BOOLEAN DEFAULT TRUE;
27 CURSOR c_person_course_exclusion IS
28 SELECT 'x'
29 FROM IGS_PE_COURSE_EXCL
30 WHERE
31 person_id = p_person_id AND
32 encumbrance_type = p_encumbrance_type AND
33 pen_start_dt = p_pen_start_dt AND
34 s_encmb_effect_type = p_s_encmb_effect_type AND
35 pee_start_dt = p_pee_start_dt AND
36 course_cd = p_course_cd AND
37 expiry_dt IS NULL AND
38 pce_start_dt <> p_pce_start_dt;
39 BEGIN
40 p_message_name := NULL;
41 OPEN c_person_course_exclusion;
42 FETCH c_person_course_exclusion INTO v_check;
43 IF (c_person_course_exclusion%FOUND) THEN
44 -- open record already exists
45 IF (p_s_encmb_effect_type = 'EXC_COURSE') THEN
46 p_message_name := 'IGS_EN_PRSN_PRG_EXCLUSION';
47 v_ret_val := FALSE;
48 ELSE
49 p_message_name := 'IGS_EN_PRSN_PRG_SUSPENSION';
50 v_ret_val := FALSE;
51 END IF;
52 END IF;
53 CLOSE c_person_course_exclusion;
54 RETURN v_ret_val;
55 END;
56 EXCEPTION
57 WHEN OTHERS THEN
58 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
59 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_pce_open');
60 IGS_GE_MSG_STACK.ADD;
61 App_Exception.Raise_Exception;
62
63
64 END enrp_val_pce_open;
65 --
66 -- Routine to process pce rowids in PL/SQL TABLE for the current commit.
67 FUNCTION enrp_prc_pce_rowids(
68 p_inserting IN BOOLEAN ,
69 p_updating IN BOOLEAN ,
70 p_deleting IN BOOLEAN ,
71 p_message_name IN OUT NOCOPY VARCHAR2 )
72 RETURN BOOLEAN AS
73 v_index BINARY_INTEGER;
74 r_person_course_exclusion IGS_PE_COURSE_EXCL%ROWTYPE;
75 BEGIN
76 -- Process saved rows.
77 FOR v_index IN 1..gv_table_index - 1
78 LOOP
79 BEGIN
80 SELECT *
81 INTO r_person_course_exclusion
82 FROM IGS_PE_COURSE_EXCL
83 WHERE ROWID = gt_rowid_table(v_index);
84 EXCEPTION
85 WHEN OTHERS THEN
86 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
87 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_prc_pce_rowids');
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90
91
92 END;
93 -- Validate for open ended person_crs_exclusion records.
94 IF r_person_course_exclusion.expiry_dt IS NULL THEN
95 IF IGS_EN_VAL_PCE.enrp_val_pce_open (
96 r_person_course_exclusion.person_id,
97 r_person_course_exclusion.encumbrance_type,
98 r_person_course_exclusion.pen_start_dt,
99 r_person_course_exclusion.s_encmb_effect_type,
100 r_person_course_exclusion.pee_start_dt,
101 r_person_course_exclusion.course_cd,
102 r_person_course_exclusion.pce_start_dt,
103 p_message_name) = FALSE THEN
104 RETURN FALSE;
105 END IF;
106 END IF;
107 END LOOP;
108 RETURN TRUE;
109 END enrp_prc_pce_rowids;
110 --
111 --
112 -- To validate the nominated date is not less than current date..
113 FUNCTION enrp_val_encmb_dt(
114 p_date IN DATE ,
115 p_message_name OUT NOCOPY VARCHAR2)
116 RETURN BOOLEAN AS
117
118 BEGIN -- Validate that encumbrance date (start or expiry)
119 -- is greater or equal to the current date
120 DECLARE
121 BEGIN
122 p_message_name := NULL;
123 -- Validate input parameters
124 IF (p_date IS NULL) THEN
125 RETURN TRUE;
126 END IF;
127 -- Validate that parameter date is not less than the current date
128 IF (TRUNC(p_date) < TRUNC(SYSDATE)) THEN
129 p_message_name := 'IGS_EN_DT_NOT_LT_CURR_DT';
130 RETURN FALSE;
131 ELSE
132 RETURN TRUE;
133 END IF;
134 END;
135 EXCEPTION
136 WHEN OTHERS THEN
137 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
138 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_encmb_dt');
139 IGS_GE_MSG_STACK.ADD;
140 App_Exception.Raise_Exception;
141
142
143 END enrp_val_encmb_dt;
144 --
145 -- To validate that expiry date is greater than or equal to start date.
146 FUNCTION enrp_val_strt_exp_dt(
147 p_start_dt IN DATE ,
148 p_expiry_dt IN DATE ,
149 p_message_name OUT NOCOPY VARCHAR2)
150 RETURN BOOLEAN AS
151 BEGIN
152 IF p_expiry_dt < p_start_dt THEN
153 p_message_name := 'IGS_EN_EXPDT_GE_STDT';
154 RETURN FALSE;
155 END IF;
156 p_message_name := NULL;
157 RETURN TRUE;
158 END enrp_val_strt_exp_dt;
159 --
160 -- Validate if a IGS_PS_COURSE must be discontinued before it can excluded.
161 FUNCTION enrp_val_crs_exclsn(
162 p_person_id IN NUMBER ,
163 p_course_cd IN VARCHAR2 ,
164 p_exclusion_start_dt IN DATE ,
165 p_message_name OUT NOCOPY VARCHAR2,
166 p_return_type OUT NOCOPY VARCHAR2 )
167 RETURN BOOLEAN AS
168 e_get_census_dt_alias_failed EXCEPTION;
169 BEGIN -- enrp_val_crs_exlcsn
170 -- Validate if a IGS_PS_COURSE must be discontinued before
171 -- a IGS_PS_COURSE exclusion can be applied. It is assumed that this
172 -- module only ever invoked with an enrolled IGS_PS_COURSE.
173 DECLARE
174 cst_warn CONSTANT VARCHAR2(1) := 'W';
175 cst_error CONSTANT VARCHAR2(1) := 'E';
176 v_census_dt_alias IGS_GE_S_GEN_CAL_CON.census_dt_alias%TYPE;
177 v_person_enrolled BOOLEAN DEFAULT FALSE;
178 v_validate_failed BOOLEAN DEFAULT FALSE;
179 v_check VARCHAR2(1);
180 CURSOR c_get_census_dt_alias IS
181 SELECT census_dt_alias
182 FROM IGS_GE_S_GEN_CAL_CON
183 WHERE s_control_num = 1;
184 CURSOR c_student_unit_attempt IS
185 SELECT cal_type,
186 ci_sequence_number
187 FROM IGS_EN_SU_ATTEMPT
188 WHERE person_id = p_person_id AND
189 course_cd = p_course_cd AND
190 unit_attempt_status = 'ENROLLED';
191 CURSOR c_check_census_dt (
192 cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
193 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE,
194 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE) IS
195 SELECT 'x'
196 FROM IGS_CA_DA_INST_V
197 WHERE cal_type = cp_cal_type AND
198 ci_sequence_number = cp_ci_sequence_number AND
199 dt_alias = cp_dt_alias AND
200 NVL(alias_val,IGS_GE_DATE.IGSDATE('1900/01/01'))
201 >= p_exclusion_start_dt;
202 BEGIN
203 p_message_name := NULL;
204 -- Validate the input parameters
205 IF (p_person_id IS NULL OR
206 p_course_cd IS NULL OR
207 p_exclusion_start_dt IS NULL) THEN
208 RETURN TRUE;
209 END IF;
210 OPEN c_get_census_dt_alias;
211 FETCH c_get_census_dt_alias INTO v_census_dt_alias;
212 IF (c_get_census_dt_alias%NOTFOUND) THEN
213 CLOSE c_get_census_dt_alias;
214 RAISE e_get_census_dt_alias_failed;
215 END IF;
216 CLOSE c_get_census_dt_alias;
217 FOR v_sua_rec IN c_student_unit_attempt LOOP
218 v_person_enrolled := TRUE;
219 -- Check if the IGS_PE_PERSON is enrolled in units which have a census date after
220 -- the exclusion start date.
221 OPEN c_check_census_dt(v_sua_rec.cal_type,
222 v_sua_rec.ci_sequence_number,
223 v_census_dt_alias);
224 FETCH c_check_census_dt INTO v_check;
225 IF (c_check_census_dt%FOUND) THEN
226 CLOSE c_check_census_dt;
227 v_validate_failed := TRUE;
228 EXIT;
229 END IF;
230 CLOSE c_check_census_dt;
231 END LOOP;
232 IF (v_person_enrolled = FALSE) THEN
233 -- IGS_PE_PERSON is not enrolled in any units within the IGS_PS_COURSE
234 p_message_name := 'IGS_EN_CANT_APPLY_ENCUM_EFFEC';
235 p_return_type := cst_error;
236 RETURN FALSE;
237 END IF;
238 IF (v_validate_failed = TRUE) THEN
239 -- These units must be discontinued before the exclusion can be applied
240 p_message_name := 'IGS_EN_DISCON_STUD_ENRL';
241 p_return_type := cst_error;
242 RETURN FALSE;
243 END IF;
244 -- The IGS_PE_PERSON is not enrolled in any units within the IGS_PS_COURSE which have a
245 -- census date after the exclusion start date
246 -- Exclusion can be applied, but a warning will be displayed about the
247 -- enrolled IGS_PS_UNITs
248 p_message_name := 'IGS_EN_PERS_ENRL_EXCL_COURSE';
249 p_return_type := cst_warn;
250 RETURN TRUE;
251 END;
252 EXCEPTION
253 WHEN e_get_census_dt_alias_failed THEN
254 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
255 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_crs_exclsn 1');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258
259
260 WHEN OTHERS THEN
261 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
262 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_crs_exclsn 2');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265
266
267 END enrp_val_crs_exclsn;
268 --
269 -- Validate the IGS_PS_COURSE code on the IGS_PE_PERSON IGS_PS_COURSE exclusion table.
270 FUNCTION enrp_val_pce_crs(
271 p_person_id IN NUMBER ,
272 p_course_cd IN VARCHAR2 ,
273 p_exclusion_start_dt IN DATE ,
274 p_message_name OUT NOCOPY VARCHAR2,
275 p_return_type OUT NOCOPY VARCHAR2 )
276 RETURN BOOLEAN AS
277
278 BEGIN -- enrp_val_pce_crs
279 -- Validate whether or not a IGS_PE_PERSON is enrolled
280 -- in a specified IGS_PS_COURSE and whether or not the IGS_PS_COURSE must
281 -- be discontinue before a IGS_PS_COURSE exclusion can be applied
282 DECLARE
283 v_check VARCHAR2(1);
284 v_result BOOLEAN;
285 CURSOR c_person_exist IS
286 SELECT 'x'
287 FROM IGS_EN_STDNT_PS_ATT
288 WHERE person_id = p_person_id AND
289 course_cd = p_course_cd AND
290 course_attempt_status IN
291 ('ENROLLED', 'INACTIVE', 'INTERMIT');
292 BEGIN
293 p_message_name := NULL;
294 -- Validate input parameters
295 IF (p_person_id IS NULL OR
296 p_course_cd IS NULL OR
297 p_exclusion_start_dt IS NULL) THEN
298 RETURN TRUE;
299 END IF;
300 -- Check if the IGS_PE_PERSON is enrolled in the specified IGS_PS_COURSE
301 OPEN c_person_exist;
302 FETCH c_person_exist INTO v_check;
303 IF (c_person_exist%FOUND) THEN
304 CLOSE c_person_exist;
305 -- validate if the IGS_PS_COURSE must be discontinued
306 v_result := IGS_EN_VAL_PCE.enrp_val_crs_exclsn(
307 p_person_id,
308 p_course_cd,
309 p_exclusion_start_dt,
310 p_message_name,
311 p_return_type);
312 RETURN v_result;
313 END IF;
314 CLOSE c_person_exist;
315 -- IGS_PE_PERSON is not enrolled in the specified IGS_PS_COURSE
316 RETURN TRUE;
317 END;
318 EXCEPTION
319 WHEN OTHERS THEN
320 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
321 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_pce_crs');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324
325
326 END enrp_val_pce_crs;
327 --
328 -- Validate the encumbrance effect table
329 FUNCTION enrp_val_pee_table(
330 p_effect_type IN VARCHAR2 ,
331 p_table_name IN VARCHAR2 ,
332 p_message_name OUT NOCOPY VARCHAR2)
333 RETURN BOOLEAN AS
334
335 BEGIN -- enrp_val_pee_table
336 -- Validate if records can be created in the nominated exclusion/requirement
337 -- table for the nominated s_encmb_effect_type
338 DECLARE
339 BEGIN
340 p_message_name := null;
341 IF (p_effect_type = 'EXC_CRS_GP' AND
342 p_table_name = 'IGS_PE_CRS_GRP_EXCL') THEN
343 RETURN TRUE;
344 ELSIF (p_effect_type IN('EXC_COURSE', 'SUS_COURSE') AND
345 p_table_name = 'IGS_PE_COURSE_EXCL') THEN
346 RETURN TRUE;
347 ELSIF (p_effect_type = 'EXC_CRS_U' AND
348 p_table_name = 'IGS_PE_PERS_UNT_EXCL') THEN
349 RETURN TRUE;
350 ELSIF (p_effect_type = 'RQRD_CRS_U' AND
351 p_table_name = 'IGS_PE_UNT_REQUIRMNT') THEN
352 RETURN TRUE;
353 ELSIF (p_effect_type = 'EXC_CRS_US' AND
354 p_table_name = 'IGS_PE_UNT_SET_EXCL') THEN
355 RETURN TRUE;
356 END IF;
357
358 -- Unable to create exclusion/requirement records for this
359 -- encumbrance effect type
360 p_message_name := 'IGS_EN_CANT_CREATE_REC_ENCUMB';
361 RETURN FALSE;
362 END;
363 EXCEPTION
364 WHEN OTHERS THEN
365 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
366 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_pee_table');
367 IGS_GE_MSG_STACK.ADD;
368 App_Exception.Raise_Exception;
369
370
371 END enrp_val_pee_table;
372 --
373 -- To validate that child date is not less than parent start date.
374 FUNCTION enrp_val_encmb_dts(
375 p_parent_start_dt IN DATE ,
376 p_child_start_dt IN DATE ,
377 p_message_name OUT NOCOPY VARCHAR2)
378 RETURN BOOLEAN AS
379
380 BEGIN -- enrp_val_encmb_dts
381 -- validate that a child encumbrace type record does not have
382 -- a start date less than of the parent record.
383 DECLARE
384 BEGIN
385 IF (p_child_start_dt < p_parent_start_dt) THEN
386 p_message_name := 'IGS_EN_CANT_SET_START_DATE';
387 RETURN FALSE;
388 END IF;
389 p_message_name := NULL;
390 RETURN TRUE;
391 END;
392 EXCEPTION
393 WHEN OTHERS THEN
394 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
395 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCE.enrp_val_encmb_dts');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398
399
400 END enrp_val_encmb_dts;
401 END IGS_EN_VAL_PCE;
[Home] [Help]