[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_FISAP_PKG
Source
1 PACKAGE BODY IGF_AW_FISAP_PKG AS
2 /* $Header: IGFAW22B.pls 120.9 2006/05/05 00:55:17 veramach 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 (reverse chronological order - newest change first)
12 ***************************************************************/
13
14 FUNCTION get_lookup_meaning ( p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2)
15 RETURN VARCHAR2 IS
16
17 CURSOR cur_igf_lookup ( cp_lookup_type IN VARCHAR2, cp_lookup_code IN VARCHAR2) IS
18 SELECT lkup.meaning
19 FROM IGF_LOOKUPS_VIEW lkup
20 WHERE lkup.lookup_type = cp_lookup_type
21 AND lkup.lookup_code = cp_lookup_code
22 AND lkup.enabled_flag = 'Y';
23
24 CURSOR cur_igs_lookup ( cp_lookup_type IN VARCHAR2, cp_lookup_code IN VARCHAR2) IS
25 SELECT lkup.meaning
26 FROM IGS_LOOKUPS_VIEW lkup
27 WHERE lkup.lookup_type = cp_lookup_type
28 AND lkup.lookup_code = cp_lookup_code
29 AND lkup.enabled_flag = 'Y';
30
31 lv_meaning VARCHAR2(80);
32
33 BEGIN
34 IF p_lookup_type IS NULL OR p_lookup_code IS NULL THEN
35 RETURN NULL;
36 END IF;
37
38 OPEN cur_igf_lookup(p_lookup_type, p_lookup_code);
39 FETCH cur_igf_lookup INTO lv_meaning;
40 IF cur_igf_lookup%NOTFOUND THEN
41 CLOSE cur_igf_lookup;
42
43 OPEN cur_igs_lookup(p_lookup_type, p_lookup_code);
44 FETCH cur_igs_lookup INTO lv_meaning;
45 CLOSE cur_igs_lookup;
46 ELSE
47 CLOSE cur_igf_lookup;
48 END IF;
49
50 RETURN lv_meaning;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
55 fnd_message.set_token('NAME','IGF_AW_FISAP.GET_LOOKUP_MEANING');
56 igs_ge_msg_stack.add;
57 app_exception.raise_exception;
58 END get_lookup_meaning;
59
60 PROCEDURE log_input_parameters ( lv_cal_type IN VARCHAR2,
61 ln_seq_number IN NUMBER,
62 p_retain_prev_batches IN VARCHAR2,
63 p_descrption IN VARCHAR2
64 )
65 IS
66 l_msg_str VARCHAR2(256);
67 BEGIN
68
69 -- show heading
70 l_msg_str := TRIM(get_lookup_meaning('IGF_GE_PARAMETERS', 'PARAMETER_PASS'));
71
72 fnd_file.new_line(fnd_file.log,1);
73 fnd_file.put_line(fnd_file.log, l_msg_str); --------------Parameters Passed--------------
74 fnd_file.new_line(fnd_file.log,1);
75
76 -- show award year
77 l_msg_str := RPAD(get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_YEAR'),30) ||
78 RPAD(igf_gr_gen.get_alt_code(lv_cal_type,ln_seq_number),20);
79 fnd_file.put_line(fnd_file.log,l_msg_str);
80
81 -- show retain previous batches
82 IF (p_retain_prev_batches IS NOT NULL) THEN
83 l_msg_str := RPAD(get_lookup_meaning('IGF_GE_PARAMETERS', 'RETAIN_PREV_BATCHES'),30) ||
84 RPAD(get_lookup_meaning('IGF_AP_YES_NO', p_retain_prev_batches),20);
85 fnd_file.put_line(fnd_file.log,l_msg_str);
86 END IF;
87
88 -- show description
89 IF (p_descrption IS NOT NULL) THEN
90 l_msg_str := RPAD(get_lookup_meaning('IGF_GE_PARAMETERS', 'DESCRPTION'),30) || p_descrption;
91 fnd_file.put_line(fnd_file.log,l_msg_str);
92 END IF;
93
94 fnd_file.new_line(fnd_file.log,1);
95 fnd_file.put_line(fnd_file.log, '--------------------------------------------------------');
96 fnd_file.new_line(fnd_file.log,1);
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
101 fnd_message.set_token('NAME','IGF_AW_FISAP.LOG_INPUT_PARAMETERS');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 END log_input_parameters;
105
106 FUNCTION is_eligible_aid_applicant (p_base_id IN NUMBER)
107 RETURN BOOLEAN IS
108
109 CURSOR cur_fa_program(cp_course_cd IN VARCHAR2, cp_version_number IN NUMBER) IS
110 SELECT federal_financial_aid
111 FROM IGS_PS_VER_V
112 WHERE course_cd = cp_course_cd
113 AND version_number = cp_version_number;
114 rec_fa_program cur_fa_program%ROWTYPE;
115
116 CURSOR cur_citizenship_requirement(cp_base_id IN NUMBER) IS
117 SELECT DECODE(ins_match_flag, 'Y', 'Y', 'N') primary_dhs,
118 DECODE(sec_ins_match_flag, 'Y', 'Y', 'N') sec_dhs
119 FROM IGF_AP_ISIR_MATCHED
120 WHERE base_id = cp_base_id
121 AND active_isir = 'Y';
122 rec_citizenship_requirement cur_citizenship_requirement%ROWTYPE;
123
124 lv_course_cd VARCHAR2(30);
125 ln_version_number NUMBER(12);
126
127 BEGIN
128 -- 1st Condition
129 -- Was enrolled in an academic or training program eligible for the campus-based program
130 -- get student's key program and its version
131 igf_ap_gen_001.get_key_program(p_base_id, lv_course_cd, ln_version_number);
132
133 -- Check whether this program is FA Program with Federal Indicator Checked or not
134 OPEN cur_fa_program(lv_course_cd, ln_version_number);
135 FETCH cur_fa_program INTO rec_fa_program;
136 CLOSE cur_fa_program;
137
138 IF (rec_fa_program.federal_financial_aid IS NULL OR rec_fa_program.federal_financial_aid = 'N') THEN
139 RETURN FALSE;
140 END IF;
141
142 -- 3rd Condition
143 -- Applied for Financial Aid for award year.
144 OPEN cur_citizenship_requirement(p_base_id);
145 FETCH cur_citizenship_requirement INTO rec_citizenship_requirement;
146 IF cur_citizenship_requirement%NOTFOUND THEN
147 -- No active isir record found
148 CLOSE cur_citizenship_requirement;
149 RETURN FALSE;
150 ELSE
151 -- 3rd condition passed.
152 -- Now, 2nd condition. Met citizenship or residency requirements for award year?
153 IF NVL(rec_citizenship_requirement.primary_dhs, 'N') = 'N' AND NVL(rec_citizenship_requirement.sec_dhs, 'N') = 'N' THEN
154 -- Neither primary nor secondary DHS Verification flag on the official ISIR is 'Y'
155 CLOSE cur_citizenship_requirement;
156 RETURN FALSE;
157 END IF;
158 CLOSE cur_citizenship_requirement;
159 END IF;
160
161 -- All conditions satisfied. He/she is eligible aid applicant
162 RETURN TRUE;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
167 fnd_message.set_token('NAME','IGF_AW_FISAP.IS_ELIGIBLE_AID_APPLICANT');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END is_eligible_aid_applicant;
171
172 FUNCTION get_student_career_level (p_base_id IN NUMBER)
173 RETURN VARCHAR2 IS
174
175 CURSOR cur_fa_prog_type(cp_course_cd IN VARCHAR2, cp_version_number IN NUMBER) IS
176 SELECT cty.fin_aid_program_type
177 FROM IGS_PS_TYPE_ALL cty,
178 IGS_PS_VER crv
179 WHERE crv.course_type = cty.course_type
180 AND crv.course_cd = cp_course_cd
181 AND crv.version_number = cp_version_number;
182 rec_fa_prog_type cur_fa_prog_type%ROWTYPE;
183
184 CURSOR cur_earned_degrees(cp_person_id NUMBER, cp_fa_program_type VARCHAR2) IS
185 SELECT ptype.fin_aid_program_type
186 FROM IGS_AD_ACAD_HISTORY_V acadhist,
187 IGS_PS_DEGREES degree,
188 IGS_PS_TYPE_ALL ptype
189 WHERE acadhist.degree_earned = degree.degree_cd
190 AND degree.program_type = ptype.course_type
191 AND acadhist.person_id = cp_person_id
192 AND ptype.fin_aid_program_type = cp_fa_program_type;
193 rec_earned_degrees cur_earned_degrees%ROWTYPE;
194
195 CURSOR cur_completed_prog(cp_person_id IN NUMBER) IS
196 SELECT course_cd
197 FROM IGS_EN_STDNT_PS_ATT_ALL
198 WHERE course_attempt_status = 'COMPLETED'
199 AND person_id = cp_person_id;
200 rec_completed_prog cur_completed_prog%ROWTYPE;
201
202 CURSOR cur_completed_ugprog(cp_person_id NUMBER, cp_fa_program_type VARCHAR2) IS
203 SELECT cty.fin_aid_program_type
204 FROM IGS_EN_STDNT_PS_ATT_ALL statt,
205 IGS_PS_TYPE_ALL cty,
206 IGS_PS_VER crv
207 WHERE crv.course_cd = statt.course_cd
208 AND crv.version_number = statt.version_number
209 AND crv.course_type = cty.course_type
210 AND statt.course_attempt_status = 'COMPLETED'
211 AND statt.person_id = cp_person_id
212 AND cty.fin_aid_program_type = cp_fa_program_type;
213 rec_completed_ugprog cur_completed_ugprog%ROWTYPE;
214
215 CURSOR cur_get_personid(cp_base_id IN NUMBER) IS
216 SELECT person_id
217 FROM IGF_AP_FA_CON_V
218 WHERE base_id = cp_base_id;
219
220 lv_course_cd VARCHAR2(30);
221 ln_version_number NUMBER(12);
222 lv_career_level VARCHAR2(30);
223 ln_person_id NUMBER(12);
224
225 BEGIN
226
227 lv_career_level := NULL;
228
229 OPEN cur_get_personid(p_base_id);
230 FETCH cur_get_personid INTO ln_person_id;
231 CLOSE cur_get_personid;
232
233 igf_ap_gen_001.get_key_program(p_base_id, lv_course_cd, ln_version_number);
234
235 OPEN cur_fa_prog_type(lv_course_cd, ln_version_number);
236 FETCH cur_fa_prog_type INTO rec_fa_prog_type;
237 CLOSE cur_fa_prog_type;
238
239 -- if student's key-program's FA program Type is Professional
240 IF rec_fa_prog_type.fin_aid_program_type = 'PROFESSIONAL' THEN
241 -- career level = "Grad/Prof"
242 lv_career_level := 'GRAD_PROF';
243 ELSE
244 -- key-program's FA program type is Bachelors or Pre-bachelors
245
246 -- if any of earned degrees is Graduate/Professional in acad history.
247 rec_earned_degrees := NULL;
248 OPEN cur_earned_degrees(ln_person_id, 'PROFESSIONAL');
249 FETCH cur_earned_degrees INTO rec_earned_degrees;
250 CLOSE cur_earned_degrees;
251 IF rec_earned_degrees.fin_aid_program_type IS NOT NULL THEN
252 -- career level = "Graduate or Professional"
253 lv_career_level := 'GRAD_PROF';
254 ELSE -- if any of earned degrees is Bachelors in acad history.
255 rec_earned_degrees := NULL;
256 OPEN cur_earned_degrees(ln_person_id, 'BACHELORS');
257 FETCH cur_earned_degrees INTO rec_earned_degrees;
258 CLOSE cur_earned_degrees;
259 IF rec_earned_degrees.fin_aid_program_type IS NOT NULL THEN
260 -- career level = "UG with degree"
261 lv_career_level := 'UG_WITH';
262 ELSE -- all earned degrees are Pre-bachelors in acad history.
263 -- if there are no COMPLETED programs (in Student Attempt Programs).
264 OPEN cur_completed_prog(ln_person_id);
265 FETCH cur_completed_prog INTO rec_completed_prog;
266 IF cur_completed_prog%NOTFOUND THEN
267 -- career level = "UG without degree"
268 CLOSE cur_completed_prog;
269 lv_career_level := 'UG_WOUT';
270 ELSE
271 CLOSE cur_completed_prog;
272
273 -- if any COMPLETED program is Graduate/Professional
274 OPEN cur_completed_ugprog(ln_person_id, 'PROFESSIONAL');
275 FETCH cur_completed_ugprog INTO rec_completed_ugprog;
276 IF cur_completed_ugprog%NOTFOUND THEN
277 -- No Graduate/Professional programs.
278 CLOSE cur_completed_ugprog;
279 -- Check for Bachelors
280 OPEN cur_completed_ugprog(ln_person_id, 'BACHELORS');
281 FETCH cur_completed_ugprog INTO rec_completed_ugprog;
282 IF cur_completed_ugprog%NOTFOUND THEN
283 -- means there are COMPLETED programs but all are pre-bachelors
284 -- career level = "UG without degree"
285 CLOSE cur_completed_ugprog;
286 lv_career_level := 'UG_WOUT';
287 ELSE
288 -- career level = "UG with degree"
289 CLOSE cur_completed_ugprog;
290 lv_career_level := 'UG_WITH';
291 END IF;
292 ELSE
293 -- career level = "Graduate or Professional"
294 CLOSE cur_completed_ugprog;
295 lv_career_level := 'GRAD_PROF';
296 END IF;
297 END IF;
298 END IF;
299 END IF;
300 END IF;
301
302 RETURN lv_career_level;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
307 fnd_message.set_token('NAME','IGF_AW_FISAP.GET_STUDENT_CAREER_LEVEL');
308 igs_ge_msg_stack.add;
309 app_exception.raise_exception;
310 END get_student_career_level;
311
312 FUNCTION get_student_enrollment_status (p_base_id IN NUMBER)
313 RETURN VARCHAR2 IS
314
315 CURSOR cur_get_personid(cp_base_id IN NUMBER) IS
316 SELECT person_id
317 FROM IGF_AP_FA_CON_V
318 WHERE base_id = cp_base_id;
319
320 term_enr_dtl_rec igs_en_spa_terms%ROWTYPE;
321 attendance_type VARCHAR2(100);
322 ln_person_id NUMBER(15);
323 BEGIN
324 -- get person id for the base id
325 OPEN cur_get_personid(p_base_id);
326 FETCH cur_get_personid INTO ln_person_id;
327 CLOSE cur_get_personid;
328
329 -- Get term enrollment details record
330 -- Get course code and term calendar type and its sequence number
331 igf_ap_gen_001.get_term_enrlmnt_dtl(p_base_id, term_enr_dtl_rec);
332
333 -- Pass on these-three to get attendance type
334 attendance_type := igs_en_prc_load.enrp_get_prg_att_type ( ln_person_id,
335 term_enr_dtl_rec.program_cd,
336 term_enr_dtl_rec.term_cal_type,
337 term_enr_dtl_rec.term_sequence_number
338 );
339 RETURN attendance_type;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
344 fnd_message.set_token('NAME','IGF_AW_FISAP.GET_STUDENT_ENROLLMENT_STATUS');
345 igs_ge_msg_stack.add;
346 app_exception.raise_exception;
347 END get_student_enrollment_status;
348
349 PROCEDURE submit_fisap_event ( p_cal_type IN VARCHAR2, p_seq_number IN NUMBER, p_batch_id IN NUMBER)
350 IS
351 /*************************************************************
352 Created By : ugummall
353 Date Created On : 2004/10/04
354 Purpose :
355 Know limitations, enhancements or remarks
356 Change History
357 Who When What
358 (reverse chronological order - newest change first)
359 ***************************************************************/
360
361 l_parameter_list wf_parameter_list_t;
362 l_event_name VARCHAR2(255);
363 l_event_key NUMBER;
364 l_c_user_name fnd_user.user_name%TYPE;
365 l_batch_id NUMBER;
366
367 CURSOR cur_sequence IS SELECT IGF_GR_PELL_GEN_XML_S.NEXTVAL FROM DUAL;
368
369 BEGIN
370
371 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
372 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap.submit_fisap_event','p_batch_id : ' || p_batch_id);
373 END IF;
374
375 l_parameter_list := wf_parameter_list_t();
376 l_event_name := 'oracle.apps.igf.aw.fisap';
377 l_c_user_name := fnd_global.user_name;
378 l_batch_id := p_batch_id;
379
380 OPEN cur_sequence;
381 FETCH cur_sequence INTO l_event_key;
382 CLOSE cur_sequence;
383
384 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
385 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap.submit_fisap_event','l_event_key : '||l_event_key);
386 END IF;
387
388 -- Now add the parameters to the list to be passed to the workflow
389
390 wf_event.addparametertolist(
391 p_name => 'USER_NAME',
392 p_value => l_c_user_name,
393 p_parameterlist => l_parameter_list
394 );
395 wf_event.addparametertolist(
396 p_name => 'FISAP_DATA',
397 p_value => p_cal_type || ':' || p_seq_number,
398 p_parameterlist => l_parameter_list
399 );
400 wf_event.addparametertolist(
401 p_name => 'BATCH_ID_PARAMETER',
402 p_value => l_batch_id,
403 p_parameterlist => l_parameter_list
404 );
405
406 wf_event.RAISE (
407 p_event_name => l_event_name,
408 p_event_key => l_event_key,
409 p_parameters => l_parameter_list);
410
411 fnd_message.set_name('IGF','IGF_AW_FISAP_RAISE_EVENT');
412 fnd_message.set_token('EVENT_KEY_VALUE',l_event_key);
413 fnd_file.new_line(fnd_file.log,1);
414 fnd_file.put_line(fnd_file.log,fnd_message.get);
415 fnd_file.new_line(fnd_file.log,1);
416
417 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
418 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap.submit_fisap_event','raised event ');
419 END IF;
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
424 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_fisap.submit_fisap_event.exception', 'Exception: ' || SQLERRM);
425 END IF;
426 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
427 fnd_message.set_token('NAME','IGF_AW_FISAP.SUBMIT_FISAP_EVENT');
428 igs_ge_msg_stack.add;
429 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
430 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap.submit_fisap_event.debug', 'SQLERRM: ' || SQLERRM);
431 END IF;
432 app_exception.raise_exception;
433 END submit_fisap_event;
434
435 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
436 retcode OUT NOCOPY NUMBER,
437 p_award_year IN VARCHAR2,
438 p_retain_prev_batches IN VARCHAR2,
439 p_descrption IN VARCHAR2
440 )
441 IS
442 /*************************************************************
443 Created By : ugummall
444 Date Created On : 2004/10/04
445 Purpose :
446 Know limitations, enhancements or remarks
447 Change History
448 Who When What
449 (reverse chronological order - newest change first)
450 ***************************************************************/
451
452 CURSOR cur_fisap_rep(cp_batch_id NUMBER) IS
453 SELECT rep.ROWID row_id
454 FROM IGF_AW_FISAP_REP rep
455 WHERE rep.batch_id = cp_batch_id;
456
457 CURSOR cur_fisap_batch(cp_cal_type VARCHAR2, cp_seq_number NUMBER) IS
458 SELECT batch.ROWID row_id, batch.batch_id
459 FROM IGF_AW_FISAP_BATCH batch
460 WHERE batch.ci_cal_type = cp_cal_type
461 AND batch.ci_sequence_number = cp_seq_number;
462
463 CURSOR cur_students(cp_cal_type VARCHAR2, cp_seq_number NUMBER) IS
464 SELECT fabase.base_id
465 FROM igf_ap_fa_base_rec fabase
466 WHERE fabase.ci_cal_type = cp_cal_type
467 AND fabase.ci_sequence_number = cp_seq_number;
468 rec_student cur_students%ROWTYPE;
469
470 CURSOR cur_isir_dtls(cp_base_id NUMBER) IS
471 SELECT isir.dependency_status,
472 isir.isir_id,
473 DECODE(isir.auto_zero_efc, 'Y', 'Y', 'N') auto_zero_efc,
474 isir.total_income,
475 isir.student_total_income
476 FROM IGF_AP_ISIR_MATCHED isir
477 WHERE isir.base_id = cp_base_id
478 AND isir.active_isir = 'Y';
479 rec_isir_dtls cur_isir_dtls%ROWTYPE;
480
481 CURSOR cur_any_cbfunds(cp_cal_type VARCHAR2, cp_seq_number NUMBER, cp_base_id NUMBER) IS
482 SELECT 'X'
483 FROM IGF_AP_FA_BASE_REC_ALL fabase,
484 IGF_AW_AWARD_ALL awd,
485 IGF_AW_FUND_MAST_ALL fmast,
486 IGF_AW_FUND_CAT_ALL fcat,
487 IGS_CA_INST_ALL ca
488 WHERE ca.cal_type = fabase.ci_cal_type
489 AND ca.sequence_number = fabase.ci_sequence_number
490 AND fabase.base_id = awd.base_id
491 AND fmast.fund_id = awd.fund_id
492 AND fcat.fund_code = fmast.fund_code
493 AND ca.cal_type = cp_cal_type
494 AND ca.sequence_number = cp_seq_number
495 AND fabase.base_id = cp_base_id
496 AND ( fcat.fed_fund_code in ('FSEOG', 'PRK')
497 OR ( fcat.fed_fund_code = 'FWS' AND fcat.fund_source = 'FEDERAL')
498 );
499
500 CURSOR cur_fws_amount(cp_base_id IN NUMBER) IS
501 SELECT SUM(NVL(pay.paid_amount, 0)) paid_amount
502 FROM IGF_SE_PAYMENT pay,
503 IGF_SE_AUTH auth,
504 IGF_AW_AWARD_ALL awd,
505 IGF_AW_FUND_MAST_ALL fmast,
506 IGF_AW_FUND_CAT_ALL fcat,
507 IGF_AP_FA_BASE_REC_ALL fa
508 WHERE fcat.fund_code=fmast.fund_code
509 AND fcat.fed_fund_code='FWS'
510 AND fcat.fund_source='FEDERAL'
511 AND fmast.fund_id=awd.fund_id
512 AND awd.base_id=fa.base_id
513 AND awd.award_id=auth.award_id
514 AND auth.flag='A'
515 AND auth.auth_id=pay.auth_id
516 AND fa.ci_cal_type=fmast.ci_cal_type
517 AND fa.ci_sequence_number=fmast.ci_sequence_number
518 AND fa.base_id = cp_base_id
519 GROUP BY fa.base_id;
520
521 CURSOR cur_perkins_amount(cp_base_id IN NUMBER) IS
522 SELECT SUM(NVL(disb.disb_paid_amt, 0)) paid_amount
523 FROM IGF_AP_FA_BASE_REC_ALL fabase,
524 IGF_AW_AWARD_ALL awd,
525 IGF_AW_AWD_DISB_ALL disb,
526 IGF_AW_FUND_MAST_ALL fmast,
527 IGF_AW_FUND_CAT_ALL fcat
528 WHERE fabase.base_id = awd.base_id
529 AND awd.award_id = disb.award_id
530 AND fmast.fund_id = awd.fund_id
531 AND fcat.fund_code = fmast.fund_code
532 AND fcat.fed_fund_code = 'PRK'
533 AND fabase.base_id = cp_base_id
534 AND disb.trans_type = 'A'
535 GROUP BY fabase.base_id;
536
537 CURSOR cur_fseog_amount(cp_base_id IN NUMBER) IS
538 SELECT SUM(NVL(disb.disb_paid_amt, 0)) paid_amount
539 FROM IGF_AP_FA_BASE_REC_ALL fabase,
540 IGF_AW_AWARD_ALL awd,
541 IGF_AW_AWD_DISB_ALL disb,
542 IGF_AW_FUND_MAST_ALL fmast,
543 IGF_AW_FUND_CAT_ALL fcat
544 WHERE fabase.base_id = awd.base_id
545 AND awd.award_id = disb.award_id
546 AND fmast.fund_id = awd.fund_id
547 AND fcat.fund_code = fmast.fund_code
548 AND fcat.fed_fund_code = 'FSEOG'
549 AND fabase.base_id = cp_base_id
550 AND disb.trans_type = 'A'
551 GROUP BY fabase.base_id;
552
553 ln_fisap_dtls_id NUMBER(15);
554 lv_part_II_flag VARCHAR2(1);
555 lv_part_VI_flag VARCHAR2(1);
556 ln_isir_id NUMBER(15);
557 lv_dependency_status VARCHAR2(1);
558 lv_auto_zero_efc VARCHAR2(1);
559 ln_fisap_income NUMBER(15);
560 lv_career_level VARCHAR2(30);
561 lv_enrollment_status VARCHAR2(30);
562 ln_perkins_disb_amt NUMBER(15);
563 ln_fseog_disb_amt NUMBER(15);
564 ln_fws_disb_amt NUMBER(15);
565
566 ln_batch_id NUMBER(15);
567 lv_reported_time_txt VARCHAR2(30);
568 lv_cal_type VARCHAR2(30);
569 ln_seq_number NUMBER(15);
570 cbfunds_flag VARCHAR2(1);
571
572 lv_batch_rowid ROWID;
573 lv_rep_rowid ROWID;
574
575 BEGIN
576
577 --
578 -- Steps
579 -- 1. print parameters
580 -- 2. delete previous batches based on p_retain_prev_batches param
581 -- 3. inserts all eligible students in IGF_AW_FISAP_REP table.
582 -- 4. raise business event to send notification mail
583 --
584
585 igf_aw_gen.set_org_id(NULL);
586 retcode := 0;
587 ln_batch_id := NULL;
588 lv_reported_time_txt := TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD') || 'T' || TO_CHAR(SYSDATE, 'HH:MM:SS');
589 lv_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
590 ln_seq_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
591
592 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
593 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug', 'p_award_year: ' || p_award_year);
594 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug', 'award cal_type : ' || lv_cal_type);
595 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug', 'award ci_seq_num : ' || ln_seq_number);
596 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug', 'p_retain_prev_batches: ' || p_retain_prev_batches);
597 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug', 'p_descrption: ' || p_descrption);
598 END IF;
599
600 -- Step 1. Print parameters
601 log_input_parameters(lv_cal_type, ln_seq_number, p_retain_prev_batches, p_descrption);
602
603 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
604 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','after log parameters');
605 END IF;
606
607 -- Step 2. Delete previous batches
608 IF (p_retain_prev_batches = 'N') THEN
609 FOR rec_fisap_batch IN cur_fisap_batch(lv_cal_type, ln_seq_number)
610 LOOP
611 -- delete from the child(reporting) table
612 FOR rec_fisap_rep IN cur_fisap_rep(rec_fisap_batch.batch_id)
613 LOOP
614 igf_aw_fisap_rep_pkg.delete_row(rec_fisap_rep.row_id);
615 END LOOP;
616
617 -- delete from the parent(batch) table
618 igf_aw_fisap_batch_pkg.delete_row(rec_fisap_batch.row_id);
619 END LOOP;
620 END IF;
621 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
622 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','after deleting previous batches');
623 END IF;
624
625 -- Step 3. Insert eligible aid applicants into reporting table.
626 FOR rec_student IN cur_students(lv_cal_type, ln_seq_number)
627 LOOP
628 -- First we will insert a record for Part II. If the same student is considered in Part VI
629 -- also then we update(not insert) the record. If a student is not considered in Part II,
630 -- but in Part VI then we insert him in Part VI.
631
632 ln_fisap_dtls_id := NULL;
633 lv_part_II_flag := NULL;
634 lv_part_VI_flag := NULL;
635 ln_isir_id := NULL;
636 lv_dependency_status := NULL;
637 lv_auto_zero_efc := NULL;
638 ln_fisap_income := NULL;
639 lv_career_level := NULL;
640 lv_enrollment_status := NULL;
641 ln_perkins_disb_amt := NULL;
642 ln_fseog_disb_amt := NULL;
643 ln_fws_disb_amt := NULL;
644 lv_batch_rowid := NULL;
645 lv_rep_rowid := NULL;
646
647 -- determine common fields
648 OPEN cur_isir_dtls(rec_student.base_id);
649 FETCH cur_isir_dtls INTO rec_isir_dtls;
650 IF cur_isir_dtls%NOTFOUND THEN
651 -- log an error message. And skip this student
652 fnd_message.set_name('IGF','IGF_AW_FISAP_SKIP_NO_ISIR');
653 fnd_message.set_token('PERNUM',igf_gr_gen.get_per_num(rec_student.base_id));
654 fnd_file.put_line(fnd_file.log,fnd_message.get);
655 CLOSE cur_isir_dtls;
656 ELSE
657 ln_isir_id := rec_isir_dtls.isir_id;
658 lv_dependency_status := rec_isir_dtls.dependency_status;
659 lv_auto_zero_efc := rec_isir_dtls.auto_zero_efc;
660 lv_career_level := get_student_career_level(rec_student.base_id);
661 IF lv_career_level = 'GRAD_PROF' THEN
662 -- If the student's career level is "Graduate/Professional" then
663 -- consider his dependency status as "Independent"
664 -- irrespective of the column value in IGF_AP_ISIR_MATCHED table.
665
666 -- Override student's dependency status to Independent
667 lv_dependency_status := 'I';
668 END IF;
669
670 IF (lv_dependency_status = 'I') THEN
671 -- FISAP Income for Independent Students should be the Total Income (TI) as given in the Awarding ISIR
672 ln_fisap_income := NVL(rec_isir_dtls.student_total_income, 0);
673 ELSE
674 -- FISAP Income for Dependent Students should be the Total Income (TI) + Student Total Income(STI), as given in the Awarding ISIR
675 ln_fisap_income := NVL(rec_isir_dtls.total_income, 0) + NVL(rec_isir_dtls.student_total_income, 0);
676 END IF;
677
678 CLOSE cur_isir_dtls;
679
680 -- if the student is Eligible Aid Applicant then only consider him/her for Part II
681 IF is_eligible_aid_applicant(rec_student.base_id) THEN
682 lv_part_II_flag := 'Y';
683
684 IF ln_batch_id IS NULL THEN
685 -- Insert batch record into IGF_AW_FISAP_BATCH table
686 -- ONLY ONCE in the entire process and collect the
687 -- primary key batch_id into lv_batch_id.
688 igf_aw_fisap_batch_pkg.insert_row (
689 x_rowid => lv_batch_rowid,
690 x_batch_id => ln_batch_id,
691 x_ci_cal_type => lv_cal_type,
692 x_ci_sequence_number => ln_seq_number,
693 x_description => p_descrption,
694 x_reported_time_txt => lv_reported_time_txt,
695 x_mode => 'R'
696 );
697 END IF;
698
699 -- INSERT this record into the IGF_AW_FISAP_REP table
700 -- call table handler to insert the record with the above calculated
701 -- attributes and collect the primary key fisap_dtls_id which may be
702 -- used in Part VI for updating record with Part VI information.
703 igf_aw_fisap_rep_pkg.insert_row (
704 x_rowid => lv_rep_rowid,
705 x_fisap_dtls_id => ln_fisap_dtls_id,
706 x_batch_id => ln_batch_id,
707 x_isir_id => ln_isir_id,
708 x_dependency_status => lv_dependency_status,
709 x_career_level => lv_career_level,
710 x_auto_zero_efc_flag => lv_auto_zero_efc,
711 x_fisap_income_amt => ln_fisap_income,
712 x_enrollment_status => lv_enrollment_status,
713 x_perkins_disb_amt => ln_perkins_disb_amt,
714 x_fws_disb_amt => ln_fws_disb_amt,
715 x_fseog_disb_amt => ln_fseog_disb_amt,
716 x_part_ii_section_f_flag => lv_part_II_flag,
717 x_part_vi_section_a_flag => lv_part_VI_flag,
718 x_mode => 'R'
719 );
720 END IF;
721
722 -- If the student is having any campus based funds(FSEOG, FWS, Perkins)
723 -- then only consider him for Part VI
724 cbfunds_flag := NULL;
725 OPEN cur_any_cbfunds(lv_cal_type, ln_seq_number, rec_student.base_id);
726 FETCH cur_any_cbfunds INTO cbfunds_flag;
727 CLOSE cur_any_cbfunds;
728
729 IF cbfunds_flag = 'X' THEN
730 -- consider this person for part VI
731 lv_part_VI_flag := 'Y';
732 -- Following extra fields need to be determined for Part VI.
733 -- lv_enrollment_status
734 -- ln_perkins_disb_amt
735 -- ln_fseog_disb_amt
736 -- ln_fws_disb_amt
737 lv_enrollment_status := get_student_enrollment_status(rec_student.base_id);
738 OPEN cur_fws_amount(rec_student.base_id);
739 FETCH cur_fws_amount INTO ln_fws_disb_amt;
740 CLOSE cur_fws_amount;
741
742 OPEN cur_perkins_amount(rec_student.base_id);
743 FETCH cur_perkins_amount INTO ln_perkins_disb_amt;
744 CLOSE cur_perkins_amount;
745
746 OPEN cur_fseog_amount(rec_student.base_id);
747 FETCH cur_fseog_amount INTO ln_fseog_disb_amt;
748 CLOSE cur_fseog_amount;
749
750 IF ln_batch_id IS NULL THEN
751 -- Insert batch record into IGF_AW_FISAP_BATCH table
752 -- ONLY ONCE in the entire process and collect the
753 -- primary key batch_id into lv_batch_id.
754 igf_aw_fisap_batch_pkg.insert_row (
755 x_rowid => lv_batch_rowid,
756 x_batch_id => ln_batch_id,
757 x_ci_cal_type => lv_cal_type,
758 x_ci_sequence_number => ln_seq_number,
759 x_description => p_descrption,
760 x_reported_time_txt => lv_reported_time_txt,
761 x_mode => 'R'
762 );
763 END IF;
764
765 -- UPDATE/INSERT this record into the IGF_AW_FISAP_REP table
766 igf_aw_fisap_rep_pkg.add_row (
767 x_rowid => lv_rep_rowid,
768 x_fisap_dtls_id => ln_fisap_dtls_id,
769 x_batch_id => ln_batch_id,
770 x_isir_id => ln_isir_id,
771 x_dependency_status => lv_dependency_status,
772 x_career_level => lv_career_level,
773 x_auto_zero_efc_flag => lv_auto_zero_efc,
774 x_fisap_income_amt => ln_fisap_income,
775 x_enrollment_status => lv_enrollment_status,
776 x_perkins_disb_amt => ln_perkins_disb_amt,
777 x_fws_disb_amt => ln_fws_disb_amt,
778 x_fseog_disb_amt => ln_fseog_disb_amt,
779 x_part_ii_section_f_flag => lv_part_II_flag,
780 x_part_vi_section_a_flag => lv_part_VI_flag,
781 x_mode => 'R'
782 );
783 END IF;
784 END IF;
785 END LOOP;
786
787 -- Step 4. Raise Business event.
788 IF ln_batch_id IS NOT NULL THEN
789 submit_fisap_event(lv_cal_type, ln_seq_number, ln_batch_id);
790 END IF;
791
792 COMMIT;
793 EXCEPTION
794 WHEN OTHERS THEN
795 ROLLBACK;
796 retcode := 2;
797 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
798 fnd_file.put_line(fnd_file.log, SQLERRM);
799 igs_ge_msg_stack.conc_exception_hndl;
800 END main;
801
802 PROCEDURE generate_aggregate_data ( itemtype IN VARCHAR2,
803 itemkey IN VARCHAR2,
804 actid IN NUMBER,
805 funcmode IN VARCHAR2,
806 resultout OUT NOCOPY VARCHAR2
807 )
808 AS
809 /*************************************************************
810 Created By : ugummall
811 Date Created On : 2004/10/04
812 Purpose :
813 Know limitations, enhancements or remarks
814 Change History
815 Who When What
816 (reverse chronological order - newest change first)
817 ***************************************************************/
818 ln_batch_id NUMBER(15);
819 ln_seq_number NUMBER(15);
820 lv_cal_type VARCHAR2(30);
821 param VARCHAR2(1000);
822 loc NUMBER(15);
823 BEGIN
824 ln_batch_id := wf_engine.getitemattrtext ( itemtype, itemkey, 'BATCH_ID_PARAMETER');
825 param := wf_engine.getitemattrtext ( itemtype, itemkey, 'FISAP_DATA');
826
827 loc := instr(param, ':');
828 lv_cal_type := substr(param, 1, loc - 1);
829 param := substr(param, loc + 1);
830 ln_seq_number := param;
831
832 IF (funcmode = 'RUN') THEN
833 wf_engine.SetItemAttrText ( itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'FISAP_DATA',
836 avalue => 'PLSQLCLOB:igf_aw_fisap_pkg.generate_partII/'|| ln_batch_id || ':' || lv_cal_type || ':' || ln_seq_number);
837 resultout:= 'COMPLETE:';
838 RETURN;
839 END IF;
840 END generate_aggregate_data;
841
842 PROCEDURE generate_partII ( document_id IN VARCHAR2,
843 display_type IN VARCHAR2,
844 document IN OUT NOCOPY CLOB,
845 document_type IN OUT NOCOPY VARCHAR2
846 )
847 IS
848 /*************************************************************
849 Created By : ugummall
850 Date Created On : 2004/10/04
851 Purpose :
852 Know limitations, enhancements or remarks
853 Change History
854 Who When What
855 (reverse chronological order - newest change first)
856 ***************************************************************/
857
858 CURSOR cur_2f_depend_smry ( cp_cal_type IN VARCHAR2, cp_seq_number IN NUMBER, cp_batch_id IN NUMBER) IS
859 SELECT fs.start_range_amt,
860 fs.end_range_amt,
861 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WOUT', 1, 0)) AS ug_wout,
862 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WITH', 1, 0)) AS ug_with
863 FROM IGF_AW_FISAP_REP fisap,
864 IGF_AW_FISAP_RANGES fs,
865 IGF_AP_BATCH_AW_MAP_ALL awmap
866 WHERE awmap.ci_cal_type = cp_cal_type
867 AND awmap.ci_sequence_number = cp_seq_number
868 AND fs.sys_awd_yr = awmap.sys_award_year
869 AND fs.part_section = '2F'
870 AND fs.dependency_status = 'D'
871 AND fisap.batch_id(+) = cp_batch_id
872 AND fisap.fisap_income_amt(+) >= fs.start_range_amt
873 AND fisap.fisap_income_amt(+) <= NVL(fs.end_range_amt, fisap.fisap_income_amt(+))
874 AND fisap.dependency_status(+) = 'D'
875 AND fisap.auto_zero_efc_flag(+) = 'N'
876 AND fisap.part_ii_section_f_flag(+) = 'Y'
877 GROUP BY fs.start_range_amt, fs.end_range_amt
878 ORDER BY fs.start_range_amt;
879 rec_2f_depend_smry cur_2f_depend_smry%ROWTYPE;
880
881 CURSOR cur_2f_independ_smry ( cp_cal_type IN VARCHAR2, cp_seq_number IN NUMBER, cp_batch_id IN NUMBER) IS
882 SELECT fs.start_range_amt,
883 fs.end_range_amt,
884 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WOUT', 1, 0)) AS ug_wout,
885 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WITH', 1, 0)) AS ug_with,
886 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'GRAD_PROF', 1, 0)) AS grad_prof
887 FROM IGF_AW_FISAP_REP fisap,
888 IGF_AW_FISAP_RANGES fs,
889 IGF_AP_BATCH_AW_MAP_ALL awmap
890 WHERE awmap.ci_cal_type = cp_cal_type
891 AND awmap.ci_sequence_number = cp_seq_number
892 AND fs.sys_awd_yr = awmap.sys_award_year
893 AND fs.part_section = '2F'
894 AND fs.dependency_status = 'I'
895 AND fisap.batch_id(+) = cp_batch_id
896 AND fisap.fisap_income_amt(+) >= fs.start_range_amt
897 AND fisap.fisap_income_amt(+) <= NVL(fs.end_range_amt, fisap.fisap_income_amt(+))
898 AND fisap.dependency_status(+) = 'I'
899 AND fisap.auto_zero_efc_flag(+) = 'N'
900 AND fisap.part_ii_section_f_flag(+) = 'Y'
901 GROUP BY fs.start_range_amt, fs.end_range_amt
902 ORDER BY fs.start_range_amt;
903 rec_2f_independ_smry cur_2f_independ_smry%ROWTYPE;
904
905 CURSOR cur_2f_depend_efc ( cp_batch_id IN NUMBER) IS
906 SELECT SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WOUT', 1, 0)) AS ug_wout,
907 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WITH', 1, 0)) AS ug_with
908 FROM IGF_AW_FISAP_REP fisap
909 WHERE fisap.batch_id = cp_batch_id
910 AND fisap.dependency_status = 'D'
911 AND fisap.auto_zero_efc_flag = 'Y'
912 AND fisap.part_ii_section_f_flag = 'Y'
913 GROUP BY fisap.batch_id;
914 rec_2f_depend_efc cur_2f_depend_efc%ROWTYPE;
915
916 CURSOR cur_2f_independ_efc ( cp_batch_id IN NUMBER) IS
917 SELECT SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WOUT', 1, 0)) AS ug_wout,
918 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'UG_WITH', 1, 0)) AS ug_with,
919 SUM(DECODE(NVL(fisap.career_level, 'ZERO'), 'GRAD_PROF', 1, 0)) AS grad_prof
920 FROM IGF_AW_FISAP_REP fisap
921 WHERE fisap.batch_id = cp_batch_id
922 AND fisap.dependency_status = 'I'
923 AND fisap.auto_zero_efc_flag = 'Y'
924 AND fisap.part_ii_section_f_flag = 'Y'
925 GROUP by fisap.batch_id;
926 rec_2f_independ_efc cur_2f_independ_efc%ROWTYPE;
927
928 CURSOR cur_6a_ugdepend ( cp_cal_type IN VARCHAR2, cp_seq_number IN NUMBER, cp_batch_id IN NUMBER) IS
929 SELECT fs.start_range_amt,
930 fs.end_range_amt,
931 SUM(DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1)) AS a,
932 SUM(NVL(fisap.perkins_disb_amt, 0)) AS b,
933 SUM(DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1)) AS c,
934 SUM(NVL(fisap.fseog_disb_amt, 0)) AS d,
935 SUM(DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1)) AS e,
936 SUM(NVL(fisap.fws_disb_amt, 0)) AS f,
937 SUM(DECODE( DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1) +
938 DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1) +
939 DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1) ,
940 0, 0, 1
941 )
942 ) g
943 FROM IGF_AW_FISAP_REP FISAP,
944 IGF_AW_FISAP_RANGES FS,
945 IGF_AP_BATCH_AW_MAP_ALL AWMAP
946 WHERE awmap.ci_cal_type = cp_cal_type
947 AND awmap.ci_sequence_number = cp_seq_number
948 AND fs.sys_awd_yr = awmap.sys_award_year
949 AND fs.part_section = '6A'
950 AND fs.dependency_status = 'D'
951 AND fisap.batch_id(+) = cp_batch_id
952 AND fisap.fisap_income_amt(+) >= fs.start_range_amt
953 AND fisap.fisap_income_amt(+) <= NVL(fs.end_range_amt, fisap.fisap_income_amt(+))
954 AND fisap.dependency_status(+) = 'D'
955 AND fisap.career_level(+) <> 'GRAD_PROF'
956 AND fisap.part_vi_section_a_flag(+) = 'Y'
957 GROUP BY fs.start_range_amt,
958 fs.end_range_amt
959 ORDER BY fs.start_range_amt;
960 rec_6a_ugdepend cur_6a_ugdepend%ROWTYPE;
961
962 CURSOR cur_6a_ugindepend ( cp_cal_type IN VARCHAR2, cp_seq_number IN NUMBER, cp_batch_id IN NUMBER) IS
963 SELECT fs.start_range_amt,
964 fs.end_range_amt,
965 SUM(DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1)) AS a,
966 SUM(NVL(fisap.perkins_disb_amt, 0)) AS b,
967 SUM(DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1)) AS c,
968 SUM(NVL(fisap.fseog_disb_amt, 0)) AS d,
969 SUM(DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1)) AS e,
970 SUM(NVL(fisap.fws_disb_amt, 0)) AS f,
971 SUM(DECODE( DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1) +
972 DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1) +
973 DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1) ,
974 0, 0, 1
975 )
976 ) g
977 FROM IGF_AW_FISAP_REP fisap,
978 IGF_AW_FISAP_RANGES fs,
979 IGF_AP_BATCH_AW_MAP_ALL awmap
980 WHERE awmap.ci_cal_type = cp_cal_type
981 AND awmap.ci_sequence_number = cp_seq_number
982 AND fs.sys_awd_yr = awmap.sys_award_year
983 AND fs.part_section = '6A'
984 AND fs.dependency_status = 'I'
985 AND fisap.batch_id(+) = cp_batch_id
986 AND fisap.fisap_income_amt(+) >= fs.start_range_amt
987 AND fisap.fisap_income_amt(+) <= NVL(fs.end_range_amt, fisap.fisap_income_amt(+))
988 AND fisap.dependency_status(+) = 'I'
989 AND fisap.career_level(+) <> 'GRAD_PROF'
990 AND fisap.part_vi_section_a_flag(+) = 'Y'
991 GROUP BY fs.start_range_amt,
992 fs.end_range_amt
993 ORDER BY fs.start_range_amt;
994 rec_6a_ugindepend cur_6a_ugindepend%ROWTYPE;
995
996 CURSOR cur_gradprof ( cp_batch_id IN NUMBER) IS
997 SELECT SUM(DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1)) AS a,
998 SUM(NVL(fisap.perkins_disb_amt, 0)) AS b,
999 SUM(DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1)) AS e,
1000 SUM(NVL(fisap.fws_disb_amt, 0)) AS f,
1001 SUM(DECODE( DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1) +
1002 DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1) ,
1003 0, 0, 1
1004 )
1005 ) g
1006 FROM IGF_AW_FISAP_REP FISAP
1007 WHERE fisap.batch_id = cp_batch_id
1008 AND fisap.dependency_status = 'I'
1009 AND fisap.career_level = 'GRAD_PROF'
1010 AND fisap.part_vi_section_a_flag = 'Y';
1011 rec_gradprof cur_gradprof%ROWTYPE;
1012
1013 CURSOR cur_ltfulltime ( cp_batch_id IN NUMBER) IS
1014 SELECT SUM(DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1)) AS a,
1015 SUM(NVL(fisap.perkins_disb_amt, 0)) AS b,
1016 SUM(DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1)) AS c,
1017 SUM(NVL(fisap.fseog_disb_amt, 0)) AS d,
1018 SUM(DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1)) AS e,
1019 SUM(NVL(fisap.fws_disb_amt, 0)) AS f,
1020 SUM(DECODE( DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1) +
1021 DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1) ,
1022 0, 0, 1
1023 )
1024 ) g
1025 FROM IGF_AW_FISAP_REP fisap
1026 WHERE fisap.batch_id = cp_batch_id
1027 AND fisap.enrollment_status = 'FT'
1028 AND fisap.part_vi_section_a_flag = 'Y';
1029 rec_ltfulltime cur_ltfulltime%ROWTYPE;
1030
1031 CURSOR cur_6a_efc ( cp_batch_id IN NUMBER) IS
1032 SELECT SUM(DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1)) AS a,
1033 SUM(NVL(fisap.perkins_disb_amt, 0)) AS b,
1034 SUM(DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1)) AS c,
1035 SUM(NVL(fisap.fseog_disb_amt, 0)) AS d,
1036 SUM(DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1)) AS e,
1037 SUM(NVL(fisap.fws_disb_amt, 0)) AS f,
1038 SUM(DECODE( DECODE(NVL(fisap.perkins_disb_amt, -1), -1, 0, 1) +
1039 DECODE(NVL(fisap.fseog_disb_amt, -1), -1, 0, 1) +
1040 DECODE(NVL(fisap.fws_disb_amt, -1), -1, 0, 1) ,
1041 0, 0, 1
1042 )
1043 ) g
1044 FROM IGF_AW_FISAP_REP fisap
1045 WHERE fisap.batch_id = cp_batch_id
1046 AND fisap.auto_zero_efc_flag = 'Y'
1047 AND fisap.part_vi_section_a_flag = 'Y';
1048 rec_6a_efc cur_6a_efc%ROWTYPE;
1049
1050 l_c_document VARCHAR2(32000);
1051 param VARCHAR2(1000);
1052 ln_batch_id NUMBER(15);
1053 ln_seq_number NUMBER(15);
1054 lv_cal_type VARCHAR2(100);
1055 lv_and_over_mesg VARCHAR2(100);
1056 end_range_amt_txt VARCHAR2(100);
1057 loc NUMBER(15);
1058 ln_depend_ugwout_total NUMBER(15);
1059 ln_depend_ugwith_total NUMBER(15);
1060 ln_independ_ugwout_total NUMBER(15);
1061 ln_independ_ugwith_total NUMBER(15);
1062 ln_independ_gp_total NUMBER(15);
1063 ln_total_a NUMBER(15);
1064 ln_total_b NUMBER(15);
1065 ln_total_c NUMBER(15);
1066 ln_total_d NUMBER(15);
1067 ln_total_e NUMBER(15);
1068 ln_total_f NUMBER(15);
1069 ln_total_g NUMBER(15);
1070 ln_line_num NUMBER(15);
1071
1072 BEGIN
1073
1074 ln_total_a := 0;
1075 ln_total_b := 0;
1076 ln_total_c := 0;
1077 ln_total_d := 0;
1078 ln_total_e := 0;
1079 ln_total_f := 0;
1080 ln_total_g := 0;
1081 ln_line_num := 0;
1082
1083 ln_depend_ugwout_total := 0;
1084 ln_depend_ugwith_total := 0;
1085 ln_independ_ugwout_total := 0;
1086 ln_independ_ugwith_total := 0;
1087 ln_independ_gp_total := 0;
1088
1089 -- Extract parameters from the document_id parameter.
1090 param := document_id;
1091 loc := instr(param, ':');
1092 ln_batch_id := substr(param, 1, loc - 1);
1093 param := substr(param, loc + 1);
1094
1095 loc := instr(param, ':');
1096 lv_cal_type := substr(param, 1, loc - 1);
1097 param := substr(param, loc + 1);
1098 ln_seq_number := param;
1099
1100 -- Part II Heading.
1101 l_c_document := '<p><B>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','2F_HEADING') || '</B><br><br>';
1102
1103 -- Part II Column Headings.
1104 l_c_document := l_c_document || '<table BORDER COLS=7 WIDTH="90%"><tr bgcolor="#C0C0C0"><td colspan=3 width=30%><b>';
1105 l_c_document := l_c_document || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','D') || '</b></td><td colspan=4 width=40%><b>';
1106 l_c_document := l_c_document || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','I') || '</b></td></tr>';
1107
1108 l_c_document := l_c_document||'<tr bgcolor="#C0C0C0"><td width=10%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TAXUNTAX');
1109 l_c_document := l_c_document||'</td><td width=10%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UGWOUT') || '</td>';
1110
1111 l_c_document := l_c_document||'<td width=10%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UGWITH') || '</td><td width=10%>';
1112 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TAXUNTAX') || '</td><td width=10%>';
1113
1114 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UGWOUT') || '</td>';
1115
1116 l_c_document := l_c_document||'<td width=10%>';
1117 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UGWITH') || '</td><td width=10%>';
1118 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','GRAD_PROF') || '</td></tr>';
1119
1120 -- Part II. Eligible Aid Grid data. Auto Zero EFC Row.
1121 OPEN cur_2f_depend_efc(ln_batch_id);
1122 FETCH cur_2f_depend_efc INTO rec_2f_depend_efc;
1123 CLOSE cur_2f_depend_efc;
1124
1125 ln_depend_ugwout_total := NVL(rec_2f_depend_efc.ug_wout, 0);
1126 ln_depend_ugwith_total := NVL(rec_2f_depend_efc.ug_with, 0);
1127
1128 OPEN cur_2f_independ_efc(ln_batch_id);
1129 FETCH cur_2f_independ_efc INTO rec_2f_independ_efc;
1130 CLOSE cur_2f_independ_efc;
1131
1132 ln_independ_ugwout_total := NVL(rec_2f_independ_efc.ug_wout, 0);
1133 ln_independ_ugwith_total := NVL(rec_2f_independ_efc.ug_with, 0);
1134 ln_independ_gp_total := NVL(rec_2f_independ_efc.grad_prof, 0);
1135
1136 l_c_document := l_c_document || '<tr><td width=10%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','WITH_AUTO_ZERO_EFC');
1137 l_c_document := l_c_document || '</td><td width=10%>' || NVL(rec_2f_depend_efc.ug_wout, 0) || '</td><td width=10%>' || NVL(rec_2f_depend_efc.ug_with, 0) || '</td><td width=10%>';
1138 l_c_document := l_c_document || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','WITH_AUTO_ZERO_EFC') || '</td>';
1139 l_c_document := l_c_document || '<td width=10%>' || NVL(rec_2f_independ_efc.ug_wout, 0) || '</td><td width=10%>';
1140 l_c_document := l_c_document || NVL(rec_2f_independ_efc.ug_with, 0) || '</td><td width=10%>' || NVL(rec_2f_independ_efc.grad_prof, 0) || '</td></tr>';
1141
1142 -- Part II. Eligible Aid Grid data. 14 Rows.
1143 OPEN cur_2f_depend_smry(lv_cal_type, ln_seq_number, ln_batch_id);
1144 OPEN cur_2f_independ_smry(lv_cal_type, ln_seq_number, ln_batch_id);
1145 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1146 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', 'Part II 1-14 rows cursor parameter values(cal type, seq no, batch id): ' || lv_cal_type || ', ' || ln_seq_number || ', ' || ln_batch_id);
1147 END IF;
1148
1149 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1150 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', 'Before entering Part II 14 rows loop');
1151 END IF;
1152
1153 lv_and_over_mesg := get_lookup_meaning('IGF_AW_FISAP_HTML_REP','AND_OVER');
1154 LOOP
1155 FETCH cur_2f_depend_smry INTO rec_2f_depend_smry;
1156 FETCH cur_2f_independ_smry INTO rec_2f_independ_smry;
1157
1158 IF cur_2f_depend_smry%NOTFOUND AND cur_2f_independ_smry%NOTFOUND THEN
1159 EXIT;
1160 END IF;
1161
1162 l_c_document := l_c_document || '<tr><td width=10%>$' || rec_2f_depend_smry.start_range_amt;
1163 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1164 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', '2F Depend Start range = ' || rec_2f_depend_smry.start_range_amt);
1165 END IF;
1166
1167 IF rec_2f_depend_smry.end_range_amt IS NULL THEN
1168 end_range_amt_txt := ' ' || lv_and_over_mesg;
1169 ELSE
1170 end_range_amt_txt := ' - $' || rec_2f_depend_smry.end_range_amt;
1171 END IF;
1172 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1173 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', '2F Depend Start range = ' || end_range_amt_txt);
1174 END IF;
1175
1176 l_c_document := l_c_document || end_range_amt_txt || '</td><td width=10%>';
1177 l_c_document := l_c_document || NVL(rec_2f_depend_smry.ug_wout, 0)|| '</td><td width=10%>';
1178 l_c_document := l_c_document || NVL(rec_2f_depend_smry.ug_with, 0);
1179 l_c_document := l_c_document || '</td><td width=10%>$' || rec_2f_independ_smry.start_range_amt;
1180 IF rec_2f_independ_smry.end_range_amt IS NULL THEN
1181 end_range_amt_txt := ' ' || lv_and_over_mesg;
1182 ELSE
1183 end_range_amt_txt := ' - $' || rec_2f_independ_smry.end_range_amt;
1184 END IF;
1185 l_c_document := l_c_document || end_range_amt_txt || '</td><td width=10%>';
1186 l_c_document := l_c_document || NVL(rec_2f_independ_smry.ug_wout, 0) || '</td><td width=10%>';
1187 l_c_document := l_c_document || NVL(rec_2f_independ_smry.ug_with, 0) || '</td><td width=10%>';
1188 l_c_document := l_c_document || NVL(rec_2f_independ_smry.grad_prof, 0) || '</td></tr>';
1189 ln_depend_ugwout_total := ln_depend_ugwout_total + NVL(rec_2f_depend_smry.ug_wout, 0);
1190 ln_depend_ugwith_total := ln_depend_ugwith_total + NVL(rec_2f_depend_smry.ug_with, 0);
1191 ln_independ_ugwout_total := ln_independ_ugwout_total + NVL(rec_2f_independ_smry.ug_wout, 0);
1192 ln_independ_ugwith_total := ln_independ_ugwith_total + NVL(rec_2f_independ_smry.ug_with, 0);
1193 ln_independ_gp_total := ln_independ_gp_total + NVL(rec_2f_independ_smry.grad_prof, 0);
1194 END LOOP;
1195 CLOSE cur_2f_depend_smry;
1196 CLOSE cur_2f_independ_smry;
1197
1198 -- Part II. Totals.
1199 l_c_document := l_c_document || '<tr bgcolor="#C0C0C0"><td width=10%><b>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TOTAL');
1200 l_c_document := l_c_document || '</b></td><td width=10%>' || ln_depend_ugwout_total || '</td><td width=10%>' || ln_depend_ugwith_total || '</td><td width=10%><b>';
1201 l_c_document := l_c_document || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TOTAL') || '</b></td><td width=10%>';
1202 l_c_document := l_c_document || ln_independ_ugwout_total || '</td><td width=10%>' || ln_independ_ugwith_total || '</td><td width=10%>' || ln_independ_gp_total || '</a></td></tr>';
1203
1204 l_c_document := l_c_document||'</table>';
1205
1206 -- Part VI Heading.
1207 l_c_document := l_c_document||'<p><B>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','6A_HEADING') || '</B><br><br>';
1208
1209 -- Part VI Column Headings.
1210 l_c_document := l_c_document||'<table BORDER COLS=9 WIDTH="90%"><tr bgcolor="#C0C0C0"><td colspan=2 rowspan=2 width=22%>';
1211 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TAXUNTAX_CAT') || '</td>';
1212 l_c_document := l_c_document||'<td colspan=2 width=22%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','PERKINS') || '</td><td colspan=2 width=22%>';
1213 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','FSEOG') || '</td><td colspan=2 width=22%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','FWS') ||'</td>';
1214 l_c_document := l_c_document||'<td colspan=1 rowspan=2 width=11%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UNDUPLICATED_REC_G') || '</td></tr>';
1215
1216 l_c_document := l_c_document||'<tr bgcolor="#C0C0C0"><td width=11%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','REC_A');
1217 l_c_document := l_c_document||'</td><td width=11%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','FUNDS_B') || '</td><td width=11%>';
1218 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','REC_C') || '</td><td width=11%>';
1219 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','FUNDS_D') || '</td>';
1220
1221 l_c_document := l_c_document||'<td width=11%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','REC_E') || '</td><td width=11%>';
1222 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','FUNDS_F') || '</td></tr>';
1223
1224 l_c_document := l_c_document||'<tr bgcolor="#C0C0C0"><td colspan=9 width=22%><b>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UG_D') || '</b></td></tr>';
1225
1226 -- Rows 1 to 7. Undergraduate Depedent.
1227 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1228 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', 'Before entering Part VI 1-7 Ug Dependent loop');
1229 END IF;
1230 FOR rec IN cur_6a_ugdepend(lv_cal_type, ln_seq_number, ln_batch_id) LOOP
1231 ln_line_num := ln_line_num + 1;
1232 l_c_document := l_c_document || '<tr bgcolor="#ffffff"><td width=22%>' || ln_line_num || '</td><td width=22%>$';
1233 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1234 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', 'preparing row# ' || ln_line_num || ' of Part VI 1-7 rows');
1235 END IF;
1236
1237 IF rec.end_range_amt IS NULL THEN
1238 end_range_amt_txt := ' ' || lv_and_over_mesg;
1239 ELSE
1240 end_range_amt_txt := '- $' || rec.end_range_amt;
1241 END IF;
1242 l_c_document := l_c_document || rec.start_range_amt || end_range_amt_txt;
1243
1244 l_c_document := l_c_document || '</td><td width=22%>' || rec.a || '</td><td width=22%>' || rec.b;
1245 l_c_document := l_c_document || '</td><td width=22%>' || rec.c || '</td><td width=22%>' || rec.d;
1246 l_c_document := l_c_document || '</td><td width=22%>' || rec.e || '</td><td width=22%>' || rec.f;
1247 l_c_document := l_c_document || '</td><td width=22%>' || rec.g || '</td></tr>';
1248
1249 ln_total_a := ln_total_a + rec.a;
1250 ln_total_b := ln_total_b + rec.b;
1251 ln_total_c := ln_total_c + rec.c;
1252 ln_total_d := ln_total_d + rec.d;
1253 ln_total_e := ln_total_e + rec.e;
1254 ln_total_f := ln_total_f + rec.f;
1255 ln_total_g := ln_total_g + rec.g;
1256 END LOOP;
1257
1258 l_c_document := l_c_document || '<tr bgcolor="#C0C0C0"><td colspan=9 width=22%><b>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','UG_I') || '</b></td></tr>';
1259
1260 -- Rows 8 to 14. Undergraduate Indepedent.
1261 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1262 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', 'Before entering Part VI 8-14 Ug Independent loop');
1263 END IF;
1264 FOR rec IN cur_6a_ugindepend(lv_cal_type, ln_seq_number, ln_batch_id) LOOP
1265 ln_line_num := ln_line_num + 1;
1266 l_c_document := l_c_document || '<tr bgcolor="#ffffff"><td width=22%>' || ln_line_num || '</td><td width=22%>$';
1267 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1268 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_fisap_pkg.generate_partII.debug', 'preparing row# ' || ln_line_num || ' of Part VI 8-14 rows');
1269 END IF;
1270
1271 IF rec.end_range_amt IS NULL THEN
1272 end_range_amt_txt := ' ' || lv_and_over_mesg;
1273 ELSE
1274 end_range_amt_txt := '- $' || rec.end_range_amt;
1275 END IF;
1276 l_c_document := l_c_document || rec.start_range_amt || end_range_amt_txt;
1277
1278 l_c_document := l_c_document || '</td><td width=22%>' || rec.a || '</td><td width=22%>' || rec.b;
1279 l_c_document := l_c_document || '</td><td width=22%>' || rec.c || '</td><td width=22%>' || rec.d;
1280 l_c_document := l_c_document || '</td><td width=22%>' || rec.e || '</td><td width=22%>' || rec.f;
1281 l_c_document := l_c_document || '</td><td width=22%>' || rec.g || '</td></tr>';
1282
1283 ln_total_a := ln_total_a + rec.a;
1284 ln_total_b := ln_total_b + rec.b;
1285 ln_total_c := ln_total_c + rec.c;
1286 ln_total_d := ln_total_d + rec.d;
1287 ln_total_e := ln_total_e + rec.e;
1288 ln_total_f := ln_total_f + rec.f;
1289 ln_total_g := ln_total_g + rec.g;
1290 END LOOP;
1291
1292 -- 15th Row. Graduate/Professional.
1293 OPEN cur_gradprof(ln_batch_id);
1294 FETCH cur_gradprof INTO rec_gradprof;
1295 CLOSE cur_gradprof;
1296
1297 ln_line_num := ln_line_num + 1;
1298
1299 l_c_document := l_c_document||'<tr bgcolor="#ffffff"><td width=22%>' || ln_line_num || '</td><td width=22%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','GRAD_PROF');
1300 l_c_document := l_c_document||'</td><td width=22%>' || NVL(rec_gradprof.a, 0) || '</td><td width=22%>' || NVL(rec_gradprof.b, 0) || '</td><td width=22% bgcolor="#C0C0C0">';
1301 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','DOES_NOT_APPLY') || '</td><td width=22% bgcolor="#C0C0C0">';
1302 l_c_document := l_c_document||get_lookup_meaning('IGF_AW_FISAP_HTML_REP','DOES_NOT_APPLY') || '</td>';
1303 l_c_document := l_c_document||'<td width=22%>' || NVL(rec_gradprof.e, 0) || '</td><td width=22%>' || NVL(rec_gradprof.f, 0) || '</td><td width=22%>' || NVL(rec_gradprof.g, 0) || '</td></tr>';
1304
1305 ln_total_a := ln_total_a + NVL(rec_gradprof.a, 0);
1306 ln_total_b := ln_total_b + NVL(rec_gradprof.b, 0);
1307 --ln_total_c := ln_total_c + NVL(rec_gradprof.c, 0); -- Does Not Apply
1308 --ln_total_d := ln_total_d + NVL(rec_gradprof.d, 0); -- Does Not Apply
1309 ln_total_e := ln_total_e + NVL(rec_gradprof.e, 0);
1310 ln_total_f := ln_total_f + NVL(rec_gradprof.f, 0);
1311 ln_total_g := ln_total_g + NVL(rec_gradprof.g, 0);
1312
1313 -- 16th Row. Totals.
1314 ln_line_num := ln_line_num + 1;
1315 l_c_document := l_c_document || '<tr bgcolor="#ffffff"><td width=22%>' || ln_line_num || '</td><td width=22%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TOTAL');
1316 l_c_document := l_c_document || '</td><td width=22%>' || ln_total_a || '</td><td width=22%>' || ln_total_b || '</td><td width=22%>' || ln_total_c;
1317 l_c_document := l_c_document || '</td><td width=22%>' || ln_total_d || '</td><td width=22%>' || ln_total_e || '</td><td width=22%>' || ln_total_f;
1318 l_c_document := l_c_document || '</td><td width=22%>' || ln_total_g || '</td></tr>';
1319
1320 -- 17th Row. Total less than full time students.
1321 OPEN cur_ltfulltime(ln_batch_id);
1322 FETCH cur_ltfulltime INTO rec_ltfulltime;
1323 CLOSE cur_ltfulltime;
1324
1325 ln_line_num := ln_line_num + 1;
1326 l_c_document := l_c_document || '<tr bgcolor="#ffffff"><td width=22%>' || ln_line_num || '</td><td width=22%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TOTAL_LT_FT');
1327 l_c_document := l_c_document || '</td><td width=22%>' || NVL(rec_ltfulltime.a, 0) || '</td><td width=22%>' || NVL(rec_ltfulltime.b, 0) || '</td><td width=22%>' || NVL(rec_ltfulltime.c, 0);
1328 l_c_document := l_c_document || '</td><td width=22%>' || NVL(rec_ltfulltime.d, 0) || '</td><td width=22%>' || NVL(rec_ltfulltime.e, 0) || '</td><td width=22%>' || NVL(rec_ltfulltime.f, 0);
1329 l_c_document := l_c_document || '</td><td width=22%>' || NVL(rec_ltfulltime.g, 0) || '</td></tr>';
1330
1331 -- 18th Row. Total "Automatic Zero EFC" students
1332 OPEN cur_6a_efc(ln_batch_id);
1333 FETCH cur_6a_efc INTO rec_6a_efc;
1334 CLOSE cur_6a_efc;
1335
1336 ln_line_num := ln_line_num + 1;
1337 l_c_document := l_c_document || '<tr bgcolor="#ffffff"><td width=22%>' || ln_line_num || '</td><td width=22%>' || get_lookup_meaning('IGF_AW_FISAP_HTML_REP','TOTAL_AUTO_ZERO_EFC');
1338 l_c_document := l_c_document || '</td><td width=22%>' || NVL(rec_6a_efc.a, 0) || '</td><td width=22%>' || NVL(rec_6a_efc.b, 0) || '</td><td width=22%>' || NVL(rec_6a_efc.c, 0);
1339 l_c_document := l_c_document || '</td><td width=22%>' || NVL(rec_6a_efc.d, 0) || '</td><td width=22%>' || NVL(rec_6a_efc.e, 0) || '</td><td width=22%>' || NVL(rec_6a_efc.f, 0);
1340 l_c_document := l_c_document || '</td><td width=22%>' || NVL(rec_6a_efc.g, 0) || '</td></tr>';
1341
1342 l_c_document := l_c_document||'</table>';
1343
1344 WF_NOTIFICATION.WriteToClob(document, l_c_document);
1345
1346 END generate_partII;
1347
1348 END IGF_AW_FISAP_PKG;