[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_EFC_CALC
Source
1 PACKAGE BODY igf_ap_efc_calc AS
2 /* $Header: IGFAP25B.pls 120.3 2006/05/31 07:46:46 rajagupt ship $ */
3 /*
4 || Created By : pkpatel
5 || Created On : 10-DEC-2001
6 || Purpose : Bug No - 2142666 EFC DLD.
7 || This Package contains procedures for the Concurrent Program EFC Calculation
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 ||
13 || masehgal,sgaddama,gmuralid,cdcruz 08-03-2003 BUG# 2833795 - EFC Mismatch Base BUG
14 ||
15 || CDCRUZ 16-DEC-2002 Bug 2691811
16 || The EFC rounding off which was defaulted is now removed
17 || Will be re-introduced once EFC Computation is brought back
18 || Handling of cursor month_calc_cur modified in get_efc_no_of_months
19 || CDCRUZ 18-OCT-2002 Bug 2613546
20 || Cursor calander_cur modifed by Bug# 2613546 FA105/FA108
21 || CDCRUZ 04-MAY-2002 Bug 2339982 - Change number precision
22 || to 3rd Decimal place
23 || nsidana 11/20/2003 FA129 EFC updates for 2004-2005.
24 */
25
26 PROCEDURE flush_values(p_isir_rec in out NOCOPY igf_ap_isir_matched%rowtype) IS
27 BEGIN
28
29 IF ( p_isir_rec.primary_efc_type in ('4','5','6') and p_isir_rec.SCA IS NULL) OR
30 ( p_isir_rec.primary_efc_type in ('1','2','3'))
31 THEN
32 p_isir_rec.secti := NULL;
33 p_isir_rec.secati := NULL;
34 p_isir_rec.secstx := NULL;
35 p_isir_rec.secea := NULL;
36 p_isir_rec.secipa := NULL;
37 p_isir_rec.secai := NULL;
38 p_isir_rec.seccai := NULL;
39 p_isir_rec.secdnw := NULL;
40 p_isir_rec.secnw := NULL;
41 p_isir_rec.secapa := NULL;
42 p_isir_rec.secpca := NULL;
43 p_isir_rec.secaai := NULL;
44 p_isir_rec.sectsc := NULL;
45 p_isir_rec.sectpc := NULL;
46 p_isir_rec.secpc := NULL;
47 p_isir_rec.secsti := NULL;
48 p_isir_rec.secsati := NULL;
49 p_isir_rec.secsic := NULL;
50 p_isir_rec.secsdnw := NULL;
51 p_isir_rec.secsca := NULL;
52 p_isir_rec.secfti := NULL;
53 p_isir_rec.sec_efc_type := NULL ;
54 -- manu added secondary_efc
55 p_isir_rec.sec_efc_type := NULL ;
56 END IF ;
57 -- code added now today morn - sunday by gautam
58
59 IF ( p_isir_rec.primary_efc_type in ('5','6') AND
60 p_isir_rec.simplified_need_test = 'Y' AND
61 p_isir_rec.auto_zero_efc IS NULL AND
62 p_isir_rec.s_investment_networth IS NULL AND
63 p_isir_rec.s_busi_farm_networth IS NULL AND
64 p_isir_rec.s_cash_savings IS NULL ) THEN
65
66 p_isir_rec.secti := NULL;
67 p_isir_rec.secati := NULL;
68 p_isir_rec.secstx := NULL;
69 p_isir_rec.secea := NULL;
70 p_isir_rec.secipa := NULL;
71 p_isir_rec.secai := NULL;
72 p_isir_rec.seccai := NULL;
73 p_isir_rec.secdnw := NULL;
74 p_isir_rec.secnw := NULL;
75 p_isir_rec.secapa := NULL;
76 p_isir_rec.secpca := NULL;
77 p_isir_rec.secaai := NULL;
78 p_isir_rec.sectsc := NULL;
79 p_isir_rec.sectpc := NULL;
80 p_isir_rec.secpc := NULL;
81 p_isir_rec.secsti := NULL;
82 p_isir_rec.secsati := NULL;
83 p_isir_rec.secsic := NULL;
84 p_isir_rec.secsdnw := NULL;
85 p_isir_rec.secsca := NULL;
86 p_isir_rec.secfti := NULL;
87 p_isir_rec.sec_efc_type := NULL ;
88 p_isir_rec.sec_alternate_month_1 := NULL;
89 p_isir_rec.sec_alternate_month_2 := NULL;
90 p_isir_rec.sec_alternate_month_3 := NULL;
91 p_isir_rec.sec_alternate_month_4 := NULL;
92 p_isir_rec.sec_alternate_month_5 := NULL;
93 p_isir_rec.sec_alternate_month_6 := NULL;
94 p_isir_rec.sec_alternate_month_7 := NULL;
95 p_isir_rec.sec_alternate_month_8 := NULL;
96 p_isir_rec.sec_alternate_month_10 := NULL;
97 p_isir_rec.sec_alternate_month_11 := NULL;
98 p_isir_rec.sec_alternate_month_12 := NULL;
99 p_isir_rec.secondary_efc := NULL;
100 END IF;
101 -- till here
102 -- GAUTAM ADDED CODE FROM HERE
103 IF ( p_isir_rec.primary_efc_type in ('4','5','6') ) THEN
104
105 p_isir_rec.efc_networth := NULL;
106 p_isir_rec.discretionary_networth := NULL;
107 p_isir_rec.asset_protect_allow := NULL;
108 p_isir_rec.sca := NULL;
109 p_isir_rec.parents_cont_from_assets := NULL;
110 p_isir_rec.sdnw := NULL;
111 END IF;
112
113 IF ( p_isir_rec.primary_efc_type in ('1')) THEN
114 -- p_isir_rec.adjusted_available_income := NULL;
115 NULL;
116 ELSIF (p_isir_rec.primary_efc_type in ('4')) THEN
117 p_isir_rec.adjusted_available_income := NULL;
118 ELSIF (p_isir_rec.primary_efc_type in ('6')) THEN
119 p_isir_rec.adjusted_available_income := NULL;
120 END IF;
121
122 --p_isir_rec.auto_zero_efc := NULL ;
123
124 END flush_values;
125
126
127 FUNCTION get_month_efc ( p_month IN NUMBER ) RETURN NUMBER IS
128 m_pcont NUMBER;
129 m_dep_stud_inc NUMBER;
130 p_month_efc NUMBER;
131
132 BEGIN
133
134 m_pcont := igf_ap_efc_calc.isir_rec.PARENTS_CONTRIBUTION/9 ;
135 m_dep_stud_inc := igf_ap_efc_calc.isir_rec.SIC/9 ;
136 p_month_efc := ROUND(m_pcont) + ROUND(m_dep_stud_inc) ;
137 p_month_efc := p_month_efc * p_month ;
138 -- l_m_efc := l_m_efc + isir_rec.SCA;
139
140 RETURN p_month_efc;
141
142 END;
143
144 -- Starts function
145 FUNCTION get_efc_no_of_months ( p_last_end_dt IN DATE,
146 p_base_id IN igf_ap_fa_base_rec.base_id%TYPE)
147 RETURN NUMBER IS
148 /*
149 || Created By : [email protected]
150 || Created On : 11-DEC-2001
151 || Purpose : Bug No - 2142666 EFC DLD.
152 || This procedure finds the exact number of months not repeating the overlapped terms
153 || and neglecting the gap between terms.
154 || Known limitations, enhancements or remarks :
155 || Change History :
156 || Who When What
157 || masehgal 11-Feb-2003 # 2758804 FACR105 - EFC Engine
158 || Reintroduced the round off factors,
159 || month days calculations as per the build
160 || rasahoo 31-July-2003 #3024112 Changed the method of calculation for no. of days.
161 || Now it finds the difference between min of start date and max of
162 || end date to get tha no. of days. To revert back the original
163 || method of calculation pliz refer the file version 115.20
164 || (reverse chronological order - newest change first)
165 */
166
167 l_no_of_days NUMBER := 0;
168 l_no_of_months NUMBER := 0;
169
170 -- Cursor to find the User preference of Round-Off Factor, Number of days Divisior
171 -- for calculating the Number of Months.
172 CURSOR month_calc_cur IS
173 SELECT num_days_divisor, roundoff_fact
174 FROM igf_ap_efc_v efc,
175 igf_ap_fa_base_rec fabase
176 WHERE efc.ci_cal_type = fabase.ci_cal_type
177 AND efc.ci_sequence_number = fabase.ci_sequence_number
178 AND fabase.base_id = p_base_id;
179
180 -- Cursor to find all the Term/Load Calander the Student is Registered.
181 -- Cursor calander_cur modifed by Bug# 2613546 FA105/FA108
182 CURSOR calander_cur IS
183 SELECT MIN(ci.start_dt) start_dt, MAX(ci.end_dt) end_dt
184 FROM igf_aw_coa_itm_terms citsn ,
185 igs_ca_inst ci
186 WHERE ci.cal_type = citsn.ld_cal_type
187 AND ci.sequence_number = citsn.ld_sequence_number
188 AND citsn.base_id = p_base_id ;
189
190 calander_rec calander_cur%ROWTYPE;
191 month_calc_rec month_calc_cur%ROWTYPE;
192
193 BEGIN
194
195 OPEN calander_cur;
196 FETCH calander_cur INTO calander_rec;
197
198 -- If no Data Found return the default value -1
199 IF calander_cur%NOTFOUND THEN
200 CLOSE calander_cur;
201 RETURN -1;
202 ELSE
203 CLOSE calander_cur;
204 END IF;
205
206 -- The logic is to find the no of days the student is registered
207 IF p_last_end_dt IS NULL THEN
208 l_no_of_days := calander_rec.end_dt - calander_rec.start_dt;
209 ELSE
210 l_no_of_days := p_last_end_dt - calander_rec.start_dt;
211 END IF;
212
213 OPEN month_calc_cur;
214 FETCH month_calc_cur INTO month_calc_rec;
215 CLOSE month_calc_cur;
216
217 l_no_of_months := l_no_of_days / NVL(month_calc_rec.num_days_divisor,30);
218
219 IF (month_calc_rec.roundoff_fact = 'RU') THEN
220 -- Round UP to the nearest whole number
221 l_no_of_months := CEIL( l_no_of_months );
222 ELSIF (month_calc_rec.roundoff_fact = 'RD' ) THEN
223 -- Round DOWN to the nearest whole number
224 l_no_of_months := FLOOR( l_no_of_months );
225 ELSE
226 -- Round off factor is 'RH', Round to the nearest whole number
227 l_no_of_months := ROUND( l_no_of_months );
228 END IF;
229
230 RETURN l_no_of_months;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
235 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.GET_EFC_NO_OF_MONTHS');
236 IGS_GE_MSG_STACK.ADD;
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 END get_efc_no_of_months;
239
240 FUNCTION chk_reject (p_number IN VARCHAR2 ) RETURN BOOLEAN IS
241 /*
242 || Created By : masehgal
243 || Created On : 11-Feb-2003
244 || Purpose : check if Rejects can /can't be suppressed
245 || Known limitations, enhancements or remarks :
246 || Change History :
247 || Who When What
248 || (reverse chronological order - newest change first)
249 */
250 BEGIN
251
252 IF TO_NUMBER (p_number) >0 THEN
253 RETURN TRUE ;
254 ELSE
255 RETURN FALSE ;
256 END IF ;
257 EXCEPTION
258 WHEN VALUE_ERROR THEN
259 RETURN FALSE ;
260 END chk_reject ;
261
262 PROCEDURE get_efc_frml ( p_isir_rec IN OUT NOCOPY igf_ap_isir_matched%ROWTYPE ,
263 p_formula OUT NOCOPY VARCHAR2 ) AS
264 /*
265 || Created By : masehgal
266 || Created On : 11-Feb-2003
267 || Purpose : EFC Formula Determination
268 || Known limitations, enhancements or remarks :
269 || Change History :
270 || Who When What
271 || rajagupt 30-May-2006 Bug #5201271. Dependent student tax return
272 || requirements deleted from edits 3001-3006
273 || for year 2006-2007.
274 || (reverse chronological order - newest change first)
275 */
276
277 l_dep_status VARCHAR2(30) ;
278 l_alternative VARCHAR2(30) ;
279 l_dep VARCHAR2(30) ;
280
281 -- Cursor for Setup information
282 CURSOR alt_fact_cur (cp_depend_stat igf_fc_efc_alt_fac.dependent_status%TYPE,
283 cp_alternative igf_fc_efc_alt_fac.alternative_code%TYPE) IS
284 SELECT alt.*
285 FROM igf_fc_efc_alt_fac alt
286 WHERE alt.s_award_year = p_sys_award_year
287 AND alt.dependent_status = cp_depend_stat
288 AND alt.alternative_code = cp_alternative ;
289
290 l_alt_fact_rec alt_fact_cur%ROWTYPE ;
291 l_simpified BOOLEAN := FALSE ;
292
293 BEGIN -- get_efc_frml
294
295 p_formula := NULL ;
296 l_dep_status := NULL ;
297 p_isir_rec.auto_zero_efc := NULL ;
298
299 IF p_isir_rec.dependency_status = 'I' THEN
300 l_dep_status := 'INDEPENDENT' ;
301 ELSE
302 l_dep_status := 'DEPENDENT' ;
303 END IF ;
304
305 IF p_isir_rec.dependency_status = 'I' THEN
306 --Edit 5004 / 5005 / 5010 / 5011
307 IF ( ( NVL(p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members) > 2
308 AND NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) = '2' )
309 OR ( NVL(p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members) > 1
310 AND NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) IN ('1' ,'3') )
311 )
312 THEN
313 l_dep_status := 'INDEPENDENT_D' ;
314 END IF;
315 END IF;
316
317 -- SNT DEPENDENT
318 IF l_dep_status = 'DEPENDENT' THEN
319
320 -- Pre simplifiedd Check
321 IF ( NVL (p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) IS NULL
322 AND NVL (p_isir_rec.a_f_work_income , p_isir_rec.f_income_work ) IS NULL
323 AND NVL (p_isir_rec.a_m_work_income , p_isir_rec.m_income_work ) IS NULL
324 AND p_isir_rec.p_income_wsa IS NULL
325 AND p_isir_rec.p_income_wsb IS NULL
326 )
327 THEN
328 p_isir_rec.simplified_need_test := 'N' ;
329 ELSE
330
331 l_dep := 'D' ;
332 l_alternative := 'SIMPLIFIED' ;
333 OPEN alt_fact_cur (l_dep, l_alternative);
337 --Simplified Need Test for Dependent Model for 0607
334 FETCH alt_fact_cur INTO l_alt_fact_rec ;
335 CLOSE alt_fact_cur ;
336
338 IF ( igf_ap_efc_calc.p_sys_award_year IN ('0607') ) THEN
339
340 --Bug #5201271
341 IF (
342 -- Edit 3001
343 ( p_isir_rec.p_type_tax_return IN ('2','4') AND
344 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
345 NVL ( p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
346 ) OR
347
348 -- EDIT 3002
349 ( p_isir_rec.p_tax_return_status = '3' AND
350 p_isir_rec.p_type_tax_return IS NULL AND
351 ( NVL( NVL( p_isir_rec.a_f_work_income, p_isir_rec.f_income_work), 0) +
352 NVL( NVL( p_isir_rec.a_m_work_income, p_isir_rec.m_income_work), 0)
353 ) < l_alt_fact_rec.alternative_income
354 ) OR
355
356 -- Edit 3003
357 ( p_isir_rec.p_elig_1040aez = '1' AND
358 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
359 NVL( p_isir_rec.a_parents_agi, p_isir_rec.p_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
360 )
361 )
362 THEN -- Set simplified flag to 'Y'
363
364 p_isir_rec.simplified_need_test := 'Y' ;
365 END IF ;
366
367 --Simplified Need Test for Dependent Model for 0506
368 ELSIF ( igf_ap_efc_calc.p_sys_award_year IN ('0506') ) THEN
369
370 -- for 0506, assumed value are not used, always use the reported values.
371 IF (
372 -- Edit 3001
373 ( p_isir_rec.p_type_tax_return IN ('2','4') AND
374 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
375 p_isir_rec.s_elig_1040ez = '1' OR
376 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
377 ) AND
378 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
379 NVL ( p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
380 ) OR
381
382 -- EDIT 3002
383 ( p_isir_rec.p_tax_return_status = '3' AND
384 p_isir_rec.p_type_tax_return IS NULL AND
385 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
386 p_isir_rec.s_elig_1040ez = '1' OR
387 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
388 ) AND
389 ( NVL( NVL( p_isir_rec.a_f_work_income, p_isir_rec.f_income_work), 0) +
390 NVL( NVL( p_isir_rec.a_m_work_income, p_isir_rec.m_income_work), 0)
391 ) < l_alt_fact_rec.alternative_income
392 ) OR
393
394 -- Edit 3003
395 ( p_isir_rec.p_elig_1040aez = '1' AND
396 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
397 p_isir_rec.s_elig_1040ez = '1' OR
398 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL)
399 ) AND
400 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
401 NVL( p_isir_rec.a_parents_agi, p_isir_rec.p_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
402 )
403 )
404 THEN -- Set simplified flag to 'Y'
405
406 p_isir_rec.simplified_need_test := 'Y' ;
407 END IF ;
408
409 ELSE
410
411 -- for years less than 0506, assumed value are used, if assumed values are not present then reported values are used.
412 IF (
413 -- Edit 3001
414 ( p_isir_rec.p_type_tax_return IN ('2','4') AND
415 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
416 p_isir_rec.s_elig_1040ez = '1'OR
417 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
418 ) AND
419 NVL ( p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
420 ) OR
421
422 -- EDIT 3002
423 ( p_isir_rec.p_tax_return_status = '3' AND
424 p_isir_rec.p_type_tax_return IS NULL AND
425 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
426 p_isir_rec.s_elig_1040ez = '1' OR
427 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
428 ) AND
429 ( NVL( NVL( p_isir_rec.a_f_work_income, p_isir_rec.f_income_work), 0) +
430 NVL( NVL( p_isir_rec.a_m_work_income, p_isir_rec.m_income_work), 0)
431 ) < l_alt_fact_rec.alternative_income
432 ) OR
433
434 -- Edit 3003
435 ( p_isir_rec.p_elig_1040aez = '1' AND
436 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
437 p_isir_rec.s_elig_1040ez = '1' OR
438 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL)
439 ) AND
440 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
444 THEN -- Set simplified flag to 'Y'
441 NVL( p_isir_rec.a_parents_agi, p_isir_rec.p_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
442 )
443 )
445
446 p_isir_rec.simplified_need_test := 'Y' ;
447 END IF ;
448
449 END IF; -- End of 0607
450
451 END IF ; -- Pre simplified Check
452
453 -- ZERO EFC DEPENDENT
454 l_dep := 'D' ;
455 l_alternative := 'AUTO_ZERO' ;
456 OPEN alt_fact_cur ( l_dep, l_alternative ) ;
457 FETCH alt_fact_cur INTO l_alt_fact_rec ;
458 CLOSE alt_fact_cur ;
459
460 IF ( igf_ap_efc_calc.p_sys_award_year IN ('0607') ) THEN
461
462 -- Bug #5201271
463 IF(
464
465 -- Edit 3004
466 ( p_isir_rec.p_type_tax_return in ('2','4') AND
467 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
468 NVL (p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
469 ) OR
470
471 -- Edit 3005
472 ( p_isir_rec.p_tax_return_status = '3' AND
473 p_isir_rec.p_type_tax_return IS NULL AND
474 ( NVL( NVL( p_isir_rec.a_f_work_income, p_isir_rec.f_income_work), 0) + NVL( NVL( p_isir_rec.a_m_work_income, p_isir_rec.m_income_work), 0))
475 <= l_alt_fact_rec.alternative_income
476 ) OR
477
478
479 -- Edit 3006
480 ( p_isir_rec.p_elig_1040aez = '1' AND
481 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
482 NVL( p_isir_rec.a_parents_agi, p_isir_rec.p_adjusted_gross_income) <= l_alt_fact_rec.alternative_income )
483 )
484 THEN
485 -- Set Auto Zero EFC to 'Y'
486 p_isir_rec.auto_zero_efc := 'Y' ;
487 END IF;
488
489 ELSIF ( igf_ap_efc_calc.p_sys_award_year IN ('0506') ) THEN
490
491 -- for 0506, assumed value are not used, always use the reported values.
492 IF(
493
494 -- Edit 3004
495 ( p_isir_rec.p_type_tax_return in ('2','4') AND
496 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
497 p_isir_rec.s_elig_1040ez = '1' OR
498 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
499 ) AND
500 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
501 NVL (p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
502 ) OR
503
504 -- Edit 3005
505 ( p_isir_rec.p_tax_return_status = '3' AND
506 p_isir_rec.p_type_tax_return IS NULL AND
507 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
508 p_isir_rec.s_elig_1040ez = '1' OR
509 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
510 ) AND
511 ( NVL( NVL( p_isir_rec.a_f_work_income, p_isir_rec.f_income_work), 0) + NVL( NVL( p_isir_rec.a_m_work_income, p_isir_rec.m_income_work), 0))
512 <= l_alt_fact_rec.alternative_income
513 ) OR
514
515
516 -- Edit 3006
517 ( p_isir_rec.p_elig_1040aez = '1' AND
518 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
519 p_isir_rec.s_elig_1040ez = '1' OR
520 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL)
521 ) AND
522 p_isir_rec.p_adjusted_gross_income IS NOT NULL AND
523 NVL( p_isir_rec.a_parents_agi, p_isir_rec.p_adjusted_gross_income) <= l_alt_fact_rec.alternative_income )
524 )
525 THEN
526 -- Set Auto Zero EFC to 'Y'
527 p_isir_rec.auto_zero_efc := 'Y' ;
528 END IF;
529
530 ELSE
531
532 -- for years less than 0506, assumed value are used, if assumed values are not present then reported values are used.
533 IF (
534 -- Edit 3004
535 ( p_isir_rec.p_type_tax_return in ('2','4') AND
536 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
537 p_isir_rec.s_elig_1040ez = '1' OR
538 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
539 ) AND
540 NVL (p_isir_rec.a_parents_agi , p_isir_rec.p_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
541 ) OR
542
543 -- Edit 3005
544 ( p_isir_rec.p_tax_return_status = '3' AND
545 p_isir_rec.p_type_tax_return IS NULL AND
546 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
547 p_isir_rec.s_elig_1040ez = '1' OR
548 ( p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL )
549 ) AND
550 ( NVL( NVL( p_isir_rec.a_f_work_income, p_isir_rec.f_income_work), 0) + NVL( NVL(p_isir_rec.a_m_work_income, p_isir_rec.m_income_work), 0) )
551 <= l_alt_fact_rec.alternative_income
552 ) OR
553
554 -- Edit 3006
555 ( p_isir_rec.p_adjusted_gross_income is not null AND
556 p_isir_rec.p_elig_1040aez = '1' AND
557 ( p_isir_rec.s_type_tax_return IN ('2','4') OR
558 p_isir_rec.s_elig_1040ez = '1' OR
559 (p_isir_rec.s_tax_return_status = '3' AND p_isir_rec.s_type_tax_return is NULL)
560 ) AND
564 THEN
561 NVL( p_isir_rec.a_parents_agi, p_isir_rec.p_adjusted_gross_income) <= l_alt_fact_rec.alternative_income
562 )
563 )
565 -- Set Auto Zero EFC to 'Y'
566 p_isir_rec.auto_zero_efc := 'Y' ;
567 END IF ;
568
569 END IF ; -- dependent check
570 -- ZERO EFC for DEPENDENT ends
571
572 END IF;
573 -- SNT DEPENDENT Ends
574
575
576
577 -- SNT INDEPENDENT
578 -- manu - checking for both independent single and independent with dependents
579 IF l_dep_status IN ('INDEPENDENT','INDEPENDENT_D') THEN
580
581 -- Pre simplified Check
582 IF ( NVL (p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) is NULL
583 AND NVL (p_isir_rec.a_s_income_work , p_isir_rec.s_income_from_work ) is NULL
584 AND NVL (p_isir_rec.a_spouse_income_work , p_isir_rec.spouse_income_from_work ) is NULL
585 AND p_isir_rec.s_toa_amt_from_wsa is NULL
586 AND p_isir_rec.s_toa_amt_from_wsb is NULL
587 )
588 THEN
589 p_isir_rec.simplified_need_test := 'N' ;
590
591 ELSE
592 l_dep := 'I' ;
593 l_alternative := 'SIMPLIFIED' ;
594 OPEN alt_fact_cur ( l_dep, l_alternative ) ;
595 FETCH alt_fact_cur INTO l_alt_fact_rec ;
596 CLOSE alt_fact_cur ;
597
598 -- start of 0506
599 IF ( igf_ap_efc_calc.p_sys_award_year IN ('0506','0607') ) THEN
600
601 -- for 0506, assumed value are not used, always use the reported values.
602 IF (
603
604 -- Edit 3007
605 ( p_isir_rec.p_elig_1040aez IN ('2','4') AND
606 p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
607 NVL( p_isir_rec.a_student_agi, p_isir_rec.s_adjusted_gross_income) < l_alt_fact_rec.alternative_income
608 ) OR
609
610 -- EDIT 3008
611 ( p_isir_rec.s_tax_return_status = '3' AND
612 p_isir_rec.s_type_tax_return IS NULL AND
613 ( NVL( p_isir_rec.a_s_income_work, p_isir_rec.s_income_from_work) + NVL( p_isir_rec.a_spouse_income_work, p_isir_rec.spouse_income_from_work) )
614 < l_alt_fact_rec.alternative_income
615 ) OR
616
617 -- Edit 3009
618
619 ( p_isir_rec.s_elig_1040ez = '1' AND
620 p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
621 NVL (p_isir_rec.a_student_agi, p_isir_rec.s_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
622 )
623
624 )
625 THEN
626 -- Set simplified flag to 'Y'
627 p_isir_rec.simplified_need_test := 'Y' ;
628 END IF ;
629
630 ELSE
631 -- for years less than 0506, assumed value are used, if assumed values are not present then reported values are used.
632 IF (
633 -- Edit 3007
634 ( p_isir_rec.p_elig_1040aez IN ('2','4') AND
635 NVL( p_isir_rec.a_student_agi, p_isir_rec.s_adjusted_gross_income) < l_alt_fact_rec.alternative_income
636 ) OR
637
638 -- EDIT 3008
639 ( p_isir_rec.s_tax_return_status = '3' AND
640 p_isir_rec.s_type_tax_return IS NULL AND
641 ( NVL( p_isir_rec.a_s_income_work, p_isir_rec.s_income_from_work) + NVL( p_isir_rec.a_spouse_income_work, p_isir_rec.spouse_income_from_work) )
642 < l_alt_fact_rec.alternative_income
643 ) OR
644
645 -- Edit 3009
646 ( p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
647 p_isir_rec.s_elig_1040ez = '1' AND
648 NVL (p_isir_rec.a_student_agi, p_isir_rec.s_adjusted_gross_income ) < l_alt_fact_rec.alternative_income
649 )
650 )
651 THEN
652 -- Set simplified flag to 'Y'
653 p_isir_rec.simplified_need_test := 'Y' ;
654 END IF ;
655
656 END IF; -- End of 0506
657
658 END IF ; -- Pre simplified Check
659 -- SNT for INDEPENDENT Ends
660
661 -- ZERO EFC INDEPENDENT
662 l_dep := 'I' ;
663 l_alternative := 'AUTO_ZERO' ;
664 OPEN alt_fact_cur ( l_dep, l_alternative ) ;
665 FETCH alt_fact_cur INTO l_alt_fact_rec ;
666 CLOSE alt_fact_cur ;
667
668 -- start of 0506
669 IF ( igf_ap_efc_calc.p_sys_award_year IN ('0506','0607') ) THEN
670
671 IF(
672 -- Edit 3010
673 ( NVL( p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) = '2' AND
674 NVL( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 2 AND
675 p_isir_rec.s_type_tax_return IN ('2','4') AND
676 p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
677 NVL ( p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
678 ) OR
679
680 -- Edit 3011
681 ( NVL( p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) = '2' AND
682 NVL ( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 2 AND
686 <= l_alt_fact_rec.alternative_income
683 p_isir_rec.s_tax_return_status = '3' AND
684 p_isir_rec.s_type_tax_return IS NULL AND
685 ( NVL( NVL(p_isir_rec.a_s_income_work, p_isir_rec.s_income_from_work), 0) + NVL( NVL( p_isir_rec.a_spouse_income_work, p_isir_rec.spouse_income_from_work), 0) )
687 ) OR
688
689 -- Edit 3012
690 (NVL( p_isir_rec.a_student_marital_status, p_isir_rec.s_marital_status ) = '2' AND
691 NVL( p_isir_rec.a_s_num_in_family, p_isir_rec.s_num_family_members ) > 2 AND
692 p_isir_rec.s_elig_1040ez = '1' AND
693 p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
694 NVL ( p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
695 ) OR
696
697 -- Edit 3013
698 ( NVL(p_isir_rec.a_student_marital_status, p_isir_rec.s_marital_status ) IN ('1','3') AND
699 NVL(p_isir_rec.a_s_num_in_family, p_isir_rec.s_num_family_members ) > 1 AND
700 p_isir_rec.s_type_tax_return IN ('2','4') AND
701 p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
702 NVL (p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
703 ) OR
704
705 -- Edit 3014
706 ( NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) IN ('1','3') AND
707 NVL(p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 1 AND
708 p_isir_rec.s_tax_return_status = '3' AND
709 p_isir_rec.s_type_tax_return is NULL AND
710 (NVL (NVL( p_isir_rec.a_s_income_work, p_isir_rec.s_income_from_work), 0) + NVL( NVL( p_isir_rec.a_spouse_income_work, p_isir_rec.spouse_income_from_work), 0) )
711 <= l_alt_fact_rec.alternative_income
712 ) OR
713
714 -- Edit 3015
715 ( NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) IN ('1','3') AND
716 NVL ( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 1 AND
717 p_isir_rec.s_elig_1040ez = '1' AND
718 p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
719 NVL ( p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
720 )
721 )
722 THEN
723 -- Set Auto Zero EFC to 'Y'
724 p_isir_rec.auto_zero_efc := 'Y' ;
725 END IF ;
726
727 ELSE
728
729 -- for years less than 0506, assumed value are used, if assumed values are not present then reported values are used.
730 IF(
731 -- Edit 3010
732 ( NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) = '2' AND
733 NVL ( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 2 AND
734 p_isir_rec.s_type_tax_return IN ('2','4') AND
735 NVL ( p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
736 ) OR
737
738 -- Edit 3011
739 ( NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) = '2' AND
740 NVL ( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 2 AND
741 p_isir_rec.s_tax_return_status = '3' AND
742 p_isir_rec.s_type_tax_return IS NULL AND
743 (NVL (NVL (p_isir_rec.a_s_income_work, p_isir_rec.s_income_from_work), 0 ) + NVL( NVL( p_isir_rec.a_spouse_income_work, p_isir_rec.spouse_income_from_work) ,0) )
744 <= l_alt_fact_rec.alternative_income
745 ) OR
746
747 -- Edit 3012
748 ( p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
749 NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) = '2' AND
750 NVL ( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 2 AND
751 p_isir_rec.s_elig_1040ez = '1' AND
752 NVL ( p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
753 ) OR
754
755 -- Edit 3013
756 ( NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) IN ('1','3') AND
757 NVL (p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 1 AND
758 p_isir_rec.s_type_tax_return IN ('2','4') AND
759 NVL (p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
760 ) OR
761
762 -- Edit 3014
763 ( NVL(p_isir_rec.a_student_marital_status, p_isir_rec.s_marital_status ) IN ('1','3') AND
764 NVL(p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 1 AND
765 p_isir_rec.s_tax_return_status = '3' AND
766 p_isir_rec.s_type_tax_return is NULL AND
767 ( NVL (NVL (p_isir_rec.a_s_income_work, p_isir_rec.s_income_from_work) , 0 ) + NVL( NVL( p_isir_rec.a_spouse_income_work, p_isir_rec.spouse_income_from_work), 0) )
768 <= l_alt_fact_rec.alternative_income
769 ) OR
770
771 -- Edit 3015
772 ( p_isir_rec.s_adjusted_gross_income IS NOT NULL AND
773 NVL(p_isir_rec.a_student_marital_status , p_isir_rec.s_marital_status ) IN ('1','3') AND
774 NVL ( p_isir_rec.a_s_num_in_family , p_isir_rec.s_num_family_members ) > 1 AND
778 )
775 p_isir_rec.s_elig_1040ez = '1' AND
776 NVL ( p_isir_rec.a_student_agi , p_isir_rec.s_adjusted_gross_income ) <= l_alt_fact_rec.alternative_income
777 )
779 THEN
780 -- Set Auto Zero EFC to 'Y'
781 p_isir_rec.auto_zero_efc := 'Y' ;
782 END IF ;
783
784 END IF;
785
786 END IF ; -- independent check
787 -- ZERO EFC for INDEPENDENT ends
788
789 -- Set the formula codes now :
790 IF p_isir_rec.auto_zero_efc = 'Y' THEN
791 -- Set the value of P_FORMULA to 0
792 -- 0 -> Zero EFC
793 p_formula := '0' ;
794
795 ELSIF l_dep_status = 'DEPENDENT' AND p_isir_rec.simplified_need_test <> 'Y' THEN
796 --Set the value of P_FORMULA to 1
797 --1 -> Dependent Regular (A Regular)
798 p_formula := '1' ;
799
800 ELSIF l_dep_status = 'DEPENDENT' AND p_isir_rec.simplified_need_test = 'Y' THEN
801 --Set the value of P_FORMULA to 4
802 -- 4 -> Dependent Simplified (A Simplified)
803 p_formula := '4' ;
804
805 ELSIF l_dep_status = 'INDEPENDENT' AND p_isir_rec.simplified_need_test <> 'Y' THEN
806 --Set the value of P_FORMULA to 2
807 p_formula := '2' ; -- ( B Regular )
808
809 ELSIF l_dep_status = 'INDEPENDENT' AND p_isir_rec.simplified_need_test = 'Y' THEN
810 -- Set the value of P_FORMULA to 5
811 -- 5 -> Independent Without Dependents Simplified (B Simplified)
812 p_formula := '5' ;
813
814 ELSIF l_dep_status = 'INDEPENDENT_D' AND p_isir_rec.simplified_need_test <> 'Y' THEN
815 -- Set the value of P_FORMULA to 3
816 -- 3 -> Independent With Dependents Regular (C Regular)
817 p_formula := '3' ;
818
819 ELSIF l_dep_status = 'INDEPENDENT_D' AND p_isir_rec.simplified_need_test = 'Y' THEN
820 -- Set the value of P_FORMULA to 6
821 -- 6 -> Independent With Dependents Simplified (C Simplified)
822 p_formula := '6' ;
823
824 END IF ;
825
826 p_isir_rec.primary_efc_type := p_formula ;
827
828 IF p_formula IN ( '4','5','6') THEN
829 p_isir_rec.sec_efc_type := TO_CHAR( TO_NUMBER(p_formula) -3 ) ;
830 ELSE
831 p_isir_rec.sec_efc_type := NULL ;
832 END IF ;
833
834 IF p_formula = '0' THEN
835 -- p_isir_rec.auto_zero_efc := 'Y' ;
836
837 IF l_dep_status = 'DEPENDENT' THEN
838 p_isir_rec.primary_efc_type := '4' ;
839 p_isir_rec.sec_efc_type := '1' ;
840
841 ELSIF l_dep_status = 'INDEPENDENT' THEN
842 p_isir_rec.primary_efc_type := '5' ;
843 p_isir_rec.sec_efc_type := '2' ;
844
845 ELSIF l_dep_status = 'INDEPENDENT_D' THEN
846 p_isir_rec.primary_efc_type := '6' ;
847 p_isir_rec.sec_efc_type := '3' ;
848
849 END IF ;
850 END IF ;
851
852 EXCEPTION
853 WHEN OTHERS THEN
854 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
855 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.GET_EFC_FRML');
856 IGS_GE_MSG_STACK.ADD;
857 APP_EXCEPTION.RAISE_EXCEPTION;
858
859 END get_efc_frml ;
860
861
862 PROCEDURE efc_a ( p_frml_code IN VARCHAR2,
863 p_no_of_months IN NUMBER,
864 p_efc OUT NOCOPY NUMBER,
865 p_sc_asset OUT NOCOPY NUMBER ) AS
866 /*
867 || Created By : masehgal
868 || Created On : 11-feb-2003
869 || Purpose : This is the procedure for calculating EFC for the Student with EFC formula A
870 || Known limitations, enhancements or remarks :
871 || Change History :
872 || Who When What
873 || (reverse chronological order - newest change first)
874 || cdcruz 24-May-2002 New parameter added p_sc_asset
875 || To compute students contribution from Assets which should not be
876 || Pro Rated - Bug# 2384837
877 */
878 l_p_inc NUMBER(12,3) ; -- Parents' Income
879 l_allow_ag_p_inc NUMBER(12,3) ; -- Allowances against Parents' Income
880 l_available_income NUMBER(12,3) ; -- Available Income
881 l_p_cont_assets NUMBER(12,3) ; -- Parents' contribution from Assets
882 l_p_aai NUMBER(12,3) ; -- Parents' Adjustable Available Income
883 l_p_cont NUMBER(12,3) ; -- Parents' Contribution from Income
884 l_s_inc NUMBER(12,3) ; -- Student's Income
885 l_allow_ag_s_inc NUMBER(12,3) ; -- Allowances against Student's Income
886 l_s_cont NUMBER(12,3) ; -- Student's Contribution from Income
887 l_s_cont_assets NUMBER(12,3) ; -- Student's contribution from Assets
888 l_p_cont_less_9 NUMBER(12,3) ; -- Parents' Contribution from Income for less than 9 months
889 l_s_cont_less_9 NUMBER(12,3) ; -- Student's Contribution from Income for less than 9 months
890 l_p_cont_more_9 NUMBER(12,3) ; -- Parents' Contribution from Income for more than 9 months
891
892 BEGIN
893 p_sc_asset := null ;
894
895 -- Get the Parents' Income in last year
896 igf_ap_efc_subf.a_p_inc(l_p_inc);
897
898 -- Get Allowances against Parents' Income
899 igf_ap_efc_subf.a_allow_ag_p_inc ( l_p_inc, l_allow_ag_p_inc ) ;
903
900
901 -- Get the Parents' Available Income
902 igf_ap_efc_subf.a_available_inc( l_p_inc, l_allow_ag_p_inc,l_available_income );
904 -- Get Parents' contribution from Assets
905 igf_ap_efc_subf.a_p_cont_assets( l_p_cont_assets );
906
907 -- Get Parents' Contribution
908 igf_ap_efc_subf.a_p_cont( l_available_income, l_p_cont_assets, l_p_aai, l_p_cont );
909
910 -- Get student's income in 2000
911 igf_ap_efc_subf.a_s_inc(l_s_inc);
912
913 -- Get Allowances against Student's income
914 igf_ap_efc_subf.a_allow_ag_s_inc( l_s_inc, l_p_aai, l_allow_ag_s_inc );
915
916 -- Get Student's contribution from income
917 igf_ap_efc_subf.a_s_cont( l_s_inc, l_allow_ag_s_inc, l_s_cont );
918
919 -- Get Student's contribution from Assets
920 igf_ap_efc_subf.a_s_cont_assets ( l_s_cont_assets );
921
922 p_sc_asset := l_s_cont_assets ;
923
924 -- Get the first 9 Months EFC
925 -- Get Parents' contribution for < 9 months
926 igf_ap_efc_subf.a_p_cont_less_9( l_p_cont, p_no_of_months, l_p_cont_less_9 );
927
928 -- Get Student's contribution from Available Income for < 9 months
929 igf_ap_efc_subf.a_s_cont_less_9 ( l_s_cont, p_no_of_months, l_s_cont_less_9 );
930
931 -- Get Student's EFC for < 9 months
932 igf_ap_efc_subf.a_efc_not_9(l_p_cont_less_9, l_s_cont_less_9, l_s_cont_assets, p_efc );
933
934 -- Due to a differential that is coming in the formula which requires
935 -- Independent calculation for 9th ,10th , 11th and 12th Month .
936 -- The following structure is added.
937 IF p_no_of_months > 8 THEN
938 -- Get Parents contribution for 9th month
939 igf_ap_efc_subf.a_efc( l_p_cont, l_s_cont, l_s_cont_assets, g_efc_a_9 );
940 END IF ;
941
942 IF p_no_of_months > 9 THEN
943 -- Get Parents contribution for 10th month
944 igf_ap_efc_subf.a_p_cont_more_9(l_p_aai, l_p_cont, 10, l_p_cont_more_9 );
945 -- Get Students EFC for > 9 months
946 igf_ap_efc_subf.a_efc_not_9(l_p_cont_more_9,l_s_cont, l_s_cont_assets, g_efc_a_10 );
947 END IF ;
948
949 IF p_no_of_months > 10 THEN
950 -- Get Parents contribution for 11th month
951 igf_ap_efc_subf.a_p_cont_more_9(l_p_aai, l_p_cont, 11, l_p_cont_more_9 );
952 -- Get Students EFC for 11th month
953 igf_ap_efc_subf.a_efc_not_9(l_p_cont_more_9,l_s_cont, l_s_cont_assets, g_efc_a_11 );
954 END IF ;
955
956
957 IF p_no_of_months > 11 THEN
958 -- Get Parents contribution for 11th month
959 igf_ap_efc_subf.a_p_cont_more_9(l_p_aai, l_p_cont, 12, l_p_cont_more_9 );
960 -- Get Students EFC for 12th month
961 igf_ap_efc_subf.a_efc_not_9(l_p_cont_more_9,l_s_cont, l_s_cont_assets, g_efc_a_12 );
962 END IF;
963
964 EXCEPTION
965 WHEN EXCEPTION_IN_SETUP THEN
966 APP_EXCEPTION.RAISE_EXCEPTION;
967 WHEN OTHERS THEN
968 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP'||SQLERRM);
969 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.EFC_A');
970 IGS_GE_MSG_STACK.ADD;
971 APP_EXCEPTION.RAISE_EXCEPTION;
972 END efc_a;
973
974
975 PROCEDURE efc_b ( p_frml_code IN VARCHAR2,
976 p_sec_efc_type IN VARCHAR2,
977 p_no_of_months IN NUMBER,
978 p_efc OUT NOCOPY NUMBER,
979 p_sec_efc OUT NOCOPY NUMBER ) AS
980 /*
981 || Created By : masehgal
982 || Created On : 11-feb-2003
983 || Purpose : This is the procedure for calculating EFC for the Student with EFC formula B
984 || Known limitations, enhancements or remarks :
985 || Change History :
986 || Who When What
987 || (reverse chronological order - newest change first)
988 */
989 l_s_inc NUMBER(12,3) ; -- Student/Spouse Income
990 l_allow_ag_s_inc NUMBER(12,3) ; -- Allowance against Student/Spouse income
991 l_s_cont NUMBER(12,3) ; -- Contribution from Available Income
992 l_s_cont_assets NUMBER(12,3) ; -- Student/Spouse contribution from Assets
993 l_sec_efc_s_cont_assets NUMBER(12,3) ; -- Student/Spouse contribution from Assets
994
995 BEGIN
996
997 -- Get Student/Spouse income in 2000
998 igf_ap_efc_subf.b_s_inc( l_s_inc );
999
1000 -- Get Allowance against Student/Spouse income
1001 igf_ap_efc_subf.b_allow_ag_s_inc( l_s_inc, l_allow_ag_s_inc );
1002
1003 -- Get contribution from Available Income
1004 igf_ap_efc_subf.b_s_cont( l_s_inc, l_allow_ag_s_inc, l_s_cont );
1005
1006 -- Get Student/Spouse contribution from Assets
1007 igf_ap_efc_subf.b_s_cont_assets ( l_s_cont_assets );
1008
1009 l_sec_efc_s_cont_assets := l_s_cont_assets;
1010
1011 IF ( p_frml_code <> '2' ) THEN
1012 l_s_cont_assets := 0;
1013 END IF;
1014
1015 -- Get Expected Family Contribution for 9 months
1016 igf_ap_efc_subf.b_efc ( l_s_cont, l_s_cont_assets, p_efc );
1017
1018 IF ( p_sec_efc_type = '2') THEN
1019 -- Get Expected Family Contribution for 9 months for secondary efc type 2.
1020 igf_ap_efc_subf.b_efc ( l_s_cont, l_sec_efc_s_cont_assets, p_sec_efc );
1021 END IF;
1022
1023 IF ( p_no_of_months >= 9 ) THEN
1024 NULL; -- In this case EFC for > 9 months is = 9 month EFC
1025 ELSE
1029 IF ( p_sec_efc_type = '2') THEN
1026 -- Get Expected Family Contribution for less than 9 months
1027 igf_ap_efc_subf.b_efc_less_9( p_no_of_months, p_efc );
1028
1030 igf_ap_efc_subf.b_efc_less_9( p_no_of_months, p_sec_efc );
1031 END IF;
1032 END IF;
1033
1034 EXCEPTION
1035 WHEN EXCEPTION_IN_SETUP THEN
1036 APP_EXCEPTION.RAISE_EXCEPTION;
1037 WHEN OTHERS THEN
1038 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1039 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.EFC_B');
1040 IGS_GE_MSG_STACK.ADD;
1041 APP_EXCEPTION.RAISE_EXCEPTION;
1042
1043 END efc_b;
1044
1045
1046 PROCEDURE efc_c ( p_frml_code IN VARCHAR2,
1047 p_sec_efc_type IN VARCHAR2,
1048 p_no_of_months IN NUMBER,
1049 p_efc OUT NOCOPY NUMBER,
1050 p_sec_efc OUT NOCOPY NUMBER
1051 ) AS
1052 /*
1053 || Created By : masehgal
1054 || Created On : 11-feb-2003
1055 || Purpose : This is the procedure for calculating EFC for the Student with EFC formula C.
1056 || Known limitations, enhancements or remarks :
1057 || Change History :
1058 || Who When What
1059 || (reverse chronological order - newest change first)
1060 */
1061 l_s_inc NUMBER(12,3) ; -- Student/Spouse Income
1062 l_allow_ag_s_inc NUMBER(12,3) ; -- Allowance against Student/Spouse Income
1063 l_available_income NUMBER(12,3) ; -- Available Income
1064 l_s_cont_assets NUMBER(12,3) ; -- Student/Spouse contribution from Assets
1065 l_s_cont NUMBER(12,3) ; -- Contribution from Available Income
1066
1067 BEGIN
1068 -- Get Student/Spouse Income in 2000.
1069 igf_ap_efc_subf.c_s_inc ( l_s_inc );
1070
1071 -- Get Allowances against Student/Spouse Income
1072 igf_ap_efc_subf.c_allow_ag_s_inc ( l_s_inc, l_allow_ag_s_inc );
1073
1074 -- Get Available Income
1075 igf_ap_efc_subf.c_available_inc( l_s_inc, l_allow_ag_s_inc, l_available_income );
1076
1077
1078 -- Get Available Income
1079 -- igf_ap_efc_subf.c_available_inc( l_s_inc, l_allow_ag_s_inc, l_s_cont );
1080
1081 -- Get Student/Spouse contribution from Assets
1082 igf_ap_efc_subf.c_s_cont_assets ( l_s_cont_assets );
1083
1084
1085 IF ( p_frml_code = '6') then
1086 igf_ap_efc_subf.c_efc ( l_available_income, 0, p_efc , 'P' );
1087 igf_ap_efc_subf.c_efc ( l_available_income, l_s_cont_assets, p_sec_efc, 'S' );
1088 ELSE
1089 igf_ap_efc_subf.c_efc ( l_available_income, l_s_cont_assets, p_efc, 'P' );
1090 END IF;
1091
1092 IF ( p_no_of_months >= 9 ) THEN
1093
1094 NULL; -- In this case EFC for > 9 months is = 9 month EFC
1095 ELSE
1096
1097 -- Get Expected Family Contribution for less than 9 months
1098 igf_ap_efc_subf.c_efc_less_9( p_no_of_months, p_efc );
1099
1100 IF ( p_sec_efc_type = '3') THEN
1101 igf_ap_efc_subf.c_efc_less_9( p_no_of_months, p_sec_efc );
1102 END IF;
1103
1104 END IF;
1105
1106 EXCEPTION
1107 WHEN EXCEPTION_IN_SETUP THEN
1108 APP_EXCEPTION.RAISE_EXCEPTION;
1109 WHEN OTHERS THEN
1110 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1111 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.EFC_C');
1112 IGS_GE_MSG_STACK.ADD;
1113 APP_EXCEPTION.RAISE_EXCEPTION;
1114 END efc_c;
1115
1116
1117 PROCEDURE calc_efc_main ( p_isir_rec IN OUT NOCOPY igf_ap_isir_matched%ROWTYPE ,
1118 l_sys_award_year IN VARCHAR2,
1119 p_formula IN VARCHAR2 ) AS
1120 /*
1121 || Created By : masehgal
1122 || Created On : 11-Feb-2003
1123 || Purpose : EFC Formula Determination
1124 || Known limitations, enhancements or remarks :
1125 || Change History :
1126 || Who When What
1127 || gmuralid 03-03-2003 BUG# 2826603 Removed width of Number type local variables.Also If EFC value
1128 exceeded 99999, wrapped it back to 99999
1129 || nsidana 11/20/2003 FA129 EFC updates for 2004-2005.
1130 || (reverse chronological order - newest change first)
1131 */
1132
1133 p_efc NUMBER := null ;
1134 p_sec_efc NUMBER := null ;
1135 l_rowid ROWID;
1136 l_category NUMBER;
1137 l_formula VARCHAR2(20); -- Regular/Simplified
1138 l_no_of_months NUMBER;
1139 l_no_fabase_months NUMBER;
1140 l_efc_per_month NUMBER;
1141 l_sec_efc_per_month NUMBER;
1142 l_efc_1 NUMBER;
1143 l_efc_2 NUMBER;
1144 l_efc_3 NUMBER;
1145 l_efc_4 NUMBER;
1146 l_efc_5 NUMBER;
1147 l_efc_6 NUMBER;
1148 l_efc_7 NUMBER;
1149 l_efc_8 NUMBER;
1150 l_efc_9 NUMBER;
1151 l_efc_10 NUMBER;
1152 l_efc_11 NUMBER;
1153 l_efc_12 NUMBER;
1154 l_efc_s_1 NUMBER;
1155 l_efc_s_2 NUMBER;
1156 l_efc_s_3 NUMBER;
1157 l_efc_s_4 NUMBER;
1158 l_efc_s_5 NUMBER;
1159 l_efc_s_6 NUMBER;
1160 l_efc_s_7 NUMBER;
1161 l_efc_s_8 NUMBER;
1165 l_efc_s_12 NUMBER;
1162 l_efc_s_9 NUMBER;
1163 l_efc_s_10 NUMBER;
1164 l_efc_s_11 NUMBER;
1166
1167 l_sc_assets_fa NUMBER:= null;
1168 l_fabase_efc igf_ap_isir_matched_all.paid_efc%TYPE;
1169 l_ftype VARCHAR2(1);
1170 p_paid_efc igf_ap_isir_matched_all.paid_efc%TYPE;
1171
1172 BEGIN -- calc_efc_main
1173
1174 -- Bug 2394936 ISIR we always calculate For 12 Months , Independent of Enrollment
1175 l_no_of_months := 12 ;
1176 l_sc_assets_fa := null ;
1177
1178 IF p_formula IN ( '1','2','3' ) THEN
1179 l_ftype := 'R'; -- Regular
1180 ELSIF p_formula IN ( '4','5','6' ) THEN
1181 l_ftype := 'S'; -- Simplified
1182 END IF;
1183
1184 IF ( p_formula = '5' and p_isir_rec.sec_efc_type = '2' ) THEN
1185 igf_ap_efc_subf.auto_zero_efc(p_isir_rec.primary_efc_type);
1186 -- AUTO ZERO
1187 p_sec_efc := 0;
1188 g_s_efc_a_9 := null ;
1189 g_s_efc_a_10 := null ;
1190 g_s_efc_a_11 := null ;
1191 g_s_efc_a_12 := null ;
1192
1193 END IF;
1194
1195 IF ( p_formula = '0' ) THEN
1196 igf_ap_efc_subf.auto_zero_efc(p_isir_rec.primary_efc_type);
1197 -- AUTO ZERO
1198 p_efc := 0;
1199 g_efc_a_9 := null ;
1200 g_efc_a_10 := null ;
1201 g_efc_a_11 := null ;
1202 g_efc_a_12 := null ;
1203
1204 ELSIF ( p_formula IN ('1','4')) THEN
1205 -- Formula A
1206 g_efc_a_9 := null ;
1207 g_efc_a_10 := null ;
1208 g_efc_a_11 := null ;
1209 g_efc_a_12 := null ;
1210
1211 efc_a( p_formula, l_no_of_months, p_efc , l_sc_assets_fa);
1212
1213 ELSIF ( p_formula IN ('2','5') ) THEN -- Formula B
1214 efc_b( p_formula, p_isir_rec.sec_efc_type,l_no_of_months, p_efc,p_sec_efc);
1215
1216 ELSIF ( p_formula IN ('3','6') ) THEN -- Formula C
1217 efc_c( p_formula, p_isir_rec.sec_efc_type, l_no_of_months, p_efc,p_sec_efc) ;
1218
1219 END IF;
1220
1221 p_efc := LEAST(99999,p_efc);
1222
1223 -- Get the EFC per month of enrollment
1224 -- Bug# 2394936 - for worksheet A divide by exact number of months
1225 -- for other worksheets the calc arrived is always for 9 month
1226 IF ( p_formula IN ('1','4')) THEN -- Formula A
1227 l_efc_per_month := p_efc / l_no_of_months;
1228 ELSE
1229 g_efc_a_9 := p_efc ;
1230 l_efc_per_month := p_efc / 9 ;
1231 END IF;
1232
1233 p_efc := ROUND ( p_efc ) ;
1234
1235 -- Round off the EFC wrt Bug# 2339982
1236 l_efc_per_month := ROUND(l_efc_per_month) ;
1237
1238 -- Bug# 2394936 - EFC will now be calculated for all months
1239 -- Hence the following code has been removed
1240 -- Get EFC value for each month
1241
1242 -- 1/3/5 => REGULAR FORMULA
1243 IF l_ftype = 'R' THEN
1244 IF p_formula IN ('1') THEN -- Formula A
1245 l_efc_1 := get_month_efc(1) + NVL(l_sc_assets_fa,0) ;
1246 l_efc_2 := get_month_efc(2) + NVL(l_sc_assets_fa,0) ;
1247 l_efc_3 := get_month_efc(3) + NVL(l_sc_assets_fa,0) ;
1248 l_efc_4 := get_month_efc(4) + NVL(l_sc_assets_fa,0) ;
1249 l_efc_5 := get_month_efc(5) + NVL(l_sc_assets_fa,0) ;
1250 l_efc_6 := get_month_efc(6) + NVL(l_sc_assets_fa,0) ;
1251 l_efc_7 := get_month_efc(7) + NVL(l_sc_assets_fa,0) ;
1252 l_efc_8 := get_month_efc(8) + NVL(l_sc_assets_fa,0) ;
1253 ELSE
1254 l_efc_1 := (l_efc_per_month * 1) + NVL(l_sc_assets_fa,0) ;
1255 l_efc_2 := (l_efc_per_month * 2) + NVL(l_sc_assets_fa,0) ;
1256 l_efc_3 := (l_efc_per_month * 3) + NVL(l_sc_assets_fa,0) ;
1257 l_efc_4 := (l_efc_per_month * 4) + NVL(l_sc_assets_fa,0) ;
1258 l_efc_5 := (l_efc_per_month * 5) + NVL(l_sc_assets_fa,0) ;
1259 l_efc_6 := (l_efc_per_month * 6) + NVL(l_sc_assets_fa,0) ;
1260 l_efc_7 := (l_efc_per_month * 7) + NVL(l_sc_assets_fa,0) ;
1261 l_efc_8 := (l_efc_per_month * 8) + NVL(l_sc_assets_fa,0) ;
1262 END IF;
1263
1264 l_efc_s_1 := NULL ;
1265 l_efc_s_2 := NULL ;
1266 l_efc_s_3 := NULL ;
1267 l_efc_s_4 := NULL ;
1268 l_efc_s_5 := NULL ;
1269 l_efc_s_6 := NULL ;
1270 l_efc_s_7 := NULL ;
1271 l_efc_s_8 := NULL ;
1272 l_efc_s_9 := NULL ;
1273 l_efc_s_10 := NULL ;
1274 l_efc_s_11 := NULL ;
1275 l_efc_s_12 := NULL ;
1276
1277 ELSIF l_ftype = 'S' THEN -- SIMPLIFIED FORMULA
1278
1279 -- primary efc will not include inc from assets
1280 IF p_formula IN ('4') THEN -- Formula A
1281 l_efc_1 := get_month_efc(1) ;
1282 l_efc_2 := get_month_efc(2) ;
1283 l_efc_3 := get_month_efc(3) ;
1284 l_efc_4 := get_month_efc(4) ;
1285 l_efc_5 := get_month_efc(5) ;
1286 l_efc_6 := get_month_efc(6) ;
1287 l_efc_7 := get_month_efc(7) ;
1288 l_efc_8 := get_month_efc(8) ;
1289 ELSE
1290
1291 l_efc_1 := (l_efc_per_month * 1) ;
1295 l_efc_5 := (l_efc_per_month * 5) ;
1292 l_efc_2 := (l_efc_per_month * 2) ;
1293 l_efc_3 := (l_efc_per_month * 3) ;
1294 l_efc_4 := (l_efc_per_month * 4) ;
1296 l_efc_6 := (l_efc_per_month * 6) ;
1297 l_efc_7 := (l_efc_per_month * 7) ;
1298 l_efc_8 := (l_efc_per_month * 8) ;
1299 END IF;
1300
1301 -- secondary efc will include inc from assets
1302 IF p_formula IN ('4') THEN -- Formula A
1303 l_efc_s_1 := get_month_efc(1) + NVL(l_sc_assets_fa,0) ;
1304 l_efc_s_2 := get_month_efc(2) + NVL(l_sc_assets_fa,0) ;
1305 l_efc_s_3 := get_month_efc(3) + NVL(l_sc_assets_fa,0) ;
1306 l_efc_s_4 := get_month_efc(4) + NVL(l_sc_assets_fa,0) ;
1307 l_efc_s_5 := get_month_efc(5) + NVL(l_sc_assets_fa,0) ;
1308 l_efc_s_6 := get_month_efc(6) + NVL(l_sc_assets_fa,0) ;
1309 l_efc_s_7 := get_month_efc(7) + NVL(l_sc_assets_fa,0) ;
1310 l_efc_s_8 := get_month_efc(8) + NVL(l_sc_assets_fa,0) ;
1311 ELSE
1312
1313 IF ( p_formula in ( '5','6') and p_isir_rec.sec_efc_type in ( '2','3') ) then
1314
1315
1316 p_sec_efc := LEAST(99999,p_sec_efc);
1317 g_s_efc_a_9 := p_sec_efc;
1318 l_sec_efc_per_month := p_sec_efc / 9 ;
1319 p_sec_efc := ROUND ( p_sec_efc ) ;
1320 l_sec_efc_per_month := ROUND(l_sec_efc_per_month) ;
1321
1322 l_efc_s_1 := (l_sec_efc_per_month * 1) + NVL(l_sc_assets_fa,0) ;
1323 l_efc_s_2 := (l_sec_efc_per_month * 2) + NVL(l_sc_assets_fa,0) ;
1324 l_efc_s_3 := (l_sec_efc_per_month * 3) + NVL(l_sc_assets_fa,0) ;
1325 l_efc_s_4 := (l_sec_efc_per_month * 4) + NVL(l_sc_assets_fa,0) ;
1326 l_efc_s_5 := (l_sec_efc_per_month * 5) + NVL(l_sc_assets_fa,0) ;
1327 l_efc_s_6 := (l_sec_efc_per_month * 6) + NVL(l_sc_assets_fa,0) ;
1328 l_efc_s_7 := (l_sec_efc_per_month * 7) + NVL(l_sc_assets_fa,0) ;
1329 l_efc_s_8 := (l_sec_efc_per_month * 8) + NVL(l_sc_assets_fa,0) ;
1330 ELSE
1331
1332 -- formula C TYPE '6' and '3'
1333
1334 l_efc_s_1 := (l_efc_per_month * 1) + NVL(l_sc_assets_fa,0) ;
1335 l_efc_s_2 := (l_efc_per_month * 2) + NVL(l_sc_assets_fa,0) ;
1336 l_efc_s_3 := (l_efc_per_month * 3) + NVL(l_sc_assets_fa,0) ;
1337 l_efc_s_4 := (l_efc_per_month * 4) + NVL(l_sc_assets_fa,0) ;
1338 l_efc_s_5 := (l_efc_per_month * 5) + NVL(l_sc_assets_fa,0) ;
1339 l_efc_s_6 := (l_efc_per_month * 6) + NVL(l_sc_assets_fa,0) ;
1340 l_efc_s_7 := (l_efc_per_month * 7) + NVL(l_sc_assets_fa,0) ;
1341 l_efc_s_8 := (l_efc_per_month * 8) + NVL(l_sc_assets_fa,0) ;
1342
1343 END IF;
1344
1345 END IF;
1346
1347
1348
1349 END IF;
1350
1351
1352 -- Bug# 2394936 - EFC for Worksheet B/C will be 9 month for months greater than 9
1353 IF p_formula IN ('1','4') THEN -- Formula A
1354 IF l_ftype = 'R' THEN
1355 l_efc_9 := g_efc_a_9 + NVL(l_sc_assets_fa,0) ;
1356 ELSE
1357 l_efc_9 := g_efc_a_9 ;
1358 l_efc_s_9 := g_efc_a_9 + NVL(l_sc_assets_fa,0) ;
1359
1360 END IF;
1361 ELSE
1362 l_efc_9 := ROUND(g_efc_a_9) ;
1363
1364
1365 -- plug in the code here ...
1366 -- manu :- our system does not gets a value for secondary_efc when formula '2','3','5','6' is used.
1367 IF ( p_isir_rec.dependency_status = 'I'
1368 AND l_efc_9 IS NOT NULL
1369 AND p_isir_rec.reject_reason_codes IS NULL
1370 AND NVL( p_isir_rec.simplified_need_test, 'N') = 'Y'
1371 AND NVL( p_isir_rec.auto_zero_efc, 'N') <> 'Y'
1372 AND ( p_isir_rec.s_investment_networth IS NOT NULL
1373 OR p_isir_rec.s_busi_farm_networth IS NOT NULL
1374 OR p_isir_rec.s_cash_savings IS NOT NULL )
1375 ) THEN
1376 -- populate sec efc value
1377 if ( p_formula IN ('5','6') AND p_isir_rec.sec_efc_type IN ('2','3') ) then
1378 l_efc_s_9 := g_s_efc_a_9 + NVL(l_sc_assets_fa,0) ;
1379 l_efc_s_9 := ROUND(l_efc_s_9) ;
1380 else
1381 l_efc_s_9 := ROUND(g_efc_a_9) ;
1382 END IF;
1383
1384 ELSE
1385 -- do not populate field
1386 l_efc_s_9 := NULL ;
1387 END IF ;
1388
1389 -- Upto here ..... as suggested by CARL over con call ;
1390
1391 /*
1392 not required as dependent means formula '1', '4'
1393 those will enter the loop above and not this one ...
1394 -- My understanding for Dependent Student ....
1395 IF ( p_isir_rec.dependency_status = 'D'
1396 AND p_isir_rec.reject_reason_codes IS NOT NULL
1397 AND p_paid_efc IS NOT NULL
1398 AND NVL( p_isir_rec.simplified_need_test, 'N') <> 'Y'
1399 AND NVL( p_isir_rec.auto_zero_efc, 'N') <> 'Y'
1400 AND ( p_isir_rec.p_investment_networth IS NULL
1401 OR p_isir_rec.p_business_networth IS NULL
1402 OR p_isir_rec.p_cash_saving IS NULL
1403 OR p_isir_rec.s_investment_networth IS NULL
1407 -- populate sec efc value ;
1404 OR p_isir_rec.s_busi_farm_networth IS NULL
1405 OR p_isir_rec.s_cash_savings IS NULL )
1406 ) THEN
1408 l_efc_s_9 := ROUND(g_efc_a_9) ;
1409 ELSE
1410 -- do not populate field
1411 l_efc_s_9 := NULL ;
1412 END IF ;
1413 -- manu :- may need to pull it out if it bombs ....
1414 */
1415
1416 END IF;
1417
1418 IF p_formula IN ('1','4') THEN -- Formula A
1419 IF l_ftype = 'R' THEN
1420 l_efc_10 := g_efc_a_10 + NVL(l_sc_assets_fa,0) ;
1421 ELSE
1422 l_efc_10 := g_efc_a_10 ;
1423 l_efc_s_10 := g_efc_a_10 + NVL(l_sc_assets_fa,0) ;
1424 END IF;
1425 ELSIF p_formula IN ('0') THEN -- Auto Zero Formula
1426 l_efc_10 := NULL;
1427 ELSE
1428 l_efc_10 := ROUND(g_efc_a_9) ;
1429 -- For simplified calculate the secondary efc as well
1430 IF l_ftype <> 'R' THEN
1431 if ( p_formula in ('5','6') and p_isir_rec.sec_efc_type in ('2','3') ) then
1432 l_efc_s_10 := ROUND(g_s_efc_a_9) ;
1433 else
1434 l_efc_s_10 := ROUND(g_efc_a_9) ;
1435 end if;
1436 END IF;
1437
1438 END IF;
1439
1440 IF p_formula IN ('1','4') THEN -- Formula A
1441 IF l_ftype = 'R' THEN
1442 l_efc_11 := g_efc_a_11 + NVL(l_sc_assets_fa,0) ;
1443 ELSE
1444 l_efc_11 := g_efc_a_11 ;
1445 l_efc_s_11 := g_efc_a_11 + NVL(l_sc_assets_fa,0) ;
1446 END IF;
1447 ELSIF p_formula IN ('0') THEN -- Auto Zero Formula
1448 l_efc_11 := NULL;
1449 ELSE
1450 l_efc_11 := ROUND(g_efc_a_9) ;
1451 -- For simplified calculate the secondary efc as well
1452 IF l_ftype <> 'R' THEN
1453 -- if ( p_formula = '5' and p_isir_rec.sec_efc_type = '2' ) then
1454 if ( p_formula in ('5','6') and p_isir_rec.sec_efc_type in ('2','3') ) then
1455 l_efc_s_11 := ROUND(g_s_efc_a_9) ;
1456 else
1457 l_efc_s_11 := ROUND(g_efc_a_9) ;
1458 end if;
1459 END IF;
1460 END IF;
1461
1462 IF p_formula IN ('1','4') THEN -- Formula A
1463 IF l_ftype = 'R' THEN
1464 l_efc_12 := g_efc_a_12 + NVL(l_sc_assets_fa,0) ;
1465 ELSE
1466 l_efc_12 := g_efc_a_12 ;
1467 l_efc_s_12 := g_efc_a_12 + NVL(l_sc_assets_fa,0) ;
1468 END IF;
1469 ELSIF p_formula IN ('0') THEN -- Auto Zero Formula
1470 l_efc_12 := NULL;
1471 ELSE
1472 l_efc_12 := ROUND(g_efc_a_9) ;
1473 -- For simplified calculate the secondary efc as well
1474
1475 IF l_ftype <> 'R' THEN
1476
1477 -- if ( p_formula = '5' and p_isir_rec.sec_efc_type = '2' ) then
1478 if ( p_formula in ('5','6') and p_isir_rec.sec_efc_type in ('2','3') ) then
1479 l_efc_s_12 := ROUND(g_s_efc_a_9) ;
1480 else
1481 l_efc_s_12 := ROUND(g_efc_a_9) ;
1482 end if;
1483 END IF;
1484
1485 END IF;
1486 p_paid_efc := l_efc_9;
1487
1488 l_efc_1 := LEAST(99999,l_efc_1) ;
1489 l_efc_2 := LEAST(99999,l_efc_2) ;
1490 l_efc_3 := LEAST(99999,l_efc_3) ;
1491 l_efc_4 := LEAST(99999,l_efc_4) ;
1492 l_efc_5 := LEAST(99999,l_efc_5) ;
1493 l_efc_6 := LEAST(99999,l_efc_6) ;
1494 l_efc_7 := LEAST(99999,l_efc_7) ;
1495 l_efc_8 := LEAST(99999,l_efc_8) ;
1496 l_efc_9 := LEAST(99999,l_efc_9) ;
1497 l_efc_10 := LEAST(99999,l_efc_10) ;
1498 l_efc_11 := LEAST(99999,l_efc_11) ;
1499 l_efc_12 := LEAST(99999,l_efc_12) ;
1500
1501 l_efc_s_1 := LEAST(99999,l_efc_s_1) ;
1502 l_efc_s_2 := LEAST(99999,l_efc_s_2) ;
1503 l_efc_s_3 := LEAST(99999,l_efc_s_3) ;
1504 l_efc_s_4 := LEAST(99999,l_efc_s_4) ;
1505 l_efc_s_5 := LEAST(99999,l_efc_s_5) ;
1506 l_efc_s_6 := LEAST(99999,l_efc_s_6) ;
1507 l_efc_s_7 := LEAST(99999,l_efc_s_7) ;
1508 l_efc_s_8 := LEAST(99999,l_efc_s_8) ;
1509 l_efc_s_9 := LEAST(99999,l_efc_s_9) ;
1510 l_efc_s_10 := LEAST(99999,l_efc_s_10) ;
1511 l_efc_s_11 := LEAST(99999,l_efc_s_11) ;
1512 l_efc_s_12 := LEAST(99999,l_efc_s_12) ;
1513
1514 p_paid_efc := LEAST(99999,p_paid_efc);
1515
1516
1517 -- Update the isir rowtype variable
1518 isir_rec.primary_alternate_month_1 := l_efc_1 ;
1519 isir_rec.primary_alternate_month_2 := l_efc_2 ;
1520 isir_rec.primary_alternate_month_3 := l_efc_3 ;
1521 isir_rec.primary_alternate_month_4 := l_efc_4 ;
1522 isir_rec.primary_alternate_month_5 := l_efc_5 ;
1523 isir_rec.primary_alternate_month_6 := l_efc_6 ;
1524 isir_rec.primary_alternate_month_7 := l_efc_7 ;
1525 isir_rec.primary_alternate_month_8 := l_efc_8 ;
1526 isir_rec.primary_alternate_month_10 := l_efc_10;
1527 isir_rec.primary_alternate_month_11 := l_efc_11;
1528 isir_rec.primary_alternate_month_12 := l_efc_12;
1529 isir_rec.paid_efc := null; -- p_paid_efc; nsidana 11/20/2003 FA129 EFC updates for 2004-2005.
1530 isir_rec.primary_efc := p_paid_efc ;
1531
1532 isir_rec.sec_alternate_month_1 := l_efc_s_1 ;
1533 isir_rec.sec_alternate_month_2 := l_efc_s_2 ;
1534 isir_rec.sec_alternate_month_3 := l_efc_s_3 ;
1535 isir_rec.sec_alternate_month_4 := l_efc_s_4 ;
1536 isir_rec.sec_alternate_month_5 := l_efc_s_5 ;
1537 isir_rec.sec_alternate_month_6 := l_efc_s_6 ;
1538 isir_rec.sec_alternate_month_7 := l_efc_s_7 ;
1539 isir_rec.sec_alternate_month_8 := l_efc_s_8 ;
1540 isir_rec.sec_alternate_month_10 := l_efc_s_10;
1541 isir_rec.sec_alternate_month_11 := l_efc_s_11;
1542 isir_rec.sec_alternate_month_12 := l_efc_s_12;
1543
1544 isir_rec.secondary_efc := l_efc_s_9 ;
1545
1546 flush_values(isir_rec);
1547
1548
1549 EXCEPTION
1550 WHEN EXCEPTION_IN_SETUP THEN
1551 APP_EXCEPTION.RAISE_EXCEPTION;
1552 WHEN OTHERS THEN
1553 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP'||SQLERRM);
1554 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.CALC_EFC_MAIN');
1555 IGS_GE_MSG_STACK.ADD;
1556 APP_EXCEPTION.RAISE_EXCEPTION;
1557
1558 END calc_efc_main ;
1562 p_ignore_warnings IN VARCHAR2 ,
1559
1560
1561 PROCEDURE calculate_efc (p_isir_rec IN OUT NOCOPY igf_ap_isir_matched%ROWTYPE ,
1563 p_sys_batch_yr IN VARCHAR2 ,
1564 p_return_status OUT NOCOPY VARCHAR2 ) AS
1565 /*
1566 || Created By : masehgal
1567 || Created On : 11-Feb-2003
1568 || Purpose : Main EFC Engine
1569 || Known limitations, enhancements or remarks :
1570 || Change History :
1571 || Who When What
1572 || (reverse chronological order - newest change first)
1573 */
1574
1575 p_formula VARCHAR2(30) := NULL ;
1576 p_reject_codes VARCHAR2(300) := NULL ;
1577 l_len_rejects NUMBER := NULL ;
1578 l_curr_pos NUMBER := 1 ;
1579
1580
1581
1582
1583 BEGIN -- calculate_efc
1584
1585 -- Reset the values to be computed/assumed only if the ignore warnings flag is not set
1586 IF NVL(p_ignore_warnings, 'N') <> 'Y' THEN
1587 -- Reset the computed values
1588 p_isir_rec.paid_efc := NULL;
1589 p_isir_rec.primary_efc := NULL;
1590 p_isir_rec.secondary_efc := NULL;
1591 p_isir_rec.fed_pell_grant_efc_type := NULL;
1592 p_isir_rec.primary_efc_type := NULL;
1593 p_isir_rec.sec_efc_type := NULL;
1594 p_isir_rec.primary_alternate_month_1 := NULL;
1595 p_isir_rec.primary_alternate_month_2 := NULL;
1596 p_isir_rec.primary_alternate_month_3 := NULL;
1597 p_isir_rec.primary_alternate_month_4 := NULL;
1598 p_isir_rec.primary_alternate_month_5 := NULL;
1599 p_isir_rec.primary_alternate_month_6 := NULL;
1600 p_isir_rec.primary_alternate_month_7 := NULL;
1601 p_isir_rec.primary_alternate_month_8 := NULL;
1602 p_isir_rec.primary_alternate_month_10 := NULL;
1603 p_isir_rec.primary_alternate_month_11 := NULL;
1604 p_isir_rec.primary_alternate_month_12 := NULL;
1605 p_isir_rec.sec_alternate_month_1 := NULL;
1606 p_isir_rec.sec_alternate_month_2 := NULL;
1607 p_isir_rec.sec_alternate_month_3 := NULL;
1608 p_isir_rec.sec_alternate_month_4 := NULL;
1609 p_isir_rec.sec_alternate_month_5 := NULL;
1610 p_isir_rec.sec_alternate_month_6 := NULL;
1611 p_isir_rec.sec_alternate_month_7 := NULL;
1612 p_isir_rec.sec_alternate_month_8 := NULL;
1613 p_isir_rec.sec_alternate_month_10 := NULL;
1614 p_isir_rec.sec_alternate_month_11 := NULL;
1615 p_isir_rec.sec_alternate_month_12 := NULL;
1616 p_isir_rec.total_income := NULL;
1617 p_isir_rec.allow_total_income := NULL;
1618 p_isir_rec.state_tax_allow := NULL;
1619 p_isir_rec.employment_allow := NULL;
1620 p_isir_rec.income_protection_allow := NULL;
1621 p_isir_rec.available_income := NULL;
1622 p_isir_rec.contribution_from_ai := NULL;
1623 p_isir_rec.discretionary_networth := NULL;
1624 p_isir_rec.efc_networth := NULL;
1625 p_isir_rec.asset_protect_allow := NULL;
1626 p_isir_rec.parents_cont_from_assets := NULL;
1627 p_isir_rec.adjusted_available_income := NULL;
1628 p_isir_rec.total_student_contribution := NULL;
1629 p_isir_rec.total_parent_contribution := NULL;
1630 p_isir_rec.parents_contribution := NULL;
1631 p_isir_rec.student_total_income := NULL;
1635 p_isir_rec.sca := NULL;
1632 p_isir_rec.sati := NULL;
1633 p_isir_rec.sic := NULL;
1634 p_isir_rec.sdnw := NULL;
1636 p_isir_rec.fti := NULL;
1637 p_isir_rec.secti := NULL;
1638 p_isir_rec.secati := NULL;
1639 p_isir_rec.secstx := NULL;
1640 p_isir_rec.secea := NULL;
1641 p_isir_rec.secipa := NULL;
1642 p_isir_rec.secai := NULL;
1643 p_isir_rec.seccai := NULL;
1644 p_isir_rec.secdnw := NULL;
1645 p_isir_rec.secnw := NULL;
1646 p_isir_rec.secapa := NULL;
1647 p_isir_rec.secpca := NULL;
1648 p_isir_rec.secaai := NULL;
1649 p_isir_rec.sectsc := NULL;
1650 p_isir_rec.sectpc := NULL;
1651 p_isir_rec.secpc := NULL;
1652 p_isir_rec.secsti := NULL;
1653 p_isir_rec.secsati := NULL;
1654 p_isir_rec.secsic := NULL;
1655 p_isir_rec.secsdnw := NULL;
1656 p_isir_rec.secsca := NULL;
1657 p_isir_rec.secfti := NULL;
1658 p_isir_rec.a_citizenship := NULL;
1659 p_isir_rec.a_student_marital_status := NULL;
1660 p_isir_rec.a_student_agi := NULL;
1661 p_isir_rec.a_s_us_tax_paid := NULL;
1662 p_isir_rec.a_s_income_work := NULL;
1663 p_isir_rec.a_spouse_income_work := NULL;
1664 p_isir_rec.a_s_total_wsc := NULL;
1665 p_isir_rec.a_date_of_birth := NULL;
1666 p_isir_rec.a_student_married := NULL;
1667 p_isir_rec.a_have_children := NULL;
1668 p_isir_rec.a_s_have_dependents := NULL;
1669 p_isir_rec.a_va_status := NULL;
1670 p_isir_rec.a_s_num_in_family := NULL;
1671 p_isir_rec.a_s_num_in_college := NULL;
1672 p_isir_rec.a_p_marital_status := NULL;
1673 p_isir_rec.a_father_ssn := NULL;
1674 p_isir_rec.a_mother_ssn := NULL;
1675 -- p_isir_rec.a_parents_num_family := NULL;
1676 -- p_isir_rec.a_parents_num_college := NULL;
1677 p_isir_rec.a_parents_agi := NULL;
1678 p_isir_rec.a_p_us_tax_paid := NULL;
1679 p_isir_rec.a_f_work_income := NULL;
1680 p_isir_rec.a_m_work_income := NULL;
1681 p_isir_rec.a_p_total_wsc := NULL;
1682 p_isir_rec.payment_isir := NULL;
1683 p_isir_rec.receipt_status := NULL;
1684 p_isir_rec.isir_receipt_completed := NULL;
1685 /* p_isir_rec.system_record_type := NULL; This value need not be nullified. */
1686 p_isir_rec.primary_efc_type := NULL;
1687 p_isir_rec.sec_efc_type := NULL;
1688 p_isir_rec.p_cal_tax_status := NULL;
1689 p_isir_rec.s_cal_tax_status := NULL;
1690 p_isir_rec.reject_reason_codes := NULL;
1691 END IF ;
1692
1693 p_sys_award_year := p_sys_batch_yr ;
1694
1695
1696 -- If P_IGNORE_WARNINGS = 'Y' then this is a second call from the ISIR Modify Page
1697 -- with the users decision to ignore warnings and proceed with the EFC Calculation.
1698 -- Move to Step CALC_EFC_MAIN
1699 IF NVL(p_ignore_warnings, 'N') <> 'Y' THEN
1700 -- do Model Determination, Assumption Edits,
1701 igf_ap_assumption_reject_edits.assume_values ( p_isir_rec,
1702 p_sys_award_year ) ;
1703 END IF;
1704
1705 -- Formula Determination
1706 get_efc_frml ( p_isir_rec,
1707 p_formula ) ;
1708
1709 -- Reject Edits before going to CALC_EFC_MAIN
1710 IF NVL(p_ignore_warnings, 'N') <> 'Y' THEN
1711 -- do Reject Edits, set return status
1712 igf_ap_assumption_reject_edits.reject_edits ( p_isir_rec ,
1713 p_sys_award_year ,
1714 p_reject_codes ) ;
1715 -- This has to be removed before final version release .....
1716 p_isir_rec.reject_reason_codes := p_reject_codes ;
1717
1718 -- Check if there are any reject reasons
1719 IF p_reject_codes is NOT NULL THEN
1720 -- If reject reasons present then check if there are non suppressable rejects
1721 l_len_rejects := LENGTH (p_reject_codes) ;
1722 l_curr_pos := 1 ;
1723 p_return_status := 'W' ;
1724 LOOP
1725 EXIT WHEN l_curr_pos >= l_len_rejects ;
1726 -- check if any of the reject reasons is a numeric code
1727 -- if yes then it is non suppressable
1728 IF chk_reject( SUBSTR ( p_reject_codes, l_curr_pos , 2 )) THEN
1729 -- numeric value exists
1730 -- throw error message, raise exception
1731 p_return_status := 'E' ;
1732 RAISE EXCEPTION_IN_REJECTS ;
1733 END IF ;
1734 l_curr_pos := l_curr_pos + 2 ;
1735 END LOOP ;
1736
1737 -- alphanumeric value exists
1738 -- throw error message, raise exception
1739 p_return_status := 'W' ;
1740 RAISE EXCEPTION_IN_REJECTS ;
1741
1742 ELSE
1743 p_return_status := 'S' ;
1744 END IF ; -- chk for NULL reject codes
1745 END IF ; -- chk for ignore warning flag
1746
1747 isir_rec := p_isir_rec ;
1748
1749 -- Call main efc calcuation engine
1750 -- once here means the rejects are to be rejected
1751 -- not to be done if CARLs Comments are to be included
1752 -- p_reject_codes := NULL ;
1753
1754 calc_efc_main ( isir_rec ,
1755 p_sys_award_year ,
1756 p_formula ) ;
1757
1758 p_isir_rec := isir_rec ;
1759
1760 -- once here means the rejects were not to be considered and therefore the return status should be 'S'
1761 p_return_status := 'S' ;
1762 /*
1763 -- Flush out secondary efc here ....
1764 -- For Dependent or Independent or Both ??? Yet to clarify ....
1765 -- Also ask abt the reject reasons being not null .... this is not mentioned in the FD ....
1766 -- Nor is the Auto Zero Flag mentioned in the FD .... ENQUIRE .....
1767 IF ( p_isir_rec.dependency_status = 'I'
1768 AND p_isir_rec.primary_efc IS NOT NULL
1769 AND p_isir_rec.reject_reason_codes IS NOT NULL
1770 AND NVL( p_isir_rec.simplified_need_test, 'N') <> 'Y'
1771 AND NVL( p_isir_rec.auto_zero_efc, 'N') <> 'Y'
1772 AND ( p_isir_rec.s_investment_networth IS NULL
1773 OR p_isir_rec.s_busi_farm_networth IS NULL
1774 OR p_isir_rec.s_cash_savings IS NULL )
1775 ) THEN
1776 -- populate sec efc value
1777 ELSE
1778 -- do not populate field
1779 p_isir_rec.secondary_efc := NULL ;
1780 END IF ;
1781 -- Upto here ..... as suggested by CARL over con call ;
1782
1783 -- My understanding for Dependent Student ....
1784 IF ( p_isir_rec.dependency_status = 'D'
1785 AND p_reject_codes IS NOT NULL
1786 AND p_isir_rec.primary_efc IS NOT NULL
1787 AND NVL( p_isir_rec.simplified_need_test, 'N') <> 'Y'
1788 AND NVL( p_isir_rec.auto_zero_efc, 'N') <> 'Y'
1789 AND ( p_isir_rec.p_investment_networth IS NULL
1790 OR p_isir_rec.p_business_networth IS NULL
1791 OR p_isir_rec.p_cash_saving IS NULL
1792 OR p_isir_rec.s_investment_networth IS NULL
1793 OR p_isir_rec.s_busi_farm_networth IS NULL
1794 OR p_isir_rec.s_cash_savings IS NULL )
1795 ) THEN
1796 -- populate sec efc value ;
1797 ELSE
1798 -- do not populate field
1799 p_isir_rec.secondary_efc := NULL ;
1800 END IF ;
1801
1802 */
1803
1804
1805
1806
1807
1808 EXCEPTION
1809 WHEN EXCEPTION_IN_REJECTS THEN
1810 RETURN ;
1811
1812 WHEN EXCEPTION_IN_SETUP THEN
1813 APP_EXCEPTION.RAISE_EXCEPTION;
1814
1815 WHEN OTHERS THEN
1816 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP'||SQLERRM);
1817 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_EFC_CALC.CALCULATE_EFC');
1818 IGS_GE_MSG_STACK.ADD;
1819 APP_EXCEPTION.RAISE_EXCEPTION;
1820
1821 END calculate_efc ;
1822
1823 END igf_ap_efc_calc;