1 PACKAGE BODY IGS_FI_VAL_FAR AS
2 /* $Header: IGSFI22B.pls 120.2 2005/08/29 02:36:37 appldev ship $ */
3
4 --Who When What
5 --gurprsin 29-Aug-2005 Bug #4564002, modified cursor c_far.
6 -- svuppala 03-Jun-2005 Enh# 3442712 - Modified finp_val_far_unique
7 --pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
8 -- Modified finp_val_far_unique() - Added 2 new params
9 -- vvutukur 29-Nov-2002 Enh#2564986.Obsoleted function FINP_VAL_FAR_CUR.
10 -- npalanis 23-OCT-2002 Bug : 2608360
11 -- p_residency_status_id column is changed to p_residency_status_cd of
12 -- datatype varchar2.
13 --
14 --
15 -- npalanis 23-OCT-2002 Bug : 2547368
16 -- Defaulting arguments in funtion and procedure definitions removed
17 -- bug id : 1956374
18 -- sjadhav , 28-aug-2001
19 -- removed FUNCTION enrp_val_att_closed
20 --
21
22 -- Validate fee assessment rate can be created for the relation type.
23 FUNCTION finp_val_far_create(
24 p_fee_type IN VARCHAR2 ,
25 p_fee_cal_type IN VARCHAR2 ,
26 p_fee_ci_sequence_number IN NUMBER ,
27 p_s_relation_type IN VARCHAR2 ,
28 p_message_name OUT NOCOPY VARCHAR2 )
29 RETURN BOOLEAN AS
30 gv_other_detail VARCHAR2(255);
31 BEGIN -- finp_val_far_create
32 -- Validate if IGS_FI_FEE_AS_RATE records can be created.
33 -- When defined at FTCI level, they cannot also be
34 -- defined at FCFL level and vice-versa.
35 DECLARE
36 CURSOR c_far (
37 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE) IS
38 SELECT 'x'
39 FROM IGS_FI_FEE_AS_RATE
40 WHERE fee_type = p_fee_type AND
41 fee_cal_type = p_fee_cal_type AND
42 fee_ci_sequence_number = p_fee_ci_sequence_number AND
43 s_relation_type = cp_s_relation_type AND
44 logical_delete_dt IS NULL;
45 v_fcfl_exists VARCHAR2(1);
46 v_ftci_exists VARCHAR2(1);
47 BEGIN
48 -- Set the default message number
49 p_message_name := Null;
50 -- 1. Check Parameters
51 IF p_fee_type IS NULL OR
52 p_fee_cal_type IS NULL OR
53 p_fee_ci_sequence_number IS NULL OR
54 p_s_relation_type IS NULL THEN
55 RETURN TRUE;
56 END IF;
57 -- 2. If p_s_relation_type = 'FCFL', check if any IGS_FI_FEE_AS_RATE records
58 -- have been defined at the FTCI level. If so, return error.
59 IF p_s_relation_type = 'FCFL' THEN
60 OPEN c_far(
61 'FTCI');
62 FETCH c_far INTO v_ftci_exists;
63 IF c_far%FOUND THEN
64 CLOSE c_far;
65 p_message_name := 'IGS_FI_ASSRATES_NOT_DEFINED';
66 RETURN FALSE;
67 END IF;
68 CLOSE c_far;
69 END IF;
70 -- 3. If p_s_relation_type = 'FTCI', check if any IGS_FI_FEE_AS_RATE records
71 -- have been defined at the FCFL level. If so, return error.
72 IF p_s_relation_type = 'FTCI' THEN
73 OPEN c_far(
74 'FCFL');
75 FETCH c_far INTO v_fcfl_exists;
76 IF c_far%FOUND THEN
77 CLOSE c_far;
78 p_message_name := 'IGS_FI_ASSRATES_NOT_DFNED_FEE';
79 RETURN FALSE;
80 END IF;
81 CLOSE c_far;
82 END IF;
83 RETURN TRUE;
84 END;
85 END finp_val_far_create;
86 --
87 -- Validate IGS_PS_COURSE location code.
88 FUNCTION crsp_val_loc_cd(
89 p_location_cd IN VARCHAR2 ,
90 p_message_name OUT NOCOPY VARCHAR2 )
91 RETURN BOOLEAN AS
92 v_location_closed_ind IGS_AD_LOCATION.closed_ind%TYPE;
93 v_location_type IGS_AD_LOCATION.location_type%TYPE;
94 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
95 CURSOR c_location_cd(
96 cp_location_cd IGS_AD_LOCATION.location_cd%TYPE) IS
97 SELECT IGS_AD_LOCATION.closed_ind,
98 location_type
99 FROM IGS_AD_LOCATION
100 WHERE location_cd = cp_location_cd;
101 CURSOR c_location_type(
102 cp_location_type IGS_AD_LOCATION_TYPE.location_type%TYPE) IS
103 SELECT s_location_type
104 FROM IGS_AD_LOCATION_TYPE
105 WHERE location_type = cp_location_type;
106 v_other_detail VARCHAR2(255);
107 BEGIN
108 -- This module based on the parameter performs validations
109 -- for for the location code within the CS and P subsystem
110 p_message_name := Null;
111 v_location_closed_ind := NULL;
112 -- Test the value of closed indicator
113 OPEN c_location_cd(
114 p_location_cd);
115 FETCH c_location_cd INTO v_location_closed_ind,
116 v_location_type;
117 CLOSE c_location_cd;
118 IF (v_location_closed_ind IS NULL) THEN
119 RETURN TRUE;
120 ELSE
121 IF(v_location_closed_ind = 'Y') THEN
122 p_message_name := 'IGS_PS_LOC_CODE_CLOSED';
123 RETURN FALSE;
124 END IF;
125 END IF;
126 -- Test the value of system location type
127 OPEN c_location_type(
128 v_location_type);
129 FETCH c_location_type INTO v_s_location_type;
130 CLOSE c_location_type;
131 IF (NVL(v_s_location_type,'NULL') <> 'CAMPUS') THEN
132 p_message_name := 'IGS_PS_LOC_NOT_TYPE_CAMPUS';
133 RETURN FALSE;
134 END IF;
135 RETURN TRUE;
136 END crsp_val_loc_cd;
137 --
138 -- Ensure govt_hecs_payment_option is specified.
139 FUNCTION finp_val_far_rqrd(
140 p_fee_type IN VARCHAR2 ,
141 p_govt_hecs_payment_option IN VARCHAR2 ,
142 p_message_name OUT NOCOPY VARCHAR2 )
143 RETURN BOOLEAN AS
144 gv_other_detail VARCHAR2(255);
145 BEGIN -- finp_val_far_rqrd
146 -- Validate if IGS_FI_FEE_TYPE.s_fee_type = 'HECS' or 'TUITION',
147 -- then IGS_FI_FEE_AS_RATE.govt_hecs_payment_option must be entered.
148 DECLARE
149 v_dummy VARCHAR2(1);
150 CURSOR c_ft IS
151 SELECT 'x'
152 FROM IGS_FI_FEE_TYPE ft
153 WHERE ft.fee_type = p_fee_type AND
154 ft.s_fee_type IN ('HECS','TUITION');
155 BEGIN
156 -- Set the default message number
157 p_message_name := Null;
158 -- 1. Check parameters :
159 IF (p_fee_type IS NULL) THEN
160 Return TRUE;
161 END IF;
162 -- 2. Determine the IGS_FI_FEE_TYPE.s_fee_type value.
163 OPEN c_ft;
164 FETCH c_ft INTO v_dummy;
165 IF c_ft%FOUND THEN --govt_hecs_payment_option must exist
166 IF (p_govt_hecs_payment_option IS NULL) THEN
167 CLOSE c_ft;
168 -- Government HECS Payment option must be specified for this Fee Type.
169 p_message_name := 'IGS_FI_GOVT_HECS_PYMNT';
170 RETURN FALSE;
171 END IF;
172 END IF;
173 CLOSE c_ft;
174 -- 3. Return no error:
175 RETURN TRUE;
176 EXCEPTION
177 WHEN OTHERS THEN
178 IF c_ft%ISOPEN THEN
179 CLOSE c_ft;
180 END IF;
181 RAISE;
182 END;
183 END finp_val_far_rqrd;
184 --
185 -- Validate fee assessment rate is unqiue.
186 FUNCTION finp_val_far_unique(
187 p_fee_type IN VARCHAR2 ,
188 p_fee_cal_type IN VARCHAR2 ,
189 p_fee_ci_sequence_number IN NUMBER ,
190 p_s_relation_type IN VARCHAR2 ,
191 p_rate_number IN NUMBER ,
192 p_fee_cat IN VARCHAR2 ,
193 p_location_cd IN VARCHAR2 ,
194 p_attendance_type IN VARCHAR2 ,
195 p_attendance_mode IN VARCHAR2 ,
196 p_govt_hecs_payment_option IN VARCHAR2 ,
197 p_govt_hecs_cntrbtn_band IN NUMBER ,
198 p_chg_rate IN NUMBER ,
199 p_unit_class IN VARCHAR2,
200 p_residency_status_cd IN VARCHAR2 ,
201 p_course_cd IN VARCHAR2 ,
202 p_version_number IN NUMBER ,
203 p_org_party_id IN NUMBER ,
204 p_class_standing IN VARCHAR2 ,
205 p_message_name OUT NOCOPY VARCHAR2 ,
206 p_unit_set_cd IN VARCHAR2,
207 p_us_version_number IN NUMBER,
208 p_unit_cd IN VARCHAR2 ,
209 p_unit_version_number IN NUMBER ,
210 p_unit_level IN VARCHAR2 ,
211 p_unit_type_id IN NUMBER ,
212 p_unit_mode IN VARCHAR2
213 ) RETURN BOOLEAN AS
214 /*****************************************************************************/
215 --Change History
216 --Who When What
217 --gurprsin 29-Aug-2005 Bug #4564002, modified cursor c_far
218 --pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
219 -- Modified finp_val_far_unique() - Added 2 new params
220 -- Modified cursor c_far
221 /*****************************************************************************/
222 gv_other_detail VARCHAR2(255);
223 BEGIN -- finp_val_far_unique
224 -- Validate if IGS_FI_FEE_AS_RATE.location_cd, IGS_FI_FEE_AS_RATE.attendance_type,
225 -- IGS_FI_FEE_AS_RATE.attendance_mode, IGS_FI_FEE_AS_RATE.govt_hecs_payment_option
226 -- and IGS_FI_FEE_AS_RATE.govt_hecs_cntrbtn_band form a unique combination.
227 -- Required as all five fields are optional.
228 DECLARE
229 v_dummy VARCHAR2(1);
230 CURSOR c_far IS
231 SELECT 'x'
232 FROM igs_fi_fee_as_rate far
233 WHERE far.fee_type = p_fee_type
234 AND far.fee_cal_type = p_fee_cal_type
235 AND far.fee_ci_sequence_number = p_fee_ci_sequence_number
236 AND far.s_relation_type = p_s_relation_type
237 AND far.rate_number <> NVL(p_rate_number,0)
238 AND NVL(far.fee_cat,'NULL') = NVL(p_fee_cat,'NULL')
239 AND NVL(far.location_cd,'NULL') = NVL(p_location_cd,'NULL')
240 AND NVL(far.attendance_type,'NULL') = NVL(p_attendance_type,'NULL')
241 AND NVL(far.attendance_mode,'NULL') = NVL(p_attendance_mode,'NULL')
242 AND NVL(far.govt_hecs_payment_option,'NULL') = NVL(p_govt_hecs_payment_option,'NULL')
243 AND NVL(far.govt_hecs_cntrbtn_band,0) = NVL(p_govt_hecs_cntrbtn_band,0)
244 AND NVL(far.chg_rate,0) = NVL(p_chg_rate,0)
245 -- AND NVL(far.unit_class,'NULL') = NVL(p_unit_class,'NULL')
246 AND far.logical_delete_dt IS NULL
247 AND NVL(far.residency_status_cd,0) = NVL(p_residency_status_cd,0)
248 AND NVL(far.course_cd,'NULL') = NVL(p_course_cd,'NULL')
249 AND NVL(far.version_number,0) = NVL(p_version_number,0)
250 AND NVL(far.org_party_id,0) = NVL(p_org_party_id,0)
251 --Bug #4564002, passed p_class_standing instead of class_standing
252 AND NVL(far.class_standing,'NULL') = NVL(p_class_standing,'NULL')
253 AND NVL(far.unit_set_cd,'NULL') = NVL(p_unit_set_cd,'NULL')
254 AND NVL(far.us_version_number,0) = NVL(p_us_version_number,0)
255 AND NVL(FAR.UNIT_TYPE_ID,0) = NVL(P_UNIT_TYPE_ID,0)
256 AND (( FAR.UNIT_CLASS = P_UNIT_CLASS) OR (FAR.UNIT_CLASS IS NULL AND P_UNIT_CLASS IS NULL))
257 AND ((FAR.UNIT_MODE = P_UNIT_MODE) OR (FAR.UNIT_MODE IS NULL AND P_UNIT_MODE IS NULL))
258 AND ((FAR.UNIT_CD = P_UNIT_CD) OR (FAR.UNIT_CD IS NULL AND P_UNIT_CD IS NULL))
259 AND NVL(FAR.UNIT_VERSION_NUMBER,0) = NVL(P_UNIT_VERSION_NUMBER,0)
260 AND ((FAR.UNIT_LEVEL = P_UNIT_LEVEL) OR (FAR.UNIT_LEVEL IS NULL AND P_UNIT_LEVEL IS NULL))
261 ;
262
263 BEGIN
264 -- Set the default message number
265 p_message_name := Null;
266 -- 1. Check parameters :
267 IF (p_fee_type IS NULL OR
268 p_fee_cal_type IS NULL OR
269 p_fee_ci_sequence_number IS NULL OR
270 p_s_relation_type IS NULL) THEN
271 RETURN TRUE;
272 END IF;
273 -- 2. Validate that the current record is unique.
274 -- Note : rate_number may be passed as a null value if the current
275 -- record has not been committed.
276 OPEN c_far;
277 FETCH c_far INTO v_dummy;
278 IF c_far%FOUND THEN --duplicate condition
279 CLOSE c_far;
280 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
281 RETURN FALSE;
282 END IF;
283 CLOSE c_far;
284 -- 3. Return no error:
285 RETURN TRUE;
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF c_far%ISOPEN THEN
292 END;
289 CLOSE c_far;
290 END IF;
291 RAISE;
293 END finp_val_far_unique;
294
295 --
296 -- Validate fee assessment rate order of precednce.
297 FUNCTION finp_val_far_order(
298 p_fee_type IN VARCHAR2 ,
299 p_fee_cal_type IN VARCHAR2 ,
300 p_fee_ci_sequence_number IN NUMBER ,
301 p_s_relation_type IN VARCHAR2 ,
302 p_rate_number IN NUMBER ,
303 p_fee_cat IN VARCHAR2 ,
304 p_location_cd IN VARCHAR2 ,
305 p_attendance_type IN VARCHAR2 ,
306 p_attendance_mode IN VARCHAR2 ,
307 p_govt_hecs_payment_option IN VARCHAR2 ,
308 p_govt_hecs_cntrbtn_band IN NUMBER ,
309 p_order_of_precedence IN NUMBER ,
310 p_message_name OUT NOCOPY VARCHAR2 )
311 RETURN BOOLEAN AS
312 gv_other_detail VARCHAR2(255);
313 BEGIN -- finp_val_far_order
314 -- Validate IGS_FI_FEE_AS_RATE.order_of_precedence exists when one or more of
315 -- IGS_FI_FEE_AS_RATE.attendance_type, IGS_FI_FEE_AS_RATE.attendance_mode or
316 -- IGS_FI_FEE_AS_RATE.location_cd are specified, resulting in a non-mutually
317 -- exclusive fee assessment rate.
318 -- The mutually exclusive combinations are :
319 -- only location code is defined across all related rates
320 -- only attendance type is defined across all related rates
321 -- only attendance mode is defined across all related rates
322 -- only location code and attendance type are defined across all related
323 -- rates
324 -- location code, attendance type and attendance mode are defined across
325 -- all related rates
326 -- only location code and attendance mode are defined across all related
327 -- rates
328 -- only attendance type and attendance mode are defined across all
329 -- related rates
330 -- All other combinations may result in non-mutually exclusive fee assessment
331 -- rates, so an order_of_precedence value is required.
332 DECLARE
333 cst_prorata CONSTANT VARCHAR2(8) := 'PRO RATA';
334 v_far_1_rec_found BOOLEAN := FALSE;
335 v_far_2_rec_found BOOLEAN := FALSE;
336 v_far_3_rec_found BOOLEAN := FALSE;
337 v_far_4_rec_found BOOLEAN := FALSE;
338 v_far_5_rec_found BOOLEAN := FALSE;
339 v_far_6_rec_found BOOLEAN := FALSE;
340 v_far_7_rec_found BOOLEAN := FALSE;
341 v_far_8_rec_found BOOLEAN := FALSE;
342 v_far_9_rec_found BOOLEAN := FALSE;
343 CURSOR c_far_1 (
344 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
345 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
346 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
347 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
348 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
349 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
350 SELECT far.order_of_precedence
351 FROM IGS_FI_FEE_AS_RATE far
352 WHERE far.fee_type = cp_fee_type AND
353 far.fee_cal_type = cp_fee_cal_type AND
354 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
355 far.s_relation_type = cp_s_relation_type AND
356 far.rate_number <> cp_rate_number AND
357 far.logical_delete_dt IS NULL AND
358 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
359 ((far.location_cd IS NULL) OR
360 (far.attendance_mode IS NOT NULL OR far.attendance_type IS NOT NULL));
361 CURSOR c_far_2 (
362 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
363 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
364 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
365 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
366 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
367 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
368 SELECT far.order_of_precedence
369 FROM IGS_FI_FEE_AS_RATE far
370 WHERE far.fee_type = cp_fee_type AND
371 far.fee_cal_type = cp_fee_cal_type AND
372 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
373 far.s_relation_type = cp_s_relation_type AND
374 far.rate_number <> cp_rate_number AND
375 far.logical_delete_dt IS NULL AND
376 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
377 ((far.location_cd IS NULL OR far.attendance_mode IS NULL) OR
378 (far.attendance_type IS NOT NULL));
379 CURSOR c_far_3 (
380 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
384 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
381 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
382 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
383 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
385 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
386 SELECT far.order_of_precedence
387 FROM IGS_FI_FEE_AS_RATE far
388 WHERE far.fee_type = cp_fee_type AND
389 far.fee_cal_type = cp_fee_cal_type AND
390 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
391 far.s_relation_type = cp_s_relation_type AND
392 far.rate_number <> cp_rate_number AND
393 far.logical_delete_dt IS NULL AND
394 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
395 ((far.location_cd IS NULL OR far.attendance_type IS NULL) OR
396 (far.attendance_mode IS NOT NULL));
397 CURSOR c_far_4 (
398 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
399 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
400 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
401 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
402 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
403 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
404 SELECT far.order_of_precedence
405 FROM IGS_FI_FEE_AS_RATE far
406 WHERE far.fee_type = cp_fee_type AND
407 far.fee_cal_type = cp_fee_cal_type AND
408 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
409 far.s_relation_type = cp_s_relation_type AND
410 far.rate_number <> cp_rate_number AND
411 far.logical_delete_dt IS NULL AND
412 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
413 ((far.location_cd IS NULL OR
414 far.attendance_mode IS NULL) OR
415 (far.attendance_type IS NULL));
416 CURSOR c_far_5 (
417 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
418 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
419 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
420 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
421 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
422 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
423 SELECT far.order_of_precedence
424 FROM IGS_FI_FEE_AS_RATE far
425 WHERE far.fee_type = cp_fee_type AND
426 far.fee_cal_type = cp_fee_cal_type AND
427 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
428 far.s_relation_type = cp_s_relation_type AND
429 far.rate_number <> cp_rate_number AND
430 far.logical_delete_dt IS NULL AND
431 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
432 ((far.attendance_type IS NULL) OR
433 (far.location_cd IS NOT NULL OR far.attendance_mode IS NOT NULL));
434 CURSOR c_far_6 (
435 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
436 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
437 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
438 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
439 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
440 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
441 SELECT far.order_of_precedence
442 FROM IGS_FI_FEE_AS_RATE far
443 WHERE far.fee_type = cp_fee_type AND
444 far.fee_cal_type = cp_fee_cal_type AND
445 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
446 far.s_relation_type = cp_s_relation_type AND
447 far.rate_number <> cp_rate_number AND
448 far.logical_delete_dt IS NULL AND
449 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
450 ((far.attendance_mode IS NULL OR far.attendance_type IS NULL) OR
451 (far.location_cd IS NOT NULL));
452 CURSOR c_far_7 (
453 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
454 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
458 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE) IS
455 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
456 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
457 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
459 SELECT far.order_of_precedence
460 FROM IGS_FI_FEE_AS_RATE far
461 WHERE far.fee_type = cp_fee_type AND
462 far.fee_cal_type = cp_fee_cal_type AND
463 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
464 far.s_relation_type = cp_s_relation_type AND
465 far.rate_number <> cp_rate_number AND
466 far.logical_delete_dt IS NULL AND
467 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
468 ((far.attendance_mode IS NULL) OR
469 (far.location_cd IS NOT NULL OR far.attendance_type IS NOT NULL));
470 CURSOR c_far_8 (
471 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
472 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
473 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
474 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
475 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
476 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE,
477 cp_order_of_precedence IGS_FI_FEE_AS_RATE.order_of_precedence%TYPE) IS
478 SELECT far.order_of_precedence
479 FROM IGS_FI_FEE_AS_RATE far
480 WHERE far.fee_type = cp_fee_type AND
481 far.fee_cal_type = cp_fee_cal_type AND
482 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
483 far.s_relation_type = cp_s_relation_type AND
484 far.rate_number <> cp_rate_number AND
485 far.logical_delete_dt IS NULL AND
486 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
487 far.order_of_precedence = cp_order_of_precedence;
488 CURSOR c_far_9 (
489 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE,
490 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
491 cp_fee_ci_sequence_number IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
492 cp_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE,
493 cp_rate_number IGS_FI_FEE_AS_RATE.rate_number%TYPE,
494 cp_fee_cat IGS_FI_FEE_AS_RATE.fee_cat%TYPE,
495 cp_location_cd IGS_FI_FEE_AS_RATE.location_cd%TYPE,
496 cp_attendance_mode IGS_FI_FEE_AS_RATE.attendance_mode%TYPE,
497 cp_attendance_type IGS_FI_FEE_AS_RATE.attendance_type%TYPE,
498 cp_govt_hecs_payment_option IGS_FI_FEE_AS_RATE.govt_hecs_payment_option%TYPE,
499 cp_govt_hecs_cntrbtn_band IGS_FI_FEE_AS_RATE.govt_hecs_cntrbtn_band%TYPE) IS
500 SELECT far.order_of_precedence
501 FROM IGS_FI_FEE_AS_RATE far
502 WHERE far.fee_type = cp_fee_type AND
503 far.fee_cal_type = cp_fee_cal_type AND
504 far.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
505 far.s_relation_type = cp_s_relation_type AND
506 far.rate_number <> cp_rate_number AND
507 far.logical_delete_dt IS NULL AND
508 NVL(far.fee_cat,'NULL') = NVL(cp_fee_cat,'NULL') AND
509 NVL(far.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
510 NVL(far.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
511 NVL(far.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
512 NVL(far.govt_hecs_payment_option,'X') =
513 NVL(cp_govt_hecs_payment_option,'X') AND
514 NVL(far.govt_hecs_cntrbtn_band,0) = nvl(cp_govt_hecs_cntrbtn_band,0);
515 BEGIN
516 p_message_name := Null;
517 -- Check parameters
518 IF(p_fee_type IS NULL OR
519 p_fee_cal_type IS NULL OR
520 p_fee_ci_sequence_number IS NULL OR
521 p_s_relation_type IS NULL OR
522 p_rate_number IS NULL) THEN
523 Return TRUE;
524 END IF;
525 -- Validate that order_of_precedence is specified if required
526 -- (ie. if varying combinations of location_cd, attendance_type and
527 -- attendance_mode have been specified).
528 IF(p_order_of_precedence IS NULL) THEN
529 IF(p_location_cd IS NOT NULL) THEN
530 IF(p_attendance_type IS NULL) THEN
531 IF(p_attendance_mode IS NULL) then
532 -- Check that other records only have location_cd specified.
536 p_fee_cal_type,
533 -- If not, an order of precedence value is required.
534 FOR v_far_1_rec IN c_far_1(
535 p_fee_type,
537 p_fee_ci_sequence_number,
538 p_s_relation_type,
539 p_rate_number,
540 p_fee_cat) LOOP
541 v_far_1_rec_found := TRUE;
542 END LOOP;
543 ELSE -- p_attendance_mode IS NOT NULL
544 -- Check location and mode.
545 -- Check that other records only have location_cd and attendance_mode
546 -- specified. If not, an order of precedence value is required.
547 FOR v_far_2_rec IN c_far_2(
548 p_fee_type,
549 p_fee_cal_type,
550 p_fee_ci_sequence_number,
551 p_s_relation_type,
552 p_rate_number,
553 p_fee_cat) LOOP
554 v_far_2_rec_found := TRUE;
555 END LOOP;
556 END IF;
557 ELSE -- p_attendance_type IS NOT NULL
558 IF(p_attendance_mode IS NULL) THEN
559 -- Check location and type.
560 -- Check that other records only have location_cd and attendance_type
561 -- specified. If not, an order of precedence value is required.
562 FOR v_far_3_rec IN c_far_3(
563 p_fee_type,
564 p_fee_cal_type,
565 p_fee_ci_sequence_number,
566 p_s_relation_type,
567 p_rate_number,
568 p_fee_cat) LOOP
569 v_far_3_rec_found := TRUE;
570 END LOOP;
571 ELSE -- p_attendance_mode IS NOT NULL
572 -- Check_loc_type_and_mode.
573 -- Check that other records all have location_cd, attendance_type
574 -- and attendance_mode specified. If not, an order of precedence value
575 -- is required.
576 FOR v_far_4_rec IN c_far_4(
577 p_fee_type,
578 p_fee_cal_type,
579 p_fee_ci_sequence_number,
580 p_s_relation_type,
581 p_rate_number,
582 p_fee_cat) LOOP
583 v_far_4_rec_found := TRUE;
584 END LOOP;
585 END IF;
586 END IF;
587 ELSE -- p_location_cd IS NULL
588 IF(p_attendance_type IS NOT NULL) THEN
589 IF(p_attendance_mode IS NULL) THEN
590 -- Check_type.
591 -- Check that other records only have attendance_type specified.
592 -- If not, an order of precedence value is required.
593 FOR v_far_5_rec IN c_far_5(
594 p_fee_type,
595 p_fee_cal_type,
596 p_fee_ci_sequence_number,
597 p_s_relation_type,
598 p_rate_number,
599 p_fee_cat) LOOP
600 v_far_5_rec_found := TRUE;
601 END LOOP;
602 ELSE -- p_attendance_mode IS NOT NULL
603 -- Check_type_and_mode.
604 -- Check that other records only have attendance_type and attendance_mode
605 -- specified. If not, an order of precedence value is required.
606 FOR v_far_6_rec IN c_far_6(
607 p_fee_type,
608 p_fee_cal_type,
612 p_fee_cat) LOOP
609 p_fee_ci_sequence_number,
610 p_s_relation_type,
611 p_rate_number,
613 v_far_6_rec_found := TRUE;
614 END LOOP;
615 END IF;
616 ELSE -- p_attendance_type IS NULL
617 IF(p_attendance_mode IS NOT NULL) THEN
618 -- Check_mode.
619 -- Check that other records only have attendance_mode specified.
620 -- If not, an order of precedence value is required.
621 FOR v_far_7_rec IN c_far_7(
622 p_fee_type,
623 p_fee_cal_type,
624 p_fee_ci_sequence_number,
625 p_s_relation_type,
626 p_rate_number,
627 p_fee_cat) LOOP
628 v_far_7_rec_found := TRUE;
629 END LOOP;
630 END IF;
631 END IF;
632 END IF;
633 -- Now validate if order of precedence is required for cases where identical
634 -- records exist, except for the charge rate values.
635 FOR v_far_9_rec IN c_far_9(
636 p_fee_type,
637 p_fee_cal_type,
638 p_fee_ci_sequence_number,
639 p_s_relation_type,
640 p_rate_number,
641 p_fee_cat,
642 p_location_cd,
643 p_attendance_mode,
644 p_attendance_type,
645 p_govt_hecs_payment_option,
646 p_govt_hecs_cntrbtn_band) LOOP
647 v_far_9_rec_found := TRUE;
648 END LOOP;
649 ELSE -- p_order_of_precedence IS NOT NULL
650 -- Check_order_uniqueness.
651 -- As the order_of_precedence has been specified,
652 -- validate that it is unique from other order_of_precedence
653 -- values for matching IGS_FI_FEE_AS_RATE records of the same FTCI/FCFL parent.
654 FOR v_far_8_rec IN c_far_8(
655 p_fee_type,
656 p_fee_cal_type,
657 p_fee_ci_sequence_number,
658 p_s_relation_type,
659 p_rate_number,
660 p_fee_cat,
661 p_order_of_precedence) LOOP
662 v_far_8_rec_found := TRUE;
663 END LOOP;
664 END IF;
665 IF(v_far_1_rec_found = TRUE OR
666 v_far_2_rec_found = TRUE OR
667 v_far_3_rec_found = TRUE OR
668 v_far_4_rec_found = TRUE OR
669 v_far_5_rec_found = TRUE OR
670 v_far_6_rec_found = TRUE OR
671 v_far_7_rec_found = TRUE) THEN
672 p_message_name := 'IGS_FI_ORDER_OF_PREC_SPECIFY';
673 RETURN FALSE;
674 END IF;
675 IF (v_far_9_rec_found = TRUE) THEN
676 p_message_name := 'IGS_FI_ORDEROF_PREC_SPECIFY';
677 RETURN FALSE;
678 END IF;
679 IF(v_far_8_rec_found = TRUE) THEN
680 p_message_name := 'IGS_FI_ORDER_OF_PREC_CONFLICT';
681 RETURN FALSE;
682 END IF;
683 RETURN TRUE;
684 END;
685 END finp_val_far_order;
686 --
687 -- Ensure fee assessment rate fields can be populated.
688 FUNCTION finp_val_far_defntn(
689 p_fee_type IN VARCHAR2 ,
690 p_location_cd IN VARCHAR2 ,
691 p_attendance_type IN VARCHAR2 ,
692 p_attendance_mode IN VARCHAR2 ,
693 p_govt_hecs_payment_option IN VARCHAR2 ,
694 p_govt_hecs_cntrbtn_band IN NUMBER ,
695 p_message_name OUT NOCOPY VARCHAR2 )
696 RETURN BOOLEAN AS
697 gv_other_detail VARCHAR2(255);
698 BEGIN -- finp_val_far_defntn
699 -- Validate if IGS_FI_FEE_AS_RATE.location_cd, IGS_FI_FEE_AS_RATE.attendance_type,
700 -- IGS_FI_FEE_AS_RATE.attendance_mode, IGS_FI_FEE_AS_RATE.govt_hecs_payment_option
701 -- and IGS_FI_FEE_AS_RATE.govt_hecs_cntrbtn_band are allowed to be specified
702 -- or not, depending on fee_type value.
703 DECLARE
704 cst_other CONSTANT VARCHAR2(10) := 'OTHER';
705 cst_tutnfee CONSTANT VARCHAR2(10) := 'TUTNFEE';
706 cst_hecs CONSTANT VARCHAR2(10) := 'HECS';
707 cst_tuition CONSTANT VARCHAR2(10) := 'TUITION';
708 CURSOR c_ft(
709 cp_fee_type IGS_FI_FEE_AS_RATE.fee_type%TYPE) IS
710 SELECT s_fee_type
714 BEGIN
711 FROM IGS_FI_FEE_TYPE
712 WHERE fee_type = cp_fee_type;
713 v_ft_rec c_ft%ROWTYPE;
715 -- Set the default message number
716 p_message_name := Null;
717 -- Check parameters
718 IF p_fee_type IS NULL OR
719 (p_location_cd IS NULL AND
720 p_attendance_type IS NULL AND
721 p_attendance_mode IS NULL AND
722 p_govt_hecs_payment_option IS NULL AND
723 p_govt_hecs_cntrbtn_band IS NULL) THEN
724 RETURN TRUE;
725 END IF;
726 -- Cursor handling
727 OPEN c_ft (p_fee_type);
728 FETCH c_ft INTO v_ft_rec;
729 IF c_ft%NOTFOUND THEN
730 CLOSE c_ft;
731 RETURN TRUE;
732 END IF;
733 CLOSE c_ft;
734 -- Validate the IGS_FI_FEE_TYPE to see if it is permissible for particular values to
735 -- be specified.
736 -- ? When IGS_FI_FEE_TYPE.s_fee_type = ?OTHER?, govt_hecs_payment_option and
737 -- govt_hecs_cntrbtn_band cannot be specified.
738 -- ? When IGS_FI_FEE_TYPE.s_fee_type = ?HECS?, location_cd, attendance_type and
739 -- attendance_mode cannot be specified.
740 -- ? When IGS_FI_FEE_TYPE.s_fee_type = ?TUITION?, govt_hecs_cntrbtn_band
741 -- cannot be specified.
742 IF v_ft_rec.s_fee_type in ( cst_other,cst_tutnfee) THEN
743 IF p_govt_hecs_payment_option IS NOT NULL THEN
744 p_message_name := 'IGS_FI_GOVTHECS_PYMTOP_OTHER';
745 RETURN FALSE;
746 ELSIF p_govt_hecs_cntrbtn_band IS NOT NULL THEN
747 p_message_name := 'IGS_FI_GOVTHECS_BAND_OTHER';
748 RETURN FALSE;
749 END IF;
750 ELSIF v_ft_rec.s_fee_type = cst_hecs THEN
751 IF p_location_cd IS NOT NULL THEN
752 p_message_name := 'IGS_FI_LOCATION_NOTBE_HECS';
753 RETURN FALSE;
754 ELSIF p_attendance_type IS NOT NULL THEN
755 p_message_name := 'IGS_FI_ATTTYPE_FEETYPE_HECS';
756 RETURN FALSE;
757 ELSIF p_attendance_mode IS NOT NULL THEN
758 p_message_name := 'IGS_FI_ATTMODE_FEETYPE_HECS';
759 RETURN FALSE;
760 END IF;
761 ELSIF v_ft_rec.s_fee_type = cst_tuition THEN
762 IF p_govt_hecs_cntrbtn_band IS NOT NULL THEN
763 p_message_name := 'IGS_FI_GOVTHECS_BAND_OTHER';
764 RETURN FALSE;
765 END IF;
766 END IF;
767 -- Return the default value
768 RETURN TRUE;
769 END;
770 END finp_val_far_defntn;
771 --
772 -- Validate the attendance mode closed indicator.
773 FUNCTION enrp_val_am_closed(
774 p_attend_mode IN VARCHAR2 ,
775 p_message_name OUT NOCOPY VARCHAR2 )
776 RETURN BOOLEAN AS
777 BEGIN
778 DECLARE
779 v_other_detail VARCHAR2(255);
780 v_closed_ind CHAR;
781 CURSOR c_attend_mode IS
782 SELECT closed_ind
783 FROM IGS_EN_ATD_MODE
784 WHERE attendance_mode = p_attend_mode;
785 BEGIN
786 -- Check if the attendance_mode is closed
787 p_message_name := Null;
788 OPEN c_attend_mode;
789 FETCH c_attend_mode INTO v_closed_ind;
790 IF (c_attend_mode%NOTFOUND) THEN
791 CLOSE c_attend_mode;
792 RETURN TRUE;
793 END IF;
794 IF (v_closed_ind = 'Y') THEN
795 p_message_name := 'IGS_PS_ATTEND_MODE_CLOSED';
796 CLOSE c_attend_mode;
797 RETURN FALSE;
798 END IF;
799 -- record is not closed
800 CLOSE c_attend_mode;
801 RETURN TRUE;
802 END;
803 END enrp_val_am_closed;
804 --
805 -- Validate if IGS_FI_GOVT_HEC_CNTB.govt_hecs_contrbn_band is closed.
806 FUNCTION finp_val_ghc_closed(
807 p_govt_hecs_cntrbtn_band IN NUMBER ,
808 p_message_name OUT NOCOPY VARCHAR2 )
809 RETURN BOOLEAN AS
810 gv_other_detail VARCHAR2(255);
811 BEGIN -- finp_val_ghc_closed
812 -- Validate if IGS_FI_GOVT_HEC_CNTB.govt_hecs_cntrbtn_band is closed.
813 DECLARE
814 CURSOR c_ghc(
815 cp_govt_hecs_cntrbtn_band
816 IGS_FI_GOVT_HEC_CNTB.govt_hecs_cntrbtn_band%TYPE) IS
817 SELECT closed_ind
818 FROM IGS_FI_GOVT_HEC_CNTB
819 WHERE govt_hecs_cntrbtn_band = cp_govt_hecs_cntrbtn_band;
820 v_ghc_rec c_ghc%ROWTYPE;
821 cst_yes CONSTANT CHAR := 'Y';
822 BEGIN
823 -- Set the default message number
824 p_message_name := Null;
825 -- Cursor handling
826 OPEN c_ghc(p_govt_hecs_cntrbtn_band);
827 FETCH c_ghc INTO v_ghc_rec;
828 IF c_ghc%NOTFOUND THEN
829 CLOSE c_ghc;
830 RETURN TRUE;
831 END IF;
832 CLOSE c_ghc;
833 IF v_ghc_rec.closed_ind = cst_yes THEN
834 p_message_name := 'IGS_FI_GOVTHECS_CONTRIB_CLS';
838 RETURN TRUE;
835 RETURN FALSE;
836 END IF;
837 -- Return the default value
839 END;
840 END finp_val_ghc_closed;
841
842 --
843 -- Validate the unit_class closed indicator.
844 /******************************************************************
845
846 Created By: Lakshmi.Priyadharshini
847
848 Date Created By: 08-09-2000
849
850 Purpose To Calculate Fee at Unit Class level.
851
852 Known limitations,enhancements,remarks:
853
854 Change History
855
856 Who When What
857
858 ******************************************************************/
859
860 FUNCTION unit_class_closed(
861 p_unit_class IN VARCHAR2 ,
862 p_message_name OUT NOCOPY VARCHAR2 )
863 RETURN BOOLEAN AS
864 BEGIN
865 DECLARE
866 v_other_detail VARCHAR2(255);
867 v_closed_ind CHAR;
868 CURSOR c_unit_class IS
869 SELECT closed_ind
870 FROM IGS_AS_UNIT_CLASS
871 WHERE unit_class = p_unit_class;
872 BEGIN
873 -- Check if the unit_class is closed
874 p_message_name := Null;
875 OPEN c_unit_class;
876 FETCH c_unit_class INTO v_closed_ind;
877 IF (c_unit_class%NOTFOUND) THEN
878 CLOSE c_unit_class;
879 RETURN TRUE;
880 END IF;
881 IF (v_closed_ind = 'Y') THEN
882 p_message_name := 'IGS_FI_UNIT_CLASS_CLOSED';
883 CLOSE c_unit_class;
884 RETURN FALSE;
885 END IF;
886 -- record is not closed
887 CLOSE c_unit_class;
888 RETURN TRUE;
889 END;
890 END unit_class_closed;
891 --
892 -- Validate if IGS_FI_GOV_HEC_PA_OP.govt_hecs_payment_opt is closed.
893 FUNCTION finp_val_ghpo_closed(
894 p_govt_hecs_payment_option IN VARCHAR2 ,
895 p_message_name OUT NOCOPY VARCHAR2 )
896 RETURN BOOLEAN AS
897 gv_other_detail VARCHAR2(255);
898 BEGIN -- finp_val_ghpo_closed
899 -- Validate if IGS_FI_GOV_HEC_PA_OP.govt_hecs_payment_option is closed.
900 DECLARE
901 CURSOR c_ghpo(
902 cp_govt_hecs_payment_option
903 IGS_FI_GOV_HEC_PA_OP.govt_hecs_payment_option%TYPE) IS
904 SELECT closed_ind
905 FROM IGS_FI_GOV_HEC_PA_OP
906 WHERE govt_hecs_payment_option = cp_govt_hecs_payment_option;
907 v_ghpo_rec c_ghpo%ROWTYPE;
908 cst_yes CONSTANT CHAR := 'Y';
909 BEGIN
910 -- Set the default message number
911 p_message_name := Null;
912 -- Cursor handling
913 OPEN c_ghpo(p_govt_hecs_payment_option);
914 FETCH c_ghpo INTO v_ghpo_rec;
915 IF c_ghpo%NOTFOUND THEN
916 CLOSE c_ghpo;
917 RETURN TRUE;
918 END IF;
919 CLOSE c_ghpo;
920 IF v_ghpo_rec.closed_ind = cst_yes THEN
921 p_message_name := 'IGS_EN_GOVT_HECS_PAY_OPT_CLOS';
922 RETURN FALSE;
923 END IF;
924 -- Return the default value
925 RETURN TRUE;
926 END;
927 END finp_val_ghpo_closed;
928 --
929 -- Ensure fee assessment rate can be created.
930 FUNCTION finp_val_far_ins(
931 p_fee_type IN VARCHAR2 ,
932 p_message_name OUT NOCOPY VARCHAR2 )
933 RETURN BOOLEAN AS
934 gv_other_detail VARCHAR2(255);
935 BEGIN -- finp_val_far_ins
936 -- Validate IGS_FI_FEE_AS_RATE.fee_type. If IGS_FI_FEE_TYPE.s_fee_trigger_cat = ?INSTITUTN?
937 -- or IGS_FI_FEE_TYPE.s_fee_type = ?HECS?, then assessment rates can only be defined
938 -- against fee_type_cal_instances.
939 DECLARE
940 CURSOR c_ft(
941 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
942 SELECT s_fee_trigger_cat,
943 s_fee_type
944 FROM IGS_FI_FEE_TYPE
945 WHERE fee_type = cp_fee_type;
946 v_ft_rec c_ft%ROWTYPE;
947 cst_institutn CONSTANT VARCHAR2(10) := 'INSTITUTN';
948 cst_hecs CONSTANT VARCHAR2(5) := 'HECS';
949 BEGIN
950 -- Set the default message number
951 p_message_name := Null;
952 -- Check parameters
953 IF p_fee_type IS NULL THEN
954 RETURN TRUE;
955 END IF;
956 -- Get the system fee trigger category of the fee_type.
957 OPEN c_ft (p_fee_type);
958 FETCH c_ft INTO v_ft_rec;
959 IF c_ft%NOTFOUND THEN
960 CLOSE c_ft;
961 RETURN TRUE;
962 END IF;
963 CLOSE c_ft;
964 IF v_ft_rec.s_fee_trigger_cat = cst_institutn THEN
965 p_message_name := 'IGS_FI_ASSRATE_ND_INSTITUTN';
966 RETURN FALSE;
967 END IF;
968 IF v_ft_rec.s_fee_type = cst_hecs THEN
969 p_message_name := 'IGS_FI_ASSRATE_ND_HECS';
970 RETURN FALSE;
971 END IF;
972 -- Return the default value
973 RETURN TRUE;
974 END;
975 END finp_val_far_ins;
976 --
977
978 --
979 -- Ensure fee ass rate relations are valid.
980 FUNCTION finp_val_far_rltn(
981 p_s_relation_type IN VARCHAR2 ,
982 p_fee_cat IN VARCHAR2 ,
983 p_message_name OUT NOCOPY VARCHAR2 )
984 RETURN BOOLEAN AS
985 gv_other_detail VARCHAR2(255);
986 BEGIN -- finp_val_far_relation
987 -- Validate IGS_FI_FEE_AS_RATE.fee_cat is only specified for the appropriate
988 -- IGS_FI_FEE_AS_RATE.s_relation_type
989 DECLARE
990 BEGIN
991 --- Set the default message number
995 RETURN TRUE;
992 p_message_name := Null;
993 -- Validate parameter values
994 IF p_s_relation_type IS NULL THEN
996 ELSIF p_s_relation_type NOT IN('FTCI','FCFL') THEN
997 p_message_name := 'IGS_FI_FINP_VAL_FAR_RLTN_CALL';
998 RETURN FALSE;
999 END IF;
1000 -- Validate that for relation type FTCI, fee_cat is NULL
1001 IF p_s_relation_type = 'FTCI' THEN
1002 IF p_fee_cat IS NULL THEN
1003 RETURN TRUE;
1004 ELSE
1005 p_message_name := 'IGS_FI_FEECAT_NULL_FEEASSRATE';
1006 RETURN FALSE;
1007 END IF;
1008 END IF;
1009 -- Validate that for relation type 'FCFL', fee_cat is NOT NULL
1010 IF p_s_relation_type = 'FCFL' THEN
1011 IF p_fee_cat IS NOT NULL THEN
1012 RETURN TRUE;
1013 ELSE
1014 p_message_name := 'IGS_FI_FEECAT_SPECIFY_FEEASS';
1015 RETURN FALSE;
1016 END IF;
1017 END IF;
1018 RETURN TRUE;
1019 END;
1020 END finp_val_far_rltn;
1021
1022 END IGS_FI_VAL_FAR;
[Home] [Help]