1 PACKAGE BODY IGF_AW_SS_GEN_PKG AS
2 /* $Header: IGFAW23B.pls 120.13 2006/08/10 17:10:31 museshad noship $ */
3
4 /*************************************************************
5 Created By : ugummall
6 Date Created On : 2004/10/04
7 Purpose :
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 museshad 10-Aug-2006 Bug 5337555. Build FA 163. TBH Impact.
12 (reverse chronological order - newest change first)
13 ***************************************************************/
14
15 PROCEDURE apply_certf_resp(p_base_id NUMBER,
16 p_ci_cal_type VARCHAR2,
17 p_ci_sequence_number NUMBER,
18 p_award_prd_cd VARCHAR2,
19 p_cert_code VARCHAR2,
20 p_response VARCHAR2)
21
22
23 IS
24 ------------------------------------------------------------------
25 --Created by : rasahoo, Oracle India
26 --Date created:
27 --
28 --Purpose:
29 --
30 --
31 --Known limitations/enhancements and/or remarks:
32 --
33 --Change History:
34 --Who When What
35 -------------------------------------------------------------------
36 CURSOR cur_cert_resp_details(cp_base_id number,
37 cp_ci_cal_type varchar2,
38 cp_ci_sequence_number number,
39 cp_award_prd_cd varchar2,
40 cp_cert_code varchar2)
41 IS
42 SELECT rowid row_id, resp.*
43 from igf_aw_awd_cert_resps resp
44 where BASE_ID = cp_base_id
45 and CI_CAL_TYPE = cp_ci_cal_type
46 and CI_SEQUENCE_NUMBER = cp_ci_sequence_number
47 and AWARD_PRD_CD = cp_award_prd_cd
48 and AWD_CERT_CODE = cp_cert_code;
49
50 cert_resps_detail cur_cert_resp_details%ROWTYPE;
51 l_rowid VARCHAR2(25);
52 Begin
53
54 OPEN cur_cert_resp_details(p_base_id,p_ci_cal_type,p_ci_sequence_number,p_award_prd_cd,p_cert_code);
55 FETCH cur_cert_resp_details INTO cert_resps_detail;
56
57 IF cur_cert_resp_details%FOUND THEN
58 CLOSE cur_cert_resp_details;
59 igf_aw_awd_cert_resps_pkg.update_row( x_rowid => cert_resps_detail.row_id,
60 x_ci_cal_type => cert_resps_detail.ci_cal_type,
61 x_ci_sequence_number => cert_resps_detail.ci_sequence_number,
62 x_award_prd_cd => cert_resps_detail.award_prd_cd,
63 x_base_id => cert_resps_detail.base_id,
64 x_awd_cert_code => cert_resps_detail.awd_cert_code,
65 x_response_txt => p_response,
66 x_object_version_number => cert_resps_detail.object_version_number,
67 x_mode => 'R');
68 ELSE
69 CLOSE cur_cert_resp_details;
70
71 igf_aw_awd_cert_resps_pkg.insert_row( x_rowid => l_rowid,
72 x_ci_cal_type => p_ci_cal_type,
73 x_ci_sequence_number => p_ci_sequence_number,
74 x_award_prd_cd => p_award_prd_cd,
75 x_base_id => p_base_id,
76 x_awd_cert_code => p_cert_code,
77 x_response_txt => p_response,
78 x_object_version_number => 1,
79 x_mode => 'R');
80
81 END IF;
82
83 END apply_certf_resp;
84
85 PROCEDURE update_awards_from_ss ( p_award_id igf_aw_award_all.award_id%TYPE,
86 p_offered_amt NUMBER,
87 p_accepted_amt NUMBER,
88 p_award_status VARCHAR2
89 ) AS
90 ------------------------------------------------------------------
91 --Created by : rasahoo, Oracle India
92 --Date created:
93 --
94 --Purpose:
95 --
96 --
97 --Known limitations/enhancements and/or remarks:
98 --
99 --Change History:
100 --Who When What
101 -------------------------------------------------------------------
102
103 CURSOR c_disb ( cp_award_id igf_aw_award_all.award_id%TYPE) IS
104 SELECT disb.rowid row_id,
105 disb.*
106 FROM IGF_AW_AWD_DISB_ALL disb
107 WHERE award_id = cp_award_id
108 AND trans_type IN ('A', 'P');
109
110 CURSOR cur_fund_details (cp_award_id NUMBER) IS
111 SELECT mast.*
112 FROM igf_aw_award awd,
113 igf_aw_fund_mast mast
114 WHERE awd.award_id = cp_award_id
115 AND awd.fund_id = mast.fund_id;
116
117 rec_fund_mast cur_fund_details%ROWTYPE;
118
119 l_dis_amt NUMBER;
120 l_net_amt NUMBER;
121 l_trans_type VARCHAR2(1);
122 l_award_status VARCHAR2(30);
123 l_lock_status VARCHAR2(1);
124
125 BEGIN
126 OPEN cur_fund_details(p_award_id);
127 FETCH cur_fund_details INTO rec_fund_mast;
128 CLOSE cur_fund_details;
129
130 IF p_award_status = 'D' THEN
131 IF rec_fund_mast.status_after_decline ='LOCKED' THEN
132 l_lock_status := 'Y';
133 ELSE
134 l_lock_status := 'N';
135 END IF;
136 l_award_status := 'DECLINED';
137
138 ELSIF p_award_status = 'A' THEN
139 l_award_status := 'ACCEPTED';
140 l_lock_status := 'N';
141 ELSE
142 l_lock_status := 'N';
143 END IF;
144
145 update_award_status(p_award_id, l_award_status, l_lock_status);
146 FOR disb_rec IN c_disb(p_award_id) LOOP
147 --Initialize the disbursement amout
148 l_dis_amt := 0;
149 IF p_award_status = 'D' THEN -- If the award is declined then the disb amt will be 0
150 l_dis_amt := 0;
151 l_net_amt := disb_rec.disb_net_amt;
152 l_trans_type := 'C';
153 ELSIF p_award_status = 'A' THEN -- if the award is accepeted then the award is prorated against all the disbursements.
154
155 IF p_accepted_amt = p_offered_amt THEN
156 /*
157 Reduce calculations if full amount is accepted
158 */
159 l_dis_amt := disb_rec.disb_gross_amt;
160 l_net_amt := disb_rec.disb_net_amt;
161 ELSE
162 l_dis_amt := (p_accepted_amt/p_offered_amt) * disb_rec.disb_gross_amt;
163 l_net_amt := disb_rec.disb_net_amt;
164 IF l_dis_amt <> TRUNC(l_dis_amt) AND rec_fund_mast.disb_rounding_code IN ('ONE_FIRST','ONE_LAST') THEN
165 l_dis_amt := TRUNC(l_dis_amt);
166 END IF;
167 IF l_dis_amt <> TRUNC(l_dis_amt,2) AND rec_fund_mast.disb_rounding_code IN ('DEC_FIRST','DEC_LAST') THEN
168 l_dis_amt := TRUNC(l_dis_amt,2);
169 END IF;
170 END IF;
171 IF l_trans_type = 'A' THEN
172 l_trans_type := 'A';
173 ELSE
174 l_trans_type := 'P';
175 END IF;
176 END IF;
177
178 igf_aw_awd_disb_pkg.update_row(
179 x_rowid => disb_rec.row_id,
180 x_award_id => disb_rec.award_id,
181 x_disb_num => disb_rec.disb_num,
182 x_tp_cal_type => disb_rec.tp_cal_type,
183 x_tp_sequence_number => disb_rec.tp_sequence_number,
184 x_disb_gross_amt => disb_rec.disb_gross_amt,
185 x_fee_1 => disb_rec.fee_1,
186 x_fee_2 => disb_rec.fee_2,
187 x_disb_net_amt => l_net_amt,
188 x_disb_date => disb_rec.disb_date,
189 x_trans_type => l_trans_type,
190 x_elig_status => disb_rec.elig_status,
191 x_elig_status_date => disb_rec.elig_status_date,
192 x_affirm_flag => disb_rec.affirm_flag,
193 x_hold_rel_ind => disb_rec.hold_rel_ind,
194 x_manual_hold_ind => disb_rec.manual_hold_ind,
195 x_disb_status => disb_rec.disb_status,
196 x_disb_status_date => disb_rec.disb_status_date,
197 x_late_disb_ind => disb_rec.late_disb_ind,
198 x_fund_dist_mthd => disb_rec.fund_dist_mthd,
199 x_prev_reported_ind => disb_rec.prev_reported_ind,
200 x_fund_release_date => disb_rec.fund_release_date,
201 x_fund_status => disb_rec.fund_status,
202 x_fund_status_date => disb_rec.fund_status_date,
203 x_fee_paid_1 => disb_rec.fee_paid_1,
204 x_fee_paid_2 => disb_rec.fee_paid_2,
205 x_cheque_number => disb_rec.cheque_number,
206 x_ld_cal_type => disb_rec.ld_cal_type,
207 x_ld_sequence_number => disb_rec.ld_sequence_number,
208 x_disb_accepted_amt => l_dis_amt,
209 x_disb_paid_amt => disb_rec.disb_paid_amt,
210 x_rvsn_id => disb_rec.rvsn_id,
211 x_int_rebate_amt => disb_rec.int_rebate_amt,
212 x_force_disb => disb_rec.force_disb,
213 x_min_credit_pts => disb_rec.min_credit_pts,
214 x_disb_exp_dt => disb_rec.disb_exp_dt,
215 x_verf_enfr_dt => disb_rec.verf_enfr_dt,
216 x_fee_class => disb_rec.fee_class,
217 x_show_on_bill => disb_rec.show_on_bill,
218 x_mode => 'R',
219 x_attendance_type_code => disb_rec.attendance_type_code,
220 x_base_attendance_type_code => disb_rec.base_attendance_type_code,
221 x_payment_prd_st_date => disb_rec.payment_prd_st_date,
222 x_change_type_code => disb_rec.change_type_code,
223 x_fund_return_mthd_code => disb_rec.fund_return_mthd_code,
224 x_direct_to_borr_flag => disb_rec.direct_to_borr_flag
225 );
226 END LOOP;
227
228 END update_awards_from_ss;
229
230 FUNCTION get_action_for_off_awds ( p_ci_cal_type VARCHAR2,
231 p_ci_sequence_number NUMBER,
232 p_award_prd_cd VARCHAR2,
233 p_base_id NUMBER)
234 RETURN VARCHAR2 AS
235
236 lv_awd_by_term_prof_val VARCHAR2(10); -- Place holder for the profile value "IGF: Award Acceptance By Term via Student Self-Service" : Scope - local
237 lv_ret_status VARCHAR2(30) := ''; -- The computed return status, returned by the function : Scope - local
238
239 -- Cursor to get the all the terms in an award period
240 CURSOR cur_all_terms_awd_prd ( cp_ci_cal_type varchar2,
241 cp_ci_sequence_number number,
242 cp_award_prd_cd varchar2) IS
243 SELECT apt.ld_cal_type ld_cal_type,
244 apt.ld_sequence_number ld_sequence_number
245 FROM IGF_AW_AWD_PRD_TERM apt,
246 IGS_CA_INST cal
247 WHERE apt.ci_sequence_number = cp_ci_sequence_number
248 AND apt.ci_cal_type = cp_ci_cal_type
249 AND award_prd_cd = cp_award_prd_cd
250 AND apt.ld_cal_type = cal.cal_type
251 AND apt.ld_sequence_number = cal.sequence_number
252 ORDER BY cal.start_dt ASC;
253
254 lc_all_terms_awd_prd cur_all_terms_awd_prd%ROWTYPE; -- place holder for the fetched value for cursor cur_all_terms_awd_prd
255 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
256 lc_version_num igs_ps_ver_all.version_number%TYPE;
257 lc_program_type igs_ps_ver_all.course_type%TYPE;
258 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
259
260 p_last_accept_date DATE;
261
262 BEGIN
263 -- Get the value of profile "IGF: Award Acceptance By Term via Student Self-Service"
264 lv_awd_by_term_prof_val := fnd_profile.value('IGF_AW_AWD_ACCPT_BY_TERM');
265
266 IF lv_awd_by_term_prof_val = 'N' THEN
267 -- do term related things here
268 -- Get the first term associated with the context awarding period and award year.
269 OPEN cur_all_terms_awd_prd (p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd);
270 FETCH cur_all_terms_awd_prd INTO lc_all_terms_awd_prd;
271 CLOSE cur_all_terms_awd_prd;
272
273 -- Get context data for the first term.
274 igf_ap_gen_001.get_context_data_for_term(
275 p_base_id, --IN
276 lc_all_terms_awd_prd.ld_cal_type, --IN
277 lc_all_terms_awd_prd.ld_sequence_number, --IN
278 lc_program_cd, --OUT
279 lc_version_num, --OUT
280 lc_program_type, --OUT
281 lc_org_unit --OUT
282 );
283
284 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
285 p_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
286 'LAST_ACCEPT_DATE', --IN
287 lc_all_terms_awd_prd.ld_cal_type, --IN
288 lc_all_terms_awd_prd.ld_sequence_number,--IN
289 lc_org_unit, --IN
290 lc_program_type, --IN
291 lc_program_cd || '/' || lc_version_num --IN
292 );
293 IF SYSDATE <= p_last_accept_date THEN
294 lv_ret_status := 'AF';
295 ELSE
296 lv_ret_status := 'VO';
297 END IF;
298 ELSE
299 -- do non term related things here
300 FOR l_cur_all_terms_awd_prd IN cur_all_terms_awd_prd (p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd)
301 LOOP
302 -- Get context data for the first term.
303 igf_ap_gen_001.get_context_data_for_term(
304 p_base_id, --IN
305 l_cur_all_terms_awd_prd.ld_cal_type, --IN
306 l_cur_all_terms_awd_prd.ld_sequence_number, --IN
307 lc_program_cd, --OUT
308 lc_version_num, --OUT
309 lc_program_type, --OUT
310 lc_org_unit --OUT
311 );
312
313 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
314 p_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
315 'LAST_ACCEPT_DATE', --IN
316 l_cur_all_terms_awd_prd.ld_cal_type, --IN
317 l_cur_all_terms_awd_prd.ld_sequence_number, --IN
318 lc_org_unit, --IN
319 lc_program_type, --IN
320 lc_program_cd || '/' || lc_version_num --IN
321 );
322
323
324 IF SYSDATE <= p_last_accept_date THEN
325 lv_ret_status := 'AF';
326 END IF;
327 END LOOP;
328
329 IF (NVL(lv_ret_status,'N') <> 'AF') THEN
330 lv_ret_status := 'VO';
331 END IF;
332 END IF; -- end of non term
333
334 RETURN lv_ret_status;
335 END get_action_for_off_awds;
336
337 FUNCTION get_action_for_non_off_awds ( p_ci_cal_type VARCHAR2,
338 p_ci_sequence_number NUMBER,
339 p_award_prd_cd VARCHAR2,
340 p_base_id NUMBER)
341 RETURN VARCHAR2 AS
342
343 lv_awd_by_term_prof_val varchar2(10); -- Place holder for the profile value "IGF: Award Acceptance By Term via Student Self-Service" : Scope - local
344 lv_ret_status varchar2(30) := ''; -- The computed return status, returned by the function : Scope - local
345
346 -- Cursor to get the all the terms in an award period
347 CURSOR cur_all_terms_awd_prd ( cp_ci_cal_type VARCHAR2, cp_ci_sequence_number NUMBER, cp_award_prd_cd VARCHAR2) IS
348 SELECT apt.ld_cal_type ld_cal_type,
349 apt.ld_sequence_number ld_sequence_number
350 FROM IGF_AW_AWD_PRD_TERM apt,
351 IGS_CA_INST cal
352 WHERE apt.ci_sequence_number = cp_ci_sequence_number
353 AND apt.ci_cal_type = cp_ci_cal_type
354 AND award_prd_cd = cp_award_prd_cd
355 AND apt.ld_cal_type = cal.cal_type
356 AND apt.ld_sequence_number = cal.sequence_number
357 ORDER BY cal.start_dt ASC;
358
359 lc_all_terms_awd_prd cur_all_terms_awd_prd%ROWTYPE; -- place holder for the fetched value for cursor cur_all_terms_awd_prd
360 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
361 lc_version_num igs_ps_ver_all.version_number%TYPE;
362 lc_program_type igs_ps_ver_all.course_type%TYPE;
363 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
364
365 p_last_accept_date DATE;
366
367 BEGIN
368 -- Get the value of profile "IGF: Award Acceptance By Term via Student Self-Service"
369 lv_awd_by_term_prof_val := fnd_profile.value('IGF_AW_AWD_ACCPT_BY_TERM');
370
371 IF lv_awd_by_term_prof_val = 'N' THEN
372 -- do term related things here
373 -- Get the first term asciciated with the context awarding period and award year.
374 OPEN cur_all_terms_awd_prd (p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd);
375 FETCH cur_all_terms_awd_prd INTO lc_all_terms_awd_prd;
376 CLOSE cur_all_terms_awd_prd;
377
378 -- Get context data for the first term.
379 igf_ap_gen_001.get_context_data_for_term(
380 p_base_id, --IN
381 lc_all_terms_awd_prd.ld_cal_type, --IN
382 lc_all_terms_awd_prd.ld_sequence_number, --IN
383 lc_program_cd, --OUT
384 lc_version_num, --OUT
385 lc_program_type, --OUT
386 lc_org_unit --OUT
387 );
388
389 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
390 p_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
391 'LAST_ACCEPT_DATE', --IN
392 lc_all_terms_awd_prd.ld_cal_type, --IN
393 lc_all_terms_awd_prd.ld_sequence_number,--IN
394 lc_org_unit, --IN
395 lc_program_type, --IN
396 lc_program_cd || '/' || lc_version_num --IN
397 );
398 IF SYSDATE <= p_last_accept_date THEN
399 lv_ret_status := 'VM';
400 ELSE
401 lv_ret_status := 'VO';
402 END IF;
403
404 ELSE
405
406 -- do non term related things here
407 FOR l_cur_all_terms_awd_prd IN cur_all_terms_awd_prd(p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd)
408 LOOP
409 -- Get context data for the first term.
410 igf_ap_gen_001.get_context_data_for_term(
411 p_base_id, --IN
412 l_cur_all_terms_awd_prd.ld_cal_type, --IN
413 l_cur_all_terms_awd_prd.ld_sequence_number, --IN
414 lc_program_cd, --OUT
415 lc_version_num, --OUT
416 lc_program_type, --OUT
417 lc_org_unit --OUT
418 );
419
420 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
421 p_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
422 'LAST_ACCEPT_DATE', --IN
423 l_cur_all_terms_awd_prd.ld_cal_type, --IN
424 l_cur_all_terms_awd_prd.ld_sequence_number, --IN
425 lc_org_unit, --IN
426 lc_program_type, --IN
427 lc_program_cd || '/' || lc_version_num --IN
428 );
429 IF SYSDATE <= p_last_accept_date THEN
430 lv_ret_status := 'VM';
431 END IF;
432 END LOOP;
433
434 IF (NVL(lv_ret_status, 'N') <> 'VM') THEN
435 lv_ret_status := 'VO';
436 END IF;
437
438 END IF; -- end of non term
439 RETURN lv_ret_status;
440 END get_action_for_non_off_awds;
441
442 FUNCTION get_awd_action ( p_ci_cal_type VARCHAR2,
443 p_ci_sequence_number NUMBER,
444 p_award_prd_cd VARCHAR2,
445 p_base_id NUMBER)
446 RETURN VARCHAR2 AS
447
448 -- Cursor to return award status for all awards in an award period
449 CURSOR c_ap_award (cp_ci_cal_type VARCHAR2, cp_ci_sequence_number NUMBER, cp_award_prd_cd VARCHAR2, cp_base_id NUMBER) IS
450 SELECT awd.*
451 FROM IGF_AW_AWARD_ALL awd,
452 IGF_AW_AWD_DISB_ALL disb,
453 IGF_AW_AWD_PRD_TERM ap
454 WHERE awd.base_id = cp_base_id
455 AND awd.publish_in_ss_flag = 'Y'
456 AND awd.award_id = disb.award_id
457 AND disb.ld_cal_type = ap.ld_cal_type
458 AND disb.ld_sequence_number = ap.ld_sequence_number
459 AND ap.ci_cal_type = cp_ci_cal_type
460 AND ap.ci_sequence_number = cp_ci_sequence_number
461 AND ap.award_prd_cd = cp_award_prd_cd;
462
463
464 -- Cursor to return award status for all awards in an award period
465 CURSOR c_ap_award_terms (cp_ci_cal_type VARCHAR2, cp_ci_sequence_number NUMBER, cp_award_prd_cd VARCHAR2, cp_base_id NUMBER) IS
466 SELECT *
467 FROM IGF_AW_SS_DISB_V
468 WHERE base_id = cp_base_id
469 AND ci_cal_type = cp_ci_cal_type
470 AND ci_sequence_number = cp_ci_sequence_number
471 AND award_prd_cd = cp_award_prd_cd;
472
473 cursor c_person_id(cp_base_id Number) IS
474 Select Person_Id
475 from igf_ap_fa_con_v
476 where base_id = cp_base_id;
477
478 l_person_id number(15);
479
480 CURSOR c_person_awd_status (cp_base_id NUMBER) IS
481 SELECT fa.lock_awd_flag
482 FROM igf_ap_fa_base_rec fa
483 WHERE fa.base_id = cp_base_id;
484
485 l_person_awd_status igf_ap_fa_base_rec.lock_awd_flag%TYPE;
486
487 lb_has_offered BOOLEAN; -- Place holder for Offered award status : Scope - local
488 lb_has_accepted BOOLEAN; -- Place holder for Accepted award status : Scope - local
489 lb_has_declined BOOLEAN; -- Place holder for Declined award status : Scope - local
490 lb_has_cancelled BOOLEAN;
491 lv_ret_status VARCHAR2(30) := ''; -- The computed return status, returned by the function : Scope - local
492 lv_view_only_prof_val VARCHAR2(10); -- Place holder for the profile value "IGF: View-only Awards via Student Self-Service" : Scope - local
493 l_ret_stat_for_acc_amt VARCHAR2(30);
494 l_ret_stat_for_acc VARCHAR2(30);
495 l_ret_stat_for_dec VARCHAR2(30);
496 l_drive_status VARCHAR2(1);
497 l_hold VARCHAR2(10);
498 lv_accept_by_term VARCHAR2(10);
499
500 BEGIN
501
502 -- Get the award status for all awards in an award period for the student (for above base id).
503 -- Set the flag lb_has_accepted if there is any award with the status ACCEPTED
504 -- Set the flag lb_has_offered if there is any award with the status OFFERED
505 -- Set the flag lb_has_declined if there is any award with the status DECLINED
506
507 lb_has_offered := FALSE;
508 lb_has_accepted := FALSE;
509 lb_has_declined := FALSE;
510 lb_has_cancelled := FALSE;
511
512 FOR l_ap_award IN c_ap_award(p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd, p_base_id)
513 LOOP
514 IF l_ap_award.award_status = 'ACCEPTED' THEN
515 lb_has_accepted := true;
516 ELSIF l_ap_award.award_status = 'OFFERED' THEN
517 lb_has_offered := true;
518 ELSIF l_ap_award.award_status = 'DECLINED' THEN
519 lb_has_declined := true;
520 ELSIF l_ap_award.award_status = 'CANCELLED' THEN
521 lb_has_cancelled := true;
522 END IF;
523 END LOOP;
524
525 -- Get the value of profile "IGF: View-only Awards via Student Self-Service"
526 lv_view_only_prof_val := fnd_profile.value('IGF_AW_AWARD_VIEW_ONLY_IN_SS');
527
528 IF (lv_view_only_prof_val = 'Y') THEN -- If the profile value is Yes
529 lv_ret_status := 'VO'; -- All the awards will be shown in View Only mode in the Student Self-Service page.
530 RETURN lv_ret_status;
531 ELSIF (lb_has_offered) THEN -- If the profile value is No and there are any awards with an offered status
532 lv_ret_status := get_action_for_off_awds(p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd, p_base_id);
533 ELSE -- There are NO awards with an offered status
534 lv_ret_status := get_action_for_non_off_awds(p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd, p_base_id);
535 END IF;
536
537 /*
538 Hold Logic goes here
539 If student has a hold on awarding, then all of the student's awards should be view only.
540 */
541 OPEN c_person_id(p_base_id);
542 FETCH c_person_id into l_person_id;
543 CLOSE c_person_id;
544
545 l_hold := igf_aw_gen_005.get_stud_hold_effect('A', l_person_id, NULL );
546
547 IF NVL(l_hold,'X') = 'F' THEN
548 lv_ret_status := 'VO';
549 RETURN lv_ret_status;
550 END IF;
551
552 /*
553 If the Lock Award checkbox is checked at the Person Context Level,
554 then all of the awards are view-only on the Student Awards self-service page for the student in context.
555 */
556 OPEN c_person_awd_status(p_base_id);
557 FETCH c_person_awd_status INTO l_person_awd_status;
558 CLOSE c_person_awd_status;
559
560 IF l_person_awd_status = 'Y' THEN
561 lv_ret_status := 'VO';
562 RETURN lv_ret_status;
563 END IF;
564
565 -- new validations for awards page synch up
566 l_drive_status := 'V';
567 l_ret_stat_for_acc_amt := '';
568 l_ret_stat_for_acc := '';
569 l_ret_stat_for_dec := '';
570
571
572 lv_accept_by_term := fnd_profile.value('IGF_AW_AWD_ACCPT_BY_TERM');
573 IF lv_accept_by_term = 'Y' THEN
574 -- By term logic
575 FOR l_ap_award_terms IN c_ap_award_terms(p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd, p_base_id)
576 LOOP
577 l_ret_stat_for_acc_amt := get_acc_amt_display_mode_term(l_ap_award_terms.award_id,
578 l_ap_award_terms.accepted_amt,
579 l_ap_award_terms.offered_amt,
580 l_ap_award_terms.ld_cal_type,
581 l_ap_award_terms.ld_sequence_number);
582
583 IF l_ret_stat_for_acc_amt = 'MODIFY' THEN
584 IF l_ap_award_terms.award_status = 'OFFERED' THEN
585 l_drive_status := 'A';
586 ELSE
587 l_drive_status := 'M';
588 END IF;
589 END IF;
590
591 l_ret_stat_for_acc := get_acc_display_mode_term(l_ap_award_terms.award_id,
592 l_ap_award_terms.accepted_amt,
593 l_ap_award_terms.offered_amt,
594 l_ap_award_terms.ld_cal_type,
595 l_ap_award_terms.ld_sequence_number);
596 IF l_ret_stat_for_acc = 'MD' THEN
597 IF l_ap_award_terms.award_status = 'OFFERED' THEN
598 l_drive_status := 'A';
599 ELSE
600 l_drive_status := 'M';
601 END IF;
602 END IF;
603
604 l_ret_stat_for_dec := get_dec_display_mode_term(l_ap_award_terms.award_id,
605 l_ap_award_terms.accepted_amt,
606 l_ap_award_terms.offered_amt,
607 l_ap_award_terms.ld_cal_type,
608 l_ap_award_terms.ld_sequence_number);
609 IF l_ret_stat_for_dec = 'M' THEN
610 IF l_ap_award_terms.award_status = 'OFFERED' THEN
611 l_drive_status := 'A';
612 ELSE
613 l_drive_status := 'M';
614 END IF;
615 END IF;
616
617 IF l_drive_status = 'A' THEN
618 EXIT;
619 END IF;
620
621 END LOOP;
622
623 IF l_drive_status ='A' THEN
624 lv_ret_status := 'AF';
625 ELSIF l_drive_status ='M' THEN
626 lv_ret_status := 'VM';
627 ELSE
628 lv_ret_status := 'VO';
629 END IF;
630
631 ELSE
632 -- By Awd Prd logic
633 FOR l_ap_award IN c_ap_award(p_ci_cal_type, p_ci_sequence_number, p_award_prd_cd, p_base_id)
634 LOOP
635 l_ret_stat_for_acc_amt := get_accept_amt_display_mode(l_ap_award.award_id, l_ap_award.accepted_amt, l_ap_award.offered_amt);
636 IF l_ret_stat_for_acc_amt = 'MODIFY' THEN
637 l_drive_status := 'M';
638 END IF;
639
640 l_ret_stat_for_acc := get_accept_display_mode(l_ap_award.award_id, l_ap_award.accepted_amt, l_ap_award.offered_amt);
641 IF l_ret_stat_for_acc = 'MD' THEN
642 l_drive_status := 'M';
643 END IF;
644
645 l_ret_stat_for_dec := get_decline_display_mode(l_ap_award.award_id, l_ap_award.accepted_amt, l_ap_award.offered_amt);
646 IF l_ret_stat_for_dec = 'M' THEN
647 l_drive_status := 'M';
648 END IF;
649 END LOOP;
650
651 IF l_drive_status <> 'M' THEN
652 lv_ret_status := 'VO';
653 END IF;
654
655 END IF;
656
657 RETURN lv_ret_status;
658
659 END get_awd_action;
660
661 FUNCTION get_accept_amt_display_mode ( p_award_id NUMBER,
662 p_accepted_amt NUMBER,
663 p_offered_amt NUMBER)
664 RETURN VARCHAR2 AS
665
666 CURSOR cur_fund_details ( cp_award_id NUMBER) IS
667 SELECT awd.lock_award_flag lock_awd_flag, mast.*
668 FROM IGF_AW_AWARD awd,
669 IGF_AW_FUND_MAST mast
670 WHERE awd.award_id = cp_award_id
671 AND awd.fund_id = mast.fund_id;
672 fund_mast_rec cur_fund_details%ROWTYPE;
673
674 CURSOR cur_min_disb_date ( cp_award_id NUMBER) IS
675 SELECT MIN(disb_date)
676 FROM IGF_AW_AWD_DISB
677 WHERE award_id = cp_award_id;
678
679 sch_disb_date DATE;
680
681 CURSOR cur_fund_type (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
682 SELECT cat.fed_fund_code
683 FROM igf_aw_fund_mast_all fund,
684 igf_aw_fund_cat_all cat
685 WHERE fund.fund_code = cat.fund_code
686 AND fund.fund_id = cp_fund_id;
687
688 l_fund_type igf_aw_fund_cat_v.fed_fund_code%TYPE;
689
690 CURSOR cur_orig_status_pell (cp_award_id NUMBER) IS
691 SELECT orig_action_code
692 FROM igf_gr_rfms
693 WHERE award_id = cp_award_id;
694
695 l_orig_status_pell igf_gr_rfms.orig_action_code%TYPE;
696
697 CURSOR cur_orig_status (cp_award_id NUMBER) IS
698 SELECT loan_status, loan_chg_status
699 FROM igf_sl_loans_all
700 WHERE award_id = cp_award_id;
701
702 l_orig_status cur_orig_status%ROWTYPE;
703
704 CURSOR cur_award_status (cp_award_id NUMBER) IS
705 SELECT award_status
706 FROM IGF_AW_AWARD
707 WHERE award_id = cp_award_id;
708
709 l_award_status igf_aw_award.AWARD_STATUS%TYPE;
710
711
712 BEGIN
713
714 OPEN cur_fund_details(p_award_id);
715 FETCH cur_fund_details INTO fund_mast_rec;
716 CLOSE cur_fund_details;
717
718 -- If the award is locked at the award context level, display it as read-only
719 IF fund_mast_rec.lock_awd_flag = 'Y' THEN
720 RETURN 'VIEW';
721 END IF;
722
723 -- Check If the view only check box is checked for the fund in fund manager form.
724 --If the View only check box is checked then return V.
725 IF NVL(fund_mast_rec.VIEW_ONLY_FLAG,'N') = 'Y' THEN
726 RETURN 'VIEW';
727 END IF;
728 -- Check the fund type.
729 -- If the fund type is pell then check the origination status of the fund.
730 -- If the origination staus is Sent then return V.
731 -- If the fund type is Direct or FFELP then check the origination status of the fund.
732 -- If the origination staus is Sent then return V.
733 OPEN cur_fund_type(fund_mast_rec.fund_id);
734 FETCH cur_fund_type INTO l_fund_type;
735 CLOSE cur_fund_type;
736 IF l_fund_type = 'PELL' THEN
737 OPEN cur_orig_status_pell(p_award_id);
738 FETCH cur_orig_status_pell INTO l_orig_status_pell;
739 CLOSE cur_orig_status_pell;
740 IF l_orig_status_pell = 'S' THEN
741 RETURN 'VIEW';
742 END IF;
743 ELSIF l_fund_type IN ('DLU', 'DLP', 'DLS', 'FLP', 'FLU', 'FLS', 'ALT') THEN
744 OPEN cur_orig_status(p_award_id);
745 FETCH cur_orig_status INTO l_orig_status;
746 CLOSE cur_orig_status;
747 IF l_orig_status.loan_status = 'S' OR l_orig_status.loan_chg_status = 'S' THEN
748 RETURN 'VIEW';
749 END IF;
750
751 IF l_fund_type IN ('FLP', 'FLU', 'FLS', 'ALT') THEN
752 IF l_orig_status.loan_status = 'A' AND NVL(l_orig_status.loan_chg_status, 'A') = 'A' THEN
753 RETURN 'VIEW';
754 END IF;
755 END IF;
756
757 END IF;
758
759 /*
760 If the "Allow Acceptance of Lesser Amount" checkbox IS NOT checked on the Fund Manager form,
761 Student Self-Service tab, the student can only accept the entire amount or decline the entire
762 amount of an award. The student cannot accept a lesser amount.
763 The Accepted Amount should NOT be editable.
764 */
765 OPEN cur_award_status(p_award_id);
766 FETCH cur_award_status INTO l_award_status;
767 CLOSE cur_award_status;
768
769 IF l_award_status = 'CANCELLED' THEN
770 RETURN 'VIEW';
771 END IF;
772
773 IF NVL(fund_mast_rec.accept_less_amt_flag,'N') <> 'Y' THEN
774 RETURN 'VIEW';
775 END IF;
776
777 IF (NVL(fund_mast_rec.allow_inc_post_accept_flag,'N') <> 'Y') AND (NVL(fund_mast_rec.allow_dec_post_accept_flag,'N') <> 'Y') THEN
778 IF l_award_status = 'ACCEPTED' THEN
779 RETURN 'VIEW';
780 END IF;
781 END IF;
782
783 /*
784 If the "View-only After Decline" radio button IS selected on the Fund Manager form,
785 Student Self-Service tab, then when a student declines an award via student self-service,
786 make the award view-only on the Fund Manager form, Student Self-Service tab.
787 This award will still be picked up during the repackaging process.
788
789 If the "View-only After Decline" radio button IS NOT selected on the Fund Manager form,
790 Student Self-Service tab, then when a student declines an award via student self-service,
791 the award remains editable. This award will still be picked up during the repackaging process.
792 */
793
794 IF l_award_status = 'DECLINED' AND NVL(fund_mast_rec.status_after_decline,'VIEW') = 'VIEW' THEN
795 RETURN 'VIEW';
796 END IF;
797
798 RETURN 'MODIFY';
799 END get_accept_amt_display_mode;
800
801 FUNCTION get_accept_display_mode ( p_award_id NUMBER,
802 p_accepted_amt NUMBER,
803 p_offered_amt NUMBER)
804 RETURN VARCHAR2 AS
805
806 CURSOR cur_fund_details (cp_award_id NUMBER) IS
807 SELECT awd.lock_award_flag lock_awd_flag, mast.*
808 FROM IGF_AW_AWARD awd,
809 IGF_AW_FUND_MAST mast
810 WHERE awd.award_id = cp_award_id
811 AND awd.fund_id = mast.fund_id;
812 fund_mast_rec cur_fund_details%ROWTYPE;
813
814 CURSOR cur_min_disb_date (cp_award_id NUMBER) IS
815 SELECT MIN(disb_date)
816 FROM IGF_AW_AWD_DISB
817 WHERE award_id = cp_award_id;
818
819 sch_disb_date DATE;
820
821 CURSOR cur_fund_type (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
822 SELECT cat.fed_fund_code
823 FROM igf_aw_fund_mast_all fund,
824 igf_aw_fund_cat_all cat
825 WHERE fund.fund_code = cat.fund_code
826 AND fund.fund_id = cp_fund_id;
827
828 l_fund_type igf_aw_fund_cat_v.fed_fund_code%TYPE;
829
830 CURSOR cur_orig_status_pell (cp_award_id NUMBER) IS
831 SELECT orig_action_code
832 FROM igf_gr_rfms
833 WHERE award_id = cp_award_id;
834
835 l_orig_status_pell igf_gr_rfms.orig_action_code%TYPE;
836
837 CURSOR cur_orig_status (cp_award_id NUMBER) IS
838 SELECT loan_status, loan_chg_status
839 FROM igf_sl_loans_all
840 WHERE award_id = cp_award_id;
841
842 l_orig_status cur_orig_status%ROWTYPE;
843
844 CURSOR cur_award_status (cp_award_id NUMBER) IS
845 SELECT award_status
846 FROM IGF_AW_AWARD
847 WHERE award_id = cp_award_id;
848
849 l_award_status igf_aw_award.AWARD_STATUS%TYPE;
850
851 BEGIN
852 OPEN cur_fund_details(p_award_id);
853 FETCH cur_fund_details INTO fund_mast_rec;
854 CLOSE cur_fund_details;
855
856 -- If the award is locked at the award context level, display it as read-only
857 IF fund_mast_rec.lock_awd_flag = 'Y' THEN
858 RETURN 'VW';
859 END IF;
860
861 -- Check If the view only check box is checked for the fund in fund manager form.
862 --If the View only check box is checked then return V.
863 IF NVL(fund_mast_rec.VIEW_ONLY_FLAG,'N') = 'Y' THEN
864 RETURN 'VW';
865 END IF;
866
867 -- Check the fund type.
868 -- If the fund type is pell then check the origination status of the fund.
869 -- If the origination staus is Sent then return V.
870 -- If the fund type is Direct or FFELP then check the origination status of the fund.
871 -- If the origination staus is Sent then return V.
872 OPEN cur_fund_type(fund_mast_rec.fund_id);
873 FETCH cur_fund_type INTO l_fund_type;
874 CLOSE cur_fund_type;
875
876 IF l_fund_type = 'PELL' THEN
877 OPEN cur_orig_status_pell(p_award_id);
878 FETCH cur_orig_status_pell INTO l_orig_status_pell;
879 CLOSE cur_orig_status_pell;
880 IF l_orig_status_pell = 'S' THEN
881 RETURN 'VW';
882 END IF;
883 ELSIF l_fund_type IN ('DLU', 'DLP', 'DLS', 'FLP', 'FLU', 'FLS', 'ALT') THEN
884 OPEN cur_orig_status(p_award_id);
885 FETCH cur_orig_status INTO l_orig_status;
886 CLOSE cur_orig_status;
887 IF l_orig_status.loan_status = 'S' OR l_orig_status.loan_chg_status = 'S' THEN
888 RETURN 'VW';
889 END IF;
890
891 IF l_fund_type IN ('FLP', 'FLU', 'FLS', 'ALT') THEN
892 IF l_orig_status.loan_status = 'A' AND NVL(l_orig_status.loan_chg_status, 'A') = 'A' THEN
893 RETURN 'VW';
894 END IF;
895 END IF;
896
897 END IF;
898
899
900 OPEN cur_award_status(p_award_id);
901 FETCH cur_award_status INTO l_award_status;
902 CLOSE cur_award_status;
903
904 IF l_award_status = 'CANCELLED' THEN
905 RETURN 'VW';
906 END IF;
907
908 /*
909 If the "Allow Increases To Accepted Amount After Acceptance" checkbox IS NOT checked
910 and the checkbox for "Allow Decreases To Accepted Amount After Acceptance" IS NOT checked
911 on the Fund Manager form, Student Self-Service tab,
912 then after the award goes into accepted status, disable the Accept radio button
913 and the Accepted Amount field to prevent further updates
914 */
915
916 IF (NVL(fund_mast_rec.allow_inc_post_accept_flag,'N') <> 'Y') AND (NVL(fund_mast_rec.allow_dec_post_accept_flag,'N') <> 'Y') THEN
917 IF l_award_status = 'ACCEPTED' THEN
918 RETURN 'VW';
919 END IF;
920 END IF;
921
922
923 /*
924 If the "View-only After Decline" radio button IS selected on the Fund Manager form,
925 Student Self-Service tab, then when a student declines an award via student self-service,
926 make the award view-only on the Fund Manager form, Student Self-Service tab.
927 This award will still be picked up during the repackaging process.
928
929 If the "View-only After Decline" radio button IS NOT selected on the Fund Manager form,
930 Student Self-Service tab, then when a student declines an award via student self-service,
931 the award remains editable. This award will still be picked up during the repackaging process.
932 */
933
934 IF l_award_status = 'DECLINED' AND NVL(fund_mast_rec.status_after_decline,'VIEW') = 'VIEW' THEN
935 RETURN 'VW';
936 END IF;
937
938 RETURN 'MD';
939 END get_accept_display_mode;
940
941 FUNCTION get_decline_display_mode ( p_award_id NUMBER,
942 p_accepted_amt NUMBER,
943 p_offered_amt NUMBER)
944 RETURN VARCHAR2 AS
945
946 CURSOR cur_fund_details (cp_award_id NUMBER) IS
947 SELECT awd.lock_award_flag lock_awd_flag, mast.*
948 FROM IGF_AW_AWARD awd,
949 IGF_AW_FUND_MAST mast
950 WHERE awd.award_id = cp_award_id
951 AND awd.fund_id = mast.fund_id;
952 fund_mast_rec cur_fund_details%ROWTYPE;
953
954 CURSOR cur_min_disb_date (cp_award_id NUMBER) IS
955 SELECT MIN(disb_date)
956 FROM IGF_AW_AWD_DISB
957 WHERE award_id = cp_award_id;
958
959 sch_disb_date DATE;
960
961 CURSOR cur_fund_type (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
962 SELECT cat.fed_fund_code
963 FROM igf_aw_fund_mast_all fund,
964 igf_aw_fund_cat_all cat
965 WHERE fund.fund_code = cat.fund_code
966 AND fund.fund_id = cp_fund_id;
967
968 l_fund_type igf_aw_fund_cat_v.fed_fund_code%TYPE;
969
970 CURSOR cur_orig_status_pell (cp_award_id NUMBER) IS
971 SELECT orig_action_code
972 FROM igf_gr_rfms
973 WHERE award_id = cp_award_id;
974
975 l_orig_status_pell igf_gr_rfms.orig_action_code%TYPE;
976
977 CURSOR cur_orig_status (cp_award_id NUMBER) IS
978 SELECT loan_status,loan_chg_status
979 FROM igf_sl_loans_all
980 WHERE award_id = cp_award_id;
981
982 l_orig_status cur_orig_status%ROWTYPE;
983
984 CURSOR cur_award_details (cp_award_id NUMBER) IS
985 SELECT *
986 FROM IGF_AW_AWARD awd
987 WHERE awd.award_id = cp_award_id;
988
989 --rec_award_status igf_aw_award.AWARD_STATUS%TYPE;
990 rec_award_details cur_award_details%ROWTYPE;
991
992 CURSOR cur_award_status (cp_award_id NUMBER) IS
993 SELECT award_status
994 FROM IGF_AW_AWARD
995 WHERE award_id = cp_award_id;
996
997 l_award_status igf_aw_award.AWARD_STATUS%TYPE;
998
999 BEGIN
1000
1001 OPEN cur_fund_details(p_award_id);
1002 FETCH cur_fund_details INTO fund_mast_rec;
1003 CLOSE cur_fund_details;
1004
1005 -- If the award is locked at the award context level, display it as read-only
1006 IF fund_mast_rec.lock_awd_flag = 'Y' THEN
1007 RETURN 'V';
1008 END IF;
1009
1010 -- Check If the view only check box is checked for the fund in fund manager form.
1011 --If the View only check box is checked then return V.
1012 IF NVL(fund_mast_rec.VIEW_ONLY_FLAG,'N') = 'Y' THEN
1013 RETURN 'V';
1014 END IF;
1015
1016 -- Check the fund type.
1017 -- If the fund type is pell then check the origination status of the fund.
1018 -- If the origination staus is Sent then return V.
1019 -- If the fund type is Direct or FFELP then check the origination status of the fund.
1020 -- If the origination staus is Sent then return V.
1021 OPEN cur_fund_type(fund_mast_rec.fund_id);
1022 FETCH cur_fund_type INTO l_fund_type;
1023 CLOSE cur_fund_type;
1024
1025 IF l_fund_type = 'PELL' THEN
1026 OPEN cur_orig_status_pell(p_award_id);
1027 FETCH cur_orig_status_pell INTO l_orig_status_pell;
1028 CLOSE cur_orig_status_pell;
1029 IF l_orig_status_pell = 'S' THEN
1030 RETURN 'V';
1031 END IF;
1032 ELSIF l_fund_type IN ('DLU', 'DLP', 'DLS', 'FLP', 'FLU', 'FLS', 'ALT') THEN
1033 OPEN cur_orig_status(p_award_id);
1034 FETCH cur_orig_status INTO l_orig_status;
1035 CLOSE cur_orig_status;
1036 IF l_orig_status.loan_status = 'S' OR l_orig_status.loan_chg_status = 'S' THEN
1037 RETURN 'V';
1038 END IF;
1039
1040 IF l_fund_type IN ('FLP', 'FLU', 'FLS', 'ALT') THEN
1041 IF l_orig_status.loan_status = 'A' AND NVL(l_orig_status.loan_chg_status, 'A') = 'A' THEN
1042 RETURN 'V';
1043 END IF;
1044 END IF;
1045
1046 END IF;
1047
1048 /*
1049 If the "Allow Decline After Accept" checkbox IS selected on the Fund Manager form,
1050 Student Self-Service tab, then if the award has an accepted status,
1051 the Decline radio button for that award will be enabled.
1052
1053 If the "Allow Decline After Accept" checkbox IS NOT selected on the Fund Manager form,
1054 Student Self-Service tab, then if the award has an accepted status,
1055 the Decline radio button for that award will NOT be enabled.
1056
1057 Note: An award cannot be declined once any part of it has been 'Paid'
1058 (i.e. if any part of the award is paid, then the decline radio button for that
1059 award should not be enabled).
1060 */
1061
1062 OPEN cur_award_details(p_award_id);
1063 FETCH cur_award_details INTO rec_award_details;
1064 CLOSE cur_award_details;
1065
1066 IF rec_award_details.award_status = 'ACCEPTED' THEN
1067 IF (NVL(fund_mast_rec.ALLOW_DECLN_POST_ACCEPT_FLAG,'N') <> 'Y') THEN
1068 RETURN 'V';
1069 END IF;
1070
1071 IF NVL(rec_award_details.PAID_AMT,0) > 0 THEN
1072 RETURN 'V';
1073 END IF;
1074 END IF;
1075
1076 IF rec_award_details.award_status = 'CANCELLED' THEN
1077 RETURN 'V';
1078 END IF;
1079
1080
1081 OPEN cur_award_status(p_award_id);
1082 FETCH cur_award_status INTO l_award_status;
1083 CLOSE cur_award_status;
1084
1085
1086 /*
1087 If the "View-only After Decline" radio button IS selected on the Fund Manager form,
1088 Student Self-Service tab, then when a student declines an award via student self-service,
1089 make the award view-only on the Fund Manager form, Student Self-Service tab.
1090 This award will still be picked up during the repackaging process.
1091
1092 If the "View-only After Decline" radio button IS NOT selected on the Fund Manager form,
1093 Student Self-Service tab, then when a student declines an award via student self-service,
1094 the award remains editable. This award will still be picked up during the repackaging process.
1095
1096 In either case, if the award is declined, it is made read-only.
1097 */
1098
1099 IF l_award_status = 'DECLINED' AND NVL(fund_mast_rec.status_after_decline,'VIEW') = 'VIEW' THEN
1100 RETURN 'V';
1101 END IF;
1102
1103 RETURN 'M';
1104 END get_decline_display_mode;
1105
1106 PROCEDURE update_award_status ( p_award_id igf_aw_award_all.award_id%TYPE,
1107 p_award_status VARCHAR2,
1108 p_lock_status VARCHAR2
1109 ) AS
1110 ------------------------------------------------------------------
1111 --Created by : rasahoo, Oracle India
1112 --Date created:
1113 --
1114 --Purpose:
1115 --
1116 --
1117 --Known limitations/enhancements and/or remarks:
1118 --
1119 --Change History:
1120 --Who When What
1121 -------------------------------------------------------------------
1122 -- Get an award
1123 CURSOR c_award (cp_award_id igf_aw_award_all.award_id%TYPE) IS
1124 SELECT awd.rowid row_id,
1125 awd.*
1126 FROM IGF_AW_AWARD_ALL awd
1127 WHERE award_id = cp_award_id;
1128 awd_rec c_award%ROWTYPE;
1129
1130 BEGIN
1131 OPEN c_award(p_award_id);
1132 FETCH c_award INTO awd_rec;
1133 CLOSE c_award;
1134
1135 igf_aw_award_pkg.set_award_change_source('STDNT_SELF_SERV');
1136
1137 igf_aw_award_pkg.update_row(
1138 x_rowid => awd_rec.row_id,
1139 x_award_id => awd_rec.award_id,
1140 x_fund_id => awd_rec.fund_id,
1141 x_base_id => awd_rec.base_id,
1142 x_offered_amt => awd_rec.offered_amt,
1143 x_accepted_amt => awd_rec.accepted_amt,
1144 x_paid_amt => awd_rec.paid_amt,
1145 x_packaging_type => awd_rec.packaging_type,
1146 x_batch_id => awd_rec.batch_id,
1147 x_manual_update => awd_rec.manual_update,
1148 x_rules_override => awd_rec.rules_override,
1149 x_award_date => awd_rec.award_date,
1150 x_award_status => p_award_status,
1151 x_attribute_category => awd_rec.attribute_category,
1152 x_attribute1 => awd_rec.attribute1,
1153 x_attribute2 => awd_rec.attribute2,
1154 x_attribute3 => awd_rec.attribute3,
1155 x_attribute4 => awd_rec.attribute4,
1156 x_attribute5 => awd_rec.attribute5,
1157 x_attribute6 => awd_rec.attribute6,
1158 x_attribute7 => awd_rec.attribute7,
1159 x_attribute8 => awd_rec.attribute8,
1160 x_attribute9 => awd_rec.attribute9,
1161 x_attribute10 => awd_rec.attribute10,
1162 x_attribute11 => awd_rec.attribute11,
1163 x_attribute12 => awd_rec.attribute12,
1164 x_attribute13 => awd_rec.attribute13,
1165 x_attribute14 => awd_rec.attribute14,
1166 x_attribute15 => awd_rec.attribute15,
1167 x_attribute16 => awd_rec.attribute16,
1168 x_attribute17 => awd_rec.attribute17,
1169 x_attribute18 => awd_rec.attribute18,
1170 x_attribute19 => awd_rec.attribute19,
1171 x_attribute20 => awd_rec.attribute20,
1172 x_rvsn_id => awd_rec.rvsn_id,
1173 x_alt_pell_schedule => awd_rec.alt_pell_schedule,
1174 x_mode => 'R',
1175 x_award_number_txt => awd_rec.award_number_txt,
1176 x_legacy_record_flag => awd_rec.legacy_record_flag,
1177 x_adplans_id => awd_rec.adplans_id,
1178 x_lock_award_flag => p_lock_status,
1179 x_app_trans_num_txt => awd_rec.app_trans_num_txt,
1180 x_awd_proc_status_code => awd_rec.awd_proc_status_code,
1181 x_publish_in_ss_flag => awd_rec.publish_in_ss_flag
1182 );
1183
1184 igf_aw_Award_pkg.reset_awd_hist_trans_id;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 NULL;
1189 END update_award_status;
1190
1191
1192
1193 PROCEDURE update_awards_by_term_from_ss ( p_award_id igf_aw_award_all.award_id%TYPE,
1194 p_ld_cal_type VARCHAR2,
1195 p_ld_seq_num NUMBER,
1196 p_offered_amt NUMBER,
1197 p_accepted_amt NUMBER,
1198 p_term_awd_status VARCHAR2
1199 ) AS
1200 ------------------------------------------------------------------
1201 --Created by : rasahoo, Oracle India
1202 --Date created:
1203 --
1204 --Purpose:
1205 --
1206 --
1207 --Known limitations/enhancements and/or remarks:
1208 --
1209 --Change History:
1210 --Who When What
1211 -------------------------------------------------------------------
1212
1213 CURSOR c_disb ( cp_award_id igf_aw_award_all.award_id%TYPE,
1214 cp_ld_cal_type VARCHAR2,
1215 cp_ld_seq_num NUMBER) IS
1216 SELECT disb.rowid row_id,
1217 disb.*
1218 FROM IGF_AW_AWD_DISB_ALL disb
1219 WHERE award_id = cp_award_id
1220 AND ld_cal_type = cp_ld_cal_type
1221 AND ld_sequence_number = cp_ld_seq_num
1222 AND trans_type IN ('A', 'P');
1223
1224 l_dis_amt NUMBER;
1225 l_net_amt NUMBER;
1226 l_trans_type VARCHAR2(1);
1227 l_award_status VARCHAR2(30);
1228 l_lock_status VARCHAR2(1);
1229
1230 CURSOR c_get_disb_rounding(
1231 cp_award_id igf_aw_award_all.award_id%TYPE
1232 ) IS
1233 SELECT fmast.disb_rounding_code
1234 FROM igf_aw_award_all awd,
1235 igf_aw_fund_mast_all fmast
1236 WHERE awd.fund_id = fmast.fund_id
1237 AND awd.award_id = cp_award_id;
1238 rec_fund_mast c_get_disb_rounding%ROWTYPE;
1239
1240 BEGIN
1241
1242 IF p_term_awd_status = 'ACCEPTED' THEN
1243 IF l_trans_type = 'A' THEN
1244 l_trans_type := 'A';
1245 ELSE
1246 l_trans_type := 'P';
1247 END IF;
1248 ELSIF p_term_awd_status = 'DECLINED' THEN
1249 l_trans_type := 'C';
1250 END IF;
1251
1252 OPEN c_get_disb_rounding(p_award_id);
1253 FETCH c_get_disb_rounding INTO rec_fund_mast;
1254 CLOSE c_get_disb_rounding;
1255
1256 FOR disb_rec IN c_disb(p_award_id, p_ld_cal_type, p_ld_seq_num) LOOP
1257 --Initialize the disbursement amout
1258 l_dis_amt := 0;
1259 IF p_accepted_amt = p_offered_amt THEN
1260 /*
1261 Reduce calculations if full amount is accepted
1262 */
1263 l_dis_amt := disb_rec.disb_gross_amt;
1264 l_net_amt := disb_rec.disb_net_amt;
1265 ELSE
1266 l_dis_amt := (p_accepted_amt/p_offered_amt) * disb_rec.disb_gross_amt;
1267 l_net_amt := disb_rec.disb_net_amt;
1268 IF l_dis_amt <> TRUNC(l_dis_amt) AND rec_fund_mast.disb_rounding_code IN ('ONE_FIRST','ONE_LAST') THEN
1269 l_dis_amt := TRUNC(l_dis_amt);
1270 END IF;
1271 IF l_dis_amt <> TRUNC(l_dis_amt,2) AND rec_fund_mast.disb_rounding_code IN ('DEC_FIRST','DEC_LAST') THEN
1272 l_dis_amt := TRUNC(l_dis_amt,2);
1273 END IF;
1274 END IF;
1275
1276 igf_aw_awd_disb_pkg.update_row(
1277 x_rowid => disb_rec.row_id,
1278 x_award_id => disb_rec.award_id,
1279 x_disb_num => disb_rec.disb_num,
1280 x_tp_cal_type => disb_rec.tp_cal_type,
1281 x_tp_sequence_number => disb_rec.tp_sequence_number,
1282 x_disb_gross_amt => disb_rec.disb_gross_amt,
1283 x_fee_1 => disb_rec.fee_1,
1284 x_fee_2 => disb_rec.fee_2,
1285 x_disb_net_amt => l_net_amt,
1286 x_disb_date => disb_rec.disb_date,
1287 x_trans_type => l_trans_type,
1288 x_elig_status => disb_rec.elig_status,
1289 x_elig_status_date => disb_rec.elig_status_date,
1290 x_affirm_flag => disb_rec.affirm_flag,
1291 x_hold_rel_ind => disb_rec.hold_rel_ind,
1292 x_manual_hold_ind => disb_rec.manual_hold_ind,
1293 x_disb_status => disb_rec.disb_status,
1294 x_disb_status_date => disb_rec.disb_status_date,
1295 x_late_disb_ind => disb_rec.late_disb_ind,
1296 x_fund_dist_mthd => disb_rec.fund_dist_mthd,
1297 x_prev_reported_ind => disb_rec.prev_reported_ind,
1298 x_fund_release_date => disb_rec.fund_release_date,
1299 x_fund_status => disb_rec.fund_status,
1300 x_fund_status_date => disb_rec.fund_status_date,
1301 x_fee_paid_1 => disb_rec.fee_paid_1,
1302 x_fee_paid_2 => disb_rec.fee_paid_2,
1303 x_cheque_number => disb_rec.cheque_number,
1304 x_ld_cal_type => disb_rec.ld_cal_type,
1305 x_ld_sequence_number => disb_rec.ld_sequence_number,
1306 x_disb_accepted_amt => l_dis_amt,
1307 x_disb_paid_amt => disb_rec.disb_paid_amt,
1308 x_rvsn_id => disb_rec.rvsn_id,
1309 x_int_rebate_amt => disb_rec.int_rebate_amt,
1310 x_force_disb => disb_rec.force_disb,
1311 x_min_credit_pts => disb_rec.min_credit_pts,
1312 x_disb_exp_dt => disb_rec.disb_exp_dt,
1313 x_verf_enfr_dt => disb_rec.verf_enfr_dt,
1314 x_fee_class => disb_rec.fee_class,
1315 x_show_on_bill => disb_rec.show_on_bill,
1316 x_mode => 'R',
1317 x_attendance_type_code => disb_rec.attendance_type_code,
1318 x_base_attendance_type_code => disb_rec.base_attendance_type_code,
1319 x_payment_prd_st_date => disb_rec.payment_prd_st_date,
1320 x_change_type_code => disb_rec.change_type_code,
1321 x_fund_return_mthd_code => disb_rec.fund_return_mthd_code,
1322 x_direct_to_borr_flag => disb_rec.direct_to_borr_flag
1323 );
1324 END LOOP;
1325 END update_awards_by_term_from_ss;
1326
1327 PROCEDURE submit_business_event (p_description VARCHAR2,
1328 p_award_prd VARCHAR2,
1329 p_person_number VARCHAR2,
1330 p_details VARCHAR2) IS
1331
1332 l_wf_event_t wf_event_t;
1333 lv_event_name VARCHAR2(50);
1334 l_seq_val VARCHAR2(100);
1335 l_wf_parameter_list_t wf_parameter_list_t;
1336
1337 BEGIN
1338
1339 SELECT igs_pe_res_chg_s.nextval INTO l_seq_val FROM DUAL;
1340
1341 -- Initialize the wf_event_t object
1342 WF_EVENT_T.Initialize(l_wf_event_t);
1343 -- Set the event name
1344 lv_event_name := 'oracle.apps.igf.aw.AcptAmtMod';
1345
1346 l_wf_event_t.setEventName(pEventName => lv_event_name);
1347
1348 -- Set the event key
1349 l_wf_event_t.setEventKey(
1350 pEventKey => lv_event_name || l_seq_val
1351 );
1352
1353 -- Set the parameter list
1354 l_wf_event_t.setParameterList(
1355 pParameterList => l_wf_parameter_list_t
1356 );
1357
1358 wf_event.addparametertolist(
1359 p_name => 'AWARD_YEAR',
1360 p_value => p_description,
1361 p_parameterlist => l_wf_parameter_list_t
1362 );
1363 wf_event.addparametertolist(
1364 p_name => 'AWARDING_PRD',
1365 p_value => p_award_prd,
1366 p_parameterlist => l_wf_parameter_list_t
1367 );
1368 wf_event.addparametertolist(
1369 p_name => 'STUDENT_NUMBER',
1370 p_value => p_person_number,
1371 p_parameterlist => l_wf_parameter_list_t
1372 );
1373 wf_event.addparametertolist(
1374 p_name => 'DETAILS',
1375 p_value => p_details,
1376 p_parameterlist => l_wf_parameter_list_t
1377 );
1378
1379 wf_Event.raise(
1380 p_event_name => lv_event_name,
1381 p_event_key => lv_event_name || l_seq_val,
1382 p_parameters => l_wf_parameter_list_t
1383 );
1384
1385 END submit_business_event;
1386
1387 FUNCTION get_acc_amt_display_mode_term( p_award_id NUMBER,
1388 p_accepted_amt NUMBER,
1389 p_offered_amt NUMBER,
1390 p_ld_cal_type VARCHAR2,
1391 p_ld_seq_num NUMBER)
1392 RETURN VARCHAR2 AS
1393 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
1394 lc_version_num igs_ps_ver_all.version_number%TYPE;
1395 lc_program_type igs_ps_ver_all.course_type%TYPE;
1396 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
1397
1398 l_last_accept_date DATE;
1399
1400 CURSOR c_get_base_id (cp_award_id NUMBER) IS
1401 SELECT base_id
1402 FROM igf_aw_award_all
1403 WHERE award_id = cp_award_id;
1404
1405 l_get_base_id c_get_base_id%ROWTYPE;
1406
1407
1408 CURSOR cur_fund_details ( cp_award_id NUMBER) IS
1409 SELECT awd.lock_award_flag lock_awd_flag, mast.*
1410 FROM IGF_AW_AWARD awd,
1411 IGF_AW_FUND_MAST mast
1412 WHERE awd.award_id = cp_award_id
1413 AND awd.fund_id = mast.fund_id;
1414 fund_mast_rec cur_fund_details%ROWTYPE;
1415
1416 CURSOR cur_min_disb_date ( cp_award_id NUMBER) IS
1417 SELECT MIN(disb_date)
1418 FROM IGF_AW_AWD_DISB
1419 WHERE award_id = cp_award_id;
1420
1421 sch_disb_date DATE;
1422
1423 CURSOR cur_fund_type (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
1424 SELECT cat.fed_fund_code
1425 FROM igf_aw_fund_mast_all fund,
1426 igf_aw_fund_cat_all cat
1427 WHERE fund.fund_code = cat.fund_code
1428 AND fund.fund_id = cp_fund_id;
1429
1430 l_fund_type igf_aw_fund_cat_v.fed_fund_code%TYPE;
1431
1432 CURSOR cur_orig_status_pell (cp_award_id NUMBER) IS
1433 SELECT orig_action_code
1434 FROM igf_gr_rfms
1435 WHERE award_id = cp_award_id;
1436
1437 l_orig_status_pell igf_gr_rfms.orig_action_code%TYPE;
1438
1439 CURSOR cur_orig_status (cp_award_id NUMBER) IS
1440 SELECT loan_status, loan_chg_status
1441 FROM igf_sl_loans_all
1442 WHERE award_id = cp_award_id;
1443
1444 l_orig_status cur_orig_status%ROWTYPE;
1445
1446 l_award_status igf_aw_award_all.award_status%TYPE;
1447
1448 BEGIN
1449
1450 OPEN c_get_base_id(p_award_id);
1451 FETCH c_get_base_id INTO l_get_base_id;
1452 CLOSE c_get_base_id;
1453
1454 -- Get context data for the first term.
1455 igf_ap_gen_001.get_context_data_for_term(
1456 l_get_base_id.base_id, --IN
1457 p_ld_cal_type, --IN
1458 p_ld_seq_num, --IN
1459 lc_program_cd, --OUT
1460 lc_version_num, --OUT
1461 lc_program_type, --OUT
1462 lc_org_unit --OUT
1463 );
1464
1465 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
1466 l_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
1467 'LAST_ACCEPT_DATE', --IN
1468 p_ld_cal_type, --IN
1469 p_ld_seq_num, --IN
1470 lc_org_unit, --IN
1471 lc_program_type, --IN
1472 lc_program_cd || '/' || lc_version_num--IN
1473 );
1474
1475 IF SYSDATE > l_last_accept_date THEN
1476 RETURN 'VIEW';
1477 END IF;
1478
1479 OPEN cur_fund_details(p_award_id);
1480 FETCH cur_fund_details INTO fund_mast_rec;
1481 CLOSE cur_fund_details;
1482
1483 -- If the award is locked at the award context level, display it as read-only
1484 IF fund_mast_rec.lock_awd_flag = 'Y' THEN
1485 RETURN 'VIEW';
1486 END IF;
1487
1488 -- Check If the view only check box is checked for the fund in fund manager form.
1489 --If the View only check box is checked then return V.
1490 IF NVL(fund_mast_rec.VIEW_ONLY_FLAG,'N') = 'Y' THEN
1491 RETURN 'VIEW';
1492 END IF;
1493
1494 -- Check the fund type.
1495 -- If the fund type is pell then check the origination status of the fund.
1496 -- If the origination staus is Sent then return V.
1497 -- If the fund type is Direct or FFELP then check the origination status of the fund.
1498 -- If the origination staus is Sent then return V.
1499 OPEN cur_fund_type(fund_mast_rec.fund_id);
1500 FETCH cur_fund_type INTO l_fund_type;
1501 CLOSE cur_fund_type;
1502 IF l_fund_type = 'PELL' THEN
1503 OPEN cur_orig_status_pell(p_award_id);
1504 FETCH cur_orig_status_pell INTO l_orig_status_pell;
1505 CLOSE cur_orig_status_pell;
1506 IF l_orig_status_pell = 'S' THEN
1507 RETURN 'VIEW';
1508 END IF;
1509 ELSIF l_fund_type IN ('DLU', 'DLP', 'DLS', 'FLP', 'FLU', 'FLS', 'ALT') THEN
1510 OPEN cur_orig_status(p_award_id);
1511 FETCH cur_orig_status INTO l_orig_status;
1512 CLOSE cur_orig_status;
1513 IF l_orig_status.loan_status = 'S' OR l_orig_status.loan_chg_status = 'S' THEN
1514 RETURN 'VIEW';
1515 END IF;
1516
1517 IF l_fund_type IN ('FLP', 'FLU', 'FLS', 'ALT') THEN
1518 IF l_orig_status.loan_status = 'A' AND NVL(l_orig_status.loan_chg_status, 'A') = 'A' THEN
1519 RETURN 'VIEW';
1520 END IF;
1521 END IF;
1522
1523 END IF;
1524
1525 l_award_status := igf_aw_ss_gen_pkg.get_term_award_status(p_award_id,p_ld_cal_type,p_ld_seq_num);
1526 /*
1527 If the "Allow Acceptance of Lesser Amount" checkbox IS NOT checked on the Fund Manager form,
1528 Student Self-Service tab, the student can only accept the entire amount or decline the entire
1529 amount of an award. The student cannot accept a lesser amount.
1530 The Accepted Amount should NOT be editable.
1531 */
1532
1533 IF l_award_status = 'CANCELLED' THEN
1534 RETURN 'VIEW';
1535 END IF;
1536
1537 IF NVL(fund_mast_rec.accept_less_amt_flag,'N') <> 'Y' THEN
1538 RETURN 'VIEW';
1539 END IF;
1540
1541 IF (NVL(fund_mast_rec.allow_inc_post_accept_flag,'N') <> 'Y') AND (NVL(fund_mast_rec.allow_dec_post_accept_flag,'N') <> 'Y') THEN
1542 IF l_award_status = 'ACCEPTED' THEN
1543 RETURN 'VIEW';
1544 END IF;
1545 END IF;
1546
1547 /*
1548 If the "View-only After Decline" radio button IS selected on the Fund Manager form,
1549 Student Self-Service tab, then when a student declines an award via student self-service,
1550 make the award view-only on the Fund Manager form, Student Self-Service tab.
1551 This award will still be picked up during the repackaging process.
1552
1553 If the "View-only After Decline" radio button IS NOT selected on the Fund Manager form,
1554 Student Self-Service tab, then when a student declines an award via student self-service,
1555 the award remains editable. This award will still be picked up during the repackaging process.
1556 */
1557
1558 IF l_award_status = 'DECLINED' AND NVL(fund_mast_rec.status_after_decline,'VIEW') = 'VIEW' THEN
1559 RETURN 'VIEW';
1560 END IF;
1561
1562 RETURN 'MODIFY';
1563
1564 END get_acc_amt_display_mode_term;
1565
1566 FUNCTION get_acc_display_mode_term( p_award_id NUMBER,
1567 p_accepted_amt NUMBER,
1568 p_offered_amt NUMBER,
1569 p_ld_cal_type VARCHAR2,
1570 p_ld_seq_num NUMBER)
1571 RETURN VARCHAR2 AS
1572 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
1573 lc_version_num igs_ps_ver_all.version_number%TYPE;
1574 lc_program_type igs_ps_ver_all.course_type%TYPE;
1575 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
1576
1577 l_last_accept_date DATE;
1578
1579 CURSOR c_get_base_id (cp_award_id NUMBER) IS
1580 SELECT base_id
1581 FROM igf_aw_award_all
1582 WHERE award_id = cp_award_id;
1583
1584 l_get_base_id c_get_base_id%ROWTYPE;
1585
1586 CURSOR cur_fund_details (cp_award_id NUMBER) IS
1587 SELECT awd.lock_award_flag lock_awd_flag, mast.*
1588 FROM IGF_AW_AWARD awd,
1589 IGF_AW_FUND_MAST mast
1590 WHERE awd.award_id = cp_award_id
1591 AND awd.fund_id = mast.fund_id;
1592 fund_mast_rec cur_fund_details%ROWTYPE;
1593
1594 CURSOR cur_min_disb_date (cp_award_id NUMBER) IS
1595 SELECT MIN(disb_date)
1596 FROM IGF_AW_AWD_DISB
1597 WHERE award_id = cp_award_id;
1598
1599 sch_disb_date DATE;
1600
1601 CURSOR cur_fund_type (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
1602 SELECT cat.fed_fund_code
1603 FROM igf_aw_fund_mast_all fund,
1604 igf_aw_fund_cat_all cat
1605 WHERE fund.fund_code = cat.fund_code
1606 AND fund.fund_id = cp_fund_id;
1607
1608 l_fund_type igf_aw_fund_cat_v.fed_fund_code%TYPE;
1609
1610 CURSOR cur_orig_status_pell (cp_award_id NUMBER) IS
1611 SELECT orig_action_code
1612 FROM igf_gr_rfms
1613 WHERE award_id = cp_award_id;
1614
1615 l_orig_status_pell igf_gr_rfms.orig_action_code%TYPE;
1616
1617 CURSOR cur_orig_status (cp_award_id NUMBER) IS
1618 SELECT loan_status, loan_chg_status
1619 FROM igf_sl_loans_all
1620 WHERE award_id = cp_award_id;
1621
1622 l_orig_status cur_orig_status%ROWTYPE;
1623
1624 l_award_status igf_aw_award_all.award_status%TYPE;
1625
1626 BEGIN
1627
1628 OPEN c_get_base_id(p_award_id);
1629 FETCH c_get_base_id INTO l_get_base_id;
1630 CLOSE c_get_base_id;
1631
1632 -- Get context data for the first term.
1633 igf_ap_gen_001.get_context_data_for_term(
1634 l_get_base_id.base_id, --IN
1635 p_ld_cal_type, --IN
1636 p_ld_seq_num, --IN
1637 lc_program_cd, --OUT
1638 lc_version_num, --OUT
1639 lc_program_type, --OUT
1640 lc_org_unit --OUT
1641 );
1642
1643 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
1644 l_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
1645 'LAST_ACCEPT_DATE', --IN
1646 p_ld_cal_type, --IN
1647 p_ld_seq_num, --IN
1648 lc_org_unit, --IN
1649 lc_program_type, --IN
1650 lc_program_cd || '/' || lc_version_num--IN
1651 );
1652
1653 IF SYSDATE > l_last_accept_date THEN
1654 RETURN 'VW';
1655 END IF;
1656
1657
1658 OPEN cur_fund_details(p_award_id);
1659 FETCH cur_fund_details INTO fund_mast_rec;
1660 CLOSE cur_fund_details;
1661
1662 -- If the award is locked at the award context level, display it as read-only
1663 IF fund_mast_rec.lock_awd_flag = 'Y' THEN
1664 RETURN 'VW';
1665 END IF;
1666
1667 -- Check If the view only check box is checked for the fund in fund manager form.
1668 --If the View only check box is checked then return V.
1669 IF NVL(fund_mast_rec.VIEW_ONLY_FLAG,'N') = 'Y' THEN
1670 RETURN 'VW';
1671 END IF;
1672
1673 -- Check the fund type.
1674 -- If the fund type is pell then check the origination status of the fund.
1675 -- If the origination staus is Sent then return V.
1676 -- If the fund type is Direct or FFELP then check the origination status of the fund.
1677 -- If the origination staus is Sent then return V.
1678 OPEN cur_fund_type(fund_mast_rec.fund_id);
1679 FETCH cur_fund_type INTO l_fund_type;
1680 CLOSE cur_fund_type;
1681
1682 IF l_fund_type = 'PELL' THEN
1683 OPEN cur_orig_status_pell(p_award_id);
1684 FETCH cur_orig_status_pell INTO l_orig_status_pell;
1685 CLOSE cur_orig_status_pell;
1686 IF l_orig_status_pell = 'S' THEN
1687 RETURN 'VW';
1688 END IF;
1689 ELSIF l_fund_type IN ('DLU', 'DLP', 'DLS', 'FLP', 'FLU', 'FLS', 'ALT') THEN
1690 OPEN cur_orig_status(p_award_id);
1691 FETCH cur_orig_status INTO l_orig_status;
1692 CLOSE cur_orig_status;
1693 IF l_orig_status.loan_status = 'S' OR l_orig_status.loan_chg_status = 'S' THEN
1694 RETURN 'VW';
1695 END IF;
1696
1697 IF l_fund_type IN ('FLP', 'FLU', 'FLS', 'ALT') THEN
1698 IF l_orig_status.loan_status = 'A' AND NVL(l_orig_status.loan_chg_status, 'A') = 'A' THEN
1699 RETURN 'VW';
1700 END IF;
1701 END IF;
1702
1703 END IF;
1704
1705 l_award_status := igf_aw_ss_gen_pkg.get_term_award_status(p_award_id,p_ld_cal_type,p_ld_seq_num);
1706
1707 IF l_award_status = 'CANCELLED' THEN
1708 RETURN 'VW';
1709 END IF;
1710
1711 /*
1712 If the "Allow Increases To Accepted Amount After Acceptance" checkbox IS NOT checked
1713 and the checkbox for "Allow Decreases To Accepted Amount After Acceptance" IS NOT checked
1714 on the Fund Manager form, Student Self-Service tab,
1715 then after the award goes into accepted status, disable the Accept radio button
1716 and the Accepted Amount field to prevent further updates
1717 */
1718
1719 IF (NVL(fund_mast_rec.allow_inc_post_accept_flag,'N') <> 'Y') AND (NVL(fund_mast_rec.allow_dec_post_accept_flag,'N') <> 'Y') THEN
1720 IF l_award_status = 'ACCEPTED' THEN
1721 RETURN 'VW';
1722 END IF;
1723 END IF;
1724
1725
1726 /*
1727 If the "View-only After Decline" radio button IS selected on the Fund Manager form,
1728 Student Self-Service tab, then when a student declines an award via student self-service,
1729 make the award view-only on the Fund Manager form, Student Self-Service tab.
1730 This award will still be picked up during the repackaging process.
1731
1732 If the "View-only After Decline" radio button IS NOT selected on the Fund Manager form,
1733 Student Self-Service tab, then when a student declines an award via student self-service,
1734 the award remains editable. This award will still be picked up during the repackaging process.
1735 */
1736
1737 IF l_award_status = 'DECLINED' AND NVL(fund_mast_rec.status_after_decline,'VIEW') = 'VIEW' THEN
1738 RETURN 'VW';
1739 END IF;
1740
1741 RETURN 'MD';
1742 END get_acc_display_mode_term;
1743
1744 FUNCTION get_dec_display_mode_term(p_award_id NUMBER,
1745 p_accepted_amt NUMBER,
1746 p_offered_amt NUMBER,
1747 p_ld_cal_type VARCHAR2,
1748 p_ld_seq_num NUMBER)
1749 RETURN VARCHAR2 AS
1750
1751 lc_program_cd igs_ps_ver_all.course_cd%TYPE;
1752 lc_version_num igs_ps_ver_all.version_number%TYPE;
1753 lc_program_type igs_ps_ver_all.course_type%TYPE;
1754 lc_org_unit igs_ps_ver_all.responsible_org_unit_cd%TYPE;
1755
1756 l_last_accept_date DATE;
1757
1758 CURSOR c_get_base_id (cp_award_id NUMBER) IS
1759 SELECT base_id
1760 FROM igf_aw_award_all
1761 WHERE award_id = cp_award_id;
1762
1763 l_get_base_id c_get_base_id%ROWTYPE;
1764
1765 CURSOR cur_fund_details (cp_award_id NUMBER) IS
1766 SELECT awd.lock_award_flag lock_awd_flag, mast.*
1767 FROM IGF_AW_AWARD awd,
1768 IGF_AW_FUND_MAST mast
1769 WHERE awd.award_id = cp_award_id
1770 AND awd.fund_id = mast.fund_id;
1771 fund_mast_rec cur_fund_details%ROWTYPE;
1772
1773 CURSOR cur_min_disb_date (cp_award_id NUMBER) IS
1774 SELECT MIN(disb_date)
1775 FROM IGF_AW_AWD_DISB
1776 WHERE award_id = cp_award_id;
1777
1778 sch_disb_date DATE;
1779
1780 CURSOR cur_fund_type (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
1781 SELECT cat.fed_fund_code
1782 FROM igf_aw_fund_mast_all fund,
1783 igf_aw_fund_cat_all cat
1784 WHERE fund.fund_code = cat.fund_code
1785 AND fund.fund_id = cp_fund_id;
1786
1787 l_fund_type igf_aw_fund_cat_v.fed_fund_code%TYPE;
1788
1789 CURSOR cur_orig_status_pell (cp_award_id NUMBER) IS
1790 SELECT orig_action_code
1791 FROM igf_gr_rfms
1792 WHERE award_id = cp_award_id;
1793
1794 l_orig_status_pell igf_gr_rfms.orig_action_code%TYPE;
1795
1796 CURSOR cur_orig_status (cp_award_id NUMBER) IS
1797 SELECT loan_status,loan_chg_status
1798 FROM igf_sl_loans_all
1799 WHERE award_id = cp_award_id;
1800
1801 l_orig_status cur_orig_status%ROWTYPE;
1802
1803 CURSOR cur_award_details (cp_award_id NUMBER) IS
1804 SELECT *
1805 FROM IGF_AW_AWARD awd
1806 WHERE awd.award_id = cp_award_id;
1807
1808 --rec_award_status igf_aw_award.AWARD_STATUS%TYPE;
1809 rec_award_details cur_award_details%ROWTYPE;
1810
1811 l_award_status igf_aw_award_all.award_status%TYPE;
1812
1813 BEGIN
1814
1815 OPEN c_get_base_id(p_award_id);
1816 FETCH c_get_base_id INTO l_get_base_id;
1817 CLOSE c_get_base_id;
1818
1819 -- Get context data for the first term.
1820 igf_ap_gen_001.get_context_data_for_term(
1821 l_get_base_id.base_id, --IN
1822 p_ld_cal_type, --IN
1823 p_ld_seq_num, --IN
1824 lc_program_cd, --OUT
1825 lc_version_num, --OUT
1826 lc_program_type, --OUT
1827 lc_org_unit --OUT
1828 );
1829
1830 --Get the Last Accept Date by using the API igs_ca_compute_da_val_pkg.cal_da_elt_val
1831 l_last_accept_date := igs_ca_compute_da_val_pkg.cal_da_elt_val(
1832 'LAST_ACCEPT_DATE', --IN
1833 p_ld_cal_type, --IN
1834 p_ld_seq_num, --IN
1835 lc_org_unit, --IN
1836 lc_program_type, --IN
1837 lc_program_cd || '/' || lc_version_num --IN
1838 );
1839
1840 IF SYSDATE > l_last_accept_date THEN
1841 RETURN 'V';
1842 END IF;
1843
1844
1845 OPEN cur_fund_details(p_award_id);
1846 FETCH cur_fund_details INTO fund_mast_rec;
1847 CLOSE cur_fund_details;
1848
1849 -- If the award is locked at the award context level, display it as read-only
1850 IF fund_mast_rec.lock_awd_flag = 'Y' THEN
1851 RETURN 'V';
1852 END IF;
1853
1854 -- Check If the view only check box is checked for the fund in fund manager form.
1855 --If the View only check box is checked then return V.
1856 IF NVL(fund_mast_rec.VIEW_ONLY_FLAG,'N') = 'Y' THEN
1857 RETURN 'V';
1858 END IF;
1859
1860 -- Check the fund type.
1861 -- If the fund type is pell then check the origination status of the fund.
1862 -- If the origination staus is Sent then return V.
1863 -- If the fund type is Direct or FFELP then check the origination status of the fund.
1864 -- If the origination staus is Sent then return V.
1865 OPEN cur_fund_type(fund_mast_rec.fund_id);
1866 FETCH cur_fund_type INTO l_fund_type;
1867 CLOSE cur_fund_type;
1868
1869 IF l_fund_type = 'PELL' THEN
1870 OPEN cur_orig_status_pell(p_award_id);
1871 FETCH cur_orig_status_pell INTO l_orig_status_pell;
1872 CLOSE cur_orig_status_pell;
1873 IF l_orig_status_pell = 'S' THEN
1874 RETURN 'V';
1875 END IF;
1876 ELSIF l_fund_type IN ('DLU', 'DLP', 'DLS', 'FLP', 'FLU', 'FLS', 'ALT') THEN
1877 OPEN cur_orig_status(p_award_id);
1878 FETCH cur_orig_status INTO l_orig_status;
1879 CLOSE cur_orig_status;
1880 IF l_orig_status.loan_status = 'S' OR l_orig_status.loan_chg_status = 'S' THEN
1881 RETURN 'V';
1882 END IF;
1883
1884 IF l_fund_type IN ('FLP', 'FLU', 'FLS', 'ALT') THEN
1885 IF l_orig_status.loan_status = 'A' AND NVL(l_orig_status.loan_chg_status, 'A') = 'A' THEN
1886 RETURN 'V';
1887 END IF;
1888 END IF;
1889
1890 END IF;
1891
1892 /*
1893 If the "Allow Decline After Accept" checkbox IS selected on the Fund Manager form,
1894 Student Self-Service tab, then if the award has an accepted status,
1895 the Decline radio button for that award will be enabled.
1896
1897 If the "Allow Decline After Accept" checkbox IS NOT selected on the Fund Manager form,
1898 Student Self-Service tab, then if the award has an accepted status,
1899 the Decline radio button for that award will NOT be enabled.
1900
1901 Note: An award cannot be declined once any part of it has been 'Paid'
1902 (i.e. if any part of the award is paid, then the decline radio button for that
1903 award should not be enabled).
1904 */
1905
1906 l_award_status := igf_aw_ss_gen_pkg.get_term_award_status(p_award_id,p_ld_cal_type,p_ld_seq_num);
1907
1908 OPEN cur_award_details(p_award_id);
1909 FETCH cur_award_details INTO rec_award_details;
1910 CLOSE cur_award_details;
1911
1912 IF l_award_status = 'ACCEPTED' THEN
1913 IF (NVL(fund_mast_rec.ALLOW_DECLN_POST_ACCEPT_FLAG,'N') <> 'Y') THEN
1914 RETURN 'V';
1915 END IF;
1916
1917 IF NVL(rec_award_details.PAID_AMT,0) > 0 THEN
1918 RETURN 'V';
1919 END IF;
1920 END IF;
1921
1922 IF l_award_status = 'CANCELLED' THEN
1923 RETURN 'V';
1924 END IF;
1925
1926 /*
1927 If the "View-only After Decline" radio button IS selected on the Fund Manager form,
1928 Student Self-Service tab, then when a student declines an award via student self-service,
1929 make the award view-only on the Fund Manager form, Student Self-Service tab.
1930 This award will still be picked up during the repackaging process.
1931
1932 If the "View-only After Decline" radio button IS NOT selected on the Fund Manager form,
1933 Student Self-Service tab, then when a student declines an award via student self-service,
1934 the award remains editable. This award will still be picked up during the repackaging process.
1935
1936 In either case, if the award is declined, it is made read-only.
1937 */
1938
1939 IF l_award_status = 'DECLINED' AND NVL(fund_mast_rec.status_after_decline,'VIEW') = 'VIEW' THEN
1940 RETURN 'V';
1941 END IF;
1942
1943 RETURN 'M';
1944
1945 END get_dec_display_mode_term;
1946
1947 FUNCTION get_term_award_status (p_award_id NUMBER,
1948 p_ld_cal_type VARCHAR2,
1949 p_ld_seq_num NUMBER)
1950 RETURN VARCHAR2 AS
1951
1952 l_accepted_amt NUMBER;
1953 l_off_acc VARCHAR2(2);
1954
1955 CURSOR c_get_disb_award_status (cp_award_id NUMBER,
1956 cp_ld_cal_type VARCHAR2,
1957 cp_ld_seq_num NUMBER) IS
1958 SELECT award_id, disb_num, trans_type, disb_accepted_amt
1959 FROM igf_aw_awd_disb_all
1960 WHERE award_id = cp_award_id
1961 AND ld_cal_type = cp_ld_cal_type
1962 AND ld_sequence_number = cp_ld_seq_num;
1963
1964 BEGIN
1965
1966 l_accepted_amt := 0;
1967 l_off_acc := 'N';
1968
1969 FOR l_get_disb_award_status IN c_get_disb_award_status(p_award_id, p_ld_cal_type, p_ld_seq_num) LOOP
1970 l_accepted_amt := l_accepted_amt + l_get_disb_award_status.disb_accepted_amt;
1971
1972 IF l_get_disb_award_status.trans_type = 'A' THEN
1973 RETURN 'ACCEPTED';
1974 ELSIF l_get_disb_award_status.trans_type = 'P' THEN
1975 l_off_acc := 'Y';
1976 END IF;
1977 END LOOP;
1978
1979 IF l_off_acc = 'Y' THEN
1980 IF l_accepted_amt > 0 THEN
1981 RETURN 'ACCEPTED';
1982 ELSE
1983 RETURN 'OFFERED';
1984 END IF;
1985 END IF;
1986
1987 RETURN 'DECLINED';
1988
1989 END get_term_award_status;
1990
1991
1992 END IGF_AW_SS_GEN_PKG;
[Home] [Help]