1 PACKAGE BODY IGS_FI_VAL_CFAR AS
2 /* $Header: IGSFI12B.pls 120.0 2005/06/01 22:43:45 appldev noship $ */
3 /*----------------------------------------------------------------------------
4 || Created By :
5 || Created On :
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || (reverse chronological order - newest change first)
11 || vvutukur 28-Jan-2004 Enh#3167098.FICR112 Build.Modified finp_val_cfar_ins.
12 || vvutukur 20-May-2002 modified findp_val_cfar_ins,finp_val_ft_closed
13 || to remove upper check on fee_type.bug#2344826.
14 ----------------------------------------------------------------------------*/
15 --
16 -- Ensure S_FEE_TYPE is 'OTHER' and S_FEE_TRIGGER_CAT is not 'INSTITUTN'
17 FUNCTION finp_val_cfar_ins(
18 p_person_id IN NUMBER ,
19 p_course_cd IN VARCHAR2 ,
20 p_fee_type IN VARCHAR2 ,
21 p_message_name OUT NOCOPY VARCHAR2 )
22 RETURN BOOLEAN AS
23 /*----------------------------------------------------------------------------
24 || Created By :
25 || Created On :
26 || Purpose :
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 || vvutukur 28-Jan-2004 Enh#3167098.FICR112.Modified cursor c_sca to consider
32 || the student term records also.
33 || vvutukur 20-May-2002 removed upper check constraint on fee_type column
34 || in c_ft cursor.bug#2344826.
35 ----------------------------------------------------------------------------*/
36 gv_other_detail VARCHAR2(255);
37 BEGIN
38 DECLARE
39 CURSOR c_ft IS
40 SELECT ft.s_fee_type,
41 ft.s_fee_trigger_cat
42 FROM IGS_FI_FEE_TYPE ft
43 WHERE ft.fee_type = p_fee_type;
44 CURSOR c_sca IS
45 SELECT 'X'
46 FROM igs_en_stdnt_ps_att sca,
47 igs_fi_f_cat_fee_lbl fcfl,
48 igs_fi_fee_str_stat fsst
49 WHERE sca.person_id = p_person_id
50 AND sca.course_cd = p_course_cd
51 AND fcfl.fee_type = p_fee_type
52 AND fcfl.fee_cat = sca.fee_cat
53 AND fcfl.fee_liability_status = fsst.fee_structure_status
54 AND fsst.s_fee_structure_status = 'ACTIVE'
55 UNION ALL
56 SELECT 'X'
57 FROM igs_en_spa_terms spa,
58 igs_fi_f_cat_fee_lbl fcfl,
59 igs_fi_fee_str_stat fsst
60 WHERE spa.person_id = p_person_id
61 AND spa.program_cd = p_course_cd
62 AND fcfl.fee_type = p_fee_type
63 AND fcfl.fee_cat = spa.fee_cat
64 AND fcfl.fee_liability_status = fsst.fee_structure_status
65 AND fsst.s_fee_structure_status = 'ACTIVE';
66
67 v_s_fee_type IGS_FI_FEE_TYPE.s_fee_type%TYPE;
68 v_s_fee_trigger_cat IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE;
69 v_dummy VARCHAR2(1);
70 CST_OTHER IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'OTHER';
71 CST_TUITION IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'TUITION';
72 CST_TUTNFEE IGS_FI_FEE_TYPE.s_fee_type%TYPE := 'TUTNFEE';
73 CST_INSTITUTN IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE := 'INSTITUTN';
74 BEGIN
75 -- Validate the IGS_FI_FEE_AS_RT can only be defined for
76 -- fee types with s_fee_type = 'OTHER'or 'TUTNFEE' or 'TUITION' and
77 -- s_fee_trigger_cat not 'INSTITUTN'.
78 -- Set the default message number
79 p_message_name := Null;
80 OPEN c_ft;
81 FETCH c_ft into v_s_fee_type, v_s_fee_trigger_cat;
82 IF c_ft%NOTFOUND THEN -- if no record is found
83 CLOSE c_ft;
84 RETURN TRUE;
85 END IF;
86 CLOSE c_ft;
87 -- Check the system fee type
88 IF v_s_fee_type <> CST_OTHER
89 AND v_s_fee_type <> CST_TUTNFEE
90 AND v_s_fee_type <> CST_TUITION THEN
91 p_message_name := 'IGS_FI_ASSRATES_OTHER_TUTUION';
92 RETURN FALSE;
93 END IF;
94 -- Check the system fee trigger category
95 IF v_s_fee_trigger_cat = CST_INSTITUTN THEN
96 p_message_name := 'IGS_FI_FEEASS_NOT_INSTITN';
97 RETURN FALSE;
98 END IF;
99 OPEN c_sca;
100 FETCH c_sca INTO v_dummy;
101 IF c_sca%NOTFOUND THEN
102 CLOSE c_sca;
103 p_message_name := 'IGS_FI_FEETYPE_NOT_ACTIVE';
104 RETURN FALSE;
105 END IF;
106 CLOSE c_sca;
107 -- Execution Complete
108 RETURN TRUE;
109 END;
110 END finp_val_cfar_ins;
111 --
112 -- Ensure the start and end dates don't overlap with other records.
113 FUNCTION finp_val_cfar_ovrlp(
114 p_person_id IN NUMBER ,
115 p_course_cd IN VARCHAR2 ,
116 p_fee_type IN VARCHAR2 ,
117 p_start_dt IN DATE ,
118 p_end_dt IN DATE ,
119 p_message_name OUT NOCOPY VARCHAR2 )
120 RETURN BOOLEAN AS
121 gv_other_detail VARCHAR2(255);
122 BEGIN
123 DECLARE
124 CURSOR c_cfar IS
125 SELECT cfar.start_dt,
126 cfar.end_dt
127 FROM IGS_FI_FEE_AS_RT cfar
128 WHERE cfar.person_id = p_person_id AND
129 cfar.course_cd = p_course_cd AND
130 cfar.fee_type = p_fee_type AND
131 cfar.start_dt <> p_start_dt;
132 BEGIN
133 -- Validate the contract_fee+ass_rate (cfar) table to ensure that for
134 -- records with the same person_id, course_cd and fee_type that the date ranges
135 -- don't overlap.
136 --- Set the default message number
137 p_message_name := Null;
138 FOR v_cfar_rec IN c_cfar LOOP
139 IF (v_cfar_rec.end_dt IS NOT NULL) THEN
140 IF (p_start_dt BETWEEN v_cfar_rec.start_dt AND v_cfar_rec.end_dt) THEN
141 p_message_name := 'IGS_FI_STDT_BTWN_STDT_ENDDT';
142 RETURN FALSE;
143 END IF;
144 IF (p_end_dt IS NOT NULL) THEN
145 IF (p_end_dt BETWEEN v_cfar_rec.start_dt AND v_cfar_rec.end_dt) THEN
146 p_message_name := 'IGS_FI_ENDDT_BTWN_STDT_ENDDT';
147 RETURN FALSE;
148 END IF;
149 IF (p_start_dt <= v_cfar_rec.start_dt AND
150 p_end_dt >= v_cfar_rec.end_dt) THEN
151 p_message_name := 'IGS_FI_STDT_ENDDT_ENCOMPASS';
152 RETURN FALSE;
153 END IF;
154 ELSE
155 IF (p_start_dt <= v_cfar_rec.start_dt) THEN
156 p_message_name := 'IGS_FI_OPEN_DATE_RANGE';
157 RETURN FALSE;
158 END IF;
159 END IF;
160 ELSE
161 IF (p_start_dt >= v_cfar_rec.start_dt OR
162 p_end_dt >= v_cfar_rec.start_dt) THEN
163 p_message_name := 'IGS_FI_DATES_OVERLAP_STDT';
164 RETURN FALSE;
165 END IF;
166 END IF;
167 END LOOP;
168 RETURN TRUE;
169 END;
170 END finp_val_cfar_ovrlp;
171 --
172 -- Validate that only one record has an open end date.
173 FUNCTION finp_val_cfar_open(
174 p_person_id IN NUMBER ,
175 p_course_cd IN VARCHAR2 ,
176 p_fee_type IN VARCHAR2 ,
177 p_start_dt IN DATE ,
178 p_message_name OUT NOCOPY VARCHAR2 )
179 RETURN BOOLEAN AS
180 gv_other_detail VARCHAR2(255);
181 BEGIN
182 DECLARE
183 v_person_id IGS_FI_FEE_AS_RT.person_id%TYPE;
184 CURSOR c_cfar IS
185 SELECT cfar.person_id
186 FROM IGS_FI_FEE_AS_RT cfar
187 WHERE cfar.person_id = p_person_id AND
188 cfar.course_cd = p_course_cd AND
189 cfar.fee_type = p_fee_type AND
190 cfar.start_dt <> p_start_dt AND
191 cfar.end_dt IS NULL;
192 BEGIN
193 -- Validate the IGS_FI_FEE_AS_RT (cfar) table to ensure that
194 -- for records with the same person_id, course_cd, and fee_type
195 -- that only one record has a NULL end_dt.
196 OPEN c_cfar;
197 FETCH c_cfar INTO v_person_id;
198 IF (c_cfar%FOUND) THEN
199 CLOSE c_cfar;
200 p_message_name := 'IGS_FI_CONTRACT_FEEASS_RATE';
201 RETURN FALSE;
202 END IF;
203 CLOSE c_cfar;
204 p_message_name := Null;
205 RETURN TRUE;
206 END;
207 END finp_val_cfar_open;
208 --
209 -- Validate that end date is null or >= start date.
210 FUNCTION finp_val_cfar_end_dt(
211 p_start_dt IN DATE ,
212 p_end_dt IN DATE ,
213 p_message_name OUT NOCOPY VARCHAR2 )
214 RETURN BOOLEAN AS
215 gv_other_detail VARCHAR2(255);
216 BEGIN
217 -- Validates the IGS_FI_FEE_AS_RT (cfar) table to ensure that if the
218 -- end_dt is NOT NULL and it is greater than or equal to the start_dt.
219 IF (p_end_dt IS NOT NULL) AND
220 (p_end_dt < p_start_dt) THEN
221 -- The end date must be greater than or equal to the start date.
222 p_message_name := 'IGS_GE_END_DT_GE_ST_DATE';
223 RETURN FALSE;
224 END IF;
225 p_message_name := Null;
226 RETURN TRUE;
227 END finp_val_cfar_end_dt;
228 --
229 -- Validate the Attendance Mode closed indicator
230 FUNCTION finp_val_am_closed(
231 p_attendance_mode IN IGS_EN_ATD_MODE_ALL.attendance_mode%TYPE ,
232 p_message_name OUT NOCOPY VARCHAR2)
233 RETURN BOOLEAN AS
234 gv_other_detail VARCHAR2(255);
235 BEGIN --finp_val_am_closed
236 --Validate if IGS_EN_ATD_MODE.attendance_mode is closed
237 DECLARE
238 v_closed_ind VARCHAR2(1);
239 CURSOR c_am IS
240 SELECT am.closed_ind
241 FROM IGS_EN_ATD_MODE am
242 WHERE am.attendance_mode = p_attendance_mode;
243 BEGIN
244 --set default message_number
245 p_message_name := Null;
246 OPEN c_am;
247 FETCH c_am INTO v_closed_ind;
248 IF (c_am%FOUND) THEN
249 IF (v_closed_ind = 'Y') THEN
250 CLOSE c_am;
251 p_message_name := 'IGS_PS_ATTEND_MODE_CLOSED';
252 RETURN FALSE;
253 END IF;
254 END IF;
255 CLOSE c_am;
256 RETURN TRUE;
257 END;
258 END finp_val_am_closed;
259 --
260 -- Validate the Attendance Type closed indicator
261 FUNCTION finp_val_att_closed(
262 p_attendance_type IN IGS_EN_ATD_TYPE_ALL.attendance_type%TYPE ,
263 p_message_name OUT NOCOPY VARCHAR2)
264 RETURN BOOLEAN AS
265 gv_other_detail VARCHAR2(255);
266 BEGIN --finp_val_att_closed
267 --Validate if IGS_EN_ATD_TYPE.attendance_type is closed
268 DECLARE
269 v_closed_ind VARCHAR2(1);
270 CURSOR c_att IS
271 SELECT att.closed_ind
272 FROM IGS_EN_ATD_TYPE att
273 WHERE att.attendance_type = p_attendance_type;
274 BEGIN
275 --set default message_number
276 p_message_name := Null;
277 OPEN c_att;
278 FETCH c_att INTO v_closed_ind;
279 IF (c_att%FOUND) THEN
280 IF (v_closed_ind = 'Y') THEN
281 CLOSE c_att;
282 p_message_name := 'IGS_PS_ATTEND_TYPE_CLOSED';
283 RETURN FALSE;
284 END IF;
285 END IF;
286 CLOSE c_att;
287 RETURN TRUE;
288 END;
289 END finp_val_att_closed;
290 --
291 -- Validate the Location closed indicator
292 FUNCTION finp_val_loc_closed(
293 p_location_cd IN IGS_AD_LOCATION_ALL.location_cd%TYPE ,
294 p_message_name OUT NOCOPY VARCHAR2)
295 RETURN BOOLEAN AS
296 gv_other_detail VARCHAR2(255);
297 BEGIN --finp_val_loc_closed
298 --Validate if IGS_EN_ATD_TYPE.attendance_type is closed
299 DECLARE
300 v_closed_ind VARCHAR2(1);
301 v_location_type igs_lookups_view.lookup_code%TYPE;
302 CURSOR c_loc IS
303 SELECT loc.closed_ind,
304 loc.location_type
305 FROM IGS_AD_LOCATION loc
306 WHERE loc.location_cd = p_location_cd;
307 BEGIN
308 --set default message_number
309 p_message_name := Null;
310 OPEN c_loc;
311 FETCH c_loc INTO v_closed_ind, v_location_type;
312 IF (c_loc%FOUND) THEN
313 IF (v_closed_ind = 'Y') THEN
314 CLOSE c_loc;
315 p_message_name := 'IGS_FI_LOCATION_CLOSED';
316 RETURN FALSE;
317 END IF;
318 IF (v_location_type <> 'CAMPUS') THEN
319 CLOSE c_loc;
320 p_message_name := 'IGS_PS_LOC_NOT_TYPE_CAMPUS';
321 RETURN FALSE;
322 END IF;
323 END IF;
324 CLOSE c_loc;
325 RETURN TRUE;
326 END;
327 END finp_val_loc_closed;
328 --
329 -- Validate the fee_type in the fee_type_account is not closed.
330 FUNCTION finp_val_ft_closed(
331 p_fee_type IN VARCHAR2 ,
332 p_message_name OUT NOCOPY VARCHAR2 )
333 RETURN BOOLEAN AS
334 /*----------------------------------------------------------------------------
335 || Created By :
336 || Created On :
337 || Purpose :
338 || Known limitations, enhancements or remarks :
339 || Change History :
340 || Who When What
341 || (reverse chronological order - newest change first)
342 || vvutukur 20-May-2002 removed upper check constraint on fee_type column
343 || in c_ft cursor.bug#2344826.
344 ----------------------------------------------------------------------------*/
345 gv_other_detail VARCHAR2(255);
346 BEGIN
347 DECLARE
348 CURSOR c_ft IS
349 SELECT ft.closed_ind
350 FROM IGS_FI_FEE_TYPE ft
351 WHERE ft.fee_type = p_fee_type;
352 v_fee_type IGS_FI_FEE_TYPE.closed_ind%TYPE;
353 BEGIN -- finp_val_ft_closed
354 -- Validate that the fee type is not closed
355 -- Set the default message number
356 p_message_name := Null;
357 OPEN c_ft;
358 FETCH c_ft into v_fee_type;
359 IF c_ft%NOTFOUND THEN -- If a record is not found
360 CLOSE c_ft;
361 RETURN TRUE;
362 END IF;
363 CLOSE c_ft;
364 IF v_fee_type = 'Y' then
365 p_message_name := 'IGS_FI_FEETYPE_CLOSED';
366 RETURN FALSE;
367 END IF;
368 -- Return the default value
369 RETURN TRUE;
370 END;
371 END finp_val_ft_closed;
372 END IGS_FI_VAL_CFAR;
[Home] [Help]