[Home] [Help]
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;
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
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)
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
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;
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:
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
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
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');
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
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:
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;
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);
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;
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
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;
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;
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;
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:
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
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;
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');
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:
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
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;
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');
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:
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
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);
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;
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 ;
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;
370: FETCH eea_cur INTO eea_rec;
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;
370: FETCH eea_cur INTO eea_rec;
371: IF eea_cur%NOTFOUND THEN
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)
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) *
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),
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),
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
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)
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
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.
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;
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
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),
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)
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);
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);
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
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
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;
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:
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:
484: l_base_date DATE;
485:
486: BEGIN
487:
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
491: l_base_date := TO_DATE('31-12-2004','DD-MM-YYYY');
492: ELSIF igf_ap_efc_calc.p_sys_award_year = '0506' THEN
486: BEGIN
487:
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
491: l_base_date := TO_DATE('31-12-2004','DD-MM-YYYY');
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
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
491: l_base_date := TO_DATE('31-12-2004','DD-MM-YYYY');
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;
490: IF igf_ap_efc_calc.p_sys_award_year = '0405' THEN
491: l_base_date := TO_DATE('31-12-2004','DD-MM-YYYY');
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 )
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;
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)
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
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;
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
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;
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
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;
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;
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
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;
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);
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;
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)
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
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;
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
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);
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
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;
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
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;
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 ;
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
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;
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;
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
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
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;
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
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'
730: AND (cp_p_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
731:
732: p_cont_aai_rec p_cont_aai_cur%ROWTYPE;
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
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);
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:
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
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
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
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;
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;
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);
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
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;
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);
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:
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 );
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
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 );
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
851:
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;
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 );
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
851:
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:
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
851:
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
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
851:
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
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.
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
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;
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
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);
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
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:
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;
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:
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;
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');
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:
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
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
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
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;
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;
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
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
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;
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
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;
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
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;
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);
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;
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;
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 );
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
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
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;
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
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;
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:
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;
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;
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
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:
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.
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;
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:
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;
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:
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:
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
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
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
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;
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;
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;
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:
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
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
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;
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');
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:
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
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;
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');
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 );
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
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);
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);
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
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 ;
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:
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
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
1732: l_ipa := NVL(efcB_rec.married_stud_ipa_amt,0);
1733: ELSE
1734: -- If both of the above conditions failed
1735: l_ipa := 0;
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
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
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
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
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);
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:
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
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),
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));
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
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;
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) ;
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 );
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
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
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;
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
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;
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:
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;
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:
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
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
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
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');
1846: IGS_GE_MSG_STACK.ADD;
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
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
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;
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
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
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
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;
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;
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
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;
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);
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;
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)
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);
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.
1962: IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
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);
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.
1962: IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
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
1958: igf_ap_efc_calc.isir_rec.efc_networth := ROUND(l_net_worth);
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.
1962: IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
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
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.
1962: IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
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');
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.
1962: IF igf_ap_efc_calc.isir_rec.date_of_birth IS NOT NULL THEN
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;
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;
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 ;
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:
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;
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;
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
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
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;
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;
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;
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) +
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)
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
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;
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:
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:
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;
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:
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
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:
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
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
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 )
2208: CURSOR ipa_cur(cp_num_family_member igf_ap_isir_matched.p_num_family_member%TYPE,
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,
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;
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
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);
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;
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;
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
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
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;
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');
2325: CLOSE sst_cur;
2326:
2327: IF sst_rec.tax_rate IS NULL THEN
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:
2327: IF sst_rec.tax_rate IS NULL THEN
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
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;
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');
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:
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
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
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);
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 ;
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)
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;
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
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
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;
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;
2438: -- If both of them are working
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;
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
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;
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)
2441: THEN
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),
2436: THEN
2437: l_eea := 0;
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)
2441: THEN
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))
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)
2441: THEN
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
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)
2441: THEN
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);
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
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);
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
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
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;
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;
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:
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
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
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
2546: AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);
2547:
2548: business_networth_rec business_networth_cur%ROWTYPE;
2549: efcC_rec efcC_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
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
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
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;
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;
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
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;
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);
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 ;
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
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);
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');
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;
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;
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;
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 ;
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:
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
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;
2670: END IF;
2671:
2672: p_s_cont_assets := ROUND( p_s_cont_assets);
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
2671:
2672: p_s_cont_assets := ROUND( p_s_cont_assets);
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
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;
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:
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
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);
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);
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)
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;
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;