[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_MK_ISIR_ACT_PKG
Source
1 PACKAGE BODY igf_ap_mk_isir_act_pkg AS
2 /* $Header: IGFAP41B.pls 120.2 2006/01/17 02:38:02 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_isir_act_ints.person_number%TYPE
18 index by binary_integer;
19
20 g_per_tab PerTab;
21
22 -- THIS IS THE GLOBAL CURSOR THAT IS USED TO UPDATE THE FA BASE RECORD IF THE TODO ITEMS ARE SUCCESSFULLY IMPORTED
23
24 CURSOR c_baseid_exists(cp_base_id NUMBER)
25 IS
26 SELECT ROWID row_id,
27 base_id,
28 ci_cal_type,
29 person_id,
30 ci_sequence_number,
31 org_id ,
32 bbay ,
33 current_enrolled_hrs ,
34 special_handling,
35 coa_pending,
36 sap_evaluation_date,
37 sap_selected_flag,
38 state_sap_status,
39 verification_process_run,
40 inst_verif_status_date,
41 manual_verif_flag,
42 fed_verif_status,
43 fed_verif_status_date,
44 inst_verif_status,
45 nslds_eligible,
46 ede_correction_batch_id,
47 fa_process_status_date,
48 isir_corr_status,
49 isir_corr_status_date,
50 isir_status,
51 isir_status_date,
52 profile_status,
53 profile_status_date,
54 profile_fc,
55 pell_eligible,
56 award_adjusted,
57 change_pending,
58 coa_code_f,
59 coa_fixed,
60 coa_code_i,
61 coa_f,
62 coa_i ,
63 coa_pell ,
64 disbursement_hold ,
65 enrolment_status ,
66 enrolment_status_date ,
67 fa_process_status ,
68 federal_sap_status ,
69 grade_level ,
70 grade_level_date ,
71 grade_level_type ,
72 inst_sap_status ,
73 last_packaged ,
74 notification_status ,
75 notification_status_date ,
76 packaging_hold ,
77 nslds_data_override_flg ,
78 packaging_status ,
79 prof_judgement_flg ,
80 packaging_status_date ,
81 qa_sampling ,
82 target_group ,
83 todo_code ,
84 total_package_accepted ,
85 total_package_offered ,
86 transcript_available ,
87 tolerance_amount ,
88 transfered ,
89 total_aid ,
90 admstruct_id,
91 admsegment_1 ,
92 admsegment_2 ,
93 admsegment_3 ,
94 admsegment_4 ,
95 admsegment_5 ,
96 admsegment_6,
97 admsegment_7,
98 admsegment_8,
99 admsegment_9,
100 admsegment_10,
101 admsegment_11,
102 admsegment_12,
103 admsegment_13,
104 admsegment_14,
105 admsegment_15,
106 admsegment_16,
107 admsegment_17,
108 admsegment_18,
109 admsegment_19,
110 admsegment_20,
111 packstruct_id,
112 packsegment_1,
113 packsegment_2,
114 packsegment_3,
115 packsegment_4,
116 packsegment_5,
117 packsegment_6,
118 packsegment_7,
119 packsegment_8,
120 packsegment_9,
121 packsegment_10,
122 packsegment_11,
123 packsegment_12,
124 packsegment_13,
125 packsegment_14,
126 packsegment_15,
127 packsegment_16,
128 packsegment_17,
129 packsegment_18,
130 packsegment_19,
131 packsegment_20,
132 miscstruct_id ,
133 miscsegment_1,
134 miscsegment_2 ,
135 miscsegment_3 ,
136 miscsegment_4,
137 miscsegment_5,
138 miscsegment_6,
139 miscsegment_7,
140 miscsegment_8,
141 miscsegment_9,
142 miscsegment_10,
143 miscsegment_11,
144 miscsegment_12,
145 miscsegment_13,
146 miscsegment_14,
147 miscsegment_15,
148 miscsegment_16,
149 miscsegment_17,
150 miscsegment_18,
151 miscsegment_19,
152 miscsegment_20,
153 request_id,
154 program_application_id,
155 program_id ,
156 program_update_date,
157 manual_disb_hold,
158 pell_alt_expense,
159 assoc_org_num , --Modified(added this attribute) by ugummall on 25-SEP-2003 w.r.t FA 126 - Multiple FA Offices
160 award_fmly_contribution_type,
161 isir_locked_by,
162 adnl_unsub_loan_elig_flag,
163 lock_awd_flag,
164 lock_coa_flag
165 FROM igf_ap_fa_base_rec_all FA
166 WHERE FA.base_id = cp_base_id;
167
168 g_baseid_exists c_baseid_exists%ROWTYPE;
169
170
171 PROCEDURE lg_make_active_isir ( errbuf OUT NOCOPY VARCHAR2,
172 retcode OUT NOCOPY NUMBER,
173 p_award_year IN VARCHAR2,
174 p_batch_id IN NUMBER,
175 p_del_ind IN VARCHAR2,
176 p_upd_ind IN VARCHAR2)
177 IS
178 /*
179 || Created By : bkkumar
180 || Created On : 26-MAY-2003
181 || Purpose : Main process makes the ISIR records active based on the data in the
182 || Legacy Make Active ISIR Interface Table .
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
187 || (reverse chronological order - newest change first)
188 */
189 l_proc_item_str VARCHAR2(50) := NULL;
190 l_message_str VARCHAR2(800) := NULL;
191 l_terminate_flag BOOLEAN := FALSE;
192 l_error_flag BOOLEAN := FALSE;
193 l_error VARCHAR2(80);
194 lv_row_id VARCHAR2(80) := NULL;
195 lv_person_id igs_pe_hz_parties.party_id%TYPE := NULL;
196 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
197 l_person_skip_flag BOOLEAN := FALSE;
198 l_success_record_cnt NUMBER := 0;
199 l_error_record_cnt NUMBER := 0;
200 l_todo_flag BOOLEAN := FALSE;
201 l_chk_profile VARCHAR2(1) := 'N';
202 l_chk_batch VARCHAR2(1) := 'N';
203 l_index NUMBER := 1;
204 l_total_record_cnt NUMBER := 0;
205 l_process_flag BOOLEAN := FALSE;
206 l_debug_str VARCHAR2(800) := NULL;
207
208 l_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ;
209 l_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
210
211
212
213 -- Cursor for getting the context award year details
214 CURSOR c_get_status(cp_cal_type VARCHAR2,
215 cp_seq_number NUMBER)
216 IS
217 SELECT sys_award_year,
218 batch_year,
219 award_year_status_code
220 FROM igf_ap_batch_aw_map
221 WHERE ci_cal_type = cp_cal_type
222 AND ci_sequence_number = cp_seq_number;
223
224 l_get_status c_get_status%ROWTYPE;
225
226 CURSOR c_get_alternate_code(cp_cal_type VARCHAR2,
227 cp_seq_number NUMBER)
228 IS
229 SELECT alternate_code
230 FROM igs_ca_inst
231 WHERE cal_type = cp_cal_type
232 AND sequence_number = cp_seq_number;
233
234 l_get_alternate_code c_get_alternate_code%ROWTYPE;
235
236 CURSOR c_get_records(cp_alternate_code VARCHAR2,
237 cp_batch_id NUMBER)
238 IS
239 SELECT A.batch_num batch_num,
240 A.actint_id actint_id,
241 A.ci_alternate_code ci_alternate_code,
242 A.person_number person_number,
243 A.transaction_num_txt transaction_num_txt,
244 A.batch_year_num batch_year_num,
245 A.import_status_type import_status_type,
246 A.ROWID ROW_ID
247 FROM igf_ap_li_isir_act_ints A
248 WHERE A.ci_alternate_code = cp_alternate_code
249 AND A.batch_num = cp_batch_id
250 AND A.import_status_type IN ('U','R')
251 ORDER BY A.person_number;
252
253
254 l_get_records c_get_records%ROWTYPE;
255
256 -- check whether that isir record came through legacy
257 CURSOR c_chk_isir_rec_legacy(cp_base_id NUMBER,
258 cp_batch_year NUMBER )
259 IS
260 SELECT ISIR.isir_id isir_id
261 FROM igf_ap_isir_matched ISIR
262 WHERE ISIR.base_id = cp_base_id
263 AND ISIR.batch_year = cp_batch_year
264 AND NVL(ISIR.legacy_record_flag,'X') <> 'Y'
265 AND rownum = 1;
266
267 l_chk_isir_rec_legacy c_chk_isir_rec_legacy%ROWTYPE;
268
269 CURSOR c_get_dup_person(cp_alternate_code VARCHAR2,
270 cp_batch_id NUMBER)
271 IS
272 SELECT person_number
273 FROM igf_ap_li_isir_act_ints
274 WHERE ci_alternate_code = cp_alternate_code
275 AND batch_num = cp_batch_id
276 AND import_status_type IN ('U','R')
277 GROUP BY person_number
278 HAVING COUNT(person_number) > 1;
279
280 l_get_dup_person c_get_dup_person%ROWTYPE;
281 CURSOR c_get_isir_rec(cp_base_id NUMBER,
282 cp_batch_year NUMBER,
283 cp_transaction_num VARCHAR2,
284 cp_rec_type VARCHAR2)
285 IS
286 SELECT ISIR.row_id row_id,
287 ISIR.isir_id isir_id,
288 ISIR.base_id base_id,
289 ISIR.batch_year batch_year,
290 ISIR.transaction_num transaction_num,
291 ISIR.current_ssn current_ssn,
292 ISIR.ssn_name_change ssn_name_change,
293 ISIR.original_ssn original_ssn,
294 ISIR.orig_name_id orig_name_id,
295 ISIR.last_name last_name,
296 ISIR.first_name first_name,
297 ISIR.middle_initial middle_initial,
298 ISIR.perm_mail_add perm_mail_add,
299 ISIR.perm_city perm_city,
300 ISIR.perm_state perm_state,
301 ISIR.perm_zip_code perm_zip_code,
302 ISIR.date_of_birth date_of_birth,
303 ISIR.phone_number phone_number,
304 ISIR.driver_license_number driver_license_number,
305 ISIR.driver_license_state driver_license_state,
306 ISIR.citizenship_status citizenship_status,
307 ISIR.alien_reg_number alien_reg_number,
308 ISIR.s_marital_status s_marital_status,
309 ISIR.s_marital_status_date s_marital_status_date,
310 ISIR.summ_enrl_status summ_enrl_status,
311 ISIR.fall_enrl_status fall_enrl_status,
312 ISIR.winter_enrl_status winter_enrl_status,
313 ISIR.spring_enrl_status spring_enrl_status,
314 ISIR.summ2_enrl_status summ2_enrl_status,
315 ISIR.fathers_highest_edu_level fathers_highest_edu_level,
316 ISIR.mothers_highest_edu_level mothers_highest_edu_level,
317 ISIR.s_state_legal_residence s_state_legal_residence,
318 ISIR.legal_residence_before_date legal_residence_before_date,
319 ISIR.s_legal_resd_date s_legal_resd_date,
320 ISIR.ss_r_u_male ss_r_u_male,
321 ISIR.selective_service_reg selective_service_reg,
322 ISIR.degree_certification degree_certification,
323 ISIR.grade_level_in_college grade_level_in_college,
324 ISIR.high_school_diploma_ged high_school_diploma_ged,
325 ISIR.first_bachelor_deg_by_date first_bachelor_deg_by_date,
326 ISIR.interest_in_loan interest_in_loan,
327 ISIR.interest_in_stud_employment interest_in_stud_employment,
328 ISIR.drug_offence_conviction drug_offence_conviction,
329 ISIR.s_tax_return_status s_tax_return_status,
330 ISIR.s_type_tax_return s_type_tax_return,
331 ISIR.s_elig_1040ez s_elig_1040ez,
332 ISIR.s_adjusted_gross_income s_adjusted_gross_income,
333 ISIR.s_fed_taxes_paid s_fed_taxes_paid,
334 ISIR.s_exemptions s_exemptions,
335 ISIR.s_income_from_work s_income_from_work,
336 ISIR.spouse_income_from_work spouse_income_from_work,
337 ISIR.s_toa_amt_from_wsa s_toa_amt_from_wsa,
338 ISIR.s_toa_amt_from_wsb s_toa_amt_from_wsb,
339 ISIR.s_toa_amt_from_wsc s_toa_amt_from_wsc,
340 ISIR.s_investment_networth s_investment_networth,
341 ISIR.s_busi_farm_networth s_busi_farm_networth,
342 ISIR.s_cash_savings s_cash_savings,
343 ISIR.va_months va_months,
344 ISIR.va_amount va_amount,
345 ISIR.stud_dob_before_date stud_dob_before_date,
346 ISIR.deg_beyond_bachelor deg_beyond_bachelor,
347 ISIR.s_married s_married,
348 ISIR.s_have_children s_have_children,
349 ISIR.legal_dependents legal_dependents,
350 ISIR.orphan_ward_of_court orphan_ward_of_court,
351 ISIR.s_veteran s_veteran,
352 ISIR.p_marital_status p_marital_status,
353 ISIR.father_ssn father_ssn,
354 ISIR.f_last_name f_last_name,
355 ISIR.mother_ssn mother_ssn,
356 ISIR.m_last_name m_last_name,
357 ISIR.p_num_family_member p_num_family_member,
358 ISIR.p_num_in_college p_num_in_college,
359 ISIR.p_state_legal_residence p_state_legal_residence,
360 ISIR.p_state_legal_res_before_dt p_state_legal_res_before_dt,
361 ISIR.p_legal_res_date p_legal_res_date,
362 ISIR.age_older_parent age_older_parent,
363 ISIR.p_tax_return_status p_tax_return_status,
364 ISIR.p_type_tax_return p_type_tax_return,
365 ISIR.p_elig_1040aez p_elig_1040aez,
366 ISIR.p_adjusted_gross_income p_adjusted_gross_income,
367 ISIR.p_taxes_paid p_taxes_paid,
368 ISIR.p_exemptions p_exemptions,
369 ISIR.f_income_work f_income_work,
370 ISIR.m_income_work m_income_work,
371 ISIR.p_income_wsa p_income_wsa,
372 ISIR.p_income_wsb p_income_wsb,
373 ISIR.p_income_wsc p_income_wsc,
374 ISIR.p_investment_networth p_investment_networth,
375 ISIR.p_business_networth p_business_networth,
376 ISIR.p_cash_saving p_cash_saving,
377 ISIR.s_num_family_members s_num_family_members,
378 ISIR.s_num_in_college s_num_in_college,
379 ISIR.first_college first_college,
380 ISIR.first_house_plan first_house_plan,
381 ISIR.second_college second_college,
382 ISIR.second_house_plan second_house_plan,
383 ISIR.third_college third_college,
384 ISIR.third_house_plan third_house_plan,
385 ISIR.fourth_college fourth_college,
386 ISIR.fourth_house_plan fourth_house_plan,
387 ISIR.fifth_college fifth_college,
388 ISIR.fifth_house_plan fifth_house_plan,
389 ISIR.sixth_college sixth_college,
390 ISIR.sixth_house_plan sixth_house_plan,
391 ISIR.date_app_completed date_app_completed,
392 ISIR.signed_by signed_by,
393 ISIR.preparer_ssn preparer_ssn,
394 ISIR.preparer_emp_id_number preparer_emp_id_number,
395 ISIR.preparer_sign preparer_sign,
396 ISIR.transaction_receipt_date transaction_receipt_date,
397 ISIR.dependency_override_ind dependency_override_ind,
398 ISIR.faa_fedral_schl_code faa_fedral_schl_code,
399 ISIR.faa_adjustment faa_adjustment,
400 ISIR.input_record_type input_record_type,
401 ISIR.serial_number serial_number,
402 ISIR.batch_number batch_number,
403 ISIR.early_analysis_flag early_analysis_flag,
404 ISIR.app_entry_source_code app_entry_source_code,
405 ISIR.eti_destination_code eti_destination_code,
406 ISIR.reject_override_b reject_override_b,
407 ISIR.reject_override_n reject_override_n,
408 ISIR.reject_override_w reject_override_w,
409 ISIR.assum_override_1 assum_override_1,
410 ISIR.assum_override_2 assum_override_2,
411 ISIR.assum_override_3 assum_override_3,
412 ISIR.assum_override_4 assum_override_4,
413 ISIR.assum_override_5 assum_override_5,
414 ISIR.assum_override_6 assum_override_6,
415 ISIR.dependency_status dependency_status,
416 ISIR.s_email_address s_email_address,
417 ISIR.nslds_reason_code nslds_reason_code,
418 ISIR.app_receipt_date app_receipt_date,
419 ISIR.processed_rec_type processed_rec_type,
420 ISIR.hist_correction_for_tran_id hist_correction_for_tran_id,
421 ISIR.system_generated_indicator system_generated_indicator,
422 ISIR.dup_request_indicator dup_request_indicator,
423 ISIR.source_of_correction source_of_correction,
424 ISIR.p_cal_tax_status p_cal_tax_status,
425 ISIR.s_cal_tax_status s_cal_tax_status,
426 ISIR.graduate_flag graduate_flag,
427 ISIR.auto_zero_efc auto_zero_efc,
428 ISIR.efc_change_flag efc_change_flag,
429 ISIR.sarc_flag sarc_flag,
430 ISIR.simplified_need_test simplified_need_test,
431 ISIR.reject_reason_codes reject_reason_codes,
432 ISIR.select_service_match_flag select_service_match_flag,
433 ISIR.select_service_reg_flag select_service_reg_flag,
434 ISIR.ins_match_flag ins_match_flag,
435 ISIR.ins_verification_number ins_verification_number,
436 ISIR.sec_ins_match_flag sec_ins_match_flag,
437 ISIR.sec_ins_ver_number sec_ins_ver_number,
438 ISIR.ssn_match_flag ssn_match_flag,
439 ISIR.ssa_citizenship_flag ssa_citizenship_flag,
440 ISIR.ssn_date_of_death ssn_date_of_death,
441 ISIR.nslds_match_flag nslds_match_flag,
442 ISIR.va_match_flag va_match_flag,
443 ISIR.prisoner_match prisoner_match,
444 ISIR.verification_flag verification_flag,
445 ISIR.subsequent_app_flag subsequent_app_flag,
446 ISIR.app_source_site_code app_source_site_code,
447 ISIR.tran_source_site_code tran_source_site_code,
448 ISIR.drn drn,
449 ISIR.tran_process_date tran_process_date,
450 ISIR.correction_flags correction_flags,
451 ISIR.computer_batch_number computer_batch_number,
452 ISIR.highlight_flags highlight_flags,
453 ISIR.paid_efc paid_efc,
454 ISIR.primary_efc primary_efc,
455 ISIR.secondary_efc secondary_efc,
456 ISIR.fed_pell_grant_efc_type fed_pell_grant_efc_type,
457 ISIR.primary_efc_type primary_efc_type,
458 ISIR.sec_efc_type sec_efc_type,
459 ISIR.primary_alternate_month_1 primary_alternate_month_1,
460 ISIR.primary_alternate_month_2 primary_alternate_month_2,
461 ISIR.primary_alternate_month_3 primary_alternate_month_3,
462 ISIR.primary_alternate_month_4 primary_alternate_month_4,
463 ISIR.primary_alternate_month_5 primary_alternate_month_5,
464 ISIR.primary_alternate_month_6 primary_alternate_month_6,
465 ISIR.primary_alternate_month_7 primary_alternate_month_7,
466 ISIR.primary_alternate_month_8 primary_alternate_month_8,
467 ISIR.primary_alternate_month_10 primary_alternate_month_10,
468 ISIR.primary_alternate_month_11 primary_alternate_month_11,
469 ISIR.primary_alternate_month_12 primary_alternate_month_12,
470 ISIR.sec_alternate_month_1 sec_alternate_month_1,
471 ISIR.sec_alternate_month_2 sec_alternate_month_2,
472 ISIR.sec_alternate_month_3 sec_alternate_month_3,
473 ISIR.sec_alternate_month_4 sec_alternate_month_4,
474 ISIR.sec_alternate_month_5 sec_alternate_month_5,
475 ISIR.sec_alternate_month_6 sec_alternate_month_6,
476 ISIR.sec_alternate_month_7 sec_alternate_month_7,
477 ISIR.sec_alternate_month_8 sec_alternate_month_8,
478 ISIR.sec_alternate_month_10 sec_alternate_month_10,
479 ISIR.sec_alternate_month_11 sec_alternate_month_11,
480 ISIR.sec_alternate_month_12 sec_alternate_month_12,
481 ISIR.total_income total_income,
482 ISIR.allow_total_income allow_total_income,
483 ISIR.state_tax_allow state_tax_allow,
484 ISIR.employment_allow employment_allow,
485 ISIR.income_protection_allow income_protection_allow,
486 ISIR.available_income available_income,
487 ISIR.contribution_from_ai contribution_from_ai,
488 ISIR.discretionary_networth discretionary_networth,
489 ISIR.efc_networth efc_networth,
490 ISIR.asset_protect_allow asset_protect_allow,
491 ISIR.parents_cont_from_assets parents_cont_from_assets,
492 ISIR.adjusted_available_income adjusted_available_income,
493 ISIR.total_student_contribution total_student_contribution,
494 ISIR.total_parent_contribution total_parent_contribution,
495 ISIR.parents_contribution parents_contribution,
496 ISIR.student_total_income student_total_income,
497 ISIR.sati sati,
498 ISIR.sic sic,
499 ISIR.sdnw sdnw,
500 ISIR.sca sca,
501 ISIR.fti fti,
502 ISIR.secti secti,
503 ISIR.secati secati,
504 ISIR.secstx secstx,
505 ISIR.secea secea,
506 ISIR.secipa secipa,
507 ISIR.secai secai,
508 ISIR.seccai seccai,
509 ISIR.secdnw secdnw,
510 ISIR.secnw secnw,
511 ISIR.secapa secapa,
512 ISIR.secpca secpca,
513 ISIR.secaai secaai,
514 ISIR.sectsc sectsc,
515 ISIR.sectpc sectpc,
516 ISIR.secpc secpc,
517 ISIR.secsti secsti,
518 ISIR.secsati secsati,
519 ISIR.secsic secsic,
520 ISIR.secsdnw secsdnw,
521 ISIR.secsca secsca,
522 ISIR.secfti secfti,
523 ISIR.a_citizenship a_citizenship,
524 ISIR.a_student_marital_status a_student_marital_status,
525 ISIR.a_student_agi a_student_agi,
526 ISIR.a_s_us_tax_paid a_s_us_tax_paid,
527 ISIR.a_s_income_work a_s_income_work,
528 ISIR.a_spouse_income_work a_spouse_income_work,
529 ISIR.a_s_total_wsc a_s_total_wsc,
530 ISIR.a_date_of_birth a_date_of_birth,
531 ISIR.a_student_married a_student_married,
532 ISIR.a_have_children a_have_children,
533 ISIR.a_s_have_dependents a_s_have_dependents,
534 ISIR.a_va_status a_va_status,
535 ISIR.a_s_num_in_family a_s_num_in_family,
536 ISIR.a_s_num_in_college a_s_num_in_college,
537 ISIR.a_p_marital_status a_p_marital_status,
538 ISIR.a_father_ssn a_father_ssn,
539 ISIR.a_mother_ssn a_mother_ssn,
540 ISIR.a_parents_num_family a_parents_num_family,
541 ISIR.a_parents_num_college a_parents_num_college,
542 ISIR.a_parents_agi a_parents_agi,
543 ISIR.a_p_us_tax_paid a_p_us_tax_paid,
544 ISIR.a_f_work_income a_f_work_income,
545 ISIR.a_m_work_income a_m_work_income,
546 ISIR.a_p_total_wsc a_p_total_wsc,
547 ISIR.comment_codes comment_codes,
548 ISIR.sar_ack_comm_code sar_ack_comm_code,
549 ISIR.pell_grant_elig_flag pell_grant_elig_flag,
550 ISIR.reprocess_reason_code reprocess_reason_code,
551 ISIR.duplicate_date duplicate_date,
552 ISIR.isir_transaction_type isir_transaction_type,
553 ISIR.fedral_schl_code_indicator fedral_schl_code_indicator,
554 ISIR.multi_school_code_flags multi_school_code_flags,
555 ISIR.dup_ssn_indicator dup_ssn_indicator,
556 ISIR.payment_isir payment_isir,
557 ISIR.receipt_status receipt_status,
558 ISIR.isir_receipt_completed isir_receipt_completed,
559 ISIR.system_record_type system_record_type,
560 ISIR.created_by created_by,
561 ISIR.creation_date creation_date,
562 ISIR.last_updated_by last_updated_by,
563 ISIR.last_update_date last_update_date,
564 ISIR.last_update_login last_update_login,
565 ISIR.request_id request_id,
566 ISIR.program_application_id program_application_id,
567 ISIR.program_id program_id,
568 ISIR.program_update_date program_update_date,
569 ISIR.org_id org_id,
570 ISIR.verif_track_flag verif_track_flag,
571 ISIR.active_isir active_isir,
572 ISIR.fafsa_data_verify_flags fafsa_data_verify_flags,
573 ISIR.reject_override_a reject_override_a,
574 ISIR.reject_override_c reject_override_c,
575 ISIR.parent_marital_status_date parent_marital_status_date,
576 ISIR.legacy_record_flag legacy_record_flag,
577 ISIR.father_first_name_initial_txt,
578 ISIR.father_step_father_birth_date,
579 ISIR.mother_first_name_initial_txt,
580 ISIR.mother_step_mother_birth_date,
581 ISIR.parents_email_address_txt,
582 ISIR.address_change_type,
583 ISIR.cps_pushed_isir_flag,
584 ISIR.electronic_transaction_type,
585 ISIR.sar_c_change_type,
586 ISIR.father_ssn_match_type,
587 ISIR.mother_ssn_match_type,
588 ISIR.reject_override_g_flag,
589 ISIR.dhs_verification_num_txt,
590 ISIR.data_file_name_txt,
591 ISIR.message_class_txt,
592 reject_override_3_flag,
593 ISIR.reject_override_12_flag,
594 ISIR.reject_override_j_flag,
595 ISIR.reject_override_k_flag,
596 ISIR.rejected_status_change_flag,
597 ISIR.verification_selection_flag
598 FROM igf_ap_isir_matched ISIR
599
600 WHERE ISIR.base_id = cp_base_id
601 AND ISIR.batch_year = cp_batch_year
602 AND ISIR.transaction_num = cp_transaction_num
603 AND ISIR.system_record_type = cp_rec_type;
604
605
606 l_get_isir_rec c_get_isir_rec%ROWTYPE;
607
608 CURSOR c_old_act_isir(cp_base_id NUMBER,
609 cp_rec_type VARCHAR2)
610 IS
611 SELECT ISIR.row_id row_id,
612 ISIR.isir_id isir_id,
613 ISIR.base_id base_id,
614 ISIR.batch_year batch_year,
615 ISIR.transaction_num transaction_num,
616 ISIR.current_ssn current_ssn,
617 ISIR.ssn_name_change ssn_name_change,
618 ISIR.original_ssn original_ssn,
619 ISIR.orig_name_id orig_name_id,
620 ISIR.last_name last_name,
621 ISIR.first_name first_name,
622 ISIR.middle_initial middle_initial,
623 ISIR.perm_mail_add perm_mail_add,
624 ISIR.perm_city perm_city,
625 ISIR.perm_state perm_state,
626 ISIR.perm_zip_code perm_zip_code,
627 ISIR.date_of_birth date_of_birth,
628 ISIR.phone_number phone_number,
629 ISIR.driver_license_number driver_license_number,
630 ISIR.driver_license_state driver_license_state,
631 ISIR.citizenship_status citizenship_status,
632 ISIR.alien_reg_number alien_reg_number,
633 ISIR.s_marital_status s_marital_status,
634 ISIR.s_marital_status_date s_marital_status_date,
635 ISIR.summ_enrl_status summ_enrl_status,
636 ISIR.fall_enrl_status fall_enrl_status,
637 ISIR.winter_enrl_status winter_enrl_status,
638 ISIR.spring_enrl_status spring_enrl_status,
639 ISIR.summ2_enrl_status summ2_enrl_status,
640 ISIR.fathers_highest_edu_level fathers_highest_edu_level,
641 ISIR.mothers_highest_edu_level mothers_highest_edu_level,
642 ISIR.s_state_legal_residence s_state_legal_residence,
643 ISIR.legal_residence_before_date legal_residence_before_date,
644 ISIR.s_legal_resd_date s_legal_resd_date,
645 ISIR.ss_r_u_male ss_r_u_male,
646 ISIR.selective_service_reg selective_service_reg,
647 ISIR.degree_certification degree_certification,
648 ISIR.grade_level_in_college grade_level_in_college,
649 ISIR.high_school_diploma_ged high_school_diploma_ged,
650 ISIR.first_bachelor_deg_by_date first_bachelor_deg_by_date,
651 ISIR.interest_in_loan interest_in_loan,
652 ISIR.interest_in_stud_employment interest_in_stud_employment,
653 ISIR.drug_offence_conviction drug_offence_conviction,
654 ISIR.s_tax_return_status s_tax_return_status,
655 ISIR.s_type_tax_return s_type_tax_return,
656 ISIR.s_elig_1040ez s_elig_1040ez,
657 ISIR.s_adjusted_gross_income s_adjusted_gross_income,
658 ISIR.s_fed_taxes_paid s_fed_taxes_paid,
659 ISIR.s_exemptions s_exemptions,
660 ISIR.s_income_from_work s_income_from_work,
661 ISIR.spouse_income_from_work spouse_income_from_work,
662 ISIR.s_toa_amt_from_wsa s_toa_amt_from_wsa,
663 ISIR.s_toa_amt_from_wsb s_toa_amt_from_wsb,
664 ISIR.s_toa_amt_from_wsc s_toa_amt_from_wsc,
665 ISIR.s_investment_networth s_investment_networth,
666 ISIR.s_busi_farm_networth s_busi_farm_networth,
667 ISIR.s_cash_savings s_cash_savings,
668 ISIR.va_months va_months,
669 ISIR.va_amount va_amount,
670 ISIR.stud_dob_before_date stud_dob_before_date,
671 ISIR.deg_beyond_bachelor deg_beyond_bachelor,
672 ISIR.s_married s_married,
673 ISIR.s_have_children s_have_children,
674 ISIR.legal_dependents legal_dependents,
675 ISIR.orphan_ward_of_court orphan_ward_of_court,
676 ISIR.s_veteran s_veteran,
677 ISIR.p_marital_status p_marital_status,
678 ISIR.father_ssn father_ssn,
679 ISIR.f_last_name f_last_name,
680 ISIR.mother_ssn mother_ssn,
681 ISIR.m_last_name m_last_name,
682 ISIR.p_num_family_member p_num_family_member,
683 ISIR.p_num_in_college p_num_in_college,
684 ISIR.p_state_legal_residence p_state_legal_residence,
685 ISIR.p_state_legal_res_before_dt p_state_legal_res_before_dt,
686 ISIR.p_legal_res_date p_legal_res_date,
687 ISIR.age_older_parent age_older_parent,
688 ISIR.p_tax_return_status p_tax_return_status,
689 ISIR.p_type_tax_return p_type_tax_return,
690 ISIR.p_elig_1040aez p_elig_1040aez,
691 ISIR.p_adjusted_gross_income p_adjusted_gross_income,
692 ISIR.p_taxes_paid p_taxes_paid,
693 ISIR.p_exemptions p_exemptions,
694 ISIR.f_income_work f_income_work,
695 ISIR.m_income_work m_income_work,
696 ISIR.p_income_wsa p_income_wsa,
697 ISIR.p_income_wsb p_income_wsb,
698 ISIR.p_income_wsc p_income_wsc,
699 ISIR.p_investment_networth p_investment_networth,
700 ISIR.p_business_networth p_business_networth,
701 ISIR.p_cash_saving p_cash_saving,
702 ISIR.s_num_family_members s_num_family_members,
703 ISIR.s_num_in_college s_num_in_college,
704 ISIR.first_college first_college,
705 ISIR.first_house_plan first_house_plan,
706 ISIR.second_college second_college,
707 ISIR.second_house_plan second_house_plan,
708 ISIR.third_college third_college,
709 ISIR.third_house_plan third_house_plan,
710 ISIR.fourth_college fourth_college,
711 ISIR.fourth_house_plan fourth_house_plan,
712 ISIR.fifth_college fifth_college,
713 ISIR.fifth_house_plan fifth_house_plan,
714 ISIR.sixth_college sixth_college,
715 ISIR.sixth_house_plan sixth_house_plan,
716 ISIR.date_app_completed date_app_completed,
717 ISIR.signed_by signed_by,
718 ISIR.preparer_ssn preparer_ssn,
719 ISIR.preparer_emp_id_number preparer_emp_id_number,
720 ISIR.preparer_sign preparer_sign,
721 ISIR.transaction_receipt_date transaction_receipt_date,
722 ISIR.dependency_override_ind dependency_override_ind,
723 ISIR.faa_fedral_schl_code faa_fedral_schl_code,
724 ISIR.faa_adjustment faa_adjustment,
725 ISIR.input_record_type input_record_type,
726 ISIR.serial_number serial_number,
727 ISIR.batch_number batch_number,
728 ISIR.early_analysis_flag early_analysis_flag,
729 ISIR.app_entry_source_code app_entry_source_code,
730 ISIR.eti_destination_code eti_destination_code,
731 ISIR.reject_override_b reject_override_b,
732 ISIR.reject_override_n reject_override_n,
733 ISIR.reject_override_w reject_override_w,
734 ISIR.assum_override_1 assum_override_1,
735 ISIR.assum_override_2 assum_override_2,
736 ISIR.assum_override_3 assum_override_3,
737 ISIR.assum_override_4 assum_override_4,
738 ISIR.assum_override_5 assum_override_5,
739 ISIR.assum_override_6 assum_override_6,
740 ISIR.dependency_status dependency_status,
741 ISIR.s_email_address s_email_address,
742 ISIR.nslds_reason_code nslds_reason_code,
743 ISIR.app_receipt_date app_receipt_date,
744 ISIR.processed_rec_type processed_rec_type,
745 ISIR.hist_correction_for_tran_id hist_correction_for_tran_id,
746 ISIR.system_generated_indicator system_generated_indicator,
747 ISIR.dup_request_indicator dup_request_indicator,
748 ISIR.source_of_correction source_of_correction,
749 ISIR.p_cal_tax_status p_cal_tax_status,
750 ISIR.s_cal_tax_status s_cal_tax_status,
751 ISIR.graduate_flag graduate_flag,
752 ISIR.auto_zero_efc auto_zero_efc,
753 ISIR.efc_change_flag efc_change_flag,
754 ISIR.sarc_flag sarc_flag,
755 ISIR.simplified_need_test simplified_need_test,
756 ISIR.reject_reason_codes reject_reason_codes,
757 ISIR.select_service_match_flag select_service_match_flag,
758 ISIR.select_service_reg_flag select_service_reg_flag,
759 ISIR.ins_match_flag ins_match_flag,
760 ISIR.ins_verification_number ins_verification_number,
761 ISIR.sec_ins_match_flag sec_ins_match_flag,
762 ISIR.sec_ins_ver_number sec_ins_ver_number,
763 ISIR.ssn_match_flag ssn_match_flag,
764 ISIR.ssa_citizenship_flag ssa_citizenship_flag,
765 ISIR.ssn_date_of_death ssn_date_of_death,
766 ISIR.nslds_match_flag nslds_match_flag,
767 ISIR.va_match_flag va_match_flag,
768 ISIR.prisoner_match prisoner_match,
769 ISIR.verification_flag verification_flag,
770 ISIR.subsequent_app_flag subsequent_app_flag,
771 ISIR.app_source_site_code app_source_site_code,
772 ISIR.tran_source_site_code tran_source_site_code,
773 ISIR.drn drn,
774 ISIR.tran_process_date tran_process_date,
775 ISIR.correction_flags correction_flags,
776 ISIR.computer_batch_number computer_batch_number,
777 ISIR.highlight_flags highlight_flags,
778 ISIR.paid_efc paid_efc,
779 ISIR.primary_efc primary_efc,
780 ISIR.secondary_efc secondary_efc,
781 ISIR.fed_pell_grant_efc_type fed_pell_grant_efc_type,
782 ISIR.primary_efc_type primary_efc_type,
783 ISIR.sec_efc_type sec_efc_type,
784 ISIR.primary_alternate_month_1 primary_alternate_month_1,
785 ISIR.primary_alternate_month_2 primary_alternate_month_2,
786 ISIR.primary_alternate_month_3 primary_alternate_month_3,
787 ISIR.primary_alternate_month_4 primary_alternate_month_4,
788 ISIR.primary_alternate_month_5 primary_alternate_month_5,
789 ISIR.primary_alternate_month_6 primary_alternate_month_6,
790 ISIR.primary_alternate_month_7 primary_alternate_month_7,
791 ISIR.primary_alternate_month_8 primary_alternate_month_8,
792 ISIR.primary_alternate_month_10 primary_alternate_month_10,
793 ISIR.primary_alternate_month_11 primary_alternate_month_11,
794 ISIR.primary_alternate_month_12 primary_alternate_month_12,
795 ISIR.sec_alternate_month_1 sec_alternate_month_1,
796 ISIR.sec_alternate_month_2 sec_alternate_month_2,
797 ISIR.sec_alternate_month_3 sec_alternate_month_3,
798 ISIR.sec_alternate_month_4 sec_alternate_month_4,
799 ISIR.sec_alternate_month_5 sec_alternate_month_5,
800 ISIR.sec_alternate_month_6 sec_alternate_month_6,
801 ISIR.sec_alternate_month_7 sec_alternate_month_7,
802 ISIR.sec_alternate_month_8 sec_alternate_month_8,
803 ISIR.sec_alternate_month_10 sec_alternate_month_10,
804 ISIR.sec_alternate_month_11 sec_alternate_month_11,
805 ISIR.sec_alternate_month_12 sec_alternate_month_12,
806 ISIR.total_income total_income,
807 ISIR.allow_total_income allow_total_income,
808 ISIR.state_tax_allow state_tax_allow,
809 ISIR.employment_allow employment_allow,
810 ISIR.income_protection_allow income_protection_allow,
811 ISIR.available_income available_income,
812 ISIR.contribution_from_ai contribution_from_ai,
813 ISIR.discretionary_networth discretionary_networth,
814 ISIR.efc_networth efc_networth,
815 ISIR.asset_protect_allow asset_protect_allow,
816 ISIR.parents_cont_from_assets parents_cont_from_assets,
817 ISIR.adjusted_available_income adjusted_available_income,
818 ISIR.total_student_contribution total_student_contribution,
819 ISIR.total_parent_contribution total_parent_contribution,
820 ISIR.parents_contribution parents_contribution,
821 ISIR.student_total_income student_total_income,
822 ISIR.sati sati,
823 ISIR.sic sic,
824 ISIR.sdnw sdnw,
825 ISIR.sca sca,
826 ISIR.fti fti,
827 ISIR.secti secti,
828 ISIR.secati secati,
829 ISIR.secstx secstx,
830 ISIR.secea secea,
831 ISIR.secipa secipa,
832 ISIR.secai secai,
833 ISIR.seccai seccai,
834 ISIR.secdnw secdnw,
835 ISIR.secnw secnw,
836 ISIR.secapa secapa,
837 ISIR.secpca secpca,
838 ISIR.secaai secaai,
839 ISIR.sectsc sectsc,
840 ISIR.sectpc sectpc,
841 ISIR.secpc secpc,
842 ISIR.secsti secsti,
843 ISIR.secsati secsati,
844 ISIR.secsic secsic,
845 ISIR.secsdnw secsdnw,
846 ISIR.secsca secsca,
847 ISIR.secfti secfti,
848 ISIR.a_citizenship a_citizenship,
849 ISIR.a_student_marital_status a_student_marital_status,
850 ISIR.a_student_agi a_student_agi,
851 ISIR.a_s_us_tax_paid a_s_us_tax_paid,
852 ISIR.a_s_income_work a_s_income_work,
853 ISIR.a_spouse_income_work a_spouse_income_work,
854 ISIR.a_s_total_wsc a_s_total_wsc,
855 ISIR.a_date_of_birth a_date_of_birth,
856 ISIR.a_student_married a_student_married,
857 ISIR.a_have_children a_have_children,
858 ISIR.a_s_have_dependents a_s_have_dependents,
859 ISIR.a_va_status a_va_status,
860 ISIR.a_s_num_in_family a_s_num_in_family,
861 ISIR.a_s_num_in_college a_s_num_in_college,
862 ISIR.a_p_marital_status a_p_marital_status,
863 ISIR.a_father_ssn a_father_ssn,
864 ISIR.a_mother_ssn a_mother_ssn,
865 ISIR.a_parents_num_family a_parents_num_family,
866 ISIR.a_parents_num_college a_parents_num_college,
867 ISIR.a_parents_agi a_parents_agi,
868 ISIR.a_p_us_tax_paid a_p_us_tax_paid,
869 ISIR.a_f_work_income a_f_work_income,
870 ISIR.a_m_work_income a_m_work_income,
871 ISIR.a_p_total_wsc a_p_total_wsc,
872 ISIR.comment_codes comment_codes,
873 ISIR.sar_ack_comm_code sar_ack_comm_code,
874 ISIR.pell_grant_elig_flag pell_grant_elig_flag,
875 ISIR.reprocess_reason_code reprocess_reason_code,
876 ISIR.duplicate_date duplicate_date,
877 ISIR.isir_transaction_type isir_transaction_type,
878 ISIR.fedral_schl_code_indicator fedral_schl_code_indicator,
879 ISIR.multi_school_code_flags multi_school_code_flags,
880 ISIR.dup_ssn_indicator dup_ssn_indicator,
881 ISIR.payment_isir payment_isir,
882 ISIR.receipt_status receipt_status,
883 ISIR.isir_receipt_completed isir_receipt_completed,
884 ISIR.system_record_type system_record_type,
885 ISIR.created_by created_by,
886 ISIR.creation_date creation_date,
887 ISIR.last_updated_by last_updated_by,
888 ISIR.last_update_date last_update_date,
889 ISIR.last_update_login last_update_login,
890 ISIR.request_id request_id,
891 ISIR.program_application_id program_application_id,
892 ISIR.program_id program_id,
893 ISIR.program_update_date program_update_date,
894 ISIR.org_id org_id,
895 ISIR.verif_track_flag verif_track_flag,
896 ISIR.active_isir active_isir,
897 ISIR.fafsa_data_verify_flags fafsa_data_verify_flags,
898 ISIR.reject_override_a reject_override_a,
899 ISIR.reject_override_c reject_override_c,
900 ISIR.parent_marital_status_date parent_marital_status_date,
901 ISIR.legacy_record_flag legacy_record_flag,
902 ISIR.father_first_name_initial_txt,
903 ISIR.father_step_father_birth_date,
904 ISIR.mother_first_name_initial_txt,
905 ISIR.mother_step_mother_birth_date,
906 ISIR.parents_email_address_txt,
907 ISIR.address_change_type,
908 ISIR.cps_pushed_isir_flag,
909 ISIR.electronic_transaction_type,
910 ISIR.sar_c_change_type,
911 ISIR.father_ssn_match_type,
912 ISIR.mother_ssn_match_type,
913 ISIR.reject_override_g_flag,
914 ISIR.dhs_verification_num_txt,
915 ISIR.data_file_name_txt,
916 ISIR.message_class_txt,
917 reject_override_3_flag,
918 ISIR.reject_override_12_flag,
919 ISIR.reject_override_j_flag,
920 ISIR.reject_override_k_flag,
921 ISIR.rejected_status_change_flag,
922 ISIR.verification_selection_flag
923 FROM igf_ap_isir_matched ISIR
924 WHERE ISIR.base_id = cp_base_id
925 AND ISIR.active_isir = 'Y'
926 AND ISIR.system_record_type = cp_rec_type;
927
928 l_old_act_isir c_old_act_isir%ROWTYPE;
929
930 BEGIN
931 igf_aw_gen.set_org_id(NULL);
932 errbuf := NULL;
933 retcode := 0;
934 l_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
935 l_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
936
937 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
938 l_chk_profile := igf_ap_gen.check_profile;
939 IF l_chk_profile = 'N' THEN
940 fnd_message.set_name('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
941 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
942 RETURN;
943 END IF;
944
945 l_chk_batch := igf_ap_gen.check_batch(p_batch_id,'ISIR');
946 IF l_chk_batch = 'N' THEN
947 fnd_message.set_name('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
948 add_log_table_process(NULL,l_error,fnd_message.get);
949 l_terminate_flag := TRUE;
950 END IF;
951 -- this is to get the alternate code
952 l_get_alternate_code := NULL;
953 OPEN c_get_alternate_code(l_cal_type,l_seq_number);
954 FETCH c_get_alternate_code INTO l_get_alternate_code;
955 CLOSE c_get_alternate_code;
956
957 -- this is to check that the award year is valid or not
958 l_get_status := NULL;
959 OPEN c_get_status(l_cal_type,l_seq_number);
960 FETCH c_get_status INTO l_get_status;
961 CLOSE c_get_status;
962
963 IF l_get_status.award_year_status_code NOT IN ('O','LD') THEN
964 fnd_message.set_name('IGF','IGF_AP_LG_INVALID_STAT');
965 fnd_message.set_token('AWARD_STATUS',l_get_status.award_year_status_code);
966 add_log_table_process(NULL,l_error,fnd_message.get);
967 l_terminate_flag := TRUE;
968 END IF;
969 IF l_terminate_flag = TRUE THEN
970 print_log_process(l_get_alternate_code.alternate_code,p_batch_id,p_del_ind);
971 RETURN;
972 END IF;
973
974 OPEN c_get_dup_person(l_get_alternate_code.alternate_code,p_batch_id);
975 LOOP
976 FETCH c_get_dup_person INTO l_get_dup_person;
977 EXIT WHEN c_get_dup_person%NOTFOUND;
978 g_per_tab(l_index) := l_get_dup_person.person_number;
979 l_index := l_index + 1;
980 END LOOP;
981 CLOSE c_get_dup_person;
982
983 -- THE MAIN LOOP STARTS HERE FOR FETCHING THE RECORD FROM THE INTERFACE TABLE
984 -- IF MORE THAN ISIR RECORD PER STUDENT IS ENTERED THEN THE PERSON IS TO BE SKIPPED ENTIRELY
985 OPEN c_get_records(l_get_alternate_code.alternate_code,p_batch_id);
986 LOOP
987 BEGIN
988 SAVEPOINT sp1;
989 FETCH c_get_records INTO l_get_records;
990 EXIT WHEN c_get_records%NOTFOUND OR c_get_records%NOTFOUND IS NULL;
991 l_debug_str := 'Actint_id is:' || l_get_records.actint_id;
992 -- here the check is there to see if the person number is repetitive so skip the person
993 IF NOT check_dup_person(l_get_records.person_number) THEN
994 lv_base_id := NULL;
995 lv_person_id := NULL;
996 igf_ap_gen.check_person(l_get_records.person_number,l_cal_type,l_seq_number,lv_person_id,lv_base_id);
997 IF lv_person_id IS NULL THEN
998 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
999 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1000 l_error_flag := TRUE;
1001 ELSE
1002 IF lv_base_id IS NULL THEN
1003 fnd_message.set_name('IGF','IGF_AP_FABASE_NOT_FOUND');
1004 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1005 l_error_flag := TRUE;
1006 ELSE
1007 l_debug_str := l_debug_str || ' Person and Base ID check passed';
1008 -- HERE THE CHK FOR THE BATCH YEAR WILL BE THERE
1009 IF l_get_records.batch_year_num <> NVL(l_get_status.batch_year,-1) THEN
1010 fnd_message.set_name('IGF','IGF_AP_AW_BATCH_NOT_EXISTS');
1011 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1012 l_error_flag := TRUE;
1013 ELSE
1014 g_baseid_exists := NULL;
1015 OPEN c_baseid_exists(lv_base_id);
1016 FETCH c_baseid_exists INTO g_baseid_exists;
1017 CLOSE c_baseid_exists;
1018 l_chk_isir_rec_legacy := NULL;
1019 OPEN c_chk_isir_rec_legacy(lv_base_id,l_get_records.batch_year_num);
1020 FETCH c_chk_isir_rec_legacy INTO l_chk_isir_rec_legacy;
1021 CLOSE c_chk_isir_rec_legacy;
1022 IF l_chk_isir_rec_legacy.isir_id IS NOT NULL THEN
1023 fnd_message.set_name('IGF','IGF_AP_NON_LI_ISIR_EXISTS');
1024 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1025 l_error_flag := TRUE;
1026 ELSE -- THIS MEANS THE THERE IS ISIR IS IMPORTED BY LEGACY ONLY
1027 l_debug_str := l_debug_str || ' Legacy ISIR check passed';
1028 l_get_isir_rec := NULL;
1029 OPEN c_get_isir_rec(lv_base_id,l_get_records.batch_year_num,l_get_records.transaction_num_txt,'ORIGINAL');
1030 FETCH c_get_isir_rec INTO l_get_isir_rec;
1031 CLOSE c_get_isir_rec;
1032 IF l_get_isir_rec.isir_id IS NULL THEN
1033 fnd_message.set_name('IGF','IGF_AP_NO_ISIR_RECS_EXIST');
1034 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1035 l_error_flag := TRUE;
1036 ELSE
1037 l_debug_str := l_debug_str || ' ISIR record exists check passed';
1038 IF l_get_isir_rec.primary_efc IS NULL THEN
1039 fnd_message.set_name('IGF','IGF_AP_ISIR_INVALID_EFC');
1040 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1041 l_error_flag := TRUE;
1042 ELSE
1043 l_debug_str := l_debug_str || ' EFC check passed';
1044 l_old_act_isir := NULL;
1045 OPEN c_old_act_isir(lv_base_id,'ORIGINAL');
1046 FETCH c_old_act_isir INTO l_old_act_isir;
1047 CLOSE c_old_act_isir;
1048 IF l_old_act_isir.isir_id IS NOT NULL THEN
1049 IF l_old_act_isir.isir_id <> l_get_isir_rec.isir_id THEN -- means that the current active ISIR is different from the new ISIR to be imported
1050 IF p_upd_ind = 'Y' THEN
1051 -- make the old as inactive and the current as the active ISIR
1052 update_isir_rec(l_old_act_isir,'N');
1053 l_process_flag := TRUE;
1054 ELSE
1055 fnd_message.set_name('IGF','IGF_AP_UPD_IND_NOT_SET');
1056 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1057 l_error_flag := TRUE;
1058 END IF; -- update flag is not set
1059 ELSE
1060 fnd_message.set_name('IGF','IGF_AP_REC_ALREADY_ACT');
1061 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1062 l_error_flag := TRUE;
1063 END IF; --
1064 ELSE -- MEANS THE CURRENT ACTIVE ISIR DOES NOT EXISTS
1065 l_process_flag := TRUE;
1066 END IF;
1067 l_debug_str := l_debug_str || ' Old ISIR check passed';
1068 IF l_process_flag = TRUE THEN
1069 update_isir_rec(l_get_isir_rec,'Y');
1070 IF NVL(l_get_isir_rec.verification_flag,'X') = 'Y' THEN
1071 update_fabase_rec_process('SELECTED');
1072 ELSE
1073 update_fabase_rec_process('NOTSELECTED');
1074 END IF;
1075 l_success_record_cnt := l_success_record_cnt + 1;
1076 END IF; -- FOR THE PROCESS FLAG CHECK
1077
1078 END IF; -- for the primary_efc null check
1079 END IF; -- FOR CHECKING THE CONTEXT ISIR
1080 END IF; -- ISIR IMPORTED BY LEGACY OR NOT
1081 END IF; -- for the batch year check
1082 END IF; -- BASE ID EXISTS OR NOT
1083 END IF; -- PERSON EXISTS OR NOT
1084
1085 ELSE
1086 fnd_message.set_name('IGF','IGF_AP_MK_ACT_SKIP');
1087 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1088 l_error_flag := TRUE;
1089 END IF; -- if the person is not duplicate
1090 IF l_error_flag = TRUE THEN
1091 l_error_flag := FALSE;
1092 l_error_record_cnt := l_error_record_cnt + 1;
1093 --update the legacy interface table column import_status to 'E'
1094 UPDATE igf_ap_li_isir_act_ints
1095 SET import_status_type = 'E'
1096 WHERE ROWID = l_get_records.ROW_ID;
1097 ELSE
1098 IF p_del_ind = 'Y' THEN
1099 DELETE FROM igf_ap_li_isir_act_ints
1100 WHERE ROWID = l_get_records.ROW_ID;
1101 ELSE
1102 --update the legacy interface table column import_status to 'I'
1103 UPDATE igf_ap_li_isir_act_ints
1104 SET import_status_type = 'I'
1105 WHERE ROWID = l_get_records.ROW_ID;
1106 END IF;
1107 END IF;
1108
1109 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1110 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_mk_isir_act_pkg.lg_make_active_isir.debug',l_debug_str);
1111 END IF;
1112 l_process_flag := FALSE;
1113 l_debug_str := NULL;
1114 EXCEPTION
1115 WHEN others THEN
1116 l_process_flag := FALSE;
1117 l_debug_str := NULL;
1118 l_error_flag := FALSE;
1119 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1120 fnd_message.set_token('NAME','IGF_AP_MK_ISIR_ACT_PKG.LG_MAKE_ACTIVE_ISIR');
1121 add_log_table_process(l_get_records.person_number,l_error,fnd_message.get);
1122 ROLLBACK TO sp1;
1123 END;
1124 COMMIT;
1125 END LOOP;
1126 CLOSE c_get_records;
1127
1128 IF l_success_record_cnt = 0 AND l_error_record_cnt = 0 THEN
1129 fnd_message.set_name('IGS','IGS_FI_NO_RECORD_AVAILABLE');
1130 add_log_table_process(NULL,l_error,fnd_message.get);
1131 END IF;
1132
1133 -- CALL THE PRINT LOG PROCESS
1134 print_log_process(l_get_alternate_code.alternate_code,p_batch_id,p_del_ind);
1135
1136 l_total_record_cnt := l_success_record_cnt + l_error_record_cnt;
1137 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
1138 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' ' || l_total_record_cnt);
1139 fnd_message.set_name('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC');
1140 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' : ' || l_success_record_cnt);
1141 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_FAILED');
1142 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' : ' || l_error_record_cnt);
1143 EXCEPTION
1144 WHEN others THEN
1145 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1146 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_mk_isir_act_pkg.lg_make_active_isir.exception','Unhandled Exception: '||SQLERRM);
1147 END IF;
1148 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1149 fnd_message.set_token('NAME','IGF_AP_MK_ISIR_ACT_PKG.LG_MAKE_ACTIVE_ISIR');
1150 errbuf := fnd_message.get;
1151 igs_ge_msg_stack.conc_exception_hndl;
1152
1153 END lg_make_active_isir;
1154
1155 FUNCTION check_dup_person(p_person_number IN VARCHAR2)
1156 RETURN BOOLEAN
1157 IS
1158 /*
1159 || Created By : bkkumar
1160 || Created On :
1161 || Purpose : Routine will verify whether the person number is duplicate
1162 || in the interface table.
1163 || Known limitations, enhancements or remarks :
1164 || Change History :
1165 || Who When What
1166 || (reverse chronological order - newest change first)
1167 */
1168 l_count NUMBER(5) := g_per_tab.COUNT;
1169 BEGIN
1170 FOR i IN 1..l_count LOOP
1171 IF g_per_tab(i) = p_person_number THEN
1172 RETURN TRUE;
1173 END IF;
1174 END LOOP;
1175 RETURN FALSE;
1176 END;
1177
1178 PROCEDURE update_isir_rec( p_isir_rec IN igf_ap_isir_matched%ROWTYPE,
1179 p_make_isir IN VARCHAR2
1180 ) IS
1181 /*
1182 || Created By : bkkumar
1183 || Created On : 26-MAY-2003
1184 || Purpose : This process updates the ISIR record active_isir
1185 || Known limitations, enhancements or remarks :
1186 || Change History :
1187 || Who When What
1188 || (reverse chronological order - newest change first)
1189 */
1190
1191 BEGIN
1192
1193 igf_ap_isir_matched_pkg.update_row(
1194 x_Mode => 'R',
1195 x_rowid => p_isir_rec.row_id,
1196 x_isir_id => p_isir_rec.isir_id,
1197 x_base_id => p_isir_rec.base_id,
1198 x_batch_year => p_isir_rec.batch_year ,
1199 x_transaction_num => p_isir_rec.transaction_num ,
1200 x_current_ssn => p_isir_rec.current_ssn ,
1201 x_ssn_name_change => p_isir_rec.ssn_name_change ,
1202 x_original_ssn => p_isir_rec.original_ssn ,
1203 x_orig_name_id => p_isir_rec.orig_name_id ,
1204 x_last_name => p_isir_rec.last_name ,
1205 x_first_name => p_isir_rec.first_name ,
1206 x_middle_initial => p_isir_rec.middle_initial ,
1207 x_perm_mail_add => p_isir_rec.perm_mail_add ,
1208 x_perm_city => p_isir_rec.perm_city ,
1209 x_perm_state => p_isir_rec.perm_state ,
1210 x_perm_zip_code => p_isir_rec.perm_zip_code ,
1211 x_date_of_birth => p_isir_rec.date_of_birth ,
1212 x_phone_number => p_isir_rec.phone_number ,
1213 x_driver_license_number => p_isir_rec.driver_license_number ,
1214 x_driver_license_state => p_isir_rec.driver_license_state ,
1215 x_citizenship_status => p_isir_rec.citizenship_status ,
1216 x_alien_reg_number => p_isir_rec.alien_reg_number ,
1217 x_s_marital_status => p_isir_rec.s_marital_status ,
1218 x_s_marital_status_date => p_isir_rec.s_marital_status_date ,
1219 x_summ_enrl_status => p_isir_rec.summ_enrl_status ,
1220 x_fall_enrl_status => p_isir_rec.fall_enrl_status ,
1221 x_winter_enrl_status => p_isir_rec.winter_enrl_status ,
1222 x_spring_enrl_status => p_isir_rec.spring_enrl_status ,
1223 x_summ2_enrl_status => p_isir_rec.summ2_enrl_status ,
1224 x_fathers_highest_edu_level => p_isir_rec.fathers_highest_edu_level ,
1225 x_mothers_highest_edu_level => p_isir_rec.mothers_highest_edu_level ,
1226 x_s_state_legal_residence => p_isir_rec.s_state_legal_residence ,
1227 x_legal_residence_before_date => p_isir_rec.legal_residence_before_date ,
1228 x_s_legal_resd_date => p_isir_rec.s_legal_resd_date ,
1229 x_ss_r_u_male => p_isir_rec.ss_r_u_male ,
1230 x_selective_service_reg => p_isir_rec.selective_service_reg ,
1231 x_degree_certification => p_isir_rec.degree_certification ,
1232 x_grade_level_in_college => p_isir_rec.grade_level_in_college ,
1233 x_high_school_diploma_ged => p_isir_rec.high_school_diploma_ged ,
1234 x_first_bachelor_deg_by_date => p_isir_rec.first_bachelor_deg_by_date ,
1235 x_interest_in_loan => p_isir_rec.interest_in_loan ,
1236 x_interest_in_stud_employment => p_isir_rec.interest_in_stud_employment ,
1237 x_drug_offence_conviction => p_isir_rec.drug_offence_conviction ,
1238 x_s_tax_return_status => p_isir_rec.s_tax_return_status ,
1239 x_s_type_tax_return => p_isir_rec.s_type_tax_return ,
1240 x_s_elig_1040ez => p_isir_rec.s_elig_1040ez ,
1241 x_s_adjusted_gross_income => p_isir_rec.s_adjusted_gross_income ,
1242 x_s_fed_taxes_paid => p_isir_rec.s_fed_taxes_paid ,
1243 x_s_exemptions => p_isir_rec.s_exemptions ,
1244 x_s_income_from_work => p_isir_rec.s_income_from_work ,
1245 x_spouse_income_from_work => p_isir_rec.spouse_income_from_work ,
1246 x_s_toa_amt_from_wsa => p_isir_rec.s_toa_amt_from_wsa ,
1247 x_s_toa_amt_from_wsb => p_isir_rec.s_toa_amt_from_wsb ,
1248 x_s_toa_amt_from_wsc => p_isir_rec.s_toa_amt_from_wsc ,
1249 x_s_investment_networth => p_isir_rec.s_investment_networth ,
1250 x_s_busi_farm_networth => p_isir_rec.s_busi_farm_networth ,
1251 x_s_cash_savings => p_isir_rec.s_cash_savings ,
1252 x_va_months => p_isir_rec.va_months ,
1253 x_va_amount => p_isir_rec.va_amount ,
1254 x_stud_dob_before_date => p_isir_rec.stud_dob_before_date ,
1255 x_deg_beyond_bachelor => p_isir_rec.deg_beyond_bachelor ,
1256 x_s_married => p_isir_rec.s_married ,
1257 x_s_have_children => p_isir_rec.s_have_children ,
1258 x_legal_dependents => p_isir_rec.legal_dependents ,
1259 x_orphan_ward_of_court => p_isir_rec.orphan_ward_of_court ,
1260 x_s_veteran => p_isir_rec.s_veteran ,
1261 x_p_marital_status => p_isir_rec.p_marital_status ,
1262 x_father_ssn => p_isir_rec.father_ssn ,
1263 x_f_last_name => p_isir_rec.f_last_name ,
1264 x_mother_ssn => p_isir_rec.mother_ssn ,
1265 x_m_last_name => p_isir_rec.m_last_name ,
1266 x_p_num_family_member => p_isir_rec.p_num_family_member ,
1267 x_p_num_in_college => p_isir_rec.p_num_in_college ,
1268 x_p_state_legal_residence => p_isir_rec.p_state_legal_residence ,
1269 x_p_state_legal_res_before_dt => p_isir_rec.p_state_legal_res_before_dt ,
1270 x_p_legal_res_date => p_isir_rec.p_legal_res_date ,
1271 x_age_older_parent => p_isir_rec.age_older_parent ,
1272 x_p_tax_return_status => p_isir_rec.p_tax_return_status ,
1273 x_p_type_tax_return => p_isir_rec.p_type_tax_return ,
1274 x_p_elig_1040aez => p_isir_rec.p_elig_1040aez ,
1275 x_p_adjusted_gross_income => p_isir_rec.p_adjusted_gross_income ,
1276 x_p_taxes_paid => p_isir_rec.p_taxes_paid ,
1277 x_p_exemptions => p_isir_rec.p_exemptions ,
1278 x_f_income_work => p_isir_rec.f_income_work ,
1279 x_m_income_work => p_isir_rec.m_income_work ,
1280 x_p_income_wsa => p_isir_rec.p_income_wsa ,
1281 x_p_income_wsb => p_isir_rec.p_income_wsb ,
1282 x_p_income_wsc => p_isir_rec.p_income_wsc ,
1283 x_p_investment_networth => p_isir_rec.p_investment_networth ,
1284 x_p_business_networth => p_isir_rec.p_business_networth ,
1285 x_p_cash_saving => p_isir_rec.p_cash_saving ,
1286 x_s_num_family_members => p_isir_rec.s_num_family_members ,
1287 x_s_num_in_college => p_isir_rec.s_num_in_college ,
1288 x_first_college => p_isir_rec.first_college ,
1289 x_first_house_plan => p_isir_rec.first_house_plan ,
1290 x_second_college => p_isir_rec.second_college ,
1291 x_second_house_plan => p_isir_rec.second_house_plan ,
1292 x_third_college => p_isir_rec.third_college ,
1293 x_third_house_plan => p_isir_rec.third_house_plan ,
1294 x_fourth_college => p_isir_rec.fourth_college ,
1295 x_fourth_house_plan => p_isir_rec.fourth_house_plan ,
1296 x_fifth_college => p_isir_rec.fifth_college ,
1297 x_fifth_house_plan => p_isir_rec.fifth_house_plan ,
1298 x_sixth_college => p_isir_rec.sixth_college ,
1299 x_sixth_house_plan => p_isir_rec.sixth_house_plan ,
1300 x_date_app_completed => p_isir_rec.date_app_completed ,
1301 x_signed_by => p_isir_rec.signed_by ,
1302 x_preparer_ssn => p_isir_rec.preparer_ssn ,
1303 x_preparer_emp_id_number => p_isir_rec.preparer_emp_id_number ,
1304 x_preparer_sign => p_isir_rec.preparer_sign ,
1305 x_transaction_receipt_date => p_isir_rec.transaction_receipt_date ,
1306 x_dependency_override_ind => p_isir_rec.dependency_override_ind ,
1307 x_faa_fedral_schl_code => p_isir_rec.faa_fedral_schl_code ,
1308 x_faa_adjustment => p_isir_rec.faa_adjustment ,
1309 x_input_record_type => p_isir_rec.input_record_type ,
1310 x_serial_number => p_isir_rec.serial_number ,
1311 x_batch_number => p_isir_rec.batch_number ,
1312 x_early_analysis_flag => p_isir_rec.early_analysis_flag ,
1313 x_app_entry_source_code => p_isir_rec.app_entry_source_code ,
1314 x_eti_destination_code => p_isir_rec.eti_destination_code ,
1315 x_reject_override_b => p_isir_rec.reject_override_b ,
1316 x_reject_override_n => p_isir_rec.reject_override_n ,
1317 x_reject_override_w => p_isir_rec.reject_override_w ,
1318 x_assum_override_1 => p_isir_rec.assum_override_1 ,
1319 x_assum_override_2 => p_isir_rec.assum_override_2 ,
1320 x_assum_override_3 => p_isir_rec.assum_override_3 ,
1321 x_assum_override_4 => p_isir_rec.assum_override_4 ,
1322 x_assum_override_5 => p_isir_rec.assum_override_5 ,
1323 x_assum_override_6 => p_isir_rec.assum_override_6 ,
1324 x_dependency_status => p_isir_rec.dependency_status ,
1325 x_s_email_address => p_isir_rec.s_email_address ,
1326 x_nslds_reason_code => p_isir_rec.nslds_reason_code ,
1327 x_app_receipt_date => p_isir_rec.app_receipt_date ,
1328 x_processed_rec_type => p_isir_rec.processed_rec_type ,
1329 x_hist_correction_for_tran_id => p_isir_rec.hist_correction_for_tran_id ,
1330 x_system_generated_indicator => p_isir_rec.system_generated_indicator ,
1331 x_dup_request_indicator => p_isir_rec.dup_request_indicator ,
1332 x_source_of_correction => p_isir_rec.source_of_correction ,
1333 x_p_cal_tax_status => p_isir_rec.p_cal_tax_status ,
1334 x_s_cal_tax_status => p_isir_rec.s_cal_tax_status ,
1335 x_graduate_flag => p_isir_rec.graduate_flag ,
1336 x_auto_zero_efc => p_isir_rec.auto_zero_efc ,
1337 x_efc_change_flag => p_isir_rec.efc_change_flag ,
1338 x_sarc_flag => p_isir_rec.sarc_flag ,
1339 x_simplified_need_test => p_isir_rec.simplified_need_test ,
1340 x_reject_reason_codes => p_isir_rec.reject_reason_codes ,
1341 x_select_service_match_flag => p_isir_rec.select_service_match_flag ,
1342 x_select_service_reg_flag => p_isir_rec.select_service_reg_flag ,
1343 x_ins_match_flag => p_isir_rec.ins_match_flag ,
1344 x_ins_verification_number => NULL ,
1345 x_sec_ins_match_flag => p_isir_rec.sec_ins_match_flag ,
1346 x_sec_ins_ver_number => p_isir_rec.sec_ins_ver_number ,
1347 x_ssn_match_flag => p_isir_rec.ssn_match_flag ,
1348 x_ssa_citizenship_flag => p_isir_rec.ssa_citizenship_flag ,
1349 x_ssn_date_of_death => p_isir_rec.ssn_date_of_death ,
1350 x_nslds_match_flag => p_isir_rec.nslds_match_flag ,
1351 x_va_match_flag => p_isir_rec.va_match_flag ,
1352 x_prisoner_match => p_isir_rec.prisoner_match ,
1353 x_verification_flag => p_isir_rec.verification_flag ,
1354 x_subsequent_app_flag => p_isir_rec.subsequent_app_flag ,
1355 x_app_source_site_code => p_isir_rec.app_source_site_code ,
1356 x_tran_source_site_code => p_isir_rec.tran_source_site_code ,
1357 x_drn => p_isir_rec.drn ,
1358 x_tran_process_date => p_isir_rec.tran_process_date ,
1359 x_computer_batch_number => p_isir_rec.computer_batch_number ,
1360 x_correction_flags => p_isir_rec.correction_flags ,
1361 x_highlight_flags => p_isir_rec.highlight_flags ,
1362 x_paid_efc => NULL ,
1363 x_primary_efc => p_isir_rec.primary_efc ,
1364 x_secondary_efc => p_isir_rec.secondary_efc ,
1365 x_fed_pell_grant_efc_type => NULL ,
1366 x_primary_efc_type => p_isir_rec.primary_efc_type ,
1367 x_sec_efc_type => p_isir_rec.sec_efc_type ,
1368 x_primary_alternate_month_1 => p_isir_rec.primary_alternate_month_1 ,
1369 x_primary_alternate_month_2 => p_isir_rec.primary_alternate_month_2 ,
1370 x_primary_alternate_month_3 => p_isir_rec.primary_alternate_month_3 ,
1371 x_primary_alternate_month_4 => p_isir_rec.primary_alternate_month_4 ,
1372 x_primary_alternate_month_5 => p_isir_rec.primary_alternate_month_5 ,
1373 x_primary_alternate_month_6 => p_isir_rec.primary_alternate_month_6 ,
1374 x_primary_alternate_month_7 => p_isir_rec.primary_alternate_month_7 ,
1375 x_primary_alternate_month_8 => p_isir_rec.primary_alternate_month_8 ,
1376 x_primary_alternate_month_10 => p_isir_rec.primary_alternate_month_10 ,
1377 x_primary_alternate_month_11 => p_isir_rec.primary_alternate_month_11 ,
1378 x_primary_alternate_month_12 => p_isir_rec.primary_alternate_month_12 ,
1379 x_sec_alternate_month_1 => p_isir_rec.sec_alternate_month_1 ,
1380 x_sec_alternate_month_2 => p_isir_rec.sec_alternate_month_2 ,
1381 x_sec_alternate_month_3 => p_isir_rec.sec_alternate_month_3 ,
1382 x_sec_alternate_month_4 => p_isir_rec.sec_alternate_month_4 ,
1383 x_sec_alternate_month_5 => p_isir_rec.sec_alternate_month_5 ,
1384 x_sec_alternate_month_6 => p_isir_rec.sec_alternate_month_6 ,
1385 x_sec_alternate_month_7 => p_isir_rec.sec_alternate_month_7 ,
1386 x_sec_alternate_month_8 => p_isir_rec.sec_alternate_month_8 ,
1387 x_sec_alternate_month_10 => p_isir_rec.sec_alternate_month_10 ,
1388 x_sec_alternate_month_11 => p_isir_rec.sec_alternate_month_11 ,
1389 x_sec_alternate_month_12 => p_isir_rec.sec_alternate_month_12 ,
1390 x_total_income => p_isir_rec.total_income ,
1391 x_allow_total_income => p_isir_rec.allow_total_income ,
1392 x_state_tax_allow => p_isir_rec.state_tax_allow ,
1393 x_employment_allow => p_isir_rec.employment_allow ,
1394 x_income_protection_allow => p_isir_rec.income_protection_allow ,
1395 x_available_income => p_isir_rec.available_income ,
1396 x_contribution_from_ai => p_isir_rec.contribution_from_ai ,
1397 x_discretionary_networth => p_isir_rec.discretionary_networth ,
1398 x_efc_networth => p_isir_rec.efc_networth ,
1399 x_asset_protect_allow => p_isir_rec.asset_protect_allow ,
1400 x_parents_cont_from_assets => p_isir_rec.parents_cont_from_assets ,
1401 x_adjusted_available_income => p_isir_rec.adjusted_available_income ,
1402 x_total_student_contribution => p_isir_rec.total_student_contribution ,
1403 x_total_parent_contribution => p_isir_rec.total_parent_contribution ,
1404 x_parents_contribution => p_isir_rec.parents_contribution ,
1405 x_student_total_income => p_isir_rec.student_total_income ,
1406 x_sati => p_isir_rec.sati ,
1407 x_sic => p_isir_rec.sic ,
1408 x_sdnw => p_isir_rec.sdnw ,
1409 x_sca => p_isir_rec.sca ,
1410 x_fti => p_isir_rec.fti ,
1411 x_secti => p_isir_rec.secti ,
1412 x_secati => p_isir_rec.secati ,
1413 x_secstx => p_isir_rec.secstx ,
1414 x_secea => p_isir_rec.secea ,
1415 x_secipa => p_isir_rec.secipa ,
1416 x_secai => p_isir_rec.secai ,
1417 x_seccai => p_isir_rec.seccai ,
1418 x_secdnw => p_isir_rec.secdnw ,
1419 x_secnw => p_isir_rec.secnw ,
1420 x_secapa => p_isir_rec.secapa ,
1421 x_secpca => p_isir_rec.secpca ,
1422 x_secaai => p_isir_rec.secaai ,
1423 x_sectsc => p_isir_rec.sectsc ,
1424 x_sectpc => p_isir_rec.sectpc ,
1425 x_secpc => p_isir_rec.secpc ,
1426 x_secsti => p_isir_rec.secsti ,
1427 x_secsic => p_isir_rec.secsic ,
1428 x_secsati => p_isir_rec.secsati ,
1429 x_secsdnw => p_isir_rec.secsdnw ,
1430 x_secsca => p_isir_rec.secsca ,
1431 x_secfti => p_isir_rec.secfti ,
1432 x_a_citizenship => p_isir_rec.a_citizenship ,
1433 x_a_student_marital_status => p_isir_rec.a_student_marital_status ,
1434 x_a_student_agi => p_isir_rec.a_student_agi ,
1435 x_a_s_us_tax_paid => p_isir_rec.a_s_us_tax_paid ,
1436 x_a_s_income_work => p_isir_rec.a_s_income_work ,
1437 x_a_spouse_income_work => p_isir_rec.a_spouse_income_work ,
1438 x_a_s_total_wsc => p_isir_rec.a_s_total_wsc ,
1439 x_a_date_of_birth => p_isir_rec.a_date_of_birth ,
1440 x_a_student_married => p_isir_rec.a_student_married ,
1441 x_a_have_children => p_isir_rec.a_have_children ,
1442 x_a_s_have_dependents => p_isir_rec.a_s_have_dependents ,
1443 x_a_va_status => p_isir_rec.a_va_status ,
1444 x_a_s_num_in_family => p_isir_rec.a_s_num_in_family ,
1445 x_a_s_num_in_college => p_isir_rec.a_s_num_in_college ,
1446 x_a_p_marital_status => p_isir_rec.a_p_marital_status ,
1447 x_a_father_ssn => p_isir_rec.a_father_ssn ,
1448 x_a_mother_ssn => p_isir_rec.a_mother_ssn ,
1449 x_a_parents_num_family => p_isir_rec.a_parents_num_family ,
1450 x_a_parents_num_college => p_isir_rec.a_parents_num_college ,
1451 x_a_parents_agi => p_isir_rec.a_parents_agi ,
1452 x_a_p_us_tax_paid => p_isir_rec.a_p_us_tax_paid ,
1453 x_a_f_work_income => p_isir_rec.a_f_work_income ,
1454 x_a_m_work_income => p_isir_rec.a_m_work_income ,
1455 x_a_p_total_wsc => p_isir_rec.a_p_total_wsc ,
1456 x_comment_codes => p_isir_rec.comment_codes ,
1457 x_sar_ack_comm_code => p_isir_rec.sar_ack_comm_code ,
1458 x_pell_grant_elig_flag => p_isir_rec.pell_grant_elig_flag ,
1459 x_reprocess_reason_code => p_isir_rec.reprocess_reason_code ,
1460 x_duplicate_date => p_isir_rec.duplicate_date ,
1461 x_isir_transaction_type => p_isir_rec.isir_transaction_type ,
1462 x_fedral_schl_code_indicator => p_isir_rec.fedral_schl_code_indicator ,
1463 x_multi_school_code_flags => p_isir_rec.multi_school_code_flags ,
1464 x_dup_ssn_indicator => p_isir_rec.dup_ssn_indicator ,
1465 x_system_record_type => p_isir_rec.system_record_type ,
1466 ---- Changed the value from p_isir_rec.payment_isir to p_make_isir (same value to be assigned to payment_isir and active_isir
1467 x_payment_isir => p_make_isir ,
1468 x_receipt_status => p_isir_rec.receipt_status ,
1469 x_isir_receipt_completed => p_isir_rec.isir_receipt_completed ,
1470 x_active_isir => p_make_isir ,
1471 x_fafsa_data_verify_flags => p_isir_rec.fafsa_data_verify_flags ,
1472 x_reject_override_a => p_isir_rec.reject_override_a ,
1473 x_reject_override_c => p_isir_rec.reject_override_c ,
1474 x_parent_marital_status_date => p_isir_rec.parent_marital_status_date ,
1475 x_legacy_record_flag => p_isir_rec.legacy_record_flag ,
1476 x_father_first_name_initial => p_isir_rec.father_first_name_initial_txt ,
1477 x_father_step_father_birth_dt => p_isir_rec.father_step_father_birth_date ,
1478 x_mother_first_name_initial => p_isir_rec.mother_first_name_initial_txt ,
1479 x_mother_step_mother_birth_dt => p_isir_rec.mother_step_mother_birth_date ,
1480 x_parents_email_address_txt => p_isir_rec.parents_email_address_txt ,
1481 x_address_change_type => p_isir_rec.address_change_type ,
1482 x_cps_pushed_isir_flag => p_isir_rec.cps_pushed_isir_flag ,
1483 x_electronic_transaction_type => p_isir_rec.electronic_transaction_type ,
1484 x_sar_c_change_type => p_isir_rec.sar_c_change_type ,
1485 x_father_ssn_match_type => p_isir_rec.father_ssn_match_type ,
1486 x_mother_ssn_match_type => p_isir_rec.mother_ssn_match_type ,
1487 x_reject_override_g_flag => p_isir_rec.reject_override_g_flag ,
1488 x_dhs_verification_num_txt => p_isir_rec.dhs_verification_num_txt ,
1489 x_data_file_name_txt => p_isir_rec.data_file_name_txt ,
1490 x_message_class_txt => p_isir_rec.message_class_txt ,
1491 x_reject_override_3_flag => p_isir_rec.reject_override_3_flag ,
1492 x_reject_override_12_flag => p_isir_rec.reject_override_12_flag ,
1493 x_reject_override_j_flag => p_isir_rec.reject_override_j_flag ,
1494 x_reject_override_k_flag => p_isir_rec.reject_override_k_flag ,
1495 x_rejected_status_change_flag => p_isir_rec.rejected_status_change_flag ,
1496 x_verification_selection_flag => p_isir_rec.verification_selection_flag
1497 );
1498
1499
1500 END update_isir_rec;
1501
1502
1503 PROCEDURE update_fabase_rec_process(
1504 p_fed_verif_status IN VARCHAR2
1505 ) IS
1506 /*
1507 || Created By : bkkumar
1508 || Created On : 26-MAY-2003
1509 || Purpose : This process updates the FA Base federal verification status
1510 || Known limitations, enhancements or remarks :
1511 || Change History :
1512 || Who When What
1513 || (reverse chronological order - newest change first)
1514 || rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
1515 || added new parameter award_fmly_contribution_type to
1516 || igf_ap_fa_base_rec_pkg.update_row
1517 || ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
1518 || added new parameter assoc_org_num to
1519 || igf_ap_fa_base_rec_pkg.update_row call
1520 */
1521
1522 BEGIN
1523
1524 igf_ap_fa_base_rec_pkg.update_row(
1525 x_Mode => 'R' ,
1526 x_rowid => g_baseid_exists.row_id ,
1527 x_base_id => g_baseid_exists.base_id ,
1528 x_ci_cal_type => g_baseid_exists.ci_cal_type ,
1529 x_person_id => g_baseid_exists.person_id ,
1530 x_ci_sequence_number => g_baseid_exists.ci_sequence_number ,
1531 x_org_id => g_baseid_exists.org_id ,
1532 x_coa_pending => g_baseid_exists.coa_pending ,
1533 x_verification_process_run => g_baseid_exists.verification_process_run ,
1534 x_inst_verif_status_date => g_baseid_exists.inst_verif_status_date ,
1535 x_manual_verif_flag => g_baseid_exists.manual_verif_flag ,
1536 x_fed_verif_status => p_fed_verif_status,
1537 x_fed_verif_status_date => SYSDATE,
1538 x_inst_verif_status => g_baseid_exists.inst_verif_status ,
1539 x_nslds_eligible => g_baseid_exists.nslds_eligible ,
1540 x_ede_correction_batch_id => g_baseid_exists.ede_correction_batch_id ,
1541 x_fa_process_status_date => g_baseid_exists.fa_process_status_date ,
1542 x_isir_corr_status => g_baseid_exists.isir_corr_status ,
1543 x_isir_corr_status_date => g_baseid_exists.isir_corr_status_date ,
1544 x_isir_status => g_baseid_exists.isir_status ,
1545 x_isir_status_date => g_baseid_exists.isir_status_date ,
1546 x_coa_code_f => g_baseid_exists.coa_code_f ,
1547 x_coa_code_i => g_baseid_exists.coa_code_i ,
1548 x_coa_f => g_baseid_exists.coa_f ,
1549 x_coa_i => g_baseid_exists.coa_i ,
1550 x_disbursement_hold => g_baseid_exists.disbursement_hold ,
1551 x_fa_process_status => g_baseid_exists.fa_process_status ,
1552 x_notification_status => g_baseid_exists.notification_status ,
1553 x_notification_status_date => g_baseid_exists.notification_status_date ,
1554 x_packaging_status => g_baseid_exists.packaging_status ,
1555 x_packaging_status_date => g_baseid_exists.packaging_status_date ,
1556 x_total_package_accepted => g_baseid_exists.total_package_accepted ,
1557 x_total_package_offered => g_baseid_exists.total_package_offered ,
1558 x_admstruct_id => g_baseid_exists.admstruct_id ,
1559 x_admsegment_1 => g_baseid_exists.admsegment_1 ,
1560 x_admsegment_2 => g_baseid_exists.admsegment_2 ,
1561 x_admsegment_3 => g_baseid_exists.admsegment_3 ,
1562 x_admsegment_4 => g_baseid_exists.admsegment_4 ,
1563 x_admsegment_5 => g_baseid_exists.admsegment_5 ,
1564 x_admsegment_6 => g_baseid_exists.admsegment_6 ,
1565 x_admsegment_7 => g_baseid_exists.admsegment_7 ,
1566 x_admsegment_8 => g_baseid_exists.admsegment_8 ,
1567 x_admsegment_9 => g_baseid_exists.admsegment_9 ,
1568 x_admsegment_10 => g_baseid_exists.admsegment_10 ,
1569 x_admsegment_11 => g_baseid_exists.admsegment_11 ,
1570 x_admsegment_12 => g_baseid_exists.admsegment_12 ,
1571 x_admsegment_13 => g_baseid_exists.admsegment_13 ,
1572 x_admsegment_14 => g_baseid_exists.admsegment_14 ,
1573 x_admsegment_15 => g_baseid_exists.admsegment_15 ,
1574 x_admsegment_16 => g_baseid_exists.admsegment_16 ,
1575 x_admsegment_17 => g_baseid_exists.admsegment_17 ,
1576 x_admsegment_18 => g_baseid_exists.admsegment_18 ,
1577 x_admsegment_19 => g_baseid_exists.admsegment_19 ,
1578 x_admsegment_20 => g_baseid_exists.admsegment_20 ,
1579 x_packstruct_id => g_baseid_exists.packstruct_id ,
1580 x_packsegment_1 => g_baseid_exists.packsegment_1 ,
1581 x_packsegment_2 => g_baseid_exists.packsegment_2 ,
1582 x_packsegment_3 => g_baseid_exists.packsegment_3 ,
1583 x_packsegment_4 => g_baseid_exists.packsegment_4 ,
1584 x_packsegment_5 => g_baseid_exists.packsegment_5 ,
1585 x_packsegment_6 => g_baseid_exists.packsegment_6 ,
1586 x_packsegment_7 => g_baseid_exists.packsegment_7 ,
1587 x_packsegment_8 => g_baseid_exists.packsegment_8 ,
1588 x_packsegment_9 => g_baseid_exists.packsegment_9 ,
1589 x_packsegment_10 => g_baseid_exists.packsegment_10 ,
1590 x_packsegment_11 => g_baseid_exists.packsegment_11 ,
1591 x_packsegment_12 => g_baseid_exists.packsegment_12 ,
1592 x_packsegment_13 => g_baseid_exists.packsegment_13 ,
1593 x_packsegment_14 => g_baseid_exists.packsegment_14 ,
1594 x_packsegment_15 => g_baseid_exists.packsegment_15 ,
1595 x_packsegment_16 => g_baseid_exists.packsegment_16 ,
1596 x_packsegment_17 => g_baseid_exists.packsegment_17 ,
1597 x_packsegment_18 => g_baseid_exists.packsegment_18 ,
1598 x_packsegment_19 => g_baseid_exists.packsegment_19 ,
1599 x_packsegment_20 => g_baseid_exists.packsegment_20 ,
1600 x_miscstruct_id => g_baseid_exists.miscstruct_id ,
1601 x_miscsegment_1 => g_baseid_exists.miscsegment_1 ,
1602 x_miscsegment_2 => g_baseid_exists.miscsegment_2 ,
1603 x_miscsegment_3 => g_baseid_exists.miscsegment_3 ,
1604 x_miscsegment_4 => g_baseid_exists.miscsegment_4 ,
1605 x_miscsegment_5 => g_baseid_exists.miscsegment_5 ,
1606 x_miscsegment_6 => g_baseid_exists.miscsegment_6 ,
1607 x_miscsegment_7 => g_baseid_exists.miscsegment_7 ,
1608 x_miscsegment_8 => g_baseid_exists.miscsegment_8 ,
1609 x_miscsegment_9 => g_baseid_exists.miscsegment_9 ,
1610 x_miscsegment_10 => g_baseid_exists.miscsegment_10 ,
1611 x_miscsegment_11 => g_baseid_exists.miscsegment_11 ,
1612 x_miscsegment_12 => g_baseid_exists.miscsegment_12 ,
1613 x_miscsegment_13 => g_baseid_exists.miscsegment_13 ,
1614 x_miscsegment_14 => g_baseid_exists.miscsegment_14 ,
1615 x_miscsegment_15 => g_baseid_exists.miscsegment_15 ,
1616 x_miscsegment_16 => g_baseid_exists.miscsegment_16 ,
1617 x_miscsegment_17 => g_baseid_exists.miscsegment_17 ,
1618 x_miscsegment_18 => g_baseid_exists.miscsegment_18 ,
1619 x_miscsegment_19 => g_baseid_exists.miscsegment_19 ,
1620 x_miscsegment_20 => g_baseid_exists.miscsegment_20 ,
1621 x_prof_judgement_flg => g_baseid_exists.prof_judgement_flg ,
1622 x_nslds_data_override_flg => g_baseid_exists.nslds_data_override_flg ,
1623 x_target_group => g_baseid_exists.target_group ,
1624 x_coa_fixed => g_baseid_exists.coa_fixed ,
1625 x_coa_pell => g_baseid_exists.coa_pell ,
1626 x_profile_status => g_baseid_exists.profile_status ,
1627 x_profile_status_date => g_baseid_exists.profile_status_date ,
1628 x_profile_fc => g_baseid_exists.profile_fc ,
1629 x_manual_disb_hold => g_baseid_exists.manual_disb_hold ,
1630 x_pell_alt_expense => g_baseid_exists.pell_alt_expense,
1631 x_assoc_org_num => g_baseid_exists.assoc_org_num,
1632 x_award_fmly_contribution_type => g_baseid_exists.award_fmly_contribution_type,
1633 x_isir_locked_by => g_baseid_exists.isir_locked_by,
1634 x_adnl_unsub_loan_elig_flag => g_baseid_exists.adnl_unsub_loan_elig_flag,
1635 x_lock_awd_flag => g_baseid_exists.lock_awd_flag,
1636 x_lock_coa_flag => g_baseid_exists.lock_coa_flag
1637 );
1638
1639
1640 END update_fabase_rec_process;
1641
1642 PROCEDURE add_log_table_process(
1643 p_person_number IN VARCHAR2,
1644 p_error IN VARCHAR2,
1645 p_message_str IN VARCHAR2
1646 ) IS
1647 /*
1648 || Created By : bkkumar
1649 || Created On : 26-MAY-2003
1650 || Purpose : This process adds a record to the global pl/sql table containing log messages
1651 || Known limitations, enhancements or remarks :
1652 || Change History :
1653 || Who When What
1654 || (reverse chronological order - newest change first)
1655 */
1656
1657 BEGIN
1658
1659 g_log_tab_index := g_log_tab_index + 1;
1660 g_log_tab(g_log_tab_index).person_number := p_person_number;
1661 g_log_tab(g_log_tab_index).message_text := RPAD(p_error,12) || p_message_str;
1662
1663 END add_log_table_process;
1664
1665 PROCEDURE print_log_process(
1666 p_alternate_code IN VARCHAR2,
1667 p_batch_id IN NUMBER,
1668 p_del_ind IN VARCHAR2
1669 ) IS
1670 /*
1671 || Created By : bkkumar
1672 || Created On : 26-MAY-2003
1673 || Purpose : This process gets the records from the pl/sql table and print in the log file
1674 || Known limitations, enhancements or remarks :
1675 || Change History :
1676 || Who When What
1677 || (reverse chronological order - newest change first)
1678 */
1679
1680 l_count NUMBER(5) := g_log_tab.COUNT;
1681 l_old_person igf_ap_li_isir_act_ints.person_number%TYPE := '*******';
1682
1683 l_person_number VARCHAR2(80);
1684 l_batch_id VARCHAR2(80);
1685 l_award_yr VARCHAR2(80);
1686 l_batch_desc VARCHAR2(80);
1687 l_yes_no VARCHAR2(10);
1688
1689 CURSOR c_get_batch_desc(cp_batch_num NUMBER) IS
1690 SELECT batch_desc
1691 FROM igf_ap_li_bat_ints
1692 WHERE batch_num = cp_batch_num;
1693
1694 l_get_batch_desc c_get_batch_desc%ROWTYPE;
1695
1696 BEGIN
1697
1698 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
1699 l_batch_id := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','BATCH_ID');
1700 l_award_yr := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','AWARD_YEAR');
1701 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_del_ind);
1702
1703 OPEN c_get_batch_desc(p_batch_id);
1704 FETCH c_get_batch_desc INTO l_get_batch_desc;
1705 CLOSE c_get_batch_desc;
1706 l_batch_desc := l_get_batch_desc.batch_desc ;
1707
1708 -- HERE THE INPUT PARAMETERS ARE TO BE LOGGED TO THE LOG FILE
1709 fnd_message.set_name('IGS','IGS_DA_JOB');
1710 fnd_file.put_line(fnd_file.log,fnd_message.get);
1711 fnd_file.put_line(fnd_file.log,l_batch_id || ' : ' || p_batch_id || ' - ' || l_batch_desc);
1712 fnd_file.put_line(fnd_file.log,l_award_yr || ' : ' || p_alternate_code);
1713 fnd_message.set_name('IGS','IGS_GE_ASK_DEL_REC');
1714 fnd_file.put_line(fnd_file.log,fnd_message.get || ' : ' || l_yes_no);
1715 fnd_file.put_line(fnd_file.log,'------------------------------------------------------------------------------');
1716
1717 FOR i IN 1..l_count LOOP
1718 IF g_log_tab(i).person_number IS NOT NULL THEN
1719 IF l_old_person <> g_log_tab(i).person_number THEN
1720 fnd_file.put_line(fnd_file.log,'---------------------------------------------------------------------------------');
1721 fnd_file.put_line(fnd_file.log,l_person_number || ' : ' || g_log_tab(i).person_number);
1722 END IF;
1723 l_old_person := g_log_tab(i).person_number;
1724 END IF;
1725 fnd_file.put_line(fnd_file.log,g_log_tab(i).message_text);
1726 END LOOP;
1727
1728 END print_log_process;
1729
1730 END igf_ap_mk_isir_act_pkg;