1 PACKAGE BODY igf_sl_rel_disb AS
2 /* $Header: IGFSL27B.pls 120.8 2006/08/10 16:32:01 museshad noship $ */
3
4 /*
5 || Created By : pssahni
6 || Created On : 23-Sep-2004
7 || Purpose : Disbursement Process evaluates disbursements for
8 || various checks in Fund Setup so that these can be
9 || picked up by SF Integration Process which will
10 || credit / debit the disbursement.
11 || Known limitations, enhancements or remarks :
12 ||
13 || (reverse chronological order - newest change first)
14 || who WHEN what
15 || museshad 10-Aug-2006 Bug 5337555. Build FA 163. TBH Impact.
16 || 23-Sep-2004 Creation of the file
17 || FA149 -
18 */
19 ------------------------------------------------------------------------------------------
20 -- who when what
21 ------------------------------------------------------------------------------------------
22 -- sjadhav 09-Nov-2004 added following checks
23 -- DRI should not set if the Pell or Loan is Sent
24 -- DRI should not be set for accepted FFELP loan is not
25 -- Release-4
26 ------------------------------------------------------------------------------------------
27
28
29 l_fund_type VARCHAR2(1);
30
31
32 ------------------------------------------------------------------------------------------
33
34 FUNCTION get_fund_desc(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
35 RETURN VARCHAR2
36 IS
37
38 --------------------------------------------------------------------------------------------
39 --
40 -- Purpose : Returns fund code + description of the fund id passed
41 --
42 --------------------------------------------------------------------------------------------
43
44 CURSOR cur_fund_des(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
45 IS
46 SELECT fund_code fdesc
47 FROM igf_aw_fund_mast
48 WHERE fund_id = p_fund_id;
49
50 fund_des_rec cur_fund_des%ROWTYPE;
51 BEGIN
52 OPEN cur_fund_des(p_fund_id);
53 FETCH cur_fund_des INTO fund_des_rec;
54
55 IF cur_fund_des%NOTFOUND
56 THEN
57 CLOSE cur_fund_des;
58 RETURN NULL;
59 ELSE
60 CLOSE cur_fund_des;
61 RETURN fund_des_rec.fdesc;
62 END IF;
63 EXCEPTION
64 WHEN OTHERS
65 THEN
66 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
67 fnd_message.set_token('NAME', 'IGF_SL_REL_DISB.GET_FUND_DESC ' || SQLERRM);
68 fnd_file.put_line(fnd_file.log, SQLERRM);
69 igs_ge_msg_stack.ADD;
70 app_exception.raise_exception;
71 END get_fund_desc;
72
73 FUNCTION chk_attendance(
74 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
75 p_load_cal_type igs_ca_inst_all.cal_type%TYPE,
76 p_load_seq_number igs_ca_inst_all.sequence_number%TYPE,
77 p_min_att_type igs_en_atd_type_all.attendance_type%TYPE,
78 p_result OUT NOCOPY VARCHAR2
79 )
80 RETURN BOOLEAN
81 AS
82
83 --------------------------------------------------------------------------------------------
84 --
85 -- Purpose : This Process evaluates min attendance for student
86 --
87 --------------------------------------------------------------------------------------------
88
89 CURSOR cur_get_range(
90 p_load_cal_type igs_ca_inst_all.cal_type%TYPE,
91 p_min_att_type igs_en_atd_type_all.attendance_type%TYPE
92 )
93 IS
94 SELECT upper_enr_load_range
95 FROM igs_en_atd_type_load
96 WHERE cal_type = p_load_cal_type
97 AND attendance_type = p_min_att_type;
98
99 get_range_rec cur_get_range%ROWTYPE;
100 l_min_range igs_en_atd_type_all.upper_enr_load_range%TYPE;
101 l_key_range igs_en_atd_type_all.upper_enr_load_range%TYPE;
102 l_key_att_type igs_en_atd_type_all.attendance_type%TYPE;
103 l_credit_pts NUMBER;
104 l_fte VARCHAR2(10);
105 BEGIN
106 OPEN cur_get_range(p_load_cal_type, p_min_att_type);
107 FETCH cur_get_range INTO get_range_rec;
108
109 IF cur_get_range%NOTFOUND
110 THEN
111 l_min_range := 0;
112 CLOSE cur_get_range;
113 ELSE
114 l_min_range := get_range_rec.upper_enr_load_range;
115 CLOSE cur_get_range;
116 END IF;
117
118 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
119 THEN
120 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_attendance.debug', 'l_min_range='|| l_min_range);
121 END IF;
122
123 BEGIN
124 igs_en_prc_load.enrp_get_inst_latt(
125 igf_gr_gen.get_person_id(p_base_id), p_load_cal_type,
126 p_load_seq_number, l_key_att_type, l_credit_pts, l_fte
127 );
128 EXCEPTION
129 WHEN OTHERS
130 THEN
131 p_result := fnd_message.get;
132 RETURN FALSE;
133 END;
134
135 OPEN cur_get_range(p_load_cal_type, l_key_att_type);
136 FETCH cur_get_range INTO get_range_rec;
137
138 IF cur_get_range%NOTFOUND
139 THEN
140 l_key_range := 0;
141 CLOSE cur_get_range;
142 ELSE
143 l_key_range := get_range_rec.upper_enr_load_range;
144 CLOSE cur_get_range;
145 END IF;
146
147 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
148 THEN
149 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_attendance.debug', 'l_key_range='|| l_key_range);
150 END IF;
151
152 IF l_key_range >= l_min_range
153 THEN
154 RETURN TRUE;
155 ELSE
156 RETURN FALSE;
157 END IF;
158 EXCEPTION
159 WHEN OTHERS
160 THEN
161 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
162 fnd_message.set_token(
163 'NAME', 'igf_sl_rel_disb.chk_attendance' || SQLERRM
164 );
165 fnd_file.put_line(fnd_file.log, SQLERRM);
166 igs_ge_msg_stack.ADD;
167 app_exception.raise_exception;
168 END chk_attendance;
169
170 FUNCTION chk_fed_elig(
171 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE, p_fund_type VARCHAR2
172 )
173 RETURN BOOLEAN
174 AS
175
176 --------------------------------------------------------------------------------------------
177 --
178 -- Purpose : This routine is for Federal Eligibility Check
179 --
180 --------------------------------------------------------------------------------------------
181
182 --Get the eligibility status of the student for an active ISIR for the context Award Year
183
184 CURSOR cur_fedl_elig(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
185 IS
186 SELECT
187 fabaserec.NSLDS_DATA_OVERRIDE_FLG,
188 match.nslds_match_flag
189 FROM
190 igf_ap_isir_matched match ,
191 igf_ap_fa_base_rec_all fabaserec
192 WHERE
193 match.base_id = p_base_id and
194 match.active_isir = 'Y' and
195 fabaserec.base_id =p_base_id;
196
197 fedl_elig_rec cur_fedl_elig%ROWTYPE;
198 l_return_status VARCHAR2(30);
199 BEGIN
200 OPEN cur_fedl_elig(p_base_id);
201 FETCH cur_fedl_elig INTO fedl_elig_rec;
202 CLOSE cur_fedl_elig;
203
204 IF p_fund_type IN ('D', 'F') AND ((NVL(fedl_elig_rec.nslds_match_flag, 'N') = '1') OR (fedl_elig_rec.NSLDS_DATA_OVERRIDE_FLG ='Y'))
205 THEN
206 RETURN TRUE;
207 ELSIF p_fund_type = 'P'
208 THEN
209 --
210 -- Use the new wrapper to determine Pell Elig
211 -- FA131 Check
212 --
213 igf_gr_pell_calc.pell_elig(p_base_id, l_return_status);
214
215 IF NVL(l_return_status, '*') <> 'E'
216 THEN
217 RETURN TRUE;
218 ELSE
219 RETURN FALSE;
220 END IF;
221 ELSE
222 RETURN FALSE;
223 END IF;
224 EXCEPTION
225 WHEN OTHERS
226 THEN
227 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
228 fnd_message.set_token(
229 'NAME', 'IGF_SL_REL_DISB.CHK_FED_ELIG ' || SQLERRM
230 );
231 fnd_file.put_line(fnd_file.log, SQLERRM);
232 igs_ge_msg_stack.ADD;
233 app_exception.raise_exception;
234 END chk_fed_elig;
235
236 FUNCTION chk_loan_active(p_loan_id igf_sl_loans_all.loan_id%TYPE)
237 RETURN BOOLEAN
238 AS
239 /*
240 || Created By : pssahni
241 || Created On : 3-Oct-2004
242 || Purpose : To Check whether a loan is active or not
243 || Who When What
244 || (reverse chronological order - newest change first)
245 */
246
247 -- Get the details of loan status
248 CURSOR cur_loan_active(p_loan_id igf_sl_loans_all.loan_id%TYPE)
249 IS
250 SELECT active
251 FROM igf_sl_loans_all
252 WHERE loan_id = p_loan_id;
253
254 loan_active_rec cur_loan_active%ROWTYPE;
255 BEGIN
256 OPEN cur_loan_active(p_loan_id);
257 FETCH cur_loan_active INTO loan_active_rec;
258 CLOSE cur_loan_active;
259
260 IF loan_active_rec.active = 'Y' OR loan_active_rec.active = 'y'
261 THEN
262 RETURN TRUE;
263 ELSE
264 RETURN FALSE;
265 END IF;
266 EXCEPTION
267 WHEN OTHERS
268 THEN
269 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
270 fnd_message.set_token(
271 'NAME', 'IGF_SL_REL_DISB.CHK_FED_ELIG ' || SQLERRM
272 );
273 fnd_file.put_line(fnd_file.log, SQLERRM);
274 igs_ge_msg_stack.ADD;
275 app_exception.raise_exception;
276 END chk_loan_active;
277
278 FUNCTION per_in_fa(
279 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE, p_ci_cal_type VARCHAR2,
280 p_ci_sequence_number NUMBER, p_base_id OUT NOCOPY NUMBER
281 )
282
283 --------------------------------------------------------------------------------------------
284 --
285 -- Purpose : Returns person number for the person id passed
286 --
287 --------------------------------------------------------------------------------------------
288
289 RETURN VARCHAR2
290 IS
291 CURSOR cur_get_pers_num(
292 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE
293 )
294 IS
295 SELECT person_number
296 FROM igs_pe_person_base_v
297 WHERE person_id = p_person_id;
298
299 get_pers_num_rec cur_get_pers_num%ROWTYPE;
300
301 CURSOR cur_get_base(
302 p_cal_type igs_ca_inst_all.cal_type%TYPE,
303 p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
304 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE
305 )
306 IS
307 SELECT base_id
308 FROM igf_ap_fa_base_rec
309 WHERE person_id = p_person_id AND ci_cal_type = p_cal_type
310 AND ci_sequence_number = p_sequence_number;
311 BEGIN
312 OPEN cur_get_pers_num(p_person_id);
313 FETCH cur_get_pers_num INTO get_pers_num_rec;
314
315 IF cur_get_pers_num%NOTFOUND
316 THEN
317 CLOSE cur_get_pers_num;
318 RETURN NULL;
319 ELSE
320 CLOSE cur_get_pers_num;
321 OPEN cur_get_base(p_ci_cal_type, p_ci_sequence_number, p_person_id);
322 FETCH cur_get_base INTO p_base_id;
323 CLOSE cur_get_base;
324 RETURN get_pers_num_rec.person_number;
325 END IF;
326 EXCEPTION
327 WHEN OTHERS
328 THEN
329 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
330 fnd_message.set_token('NAME', 'IGF_SL_REL_DISB.PER_IN_FA ' || SQLERRM);
331 fnd_file.put_line(fnd_file.log, SQLERRM);
332 igs_ge_msg_stack.ADD;
333 app_exception.raise_exception;
334 END per_in_fa;
335
336
337 ----------------------------------------------------------------------------------------------------------------------------------
338
339 PROCEDURE log_parameters(
340 p_alt_code VARCHAR2, p_fund_id NUMBER, p_base_id NUMBER, p_loan_id NUMBER,
341 p_trans_type VARCHAR2, p_per_grp_id NUMBER, p_fund_code VARCHAR2
342 )
343
344 ------------------------------------------------------------------------------------------------------------------------------------
345 --
346 -- Purpose: This process log the parameters in the log file
347 --
348 ------------------------------------------------------------------------------------------------------------------------------------
349 IS
350
351 -- Get the values from the lookups
352 CURSOR cur_get_fund_code(p_fund_id NUMBER)
353 IS
354 SELECT fund_code
355 FROM igf_aw_fund_mast_all
356 WHERE fund_id = p_fund_id;
357
358 get_fund_code_rec cur_get_fund_code%ROWTYPE;
359
360 CURSOR cur_get_fed_fund_code(p_fund_id NUMBER)
361 IS
362 SELECT fcat.fed_fund_code
363 FROM igf_aw_fund_mast_all fmast,igf_aw_fund_cat_all fcat
364 WHERE fmast.fund_id = p_fund_id
365 AND fmast.fund_code = fcat.fund_code;
366
367 get_fed_fund_code cur_get_fed_fund_code%ROWTYPE;
368
369 CURSOR cur_get_grp_name(p_per_grp_id NUMBER)
370 IS
371 SELECT group_cd
372 FROM igs_pe_persid_group_all
373 WHERE GROUP_ID = p_per_grp_id;
374
375 get_grp_name_rec cur_get_grp_name%ROWTYPE;
376
377 CURSOR cur_get_trans_type(p_trans_type VARCHAR2,p_lookup_type VARCHAR2)
378 IS
379 SELECT meaning
380 FROM igf_lookups_view
381 WHERE lookup_type = p_lookup_type
382 AND lookup_code = p_trans_type;
383
384 get_trans_type_rec cur_get_trans_type%ROWTYPE;
385
386 CURSOR c_get_parameters
387 IS
388 SELECT meaning, lookup_code
389 FROM igf_lookups_view
390 WHERE lookup_type = 'IGF_GE_PARAMETERS'
391 AND lookup_code IN (
392 'AWARD_YEAR',
393 'FUND_CODE',
394 'PERSON_NUMBER',
395 'LOAN_ID',
396 'TRANS_TYPE',
397 'PERSON_ID_GROUP'
398 );
399
400 parameter_rec c_get_parameters%ROWTYPE;
401 l_award_year VARCHAR2(80);
402 l_fund_code VARCHAR2(80);
403 l_person_number VARCHAR2(80);
404 l_loan_id VARCHAR2(80);
405 l_trans_type VARCHAR2(80);
406 l_person_id_group VARCHAR2(80);
407 BEGIN
408 OPEN c_get_parameters;
409
410 LOOP
411 FETCH c_get_parameters INTO parameter_rec;
412 EXIT WHEN c_get_parameters%NOTFOUND;
413
414 IF parameter_rec.lookup_code = 'AWARD_YEAR'
415 THEN
416 l_award_year := TRIM(parameter_rec.meaning);
417 ELSIF parameter_rec.lookup_code = 'FUND_CODE'
418 THEN
419 l_fund_code := TRIM(parameter_rec.meaning);
420 ELSIF parameter_rec.lookup_code = 'PERSON_NUMBER'
421 THEN
422 l_person_number := TRIM(parameter_rec.meaning);
423 ELSIF parameter_rec.lookup_code = 'LOAN_ID'
424 THEN
425 l_loan_id := TRIM(parameter_rec.meaning);
426 ELSIF parameter_rec.lookup_code = 'TRANS_TYPE'
427 THEN
428 l_trans_type := TRIM(parameter_rec.meaning);
429 ELSIF parameter_rec.lookup_code = 'PERSON_ID_GROUP'
430 THEN
431 l_person_id_group := TRIM(parameter_rec.meaning);
432 END IF;
433 END LOOP;
434
435 CLOSE c_get_parameters;
436
437 IF p_fund_id IS NOT NULL
438 THEN
439 OPEN cur_get_fund_code(p_fund_id);
440 FETCH cur_get_fund_code INTO get_fund_code_rec;
441 CLOSE cur_get_fund_code;
442 END IF;
443
444 IF p_per_grp_id IS NOT NULL
445 THEN
446 OPEN cur_get_grp_name(p_per_grp_id);
447 FETCH cur_get_grp_name INTO get_grp_name_rec;
448 CLOSE cur_get_grp_name;
449 END IF;
450
451 IF p_trans_type IS NOT NULL
452 THEN
453 OPEN cur_get_fed_fund_code(p_fund_id);
454 FETCH cur_get_fed_fund_code INTO get_fed_fund_code;
455 CLOSE cur_get_fed_fund_code;
456
457 IF get_fed_fund_code.fed_fund_code IN ('FLP','FLS','FLU','ALT') THEN
458 OPEN cur_get_trans_type(p_trans_type,'IGF_DB_TRANS_TYPE');
459 FETCH cur_get_trans_type INTO get_trans_type_rec;
460 CLOSE cur_get_trans_type;
461 ELSIF get_fed_fund_code.fed_fund_code IN ('DLP','DLS','DLU','PELL') THEN
462 OPEN cur_get_trans_type(p_trans_type,'IGF_GR_TRANS_TYPE');
463 FETCH cur_get_trans_type INTO get_trans_type_rec;
464 CLOSE cur_get_trans_type;
465 END IF;
466 END IF;
467
468 fnd_file.new_line(fnd_file.log, 1);
469 fnd_file.put_line(
470 fnd_file.log, igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS')
471 );
472 fnd_file.new_line(fnd_file.log, 1);
473 fnd_file.put_line(
474 fnd_file.log, RPAD(l_award_year, 40) || ' : ' || p_alt_code
475 );
476 fnd_file.put_line(
477 fnd_file.log, RPAD(l_fund_code, 40) || ' : '
478 || get_fund_code_rec.fund_code
479 );
480 fnd_file.put_line(
481 fnd_file.log, RPAD(l_person_number, 40) || ' : '
482 || igf_gr_gen.get_per_num(p_base_id)
483 );
484 fnd_file.put_line(fnd_file.log, RPAD(l_loan_id, 40) || ' : ' || p_loan_id);
485
486 fnd_file.put_line(
487 fnd_file.log, RPAD(l_trans_type, 40) || ' : '
488 || get_trans_type_rec.meaning
489 );
490
491 fnd_file.put_line(
492 fnd_file.log, RPAD(l_person_id_group, 40) || ' : '
493 || get_grp_name_rec.group_cd
494 );
495 fnd_file.new_line(fnd_file.log, 1);
496 fnd_file.put_line(
497 fnd_file.log,
498 '--------------------------------------------------------'
499 );
500 fnd_file.new_line(fnd_file.log, 1);
501 EXCEPTION
502 WHEN OTHERS
503 THEN
504 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level
505 THEN
506 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_rel_dis.log_parameters.exception','Exception:' || SQLERRM);
507 END IF;
508
509 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
510 fnd_message.set_token('NAME', 'IGF_SL_REL_DISB.LOG_PARAMETERS');
511 igs_ge_msg_stack.ADD;
512 END log_parameters;
513
514 FUNCTION chk_fund_meth_dl(
515 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
516 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
517 p_award_id igf_aw_award_all.award_id%TYPE,
518 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
519 )
520 RETURN BOOLEAN
521 AS
522 /*-------------------------------------------------------------------------------------------------------
523 || Created By : pssahni
524 || Created On : 3-10-2004
525 || Purpose : Returns true only for a valid combination of Funding method and number of days
526 Return NULL if the funding method is not valid
527 || Change History :
528 || Who When What
529 || (reverse chronological order - newest change first)
530 --------------------------------------------------------------------------------------------------------*/
531
532 -- Get the funding method for direct loan
533 CURSOR cur_get_fund_meth(
534 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
535 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE
536 )
537 IS
538 SELECT funding_method
539 FROM igf_sl_dl_setup_all
540 WHERE p_ci_cal_type = ci_cal_type
541 AND p_ci_sequence_number = ci_sequence_number;
542
543 get_fund_meth_rec cur_get_fund_meth%ROWTYPE;
544
545
546 -- Get the disbursment date
547 CURSOR cur_get_disb_date(
548 p_award_id igf_aw_awd_disb_all.award_id%TYPE,
549 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
550 )
551 IS
552 SELECT disb_date
553 FROM igf_aw_awd_disb_all
554 WHERE p_award_id = award_id AND p_disb_num = disb_num;
555
556 get_disb_date_rec cur_get_disb_date%ROWTYPE;
557 BEGIN
558 OPEN cur_get_fund_meth(p_ci_cal_type, p_ci_sequence_number);
559 FETCH cur_get_fund_meth INTO get_fund_meth_rec;
560
561 IF cur_get_fund_meth%NOTFOUND
562 THEN
563 CLOSE cur_get_fund_meth;
564
565 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
566 THEN
567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_dl.debug','cur_get_fund_meth%NOTFOUND');
568 END IF;
569
570 RETURN NULL;
571 ELSE
572 CLOSE cur_get_fund_meth;
573
574 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
575 THEN
576 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_dl.debug','cur_get_fund_meth%FOUND');
577 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_dl.debug','funding method code = ' || get_fund_meth_rec.funding_method);
578 END IF;
579
580 OPEN cur_get_disb_date(p_award_id, p_disb_num);
581 FETCH cur_get_disb_date INTO get_disb_date_rec;
582
583 IF cur_get_disb_date%NOTFOUND
584 THEN
585 CLOSE cur_get_disb_date;
586
587 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
588 THEN
589 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_dl.debug','cur_get_disb_date%NOTFOUND');
590 END IF;
591
592 RETURN NULL;
593 ELSE
594 CLOSE cur_get_disb_date;
595
596 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
597 THEN
598 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_dl.debug','cur_get_disb_date%FOUND');
599 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_dl.debug','disbursment date =' || get_disb_date_rec.disb_date);
600 END IF;
601
602 IF get_fund_meth_rec.funding_method IN ('A', 'P', 'CM1')
603 THEN
604 -- If Funding method is advance pay, pushed cash or CM1 then DRI can be set to true 7 days before disbursment date
605 IF (TRUNC(SYSDATE) + 7) <= get_disb_date_rec.disb_date
606 THEN
607 RETURN FALSE;
608 ELSE
609 RETURN TRUE;
610 END IF;
611 ELSIF get_fund_meth_rec.funding_method IN ('R', 'CM2')
612 THEN
613 -- If funding method is CM2 or reimbursment then DRI cant be set true
614 IF TRUNC(SYSDATE) <= get_disb_date_rec.disb_date
615 THEN
616 RETURN FALSE;
617 ELSE
618 RETURN TRUE;
619 END IF;
620 ELSE
621 -- Funding method specified is not correct
622 RETURN NULL;
623 END IF;
624 END IF;
625 END IF;
626 END chk_fund_meth_dl;
627
628 FUNCTION chk_fund_meth_pell(
629 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
630 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
631 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
632 p_award_id igf_aw_award_all.award_id%TYPE,
633 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE,
634 cp_return_status IN OUT NOCOPY VARCHAR2,
635 cp_message IN OUT NOCOPY VARCHAR2
636 )
637 RETURN BOOLEAN
638 AS
639 /*-------------------------------------------------------------------------------------------------------
640 || Created By : pssahni
641 || Created On : 3-10-2004
642 || Purpose : Returns true only for a valid combination of Funding method and number of days
643 Return NULL if the funding method is not valid
644 || Change History :
645 || Who When What
646 || (reverse chronological order - newest change first)
647 --------------------------------------------------------------------------------------------------------*/
648
649 -- Get the funding method for pell grant
650 CURSOR cur_get_fund_meth(
651 p_pell_seq_id igf_gr_pell_setup_all.pell_seq_id%TYPE
652 )
653 IS
654 SELECT funding_method
655 FROM igf_gr_pell_setup_all
656 WHERE p_pell_seq_id = pell_seq_id;
657
658 get_fund_meth_rec cur_get_fund_meth%ROWTYPE;
659
660
661 -- Get the disbursment date
662 CURSOR cur_get_disb_date(
663 p_award_id igf_aw_awd_disb_all.award_id%TYPE,
664 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
665 )
666 IS
667 SELECT disb_date
668 FROM igf_aw_awd_disb_all
669 WHERE p_award_id = award_id AND p_disb_num = disb_num;
670
671 get_disb_date_rec cur_get_disb_date%ROWTYPE;
672 l_pell_setup_rec igf_gr_pell_setup_all%ROWTYPE;
673 l_program_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
674 l_program_version igs_en_stdnt_ps_att_all.version_number%TYPE;
675 l_message fnd_new_messages.message_text%TYPE;
676 l_return_status VARCHAR2(30);
677 BEGIN
678 -- Get the students key program details
679 -- Based on these details the Pell Setup record is arrived at
680 igf_ap_gen_001.get_key_program(
681 cp_base_id => p_base_id, cp_course_cd => l_program_cd,
682 cp_version_number => l_program_version
683 );
684
685 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
686 THEN
687 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.chk_fund_meth_pell.debug','Key Program > Course cd>' || l_program_cd || ' Version >'|| TO_CHAR(l_program_version));
688 END IF;
689
690 -- Get the Pell Setup
691 igf_gr_pell_calc.get_pell_setup(
692 cp_base_id => p_base_id,
693 cp_course_cd => l_program_cd,
694 cp_version_number => l_program_version,
695 cp_cal_type => p_ci_cal_type,
696 cp_sequence_number => p_ci_sequence_number,
697 cp_pell_setup_rec => l_pell_setup_rec,
698 cp_message => l_message,
699 cp_return_status => l_return_status
700 );
701
702 IF l_return_status = 'E'
703 THEN
704 cp_message := l_message;
705 cp_return_status := 'E';
706 RETURN NULL;
707 END IF;
708
709 OPEN cur_get_fund_meth(l_pell_setup_rec.pell_seq_id);
710 FETCH cur_get_fund_meth INTO get_fund_meth_rec;
711
712 IF cur_get_fund_meth%NOTFOUND
713 THEN
714 CLOSE cur_get_fund_meth;
715 RETURN NULL;
716 ELSE
717 CLOSE cur_get_fund_meth;
718 OPEN cur_get_disb_date(p_award_id, p_disb_num);
719 FETCH cur_get_disb_date INTO get_disb_date_rec;
720
721 IF cur_get_disb_date%NOTFOUND
722 THEN
723 CLOSE cur_get_disb_date;
724 RETURN NULL;
725 ELSE
726 CLOSE cur_get_disb_date;
727
728 IF get_fund_meth_rec.funding_method = 'A'
729 THEN
730 -- If Funding method is advance pay then DRI can be set to true 30 days before disbursment date
731 IF (TRUNC(SYSDATE) + 30) <= get_disb_date_rec.disb_date
732 THEN
733 RETURN FALSE;
734 ELSE
735 RETURN TRUE;
736 END IF;
737 ELSIF get_fund_meth_rec.funding_method IN ('P', 'CM1', 'J')
738 THEN
739 -- If funding method is Pushed cash or CM1 or JIT then DRI cant be set true 7 days before disbursment date
740 IF (TRUNC(SYSDATE) + 7) <= get_disb_date_rec.disb_date
741 THEN
742 RETURN FALSE;
743 ELSE
744 RETURN TRUE;
745 END IF;
746 ELSIF get_fund_meth_rec.funding_method IN ('R', 'CM2')
747 THEN
748 -- If funding method is CM2 or reimbursment then DRI cant be set true
749 IF TRUNC(SYSDATE) <= get_disb_date_rec.disb_date
750 THEN
751 RETURN FALSE;
752 ELSE
753 RETURN TRUE;
754 END IF;
755 ELSE
756 -- Funding method specified is not correct
757 RETURN NULL;
758 END IF;
759 END IF;
760 END IF;
761 EXCEPTION
762 WHEN OTHERS
763 THEN
764 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
765 fnd_message.set_token(
766 'NAME', 'IGF_SL__REL_DISB.CHK_FUND_METHOD_PELL ' || SQLERRM
767 );
768 fnd_file.put_line(fnd_file.log, SQLERRM);
769 igs_ge_msg_stack.ADD;
770 app_exception.raise_exception;
771 END chk_fund_meth_pell;
772
773 FUNCTION chk_credit_status(p_award_id igf_aw_award_all.award_id%TYPE)
774 RETURN BOOLEAN
775 AS
776 /*------------------------------------------------------------------------------------------------
777 || Created By : pssahni
778 || Created On : 04-Oct-2004
779 || Purpose : To check the credit status of Direct Plus loans. Returns true if the DRI can be set to true
780 otherwise false
781 || Known limitations, enhancements or remarks :
782 || Change History :
783 || Who When What
784 || (reverse chronological order - newest change first)
785 -------------------------------------------------------------------------------------------------- */
786
787 CURSOR cur_get_crdt_status(p_award_id igf_aw_award_all.award_id%TYPE)
788 IS
789 SELECT lor.crdt_decision_status status, lor.credit_override override
790 FROM igf_sl_lor_all lor, igf_sl_loans_all loan
791 WHERE loan.loan_id = lor.loan_id AND loan.award_id = p_award_id;
792
793 get_crdt_status_rec cur_get_crdt_status%ROWTYPE;
794 BEGIN
795 OPEN cur_get_crdt_status(p_award_id);
796 FETCH cur_get_crdt_status INTO get_crdt_status_rec;
797
798 IF cur_get_crdt_status%NOTFOUND
799 THEN
800 CLOSE cur_get_crdt_status;
801 RETURN TRUE;
802 ELSE
803 CLOSE cur_get_crdt_status;
804
805 IF NVL(get_crdt_status_rec.status, '*') = 'A'
806 OR NVL(get_crdt_status_rec.override, '*') = 'C'
807 OR NVL(get_crdt_status_rec.override, '*') = 'E'
808 THEN
809 RETURN TRUE;
810 ELSE
811 RETURN FALSE;
812 END IF;
813 END IF;
814 EXCEPTION
815 WHEN OTHERS
816 THEN
817 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
818 fnd_message.set_token(
819 'NAME', 'IGF_SL_REL_DISB.CHK_CREDIT_STATUS ' || SQLERRM
820 );
821 fnd_file.put_line(fnd_file.log, SQLERRM);
822 igs_ge_msg_stack.ADD;
823 app_exception.raise_exception;
824 END chk_credit_status;
825
826 FUNCTION chk_todo_result(
827 p_message_name OUT NOCOPY VARCHAR2,
828 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
829 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
830 )
831 RETURN BOOLEAN
832 AS
833 /*
834 || Created By : sjadhav
835 || Created On : 07-Jan-2002
836 || Purpose : This routine checks for app process statuses.
837 ||
838 || Known limitations, enhancements or remarks :
839 || Change History : (reverse chronological order - newest change first)
840 || Who When What
841 || brajendr 18-Oct-2002 Bug : 2591643
842 || Modified the Code for FA104- To Do Enhancements
843 */
844
845
846
847 CURSOR c_student_details(
848 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
849 )
850 IS
851 SELECT manual_disb_hold, fa_process_status
852 FROM igf_ap_fa_base_rec
853 WHERE base_id = cp_base_id;
854
855 CURSOR c_fund_details(cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
856 IS
857 SELECT ver_app_stat_override
858 FROM igf_aw_fund_mast
859 WHERE fund_id = cp_fund_id;
860
861 CURSOR c_chk_verif_status(
862 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
863 )
864 IS
865 SELECT fed_verif_status
866 FROM igf_ap_fa_base_rec fab
867 WHERE fab.base_id = p_base_id
868 AND fab.fed_verif_status IN (
869 'ACCURATE',
870 'CALCULATED',
871 'NOTVERIFIED',
872 'NOTSELECTED',
873 'REPROCESSED',
874 'TOLERANCE',
875 'WAIVED'
876 );
877
878 CURSOR c_fnd_todo(
879 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
880 cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
881 cp_status igf_ap_td_item_inst_all.status%TYPE,
882 cp_inactive igf_ap_td_item_inst_all.inactive_flag%TYPE
883 ) IS
884 SELECT 'x'
885 FROM igf_aw_fund_mast_all fmast,
886 igf_aw_fund_td_map_all fndtd,
887 igf_ap_td_item_inst_all tdinst,
888 igf_ap_td_item_mst_all tdmst
889 WHERE fmast.fund_id = cp_fund_id
890 AND tdinst.base_id = cp_base_id
891 AND fndtd.fund_id = fmast.fund_id
892 AND fndtd.item_sequence_number = tdinst.item_sequence_number
893 AND fndtd.item_sequence_number = tdmst.todo_number
894 AND NVL(tdmst.career_item,'N') = 'N'
895 AND tdinst.status <> cp_status
896 AND tdinst.inactive_flag <> cp_inactive
897 UNION
898 SELECT 'x'
899 FROM igf_aw_fund_mast_all fmast,
900 igf_aw_fund_td_map_all fndtd,
901 igf_ap_td_item_inst_v tdinst,
902 igf_ap_td_item_mst_all tdmst,
903 igf_ap_fa_base_rec_all fa
904 WHERE fmast.fund_id = cp_fund_id
905 AND fa.base_id = cp_base_id
906 AND fa.person_id = tdinst.person_id
907 AND fndtd.fund_id = fmast.fund_id
908 AND fndtd.item_sequence_number = tdinst.item_sequence_number
909 AND fndtd.item_sequence_number = tdmst.todo_number
910 AND NVL(tdmst.career_item,'N') = 'Y'
911 AND tdinst.status <> cp_status
912 AND tdinst.inactive_flag <> cp_inactive;
913
914 lc_student_details_rec c_student_details%ROWTYPE;
915 lc_fund_details_rec c_fund_details%ROWTYPE;
916 lc_chk_verif_status_rec c_chk_verif_status%ROWTYPE;
917 l_fnd_todo c_fnd_todo%ROWTYPE;
918 lb_result BOOLEAN;
919 BEGIN
920 lb_result := TRUE;
921 OPEN c_fund_details(p_fund_id);
922 FETCH c_fund_details INTO lc_fund_details_rec;
923 CLOSE c_fund_details;
924
925 lc_chk_verif_status_rec := NULL;
926 OPEN c_chk_verif_status( p_base_id);
927 FETCH c_chk_verif_status INTO lc_chk_verif_status_rec;
928 CLOSE c_chk_verif_status;
929
930 -- Return TRUE if Fund has "Verification and Applicaitons status Override" is present, else check for other status
931 IF NVL(lc_fund_details_rec.ver_app_stat_override, 'N') = 'Y' THEN
932 /*
933 bug 4747156 - check for incomplete to do items attached to the fund
934 these have to be complete.
935 */
936 OPEN c_fnd_todo(p_base_id,p_fund_id,'COM','Y');
937 FETCH c_fnd_todo INTO l_fnd_todo;
938 IF c_fnd_todo%FOUND THEN
939 CLOSE c_fnd_todo;
940 p_message_name := 'IGF_DB_FAIL_TODO';
941 lb_result := FALSE;
942 ELSE
943 CLOSE c_fnd_todo;
944 p_message_name := NULL;
945 lb_result := TRUE;
946 END IF;
947 ELSE
948 OPEN c_student_details(p_base_id);
949 FETCH c_student_details INTO lc_student_details_rec;
950 CLOSE c_student_details;
951
952 --
953 -- Return FALSE if "Disbursement Hold for manual Re-Award" is present
954 --
955 IF NVL(lc_student_details_rec.manual_disb_hold, 'N') = 'Y'
956 THEN
957 p_message_name := 'IGF_DB_FAIL_DISB_HOLD_RE_AWD';
958 lb_result := FALSE;
959 --
960 -- Return FALSE if students Application Process is not completed i.e. stuatus is not "Applicaiton Complete"
961 --
962 ELSIF lc_student_details_rec.fa_process_status <> 'COMPLETE'
963 THEN
964 p_message_name := 'IGF_DB_FAIL_APPL_NOT_CMPLT';
965 lb_result := FALSE;
966 --
967 -- Return TRUE if students has "Verification Status" as "Termial" status.
968 --
969 ELSIF lc_chk_verif_status_rec.fed_verif_status IS NULL THEN
970 p_message_name := 'IGF_DB_FAIL_VER_NOT_TERMINAL';
971 lb_result := FALSE;
972 ELSE
973 /*
974 bug 4747156 - check for incomplete to do items attached to the fund
975 these have to be complete.
976 */
977 OPEN c_fnd_todo(p_base_id,p_fund_id,'COM','Y');
978 FETCH c_fnd_todo INTO l_fnd_todo;
979 IF c_fnd_todo%FOUND THEN
980 CLOSE c_fnd_todo;
981 p_message_name := 'IGF_DB_FAIL_TODO';
982 lb_result := FALSE;
983 ELSE
984 CLOSE c_fnd_todo;
985 END IF;
986 END IF;
987 END IF;
988
989 RETURN lb_result;
990 EXCEPTION
991 WHEN OTHERS
992 THEN
993 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
994 fnd_message.set_token(
995 'NAME', 'IGF_SL_REL_DISB.CHK_TODO_RESULT ' || SQLERRM
996 );
997 fnd_file.put_line(fnd_file.log, SQLERRM);
998 igs_ge_msg_stack.ADD;
999 app_exception.raise_exception;
1000 END chk_todo_result;
1001
1002 PROCEDURE insert_pays_prg_uts(
1003 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1004 p_acad_cal_type igs_ca_inst_all.cal_type%TYPE,
1005 p_acad_ci_seq_num igs_ca_inst_all.sequence_number%TYPE
1006 )
1007 AS
1008
1009 --------------------------------------------------------------------------------------------
1010 --
1011 -- Created By : sjadhav
1012 -- Date Created By : Jan 07,2002
1013 -- Purpose : This routine inserts Pays Only Programs, Units into
1014 -- temporary table igf_db_pays_prg_t
1015 --
1016 --------------------------------------------------------------------------------------------
1017
1018 -- Get all the teaching periods for the academic calendar instance
1019 CURSOR cur_get_acad_tp(
1020 p_acad_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
1021 p_acad_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE
1022 )
1023 IS
1024 SELECT sub_cal_type tp_cal_type,
1025 sub_ci_sequence_number tp_sequence_number
1026 FROM igs_ca_inst_rel cr_1, igs_ca_type ct_1, igs_ca_type ct_2
1027 WHERE ct_1.cal_type = cr_1.sup_cal_type
1028 AND ct_1.s_cal_cat = 'ACADEMIC'
1029 AND ct_2.cal_type = cr_1.sub_cal_type
1030 AND ct_2.s_cal_cat = 'TEACHING'
1031 AND cr_1.sup_cal_type = p_acad_ci_cal_type
1032 AND cr_1.sup_ci_sequence_number = p_acad_ci_sequence_number;
1033
1034 get_acad_tp_rec cur_get_acad_tp%ROWTYPE;
1035
1036
1037 -- Get all the programs,unit attempts in which student has 'enrolled'
1038
1039 CURSOR cur_get_att(
1040 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
1041 p_acad_cal_type igs_ca_inst_all.cal_type%TYPE,
1042 p_tp_cal_type igs_ca_inst_all.cal_type%TYPE,
1043 p_tp_sequence_number igs_ca_inst_all.sequence_number%TYPE
1044 )
1045 IS
1046 SELECT pg.course_cd prg_course_cd, pg.version_number prg_ver_num,
1047 su.unit_cd unit_course_cd, su.version_number unit_ver_num
1048 FROM igs_en_su_attempt su, igs_en_stdnt_ps_att pg
1049 WHERE su.person_id = p_person_id AND pg.person_id = su.person_id
1050 AND su.unit_attempt_status IN
1051 ('COMPLETED', 'ENROLLED', 'DUPLICATE')
1052 AND su.cal_type = p_tp_cal_type
1053 AND su.ci_sequence_number = p_tp_sequence_number
1054 AND pg.cal_type = p_acad_cal_type
1055 AND pg.course_cd(+) = su.course_cd;
1056
1057 get_att_rec cur_get_att%ROWTYPE;
1058 l_acad_cal_type igs_ca_inst_all.cal_type%TYPE;
1059 l_acad_seq_num igs_ca_inst_all.sequence_number%TYPE;
1060 l_acad_alt_code igs_ca_inst_all.alternate_code%TYPE;
1061 dbpays_rec igf_db_pays_prg_t%ROWTYPE;
1062 l_rowid ROWID;
1063 BEGIN
1064 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1065 THEN
1066 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','p_acad_cal_type:' || p_acad_cal_type);
1067 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','p_acad_ci_seq_num:' || p_acad_ci_seq_num);
1068 END IF;
1069
1070 FOR get_acad_tp_rec IN cur_get_acad_tp(
1071 p_acad_cal_type, p_acad_ci_seq_num
1072 )
1073 LOOP
1074 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1075 THEN
1076 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','igf_gr_gen.get_person_id(' || p_base_id || '):'|| igf_gr_gen.get_person_id(p_base_id));
1077 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','get_acad_tp_rec.tp_cal_type:' || get_acad_tp_rec.tp_cal_type);
1078 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','get_acad_tp_rec.tp_sequence_number:'|| get_acad_tp_rec.tp_sequence_number);
1079 END IF;
1080
1081 FOR get_att_rec IN cur_get_att(
1082 igf_gr_gen.get_person_id(p_base_id),
1083 p_acad_cal_type, get_acad_tp_rec.tp_cal_type,
1084 get_acad_tp_rec.tp_sequence_number
1085 )
1086 LOOP
1087 dbpays_rec.base_id := p_base_id;
1088 dbpays_rec.program_cd := get_att_rec.prg_course_cd;
1089 dbpays_rec.prg_ver_num := get_att_rec.prg_ver_num;
1090 dbpays_rec.unit_cd := get_att_rec.unit_course_cd;
1091 dbpays_rec.unit_ver_num := get_att_rec.unit_ver_num;
1092
1093 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1094 THEN
1095 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.program_cd:' || dbpays_rec.program_cd);
1096 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.prg_ver_num:' || dbpays_rec.prg_ver_num);
1097 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.unit_cd:' || dbpays_rec.unit_cd);
1098 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.unit_ver_num:' || dbpays_rec.unit_ver_num);
1099 END IF;
1100
1101 l_rowid := NULL;
1102
1103 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1104 THEN
1105 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','inserting to igf_db_pays_prg_t');
1106 END IF;
1107
1108 igf_db_pays_prg_t_pkg.insert_row(
1109 x_rowid => l_rowid, x_dbpays_id => dbpays_rec.dbpays_id,
1110 x_base_id => dbpays_rec.base_id,
1111 x_program_cd => dbpays_rec.program_cd,
1112 x_prg_ver_num => dbpays_rec.prg_ver_num,
1113 x_unit_cd => dbpays_rec.unit_cd,
1114 x_unit_ver_num => dbpays_rec.unit_ver_num, x_mode => 'R'
1115 );
1116 END LOOP;
1117 END LOOP;
1118 EXCEPTION
1119 WHEN OTHERS
1120 THEN
1121 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1122 fnd_message.set_token(
1123 'NAME', 'IGF_SL_REL_DISB.INSERT_PAYS_PRG_UTS ' || SQLERRM
1124 );
1125 fnd_file.put_line(fnd_file.log, SQLERRM);
1126 igs_ge_msg_stack.ADD;
1127 app_exception.raise_exception;
1128 END insert_pays_prg_uts;
1129
1130 FUNCTION chk_pays_prg(
1131 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1132 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1133 )
1134 RETURN BOOLEAN
1135 AS
1136
1137 --------------------------------------------------------------------------------------------
1138 --
1139 -- Created By : sjadhav
1140 -- Date Created By : Jan 07,2002
1141 -- Purpose : This routine Pays Only Program Check
1142 --
1143 --------------------------------------------------------------------------------------------
1144
1145 --
1146 -- This cursor will retreive records which are
1147 -- common to temp table ( igf_db_pays_prg_t ) and fund setup for pays only program
1148 -- If there are no records, the check is failed else passed
1149 --
1150
1151 CURSOR cur_std_pays(
1152 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1153 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE
1154 )
1155 IS
1156 SELECT program_cd, prg_ver_num
1157 FROM igf_db_pays_prg_t
1158 WHERE base_id = p_base_id
1159 INTERSECT
1160 SELECT course_cd, version_number
1161 FROM igf_aw_fund_prg_v fprg
1162 WHERE fprg.fund_id = p_fund_id;
1163
1164 std_pays_rec cur_std_pays%ROWTYPE;
1165
1166
1167 --
1168 -- This cursor will retreive records from fund setup for pays only program
1169 -- If there are no records, then the pays only prog check is passed
1170 --
1171 CURSOR cur_fund_pprg(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1172 IS
1173 SELECT course_cd, version_number
1174 FROM igf_aw_fund_prg_v fprg
1175 WHERE fprg.fund_id = p_fund_id;
1176
1177 fund_pprg_rec cur_fund_pprg%ROWTYPE;
1178 BEGIN
1179 OPEN cur_fund_pprg(p_fund_id);
1180 FETCH cur_fund_pprg INTO fund_pprg_rec;
1181
1182 IF cur_fund_pprg%NOTFOUND
1183 THEN
1184 CLOSE cur_fund_pprg;
1185 RETURN TRUE;
1186 ELSIF cur_fund_pprg%FOUND
1187 THEN
1188 CLOSE cur_fund_pprg;
1189 OPEN cur_std_pays(p_base_id, p_fund_id);
1190 FETCH cur_std_pays INTO std_pays_rec;
1191
1192 IF cur_std_pays%FOUND
1193 THEN
1194 CLOSE cur_std_pays;
1195 RETURN TRUE;
1196 ELSIF cur_std_pays%NOTFOUND
1197 THEN
1198 CLOSE cur_std_pays;
1199 RETURN FALSE;
1200 END IF;
1201 END IF;
1202 EXCEPTION
1203 WHEN OTHERS
1204 THEN
1205 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1206 fnd_message.set_token('NAME', 'IGF_DB_DISB.CHK_PAYS_PRG ' || SQLERRM);
1207 fnd_file.put_line(fnd_file.log, SQLERRM);
1208 igs_ge_msg_stack.ADD;
1209 app_exception.raise_exception;
1210 END chk_pays_prg;
1211
1212 FUNCTION chk_pays_uts(
1213 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1214 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1215 )
1216 RETURN BOOLEAN
1217 AS
1218
1219 --------------------------------------------------------------------------------------------
1220 --
1221 -- Created By : sjadhav
1222 -- Date Created By : Jan 07,2002
1223 -- Purpose : This routine Pays Only Units Check
1224 -- previous run of eligibility checks
1225 --
1226 --------------------------------------------------------------------------------------------
1227 --
1228 -- This cursor will retreive records which are
1229 -- common to temp table ( igf_db_pays_prg_t ) and fund setup for pays only units
1230 -- If there are no records, the check is failed else passed
1231 --
1232
1233 CURSOR cur_std_pays(
1234 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1235 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE
1236 )
1237 IS
1238 SELECT unit_cd, unit_ver_num
1239 FROM igf_db_pays_prg_t
1240 WHERE base_id = p_base_id
1241 INTERSECT
1242 SELECT unit_cd, version_number
1243 FROM igf_aw_fund_unit_v funit
1244 WHERE funit.fund_id = p_fund_id;
1245
1246 std_pays_rec cur_std_pays%ROWTYPE;
1247
1248
1249 --
1250 -- This cursor will retreive records from fund setup for pays only program
1251 -- If there are no records, then the pays only prog check is passed
1252 --
1253 CURSOR cur_fund_unit(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1254 IS
1255 SELECT unit_cd, version_number
1256 FROM igf_aw_fund_unit_v funit
1257 WHERE funit.fund_id = p_fund_id;
1258
1259 fund_unit_rec cur_fund_unit%ROWTYPE;
1260 BEGIN
1261 OPEN cur_fund_unit(p_fund_id);
1262 FETCH cur_fund_unit INTO fund_unit_rec;
1263
1264 IF cur_fund_unit%NOTFOUND
1265 THEN
1266 CLOSE cur_fund_unit;
1267 RETURN TRUE;
1268 ELSIF cur_fund_unit%FOUND
1269 THEN
1270 CLOSE cur_fund_unit;
1271 OPEN cur_std_pays(p_base_id, p_fund_id);
1272 FETCH cur_std_pays INTO std_pays_rec;
1273
1274 IF cur_std_pays%FOUND
1275 THEN
1276 CLOSE cur_std_pays;
1277 RETURN TRUE;
1278 ELSIF cur_std_pays%NOTFOUND
1279 THEN
1280 CLOSE cur_std_pays;
1281 RETURN FALSE;
1282 END IF;
1283 END IF;
1284 EXCEPTION
1285 WHEN OTHERS
1286 THEN
1287 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1288 fnd_message.set_token('NAME', 'IGF_DB_DISB.CHK_PAYS_UTS ' || SQLERRM);
1289 fnd_file.put_line(fnd_file.log, SQLERRM);
1290 igs_ge_msg_stack.ADD;
1291 app_exception.raise_exception;
1292 END chk_pays_uts;
1293
1294 FUNCTION chk_fclass_result(
1295 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1296 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1297 p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
1298 p_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE
1299 )
1300 RETURN BOOLEAN
1301 AS
1302
1303 --------------------------------------------------------------------------------------------
1304 --
1305 -- Created By : pssahni
1306 -- Date Created By : Oct 4,2004
1307 -- Purpose : This Process evaluates student for fee class
1308 --
1309 --------------------------------------------------------------------------------------------
1310
1311
1312 p_fee_cal_type igs_ca_inst_all.cal_type%TYPE;
1313 p_fee_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1314 p_message_name fnd_new_messages.message_name%TYPE;
1315
1316
1317 --
1318 -- This cursor will retreive records from fund setup for pays only Fee Class
1319 -- If there are no records, then the pays only fee class check is passed
1320 --
1321 CURSOR cur_fund_fcls(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1322 IS
1323 SELECT fee_class
1324 FROM igf_aw_fund_feeclas
1325 WHERE fund_id = p_fund_id;
1326
1327 fund_fcls_rec cur_fund_fcls%ROWTYPE;
1328
1329
1330 --
1331 -- This cursor will return common fee classes from
1332 -- fund setup and persons charges
1333 --
1334
1335 CURSOR cur_fee_cls(
1336 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1337 p_fee_cal_type igs_ca_inst_all.cal_type%TYPE,
1338 p_fee_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1339 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE
1340 )
1341 IS
1342 SELECT fee_class
1343 FROM igf_aw_fund_feeclas
1344 WHERE fund_id = p_fund_id
1345 INTERSECT
1346 SELECT fee_class
1347 FROM igs_fi_inv_igf_v
1348 WHERE fee_cal_type = p_fee_cal_type
1349 AND fee_ci_sequence_number = p_fee_ci_sequence_number
1350 AND person_id = p_person_id;
1351
1352 fee_cls_rec cur_fee_cls%ROWTYPE;
1353 lv_bool BOOLEAN;
1354 BEGIN
1355 OPEN cur_fund_fcls(p_fund_id);
1356 FETCH cur_fund_fcls INTO fund_fcls_rec;
1357
1358 IF cur_fund_fcls%NOTFOUND
1359 THEN
1360 CLOSE cur_fund_fcls;
1361 RETURN TRUE;
1362 ELSIF cur_fund_fcls%FOUND
1363 THEN
1364 CLOSE cur_fund_fcls;
1365 lv_bool := igs_fi_gen_001.finp_get_lfci_reln(
1366 p_ld_cal_type, p_ld_sequence_number, 'LOAD',
1367 p_fee_cal_type, p_fee_ci_sequence_number,
1368 p_message_name
1369 );
1370
1371 IF p_message_name IS NULL
1372 THEN
1373 OPEN cur_fee_cls(
1374 p_fund_id, p_fee_cal_type, p_fee_ci_sequence_number,
1375 igf_gr_gen.get_person_id(p_base_id)
1376 );
1377 FETCH cur_fee_cls INTO fee_cls_rec;
1378
1379 IF cur_fee_cls%FOUND
1380 THEN
1381 CLOSE cur_fee_cls;
1382 RETURN TRUE;
1383 ELSIF cur_fee_cls%NOTFOUND
1384 THEN
1385 CLOSE cur_fee_cls;
1386 RETURN FALSE;
1387 END IF;
1388 ELSE
1389 --
1390 -- The message if not null means the relation does not exist
1391 --
1392 RETURN FALSE;
1393 END IF;
1394 END IF;
1395 EXCEPTION
1396 WHEN OTHERS
1397 THEN
1398 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1399 fnd_message.set_token(
1400 'NAME', 'IGF_DB_DISB.CHK_FCLASS_RESULT ' || SQLERRM
1401 );
1402 fnd_file.put_line(fnd_file.log, SQLERRM);
1403 igs_ge_msg_stack.ADD;
1404 app_exception.raise_exception;
1405 END chk_fclass_result;
1406
1407 PROCEDURE delete_pays
1408 AS
1409
1410 --------------------------------------------------------------------------------------------
1411 --
1412 -- Created By : pssahni
1413 -- Date Created By : 04-Oct-2004
1414 -- Purpose : This routine truncates pays only data used in
1415 -- previous run of eligibility checks
1416 --
1417 --------------------------------------------------------------------------------------------
1418 CURSOR cur_pays_prg
1419 IS
1420 SELECT db.ROWID row_id, db.*
1421 FROM igf_db_pays_prg_t db;
1422
1423 pays_prg_rec cur_pays_prg%ROWTYPE;
1424 BEGIN
1425 OPEN cur_pays_prg;
1426
1427 LOOP
1428 FETCH cur_pays_prg INTO pays_prg_rec;
1429 EXIT WHEN cur_pays_prg%NOTFOUND;
1430 igf_db_pays_prg_t_pkg.delete_row(pays_prg_rec.row_id);
1431
1432 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1433 THEN
1434 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_disb.delete_pays.debug','deleted from igf_db_pays_prg_t');
1435 END IF;
1436 END LOOP;
1437
1438 CLOSE cur_pays_prg;
1439 EXCEPTION
1440 WHEN OTHERS
1441 THEN
1442 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1443 fnd_message.set_token(
1444 'NAME', 'IGF_SL_REL_DISB.DELETE_PAYS ' || SQLERRM
1445 );
1446 fnd_file.put_line(fnd_file.log, SQLERRM);
1447 igs_ge_msg_stack.ADD;
1448 app_exception.raise_exception;
1449 END delete_pays;
1450
1451 FUNCTION get_loan_num(p_loan_id igf_sl_loans_all.loan_id%TYPE)
1452 RETURN VARCHAR2
1453 AS
1454 /*
1455 || Created By : pssahni
1456 || Created On :
1457 || Purpose : To get loan number from a loan id
1458 || Known limitations, enhancements or remarks :
1459 || Change History :
1460 || Who When What
1461 || (reverse chronological order - newest change first)
1462 */
1463
1464 -- Get loan number
1465 CURSOR cur_get_loan_num(p_loan_id igf_sl_loans_all.loan_id%TYPE)
1466 IS
1467 SELECT loan_number
1468 FROM igf_sl_loans
1469 WHERE p_loan_id = loan_id;
1470
1471 get_loan_num_rec cur_get_loan_num%ROWTYPE;
1472 BEGIN
1473 OPEN cur_get_loan_num(p_loan_id);
1474 FETCH cur_get_loan_num INTO get_loan_num_rec;
1475
1476 IF cur_get_loan_num%NOTFOUND
1477 THEN
1478 CLOSE cur_get_loan_num;
1479 RETURN NULL;
1480 ELSE --cur_fund_fcls%FOUND THEN
1481 CLOSE cur_get_loan_num;
1482 RETURN get_loan_num_rec.loan_number;
1483 END IF;
1484 EXCEPTION
1485 WHEN OTHERS
1486 THEN
1487 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1488 fnd_message.set_token(
1489 'NAME', 'IGF_SL_REL_DISB.DELETE_PAYS ' || SQLERRM
1490 );
1491 fnd_file.put_line(fnd_file.log, SQLERRM);
1492 igs_ge_msg_stack.ADD;
1493 app_exception.raise_exception;
1494 END get_loan_num;
1495
1496 FUNCTION get_cut_off_dt(
1497 p_ld_seq_num igs_ca_inst_all.sequence_number%TYPE,
1498 p_disb_date igf_aw_awd_disb_all.disb_date%TYPE
1499 )
1500 RETURN DATE
1501 IS
1502
1503 -----------------------------------------------------------------------------------------
1504 -- sjadhav,May.30.2002.
1505 -- Bug 2387496
1506 --
1507 -- This function will return the cut off date
1508 -- to be paased to enrolment api for getting
1509 -- poin-in-time credit points
1510 -----------------------------------------------------------------------------------------
1511
1512 CURSOR cur_get_eff_date(
1513 p_ld_seq_num igs_ca_inst_all.sequence_number%TYPE
1514 )
1515 IS
1516 SELECT start_dt, end_dt
1517 FROM igs_ca_inst_all
1518 WHERE p_ld_seq_num = sequence_number;
1519
1520 get_eff_date_rec cur_get_eff_date%ROWTYPE;
1521 ld_cut_off_dt igf_aw_awd_disb_all.disb_date%TYPE;
1522 ld_system_dt igf_aw_awd_disb_all.disb_date%TYPE;
1523 ld_start_dt igf_aw_awd_disb_all.disb_date%TYPE;
1524 ld_end_dt igf_aw_awd_disb_all.disb_date%TYPE;
1525 BEGIN
1526 ld_system_dt := TRUNC(SYSDATE);
1527 ld_cut_off_dt := ld_system_dt;
1528 OPEN cur_get_eff_date(p_ld_seq_num);
1529 FETCH cur_get_eff_date INTO get_eff_date_rec;
1530 CLOSE cur_get_eff_date;
1531 ld_start_dt := TRUNC(get_eff_date_rec.start_dt);
1532 ld_end_dt := TRUNC(get_eff_date_rec.end_dt);
1533
1534
1535 -- 1.
1536
1537 IF p_disb_date < ld_system_dt
1538 THEN
1539 IF p_disb_date >= ld_start_dt AND p_disb_date <= ld_end_dt
1540 THEN
1541 ld_cut_off_dt := ld_system_dt;
1542 END IF;
1543 END IF;
1544
1545
1546 -- 2.
1547
1548 IF p_disb_date < ld_start_dt
1549 THEN
1550 IF ld_system_dt > ld_start_dt AND ld_system_dt < ld_end_dt
1551 THEN
1552 ld_cut_off_dt := ld_system_dt;
1553 END IF;
1554 END IF;
1555
1556
1557 -- 3.
1558
1559 IF p_disb_date < ld_system_dt AND ld_system_dt < ld_start_dt
1560 THEN
1561 ld_cut_off_dt := ld_start_dt;
1562 END IF;
1563
1564
1565 -- 4.
1566
1567 IF p_disb_date > ld_end_dt
1568 THEN
1569 ld_cut_off_dt := ld_end_dt;
1570 END IF;
1571
1572 RETURN ld_cut_off_dt;
1573 EXCEPTION
1574 WHEN OTHERS
1575 THEN
1576 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1577 fnd_message.set_token(
1578 'NAME', 'IGF_DB_DISB.GET_CUT_OFF_DT ' || SQLERRM
1579 );
1580 fnd_file.put_line(fnd_file.log, SQLERRM);
1581 igs_ge_msg_stack.ADD;
1582 app_exception.raise_exception;
1583 END get_cut_off_dt;
1584
1585 PROCEDURE set_dri_true(
1586 p_award_id igf_aw_awd_disb_all.award_id%TYPE,
1587 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE,
1588 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE, p_fund_type VARCHAR2
1589 )
1590 AS
1591
1592 --------------------------------------------------------------------------------------------
1593 --
1594 -- Created By : pssahni
1595 -- Date Created By : Oct 7,2004
1596 -- Purpose : This routine sets the Disbursment Release Indicator to true
1597 --
1598 --------------------------------------------------------------------------------------------
1599
1600
1601 CURSOR cur_get_adisb(
1602 p_award_id igf_aw_awd_disb_all.award_id%TYPE,
1603 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
1604 )
1605 IS
1606 SELECT ROWID row_id, adisb.*
1607 FROM igf_aw_awd_disb_all adisb
1608 WHERE adisb.award_id = p_award_id
1609 AND adisb.disb_num = p_disb_num
1610 FOR UPDATE OF elig_status NOWAIT;
1611
1612 get_adisb_rec cur_get_adisb%ROWTYPE;
1613 lv_rowid ROWID;
1614 lv_called_from VARCHAR2(30);
1615 BEGIN
1616 OPEN cur_get_adisb(p_award_id, p_disb_num);
1617 FETCH cur_get_adisb INTO get_adisb_rec;
1618 CLOSE cur_get_adisb;
1619
1620 -- Update DRI to true
1621 lv_called_from := NULL;
1622
1623 IF p_fund_type IN ('P', 'D')
1624 THEN
1625 get_adisb_rec.hold_rel_ind := 'TRUE';
1626 ELSIF p_fund_type = 'F'
1627 THEN
1628 get_adisb_rec.hold_rel_ind := 'R';
1629 lv_called_from := 'IGFSL27B';
1630 END IF;
1631
1632 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1633 THEN
1634 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.set_dri_true .debug','set Disbursment Release Indicator to True');
1635 END IF;
1636
1637 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1638 THEN
1639 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_disb.create_actual.debug','updating igf_aw_awd_disb for award ' || get_adisb_rec.award_id);
1640 END IF;
1641
1642 igf_aw_awd_disb_pkg.update_row(
1643 x_rowid => get_adisb_rec.row_id,
1644 x_award_id => get_adisb_rec.award_id,
1645 x_disb_num => get_adisb_rec.disb_num,
1646 x_tp_cal_type => get_adisb_rec.tp_cal_type,
1647 x_tp_sequence_number => get_adisb_rec.tp_sequence_number,
1648 x_disb_gross_amt => get_adisb_rec.disb_gross_amt,
1649 x_fee_1 => get_adisb_rec.fee_1,x_fee_2 => get_adisb_rec.fee_2,
1650 x_disb_net_amt => get_adisb_rec.disb_net_amt,
1651 x_disb_date => get_adisb_rec.disb_date,
1652 x_trans_type => get_adisb_rec.trans_type,
1653 x_elig_status => get_adisb_rec.elig_status,
1654 x_elig_status_date => get_adisb_rec.elig_status_date,
1655 x_affirm_flag => get_adisb_rec.affirm_flag,
1656 x_hold_rel_ind => get_adisb_rec.hold_rel_ind,
1657 x_manual_hold_ind => get_adisb_rec.manual_hold_ind,
1658 x_disb_status => get_adisb_rec.disb_status,
1659 x_disb_status_date => get_adisb_rec.disb_status_date,
1660 x_late_disb_ind => get_adisb_rec.late_disb_ind,
1661 x_fund_dist_mthd => get_adisb_rec.fund_dist_mthd,
1662 x_prev_reported_ind => get_adisb_rec.prev_reported_ind,
1663 x_fund_release_date => get_adisb_rec.fund_release_date,
1664 x_fund_status => get_adisb_rec.fund_status,
1665 x_fund_status_date => get_adisb_rec.fund_status_date,
1666 x_fee_paid_1 => get_adisb_rec.fee_paid_1,
1667 x_fee_paid_2 => get_adisb_rec.fee_paid_2,
1668 x_cheque_number => get_adisb_rec.cheque_number,
1669 x_ld_cal_type => get_adisb_rec.ld_cal_type,
1670 x_ld_sequence_number => get_adisb_rec.ld_sequence_number,
1671 x_disb_accepted_amt => get_adisb_rec.disb_accepted_amt,
1672 x_disb_paid_amt => get_adisb_rec.disb_paid_amt,
1673 x_rvsn_id => get_adisb_rec.rvsn_id,
1674 x_int_rebate_amt => get_adisb_rec.int_rebate_amt,
1675 x_force_disb => get_adisb_rec.force_disb,
1676 x_min_credit_pts => get_adisb_rec.min_credit_pts,
1677 x_disb_exp_dt => get_adisb_rec.disb_exp_dt,
1678 x_verf_enfr_dt => get_adisb_rec.verf_enfr_dt,
1679 x_fee_class => get_adisb_rec.fee_class,
1680 x_show_on_bill => get_adisb_rec.show_on_bill,
1681 x_attendance_type_code => get_adisb_rec.attendance_type_code,
1682 x_base_attendance_type_code => get_adisb_rec.base_attendance_type_code,
1683 x_mode => 'R',
1684 x_called_from => lv_called_from,
1685 x_payment_prd_st_date => get_adisb_rec.payment_prd_st_date,
1686 x_change_type_code => get_adisb_rec.change_type_code,
1687 x_fund_return_mthd_code => get_adisb_rec.fund_return_mthd_code,
1688 x_direct_to_borr_flag => get_adisb_rec.direct_to_borr_flag
1689 );
1690 EXCEPTION
1691 WHEN app_exception.record_lock_exception
1692 THEN
1693 RAISE;
1694 WHEN OTHERS
1695 THEN
1696 fnd_message.CLEAR;
1697 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1698 fnd_message.set_token('NAME', 'IGF_DB_DISB.CREATE_ACTUAL ' || SQLERRM);
1699 fnd_file.put_line(fnd_file.log, SQLERRM);
1700 igs_ge_msg_stack.initialize;
1701 igs_ge_msg_stack.ADD;
1702 app_exception.raise_exception;
1703 END set_dri_true;
1704
1705
1706 --------------------------------------------------------------------------------------------------------------------------------
1707
1708 PROCEDURE process_student(
1709 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1710 p_result IN OUT NOCOPY VARCHAR2,
1711 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1712 p_award_id igf_aw_award_all.award_id%TYPE,
1713 p_loan_id igf_sl_loans_all.loan_id%TYPE,
1714 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE, p_trans_type VARCHAR2
1715 )
1716 AS
1717
1718 --------------------------------------------------------------------------------------------
1719 -- Purpose : This Process performs various validations required
1720 -- inorder to set the disbursment hold indicator to true
1721 --The checks that are to be done for a fund in the award year context are:
1722 --1. To Do Item Validations
1723 --2. Pays Only Program
1724 --3. Pays Only Units
1725 --4. Eligibility check for getting loans (NSLDS_ELIGIBLE)
1726 --5. Eligibility check for getting PELL Grant (PELL_ELIGIBLE)
1727 --Award Year Level checks are to be done only for the first disbursement record for the Fund
1728
1729 --Checks that should be done for an Award
1730 --6. Active Loan
1731 --7. System holds
1732
1733 --Checks for an Award should be done only once and the result should be stored
1734
1735 --The checks that are to be done at the term level
1736
1737 --10. Pays Only Fee Class
1738 --11. Minimum Attendance type if specified
1739
1740
1741 --Term Level checks should be done only once for the first disbursement record for a Term
1742
1743 --The cheks that are to be done at the disbursement level for disbursing a fund
1744
1745 --12. Cumulative Current Credit Points
1746 --13. Fuding method check
1747 --14. Credit Status Check (Applicable for PLUS-Direct Loans only)
1748
1749 --------------------------------------------------------------------------------------------
1750
1751 skip_record EXCEPTION;
1752
1753
1754 --Get all the awards and corresponding disbursements for the student for a given fund in case of Direct Loans
1755 CURSOR cur_awd_disb_dl(
1756 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1757 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1758 p_award_id igf_aw_award_all.award_id%TYPE,
1759 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE,
1760 p_trans_type igf_aw_awd_disb_all.trans_type%TYPE
1761 )
1762 IS
1763 SELECT adisb.*, awd.base_id, fmast.ci_cal_type,
1764 fmast.ci_sequence_number, fmast.fund_id, fmast.fund_code,
1765 cat.fed_fund_code
1766 FROM igf_aw_awd_disb_all adisb, igf_aw_fund_mast_all fmast,
1767 igf_aw_award_all awd, igf_aw_fund_cat_all cat
1768 WHERE adisb.award_id = awd.award_id
1769 AND fmast.fund_code = cat.fund_code
1770 AND fmast.fund_id = awd.fund_id AND awd.base_id = p_base_id
1771 AND awd.fund_id = p_fund_id
1772 AND cat.fed_fund_code IN ('PELL', 'DLP', 'DLS', 'DLU')
1773 AND adisb.award_id = NVL(p_award_id, adisb.award_id)
1774 AND adisb.disb_num = NVL(p_disb_num, adisb.disb_num)
1775 AND (
1776 (adisb.trans_type = 'A') OR
1777 (p_trans_type = 'P' AND adisb.trans_type = 'P')
1778 )
1779 AND (adisb.hold_rel_ind = 'FALSE' OR adisb.hold_rel_ind IS NULL)
1780 ORDER BY awd.fund_id, awd.award_id, adisb.disb_num;
1781
1782
1783 --Get all the awards and corresponding disbursements for the student for a given fund
1784 -- in case of FFELP Loans and if transaction type is specified
1785 CURSOR cur_awd_disb_fed(
1786 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1787 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1788 p_award_id igf_aw_award_all.award_id%TYPE,
1789 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE, p_trans_type VARCHAR2
1790 )
1791 IS
1792 SELECT adisb.*, awd.base_id, fmast.ci_cal_type,
1793 fmast.ci_sequence_number, fmast.fund_id, fmast.fund_code,
1794 cat.fed_fund_code
1795 FROM igf_aw_awd_disb_all adisb, igf_aw_fund_mast_all fmast,
1796 igf_aw_award_all awd, igf_aw_fund_cat_all cat
1797 WHERE adisb.award_id = awd.award_id
1798 AND fmast.fund_code = cat.fund_code
1799 AND fmast.fund_id = awd.fund_id AND awd.base_id = p_base_id
1800 AND awd.fund_id = p_fund_id
1801 AND cat.fed_fund_code IN ('ALT', 'FLP', 'FLS', 'FLU')
1802 AND adisb.award_id = NVL(p_award_id, adisb.award_id)
1803 AND adisb.disb_num = NVL(p_disb_num, adisb.disb_num)
1804 AND (adisb.hold_rel_ind = 'H' OR adisb.hold_rel_ind IS NULL)
1805 AND adisb.trans_type = p_trans_type
1806 ORDER BY awd.fund_id, awd.award_id, adisb.disb_num;
1807
1808
1809 --Get all the awards and corresponding disbursements for the student for a given fund
1810 -- in case of FFELP Loans and if transaction type is not specified
1811 CURSOR cur_awd_disb_fed_no_trans(
1812 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1813 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1814 p_award_id igf_aw_award_all.award_id%TYPE,
1815 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
1816 )
1817 IS
1818 SELECT adisb.*, awd.base_id, fmast.ci_cal_type,
1819 fmast.ci_sequence_number, fmast.fund_id, fmast.fund_code,
1820 cat.fed_fund_code
1821 FROM igf_aw_awd_disb_all adisb, igf_aw_fund_mast_all fmast,
1822 igf_aw_award_all awd, igf_aw_fund_cat_all cat
1823 WHERE adisb.award_id = awd.award_id
1824 AND fmast.fund_code = cat.fund_code
1825 AND fmast.fund_id = awd.fund_id AND awd.base_id = p_base_id
1826 AND awd.fund_id = p_fund_id
1827 AND cat.fed_fund_code IN ('ALT', 'FLP', 'FLS', 'FLU')
1828 AND adisb.award_id = NVL(p_award_id, adisb.award_id)
1829 AND adisb.disb_num = NVL(p_disb_num, adisb.disb_num)
1830 AND (adisb.hold_rel_ind = 'H' OR adisb.hold_rel_ind IS NULL)
1831 AND adisb.disb_date <= TRUNC(SYSDATE)
1832 AND adisb.trans_type IN ('A', 'P')
1833 ORDER BY awd.fund_id, awd.award_id, adisb.disb_num;
1834
1835 awd_disb_rec cur_awd_disb_dl%ROWTYPE;
1836
1837
1838 -- Cursor to get Verification Status of Student
1839
1840 CURSOR cur_get_ver(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1841 IS
1842 SELECT NVL(fed_verif_status, '*') fed_verif_status
1843 FROM igf_ap_fa_base_rec
1844 WHERE base_id = p_base_id;
1845
1846 get_ver_rec cur_get_ver%ROWTYPE;
1847
1848 -- Get the loan generated for this award
1849 CURSOR cur_get_loans(p_award_id NUMBER, p_loan_id NUMBER)
1850 IS
1851 SELECT loan_id, loan_number, loan_status, active, loan_chg_status
1852 FROM igf_sl_loans_all
1853 WHERE award_id = p_award_id AND loan_id = NVL(loan_id, p_loan_id);
1854
1855 get_loans_rec cur_get_loans%ROWTYPE;
1856
1857 CURSOR cur_get_pell(p_award_id igf_aw_award_all.award_id%TYPE)
1858 IS
1859 SELECT orig_action_code, origination_id
1860 FROM igf_gr_rfms_all
1861 WHERE award_id = p_award_id;
1862
1863 get_pell_rec cur_get_pell%ROWTYPE;
1864
1865 -- Get the fed fund code of the fund
1866 CURSOR cur_get_fed_fund_code(
1867 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE
1868 )
1869 IS
1870 SELECT cat.fed_fund_code
1871 FROM igf_aw_fund_cat_all cat, igf_aw_fund_mast_all fmast
1872 WHERE fmast.fund_code = cat.fund_code AND fmast.fund_id = p_fund_id;
1873
1874 get_fed_fund_code_rec cur_get_fed_fund_code%ROWTYPE;
1875 l_acad_cal_type igs_ca_inst_all.cal_type%TYPE;
1876 l_acad_seq_num igs_ca_inst_all.sequence_number%TYPE;
1877 l_acad_alt_code igs_ca_inst_all.alternate_code%TYPE;
1878 l_message_name fnd_new_messages.message_text%TYPE;
1879 l_credit_pts igf_aw_awd_disb_all.min_credit_pts%TYPE;
1880
1881 --Variables to Store the validation results
1882
1883 l_pays_prg BOOLEAN := TRUE; -- Pays Only Program Result
1884 l_fclass_result BOOLEAN := TRUE; -- Fee Class Result
1885 l_pays_uts BOOLEAN := TRUE; -- Pays Only Units Result
1886 l_att_result BOOLEAN := TRUE; -- Attendance Type Result
1887 l_crdt_pt_result BOOLEAN := TRUE; -- Credit Points Result
1888 l_active_result BOOLEAN := TRUE; -- Loan Active Result
1889 l_sys_hold_result BOOLEAN := TRUE; -- System Hold Result
1890 l_ac_hold_result BOOLEAN := TRUE; -- Academic Hold Result
1891 l_elig_result BOOLEAN := TRUE; -- DL and PELL Federal Eligibility Result
1892 l_todo_result BOOLEAN := TRUE; -- To Do Result
1893 l_fund_meth_result BOOLEAN := TRUE; -- Funding method check
1894 l_crdt_st_check_plus BOOLEAN := TRUE; -- Credit status check for PLUS loans
1895
1896 -- following variables are used to make sure that the fund level,award level and term level
1897 -- checks are not repeated for each disbursement belonging to the same fund/award/term
1898
1899 l_old_fund igf_aw_fund_mast_all.fund_id%TYPE;
1900 l_new_fund igf_aw_fund_mast_all.fund_id%TYPE;
1901 l_old_awd igf_aw_award_all.award_id%TYPE;
1902 l_new_awd igf_aw_award_all.award_id%TYPE;
1903 l_old_ld_cal igf_aw_fund_tp_all.tp_cal_type%TYPE;
1904 l_old_ld_seq igf_aw_fund_tp_all.tp_sequence_number%TYPE;
1905 l_new_ld_cal igf_aw_fund_tp_all.tp_cal_type%TYPE;
1906 l_new_ld_seq igf_aw_fund_tp_all.tp_sequence_number%TYPE;
1907 p_message VARCHAR2(1000);
1908 l_status VARCHAR2(1);
1909 l_record_found BOOLEAN;
1910 BEGIN
1911 -- First we need to check if the fund being disbrused is of type Direct or PELL
1912 -- Store the type of fund as we need it in subsequent processing
1913
1914 OPEN cur_get_fed_fund_code(p_fund_id);
1915 FETCH cur_get_fed_fund_code INTO get_fed_fund_code_rec;
1916 CLOSE cur_get_fed_fund_code;
1917 l_fund_type := 'G'; -- General Fund
1918
1919 IF igf_sl_gen.chk_dl_fed_fund_code(get_fed_fund_code_rec.fed_fund_code) =
1920 'TRUE'
1921 THEN
1922 l_fund_type := 'D'; -- Direct Loan Fund
1923 ELSIF igf_sl_gen.chk_cl_fed_fund_code(
1924 get_fed_fund_code_rec.fed_fund_code
1925 ) = 'TRUE'
1926 THEN
1927 l_fund_type := 'F'; -- FFELP Fund
1928 ELSIF get_fed_fund_code_rec.fed_fund_code = 'PELL'
1929 THEN
1930 l_fund_type := 'P'; -- Pell Fund
1931 ELSE
1932 l_fund_type := 'X'; -- These fund types are not supported
1933 END IF;
1934
1935 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1936 THEN
1937 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug', 'fund code ='|| l_fund_type);
1938 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','trans type =' || p_trans_type);
1939 END IF;
1940
1941 -- Open the cursor according to the fund code and transaction type specified
1942 IF l_fund_type IN ('D', 'P')
1943 THEN
1944 OPEN cur_awd_disb_dl(p_base_id, p_fund_id, p_award_id, p_disb_num,p_trans_type);
1945 FETCH cur_awd_disb_dl INTO awd_disb_rec;
1946
1947 IF cur_awd_disb_dl%NOTFOUND
1948 THEN
1949 l_record_found := FALSE;
1950 CLOSE cur_awd_disb_dl;
1951 ELSE
1952 l_record_found := TRUE;
1953 END IF;
1954 ELSIF l_fund_type = 'F' AND p_trans_type IS NOT NULL
1955 THEN
1956 OPEN cur_awd_disb_fed(
1957 p_base_id, p_fund_id, p_award_id, p_disb_num, p_trans_type
1958 );
1959 FETCH cur_awd_disb_fed INTO awd_disb_rec;
1960
1961 IF cur_awd_disb_fed%NOTFOUND
1962 THEN
1963 l_record_found := FALSE;
1964 CLOSE cur_awd_disb_fed;
1965 ELSE
1966 l_record_found := TRUE;
1967 END IF;
1968 ELSIF l_fund_type = 'F' AND p_trans_type IS NULL
1969 THEN
1970 OPEN cur_awd_disb_fed_no_trans(
1971 p_base_id, p_fund_id, p_award_id, p_disb_num
1972 );
1973 FETCH cur_awd_disb_fed_no_trans INTO awd_disb_rec;
1974
1975 IF cur_awd_disb_fed_no_trans%NOTFOUND
1976 THEN
1977 l_record_found := FALSE;
1978 CLOSE cur_awd_disb_fed_no_trans;
1979 ELSE
1980 l_record_found := TRUE;
1981 END IF;
1982 END IF;
1983
1984 IF NOT (l_record_found)
1985 THEN
1986 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1987 THEN
1988 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','no records found');
1989 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','p_base_id =' || p_base_id);
1990 END IF;
1991
1992
1993 -- No Disbursements found for Student <person number> and Fund <fund desc>)
1994 fnd_message.set_name('IGF', 'IGF_SL_NO_DISB_TO_REL');
1995 fnd_message.set_token('PER_NUM', igf_gr_gen.get_per_num(p_base_id));
1996 fnd_message.set_token('FDESC', get_fund_desc(p_fund_id));
1997
1998 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1999 THEN
2000 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','no disbursments availaible for release'|| igf_gr_gen.get_per_num(p_base_id));
2001 END IF;
2002
2003 p_result := fnd_message.get;
2004 fnd_file.put_line(fnd_file.log, RPAD(' ', 10) || p_result);
2005 ELSE -- record found then
2006 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2007 THEN
2008 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','cur_awd_disb%FOUND');
2009 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','calling delete_pays');
2010 END IF;
2011
2012 -- truncate previous records that were used in determining eligibility of the student in the previous run
2013 delete_pays();
2014
2015 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2016 THEN
2017 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','called delete_pays');
2018 END IF;
2019
2020 l_old_fund := -1;
2021 l_new_fund := 0;
2022 l_old_awd := -1;
2023 l_new_awd := 0;
2024 l_old_ld_cal := '-1';
2025 l_old_ld_seq := -1;
2026 l_new_ld_cal := '0';
2027 l_new_ld_seq := 0;
2028
2029 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2030 THEN
2031 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','calling get_acad_cal_from_awd with the following info');
2032 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','awd_disb_rec.ci_cal_type:' || awd_disb_rec.ci_cal_type);
2033 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','awd_disb_rec.ci_sequence_number:'|| awd_disb_rec.ci_sequence_number);
2034 END IF;
2035
2036 -- Get Academic Calendar Information
2037 igf_ap_oss_integr.get_acad_cal_from_awd(
2038 awd_disb_rec.ci_cal_type, awd_disb_rec.ci_sequence_number,
2039 l_acad_cal_type, l_acad_seq_num, l_acad_alt_code
2040 );
2041
2042 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2043 THEN
2044 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','get_acad_cal_from_awd returned the following info');
2045 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','l_acad_cal_type:'||l_acad_cal_type);
2046 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','l_acad_seq_num:'||l_acad_seq_num);
2047 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','l_acad_alt_code:'||l_acad_alt_code);
2048 END IF;
2049
2050
2051 --
2052 -- First get all the enrolled programs, unit sets for the student and insert into IGF_DB_PAYS_PRG_T
2053 -- We are doing this before starting the main loop.
2054 -- It may very well happen that there are no Pays only units or programs defined in fund setup
2055 -- Still we need to have the the enrolled programs, unit sets for the student into IGF_DB_PAYS_PRG_T
2056 --
2057
2058 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2059 THEN
2060 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','calling insert_pays_prg_uts');
2061 END IF;
2062
2063 insert_pays_prg_uts(awd_disb_rec.base_id, l_acad_cal_type, l_acad_seq_num);
2064
2065 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2066 THEN
2067 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','called insert_pays_prg_uts');
2068 END IF;
2069
2070 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2071 THEN
2072 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','starting processing for ' || igf_gr_gen.get_per_num(p_base_id)|| ' ' || get_fund_desc(awd_disb_rec.fund_id));
2073 END IF;
2074
2075 -- FOR all the records IN CUR_AWD_DISB
2076 LOOP
2077 BEGIN
2078 -- clear message stack
2079 fnd_message.CLEAR;
2080
2081 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2082 THEN
2083 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','l_fund_type:' || l_fund_type);
2084 END IF;
2085
2086 l_old_fund := l_new_fund;
2087 l_new_fund := awd_disb_rec.fund_id;
2088
2089 IF l_old_fund = 0
2090 OR (l_old_fund <> l_new_fund AND l_old_fund > 1)
2091 THEN
2092 fnd_file.new_line(fnd_file.log, 1);
2093 fnd_message.set_name('IGF', 'IGF_DB_PROCESS_STD_FUND');
2094 fnd_message.set_token('PER_NUM', igf_gr_gen.get_per_num(p_base_id));
2095 fnd_message.set_token('FDESC', get_fund_desc(awd_disb_rec.fund_id));
2096 fnd_file.put_line(
2097 fnd_file.log, RPAD(' ', 10) || fnd_message.get
2098 );
2099 END IF;
2100
2101 fnd_file.new_line(fnd_file.log, 1);
2102 fnd_message.set_name('IGF', 'IGF_DB_PROCESS_AWD_DISB');
2103 -- 'Processing disbursement for award <award id > ,disbursement <disbursement number>'
2104 fnd_message.set_token('AWARD_ID', TO_CHAR(awd_disb_rec.award_id));
2105 fnd_message.set_token('DISB_NUM', TO_CHAR(awd_disb_rec.disb_num));
2106 fnd_file.put_line(fnd_file.log, RPAD(' ', 15) || fnd_message.get);
2107 fnd_file.new_line(fnd_file.log, 1);
2108
2109 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2110 THEN
2111 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','awd_disb_rec.award_id:' || awd_disb_rec.award_id);
2112 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','awd_disb_rec.disb_num:' || awd_disb_rec.disb_num);
2113 END IF;
2114
2115 -- Validations
2116 --
2117 -- The checks that are to be done for a fund in the award year context are:
2118 -- 1. To Do Item Validations
2119 -- 2. Pays Only Program
2120 -- 3. Pays Only Units
2121 -- 4. Eligibility check for getting loans (NSLDS_ELIGIBLE)
2122 -- 5. Eligibility check for getting PELL Grant (PELL_ELIGIBLE)
2123 --
2124 IF l_old_fund <> l_new_fund
2125 THEN
2126 IF fnd_log.level_statement >=fnd_log.g_current_runtime_level
2127 THEN
2128 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','processing in award year context '|| igf_gr_gen.get_per_num(p_base_id) || ' '|| get_fund_desc(awd_disb_rec.fund_id));
2129 END IF;
2130
2131 -- For each new fund that is visible within this scope,
2132 -- the result variables are initialized
2133
2134 l_todo_result := TRUE;
2135 l_pays_prg := TRUE;
2136 l_pays_uts := TRUE;
2137 l_elig_result := TRUE;
2138 l_ac_hold_result := TRUE;
2139
2140 IF igf_aw_gen_005.get_stud_hold_effect(
2141 'D', igf_gr_gen.get_person_id(p_base_id),
2142 awd_disb_rec.fund_code
2143 ) = 'F'
2144 THEN
2145 l_ac_hold_result := FALSE;
2146 END IF;
2147
2148 l_todo_result := chk_todo_result(
2149 l_message_name, awd_disb_rec.fund_id,
2150 awd_disb_rec.base_id
2151 );
2152 l_pays_prg := chk_pays_prg(
2153 awd_disb_rec.fund_id, awd_disb_rec.base_id
2154 );
2155 l_pays_uts := chk_pays_uts(
2156 awd_disb_rec.fund_id, awd_disb_rec.base_id
2157 );
2158 l_elig_result := chk_fed_elig(
2159 awd_disb_rec.base_id, l_fund_type
2160 );
2161
2162 IF NOT l_ac_hold_result
2163 THEN
2164 fnd_message.set_name('IGF', 'IGF_SL_DISB_FUND_HOLD_FAIL');
2165 p_result := fnd_message.get;
2166 fnd_file.put_line(
2167 fnd_file.log, RPAD(' ', 17) || p_result
2168 );
2169 END IF;
2170
2171 IF NOT l_todo_result AND l_message_name IS NOT NULL
2172 THEN
2173 fnd_message.set_name('IGF', l_message_name);
2174 -- 'Disbursement failed Fund To Do check'
2175 p_result := fnd_message.get;
2176 fnd_file.put_line(
2177 fnd_file.log, RPAD(' ', 17) || p_result
2178 );
2179 END IF;
2180
2181 IF NOT l_pays_prg
2182 THEN
2183 fnd_message.set_name('IGF', 'IGF_DB_FAIL_PPRG');
2184 -- 'Disbursement failed Fund Pays Only Program check'
2185 p_result := fnd_message.get;
2186 fnd_file.put_line(
2187 fnd_file.log, RPAD(' ', 17) || p_result
2188 );
2189 END IF;
2190
2191 IF NOT l_pays_uts
2192 THEN
2193 fnd_message.set_name('IGF', 'IGF_DB_FAIL_PUNT');
2194 -- 'Disbursement failed Fund Pays Only Unit check'
2195 p_result := fnd_message.get;
2196 fnd_file.put_line(
2197 fnd_file.log, RPAD(' ', 17) || p_result
2198 );
2199 END IF;
2200
2201 IF NOT l_elig_result
2202 THEN
2203 IF l_fund_type = 'P'
2204 THEN
2205 fnd_message.set_name('IGF', 'IGF_GR_PELL_INELIGIBLE');
2206 -- 'Disbursement failed Pell Eligiblity check'
2207 END IF;
2208
2209 IF l_fund_type = 'D'
2210 THEN
2211 fnd_message.set_name('IGF', 'IGF_DB_FAIL_FEDL_ELIG');
2212 -- 'Disbursement failed NSLDS Eligiblity check'
2213 END IF;
2214
2215 p_result := fnd_message.get;
2216 fnd_file.put_line(fnd_file.log, RPAD(' ', 17) || p_result);
2217 END IF;
2218
2219 IF fnd_log.level_statement >=fnd_log.g_current_runtime_level
2220 THEN
2221 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','fund level validation results');
2222 IF NOT l_ac_hold_result THEN
2223 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','disbursment hold exist');
2224 ELSE
2225 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','disbursment hold do not exist');
2226 END IF;
2227
2228 IF NOT l_todo_result THEN
2229 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','failed to do items check');
2230 ELSE
2231 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','passed to do items check');
2232 END IF;
2233
2234 IF NOT l_pays_prg THEN
2235 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','failed pays only prog check');
2236 ELSE
2237 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','passed pays only prog check');
2238 END IF;
2239
2240 IF NOT l_pays_uts THEN
2241 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','failed pays only units check');
2242 ELSE
2243 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','passed pays only units check');
2244 END IF;
2245
2246 IF NOT l_elig_result THEN
2247
2248 IF l_fund_type = 'P' THEN
2249 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','failed pell eligibilty check');
2250 ELSE
2251 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student..debug','passed pell eligibilty check');
2252 END IF;
2253
2254 IF l_fund_type ='D' THEN
2255 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','failed NSLDS eligibilty check');
2256 ELSE
2257 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','passed NSLDS eligibilty check');
2258 END IF;
2259 END IF; -- Pell Elig
2260 END IF; -- FND Log End If
2261 END IF; -- old fund id not new fund
2262 --
2263 -- Validations
2264 -- Checks that should be done at Award Level
2265 -- 1.Active Loan
2266 -- 2.Credit Status
2267 l_old_awd := l_new_awd;
2268 l_new_awd := awd_disb_rec.award_id;
2269
2270 IF l_old_awd <> l_new_awd
2271 THEN
2272 -- For each new fund that is visible within this scope,
2273 -- the result variables are initialized
2274 l_active_result := TRUE;
2275
2276 IF fnd_log.level_statement >=
2277 fnd_log.g_current_runtime_level
2278 THEN
2279 fnd_log.string(
2280 fnd_log.level_statement,
2281 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2282 'performing awd level validations'
2283 );
2284 END IF;
2285
2286 IF l_fund_type IN ('D', 'F')
2287 THEN -- for direct loan and pell
2288 -- if loan id is not specified then get the loans generated for this awd
2289 OPEN cur_get_loans(awd_disb_rec.award_id, p_loan_id);
2290 FETCH cur_get_loans INTO get_loans_rec;
2291
2292 IF cur_get_loans%NOTFOUND
2293 THEN
2294 l_active_result := FALSE;
2295 CLOSE cur_get_loans;
2296 fnd_message.set_name('IGF', 'IGF_SL_LOAN_ID_NOT_FOUND');
2297 p_result := fnd_message.get;
2298 fnd_file.put_line(
2299 fnd_file.log, RPAD(' ', 17) || p_result
2300 );
2301
2302 -- message ' Loan Not created for this award <Award ID>'
2303 IF fnd_log.level_statement >=
2304 fnd_log.g_current_runtime_level
2305 THEN
2306 fnd_log.string(
2307 fnd_log.level_statement,
2308 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2309 'cur_get_loans%NOTFOUND'
2310 );
2311 END IF;
2312 ELSIF cur_get_loans%FOUND
2313 THEN
2314 CLOSE cur_get_loans;
2315
2316 IF NVL(get_loans_rec.active, 'N') <> 'Y'
2317 THEN
2318 fnd_message.set_name('IGF', 'IGF_SL_LOAN_INACTIVE_DRI');
2319 fnd_message.set_token(
2320 'LOAN_NUMBER', get_loans_rec.loan_number
2321 );
2322 p_result := fnd_message.get;
2323 fnd_file.put_line(
2324 fnd_file.log, RPAD(' ', 17) || p_result
2325 );
2326 l_active_result := FALSE;
2327
2328 IF fnd_log.level_statement >=
2329 fnd_log.g_current_runtime_level
2330 THEN
2331 fnd_log.string(
2332 fnd_log.level_statement,
2333 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2334 'loan ' || get_loans_rec.loan_number
2335 || ' is inactive'
2336 );
2337 END IF;
2338 END IF;
2339
2340 IF get_loans_rec.loan_status = 'S'
2341 OR NVL(get_loans_rec.loan_chg_status, '*') = 'S'
2342 THEN
2343 fnd_message.set_name('IGF', 'IGF_SL_LOAN_SENT_DRI');
2344 fnd_message.set_token('LOAN_NUMBER', get_loans_rec.loan_number);
2345 p_result := fnd_message.get;
2346 fnd_file.put_line(fnd_file.log, RPAD(' ', 17) || p_result);
2347 l_active_result := FALSE;
2348
2349 IF fnd_log.level_statement >=fnd_log.g_current_runtime_level THEN
2350 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','loan ' || get_loans_rec.loan_number
2351 || ' is sent'
2352 );
2353 END IF;
2354 END IF;
2355
2356 IF get_loans_rec.loan_status = 'R'
2357 OR NVL(get_loans_rec.loan_chg_status, '*') = 'R'
2358 THEN
2359 fnd_message.set_name('IGF', 'IGF_SL_LOAN_REJ_DRI'); -- Rejected Loan
2360 fnd_message.set_token(
2361 'LOAN_NUMBER', get_loans_rec.loan_number
2362 );
2363 p_result := fnd_message.get;
2364 fnd_file.put_line(
2365 fnd_file.log, RPAD(' ', 17) || p_result
2366 );
2367 l_active_result := FALSE;
2368
2369 IF fnd_log.level_statement >=
2370 fnd_log.g_current_runtime_level
2371 THEN
2372 fnd_log.string(
2373 fnd_log.level_statement,
2374 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2375 'loan ' || get_loans_rec.loan_number
2376 || ' is rejected'
2377 );
2378 END IF;
2379 END IF;
2380
2381 IF l_fund_type = 'F' THEN
2382 IF get_loans_rec.loan_status = 'A'THEN
2383 IF igf_sl_award.get_loan_cl_version(awd_disb_rec.award_id) <> 'RELEASE-4' THEN
2384 fnd_message.set_name('IGF', 'IGF_SL_LOAN_RELEASE4_DRI'); -- not a release 4 loan
2385 fnd_message.set_token('LOAN_NUMBER', get_loans_rec.loan_number);
2386 p_result := fnd_message.get;
2387 fnd_file.put_line(fnd_file.log, RPAD(' ', 17) || p_result);
2388 l_active_result := FALSE;
2389 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2390 THEN fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug',
2391 'loan ' || get_loans_rec.loan_number || ' is not release-4');
2392 END IF;
2393 END IF; -- release 4
2394 END IF; -- loan status 'A'
2395 END IF; --fund type 'F'
2396 END IF; -- ( cur_get_loans%NOTFOUND)
2397 END IF; -- fund = 'D', 'F'
2398
2399 IF fnd_log.level_statement >=
2400 fnd_log.g_current_runtime_level
2401 THEN
2402 IF NOT l_active_result
2403 THEN
2404 fnd_log.string(
2405 fnd_log.level_statement,
2406 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2407 'loan not active'
2408 );
2409 ELSE
2410 fnd_log.string(
2411 fnd_log.level_statement,
2412 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2413 'loan active'
2414 );
2415 END IF;
2416 END IF;
2417 -- Credit Status
2418 l_crdt_st_check_plus := TRUE;
2419
2420 IF awd_disb_rec.fed_fund_code = 'DLP'
2421 THEN
2422 -- If plus loan then credit status check must be passed
2423
2424 l_crdt_st_check_plus := chk_credit_status(
2425 awd_disb_rec.award_id
2426 );
2427
2428 IF NOT l_crdt_st_check_plus
2429 THEN
2430 fnd_message.set_name(
2431 'IGF', 'IGF_SL_DLP_CRDT_STATUS_FAIL'
2432 );
2433 p_result := fnd_message.get;
2434 fnd_file.put_line(
2435 fnd_file.log, RPAD(' ', 17) || p_result
2436 );
2437
2438 IF fnd_log.level_statement >=
2439 fnd_log.g_current_runtime_level
2440 THEN
2441 fnd_log.string(
2442 fnd_log.level_statement,
2443 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2444 'credit status check failed'
2445 );
2446 END IF;
2447 ELSE
2448 IF fnd_log.level_statement >=
2449 fnd_log.g_current_runtime_level
2450 THEN
2451 fnd_log.string(
2452 fnd_log.level_statement,
2453 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2454 'credit status check passed'
2455 );
2456 END IF;
2457 END IF;
2458 END IF;
2459
2460 IF l_fund_type = 'P'
2461 THEN
2462 OPEN cur_get_pell(awd_disb_rec.award_id);
2463 FETCH cur_get_pell INTO get_pell_rec;
2464 CLOSE cur_get_pell;
2465
2466 IF NVL(get_pell_rec.orig_action_code, '*') = 'S'
2467 THEN
2468 fnd_message.set_name('IGF', 'IGF_SL_PELL_SENT_DRI');
2469 fnd_message.set_token(
2470 'ORIGINATION_ID', get_pell_rec.origination_id
2471 );
2472 p_result := fnd_message.get;
2473 fnd_file.put_line(
2474 fnd_file.log, RPAD(' ', 17) || p_result
2475 );
2476 l_active_result := FALSE;
2477
2478 IF fnd_log.level_statement >=
2479 fnd_log.g_current_runtime_level
2480 THEN
2481 fnd_log.string(
2482 fnd_log.level_statement,
2483 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2484 'pell ' || get_pell_rec.origination_id
2485 || ' is in sent'
2486 );
2487 END IF;
2488 END IF;
2489
2490 IF NVL(get_pell_rec.orig_action_code, '*') = 'E'
2491 THEN -- Rejected Pell
2492 fnd_message.set_name('IGF', 'IGF_SL_PELL_REJ_DRI');
2493 fnd_message.set_token(
2494 'ORIGINATION_ID', get_pell_rec.origination_id
2495 );
2496 p_result := fnd_message.get;
2497 fnd_file.put_line(
2498 fnd_file.log, RPAD(' ', 17) || p_result
2499 );
2500 l_active_result := FALSE;
2501
2502 IF fnd_log.level_statement >=
2503 fnd_log.g_current_runtime_level
2504 THEN
2505 fnd_log.string(
2506 fnd_log.level_statement,
2507 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2508 'pell ' || get_pell_rec.origination_id
2509 || ' is in Rejected'
2510 );
2511 END IF;
2512 END IF;
2513 END IF;
2514 END IF; -- (IF l_old_awd <> l_new_awd)
2515
2516
2517 --
2518 -- Validations
2519 -- Checks that should be done at Term Level
2520 -- This has to be done if the Fund Changes,
2521 -- but terms are same
2522 --
2523 -- 1. Pays Only Fee Class
2524 -- 2. System Hold
2525
2526 l_old_ld_cal := l_new_ld_cal;
2527 l_old_ld_seq := l_new_ld_seq;
2528 l_new_ld_cal := awd_disb_rec.ld_cal_type;
2529 l_new_ld_seq := awd_disb_rec.ld_sequence_number;
2530
2531 IF (l_old_ld_cal <> l_new_ld_cal
2532 AND l_old_ld_seq <> l_new_ld_seq
2533 )
2534 OR (l_old_fund <> l_new_fund)
2535 THEN
2536 l_fclass_result := TRUE;
2537 l_sys_hold_result := TRUE;
2538
2539 IF fnd_log.level_statement >=
2540 fnd_log.g_current_runtime_level
2541 THEN
2542 fnd_log.string(
2543 fnd_log.level_statement,
2544 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2545 'pays only fee class check'
2546 );
2547 fnd_log.string(
2548 fnd_log.level_statement,
2549 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2550 'fund_id' || awd_disb_rec.fund_id
2551 );
2552 fnd_log.string(
2553 fnd_log.level_statement,
2554 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2555 'ld_cal_type' || awd_disb_rec.ld_cal_type
2556 );
2557 fnd_log.string(
2558 fnd_log.level_statement,
2559 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2560 'ld_sequence_number'
2561 || awd_disb_rec.ld_sequence_number
2562 );
2563 END IF;
2564
2565 l_fclass_result := chk_fclass_result(
2566 awd_disb_rec.base_id,
2567 awd_disb_rec.fund_id,
2568 awd_disb_rec.ld_cal_type,
2569 awd_disb_rec.ld_sequence_number
2570 );
2571
2572 IF l_fund_type IN ('D', 'F')
2573 THEN
2574 IF NVL(awd_disb_rec.manual_hold_ind, 'N') = 'Y'
2575 THEN
2576 --Hold exsists
2577 l_sys_hold_result := FALSE;
2578 ELSE
2579 l_sys_hold_result := TRUE;
2580 END IF;
2581 END IF;
2582
2583 IF NOT l_fclass_result
2584 THEN
2585 fnd_message.set_name('IGF', 'IGF_DB_FAIL_FCLS');
2586 --Disbursement failed Pays Only Fee Class Check
2587 p_result := fnd_message.get;
2588 fnd_file.put_line(
2589 fnd_file.log, RPAD(' ', 17) || p_result
2590 );
2591 END IF;
2592
2593 IF NOT l_sys_hold_result
2594 THEN
2595 fnd_message.set_name('IGF', 'IGF_SL_SYS_HOLD_FAIL');
2596 --System hold exsist on the disbursment
2597 p_result := fnd_message.get;
2598 fnd_file.put_line(
2599 fnd_file.log, RPAD(' ', 17) || p_result
2600 );
2601 END IF;
2602
2603 IF fnd_log.level_statement >=
2604 fnd_log.g_current_runtime_level
2605 THEN
2606 IF NOT l_fclass_result
2607 THEN
2608 fnd_log.string(
2609 fnd_log.level_statement,
2610 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2611 'failed pays only fees class check'
2612 );
2613 ELSE
2614 fnd_log.string(
2615 fnd_log.level_statement,
2616 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2617 'passed pays only fees class check'
2618 );
2619 END IF;
2620
2621 IF NOT l_sys_hold_result
2622 THEN
2623 fnd_log.string(
2624 fnd_log.level_statement,
2625 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2626 'system hold exsist on the disbursment'
2627 );
2628 ELSE
2629 fnd_log.string(
2630 fnd_log.level_statement,
2631 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2632 'system hold do not exsist on the disbursment'
2633 );
2634 END IF;
2635 END IF;
2636 END IF; -- OLD <> NEW
2637
2638
2639 -- Validations to be performed at disbursment level
2640 -- 1. Cumulative Current Credit Points
2641 -- 2. Min Att Type if specified
2642 -- 3. Funding method
2643 -- 4. Credit status for PLUS- Direct loans
2644
2645 -- Min Credit Points
2646
2647 l_crdt_pt_result := TRUE;
2648
2649 IF awd_disb_rec.min_credit_pts IS NOT NULL
2650 THEN
2651 IF fnd_log.level_statement >=
2652 fnd_log.g_current_runtime_level
2653 THEN
2654 fnd_log.string(
2655 fnd_log.level_statement,
2656 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2657 'awd_disb_rec.min_credit_pts:'
2658 || awd_disb_rec.min_credit_pts
2659 );
2660 END IF;
2661
2662 igs_en_prc_load.enrp_clc_cp_upto_tp_start_dt(
2663 igf_gr_gen.get_person_id(awd_disb_rec.base_id),
2664 awd_disb_rec.ld_cal_type, awd_disb_rec.ld_sequence_number,
2665 'Y', get_cut_off_dt(
2666 awd_disb_rec.ld_sequence_number,
2667 awd_disb_rec.disb_date
2668 ), l_credit_pts
2669 );
2670
2671 IF fnd_log.level_statement >=
2672 fnd_log.g_current_runtime_level
2673 THEN
2674 fnd_log.string(
2675 fnd_log.level_statement,
2676 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2677 'l_credit_pts:' || l_credit_pts
2678 );
2679 END IF;
2680
2681 IF NVL(l_credit_pts, 0) <
2682 NVL(awd_disb_rec.min_credit_pts, 0)
2683 THEN
2684 l_crdt_pt_result := FALSE;
2685 fnd_message.set_name('IGF', 'IGF_DB_FAIL_CRP');
2686 p_result := fnd_message.get;
2687 fnd_file.put_line(
2688 fnd_file.log, RPAD(' ', 17) || p_result
2689 );
2690
2691 IF fnd_log.level_statement >=
2692 fnd_log.g_current_runtime_level
2693 THEN
2694 fnd_log.string(
2695 fnd_log.level_statement,
2696 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2697 'min credit check failed'
2698 );
2699 END IF;
2700 ELSE
2701 IF fnd_log.level_statement >=
2702 fnd_log.g_current_runtime_level
2703 THEN
2704 fnd_log.string(
2705 fnd_log.level_statement,
2706 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2707 'min credit check passed'
2708 );
2709 END IF;
2710 END IF;
2711 ELSE -- (awd_disb_rec.min_credit_pts IS NOT NULL)
2712 IF fnd_log.level_statement >=
2713 fnd_log.g_current_runtime_level
2714 THEN
2715 fnd_log.string(
2716 fnd_log.level_statement,
2717 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2718 'awd_disb_rec.min_credit_pts is NULL'
2719 );
2720 END IF;
2721 END IF; -- (IF awd_disb_rec.min_credit_pts IS NOT NULL )
2722
2723 -- Attendance type
2724 l_att_result := TRUE;
2725
2726 IF awd_disb_rec.attendance_type_code IS NOT NULL
2727 THEN
2728 IF fnd_log.level_statement >=
2729 fnd_log.g_current_runtime_level
2730 THEN
2731 fnd_log.string(
2732 fnd_log.level_statement,
2733 'igf.plsql.igf_db_disb.process_student.debug',
2734 'awd_disb_rec.attendance_type_code:'
2735 || awd_disb_rec.attendance_type_code
2736 );
2737 END IF;
2738
2739 p_message := NULL;
2740 l_att_result := chk_attendance(
2741 awd_disb_rec.base_id,
2742 awd_disb_rec.ld_cal_type,
2743 awd_disb_rec.ld_sequence_number,
2744 awd_disb_rec.attendance_type_code,
2745 p_message
2746 );
2747 END IF;
2748
2749 IF NOT l_att_result
2750 THEN
2751 fnd_message.set_name('IGF', 'IGF_DB_FAIL_ATT');
2752 --Disbursement failed Attendance Type Check
2753 p_result := fnd_message.get;
2754
2755 IF p_message IS NOT NULL
2756 THEN
2757 IF fnd_log.level_statement >=
2758 fnd_log.g_current_runtime_level
2759 THEN
2760 fnd_log.string(
2761 fnd_log.level_statement,
2762 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2763 'message = ' || p_message
2764 );
2765 END IF;
2766
2767 --p_result := p_message ||fnd_global.newline ||p_result;
2768 p_result := p_message || ' ' || p_result;
2769 END IF;
2770
2771 fnd_file.put_line(fnd_file.log, RPAD(' ', 17) || p_result);
2772
2773 IF fnd_log.level_statement >=
2774 fnd_log.g_current_runtime_level
2775 THEN
2776 fnd_log.string(
2777 fnd_log.level_statement,
2778 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2779 'min attendance type check failed'
2780 );
2781 END IF;
2782 ELSE
2783 IF fnd_log.level_statement >=
2784 fnd_log.g_current_runtime_level
2785 THEN
2786 fnd_log.string(
2787 fnd_log.level_statement,
2788 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2789 'min attendance type check passed'
2790 );
2791 END IF;
2792 END IF;
2793
2794 -- Funding method
2795
2796 IF l_fund_type = 'D'
2797 THEN -- funding method check for direct loans
2798 l_fund_meth_result := chk_fund_meth_dl(
2799 awd_disb_rec.ci_cal_type,
2800 awd_disb_rec.ci_sequence_number,
2801 awd_disb_rec.award_id,
2802 awd_disb_rec.disb_num
2803 );
2804
2805 IF l_fund_meth_result IS NULL
2806 THEN
2807 -- null is returned if the funding type is incorrect
2808 IF fnd_log.level_statement >=
2809 fnd_log.g_current_runtime_level
2810 THEN
2811 fnd_log.string(
2812 fnd_log.level_statement,
2813 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2814 'result of funding method check is NULL'
2815 );
2816 END IF;
2817
2818 fnd_message.set_name('IGF', 'IGF_SL_FUND_METH_NOT_CORR');
2819 p_result := fnd_message.get;
2820 fnd_file.put_line(fnd_file.log, RPAD(' ', 17) || p_result);
2821 RAISE skip_record;
2822
2823 IF fnd_log.level_statement >=
2824 fnd_log.g_current_runtime_level
2825 THEN
2826 fnd_log.string(
2827 fnd_log.level_statement,
2828 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2829 'error in funding method'
2830 );
2831 END IF;
2832 ELSIF NOT l_fund_meth_result
2833 THEN
2834 -- funding method check failed so DRI can't be set to true
2835
2836 fnd_message.set_name('IGF', 'IGF_SL_FUND_METH_CHK_FAIL');
2837 p_result := fnd_message.get;
2838 fnd_file.put_line(
2839 fnd_file.log, RPAD(' ', 17) || p_result
2840 );
2841
2842 IF fnd_log.level_statement >=
2843 fnd_log.g_current_runtime_level
2844 THEN
2845 fnd_log.string(
2846 fnd_log.level_statement,
2847 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2848 'funding method check failed'
2849 );
2850 END IF;
2851 ELSE
2852 IF fnd_log.level_statement >=
2853 fnd_log.g_current_runtime_level
2854 THEN
2855 fnd_log.string(
2856 fnd_log.level_statement,
2857 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2858 'funding method check passed'
2859 );
2860 END IF;
2861 END IF;
2862 ELSIF l_fund_type = 'P'
2863 THEN -- funding method check for pell
2864 l_fund_meth_result := chk_fund_meth_pell(
2865 p_base_id,
2866 awd_disb_rec.ci_cal_type,
2867 awd_disb_rec.ci_sequence_number,
2868 awd_disb_rec.award_id,
2869 awd_disb_rec.disb_num, l_status,
2870 p_message
2871 );
2872
2873 IF l_fund_meth_result IS NULL
2874 THEN
2875 IF l_status = 'E'
2876 THEN
2877 -- error message is returned by the method
2878 p_result := p_message;
2879 fnd_file.put_line(
2880 fnd_file.log, RPAD(' ', 17) || p_result
2881 );
2882 RAISE skip_record;
2883
2884 IF fnd_log.level_statement >=
2885 fnd_log.g_current_runtime_level
2886 THEN
2887 fnd_log.string(
2888 fnd_log.level_statement,
2889 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2890 'error returned by function chk_fund_meth_pell'
2891 );
2892 END IF;
2893 ELSE
2894 -- null is returned if the funding type is incorrect
2895 fnd_message.set_name(
2896 'IGF', 'IGF_SL_FUND_METH_NOT_CORR'
2897 );
2898 p_result := fnd_message.get;
2899 fnd_file.put_line(
2900 fnd_file.log, RPAD(' ', 17) || p_result
2901 );
2902 RAISE skip_record;
2903
2904 IF fnd_log.level_statement >=
2905 fnd_log.g_current_runtime_level
2906 THEN
2907 fnd_log.string(
2908 fnd_log.level_statement,
2909 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2910 'error in funding method'
2911 );
2912 END IF;
2913 END IF;
2914 ELSIF NOT l_fund_meth_result
2915 THEN
2916 -- funding method check failed so DRI can't be set to true
2917
2918 fnd_message.set_name('IGF', 'IGF_SL_FUND_METH_CHK_FAIL');
2919 p_result := fnd_message.get;
2920 fnd_file.put_line(
2921 fnd_file.log, RPAD(' ', 17) || p_result
2922 );
2923
2924 IF fnd_log.level_statement >=
2925 fnd_log.g_current_runtime_level
2926 THEN
2927 fnd_log.string(
2928 fnd_log.level_statement,
2929 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2930 'funding method check failed'
2931 );
2932 END IF;
2933 ELSE
2934 IF fnd_log.level_statement >=
2935 fnd_log.g_current_runtime_level
2936 THEN
2937 fnd_log.string(
2938 fnd_log.level_statement,
2939 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2940 'funding method check passed'
2941 );
2942 END IF;
2943 END IF;
2944 END IF;
2945
2946
2947 --
2948 -- Based on these results, set DRI to true
2949 --
2950 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2951 THEN
2952 fnd_log.string(
2953 fnd_log.level_statement,
2954 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2955 'checking the result of all eligibilty checks'
2956 );
2957 fnd_log.string(
2958 fnd_log.level_statement,
2959 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2960 'fed_fund_code' || awd_disb_rec.fed_fund_code
2961 );
2962
2963 IF NOT l_pays_prg
2964 THEN
2965 fnd_log.string(
2966 fnd_log.level_statement,
2967 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2968 'l_pays_prg passed'
2969 );
2970 END IF;
2971
2972 IF NOT l_fclass_result
2973 THEN
2974 fnd_log.string(
2975 fnd_log.level_statement,
2976 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2977 'l_fclass_result'
2978 );
2979 END IF;
2980
2981 IF NOT l_pays_uts
2982 THEN
2983 fnd_log.string(
2984 fnd_log.level_statement,
2985 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2986 'l_pays_uts'
2987 );
2988 END IF;
2989
2990 IF NOT l_att_result
2991 THEN
2992 fnd_log.string(
2993 fnd_log.level_statement,
2994 'igf.plsql.igf_sl_rel_disb.process_student.debug',
2995 'l_att_result'
2996 );
2997 END IF;
2998
2999 IF NOT l_active_result
3000 THEN
3001 fnd_log.string(
3002 fnd_log.level_statement,
3003 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3004 'l_active_result'
3005 );
3006 END IF;
3007
3008 IF NOT l_sys_hold_result
3009 THEN
3010 fnd_log.string(
3011 fnd_log.level_statement,
3012 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3013 'l_sys_hold_result'
3014 );
3015 END IF;
3016
3017 IF NOT l_ac_hold_result
3018 THEN
3019 fnd_log.string(
3020 fnd_log.level_statement,
3021 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3022 'l_ac_hold_result'
3023 );
3024 END IF;
3025
3026 IF NOT l_todo_result
3027 THEN
3028 fnd_log.string(
3029 fnd_log.level_statement,
3030 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3031 'l_elig_result'
3032 );
3033 END IF;
3034
3035 IF NOT l_fund_meth_result
3036 THEN
3037 fnd_log.string(
3038 fnd_log.level_statement,
3039 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3040 'l_fund_meth_result'
3041 );
3042 END IF;
3043
3044 IF NOT l_crdt_st_check_plus
3045 THEN
3046 fnd_log.string(
3047 fnd_log.level_statement,
3048 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3049 'l_crdt_st_check_PLUS '
3050 );
3051 END IF;
3052 END IF;
3053
3054 IF awd_disb_rec.fed_fund_code = 'DLP'
3055 THEN
3056 IF l_pays_prg AND l_fclass_result AND l_pays_uts
3057 AND l_att_result AND l_crdt_pt_result
3058 AND l_active_result AND l_sys_hold_result
3059 AND l_ac_hold_result AND l_elig_result AND l_todo_result
3060 AND l_fund_meth_result AND l_crdt_st_check_plus
3061 THEN
3062 set_dri_true(
3063 awd_disb_rec.award_id, awd_disb_rec.disb_num,
3064 awd_disb_rec.fund_id, l_fund_type
3065 );
3066
3067 IF fnd_log.level_statement >=
3068 fnd_log.g_current_runtime_level
3069 THEN
3070 fnd_log.string(
3071 fnd_log.level_statement,
3072 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3073 'called set_dri_true with fund_id '
3074 || awd_disb_rec.fund_id
3075 );
3076 END IF;
3077 END IF;
3078 ELSIF l_fund_type IN ('D', 'P')
3079 THEN
3080 IF l_pays_prg AND l_fclass_result AND l_pays_uts
3081 AND l_att_result AND l_crdt_pt_result
3082 AND l_active_result AND l_sys_hold_result
3083 AND l_ac_hold_result AND l_elig_result AND l_todo_result
3084 AND l_fund_meth_result
3085 THEN
3086 set_dri_true(
3087 awd_disb_rec.award_id, awd_disb_rec.disb_num,
3088 awd_disb_rec.fund_id, l_fund_type
3089 );
3090
3091 IF fnd_log.level_statement >=
3092 fnd_log.g_current_runtime_level
3093 THEN
3094 fnd_log.string(
3095 fnd_log.level_statement,
3096 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3097 'called set_dri_true with fund_id '
3098 || awd_disb_rec.fund_id
3099 );
3100 END IF;
3101 END IF;
3102 ELSIF l_fund_type = 'F'
3103 THEN
3104 IF l_pays_prg AND l_fclass_result AND l_pays_uts
3105 AND l_att_result AND l_crdt_pt_result
3106 AND l_active_result AND l_sys_hold_result
3107 AND l_ac_hold_result AND l_elig_result AND l_todo_result
3108 THEN
3109 set_dri_true(
3110 awd_disb_rec.award_id, awd_disb_rec.disb_num,
3111 awd_disb_rec.fund_id, l_fund_type
3112 );
3113
3114 IF fnd_log.level_statement >=
3115 fnd_log.g_current_runtime_level
3116 THEN
3117 fnd_log.string(
3118 fnd_log.level_statement,
3119 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3120 'called set_dri_true with fund_id '
3121 || awd_disb_rec.fund_id
3122 );
3123 END IF;
3124 END IF;
3125 END IF;
3126
3127 IF l_fund_type IN ('D', 'P')
3128 THEN
3129 FETCH cur_awd_disb_dl INTO awd_disb_rec;
3130 EXIT WHEN cur_awd_disb_dl%NOTFOUND;
3131
3132 IF fnd_log.level_statement >=
3133 fnd_log.g_current_runtime_level
3134 THEN
3135 fnd_log.string(
3136 fnd_log.level_statement,
3137 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3138 'exiting with fund type' || l_fund_type
3139 );
3140 END IF;
3141 ELSIF l_fund_type = 'F' AND p_trans_type IS NOT NULL
3142 THEN
3143 FETCH cur_awd_disb_fed INTO awd_disb_rec;
3144 EXIT WHEN cur_awd_disb_fed%NOTFOUND;
3145
3146 IF fnd_log.level_statement >=
3147 fnd_log.g_current_runtime_level
3148 THEN
3149 fnd_log.string(
3150 fnd_log.level_statement,
3151 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3152 'exiting with fund type' || l_fund_type
3153 );
3154 END IF;
3155 ELSIF l_fund_type = 'F' AND p_trans_type IS NULL
3156 THEN
3157 FETCH cur_awd_disb_fed_no_trans INTO awd_disb_rec;
3158 EXIT WHEN cur_awd_disb_fed_no_trans%NOTFOUND;
3159
3160 IF fnd_log.level_statement >=
3161 fnd_log.g_current_runtime_level
3162 THEN
3163 fnd_log.string(
3164 fnd_log.level_statement,
3165 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3166 'exiting with fund type' || l_fund_type
3167 );
3168 END IF;
3169 ELSE
3170 IF fnd_log.level_statement >=
3171 fnd_log.g_current_runtime_level
3172 THEN
3173 fnd_log.string(
3174 fnd_log.level_statement,
3175 'igf.plsql.igf_sl_rel_disb.process_student.debug',
3176 'exiting with fund type' || l_fund_type
3177 );
3178 END IF;
3179
3180 EXIT;
3181 END IF;
3182 EXCEPTION
3183 WHEN app_exception.record_lock_exception
3184 THEN
3185 RAISE;
3186 WHEN skip_record
3187 THEN
3188 -- clear message stack
3189 fnd_message.CLEAR;
3190
3191 IF l_fund_type IN ('D', 'P')
3192 THEN
3193 FETCH cur_awd_disb_dl INTO awd_disb_rec;
3194 EXIT WHEN cur_awd_disb_dl%NOTFOUND;
3195 ELSIF l_fund_type = 'F' AND p_trans_type IS NOT NULL
3196 THEN
3197 FETCH cur_awd_disb_fed INTO awd_disb_rec;
3198 EXIT WHEN cur_awd_disb_fed%NOTFOUND;
3199 ELSIF l_fund_type = 'F' AND p_trans_type IS NULL
3200 THEN
3201 FETCH cur_awd_disb_fed_no_trans INTO awd_disb_rec;
3202 EXIT WHEN cur_awd_disb_fed_no_trans%NOTFOUND;
3203 ELSE
3204 EXIT;
3205 END IF;
3206 END;
3207 END LOOP; -- (-- FOR all the records IN CUR_AWD_DISB)
3208
3209 -- Close the cursors
3210 IF cur_awd_disb_dl%ISOPEN
3211 THEN
3212 CLOSE cur_awd_disb_dl;
3213 END IF;
3214
3215 IF cur_awd_disb_fed%ISOPEN
3216 THEN
3217 CLOSE cur_awd_disb_fed;
3218 END IF;
3219
3220 IF cur_awd_disb_fed_no_trans%ISOPEN
3221 THEN
3222 CLOSE cur_awd_disb_fed_no_trans;
3223 END IF;
3224 END IF; --(IF cur_awd_disb%NOTFOUND)
3225 EXCEPTION
3226 WHEN app_exception.record_lock_exception
3227 THEN
3228 RAISE;
3229 WHEN OTHERS
3230 THEN
3231 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
3232 fnd_message.set_token(
3233 'NAME', 'IGF_DB_DISB.PROCESS_STUDENT' || SQLERRM
3234 );
3235 fnd_file.put_line(fnd_file.log, SQLERRM);
3236 igs_ge_msg_stack.ADD;
3237 app_exception.raise_exception;
3238 END process_student;
3239
3240
3241 --------------------------------------------------------------------------------------------------------------------------------
3242 -----------------------------------------------------------------------------------
3243 -- Know limitations, enhancements or remarks
3244 -- Change History:
3245 -----------------------------------------------------------------------------------
3246 -- Who When What
3247 --tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
3248 -----------------------------------------------------------------------------------
3249 PROCEDURE rel_disb_process_dl(
3250 errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER,
3251 p_award_year IN VARCHAR2, p_pell_dummy IN VARCHAR2,
3252 p_dl_dummy IN VARCHAR2, p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
3253 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE, p_per_dummy IN NUMBER,
3254 p_loan_id IN NUMBER, p_loan_dummy IN NUMBER, p_per_grp_id IN NUMBER,
3255 p_trans_type IN igf_aw_awd_disb_all.trans_type%TYPE
3256 )
3257
3258 ------------------------------------------------------------------------------------------------------------------------------------
3259 --
3260 -- This process would be called from concurrent mananger for Direct Loans
3261 -- This process, depending on the input parameters passed
3262 -- will call the main process ie process_student()
3263
3264 ------------------------------------------------------------------------------------------------------------------------------------
3265 IS
3266 BEGIN
3267 igf_aw_gen.set_org_id(NULL);
3268 rel_disb_process(
3269 errbuf, retcode, p_award_year, p_fund_id, p_base_id, p_loan_id, p_trans_type,
3270 p_per_grp_id
3271 );
3272 END rel_disb_process_dl;
3273
3274
3275 ------------------------------------------------------------------------------------------------------------------------------------
3276
3277 --------------------------------------------------------------------------------------------------------------------------------
3278
3279 -----------------------------------------------------------------------------------
3280 -- Know limitations, enhancements or remarks
3281 -- Change History:
3282 -----------------------------------------------------------------------------------
3283 -- Who When What
3284 --tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
3285 -----------------------------------------------------------------------------------
3286 PROCEDURE rel_disb_process_fed(
3287 errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER,
3288 p_award_year IN VARCHAR2,
3289 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
3290 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE, p_per_dummy IN NUMBER,
3291 p_loan_id IN NUMBER, p_loan_dummy IN NUMBER, p_trans_type IN VARCHAR2,
3292 p_per_grp_id IN NUMBER
3293 )
3294
3295 ------------------------------------------------------------------------------------------------------------------------------------
3296 --
3297 -- This process would be called from concurrent mananger for Direct Loans
3298 -- This process, depending on the input parameters passed
3299 -- will call the main process ie process_student()
3300
3301 ------------------------------------------------------------------------------------------------------------------------------------
3302 IS
3303 BEGIN
3304 igf_aw_gen.set_org_id(NULL);
3305 rel_disb_process(
3306 errbuf, retcode, p_award_year, p_fund_id, p_base_id, p_loan_id,
3307 p_trans_type, p_per_grp_id
3308 );
3309 END rel_disb_process_fed;
3310
3311
3312 ------------------------------------------------------------------------------------------------------------------------------------
3313
3314
3315
3316 PROCEDURE rel_disb_process(
3317 p_errbuf OUT NOCOPY VARCHAR2, p_retcode OUT NOCOPY NUMBER,
3318 p_award_year IN VARCHAR2,
3319 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
3320 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE, p_loan_id IN NUMBER,
3321 p_trans_type IN VARCHAR2, p_per_grp_id IN NUMBER
3322 )
3323 IS
3324 param_exception EXCEPTION;
3325 l_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
3326 l_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
3327 l_list VARCHAR2(32767);
3328 l_status VARCHAR2(1);
3329 l_base_id NUMBER;
3330 l_person_number VARCHAR2(30);
3331 l_result VARCHAR2(4000) := NULL;
3332
3333 TYPE cur_person_id_type IS REF CURSOR;
3334
3335 cur_per_grp cur_person_id_type;
3336 l_person_id hz_parties.party_id%TYPE;
3337
3338
3339 -- Cursor to get alternate code for the calendar instance
3340 CURSOR cur_awdyear(cp_cal_type VARCHAR2, cp_seq_number NUMBER)
3341 IS
3342 SELECT alternate_code
3343 FROM igs_ca_inst_all
3344 WHERE cal_type = cp_cal_type AND sequence_number = cp_seq_number;
3345
3346 awdyear_rec cur_awdyear%ROWTYPE;
3347
3348
3349 -- Cursor to retreive Persons from Person Group
3350 -- The code can handle dynamic person id groups
3351 CURSOR cur_per_grp_name(
3352 p_per_grp_id igs_pe_persid_group_all.GROUP_ID%TYPE
3353 )
3354 IS
3355 SELECT group_cd --Code of a person ID group.
3356 --A person ID group also has a unique system generated sequencenumber or group ID
3357 FROM igs_pe_persid_group
3358 WHERE GROUP_ID = p_per_grp_id;
3359
3360 per_grp_rec cur_per_grp_name%ROWTYPE;
3361
3362 -- Cursor to retreive Student having awards for a given fund
3363 CURSOR cur_award_std(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
3364 IS
3365 SELECT DISTINCT awd.base_id base_id, fcat.fed_fund_code
3366 fed_fund_code
3367 FROM igf_aw_award awd, igf_aw_fund_mast fmast,
3368 igf_aw_fund_cat fcat
3369 WHERE awd.fund_id = p_fund_id
3370 AND awd.fund_id = fmast.fund_id
3371 AND fmast.fund_code = fcat.fund_code
3372 AND awd.award_status = 'ACCEPTED';
3373
3374 award_std_rec cur_award_std%ROWTYPE;
3375
3376
3377 -- Cursor to get award_id from a loan_id
3378
3379 CURSOR cur_get_awd_id(p_loan_id igf_sl_loans_all.loan_id%TYPE)
3380 IS
3381 SELECT award_id
3382 FROM igf_sl_loans_all
3383 WHERE loan_id = p_loan_id;
3384
3385 get_awd_id_rec cur_get_awd_id%ROWTYPE;
3386
3387 -- Get the fund type
3388 CURSOR cur_get_fund_type(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
3389 IS
3390 SELECT cat.fed_fund_code fed_fund_code
3391 FROM igf_aw_fund_cat_all cat, igf_aw_fund_mast_all mast
3392 WHERE mast.fund_code = cat.fund_code AND mast.fund_id = p_fund_id;
3393
3394 get_fund_type_rec cur_get_fund_type%ROWTYPE;
3395 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
3396
3397 BEGIN
3398 p_errbuf := NULL;
3399 p_retcode := 0;
3400 l_ci_cal_type := RTRIM(SUBSTR(p_award_year, 1, 10));
3401 l_ci_sequence_number := RTRIM(SUBSTR(p_award_year, 11));
3402
3403 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3404 THEN
3405 fnd_log.string(
3406 fnd_log.level_statement,
3407 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3408 'l_ci_cal_type:' || l_ci_cal_type
3409 );
3410 fnd_log.string(
3411 fnd_log.level_statement,
3412 'igf.plsql.igf_sl_rel_disb.rel_rel_disb_process.debug',
3413 'l_ci_sequence_number:' || l_ci_sequence_number
3414 );
3415 fnd_log.string(
3416 fnd_log.level_statement,
3417 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug', 'p_fund_id:'
3418 || p_fund_id
3419 );
3420 fnd_log.string(
3421 fnd_log.level_statement,
3422 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug', 'p_base_id:'
3423 || p_base_id
3424 );
3425 fnd_log.string(
3426 fnd_log.level_statement,
3427 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug', 'p_loan_id:'
3428 || p_loan_id
3429 );
3430 fnd_log.string(
3431 fnd_log.level_statement,
3432 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3433 'p_trans_type:' || p_trans_type
3434 );
3435 fnd_log.string(
3436 fnd_log.level_statement,
3437 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3438 'p_per_grp_id:' || p_per_grp_id
3439 );
3440 END IF;
3441
3442 --- Print all the parameters in log file
3443 OPEN cur_awdyear(l_ci_cal_type, l_ci_sequence_number);
3444 FETCH cur_awdyear INTO awdyear_rec;
3445
3446 IF cur_awdyear%NOTFOUND
3447 THEN
3448 fnd_message.set_name('IGF', 'IGF_SL_AWD_YR_NOT_FOUND');
3449 fnd_file.put_line(fnd_file.log, fnd_message.get);
3450 fnd_file.new_line(fnd_file.log, 1);
3451 CLOSE cur_awdyear;
3452 RETURN;
3453 ELSE
3454 CLOSE cur_awdyear;
3455 END IF;
3456
3457 OPEN cur_get_fund_type(p_fund_id);
3458 FETCH cur_get_fund_type INTO get_fund_type_rec;
3459
3460 IF cur_get_fund_type%NOTFOUND
3461 THEN
3462 fnd_message.set_name('IGF', 'IGF_AW_NO_SUCH_FUND');
3463 fnd_message.set_token('FUND_ID', p_fund_id);
3464 fnd_file.put_line(fnd_file.log, fnd_message.get);
3465 fnd_file.new_line(fnd_file.log, 1);
3466 CLOSE cur_get_fund_type;
3467 RETURN;
3468 ELSE
3469 CLOSE cur_get_fund_type;
3470 END IF;
3471
3472 log_parameters(
3473 awdyear_rec.alternate_code, p_fund_id, p_base_id, p_loan_id,
3474 p_trans_type, p_per_grp_id, get_fund_type_rec.fed_fund_code
3475 );
3476
3477 -- Check for valid input combinations of Parameters
3478
3479 -- Award year and Fund Id cannot be NULL
3480
3481 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL
3482 OR p_fund_id IS NULL
3483 THEN
3484 fnd_message.set_name('IGF', 'IGF_SL_REL_DISB_PARAM_EX');
3485 fnd_file.new_line(fnd_file.log, 2);
3486 fnd_file.put_line(fnd_file.log, fnd_message.get);
3487 fnd_file.new_line(fnd_file.log, 2);
3488 RAISE param_exception;
3489 END IF;
3490
3491 -- Person ID Group would not pick up any value in case the Person Number or Loan Number is populated
3492
3493 IF p_per_grp_id IS NOT NULL
3494 THEN
3495 -- If Person ID Group is specified then cannot specify values for Person Number or Loan number
3496 IF p_base_id IS NOT NULL OR p_loan_id IS NOT NULL
3497 THEN
3498 fnd_message.set_name('IGF', 'IGF_SL_REL_DISB_PARAM_EX');
3499 fnd_file.new_line(fnd_file.log, 2);
3500 fnd_file.put_line(fnd_file.log, fnd_message.get);
3501 fnd_file.new_line(fnd_file.log, 2);
3502 RAISE param_exception;
3503 END IF;
3504 END IF;
3505
3506 IF p_per_grp_id IS NOT NULL
3507 THEN
3508 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3509 THEN
3510 fnd_log.string(
3511 fnd_log.level_statement,
3512 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3513 'Starting to process person group ' || p_per_grp_id
3514 );
3515 END IF;
3516
3517 --- If person id group is specified then Get all the persons in person group
3518 --Bug #5021084
3519 l_list := igf_ap_ss_pkg.get_pid(p_per_grp_id, l_status, lv_group_type);
3520
3521 --Bug #5021084. Passing Group ID if the group type is STATIC.
3522 IF lv_group_type = 'STATIC' THEN
3523 OPEN cur_per_grp FOR ' SELECT party_id FROM hz_parties WHERE party_id IN ('
3524 || l_list || ') ' USING p_per_grp_id;
3525 ELSIF lv_group_type = 'DYNAMIC' THEN
3526 OPEN cur_per_grp FOR ' SELECT party_id FROM hz_parties WHERE party_id IN ('
3527 || l_list || ') ';
3528 END IF;
3529
3530 FETCH cur_per_grp INTO l_person_id;
3531
3532 -- If no student found in Person Group
3533 IF cur_per_grp%NOTFOUND
3534 THEN
3535 CLOSE cur_per_grp;
3536 fnd_message.set_name('IGF', 'IGF_DB_NO_PER_GRP');
3537 fnd_file.put_line(fnd_file.log, RPAD(' ', 5) || fnd_message.get);
3538
3539 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3540 THEN
3541 fnd_log.string(
3542 fnd_log.level_statement,
3543 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3544 'No persons in group ' || p_per_grp_id
3545 );
3546 END IF;
3547 ELSE
3548 -- IF cur_per_grp%FOUND THEN
3549 fnd_message.set_name('IGF', 'IGF_DB_PROCESS_PER_GRP');
3550 -- Processing Disbursements for Person Group
3551 OPEN cur_per_grp_name(p_per_grp_id);
3552 FETCH cur_per_grp_name INTO per_grp_rec;
3553 CLOSE cur_per_grp_name;
3554 fnd_file.put_line(
3555 fnd_file.log, RPAD(' ', 5) || fnd_message.get || ' '
3556 || per_grp_rec.group_cd
3557 );
3558
3559 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3560 THEN
3561 fnd_log.string(
3562 fnd_log.level_statement,
3563 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3564 'Processing for ' || p_per_grp_id
3565 );
3566 END IF;
3567
3568 -- Check if the person exists in FA.
3569
3570 LOOP
3571 l_base_id := 0;
3572 l_person_number := NULL;
3573 l_person_number := per_in_fa(
3574 l_person_id, l_ci_cal_type,
3575 l_ci_sequence_number, l_base_id
3576 );
3577
3578 IF l_person_number IS NOT NULL
3579 THEN
3580 IF l_base_id IS NOT NULL
3581 THEN
3582 fnd_message.set_name('IGF', 'IGF_AW_PROC_STUD');
3583 fnd_message.set_token('STDNT', l_person_number);
3584 fnd_file.put_line(
3585 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3586 );
3587
3588 IF fnd_log.level_statement >=
3589 fnd_log.g_current_runtime_level
3590 THEN
3591 fnd_log.string(
3592 fnd_log.level_statement,
3593 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3594 'Processing student ' || l_person_number
3595 );
3596 END IF;
3597
3598 --
3599 -- Check for Academic Holds, only if con job is run
3600 --
3601 IF igf_aw_gen_005.get_stud_hold_effect(
3602 'D', igf_gr_gen.get_person_id(l_base_id)
3603 ) = 'F'
3604 THEN
3605 fnd_message.set_name(
3606 'IGF', 'IGF_SL_DISB_FUND_HOLD_FAIL'
3607 );
3608 fnd_file.put_line(
3609 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3610 );
3611
3612 IF fnd_log.level_statement >=
3613 fnd_log.g_current_runtime_level
3614 THEN
3615 fnd_log.string(
3616 fnd_log.level_statement,
3617 'igf.plsql.igf_sl_rel_disb.disb_process.debug',
3618 'get_stud_hold_effect returned F'
3619 );
3620 END IF;
3621 ELSE
3622 IF fnd_log.level_statement >=
3623 fnd_log.g_current_runtime_level
3624 THEN
3625 fnd_log.string(
3626 fnd_log.level_statement,
3627 'igf.plsql.igf_sl_rel_disb.disb_process.debug',
3628 'calling process_student for base_id 1'
3629 || l_base_id
3630 );
3631 END IF;
3632
3633 process_student(
3634 p_base_id => l_base_id, p_result => l_result,
3635 p_fund_id => p_fund_id, p_award_id => NULL,
3636 p_loan_id => NULL, p_disb_num => NULL,
3637 p_trans_type => p_trans_type
3638 );
3639 END IF;
3640
3641 fnd_message.set_name('IGF', 'IGF_DB_END_PROC_PER'); -- if hold exsist
3642 -- End of processing for person number
3643 fnd_message.set_token('PER_NUM', l_person_number);
3644 fnd_file.put_line(
3645 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3646 );
3647 fnd_file.new_line(fnd_file.log, 1);
3648
3649 IF fnd_log.level_statement >=
3650 fnd_log.g_current_runtime_level
3651 THEN
3652 fnd_log.string(
3653 fnd_log.level_statement,
3654 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3655 'end processing ' || l_person_number
3656 );
3657 END IF;
3658 ELSE -- l_base_id IS NULL THEN
3659 -- log a message and skip this person since the person doesnt exsist in FA
3660 fnd_message.set_name('IGF', 'IGF_GR_LI_PER_INVALID');
3661 fnd_message.set_token('PERSON_NUMBER', l_person_number);
3662 fnd_message.set_token(
3663 'AWD_YR', awdyear_rec.alternate_code
3664 );
3665 -- Person PER_NUM does not exist in FA
3666 fnd_file.put_line(
3667 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3668 );
3669
3670 IF fnd_log.level_statement >=
3671 fnd_log.g_current_runtime_level
3672 THEN
3673 fnd_log.string(
3674 fnd_log.level_statement,
3675 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3676 igf_gr_gen.get_per_num_oss(l_person_id)
3677 || ' not in FA'
3678 );
3679 END IF;
3680 END IF; -- (IF l_base_id IS NOT NULL)
3681 ELSE -- IF l_person_number IS NULL THEN
3682 fnd_message.set_name('IGF', 'IGF_AP_PE_NOT_EXIST');
3683 fnd_file.put_line(
3684 fnd_file.log, RPAD(' ', 5) || fnd_message.get
3685 );
3686 END IF; ---- (IF l_person_number IS NOT NULL)
3687
3688 FETCH cur_per_grp INTO l_person_id;
3689 EXIT WHEN cur_per_grp%NOTFOUND;
3690 END LOOP;
3691
3692 CLOSE cur_per_grp;
3693 END IF; -- (IF cur_per_grp%NOTFOUND)
3694 ELSE -- IF p_per_grp_id IS NULL
3695 -- we need to check if person no is provided or not. then we will process for that student otherwise for award
3696 --
3697 IF p_base_id IS NULL
3698 THEN
3699 -- if person no is not given then process for all the students whom that award is given.
3700
3701 IF p_fund_id IS NOT NULL
3702 THEN
3703 -- Get all the Students for which the Award is given
3704 OPEN cur_award_std(p_fund_id);
3705 FETCH cur_award_std INTO award_std_rec;
3706
3707 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3708 THEN
3709 fnd_log.string(
3710 fnd_log.level_statement,
3711 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3712 'starting processing for fund_id ' || p_fund_id
3713 );
3714 END IF;
3715
3716 IF cur_award_std%NOTFOUND
3717 THEN
3718 CLOSE cur_award_std;
3719 fnd_message.set_name('IGF', 'IGF_DB_NO_AWARDS');
3720 fnd_message.set_token('FDESC', get_fund_desc(p_fund_id));
3721 -- No Awards found for this Fund <fund code > : < fund desc >
3722 fnd_file.put_line(
3723 fnd_file.log, RPAD(' ', 5) || fnd_message.get
3724 );
3725
3726 IF fnd_log.level_statement >=
3727 fnd_log.g_current_runtime_level
3728 THEN
3729 fnd_log.string(
3730 fnd_log.level_statement,
3731 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3732 'No award for fund ' || get_fund_desc(p_fund_id)
3733 );
3734 END IF;
3735 ELSE
3736 IF fnd_log.level_statement >=
3737 fnd_log.g_current_runtime_level
3738 THEN
3739 fnd_log.string(
3740 fnd_log.level_statement,
3741 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3742 'award_std_rec.fed_fund_code:'
3743 || award_std_rec.fed_fund_code
3744 );
3745 END IF;
3746
3747 IF award_std_rec.fed_fund_code NOT IN
3748 ('FWS', 'SPNSR', 'PRK')
3749 THEN
3750 -- process only for PELL and Direct Loans.
3751 LOOP
3752 IF fnd_log.level_statement >=
3753 fnd_log.g_current_runtime_level
3754 THEN
3755 fnd_log.string(
3756 fnd_log.level_statement,
3757 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3758 'award_std_rec.base_id: '
3759 || award_std_rec.base_id
3760 );
3761 END IF;
3762
3763 l_person_number := igf_gr_gen.get_per_num(
3764 award_std_rec.base_id
3765 );
3766
3767 IF fnd_log.level_statement >=
3768 fnd_log.g_current_runtime_level
3769 THEN
3770 fnd_log.string(
3771 fnd_log.level_statement,
3772 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3773 'l_person_number:' || l_person_number
3774 );
3775 END IF;
3776
3777 fnd_message.set_name('IGF', 'IGF_AW_PROC_STUD');
3778 fnd_message.set_token('STDNT', l_person_number);
3779 fnd_file.put_line(
3780 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3781 );
3782
3783 IF fnd_log.level_statement >=
3784 fnd_log.g_current_runtime_level
3785 THEN
3786 fnd_log.string(
3787 fnd_log.level_statement,
3788 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3789 'starting processing ' || l_person_number
3790 );
3791 END IF;
3792
3793 --
3794 -- Check for Academic Holds, only if con job is run
3795 --
3796 IF igf_aw_gen_005.get_stud_hold_effect(
3797 'D',
3798 igf_gr_gen.get_person_id(award_std_rec.base_id)
3799 ) = 'F'
3800 THEN
3801 fnd_message.set_name(
3802 'IGF', 'IGF_SL_DISB_FUND_HOLD_FAIL'
3803 );
3804 fnd_file.put_line(
3805 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3806 );
3807
3808 IF fnd_log.level_statement >=
3809 fnd_log.g_current_runtime_level
3810 THEN
3811 fnd_log.string(
3812 fnd_log.level_statement,
3813 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3814 'get_stud_hold_effect returned F'
3815 );
3816 END IF;
3817 ELSE
3818 IF p_loan_id IS NULL
3819 THEN
3820 IF fnd_log.level_statement >=
3821 fnd_log.g_current_runtime_level
3822 THEN
3823 fnd_log.string(
3824 fnd_log.level_statement,
3825 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3826 'calling process_student for base_id 2'
3827 || award_std_rec.base_id
3828 || 'wihtout loan_id'
3829 );
3830 END IF;
3831
3832 process_student(
3833 p_base_id => award_std_rec.base_id,
3834 p_result => l_result, p_fund_id => p_fund_id,
3835 p_award_id => NULL, p_loan_id => NULL,
3836 p_disb_num => NULL,
3837 p_trans_type => p_trans_type
3838 );
3839 ELSE
3840 -- extract award id from loan_id
3841 OPEN cur_get_awd_id(p_loan_id);
3842 FETCH cur_get_awd_id INTO get_awd_id_rec;
3843 CLOSE cur_get_awd_id;
3844
3845 IF fnd_log.level_statement >=
3846 fnd_log.g_current_runtime_level
3847 THEN
3848 fnd_log.string(
3849 fnd_log.level_statement,
3850 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3851 'calling process_student for base_id 3'
3852 || award_std_rec.base_id || 'wiht loan_id'
3853 || p_loan_id
3854 );
3855 END IF;
3856
3857 process_student(
3858 p_base_id => award_std_rec.base_id,
3859 p_result => l_result, p_fund_id => p_fund_id,
3860 p_award_id => get_awd_id_rec.award_id,
3861 p_loan_id => p_loan_id, p_disb_num => NULL,
3862 p_trans_type => p_trans_type
3863 );
3864 END IF;
3865 END IF;
3866
3867 fnd_message.set_name('IGF', 'IGF_DB_END_PROC_PER');
3868 -- End of processing for person number
3869 fnd_message.set_token('PER_NUM', l_person_number);
3870 fnd_file.put_line(
3871 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3872 );
3873 fnd_file.new_line(fnd_file.log, 1);
3874
3875 IF fnd_log.level_statement >=
3876 fnd_log.g_current_runtime_level
3877 THEN
3878 fnd_log.string(
3879 fnd_log.level_statement,
3880 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3881 'end processing ' || l_person_number
3882 );
3883 END IF;
3884
3885 FETCH cur_award_std INTO award_std_rec;
3886 EXIT WHEN cur_award_std%NOTFOUND;
3887 END LOOP;
3888
3889 CLOSE cur_award_std;
3890 ELSE -- Fund code is not PELL or Direct Loan so raise an error
3891 fnd_message.set_name('IGF', 'IGF_SL_ONLY_PELL_LOANS');
3892 fnd_file.put_line(
3893 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3894 );
3895
3896 IF fnd_log.level_statement >=
3897 fnd_log.g_current_runtime_level
3898 THEN
3899 fnd_log.string(
3900 fnd_log.level_statement,
3901 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3902 'unsupported fund type '
3903 || award_std_rec.fed_fund_code
3904 );
3905 END IF;
3906 END IF; -- (IF award_std_rec.fed_fund_code NOT IN ('FWS','SPNSR','PRK'))
3907 END IF; -- (IF cur_award_std%NOTFOUND)
3908 ELSE
3909 -- Fund Id is NULL so raise error
3910 fnd_message.set_name('IGF', 'IGF_SL_REL_DISB_PARAM_EX');
3911 fnd_file.new_line(fnd_file.log, 2);
3912 fnd_file.put_line(fnd_file.log, fnd_message.get);
3913 fnd_file.new_line(fnd_file.log, 2);
3914 RAISE param_exception;
3915 END IF; -- (IF p_fund_id IS NOT NULL)
3916 ELSE
3917 -- i.e, base_id is specified therefore process for the particular student only
3918
3919 l_person_number := igf_gr_gen.get_per_num(p_base_id);
3920 fnd_message.set_name('IGF', 'IGF_AW_PROC_STUD');
3921 fnd_message.set_token('STDNT', l_person_number);
3922 fnd_file.put_line(fnd_file.log, RPAD(' ', 10) || fnd_message.get);
3923
3924 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3925 THEN
3926 fnd_log.string(
3927 fnd_log.level_statement,
3928 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3929 'Starting processing single student ' || l_person_number
3930 );
3931 END IF;
3932
3933 --
3934 -- Check for Academic Holds, only if con job is run
3935 --
3936
3937 IF igf_aw_gen_005.get_stud_hold_effect(
3938 'D', igf_gr_gen.get_person_id(p_base_id)
3939 ) = 'F'
3940 THEN
3941 fnd_message.set_name('IGF', 'IGF_SL_DISB_FUND_HOLD_FAIL');
3942 fnd_file.put_line(
3943 fnd_file.log, RPAD(' ', 10) || fnd_message.get
3944 );
3945
3946 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3947 THEN
3948 fnd_log.string(
3949 fnd_log.level_statement,
3950 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
3951 'get_stud_hold_effect returned F'
3952 );
3953 END IF;
3954 ELSE
3955 IF p_loan_id IS NULL
3956 THEN
3957 process_student(
3958 p_base_id => p_base_id, p_result => l_result,
3959 p_fund_id => p_fund_id, p_award_id => NULL,
3960 p_loan_id => NULL, p_disb_num => NULL,
3961 p_trans_type => p_trans_type
3962 );
3963
3964 IF fnd_log.level_statement >=
3965 fnd_log.g_current_runtime_level
3966 THEN
3967 fnd_log.string(
3968 fnd_log.level_statement,
3969 'igf.plsql.igf_sl_rel_disb.disb_process.debug',
3970 'calling process_student for base_id 4' || p_base_id
3971 || 'without loan_id'
3972 );
3973 END IF;
3974 ELSE
3975 -- extract award id from loan_id
3976 OPEN cur_get_awd_id(p_loan_id);
3977 FETCH cur_get_awd_id INTO get_awd_id_rec;
3978 CLOSE cur_get_awd_id;
3979
3980 IF fnd_log.level_statement >=
3981 fnd_log.g_current_runtime_level
3982 THEN
3983 fnd_log.string(
3984 fnd_log.level_statement,
3985 'igf.plsql.igf_sl_rel_disb.disb_process.debug',
3986 'calling process_student for base_id 5' || p_base_id
3987 || 'with loan_id' || p_loan_id
3988 );
3989 END IF;
3990
3991 process_student(
3992 p_base_id => p_base_id, p_result => l_result,
3993 p_fund_id => p_fund_id,
3994 p_award_id => get_awd_id_rec.award_id,
3995 p_loan_id => p_loan_id, p_disb_num => NULL,
3996 p_trans_type => p_trans_type
3997 );
3998 END IF;
3999 END IF;
4000
4001 fnd_message.set_name('IGF', 'IGF_DB_END_PROC_PER');
4002 -- End of processing for person number
4003 fnd_message.set_token('PER_NUM', l_person_number);
4004 fnd_file.put_line(fnd_file.log, RPAD(' ', 10) || fnd_message.get);
4005 fnd_file.new_line(fnd_file.log, 1);
4006
4007 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
4008 THEN
4009 fnd_log.string(
4010 fnd_log.level_statement,
4011 'igf.plsql.igf_sl_rel_disb.rel_disb_process.debug',
4012 'end processing ' || l_person_number
4013 );
4014 END IF;
4015 END IF; -- (IF p_base_id IS NULL)
4016 END IF; -- (p_per_grp_id IS NOT NULL)
4017
4018
4019 --- more
4020
4021 COMMIT;
4022 EXCEPTION
4023 WHEN param_exception
4024 THEN
4025 ROLLBACK;
4026 p_retcode := 2;
4027 fnd_message.set_name('IGF', 'IGF_SL_REL_DISB_PARAM_EX');
4028 igs_ge_msg_stack.ADD;
4029 igs_ge_msg_stack.conc_exception_hndl;
4030 WHEN app_exception.record_lock_exception
4031 THEN
4032 ROLLBACK;
4033 p_retcode := 2;
4034 fnd_message.set_name('IGF', 'IGF_GE_LOCK_ERROR');
4035 igs_ge_msg_stack.ADD;
4036 p_errbuf := fnd_message.get;
4037 WHEN OTHERS
4038 THEN
4039 ROLLBACK;
4040 p_retcode := 2;
4041 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
4042 fnd_file.put_line(fnd_file.log, SQLERRM);
4043 igs_ge_msg_stack.ADD;
4044 p_errbuf := fnd_message.get;
4045 END rel_disb_process;
4046 END igf_sl_rel_disb;
[Home] [Help]