1 PACKAGE BODY IGS_FI_VAL_FT AS
2 /* $Header: IGSFI33B.pls 120.0 2005/06/01 18:46:11 appldev noship $ */
3 --
4 /*----------------------------------------------------------------------------
5 || Created By :
6 || Created On :
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 || rmaddipa 28-SEP-04 Enh #: 3880438 Retention Enhancements. Modified finp_val_ft_opt_pymt
13 || rmaddipa 22-SEP-04 Bug #: 3864296 Modified finp_val_ft_trig
14 || rmaddipa 02-SEP-04 Bug #: 3864296 Modified local functions of function finp_val_ft_trig
15 || rmaddipa 31-AUG-04 Bug #: 3864296 Modified a local function of function finp_val_ft_trig
16 ||
17 || vvutukur 26-Aug-2002 Bug#2531390.Modified function finp_val_ft_sftc.
18 ----------------------------------------------------------------------------*/
19
20 -- removed code related to table charge method apportionemtn as the table is being obsoleted (2187247) (rnirwani) (18.Jan.02)
21 -- Removed reference to IGS_FI_FEE_ENCMB table asthe table is obseleted as part of bug 2126091 sykrishn -30112001
22 -- Validate the optional payment indicator can be set to 'Y'.
23 FUNCTION finp_val_ft_opt_pymt(
24 p_fee_type IN VARCHAR2 ,
25 p_optional_payment_ind IN VARCHAR2 ,
26 p_message_name OUT NOCOPY VARCHAR2 )
27 RETURN BOOLEAN AS
28 ------------------------------------------------------------------
29 --Known limitations/enhancements and/or remarks:
30 --
31 --Change History:
32 --Who When What
33 --rmaddipa 28-SEP-2004 Enh #:3880438 . Added code to check whether any retention schedule exist at teaching period level
34 -- for the context fee type.
35 --------------------------------------------------------------------
36 BEGIN -- finp_val_ft_opt_pymt
37 -- Validate changes to the IGS_FI_FEE_TYPE.s_fee_trigger_cat, cannot be set yo 'Y'
38 -- when related fee_rentention_schedule records exist.
39 DECLARE
40 v_dummy VARCHAR2(1);
41 CURSOR c_fee_retention_schedule (
42 cp_fee_type IGS_FI_FEE_RET_SCHD.fee_type%TYPE) IS
43 SELECT 'x'
44 FROM IGS_FI_FEE_RET_SCHD frs
45 WHERE frs.fee_type = cp_fee_type;
46 -- Removed reference to IGS_FI_FEE_ENCMB(c_fee_encumbrance cursor) table asthe table is obseleted as part of bug 2126091 sykrishn -30112001
47
48 --cursor to check whether any retention schedule exists at teaching period level for a given fee type.
49 CURSOR cur_tp_ret_schd (
50 cp_fee_type igs_fi_fee_type_all.fee_type%TYPE) IS
51 SELECT 'X'
52 FROM igs_fi_tp_ret_schd
53 WHERE fee_type = cp_fee_type;
54
55 BEGIN
56 p_message_name := NULL;
57 -- Validate parameters
58 IF(p_fee_type IS NULL OR
59 p_optional_payment_ind IS NULL) THEN
60 p_message_name := NULL;
61 Return TRUE;
62 END IF;
63 -- Check if the optional_payment_ind is set to 'Y' if it is look for related
64 -- IGS_FI_FEE_RET_SCHD
65 IF(p_optional_payment_ind = 'Y') THEN
66 -- Check for Fee Retention Schedules related to the Fee Type
67 OPEN c_fee_retention_schedule(
68 p_fee_type);
69 FETCH c_fee_retention_schedule INTO v_dummy;
70 IF(c_fee_retention_schedule%FOUND) THEN
71 CLOSE c_fee_retention_schedule;
72 p_message_name := 'IGS_FI_OPPYMNT_Y_FEERETSCH';
73 RETURN FALSE;
74 END IF;
75 CLOSE c_fee_retention_schedule;
76
77 OPEN cur_tp_ret_schd(cp_fee_type => p_fee_type);
78 FETCH cur_tp_ret_schd INTO v_dummy;
79 IF (cur_tp_ret_schd%FOUND) THEN
80 CLOSE cur_tp_ret_schd;
81 p_message_name := 'IGS_FI_OPPYMNT_Y_FEERETSCH';
82 RETURN FALSE;
83 END IF;
84 CLOSE cur_tp_ret_schd;
85 -- Check for Fee Encumbrances related to the Fee Type
86 -- Removed reference to IGS_FI_FEE_ENCMB table asthe table is obseleted as part of bug 2126091 sykrishn -30112001
87 END IF;
88 RETURN TRUE;
89 END;
90 END finp_val_ft_opt_pymt;
91 --
92 -- Validate changes to s_fee_trigger_cat.
93 FUNCTION finp_val_ft_trig(
94 p_fee_type IN VARCHAR2 ,
95 p_new_s_fee_trigger_cat IN VARCHAR2 ,
96 p_old_s_fee_trigger_cat IN VARCHAR2 ,
97 p_message_name OUT NOCOPY VARCHAR2 )
98 RETURN BOOLEAN AS
99
100 ------------------------------------------------------------------
101 --Known limitations/enhancements and/or remarks:
102 --
103 --Change History:
104 --Who When What
105 --rmaddipa 22-SEP-04 Bug #:3864296 added calls to functions finpl_get_course_type_trig() and
106 -- finpl_get_course_group_trig() to check for existence of Program Type and Program Group triggers
107 --rmaddipa 01-SEP-04 Bug #: 3864296
108 -- Local functions finpl_get_course_type_trig,
109 -- finpl_get_course_group_trig,
110 -- finpl_get_unit_trig,
111 -- finpl_get_unit_set_trig Modified
112 --rmaddipa 31-AUG-04 Bug #: 3864296
113 -- Local function finpl_get_course_trig Modified
114 -------------------------------------------------------------------
115
116
117 BEGIN -- finp_val_ft_trig
118 -- Validate changes to the IGS_FI_FEE_TYPE.s_fee_trigger_cat.
119 -- If changing from INSTITUTN to anything else ensure there are
120 -- no related IGS_FI_FEE_AS records.
121 -- If changing to INSTITUTN from anything else ensure there are no
122 -- related IGS_FI_FEE_AS records
123 -- and that there are no related IGS_PS_COURSE, IGS_PS_UNIT or IGS_PS_UNIT set triggers.
124 -- If changing to IGS_PS_COURSE ensure there are only IGS_PS_COURSE triggers.
125 -- If changing to IGS_PS_UNIT ensure there are only IGS_PS_UNIT triggers.
126 -- If changing to UNITSET ensure there are only IGS_PS_UNIT set triggers.
127 DECLARE
128 cst_institutn CONSTANT VARCHAR2(10) := 'INSTITUTN';
129 cst_unit CONSTANT VARCHAR2(10) := 'UNIT';
130 cst_unitset CONSTANT VARCHAR2(10) := 'UNITSET';
131 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
132 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
133 v_dummy VARCHAR2(1);
134 FUNCTION finpl_get_fee_ass(
135 p_fee_type IGS_FI_FEE_AS.fee_type%TYPE)
136 RETURN BOOLEAN AS
137 BEGIN
138 DECLARE
139 v_dummy VARCHAR2(1);
140 CURSOR c_fee_ass (
141 cp_fee_type IGS_FI_FEE_AS.fee_type%TYPE) IS
142 SELECT 'x'
143 FROM IGS_FI_FEE_AS fa
144 WHERE fa.fee_type = cp_fee_type;
145 BEGIN
146 -- Check for Fee Assessment records in the IGS_FI_FEE_AS table. If any are found
147 -- the function should return TRUE otherwise return FALSE.
148 OPEN c_fee_ass(
149 p_fee_type);
150 FETCH c_fee_ass INTO v_dummy;
151 IF(c_fee_ass%FOUND) THEN
152 CLOSE c_fee_ass;
153 RETURN TRUE;
154 END IF;
155 CLOSE c_fee_ass;
156 RETURN FALSE;
157 END;
158 END finpl_get_fee_ass;
159 FUNCTION finpl_get_course_type_trig(
160 p_fee_type IGS_PS_TYPE_FEE_TRG.fee_type%TYPE)
161 RETURN BOOLEAN AS
162 ------------------------------------------------------------------
163 --Change History:
164 --Who When What
165 --rmaddipa 2-SEP-04 Bug #: 3864296
166 -- Modified the definition of cursor c_crs_typ_fee_trig
167 -- so that the records in table IGS_PS_TYPE_FEE_TRG which are logically deleted
168 -- are not considered.
169 -------------------------------------------------------------------
170 BEGIN
171 DECLARE
172 v_dummy VARCHAR2(1);
173
174 -- cursor definition modified by rmaddipa ( Bug #: 3864296)
175 -- records that are deleted should'nt be considered
176 CURSOR c_crs_typ_fee_trig (
177 cp_fee_type IGS_PS_TYPE_FEE_TRG.fee_type%TYPE) IS
178 SELECT 'x'
179 FROM IGS_PS_TYPE_FEE_TRG ctft
180 WHERE ctft.fee_type = cp_fee_type
181 AND logical_delete_dt IS NULL;
182 BEGIN
183 -- Check for IGS_PS_COURSE Trigger records in the IGS_PS_TYPE_FEE_TRG table.
184 -- If any are found the function should return TRUE otherwise return FALSE.
185 OPEN c_crs_typ_fee_trig(
186 p_fee_type);
187 FETCH c_crs_typ_fee_trig INTO v_dummy;
188 IF(c_crs_typ_fee_trig%FOUND) THEN
189 CLOSE c_crs_typ_fee_trig;
190 RETURN TRUE;
191 END IF;
192 CLOSE c_crs_typ_fee_trig;
193 RETURN FALSE;
194 END;
195 END finpl_get_course_type_trig;
196 FUNCTION finpl_get_course_group_trig(
197 p_fee_type IGS_PS_GRP_FEE_TRG.fee_type%TYPE)
198 RETURN BOOLEAN AS
199 ------------------------------------------------------------------
200 --Change History:
201 --Who When What
202 --rmaddipa 2-SEP-04 Bug #: 3864296
203 -- Modified the definition of cursor c_crs_grp_fee_trig
204 -- so that the records in table IGS_PS_GRP_FEE_TRG which are logically deleted
205 -- are not considered.
206 -------------------------------------------------------------------
207 BEGIN
208 DECLARE
209 v_dummy VARCHAR2(1);
210
211 -- cursor definition modified by rmaddipa ( Bug #: 3864296)
212 -- records that are deleted should'nt be considered
213 CURSOR c_crs_grp_fee_trig (
214 cp_fee_type IGS_PS_GRP_FEE_TRG.fee_type%TYPE) IS
215 SELECT 'x'
216 FROM IGS_PS_GRP_FEE_TRG cgft
217 WHERE cgft.fee_type = cp_fee_type
218 AND logical_delete_dt IS NULL;
219 BEGIN
220 -- Check for IGS_PS_COURSE Trigger records in the IGS_PS_GRP_FEE_TRG table.
221 -- If any are found the function should return TRUE otherwise return FALSE.
222 OPEN c_crs_grp_fee_trig(
223 p_fee_type);
224 FETCH c_crs_grp_fee_trig INTO v_dummy;
225 IF(c_crs_grp_fee_trig%FOUND) THEN
226 CLOSE c_crs_grp_fee_trig;
227 RETURN TRUE;
228 END IF;
229 CLOSE c_crs_grp_fee_trig;
230 RETURN FALSE;
231 END;
232 END finpl_get_course_group_trig;
233 FUNCTION finpl_get_course_trig(
234 p_fee_type IGS_PS_TYPE_FEE_TRG.fee_type%TYPE)
235 RETURN BOOLEAN AS
236 ------------------------------------------------------------------
237 --Change History:
238 --Who When What
239 --rmaddipa 22-SEP-04 Bug #:3864296 removed calls to functions finpl_get_course_type_trig() and
240 -- finpl_get_course_group_trig() since they are being called from finp_val_ft_trig().
241 --rmaddipa 31-AUG-04 Bug #: 3864296
242 -- Modified the definition of cursor c_crs_fee_trig
243 -- so that the records in table IGS_PS_FEE_TRG which are logically deleted
244 -- are not considered.
245 -------------------------------------------------------------------
246 BEGIN
247 DECLARE
248 v_dummy VARCHAR2(1);
249
250 -- cursor definition modified by rmaddipa ( Bug #: 3864296)
251 -- records that are deleted should'nt be considered
252 CURSOR c_crs_fee_trig (
253 cp_fee_type IGS_PS_FEE_TRG.fee_type%TYPE) IS
254 SELECT 'x'
255 FROM IGS_PS_FEE_TRG cft
256 WHERE cft.fee_type = cp_fee_type
257 AND logical_delete_dt IS NULL;
258
259 BEGIN
260 -- Check for IGS_PS_COURSE Trigger records in the IGS_PS_TYPE_FEE_TRG,
261 -- IGS_PS_GRP_FEE_TRG OR IGS_PS_FEE_TRG tables. If any are found
262 -- the function should return TRUE otherwise return FALSE.
263 OPEN c_crs_fee_trig (p_fee_type);
264 FETCH c_crs_fee_trig INTO v_dummy;
265 IF(c_crs_fee_trig%FOUND) THEN
266 CLOSE c_crs_fee_trig;
267 RETURN TRUE;
268 END IF;
269 CLOSE c_crs_fee_trig;
270 RETURN FALSE;
271 END;
272 END finpl_get_course_trig;
273 FUNCTION finpl_get_unit_trig(
274 p_fee_type IGS_FI_UNIT_FEE_TRG.fee_type%TYPE)
275 RETURN BOOLEAN AS
276 ------------------------------------------------------------------
277 --Change History:
278 --Who When What
279 --rmaddipa 2-SEP-04 Bug #: 3864296
280 -- Modified the definition of cursor c_unit_fee_trig
281 -- so that the records in table IGS_FI_UNIT_FEE_TRG which are logically deleted
282 -- are not considered.
283 -------------------------------------------------------------------
284 BEGIN
285 DECLARE
286 v_dummy VARCHAR2(1);
287 -- cursor definition modified by rmaddipa ( Bug #: 3864296)
288 -- records that are deleted should'nt be considered
289 CURSOR c_unit_fee_trig (
290 cp_fee_type IGS_FI_UNIT_FEE_TRG.fee_type%TYPE) IS
291 SELECT 'x'
292 FROM IGS_FI_UNIT_FEE_TRG uft
293 WHERE uft.fee_type = cp_fee_type
294 AND logical_delete_dt IS NULL;
295 BEGIN
296 -- Check if there are any IGS_PS_UNIT Triggers in the IGS_FI_UNIT_FEE_TRG table.
297 -- If any are found the function should return TRUE otherwise return FALSE.
298 OPEN c_unit_fee_trig(
299 p_fee_type);
300 FETCH c_unit_fee_trig INTO v_dummy;
301 IF(c_unit_fee_trig%FOUND) THEN
302 CLOSE c_unit_fee_trig;
303 RETURN TRUE;
304 END IF;
305 CLOSE c_unit_fee_trig;
306 RETURN FALSE;
307 END;
308 END finpl_get_unit_trig;
309 FUNCTION finpl_get_unit_set_trig(
310 p_fee_type IGS_EN_UNITSETFEETRG.fee_type%TYPE)
311 RETURN BOOLEAN AS
312 ------------------------------------------------------------------
313 --Change History:
314 --Who When What
315 --rmaddipa 2-SEP-04 Bug #: 3864296
316 -- Modified the definition of cursor c_unit_set_fee_trig
317 -- so that the records in table IGS_EN_UNITSETFEETRG which are logically deleted
318 -- are not considered.
319 -------------------------------------------------------------------
320 BEGIN
321 DECLARE
322 v_dummy VARCHAR2(1);
323 -- cursor definition modified by rmaddipa ( Bug #: 3864296)
324 -- records that are deleted should'nt be considered
325 CURSOR c_unit_set_fee_trig(
326 cp_fee_type IGS_EN_UNITSETFEETRG.fee_type%TYPE) IS
327 SELECT 'x'
328 FROM IGS_EN_UNITSETFEETRG usft
329 WHERE usft.fee_type = cp_fee_type
330 AND logical_delete_dt IS NULL;
331 BEGIN
332 -- Check if there are any IGS_PS_UNIT Set Triggers in the IGS_EN_UNITSETFEETRG table.
333 -- If any are found the function should return TRUE otherwise return FALSE.
334 OPEN c_unit_set_fee_trig(
335 p_fee_type);
336 FETCH c_unit_set_fee_trig INTO v_dummy;
337 IF(c_unit_set_fee_trig%FOUND) THEN
338 CLOSE c_unit_set_fee_trig;
339 RETURN TRUE;
340 END IF;
341 CLOSE c_unit_set_fee_trig;
342 RETURN FALSE;
343 END;
344 END finpl_get_unit_set_trig;
345 BEGIN -- finp_val_ft_trig
346 p_message_name := NULL;
347 -- Validate parameters
348 IF(p_fee_type IS NULL OR
349 p_old_s_fee_trigger_cat IS NULL OR
350 p_new_s_fee_trigger_cat IS NULL) THEN
351 p_message_name := NULL;
352 Return TRUE;
353 END IF;
354 -- Check trigger details if the s_fee_trigger_cat has changed
355 IF(p_old_s_fee_trigger_cat <> p_new_s_fee_trigger_cat) THEN
356 IF(p_old_s_fee_trigger_cat = cst_institutn OR
357 p_new_s_fee_trigger_cat = cst_institutn) THEN
358 IF(finpl_get_fee_ass(p_fee_type) = TRUE) THEN
359 p_message_name := 'IGS_FI_CAT_INSTITUTN';
360 Return FALSE;
361 END IF;
362 END IF;
363 IF(p_new_s_fee_trigger_cat = cst_institutn OR
364 p_new_s_fee_trigger_cat = cst_unit OR
365 p_new_s_fee_trigger_cat = cst_unitset) THEN
366 IF(finpl_get_course_trig(p_fee_type) = TRUE) THEN
367 p_message_name := 'IGS_FI_CAT_PRGFEE_TRG_EXISTS';
368 Return FALSE;
369 END IF;
370 IF(finpl_get_course_type_trig(p_fee_type) = TRUE) THEN
371 p_message_name := 'IGS_FI_NOTCHG_SYSFEE_TRGCAT';
372 Return FALSE;
373 END IF;
374 IF(finpl_get_course_group_trig(p_fee_type) = TRUE) THEN
375 p_message_name := 'IGS_FI_NOTCHG_SYSFEE_TRGCAT';
376 Return FALSE;
377 END IF;
378 END IF;
379 IF(p_new_s_fee_trigger_cat = cst_institutn OR
380 p_new_s_fee_trigger_cat = cst_course OR
381 p_new_s_fee_trigger_cat = cst_unitset) THEN
382 IF(finpl_get_unit_trig(p_fee_type) = TRUE) THEN
383 p_message_name := 'IGS_FI_CAT_UNITFEE_TRG_EXISTS';
384 Return FALSE;
385 END IF;
386 END IF;
387 IF(p_new_s_fee_trigger_cat = cst_institutn OR
388 p_new_s_fee_trigger_cat = cst_course OR
389 p_new_s_fee_trigger_cat = cst_unit) THEN
390 IF(finpl_get_unit_set_trig(p_fee_type) = TRUE) THEN
391 p_message_name := 'IGS_FI_CAT_UNIT_SETFEE_TRG';
392 Return FALSE;
393 END IF;
394 END IF;
398 Return FALSE;
395 IF(p_new_s_fee_trigger_cat = cst_composite) THEN
396 IF(finpl_get_course_type_trig(p_fee_type) = TRUE) THEN
397 p_message_name := 'IGS_FI_NOTCHG_SYSFEE_TRGCAT';
399 END IF;
400 IF(finpl_get_course_group_trig(p_fee_type) = TRUE) THEN
401 p_message_name := 'IGS_FI_NOTCHG_SYSFEE_TRGCAT';
402 Return FALSE;
403 END IF;
404 END IF;
405 END IF;
406 RETURN TRUE;
407 END;
408 END finp_val_ft_trig;
409 --
410 -- Validate the s_fee_type and s_fee_trigger_cat are compatible.
411 FUNCTION finp_val_ft_sft_trig(
412 p_s_fee_type IN VARCHAR2 ,
413 p_s_fee_trigger_cat IN VARCHAR2 ,
414 p_message_name OUT NOCOPY VARCHAR2 )
415 RETURN BOOLEAN AS
416 BEGIN
417 -- finp_val_ft_sft_trig
418 -- Validate the IGS_FI_FEE_TYPE.s_fee_type and fee_type.s_fee_trigger_cat are
419 -- compatible.
420 DECLARE
421 BEGIN
422 p_message_name := NULL;
423 -- Validate parameters
424 IF ( p_s_fee_type IS NULL OR p_s_fee_trigger_cat IS NULL) THEN
425 RETURN TRUE;
426 END IF;
427 -- Check the system fee type against the system trigger category
428 IF p_s_fee_type IN ('HECS', 'TUITION') AND
429 p_s_fee_trigger_cat NOT IN ('COURSE', 'UNITSET') THEN
430 p_message_name := 'IGS_FI_SYSFEETYPE_HECS_TUITIO';
431 RETURN FALSE;
432 END IF;
433 RETURN TRUE;
434 END;
435 END finp_val_ft_sft_trig;
436 --
437 -- Validate changes to s_fee_trigger_cat.
438 FUNCTION finp_val_ft_sftc(
439 p_fee_type IN VARCHAR2 ,
440 p_new_s_fee_trigger_cat IN VARCHAR2 ,
441 p_old_s_fee_trigger_cat IN VARCHAR2 ,
442 p_message_name OUT NOCOPY VARCHAR2 )
443 RETURN BOOLEAN AS
444 /*----------------------------------------------------------------------------
445 || Created By :
446 || Created On :
447 || Purpose :
448 || Known limitations, enhancements or remarks :
449 || Change History :
450 || Who When What
451 || (reverse chronological order - newest change first)
452 || vvutukur 26-Aug-2002 Bug#2531390.Removed cursor C_FEE_PAYMENT_SCHEDULE and related code, in
453 || which there is Check for FCFL level Fee Payment Schedules related to
454 || the Fee Type.
455 ----------------------------------------------------------------------------*/
456 BEGIN -- finp_val_ft_sftc
457 -- Validate changes to the IGS_FI_FEE_TYPE.s_fee_trigger_cat.
458 -- If changing to INSTITUTN from anything else ensure there are no
459 -- related IGS_FI_F_CAT_FEE_LBL records with SI_FI_S_CHG_MTH or
460 -- rule_sequence_number set.
461 -- If changing to INSTITUTN from anything else ensure there are no
462 -- related records at FCFL level for the following:
463 -- IGS_FI_FEE_PAY_SCHD
464 -- IGS_FI_FEE_RET_SCHD
465 -- IGS_FI_FEE_ENCMB - Removed reference from this package-
466 -- IGS_FI_CHG_MTH_APP
467 -- IGS_FI_FEE_AS_RATE
468 -- IGS_FI_ELM_RANGE
469 DECLARE
470 v_dummy VARCHAR2(1);
471 CURSOR c_fee_cat_fee_liability (cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
472 SELECT 'x'
473 FROM IGS_FI_F_CAT_FEE_LBL
474 WHERE FEE_TYPE = cp_fee_type AND
475 (s_chg_method_type IS NOT NULL OR
476 rul_sequence_number IS NOT NULL);
477
478 CURSOR c_fee_retention_schedule (cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
479 SELECT 'x'
480 FROM IGS_FI_FEE_RET_SCHD
481 WHERE FEE_TYPE = cp_fee_type AND
482 s_relation_type = 'FCFL';
483
484 -- Removed reference to IGS_FI_FEE_ENCMB table asthe table is obseleted as part of bug 2126091 sykrishn -30112001
485
486 -- removed the cursor selecting data from the charge method apportionment table
487 -- tbl obsoleted as a prt of bug - 2187247 (rnirwani)
488
489 CURSOR c_fee_ass_rate (cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
490 SELECT 'x'
491 FROM IGS_FI_FEE_AS_RATE
492 WHERE FEE_TYPE = cp_fee_type AND
493 s_relation_type = 'FCFL' AND
494 logical_delete_dt IS NULL;
495 CURSOR c_elements_range (cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
496 SELECT 'x'
497 FROM IGS_FI_ELM_RANGE
498 WHERE FEE_TYPE = cp_fee_type AND
499 s_relation_type = 'FCFL' AND
500 logical_delete_dt IS NULL;
501 BEGIN
502 p_message_name := NULL;
503 -- Validate parameters
504 IF(p_fee_type IS NULL OR
505 p_old_s_fee_trigger_cat IS NULL OR
506 p_new_s_fee_trigger_cat IS NULL) THEN
507 p_message_name := NULL;
508 Return TRUE;
509 END IF;
510 IF p_new_s_fee_trigger_cat = 'INSTITUTN' AND
511 p_old_s_fee_trigger_cat <> p_new_s_fee_trigger_cat THEN
512 -- Check for fee cat fee liability records related to this Fee Type
513 OPEN c_fee_cat_fee_liability(p_fee_type);
514 FETCH c_fee_cat_fee_liability INTO v_dummy;
515 IF(c_fee_cat_fee_liability%FOUND) THEN
516 CLOSE c_fee_cat_fee_liability;
517 p_message_name := 'IGS_FI_SYS_FEE_TRGCAT_NOTCHG';
518 RETURN FALSE;
519 END IF;
520 CLOSE c_fee_cat_fee_liability;
521
522 -- Check for FCFL level Fee Retention Schedules related to this Fee Type
523 OPEN c_fee_retention_schedule(p_fee_type);
524 FETCH c_fee_retention_schedule INTO v_dummy;
525 IF(c_fee_retention_schedule%FOUND) THEN
526 CLOSE c_fee_retention_schedule;
527 p_message_name := 'IGS_FI_TRGCAT_FEE_RETNSCH';
528 RETURN FALSE;
529 END IF;
530 CLOSE c_fee_retention_schedule;
531 -- Check for FCFL level Fee Encumbrances related to this Fee Type
532 -- Removed reference to IGS_FI_FEE_ENCMB table asthe table is obseleted as part of bug 2126091 sykrishn -30112001
533
534 -- Check for FCFL level Charge Method Apportions related to this Fee Type
535 -- removed the reference to charge method approtionemnt table since this has been obsoleted. bug-2187247 (rnirwani)
536
537
538 -- Check for FCFL level Fee Ass Rates related to this Fee Type
539 OPEN c_fee_ass_rate (p_fee_type);
540 FETCH c_fee_ass_rate INTO v_dummy;
541 IF(c_fee_ass_rate%FOUND) THEN
542 CLOSE c_fee_ass_rate;
543 p_message_name := 'IGS_FI_TRGSCH_FEEASS_RATE';
544 RETURN FALSE;
545 END IF;
546 CLOSE c_fee_ass_rate;
547 -- Check for FCFL level Elements Range records related to this Fee Type
548 OPEN c_elements_range (p_fee_type);
549 FETCH c_elements_range INTO v_dummy;
550 IF(c_elements_range%FOUND) THEN
551 CLOSE c_elements_range;
552 p_message_name := 'IGS_FI_TRGSCH_ELERNG_RECORD';
553 RETURN FALSE;
554 END IF;
555 CLOSE c_elements_range;
556 END IF;
557 Return TRUE;
558 END;
559 END finp_val_ft_sftc;
560 END IGS_FI_VAL_FT;
[Home] [Help]