1 PACKAGE BODY igf_ap_efc_subf AS
2 /* $Header: IGFAP32B.pls 120.1 2006/04/09 23:48:34 ridas noship $ */
3 /*
4 || Created By : gmuralid
5 || Created On : 11 Feb 2003
6 || Purpose : EFC Sub Functions Package, Bug# 2758804 , EFC build TD
7 ||
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || svuppala 16-Nov-2004 Bug # 3416849 Expected Family Contribution Updates 2005-2006
12 || gmuralid 09-03-2003 BUG# 2833795 - EFC Mismatch Base BUG - Added a extra parameter l_call_type to procedure c_efc
13 || gmuralid 08-03-2003 BUG# 2833795 - EFC Mismatch Base BUG
14 || gmuralid 06-03-2003 Modifed Rounding off mechanism
15 || gmuralid 04-03-2003 BUG#2831089 - Corrected population of intermediate values into isir record
16 || gmuralid 03-03-2003 BUG#2826603 - Implemented Null Handling In EEA calculation
17 || gmuralid 03-03-2003 BUG#2826603 - Included negative and non existant value check in set up cursors including state cursor
18 || gmuralid 17-02-2003 Included assumed values in sub functions
19 || (reverse chronological order - newest change first)
20
21 */
22
23 -- SUB FUNCTIONS for calculating EFC with FORMULA A
24 PROCEDURE a_p_inc ( p_p_inc OUT NOCOPY NUMBER) AS
25 /*
26 || Created By : gmuralid
27 || Created On : 11 Feb 2003
28 || Purpose : Procedure to get Parents Income , Bug# 2758804 , EFC build TD
29 ||
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 ||
34 || (reverse chronological order - newest change first)
35 */
36 -- Initialize the local variables with the Global variables.
37 l_adjusted_gross_income igf_ap_isir_matched.p_adjusted_gross_income%TYPE;
38 l_p_inc_work NUMBER;
39 l_tot_tax_inc NUMBER;
40 l_tot_untax_inc NUMBER;
41 l_tot_inc NUMBER;
42
43 -- Cursor to find out NOCOPY the ISIR information for the Student
44
45 BEGIN
46
47 -- Get Parents Adjusted Gross Income( If negative take 0)
48 IF NVL(NVL(igf_ap_efc_calc.isir_rec.a_parents_agi,igf_ap_efc_calc.isir_rec.p_adjusted_gross_income),0) <= 0 THEN
49 l_adjusted_gross_income := 0;
50 ELSE
51 l_adjusted_gross_income := NVL(igf_ap_efc_calc.isir_rec.a_parents_agi,igf_ap_efc_calc.isir_rec.p_adjusted_gross_income);
52 END IF;
53
54 -- Get Total Parents income earned from work
55 -- Father income earned from work + Mother income earned from work
56
57 l_p_inc_work := NVL(NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work),0) +
58 NVL(NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work),0);
59
60 -- Get the Parents Taxable income( If Taxable = Adjusted Gross Income Else
61 -- = Total Income earned from Work)
62 IF igf_ap_efc_calc.isir_rec.p_cal_tax_status IN ('1','2','3') THEN
63 l_tot_tax_inc := l_adjusted_gross_income;
64 ELSE
65 l_tot_tax_inc := l_p_inc_work;
66 END IF;
67
68 -- Total Untaxed Income and benifits
69 -- Total from FAFSA Worksheet A + Total from FAFSA Worksheet B
70 l_tot_untax_inc := NVL(igf_ap_efc_calc.isir_rec.p_income_wsa,0) + NVL(igf_ap_efc_calc.isir_rec.p_income_wsb,0);
71
72 -- Total Income = Total Taxable Income + Total Untaxable Income.
73 l_tot_inc := l_tot_tax_inc + l_tot_untax_inc;
74
75 -- Parents total Income = Total Income - Total from FAFSA Worksheet C
76 p_p_inc := l_tot_inc - NVL (NVL (igf_ap_efc_calc.isir_rec.a_p_total_wsc,igf_ap_efc_calc.isir_rec.p_income_wsc),0);
77 p_p_inc := ROUND(p_p_inc);
78
79 igf_ap_efc_calc.isir_rec.total_income := p_p_inc; --Assignment of intermediate values
80 igf_ap_efc_calc.isir_rec.secti := p_p_inc; --Assignment of intermediate values
81
82 EXCEPTION
83 WHEN OTHERS THEN
84 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
85 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_P_INC');
86 IGS_GE_MSG_STACK.ADD;
87 APP_EXCEPTION.RAISE_EXCEPTION;
88 END a_p_inc;
89
90
91 PROCEDURE a_allow_ag_p_inc ( p_p_inc IN NUMBER,
92 p_allow_ag_p_inc OUT NOCOPY NUMBER) AS
93 /*
94 || Created By : gmuralid
95 || Created On : 11 Feb 2003
96 || Purpose : Procedure to get allowances against Parents Income , Bug# 2758804 , EFC build TD
97 || Known limitations, enhancements or remarks :
98 || Change History :
99 || Who When What
100 || (reverse chronological order - newest change first)
101 */
102
103 l_p_taxes_paid igf_ap_isir_matched.p_taxes_paid%TYPE;
104 l_state igf_lookups_view.lookup_code%TYPE;
105 l_tax_allowance NUMBER;
106 l_f_sst NUMBER;
107 l_m_sst NUMBER;
108 l_ipa NUMBER;
109 l_eea NUMBER;
110
111 -- Cursor to find the Valid State
112 CURSOR state_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
113 SELECT lookup_code
114 FROM igf_lookups_view
115 WHERE lookup_type = 'IGF_AP_STATE_CODES'
116 AND lookup_code = cp_state;
117
118 -- Cursor to calculate State and other tax allowance
119 CURSOR state_allow_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
120 SELECT tax_rate
121 FROM igf_fc_state_tx txrng
122 WHERE txrng.table_code = 'A1'
123 AND (p_p_inc BETWEEN txrng.income_range_start AND txrng.income_range_end)
124 AND txrng.state_code = cp_state
125 AND txrng.s_award_year = igf_ap_efc_calc.p_sys_award_year;
126
127 -- Cursor to calculate Social Security Tax for Father and Mother.
128 CURSOR sst_cur(cp_inc_work igf_ap_isir_matched.m_income_work%TYPE) IS
129 SELECT tax_rate, amount, tax_rate_excess, amount_excess
130 FROM igf_fc_gen_tax_rts gtxrts
131 WHERE gtxrts.table_code = 'A2'
132 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
133 AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
134
135 -- Cursor to calculate Income Protection Allowance
136 CURSOR ipa_cur(cp_num_family_member igf_ap_isir_matched.p_num_family_member%TYPE,
137 cp_num_in_college igf_ap_isir_matched.p_num_in_college%TYPE) IS
138 SELECT ip_allowance_amt
139 FROM igf_fc_inc_prct ipa
140 WHERE ipa.table_code = 'A3'
141 AND ipa.s_award_year = igf_ap_efc_calc.p_sys_award_year
142 AND ipa.people_in_household = cp_num_family_member
143 AND ipa.students_in_household = cp_num_in_college ;
144
145 -- Cursor to calculate Employment Expense Allowance:
146 CURSOR eea_cur IS
147 SELECT empl_exp_allowance_rate1, empl_exp_allowance_amount1,empl_exp_allowance_rate2, empl_exp_allowance_amount2
148 FROM igf_fc_efc_frm_a efca
149 WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;
150
151 state_rec state_cur%ROWTYPE;
152 state_allow_rec state_allow_cur%ROWTYPE;
153 sst_rec sst_cur%ROWTYPE;
154 ipa_rec ipa_cur%ROWTYPE;
155 eea_rec eea_cur%ROWTYPE;
156 l_parent_cont NUMBER ;
157 l_student_cont NUMBER ;
158 p_n_fam NUMBER;
159 p_n_col NUMBER;
160 p_n_fam1 NUMBER;
161 p_n_col1 NUMBER;
162 l_state_cd VARCHAR2(30);
163
164
165 BEGIN
166 -- For Non Tax Filers and with Income Tax Paid Negative should be processed, process with 0
167 IF ( (NVL(igf_ap_efc_calc.isir_rec.p_cal_tax_status,'-1') NOT IN ('1','2','3'))
168 OR (NVL(NVL(igf_ap_efc_calc.isir_rec.a_p_us_tax_paid,igf_ap_efc_calc.isir_rec.p_taxes_paid),0) <= 0 ) ) THEN
169 l_p_taxes_paid := 0;
170 ELSE
171 l_p_taxes_paid := NVL(igf_ap_efc_calc.isir_rec.a_p_us_tax_paid,igf_ap_efc_calc.isir_rec.p_taxes_paid);
172 END IF;
173
174 -- Process to calculate State and other tax allowance only if Parents Income > 0
175 IF p_p_inc > 0 THEN
176 -- To find the proper State process in the order of
177 -- Parents state of legal residence (If invalid then)
178 -- Students state of legal residence (If invalid then)
179 -- State in the Student mailing address (If invalid then)
180 -- Blank or Invalid state
181
182 l_state_cd := igf_ap_efc_calc.isir_rec.p_state_legal_residence;
183
184 IF l_state_cd = 'FC' THEN
185 l_state_cd := 'OT';
186 END IF;
187
188
189 OPEN state_cur(l_state_cd);
190 FETCH state_cur INTO state_rec;
191 l_state := state_rec.lookup_code;
192 CLOSE state_cur;
193
194 IF l_state IS NULL THEN
195
196 l_state_cd := igf_ap_efc_calc.isir_rec.s_state_legal_residence;
197
198 IF l_state_cd = 'FC' THEN
199 l_state_cd := 'OT';
200 END IF;
201
202 OPEN state_cur(l_state_cd);
203 FETCH state_cur INTO state_rec;
204 l_state := state_rec.lookup_code;
205 CLOSE state_cur;
206 END IF;
207
208 IF l_state IS NULL THEN
209
210 l_state_cd := igf_ap_efc_calc.isir_rec.perm_state;
211
212 IF l_state_cd = 'FC' THEN
213 l_state_cd := 'OT';
214 END IF;
215
216 OPEN state_cur(l_state_cd);
217 FETCH state_cur INTO state_rec;
218 l_state := state_rec.lookup_code;
219 CLOSE state_cur;
220 END IF;
221
222 IF l_state IS NULL THEN
223 l_state := 'BL';
224 END IF;
225
226 OPEN state_allow_cur(l_state);
227 FETCH state_allow_cur INTO state_allow_rec;
228
229 IF state_allow_cur%NOTFOUND THEN
230 CLOSE state_allow_cur;
231 OPEN state_allow_cur('OT');
232 FETCH state_allow_cur INTO state_allow_rec;
233 IF state_allow_cur%NOTFOUND THEN
234 CLOSE state_allow_cur;
235 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_TAX_SETUP');
236 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A1');
237 IGS_GE_MSG_STACK.ADD;
238 RAISE exception_in_setup;
239 END IF;
240 END IF;
241 CLOSE state_allow_cur;
242
243 -- Determine the State and other tax allowance
244 l_tax_allowance := ( p_p_inc * state_allow_rec.tax_rate ) / 100 ;
245
246 -- State and other tax allowance can not be Negative
247 IF ( l_tax_allowance < 0 ) THEN
248 l_tax_allowance := 0;
249 END IF;
250
251 ELSE
252 -- If Parents Income <= 0 then assume Tax Allowance to be 0
253 l_tax_allowance := 0;
254 END IF;
255
256
257 igf_ap_efc_calc.isir_rec.state_tax_allow := ROUND(l_tax_allowance); --Assignment of intermediate values
258 igf_ap_efc_calc.isir_rec.secstx := ROUND(l_tax_allowance); --Assignment of intermediate values
259
260 -- Calculating the Social Security Tax for Father
261
262 IF NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work) IS NOT NULL THEN
263 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work),0));
264 FETCH sst_cur INTO sst_rec;
265 IF sst_cur%NOTFOUND THEN
266 CLOSE sst_cur;
267 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
268 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A2');
269 IGS_GE_MSG_STACK.ADD;
270 RAISE exception_in_setup;
271 END IF;
272 CLOSE sst_cur;
273
274 IF sst_rec.tax_rate IS NULL THEN
275 l_f_sst := sst_rec.amount +
276 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work),0) - sst_rec.amount_excess)) / 100 );
277 ELSE
278 l_f_sst := ( sst_rec.tax_rate * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work),0) ) / 100 );
279 END IF ;
280
281 -- Social Security Tax can not be Negative
282 IF ( l_f_sst < 0 ) THEN
283 l_f_sst := 0;
284 END IF;
285 ELSE
286 l_f_sst := 0;
287 END IF;
288
289 l_f_sst := ROUND(l_f_sst) ;
290
291 -- Calculating the Social Security Tax for Mother
292 IF NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work) IS NOT NULL THEN
293 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work),0));
294 FETCH sst_cur INTO sst_rec;
295 IF sst_cur%NOTFOUND THEN
296 CLOSE sst_cur;
297 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
298 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A2');
299 IGS_GE_MSG_STACK.ADD;
300 RAISE exception_in_setup;
301 END IF;
302 CLOSE sst_cur;
303
304 IF sst_rec.tax_rate IS NULL THEN
305 l_m_sst := sst_rec.amount +
306 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work),0) - sst_rec.amount_excess)) / 100 );
307 ELSE
308 l_m_sst := ( sst_rec.tax_rate * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work),0) ) / 100) ;
309 END IF ;
310
311 -- Social Security Tax can not be Negative
312 IF ( l_m_sst < 0 ) THEN
313 l_m_sst := 0;
314 END IF;
315 ELSE
316 l_m_sst := 0;
317 END IF;
318
319 l_m_sst := ROUND(l_m_sst) ;
320
321 p_n_fam := NVL(igf_ap_efc_calc.isir_rec.a_parents_num_family,igf_ap_efc_calc.isir_rec.p_num_family_member);
322 p_n_col := NVL(igf_ap_efc_calc.isir_rec.a_parents_num_college,igf_ap_efc_calc.isir_rec.p_num_in_college);
323 IF p_n_fam IS NOT NULL AND p_n_col IS NOT NULL THEN
324 -- Calculate Income Protection Allowance of Parent
325 OPEN ipa_cur(p_n_fam,p_n_col);
326 FETCH ipa_cur INTO ipa_rec;
327
328 IF ipa_cur%NOTFOUND THEN
329 CLOSE ipa_cur;
330 IF( p_n_fam IS NULL) OR (p_n_col IS NULL ) THEN
331 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_INC_PRT_ALW_SETUP');
332 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A3');
333 IGS_GE_MSG_STACK.ADD;
334 RAISE exception_in_setup;
335 END IF;
336
337 p_n_fam1 := 0;
338 p_n_col1 := 0;
339
340 IF p_n_fam > 6 THEN
341 p_n_fam1 := p_n_fam - 6;
342 p_n_fam := 6 ;
343 END IF;
344
345 IF p_n_col > 5 THEN
346 p_n_col1 := p_n_col - 5;
347 p_n_col := 5 ;
348 END IF;
349
350 get_par_stud_cont( igf_ap_efc_calc.p_sys_award_year, l_parent_cont,l_student_cont );
351
352 OPEN ipa_cur(p_n_fam,p_n_col);
353 FETCH ipa_cur INTO ipa_rec;
354 CLOSE ipa_cur ;
355 l_ipa := (ipa_rec.ip_allowance_amt + (p_n_fam1 * l_parent_cont ) ) - ((p_n_col1 * l_student_cont ));
356 ELSE
357 CLOSE ipa_cur; --IF IPA CUR FOUND
358 l_ipa := ipa_rec.ip_allowance_amt;
359 END IF;
360
361 ELSE
362 l_ipa := 0;
363
364 END IF;
365
366 igf_ap_efc_calc.isir_rec.income_protection_allow := l_ipa; --Assignment of intermediate values
367 igf_ap_efc_calc.isir_rec.secipa := l_ipa; --Assignment of intermediate values
368
369 OPEN eea_cur;
373 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCA_SETUP');
370 FETCH eea_cur INTO eea_rec;
371 IF eea_cur%NOTFOUND THEN
372 CLOSE eea_cur;
374 IGS_GE_MSG_STACK.ADD;
375 RAISE exception_in_setup;
376 END IF;
377 CLOSE eea_cur;
378
379 -- Two working parents: 35% of the lesser of the
380 -- earned incomes, or $2,900, whichever is less
381 --IF NVL(igf_ap_efc_calc.isir_rec.a_p_marital_status,igf_ap_efc_calc.isir_rec.p_marital_status) = '1' THEN
382
383 IF NVL(igf_ap_efc_calc.isir_rec.a_p_marital_status,igf_ap_efc_calc.isir_rec.p_marital_status ) = '1' THEN
384 IF ( NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work) IS NOT NULL
385 AND NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work) IS NOT NULL)
386 THEN
387 l_eea := LEAST((NVL(eea_rec.empl_exp_allowance_rate2,0) *
388 LEAST(greatest(NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work),0),
389 greatest(NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work),0))/100),
390 NVL(eea_rec.empl_exp_allowance_amount2,0));
391
392 ELSIF ( ( NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work) IS NOT NULL
393 AND NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work) IS NULL)
394 OR ( NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work) IS NULL
395 AND NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work) IS NOT NULL))
396 THEN
397 -- Two-parent families, one working parent EEA is 0.
398 l_eea := 0;
399 ELSE
400 l_eea := 0;
401
402 END IF;
403
404
405 ELSE
406 -- One Parent Family
407 -- One-parent families: 35% of earned income,
408 -- or $2,900, whichever is less
409
410 l_eea := greatest(NVL (NVL(igf_ap_efc_calc.isir_rec.a_f_work_income,igf_ap_efc_calc.isir_rec.f_income_work),0), 0) ;
411 l_eea := greatest(NVL (NVL(igf_ap_efc_calc.isir_rec.a_m_work_income,igf_ap_efc_calc.isir_rec.m_income_work), 0) ,0,l_eea) ;
412
413 l_eea := LEAST(
414 (NVL(eea_rec.empl_exp_allowance_rate1,0) * l_eea/100),
415 NVL(eea_rec.empl_exp_allowance_amount1,0)
416 );
417
418 END IF;
419
420
421 igf_ap_efc_calc.isir_rec.employment_allow := ROUND(l_eea); --Assignment of intermediate values
422 igf_ap_efc_calc.isir_rec.secea := ROUND(l_eea); --Assignment of intermediate values
423
424 -- Allowance against Parents Income
425 p_allow_ag_p_inc := ROUND(l_p_taxes_paid) + ROUND(l_tax_allowance) + ROUND(l_f_sst) + ROUND(l_m_sst) + ROUND(l_ipa) + ROUND(l_eea);
426 -- p_allow_ag_p_inc := ROUND( p_allow_ag_p_inc);
427
428 igf_ap_efc_calc.isir_rec.allow_total_income := p_allow_ag_p_inc; --Assignment of intermediate values
429 igf_ap_efc_calc.isir_rec.secati := p_allow_ag_p_inc; --Assignment of intermediate values
430
431
432 EXCEPTION
433 WHEN exception_in_setup THEN
434 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
435 WHEN OTHERS THEN
436 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
437 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_ALLOW_AG_P_INC');
438 IGS_GE_MSG_STACK.ADD;
439 APP_EXCEPTION.RAISE_EXCEPTION;
440 END a_allow_ag_p_inc;
441
442
443 PROCEDURE a_available_inc ( p_p_inc IN NUMBER,
444 p_allow_ag_p_inc IN NUMBER,
445 p_available_income OUT NOCOPY NUMBER) AS
446 /*
447 || Created By : gmuralid
448 || Created On : 11 Feb 2003
449 || Purpose : Procedure to get available Income , Bug# 2758804 , EFC build TD
450 ||
451 || Known limitations, enhancements or remarks :
452 || Change History :
453 || Who When What
454 || (reverse chronological order - newest change first)
455 */
456 BEGIN
457 -- Total Income - Total Allowance
458 p_available_income := p_p_inc - p_allow_ag_p_inc;
459 p_available_income := ROUND( p_available_income);
460
461
462 igf_ap_efc_calc.isir_rec.available_income := p_available_income; --Assignment of intermediate values
463 igf_ap_efc_calc.isir_rec.secai := p_available_income; --Assignment of intermediate values
464
465 END a_available_inc;
466
467
468
469 PROCEDURE get_age_older_parent(age_older_parent OUT NOCOPY NUMBER) AS
470 /*
471 || Created By : nsidana
472 || Created On : 11/20/2003
473 || Purpose : Procedure to get the age of the older parent. This is introduced as part of
474 || FA129 build.
475 ||
476 || Known limitations, enhancements or remarks :
477 || Change History :
478 || Who When What
479 || (reverse chronological order - newest change first)
480
481 */
482 l_fathers_age NUMBER(3) := NULL;
483 l_mothers_age NUMBER(3) := NULL;
484 l_base_date DATE;
485
486 BEGIN
487
491 l_base_date := TO_DATE('31-12-2004','DD-MM-YYYY');
488 IF igf_ap_efc_calc.p_sys_award_year IN ( '0405','0506','0607') THEN
489
490 IF igf_ap_efc_calc.p_sys_award_year = '0405' THEN
492 ELSIF igf_ap_efc_calc.p_sys_award_year = '0506' THEN
493 l_base_date := TO_DATE('31-12-2005','DD-MM-YYYY');
494 ELSIF igf_ap_efc_calc.p_sys_award_year = '0607' THEN
495 l_base_date := TO_DATE('31-12-2006','DD-MM-YYYY');
496 END IF;
497
498 IF (igf_ap_efc_calc.isir_rec.father_step_father_birth_date IS NOT NULL )
499 THEN
500 -- l_fathers_age := FLOOR((l_base_date - igf_ap_efc_calc.isir_rec.father_step_father_birth_date)/365);
501 l_fathers_age := FLOOR((TO_NUMBER(TO_CHAR(l_base_date,'YYYY')) - TO_NUMBER(TO_CHAR(igf_ap_efc_calc.isir_rec.father_step_father_birth_date,'YYYY'))));
502 END IF;
503
504 IF (igf_ap_efc_calc.isir_rec.mother_step_mother_birth_date IS NOT NULL)
505 THEN
506 -- l_mothers_age := FLOOR ((l_base_date - igf_ap_efc_calc.isir_rec.mother_step_mother_birth_date)/365);
507 l_mothers_age := FLOOR ((TO_NUMBER(TO_CHAR(l_base_date,'YYYY') - TO_NUMBER(TO_CHAR(igf_ap_efc_calc.isir_rec.mother_step_mother_birth_date,'YYYY')))));
508 END IF;
509
510 IF l_fathers_age IS NULL AND l_mothers_age IS NULL THEN
511 age_older_parent := NULL;
512 ELSIF NVL(l_fathers_age , 0) > NVL(l_mothers_age , 0) THEN
513 age_older_parent := l_fathers_age;
514 ELSE
515 age_older_parent := l_mothers_age;
516 END IF;
517 END IF;
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
522 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.GET_AGE_OLDER_PARENT');
523 IGS_GE_MSG_STACK.ADD;
524 APP_EXCEPTION.RAISE_EXCEPTION;
525 END get_age_older_parent ;
526
527
528 PROCEDURE a_p_cont_assets ( p_p_cont_assets OUT NOCOPY NUMBER) AS
529 /*
530 || Created By : gmuralid
531 || Created On : 11 Feb 2003
532 || Purpose : Procedure to get Parents Contribution from assets , Bug# 2758804 , EFC build TD
533 ||
534 || Known limitations, enhancements or remarks :
535 || Change History :
536 || Who When What
537 || nsidana 11/20/2003 FA129 EFC updates for 2004-2005.
538 || (reverse chronological order - newest change first)
539
540 */
541 l_investment_networth igf_ap_isir_matched.p_investment_networth%TYPE;
542 l_business_networth igf_ap_isir_matched.p_business_networth%TYPE;
543 l_adj_business_networth NUMBER;
544 l_cash_saving igf_ap_isir_matched.p_cash_saving%TYPE;
545 l_net_worth NUMBER;
546 l_age_parent NUMBER;
547 l_edu_sav_assets igf_fc_ast_pc_dt.parent2_allowance%TYPE;
548 l_d_net_worth NUMBER;
549
550 -- Cursor to find the Setting for Formula A
551 CURSOR efcA_cur IS
552 SELECT A5_default_age, parent_asset_conv_rate
553 FROM igf_fc_efc_frm_a efca
554 WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;
555
556 -- Cursor to calculate Asset Protection Allowance
557 CURSOR asset_cur(cp_age_older_parent igf_fc_ast_pc_dt.older_parent_age%TYPE) IS
558 SELECT parent1_allowance, parent2_allowance
559 FROM igf_fc_ast_pc_dt apdt
560 WHERE apdt.s_award_year = igf_ap_efc_calc.p_sys_award_year
561 AND apdt.table_code = 'A5'
562 AND apdt.older_parent_age = cp_age_older_parent;
563
564 -- Cursor to calculate Adjusted Net worth of Business/farm
565 CURSOR business_networth_cur(cp_business_networth igf_ap_isir_matched.p_business_networth%TYPE) IS
566 SELECT tax_rate, amount, tax_rate_excess, amount_excess
567 FROM igf_fc_gen_tax_rts gtxrts
568 WHERE gtxrts.table_code = 'A4'
569 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
570 AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
571
572 business_networth_rec business_networth_cur%ROWTYPE;
573 efcA_rec efcA_cur%ROWTYPE;
574 asset_rec asset_cur%ROWTYPE;
575
576 BEGIN
577
578 -- Get Net worth of investments( Can not be Negative)
579 IF NVL(igf_ap_efc_calc.isir_rec.p_investment_networth,0) <= 0 THEN
580 l_investment_networth := 0;
581 ELSE
582 l_investment_networth := igf_ap_efc_calc.isir_rec.p_investment_networth;
583 END IF;
584
585 -- Get Net Worth of business/investments farm(Can not be Negative)
586 IF NVL(igf_ap_efc_calc.isir_rec.p_business_networth,0) <= 0 THEN
587 l_business_networth := 0;
588 ELSE
589 l_business_networth := igf_ap_efc_calc.isir_rec.p_business_networth;
590 END IF;
591
592 -- Get Adjusted net worth of Business/Farm
593 OPEN business_networth_cur(l_business_networth);
594 FETCH business_networth_cur INTO business_networth_rec;
595 IF business_networth_cur%NOTFOUND THEN
596 CLOSE business_networth_cur;
597 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
598 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A4');
599 IGS_GE_MSG_STACK.ADD;
600 RAISE exception_in_setup;
601 END IF;
602 CLOSE business_networth_cur;
603
604 IF business_networth_rec.tax_rate IS NULL THEN
608 l_adj_business_networth := ( business_networth_rec.tax_rate * l_business_networth ) / 100 ;
605 l_adj_business_networth := business_networth_rec.amount +
606 (( business_networth_rec.tax_rate_excess * (l_business_networth - business_networth_rec.amount_excess)) / 100 );
607 ELSE
609 END IF ;
610
611 l_adj_business_networth := ROUND(l_adj_business_networth) ;
612
613 -- Get Cash, Savings and Checking
614 IF igf_ap_efc_calc.isir_rec.p_cash_saving IS NULL THEN
615 l_cash_saving := 0;
616 ELSE
617 l_cash_saving := igf_ap_efc_calc.isir_rec.p_cash_saving;
618 END IF;
619
620 -- Get Net Worth
621 -- (Investment Net worth + Adjusted Business Net Worth + Cash Saving)
622 l_net_worth := l_investment_networth + l_adj_business_networth + l_cash_saving ;
623
624 igf_ap_efc_calc.isir_rec.efc_networth := l_net_worth; --Assignment of intermediate values
625 igf_ap_efc_calc.isir_rec.secnw := l_net_worth; --Assignment of intermediate values
626
627 -- If parent Age is not specified then get the Default Age for Formula A
628 IF (igf_ap_efc_calc.p_sys_award_year = '0304' ) THEN
629 l_age_parent := igf_ap_efc_calc.isir_rec.age_older_parent;
630 ELSIF (igf_ap_efc_calc.p_sys_award_year = '0405') THEN
631 igf_ap_efc_subf.get_age_older_parent(l_age_parent);
632 ELSIF (igf_ap_efc_calc.p_sys_award_year = '0506') THEN
633 igf_ap_efc_subf.get_age_older_parent(l_age_parent);
634 ELSIF (igf_ap_efc_calc.p_sys_award_year = '0607') THEN
635 igf_ap_efc_subf.get_age_older_parent(l_age_parent);
636 END IF;
637
638 IF (l_age_parent IS NULL) THEN
639 l_age_parent := NVL(efca_rec.A5_default_age,45);
640 ELSIF (l_age_parent < 25) THEN
641 l_age_parent := 25;
642 ELSIF (l_age_parent > 65) THEN
643 l_age_parent := 65;
644 END IF;
645
646 -- Get Education savings and Asset Protection Allowance
647 OPEN asset_cur(l_age_parent);
648 FETCH asset_cur INTO asset_rec;
649 IF asset_cur%NOTFOUND THEN
650 CLOSE asset_cur;
651 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_ASSET_SETUP');
652 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A5');
653 IGS_GE_MSG_STACK.ADD;
654 RAISE exception_in_setup;
655 END IF;
656 CLOSE asset_cur;
657
658 IF NVL(igf_ap_efc_calc.isir_rec.a_p_marital_status,igf_ap_efc_calc.isir_rec.p_marital_status) = '1' THEN
659 l_edu_sav_assets := NVL(asset_rec.parent2_allowance,0);
660 ELSE
661 l_edu_sav_assets := NVL(asset_rec.parent1_allowance,0);
662 END IF;
663
664 igf_ap_efc_calc.isir_rec.asset_protect_allow := l_edu_sav_assets; --Assignment of intermediate values
665 igf_ap_efc_calc.isir_rec.secapa := l_edu_sav_assets; --Assignment of intermediate values
666
667 -- Get Discretionary Net Worth
668 l_d_net_worth := l_net_worth - l_edu_sav_assets ;
669 igf_ap_efc_calc.isir_rec.discretionary_networth := l_d_net_worth; --Assignment of intermediate values
670 igf_ap_efc_calc.isir_rec.secdnw := l_d_net_worth; --Assignment of intermediate values
671
672 -- Get the Setting for Formula A
673 OPEN efca_cur;
674 FETCH efca_cur INTO efca_rec;
675 IF efca_cur%NOTFOUND THEN
676 CLOSE efca_cur;
677 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCA_SETUP');
678 IGS_GE_MSG_STACK.ADD;
679 RAISE exception_in_setup;
680 END IF;
681 CLOSE efca_cur;
682
683 -- Get Contribution from Assets
684 p_p_cont_assets := l_d_net_worth * NVL(efcA_rec.parent_asset_conv_rate,0)/100;
685
686 IF p_p_cont_assets < 0 THEN
687 p_p_cont_assets := 0;
688 END IF;
689
690 p_p_cont_assets := ROUND( p_p_cont_assets );
691
692
693 igf_ap_efc_calc.isir_rec.parents_cont_from_assets := p_p_cont_assets; --Assignment of intermediate values
694 igf_ap_efc_calc.isir_rec.secpca := p_p_cont_assets; --Assignment of intermediate values
695
696 EXCEPTION
697 WHEN exception_in_setup THEN
698 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
699 WHEN OTHERS THEN
700 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
701 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_P_CONT_ASSETS');
702 IGS_GE_MSG_STACK.ADD;
703 APP_EXCEPTION.RAISE_EXCEPTION;
704 END a_p_cont_assets;
705
706
707 PROCEDURE a_p_cont ( p_available_income IN NUMBER,
708 p_p_cont_assets IN NUMBER,
709 p_p_aai OUT NOCOPY NUMBER,
710 p_p_cont OUT NOCOPY NUMBER) AS
711 /*
712 || Created By : gmuralid
713 || Created On : 11 Feb 2003
714 || Purpose : Procedure to get Parents Contribution , Bug# 2758804 , EFC build TD
715 ||
716 || Known limitations, enhancements or remarks :
717 || Change History :
718 || Who When What
719 || (reverse chronological order - newest change first)
720 */
721
722 l_p_cont_aai NUMBER;
723
724 -- Cursor to calculate Parents Contribution from AAI
725 CURSOR p_cont_aai_cur(cp_p_aai NUMBER) IS
726 SELECT tax_rate, amount, tax_rate_excess, amount_excess
730 AND (cp_p_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
727 FROM igf_fc_gen_tax_rts gtxrts
728 WHERE gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
729 AND gtxrts.table_code = 'A6'
731
732 p_cont_aai_rec p_cont_aai_cur%ROWTYPE;
733
734 BEGIN
735 -- Get Adjusted Available Income
736 -- ( Available Income + Contribution from Assets)
737
738 p_p_aai := p_available_income + p_p_cont_assets;
739
740 igf_ap_efc_calc.isir_rec.adjusted_available_income := p_p_aai; --Assignment of intermediate values
741 igf_ap_efc_calc.isir_rec.secaai := p_p_aai; --Assignment of intermediate values
742
743
744 -- Get Total Parents contribution from Adjusted Available Income
745 OPEN p_cont_aai_cur(p_p_aai);
746 FETCH p_cont_aai_cur INTO p_cont_aai_rec;
747 IF p_cont_aai_cur%NOTFOUND THEN
748 CLOSE p_cont_aai_cur;
749 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
750 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A6');
751 IGS_GE_MSG_STACK.ADD;
752 RAISE exception_in_setup;
753 END IF;
754 CLOSE p_cont_aai_cur;
755
756 IF p_cont_aai_rec.tax_rate IS NULL THEN
757 l_p_cont_aai := p_cont_aai_rec.amount +
758 (( p_cont_aai_rec.tax_rate_excess * (p_p_aai - p_cont_aai_rec.amount_excess)) / 100 );
759 ELSE
760 l_p_cont_aai := ( p_cont_aai_rec.tax_rate * p_p_aai ) / 100 ;
761 END IF ;
762
763 -- Total Parents contribution from Adjusted Available Income
764 -- (Can not be Negative)
765 IF l_p_cont_aai < 0 THEN
766 l_p_cont_aai := 0;
767 END IF;
768
769 -- Get parents contribution (Total Parents contribution from AAI/Number in college in 2001-02)
770 -- (Can not be Negative)
771 p_p_cont := ROUND(l_p_cont_aai)/ NVL(NVL(igf_ap_efc_calc.isir_rec.a_parents_num_college,igf_ap_efc_calc.isir_rec.p_num_in_college),1);
772
773 IF p_p_cont < 0 THEN
774 p_p_cont := 0;
775
776 END IF;
777
778 p_p_cont := ROUND( p_p_cont );
779
780 igf_ap_efc_calc.isir_rec.parents_contribution := p_p_cont; --Assignment of intermediate values
781 igf_ap_efc_calc.isir_rec.secpc := p_p_cont; --Assignment of intermediate values
782
783 l_p_cont_aai := ROUND( l_p_cont_aai );
784
785 igf_ap_efc_calc.isir_rec.total_parent_contribution := l_p_cont_aai; --Assignment of intermediate values
786 igf_ap_efc_calc.isir_rec.sectpc := l_p_cont_aai; --Assignment of intermediate values
787
788 EXCEPTION
789 WHEN EXCEPTION_IN_SETUP THEN
790 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
791 WHEN OTHERS THEN
792 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
793 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_P_CONT');
794 IGS_GE_MSG_STACK.ADD;
795 APP_EXCEPTION.RAISE_EXCEPTION;
796 END a_p_cont;
797
798
799 PROCEDURE a_s_inc ( p_s_inc OUT NOCOPY NUMBER) AS
800 /*
801 || Created By : gmuralid
802 || Created On : 11 Feb 2003
803 || Purpose : Procedure to get Students Income , Bug# 2758804 , EFC build TD
804 ||
805 || Known limitations, enhancements or remarks :
806 || Change History :
807 || Who When What
808 || (reverse chronological order - newest change first)
809 */
810 -- Initialize the local variables with the Global variables.
811
812 l_adjusted_gross_income igf_ap_isir_matched.s_adjusted_gross_income%TYPE;
813 l_s_inc_work NUMBER;
814 l_tot_tax_inc NUMBER;
815 l_tot_untax_inc NUMBER;
816 l_tot_inc NUMBER;
817
818 BEGIN
819 -- Get Student's Adjusted Gross Income(If negative take 0)
820 IF NVL(NVL(igf_ap_efc_calc.isir_rec.a_student_agi,igf_ap_efc_calc.isir_rec.s_adjusted_gross_income),0) <= 0 THEN
821 l_adjusted_gross_income := 0;
822 ELSE
823 l_adjusted_gross_income := NVL(igf_ap_efc_calc.isir_rec.a_student_agi,igf_ap_efc_calc.isir_rec.s_adjusted_gross_income);
824 END IF;
825
826 -- Get Student's income earned from work
827 l_s_inc_work := NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0);
828
829 -- Get the Student's Taxable income( If Taxable Then Adjusted Gross Income Else
830 -- Income earned from Work)
831 IF igf_ap_efc_calc.isir_rec.s_cal_tax_status IN ('1','2','3') THEN
832 l_tot_tax_inc := l_adjusted_gross_income;
833 ELSE
834 l_tot_tax_inc := l_s_inc_work;
835 END IF;
836
837 -- Total Untaxed Income and benefits
838 -- Total from FAFSA Worksheet A + Total from FAFSA Worksheet B
839 l_tot_untax_inc := NVL(igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsa,0) +
840 NVL(igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsb,0);
841
842 -- Total Income = Total Taxable Income + Total Untaxable Income.
843 l_tot_inc := NVL(l_tot_tax_inc,0) + NVL(l_tot_untax_inc,0);
844
845 -- Student's total Income = Total Income - Total from FAFSA Worksheet C
846 p_s_inc := l_tot_inc - NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_total_wsc,igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsc),0);
847 p_s_inc := ROUND( p_s_inc );
851
848
849 igf_ap_efc_calc.isir_rec.student_total_income := p_s_inc; --Assignment of intermediate values
850 igf_ap_efc_calc.isir_rec.secsti := p_s_inc; --Assignment of intermediate values
852 igf_ap_efc_calc.isir_rec.fti := igf_ap_efc_calc.isir_rec.student_total_income + igf_ap_efc_calc.isir_rec.total_income;
853 igf_ap_efc_calc.isir_rec.secfti := igf_ap_efc_calc.isir_rec.student_total_income + igf_ap_efc_calc.isir_rec.total_income;
854
855
856 EXCEPTION
857 WHEN OTHERS THEN
858 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
859 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_S_INC');
860 IGS_GE_MSG_STACK.ADD;
861 APP_EXCEPTION.RAISE_EXCEPTION;
862 END a_s_inc;
863
864
865 PROCEDURE a_allow_ag_s_inc ( p_s_inc IN NUMBER,
866 p_p_aai IN NUMBER,
867 p_allow_ag_s_inc OUT NOCOPY NUMBER) AS
868 /*
869 || Created By : gmuralid
870 || Created On : 11 Feb 2003
871 || Purpose : Procedure to get Allowances against Student's Income , Bug# 2758804 , EFC build TD
872 ||
873 || Known limitations, enhancements or remarks :
874 || Change History :
875 || Who When What
876 || (reverse chronological order - newest change first)
877 */
878 -- Initialize the local variables with the Global variables.
879
880 l_s_taxes_paid igf_ap_isir_matched.p_taxes_paid%TYPE;
881 l_state igf_lookups_view.lookup_code%TYPE;
882 l_tax_allowance NUMBER;
883 l_s_sst NUMBER;
884 l_ipa NUMBER;
885 l_p_aai NUMBER;
886 l_state_cd VARCHAR2(30);
887
888 -- Cursor to find the Valid State
889 CURSOR state_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
890 SELECT lookup_code
891 FROM igf_lookups_view
892 WHERE lookup_type = 'IGF_AP_STATE_CODES'
893 AND lookup_code = cp_state;
894
895 -- Cursor to calculate State and other tax allowance
896 CURSOR state_allow_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
897 SELECT tax_rate
898 FROM igf_fc_state_tx txrng
899 WHERE txrng.s_award_year = igf_ap_efc_calc.p_sys_award_year
900 AND txrng.table_code = 'A7'
901 AND txrng.state_code = cp_state;
902
903 -- Cursor to calculate Social Security Tax for Father and Mother.
904
905 CURSOR sst_cur(cp_inc_work igf_ap_isir_matched.m_income_work%TYPE) IS
906 SELECT tax_rate, amount, tax_rate_excess, amount_excess
907 FROM igf_fc_gen_tax_rts gtxrts
908 WHERE gtxrts.table_code = 'A2'
909 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
910 AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
911
912 -- Cursor to calculate Income Protection Allowance
913 CURSOR ipa_cur IS
914 SELECT stud_inc_prot_allowance
915 FROM igf_fc_efc_frm_a efca
916 WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;
917
918 state_rec state_cur%ROWTYPE;
919 state_allow_rec state_allow_cur%ROWTYPE;
920 sst_rec sst_cur%ROWTYPE;
921 ipa_rec ipa_cur%ROWTYPE;
922 BEGIN
923
924
925 -- For Non Tax Filers OR with Income Tax Paid Negative should be processed, process with 0
926 IF ((NVL(igf_ap_efc_calc.isir_rec.s_cal_tax_status,'-1') NOT IN ('1','2','3'))
927 OR ( NVL (NVL (igf_ap_efc_calc.isir_rec.a_s_us_tax_paid,igf_ap_efc_calc.isir_rec.s_fed_taxes_paid),0) <= 0))
928 THEN
929 l_s_taxes_paid := 0;
930 ELSE
931 l_s_taxes_paid := NVL(igf_ap_efc_calc.isir_rec.a_s_us_tax_paid,igf_ap_efc_calc.isir_rec.s_fed_taxes_paid);
932 END IF;
933
934 -- Process to calculate State and other tax allowance only if Student's Income > 0
935 IF p_s_inc > 0 THEN
936 -- To find the proper State process in the order of
937 -- Students state of legal residence (If invalid then)
938 -- State in the Students mailing address (If invalid then) -- Parents state of legal residence (If invalid then)
939 -- Blank or Invalid state
940
941 l_state_cd := igf_ap_efc_calc.isir_rec.s_state_legal_residence;
942 IF l_state_cd = 'FC' THEN
943 l_state_cd := 'OT';
944 END IF;
945
946 OPEN state_cur(l_state_cd);
947 FETCH state_cur INTO state_rec;
948 l_state := state_rec.lookup_code;
949 CLOSE state_cur;
950
951 IF l_state IS NULL THEN
952
953 l_state_cd := igf_ap_efc_calc.isir_rec.perm_state;
954
955 IF l_state_cd = 'FC' THEN
956 l_state_cd := 'OT';
957 END IF;
958
959 OPEN state_cur(l_state_cd);
960 FETCH state_cur INTO state_rec;
961 l_state := state_rec.lookup_code;
962 CLOSE state_cur;
963 END IF;
964
965 IF l_state IS NULL THEN
966
967 l_state_cd := igf_ap_efc_calc.isir_rec.p_state_legal_residence;
968 IF l_state_cd = 'FC' THEN
969 l_state_cd := 'OT';
970 END IF;
971
972 OPEN state_cur(l_state_cd);
973 FETCH state_cur INTO state_rec;
974 l_state := state_rec.lookup_code;
975 CLOSE state_cur;
979 l_state := 'BL';
976 END IF;
977
978 IF l_state IS NULL THEN
980 END IF;
981
982 OPEN state_allow_cur(l_state);
983 FETCH state_allow_cur INTO state_allow_rec;
984 IF state_allow_cur%NOTFOUND THEN
985 CLOSE state_allow_cur;
986 OPEN state_allow_cur('OT');
987 FETCH state_allow_cur INTO state_allow_rec;
988 IF state_allow_cur%NOTFOUND THEN
989 CLOSE state_allow_cur;
990 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_TAX_SETUP');
991 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A7');
992 IGS_GE_MSG_STACK.ADD;
993 RAISE exception_in_setup;
994 END IF;
995 END IF;
996 CLOSE state_allow_cur;
997
998 -- Determine the State and other tax allowance
999 l_tax_allowance := ( p_s_inc * state_allow_rec.tax_rate ) / 100 ;
1000
1001 -- State and other tax allowance can not be Negative
1002 IF ( l_tax_allowance < 0 ) THEN
1003 l_tax_allowance := 0;
1004 END IF;
1005
1006 ELSE -- If Student's Income <= 0 then assume Tax Allowance to be 0
1007 l_tax_allowance := 0;
1008 END IF;
1009
1010 l_tax_allowance := ROUND(l_tax_allowance) ;
1011
1012 -- Calculating the Social Security Tax for Student
1013 IF NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL THEN
1014 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0));
1015 FETCH sst_cur INTO sst_rec;
1016 IF sst_cur%NOTFOUND THEN
1017 CLOSE sst_cur;
1018 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
1019 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A2');
1020 IGS_GE_MSG_STACK.ADD;
1021 RAISE exception_in_setup;
1022 END IF;
1023 CLOSE sst_cur;
1024
1025 IF sst_rec.tax_rate IS NULL THEN
1026 l_s_sst := sst_rec.amount +
1027 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) - sst_rec.amount_excess)) / 100 );
1028 ELSE
1029 l_s_sst := ( sst_rec.tax_rate * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) ) / 100 );
1030 END IF ;
1031
1032 -- Social Security Tax can not be Negative
1033 IF ( l_s_sst < 0 ) THEN
1034 l_s_sst := 0;
1035 END IF;
1036
1037 ELSE
1038 l_s_sst := 0;
1039 END IF;
1040
1041 l_s_sst := ROUND(l_s_sst) ;
1042
1043 -- Calculating the Income Protection Allowance for Student
1044 OPEN ipa_cur;
1045 FETCH ipa_cur INTO ipa_rec;
1046 IF ipa_cur%NOTFOUND THEN
1047 CLOSE ipa_cur;
1048 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCA_SETUP');
1049 IGS_GE_MSG_STACK.ADD;
1050 RAISE exception_in_setup;
1051 END IF;
1052
1053 l_ipa := NVL(ipa_rec.stud_inc_prot_allowance,0);
1054 CLOSE ipa_cur;
1055
1056 -- Allowance for parents negative Adjusted Available Income (If Its negative,
1057 -- enter as a positive number. If Its zero or positive, enter zero)
1058 IF p_p_aai >= 0 THEN
1059 l_p_aai := 0;
1060 ELSE
1061 l_p_aai := -p_p_aai;
1062 END IF;
1063
1064 -- Allowance against Student's Income
1065 p_allow_ag_s_inc := ROUND(l_s_taxes_paid) + ROUND(l_tax_allowance) + ROUND(l_s_sst) + ROUND(l_ipa) + ROUND(l_p_aai) ;
1066 -- p_allow_ag_s_inc := ROUND( p_allow_ag_s_inc);
1067
1068 igf_ap_efc_calc.isir_rec.sati := p_allow_ag_s_inc; --Assignment of intermediate values
1069 igf_ap_efc_calc.isir_rec.secsati := p_allow_ag_s_inc; --Assignment of intermediate values
1070
1071 EXCEPTION
1072 WHEN exception_in_setup THEN
1073 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
1074 WHEN OTHERS THEN
1075 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1076 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_ALLOW_AG_S_INC');
1077 IGS_GE_MSG_STACK.ADD;
1078 APP_EXCEPTION.RAISE_EXCEPTION;
1079 END a_allow_ag_s_inc;
1080
1081
1082 PROCEDURE a_s_cont ( p_s_inc IN NUMBER,
1083 p_allow_ag_s_inc IN NUMBER,
1084 p_s_cont OUT NOCOPY NUMBER) AS
1085 /*
1086 || Created By :
1087 || Created On :
1088 || Purpose :
1089 || Known limitations, enhancements or remarks :
1090 || Change History :
1091 || Who When What
1092 || (reverse chronological order - newest change first)
1093 */
1094 l_available_income NUMBER;
1095
1096 -- Cursor to Find the Default Setting for Formula A
1097 CURSOR efcA_cur IS
1098 SELECT stud_available_income
1099 FROM igf_fc_efc_frm_a efca
1100 WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1101 efcA_rec efcA_cur%ROWTYPE;
1102
1103 BEGIN
1104 l_available_income := p_s_inc - p_allow_ag_s_inc;
1105
1106 OPEN efcA_cur;
1110 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCA_SETUP');
1107 FETCH efcA_cur INTO efcA_rec;
1108 IF efcA_cur%NOTFOUND THEN
1109 CLOSE efcA_cur;
1111 IGS_GE_MSG_STACK.ADD;
1112 RAISE exception_in_setup;
1113 END IF;
1114 CLOSE efcA_cur;
1115
1116 p_s_cont := (l_available_income * NVL(efcA_rec.stud_available_income,0)) / 100;
1117
1118 IF p_s_cont < 0 THEN
1119 p_s_cont := 0;
1120 END IF;
1121
1122 p_s_cont := ROUND( p_s_cont );
1123
1124 igf_ap_efc_calc.isir_rec.sic := p_s_cont; --Assignment of intermediate values
1125 igf_ap_efc_calc.isir_rec.secsic := p_s_cont; --Assignment of intermediate values
1126
1127 EXCEPTION
1128 WHEN exception_in_setup THEN
1129 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
1130 WHEN OTHERS THEN
1131 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1132 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_S_CONT');
1133 IGS_GE_MSG_STACK.ADD;
1134 APP_EXCEPTION.RAISE_EXCEPTION;
1135
1136 END a_s_cont;
1137
1138
1139 PROCEDURE a_s_cont_assets ( p_s_cont_assets OUT NOCOPY NUMBER) AS
1140 /*
1141 || Created By : gmuralid
1142 || Created On : 11 Feb 2003
1143 || Purpose : Procedure to get Student's Contribution From Assets , Bug# 2758804 , EFC build TD
1144 || Known limitations, enhancements or remarks :
1145 || Change History :
1146 || Who When What
1147 || (reverse chronological order - newest change first)
1148 */
1149 l_investment_networth igf_ap_isir_matched.p_investment_networth%TYPE;
1150 l_business_networth igf_ap_isir_matched.p_business_networth%TYPE;
1151 l_cash_saving igf_ap_isir_matched.p_cash_saving%TYPE;
1152 l_net_worth NUMBER;
1153
1154 -- Cursor to find the Default Setting for Formula A
1155 CURSOR efcA_cur IS
1156 SELECT stud_asset_assessment_rate
1157 FROM igf_fc_efc_frm_a efca
1158 WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1159
1160 efcA_rec efcA_cur%ROWTYPE;
1161
1162 BEGIN
1163 -- Get Net worth of investments( Can not be Negative)
1164 IF NVL(igf_ap_efc_calc.isir_rec.s_investment_networth,0) <= 0 THEN
1165 l_investment_networth := 0;
1166 ELSE
1167 l_investment_networth := igf_ap_efc_calc.isir_rec.s_investment_networth;
1168 END IF;
1169
1170 -- Get Net Worth of business/investments farm(Can not be Negative)
1171 IF NVL(igf_ap_efc_calc.isir_rec.s_busi_farm_networth,0) <= 0 THEN
1172 l_business_networth := 0;
1173 ELSE
1174 l_business_networth := igf_ap_efc_calc.isir_rec.s_busi_farm_networth;
1175 END IF;
1176
1177 -- Get Cash, Savings and Checking
1178 l_cash_saving := NVL(igf_ap_efc_calc.isir_rec.s_cash_savings,0);
1179
1180 -- Get the Default Setting for Formula A
1181 OPEN efcA_cur;
1182 FETCH efcA_cur INTO efcA_rec;
1183 IF efcA_cur%NOTFOUND THEN
1184 CLOSE efcA_cur;
1185 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCA_SETUP');
1186 IGS_GE_MSG_STACK.ADD;
1187 RAISE exception_in_setup;
1188 END IF;
1189 CLOSE efcA_cur;
1190
1191 -- Get Net Worth
1192 l_net_worth := l_investment_networth + l_business_networth + l_cash_saving;
1193
1194 igf_ap_efc_calc.isir_rec.sdnw := l_net_worth; --Assignment of intermediate values
1195 igf_ap_efc_calc.isir_rec.secsdnw := l_net_worth; --Assignment of intermediate values
1196
1197 -- Get Contribution from Assets
1198 p_s_cont_assets := l_net_worth * NVL(efcA_rec.stud_asset_assessment_rate,0)/100;
1199 p_s_cont_assets := ROUND( p_s_cont_assets );
1200
1201 igf_ap_efc_calc.isir_rec.sca := p_s_cont_assets; --Assignment of intermediate values
1202 igf_ap_efc_calc.isir_rec.secsca := p_s_cont_assets; --Assignment of intermediate values
1203
1204 EXCEPTION
1205 WHEN exception_in_setup THEN
1206 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
1207 WHEN OTHERS THEN
1208 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1209 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_S_CONT_ASSETS');
1210 IGS_GE_MSG_STACK.ADD;
1211 APP_EXCEPTION.RAISE_EXCEPTION;
1212 END a_s_cont_assets;
1213
1214
1215 PROCEDURE a_efc ( p_p_cont IN NUMBER,
1216 p_s_cont IN NUMBER,
1217 p_s_cont_assets IN NUMBER,
1218 p_efc OUT NOCOPY NUMBER) AS
1219 /*
1220 || Created By : gmuralid
1221 || Created On : 11 Feb 2003
1222 || Purpose : Procedure to get EFC from formula A , Bug# 2758804 , EFC build TD
1223 ||
1224 || Known limitations, enhancements or remarks :
1225 || Change History :
1226 || Who When What
1227 || (reverse chronological order - newest change first)
1228 */
1229 BEGIN
1230 -- p_efc := p_p_cont + p_s_cont + p_s_cont_assets;
1231 -- Students Contribution from Assets is not taken .
1232 p_efc := p_p_cont + p_s_cont ;
1233 IF p_efc < 0 THEN
1234 p_efc := 0;
1235 END IF;
1236 END a_efc;
1237
1241 p_p_cont_less_9 OUT NOCOPY NUMBER) AS
1238
1239 PROCEDURE a_p_cont_less_9 ( p_p_cont IN NUMBER,
1240 p_no_of_months IN NUMBER,
1242 /*
1243 || Created By : gmuralid
1244 || Created On : 11 Feb 2003
1245 || Purpose : Procedure to get Parents Contribution for student enrolled for less than 9 months , Bug# 2758804 , EFC build TD
1246 ||
1247 || Known limitations, enhancements or remarks :
1248 || Change History :
1249 || Who When What
1250 || (reverse chronological order - newest change first)
1251 */
1252
1253 l_p_cont_per_mon NUMBER;
1254
1255 BEGIN
1256 -- Get Parents contribution per month
1257 l_p_cont_per_mon := p_p_cont / 9 ;
1258
1259 -- Parents contribution for less than 9 months Enrollment
1260 p_p_cont_less_9 := l_p_cont_per_mon * p_no_of_months;
1261
1262 END a_p_cont_less_9;
1263
1264
1265 PROCEDURE a_s_cont_less_9 ( p_s_cont IN NUMBER,
1266 p_no_of_months IN NUMBER,
1267 p_s_cont_less_9 OUT NOCOPY NUMBER) AS
1268 /*
1269 || Created By : gmuralid
1270 || Created On : 11 Feb 2003
1271 || Purpose : Procedure to get students Contribution for student enrolled for less than 9 months , Bug# 2758804 , EFC build TD
1272 || Known limitations, enhancements or remarks :
1273 || Change History :
1274 || Who When What
1275 || (reverse chronological order - newest change first)
1276 */
1277 l_s_cont_per_month NUMBER;
1278 BEGIN
1279 -- Students contribution from AI per month
1280 l_s_cont_per_month := p_s_cont / 9 ;
1281
1282 -- Student contribution from AI for less than 9 month enrollment
1283 p_s_cont_less_9 := l_s_cont_per_month * p_no_of_months ;
1284
1285 END a_s_cont_less_9;
1286
1287
1288 PROCEDURE a_efc_not_9 ( p_p_cont_not_9 IN NUMBER,
1289 p_s_cont_not_9 IN NUMBER,
1290 p_s_cont_assets IN NUMBER,
1291 p_efc OUT NOCOPY NUMBER) AS
1292 /*
1293 || Created By : gmuralid
1294 || Created On : 11 Feb 2003
1295 || Purpose : Procedure to get EFC for student enrolled other than 9 months , Bug# 2758804 , EFC build TD
1296 || Known limitations, enhancements or remarks :
1297 || Change History :
1298 || Who When What
1299 || (reverse chronological order - newest change first)
1300 */
1301 BEGIN
1302
1303 -- p_efc := p_p_cont_not_9 + p_s_cont_not_9 + p_s_cont_assets ;
1304 -- Students contribution from Assets not considered for approtioning
1305 -- Ref Bug# 2384837
1306
1307 p_efc := p_p_cont_not_9 + p_s_cont_not_9 ;
1308 IF p_efc < 0 THEN
1309 p_efc := 0;
1310 END IF;
1311
1312 END a_efc_not_9;
1313
1314
1315 PROCEDURE a_p_cont_more_9 ( p_p_aai IN NUMBER,
1316 p_p_cont IN NUMBER,
1317 p_no_of_months IN NUMBER,
1318 p_p_cont_more_9 OUT NOCOPY NUMBER) AS
1319 /*
1320 || Created By : gmuralid
1321 || Created On : 11 Feb 2003
1322 || Purpose : Procedure to get parents contribution for student enrolled more than 9 months , Bug# 2758804 , EFC build TD
1323 || Known limitations, enhancements or remarks :
1324 || Change History :
1325 || Who When What
1326 || (reverse chronological order - newest change first)
1327 */
1328
1329 l_aai_more_9 NUMBER;
1330 l_p_cont_aai NUMBER;
1331 l_p_cont NUMBER;
1332 l_p_cont_diff NUMBER;
1333 l_p_cont_per_month NUMBER;
1334 l_p_cont_adj NUMBER;
1335
1336 -- Cursor to find the Default Setting for Formula A
1337 CURSOR efcA_cur IS
1338 SELECT income_protection_diff_9
1339 FROM igf_fc_efc_frm_a efca
1340 WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1341
1342
1343 -- Cursor to calculate Parents Contribution from AAI
1344 CURSOR p_cont_aai_cur(cp_p_aai NUMBER) IS
1345 SELECT tax_rate, amount, tax_rate_excess, amount_excess
1346 FROM igf_fc_gen_tax_rts gtxrts
1347 WHERE gtxrts.table_code = 'A6'
1348 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
1349 AND (cp_p_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
1350
1351 p_cont_aai_rec p_cont_aai_cur%ROWTYPE;
1352 efcA_rec efcA_cur%ROWTYPE;
1353
1354 BEGIN
1355 -- Get the Default Setting for Formula A
1356 OPEN efcA_cur;
1357 FETCH efcA_cur INTO efcA_rec;
1358 IF efcA_cur%NOTFOUND THEN
1359 CLOSE efcA_cur;
1360 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCA_SETUP');
1361 IGS_GE_MSG_STACK.ADD;
1362 RAISE exception_in_setup;
1363 END IF;
1364 CLOSE efcA_cur;
1365
1366 -- Get Adjusted Available Income for more than 9 months
1367 -- ( Parents AAI + Difference between the income protection allowance for a
1371 -- Get Total Parents contribution from Adjusted Available Income
1368 -- family of four and a family of five, with one in college)
1369 l_aai_more_9 := p_p_aai + NVL(efcA_rec.income_protection_diff_9,0);
1370
1372 OPEN p_cont_aai_cur(l_aai_more_9);
1373 FETCH p_cont_aai_cur INTO p_cont_aai_rec;
1374 IF p_cont_aai_cur%NOTFOUND THEN
1375 CLOSE p_cont_aai_cur;
1376 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
1377 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A6');
1378 IGS_GE_MSG_STACK.ADD;
1379 RAISE exception_in_setup;
1380 END IF;
1381 CLOSE p_cont_aai_cur;
1382
1383 IF p_cont_aai_rec.tax_rate IS NULL THEN
1384 l_p_cont_aai := p_cont_aai_rec.amount +
1385 (( p_cont_aai_rec.tax_rate_excess * (l_aai_more_9 - p_cont_aai_rec.amount_excess)) / 100 );
1386 ELSE
1387 l_p_cont_aai := ( p_cont_aai_rec.tax_rate * l_aai_more_9 ) / 100 ;
1388 END IF ;
1389
1390 -- Total Parents contribution from Adjusted Available Income
1391 -- (Can not be Negative)
1392 IF l_p_cont_aai < 0 THEN
1393 l_p_cont_aai := 0;
1394 END IF;
1395
1396 l_p_cont_aai := ROUND(l_p_cont_aai) ;
1397
1398 -- Get parents contribution (Total Parents contribution from AAI/Number in college in 2001-02)
1399 l_p_cont := ROUND(l_p_cont_aai / NVL(NVL(igf_ap_efc_calc.isir_rec.a_parents_num_college,igf_ap_efc_calc.isir_rec.p_num_in_college),1));
1400
1401 -- Difference Between Calculated Parents contribution and Standard Parents Contribution(For 9 months)
1402 l_p_cont_diff := l_p_cont - p_p_cont;
1403
1404 -- Parents contribution per month
1405 l_p_cont_per_month := ROUND(l_p_cont_diff / 12);
1406
1407 -- Adjustment to parents contribution
1408 -- for months that exceed 9
1409 l_p_cont_adj := ROUND(l_p_cont_per_month * (p_no_of_months -9));
1410
1411 --Parents contribution for MORE than 9-month enrollment
1412 p_p_cont_more_9 := p_p_cont + l_p_cont_adj;
1413
1414
1415 EXCEPTION
1416 WHEN exception_in_setup THEN
1417 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
1418 WHEN OTHERS THEN
1419 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1420 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.A_P_CONT_MORE_9');
1421 IGS_GE_MSG_STACK.ADD;
1422 APP_EXCEPTION.RAISE_EXCEPTION;
1423 END a_p_cont_more_9;
1424
1425
1426 -- SUB FUNCTIONS for calculating EFC with FORMULA B
1427
1428 PROCEDURE b_s_inc ( p_s_inc OUT NOCOPY NUMBER) AS
1429 /*
1430 || Created By : gmuralid
1431 || Created On : 11 Feb 2003
1432 || Purpose : Procedure to get students Income for formula B , Bug# 2758804 , EFC build TD
1433 || Known limitations, enhancements or remarks :
1434 || Change History :
1435 || Who When What
1436 || (reverse chronological order - newest change first)
1437 */
1438
1439 l_adjusted_gross_income igf_ap_isir_matched.s_adjusted_gross_income%TYPE;
1440 l_s_inc_work NUMBER;
1441 l_tot_tax_inc NUMBER;
1442 l_tot_untax_inc NUMBER;
1443 l_tot_inc NUMBER;
1444
1445 BEGIN
1446 -- Get Student's and spouse's Adjusted Gross Income( If negative take 0)
1447 IF NVL(NVL(igf_ap_efc_calc.isir_rec.a_student_agi,igf_ap_efc_calc.isir_rec.s_adjusted_gross_income),0) <= 0 THEN
1448 l_adjusted_gross_income := 0;
1449 ELSE
1450 l_adjusted_gross_income := NVL(igf_ap_efc_calc.isir_rec.a_student_agi,igf_ap_efc_calc.isir_rec.s_adjusted_gross_income);
1451 END IF;
1452
1453
1454 -- Get Student's and Spouse's Toatal income earned from work
1455 l_s_inc_work := NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) + NVL(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0);
1456
1457 -- Get the Student's Taxable income( If Taxable = Adjusted Gross Income Else
1458 -- = Income earned from Work)
1459
1460
1461
1462 IF igf_ap_efc_calc.isir_rec.s_cal_tax_status IN ('1','2','3') THEN
1463 l_tot_tax_inc := l_adjusted_gross_income;
1464 ELSE
1465 l_tot_tax_inc := l_s_inc_work;
1466 END IF;
1467
1468
1469 -- Total Untaxed Income and benefits
1470 -- Total from FAFSA Worksheet A + Total from FAFSA Worksheet B
1471 l_tot_untax_inc := NVL(igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsa,0) +
1472 NVL(igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsb,0);
1473
1474
1475 -- Total Income = Total Taxable Income + Total Untaxable Income.
1476 l_tot_inc := l_tot_tax_inc + l_tot_untax_inc;
1477
1478
1479 -- Student's total Income = Total Income - Total from FAFSA Worksheet C
1480 p_s_inc := l_tot_inc - NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_total_wsc,igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsc),0);
1481
1482 p_s_inc := ROUND( p_s_inc );
1483
1484
1485 igf_ap_efc_calc.isir_rec.total_income := p_s_inc; --Assignment of intermediate values
1486 igf_ap_efc_calc.isir_rec.secti := p_s_inc; --Assignment of intermediate values
1487
1488 igf_ap_efc_calc.isir_rec.fti := p_s_inc;
1489 igf_ap_efc_calc.isir_rec.secfti := p_s_inc;
1490
1491 EXCEPTION
1492
1493 WHEN OTHERS THEN
1494 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1498
1495 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.B_S_INC');
1496 IGS_GE_MSG_STACK.ADD;
1497 APP_EXCEPTION.RAISE_EXCEPTION;
1499 END b_s_inc;
1500
1501
1502 PROCEDURE b_allow_ag_s_inc ( p_s_inc IN NUMBER,
1503 p_allow_ag_s_inc OUT NOCOPY NUMBER) AS
1504 /*
1505 || Created By : gmuralid
1506 || Created On : 11 Feb 2003
1507 || Purpose : Procedure to get allowances against students Income for formula B , Bug# 2758804 , EFC build TD
1508 ||
1509 || Known limitations, enhancements or remarks :
1510 || Change History :
1511 || Who When What
1512 || (reverse chronological order - newest change first)
1513 */
1514
1515 l_s_taxes_paid igf_ap_isir_matched.p_taxes_paid%TYPE;
1516 l_state igf_lookups_view.lookup_code%TYPE;
1517 l_tax_allowance NUMBER;
1518 l_s_sst NUMBER;
1519 l_spouse_sst NUMBER;
1520 l_ipa NUMBER;
1521 l_eea NUMBER;
1522 l_state_cd VARCHAR2(30);
1523
1524 -- Cursor to find the Valid State
1525 CURSOR state_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
1526 SELECT lookup_code
1527 FROM igf_lookups_view
1528 WHERE lookup_type = 'IGF_AP_STATE_CODES'
1529 AND lookup_code = cp_state;
1530
1531 -- Cursor to calculate State and other tax allowance
1532 CURSOR state_allow_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
1533 SELECT tax_rate
1534 FROM igf_fc_state_tx txrng
1535 WHERE txrng.table_code = 'B1'
1536 AND txrng.s_award_year = igf_ap_efc_calc.p_sys_award_year
1537 AND txrng.state_code = cp_state;
1538
1539 -- Cursor to calculate Social Security Tax for Student and Spouse.
1540 CURSOR sst_cur(cp_inc_work igf_ap_isir_matched.s_income_from_work%TYPE) IS
1541 SELECT tax_rate, amount, tax_rate_excess, amount_excess
1542 FROM igf_fc_gen_tax_rts gtxrts
1543 WHERE gtxrts.table_code = 'B2'
1544 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
1545 AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
1546
1547 -- Cursor to find the Default values defined for Formula B.
1548 CURSOR efcB_cur IS
1549 SELECT unmarried_stud_ipa_amt, mss_half_time_ipa_amt,married_stud_ipa_amt, unmarried_stud_eea_amt,
1550 married_one_work_eea_amt,married_two_work_eea_rate, married_two_work_eea_amt
1551 FROM igf_fc_efc_frm_b efcb
1552 WHERE efcb.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1553
1554 state_rec state_cur%ROWTYPE;
1555 state_allow_rec state_allow_cur%ROWTYPE;
1556 sst_rec sst_cur%ROWTYPE;
1557 efcB_rec efcB_cur%ROWTYPE;
1558
1559 BEGIN
1560
1561 -- For Non Tax Filers OR with Income Tax Paid Negative should be processed, process with 0
1562 IF ((NVL(igf_ap_efc_calc.isir_rec.s_cal_tax_status,'-1') NOT IN ('1','2','3')) OR (NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_us_tax_paid,igf_ap_efc_calc.isir_rec.s_fed_taxes_paid),0) <= 0)) THEN
1563 l_s_taxes_paid := 0;
1564 ELSE
1565 l_s_taxes_paid := NVL(igf_ap_efc_calc.isir_rec.a_s_us_tax_paid,igf_ap_efc_calc.isir_rec.s_fed_taxes_paid);
1566 END IF;
1567
1568 -- Process to calculate State and other tax allowance only if Student's Income > 0
1569 IF p_s_inc > 0 THEN
1570 -- To find the proper State process in the order of
1571 -- Students state of legal residence (If invalid then)
1572 -- State in the Students mailing address (If invalid then)
1573 -- Blank or Invalid state
1574 l_state_cd := igf_ap_efc_calc.isir_rec.s_state_legal_residence;
1575
1576 IF l_state_cd = 'FC' THEN
1577 l_state_cd := 'OT';
1578 END IF;
1579
1580 OPEN state_cur(l_state_cd);
1581 FETCH state_cur INTO state_rec;
1582 l_state := state_rec.lookup_code;
1583 CLOSE state_cur;
1584
1585 IF l_state IS NULL THEN
1586
1587 l_state_cd := igf_ap_efc_calc.isir_rec.perm_state;
1588 IF l_state_cd = 'FC' THEN
1589 l_state_cd := 'OT';
1590 END IF;
1591
1592 OPEN state_cur(l_state_cd);
1593 FETCH state_cur INTO state_rec;
1594 l_state := state_rec.lookup_code;
1595 CLOSE state_cur;
1596 END IF;
1597
1598 IF l_state IS NULL THEN
1599 l_state := 'BL';
1600 END IF;
1601
1602 OPEN state_allow_cur(l_state);
1603 FETCH state_allow_cur INTO state_allow_rec;
1604 IF state_allow_cur%NOTFOUND THEN
1605 CLOSE state_allow_cur;
1606 OPEN state_allow_cur('OT');
1607 FETCH state_allow_cur INTO state_allow_rec;
1608 IF state_allow_cur%NOTFOUND THEN
1609 CLOSE state_allow_cur;
1610 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_TAX_SETUP');
1611 FND_MESSAGE.SET_TOKEN('TABLE_NAME','B1');
1612 IGS_GE_MSG_STACK.ADD;
1613 RAISE exception_in_setup;
1614 END IF;
1615 END IF;
1616 CLOSE state_allow_cur;
1617
1621 ELSE
1618 -- Determine the State and other tax allowance
1619 l_tax_allowance := ( p_s_inc * state_allow_rec.tax_rate ) / 100 ;
1620
1622 -- If Student's Income <= 0 then assume Tax Allowance to be 0
1623 l_tax_allowance := 0;
1624 END IF;
1625
1626 l_tax_allowance := ROUND(l_tax_allowance) ;
1627
1628 igf_ap_efc_calc.isir_rec.state_tax_allow := l_tax_allowance;
1629 igf_ap_efc_calc.isir_rec.secstx := l_tax_allowance;
1630 --from here
1631
1632 -- Calculating the Social Security Tax for Student
1633 IF NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL THEN
1634 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0));
1635 FETCH sst_cur INTO sst_rec;
1636 IF sst_cur%NOTFOUND THEN
1637 CLOSE sst_cur;
1638 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
1639 FND_MESSAGE.SET_TOKEN('TABLE_NAME','A2');
1640 IGS_GE_MSG_STACK.ADD;
1641 RAISE exception_in_setup;
1642 END IF;
1643 CLOSE sst_cur;
1644
1645 IF sst_rec.tax_rate IS NULL THEN
1646 l_s_sst := sst_rec.amount +
1647 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) - sst_rec.amount_excess)) / 100 );
1648 ELSE
1649 l_s_sst := ( sst_rec.tax_rate * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0)) / 100 );
1650 END IF ;
1651
1652 -- Social Security Tax can not be Negative
1653 IF ( l_s_sst < 0 ) THEN
1654 l_s_sst := 0;
1655 END IF;
1656 ELSE
1657 l_s_sst := 0;
1658 END IF;
1659
1660 l_s_sst := ROUND(l_s_sst) ;
1661
1662
1663 -- Calculating the Social Security Tax for Spouse
1664 IF NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NOT NULL THEN
1665 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0));
1666 FETCH sst_cur INTO sst_rec;
1667 IF sst_cur%NOTFOUND THEN
1668 CLOSE sst_cur;
1669 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
1670 FND_MESSAGE.SET_TOKEN('TABLE_NAME','B2');
1671 IGS_GE_MSG_STACK.ADD;
1672 RAISE exception_in_setup;
1673 END IF;
1674 CLOSE sst_cur;
1675
1676 IF sst_rec.tax_rate IS NULL THEN
1677
1678
1679 l_spouse_sst := sst_rec.amount +
1680 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0) - sst_rec.amount_excess)) / 100 );
1681
1682
1683 ELSE
1684 l_spouse_sst := ( sst_rec.tax_rate * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0) ) / 100 );
1685 END IF ;
1686
1687 -- Social Security Tax can not be Negative
1688 IF ( l_spouse_sst < 0 ) THEN
1689 l_spouse_sst := 0;
1690 END IF;
1691 ELSE
1692 l_spouse_sst := 0;
1693 END IF;
1694
1695 l_spouse_sst := ROUND(l_spouse_sst) ;
1696
1697
1698 -- To find the Default values defined for EFC Formula B
1699 OPEN efcB_cur;
1700 FETCH efcB_cur INTO efcB_rec;
1701 IF efcB_cur%NOTFOUND THEN
1702 CLOSE efcB_cur;
1703 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCB_SETUP');
1704 IGS_GE_MSG_STACK.ADD;
1705 RAISE exception_in_setup;
1706 END IF;
1707 CLOSE efcB_cur;
1708 -- Calculate the Income Protection Allowance
1709 IF NVL ( igf_ap_efc_calc.isir_rec.a_student_marital_status , igf_ap_efc_calc.isir_rec.s_marital_status ) IN ( '1','3') THEN
1710 l_ipa := NVL(efcB_rec.unmarried_stud_ipa_amt,0);
1711 ELSIF NVL ( igf_ap_efc_calc.isir_rec.a_student_marital_status , igf_ap_efc_calc.isir_rec.s_marital_status ) = '2' THEN
1712 IF NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college) = 2 THEN
1713 l_ipa := NVL(efcB_rec.mss_half_time_ipa_amt,0);
1714 ELSIF NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college) < 2 THEN
1715 l_ipa := NVL(efcB_rec.married_stud_ipa_amt,0);
1716 ELSE
1717 l_ipa := 0 ;
1718 END IF ;
1719 ELSE
1720 l_ipa := 0 ;
1721 END IF ;
1722
1723 /*
1724 -- Calculate the Income Protection Allowance
1725 IF igf_ap_efc_calc.isir_rec.s_marital_status = '2' THEN -- Married Student
1726 -- Both Student and Spouse enrolled at least 1/2 time
1727 IF NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_family,igf_ap_efc_calc.isir_rec.s_num_family_members) = 2 AND NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college) = 2 THEN
1728 l_ipa := NVL(efcB_rec.mss_half_time_ipa_amt,0);
1729
1730 -- Only one of Student and Spouse enrolled at least 1/2 time
1731 ELSIF NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_family,igf_ap_efc_calc.isir_rec.s_num_family_members) = 2 AND NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college) = 1 THEN
1735 l_ipa := 0;
1732 l_ipa := NVL(efcB_rec.married_stud_ipa_amt,0);
1733 ELSE
1734 -- If both of the above conditions failed
1736 END IF;
1737 ELSE -- Student is unmarried / Separated
1738 l_ipa := NVL(efcB_rec.unmarried_stud_ipa_amt,0);
1739 END IF;*/
1740
1741 igf_ap_efc_calc.isir_rec.income_protection_allow := l_ipa;
1742 igf_ap_efc_calc.isir_rec.secipa := l_ipa;
1743
1744
1745 -- Calculate the Employment Expense Allowance
1746 IF NVL ( igf_ap_efc_calc.isir_rec.a_student_marital_status , igf_ap_efc_calc.isir_rec.s_marital_status ) = '2' THEN -- Married Student
1747 -- If only one out of them is working
1748 IF ( (NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NULL
1749 AND NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NOT NULL)
1750 OR ( NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL
1751 AND NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NULL))
1752 THEN
1753 l_eea := NVL(efcB_rec.married_one_work_eea_amt,0);
1754
1755 -- If both of them are working
1756 ELSIF ( NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL
1757 AND NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NOT NULL)
1758 THEN
1759 l_eea := LEAST((NVL(efcB_rec.married_two_work_eea_rate,0) *
1760 LEAST(greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0),
1761 greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0)) /100),NVL(efcB_rec.married_two_work_eea_amt,0));
1762
1763 -- If both of the above conditions are failed
1764 ELSE
1765 l_eea := 0;
1766 END IF;
1767 ELSE -- Student is unmarried / Separated
1768 l_eea := NVL(efcB_rec.unmarried_stud_eea_amt,0);
1769 END IF;
1770
1771 igf_ap_efc_calc.isir_rec.employment_allow := ROUND(l_eea);
1772 igf_ap_efc_calc.isir_rec.secea := ROUND(l_eea);
1773
1774 -- Allowance against Student's Income
1775 p_allow_ag_s_inc := ROUND(l_s_taxes_paid) + ROUND(l_tax_allowance) + ROUND(l_s_sst) + ROUND(l_spouse_sst) + ROUND(l_ipa) + ROUND(l_eea) ;
1776 -- p_allow_ag_s_inc := ROUND( p_allow_ag_s_inc );
1777
1778 igf_ap_efc_calc.isir_rec.allow_total_income := p_allow_ag_s_inc; --Assignment of intermediate values
1779 igf_ap_efc_calc.isir_rec.secati := p_allow_ag_s_inc; --Assignment of intermediate values
1780
1781 EXCEPTION
1782 WHEN exception_in_setup THEN
1783 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
1784 WHEN OTHERS THEN
1785 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1786 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.B_ALLOW_AG_S_INC');
1787 IGS_GE_MSG_STACK.ADD;
1788 APP_EXCEPTION.RAISE_EXCEPTION;
1789 END b_allow_ag_s_inc;
1790
1791
1792 PROCEDURE b_s_cont ( p_s_inc IN NUMBER,
1793 p_allow_ag_s_inc IN NUMBER,
1794 p_s_cont OUT NOCOPY NUMBER) AS
1795 /*
1796 || Created By : gmuralid
1797 || Created On : 11 Feb 2003
1798 || Purpose : Procedure to get students contribution for formula B , Bug# 2758804 , EFC build TD
1799 || Known limitations, enhancements or remarks :
1800 || Change History :
1801 || Who When What
1802 || (reverse chronological order - newest change first)
1803 */
1804 l_available_income NUMBER;
1805
1806 -- Cursor to Find the Default Setting for Formula B
1807 CURSOR efcB_cur IS
1808 SELECT stud_available_income
1809 FROM igf_fc_efc_frm_b efcb
1810 WHERE efcb.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1811
1812 efcB_rec efcB_cur%ROWTYPE;
1813
1814 BEGIN
1815 l_available_income := p_s_inc - p_allow_ag_s_inc;
1816
1817 OPEN efcB_cur;
1818 FETCH efcB_cur INTO efcB_rec;
1819 IF efcB_cur%NOTFOUND THEN
1820 CLOSE efcB_cur;
1821 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCB_SETUP');
1822 IGS_GE_MSG_STACK.ADD;
1823 RAISE exception_in_setup;
1824 END IF;
1825 CLOSE efcB_cur;
1826
1827 p_s_cont := (l_available_income * NVL(efcB_rec.stud_available_income,0)) / 100;
1828 p_s_cont := ROUND( p_s_cont );
1829
1830 -- igf_ap_efc_calc.isir_rec.sic := p_s_cont; --Assignment of intermediate values
1831 -- igf_ap_efc_calc.isir_rec.secsic := p_s_cont; --Assignment of intermediate values
1832
1833 igf_ap_efc_calc.isir_rec.available_income := l_available_income;
1834 igf_ap_efc_calc.isir_rec.secai := l_available_income;
1835
1836 igf_ap_efc_calc.isir_rec.contribution_from_ai :=p_s_cont;
1837 igf_ap_efc_calc.isir_rec.seccai := p_s_cont;
1838
1839
1840 EXCEPTION
1841 WHEN exception_in_setup THEN
1842 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
1846 IGS_GE_MSG_STACK.ADD;
1843 WHEN OTHERS THEN
1844 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1845 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.B_S_CONT');
1847 APP_EXCEPTION.RAISE_EXCEPTION;
1848
1849 END b_s_cont;
1850
1851
1852 PROCEDURE b_s_cont_assets ( p_s_cont_assets OUT NOCOPY NUMBER) AS
1853 /*
1854 || Created By : gmuralid
1855 || Created On : 11 Feb 2003
1856 || Purpose : Procedure to get students contribution from assets for formula B , Bug# 2758804 , EFC build TD
1857 || Known limitations, enhancements or remarks :
1858 || Change History :
1859 || Who When What
1860 || (reverse chronological order - newest change first)
1861 */
1862
1863 l_investment_networth igf_ap_isir_matched.p_investment_networth%TYPE;
1864 l_business_networth igf_ap_isir_matched.p_business_networth%TYPE;
1865 l_adj_business_networth NUMBER;
1866 l_cash_saving igf_ap_isir_matched.p_cash_saving%TYPE;
1867 l_net_worth NUMBER;
1868 l_age_student NUMBER;
1869 l_asset_pro_allow igf_fc_ast_pc_dt.parent2_allowance%TYPE;
1870 l_d_net_worth NUMBER;
1871
1872 -- Cursor to find the Setting for Formula B
1873 CURSOR efcB_cur IS
1874 SELECT stud_asset_conv_rate
1875 FROM igf_fc_efc_frm_b efcb
1876 WHERE efcb.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1877
1878 -- Cursor to calculate Asset Protection Allowance
1879 CURSOR asset_cur(cp_age_student igf_fc_ast_pc_dt.older_parent_age%TYPE) IS
1880 SELECT parent1_allowance, parent2_allowance
1881 FROM igf_fc_ast_pc_dt apdt
1882 WHERE apdt.table_code = 'B4' --gmuralid table code I think needs to be B4 , it was A5
1883 AND apdt.older_parent_age = cp_age_student
1884 AND apdt.s_award_year = igf_ap_efc_calc.p_sys_award_year;
1885
1886 -- Cursor to calculate Adjusted Net worth of Business/farm
1887
1888 CURSOR business_networth_cur(cp_business_networth igf_ap_isir_matched.p_business_networth%TYPE) IS
1889 SELECT tax_rate, amount, tax_rate_excess, amount_excess
1890 FROM igf_fc_gen_tax_rts gtxrts
1891 WHERE gtxrts.table_code = 'B3' --gmuralid Table code i think needs to be B3, it was A4
1892 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
1893 AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
1894
1895 business_networth_rec business_networth_cur%ROWTYPE;
1896 efcB_rec efcB_cur%ROWTYPE;
1897 asset_rec asset_cur%ROWTYPE;
1898 l_base_date DATE;
1899
1900 BEGIN
1901
1902 IF igf_ap_efc_calc.p_sys_award_year ='0304' THEN
1903 l_base_Date := to_date('31-12-2003', 'DD-MM-YYYY');
1904 ELSIF igf_ap_efc_calc.p_sys_award_year ='0405' THEN
1905 l_base_Date := to_date('31-12-2004', 'DD-MM-YYYY');
1906 ELSIF igf_ap_efc_calc.p_sys_award_year ='0506' THEN
1907 l_base_Date := to_date('31-12-2005', 'DD-MM-YYYY');
1908 ELSIF igf_ap_efc_calc.p_sys_award_year ='0607' THEN
1909 l_base_Date := to_date('31-12-2006', 'DD-MM-YYYY');
1910 ELSE
1911 l_base_Date := to_date('31-12-2002', 'dd-MM-YYYY');
1912 END IF;
1913
1914 -- Get Net worth of investments( Can not be Negative)
1915 IF NVL(igf_ap_efc_calc.isir_rec.s_investment_networth,0) <= 0 THEN
1916 l_investment_networth := 0;
1917 ELSE
1918 l_investment_networth := igf_ap_efc_calc.isir_rec.s_investment_networth;
1919 END IF;
1920
1921 -- Get Net Worth of business/investments farm(Can not be Negative)
1922 IF NVL(igf_ap_efc_calc.isir_rec.s_busi_farm_networth,0) <= 0 THEN
1923 l_business_networth := 0;
1924 ELSE
1925 l_business_networth := igf_ap_efc_calc.isir_rec.s_busi_farm_networth;
1926 END IF;
1927
1928 -- Get Adjusted net worth of Business/Farm(Using B3 as both contains the same Information)
1929 OPEN business_networth_cur(l_business_networth);
1930 FETCH business_networth_cur INTO business_networth_rec;
1931 IF business_networth_cur%NOTFOUND THEN
1932 CLOSE business_networth_cur;
1933 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
1934 FND_MESSAGE.SET_TOKEN('TABLE_NAME','B3'); --made B3 from A4 by gmuralid
1935 IGS_GE_MSG_STACK.ADD;
1936 RAISE exception_in_setup;
1937 END IF;
1938 CLOSE business_networth_cur;
1939
1940 IF business_networth_rec.tax_rate IS NULL THEN
1941 l_adj_business_networth := business_networth_rec.amount +
1942 (( business_networth_rec.tax_rate_excess * (l_business_networth - business_networth_rec.amount_excess)) / 100 );
1943 ELSE
1944 l_adj_business_networth := ( business_networth_rec.tax_rate * l_business_networth ) / 100 ;
1945 END IF ;
1946
1947 -- Get Cash, Savings and Checking
1948 IF igf_ap_efc_calc.isir_rec.s_cash_savings IS NULL THEN
1949 l_cash_saving := 0;
1950 ELSE
1951 l_cash_saving := igf_ap_efc_calc.isir_rec.s_cash_savings;
1952 END IF;
1953
1954 -- Get Net Worth
1955 -- (Investment Net worth + Adjusted Business Net Worth + Cash Saving)
1956 l_net_worth := l_investment_networth + l_adj_business_networth + l_cash_saving ;
1957
1958 igf_ap_efc_calc.isir_rec.efc_networth := ROUND(l_net_worth);
1962 IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
1959 igf_ap_efc_calc.isir_rec.secnw := ROUND(l_net_worth);
1960
1961 -- Get the Student's Age. If the Date of Birth is not specified in the ISIR then stop the Processing of the Student.
1963 -- l_age_student := FLOOR(MONTHS_BETWEEN(l_base_date,igf_ap_efc_calc.isir_rec.date_of_birth)/12); -- confirmed with ches and pdf , made 31/12/2003
1964 l_age_student := FLOOR((TO_NUMBER(TO_CHAR(l_base_date,'YYYY')) - TO_NUMBER(TO_CHAR(igf_ap_efc_calc.isir_rec.date_of_birth,'YYYY'))));
1965
1966 ELSE
1967 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_DOB_SPECIFIED');
1968 IGS_GE_MSG_STACK.ADD;
1969 RAISE exception_in_setup;
1970 END IF;
1971
1972 IF l_age_student < 25 THEN
1973 l_age_student := 25 ;
1974 ELSIF l_age_student > 65 THEN
1975 l_age_student := 65;
1976 END IF ;
1977
1978 -- Get Education savings and Asset Protection Allowance(Using B4 as both contains the same Information)
1979 OPEN asset_cur(l_age_student);
1980 FETCH asset_cur INTO asset_rec;
1981 IF asset_cur%NOTFOUND THEN
1982 CLOSE asset_cur;
1983 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_ASSET_SETUP');
1984 FND_MESSAGE.SET_TOKEN('TABLE_NAME','B4');
1985 IGS_GE_MSG_STACK.ADD;
1986 RAISE exception_in_setup;
1987 END IF;
1988 CLOSE asset_cur;
1989
1990 IF NVL ( igf_ap_efc_calc.isir_rec.a_student_marital_status , igf_ap_efc_calc.isir_rec.s_marital_status ) = '2' THEN
1991 l_asset_pro_allow := NVL(asset_rec.parent2_allowance,0);
1992 ELSE
1993 l_asset_pro_allow := NVL(asset_rec.parent1_allowance,0);
1994 END IF;
1995
1996
1997 igf_ap_efc_calc.isir_rec.asset_protect_allow := l_asset_pro_allow;
1998 igf_ap_efc_calc.isir_rec.secapa := l_asset_pro_allow;
1999
2000 -- Get Discretionary Net Worth
2001 l_d_net_worth := l_net_worth - l_asset_pro_allow ;
2002
2003 igf_ap_efc_calc.isir_rec.discretionary_networth := ROUND(l_d_net_worth);
2004 igf_ap_efc_calc.isir_rec.secdnw := ROUND(l_d_net_worth);
2005
2006 -- Get the Setting for Formula B
2007 OPEN efcB_cur;
2008 FETCH efcB_cur INTO efcB_rec;
2009 IF efcB_cur%NOTFOUND THEN
2010 CLOSE efcB_cur;
2011 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCB_SETUP');
2012 IGS_GE_MSG_STACK.ADD;
2013 RAISE exception_in_setup;
2014 END IF;
2015 CLOSE efcB_cur;
2016
2017 -- Get Contribution from Assets
2018 p_s_cont_assets := l_d_net_worth * NVL(efcB_rec.stud_asset_conv_rate,0) / 100;
2019
2020 IF p_s_cont_assets < 0 THEN
2021 p_s_cont_assets := 0;
2022 END IF;
2023
2024 p_s_cont_assets:= ROUND( p_s_cont_assets );
2025
2026 igf_ap_efc_calc.isir_rec.sca := p_s_cont_assets; --Assignment of intermediate values
2027 igf_ap_efc_calc.isir_rec.secsca := p_s_cont_assets; --Assignment of intermediate values
2028
2029 EXCEPTION
2030 WHEN exception_in_setup THEN
2031 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
2032 WHEN OTHERS THEN
2033 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2034 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.B_S_CONT_ASSETS');
2035 IGS_GE_MSG_STACK.ADD;
2036 APP_EXCEPTION.RAISE_EXCEPTION;
2037
2038 END b_s_cont_assets;
2039
2040
2041 PROCEDURE b_efc ( p_s_cont IN NUMBER,
2042 p_s_cont_assets IN NUMBER,
2043 p_efc OUT NOCOPY NUMBER) AS
2044 /*
2045 || Created By : gmuralid
2046 || Created On : 11 Feb 2003
2047 || Purpose : Procedure to get efc from formula B , Bug# 2758804 , EFC build TD
2048 || Known limitations, enhancements or remarks :
2049 || Change History :
2050 || Who When What
2051 || (reverse chronological order - newest change first)
2052 */
2053 l_s_cont NUMBER;
2054
2055 BEGIN
2056 l_s_cont := p_s_cont + p_s_cont_assets;
2057
2058 p_efc := ROUND(l_s_cont) / NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college),1);
2059 p_efc:= ROUND(p_efc);
2060 IF p_efc < 0 THEN
2061 p_efc := 0;
2062 END IF;
2063
2064 END b_efc;
2065
2066
2067 PROCEDURE b_efc_less_9 ( p_no_of_months IN NUMBER,
2068 p_efc IN OUT NOCOPY NUMBER) AS
2069 /*
2070 || Created By : gmuralid
2071 || Created On : 11 Feb 2003
2072 || Purpose : Procedure to get efc from formula B for less than 9 months, Bug# 2758804 , EFC build TD
2073 || Known limitations, enhancements or remarks :
2074 || Change History :
2075 || Who When What
2076 || (reverse chronological order - newest change first)
2077 */
2078
2079 l_efc_per_month NUMBER;
2080
2081 BEGIN
2082 -- To determine the EFC per month
2083
2084
2085 l_efc_per_month := p_efc / 9;
2086
2087 -- Get EFC for no of months of enrollment
2088 p_efc := l_efc_per_month * p_no_of_months ;
2089
2090 END b_efc_less_9;
2091
2092
2093 -- SUB FUNCTIONS for calculating EFC with FORMULA C
2094
2098 || Created On :11 Feb 2003
2095 PROCEDURE c_s_inc ( p_s_inc OUT NOCOPY NUMBER) AS
2096 /*
2097 || Created By :gmuralid
2099 || Purpose : Procedure to get students income for formula C, Bug# 2758804 , EFC build TD
2100 || Known limitations, enhancements or remarks :
2101 || Change History :
2102 || Who When What
2103 || (reverse chronological order - newest change first)
2104 */
2105 -- Initialize the local variables with the Global variables.
2106
2107 l_adjusted_gross_income igf_ap_isir_matched.s_adjusted_gross_income%TYPE;
2108 l_s_inc_work NUMBER;
2109 l_tot_tax_inc NUMBER;
2110 l_tot_untax_inc NUMBER;
2111 l_tot_inc NUMBER;
2112
2113 -- Cursor to find out the ISIR information for the Student
2114 BEGIN
2115
2116 -- Get Student's and spouse's Adjusted Gross Income( If negative take 0)
2117 IF NVL(NVL(igf_ap_efc_calc.isir_rec.a_student_agi,igf_ap_efc_calc.isir_rec.s_adjusted_gross_income),0) <= 0 THEN
2118 l_adjusted_gross_income := 0;
2119 ELSE
2120 l_adjusted_gross_income := NVL(igf_ap_efc_calc.isir_rec.a_student_agi,igf_ap_efc_calc.isir_rec.s_adjusted_gross_income);
2121 END IF;
2122
2123 -- Get Student's and Spouse's Toatal income earned from work
2124 l_s_inc_work := NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) +
2125 NVL(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0);
2126
2127 -- Get the Student's Taxable income( If Taxable = Adjusted Gross Income Else
2128 -- = Income earned from Work)
2129 IF igf_ap_efc_calc.isir_rec.s_cal_tax_status IN ('1','2','3') THEN
2130 l_tot_tax_inc := l_adjusted_gross_income;
2131 ELSE
2132 l_tot_tax_inc := l_s_inc_work;
2133 END IF;
2134
2135 -- Total Untaxed Income and benefits
2136 -- Total from FAFSA Worksheet A + Total from FAFSA Worksheet B
2137 l_tot_untax_inc := NVL(igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsa,0) + NVL(igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsb,0);
2138
2139 -- Total Income = Total Taxable Income + Total Untaxable Income.
2140 l_tot_inc := l_tot_tax_inc + l_tot_untax_inc;
2141
2142 -- Student's total Income = Total Income - Total from FAFSA Worksheet C
2143 p_s_inc := l_tot_inc - NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_total_wsc,igf_ap_efc_calc.isir_rec.s_toa_amt_from_wsc),0);
2144
2145 igf_ap_efc_calc.isir_rec.total_income := p_s_inc; --Assignment of intermediate values
2146 igf_ap_efc_calc.isir_rec.secti := p_s_inc; --Assignment of intermediate values
2147
2148 igf_ap_efc_calc.isir_rec.fti := p_s_inc;
2149 igf_ap_efc_calc.isir_rec.secfti := p_s_inc;
2150
2151
2152 EXCEPTION
2153
2154 WHEN OTHERS THEN
2155 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2156 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.C_S_INC');
2157 IGS_GE_MSG_STACK.ADD;
2158 APP_EXCEPTION.RAISE_EXCEPTION;
2159 END c_s_inc;
2160
2161
2162 PROCEDURE c_allow_ag_s_inc ( p_s_inc IN NUMBER,
2163 p_allow_ag_s_inc OUT NOCOPY NUMBER) AS
2164 /*
2165 || Created By : gmuralid
2166 || Created On : 11 Feb 2003
2167 || Purpose : Procedure to get allowances against students income for formula C, Bug# 2758804 , EFC build TD
2168 || Known limitations, enhancements or remarks :
2169 || Change History :
2170 || Who When What
2171 || (reverse chronological order - newest change first)
2172 */
2173 -- Initialize the local variables with the Global variables.
2174
2175 l_s_taxes_paid igf_ap_isir_matched.p_taxes_paid%TYPE;
2176 l_state igf_lookups_view.lookup_code%TYPE;
2177 l_tax_allowance NUMBER;
2178 l_s_sst NUMBER;
2179 l_spouse_sst NUMBER;
2180 l_ipa NUMBER;
2181 l_eea NUMBER;
2182
2183 -- Cursor to find the Valid State
2184 CURSOR state_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
2185 SELECT lookup_code
2186 FROM igf_lookups_view
2187 WHERE lookup_type = 'IGF_AP_STATE_CODES'
2188 AND lookup_code = cp_state;
2189
2190 -- Cursor to calculate State and other tax allowance(Use of table A1 instead C1 as both have same Information)
2191 CURSOR state_allow_cur(cp_state igf_lookups_view.lookup_code%TYPE) IS
2192 SELECT tax_rate
2193 FROM igf_fc_state_tx txrng
2194 WHERE txrng.table_code = 'C1'
2195 AND txrng.state_code = cp_state
2196 AND txrng.s_award_year = igf_ap_efc_calc.p_sys_award_year
2197 AND (p_s_inc BETWEEN txrng.income_range_start AND txrng.income_range_end) ;
2198
2199 -- Cursor to calculate Social Security Tax for Student and Spouse.(Use of table A2 instead C2 as both have same Information)
2200 CURSOR sst_cur(cp_inc_work igf_ap_isir_matched.s_income_from_work%TYPE) IS
2201 SELECT tax_rate, amount, tax_rate_excess, amount_excess
2202 FROM igf_fc_gen_tax_rts gtxrts
2203 WHERE gtxrts.table_code = 'C2'
2204 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
2208 CURSOR ipa_cur(cp_num_family_member igf_ap_isir_matched.p_num_family_member%TYPE,
2205 AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
2206
2207 -- Cursor to calculate Income Protection Allowance(Use of table C3 )
2209 cp_num_in_college igf_ap_isir_matched.p_num_in_college%TYPE) IS
2210 SELECT ip_allowance_amt
2211 FROM igf_fc_inc_prct ipa
2212 WHERE ipa.table_code = 'C3'
2213 AND ipa.people_in_household = cp_num_family_member
2214 AND ipa.students_in_household = cp_num_in_college
2215 AND ipa.s_award_year = igf_ap_efc_calc.p_sys_award_year;
2216
2217 -- Cursor to find the Default values defined for Formula C.
2218 CURSOR efcC_cur IS
2219 SELECT eea_mrd_2_wrk_rate, eea_mrd_2_wrk_amt,
2220 eea_mrd_1_wrk_rate, eea_mrd_1_wrk_amt
2221 FROM igf_fc_efc_frm_c efcc
2222 WHERE efcc.s_award_year = igf_ap_efc_calc.p_sys_award_year;
2223
2224 state_rec state_cur%ROWTYPE;
2225 state_allow_rec state_allow_cur%ROWTYPE;
2226 sst_rec sst_cur%ROWTYPE;
2227 ipa_rec ipa_cur%ROWTYPE;
2228 efcC_rec efcC_cur%ROWTYPE;
2229 l_stud_fam_cont NUMBER ;
2230 l_stud_col_cont NUMBER ;
2231 s_n_fam NUMBER ;
2232 s_n_fam1 NUMBER ;
2233 s_n_col NUMBER ;
2234 s_n_col1 NUMBER ;
2235 l_state_cd VARCHAR2(30);
2236
2237 BEGIN
2238 -- For Non Tax Filers OR with Income Tax Paid Negative should be processed, process with 0
2239 IF ((NVL(igf_ap_efc_calc.isir_rec.s_cal_tax_status,'-1') NOT IN ('1','2','3'))
2240 OR (NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_us_tax_paid,igf_ap_efc_calc.isir_rec.s_fed_taxes_paid),0) <= 0))
2241 THEN
2242 l_s_taxes_paid := 0;
2243 ELSE
2244 l_s_taxes_paid := NVL(igf_ap_efc_calc.isir_rec.a_s_us_tax_paid,igf_ap_efc_calc.isir_rec.s_fed_taxes_paid);
2245 END IF;
2246
2247 -- Process to calculate State and other tax allowance only if Student's Income > 0
2248 IF p_s_inc > 0 THEN
2249 -- To find the proper State process in the order of
2250 -- Students state of legal residence (If invalid then)
2251 -- State in the Students mailing address (If invalid then)
2252 -- Blank or Invalid state
2253 l_state_cd := igf_ap_efc_calc.isir_rec.s_state_legal_residence;
2254
2255 IF l_state_cd = 'FC' THEN
2256 l_state_cd := 'OT';
2257 END IF;
2258
2259 OPEN state_cur(l_state_cd);
2260 FETCH state_cur INTO state_rec;
2261 l_state := state_rec.lookup_code;
2262 CLOSE state_cur;
2263
2264 IF l_state IS NULL THEN
2265
2266 l_state_cd := igf_ap_efc_calc.isir_rec.perm_state;
2267
2268 IF l_state_cd = 'FC' THEN
2269 l_state_cd := 'OT';
2270 END IF;
2271
2272 OPEN state_cur(l_state_cd);
2273 FETCH state_cur INTO state_rec;
2274 l_state := state_rec.lookup_code;
2275 CLOSE state_cur;
2276 END IF;
2277
2278 IF l_state IS NULL THEN
2279 l_state := 'BL';
2280 END IF;
2281
2282 OPEN state_allow_cur(l_state);
2283 FETCH state_allow_cur INTO state_allow_rec;
2284 IF state_allow_cur%NOTFOUND THEN
2285 CLOSE state_allow_cur;
2286 OPEN state_allow_cur('OT');
2287 FETCH state_allow_cur INTO state_allow_rec;
2288 IF state_allow_cur%NOTFOUND THEN
2289 CLOSE state_allow_cur;
2290 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_TAX_SETUP');
2291 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C1');
2292 IGS_GE_MSG_STACK.ADD;
2293 RAISE exception_in_setup;
2294 END IF;
2295 END IF;
2296 CLOSE state_allow_cur;
2297
2298 -- Determine the State and other tax allowance
2299 l_tax_allowance := ( p_s_inc * state_allow_rec.tax_rate) / 100 ;
2300
2301 -- State and other tax allowance can not be Negative
2302 IF ( l_tax_allowance < 0 ) THEN
2303 l_tax_allowance := 0;
2304 END IF;
2305
2306 ELSE -- If Student's Income <= 0 then assume Tax Allowance to be 0
2307 l_tax_allowance := 0;
2308 END IF;
2309
2310 igf_ap_efc_calc.isir_rec.state_tax_allow := ROUND(l_tax_allowance);
2311 igf_ap_efc_calc.isir_rec.secstx := ROUND(l_tax_allowance);
2312
2313
2314 -- Calculating the Social Security Tax for Student
2315 IF NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL THEN
2316 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0));
2317 FETCH sst_cur INTO sst_rec;
2318 IF sst_cur%NOTFOUND THEN
2319 CLOSE sst_cur;
2320 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
2321 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C2');
2322 IGS_GE_MSG_STACK.ADD;
2323 RAISE exception_in_setup;
2327 IF sst_rec.tax_rate IS NULL THEN
2324 END IF;
2325 CLOSE sst_cur;
2326
2328 l_s_sst := sst_rec.amount +
2329 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) - sst_rec.amount_excess)) / 100 );
2330 ELSE
2331 l_s_sst := ( sst_rec.tax_rate * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0) ) / 100 );
2332 END IF ;
2333
2334 -- Social Security Tax can not be Negative
2335 IF ( l_s_sst < 0 ) THEN
2336 l_s_sst := 0;
2337 END IF;
2338 ELSE
2339 l_s_sst := 0;
2340 END IF;
2341
2342 -- Calculating the Social Security Tax for Spouse
2343 IF NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NOT NULL THEN
2344 OPEN sst_cur(greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0));
2345 FETCH sst_cur INTO sst_rec;
2346 IF sst_cur%NOTFOUND THEN
2347 CLOSE sst_cur;
2348 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
2349 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C2');
2350 IGS_GE_MSG_STACK.ADD;
2351 RAISE exception_in_setup;
2352 END IF;
2353 CLOSE sst_cur;
2354
2355 IF sst_rec.tax_rate IS NULL THEN
2356 l_spouse_sst := sst_rec.amount +
2357 (( sst_rec.tax_rate_excess * (greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0) - sst_rec.amount_excess)) / 100 );
2358 ELSE
2359 l_spouse_sst := ( sst_rec.tax_rate * greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0) ) / 100 ;
2360 END IF ;
2361
2362 -- Social Security Tax can not be Negative
2363 IF ( l_spouse_sst < 0 ) THEN
2364 l_spouse_sst := 0;
2365 END IF;
2366 ELSE
2367 l_spouse_sst := 0;
2368 END IF;
2369
2370
2371 s_n_fam := NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_family,igf_ap_efc_calc.isir_rec.s_num_family_members);
2372 s_n_col := NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college);
2373
2374 IF s_n_fam IS NOT NULL AND s_n_col IS NOT NULL THEN
2375 -- Calculate Income Protection Allowance of Student
2376 OPEN ipa_cur(s_n_fam,s_n_col);
2377 FETCH ipa_cur INTO ipa_rec;
2378 IF ipa_cur%NOTFOUND THEN
2379 CLOSE ipa_cur;
2380 IF( s_n_fam IS NULL) OR (s_n_col IS NULL ) THEN
2381 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_INC_PRT_ALW_SETUP');
2382 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C3');
2383 IGS_GE_MSG_STACK.ADD;
2384 RAISE exception_in_setup;
2385 END IF;
2386
2387 s_n_fam1 := 0;
2388 s_n_col1 := 0;
2389
2390 IF s_n_fam > 6 THEN
2391 s_n_fam1 := s_n_fam - 6;
2392 s_n_fam := 6 ;
2393 END IF;
2394
2395 IF s_n_col > 5 THEN
2396 s_n_col1 := s_n_col - 5;
2397 s_n_col := 5 ;
2398 END IF;
2399
2400 get_par_stud_cont( igf_ap_efc_calc.p_sys_award_year, l_stud_fam_cont,l_stud_col_cont );
2401
2402 OPEN ipa_cur(s_n_fam,s_n_col);
2403 FETCH ipa_cur INTO ipa_rec;
2404 CLOSE ipa_cur ;
2405 l_ipa := (ipa_rec.ip_allowance_amt + (s_n_fam1 * l_stud_fam_cont ) ) - ((s_n_col1 * l_stud_col_cont ));
2406 ELSE -- IPA CUR FOUND
2407 CLOSE ipa_cur;
2408 l_ipa := ipa_rec.ip_allowance_amt;
2409 END IF;
2410 ELSE
2411 l_ipa := 0;
2412 END IF;
2413
2414 igf_ap_efc_calc.isir_rec.income_protection_allow := l_ipa;
2415 igf_ap_efc_calc.isir_rec.secipa := l_ipa;
2416
2417
2418 -- To find the Default values defined for EFC Formula C( To determine the Employment Expense Allowance)
2419 OPEN efcC_cur;
2420 FETCH efcC_cur INTO efcC_rec;
2421 IF efcC_cur%NOTFOUND THEN
2422 CLOSE efcC_cur;
2423 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCC_SETUP');
2424 IGS_GE_MSG_STACK.ADD;
2425 RAISE exception_in_setup;
2426 END IF;
2427 CLOSE efcC_cur;
2428
2429 -- Calculate the Employment Expense Allowance
2430 IF NVL ( igf_ap_efc_calc.isir_rec.a_student_marital_status , igf_ap_efc_calc.isir_rec.s_marital_status ) = '2' THEN -- Married Student
2431 -- If only one out of them is working
2432 IF ( (NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NULL
2433 AND NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NOT NULL)
2434 OR ( NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL
2435 AND NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NULL))
2436 THEN
2437 l_eea := 0;
2441 THEN
2438 -- If both of them are working
2439 ELSIF ( NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work) IS NOT NULL
2440 AND NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work) IS NOT NULL)
2442 l_eea := LEAST((NVL(efcC_rec.eea_mrd_2_wrk_rate,0) *
2443 LEAST(greatest(NVL(igf_ap_efc_calc.isir_rec.a_spouse_income_work,igf_ap_efc_calc.isir_rec.spouse_income_from_work),0),
2444 greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0))
2445 /100),NVL(efcC_rec.eea_mrd_2_wrk_amt,0));
2446
2447 -- If both of the above conditions are failed
2448 ELSE
2449 l_eea := 0;
2450 END IF;
2451 ELSE -- Student is unmarried / Separated( One Parent Family)
2452 l_eea := LEAST((NVL(efcC_rec.eea_mrd_1_wrk_rate,0) *
2453 NVL(greatest(NVL(igf_ap_efc_calc.isir_rec.a_s_income_work,igf_ap_efc_calc.isir_rec.s_income_from_work),0),0)/100),
2454 NVL(efcC_rec.eea_mrd_1_wrk_amt,0));
2455 END IF;
2456
2457 igf_ap_efc_calc.isir_rec.employment_allow := ROUND(l_eea);
2458 igf_ap_efc_calc.isir_rec.secea := ROUND(l_eea);
2459
2460
2461 -- Allowance against Student's Income
2462 p_allow_ag_s_inc := ROUND(l_s_taxes_paid) + ROUND(l_tax_allowance) + ROUND(l_s_sst) + ROUND(l_spouse_sst) + ROUND(l_ipa) + ROUND(l_eea);
2463 -- p_allow_ag_s_inc := ROUND( p_allow_ag_s_inc );
2464
2465 igf_ap_efc_calc.isir_rec.allow_total_income := p_allow_ag_s_inc; --Assignment of intermediate values
2466 igf_ap_efc_calc.isir_rec.secati := p_allow_ag_s_inc; --Assignment of intermediate values
2467
2468
2469 EXCEPTION
2470 WHEN exception_in_setup THEN
2471 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
2472 WHEN OTHERS THEN FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2473 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.C_ALLOW_AG_S_INC');
2474 IGS_GE_MSG_STACK.ADD;
2475 APP_EXCEPTION.RAISE_EXCEPTION;
2476
2477 END c_allow_ag_s_inc;
2478
2479
2480 PROCEDURE c_available_inc ( p_s_inc IN NUMBER,
2481 p_allow_ag_s_inc IN NUMBER,
2482 p_available_income OUT NOCOPY NUMBER) AS
2483 /*
2484 || Created By : gmuralid
2485 || Created On : 11 Feb 2003
2486 || Purpose : Procedure to get available income for formula C, Bug# 2758804 , EFC build TD
2487 || Known limitations, enhancements or remarks :
2488 || Change History :
2489 || Who When What
2490 || (reverse chronological order - newest change first)
2491 */
2492
2493 BEGIN
2494
2495 p_available_income := p_s_inc - p_allow_ag_s_inc;
2496 p_available_income := ROUND( p_available_income);
2497
2498 igf_ap_efc_calc.isir_rec.available_income := p_available_income; --Assignment of intermediate values
2499 igf_ap_efc_calc.isir_rec.secai := p_available_income; --Assignment of intermediate values
2500
2501
2502 END c_available_inc;
2503
2504
2505 PROCEDURE c_s_cont_assets ( p_s_cont_assets OUT NOCOPY NUMBER) AS
2506 /*
2507 || Created By : gmuralid
2508 || Created On : 11 Feb 2003
2509 || Purpose : Procedure to get students contribution from assets for formula C, Bug# 2758804 , EFC build TD
2510 || Known limitations, enhancements or remarks :
2511 || Change History :
2512 || Who When What
2513 || (reverse chronological order - newest change first)
2514 */
2515 -- Initialize the local variables with the Global variables.
2516
2517 l_investment_networth igf_ap_isir_matched.p_investment_networth%TYPE;
2518 l_business_networth igf_ap_isir_matched.p_business_networth%TYPE;
2519 l_adj_business_networth NUMBER;
2520 l_cash_saving igf_ap_isir_matched.p_cash_saving%TYPE;
2521 l_net_worth NUMBER;
2522 l_age_student NUMBER;
2523 l_asset_pro_allow igf_fc_ast_pc_dt.parent2_allowance%TYPE;
2524 l_d_net_worth NUMBER;
2525
2526 -- Cursor to find the Setting for Formula C
2527 CURSOR efcC_cur IS
2528 SELECT stud_asset_conv_rate
2529 FROM igf_fc_efc_frm_c efcc
2530 WHERE efcc.s_award_year = igf_ap_efc_calc.p_sys_award_year;
2531
2532 -- Cursor to calculate Asset Protection Allowance(Using the A5 instead of C5 as both contains the same Information)
2533 CURSOR asset_cur(cp_age_student igf_fc_ast_pc_dt.older_parent_age%TYPE) IS
2534 SELECT parent1_allowance, parent2_allowance
2535 FROM igf_fc_ast_pc_dt apdt
2536 WHERE apdt.table_code = 'C5'
2537 AND apdt.older_parent_age = cp_age_student
2538 AND apdt.s_award_year = igf_ap_efc_calc.p_sys_award_year;
2539
2540 -- Cursor to calculate Adjusted Net worth of Business/farm(Using the A4 instead of C4 as both contains the same Information)
2541 CURSOR business_networth_cur(cp_business_networth igf_ap_isir_matched.p_business_networth%TYPE) IS
2542 SELECT tax_rate, amount, tax_rate_excess, amount_excess
2543 FROM igf_fc_gen_tax_rts gtxrts
2544 WHERE gtxrts.table_code = 'C4'
2545 AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
2549 efcC_rec efcC_cur%ROWTYPE;
2546 AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
2547
2548 business_networth_rec business_networth_cur%ROWTYPE;
2550 asset_rec asset_cur%ROWTYPE;
2551 l_base_Date DATE;
2552
2553 BEGIN
2554
2555 IF igf_ap_efc_calc.p_sys_award_year ='0304' THEN
2556 l_base_Date := to_date('31-12-2003', 'DD-MM-YYYY');
2557 ELSIF igf_ap_efc_calc.p_sys_award_year ='0405' THEN
2558 l_base_Date := to_date('31-12-2004', 'DD-MM-YYYY');
2559 ELSIF igf_ap_efc_calc.p_sys_award_year ='0506' THEN
2560 l_base_Date := to_date('31-12-2005', 'DD-MM-YYYY');
2561 ELSIF igf_ap_efc_calc.p_sys_award_year ='0607' THEN
2562 l_base_Date := to_date('31-12-2006', 'DD-MM-YYYY');
2563 ELSE
2564 l_base_Date := to_date('31-12-2002', 'dd-MM-YYYY');
2565 END IF;
2566
2567 -- Get Net worth of investments( Can not be Negative)
2568 IF NVL(igf_ap_efc_calc.isir_rec.s_investment_networth,0) <= 0 THEN
2569 l_investment_networth := 0;
2570 ELSE
2571 l_investment_networth := igf_ap_efc_calc.isir_rec.s_investment_networth;
2572 END IF;
2573
2574 -- Get Net Worth of business/investments farm(Can not be Negative)
2575 IF NVL(igf_ap_efc_calc.isir_rec.s_busi_farm_networth,0) <= 0 THEN
2576 l_business_networth := 0;
2577 ELSE
2578 l_business_networth := igf_ap_efc_calc.isir_rec.s_busi_farm_networth;
2579 END IF;
2580
2581 -- Get Adjusted net worth of Business/Farm
2582 OPEN business_networth_cur(l_business_networth);
2583 FETCH business_networth_cur INTO business_networth_rec;
2584 IF business_networth_cur%NOTFOUND THEN
2585 CLOSE business_networth_cur;
2586 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
2587 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C4');
2588 IGS_GE_MSG_STACK.ADD;
2589 RAISE exception_in_setup;
2590 END IF;
2591 CLOSE business_networth_cur;
2592
2593 IF business_networth_rec.tax_rate IS NULL THEN
2594 l_adj_business_networth := business_networth_rec.amount +
2595 (( business_networth_rec.tax_rate_excess * (l_business_networth - business_networth_rec.amount_excess)) / 100 );
2596 ELSE
2597 l_adj_business_networth := ( business_networth_rec.tax_rate * l_business_networth ) / 100 ;
2598 END IF ;
2599
2600 -- Get Cash, Savings and Checking
2601 l_cash_saving := NVL(igf_ap_efc_calc.isir_rec.s_cash_savings,0);
2602
2603 -- Get Net Worth
2604 -- (Investment Net worth + Adjusted Business Net Worth + Cash Saving)
2605 l_net_worth := l_investment_networth + l_adj_business_networth + l_cash_saving ;
2606
2607 igf_ap_efc_calc.isir_rec.efc_networth := ROUND(l_net_worth);
2608 igf_ap_efc_calc.isir_rec.secnw := ROUND(l_net_worth);
2609
2610 -- Get the Student's Age. If the Date of Birth is not specified in the ISIR then stop the Processing of the Student.
2611 IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
2612 -- l_age_student := FLOOR(MONTHS_BETWEEN(l_base_date,igf_ap_efc_calc.isir_rec.date_of_birth)/12);
2613 l_age_student := FLOOR((TO_NUMBER(TO_CHAR(l_base_date,'YYYY')) - TO_NUMBER(TO_CHAR(igf_ap_efc_calc.isir_rec.date_of_birth,'YYYY'))));
2614 ELSE
2615 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_DOB_SPECIFIED');
2616 IGS_GE_MSG_STACK.ADD;
2617 RAISE exception_in_setup;
2618 END IF;
2619
2620 IF l_age_student < 25 THEN
2621 l_age_student := 25 ;
2622 ELSIF l_age_student > 65 THEN
2623 l_age_student := 65;
2624 END IF ;
2625
2626 -- Get Education savings and Asset Protection Allowance(Using the A5 instead of C5 as both contains the same Information)
2627 OPEN asset_cur(l_age_student);
2628 FETCH asset_cur INTO asset_rec;
2629 IF asset_cur%NOTFOUND THEN
2630 CLOSE asset_cur;
2631 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_ASSET_SETUP');
2632 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C5');
2633 IGS_GE_MSG_STACK.ADD;
2634 RAISE exception_in_setup;
2635 END IF;
2636 CLOSE asset_cur;
2637
2638 IF NVL ( igf_ap_efc_calc.isir_rec.a_student_marital_status , igf_ap_efc_calc.isir_rec.s_marital_status ) = '2' THEN
2639 l_asset_pro_allow := NVL(asset_rec.parent2_allowance,0);
2640 ELSE
2641 l_asset_pro_allow := NVL(asset_rec.parent1_allowance,0);
2642 END IF;
2643
2644 igf_ap_efc_calc.isir_rec.asset_protect_allow := l_asset_pro_allow;
2645 igf_ap_efc_calc.isir_rec.secapa := l_asset_pro_allow;
2646
2647 -- Get Discretionary Net Worth
2648 l_d_net_worth := l_net_worth - l_asset_pro_allow ;
2649
2650 igf_ap_efc_calc.isir_rec.discretionary_networth := ROUND(l_d_net_worth) ;
2651 igf_ap_efc_calc.isir_rec.secdnw := ROUND(l_d_net_worth);
2652
2653
2654 -- Get the Setting for Formula C
2655 OPEN efcC_cur;
2656 FETCH efcC_cur INTO efcC_rec;
2657 IF efcC_cur%NOTFOUND THEN
2658 CLOSE efcC_cur;
2659 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_EFCC_SETUP');
2660 IGS_GE_MSG_STACK.ADD;
2661 RAISE exception_in_setup;
2662 END IF;
2663 CLOSE efcC_cur;
2664
2665 -- Get Contribution from Assets
2666 p_s_cont_assets := l_d_net_worth * NVL(efcC_rec.stud_asset_conv_rate,0) / 100;
2667
2668 IF p_s_cont_assets < 0 THEN
2672 p_s_cont_assets := ROUND( p_s_cont_assets);
2669 p_s_cont_assets := 0;
2670 END IF;
2671
2673
2674 igf_ap_efc_calc.isir_rec.sca := p_s_cont_assets ; --Assignment of intermediate values
2675 igf_ap_efc_calc.isir_rec.secsca := p_s_cont_assets ; --Assignment of intermediate values
2676
2677 EXCEPTION
2678 WHEN exception_in_setup THEN
2679 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
2680 WHEN OTHERS THEN
2681 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2682 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.C_S_CONT_ASSETS');
2683 IGS_GE_MSG_STACK.ADD;
2684 APP_EXCEPTION.RAISE_EXCEPTION;
2685
2686 END c_s_cont_assets;
2687
2688
2689 PROCEDURE c_efc ( p_available_income IN NUMBER,
2690 p_s_cont_assets IN NUMBER,
2691 p_efc OUT NOCOPY NUMBER,
2692 l_call_type IN VARCHAR2 ) AS
2693 /*
2694 || Created By : gmuralid
2695 || Created On : 11 Feb 2003
2696 || Purpose : Procedure to get efc by using formula C, Bug# 2758804 , EFC build TD
2697 || Known limitations, enhancements or remarks :
2698 || Change History :
2699 || Who When What
2700 || (reverse chronological order - newest change first)
2701 */
2702 -- Initialize the local variables with the Global variables.
2703 -- l_call_type = P/S to compute for primary or secondary
2704
2705 l_aai NUMBER;
2706 l_cont_aai NUMBER;
2707
2708 -- Cursor to calculate Student's Contribution from AAI
2709 CURSOR cont_aai_cur(cp_aai NUMBER) IS
2710 SELECT tax_rate, amount, tax_rate_excess, amount_excess
2711 FROM igf_fc_gen_tax_rts gtxrts
2712 WHERE gtxrts.table_code = 'C6'
2713 AND gtxrts.s_award_year= igf_ap_efc_calc.p_sys_award_year
2714 AND (cp_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
2715
2716 cont_aai_rec cont_aai_cur%ROWTYPE;
2717
2718 BEGIN
2719 -- Get Adjusted Available Income
2720 -- ( Available Income + Contribution from Assets)
2721
2722 l_aai := p_available_income + p_s_cont_assets;
2723
2724 igf_ap_efc_calc.isir_rec.adjusted_available_income := l_aai;
2725 igf_ap_efc_calc.isir_rec.secaai := l_aai;
2726
2727
2728 -- Get Total students contribution from Adjusted Available Income
2729 OPEN cont_aai_cur(l_aai);
2730 FETCH cont_aai_cur INTO cont_aai_rec;
2731 IF cont_aai_cur%NOTFOUND THEN
2732 CLOSE cont_aai_cur;
2733 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_GEN_TAX_SETUP');
2734 FND_MESSAGE.SET_TOKEN('TABLE_NAME','C6');
2735 IGS_GE_MSG_STACK.ADD;
2736 RAISE exception_in_setup;
2737 END IF;
2738 CLOSE cont_aai_cur;
2739
2740 IF cont_aai_rec.tax_rate IS NULL THEN
2741 l_cont_aai := cont_aai_rec.amount +
2742 (( cont_aai_rec.tax_rate_excess * (l_aai - cont_aai_rec.amount_excess)) / 100 );
2743 ELSE
2744 l_cont_aai := ( cont_aai_rec.tax_rate * l_aai ) / 100 ;
2745 END IF ;
2746
2747 -- Total students contribution from Adjusted Available Income
2748 -- (Can not be Negative)
2749 IF l_cont_aai < 0 THEN
2750 l_cont_aai := 0;
2751 END IF;
2752
2753 IF l_call_type = 'P' THEN
2754
2755 igf_ap_efc_calc.isir_rec.total_student_contribution := ROUND(l_cont_aai); --- ROUND(NVL(p_s_cont_assets,0));
2756
2757 ELSE
2758
2759 igf_ap_efc_calc.isir_rec.sectsc := ROUND(l_cont_aai);
2760
2761 END IF;
2762
2763 -- Get parents contribution (Total Parents contribution from AAI/Number in college in 2001-02)
2764 -- (Can not be Negative)
2765
2766 p_efc := ROUND(l_cont_aai) / NVL(NVL(igf_ap_efc_calc.isir_rec.a_s_num_in_college,igf_ap_efc_calc.isir_rec.s_num_in_college),1);
2767 p_efc := ROUND(p_efc);
2768
2769 IF p_efc < 0 THEN
2770 p_efc := 0;
2771 END IF;
2772 EXCEPTION
2773 WHEN exception_in_setup THEN
2774 RAISE igf_ap_efc_calc.exception_in_setup; -- Exception to be handled in the Calling Procedures
2775 WHEN OTHERS THEN
2776 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2777 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.C_EFC');
2778 IGS_GE_MSG_STACK.ADD;
2779 APP_EXCEPTION.RAISE_EXCEPTION;
2780
2781 END c_efc;
2782
2783
2784 PROCEDURE c_efc_less_9 ( p_no_of_months IN NUMBER,
2785 p_efc IN OUT NOCOPY NUMBER) AS
2786 /*
2787 || Created By : gmuralid
2788 || Created On : 11 Feb 2003
2789 || Purpose : Procedure to get efc for less than 9 months using formula C, Bug# 2758804 , EFC build TD
2790 || Known limitations, enhancements or remarks :
2791 || Change History :
2792 || Who When What
2793 || (reverse chronological order - newest change first)
2794 */
2795
2796 l_efc_per_month NUMBER;
2797
2798 BEGIN
2799
2800 -- To determine the EFC per month
2801 l_efc_per_month := p_efc / 9;
2802
2803 -- Get EFC for no of months of enrollment
2804 p_efc := l_efc_per_month * p_no_of_months ;
2805
2806 END c_efc_less_9;
2807
2808 FUNCTION efc_cutoff_date ( p_sys_award_year IN VARCHAR2 )
2809 RETURN DATE IS
2810 /*
2811 || Created By : gmuralid
2812 || Created On : 11 Feb 2003
2813 || Purpose : Procedure to get efc cut off date, Bug# 2758804 , EFC build TD
2814 ||
2815 || Known limitations, enhancements or remarks :
2816 ||
2817 || Change History :
2818 || Who When What
2819 || nsidana 11/18/2003 FA129 EFC updates for 2004-2005.
2820 ||
2821 || (reverse chronological order - newest change first)
2822 */
2823
2824 l_date DATE;
2825
2826 BEGIN
2827
2828 IF p_sys_award_year = '0304' THEN
2829 l_date := TO_DATE('01/01/1980','DD/MM/YYYY');
2830 ELSIF p_sys_award_year = '0405' THEN
2831 l_date := TO_DATE('01/01/1981','DD/MM/YYYY');
2832 ELSIF p_sys_award_year = '0506' THEN
2833 l_date := TO_DATE('01/01/1982','DD/MM/YYYY');
2834 ELSIF p_sys_award_year = '0607' THEN
2835 l_date := TO_DATE('01/01/1983','DD/MM/YYYY');
2836 ELSE
2837 l_date := TO_DATE('01/01/1979','DD/MM/YYYY');
2838 END IF;
2839 RETURN (l_date);
2840
2841 EXCEPTION
2842 WHEN OTHERS THEN
2843 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2844 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.EFC_CUTOFF_DATE');
2845 IGS_GE_MSG_STACK.ADD;
2846 APP_EXCEPTION.RAISE_EXCEPTION;
2847
2848 END efc_cutoff_date;
2849
2850 PROCEDURE get_par_stud_cont( p_sys_award_year IN VARCHAR2,
2851 p_parent_cont OUT NOCOPY NUMBER,
2852 p_student_cont OUT NOCOPY NUMBER) AS
2853 /*
2854 || Created By : gmuralid
2855 || Created On : 11 Feb 2003
2856 || Purpose : Procedure to get award year specific parent student contributions for table A3 and C3
2857 || Bug# 2758804 , EFC build TD
2858 || Known limitations, enhancements or remarks :
2859 || Change History :
2860 || Who When What
2861 || (reverse chronological order - newest change first)
2862 */
2863
2864 BEGIN
2865
2866 IF p_sys_award_year = '0304' THEN
2867 p_parent_cont := 3230 ;
2868 p_student_cont := 2290 ;
2869 ELSIF p_sys_award_year = '0506' THEN
2870 p_parent_cont := 3320 ;
2871 p_student_cont := 2360 ;
2872 ELSIF p_sys_award_year = '0607' THEN
2873 p_parent_cont := 3460;
2874 p_student_cont := 2460;
2875 END IF;
2876
2877 EXCEPTION
2878 WHEN OTHERS THEN
2879 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2880 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_SUBF.GET_PAR_STUD_CONT');
2881 IGS_GE_MSG_STACK.ADD;
2882 APP_EXCEPTION.RAISE_EXCEPTION;
2883
2884 END get_par_stud_cont ;
2885
2886 PROCEDURE auto_zero_efc ( p_primary_efc_type IN VARCHAR2)
2887 AS
2888 /*
2889 || Created By : gmuralid
2890 || Created On : 11 Feb 2003
2891 || Purpose : Procedure to get award year specific parent student contributions for table A3 and C3
2892 || Bug# 2758804 , EFC build TD
2893 || Known limitations, enhancements or remarks :
2894 || Change History :
2895 || Who When What
2896 || (reverse chronological order - newest change first)
2897 */
2898 p_p_inc NUMBER;
2899 p_s_inc NUMBER;
2900
2901 BEGIN
2902
2903 IF p_primary_efc_type = '4' THEN
2904 igf_ap_efc_subf.a_p_inc(p_p_inc);
2905 igf_ap_efc_subf.a_s_inc( p_s_inc);
2906 ELSIF p_primary_efc_type = '5' THEN
2907 igf_ap_efc_subf.b_s_inc( p_s_inc);
2908 ELSIF p_primary_efc_type = '6' THEN
2909 igf_ap_efc_subf.c_s_inc( p_s_inc);
2910 END IF;
2911
2912 END auto_zero_efc;
2913
2914 END igf_ap_efc_subf;
[Home] [Help]