1 PACKAGE BODY IGS_FI_VAL_FAS AS
2 /* $Header: IGSFI23B.pls 120.2 2006/02/23 21:33:05 skharida noship $ */
3 /* Who When What
4 skharida 09-feb-2006 Bug# 5018036 - SQL Tuning, changed to IGS_LOOKUPS_VAL instead to IGS_LOOKUPS_VIEW.
5 uudayapr 07-dec-2004 ENH# 3167098, Modified finp_val_fas_ass_ind.
6 shtatiko 24-DEC-2003 Enh# 3167098, Modified finp_val_fas_ass_ind.
7 uudayapr 12-dec-2003 Bug#3080983 Modified the cursor c_fadv in the Function
8 finp_val_fas_balance .
9 vvutukur 19-Dec-2002 Bug#2680885. Modified finp_val_fas_balance.
10 masehgal 17-Jan-2002 ENH # 2170429
11 Obsoletion of SPONSOR_CD related Parameters and Check from Function FINP_VAL_FAS_UPD
12 schodava 28-NOV-2001 Enh # 2122257 : Implements the CR for 'Fee Category Change'
13 Change in function finp_val_fas_ass_ind
14 jbegum 26-Nov-2001 As part of bug #2040038
15 1)Replaced RAISE NO_DATA_FOUND in code with message IGS_FI_FEE_ASS_DAT
16 2)In the procedure finp_val_fas_upd TRUNC function was added in the IF condition
17 checks being done on effective_dt and transaction_dt
18 */
19 --
20 -- Validate fee assessable indicator value.
21 FUNCTION finp_val_fas_ass_ind(
22 p_person_id IN NUMBER ,
23 p_course_cd IN VARCHAR2 ,
24 p_fee_cat IN VARCHAR2 ,
25 p_effective_dt IN DATE ,
26 p_transaction_type IN VARCHAR2 ,
27 p_fee_cal_type IN VARCHAR2 DEFAULT NULL,
28 p_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
29 p_message_name OUT NOCOPY VARCHAR2 )
30 RETURN BOOLEAN AS
31 /*************************************************************
32 Created By :
33 Date Created By :
34 Purpose :
35 Know limitations, enhancements or remarks
36 Change History
37 Who When What
38 uudayapr 07-dec-2004 Enh# 3167098, Modified cursor c_sca_scas and added the logic for deriving the
39 Load calendar type and sequence number.
40 shtatiko 24-DEC-2003 Enh# 3167098, Modified cursor c_sca_scas.
41 SCHODAVA 28-NOV-2001 Enh # 2122257
42 (SFCR015 : Change In Fee Category)
43 Changed the signature of this function.
44 Added params fee_cal_type and fee_ci_sequence_number
45 (reverse chronological order - newest change first)
46 ***************************************************************/
47 gv_other_detail VARCHAR2(255);
48 BEGIN --finp_val_fas_ass_ind
49 --validate IGS_FI_FEE_AS.course_cd has a course_attempt_status with a fee_ass_ind
50 --of 'Y'
51 DECLARE
52 v_system_generated_ind IGS_LOOKUPS_VAL.system_generated_ind%TYPE;
53 v_fee_ass_ind IGS_LOOKUPS_VAL.fee_ass_ind%TYPE;
54 v_course_attempt_status IGS_LOOKUPS_VAL.lookup_code%TYPE;
55 -- added this variable for stroing the load calendar derived data.
56 l_v_load_cal_type igs_fi_f_cat_ca_inst.fee_cal_type%TYPE;
57 l_n_load_ci_seq_number igs_fi_f_cat_ca_inst.fee_ci_sequence_number%TYPE;
58 CURSOR c_strty IS
59 SELECT strty.system_generated_ind
60 FROM IGS_LOOKUPS_VAL strty
61 WHERE strty.lookup_code = p_transaction_type AND
62 strty.lookup_type = 'TRANSACTION_TYPE';
63
64 -- Enh# 3167098, Removed reference to igs_fi_f_cat_cal_rel and igs_fi_stdnt_ps_att_cat_v
65 -- Modified The Select Clause Of The Cursor
66 CURSOR c_sca_scas IS
67 SELECT scas.fee_ass_ind,
68 std.course_attempt_status
69 FROM igs_en_spa_terms sca,
70 igs_en_stdnt_ps_att std,
71 igs_lookups_view scas
72 WHERE sca.person_id = p_person_id
73 AND sca.program_cd = p_course_cd
74 AND sca.term_cal_type = l_v_load_cal_type
75 AND sca.term_sequence_number = l_n_load_ci_seq_number
76 AND sca.person_id = std.person_id
77 AND sca.program_cd = std.course_cd
78 AND ((sca.fee_cat IS NULL AND p_fee_cat IS NULL ) OR ( sca.fee_cat = p_fee_cat))
79 AND scas.lookup_type = 'CRS_ATTEMPT_STATUS'
80 AND scas.lookup_code = std.course_attempt_status;
81
82 CURSOR c_scaehv IS
83 SELECT scas.fee_ass_ind,
84 scaehv.course_attempt_status
85 FROM IGS_AS_SCAH_EFFECTIVE_H_V scaehv,
86 IGS_LOOKUPS_VIEW scas
87 WHERE
88 scaehv.person_id = p_person_id AND
89 scaehv.course_cd = p_course_cd AND
90 ((scaehv.fee_cat IS NULL AND
91 p_fee_cat IS NULL) OR
92 (scaehv.fee_cat = p_fee_cat)) AND
93 scas.lookup_type = 'CRS_ATTEMPT_STATUS' AND
94 TRUNC(p_effective_dt) BETWEEN scaehv.effective_start_dt AND scaehv.effective_end_dt AND
95 scas.lookup_code = scaehv.course_attempt_status;
96
97
98 BEGIN
99 --Set the default message number
100 p_message_name := NULL;
101 --Validate parameters (all must have values to proceed with validation)
102 IF (p_person_id IS NULL OR
103 p_course_cd IS NULL OR
104 p_fee_cat IS NULL OR
105 p_effective_dt IS NULL OR
106 p_transaction_type IS NULL OR
107 p_fee_cal_type IS NULL OR
108 p_fee_ci_sequence_number IS NULL) THEN
109 RETURN TRUE;
110 END IF;
111 OPEN c_strty;
112 FETCH c_strty INTO v_system_generated_ind;
113 CLOSE c_strty;
114 IF (v_system_generated_ind = 'N') THEN
115 -- Get student IGS_PS_COURSE attempt detail
116 IF TRUNC(p_effective_dt) >= TRUNC(SYSDATE) THEN
117 --derive the load calendar from the fee calendar data to be used in
118 IF NOT igs_fi_gen_001.finp_get_lfci_reln ( p_cal_type => p_fee_cal_type,
119 p_ci_sequence_number => p_fee_ci_sequence_number,
120 p_cal_category => 'FEE',
121 p_ret_cal_type => l_v_load_cal_type,
122 p_ret_ci_sequence_number => l_n_load_ci_seq_number,
123 p_message_name =>p_message_name) THEN
124 RETURN FALSE;
125 END IF;
126 -- use current data
127 OPEN c_sca_scas;
128 FETCH c_sca_scas INTO v_fee_ass_ind,
129 v_course_attempt_status;
130 IF (c_sca_scas%NOTFOUND) THEN
131 CLOSE c_sca_scas;
132 p_message_name := 'IGS_FI_FEE_ASS_DAT';
133 RETURN FALSE;
134 END IF;
135 CLOSE c_sca_scas;
136 ELSE -- look back into history
137 OPEN c_scaehv;
138 FETCH c_scaehv INTO v_fee_ass_ind,
139 v_course_attempt_status;
140 IF (c_scaehv%NOTFOUND) THEN
141 CLOSE c_scaehv;
142 p_message_name := 'IGS_FI_FEE_ASS_DAT';
143 RETURN FALSE;
144 END IF;
145 CLOSE c_scaehv;
146 END IF;
147 -- Test student IGS_PS_COURSE attempt is fee assessible
148 IF v_fee_ass_ind = 'N' THEN
149 IF v_course_attempt_status <> 'UNCONFIRM' THEN
150 p_message_name := 'IGS_FI_STUD_PRGATTEM_STATUS_Y' ;
151 RETURN FALSE;
152 ELSE
153 p_message_name := 'IGS_FI_STUD_PRGATT_STATUS_Y' ;
154 RETURN TRUE; -- warning only
155 END IF;
156 END IF;
157 END IF;
158 RETURN TRUE;
159 END;
160 END finp_val_fas_ass_ind;
161 --
162 -- Validate retrospective date of fee assessment period.
163 FUNCTION finp_val_fas_retro(
164 p_fee_type IN VARCHAR2 ,
165 p_fee_cal_type IN VARCHAR2 ,
166 p_fee_ci_sequence_number IN NUMBER ,
167 p_fee_cat IN VARCHAR2 ,
168 p_message_name OUT NOCOPY VARCHAR2 )
169 RETURN BOOLEAN AS
170 gv_other_detail VARCHAR2(255);
171 BEGIN --finp_val_fas_retro
172 --validate the current date against any retrospective assessment period
173 --when recording a manual fee assessment.
174 --Current date must be <= IGS_FI_F_CAT_FEE_LBL_V.retro_dt (if specified,
175 --may be null)
176 DECLARE
177 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
178 CURSOR c_fcflv IS
179 SELECT daiv.alias_val
180 FROM IGS_FI_F_CAT_FEE_LBL_V fcflv,
181 IGS_CA_DA_INST_V daiv
182 WHERE
183 fcflv.fee_cal_type = p_fee_cal_type AND
184 fcflv.fee_ci_sequence_number = p_fee_ci_sequence_number AND
185 fcflv.fee_type = p_fee_type AND
186 fcflv.fee_cat = p_fee_cat AND
187 fcflv.retro_dt_alias = daiv.dt_alias AND
188 fcflv.retro_dai_sequence_number = daiv.sequence_number AND
189 fcflv.fee_cal_type = daiv.cal_type AND
190 fcflv.fee_ci_sequence_number = daiv.ci_sequence_number AND
191 daiv.alias_val < SYSDATE;
192 BEGIN
193 --Set the default message number
194 p_message_name := NULL;
195 --Validate parameters
196 IF (p_fee_type IS NULL OR
197 p_fee_cal_type IS NULL OR
198 p_fee_ci_sequence_number IS NULL OR
199 p_fee_cat IS NULL) THEN
200 RETURN TRUE;
201 END IF;
202 --If a record exists then daiv.alias_val > SYSDATE so set p_message_name
203 OPEN c_fcflv;
204 FETCH c_fcflv INTO v_alias_val;
205 IF (c_fcflv%FOUND) THEN
206 p_message_name := 'IGS_FI_RETRO_ASS_DATE';
207 CLOSE c_fcflv;
208 RETURN FALSE;
209 END IF;
210 CLOSE c_fcflv;
211 RETURN TRUE;
212 END;
213 END finp_val_fas_retro;
214 --
215 -- Validate IGS_FI_FEE_AS.SI_FI_S_TRN_TYPE for a manual assessment.
216 FUNCTION finp_val_fas_cat(
217 p_transaction_type IN VARCHAR2 ,
218 p_message_name OUT NOCOPY VARCHAR2 )
219 RETURN BOOLEAN AS
220 gv_other_detail VARCHAR2(255);
221 BEGIN --finp_val_fas_cat
222 --This module validates IGS_FI_FEE_AS.SI_FI_S_TRN_TYPE.
223 --SI_FI_S_TRN_TYPE.transaction_cat must equal 'DEBT' and
224 --s_tranaction_type.system_generated_ind must equal 'N'
225 DECLARE
226 v_transaction_cat IGS_LOOKUPS_VAL.transaction_cat%TYPE;
227 v_sys_generated_ind IGS_LOOKUPS_VAL.system_generated_ind%TYPE;
228 CURSOR c_strty IS
229 SELECT strty.transaction_cat,
230 strty.system_generated_ind
231 FROM IGS_LOOKUPS_VAL strty
232 WHERE lookup_code = p_transaction_type AND
233 lookup_type = 'TRANSACTION_TYPE';
234 BEGIN
235 --- Set the default message number
236 p_message_name := NULL;
237 --validate parameter
238 IF (p_transaction_type IS NULL) THEN
239 RETURN TRUE;
240 END IF;
241 --Get the system transaction category and system generated indicator
242 --values for the tranaction_type. If not 'DEBT' and 'N' respectively,
243 --return error.
244 OPEN c_strty;
245 FETCH c_strty INTO v_transaction_cat,
246 v_sys_generated_ind;
247 CLOSE c_strty;
248 IF (v_transaction_cat <> 'DEBT') THEN
249 p_message_name := 'IGS_FI_TRANSTYPE_CAT_DEBT';
250 RETURN FALSE;
251 END IF;
252 IF (v_sys_generated_ind <> 'N') THEN
253 p_message_name := 'IGS_FI_TRANSTYPE_SYSIND_N';
254 RETURN FALSE;
255 END IF;
256 RETURN TRUE;
257 END;
258 END finp_val_fas_cat;
259 --
260 -- Check if contract fee assessment rate exists for the student.
261 FUNCTION finp_val_fas_cntrct(
262 p_person_id IN NUMBER ,
263 p_course_cd IN VARCHAR2 ,
264 p_fee_type IN VARCHAR2 ,
265 p_effective_dt IN DATE ,
266 p_message_name OUT NOCOPY VARCHAR2 )
267 RETURN BOOLEAN AS
268 gv_other_detail VARCHAR2(255);
269 BEGIN --finp_val_fas_cntrct
270 --This module validates if the student has a contract fee assessment rate
271 --when recording a manual fee assessment. If so issue a warning.
272 DECLARE
273 v_cfar_rec CHAR;
274 CURSOR c_cfar IS
275 SELECT 'X'
276 FROM IGS_FI_FEE_AS_RT cfar
277 WHERE cfar.person_id = p_person_id AND
278 cfar.course_cd = p_course_cd AND
279 cfar.fee_type = p_fee_type AND
280 p_effective_dt BETWEEN cfar.start_dt AND
281 NVL(cfar.end_dt, igs_ge_date.igsdate('9999/01/01'));
282 BEGIN
283 --- Set the default message number
284 p_message_name := NULL;
285 --validate parameters
286 IF (p_fee_type IS NULL OR
287 p_course_cd IS NULL OR
288 p_fee_type IS NULL OR
289 p_effective_dt IS NULL) THEN
290 RETURN TRUE;
291 END IF;
292 --Determine if a IGS_FI_FEE_AS_RT exists
293 OPEN c_cfar;
294 FETCH c_cfar INTO v_cfar_rec;
295 IF (c_cfar%FOUND) THEN
296 p_message_name := 'IGS_FI_STUD_ACTIVE_CONT_FEEAS';
297 CLOSE c_cfar;
298 RETURN FALSE;
299 END IF;
300 CLOSE c_cfar;
301 RETURN TRUE;
302 END;
303 END finp_val_fas_cntrct;
304 --
305 -- Validate that appropriate optional fields are entered for IGS_FI_FEE_AS.
306 FUNCTION finp_val_fas_create(
307 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
308 p_fee_cat IN IGS_FI_FEE_CAT_ALL.fee_cat%TYPE ,
309 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
310 p_message_name OUT NOCOPY VARCHAR2 )
311 RETURN BOOLEAN AS
312 gv_other_detail VARCHAR2(255);
313 BEGIN --finp_val_fas_create
314 --This module validates IGS_FI_FEE_AS.IGS_FI_FEE_TYPE with the fee category and
315 -- IGS_PS_COURSE code.
316 --If IGS_FI_FEE_TYPE.s_fee_trigger_cat = 'INSTITUTN',then fee category and
317 --course_cd cannot be specified in the IGS_FI_FEE_AS record. Otherwise they
318 --must be specified
319 DECLARE
320 v_fee_trigger_cat IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE;
321 CURSOR c_ft IS
322 SELECT ft.s_fee_trigger_cat
323 FROM IGS_FI_FEE_TYPE ft
324 WHERE fee_type = p_fee_type;
325
326 CURSOR c_fee_calc_mthd IS
327 SELECT fee_calc_mthd_code
328 FROM igs_fi_control;
329 l_v_fee_calc_mthd igs_fi_control.fee_calc_mthd_code%TYPE;
330
331 BEGIN
332 --- Set the default message number
333 p_message_name := NULL;
334 --validate parameters
335 IF (p_fee_type IS NULL) THEN
336 RETURN TRUE;
337 END IF;
338 --Determine the s_fee_trigger_cat
339 OPEN c_ft;
340 FETCH c_ft INTO v_fee_trigger_cat;
341 IF (c_ft%NOTFOUND) THEN
342 CLOSE c_ft;
343 p_message_name := 'IGS_FI_FEE_ASS_DAT';
344 RETURN FALSE;
345 END IF;
346 CLOSE c_ft;
347
348 OPEN c_fee_calc_mthd;
349 FETCH c_fee_calc_mthd INTO l_v_fee_calc_mthd;
350 CLOSE c_fee_calc_mthd;
351
352 -- Enh# 3167098, In case of Primary Career, For Institution fee we store Fee category and Course Code (From Key Program).
353 IF (v_fee_trigger_cat = 'INSTITUTN' AND l_v_fee_calc_mthd <> 'PRIMARY_CAREER' AND
354 (p_fee_cat IS NOT NULL OR
355 p_course_cd IS NOT NULL)) THEN
356 p_message_name := 'IGS_FI_PRGCD_CAT_NULL';
357 RETURN FALSE;
358 END IF;
359 IF (v_fee_trigger_cat <> 'INSTITUTN' AND
360 (p_fee_cat IS NULL OR
361 p_course_cd IS NULL)) THEN
362 p_message_name := 'IGS_FI_PRGCD_CAT_INSTITUTN';
363 RETURN FALSE;
364 END IF;
365 RETURN TRUE;
366 END;
367 END finp_val_fas_create;
368 --
369 -- Ensure comment is recorded for a manual fee assessment.
370 FUNCTION finp_val_fas_com(
371 p_transaction_type IN VARCHAR2 ,
372 p_comments IN VARCHAR2 ,
373 p_message_name OUT NOCOPY VARCHAR2 )
374 RETURN BOOLEAN AS
375 gv_other_detail VARCHAR2(255);
376 BEGIN --finp_val_fas_com
380 DECLARE
377 --This module validates IGS_FI_FEE_AS.comments.
378 --IGS_FI_FEE_AS.comments cannot be NULL when a manual fee assessment record is
379 --created
381 v_transaction_cat IGS_LOOKUPS_VAL.transaction_cat%TYPE;
382 v_sys_generated_ind IGS_LOOKUPS_VAL.system_generated_ind%TYPE;
383 CURSOR c_strty IS
384 SELECT strty.transaction_cat,
385 strty.system_generated_ind
386 FROM IGS_LOOKUPS_VAL strty
387 WHERE lookup_code = p_transaction_type AND
388 lookup_type = 'TRANSACTION_TYPE';
389 BEGIN
390 --- Set the default message number
391 p_message_name := NULL;
392 --validate parameter
393 IF (p_transaction_type IS NULL) THEN
394 RETURN TRUE;
395 END IF;
396 --Get the system transaction category and system generated indicator
397 --values for the tranaction_type. If the values indicate a manual entry
398 --then verify that comments field is NOT NULL
399 OPEN c_strty;
400 FETCH c_strty INTO v_transaction_cat,
401 v_sys_generated_ind;
402 CLOSE c_strty;
403 IF (v_transaction_cat = 'DEBT' AND
404 v_sys_generated_ind = 'N') THEN
405 IF (p_comments IS NULL) THEN
406 p_message_name := 'IGS_GE_MANDATORY_FLD';
407 RETURN FALSE;
408 END IF;
409 END IF;
410 RETURN TRUE;
411 END;
412 END finp_val_fas_com;
413 --
414 -- Validate effective date of fee assessment.
415 FUNCTION finp_val_fas_eff_dt(
416 p_fee_type IN VARCHAR2 ,
417 p_fee_cal_type IN VARCHAR2 ,
418 p_fee_ci_sequence_number IN NUMBER ,
419 p_fee_cat IN VARCHAR2 ,
420 p_effective_dt IN DATE ,
421 p_s_transaction_type IN VARCHAR2 ,
422 p_message_name OUT NOCOPY VARCHAR2 )
423 RETURN BOOLEAN AS
424 gv_other_detail VARCHAR2(255);
425 BEGIN -- finp_val_fas_eff_dt
426 -- This module validates the effective_dt when recording a manual fee
427 -- assessment, effective_dt must be between IGS_FI_F_CAT_FEE_LBL_V.start_dt
428 -- and IGS_FI_F_CAT_FEE_LBL_V.end_dt.
429 DECLARE
430 v_start_dt IGS_CA_DA_INST_V.alias_val%TYPE;
431 v_end_dt IGS_CA_DA_INST_V.alias_val%TYPE;
432 v_transaction_cat IGS_LOOKUPS_VAL.transaction_cat%TYPE;
433 CURSOR c_daiv_sd IS
434 SELECT daiv.alias_val
435 FROM IGS_FI_F_CAT_FEE_LBL_V fcflv,
436 IGS_CA_DA_INST_V daiv
437 WHERE fcflv.fee_cal_type = p_fee_cal_type AND
438 fcflv.fee_ci_sequence_number = p_fee_ci_sequence_number AND
439 fcflv.fee_type = p_fee_type AND
440 fcflv.fee_cat = nvl(p_fee_cat,fcflv.fee_cat) AND
441 fcflv.start_dt_alias = daiv.dt_alias AND
442 fcflv.start_dai_sequence_number = daiv.sequence_number AND
443 fcflv.fee_cal_type = daiv.cal_type AND
444 fcflv.fee_ci_sequence_number = daiv.ci_sequence_number;
445 CURSOR c_daiv_ed IS
446 SELECT daiv.alias_val
447 FROM IGS_FI_F_CAT_FEE_LBL_V fcflv,
448 IGS_CA_DA_INST_V daiv
449 WHERE fcflv.fee_cal_type = p_fee_cal_type AND
450 fcflv.fee_ci_sequence_number = p_fee_ci_sequence_number AND
451 fcflv.fee_type = p_fee_type AND
452 fcflv.fee_cat = nvl(p_fee_cat,fcflv.fee_cat) AND
453 fcflv.end_dt_alias = daiv.dt_alias AND
454 fcflv.end_dai_sequence_number = daiv.sequence_number AND
455 fcflv.fee_cal_type = daiv.cal_type AND
456 fcflv.fee_ci_sequence_number = daiv.ci_sequence_number;
457 CURSOR c_strty IS
458 SELECT strty.transaction_cat
459 FROM IGS_LOOKUPS_VAL strty
460 WHERE strty.lookup_code = p_s_transaction_type
461 AND strty.lookup_type = 'TRANSACTION_TYPE';
462
463 BEGIN
464 --- Set the default message number
465 p_message_name := NULL;
466 --- Check what transaction_type maps to: DEBT or PAYMENT
467 --- Payment is not subject to the validation.
468 OPEN c_strty;
469 FETCH c_strty INTO v_transaction_cat;
470 IF (c_strty%NOTFOUND) THEN
471 CLOSE c_strty;
472 p_message_name := 'IGS_FI_FEE_ASS_DAT';
473 RETURN FALSE;
474 END IF;
475 CLOSE c_strty;
476 IF v_transaction_cat = 'PAYMENT' THEN
477 RETURN TRUE;
478 END IF;
479 --validate parameters
480 IF (p_fee_type IS NULL OR
481 p_fee_cal_type IS NULL OR
482 p_fee_ci_sequence_number IS NULL OR
483 p_effective_dt IS NULL) THEN
484 RETURN TRUE;
485 END IF;
486
487 --Get the start date value of the liability
488 OPEN c_daiv_sd;
489 FETCH c_daiv_sd INTO v_start_dt;
490 IF (c_daiv_sd%NOTFOUND) THEN
491 CLOSE c_daiv_sd;
492 p_message_name := 'IGS_FI_FEE_ASS_DAT';
493 RETURN FALSE;
494 END IF;
495 CLOSE c_daiv_sd;
496 --Get the end date value of the liability
497 OPEN c_daiv_ed;
498 FETCH c_daiv_ed INTO v_end_dt;
499 IF (c_daiv_ed%NOTFOUND) THEN
500 CLOSE c_daiv_ed;
501 p_message_name := 'IGS_FI_FEE_ASS_DAT';
502 RETURN FALSE;
503 END IF;
504 CLOSE c_daiv_ed;
505 --Check that effective date is between the start and end dates
506 IF (TRUNC(p_effective_dt) NOT BETWEEN TRUNC(v_start_dt) AND
507 TRUNC(v_end_dt)) THEN
508 p_message_name := 'IGS_FI_EFFDT_NOTBE_OUTSIDE';
509 RETURN FALSE;
510 END IF;
511 RETURN TRUE;
512 END;
513 END finp_val_fas_eff_dt;
514 --
515 -- Validate effect of transaction amount on student's balance.
516 FUNCTION finp_val_fas_balance(
517 p_person_id IN NUMBER ,
518 p_fee_type IN VARCHAR2 ,
519 p_fee_cal_type IN VARCHAR2 ,
520 p_fee_ci_sequence_number IN NUMBER ,
521 p_fee_cat IN VARCHAR2 ,
522 p_course_cd IN VARCHAR2 ,
523 p_transaction_amount IN NUMBER ,
524 p_message_name OUT NOCOPY VARCHAR2 )
525 RETURN BOOLEAN AS
526 /*----------------------------------------------------------------------------
527 || Created By :
528 || Created On :
529 || Purpose :
530 || Known limitations, enhancements or remarks :
531 || Change History :
532 || Who When What
533 || (reverse chronological order - newest change first)
534 uudayapr 12-12-2003 bug#3080983 made the modification to v_total_amount_due
535 declartion as number instead
536 IGS_FI_FEE_ASS_DEBT_V.local_assessment_amount%TYPE
537 and the Cursor c_fadv to point to the table
538 IGS_FI_FEE_AS instead IGS_FI_FEE_ASS_DEBT_V view.
539
540 || vvutukur 19-Dec-2002 Bug#2680885.Commented out cursor c_fpv which selects from igs_fi_fee_pay_v, which
541 || is to be dropped.Instead, cursor c_fpv is redefined selecting 0 from dual.
542 || The datatype for v_total_payments variable is also changed to NUMBER after removing
543 || the reference to igs_fi_fee_pay_v.
544 ----------------------------------------------------------------------------*/
545
546 gv_other_detail VARCHAR2(255);
547
548 BEGIN --finp_val_fas_balance
549 --This module validates that the current manual fee assessment will not
550 --cause the students balance for the liability to be less than zero
551 DECLARE
552 v_total_amount_due NUMBER;
553 v_total_payments NUMBER;
554 --Modified the cursor to fetch data from the Base table instead of the view IGS_FI_FEE_ASS_DEBT_V
555 CURSOR c_fadv IS
556 SELECT SUM(fadv.transaction_amount)
557 FROM IGS_FI_FEE_AS fadv
558 WHERE fadv.person_id = p_person_id
559 AND fadv.fee_type = p_fee_type
560 AND fadv.fee_cal_type = p_fee_cal_type
561 AND fadv.fee_ci_sequence_number = p_fee_ci_sequence_number
562 AND ((fadv.fee_cat = p_fee_cat) OR(fadv.fee_cat IS NULL AND p_fee_cat IS NULL))
563 AND ((fadv.course_cd = p_course_cd) OR (fadv.course_cd IS NULL AND p_course_cd IS NULL))
564 AND fadv.logical_delete_dt IS NULL;
565
566 CURSOR c_fpv IS
567 SELECT 0
568 FROM dual;
569
570 /* CURSOR c_fpv IS
571 SELECT SUM(fpv.payment_amount)
572 FROM IGS_FI_FEE_PAY_V fpv
573 WHERE fpv.person_id = p_person_id AND
574 fpv.fee_type = p_fee_type AND
575 fpv.fee_cal_type = p_fee_cal_type AND
576 fpv.fee_ci_sequence_number = p_fee_ci_sequence_number AND
577 NVL(fpv.fee_cat, 'NULL') = NVL(p_fee_cat, 'NULL') AND
578 NVL(fpv.course_cd,'NULL') = NVL(p_course_cd,'NULL');*/
579 BEGIN
580 --- Set the default message number
581 p_message_name := NULL;
582 --validate parameters
583 IF (p_person_id IS NULL OR
584 p_fee_type IS NULL OR
585 p_fee_cal_type IS NULL OR
586 p_fee_ci_sequence_number IS NULL) THEN
587 RETURN TRUE;
588 END IF;
589 --Determine the total amount owing
590 OPEN c_fadv;
591 FETCH c_fadv INTO v_total_amount_due;
592 CLOSE c_fadv;
593 --Determine the total payments
594 OPEN c_fpv;
598 --from the total owing to get the current balance).
595 FETCH c_fpv INTO v_total_payments;
596 CLOSE c_fpv;
597 --Check if the amount owing will be less than zero.(subtract the total payments
599 IF ((NVL(v_total_amount_due, 0) - NVL(v_total_payments, 0))
600 + NVL(p_transaction_amount, 0) < 0) THEN
601 p_message_name := 'IGS_FI_STUDBAL_LT_ZERO';
602 RETURN FALSE;
603 END IF;
604 RETURN TRUE;
605 END;
606 END finp_val_fas_balance;
607 --
608 -- Validate update to columns in the IGS_FI_FEE_AS table.
609
610 -- Change History
611 -- Who When What
612 -- masehgal 17-Jan-2002 ENH # 2170429
613 -- Obsoletion of SPONSOR_CD related Parameters from Function FINP_VAL_FAS_UPD
614
615 FUNCTION finp_val_fas_upd(
616 p_new_person_id IGS_FI_FEE_AS_ALL.person_id%TYPE ,
617 p_old_person_id IGS_FI_FEE_AS_ALL.person_id%TYPE ,
618 p_new_transaction_id IGS_FI_FEE_AS_ALL.transaction_id%TYPE ,
619 p_old_transaction_id IGS_FI_FEE_AS_ALL.transaction_id%TYPE ,
620 p_new_fee_type IGS_FI_FEE_AS_ALL.fee_type%TYPE ,
621 p_old_fee_type IGS_FI_FEE_AS_ALL.fee_type%TYPE ,
622 p_new_fee_cal_type IGS_FI_FEE_AS_ALL.fee_cal_type%TYPE ,
623 p_old_fee_cal_type IGS_FI_FEE_AS_ALL.fee_cal_type%TYPE ,
624 p_new_fee_ci_seq_num IGS_FI_FEE_AS_ALL.fee_ci_sequence_number%TYPE ,
625 p_old_fee_ci_seq_num IGS_FI_FEE_AS_ALL.fee_ci_sequence_number%TYPE ,
626 p_new_fee_cat IGS_FI_FEE_AS_ALL.fee_cat%TYPE ,
627 p_old_fee_cat IGS_FI_FEE_AS_ALL.fee_cat%TYPE ,
628 p_new_transaction_type IGS_FI_FEE_AS_ALL.s_transaction_type%TYPE ,
629 p_old_transaction_type IGS_FI_FEE_AS_ALL.s_transaction_type%TYPE ,
630 p_new_transaction_dt IGS_FI_FEE_AS_ALL.transaction_dt%TYPE ,
631 p_old_transaction_dt IGS_FI_FEE_AS_ALL.transaction_dt%TYPE ,
632 p_new_transaction_amount IGS_FI_FEE_AS_ALL.transaction_amount%TYPE ,
633 p_old_transaction_amount IGS_FI_FEE_AS_ALL.transaction_amount%TYPE ,
634 p_new_currency_cd IGS_FI_FEE_AS_ALL.currency_cd%TYPE ,
635 p_old_currency_cd IGS_FI_FEE_AS_ALL.currency_cd%TYPE ,
636 p_new_exchange_rate IGS_FI_FEE_AS_ALL.exchange_rate%TYPE ,
637 p_old_exchange_rate IGS_FI_FEE_AS_ALL.exchange_rate%TYPE ,
638 p_new_chg_elements IGS_FI_FEE_AS_ALL.chg_elements%TYPE ,
639 p_old_chg_elements IGS_FI_FEE_AS_ALL.chg_elements%TYPE ,
640 p_new_effective_dt IGS_FI_FEE_AS_ALL.effective_dt%TYPE ,
641 p_old_effective_dt IGS_FI_FEE_AS_ALL.effective_dt%TYPE ,
642 p_new_course_cd IGS_FI_FEE_AS_ALL.course_cd%TYPE ,
643 p_old_course_cd IGS_FI_FEE_AS_ALL.course_cd%TYPE ,
644 p_new_notification_dt IGS_FI_FEE_AS_ALL.notification_dt%TYPE ,
645 p_old_notification_dt IGS_FI_FEE_AS_ALL.notification_dt%TYPE ,
646 p_new_logical_delete_dt IGS_FI_FEE_AS_ALL.logical_delete_dt%TYPE ,
647 p_old_logical_delete_dt IGS_FI_FEE_AS_ALL.logical_delete_dt%TYPE ,
648 p_message_name OUT NOCOPY VARCHAR2 )
649 RETURN BOOLEAN AS
650 gv_other_detail VARCHAR2(255);
651 BEGIN -- finp_val_fas_upd
652 -- This routine validates fields being updated in the IGS_FI_FEE_AS
653 -- table may be changed.
654 BEGIN
655 p_message_name := NULL;
656 -- 1. Check for allowable changes
657
658 --Change History
659 --Who When What
660 --masehgal 17-Jan-2002 Obsoletion of SPONSOR_CD related Check from Function FINP_VAL_FAS_UPD
661
662 IF p_new_person_id <> p_old_person_id THEN
663 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
664 RETURN FALSE;
665 END IF;
666 IF p_new_transaction_id <> p_old_transaction_id THEN
667 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
668 RETURN FALSE;
669 END IF;
670 IF p_new_fee_type <> p_old_fee_type THEN
671 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
672 RETURN FALSE;
673 END IF;
674 IF p_new_fee_cal_type <> p_old_fee_cal_type THEN
675 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
676 RETURN FALSE;
677 END IF;
678 IF p_new_fee_ci_seq_num <> p_old_fee_ci_seq_num THEN
679 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
680 RETURN FALSE;
681 END IF;
682 IF NVL(p_new_fee_cat,'NULL') <> NVL(p_old_fee_cat,'NULL') THEN
683 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
684 RETURN FALSE;
685 END IF;
686 IF p_new_transaction_type <> p_old_transaction_type THEN
687 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
688 RETURN FALSE;
689 END IF;
690 IF trunc(p_new_transaction_dt) <> trunc(p_old_transaction_dt) THEN
691 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
692 RETURN FALSE;
693 END IF;
694 IF p_new_transaction_amount <> p_old_transaction_amount THEN
695 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
696 RETURN FALSE;
697 END IF;
698 IF p_new_currency_cd <> p_old_currency_cd THEN
699 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
700 RETURN FALSE;
701 END IF;
702 IF NVL(trunc(p_new_effective_dt),igs_ge_date.igsdate('1900/01/01'))
703 <> NVL(trunc(p_old_effective_dt),igs_ge_date.igsdate('1900/01/01')) THEN
704 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
705 RETURN FALSE;
706 END IF;
707 IF p_new_course_cd <> p_old_course_cd THEN
708 p_message_name := 'IGS_FI_DATA_CANNOT_BE_UPDATED';
709 RETURN FALSE;
710 END IF;
711 IF NVL(p_new_logical_delete_dt,igs_ge_date.igsdate('1900/01/01'))
712 <> NVL(p_old_logical_delete_dt,igs_ge_date.igsdate('1900/01/01')) AND
713 p_old_logical_delete_dt IS NOT NULL THEN
714 p_message_name := 'IGS_FI_LOGDEL_DATE_NOT_CLEAR';
715 RETURN FALSE;
716 END IF;
717 -- 2. No error
718 RETURN TRUE;
719 END;
720 END finp_val_fas_upd;
721 END IGS_FI_VAL_FAS;
[Home] [Help]