[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_MK_PROF_ACT_PKG
Source
1 PACKAGE BODY igf_ap_mk_prof_act_pkg AS
2 /* $Header: IGFAP42B.pls 120.2 2006/01/17 02:38:20 tsailaja noship $ */
3
4 g_log_tab_index NUMBER := 0;
5
6 TYPE log_record IS RECORD
7 ( person_number VARCHAR2(30),
8 message_text VARCHAR2(500));
9
10 -- The PL/SQL table for storing the log messages
11 TYPE LogTab IS TABLE OF log_record
12 index by binary_integer;
13
14 g_log_tab LogTab;
15
16 -- The PL/SQL table for storing the duplicate person number
17 TYPE PerTab IS TABLE OF igf_ap_li_css_act_ints.person_number%TYPE
18 index by binary_integer;
19
20 g_per_tab PerTab;
21
22 PROCEDURE lg_make_active_profile ( errbuf OUT NOCOPY VARCHAR2,
23 retcode OUT NOCOPY NUMBER,
24 p_award_year IN VARCHAR2,
25 p_batch_id IN NUMBER,
26 p_del_ind IN VARCHAR2
27 )
28 IS
29 /*
30 || Created By : bkkumar
31 || Created On : 26-MAY-2003
32 || Purpose : Main process makes the Profile records active based on the data in the
33 || Legacy Make Active Profile Interface Table .
34 || Known limitations, enhancements or remarks :
35 || Change History :
36 || Who When What
37 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
38 || (reverse chronological order - newest change first)
39 */
40 l_proc_item_str VARCHAR2(50) := NULL;
41 l_message_str VARCHAR2(800) := NULL;
42 l_terminate_flag BOOLEAN := FALSE;
43 l_error_flag BOOLEAN := FALSE;
44 l_error VARCHAR2(80);
45 lv_row_id VARCHAR2(80) := NULL;
46 lv_person_id igs_pe_hz_parties.party_id%TYPE := NULL;
47 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
48 l_person_skip_flag BOOLEAN := FALSE;
49 l_success_record_cnt NUMBER := 0;
50 l_error_record_cnt NUMBER := 0;
51 l_todo_flag BOOLEAN := FALSE;
52 l_chk_profile VARCHAR2(1) := 'N';
53 l_chk_batch VARCHAR2(1) := 'N';
54 l_index NUMBER := 1;
55 l_total_record_cnt NUMBER := 0;
56 l_process_flag BOOLEAN := FALSE;
57 l_debug_str VARCHAR2(800) := NULL;
58
59 l_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ;
60 l_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
61
62
63
64 -- Cursor for getting the context award year details
65 CURSOR c_get_status(cp_cal_type VARCHAR2,
66 cp_seq_number NUMBER)
67 IS
68 SELECT sys_award_year,
69 batch_year,
70 award_year_status_code,
71 css_academic_year
72 FROM igf_ap_batch_aw_map
73 WHERE ci_cal_type = cp_cal_type
74 AND ci_sequence_number = cp_seq_number;
75
76 l_get_status c_get_status%ROWTYPE;
77
78 CURSOR c_get_alternate_code(cp_cal_type VARCHAR2,
79 cp_seq_number NUMBER)
80 IS
81 SELECT alternate_code
82 FROM igs_ca_inst
83 WHERE cal_type = cp_cal_type
84 AND sequence_number = cp_seq_number;
85
86 l_get_alternate_code c_get_alternate_code%ROWTYPE;
87
88 CURSOR c_get_records(cp_alternate_code VARCHAR2,
89 cp_batch_id NUMBER)
90 IS
91 SELECT A.batch_num batch_num,
92 A.csaint_id csaint_id,
93 A.ci_alternate_code ci_alternate_code,
94 A.person_number person_number,
95 A.css_id_number_txt css_id_number_txt,
96 A.academic_year_txt academic_year_txt,
97 A.stu_record_type stu_record_type,
98 A.import_status_type import_status_type,
99 A.ROWID ROW_ID
100 FROM igf_ap_li_css_act_ints A
101 WHERE A.ci_alternate_code = cp_alternate_code
102 AND A.batch_num = cp_batch_id
103 AND A.import_status_type IN ('U','R')
104 ORDER BY A.person_number;
105
106
107 l_get_records c_get_records%ROWTYPE;
108
109 -- check whether that profile record came through legacy
110 CURSOR c_chk_prof_rec_legacy(cp_base_id NUMBER,
111 cp_academic_year VARCHAR2 )
112 IS
113 SELECT PROF.row_id row_id
114 FROM igf_ap_css_profile PROF
115
116 WHERE PROF.base_id = cp_base_id
117 AND PROF.academic_year = cp_academic_year
118 AND NVL(PROF.legacy_record_flag,'X') <> 'Y'
119 AND rownum = 1;
120
121 l_chk_prof_rec_legacy c_chk_prof_rec_legacy%ROWTYPE;
122
123 CURSOR c_get_dup_person(cp_alternate_code VARCHAR2,
124 cp_batch_id NUMBER)
125 IS
126 SELECT person_number
127 FROM igf_ap_li_css_act_ints
128 WHERE ci_alternate_code = cp_alternate_code
129 AND batch_num = cp_batch_id
130 GROUP BY person_number
131 HAVING COUNT(person_number) > 1;
132
133 l_get_dup_person c_get_dup_person%ROWTYPE;
134
135 CURSOR c_get_efc(cp_cssp_id igf_ap_css_profile.cssp_id%TYPE)
136 IS
137 SELECT fm_inst_1_federal_efc
138 FROM igf_ap_css_fnar
139 WHERE cssp_id = cp_cssp_id;
140
141 l_get_efc c_get_efc%ROWTYPE;
142
143 CURSOR c_get_prof_rec(cp_base_id NUMBER,
144 cp_academic_year VARCHAR2,
145 cp_css_id_number VARCHAR2,
146 cp_rec_type VARCHAR2)
147 IS
148 SELECT ROWID row_id,
149 PROF.cssp_id cssp_id,
150 PROF.org_id org_id,
151 PROF.base_id base_id,
152 PROF.system_record_type system_record_type,
153 PROF.active_profile active_profile,
154 PROF.college_code college_code,
155 PROF.academic_year academic_year,
156 PROF.stu_record_type stu_record_type,
157 PROF.css_id_number css_id_number,
158 PROF.registration_receipt_date registration_receipt_date,
159 PROF.registration_type registration_type,
160 PROF.application_receipt_date application_receipt_date,
161 PROF.application_type application_type,
162 PROF.original_fnar_compute original_fnar_compute,
163 PROF.revision_fnar_compute_date revision_fnar_compute_date,
164 PROF.electronic_extract_date electronic_extract_date,
165 PROF.institutional_reporting_type institutional_reporting_type,
166 PROF.asr_receipt_date asr_receipt_date,
167 PROF.last_name last_name,
168 PROF.first_name first_name,
169 PROF.middle_initial middle_initial,
170 PROF.address_number_and_street address_number_and_street,
171 PROF.city city,
172 PROF.state_mailing state_mailing,
173 PROF.zip_code zip_code,
174 PROF.s_telephone_number s_telephone_number,
175 PROF.s_title s_title,
176 PROF.date_of_birth date_of_birth,
177 PROF.social_security_number social_security_number,
178 PROF.state_legal_residence state_legal_residence,
179 PROF.foreign_address_indicator foreign_address_indicator,
180 PROF.foreign_postal_code foreign_postal_code,
181 PROF.country country,
182 PROF.financial_aid_status financial_aid_status,
183 PROF.year_in_college year_in_college,
184 PROF.marital_status marital_status,
185 PROF.ward_court ward_court,
186 PROF.legal_dependents_other legal_dependents_other,
187 PROF.household_size household_size,
188 PROF.number_in_college number_in_college,
189 PROF.citizenship_status citizenship_status,
190 PROF.citizenship_country citizenship_country,
191 PROF.visa_classification visa_classification,
192 PROF.tax_figures tax_figures,
193 PROF.number_exemptions number_exemptions,
194 PROF.adjusted_gross_inc adjusted_gross_inc,
195 PROF.us_tax_paid us_tax_paid,
196 PROF.itemized_deductions itemized_deductions,
197 PROF.stu_income_work stu_income_work,
198 PROF.spouse_income_work spouse_income_work,
199 PROF.divid_int_inc divid_int_inc,
200 PROF.soc_sec_benefits soc_sec_benefits,
201 PROF.welfare_tanf welfare_tanf,
202 PROF.child_supp_rcvd child_supp_rcvd,
203 PROF.earned_income_credit earned_income_credit,
204 PROF.other_untax_income other_untax_income,
205 PROF.tax_stu_aid tax_stu_aid,
206 PROF.cash_sav_check cash_sav_check,
207 PROF.ira_keogh ira_keogh,
208 PROF.invest_value invest_value,
209 PROF.invest_debt invest_debt,
210 PROF.home_value home_value,
211 PROF.home_debt home_debt,
212 PROF.oth_real_value oth_real_value,
213 PROF.oth_real_debt oth_real_debt,
214 PROF.bus_farm_value bus_farm_value,
215 PROF.bus_farm_debt bus_farm_debt,
216 PROF.live_on_farm live_on_farm,
217 PROF.home_purch_price home_purch_price,
218 PROF.hope_ll_credit hope_ll_credit,
219 PROF.home_purch_year home_purch_year,
220 PROF.trust_amount trust_amount,
221 PROF.trust_avail trust_avail,
222 PROF.trust_estab trust_estab,
223 PROF.child_support_paid child_support_paid,
224 PROF.med_dent_expenses med_dent_expenses,
225 PROF.vet_us vet_us,
226 PROF.vet_ben_amount vet_ben_amount,
227 PROF.vet_ben_months vet_ben_months,
228 PROF.stu_summer_wages stu_summer_wages,
229 PROF.stu_school_yr_wages stu_school_yr_wages,
230 PROF.spouse_summer_wages spouse_summer_wages,
231 PROF.spouse_school_yr_wages spouse_school_yr_wages,
232 PROF.summer_other_tax_inc summer_other_tax_inc,
233 PROF.school_yr_other_tax_inc school_yr_other_tax_inc,
234 PROF.summer_untax_inc summer_untax_inc,
235 PROF.school_yr_untax_inc school_yr_untax_inc,
236 PROF.grants_schol_etc grants_schol_etc,
237 PROF.tuit_benefits tuit_benefits,
238 PROF.cont_parents cont_parents,
239 PROF.cont_relatives cont_relatives,
240 PROF.p_siblings_pre_tuit p_siblings_pre_tuit,
241 PROF.p_student_pre_tuit p_student_pre_tuit,
242 PROF.p_household_size p_household_size,
243 PROF.p_number_in_college p_number_in_college,
244 PROF.p_parents_in_college p_parents_in_college,
245 PROF.p_marital_status p_marital_status,
246 PROF.p_state_legal_residence p_state_legal_residence,
247 PROF.p_natural_par_status p_natural_par_status,
248 PROF.p_child_supp_paid p_child_supp_paid,
249 PROF.p_repay_ed_loans p_repay_ed_loans,
250 PROF.p_med_dent_expenses p_med_dent_expenses,
251 PROF.p_tuit_paid_amount p_tuit_paid_amount,
252 PROF.p_tuit_paid_number p_tuit_paid_number,
253 PROF.p_exp_child_supp_paid p_exp_child_supp_paid,
254 PROF.p_exp_repay_ed_loans p_exp_repay_ed_loans,
255 PROF.p_exp_med_dent_expenses p_exp_med_dent_expenses,
256 PROF.p_exp_tuit_pd_amount p_exp_tuit_pd_amount,
257 PROF.p_exp_tuit_pd_number p_exp_tuit_pd_number,
258 PROF.p_cash_sav_check p_cash_sav_check,
259 PROF.p_month_mortgage_pay p_month_mortgage_pay,
260 PROF.p_invest_value p_invest_value,
261 PROF.p_invest_debt p_invest_debt,
262 PROF.p_home_value p_home_value,
263 PROF.p_home_debt p_home_debt,
264 PROF.p_home_purch_price p_home_purch_price,
265 PROF.p_own_business_farm p_own_business_farm,
266 PROF.p_business_value p_business_value,
267 PROF.p_business_debt p_business_debt,
268 PROF.p_farm_value p_farm_value,
269 PROF.p_farm_debt p_farm_debt,
270 PROF.p_live_on_farm p_live_on_farm,
271 PROF.p_oth_real_estate_value p_oth_real_estate_value,
272 PROF.p_oth_real_estate_debt p_oth_real_estate_debt,
273 PROF.p_oth_real_purch_price p_oth_real_purch_price,
274 PROF.p_siblings_assets p_siblings_assets,
275 PROF.p_home_purch_year p_home_purch_year,
276 PROF.p_oth_real_purch_year p_oth_real_purch_year,
277 PROF.p_prior_agi p_prior_agi,
278 PROF.p_prior_us_tax_paid p_prior_us_tax_paid,
279 PROF.p_prior_item_deductions p_prior_item_deductions,
280 PROF.p_prior_other_untax_inc p_prior_other_untax_inc,
281 PROF.p_tax_figures p_tax_figures,
282 PROF.p_number_exemptions p_number_exemptions,
283 PROF.p_adjusted_gross_inc p_adjusted_gross_inc,
284 PROF.p_wages_sal_tips p_wages_sal_tips,
285 PROF.p_interest_income p_interest_income,
286 PROF.p_dividend_income p_dividend_income,
287 PROF.p_net_inc_bus_farm p_net_inc_bus_farm,
288 PROF.p_other_taxable_income p_other_taxable_income,
289 PROF.p_adj_to_income p_adj_to_income,
290 PROF.p_us_tax_paid p_us_tax_paid,
291 PROF.p_itemized_deductions p_itemized_deductions,
292 PROF.p_father_income_work p_father_income_work,
293 PROF.p_mother_income_work p_mother_income_work,
294 PROF.p_soc_sec_ben p_soc_sec_ben,
295 PROF.p_welfare_tanf p_welfare_tanf,
296 PROF.p_child_supp_rcvd p_child_supp_rcvd,
297 PROF.p_ded_ira_keogh p_ded_ira_keogh,
298 PROF.p_tax_defer_pens_savs p_tax_defer_pens_savs,
299 PROF.p_dep_care_med_spending p_dep_care_med_spending,
300 PROF.p_earned_income_credit p_earned_income_credit,
301 PROF.p_living_allow p_living_allow,
302 PROF.p_tax_exmpt_int p_tax_exmpt_int,
303 PROF.p_foreign_inc_excl p_foreign_inc_excl,
304 PROF.p_other_untax_inc p_other_untax_inc,
305 PROF.p_hope_ll_credit p_hope_ll_credit,
306 PROF.p_yr_separation p_yr_separation,
307 PROF.p_yr_divorce p_yr_divorce,
308 PROF.p_exp_father_inc p_exp_father_inc,
309 PROF.p_exp_mother_inc p_exp_mother_inc,
310 PROF.p_exp_other_tax_inc p_exp_other_tax_inc,
311 PROF.p_exp_other_untax_inc p_exp_other_untax_inc,
312 PROF.line_2_relation line_2_relation,
313 PROF.line_2_attend_college line_2_attend_college,
314 PROF.line_3_relation line_3_relation,
315 PROF.line_3_attend_college line_3_attend_college,
316 PROF.line_4_relation line_4_relation,
317 PROF.line_4_attend_college line_4_attend_college,
318 PROF.line_5_relation line_5_relation,
319 PROF.line_5_attend_college line_5_attend_college,
320 PROF.line_6_relation line_6_relation,
321 PROF.line_6_attend_college line_6_attend_college,
322 PROF.line_7_relation line_7_relation,
323 PROF.line_7_attend_college line_7_attend_college,
324 PROF.line_8_relation line_8_relation,
325 PROF.line_8_attend_college line_8_attend_college,
326 PROF.p_age_father p_age_father,
327 PROF.p_age_mother p_age_mother,
328 PROF.p_div_sep_ind p_div_sep_ind,
329 PROF.b_cont_non_custodial_par b_cont_non_custodial_par,
330 PROF.college_type_2 college_type_2,
331 PROF.college_type_3 college_type_3,
332 PROF.college_type_4 college_type_4,
333 PROF.college_type_5 college_type_5,
334 PROF.college_type_6 college_type_6,
335 PROF.college_type_7 college_type_7,
336 PROF.college_type_8 college_type_8,
337 PROF.school_code_1 school_code_1,
338 PROF.housing_code_1 housing_code_1,
339 PROF.school_code_2 school_code_2,
340 PROF.housing_code_2 housing_code_2,
341 PROF.school_code_3 school_code_3,
342 PROF.housing_code_3 housing_code_3,
343 PROF.school_code_4 school_code_4,
344 PROF.housing_code_4 housing_code_4,
345 PROF.school_code_5 school_code_5,
346 PROF.housing_code_5 housing_code_5,
347 PROF.school_code_6 school_code_6,
348 PROF.housing_code_6 housing_code_6,
349 PROF.school_code_7 school_code_7,
350 PROF.housing_code_7 housing_code_7,
351 PROF.school_code_8 school_code_8,
352 PROF.housing_code_8 housing_code_8,
353 PROF.school_code_9 school_code_9,
354 PROF.housing_code_9 housing_code_9,
355 PROF.school_code_10 school_code_10,
356 PROF.housing_code_10 housing_code_10,
357 PROF.additional_school_code_1 additional_school_code_1,
358 PROF.additional_school_code_2 additional_school_code_2,
359 PROF.additional_school_code_3 additional_school_code_3,
360 PROF.additional_school_code_4 additional_school_code_4,
361 PROF.additional_school_code_5 additional_school_code_5,
362 PROF.additional_school_code_6 additional_school_code_6,
363 PROF.additional_school_code_7 additional_school_code_7,
364 PROF.additional_school_code_8 additional_school_code_8,
365 PROF.additional_school_code_9 additional_school_code_9,
366 PROF.additional_school_code_10 additional_school_code_10,
367 PROF.explanation_spec_circum explanation_spec_circum,
368 PROF.signature_student signature_student,
369 PROF.signature_spouse signature_spouse,
370 PROF.signature_father signature_father,
371 PROF.signature_mother signature_mother,
372 PROF.month_day_completed month_day_completed,
373 PROF.year_completed year_completed,
374 PROF.age_line_2 age_line_2,
375 PROF.age_line_3 age_line_3,
376 PROF.age_line_4 age_line_4,
377 PROF.age_line_5 age_line_5,
378 PROF.age_line_6 age_line_6,
379 PROF.age_line_7 age_line_7,
380 PROF.age_line_8 age_line_8,
381 PROF.a_online_signature a_online_signature,
382 PROF.question_1_number question_1_number,
383 PROF.question_1_size question_1_size,
384 PROF.question_1_answer question_1_answer,
385 PROF.question_2_number question_2_number,
386 PROF.question_2_size question_2_size,
387 PROF.question_2_answer question_2_answer,
388 PROF.question_3_number question_3_number,
389 PROF.question_3_size question_3_size,
390 PROF.question_3_answer question_3_answer,
391 PROF.question_4_number question_4_number,
392 PROF.question_4_size question_4_size,
393 PROF.question_4_answer question_4_answer,
394 PROF.question_5_number question_5_number,
395 PROF.question_5_size question_5_size,
396 PROF.question_5_answer question_5_answer,
397 PROF.question_6_number question_6_number,
398 PROF.question_6_size question_6_size,
399 PROF.question_6_answer question_6_answer,
400 PROF.question_7_number question_7_number,
401 PROF.question_7_size question_7_size,
402 PROF.question_7_answer question_7_answer,
403 PROF.question_8_number question_8_number,
404 PROF.question_8_size question_8_size,
405 PROF.question_8_answer question_8_answer,
406 PROF.question_9_number question_9_number,
407 PROF.question_9_size question_9_size,
408 PROF.question_9_answer question_9_answer,
409 PROF.question_10_number question_10_number,
410 PROF.question_10_size question_10_size,
411 PROF.question_10_answer question_10_answer,
412 PROF.question_11_number question_11_number,
413 PROF.question_11_size question_11_size,
414 PROF.question_11_answer question_11_answer,
415 PROF.question_12_number question_12_number,
416 PROF.question_12_size question_12_size,
417 PROF.question_12_answer question_12_answer,
418 PROF.question_13_number question_13_number,
419 PROF.question_13_size question_13_size,
420 PROF.question_13_answer question_13_answer,
421 PROF.question_14_number question_14_number,
422 PROF.question_14_size question_14_size,
423 PROF.question_14_answer question_14_answer,
424 PROF.question_15_number question_15_number,
425 PROF.question_15_size question_15_size,
426 PROF.question_15_answer question_15_answer,
427 PROF.question_16_number question_16_number,
428 PROF.question_16_size question_16_size,
429 PROF.question_16_answer question_16_answer,
430 PROF.question_17_number question_17_number,
431 PROF.question_17_size question_17_size,
432 PROF.question_17_answer question_17_answer,
433 PROF.question_18_number question_18_number,
434 PROF.question_18_size question_18_size,
435 PROF.question_18_answer question_18_answer,
436 PROF.question_19_number question_19_number,
437 PROF.question_19_size question_19_size,
438 PROF.question_19_answer question_19_answer,
439 PROF.question_20_number question_20_number,
440 PROF.question_20_size question_20_size,
441 PROF.question_20_answer question_20_answer,
442 PROF.question_21_number question_21_number,
443 PROF.question_21_size question_21_size,
444 PROF.question_21_answer question_21_answer,
445 PROF.question_22_number question_22_number,
446 PROF.question_22_size question_22_size,
447 PROF.question_22_answer question_22_answer,
448 PROF.question_23_number question_23_number,
449 PROF.question_23_size question_23_size,
450 PROF.question_23_answer question_23_answer,
451 PROF.question_24_number question_24_number,
452 PROF.question_24_size question_24_size,
453 PROF.question_24_answer question_24_answer,
454 PROF.question_25_number question_25_number,
455 PROF.question_25_size question_25_size,
456 PROF.question_25_answer question_25_answer,
457 PROF.question_26_number question_26_number,
458 PROF.question_26_size question_26_size,
459 PROF.question_26_answer question_26_answer,
460 PROF.question_27_number question_27_number,
461 PROF.question_27_size question_27_size,
462 PROF.question_27_answer question_27_answer,
463 PROF.question_28_number question_28_number,
464 PROF.question_28_size question_28_size,
465 PROF.question_28_answer question_28_answer,
466 PROF.question_29_number question_29_number,
467 PROF.question_29_size question_29_size,
468 PROF.question_29_answer question_29_answer,
469 PROF.question_30_number question_30_number,
470 PROF.questions_30_size questions_30_size,
471 PROF.question_30_answer question_30_answer,
472 PROF.legacy_record_flag legacy_record_flag,
473 PROF.coa_duration_efc_amt,
474 PROF.coa_duration_num,
475 PROF.p_soc_sec_ben_student_amt,
476 PROF.p_tuit_fee_deduct_amt,
477 PROF.stu_lives_with_num,
478 PROF.stu_most_support_from_num,
479 PROF.location_computer_num
480 FROM igf_ap_css_profile_all PROF
481 WHERE PROF.base_id = cp_base_id
482 AND PROF.academic_year = cp_academic_year
483 AND PROF.css_id_number = cp_css_id_number
484 AND PROF.system_record_type = cp_rec_type;
485
486
487 l_get_prof_rec c_get_prof_rec%ROWTYPE;
488
489 CURSOR c_old_act_prof(cp_base_id NUMBER,
490 cp_rec_type VARCHAR2)
491 IS
492 SELECT PROF.row_id row_id
493 FROM igf_ap_css_profile PROF
494 WHERE PROF.base_id = cp_base_id
495 AND PROF.active_profile = 'Y'
496 AND PROF.system_record_type = cp_rec_type;
497
498 l_old_act_prof c_old_act_prof%ROWTYPE;
499
500 BEGIN
501 igf_aw_gen.set_org_id(NULL);
502 errbuf := NULL;
503 retcode := 0;
504 l_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
505 l_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
506
507 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
508 l_chk_profile := igf_ap_gen.check_profile;
509 IF l_chk_profile = 'N' THEN
510 fnd_message.set_name('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
511 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
512 RETURN;
513 END IF;
514
515 l_chk_batch := igf_ap_gen.check_batch(p_batch_id,'PROFILE');
516 IF l_chk_batch = 'N' THEN
517 fnd_message.set_name('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
518 add_log_table_process(NULL,l_error,fnd_message.get);
519 l_terminate_flag := TRUE;
520 END IF;
521 -- this is to get the alternate code
522 l_get_alternate_code := NULL;
523 OPEN c_get_alternate_code(l_cal_type,l_seq_number);
524 FETCH c_get_alternate_code INTO l_get_alternate_code;
525 CLOSE c_get_alternate_code;
526
527 -- this is to check that the award year is valid or not
528 l_get_status := NULL;
529 OPEN c_get_status(l_cal_type,l_seq_number);
530 FETCH c_get_status INTO l_get_status;
531 CLOSE c_get_status;
532
533 IF l_get_status.award_year_status_code NOT IN ('O','LD') THEN
534 fnd_message.set_name('IGF','IGF_AP_LG_INVALID_STAT');
535 fnd_message.set_token('AWARD_STATUS',l_get_status.award_year_status_code);
536 add_log_table_process(NULL,l_error,fnd_message.get);
537 l_terminate_flag := TRUE;
538 END IF;
539 IF l_terminate_flag = TRUE THEN
540 print_log_process(l_get_alternate_code.alternate_code,p_batch_id,p_del_ind);
541 RETURN;
542 END IF;
543
544 OPEN c_get_dup_person(l_get_alternate_code.alternate_code,p_batch_id);
545 LOOP
546 FETCH c_get_dup_person INTO l_get_dup_person;
547 EXIT WHEN c_get_dup_person%NOTFOUND;
548 g_per_tab(l_index) := l_get_dup_person.person_number;
549 l_index := l_index + 1;
550 END LOOP;
551 CLOSE c_get_dup_person;
552
553 -- THE MAIN LOOP STARTS HERE FOR FETCHING THE RECORD FROM THE INTERFACE TABLE
554 -- IF MORE THAN PROFILE RECORD PER STUDENT IS ENTERED THEN THE PERSON IS TO BE SKIPPED ENTIRELY
555 OPEN c_get_records(l_get_alternate_code.alternate_code,p_batch_id);
556 LOOP
557 BEGIN
558 SAVEPOINT sp1;
559 FETCH c_get_records INTO l_get_records;
560 EXIT WHEN c_get_records%NOTFOUND OR c_get_records%NOTFOUND IS NULL;
561 l_debug_str := 'Csaint_id is:' || l_get_records.csaint_id;
562 -- here the check is there to see if the person number is repetitive so skip the person
563 IF NOT check_dup_person(l_get_records.person_number) THEN
564 lv_base_id := NULL;
565 lv_person_id := NULL;
566 igf_ap_gen.check_person(l_get_records.person_number,l_cal_type,l_seq_number,lv_person_id,lv_base_id);
567 IF lv_person_id IS NULL THEN
568 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
569 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
570 l_error_flag := TRUE;
571 ELSE
572 IF lv_base_id IS NULL THEN
573 fnd_message.set_name('IGF','IGF_AP_FABASE_NOT_FOUND');
574 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
575 l_error_flag := TRUE;
576 ELSE
577 l_debug_str := l_debug_str || ' Person and Base ID check passed';
578 -- here the academic year validation is done it shd correspond to the award year
579 IF l_get_records.academic_year_txt <> NVL(l_get_status.css_academic_year,-1) THEN
580 fnd_message.set_name('IGF','IGF_AP_AW_BATCH_NOT_EXISTS');
581 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
582 l_error_flag := TRUE;
583
584 ELSE
585 l_chk_prof_rec_legacy := NULL;
586 OPEN c_chk_prof_rec_legacy(lv_base_id,l_get_records.academic_year_txt);
587 FETCH c_chk_prof_rec_legacy INTO l_chk_prof_rec_legacy;
588 CLOSE c_chk_prof_rec_legacy;
589 IF l_chk_prof_rec_legacy.row_id IS NOT NULL THEN
590 fnd_message.set_name('IGF','IGF_AP_NON_LI_PROF_EXISTS');
591 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
592 l_error_flag := TRUE;
593 ELSE -- THIS MEANS THE THERE IS PROFILE IS IMPORTED BY LEGACY ONLY
594 l_debug_str := l_debug_str || ' Legacy PROFILE check passed';
595 l_get_prof_rec := NULL;
596 OPEN c_get_prof_rec(lv_base_id,l_get_records.academic_year_txt,l_get_records.css_id_number_txt,'ORIGINAL');
597 FETCH c_get_prof_rec INTO l_get_prof_rec;
598 CLOSE c_get_prof_rec;
599 IF l_get_prof_rec.row_id IS NULL THEN
600 fnd_message.set_name('IGF','IGF_AP_NO_PROF_RECS_EXIST');
601 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
602 l_error_flag := TRUE;
603 ELSE
604 l_debug_str := l_debug_str || ' PROFILE record exists check passed';
605 -- HERE GET THE VALUE OF THE EFC FROM THE OTHER PROFILE TABLE IGF_AP_CSS_FNAR
606 l_get_efc := NULL;
607 OPEN c_get_efc(l_get_prof_rec.cssp_id);
608 FETCH c_get_efc INTO l_get_efc;
609 CLOSE c_get_efc;
610 IF l_get_efc.fm_inst_1_federal_efc IS NULL THEN
611 fnd_message.set_name('IGF','IGF_AP_PROF_INVALID_EFC');
612 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
613 l_error_flag := TRUE;
614 ELSE
615 l_debug_str := l_debug_str || ' FM_INST_FEDERAL_EFC check passed';
616 l_old_act_prof := NULL;
617 OPEN c_old_act_prof(lv_base_id,'ORIGINAL');
618 FETCH c_old_act_prof INTO l_old_act_prof;
619 CLOSE c_old_act_prof;
620 IF l_old_act_prof.row_id IS NOT NULL THEN
621 fnd_message.set_name('IGF','IGF_AP_REC_ALREADY_ACT');
622 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
623 l_error_flag := TRUE;
624 ELSE
625 -- update_profile_rec(l_get_prof_rec);
626 igf_ap_css_profile_pkg.update_row (
627 x_mode => 'R',
628 x_rowid => l_get_prof_rec.row_id,
629 x_cssp_id => l_get_prof_rec.cssp_id,
630 x_base_id => l_get_prof_rec.base_id,
631 x_system_record_type => l_get_prof_rec.system_record_type,
632 x_active_profile => 'Y',
633 x_college_code => l_get_prof_rec.college_code,
634 x_academic_year => l_get_prof_rec.academic_year,
635 x_stu_record_type => l_get_prof_rec.stu_record_type,
636 x_css_id_number => l_get_prof_rec.css_id_number,
637 x_registration_receipt_date => l_get_prof_rec.registration_receipt_date,
638 x_registration_type => l_get_prof_rec.registration_type,
639 x_application_receipt_date => l_get_prof_rec.application_receipt_date,
640 x_application_type => l_get_prof_rec.application_type,
641 x_original_fnar_compute => l_get_prof_rec.original_fnar_compute,
642 x_revision_fnar_compute_date => l_get_prof_rec.revision_fnar_compute_date,
643 x_electronic_extract_date => l_get_prof_rec.electronic_extract_date,
644 x_institutional_reporting_type => l_get_prof_rec.institutional_reporting_type,
645 x_asr_receipt_date => l_get_prof_rec.asr_receipt_date,
646 x_last_name => l_get_prof_rec.last_name,
647 x_first_name => l_get_prof_rec.first_name,
648 x_middle_initial => l_get_prof_rec.middle_initial,
649 x_address_number_and_street => l_get_prof_rec.address_number_and_street,
650 x_city => l_get_prof_rec.city,
651 x_state_mailing => l_get_prof_rec.state_mailing,
652 x_zip_code => l_get_prof_rec.zip_code,
653 x_s_telephone_number => l_get_prof_rec.s_telephone_number,
654 x_s_title => l_get_prof_rec.s_title,
655 x_date_of_birth => l_get_prof_rec.date_of_birth,
656 x_social_security_number => l_get_prof_rec.social_security_number,
657 x_state_legal_residence => l_get_prof_rec.state_legal_residence,
658 x_foreign_address_indicator => l_get_prof_rec.foreign_address_indicator,
659 x_foreign_postal_code => l_get_prof_rec.foreign_postal_code,
660 x_country => l_get_prof_rec.country,
661 x_financial_aid_status => l_get_prof_rec.financial_aid_status,
662 x_year_in_college => l_get_prof_rec.year_in_college,
663 x_marital_status => l_get_prof_rec.marital_status,
664 x_ward_court => l_get_prof_rec.ward_court,
665 x_legal_dependents_other => l_get_prof_rec.legal_dependents_other,
666 x_household_size => l_get_prof_rec.household_size,
667 x_number_in_college => l_get_prof_rec.number_in_college,
668 x_citizenship_status => l_get_prof_rec.citizenship_status,
669 x_citizenship_country => l_get_prof_rec.citizenship_country,
670 x_visa_classification => l_get_prof_rec.visa_classification,
671 x_tax_figures => l_get_prof_rec.tax_figures,
672 x_number_exemptions => l_get_prof_rec.number_exemptions,
673 x_adjusted_gross_inc => l_get_prof_rec.adjusted_gross_inc,
674 x_us_tax_paid => l_get_prof_rec.us_tax_paid,
675 x_itemized_deductions => l_get_prof_rec.itemized_deductions,
676 x_stu_income_work => l_get_prof_rec.stu_income_work,
677 x_spouse_income_work => l_get_prof_rec.spouse_income_work,
678 x_divid_int_inc => l_get_prof_rec.divid_int_inc,
679 x_soc_sec_benefits => l_get_prof_rec.soc_sec_benefits,
680 x_welfare_tanf => l_get_prof_rec.welfare_tanf,
681 x_child_supp_rcvd => l_get_prof_rec.child_supp_rcvd,
682 x_earned_income_credit => l_get_prof_rec.earned_income_credit,
683 x_other_untax_income => l_get_prof_rec.other_untax_income,
684 x_tax_stu_aid => l_get_prof_rec.tax_stu_aid,
685 x_cash_sav_check => l_get_prof_rec.cash_sav_check,
686 x_ira_keogh => l_get_prof_rec.ira_keogh,
687 x_invest_value => l_get_prof_rec.invest_value,
688 x_invest_debt => l_get_prof_rec.invest_debt,
689 x_home_value => l_get_prof_rec.home_value,
690 x_home_debt => l_get_prof_rec.home_debt,
691 x_oth_real_value => l_get_prof_rec.oth_real_value,
692 x_oth_real_debt => l_get_prof_rec.oth_real_debt,
693 x_bus_farm_value => l_get_prof_rec.bus_farm_value,
694 x_bus_farm_debt => l_get_prof_rec.bus_farm_debt,
695 x_live_on_farm => l_get_prof_rec.live_on_farm,
696 x_home_purch_price => l_get_prof_rec.home_purch_price,
697 x_hope_ll_credit => l_get_prof_rec.hope_ll_credit,
698 x_home_purch_year => l_get_prof_rec.home_purch_year,
699 x_trust_amount => l_get_prof_rec.trust_amount,
700 x_trust_avail => l_get_prof_rec.trust_avail,
701 x_trust_estab => l_get_prof_rec.trust_estab,
702 x_child_support_paid => l_get_prof_rec.child_support_paid,
703 x_med_dent_expenses => l_get_prof_rec.med_dent_expenses,
704 x_vet_us => l_get_prof_rec.vet_us,
705 x_vet_ben_amount => l_get_prof_rec.vet_ben_amount,
706 x_vet_ben_months => l_get_prof_rec.vet_ben_months,
707 x_stu_summer_wages => l_get_prof_rec.stu_summer_wages,
708 x_stu_school_yr_wages => l_get_prof_rec.stu_school_yr_wages,
709 x_spouse_summer_wages => l_get_prof_rec.spouse_summer_wages,
710 x_spouse_school_yr_wages => l_get_prof_rec.spouse_school_yr_wages,
711 x_summer_other_tax_inc => l_get_prof_rec.summer_other_tax_inc,
712 x_school_yr_other_tax_inc => l_get_prof_rec.school_yr_other_tax_inc,
713 x_summer_untax_inc => l_get_prof_rec.summer_untax_inc,
714 x_school_yr_untax_inc => l_get_prof_rec.school_yr_untax_inc,
715 x_grants_schol_etc => l_get_prof_rec.grants_schol_etc,
716 x_tuit_benefits => l_get_prof_rec.tuit_benefits,
717 x_cont_parents => l_get_prof_rec.cont_parents,
718 x_cont_relatives => l_get_prof_rec.cont_relatives,
719 x_p_siblings_pre_tuit => l_get_prof_rec.p_siblings_pre_tuit,
720 x_p_student_pre_tuit => l_get_prof_rec.p_student_pre_tuit,
721 x_p_household_size => l_get_prof_rec.p_household_size,
722 x_p_number_in_college => l_get_prof_rec.p_number_in_college,
723 x_p_parents_in_college => l_get_prof_rec.p_parents_in_college,
724 x_p_marital_status => l_get_prof_rec.p_marital_status,
725 x_p_state_legal_residence => l_get_prof_rec.p_state_legal_residence,
726 x_p_natural_par_status => l_get_prof_rec.p_natural_par_status,
727 x_p_child_supp_paid => l_get_prof_rec.p_child_supp_paid,
728 x_p_repay_ed_loans => l_get_prof_rec.p_repay_ed_loans,
729 x_p_med_dent_expenses => l_get_prof_rec.p_med_dent_expenses,
730 x_p_tuit_paid_amount => l_get_prof_rec.p_tuit_paid_amount,
731 x_p_tuit_paid_number => l_get_prof_rec.p_tuit_paid_number,
732 x_p_exp_child_supp_paid => l_get_prof_rec.p_exp_child_supp_paid,
733 x_p_exp_repay_ed_loans => l_get_prof_rec.p_exp_repay_ed_loans,
734 x_p_exp_med_dent_expenses => l_get_prof_rec.p_exp_med_dent_expenses,
735 x_p_exp_tuit_pd_amount => l_get_prof_rec.p_exp_tuit_pd_amount,
736 x_p_exp_tuit_pd_number => l_get_prof_rec.p_exp_tuit_pd_number,
737 x_p_cash_sav_check => l_get_prof_rec.p_cash_sav_check,
738 x_p_month_mortgage_pay => l_get_prof_rec.p_month_mortgage_pay,
739 x_p_invest_value => l_get_prof_rec.p_invest_value,
740 x_p_invest_debt => l_get_prof_rec.p_invest_debt,
741 x_p_home_value => l_get_prof_rec.p_home_value,
742 x_p_home_debt => l_get_prof_rec.p_home_debt,
743 x_p_home_purch_price => l_get_prof_rec.p_home_purch_price,
744 x_p_own_business_farm => l_get_prof_rec.p_own_business_farm,
745 x_p_business_value => l_get_prof_rec.p_business_value,
746 x_p_business_debt => l_get_prof_rec.p_business_debt,
747 x_p_farm_value => l_get_prof_rec.p_farm_value,
748 x_p_farm_debt => l_get_prof_rec.p_farm_debt,
749 x_p_live_on_farm => l_get_prof_rec.p_live_on_farm,
750 x_p_oth_real_estate_value => l_get_prof_rec.p_oth_real_estate_value,
751 x_p_oth_real_estate_debt => l_get_prof_rec.p_oth_real_estate_debt,
752 x_p_oth_real_purch_price => l_get_prof_rec.p_oth_real_purch_price,
753 x_p_siblings_assets => l_get_prof_rec.p_siblings_assets,
754 x_p_home_purch_year => l_get_prof_rec.p_home_purch_year,
755 x_p_oth_real_purch_year => l_get_prof_rec.p_oth_real_purch_year,
756 x_p_prior_agi => l_get_prof_rec.p_prior_agi,
757 x_p_prior_us_tax_paid => l_get_prof_rec.p_prior_us_tax_paid,
758 x_p_prior_item_deductions => l_get_prof_rec.p_prior_item_deductions,
759 x_p_prior_other_untax_inc => l_get_prof_rec.p_prior_other_untax_inc,
760 x_p_tax_figures => l_get_prof_rec.p_tax_figures,
761 x_p_number_exemptions => l_get_prof_rec.p_number_exemptions,
762 x_p_adjusted_gross_inc => l_get_prof_rec.p_adjusted_gross_inc,
763 x_p_wages_sal_tips => l_get_prof_rec.p_wages_sal_tips,
764 x_p_interest_income => l_get_prof_rec.p_interest_income,
765 x_p_dividend_income => l_get_prof_rec.p_dividend_income,
766 x_p_net_inc_bus_farm => l_get_prof_rec.p_net_inc_bus_farm,
767 x_p_other_taxable_income => l_get_prof_rec.p_other_taxable_income,
768 x_p_adj_to_income => l_get_prof_rec.p_adj_to_income,
769 x_p_us_tax_paid => l_get_prof_rec.p_us_tax_paid,
770 x_p_itemized_deductions => l_get_prof_rec.p_itemized_deductions,
771 x_p_father_income_work => l_get_prof_rec.p_father_income_work,
772 x_p_mother_income_work => l_get_prof_rec.p_mother_income_work,
773 x_p_soc_sec_ben => l_get_prof_rec.p_soc_sec_ben,
774 x_p_welfare_tanf => l_get_prof_rec.p_welfare_tanf,
775 x_p_child_supp_rcvd => l_get_prof_rec.p_child_supp_rcvd,
776 x_p_ded_ira_keogh => l_get_prof_rec.p_ded_ira_keogh,
777 x_p_tax_defer_pens_savs => l_get_prof_rec.p_tax_defer_pens_savs,
778 x_p_dep_care_med_spending => l_get_prof_rec.p_dep_care_med_spending,
779 x_p_earned_income_credit => l_get_prof_rec.p_earned_income_credit,
780 x_p_living_allow => l_get_prof_rec.p_living_allow,
781 x_p_tax_exmpt_int => l_get_prof_rec.p_tax_exmpt_int,
782 x_p_foreign_inc_excl => l_get_prof_rec.p_foreign_inc_excl,
783 x_p_other_untax_inc => l_get_prof_rec.p_other_untax_inc,
784 x_p_hope_ll_credit => l_get_prof_rec.p_hope_ll_credit,
785 x_p_yr_separation => l_get_prof_rec.p_yr_separation,
786 x_p_yr_divorce => l_get_prof_rec.p_yr_divorce,
787 x_p_exp_father_inc => l_get_prof_rec.p_exp_father_inc,
788 x_p_exp_mother_inc => l_get_prof_rec.p_exp_mother_inc,
789 x_p_exp_other_tax_inc => l_get_prof_rec.p_exp_other_tax_inc,
790 x_p_exp_other_untax_inc => l_get_prof_rec.p_exp_other_untax_inc,
791 x_line_2_relation => l_get_prof_rec.line_2_relation,
792 x_line_2_attend_college => l_get_prof_rec.line_2_attend_college,
793 x_line_3_relation => l_get_prof_rec.line_3_relation,
794 x_line_3_attend_college => l_get_prof_rec.line_3_attend_college,
795 x_line_4_relation => l_get_prof_rec.line_4_relation,
796 x_line_4_attend_college => l_get_prof_rec.line_4_attend_college,
797 x_line_5_relation => l_get_prof_rec.line_5_relation,
798 x_line_5_attend_college => l_get_prof_rec.line_5_attend_college,
799 x_line_6_relation => l_get_prof_rec.line_6_relation,
800 x_line_6_attend_college => l_get_prof_rec.line_6_attend_college,
801 x_line_7_relation => l_get_prof_rec.line_7_relation,
802 x_line_7_attend_college => l_get_prof_rec.line_7_attend_college,
803 x_line_8_relation => l_get_prof_rec.line_8_relation,
804 x_line_8_attend_college => l_get_prof_rec.line_8_attend_college,
805 x_p_age_father => l_get_prof_rec.p_age_father,
806 x_p_age_mother => l_get_prof_rec.p_age_mother,
807 x_p_div_sep_ind => l_get_prof_rec.p_div_sep_ind,
808 x_b_cont_non_custodial_par => l_get_prof_rec.b_cont_non_custodial_par,
809 x_college_type_2 => l_get_prof_rec.college_type_2,
810 x_college_type_3 => l_get_prof_rec.college_type_3,
811 x_college_type_4 => l_get_prof_rec.college_type_4,
812 x_college_type_5 => l_get_prof_rec.college_type_5,
813 x_college_type_6 => l_get_prof_rec.college_type_6,
814 x_college_type_7 => l_get_prof_rec.college_type_7,
815 x_college_type_8 => l_get_prof_rec.college_type_8,
816 x_school_code_1 => l_get_prof_rec.school_code_1,
817 x_housing_code_1 => l_get_prof_rec.housing_code_1,
818 x_school_code_2 => l_get_prof_rec.school_code_2,
819 x_housing_code_2 => l_get_prof_rec.housing_code_2,
820 x_school_code_3 => l_get_prof_rec.school_code_3,
821 x_housing_code_3 => l_get_prof_rec.housing_code_3,
822 x_school_code_4 => l_get_prof_rec.school_code_4,
823 x_housing_code_4 => l_get_prof_rec.housing_code_4,
824 x_school_code_5 => l_get_prof_rec.school_code_5,
825 x_housing_code_5 => l_get_prof_rec.housing_code_5,
826 x_school_code_6 => l_get_prof_rec.school_code_6,
827 x_housing_code_6 => l_get_prof_rec.housing_code_6,
828 x_school_code_7 => l_get_prof_rec.school_code_7,
829 x_housing_code_7 => l_get_prof_rec.housing_code_7,
830 x_school_code_8 => l_get_prof_rec.school_code_8,
831 x_housing_code_8 => l_get_prof_rec.housing_code_8,
832 x_school_code_9 => l_get_prof_rec.school_code_9,
833 x_housing_code_9 => l_get_prof_rec.housing_code_9,
834 x_school_code_10 => l_get_prof_rec.school_code_10,
835 x_housing_code_10 => l_get_prof_rec.housing_code_10,
836 x_additional_school_code_1 => l_get_prof_rec.additional_school_code_1,
837 x_additional_school_code_2 => l_get_prof_rec.additional_school_code_2,
838 x_additional_school_code_3 => l_get_prof_rec.additional_school_code_3,
839 x_additional_school_code_4 => l_get_prof_rec.additional_school_code_4,
840 x_additional_school_code_5 => l_get_prof_rec.additional_school_code_5,
841 x_additional_school_code_6 => l_get_prof_rec.additional_school_code_6,
842 x_additional_school_code_7 => l_get_prof_rec.additional_school_code_7,
843 x_additional_school_code_8 => l_get_prof_rec.additional_school_code_8,
844 x_additional_school_code_9 => l_get_prof_rec.additional_school_code_9,
845 x_additional_school_code_10 => l_get_prof_rec.additional_school_code_10,
846 x_explanation_spec_circum => l_get_prof_rec.explanation_spec_circum,
847 x_signature_student => l_get_prof_rec.signature_student,
848 x_signature_spouse => l_get_prof_rec.signature_spouse,
849 x_signature_father => l_get_prof_rec.signature_father,
850 x_signature_mother => l_get_prof_rec.signature_mother,
851 x_month_day_completed => l_get_prof_rec.month_day_completed,
852 x_year_completed => l_get_prof_rec.year_completed,
853 x_age_line_2 => l_get_prof_rec.age_line_2,
854 x_age_line_3 => l_get_prof_rec.age_line_3,
855 x_age_line_4 => l_get_prof_rec.age_line_4,
856 x_age_line_5 => l_get_prof_rec.age_line_5,
857 x_age_line_6 => l_get_prof_rec.age_line_6,
858 x_age_line_7 => l_get_prof_rec.age_line_7,
859 x_age_line_8 => l_get_prof_rec.age_line_8,
860 x_a_online_signature => l_get_prof_rec.a_online_signature,
861 x_question_1_number => l_get_prof_rec.question_1_number,
862 x_question_1_size => l_get_prof_rec.question_1_size,
863 x_question_1_answer => l_get_prof_rec.question_1_answer,
864 x_question_2_number => l_get_prof_rec.question_2_number,
865 x_question_2_size => l_get_prof_rec.question_2_size,
866 x_question_2_answer => l_get_prof_rec.question_2_answer,
867 x_question_3_number => l_get_prof_rec.question_3_number,
868 x_question_3_size => l_get_prof_rec.question_3_size,
869 x_question_3_answer => l_get_prof_rec.question_3_answer,
870 x_question_4_number => l_get_prof_rec.question_4_number,
871 x_question_4_size => l_get_prof_rec.question_4_size,
872 x_question_4_answer => l_get_prof_rec.question_4_answer,
873 x_question_5_number => l_get_prof_rec.question_5_number,
874 x_question_5_size => l_get_prof_rec.question_5_size,
875 x_question_5_answer => l_get_prof_rec.question_5_answer,
876 x_question_6_number => l_get_prof_rec.question_6_number,
877 x_question_6_size => l_get_prof_rec.question_6_size,
878 x_question_6_answer => l_get_prof_rec.question_6_answer,
879 x_question_7_number => l_get_prof_rec.question_7_number,
880 x_question_7_size => l_get_prof_rec.question_7_size,
881 x_question_7_answer => l_get_prof_rec.question_7_answer,
882 x_question_8_number => l_get_prof_rec.question_8_number,
883 x_question_8_size => l_get_prof_rec.question_8_size,
884 x_question_8_answer => l_get_prof_rec.question_8_answer,
885 x_question_9_number => l_get_prof_rec.question_9_number,
886 x_question_9_size => l_get_prof_rec.question_9_size,
887 x_question_9_answer => l_get_prof_rec.question_9_answer,
888 x_question_10_number => l_get_prof_rec.question_10_number,
889 x_question_10_size => l_get_prof_rec.question_10_size,
890 x_question_10_answer => l_get_prof_rec.question_10_answer,
891 x_question_11_number => l_get_prof_rec.question_11_number,
892 x_question_11_size => l_get_prof_rec.question_11_size,
893 x_question_11_answer => l_get_prof_rec.question_11_answer,
894 x_question_12_number => l_get_prof_rec.question_12_number,
895 x_question_12_size => l_get_prof_rec.question_12_size,
896 x_question_12_answer => l_get_prof_rec.question_12_answer,
897 x_question_13_number => l_get_prof_rec.question_13_number,
898 x_question_13_size => l_get_prof_rec.question_13_size,
899 x_question_13_answer => l_get_prof_rec.question_13_answer,
900 x_question_14_number => l_get_prof_rec.question_14_number,
901 x_question_14_size => l_get_prof_rec.question_14_size,
902 x_question_14_answer => l_get_prof_rec.question_14_answer,
903 x_question_15_number => l_get_prof_rec.question_15_number,
904 x_question_15_size => l_get_prof_rec.question_15_size,
905 x_question_15_answer => l_get_prof_rec.question_15_answer,
906 x_question_16_number => l_get_prof_rec.question_16_number,
907 x_question_16_size => l_get_prof_rec.question_16_size,
908 x_question_16_answer => l_get_prof_rec.question_16_answer,
909 x_question_17_number => l_get_prof_rec.question_17_number,
910 x_question_17_size => l_get_prof_rec.question_17_size,
911 x_question_17_answer => l_get_prof_rec.question_17_answer,
912 x_question_18_number => l_get_prof_rec.question_18_number,
913 x_question_18_size => l_get_prof_rec.question_18_size,
914 x_question_18_answer => l_get_prof_rec.question_18_answer,
915 x_question_19_number => l_get_prof_rec.question_19_number,
916 x_question_19_size => l_get_prof_rec.question_19_size,
917 x_question_19_answer => l_get_prof_rec.question_19_answer,
918 x_question_20_number => l_get_prof_rec.question_20_number,
919 x_question_20_size => l_get_prof_rec.question_20_size,
920 x_question_20_answer => l_get_prof_rec.question_20_answer,
921 x_question_21_number => l_get_prof_rec.question_21_number,
922 x_question_21_size => l_get_prof_rec.question_21_size,
923 x_question_21_answer => l_get_prof_rec.question_21_answer,
924 x_question_22_number => l_get_prof_rec.question_22_number,
925 x_question_22_size => l_get_prof_rec.question_22_size,
926 x_question_22_answer => l_get_prof_rec.question_22_answer,
927 x_question_23_number => l_get_prof_rec.question_23_number,
928 x_question_23_size => l_get_prof_rec.question_23_size,
929 x_question_23_answer => l_get_prof_rec.question_23_answer,
930 x_question_24_number => l_get_prof_rec.question_24_number,
931 x_question_24_size => l_get_prof_rec.question_24_size,
932 x_question_24_answer => l_get_prof_rec.question_24_answer,
933 x_question_25_number => l_get_prof_rec.question_25_number,
934 x_question_25_size => l_get_prof_rec.question_25_size,
935 x_question_25_answer => l_get_prof_rec.question_25_answer,
936 x_question_26_number => l_get_prof_rec.question_26_number,
937 x_question_26_size => l_get_prof_rec.question_26_size,
938 x_question_26_answer => l_get_prof_rec.question_26_answer,
939 x_question_27_number => l_get_prof_rec.question_27_number,
940 x_question_27_size => l_get_prof_rec.question_27_size,
941 x_question_27_answer => l_get_prof_rec.question_27_answer,
942 x_question_28_number => l_get_prof_rec.question_28_number,
943 x_question_28_size => l_get_prof_rec.question_28_size,
944 x_question_28_answer => l_get_prof_rec.question_28_answer,
945 x_question_29_number => l_get_prof_rec.question_29_number,
946 x_question_29_size => l_get_prof_rec.question_29_size,
947 x_question_29_answer => l_get_prof_rec.question_29_answer,
948 x_question_30_number => l_get_prof_rec.question_30_number,
949 x_questions_30_size => l_get_prof_rec.questions_30_size,
950 x_question_30_answer => l_get_prof_rec.question_30_answer,
951 x_legacy_record_flag => l_get_prof_rec.legacy_record_flag,
952 x_coa_duration_efc_amt => l_get_prof_rec.coa_duration_efc_amt,
953 x_coa_duration_num => l_get_prof_rec.coa_duration_num,
954 x_p_soc_sec_ben_student_amt => l_get_prof_rec.p_soc_sec_ben_student_amt,
955 x_p_tuit_fee_deduct_amt => l_get_prof_rec.p_tuit_fee_deduct_amt,
956 x_stu_lives_with_num => l_get_prof_rec.stu_lives_with_num,
957 x_stu_most_support_from_num => l_get_prof_rec.stu_most_support_from_num,
958 x_location_computer_num => l_get_prof_rec.location_computer_num
959 );
960
961
962 l_success_record_cnt := l_success_record_cnt + 1;
963 END IF;
964 l_debug_str := l_debug_str || ' Old PROFILE check passed';
965
966 END IF; -- for the efc null check
967 END IF;
968 END IF; -- PROFILE IMPORTED BY LEGACY OR NOT
969 END IF; -- THIS IS FOR THE ACADEMIC YEAR VALIDATION
970 END IF; -- BASE ID EXISTS OR NOT
971 END IF; -- PERSON EXISTS OR NOT
972
973 ELSE
974 fnd_message.set_name('IGF','IGF_AP_MK_ACT_PROF_SKIP');
975 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
976 l_error_flag := TRUE;
977 END IF; -- if the person is not duplicate
978 IF l_error_flag = TRUE THEN
979 l_error_flag := FALSE;
980 l_error_record_cnt := l_error_record_cnt + 1;
981 --update the legacy interface table column import_status to 'E'
982 UPDATE igf_ap_li_css_act_ints
983 SET import_status_type = 'E'
984 WHERE ROWID = l_get_records.ROW_ID;
985 ELSE
986 IF p_del_ind = 'Y' THEN
987 DELETE FROM igf_ap_li_css_act_ints
988 WHERE ROWID = l_get_records.ROW_ID;
989 ELSE
990 --update the legacy interface table column import_status to 'I'
991 UPDATE igf_ap_li_css_act_ints
992 SET import_status_type = 'I'
993 WHERE ROWID = l_get_records.ROW_ID;
994 END IF;
995 END IF;
996
997 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
998 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_mk_prof_act_pkg.lg_make_active_profile.debug',l_debug_str);
999 END IF;
1000 l_process_flag := FALSE;
1001 l_debug_str := NULL;
1002 EXCEPTION
1003 WHEN others THEN
1004 l_process_flag := FALSE;
1005 l_debug_str := NULL;
1006 l_error_flag := FALSE;
1007 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1008 fnd_message.set_token('NAME','IGF_AP_MK_PROF_ACT_PKG.LG_MAKE_ACTIVE_PROFILE');
1009 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1010 ROLLBACK TO sp1;
1011 END;
1012 COMMIT;
1013 END LOOP;
1014 CLOSE c_get_records;
1015
1016 IF l_success_record_cnt = 0 AND l_error_record_cnt = 0 THEN
1017 fnd_message.set_name('IGS','IGS_FI_NO_RECORD_AVAILABLE');
1018 add_log_table_process(NULL,l_error,fnd_message.get);
1019 END IF;
1020
1021 -- CALL THE PRINT LOG PROCESS
1022 print_log_process(l_get_alternate_code.alternate_code,p_batch_id,p_del_ind);
1023
1024 l_total_record_cnt := l_success_record_cnt + l_error_record_cnt;
1025 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
1026 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' ' || l_total_record_cnt);
1027 fnd_message.set_name('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC');
1028 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' : ' || l_success_record_cnt);
1029 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_FAILED');
1030 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' : ' || l_error_record_cnt);
1031 EXCEPTION
1032 WHEN others THEN
1033 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1034 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_mk_prof_act_pkg.lg_make_active_profile.exception','Unhandled exception error:'||SQLERRM);
1035 END IF;
1036 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1037 fnd_message.set_token('NAME','IGF_AP_MK_PROF_ACT_PKG.LG_MAKE_ACTIVE_PROFILE');
1038 errbuf := fnd_message.get;
1039 igs_ge_msg_stack.conc_exception_hndl;
1040
1041 END lg_make_active_profile;
1042
1043 FUNCTION check_dup_person(p_person_number IN VARCHAR2)
1044 RETURN BOOLEAN
1045 IS
1046 /*
1047 || Created By : bkkumar
1048 || Created On :
1049 || Purpose : Routine will verify whether the person number is duplicate
1050 || in the interface table.
1051 || Known limitations, enhancements or remarks :
1052 || Change History :
1053 || Who When What
1054 || (reverse chronological order - newest change first)
1055 */
1056 l_count NUMBER(5) := g_per_tab.COUNT;
1057 BEGIN
1058 FOR i IN 1..l_count LOOP
1059 IF g_per_tab(i) = p_person_number THEN
1060 RETURN TRUE;
1061 END IF;
1062 END LOOP;
1063 RETURN FALSE;
1064 END;
1065
1066 PROCEDURE add_log_table_process(
1067 p_person_number IN VARCHAR2,
1068 p_error IN VARCHAR2,
1069 p_message_str IN VARCHAR2
1070 ) IS
1071 /*
1072 || Created By : bkkumar
1073 || Created On : 26-MAY-2003
1074 || Purpose : This process adds a record to the global pl/sql table containing log messages
1075 || Known limitations, enhancements or remarks :
1076 || Change History :
1077 || Who When What
1078 || (reverse chronological order - newest change first)
1079 */
1080
1081 BEGIN
1082
1083 g_log_tab_index := g_log_tab_index + 1;
1084 g_log_tab(g_log_tab_index).person_number := p_person_number;
1085 g_log_tab(g_log_tab_index).message_text := RPAD(p_error,12) || p_message_str;
1086
1087 END add_log_table_process;
1088
1089 PROCEDURE print_log_process(
1090 p_alternate_code IN VARCHAR2,
1091 p_batch_id IN NUMBER,
1092 p_del_ind IN VARCHAR2
1093 ) IS
1094 /*
1095 || Created By : bkkumar
1096 || Created On : 26-MAY-2003
1097 || Purpose : This process gets the records from the pl/sql table and print in the log file
1098 || Known limitations, enhancements or remarks :
1099 || Change History :
1100 || Who When What
1101 || (reverse chronological order - newest change first)
1102 */
1103
1104 l_count NUMBER(5) := g_log_tab.COUNT;
1105 l_old_person igf_ap_li_css_act_ints.person_number%TYPE := '*******';
1106
1107 l_person_number VARCHAR2(80);
1108 l_batch_id VARCHAR2(80);
1109 l_award_yr VARCHAR2(80);
1110 l_batch_desc VARCHAR2(80);
1111 l_yes_no VARCHAR2(10);
1112
1113 CURSOR c_get_batch_desc(cp_batch_num NUMBER) IS
1114 SELECT batch_desc
1115 FROM igf_ap_li_bat_ints
1116 WHERE batch_num = cp_batch_num;
1117
1118 l_get_batch_desc c_get_batch_desc%ROWTYPE;
1119
1120 BEGIN
1121
1122 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
1123 l_batch_id := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','BATCH_ID');
1124 l_award_yr := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','AWARD_YEAR');
1125 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_del_ind);
1126
1127 OPEN c_get_batch_desc(p_batch_id);
1128 FETCH c_get_batch_desc INTO l_get_batch_desc;
1129 CLOSE c_get_batch_desc;
1130 l_batch_desc := l_get_batch_desc.batch_desc ;
1131
1132 -- HERE THE INPUT PARAMETERS ARE TO BE LOGGED TO THE LOG FILE
1133 fnd_message.set_name('IGS','IGS_DA_JOB');
1134 fnd_file.put_line(fnd_file.log,fnd_message.get);
1135 fnd_file.put_line(fnd_file.log,RPAD(l_batch_id,50) || ' : ' || p_batch_id || ' - ' || l_batch_desc);
1136 fnd_file.put_line(fnd_file.log,RPAD(l_award_yr,50) || ' : ' || p_alternate_code);
1137 fnd_message.set_name('IGS','IGS_GE_ASK_DEL_REC');
1138 fnd_file.put_line(fnd_file.log,RPAD(fnd_message.get,50) || ' : ' || l_yes_no);
1139 fnd_file.put_line(fnd_file.log,'------------------------------------------------------------------------------');
1140
1141 FOR i IN 1..l_count LOOP
1142 IF g_log_tab(i).person_number IS NOT NULL THEN
1143 IF l_old_person <> g_log_tab(i).person_number THEN
1144 fnd_file.put_line(fnd_file.log,'---------------------------------------------------------------------------------');
1145 fnd_file.put_line(fnd_file.log,l_person_number || ' : ' || g_log_tab(i).person_number);
1146 END IF;
1147 l_old_person := g_log_tab(i).person_number;
1148 END IF;
1149 fnd_file.put_line(fnd_file.log,g_log_tab(i).message_text);
1150 END LOOP;
1151
1152 END print_log_process;
1153
1154 END igf_ap_mk_prof_act_pkg;