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