[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_CALC_IM_EFC
Source
1 PACKAGE BODY igf_ap_calc_im_efc AS
2 /* $Header: IGFAP45B.pls 120.3 2006/02/08 23:35:52 ridas noship $ */
3 ------------------------------------------------------------------
4 --Created by : veramach, Oracle India
5 --Date created: 08-OCT-2003
6 --
7 --Purpose: This package calls the user hook for calculating IM EFC if INAS is integrated
8 --
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 -------------------------------------------------------------------
15 -- Get person number
16 CURSOR c_person_number(
17 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
18 ) IS
19 SELECT party_number
20 FROM hz_parties parties,
21 igf_ap_fa_base_rec_all fabase
22 WHERE fabase.person_id = parties.party_id
23 AND fabase.base_id = cp_base_id;
24
25 l_person_number hz_parties.party_number%TYPE;
26
27 g_tab_1 VARCHAR2(20) DEFAULT ' ';
28 g_tab_2 VARCHAR2(20) DEFAULT ' ';
29
30 g_success NUMBER := 0;
31 g_error NUMBER := 0;
32 g_total NUMBER := 0;
33
34 PROCEDURE log_parameters(
35 p_cal_type IN igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
36 p_sequence_number IN igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
37 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
38 p_persid_grp IN igs_pe_persid_group_all.group_id%TYPE
39 ) AS
40 ------------------------------------------------------------------
41 --Created by : veramach, Oracle India
42 --Date created: 08-OCT-2003
43 --
44 --Purpose:
45 --
46 --
47 --Known limitations/enhancements and/or remarks:
48 --
49 --Change History:
50 --Who When What
51 -------------------------------------------------------------------
52
53 l_param_pass_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PARAMETER_PASS');
54 l_awd_yr_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_YEAR');
55 l_pers_number_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PERSON_NUMBER');
56 l_pers_id_grp_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PERSON_ID_GROUP');
57
58 -- Get alternate code
59 CURSOR c_alternate_code(
60 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
61 cp_seq_number igs_ca_inst_all.sequence_number%TYPE
62 ) IS
63 SELECT alternate_code
64 FROM igs_ca_inst_all
65 WHERE cal_type = cp_cal_type
66 AND sequence_number = cp_seq_number;
67
68 l_alternate_code igs_ca_inst_all.alternate_code%TYPE;
69
70 -- Get get group description for group_id
71 CURSOR c_person_group(
72 cp_persid_grp igs_pe_persid_group_all.group_id%TYPE
73 ) IS
74 SELECT group_cd group_name
75 FROM igs_pe_persid_group_all
76 WHERE group_id = cp_persid_grp;
77
78 l_persid_grp_name c_person_group%ROWTYPE;
79
80 BEGIN
81 fnd_file.put_line(fnd_file.log,l_param_pass_log);
82
83 OPEN c_alternate_code(p_cal_type,p_sequence_number);
84 FETCH c_alternate_code INTO l_alternate_code;
85 CLOSE c_alternate_code;
86
87 fnd_file.put_line(fnd_file.log,RPAD(l_awd_yr_log,40) || ' : ' || l_alternate_code);
88
89 OPEN c_person_number(p_base_id);
90 FETCH c_person_number INTO l_person_number;
91 CLOSE c_person_number;
92
93 OPEN c_person_group(p_persid_grp);
94 FETCH c_person_group INTO l_persid_grp_name;
95 CLOSE c_person_group;
96
97 fnd_file.put_line(fnd_file.log,RPAD(l_pers_number_log,40) || ' : ' || l_person_number);
98 fnd_file.put_line(fnd_file.log,RPAD(l_pers_id_grp_log,40) || ' : ' || l_persid_grp_name.group_name);
99 fnd_file.put_line(fnd_file.log,RPAD('-',55,'-'));
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
104 fnd_message.set_token('NAME','IGF_AP_CALC_IM_EFC.LOG_PARAMETERS '||SQLERRM);
105 igs_ge_msg_stack.add;
106 app_exception.raise_exception;
107 END log_parameters;
108
109 PROCEDURE calculate_efc(
110 p_cal_type IN igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
111 p_seq_number IN igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
112 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
113 ) AS
114 ------------------------------------------------------------------
115 --Created by : veramach, Oracle India
116 --Date created: 8-OCT-2003
117 --
118 --Purpose: This is the main procedure which calculates EFC by invoking the userhook for a given base_id
119 --
120 --
121 --Known limitations/enhancements and/or remarks:
122 --
123 --Change History:
124 --Who When What
125 -- brajendr 2-Dec-2003 Bug # 3026594
126 -- Removed the Code to updated the FA Base record with the calculated ned
127 -------------------------------------------------------------------
128
129 l_internal_id_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','INTERNAL_ID') || ' ';
130 l_fnar_rec_fnd BOOLEAN := FALSE;
131
132 -- Get all PROFILES of a given base_id
133 CURSOR c_all_profiles(
134 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
135 ) IS
136 SELECT prof.*
137 FROM igf_ap_css_profile prof
138 WHERE base_id = cp_base_id;
139
140 l_profile c_all_profiles%ROWTYPE;
141
142 -- Get fnar record for a PROFILE
143 CURSOR c_fnar(
144 cp_cssp_id igf_ap_css_profile_all.cssp_id%TYPE
145 ) IS
146 SELECT fnar.*
147 FROM igf_ap_css_fnar fnar
148 WHERE cssp_id = cp_cssp_id;
149
150 l_fnar igf_ap_css_fnar%ROWTYPE;
151
152 l_efc_duration igf_ap_css_profile_all.coa_duration_num%TYPE;
153 l_coa_duration_efc_amt igf_ap_css_profile_all.coa_duration_efc_amt%TYPE;
154
155 -- Get sys_award_year based on cal type and sequence number
156 CURSOR c_award_year(
157 cp_cal_type IN igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
158 cp_seq_number IN igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
159 ) IS
160 SELECT sys_award_year
161 FROM igf_ap_batch_aw_map_all
162 WHERE ci_cal_type = cp_cal_type
163 AND ci_sequence_number = cp_seq_number;
164
165 l_award_year igf_ap_batch_aw_map_all.sys_award_year%TYPE;
166
167 lv_success BOOLEAN;
168 l_error_msg fnd_new_messages.message_name%TYPE DEFAULT NULL;
169
170 lv_cssp_rowid ROWID;
171 lv_fnar_rowid ROWID;
172 l_cssp_id igf_ap_css_profile_all.cssp_id%TYPE;
173 l_fnar_id igf_ap_css_fnar_all.fnar_id%TYPE;
174 l_fnar_cssp_id igf_ap_css_fnar_all.cssp_id%TYPE;
175 l_base_id igf_ap_css_profile_all.base_id%TYPE;
176
177 SKIP_PROFILE_RECORD EXCEPTION;
178
179 BEGIN
180
181 --Get EFC Duration
182 l_efc_duration := igf_ap_efc_calc.get_efc_no_of_months(NULL,p_base_id);
183 IF l_efc_duration > 12 THEN
184 l_efc_duration := 12;
185 ELSIF l_efc_duration = 0 THEN
186 RETURN;
187 END IF;
188
189 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
190 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.calculate_efc.main','l_efc_duration:'||l_efc_duration);
191 END IF;
192
193 --get sys award year based on cal type and sequence number
194 OPEN c_award_year(p_cal_type,p_seq_number);
195 FETCH c_award_year INTO l_award_year;
196 CLOSE c_award_year;
197
198 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
199 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.calculate_efc.main','l_award_year:'||l_award_year);
200 END IF;
201
202 --Get all profiles
203 OPEN c_all_profiles(p_base_id);
204 FETCH c_all_profiles INTO l_profile;
205
206 IF c_all_profiles%NOTFOUND THEN
207 --log a message
208 fnd_message.set_name('IGF','IGF_AP_NO_PROF_RECS_EXIST');
209 fnd_file.put_line(fnd_file.log,g_tab_2 || fnd_message.get);
210 RETURN;
211 ELSE
212
213 WHILE c_all_profiles%FOUND
214 LOOP
215 --store the PK of PROFILE
216 lv_cssp_rowid := l_profile.row_id;
217 l_cssp_id := l_profile.cssp_id;
218 l_base_id := l_profile.base_id;
219
220 BEGIN
221
222 OPEN c_fnar(l_profile.cssp_id);
223 FETCH c_fnar INTO l_fnar;
224 IF c_fnar%FOUND THEN
225 l_fnar_rec_fnd := TRUE;
226 l_fnar_id := l_fnar.fnar_id;
227 l_fnar_cssp_id := l_fnar.cssp_id;
228 lv_fnar_rowid := l_fnar.row_id;
229 ELSE
230 l_fnar_rec_fnd := FALSE;
231 END IF;
232 CLOSE c_fnar;
233
234 SAVEPOINT SP_PROFILE_ID;
235
236 --for each profile
237 fnd_file.put_line(fnd_file.log,g_tab_1 || l_internal_id_log || l_profile.css_id_number || l_profile.stu_record_type);
238
239 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
240 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.calculate_efc.debug','processing internal id '||l_profile.css_id_number || l_profile.stu_record_type);
241 END IF;
242
243 --call user hook
244 lv_success := igf_ap_uhk_inas_pkg.get_im_efc(
245 p_sys_awd_year => l_award_year,
246 p_profile_rec => l_profile,
247 p_fnar_rec => l_fnar,
248 p_error_msg => l_error_msg
249 );
250
251 IF NOT lv_success THEN
252 ROLLBACK TO SP_PROFILE_ID;
253 fnd_message.set_name('IGF',l_error_msg);
254 fnd_file.put_line(fnd_file.log,g_tab_2 || fnd_message.get);
255
256 fnd_message.set_name('IGF','IGF_AP_PROF_EFC_CALC_FAILED');
257 fnd_file.put_line(fnd_file.log,g_tab_2 || fnd_message.get);
258 RAISE SKIP_PROFILE_RECORD;
259 END IF;
260
261 IF l_profile.row_id <> lv_cssp_rowid OR
262 l_profile.cssp_id <> l_cssp_id OR
263 l_profile.base_id <> l_base_id OR
264 (l_fnar_rec_fnd AND (l_fnar.row_id <> lv_fnar_rowid OR l_fnar.fnar_id <> l_fnar_id OR l_fnar.cssp_id <> l_fnar_cssp_id)) THEN
265 fnd_message.set_name('IGF','IGF_AP_PROF_UHK_UPDATED_PK');
266 fnd_file.put_line(fnd_file.log,fnd_message.get);
267 RAISE SKIP_PROFILE_RECORD;
268 END IF;
269
270
271 BEGIN
272 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
273 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.calculate_efc.debug','l_coa_duration_efc_amt:'||l_coa_duration_efc_amt);
274 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.calculate_efc.debug','l_efc_duration:'||l_efc_duration);
275 END IF;
276 --update coa_duration_efc_amt,coa_duration_num
277 igf_ap_css_profile_pkg.update_row(
278 x_rowid => l_profile.row_id,
279 x_cssp_id => l_profile.cssp_id,
280 x_base_id => l_profile.base_id,
281 x_system_record_type => l_profile.system_record_type,
282 x_active_profile => l_profile.active_profile,
283 x_college_code => l_profile.college_code,
284 x_academic_year => l_profile.academic_year,
285 x_stu_record_type => l_profile.stu_record_type,
286 x_css_id_number => l_profile.css_id_number,
287 x_registration_receipt_date => l_profile.registration_receipt_date,
288 x_registration_type => l_profile.registration_type,
289 x_application_receipt_date => l_profile.application_receipt_date,
290 x_application_type => l_profile.application_type,
291 x_original_fnar_compute => l_profile.original_fnar_compute,
292 x_revision_fnar_compute_date => l_profile.revision_fnar_compute_date,
293 x_electronic_extract_date => l_profile.electronic_extract_date,
294 x_institutional_reporting_type => l_profile.institutional_reporting_type,
295 x_asr_receipt_date => l_profile.asr_receipt_date,
296 x_last_name => l_profile.last_name,
297 x_first_name => l_profile.first_name,
298 x_middle_initial => l_profile.middle_initial,
299 x_address_number_and_street => l_profile.address_number_and_street,
300 x_city => l_profile.city,
301 x_state_mailing => l_profile.state_mailing,
302 x_zip_code => l_profile.zip_code,
303 x_s_telephone_number => l_profile.s_telephone_number,
304 x_s_title => l_profile.s_title,
305 x_date_of_birth => l_profile.date_of_birth,
306 x_social_security_number => l_profile.social_security_number,
307 x_state_legal_residence => l_profile.state_legal_residence,
308 x_foreign_address_indicator => l_profile.foreign_address_indicator,
309 x_foreign_postal_code => l_profile.foreign_postal_code,
310 x_country => l_profile.country,
311 x_financial_aid_status => l_profile.financial_aid_status,
312 x_year_in_college => l_profile.year_in_college,
313 x_marital_status => l_profile.marital_status,
314 x_ward_court => l_profile.ward_court,
315 x_legal_dependents_other => l_profile.legal_dependents_other,
316 x_household_size => l_profile.household_size,
317 x_number_in_college => l_profile.number_in_college,
318 x_citizenship_status => l_profile.citizenship_status,
319 x_citizenship_country => l_profile.citizenship_country,
320 x_visa_classification => l_profile.visa_classification,
321 x_tax_figures => l_profile.tax_figures,
322 x_number_exemptions => l_profile.number_exemptions,
323 x_adjusted_gross_inc => l_profile.adjusted_gross_inc,
324 x_us_tax_paid => l_profile.us_tax_paid,
325 x_itemized_deductions => l_profile.itemized_deductions,
326 x_stu_income_work => l_profile.stu_income_work,
327 x_spouse_income_work => l_profile.spouse_income_work,
328 x_divid_int_inc => l_profile.divid_int_inc,
329 x_soc_sec_benefits => l_profile.soc_sec_benefits,
330 x_welfare_tanf => l_profile.welfare_tanf,
331 x_child_supp_rcvd => l_profile.child_supp_rcvd,
332 x_earned_income_credit => l_profile.earned_income_credit,
333 x_other_untax_income => l_profile.other_untax_income,
334 x_tax_stu_aid => l_profile.tax_stu_aid,
335 x_cash_sav_check => l_profile.cash_sav_check,
336 x_ira_keogh => l_profile.ira_keogh,
337 x_invest_value => l_profile.invest_value,
338 x_invest_debt => l_profile.invest_debt,
339 x_home_value => l_profile.home_value,
340 x_home_debt => l_profile.home_debt,
341 x_oth_real_value => l_profile.oth_real_value,
342 x_oth_real_debt => l_profile.oth_real_debt,
343 x_bus_farm_value => l_profile.bus_farm_value,
344 x_bus_farm_debt => l_profile.bus_farm_debt,
345 x_live_on_farm => l_profile.live_on_farm,
346 x_home_purch_price => l_profile.home_purch_price,
347 x_hope_ll_credit => l_profile.hope_ll_credit,
348 x_home_purch_year => l_profile.home_purch_year,
349 x_trust_amount => l_profile.trust_amount,
350 x_trust_avail => l_profile.trust_avail,
351 x_trust_estab => l_profile.trust_estab,
352 x_child_support_paid => l_profile.child_support_paid,
353 x_med_dent_expenses => l_profile.med_dent_expenses,
354 x_vet_us => l_profile.vet_us,
355 x_vet_ben_amount => l_profile.vet_ben_amount,
356 x_vet_ben_months => l_profile.vet_ben_months,
357 x_stu_summer_wages => l_profile.stu_summer_wages,
358 x_stu_school_yr_wages => l_profile.stu_school_yr_wages,
359 x_spouse_summer_wages => l_profile.spouse_summer_wages,
360 x_spouse_school_yr_wages => l_profile.spouse_school_yr_wages,
361 x_summer_other_tax_inc => l_profile.summer_other_tax_inc,
362 x_school_yr_other_tax_inc => l_profile.school_yr_other_tax_inc,
363 x_summer_untax_inc => l_profile.summer_untax_inc,
364 x_school_yr_untax_inc => l_profile.school_yr_untax_inc,
365 x_grants_schol_etc => l_profile.grants_schol_etc,
366 x_tuit_benefits => l_profile.tuit_benefits,
367 x_cont_parents => l_profile.cont_parents,
368 x_cont_relatives => l_profile.cont_relatives,
369 x_p_siblings_pre_tuit => l_profile.p_siblings_pre_tuit,
370 x_p_student_pre_tuit => l_profile.p_student_pre_tuit,
371 x_p_household_size => l_profile.p_household_size,
372 x_p_number_in_college => l_profile.p_number_in_college,
373 x_p_parents_in_college => l_profile.p_parents_in_college,
374 x_p_marital_status => l_profile.p_marital_status,
375 x_p_state_legal_residence => l_profile.p_state_legal_residence,
376 x_p_natural_par_status => l_profile.p_natural_par_status,
377 x_p_child_supp_paid => l_profile.p_child_supp_paid,
378 x_p_repay_ed_loans => l_profile.p_repay_ed_loans,
379 x_p_med_dent_expenses => l_profile.p_med_dent_expenses,
380 x_p_tuit_paid_amount => l_profile.p_tuit_paid_amount,
381 x_p_tuit_paid_number => l_profile.p_tuit_paid_number,
382 x_p_exp_child_supp_paid => l_profile.p_exp_child_supp_paid,
383 x_p_exp_repay_ed_loans => l_profile.p_exp_repay_ed_loans,
384 x_p_exp_med_dent_expenses => l_profile.p_exp_med_dent_expenses,
385 x_p_exp_tuit_pd_amount => l_profile.p_exp_tuit_pd_amount,
386 x_p_exp_tuit_pd_number => l_profile.p_exp_tuit_pd_number,
387 x_p_cash_sav_check => l_profile.p_cash_sav_check,
388 x_p_month_mortgage_pay => l_profile.p_month_mortgage_pay,
389 x_p_invest_value => l_profile.p_invest_value,
390 x_p_invest_debt => l_profile.p_invest_debt,
391 x_p_home_value => l_profile.p_home_value,
392 x_p_home_debt => l_profile.p_home_debt,
393 x_p_home_purch_price => l_profile.p_home_purch_price,
394 x_p_own_business_farm => l_profile.p_own_business_farm,
395 x_p_business_value => l_profile.p_business_value,
396 x_p_business_debt => l_profile.p_business_debt,
397 x_p_farm_value => l_profile.p_farm_value,
398 x_p_farm_debt => l_profile.p_farm_debt,
399 x_p_live_on_farm => l_profile.p_live_on_farm,
400 x_p_oth_real_estate_value => l_profile.p_oth_real_estate_value,
401 x_p_oth_real_estate_debt => l_profile.p_oth_real_estate_debt,
402 x_p_oth_real_purch_price => l_profile.p_oth_real_purch_price,
403 x_p_siblings_assets => l_profile.p_siblings_assets,
404 x_p_home_purch_year => l_profile.p_home_purch_year,
405 x_p_oth_real_purch_year => l_profile.p_oth_real_purch_year,
406 x_p_prior_agi => l_profile.p_prior_agi,
407 x_p_prior_us_tax_paid => l_profile.p_prior_us_tax_paid,
408 x_p_prior_item_deductions => l_profile.p_prior_item_deductions,
409 x_p_prior_other_untax_inc => l_profile.p_prior_other_untax_inc,
410 x_p_tax_figures => l_profile.p_tax_figures,
411 x_p_number_exemptions => l_profile.p_number_exemptions,
412 x_p_adjusted_gross_inc => l_profile.p_adjusted_gross_inc,
413 x_p_wages_sal_tips => l_profile.p_wages_sal_tips,
414 x_p_interest_income => l_profile.p_interest_income,
415 x_p_dividend_income => l_profile.p_dividend_income,
416 x_p_net_inc_bus_farm => l_profile.p_net_inc_bus_farm,
417 x_p_other_taxable_income => l_profile.p_other_taxable_income,
418 x_p_adj_to_income => l_profile.p_adj_to_income,
419 x_p_us_tax_paid => l_profile.p_us_tax_paid,
420 x_p_itemized_deductions => l_profile.p_itemized_deductions,
421 x_p_father_income_work => l_profile.p_father_income_work,
422 x_p_mother_income_work => l_profile.p_mother_income_work,
423 x_p_soc_sec_ben => l_profile.p_soc_sec_ben,
424 x_p_welfare_tanf => l_profile.p_welfare_tanf,
425 x_p_child_supp_rcvd => l_profile.p_child_supp_rcvd,
426 x_p_ded_ira_keogh => l_profile.p_ded_ira_keogh,
427 x_p_tax_defer_pens_savs => l_profile.p_tax_defer_pens_savs,
428 x_p_dep_care_med_spending => l_profile.p_dep_care_med_spending,
429 x_p_earned_income_credit => l_profile.p_earned_income_credit,
430 x_p_living_allow => l_profile.p_living_allow,
431 x_p_tax_exmpt_int => l_profile.p_tax_exmpt_int,
432 x_p_foreign_inc_excl => l_profile.p_foreign_inc_excl,
433 x_p_other_untax_inc => l_profile.p_other_untax_inc,
434 x_p_hope_ll_credit => l_profile.p_hope_ll_credit,
435 x_p_yr_separation => l_profile.p_yr_separation,
436 x_p_yr_divorce => l_profile.p_yr_divorce,
437 x_p_exp_father_inc => l_profile.p_exp_father_inc,
438 x_p_exp_mother_inc => l_profile.p_exp_mother_inc,
439 x_p_exp_other_tax_inc => l_profile.p_exp_other_tax_inc,
440 x_p_exp_other_untax_inc => l_profile.p_exp_other_untax_inc,
441 x_line_2_relation => l_profile.line_2_relation,
442 x_line_2_attend_college => l_profile.line_2_attend_college,
443 x_line_3_relation => l_profile.line_3_relation,
444 x_line_3_attend_college => l_profile.line_3_attend_college,
445 x_line_4_relation => l_profile.line_4_relation,
446 x_line_4_attend_college => l_profile.line_4_attend_college,
447 x_line_5_relation => l_profile.line_5_relation,
448 x_line_5_attend_college => l_profile.line_5_attend_college,
449 x_line_6_relation => l_profile.line_6_relation,
450 x_line_6_attend_college => l_profile.line_6_attend_college,
451 x_line_7_relation => l_profile.line_7_relation,
452 x_line_7_attend_college => l_profile.line_7_attend_college,
453 x_line_8_relation => l_profile.line_8_relation,
454 x_line_8_attend_college => l_profile.line_8_attend_college,
455 x_p_age_father => l_profile.p_age_father,
456 x_p_age_mother => l_profile.p_age_mother,
457 x_p_div_sep_ind => l_profile.p_div_sep_ind,
458 x_b_cont_non_custodial_par => l_profile.b_cont_non_custodial_par,
459 x_college_type_2 => l_profile.college_type_2,
460 x_college_type_3 => l_profile.college_type_3,
461 x_college_type_4 => l_profile.college_type_4,
462 x_college_type_5 => l_profile.college_type_5,
463 x_college_type_6 => l_profile.college_type_6,
464 x_college_type_7 => l_profile.college_type_7,
465 x_college_type_8 => l_profile.college_type_8,
466 x_school_code_1 => l_profile.school_code_1,
467 x_housing_code_1 => l_profile.housing_code_1,
468 x_school_code_2 => l_profile.school_code_2,
469 x_housing_code_2 => l_profile.housing_code_2,
470 x_school_code_3 => l_profile.school_code_3,
471 x_housing_code_3 => l_profile.housing_code_3,
472 x_school_code_4 => l_profile.school_code_4,
473 x_housing_code_4 => l_profile.housing_code_4,
474 x_school_code_5 => l_profile.school_code_5,
475 x_housing_code_5 => l_profile.housing_code_5,
476 x_school_code_6 => l_profile.school_code_6,
477 x_housing_code_6 => l_profile.housing_code_6,
478 x_school_code_7 => l_profile.school_code_7,
479 x_housing_code_7 => l_profile.housing_code_7,
480 x_school_code_8 => l_profile.school_code_8,
481 x_housing_code_8 => l_profile.housing_code_8,
482 x_school_code_9 => l_profile.school_code_9,
483 x_housing_code_9 => l_profile.housing_code_9,
484 x_school_code_10 => l_profile.school_code_10,
485 x_housing_code_10 => l_profile.housing_code_10,
486 x_additional_school_code_1 => l_profile.additional_school_code_1,
487 x_additional_school_code_2 => l_profile.additional_school_code_2,
488 x_additional_school_code_3 => l_profile.additional_school_code_3,
489 x_additional_school_code_4 => l_profile.additional_school_code_4,
490 x_additional_school_code_5 => l_profile.additional_school_code_5,
491 x_additional_school_code_6 => l_profile.additional_school_code_6,
492 x_additional_school_code_7 => l_profile.additional_school_code_7,
493 x_additional_school_code_8 => l_profile.additional_school_code_8,
494 x_additional_school_code_9 => l_profile.additional_school_code_9,
495 x_additional_school_code_10 => l_profile.additional_school_code_10,
496 x_explanation_spec_circum => l_profile.explanation_spec_circum,
497 x_signature_student => l_profile.signature_student,
498 x_signature_spouse => l_profile.signature_spouse,
499 x_signature_father => l_profile.signature_father,
500 x_signature_mother => l_profile.signature_mother,
501 x_month_day_completed => l_profile.month_day_completed,
502 x_year_completed => l_profile.year_completed,
503 x_age_line_2 => l_profile.age_line_2,
504 x_age_line_3 => l_profile.age_line_3,
505 x_age_line_4 => l_profile.age_line_4,
506 x_age_line_5 => l_profile.age_line_5,
507 x_age_line_6 => l_profile.age_line_6,
508 x_age_line_7 => l_profile.age_line_7,
509 x_age_line_8 => l_profile.age_line_8,
510 x_a_online_signature => l_profile.a_online_signature,
511 x_question_1_number => l_profile.question_1_number,
512 x_question_1_size => l_profile.question_1_size,
513 x_question_1_answer => l_profile.question_1_answer,
514 x_question_2_number => l_profile.question_2_number,
515 x_question_2_size => l_profile.question_2_size,
516 x_question_2_answer => l_profile.question_2_answer,
517 x_question_3_number => l_profile.question_3_number,
518 x_question_3_size => l_profile.question_3_size,
519 x_question_3_answer => l_profile.question_3_answer,
520 x_question_4_number => l_profile.question_4_number,
521 x_question_4_size => l_profile.question_4_size,
522 x_question_4_answer => l_profile.question_4_answer,
523 x_question_5_number => l_profile.question_5_number,
524 x_question_5_size => l_profile.question_5_size,
525 x_question_5_answer => l_profile.question_5_answer,
526 x_question_6_number => l_profile.question_6_number,
527 x_question_6_size => l_profile.question_6_size,
528 x_question_6_answer => l_profile.question_6_answer,
529 x_question_7_number => l_profile.question_7_number,
530 x_question_7_size => l_profile.question_7_size,
531 x_question_7_answer => l_profile.question_7_answer,
532 x_question_8_number => l_profile.question_8_number,
533 x_question_8_size => l_profile.question_8_size,
534 x_question_8_answer => l_profile.question_8_answer,
535 x_question_9_number => l_profile.question_9_number,
536 x_question_9_size => l_profile.question_9_size,
537 x_question_9_answer => l_profile.question_9_answer,
538 x_question_10_number => l_profile.question_10_number,
539 x_question_10_size => l_profile.question_10_size,
540 x_question_10_answer => l_profile.question_10_answer,
541 x_question_11_number => l_profile.question_11_number,
542 x_question_11_size => l_profile.question_11_size,
543 x_question_11_answer => l_profile.question_11_answer,
544 x_question_12_number => l_profile.question_12_number,
545 x_question_12_size => l_profile.question_12_size,
546 x_question_12_answer => l_profile.question_12_answer,
547 x_question_13_number => l_profile.question_13_number,
548 x_question_13_size => l_profile.question_13_size,
549 x_question_13_answer => l_profile.question_13_answer,
550 x_question_14_number => l_profile.question_14_number,
551 x_question_14_size => l_profile.question_14_size,
552 x_question_14_answer => l_profile.question_14_answer,
553 x_question_15_number => l_profile.question_15_number,
554 x_question_15_size => l_profile.question_15_size,
555 x_question_15_answer => l_profile.question_15_answer,
556 x_question_16_number => l_profile.question_16_number,
557 x_question_16_size => l_profile.question_16_size,
558 x_question_16_answer => l_profile.question_16_answer,
559 x_question_17_number => l_profile.question_17_number,
560 x_question_17_size => l_profile.question_17_size,
561 x_question_17_answer => l_profile.question_17_answer,
562 x_question_18_number => l_profile.question_18_number,
563 x_question_18_size => l_profile.question_18_size,
564 x_question_18_answer => l_profile.question_18_answer,
565 x_question_19_number => l_profile.question_19_number,
566 x_question_19_size => l_profile.question_19_size,
567 x_question_19_answer => l_profile.question_19_answer,
568 x_question_20_number => l_profile.question_20_number,
569 x_question_20_size => l_profile.question_20_size,
570 x_question_20_answer => l_profile.question_20_answer,
571 x_question_21_number => l_profile.question_21_number,
572 x_question_21_size => l_profile.question_21_size,
573 x_question_21_answer => l_profile.question_21_answer,
574 x_question_22_number => l_profile.question_22_number,
575 x_question_22_size => l_profile.question_22_size,
576 x_question_22_answer => l_profile.question_22_answer,
577 x_question_23_number => l_profile.question_23_number,
578 x_question_23_size => l_profile.question_23_size,
579 x_question_23_answer => l_profile.question_23_answer,
580 x_question_24_number => l_profile.question_24_number,
581 x_question_24_size => l_profile.question_24_size,
582 x_question_24_answer => l_profile.question_24_answer,
583 x_question_25_number => l_profile.question_25_number,
584 x_question_25_size => l_profile.question_25_size,
585 x_question_25_answer => l_profile.question_25_answer,
586 x_question_26_number => l_profile.question_26_number,
587 x_question_26_size => l_profile.question_26_size,
588 x_question_26_answer => l_profile.question_26_answer,
589 x_question_27_number => l_profile.question_27_number,
590 x_question_27_size => l_profile.question_27_size,
591 x_question_27_answer => l_profile.question_27_answer,
592 x_question_28_number => l_profile.question_28_number,
593 x_question_28_size => l_profile.question_28_size,
594 x_question_28_answer => l_profile.question_28_answer,
595 x_question_29_number => l_profile.question_29_number,
596 x_question_29_size => l_profile.question_29_size,
597 x_question_29_answer => l_profile.question_29_answer,
598 x_question_30_number => l_profile.question_30_number,
599 x_questions_30_size => l_profile.questions_30_size,
600 x_question_30_answer => l_profile.question_30_answer,
601 x_mode => 'R',
602 x_legacy_record_flag => l_profile.legacy_record_flag,
603 x_coa_duration_efc_amt => l_coa_duration_efc_amt,
604 x_coa_duration_num => l_efc_duration,
605 x_p_soc_sec_ben_student_amt => l_profile.p_soc_sec_ben_student_amt,
606 x_p_tuit_fee_deduct_amt => l_profile.p_tuit_fee_deduct_amt,
607 x_stu_lives_with_num => l_profile.stu_lives_with_num,
608 x_stu_most_support_from_num => l_profile.stu_most_support_from_num,
609 x_location_computer_num => l_profile.location_computer_num
610 );
611 IF l_fnar_rec_fnd THEN
612 igf_ap_css_fnar_pkg.update_row(
613 x_rowid => l_fnar.row_id,
614 x_fnar_id => l_fnar.fnar_id,
615 x_cssp_id => l_fnar.cssp_id,
616 x_r_s_email_address => l_fnar.r_s_email_address,
617 x_eps_code => l_fnar.eps_code,
618 x_comp_css_dependency_status => l_fnar.comp_css_dependency_status,
619 x_stu_age => l_fnar.stu_age,
620 x_assumed_stu_yr_in_coll => l_fnar.assumed_stu_yr_in_coll,
621 x_comp_stu_marital_status => l_fnar.comp_stu_marital_status,
622 x_stu_family_members => l_fnar.stu_family_members,
623 x_stu_fam_members_in_college => l_fnar.stu_fam_members_in_college,
624 x_par_marital_status => l_fnar.par_marital_status,
625 x_par_family_members => l_fnar.par_family_members,
626 x_par_total_in_college => l_fnar.par_total_in_college,
627 x_par_par_in_college => l_fnar.par_par_in_college,
628 x_par_others_in_college => l_fnar.par_others_in_college,
629 x_par_aesa => l_fnar.par_aesa,
630 x_par_cesa => l_fnar.par_cesa,
631 x_stu_aesa => l_fnar.stu_aesa,
632 x_stu_cesa => l_fnar.stu_cesa,
633 x_im_p_bas_agi_taxable_income => l_fnar.im_p_bas_agi_taxable_income,
634 x_im_p_bas_untx_inc_and_ben => l_fnar.im_p_bas_untx_inc_and_ben,
635 x_im_p_bas_inc_adj => l_fnar.im_p_bas_inc_adj,
636 x_im_p_bas_total_income => l_fnar.im_p_bas_total_income,
637 x_im_p_bas_us_income_tax => l_fnar.im_p_bas_us_income_tax,
638 x_im_p_bas_st_and_other_tax => l_fnar.im_p_bas_st_and_other_tax,
639 x_im_p_bas_fica_tax => l_fnar.im_p_bas_fica_tax,
640 x_im_p_bas_med_dental => l_fnar.im_p_bas_med_dental,
641 x_im_p_bas_employment_allow => l_fnar.im_p_bas_employment_allow,
642 x_im_p_bas_annual_ed_savings => l_fnar.im_p_bas_annual_ed_savings,
643 x_im_p_bas_inc_prot_allow_m => l_fnar.im_p_bas_inc_prot_allow_m,
644 x_im_p_bas_total_inc_allow => l_fnar.im_p_bas_total_inc_allow,
645 x_im_p_bas_cal_avail_inc => l_fnar.im_p_bas_cal_avail_inc,
646 x_im_p_bas_avail_income => l_fnar.im_p_bas_avail_income,
647 x_im_p_bas_total_cont_inc => l_fnar.im_p_bas_total_cont_inc,
648 x_im_p_bas_cash_bank_accounts => l_fnar.im_p_bas_cash_bank_accounts,
649 x_im_p_bas_home_equity => l_fnar.im_p_bas_home_equity,
650 x_im_p_bas_ot_rl_est_inv_eq => l_fnar.im_p_bas_ot_rl_est_inv_eq,
651 x_im_p_bas_adj_bus_farm_worth => l_fnar.im_p_bas_adj_bus_farm_worth,
652 x_im_p_bas_ass_sibs_pre_tui => l_fnar.im_p_bas_ass_sibs_pre_tui,
653 x_im_p_bas_net_worth => l_fnar.im_p_bas_net_worth,
654 x_im_p_bas_emerg_res_allow => l_fnar.im_p_bas_emerg_res_allow,
655 x_im_p_bas_cum_ed_savings => l_fnar.im_p_bas_cum_ed_savings,
656 x_im_p_bas_low_inc_allow => l_fnar.im_p_bas_low_inc_allow,
657 x_im_p_bas_total_asset_allow => l_fnar.im_p_bas_total_asset_allow,
658 x_im_p_bas_disc_net_worth => l_fnar.im_p_bas_disc_net_worth,
659 x_im_p_bas_total_cont_asset => l_fnar.im_p_bas_total_cont_asset,
660 x_im_p_bas_total_cont => l_fnar.im_p_bas_total_cont,
661 x_im_p_bas_num_in_coll_adj => l_fnar.im_p_bas_num_in_coll_adj,
662 x_im_p_bas_cont_for_stu => l_fnar.im_p_bas_cont_for_stu,
663 x_im_p_bas_cont_from_income => l_fnar.im_p_bas_cont_from_income,
664 x_im_p_bas_cont_from_assets => l_fnar.im_p_bas_cont_from_assets,
665 x_im_p_opt_agi_taxable_income => l_fnar.im_p_opt_agi_taxable_income,
666 x_im_p_opt_untx_inc_and_ben => l_fnar.im_p_opt_untx_inc_and_ben,
667 x_im_p_opt_inc_adj => l_fnar.im_p_opt_inc_adj,
668 x_im_p_opt_total_income => l_fnar.im_p_opt_total_income,
669 x_im_p_opt_us_income_tax => l_fnar.im_p_opt_us_income_tax,
670 x_im_p_opt_st_and_other_tax => l_fnar.im_p_opt_st_and_other_tax,
671 x_im_p_opt_fica_tax => l_fnar.im_p_opt_fica_tax,
672 x_im_p_opt_med_dental => l_fnar.im_p_opt_med_dental,
673 x_im_p_opt_elem_sec_tuit => l_fnar.im_p_opt_elem_sec_tuit,
674 x_im_p_opt_employment_allow => l_fnar.im_p_opt_employment_allow,
675 x_im_p_opt_annual_ed_savings => l_fnar.im_p_opt_annual_ed_savings,
676 x_im_p_opt_inc_prot_allow_m => l_fnar.im_p_opt_inc_prot_allow_m,
677 x_im_p_opt_total_inc_allow => l_fnar.im_p_opt_total_inc_allow,
678 x_im_p_opt_cal_avail_inc => l_fnar.im_p_opt_cal_avail_inc,
679 x_im_p_opt_avail_income => l_fnar.im_p_opt_avail_income,
680 x_im_p_opt_total_cont_inc => l_fnar.im_p_opt_total_cont_inc,
681 x_im_p_opt_cash_bank_accounts => l_fnar.im_p_opt_cash_bank_accounts,
682 x_im_p_opt_home_equity => l_fnar.im_p_opt_home_equity,
683 x_im_p_opt_ot_rl_est_inv_eq => l_fnar.im_p_opt_ot_rl_est_inv_eq,
684 x_im_p_opt_adj_bus_farm_worth => l_fnar.im_p_opt_adj_bus_farm_worth,
685 x_im_p_opt_ass_sibs_pre_tui => l_fnar.im_p_opt_ass_sibs_pre_tui,
686 x_im_p_opt_net_worth => l_fnar.im_p_opt_net_worth,
687 x_im_p_opt_emerg_res_allow => l_fnar.im_p_opt_emerg_res_allow,
688 x_im_p_opt_cum_ed_savings => l_fnar.im_p_opt_cum_ed_savings,
689 x_im_p_opt_low_inc_allow => l_fnar.im_p_opt_low_inc_allow,
690 x_im_p_opt_total_asset_allow => l_fnar.im_p_opt_total_asset_allow,
691 x_im_p_opt_disc_net_worth => l_fnar.im_p_opt_disc_net_worth,
692 x_im_p_opt_total_cont_asset => l_fnar.im_p_opt_total_cont_asset,
693 x_im_p_opt_total_cont => l_fnar.im_p_opt_total_cont,
694 x_im_p_opt_num_in_coll_adj => l_fnar.im_p_opt_num_in_coll_adj,
695 x_im_p_opt_cont_for_stu => l_fnar.im_p_opt_cont_for_stu,
696 x_im_p_opt_cont_from_income => l_fnar.im_p_opt_cont_from_income,
697 x_im_p_opt_cont_from_assets => l_fnar.im_p_opt_cont_from_assets,
698 x_fm_p_analysis_type => l_fnar.fm_p_analysis_type,
699 x_fm_p_agi_taxable_income => l_fnar.fm_p_agi_taxable_income,
700 x_fm_p_untx_inc_and_ben => l_fnar.fm_p_untx_inc_and_ben,
701 x_fm_p_inc_adj => l_fnar.fm_p_inc_adj,
702 x_fm_p_total_income => l_fnar.fm_p_total_income,
703 x_fm_p_us_income_tax => l_fnar.fm_p_us_income_tax,
704 x_fm_p_state_and_other_taxes => l_fnar.fm_p_state_and_other_taxes,
705 x_fm_p_fica_tax => l_fnar.fm_p_fica_tax,
706 x_fm_p_employment_allow => l_fnar.fm_p_employment_allow,
707 x_fm_p_income_prot_allow => l_fnar.fm_p_income_prot_allow,
708 x_fm_p_total_allow => l_fnar.fm_p_total_allow,
709 x_fm_p_avail_income => l_fnar.fm_p_avail_income,
710 x_fm_p_cash_bank_accounts => l_fnar.fm_p_cash_bank_accounts,
711 x_fm_p_ot_rl_est_inv_equity => l_fnar.fm_p_ot_rl_est_inv_equity,
712 x_fm_p_adj_bus_farm_net_worth => l_fnar.fm_p_adj_bus_farm_net_worth,
713 x_fm_p_net_worth => l_fnar.fm_p_net_worth,
714 x_fm_p_asset_prot_allow => l_fnar.fm_p_asset_prot_allow,
715 x_fm_p_disc_net_worth => l_fnar.fm_p_disc_net_worth,
716 x_fm_p_total_contribution => l_fnar.fm_p_total_contribution,
717 x_fm_p_num_in_coll => l_fnar.fm_p_num_in_coll,
718 x_fm_p_cont_for_stu => l_fnar.fm_p_cont_for_stu,
719 x_fm_p_cont_from_income => l_fnar.fm_p_cont_from_income,
720 x_fm_p_cont_from_assets => l_fnar.fm_p_cont_from_assets,
721 x_im_s_bas_agi_taxable_income => l_fnar.im_s_bas_agi_taxable_income,
722 x_im_s_bas_untx_inc_and_ben => l_fnar.im_s_bas_untx_inc_and_ben,
723 x_im_s_bas_inc_adj => l_fnar.im_s_bas_inc_adj,
724 x_im_s_bas_total_income => l_fnar.im_s_bas_total_income,
725 x_im_s_bas_us_income_tax => l_fnar.im_s_bas_us_income_tax,
726 x_im_s_bas_state_and_oth_taxes => l_fnar.im_s_bas_state_and_oth_taxes,
727 x_im_s_bas_fica_tax => l_fnar.im_s_bas_fica_tax,
728 x_im_s_bas_med_dental => l_fnar.im_s_bas_med_dental,
729 x_im_s_bas_employment_allow => l_fnar.im_s_bas_employment_allow,
730 x_im_s_bas_annual_ed_savings => l_fnar.im_s_bas_annual_ed_savings,
731 x_im_s_bas_inc_prot_allow_m => l_fnar.im_s_bas_inc_prot_allow_m,
732 x_im_s_bas_total_inc_allow => l_fnar.im_s_bas_total_inc_allow,
733 x_im_s_bas_cal_avail_income => l_fnar.im_s_bas_cal_avail_income,
734 x_im_s_bas_avail_income => l_fnar.im_s_bas_avail_income,
735 x_im_s_bas_total_cont_inc => l_fnar.im_s_bas_total_cont_inc,
736 x_im_s_bas_cash_bank_accounts => l_fnar.im_s_bas_cash_bank_accounts,
737 x_im_s_bas_home_equity => l_fnar.im_s_bas_home_equity,
738 x_im_s_bas_ot_rl_est_inv_eq => l_fnar.im_s_bas_ot_rl_est_inv_eq,
739 x_im_s_bas_adj_busfarm_worth => l_fnar.im_s_bas_adj_busfarm_worth,
740 x_im_s_bas_trusts => l_fnar.im_s_bas_trusts,
741 x_im_s_bas_net_worth => l_fnar.im_s_bas_net_worth,
742 x_im_s_bas_emerg_res_allow => l_fnar.im_s_bas_emerg_res_allow,
743 x_im_s_bas_cum_ed_savings => l_fnar.im_s_bas_cum_ed_savings,
744 x_im_s_bas_total_asset_allow => l_fnar.im_s_bas_total_asset_allow,
745 x_im_s_bas_disc_net_worth => l_fnar.im_s_bas_disc_net_worth,
746 x_im_s_bas_total_cont_asset => l_fnar.im_s_bas_total_cont_asset,
747 x_im_s_bas_total_cont => l_fnar.im_s_bas_total_cont,
748 x_im_s_bas_num_in_coll_adj => l_fnar.im_s_bas_num_in_coll_adj,
749 x_im_s_bas_cont_for_stu => l_fnar.im_s_bas_cont_for_stu,
750 x_im_s_bas_cont_from_income => l_fnar.im_s_bas_cont_from_income,
751 x_im_s_bas_cont_from_assets => l_fnar.im_s_bas_cont_from_assets,
752 x_im_s_est_agitaxable_income => l_fnar.im_s_est_agitaxable_income,
753 x_im_s_est_untx_inc_and_ben => l_fnar.im_s_est_untx_inc_and_ben,
754 x_im_s_est_inc_adj => l_fnar.im_s_est_inc_adj,
755 x_im_s_est_total_income => l_fnar.im_s_est_total_income,
756 x_im_s_est_us_income_tax => l_fnar.im_s_est_us_income_tax,
757 x_im_s_est_state_and_oth_taxes => l_fnar.im_s_est_state_and_oth_taxes,
758 x_im_s_est_fica_tax => l_fnar.im_s_est_fica_tax,
759 x_im_s_est_med_dental => l_fnar.im_s_est_med_dental,
760 x_im_s_est_employment_allow => l_fnar.im_s_est_employment_allow,
761 x_im_s_est_annual_ed_savings => l_fnar.im_s_est_annual_ed_savings,
762 x_im_s_est_inc_prot_allow_m => l_fnar.im_s_est_inc_prot_allow_m,
763 x_im_s_est_total_inc_allow => l_fnar.im_s_est_total_inc_allow,
764 x_im_s_est_cal_avail_income => l_fnar.im_s_est_cal_avail_income,
765 x_im_s_est_avail_income => l_fnar.im_s_est_avail_income,
766 x_im_s_est_total_cont_inc => l_fnar.im_s_est_total_cont_inc,
767 x_im_s_est_cash_bank_accounts => l_fnar.im_s_est_cash_bank_accounts,
768 x_im_s_est_home_equity => l_fnar.im_s_est_home_equity,
769 x_im_s_est_ot_rl_est_inv_eq => l_fnar.im_s_est_ot_rl_est_inv_eq,
770 x_im_s_est_adj_bus_farm_worth => l_fnar.im_s_est_adj_bus_farm_worth,
771 x_im_s_est_est_trusts => l_fnar.im_s_est_est_trusts,
772 x_im_s_est_net_worth => l_fnar.im_s_est_net_worth,
773 x_im_s_est_emerg_res_allow => l_fnar.im_s_est_emerg_res_allow,
774 x_im_s_est_cum_ed_savings => l_fnar.im_s_est_cum_ed_savings,
775 x_im_s_est_total_asset_allow => l_fnar.im_s_est_total_asset_allow,
776 x_im_s_est_disc_net_worth => l_fnar.im_s_est_disc_net_worth,
777 x_im_s_est_total_cont_asset => l_fnar.im_s_est_total_cont_asset,
778 x_im_s_est_total_cont => l_fnar.im_s_est_total_cont,
779 x_im_s_est_num_in_coll_adj => l_fnar.im_s_est_num_in_coll_adj,
780 x_im_s_est_cont_for_stu => l_fnar.im_s_est_cont_for_stu,
781 x_im_s_est_cont_from_income => l_fnar.im_s_est_cont_from_income,
782 x_im_s_est_cont_from_assets => l_fnar.im_s_est_cont_from_assets,
783 x_im_s_opt_agi_taxable_income => l_fnar.im_s_opt_agi_taxable_income,
784 x_im_s_opt_untx_inc_and_ben => l_fnar.im_s_opt_untx_inc_and_ben,
785 x_im_s_opt_inc_adj => l_fnar.im_s_opt_inc_adj,
786 x_im_s_opt_total_income => l_fnar.im_s_opt_total_income,
787 x_im_s_opt_us_income_tax => l_fnar.im_s_opt_us_income_tax,
788 x_im_s_opt_state_and_oth_taxes => l_fnar.im_s_opt_state_and_oth_taxes,
789 x_im_s_opt_fica_tax => l_fnar.im_s_opt_fica_tax,
790 x_im_s_opt_med_dental => l_fnar.im_s_opt_med_dental,
791 x_im_s_opt_employment_allow => l_fnar.im_s_opt_employment_allow,
792 x_im_s_opt_annual_ed_savings => l_fnar.im_s_opt_annual_ed_savings,
793 x_im_s_opt_inc_prot_allow_m => l_fnar.im_s_opt_inc_prot_allow_m,
794 x_im_s_opt_total_inc_allow => l_fnar.im_s_opt_total_inc_allow,
795 x_im_s_opt_cal_avail_income => l_fnar.im_s_opt_cal_avail_income,
796 x_im_s_opt_avail_income => l_fnar.im_s_opt_avail_income,
797 x_im_s_opt_total_cont_inc => l_fnar.im_s_opt_total_cont_inc,
798 x_im_s_opt_cash_bank_accounts => l_fnar.im_s_opt_cash_bank_accounts,
799 x_im_s_opt_ira_keogh_accounts => l_fnar.im_s_opt_ira_keogh_accounts,
800 x_im_s_opt_home_equity => l_fnar.im_s_opt_home_equity,
801 x_im_s_opt_ot_rl_est_inv_eq => l_fnar.im_s_opt_ot_rl_est_inv_eq,
802 x_im_s_opt_adj_bus_farm_worth => l_fnar.im_s_opt_adj_bus_farm_worth,
803 x_im_s_opt_trusts => l_fnar.im_s_opt_trusts,
804 x_im_s_opt_net_worth => l_fnar.im_s_opt_net_worth,
805 x_im_s_opt_emerg_res_allow => l_fnar.im_s_opt_emerg_res_allow,
806 x_im_s_opt_cum_ed_savings => l_fnar.im_s_opt_cum_ed_savings,
807 x_im_s_opt_total_asset_allow => l_fnar.im_s_opt_total_asset_allow,
808 x_im_s_opt_disc_net_worth => l_fnar.im_s_opt_disc_net_worth,
809 x_im_s_opt_total_cont_asset => l_fnar.im_s_opt_total_cont_asset,
810 x_im_s_opt_total_cont => l_fnar.im_s_opt_total_cont,
811 x_im_s_opt_num_in_coll_adj => l_fnar.im_s_opt_num_in_coll_adj,
812 x_im_s_opt_cont_for_stu => l_fnar.im_s_opt_cont_for_stu,
813 x_im_s_opt_cont_from_income => l_fnar.im_s_opt_cont_from_income,
814 x_im_s_opt_cont_from_assets => l_fnar.im_s_opt_cont_from_assets,
815 x_fm_s_analysis_type => l_fnar.fm_s_analysis_type,
816 x_fm_s_agi_taxable_income => l_fnar.fm_s_agi_taxable_income,
817 x_fm_s_untx_inc_and_ben => l_fnar.fm_s_untx_inc_and_ben,
818 x_fm_s_inc_adj => l_fnar.fm_s_inc_adj,
819 x_fm_s_total_income => l_fnar.fm_s_total_income,
820 x_fm_s_us_income_tax => l_fnar.fm_s_us_income_tax,
821 x_fm_s_state_and_oth_taxes => l_fnar.fm_s_state_and_oth_taxes,
822 x_fm_s_fica_tax => l_fnar.fm_s_fica_tax,
823 x_fm_s_employment_allow => l_fnar.fm_s_employment_allow,
824 x_fm_s_income_prot_allow => l_fnar.fm_s_income_prot_allow,
825 x_fm_s_total_allow => l_fnar.fm_s_total_allow,
826 x_fm_s_cal_avail_income => l_fnar.fm_s_cal_avail_income,
827 x_fm_s_avail_income => l_fnar.fm_s_avail_income,
828 x_fm_s_cash_bank_accounts => l_fnar.fm_s_cash_bank_accounts,
829 x_fm_s_ot_rl_est_inv_equity => l_fnar.fm_s_ot_rl_est_inv_equity,
830 x_fm_s_adj_bus_farm_worth => l_fnar.fm_s_adj_bus_farm_worth,
831 x_fm_s_trusts => l_fnar.fm_s_trusts,
832 x_fm_s_net_worth => l_fnar.fm_s_net_worth,
833 x_fm_s_asset_prot_allow => l_fnar.fm_s_asset_prot_allow,
834 x_fm_s_disc_net_worth => l_fnar.fm_s_disc_net_worth,
835 x_fm_s_total_cont => l_fnar.fm_s_total_cont,
836 x_fm_s_num_in_coll => l_fnar.fm_s_num_in_coll,
837 x_fm_s_cont_for_stu => l_fnar.fm_s_cont_for_stu,
838 x_fm_s_cont_from_income => l_fnar.fm_s_cont_from_income,
839 x_fm_s_cont_from_assets => l_fnar.fm_s_cont_from_assets,
840 x_im_inst_resident_ind => l_fnar.im_inst_resident_ind,
841 x_institutional_1_budget_name => l_fnar.institutional_1_budget_name,
842 x_im_inst_1_budget_duration => l_fnar.im_inst_1_budget_duration,
843 x_im_inst_1_tuition_fees => l_fnar.im_inst_1_tuition_fees,
844 x_im_inst_1_books_supplies => l_fnar.im_inst_1_books_supplies,
845 x_im_inst_1_living_expenses => l_fnar.im_inst_1_living_expenses,
846 x_im_inst_1_tot_expenses => l_fnar.im_inst_1_tot_expenses,
847 x_im_inst_1_tot_stu_cont => l_fnar.im_inst_1_tot_stu_cont,
848 x_im_inst_1_tot_par_cont => l_fnar.im_inst_1_tot_par_cont,
849 x_im_inst_1_tot_family_cont => l_fnar.im_inst_1_tot_family_cont,
850 x_im_inst_1_va_benefits => l_fnar.im_inst_1_va_benefits,
851 x_im_inst_1_ot_cont => l_fnar.im_inst_1_ot_cont,
852 x_im_inst_1_est_financial_need => l_fnar.im_inst_1_est_financial_need,
853 x_institutional_2_budget_name => l_fnar.institutional_2_budget_name,
854 x_im_inst_2_budget_duration => l_fnar.im_inst_2_budget_duration,
855 x_im_inst_2_tuition_fees => l_fnar.im_inst_2_tuition_fees,
856 x_im_inst_2_books_supplies => l_fnar.im_inst_2_books_supplies,
857 x_im_inst_2_living_expenses => l_fnar.im_inst_2_living_expenses,
858 x_im_inst_2_tot_expenses => l_fnar.im_inst_2_tot_expenses,
859 x_im_inst_2_tot_stu_cont => l_fnar.im_inst_2_tot_stu_cont,
860 x_im_inst_2_tot_par_cont => l_fnar.im_inst_2_tot_par_cont,
861 x_im_inst_2_tot_family_cont => l_fnar.im_inst_2_tot_family_cont,
862 x_im_inst_2_va_benefits => l_fnar.im_inst_2_va_benefits,
863 x_im_inst_2_est_financial_need => l_fnar.im_inst_2_est_financial_need,
864 x_institutional_3_budget_name => l_fnar.institutional_3_budget_name,
865 x_im_inst_3_budget_duration => l_fnar.im_inst_3_budget_duration,
866 x_im_inst_3_tuition_fees => l_fnar.im_inst_3_tuition_fees,
867 x_im_inst_3_books_supplies => l_fnar.im_inst_3_books_supplies,
868 x_im_inst_3_living_expenses => l_fnar.im_inst_3_living_expenses,
869 x_im_inst_3_tot_expenses => l_fnar.im_inst_3_tot_expenses,
870 x_im_inst_3_tot_stu_cont => l_fnar.im_inst_3_tot_stu_cont,
871 x_im_inst_3_tot_par_cont => l_fnar.im_inst_3_tot_par_cont,
872 x_im_inst_3_tot_family_cont => l_fnar.im_inst_3_tot_family_cont,
873 x_im_inst_3_va_benefits => l_fnar.im_inst_3_va_benefits,
874 x_im_inst_3_est_financial_need => l_fnar.im_inst_3_est_financial_need,
875 x_fm_inst_1_federal_efc => l_fnar.fm_inst_1_federal_efc,
876 x_fm_inst_1_va_benefits => l_fnar.fm_inst_1_va_benefits,
877 x_fm_inst_1_fed_eligibility => l_fnar.fm_inst_1_fed_eligibility,
878 x_fm_inst_1_pell => l_fnar.fm_inst_1_pell,
879 x_option_par_loss_allow_ind => l_fnar.option_par_loss_allow_ind,
880 x_option_par_tuition_ind => l_fnar.option_par_tuition_ind,
881 x_option_par_home_ind => l_fnar.option_par_home_ind,
882 x_option_par_home_value => l_fnar.option_par_home_value,
883 x_option_par_home_debt => l_fnar.option_par_home_debt,
884 x_option_stu_ira_keogh_ind => l_fnar.option_stu_ira_keogh_ind,
885 x_option_stu_home_ind => l_fnar.option_stu_home_ind,
886 x_option_stu_home_value => l_fnar.option_stu_home_value,
887 x_option_stu_home_debt => l_fnar.option_stu_home_debt,
888 x_option_stu_sum_ay_inc_ind => l_fnar.option_stu_sum_ay_inc_ind,
889 x_option_par_hope_ll_credit => l_fnar.option_par_hope_ll_credit,
890 x_option_stu_hope_ll_credit => l_fnar.option_stu_hope_ll_credit,
891 x_im_parent_1_8_months_bas => l_fnar.im_parent_1_8_months_bas,
892 x_im_p_more_than_9_mth_ba => l_fnar.im_p_more_than_9_mth_ba,
893 x_im_parent_1_8_months_opt => l_fnar.im_parent_1_8_months_opt,
894 x_im_p_more_than_9_mth_op => l_fnar.im_p_more_than_9_mth_op,
895 x_fnar_message_1 => l_fnar.fnar_message_1,
896 x_fnar_message_2 => l_fnar.fnar_message_2,
897 x_fnar_message_3 => l_fnar.fnar_message_3,
898 x_fnar_message_4 => l_fnar.fnar_message_4,
899 x_fnar_message_5 => l_fnar.fnar_message_5,
900 x_fnar_message_6 => l_fnar.fnar_message_6,
901 x_fnar_message_7 => l_fnar.fnar_message_7,
902 x_fnar_message_8 => l_fnar.fnar_message_8,
903 x_fnar_message_9 => l_fnar.fnar_message_9,
904 x_fnar_message_10 => l_fnar.fnar_message_10,
905 x_fnar_message_11 => l_fnar.fnar_message_11,
906 x_fnar_message_12 => l_fnar.fnar_message_12,
907 x_fnar_message_13 => l_fnar.fnar_message_13,
908 x_fnar_message_20 => l_fnar.fnar_message_20,
909 x_fnar_message_21 => l_fnar.fnar_message_21,
910 x_fnar_message_22 => l_fnar.fnar_message_22,
911 x_fnar_message_23 => l_fnar.fnar_message_23,
912 x_fnar_message_24 => l_fnar.fnar_message_24,
913 x_fnar_message_25 => l_fnar.fnar_message_25,
914 x_fnar_message_26 => l_fnar.fnar_message_26,
915 x_fnar_message_27 => l_fnar.fnar_message_27,
916 x_fnar_message_30 => l_fnar.fnar_message_30,
917 x_fnar_message_31 => l_fnar.fnar_message_31,
918 x_fnar_message_32 => l_fnar.fnar_message_32,
919 x_fnar_message_33 => l_fnar.fnar_message_33,
920 x_fnar_message_34 => l_fnar.fnar_message_34,
921 x_fnar_message_35 => l_fnar.fnar_message_35,
922 x_fnar_message_36 => l_fnar.fnar_message_36,
923 x_fnar_message_37 => l_fnar.fnar_message_37,
924 x_fnar_message_38 => l_fnar.fnar_message_38,
925 x_fnar_message_39 => l_fnar.fnar_message_39,
926 x_fnar_message_45 => l_fnar.fnar_message_45,
927 x_fnar_message_46 => l_fnar.fnar_message_46,
928 x_fnar_message_47 => l_fnar.fnar_message_47,
929 x_fnar_message_48 => l_fnar.fnar_message_48,
930 x_fnar_message_50 => l_fnar.fnar_message_50,
931 x_fnar_message_51 => l_fnar.fnar_message_51,
932 x_fnar_message_52 => l_fnar.fnar_message_52,
933 x_fnar_message_53 => l_fnar.fnar_message_53,
934 x_fnar_message_56 => l_fnar.fnar_message_56,
935 x_fnar_message_57 => l_fnar.fnar_message_57,
936 x_fnar_message_58 => l_fnar.fnar_message_58,
937 x_fnar_message_59 => l_fnar.fnar_message_59,
938 x_fnar_message_60 => l_fnar.fnar_message_60,
939 x_fnar_message_61 => l_fnar.fnar_message_61,
940 x_fnar_message_62 => l_fnar.fnar_message_62,
941 x_fnar_message_63 => l_fnar.fnar_message_63,
942 x_fnar_message_64 => l_fnar.fnar_message_64,
943 x_fnar_message_65 => l_fnar.fnar_message_65,
944 x_fnar_message_71 => l_fnar.fnar_message_71,
945 x_fnar_message_72 => l_fnar.fnar_message_72,
946 x_fnar_message_73 => l_fnar.fnar_message_73,
947 x_fnar_message_74 => l_fnar.fnar_message_74,
948 x_fnar_message_75 => l_fnar.fnar_message_75,
949 x_fnar_message_76 => l_fnar.fnar_message_76,
950 x_fnar_message_77 => l_fnar.fnar_message_77,
951 x_fnar_message_78 => l_fnar.fnar_message_78,
952 x_fnar_mesg_10_stu_fam_mem => l_fnar.fnar_mesg_10_stu_fam_mem,
953 x_fnar_mesg_11_stu_no_in_coll => l_fnar.fnar_mesg_11_stu_no_in_coll,
954 x_fnar_mesg_24_stu_avail_inc => l_fnar.fnar_mesg_24_stu_avail_inc,
955 x_fnar_mesg_26_stu_taxes => l_fnar.fnar_mesg_26_stu_taxes,
956 x_fnar_mesg_33_stu_home_value => l_fnar.fnar_mesg_33_stu_home_value,
957 x_fnar_mesg_34_stu_home_value => l_fnar.fnar_mesg_34_stu_home_value,
958 x_fnar_mesg_34_stu_home_equity => l_fnar.fnar_mesg_34_stu_home_equity,
959 x_fnar_mesg_35_stu_home_value => l_fnar.fnar_mesg_35_stu_home_value,
960 x_fnar_mesg_35_stu_home_equity => l_fnar.fnar_mesg_35_stu_home_equity,
961 x_fnar_mesg_36_stu_home_equity => l_fnar.fnar_mesg_36_stu_home_equity,
962 x_fnar_mesg_48_par_fam_mem => l_fnar.fnar_mesg_48_par_fam_mem,
963 x_fnar_mesg_49_par_no_in_coll => l_fnar.fnar_mesg_49_par_no_in_coll,
964 x_fnar_mesg_56_par_agi => l_fnar.fnar_mesg_56_par_agi,
965 x_fnar_mesg_62_par_taxes => l_fnar.fnar_mesg_62_par_taxes,
966 x_fnar_mesg_73_par_home_value => l_fnar.fnar_mesg_73_par_home_value,
967 x_fnar_mesg_74_par_home_value => l_fnar.fnar_mesg_74_par_home_value,
968 x_fnar_mesg_74_par_home_equity => l_fnar.fnar_mesg_74_par_home_equity,
969 x_fnar_mesg_75_par_home_value => l_fnar.fnar_mesg_75_par_home_value,
970 x_fnar_mesg_75_par_home_equity => l_fnar.fnar_mesg_75_par_home_equity,
971 x_fnar_mesg_76_par_home_equity => l_fnar.fnar_mesg_76_par_home_equity,
972 x_assumption_message_1 => l_fnar.assumption_message_1,
973 x_assumption_message_2 => l_fnar.assumption_message_2,
974 x_assumption_message_3 => l_fnar.assumption_message_3,
975 x_assumption_message_4 => l_fnar.assumption_message_4,
976 x_assumption_message_5 => l_fnar.assumption_message_5,
977 x_assumption_message_6 => l_fnar.assumption_message_6,
978 x_record_mark => l_fnar.record_mark,
979 x_mode => 'R',
980 x_fnar_message_55 => l_fnar.fnar_message_55,
981 x_fnar_message_49 => l_fnar.fnar_message_49,
982 x_opt_par_cola_adj_ind => l_fnar.option_par_cola_adj_ind,
983 x_opt_par_stu_fa_assets_ind => l_fnar.option_par_stu_fa_assets_ind,
984 x_opt_par_ipt_assets_ind => l_fnar.option_par_ipt_assets_ind,
985 x_opt_stu_ipt_assets_ind => l_fnar.option_stu_ipt_assets_ind,
986 x_opt_par_cola_adj_value => l_fnar.option_par_cola_adj_value,
987 x_legacy_record_flag => l_fnar.legacy_record_flag,
988 x_opt_ind_stu_ipt_assets_flag => l_fnar.option_ind_stu_ipt_assets_flag,
989 x_cust_parent_cont_adj_num => l_fnar.cust_parent_cont_adj_num,
990 x_custodial_parent_num => l_fnar.custodial_parent_num,
991 x_cust_par_base_prcnt_inc_amt => l_fnar.cust_par_base_prcnt_inc_amt,
992 x_cust_par_base_cont_inc_amt => l_fnar.cust_par_base_cont_inc_amt,
993 x_cust_par_base_cont_ast_amt => l_fnar.cust_par_base_cont_ast_amt,
994 x_cust_par_base_tot_cont_amt => l_fnar.cust_par_base_tot_cont_amt,
995 x_cust_par_opt_prcnt_inc_amt => l_fnar.cust_par_opt_prcnt_inc_amt,
996 x_cust_par_opt_cont_inc_amt => l_fnar.cust_par_opt_cont_inc_amt,
997 x_cust_par_opt_cont_ast_amt => l_fnar.cust_par_opt_cont_ast_amt,
998 x_cust_par_opt_tot_cont_amt => l_fnar.cust_par_opt_cont_ast_amt,
999 x_parents_email_txt => l_fnar.parents_email_txt,
1000 x_parent_1_birth_date => l_fnar.parent_1_birth_date,
1001 x_parent_2_birth_date => l_fnar.parent_2_birth_date
1002 );
1003 END IF;
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 ROLLBACK TO SP_PROFILE_ID;
1008 fnd_message.set_name('IGF','IGF_AP_PROF_UPD_FAIL');
1009 fnd_file.put_line(fnd_file.log,g_tab_2 || fnd_message.get);
1010 RAISE SKIP_PROFILE_RECORD;
1011 END; -- End of PROFILE Update block
1012
1013 fnd_message.set_name('IGF','IGF_AP_PROF_EFC_CALC_SUCCESS');
1014 fnd_file.put_line(fnd_file.log,g_tab_2 || fnd_message.get);
1015 fnd_file.new_line(fnd_file.log,1);
1016 g_success := g_success + 1;
1017
1018 EXCEPTION
1019 WHEN SKIP_PROFILE_RECORD THEN
1020 g_error := g_error + 1;
1021 END; -- End of PROFILE Prcoessing block
1022
1023 -- Get the Next PROFILE Record
1024 FETCH c_all_profiles INTO l_profile;
1025
1026 END LOOP;
1027
1028 END IF;
1029
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 igs_ge_msg_stack.add;
1033 app_exception.raise_exception;
1034 END calculate_efc;
1035
1036
1037 PROCEDURE main(
1038 errbuf OUT NOCOPY VARCHAR2,
1039 retcode OUT NOCOPY NUMBER,
1040 p_award_year IN VARCHAR2,
1041 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1042 p_persid_grp IN igs_pe_persid_group_all.group_id%TYPE
1043 ) AS
1044 ------------------------------------------------------------------
1045 --Created by : veramach, Oracle India
1046 --Date created: 08-OCT-2003
1047 --
1048 --Purpose: This is the main procedure invoked when the concurrent job is called
1049 --
1050 --
1051 --Known limitations/enhancements and/or remarks:
1052 --
1053 --Change History:
1054 -- Who When What
1055 -- ridas 07-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
1056 -- tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1057 -------------------------------------------------------------------
1058 lv_status VARCHAR2(1);
1059 l_list VARCHAR2(32767);
1060
1061 l_processing_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' ';
1062 l_pers_number_log igf_lookups_view.meaning%TYPE DEFAULT igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PERSON_NUMBER') || ' ';
1063
1064 -- Get base_ids from person id group
1065 CURSOR c_base_id(
1066 cp_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
1067 cp_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
1068 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE
1069 ) IS
1070 SELECT base_id
1071 FROM igf_ap_fa_base_rec_all
1072 WHERE ci_sequence_number = cp_seq_number
1073 AND ci_cal_type = cp_cal_type
1074 AND person_id = cp_person_id;
1075
1076 l_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE;
1077 l_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
1078
1079 -- Get all base ids in a award year
1080 CURSOR c_all_base_id(
1081 cp_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
1082 cp_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE
1083 ) IS
1084 SELECT base_id
1085 FROM igf_ap_fa_base_rec_all
1086 WHERE ci_sequence_number = cp_seq_number
1087 AND ci_cal_type = cp_cal_type;
1088
1089 TYPE cur_person_id_type IS REF CURSOR;
1090 lc_person_id cur_person_id_type;
1091
1092 l_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
1093
1094 TYPE base_idRefCur IS REF CURSOR;
1095 lc_base_id base_idRefCur;
1096 lbase igf_ap_fa_base_rec_all.base_id%TYPE;
1097
1098 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1099
1100 BEGIN
1101 igf_aw_gen.set_org_id(NULL);
1102 --find cal type and sequence number
1103 l_cal_type := TRIM(SUBSTR(p_award_year,1,10));
1104 l_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
1105 errbuf := NULL;
1106 retcode := 0;
1107
1108 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1109 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','l_cal_type:'||l_cal_type);
1110 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','l_seq_number:'||l_seq_number);
1111 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','p_base_id:'||p_base_id);
1112 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','p_persid_grp:'||p_persid_grp);
1113 END IF;
1114 --Start Logging the parameters
1115 log_parameters(l_cal_type,l_seq_number,p_base_id,p_persid_grp);
1116
1117 IF p_base_id IS NOT NULL AND p_persid_grp IS NOT NULL THEN
1118 --Error.Cant have both parameters as not null
1119 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1120 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','Cant specify both base_id and persid_grp.exiting');
1121 END IF;
1122 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
1123 fnd_file.put_line(fnd_file.log,fnd_message.get);
1124 retcode := 2;
1125 RETURN;
1126 END IF;
1127
1128 IF NOT igf_aw_gen_004.is_inas_integrated THEN
1129 --Error.INAS has to be integrated
1130 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1131 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','INAS not integrated');
1132 END IF;
1133 fnd_message.set_name('IGF','IGF_AP_INAS_NOT_ITEGRATED');
1134 fnd_file.put_line(fnd_file.log,fnd_message.get);
1135 retcode := 2;
1136 RETURN;
1137 END IF;
1138
1139
1140 IF p_base_id IS NOT NULL THEN
1141
1142 --log a message saying processing person number
1143 OPEN c_person_number(p_base_id);
1144 FETCH c_person_number INTO l_person_number;
1145 CLOSE c_person_number;
1146
1147 fnd_file.put_line(fnd_file.log,l_processing_log || l_pers_number_log || l_person_number);
1148
1149 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1150 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','calling calculate_efc with base_id:'||p_base_id);
1151 END IF;
1152 --base_id specified. so calculate EFC for all profiles of the current base_id
1153 calculate_efc(l_cal_type,l_seq_number,p_base_id);
1154
1155 ELSIF p_persid_grp IS NOT NULL THEN
1156 --person_id_group specified. So, calculate EFC for all PROFILEs of all persons in the group
1157 --get the list of persons in the person id group
1158
1159 --Bug #5021084. Added new parameter 'lv_group_type'
1160 l_list := igf_ap_ss_pkg.get_pid(p_persid_grp,lv_status,lv_group_type);
1161
1162 --Bug #5021084. Passing Group ID if the group type is STATIC.
1163 IF lv_group_type = 'STATIC' THEN
1164 OPEN lc_base_id FOR ' SELECT base_id FROM igf_ap_fa_base_rec_all WHERE ci_cal_type = :p_ci_cal_type AND ci_sequence_number = :p_ci_sequence_number AND person_id IN (' || l_list || ') ' USING l_cal_type, l_seq_number, p_persid_grp;
1165 ELSIF lv_group_type = 'DYNAMIC' THEN
1166 OPEN lc_base_id FOR ' SELECT base_id FROM igf_ap_fa_base_rec_all WHERE ci_cal_type = :p_ci_cal_type AND ci_sequence_number = :p_ci_sequence_number AND person_id IN (' || l_list || ') ' USING l_cal_type, l_seq_number;
1167 END IF;
1168
1169 FETCH lc_base_id INTO lbase;
1170 IF lc_base_id%FOUND THEN
1171 WHILE lc_base_id%FOUND
1172 LOOP
1173 --calculate efc
1174
1175 --log a message saying processing person number
1176 OPEN c_person_number(lbase);
1177 FETCH c_person_number INTO l_person_number;
1178 CLOSE c_person_number;
1179
1180 fnd_file.put_line(fnd_file.log,l_processing_log || l_pers_number_log || l_person_number);
1181 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1182 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','calling calculate_efc with base_id:'||lbase);
1183 END IF;
1184
1185 calculate_efc(l_cal_type,l_seq_number,lbase);
1186
1187 FETCH lc_base_id INTO lbase;
1188 END LOOP;
1189 ELSE
1190 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
1191 fnd_file.put_line(fnd_file.log,fnd_message.get);
1192 END IF;
1193 ELSIF p_base_id IS NULL AND p_persid_grp IS NULL THEN
1194 --both person id and person id group is null
1195 --calculate efc for all persons in the award year
1196
1197 FOR l_all_base_id IN c_all_base_id(l_seq_number,l_cal_type)
1198 LOOP
1199
1200 --log a message saying processing person number
1201 OPEN c_person_number(l_all_base_id.base_id);
1202 FETCH c_person_number INTO l_person_number;
1203 CLOSE c_person_number;
1204
1205 fnd_file.put_line(fnd_file.log,l_processing_log || l_pers_number_log || l_person_number);
1206 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1207 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_calc_im_efc.main.debug','calling calculate_efc with base_id:'||l_all_base_id.base_id);
1208 END IF;
1209 --calculate efc
1210 calculate_efc(l_cal_type,l_seq_number,l_all_base_id.base_id);
1211
1212 END LOOP;
1213
1214 END IF;
1215
1216 g_total := g_success + g_error;
1217
1218 fnd_file.put_line(fnd_file.output,' ');
1219 fnd_file.put_line(fnd_file.output, RPAD('-',50,'-'));
1220 fnd_file.put_line(fnd_file.output,' ');
1221 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
1222 fnd_file.put_line(fnd_file.output,RPAD(fnd_message.get || ' ',40) || g_total);
1223 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_COMPLETED');
1224 fnd_file.put_line(fnd_file.output,RPAD(fnd_message.get || ' ',40) || g_success);
1225 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_FAILED');
1226 fnd_file.put_line(fnd_file.output,RPAD(fnd_message.get || ' : ',40) || g_error);
1227 fnd_file.put_line(fnd_file.output,' ');
1228 fnd_file.put_line(fnd_file.output, RPAD('-',50,'-'));
1229 fnd_file.put_line(fnd_file.output,' ');
1230
1231 fnd_file.put_line(fnd_file.log,' ');
1232 fnd_file.put_line(fnd_file.log, RPAD('-',50,'-'));
1233 fnd_file.put_line(fnd_file.log,' ');
1234 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
1235 fnd_file.put_line(fnd_file.log,RPAD(fnd_message.get || ' ',40) || g_total);
1236 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_COMPLETED');
1237 fnd_file.put_line(fnd_file.log,RPAD(fnd_message.get || ' ',40) || g_success);
1238 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_FAILED');
1239 fnd_file.put_line(fnd_file.log,RPAD(fnd_message.get || ' : ',40) || g_error);
1240 fnd_file.put_line(fnd_file.log,' ');
1241 fnd_file.put_line(fnd_file.log, RPAD('-',50,'-'));
1242 fnd_file.put_line(fnd_file.log,' ');
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 retcode := 2;
1247 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1248 fnd_message.set_token('NAME','IGF_AP_CALC_IM_EFC.MAIN '||SQLERRM);
1249 errbuf := fnd_message.get;
1250 igs_ge_msg_stack.conc_exception_hndl;
1251 END main;
1252
1253 END igf_ap_calc_im_efc;