[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
a.rowid row_id,
a.person_number,
a.batch_year_num,
a.transaction_num_txt,
a.ssn_name_change_type,
a.original_ssn_txt,
a.orig_name_id_txt,
a.current_ssn_txt,
a.last_name,
a.first_name,
a.middle_initial_txt,
a.perm_mail_address_txt,
a.perm_city_txt,
a.perm_state_txt,
a.perm_zip_cd,
a.birth_date,
a.phone_number_txt,
a.driver_license_number_txt,
a.driver_license_state_txt,
a.citizenship_status_type,
a.alien_reg_number_txt,
a.s_marital_status_type,
a.s_marital_status_date,
a.summ_enrl_status_type,
a.fall_enrl_status_type,
a.winter_enrl_status_type,
a.spring_enrl_status_type,
a.summ2_enrl_status_type,
a.fathers_highest_edu_level_type,
a.mothers_highest_edu_level_type,
a.s_state_legal_residence,
a.legal_res_before_year_flag,
a.s_legal_resd_date,
a.ss_r_u_male_flag,
a.selective_service_reg_flag,
a.degree_certification_type,
a.grade_level_in_college_type,
a.high_school_diploma_ged_flag,
a.first_bachelor_deg_year_flag,
a.interest_in_loan_flag,
a.interest_in_stu_employmnt_flag,
a.drug_offence_conviction_type,
a.s_tax_return_status_type,
a.s_type_tax_return_type,
a.s_elig_1040ez_type,
a.s_adjusted_gross_income_amt,
a.s_fed_taxes_paid_amt,
a.s_exemptions_amt,
a.s_income_from_work_amt,
a.spouse_income_from_work_amt,
a.s_total_from_wsa_amt,
a.s_total_from_wsb_amt,
a.s_total_from_wsc_amt,
a.s_investment_networth_amt,
a.s_busi_farm_networth_amt,
a.s_cash_savings_amt,
a.va_months_num,
a.va_amt,
a.stud_dob_before_year_flag,
a.deg_beyond_bachelor_flag,
a.s_married_flag,
a.s_have_children_flag,
a.legal_dependents_flag,
a.orphan_ward_of_court_flag,
a.s_veteran_flag,
a.p_marital_status_type,
a.father_ssn_txt,
a.f_last_name,
a.mother_ssn_txt,
a.m_last_name,
a.p_family_members_num,
a.p_in_college_num,
a.p_state_legal_residence_txt,
a.p_legal_res_before_dt_flag,
a.p_legal_res_date,
a.age_older_parent_num,
a.p_tax_return_status_type,
a.p_type_tax_return_type,
a.p_elig_1040aez_type,
a.p_adjusted_gross_income_amt,
a.p_taxes_paid_amt,
a.p_exemptions_amt,
a.f_income_work_amt,
a.m_income_work_amt,
a.p_income_wsa_amt,
a.p_income_wsb_amt,
a.p_income_wsc_amt,
a.p_investment_networth_amt,
a.p_business_networth_amt,
a.p_cash_saving_amt,
a.s_family_members_num,
a.s_in_college_num,
a.first_college_cd,
a.first_house_plan_type,
a.second_college_cd,
a.second_house_plan_type,
a.third_college_cd,
a.third_house_plan_type,
a.fourth_college_cd,
a.fourth_house_plan_type,
a.fifth_college_cd,
a.fifth_house_plan_type,
a.sixth_college_cd,
a.sixth_house_plan_type,
a.app_completed_date,
a.signed_by_type,
a.preparer_ssn_txt,
a.preparer_emp_id_number_txt,
a.preparer_sign_flag,
a.transaction_receipt_date,
a.dependency_override_type,
a.faa_fedral_schl_cd,
a.faa_adjustment_type,
a.input_record_type,
a.serial_num,
a.batch_number_txt,
a.early_analysis_flag,
a.app_entry_source_type,
a.eti_destination_cd,
a.reject_override_b_flag,
a.reject_override_n_flag,
a.reject_override_w_flag,
a.assum_override_1_flag,
a.assum_override_2_flag,
a.assum_override_3_flag,
a.assum_override_4_flag,
a.assum_override_5_flag,
a.assum_override_6_flag,
a.dependency_status_type,
a.s_email_address_txt,
a.nslds_reason_cd,
a.app_receipt_date,
a.processed_rec_type,
a.hist_corr_for_tran_num,
a.sys_generated_indicator_type,
a.dup_request_indicator_type,
a.source_of_correction_type,
a.p_cal_tax_status_type,
a.s_cal_tax_status_type,
a.graduate_flag,
a.auto_zero_efc_flag,
a.efc_change_flag,
a.sarc_flag,
a.simplified_need_test_flag,
a.reject_reason_codes_txt,
a.select_service_match_type,
a.select_service_reg_type,
a.ins_match_flag,
a.ins_verification_num,
a.sec_ins_match_type,
a.sec_ins_ver_num,
a.ssn_match_type,
a.ssa_citizenship_type,
a.ssn_death_date,
a.nslds_match_type,
a.va_match_type,
a.prisoner_match_flag,
a.verification_flag,
a.subsequent_app_flag,
a.app_source_site_cd,
a.tran_source_site_cd,
a.drn_num,
a.tran_process_date,
a.correction_flags_txt,
a.computer_batch_num,
a.highlight_flags_txt,
a.paid_efc_amt,
a.primary_efc_amt,
a.secondary_efc_amt,
a.fed_pell_grant_efc_type,
a.primary_efc_type,
a.sec_efc_type,
a.primary_alt_month_1_amt,
a.primary_alt_month_2_amt,
a.primary_alt_month_3_amt,
a.primary_alt_month_4_amt,
a.primary_alt_month_5_amt,
a.primary_alt_month_6_amt,
a.primary_alt_month_7_amt,
a.primary_alt_month_8_amt,
a.primary_alt_month_10_amt,
a.primary_alt_month_11_amt,
a.primary_alt_month_12_amt,
a.sec_alternate_month_1_amt,
a.sec_alternate_month_2_amt,
a.sec_alternate_month_3_amt,
a.sec_alternate_month_4_amt,
a.sec_alternate_month_5_amt,
a.sec_alternate_month_6_amt,
a.sec_alternate_month_7_amt,
a.sec_alternate_month_8_amt,
a.sec_alternate_month_10_amt,
a.sec_alternate_month_11_amt,
a.sec_alternate_month_12_amt,
a.total_income_amt,
a.allow_total_income_amt,
a.state_tax_allow_amt,
a.employment_allow_amt,
a.income_protection_allow_amt,
a.available_income_amt,
a.contribution_from_ai_amt,
a.discretionary_networth_amt,
a.efc_networth_amt,
a.asset_protect_allow_amt,
a.parents_cont_from_assets_amt,
a.adjusted_available_income_amt,
a.total_student_contribution_amt,
a.total_parent_contribution_amt,
a.parents_contribution_amt,
a.student_total_income_amt,
a.sati_amt,
a.sic_amt,
a.sdnw_amt,
a.sca_amt,
a.fti_amt,
a.secti_amt,
a.secati_amt,
a.secstx_amt,
a.secea_amt,
a.secipa_amt,
a.secai_amt,
a.seccai_amt,
a.secdnw_amt,
a.secnw_amt,
a.secapa_amt,
a.secpca_amt,
a.secaai_amt,
a.sectsc_amt,
a.sectpc_amt,
a.secpc_amt,
a.secsti_amt,
a.secsati_amt,
a.secsic_amt,
a.secsdnw_amt,
a.secsca_amt,
a.secfti_amt,
a.a_citizenship_flag,
a.a_student_marital_status_flag,
a.a_student_agi_amt,
a.a_s_us_tax_paid_amt,
a.a_s_income_work_amt,
a.a_spouse_income_work_amt,
a.a_s_total_wsc_amt,
a.a_date_of_birth_flag,
a.a_student_married_flag,
a.a_have_children_flag,
a.a_s_have_dependents_flag,
a.a_va_status_flag,
a.a_s_in_family_num,
a.a_s_in_college_num,
a.a_p_marital_status_flag,
a.a_father_ssn_txt,
a.a_mother_ssn_txt,
a.a_parents_family_num,
a.a_parents_college_num,
a.a_parents_agi_amt,
a.a_p_us_tax_paid_amt,
a.a_f_work_income_amt,
a.a_m_work_income_amt,
a.a_p_total_wsc_amt,
a.comment_codes_txt,
a.sar_ack_comm_codes_txt,
a.pell_grant_elig_flag,
a.reprocess_reason_cd,
a.duplicate_date,
a.isir_transaction_type,
a.fedral_schl_type,
a.multi_school_cd_flags_txt,
a.dup_ssn_indicator_flag,
a.nslds_transaction_num,
a.nslds_database_results_type,
a.nslds_flag,
a.nslds_pell_overpay_type,
a.nslds_pell_overpay_contact_txt,
a.nslds_seog_overpay_type,
a.nslds_seog_overpay_contact_txt,
a.nslds_perkins_overpay_type,
a.nslds_perkins_ovrpay_cntct_txt,
a.nslds_defaulted_loan_flag,
a.nslds_discharged_loan_type,
a.nslds_satis_repay_flag,
a.nslds_act_bankruptcy_flag,
a.nslds_agg_subsz_out_pbal_amt,
a.nslds_agg_unsbz_out_pbal_amt,
a.nslds_agg_comb_out_pbal_amt,
a.nslds_agg_cons_out_pbal_amt,
a.nslds_agg_subsz_pend_disb_amt,
a.nslds_agg_unsbz_pend_disb_amt,
a.nslds_agg_comb_pend_disb_amt,
a.nslds_agg_subsz_total_amt,
a.nslds_agg_unsbz_total_amt,
a.nslds_agg_comb_total_amt,
a.nslds_agg_consd_total_amt,
a.nslds_perkins_out_bal_amt,
a.nslds_perkins_cur_yr_disb_amt,
a.nslds_default_loan_chng_flag,
a.nslds_dischged_loan_chng_flag,
a.nslds_satis_repay_chng_flag,
a.nslds_act_bnkrupt_chng_flag,
a.nslds_overpay_chng_flag,
a.nslds_agg_loan_chng_flag,
a.nslds_perkins_loan_chng_flag,
a.nslds_pell_paymnt_chng_flag,
a.nslds_addtnl_pell_flag,
a.nslds_addtnl_loan_flag,
a.direct_loan_mas_prom_nt_type,
a.nslds_pell_1_seq_num,
a.nslds_pell_1_verify_f_txt,
a.nslds_pell_1_efc_amt,
a.nslds_pell_1_school_num,
a.nslds_pell_1_transcn_num,
a.nslds_pell_1_last_updt_date,
a.nslds_pell_1_scheduled_amt,
a.nslds_pell_1_paid_todt_amt,
a.nslds_pell_1_remng_amt,
a.nslds_pell_1_pc_schawd_use_amt,
a.nslds_pell_1_award_amt,
a.nslds_pell_2_seq_num,
a.nslds_pell_2_verify_f_txt,
a.nslds_pell_2_efc_amt,
a.nslds_pell_2_school_num,
a.nslds_pell_2_transcn_num,
a.nslds_pell_2_last_updt_date,
a.nslds_pell_2_scheduled_amt,
a.nslds_pell_2_paid_todt_amt,
a.nslds_pell_2_remng_amt,
a.nslds_pell_2_pc_schawd_use_amt,
a.nslds_pell_2_award_amt,
a.nslds_pell_3_seq_num,
a.nslds_pell_3_verify_f_txt,
a.nslds_pell_3_efc_amt,
a.nslds_pell_3_school_num,
a.nslds_pell_3_transcn_num,
a.nslds_pell_3_last_updt_date,
a.nslds_pell_3_scheduled_amt,
a.nslds_pell_3_paid_todt_amt,
a.nslds_pell_3_remng_amt,
a.nslds_pell_3_pc_schawd_use_amt,
a.nslds_pell_3_award_amt,
a.nslds_loan_1_seq_num,
a.nslds_loan_1_type,
a.nslds_loan_1_chng_flag,
a.nslds_loan_1_prog_cd,
a.nslds_loan_1_net_amt,
a.nslds_loan_1_cur_st_cd,
a.nslds_loan_1_cur_st_date,
a.nslds_loan_1_agg_pr_bal_amt,
a.nslds_loan_1_out_pr_bal_date,
a.nslds_loan_1_begin_date,
a.nslds_loan_1_end_date,
a.nslds_loan_1_ga_cd,
a.nslds_loan_1_cont_type,
a.nslds_loan_1_schol_cd,
a.nslds_loan_1_cont_cd,
a.nslds_loan_1_grade_lvl_txt,
a.nslds_loan_1_xtr_unsbz_ln_type,
a.nslds_loan_1_capital_int_flag,
a.nslds_loan_2_seq_num,
a.nslds_loan_2_type,
a.nslds_loan_2_chng_flag,
a.nslds_loan_2_prog_cd,
a.nslds_loan_2_net_amt,
a.nslds_loan_2_cur_st_cd,
a.nslds_loan_2_cur_st_date,
a.nslds_loan_2_agg_pr_bal_amt,
a.nslds_loan_2_out_pr_bal_date,
a.nslds_loan_2_begin_date,
a.nslds_loan_2_end_date,
a.nslds_loan_2_ga_cd,
a.nslds_loan_2_cont_type,
a.nslds_loan_2_schol_cd,
a.nslds_loan_2_cont_cd,
a.nslds_loan_2_grade_lvl_txt,
a.nslds_loan_2_xtr_unsbz_ln_type,
a.nslds_loan_2_capital_int_flag,
a.nslds_loan_3_seq_num,
a.nslds_loan_3_type,
a.nslds_loan_3_chng_flag,
a.nslds_loan_3_prog_cd,
a.nslds_loan_3_net_amt,
a.nslds_loan_3_cur_st_cd,
a.nslds_loan_3_cur_st_date,
a.nslds_loan_3_agg_pr_bal_amt,
a.nslds_loan_3_out_pr_bal_date,
a.nslds_loan_3_begin_date,
a.nslds_loan_3_end_date,
a.nslds_loan_3_ga_cd,
a.nslds_loan_3_cont_type,
a.nslds_loan_3_schol_cd,
a.nslds_loan_3_cont_cd,
a.nslds_loan_3_grade_lvl_txt,
a.nslds_loan_3_xtr_unsbz_ln_type,
a.nslds_loan_3_capital_int_flag,
a.nslds_loan_4_seq_num,
a.nslds_loan_4_type,
a.nslds_loan_4_chng_flag,
a.nslds_loan_4_prog_cd,
a.nslds_loan_4_net_amt,
a.nslds_loan_4_cur_st_cd,
a.nslds_loan_4_cur_st_date,
a.nslds_loan_4_agg_pr_bal_amt,
a.nslds_loan_4_out_pr_bal_date,
a.nslds_loan_4_begin_date,
a.nslds_loan_4_end_date,
a.nslds_loan_4_ga_cd,
a.nslds_loan_4_cont_type,
a.nslds_loan_4_schol_cd,
a.nslds_loan_4_cont_cd,
a.nslds_loan_4_grade_lvl_txt,
a.nslds_loan_4_xtr_unsbz_ln_type,
a.nslds_loan_4_capital_int_flag,
a.nslds_loan_5_seq_num,
a.nslds_loan_5_type,
a.nslds_loan_5_chng_flag,
a.nslds_loan_5_prog_cd,
a.nslds_loan_5_net_amt,
a.nslds_loan_5_cur_st_cd,
a.nslds_loan_5_cur_st_date,
a.nslds_loan_5_agg_pr_bal_amt,
a.nslds_loan_5_out_pr_bal_date,
a.nslds_loan_5_begin_date,
a.nslds_loan_5_end_date,
a.nslds_loan_5_ga_cd,
a.nslds_loan_5_cont_type,
a.nslds_loan_5_schol_cd,
a.nslds_loan_5_cont_cd,
a.nslds_loan_5_grade_lvl_txt,
a.nslds_loan_5_xtr_unsbz_ln_type,
a.nslds_loan_5_capital_int_flag,
a.nslds_loan_6_seq_num,
a.nslds_loan_6_type,
a.nslds_loan_6_chng_flag,
a.nslds_loan_6_prog_cd,
a.nslds_loan_6_net_amt,
a.nslds_loan_6_cur_st_cd,
a.nslds_loan_6_cur_st_date,
a.nslds_loan_6_agg_pr_bal_amt,
a.nslds_loan_6_out_pr_bal_date,
a.nslds_loan_6_begin_date,
a.nslds_loan_6_end_date,
a.nslds_loan_6_ga_cd,
a.nslds_loan_6_cont_type,
a.nslds_loan_6_schol_cd,
a.nslds_loan_6_cont_cd,
a.nslds_loan_6_grade_lvl_txt,
a.nslds_loan_6_xtr_unsbz_ln_type,
a.nslds_loan_6_capital_int_flag,
a.request_id,
a.program_application_id,
a.program_id,
a.program_update_date,
a.nslds_loan_1_last_disb_amt,
a.nslds_loan_1_last_disb_date,
a.nslds_loan_2_last_disb_amt,
a.nslds_loan_2_last_disb_date,
a.nslds_loan_3_last_disb_amt,
a.nslds_loan_3_last_disb_date,
a.nslds_loan_4_last_disb_amt,
a.nslds_loan_4_last_disb_date,
a.nslds_loan_5_last_disb_amt,
a.nslds_loan_5_last_disb_date,
a.nslds_loan_6_last_disb_amt,
a.nslds_loan_6_last_disb_date,
a.verif_track_type,
a.fafsa_data_verification_txt,
a.reject_override_a_flag,
a.reject_override_c_flag,
a.parent_marital_status_date,
a.dlp_master_prom_note_type,
NVL(a.import_record_type,'I') import_record_type,
a.father_first_name_initial_txt,
a.father_step_father_birth_date,
a.mother_first_name_initial_txt,
a.mother_step_mother_birth_date,
a.parents_email_address_txt,
a.address_change_type,
a.cps_pushed_isir_flag,
a.electronic_transaction_type,
a.sar_c_change_type,
a.father_ssn_match_type,
a.mother_ssn_match_type,
a.subsidized_loan_limit_type,
a.combined_loan_limit_type,
a.reject_override_g_flag,
a.dhs_verification_num_txt,
'IDAP05OP.dat' data_file_name_txt,
'IDAP05OP' message_class_txt,
reject_override_3_flag,
reject_override_12_flag,
reject_override_j_flag,
reject_override_k_flag,
rejected_status_change_flag,
verification_selection_flag
FROM
igf_ap_li_isir_ints a
WHERE
a.batch_num = p_batch_id AND
a.import_status_type IN ('U','R')
ORDER BY a.person_number;
SELECT rowid row_id, a.*
FROM
igf_ap_isir_ints_all a
WHERE
a.record_status = 'LEGACY' AND
a.batch_year_num = p_batch_year
ORDER BY a.original_ssn_txt;
SELECT DECODE( lv_signed_char, '{','0','A','1', 'B','2', 'C','3', 'D','4', 'E','5', 'F','6',
'G','7', 'H','8', 'I','9', 'J','1', 'K','2',
'L','3', 'M','4', 'N','5', 'O','6', 'P','7', 'Q','8', 'R','9', '}','0' )
INTO lv_signed_value
FROM dual;
c_int_data_rec.selective_service_reg_flag := l_cps_int_data_rec.selective_service_reg_flag ;
c_int_data_rec.select_service_match_type := l_cps_int_data_rec.select_service_match_type ;
c_int_data_rec.select_service_reg_type := l_cps_int_data_rec.select_service_reg_type ;
c_int_data_rec.verification_selection_flag := l_cps_int_data_rec.verification_selection_flag;
p_delete_flag IN VARCHAR2,
p_import_type IN VARCHAR2) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : Logs all the Input Parameters
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
SELECT batch_desc, batch_type
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num ;
SELECT message_text
FROM fnd_new_messages
WHERE message_name = cp_message_name;
l_delete_flag_prmpt fnd_new_messages.message_text%TYPE;
FETCH c_get_message INTO l_delete_flag_prmpt;
l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
fnd_file.put_line( fnd_file.log, RPAD( l_delete_flag_prmpt, 40) || ' : '|| l_yes_no ) ;
IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_PE_PERSON_ID => cp_person_id,
X_API_PERSON_ID => cp_original_ssn_txt,
X_PERSON_ID_TYPE => 'SSN',
X_START_DT => SYSDATE,
X_END_DT => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_REGION_CD => NULL,
X_MODE => 'R'
);
|| rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
|| added new parameter award_fmly_contribution_type to
|| igf_ap_fa_base_rec_pkg.insert_row
*/
-- cursor to get the ssn no of a person
CURSOR ssn_cur(cp_person_id number) IS
SELECT api_person_id,api_person_id_uf, end_dt
FROM igs_pe_alt_pers_id
WHERE pe_person_id=cp_person_id
AND person_id_type like 'SSN'
AND SYSDATE < = NVL(end_dt,SYSDATE);
igf_ap_fa_base_rec_pkg.insert_row(
x_Mode => 'R',
x_rowid => l_rowid,
x_base_id => l_base_id,
x_ci_cal_type => p_ci_cal_type,
x_person_id => p_person_id, -- p_int_data_rec.igs_person_id,
x_ci_sequence_number => p_ci_sequence_number,
x_org_id => NULL,
x_coa_pending => NULL,
x_verification_process_run => NULL,
x_inst_verif_status_date => NULL,
x_manual_verif_flag => NULL,
x_fed_verif_status => NULL,
x_fed_verif_status_date => NULL,
x_inst_verif_status => NULL,
x_nslds_eligible => p_nslds_match_type, -- p_int_data_rec.NSLDS_MATCH_TYPE,
x_ede_correction_batch_id => NULL,
x_fa_process_status_date => TRUNC(SYSDATE),
x_isir_corr_status => NULL,
x_isir_corr_status_date => NULL,
x_isir_status => NULL,
x_isir_status_date => NULL,
x_coa_code_f => NULL,
x_coa_code_i => NULL,
x_coa_f => NULL,
x_coa_i => NULL,
x_disbursement_hold => NULL,
x_fa_process_status => 'RECEIVED',
x_notification_status => NULL,
x_notification_status_date => NULL,
x_packaging_status => NULL,
x_packaging_status_date => NULL,
x_total_package_accepted => NULL,
x_total_package_offered => NULL,
x_admstruct_id => NULL,
x_admsegment_1 => NULL,
x_admsegment_2 => NULL,
x_admsegment_3 => NULL,
x_admsegment_4 => NULL,
x_admsegment_5 => NULL,
x_admsegment_6 => NULL,
x_admsegment_7 => NULL,
x_admsegment_8 => NULL,
x_admsegment_9 => NULL,
x_admsegment_10 => NULL,
x_admsegment_11 => NULL,
x_admsegment_12 => NULL,
x_admsegment_13 => NULL,
x_admsegment_14 => NULL,
x_admsegment_15 => NULL,
x_admsegment_16 => NULL,
x_admsegment_17 => NULL,
x_admsegment_18 => NULL,
x_admsegment_19 => NULL,
x_admsegment_20 => NULL,
x_packstruct_id => NULL,
x_packsegment_1 => NULL,
x_packsegment_2 => NULL,
x_packsegment_3 => NULL,
x_packsegment_4 => NULL,
x_packsegment_5 => NULL,
x_packsegment_6 => NULL,
x_packsegment_7 => NULL,
x_packsegment_8 => NULL,
x_packsegment_9 => NULL,
x_packsegment_10 => NULL,
x_packsegment_11 => NULL,
x_packsegment_12 => NULL,
x_packsegment_13 => NULL,
x_packsegment_14 => NULL,
x_packsegment_15 => NULL,
x_packsegment_16 => NULL,
x_packsegment_17 => NULL,
x_packsegment_18 => NULL,
x_packsegment_19 => NULL,
x_packsegment_20 => NULL,
x_miscstruct_id => NULL,
x_miscsegment_1 => NULL,
x_miscsegment_2 => NULL,
x_miscsegment_3 => NULL,
x_miscsegment_4 => NULL,
x_miscsegment_5 => NULL,
x_miscsegment_6 => NULL,
x_miscsegment_7 => NULL,
x_miscsegment_8 => NULL,
x_miscsegment_9 => NULL,
x_miscsegment_10 => NULL,
x_miscsegment_11 => NULL,
x_miscsegment_12 => NULL,
x_miscsegment_13 => NULL,
x_miscsegment_14 => NULL,
x_miscsegment_15 => NULL,
x_miscsegment_16 => NULL,
x_miscsegment_17 => NULL,
x_miscsegment_18 => NULL,
x_miscsegment_19 => NULL,
x_miscsegment_20 => NULL,
x_prof_judgement_flg => NULL,
x_nslds_data_override_flg => NULL,
x_target_group => NULL,
x_coa_fixed => NULL,
x_coa_pell => NULL,
x_profile_status => NULL,
x_profile_status_date => NULL,
x_profile_fc => NULL,
x_manual_disb_hold => NULL,
x_pell_alt_expense => NULL,
x_assoc_org_num => NULL,
x_award_fmly_contribution_type => p_award_fmly_contribution_type,
x_isir_locked_by => NULL,
x_adnl_unsub_loan_elig_flag => 'N',
x_lock_awd_flag => 'N',
x_lock_coa_flag => 'N'
);
PROCEDURE update_row(p_int_data_rec IN c_int_data%ROWTYPE,
p_base_id IN NUMBER,
p_rowid IN VARCHAR2,
p_isir_id IN NUMBER
) AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : update the isir matched table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
igf_ap_isir_matched_pkg.update_row(
x_Mode => 'R',
x_rowid => p_rowid,
x_isir_id => p_isir_id,
x_base_id => p_base_id,
x_batch_year => p_int_data_rec.batch_year_num,
x_transaction_num => p_int_data_rec.transaction_num_txt,
x_current_ssn => p_int_data_rec.current_ssn_txt,
x_ssn_name_change => p_int_data_rec.ssn_name_change_type,
x_original_ssn => p_int_data_rec.original_ssn_txt,
x_orig_name_id => p_int_data_rec.orig_name_id_txt,
x_last_name => p_int_data_rec.last_name,
x_first_name => p_int_data_rec.first_name,
x_middle_initial => p_int_data_rec.middle_initial_txt,
x_perm_mail_add => p_int_data_rec.perm_mail_address_txt,
x_perm_city => p_int_data_rec.perm_city_txt,
x_perm_state => p_int_data_rec.perm_state_txt,
x_perm_zip_code => p_int_data_rec.perm_zip_cd,
x_date_of_birth => p_int_data_rec.birth_date,
x_phone_number => p_int_data_rec.phone_number_txt,
x_driver_license_number => p_int_data_rec.driver_license_number_txt,
x_driver_license_state => p_int_data_rec.driver_license_state_txt,
x_citizenship_status => p_int_data_rec.citizenship_status_type,
x_alien_reg_number => p_int_data_rec.alien_reg_number_txt,
x_s_marital_status => p_int_data_rec.s_marital_status_type,
x_s_marital_status_date => p_int_data_rec.s_marital_status_date,
x_summ_enrl_status => p_int_data_rec.summ_enrl_status_type,
x_fall_enrl_status => p_int_data_rec.fall_enrl_status_type,
x_winter_enrl_status => p_int_data_rec.winter_enrl_status_type,
x_spring_enrl_status => p_int_data_rec.spring_enrl_status_type,
x_summ2_enrl_status => p_int_data_rec.summ2_enrl_status_type,
x_fathers_highest_edu_level => p_int_data_rec.fathers_highest_edu_level_type,
x_mothers_highest_edu_level => p_int_data_rec.mothers_highest_edu_level_type,
x_s_state_legal_residence => p_int_data_rec.s_state_legal_residence,
x_legal_residence_before_date => p_int_data_rec.legal_res_before_year_flag,
x_s_legal_resd_date => p_int_data_rec.s_legal_resd_date,
x_ss_r_u_male => p_int_data_rec.ss_r_u_male_flag,
x_selective_service_reg => p_int_data_rec.selective_service_reg_flag,
x_degree_certification => p_int_data_rec.degree_certification_type,
x_grade_level_in_college => p_int_data_rec.grade_level_in_college_type,
x_high_school_diploma_ged => p_int_data_rec.high_school_diploma_ged_flag,
x_first_bachelor_deg_by_date => p_int_data_rec.first_bachelor_deg_year_flag,
x_interest_in_loan => p_int_data_rec.interest_in_loan_flag,
x_interest_in_stud_employment => p_int_data_rec.interest_in_stu_employmnt_flag,
x_drug_offence_conviction => p_int_data_rec.drug_offence_conviction_type,
x_s_tax_return_status => p_int_data_rec.s_tax_return_status_type,
x_s_type_tax_return => p_int_data_rec.s_type_tax_return_type,
x_s_elig_1040ez => p_int_data_rec.s_elig_1040ez_type,
x_s_adjusted_gross_income => p_int_data_rec.s_adjusted_gross_income_amt,
x_s_fed_taxes_paid => p_int_data_rec.s_fed_taxes_paid_amt,
x_s_exemptions => p_int_data_rec.s_exemptions_amt,
x_s_income_from_work => p_int_data_rec.s_income_from_work_amt,
x_spouse_income_from_work => p_int_data_rec.spouse_income_from_work_amt,
x_s_toa_amt_from_wsa => p_int_data_rec.s_total_from_wsa_amt,
x_s_toa_amt_from_wsb => p_int_data_rec.s_total_from_wsb_amt,
x_s_toa_amt_from_wsc => p_int_data_rec.s_total_from_wsc_amt,
x_s_investment_networth => p_int_data_rec.s_investment_networth_amt,
x_s_busi_farm_networth => p_int_data_rec.s_busi_farm_networth_amt,
x_s_cash_savings => p_int_data_rec.s_cash_savings_amt,
x_va_months => p_int_data_rec.va_months_num,
x_va_amount => p_int_data_rec.va_amt,
x_stud_dob_before_date => p_int_data_rec.stud_dob_before_year_flag,
x_deg_beyond_bachelor => p_int_data_rec.deg_beyond_bachelor_flag,
x_s_married => p_int_data_rec.s_married_flag,
x_s_have_children => p_int_data_rec.s_have_children_flag,
x_legal_dependents => p_int_data_rec.legal_dependents_flag,
x_orphan_ward_of_court => p_int_data_rec.orphan_ward_of_court_flag,
x_s_veteran => p_int_data_rec.s_veteran_flag,
x_p_marital_status => p_int_data_rec.p_marital_status_type,
x_father_ssn => p_int_data_rec.father_ssn_txt,
x_f_last_name => p_int_data_rec.f_last_name,
x_mother_ssn => p_int_data_rec.mother_ssn_txt,
x_m_last_name => p_int_data_rec.m_last_name,
x_p_num_family_member => p_int_data_rec.p_family_members_num,
x_p_num_in_college => p_int_data_rec.p_in_college_num,
x_p_state_legal_residence => p_int_data_rec.p_state_legal_residence_txt,
x_p_state_legal_res_before_dt => p_int_data_rec.p_legal_res_before_dt_flag,
x_p_legal_res_date => p_int_data_rec.p_legal_res_date,
x_age_older_parent => p_int_data_rec.age_older_parent_num,
x_p_tax_return_status => p_int_data_rec.p_tax_return_status_type,
x_p_type_tax_return => p_int_data_rec.p_type_tax_return_type,
x_p_elig_1040aez => p_int_data_rec.p_elig_1040aez_type,
x_p_adjusted_gross_income => p_int_data_rec.p_adjusted_gross_income_amt,
x_p_taxes_paid => p_int_data_rec.p_taxes_paid_amt,
x_p_exemptions => p_int_data_rec.p_exemptions_amt,
x_f_income_work => p_int_data_rec.f_income_work_amt,
x_m_income_work => p_int_data_rec.m_income_work_amt,
x_p_income_wsa => p_int_data_rec.p_income_wsa_amt,
x_p_income_wsb => p_int_data_rec.p_income_wsb_amt,
x_p_income_wsc => p_int_data_rec.p_income_wsc_amt,
x_p_investment_networth => p_int_data_rec.p_investment_networth_amt,
x_p_business_networth => p_int_data_rec.p_business_networth_amt,
x_p_cash_saving => p_int_data_rec.p_cash_saving_amt,
x_s_num_family_members => p_int_data_rec.s_family_members_num,
x_s_num_in_college => p_int_data_rec.s_in_college_num,
x_first_college => p_int_data_rec.first_college_cd,
x_first_house_plan => p_int_data_rec.first_house_plan_type,
x_second_college => p_int_data_rec.second_college_cd,
x_second_house_plan => p_int_data_rec.second_house_plan_type,
x_third_college => p_int_data_rec.third_college_cd,
x_third_house_plan => p_int_data_rec.third_house_plan_type,
x_fourth_college => p_int_data_rec.fourth_college_cd,
x_fourth_house_plan => p_int_data_rec.fourth_house_plan_type,
x_fifth_college => p_int_data_rec.fifth_college_cd,
x_fifth_house_plan => p_int_data_rec.fifth_house_plan_type,
x_sixth_college => p_int_data_rec.sixth_college_cd,
x_sixth_house_plan => p_int_data_rec.sixth_house_plan_type,
x_date_app_completed => p_int_data_rec.app_completed_date,
x_signed_by => p_int_data_rec.signed_by_type,
x_preparer_ssn => p_int_data_rec.preparer_ssn_txt,
x_preparer_emp_id_number => p_int_data_rec.preparer_emp_id_number_txt,
x_preparer_sign => p_int_data_rec.preparer_sign_flag,
x_transaction_receipt_date => p_int_data_rec.transaction_receipt_date,
x_dependency_override_ind => p_int_data_rec.dependency_override_type,
x_faa_fedral_schl_code => p_int_data_rec.faa_fedral_schl_cd,
x_faa_adjustment => p_int_data_rec.faa_adjustment_type,
x_input_record_type => p_int_data_rec.input_record_type,
x_serial_number => p_int_data_rec.serial_num,
x_batch_number => p_int_data_rec.batch_number_txt,
x_early_analysis_flag => p_int_data_rec.early_analysis_flag,
x_app_entry_source_code => p_int_data_rec.app_entry_source_type,
x_eti_destination_code => p_int_data_rec.eti_destination_cd,
x_reject_override_b => p_int_data_rec.reject_override_b_flag,
x_reject_override_n => p_int_data_rec.reject_override_n_flag,
x_reject_override_w => p_int_data_rec.reject_override_w_flag,
x_assum_override_1 => p_int_data_rec.assum_override_1_flag,
x_assum_override_2 => p_int_data_rec.assum_override_2_flag,
x_assum_override_3 => p_int_data_rec.assum_override_3_flag,
x_assum_override_4 => p_int_data_rec.assum_override_4_flag,
x_assum_override_5 => p_int_data_rec.assum_override_5_flag,
x_assum_override_6 => p_int_data_rec.assum_override_6_flag,
x_dependency_status => p_int_data_rec.dependency_status_type,
x_s_email_address => p_int_data_rec.s_email_address_txt,
x_nslds_reason_code => p_int_data_rec.nslds_reason_cd,
x_app_receipt_date => p_int_data_rec.app_receipt_date,
x_processed_rec_type => p_int_data_rec.processed_rec_type,
x_hist_correction_for_tran_id => p_int_data_rec.hist_corr_for_tran_num,
x_system_generated_indicator => p_int_data_rec.sys_generated_indicator_type,
x_dup_request_indicator => p_int_data_rec.dup_request_indicator_type,
x_source_of_correction => p_int_data_rec.source_of_correction_type,
x_p_cal_tax_status => p_int_data_rec.p_cal_tax_status_type,
x_s_cal_tax_status => p_int_data_rec.s_cal_tax_status_type,
x_graduate_flag => p_int_data_rec.graduate_flag,
x_auto_zero_efc => p_int_data_rec.auto_zero_efc_flag,
x_efc_change_flag => p_int_data_rec.efc_change_flag,
x_sarc_flag => p_int_data_rec.sarc_flag,
x_simplified_need_test => p_int_data_rec.simplified_need_test_flag,
x_reject_reason_codes => p_int_data_rec.reject_reason_codes_txt,
x_select_service_match_flag => p_int_data_rec.select_service_match_type,
x_select_service_reg_flag => p_int_data_rec.select_service_reg_type,
x_ins_match_flag => p_int_data_rec.ins_match_flag,
x_ins_verification_number => NULL,
x_sec_ins_match_flag => p_int_data_rec.sec_ins_match_type,
x_sec_ins_ver_number => p_int_data_rec.sec_ins_ver_num,
x_ssn_match_flag => p_int_data_rec.ssn_match_type,
x_ssa_citizenship_flag => p_int_data_rec.ssa_citizenship_type,
x_ssn_date_of_death => p_int_data_rec.ssn_death_date,
x_nslds_match_flag => p_int_data_rec.nslds_match_type,
x_va_match_flag => p_int_data_rec.va_match_type,
x_prisoner_match => p_int_data_rec.prisoner_match_flag,
x_verification_flag => p_int_data_rec.verification_flag,
x_subsequent_app_flag => p_int_data_rec.subsequent_app_flag,
x_app_source_site_code => p_int_data_rec.app_source_site_cd,
x_tran_source_site_code => p_int_data_rec.tran_source_site_cd,
x_drn => p_int_data_rec.drn_num,
x_tran_process_date => p_int_data_rec.tran_process_date,
x_computer_batch_number => p_int_data_rec.computer_batch_num,
x_correction_flags => p_int_data_rec.correction_flags_txt,
x_highlight_flags => p_int_data_rec.highlight_flags_txt,
x_paid_efc => NULL,
x_primary_efc => p_int_data_rec.primary_efc_amt,
x_secondary_efc => p_int_data_rec.secondary_efc_amt,
x_fed_pell_grant_efc_type => NULL,
x_primary_efc_type => p_int_data_rec.primary_efc_type,
x_sec_efc_type => p_int_data_rec.sec_efc_type,
x_primary_alternate_month_1 => p_int_data_rec.primary_alt_month_1_amt,
x_primary_alternate_month_2 => p_int_data_rec.primary_alt_month_2_amt,
x_primary_alternate_month_3 => p_int_data_rec.primary_alt_month_3_amt,
x_primary_alternate_month_4 => p_int_data_rec.primary_alt_month_4_amt,
x_primary_alternate_month_5 => p_int_data_rec.primary_alt_month_5_amt,
x_primary_alternate_month_6 => p_int_data_rec.primary_alt_month_6_amt,
x_primary_alternate_month_7 => p_int_data_rec.primary_alt_month_7_amt,
x_primary_alternate_month_8 => p_int_data_rec.primary_alt_month_8_amt,
x_primary_alternate_month_10 => p_int_data_rec.primary_alt_month_10_amt,
x_primary_alternate_month_11 => p_int_data_rec.primary_alt_month_11_amt,
x_primary_alternate_month_12 => p_int_data_rec.primary_alt_month_12_amt,
x_sec_alternate_month_1 => p_int_data_rec.sec_alternate_month_1_amt,
x_sec_alternate_month_2 => p_int_data_rec.sec_alternate_month_2_amt,
x_sec_alternate_month_3 => p_int_data_rec.sec_alternate_month_3_amt,
x_sec_alternate_month_4 => p_int_data_rec.sec_alternate_month_4_amt,
x_sec_alternate_month_5 => p_int_data_rec.sec_alternate_month_5_amt,
x_sec_alternate_month_6 => p_int_data_rec.sec_alternate_month_6_amt,
x_sec_alternate_month_7 => p_int_data_rec.sec_alternate_month_7_amt,
x_sec_alternate_month_8 => p_int_data_rec.sec_alternate_month_8_amt,
x_sec_alternate_month_10 => p_int_data_rec.sec_alternate_month_10_amt,
x_sec_alternate_month_11 => p_int_data_rec.sec_alternate_month_11_amt,
x_sec_alternate_month_12 => p_int_data_rec.sec_alternate_month_12_amt,
x_total_income => p_int_data_rec.total_income_amt,
x_allow_total_income => p_int_data_rec.allow_total_income_amt,
x_state_tax_allow => p_int_data_rec.state_tax_allow_amt,
x_employment_allow => p_int_data_rec.employment_allow_amt,
x_income_protection_allow => p_int_data_rec.income_protection_allow_amt,
x_available_income => p_int_data_rec.available_income_amt,
x_contribution_from_ai => p_int_data_rec.contribution_from_ai_amt,
x_discretionary_networth => p_int_data_rec.discretionary_networth_amt,
x_efc_networth => p_int_data_rec.efc_networth_amt,
x_asset_protect_allow => p_int_data_rec.asset_protect_allow_amt,
x_parents_cont_from_assets => p_int_data_rec.parents_cont_from_assets_amt,
x_adjusted_available_income => p_int_data_rec.adjusted_available_income_amt,
x_total_student_contribution => p_int_data_rec.total_student_contribution_amt,
x_total_parent_contribution => p_int_data_rec.total_parent_contribution_amt,
x_parents_contribution => p_int_data_rec.parents_contribution_amt,
x_student_total_income => p_int_data_rec.student_total_income_amt,
x_sati => p_int_data_rec.sati_amt,
x_sic => p_int_data_rec.sic_amt,
x_sdnw => p_int_data_rec.sdnw_amt,
x_sca => p_int_data_rec.sca_amt,
x_fti => p_int_data_rec.fti_amt,
x_secti => p_int_data_rec.secti_amt,
x_secati => p_int_data_rec.secati_amt,
x_secstx => p_int_data_rec.secstx_amt,
x_secea => p_int_data_rec.secea_amt,
x_secipa => p_int_data_rec.secipa_amt,
x_secai => p_int_data_rec.secai_amt,
x_seccai => p_int_data_rec.seccai_amt,
x_secdnw => p_int_data_rec.secdnw_amt,
x_secnw => p_int_data_rec.secnw_amt,
x_secapa => p_int_data_rec.secapa_amt,
x_secpca => p_int_data_rec.secpca_amt,
x_secaai => p_int_data_rec.secaai_amt,
x_sectsc => p_int_data_rec.sectsc_amt,
x_sectpc => p_int_data_rec.sectpc_amt,
x_secpc => p_int_data_rec.secpc_amt,
x_secsti => p_int_data_rec.secsti_amt,
x_secsic => p_int_data_rec.secsati_amt,
x_secsati => p_int_data_rec.secsic_amt,
x_secsdnw => p_int_data_rec.secsdnw_amt,
x_secsca => p_int_data_rec.secsca_amt,
x_secfti => p_int_data_rec.secfti_amt,
x_a_citizenship => p_int_data_rec.a_citizenship_flag,
x_a_student_marital_status => p_int_data_rec.a_student_marital_status_flag,
x_a_student_agi => p_int_data_rec.a_student_agi_amt,
x_a_s_us_tax_paid => p_int_data_rec.a_s_us_tax_paid_amt,
x_a_s_income_work => p_int_data_rec.a_s_income_work_amt,
x_a_spouse_income_work => p_int_data_rec.a_spouse_income_work_amt,
x_a_s_total_wsc => p_int_data_rec.a_s_total_wsc_amt,
x_a_date_of_birth => p_int_data_rec.a_date_of_birth_flag,
x_a_student_married => p_int_data_rec.a_student_married_flag,
x_a_have_children => p_int_data_rec.a_have_children_flag,
x_a_s_have_dependents => p_int_data_rec.a_s_have_dependents_flag,
x_a_va_status => p_int_data_rec.a_va_status_flag,
x_a_s_num_in_family => p_int_data_rec.a_s_in_family_num,
x_a_s_num_in_college => p_int_data_rec.a_s_in_college_num,
x_a_p_marital_status => p_int_data_rec.a_p_marital_status_flag,
x_a_father_ssn => p_int_data_rec.a_father_ssn_txt,
x_a_mother_ssn => p_int_data_rec.a_mother_ssn_txt,
x_a_parents_num_family => p_int_data_rec.a_parents_family_num,
x_a_parents_num_college => p_int_data_rec.a_parents_college_num,
x_a_parents_agi => p_int_data_rec.a_parents_agi_amt,
x_a_p_us_tax_paid => p_int_data_rec.a_p_us_tax_paid_amt,
x_a_f_work_income => p_int_data_rec.a_f_work_income_amt,
x_a_m_work_income => p_int_data_rec.a_m_work_income_amt,
x_a_p_total_wsc => p_int_data_rec.a_p_total_wsc_amt,
x_comment_codes => p_int_data_rec.comment_codes_txt,
x_sar_ack_comm_code => p_int_data_rec.sar_ack_comm_codes_txt,
x_pell_grant_elig_flag => p_int_data_rec.pell_grant_elig_flag,
x_reprocess_reason_code => p_int_data_rec.reprocess_reason_cd,
x_duplicate_date => p_int_data_rec.duplicate_date,
x_isir_transaction_type => p_int_data_rec.isir_transaction_type,
x_fedral_schl_code_indicator => p_int_data_rec.fedral_schl_type,
x_multi_school_code_flags => p_int_data_rec.multi_school_cd_flags_txt,
x_dup_ssn_indicator => p_int_data_rec.dup_ssn_indicator_flag,
x_system_record_type => 'ORIGINAL',
x_payment_isir => NULL,
x_receipt_status => NULL,
x_isir_receipt_completed => NULL,
x_active_isir => NULL,
x_fafsa_data_verify_flags => p_int_data_rec.fafsa_data_verification_txt,
x_reject_override_a => p_int_data_rec.reject_override_a_flag,
x_reject_override_c => p_int_data_rec.reject_override_c_flag,
x_parent_marital_status_date => p_int_data_rec.parent_marital_status_date,
x_legacy_record_flag => 'Y',
x_father_first_name_initial => p_int_data_rec.father_first_name_initial_txt,
x_father_step_father_birth_dt => p_int_data_rec.father_step_father_birth_date,
x_mother_first_name_initial => p_int_data_rec.mother_first_name_initial_txt,
x_mother_step_mother_birth_dt => p_int_data_rec.mother_step_mother_birth_date,
x_parents_email_address_txt => p_int_data_rec.parents_email_address_txt,
x_address_change_type => p_int_data_rec.address_change_type,
x_cps_pushed_isir_flag => p_int_data_rec.cps_pushed_isir_flag,
x_electronic_transaction_type => p_int_data_rec.electronic_transaction_type,
x_sar_c_change_type => p_int_data_rec.sar_c_change_type,
x_father_ssn_match_type => p_int_data_rec.father_ssn_match_type,
x_mother_ssn_match_type => p_int_data_rec.mother_ssn_match_type,
x_reject_override_g_flag => p_int_data_rec.reject_override_g_flag,
x_dhs_verification_num_txt => p_int_data_rec.dhs_verification_num_txt,
x_data_file_name_txt => p_int_data_rec.data_file_name_txt,
x_message_class_txt => p_int_data_rec.message_class_txt,
x_reject_override_3_flag => p_int_data_rec.reject_override_3_flag,
x_reject_override_12_flag => p_int_data_rec.reject_override_12_flag,
x_reject_override_j_flag => p_int_data_rec.reject_override_j_flag,
x_reject_override_k_flag => p_int_data_rec.reject_override_k_flag,
x_rejected_status_change_flag => p_int_data_rec.rejected_status_change_flag,
x_verification_selection_flag => p_int_data_rec.verification_selection_flag
);
END update_row;
PROCEDURE insert_row( p_int_data_rec IN c_int_data%ROWTYPE,
p_base_id IN NUMBER,
pv_isir_id OUT NOCOPY NUMBER)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : insert into the isir matched table
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
l_rowid VARCHAR2(30);
igf_ap_isir_matched_pkg.insert_row(
x_Mode => 'R',
x_rowid => l_rowid,
x_isir_id => l_isir_id,
x_base_id => p_base_id,
x_batch_year => p_int_data_rec.batch_year_num,
x_transaction_num => p_int_data_rec.transaction_num_txt,
x_current_ssn => p_int_data_rec.current_ssn_txt,
x_ssn_name_change => p_int_data_rec.ssn_name_change_type,
x_original_ssn => p_int_data_rec.original_ssn_txt,
x_orig_name_id => p_int_data_rec.orig_name_id_txt,
x_last_name => p_int_data_rec.last_name,
x_first_name => p_int_data_rec.first_name,
x_middle_initial => p_int_data_rec.middle_initial_txt,
x_perm_mail_add => p_int_data_rec.perm_mail_address_txt,
x_perm_city => p_int_data_rec.perm_city_txt,
x_perm_state => p_int_data_rec.perm_state_txt,
x_perm_zip_code => p_int_data_rec.perm_zip_cd,
x_date_of_birth => p_int_data_rec.birth_date,
x_phone_number => p_int_data_rec.phone_number_txt,
x_driver_license_number => p_int_data_rec.driver_license_number_txt,
x_driver_license_state => p_int_data_rec.driver_license_state_txt,
x_citizenship_status => p_int_data_rec.citizenship_status_type,
x_alien_reg_number => p_int_data_rec.alien_reg_number_txt,
x_s_marital_status => p_int_data_rec.s_marital_status_type,
x_s_marital_status_date => p_int_data_rec.s_marital_status_date,
x_summ_enrl_status => p_int_data_rec.summ_enrl_status_type,
x_fall_enrl_status => p_int_data_rec.fall_enrl_status_type,
x_winter_enrl_status => p_int_data_rec.winter_enrl_status_type,
x_spring_enrl_status => p_int_data_rec.spring_enrl_status_type,
x_summ2_enrl_status => p_int_data_rec.summ2_enrl_status_type,
x_fathers_highest_edu_level => p_int_data_rec.fathers_highest_edu_level_type,
x_mothers_highest_edu_level => p_int_data_rec.mothers_highest_edu_level_type,
x_s_state_legal_residence => p_int_data_rec.s_state_legal_residence,
x_legal_residence_before_date => p_int_data_rec.legal_res_before_year_flag,
x_s_legal_resd_date => p_int_data_rec.s_legal_resd_date,
x_ss_r_u_male => p_int_data_rec.ss_r_u_male_flag,
x_selective_service_reg => p_int_data_rec.selective_service_reg_flag,
x_degree_certification => p_int_data_rec.degree_certification_type,
x_grade_level_in_college => p_int_data_rec.grade_level_in_college_type,
x_high_school_diploma_ged => p_int_data_rec.high_school_diploma_ged_flag,
x_first_bachelor_deg_by_date => p_int_data_rec.first_bachelor_deg_year_flag,
x_interest_in_loan => p_int_data_rec.interest_in_loan_flag,
x_interest_in_stud_employment => p_int_data_rec.interest_in_stu_employmnt_flag,
x_drug_offence_conviction => p_int_data_rec.drug_offence_conviction_type,
x_s_tax_return_status => p_int_data_rec.s_tax_return_status_type,
x_s_type_tax_return => p_int_data_rec.s_type_tax_return_type,
x_s_elig_1040ez => p_int_data_rec.s_elig_1040ez_type,
x_s_adjusted_gross_income => p_int_data_rec.s_adjusted_gross_income_amt,
x_s_fed_taxes_paid => p_int_data_rec.s_fed_taxes_paid_amt,
x_s_exemptions => p_int_data_rec.s_exemptions_amt,
x_s_income_from_work => p_int_data_rec.s_income_from_work_amt,
x_spouse_income_from_work => p_int_data_rec.spouse_income_from_work_amt,
x_s_toa_amt_from_wsa => p_int_data_rec.s_total_from_wsa_amt,
x_s_toa_amt_from_wsb => p_int_data_rec.s_total_from_wsb_amt,
x_s_toa_amt_from_wsc => p_int_data_rec.s_total_from_wsc_amt,
x_s_investment_networth => p_int_data_rec.s_investment_networth_amt,
x_s_busi_farm_networth => p_int_data_rec.s_busi_farm_networth_amt,
x_s_cash_savings => p_int_data_rec.s_cash_savings_amt,
x_va_months => p_int_data_rec.va_months_num,
x_va_amount => p_int_data_rec.va_amt,
x_stud_dob_before_date => p_int_data_rec.stud_dob_before_year_flag,
x_deg_beyond_bachelor => p_int_data_rec.deg_beyond_bachelor_flag,
x_s_married => p_int_data_rec.s_married_flag,
x_s_have_children => p_int_data_rec.s_have_children_flag,
x_legal_dependents => p_int_data_rec.legal_dependents_flag,
x_orphan_ward_of_court => p_int_data_rec.orphan_ward_of_court_flag,
x_s_veteran => p_int_data_rec.s_veteran_flag,
x_p_marital_status => p_int_data_rec.p_marital_status_type,
x_father_ssn => p_int_data_rec.father_ssn_txt,
x_f_last_name => p_int_data_rec.f_last_name,
x_mother_ssn => p_int_data_rec.mother_ssn_txt,
x_m_last_name => p_int_data_rec.m_last_name,
x_p_num_family_member => p_int_data_rec.p_family_members_num,
x_p_num_in_college => p_int_data_rec.p_in_college_num,
x_p_state_legal_residence => p_int_data_rec.p_state_legal_residence_txt,
x_p_state_legal_res_before_dt => p_int_data_rec.p_legal_res_before_dt_flag,
x_p_legal_res_date => p_int_data_rec.p_legal_res_date,
x_age_older_parent => p_int_data_rec.age_older_parent_num,
x_p_tax_return_status => p_int_data_rec.p_tax_return_status_type,
x_p_type_tax_return => p_int_data_rec.p_type_tax_return_type,
x_p_elig_1040aez => p_int_data_rec.p_elig_1040aez_type,
x_p_adjusted_gross_income => p_int_data_rec.p_adjusted_gross_income_amt,
x_p_taxes_paid => p_int_data_rec.p_taxes_paid_amt,
x_p_exemptions => p_int_data_rec.p_exemptions_amt,
x_f_income_work => p_int_data_rec.f_income_work_amt,
x_m_income_work => p_int_data_rec.m_income_work_amt,
x_p_income_wsa => p_int_data_rec.p_income_wsa_amt,
x_p_income_wsb => p_int_data_rec.p_income_wsb_amt,
x_p_income_wsc => p_int_data_rec.p_income_wsc_amt,
x_p_investment_networth => p_int_data_rec.p_investment_networth_amt,
x_p_business_networth => p_int_data_rec.p_business_networth_amt,
x_p_cash_saving => p_int_data_rec.p_cash_saving_amt,
x_s_num_family_members => p_int_data_rec.s_family_members_num,
x_s_num_in_college => p_int_data_rec.s_in_college_num,
x_first_college => p_int_data_rec.first_college_cd,
x_first_house_plan => p_int_data_rec.first_house_plan_type,
x_second_college => p_int_data_rec.second_college_cd,
x_second_house_plan => p_int_data_rec.second_house_plan_type,
x_third_college => p_int_data_rec.third_college_cd,
x_third_house_plan => p_int_data_rec.third_house_plan_type,
x_fourth_college => p_int_data_rec.fourth_college_cd,
x_fourth_house_plan => p_int_data_rec.fourth_house_plan_type,
x_fifth_college => p_int_data_rec.fifth_college_cd,
x_fifth_house_plan => p_int_data_rec.fifth_house_plan_type,
x_sixth_college => p_int_data_rec.sixth_college_cd,
x_sixth_house_plan => p_int_data_rec.sixth_house_plan_type,
x_date_app_completed => p_int_data_rec.app_completed_date,
x_signed_by => p_int_data_rec.signed_by_type,
x_preparer_ssn => p_int_data_rec.preparer_ssn_txt,
x_preparer_emp_id_number => p_int_data_rec.preparer_emp_id_number_txt,
x_preparer_sign => p_int_data_rec.preparer_sign_flag,
x_transaction_receipt_date => p_int_data_rec.transaction_receipt_date,
x_dependency_override_ind => p_int_data_rec.dependency_override_type,
x_faa_fedral_schl_code => p_int_data_rec.faa_fedral_schl_cd,
x_faa_adjustment => p_int_data_rec.faa_adjustment_type,
x_input_record_type => p_int_data_rec.input_record_type,
x_serial_number => p_int_data_rec.serial_num,
x_batch_number => p_int_data_rec.batch_number_txt,
x_early_analysis_flag => p_int_data_rec.early_analysis_flag,
x_app_entry_source_code => p_int_data_rec.app_entry_source_type,
x_eti_destination_code => p_int_data_rec.eti_destination_cd,
x_reject_override_b => p_int_data_rec.reject_override_b_flag,
x_reject_override_n => p_int_data_rec.reject_override_n_flag,
x_reject_override_w => p_int_data_rec.reject_override_w_flag,
x_assum_override_1 => p_int_data_rec.assum_override_1_flag,
x_assum_override_2 => p_int_data_rec.assum_override_2_flag,
x_assum_override_3 => p_int_data_rec.assum_override_3_flag,
x_assum_override_4 => p_int_data_rec.assum_override_4_flag,
x_assum_override_5 => p_int_data_rec.assum_override_5_flag,
x_assum_override_6 => p_int_data_rec.assum_override_6_flag,
x_dependency_status => p_int_data_rec.dependency_status_type,
x_s_email_address => p_int_data_rec.s_email_address_txt,
x_nslds_reason_code => p_int_data_rec.nslds_reason_cd,
x_app_receipt_date => p_int_data_rec.app_receipt_date,
x_processed_rec_type => p_int_data_rec.processed_rec_type,
x_hist_correction_for_tran_id => p_int_data_rec.hist_corr_for_tran_num,
x_system_generated_indicator => p_int_data_rec.sys_generated_indicator_type,
x_dup_request_indicator => p_int_data_rec.dup_request_indicator_type,
x_source_of_correction => p_int_data_rec.source_of_correction_type,
x_p_cal_tax_status => p_int_data_rec.p_cal_tax_status_type,
x_s_cal_tax_status => p_int_data_rec.s_cal_tax_status_type,
x_graduate_flag => p_int_data_rec.graduate_flag,
x_auto_zero_efc => p_int_data_rec.auto_zero_efc_flag,
x_efc_change_flag => p_int_data_rec.efc_change_flag,
x_sarc_flag => p_int_data_rec.sarc_flag,
x_simplified_need_test => p_int_data_rec.simplified_need_test_flag,
x_reject_reason_codes => p_int_data_rec.reject_reason_codes_txt,
x_select_service_match_flag => p_int_data_rec.select_service_match_type,
x_select_service_reg_flag => p_int_data_rec.select_service_reg_type,
x_ins_match_flag => p_int_data_rec.ins_match_flag,
x_ins_verification_number => NULL,
x_sec_ins_match_flag => p_int_data_rec.sec_ins_match_type,
x_sec_ins_ver_number => p_int_data_rec.sec_ins_ver_num,
x_ssn_match_flag => p_int_data_rec.ssn_match_type,
x_ssa_citizenship_flag => p_int_data_rec.ssa_citizenship_type,
x_ssn_date_of_death => p_int_data_rec.ssn_death_date,
x_nslds_match_flag => p_int_data_rec.nslds_match_type,
x_va_match_flag => p_int_data_rec.va_match_type,
x_prisoner_match => p_int_data_rec.prisoner_match_flag,
x_verification_flag => p_int_data_rec.verification_flag,
x_subsequent_app_flag => p_int_data_rec.subsequent_app_flag,
x_app_source_site_code => p_int_data_rec.app_source_site_cd,
x_tran_source_site_code => p_int_data_rec.tran_source_site_cd,
x_drn => p_int_data_rec.drn_num,
x_tran_process_date => p_int_data_rec.tran_process_date,
x_computer_batch_number => p_int_data_rec.computer_batch_num,
x_correction_flags => p_int_data_rec.correction_flags_txt,
x_highlight_flags => p_int_data_rec.highlight_flags_txt,
x_paid_efc => NULL,
x_primary_efc => p_int_data_rec.primary_efc_amt,
x_secondary_efc => p_int_data_rec.secondary_efc_amt,
x_fed_pell_grant_efc_type => NULL,
x_primary_efc_type => p_int_data_rec.primary_efc_type,
x_sec_efc_type => p_int_data_rec.sec_efc_type,
x_primary_alternate_month_1 => p_int_data_rec.primary_alt_month_1_amt,
x_primary_alternate_month_2 => p_int_data_rec.primary_alt_month_2_amt,
x_primary_alternate_month_3 => p_int_data_rec.primary_alt_month_3_amt,
x_primary_alternate_month_4 => p_int_data_rec.primary_alt_month_4_amt,
x_primary_alternate_month_5 => p_int_data_rec.primary_alt_month_5_amt,
x_primary_alternate_month_6 => p_int_data_rec.primary_alt_month_6_amt,
x_primary_alternate_month_7 => p_int_data_rec.primary_alt_month_7_amt,
x_primary_alternate_month_8 => p_int_data_rec.primary_alt_month_8_amt,
x_primary_alternate_month_10 => p_int_data_rec.primary_alt_month_10_amt,
x_primary_alternate_month_11 => p_int_data_rec.primary_alt_month_11_amt,
x_primary_alternate_month_12 => p_int_data_rec.primary_alt_month_12_amt,
x_sec_alternate_month_1 => p_int_data_rec.sec_alternate_month_1_amt,
x_sec_alternate_month_2 => p_int_data_rec.sec_alternate_month_2_amt,
x_sec_alternate_month_3 => p_int_data_rec.sec_alternate_month_3_amt,
x_sec_alternate_month_4 => p_int_data_rec.sec_alternate_month_4_amt,
x_sec_alternate_month_5 => p_int_data_rec.sec_alternate_month_5_amt,
x_sec_alternate_month_6 => p_int_data_rec.sec_alternate_month_6_amt,
x_sec_alternate_month_7 => p_int_data_rec.sec_alternate_month_7_amt,
x_sec_alternate_month_8 => p_int_data_rec.sec_alternate_month_8_amt,
x_sec_alternate_month_10 => p_int_data_rec.sec_alternate_month_10_amt,
x_sec_alternate_month_11 => p_int_data_rec.sec_alternate_month_11_amt,
x_sec_alternate_month_12 => p_int_data_rec.sec_alternate_month_12_amt,
x_total_income => p_int_data_rec.total_income_amt,
x_allow_total_income => p_int_data_rec.allow_total_income_amt,
x_state_tax_allow => p_int_data_rec.state_tax_allow_amt,
x_employment_allow => p_int_data_rec.employment_allow_amt,
x_income_protection_allow => p_int_data_rec.income_protection_allow_amt,
x_available_income => p_int_data_rec.available_income_amt,
x_contribution_from_ai => p_int_data_rec.contribution_from_ai_amt,
x_discretionary_networth => p_int_data_rec.discretionary_networth_amt,
x_efc_networth => p_int_data_rec.efc_networth_amt,
x_asset_protect_allow => p_int_data_rec.asset_protect_allow_amt,
x_parents_cont_from_assets => p_int_data_rec.parents_cont_from_assets_amt,
x_adjusted_available_income => p_int_data_rec.adjusted_available_income_amt,
x_total_student_contribution => p_int_data_rec.total_student_contribution_amt,
x_total_parent_contribution => p_int_data_rec.total_parent_contribution_amt,
x_parents_contribution => p_int_data_rec.parents_contribution_amt,
x_student_total_income => p_int_data_rec.student_total_income_amt,
x_sati => p_int_data_rec.sati_amt,
x_sic => p_int_data_rec.sic_amt,
x_sdnw => p_int_data_rec.sdnw_amt,
x_sca => p_int_data_rec.sca_amt,
x_fti => p_int_data_rec.fti_amt,
x_secti => p_int_data_rec.secti_amt,
x_secati => p_int_data_rec.secati_amt,
x_secstx => p_int_data_rec.secstx_amt,
x_secea => p_int_data_rec.secea_amt,
x_secipa => p_int_data_rec.secipa_amt,
x_secai => p_int_data_rec.secai_amt,
x_seccai => p_int_data_rec.seccai_amt,
x_secdnw => p_int_data_rec.secdnw_amt,
x_secnw => p_int_data_rec.secnw_amt,
x_secapa => p_int_data_rec.secapa_amt,
x_secpca => p_int_data_rec.secpca_amt,
x_secaai => p_int_data_rec.secaai_amt,
x_sectsc => p_int_data_rec.sectsc_amt,
x_sectpc => p_int_data_rec.sectpc_amt,
x_secpc => p_int_data_rec.secpc_amt,
x_secsti => p_int_data_rec.secsti_amt,
x_secsic => p_int_data_rec.secsati_amt,
x_secsati => p_int_data_rec.secsic_amt,
x_secsdnw => p_int_data_rec.secsdnw_amt,
x_secsca => p_int_data_rec.secsca_amt,
x_secfti => p_int_data_rec.secfti_amt,
x_a_citizenship => p_int_data_rec.a_citizenship_flag,
x_a_student_marital_status => p_int_data_rec.a_student_marital_status_flag,
x_a_student_agi => p_int_data_rec.a_student_agi_amt,
x_a_s_us_tax_paid => p_int_data_rec.a_s_us_tax_paid_amt,
x_a_s_income_work => p_int_data_rec.a_s_income_work_amt,
x_a_spouse_income_work => p_int_data_rec.a_spouse_income_work_amt,
x_a_s_total_wsc => p_int_data_rec.a_s_total_wsc_amt,
x_a_date_of_birth => p_int_data_rec.a_date_of_birth_flag,
x_a_student_married => p_int_data_rec.a_student_married_flag,
x_a_have_children => p_int_data_rec.a_have_children_flag,
x_a_s_have_dependents => p_int_data_rec.a_s_have_dependents_flag,
x_a_va_status => p_int_data_rec.a_va_status_flag,
x_a_s_num_in_family => p_int_data_rec.a_s_in_family_num,
x_a_s_num_in_college => p_int_data_rec.a_s_in_college_num,
x_a_p_marital_status => p_int_data_rec.a_p_marital_status_flag,
x_a_father_ssn => p_int_data_rec.a_father_ssn_txt,
x_a_mother_ssn => p_int_data_rec.a_mother_ssn_txt,
x_a_parents_num_family => p_int_data_rec.a_parents_family_num,
x_a_parents_num_college => p_int_data_rec.a_parents_college_num,
x_a_parents_agi => p_int_data_rec.a_parents_agi_amt,
x_a_p_us_tax_paid => p_int_data_rec.a_p_us_tax_paid_amt,
x_a_f_work_income => p_int_data_rec.a_f_work_income_amt,
x_a_m_work_income => p_int_data_rec.a_m_work_income_amt,
x_a_p_total_wsc => p_int_data_rec.a_p_total_wsc_amt,
x_comment_codes => p_int_data_rec.comment_codes_txt,
x_sar_ack_comm_code => p_int_data_rec.sar_ack_comm_codes_txt,
x_pell_grant_elig_flag => p_int_data_rec.pell_grant_elig_flag,
x_reprocess_reason_code => p_int_data_rec.reprocess_reason_cd,
x_duplicate_date => p_int_data_rec.duplicate_date,
x_isir_transaction_type => p_int_data_rec.isir_transaction_type,
x_fedral_schl_code_indicator => p_int_data_rec.fedral_schl_type,
x_multi_school_code_flags => p_int_data_rec.multi_school_cd_flags_txt,
x_dup_ssn_indicator => p_int_data_rec.dup_ssn_indicator_flag,
x_system_record_type => 'ORIGINAL',
x_payment_isir => NULL,
x_receipt_status => NULL,
x_isir_receipt_completed => NULL,
x_active_isir => NULL,
x_fafsa_data_verify_flags => p_int_data_rec.fafsa_data_verification_txt,
x_reject_override_a => p_int_data_rec.reject_override_a_flag,
x_reject_override_c => p_int_data_rec.reject_override_c_flag,
x_parent_marital_status_date => p_int_data_rec.parent_marital_status_date,
x_legacy_record_flag => 'Y',
x_father_first_name_initial => p_int_data_rec.father_first_name_initial_txt,
x_father_step_father_birth_dt => p_int_data_rec.father_step_father_birth_date,
x_mother_first_name_initial => p_int_data_rec.mother_first_name_initial_txt,
x_mother_step_mother_birth_dt => p_int_data_rec.mother_step_mother_birth_date,
x_parents_email_address_txt => p_int_data_rec.parents_email_address_txt,
x_address_change_type => p_int_data_rec.address_change_type,
x_cps_pushed_isir_flag => p_int_data_rec.cps_pushed_isir_flag,
x_electronic_transaction_type => p_int_data_rec.electronic_transaction_type,
x_sar_c_change_type => p_int_data_rec.sar_c_change_type,
x_father_ssn_match_type => p_int_data_rec.father_ssn_match_type,
x_mother_ssn_match_type => p_int_data_rec.mother_ssn_match_type,
x_reject_override_g_flag => p_int_data_rec.reject_override_g_flag,
x_dhs_verification_num_txt => p_int_data_rec.dhs_verification_num_txt,
x_data_file_name_txt => p_int_data_rec.data_file_name_txt,
x_message_class_txt => p_int_data_rec.message_class_txt,
x_reject_override_3_flag => p_int_data_rec.reject_override_3_flag,
x_reject_override_12_flag => p_int_data_rec.reject_override_12_flag,
x_reject_override_j_flag => p_int_data_rec.reject_override_j_flag,
x_reject_override_k_flag => p_int_data_rec.reject_override_k_flag,
x_rejected_status_change_flag => p_int_data_rec.rejected_status_change_flag,
x_verification_selection_flag => p_int_data_rec.verification_selection_flag
);
END insert_row;
PROCEDURE nslds_insert_row(p_int_data_rec IN c_int_data%ROWTYPE,
p_base_id IN NUMBER,
p_isir_id IN NUMBER)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Insert NSLDS data
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
l_rowid VARCHAR2(30);
igf_ap_nslds_data_pkg.insert_row(
x_mode => 'R',
x_rowid => l_rowid,
x_nslds_id => l_nslds_id,
x_isir_id => p_isir_id,
x_base_id => p_base_id,
x_nslds_transaction_num => p_int_data_rec.transaction_num_txt,
x_nslds_database_results_f => p_int_data_rec.nslds_database_results_type,
x_nslds_f => p_int_data_rec.nslds_flag,
x_nslds_pell_overpay_f => p_int_data_rec.nslds_pell_overpay_type,
x_nslds_pell_overpay_contact => p_int_data_rec.nslds_pell_overpay_contact_txt,
x_nslds_seog_overpay_f => p_int_data_rec.nslds_seog_overpay_type,
x_nslds_seog_overpay_contact => p_int_data_rec.nslds_seog_overpay_contact_txt,
x_nslds_perkins_overpay_f => p_int_data_rec.nslds_perkins_overpay_type,
x_nslds_perkins_overpay_cntct => p_int_data_rec.nslds_perkins_ovrpay_cntct_txt,
x_nslds_defaulted_loan_f => p_int_data_rec.nslds_defaulted_loan_flag,
x_nslds_dischged_loan_chng_f => p_int_data_rec.nslds_discharged_loan_type,
x_nslds_satis_repay_f => p_int_data_rec.nslds_satis_repay_flag,
x_nslds_act_bankruptcy_f => p_int_data_rec.nslds_act_bankruptcy_flag,
x_nslds_agg_subsz_out_prin_bal => p_int_data_rec.nslds_agg_subsz_out_pbal_amt,
x_nslds_agg_unsbz_out_prin_bal => p_int_data_rec.nslds_agg_unsbz_out_pbal_amt,
x_nslds_agg_comb_out_prin_bal => p_int_data_rec.nslds_agg_comb_out_pbal_amt,
x_nslds_agg_cons_out_prin_bal => p_int_data_rec.nslds_agg_cons_out_pbal_amt,
x_nslds_agg_subsz_pend_dismt => p_int_data_rec.nslds_agg_subsz_pend_disb_amt,
x_nslds_agg_unsbz_pend_dismt => p_int_data_rec.nslds_agg_unsbz_pend_disb_amt,
x_nslds_agg_comb_pend_dismt => p_int_data_rec.nslds_agg_comb_pend_disb_amt,
x_nslds_agg_subsz_total => p_int_data_rec.nslds_agg_subsz_total_amt,
x_nslds_agg_unsbz_total => p_int_data_rec.nslds_agg_unsbz_total_amt,
x_nslds_agg_comb_total => p_int_data_rec.nslds_agg_comb_total_amt,
x_nslds_agg_consd_total => p_int_data_rec.nslds_agg_consd_total_amt,
x_nslds_perkins_out_bal => p_int_data_rec.nslds_perkins_out_bal_amt,
x_nslds_perkins_cur_yr_dismnt => p_int_data_rec.nslds_perkins_cur_yr_disb_amt,
x_nslds_default_loan_chng_f => p_int_data_rec.nslds_default_loan_chng_flag,
x_nslds_discharged_loan_f => p_int_data_rec.nslds_dischged_loan_chng_flag,
x_nslds_satis_repay_chng_f => p_int_data_rec.nslds_satis_repay_chng_flag,
x_nslds_act_bnkrupt_chng_f => p_int_data_rec.nslds_act_bnkrupt_chng_flag,
x_nslds_overpay_chng_f => p_int_data_rec.nslds_overpay_chng_flag,
x_nslds_agg_loan_chng_f => p_int_data_rec.nslds_agg_loan_chng_flag,
x_nslds_perkins_loan_chng_f => p_int_data_rec.nslds_perkins_loan_chng_flag,
x_nslds_pell_paymnt_chng_f => p_int_data_rec.nslds_pell_paymnt_chng_flag,
x_nslds_addtnl_pell_f => p_int_data_rec.nslds_addtnl_pell_flag,
x_nslds_addtnl_loan_f => p_int_data_rec.nslds_addtnl_loan_flag,
x_direct_loan_mas_prom_nt_f => p_int_data_rec.direct_loan_mas_prom_nt_type,
x_nslds_pell_seq_num_1 => p_int_data_rec.nslds_pell_1_seq_num,
x_nslds_pell_verify_f_1 => p_int_data_rec.nslds_pell_1_verify_f_txt,
x_nslds_pell_efc_1 => p_int_data_rec.nslds_pell_1_efc_amt,
x_nslds_pell_school_code_1 => p_int_data_rec.nslds_pell_1_school_num,
x_nslds_pell_transcn_num_1 => p_int_data_rec.nslds_pell_1_transcn_num,
x_nslds_pell_last_updt_dt_1 => p_int_data_rec.nslds_pell_1_last_updt_date,
x_nslds_pell_scheduled_amt_1 => p_int_data_rec.nslds_pell_1_scheduled_amt,
x_nslds_pell_amt_paid_todt_1 => p_int_data_rec.nslds_pell_1_paid_todt_amt,
x_nslds_pell_remng_amt_1 => p_int_data_rec.nslds_pell_1_remng_amt,
x_nslds_pell_pc_schd_awd_us_1 => p_int_data_rec.nslds_pell_1_pc_schawd_use_amt,
x_nslds_pell_award_amt_1 => p_int_data_rec.nslds_pell_1_award_amt,
x_nslds_pell_seq_num_2 => p_int_data_rec.nslds_pell_2_seq_num,
x_nslds_pell_verify_f_2 => p_int_data_rec.nslds_pell_2_verify_f_txt,
x_nslds_pell_efc_2 => p_int_data_rec.nslds_pell_2_efc_amt,
x_nslds_pell_school_code_2 => p_int_data_rec.nslds_pell_2_school_num,
x_nslds_pell_transcn_num_2 => p_int_data_rec.nslds_pell_2_transcn_num,
x_nslds_pell_last_updt_dt_2 => p_int_data_rec.nslds_pell_2_last_updt_date,
x_nslds_pell_scheduled_amt_2 => p_int_data_rec.nslds_pell_2_scheduled_amt,
x_nslds_pell_amt_paid_todt_2 => p_int_data_rec.nslds_pell_2_paid_todt_amt,
x_nslds_pell_remng_amt_2 => p_int_data_rec.nslds_pell_2_remng_amt,
x_nslds_pell_pc_schd_awd_us_2 => p_int_data_rec.nslds_pell_2_pc_schawd_use_amt,
x_nslds_pell_award_amt_2 => p_int_data_rec.nslds_pell_2_award_amt,
x_nslds_pell_seq_num_3 => p_int_data_rec.nslds_pell_3_seq_num,
x_nslds_pell_verify_f_3 => p_int_data_rec.nslds_pell_3_verify_f_txt,
x_nslds_pell_efc_3 => p_int_data_rec.nslds_pell_3_efc_amt,
x_nslds_pell_school_code_3 => p_int_data_rec.nslds_pell_3_school_num,
x_nslds_pell_transcn_num_3 => p_int_data_rec.nslds_pell_3_transcn_num,
x_nslds_pell_last_updt_dt_3 => p_int_data_rec.nslds_pell_3_last_updt_date,
x_nslds_pell_scheduled_amt_3 => p_int_data_rec.nslds_pell_3_scheduled_amt,
x_nslds_pell_amt_paid_todt_3 => p_int_data_rec.nslds_pell_3_paid_todt_amt,
x_nslds_pell_remng_amt_3 => p_int_data_rec.nslds_pell_3_remng_amt,
x_nslds_pell_pc_schd_awd_us_3 => p_int_data_rec.nslds_pell_3_pc_schawd_use_amt,
x_nslds_pell_award_amt_3 => p_int_data_rec.nslds_pell_3_award_amt,
x_nslds_loan_seq_num_1 => p_int_data_rec.nslds_loan_1_seq_num,
x_nslds_loan_type_code_1 => p_int_data_rec.nslds_loan_1_type,
x_nslds_loan_chng_f_1 => p_int_data_rec.nslds_loan_1_chng_flag,
x_nslds_loan_prog_code_1 => p_int_data_rec.nslds_loan_1_prog_cd,
x_nslds_loan_net_amnt_1 => p_int_data_rec.nslds_loan_1_net_amt,
x_nslds_loan_cur_st_code_1 => p_int_data_rec.nslds_loan_1_cur_st_cd,
x_nslds_loan_cur_st_date_1 => p_int_data_rec.nslds_loan_1_cur_st_date,
x_nslds_loan_agg_pr_bal_1 => p_int_data_rec.nslds_loan_1_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_1 => p_int_data_rec.nslds_loan_1_out_pr_bal_date,
x_nslds_loan_begin_dt_1 => p_int_data_rec.nslds_loan_1_begin_date,
x_nslds_loan_end_dt_1 => p_int_data_rec.nslds_loan_1_end_date,
x_nslds_loan_ga_code_1 => p_int_data_rec.nslds_loan_1_ga_cd,
x_nslds_loan_cont_type_1 => p_int_data_rec.nslds_loan_1_cont_type,
x_nslds_loan_schol_code_1 => p_int_data_rec.nslds_loan_1_schol_cd,
x_nslds_loan_cont_code_1 => p_int_data_rec.nslds_loan_1_cont_cd,
x_nslds_loan_grade_lvl_1 => p_int_data_rec.nslds_loan_1_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_1 => p_int_data_rec.nslds_loan_1_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_1 => p_int_data_rec.nslds_loan_1_capital_int_flag,
x_nslds_loan_seq_num_2 => p_int_data_rec.nslds_loan_2_seq_num,
x_nslds_loan_type_code_2 => p_int_data_rec.nslds_loan_2_type,
x_nslds_loan_chng_f_2 => p_int_data_rec.nslds_loan_2_chng_flag,
x_nslds_loan_prog_code_2 => p_int_data_rec.nslds_loan_2_prog_cd,
x_nslds_loan_net_amnt_2 => p_int_data_rec.nslds_loan_2_net_amt,
x_nslds_loan_cur_st_code_2 => p_int_data_rec.nslds_loan_2_cur_st_cd,
x_nslds_loan_cur_st_date_2 => p_int_data_rec.nslds_loan_2_cur_st_date,
x_nslds_loan_agg_pr_bal_2 => p_int_data_rec.nslds_loan_2_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_2 => p_int_data_rec.nslds_loan_2_out_pr_bal_date,
x_nslds_loan_begin_dt_2 => p_int_data_rec.nslds_loan_2_begin_date,
x_nslds_loan_end_dt_2 => p_int_data_rec.nslds_loan_2_end_date,
x_nslds_loan_ga_code_2 => p_int_data_rec.nslds_loan_2_ga_cd,
x_nslds_loan_cont_type_2 => p_int_data_rec.nslds_loan_2_cont_type,
x_nslds_loan_schol_code_2 => p_int_data_rec.nslds_loan_2_schol_cd,
x_nslds_loan_cont_code_2 => p_int_data_rec.nslds_loan_2_cont_cd,
x_nslds_loan_grade_lvl_2 => p_int_data_rec.nslds_loan_2_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_2 => p_int_data_rec.nslds_loan_2_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_2 => p_int_data_rec.nslds_loan_2_capital_int_flag,
x_nslds_loan_seq_num_3 => p_int_data_rec.nslds_loan_3_seq_num,
x_nslds_loan_type_code_3 => p_int_data_rec.nslds_loan_3_type,
x_nslds_loan_chng_f_3 => p_int_data_rec.nslds_loan_3_chng_flag,
x_nslds_loan_prog_code_3 => p_int_data_rec.nslds_loan_3_prog_cd,
x_nslds_loan_net_amnt_3 => p_int_data_rec.nslds_loan_3_net_amt,
x_nslds_loan_cur_st_code_3 => p_int_data_rec.nslds_loan_3_cur_st_cd,
x_nslds_loan_cur_st_date_3 => p_int_data_rec.nslds_loan_3_cur_st_date,
x_nslds_loan_agg_pr_bal_3 => p_int_data_rec.nslds_loan_3_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_3 => p_int_data_rec.nslds_loan_3_out_pr_bal_date,
x_nslds_loan_begin_dt_3 => p_int_data_rec.nslds_loan_3_begin_date,
x_nslds_loan_end_dt_3 => p_int_data_rec.nslds_loan_3_end_date,
x_nslds_loan_ga_code_3 => p_int_data_rec.nslds_loan_3_ga_cd,
x_nslds_loan_cont_type_3 => p_int_data_rec.nslds_loan_3_cont_type,
x_nslds_loan_schol_code_3 => p_int_data_rec.nslds_loan_3_schol_cd,
x_nslds_loan_cont_code_3 => p_int_data_rec.nslds_loan_3_cont_cd,
x_nslds_loan_grade_lvl_3 => p_int_data_rec.nslds_loan_3_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_3 => p_int_data_rec.nslds_loan_3_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_3 => p_int_data_rec.nslds_loan_3_capital_int_flag,
x_nslds_loan_seq_num_4 => p_int_data_rec.nslds_loan_4_seq_num,
x_nslds_loan_type_code_4 => p_int_data_rec.nslds_loan_4_type,
x_nslds_loan_chng_f_4 => p_int_data_rec.nslds_loan_4_chng_flag,
x_nslds_loan_prog_code_4 => p_int_data_rec.nslds_loan_4_prog_cd,
x_nslds_loan_net_amnt_4 => p_int_data_rec.nslds_loan_4_net_amt,
x_nslds_loan_cur_st_code_4 => p_int_data_rec.nslds_loan_4_cur_st_cd,
x_nslds_loan_cur_st_date_4 => p_int_data_rec.nslds_loan_4_cur_st_date,
x_nslds_loan_agg_pr_bal_4 => p_int_data_rec.nslds_loan_4_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_4 => p_int_data_rec.nslds_loan_4_out_pr_bal_date,
x_nslds_loan_begin_dt_4 => p_int_data_rec.nslds_loan_4_begin_date,
x_nslds_loan_end_dt_4 => p_int_data_rec.nslds_loan_4_end_date,
x_nslds_loan_ga_code_4 => p_int_data_rec.nslds_loan_4_ga_cd,
x_nslds_loan_cont_type_4 => p_int_data_rec.nslds_loan_4_cont_type,
x_nslds_loan_schol_code_4 => p_int_data_rec.nslds_loan_4_schol_cd,
x_nslds_loan_cont_code_4 => p_int_data_rec.nslds_loan_4_cont_cd,
x_nslds_loan_grade_lvl_4 => p_int_data_rec.nslds_loan_4_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_4 => p_int_data_rec.nslds_loan_4_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_4 => p_int_data_rec.nslds_loan_4_capital_int_flag,
x_nslds_loan_seq_num_5 => p_int_data_rec.nslds_loan_5_seq_num,
x_nslds_loan_type_code_5 => p_int_data_rec.nslds_loan_5_type,
x_nslds_loan_chng_f_5 => p_int_data_rec.nslds_loan_5_chng_flag,
x_nslds_loan_prog_code_5 => p_int_data_rec.nslds_loan_5_prog_cd,
x_nslds_loan_net_amnt_5 => p_int_data_rec.nslds_loan_5_net_amt,
x_nslds_loan_cur_st_code_5 => p_int_data_rec.nslds_loan_5_cur_st_cd,
x_nslds_loan_cur_st_date_5 => p_int_data_rec. nslds_loan_5_cur_st_date,
x_nslds_loan_agg_pr_bal_5 => p_int_data_rec. nslds_loan_5_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_5 => p_int_data_rec. nslds_loan_5_out_pr_bal_date,
x_nslds_loan_begin_dt_5 => p_int_data_rec. nslds_loan_5_begin_date,
x_nslds_loan_end_dt_5 => p_int_data_rec. nslds_loan_5_end_date,
x_nslds_loan_ga_code_5 => p_int_data_rec.nslds_loan_5_ga_cd,
x_nslds_loan_cont_type_5 => p_int_data_rec.nslds_loan_5_cont_type,
x_nslds_loan_schol_code_5 => p_int_data_rec.nslds_loan_5_schol_cd,
x_nslds_loan_cont_code_5 => p_int_data_rec.nslds_loan_5_cont_cd,
x_nslds_loan_grade_lvl_5 => p_int_data_rec.nslds_loan_5_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_5 => p_int_data_rec.nslds_loan_5_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_5 => p_int_data_rec.nslds_loan_5_capital_int_flag,
x_nslds_loan_seq_num_6 => p_int_data_rec.nslds_loan_6_seq_num,
x_nslds_loan_type_code_6 => p_int_data_rec.nslds_loan_6_type,
x_nslds_loan_chng_f_6 => p_int_data_rec.nslds_loan_6_chng_flag,
x_nslds_loan_prog_code_6 => p_int_data_rec.nslds_loan_6_prog_cd,
x_nslds_loan_net_amnt_6 => p_int_data_rec.nslds_loan_6_net_amt,
x_nslds_loan_cur_st_code_6 => p_int_data_rec.nslds_loan_6_cur_st_cd,
x_nslds_loan_cur_st_date_6 => p_int_data_rec.nslds_loan_6_cur_st_date,
x_nslds_loan_agg_pr_bal_6 => p_int_data_rec.nslds_loan_6_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_6 => p_int_data_rec.nslds_loan_6_out_pr_bal_date,
x_nslds_loan_begin_dt_6 => p_int_data_rec.nslds_loan_6_begin_date,
x_nslds_loan_end_dt_6 => p_int_data_rec.nslds_loan_6_end_date,
x_nslds_loan_ga_code_6 => p_int_data_rec.nslds_loan_6_ga_cd,
x_nslds_loan_cont_type_6 => p_int_data_rec.nslds_loan_6_cont_type,
x_nslds_loan_schol_code_6 => p_int_data_rec.nslds_loan_6_schol_cd,
x_nslds_loan_cont_code_6 => p_int_data_rec.nslds_loan_6_cont_cd,
x_nslds_loan_grade_lvl_6 => p_int_data_rec.nslds_loan_6_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_6 => p_int_data_rec.nslds_loan_6_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_6 => p_int_data_rec.nslds_loan_6_capital_int_flag,
x_nslds_loan_last_d_amt_1 => p_int_data_rec.nslds_loan_1_last_disb_amt,
x_nslds_loan_last_d_date_1 => p_int_data_rec.NSLDS_LOAN_1_LAST_DISB_DATE,
x_nslds_loan_last_d_amt_2 => p_int_data_rec.nslds_loan_2_last_disb_amt,
x_nslds_loan_last_d_date_2 => p_int_data_rec.nslds_loan_2_last_disb_date,
x_nslds_loan_last_d_amt_3 => p_int_data_rec.nslds_loan_3_last_disb_amt,
x_nslds_loan_last_d_date_3 => p_int_data_rec.nslds_loan_3_last_disb_date,
x_nslds_loan_last_d_amt_4 => p_int_data_rec.nslds_loan_4_last_disb_amt,
x_nslds_loan_last_d_date_4 => p_int_data_rec.nslds_loan_4_last_disb_date,
x_nslds_loan_last_d_amt_5 => p_int_data_rec.nslds_loan_5_last_disb_amt,
x_nslds_loan_last_d_date_5 => p_int_data_rec.nslds_loan_5_last_disb_date,
x_nslds_loan_last_d_amt_6 => p_int_data_rec.nslds_loan_6_last_disb_amt,
x_nslds_loan_last_d_date_6 => p_int_data_rec.nslds_loan_6_last_disb_date,
x_dlp_master_prom_note_flag => p_int_data_rec.dlp_master_prom_note_type,
x_subsidized_loan_limit_type => p_int_data_rec.subsidized_loan_limit_type,
x_combined_loan_limit_type => p_int_data_rec.combined_loan_limit_type,
x_transaction_num_txt => p_int_data_rec.transaction_num_txt
);
END nslds_insert_row;
PROCEDURE nslds_update_row(p_int_data_rec IN c_int_data%ROWTYPE,
p_base_id IN NUMBER,
p_rowid IN VARCHAR2,
p_nslds_id IN NUMBER,
p_isir_id IN NUMBER)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Update NSLDS data
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
igf_ap_nslds_data_pkg.update_row(
x_mode => 'R',
x_rowid => p_rowid,
x_nslds_id => p_nslds_id,
x_isir_id => p_isir_id,
x_base_id => p_base_id,
x_nslds_transaction_num => p_int_data_rec.transaction_num_txt,
x_nslds_database_results_f => p_int_data_rec.nslds_database_results_type,
x_nslds_f => p_int_data_rec.nslds_flag,
x_nslds_pell_overpay_f => p_int_data_rec.nslds_pell_overpay_type,
x_nslds_pell_overpay_contact => p_int_data_rec.nslds_pell_overpay_contact_txt,
x_nslds_seog_overpay_f => p_int_data_rec.nslds_seog_overpay_type,
x_nslds_seog_overpay_contact => p_int_data_rec.nslds_seog_overpay_contact_txt,
x_nslds_perkins_overpay_f => p_int_data_rec.nslds_perkins_overpay_type,
x_nslds_perkins_overpay_cntct => p_int_data_rec.nslds_perkins_ovrpay_cntct_txt,
x_nslds_defaulted_loan_f => p_int_data_rec.nslds_defaulted_loan_flag,
x_nslds_dischged_loan_chng_f => p_int_data_rec.nslds_discharged_loan_type,
x_nslds_satis_repay_f => p_int_data_rec.nslds_satis_repay_flag,
x_nslds_act_bankruptcy_f => p_int_data_rec.nslds_act_bankruptcy_flag,
x_nslds_agg_subsz_out_prin_bal => p_int_data_rec.nslds_agg_subsz_out_pbal_amt,
x_nslds_agg_unsbz_out_prin_bal => p_int_data_rec.nslds_agg_unsbz_out_pbal_amt,
x_nslds_agg_comb_out_prin_bal => p_int_data_rec.nslds_agg_comb_out_pbal_amt,
x_nslds_agg_cons_out_prin_bal => p_int_data_rec.nslds_agg_cons_out_pbal_amt,
x_nslds_agg_subsz_pend_dismt => p_int_data_rec.nslds_agg_subsz_pend_disb_amt,
x_nslds_agg_unsbz_pend_dismt => p_int_data_rec.nslds_agg_unsbz_pend_disb_amt,
x_nslds_agg_comb_pend_dismt => p_int_data_rec.nslds_agg_comb_pend_disb_amt,
x_nslds_agg_subsz_total => p_int_data_rec.nslds_agg_subsz_total_amt,
x_nslds_agg_unsbz_total => p_int_data_rec.nslds_agg_unsbz_total_amt,
x_nslds_agg_comb_total => p_int_data_rec.nslds_agg_comb_total_amt,
x_nslds_agg_consd_total => p_int_data_rec.nslds_agg_consd_total_amt,
x_nslds_perkins_out_bal => p_int_data_rec.nslds_perkins_out_bal_amt,
x_nslds_perkins_cur_yr_dismnt => p_int_data_rec.nslds_perkins_cur_yr_disb_amt,
x_nslds_default_loan_chng_f => p_int_data_rec.nslds_default_loan_chng_flag,
x_nslds_discharged_loan_f => p_int_data_rec.nslds_dischged_loan_chng_flag,
x_nslds_satis_repay_chng_f => p_int_data_rec.nslds_satis_repay_chng_flag,
x_nslds_act_bnkrupt_chng_f => p_int_data_rec.nslds_act_bnkrupt_chng_flag,
x_nslds_overpay_chng_f => p_int_data_rec.nslds_overpay_chng_flag,
x_nslds_agg_loan_chng_f => p_int_data_rec.nslds_agg_loan_chng_flag,
x_nslds_perkins_loan_chng_f => p_int_data_rec.nslds_perkins_loan_chng_flag,
x_nslds_pell_paymnt_chng_f => p_int_data_rec.nslds_pell_paymnt_chng_flag,
x_nslds_addtnl_pell_f => p_int_data_rec.nslds_addtnl_pell_flag,
x_nslds_addtnl_loan_f => p_int_data_rec.nslds_addtnl_loan_flag,
x_direct_loan_mas_prom_nt_f => p_int_data_rec.direct_loan_mas_prom_nt_type,
x_nslds_pell_seq_num_1 => p_int_data_rec.nslds_pell_1_seq_num,
x_nslds_pell_verify_f_1 => p_int_data_rec.nslds_pell_1_verify_f_txt,
x_nslds_pell_efc_1 => p_int_data_rec.nslds_pell_1_efc_amt,
x_nslds_pell_school_code_1 => p_int_data_rec.nslds_pell_1_school_num,
x_nslds_pell_transcn_num_1 => p_int_data_rec.nslds_pell_1_transcn_num,
x_nslds_pell_last_updt_dt_1 => p_int_data_rec.nslds_pell_1_last_updt_date,
x_nslds_pell_scheduled_amt_1 => p_int_data_rec.nslds_pell_1_scheduled_amt,
x_nslds_pell_amt_paid_todt_1 => p_int_data_rec.nslds_pell_1_paid_todt_amt,
x_nslds_pell_remng_amt_1 => p_int_data_rec.nslds_pell_1_remng_amt,
x_nslds_pell_pc_schd_awd_us_1 => p_int_data_rec.nslds_pell_1_pc_schawd_use_amt,
x_nslds_pell_award_amt_1 => p_int_data_rec.nslds_pell_1_award_amt,
x_nslds_pell_seq_num_2 => p_int_data_rec.nslds_pell_2_seq_num,
x_nslds_pell_verify_f_2 => p_int_data_rec.nslds_pell_2_verify_f_txt,
x_nslds_pell_efc_2 => p_int_data_rec.nslds_pell_2_efc_amt,
x_nslds_pell_school_code_2 => p_int_data_rec.nslds_pell_2_school_num,
x_nslds_pell_transcn_num_2 => p_int_data_rec.nslds_pell_2_transcn_num,
x_nslds_pell_last_updt_dt_2 => p_int_data_rec.nslds_pell_2_last_updt_date,
x_nslds_pell_scheduled_amt_2 => p_int_data_rec.nslds_pell_2_scheduled_amt,
x_nslds_pell_amt_paid_todt_2 => p_int_data_rec.nslds_pell_2_paid_todt_amt,
x_nslds_pell_remng_amt_2 => p_int_data_rec.nslds_pell_2_remng_amt,
x_nslds_pell_pc_schd_awd_us_2 => p_int_data_rec.nslds_pell_2_pc_schawd_use_amt,
x_nslds_pell_award_amt_2 => p_int_data_rec.nslds_pell_2_award_amt,
x_nslds_pell_seq_num_3 => p_int_data_rec.nslds_pell_3_seq_num,
x_nslds_pell_verify_f_3 => p_int_data_rec.nslds_pell_3_verify_f_txt,
x_nslds_pell_efc_3 => p_int_data_rec.nslds_pell_3_efc_amt,
x_nslds_pell_school_code_3 => p_int_data_rec.nslds_pell_3_school_num,
x_nslds_pell_transcn_num_3 => p_int_data_rec.nslds_pell_3_transcn_num,
x_nslds_pell_last_updt_dt_3 => p_int_data_rec.nslds_pell_3_last_updt_date,
x_nslds_pell_scheduled_amt_3 => p_int_data_rec.nslds_pell_3_scheduled_amt,
x_nslds_pell_amt_paid_todt_3 => p_int_data_rec.nslds_pell_3_paid_todt_amt,
x_nslds_pell_remng_amt_3 => p_int_data_rec.nslds_pell_3_remng_amt,
x_nslds_pell_pc_schd_awd_us_3 => p_int_data_rec.nslds_pell_3_pc_schawd_use_amt,
x_nslds_pell_award_amt_3 => p_int_data_rec.nslds_pell_3_award_amt,
x_nslds_loan_seq_num_1 => p_int_data_rec.nslds_loan_1_seq_num,
x_nslds_loan_type_code_1 => p_int_data_rec.nslds_loan_1_type,
x_nslds_loan_chng_f_1 => p_int_data_rec.nslds_loan_1_chng_flag,
x_nslds_loan_prog_code_1 => p_int_data_rec.nslds_loan_1_prog_cd,
x_nslds_loan_net_amnt_1 => p_int_data_rec.nslds_loan_1_net_amt,
x_nslds_loan_cur_st_code_1 => p_int_data_rec.nslds_loan_1_cur_st_cd,
x_nslds_loan_cur_st_date_1 => p_int_data_rec.nslds_loan_1_cur_st_date,
x_nslds_loan_agg_pr_bal_1 => p_int_data_rec.nslds_loan_1_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_1 => p_int_data_rec.nslds_loan_1_out_pr_bal_date,
x_nslds_loan_begin_dt_1 => p_int_data_rec.nslds_loan_1_begin_date,
x_nslds_loan_end_dt_1 => p_int_data_rec.nslds_loan_1_end_date,
x_nslds_loan_ga_code_1 => p_int_data_rec.nslds_loan_1_ga_cd,
x_nslds_loan_cont_type_1 => p_int_data_rec.nslds_loan_1_cont_type,
x_nslds_loan_schol_code_1 => p_int_data_rec.nslds_loan_1_schol_cd,
x_nslds_loan_cont_code_1 => p_int_data_rec.nslds_loan_1_cont_cd,
x_nslds_loan_grade_lvl_1 => p_int_data_rec.nslds_loan_1_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_1 => p_int_data_rec.nslds_loan_1_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_1 => p_int_data_rec.nslds_loan_1_capital_int_flag,
x_nslds_loan_seq_num_2 => p_int_data_rec.nslds_loan_2_seq_num,
x_nslds_loan_type_code_2 => p_int_data_rec.nslds_loan_2_type,
x_nslds_loan_chng_f_2 => p_int_data_rec.nslds_loan_2_chng_flag,
x_nslds_loan_prog_code_2 => p_int_data_rec.nslds_loan_2_prog_cd,
x_nslds_loan_net_amnt_2 => p_int_data_rec.nslds_loan_2_net_amt,
x_nslds_loan_cur_st_code_2 => p_int_data_rec.nslds_loan_2_cur_st_cd,
x_nslds_loan_cur_st_date_2 => p_int_data_rec.nslds_loan_2_cur_st_date,
x_nslds_loan_agg_pr_bal_2 => p_int_data_rec.nslds_loan_2_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_2 => p_int_data_rec.nslds_loan_2_out_pr_bal_date,
x_nslds_loan_begin_dt_2 => p_int_data_rec.nslds_loan_2_begin_date,
x_nslds_loan_end_dt_2 => p_int_data_rec.nslds_loan_2_end_date,
x_nslds_loan_ga_code_2 => p_int_data_rec.nslds_loan_2_ga_cd,
x_nslds_loan_cont_type_2 => p_int_data_rec.nslds_loan_2_cont_type,
x_nslds_loan_schol_code_2 => p_int_data_rec.nslds_loan_2_schol_cd,
x_nslds_loan_cont_code_2 => p_int_data_rec.nslds_loan_2_cont_cd,
x_nslds_loan_grade_lvl_2 => p_int_data_rec.nslds_loan_2_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_2 => p_int_data_rec.nslds_loan_2_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_2 => p_int_data_rec.nslds_loan_2_capital_int_flag,
x_nslds_loan_seq_num_3 => p_int_data_rec.nslds_loan_3_seq_num,
x_nslds_loan_type_code_3 => p_int_data_rec.nslds_loan_3_type,
x_nslds_loan_chng_f_3 => p_int_data_rec.nslds_loan_3_chng_flag,
x_nslds_loan_prog_code_3 => p_int_data_rec.nslds_loan_3_prog_cd,
x_nslds_loan_net_amnt_3 => p_int_data_rec.nslds_loan_3_net_amt,
x_nslds_loan_cur_st_code_3 => p_int_data_rec.nslds_loan_3_cur_st_cd,
x_nslds_loan_cur_st_date_3 => p_int_data_rec. nslds_loan_3_cur_st_date,
x_nslds_loan_agg_pr_bal_3 => p_int_data_rec. nslds_loan_3_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_3 => p_int_data_rec. nslds_loan_3_out_pr_bal_date,
x_nslds_loan_begin_dt_3 => p_int_data_rec. nslds_loan_3_begin_date,
x_nslds_loan_end_dt_3 => p_int_data_rec. nslds_loan_3_end_date,
x_nslds_loan_ga_code_3 => p_int_data_rec.nslds_loan_3_ga_cd,
x_nslds_loan_cont_type_3 => p_int_data_rec.nslds_loan_3_cont_type,
x_nslds_loan_schol_code_3 => p_int_data_rec.nslds_loan_3_schol_cd,
x_nslds_loan_cont_code_3 => p_int_data_rec.nslds_loan_3_cont_cd,
x_nslds_loan_grade_lvl_3 => p_int_data_rec.nslds_loan_3_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_3 => p_int_data_rec.nslds_loan_3_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_3 => p_int_data_rec.nslds_loan_3_capital_int_flag,
x_nslds_loan_seq_num_4 => p_int_data_rec.nslds_loan_4_seq_num,
x_nslds_loan_type_code_4 => p_int_data_rec.nslds_loan_4_type,
x_nslds_loan_chng_f_4 => p_int_data_rec.nslds_loan_4_chng_flag,
x_nslds_loan_prog_code_4 => p_int_data_rec.nslds_loan_4_prog_cd,
x_nslds_loan_net_amnt_4 => p_int_data_rec.nslds_loan_4_net_amt,
x_nslds_loan_cur_st_code_4 => p_int_data_rec.nslds_loan_4_cur_st_cd,
x_nslds_loan_cur_st_date_4 => p_int_data_rec.nslds_loan_4_cur_st_date,
x_nslds_loan_agg_pr_bal_4 => p_int_data_rec.nslds_loan_4_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_4 => p_int_data_rec.nslds_loan_4_out_pr_bal_date,
x_nslds_loan_begin_dt_4 => p_int_data_rec.nslds_loan_4_begin_date,
x_nslds_loan_end_dt_4 => p_int_data_rec.nslds_loan_4_end_date,
x_nslds_loan_ga_code_4 => p_int_data_rec.nslds_loan_4_ga_cd,
x_nslds_loan_cont_type_4 => p_int_data_rec.nslds_loan_4_cont_type,
x_nslds_loan_schol_code_4 => p_int_data_rec.nslds_loan_4_schol_cd,
x_nslds_loan_cont_code_4 => p_int_data_rec.nslds_loan_4_cont_cd,
x_nslds_loan_grade_lvl_4 => p_int_data_rec.nslds_loan_4_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_4 => p_int_data_rec.nslds_loan_4_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_4 => p_int_data_rec.nslds_loan_4_capital_int_flag,
x_nslds_loan_seq_num_5 => p_int_data_rec.nslds_loan_5_seq_num,
x_nslds_loan_type_code_5 => p_int_data_rec.nslds_loan_5_type,
x_nslds_loan_chng_f_5 => p_int_data_rec.nslds_loan_5_chng_flag,
x_nslds_loan_prog_code_5 => p_int_data_rec.nslds_loan_5_prog_cd,
x_nslds_loan_net_amnt_5 => p_int_data_rec.nslds_loan_5_net_amt,
x_nslds_loan_cur_st_code_5 => p_int_data_rec.nslds_loan_5_cur_st_cd,
x_nslds_loan_cur_st_date_5 => p_int_data_rec. nslds_loan_5_cur_st_date,
x_nslds_loan_agg_pr_bal_5 => p_int_data_rec. nslds_loan_5_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_5 => p_int_data_rec. nslds_loan_5_out_pr_bal_date,
x_nslds_loan_begin_dt_5 => p_int_data_rec. nslds_loan_5_begin_date,
x_nslds_loan_end_dt_5 => p_int_data_rec. nslds_loan_5_end_date,
x_nslds_loan_ga_code_5 => p_int_data_rec.nslds_loan_5_ga_cd,
x_nslds_loan_cont_type_5 => p_int_data_rec.nslds_loan_5_cont_type,
x_nslds_loan_schol_code_5 => p_int_data_rec.nslds_loan_5_schol_cd,
x_nslds_loan_cont_code_5 => p_int_data_rec.nslds_loan_5_cont_cd,
x_nslds_loan_grade_lvl_5 => p_int_data_rec.nslds_loan_5_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_5 => p_int_data_rec.nslds_loan_5_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_5 => p_int_data_rec.nslds_loan_5_capital_int_flag,
x_nslds_loan_seq_num_6 => p_int_data_rec.nslds_loan_6_seq_num,
x_nslds_loan_type_code_6 => p_int_data_rec.nslds_loan_6_type,
x_nslds_loan_chng_f_6 => p_int_data_rec.nslds_loan_6_chng_flag,
x_nslds_loan_prog_code_6 => p_int_data_rec.nslds_loan_6_prog_cd,
x_nslds_loan_net_amnt_6 => p_int_data_rec.nslds_loan_6_net_amt,
x_nslds_loan_cur_st_code_6 => p_int_data_rec.nslds_loan_6_cur_st_cd,
x_nslds_loan_cur_st_date_6 => p_int_data_rec.nslds_loan_6_cur_st_date,
x_nslds_loan_agg_pr_bal_6 => p_int_data_rec.nslds_loan_6_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_6 => p_int_data_rec.nslds_loan_6_out_pr_bal_date,
x_nslds_loan_begin_dt_6 => p_int_data_rec.nslds_loan_6_begin_date,
x_nslds_loan_end_dt_6 => p_int_data_rec.nslds_loan_6_end_date,
x_nslds_loan_ga_code_6 => p_int_data_rec.nslds_loan_6_ga_cd,
x_nslds_loan_cont_type_6 => p_int_data_rec.nslds_loan_6_cont_type,
x_nslds_loan_schol_code_6 => p_int_data_rec.nslds_loan_6_schol_cd,
x_nslds_loan_cont_code_6 => p_int_data_rec.nslds_loan_6_cont_cd,
x_nslds_loan_grade_lvl_6 => p_int_data_rec.nslds_loan_6_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_6 => p_int_data_rec.nslds_loan_6_xtr_unsbz_ln_type,
x_nslds_loan_capital_int_f_6 => p_int_data_rec.nslds_loan_6_capital_int_flag,
x_nslds_loan_last_d_amt_1 => p_int_data_rec.nslds_loan_1_last_disb_amt,
x_nslds_loan_last_d_date_1 => p_int_data_rec.NSLDS_LOAN_1_LAST_DISB_DATE,
x_nslds_loan_last_d_amt_2 => p_int_data_rec.nslds_loan_2_last_disb_amt,
x_nslds_loan_last_d_date_2 => p_int_data_rec.nslds_loan_2_last_disb_date,
x_nslds_loan_last_d_amt_3 => p_int_data_rec.nslds_loan_3_last_disb_amt,
x_nslds_loan_last_d_date_3 => p_int_data_rec.nslds_loan_3_last_disb_date,
x_nslds_loan_last_d_amt_4 => p_int_data_rec.nslds_loan_4_last_disb_amt,
x_nslds_loan_last_d_date_4 => p_int_data_rec.nslds_loan_4_last_disb_date,
x_nslds_loan_last_d_amt_5 => p_int_data_rec.nslds_loan_5_last_disb_amt,
x_nslds_loan_last_d_date_5 => p_int_data_rec.nslds_loan_5_last_disb_date,
x_nslds_loan_last_d_amt_6 => p_int_data_rec.nslds_loan_6_last_disb_amt,
x_nslds_loan_last_d_date_6 => p_int_data_rec.nslds_loan_6_last_disb_date,
x_dlp_master_prom_note_flag => p_int_data_rec.dlp_master_prom_note_type,
x_subsidized_loan_limit_type => p_int_data_rec.subsidized_loan_limit_type,
x_combined_loan_limit_type => p_int_data_rec.combined_loan_limit_type,
x_transaction_num_txt => p_int_data_rec.transaction_num_txt
);
END nslds_update_row;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_code=p_lookup_code
AND lookup_type = p_lookup_type
AND enabled_flag = 'Y' ;
SELECT LOOKUP_CODE
FROM IGF_AW_LOOKUPS_VIEW
WHERE LOOKUP_TYPE = p_lookup_type
AND SYS_AWARD_YEAR =p_award_year
AND enabled_flag = 'Y' ;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type ='IGF_AW_LOOKUPS_MSG'
AND lookup_code =p_lookup_code
AND enabled_flag = 'Y' ;
SELECT meaning
FROM igf_aw_lookups_view
WHERE lookup_type ='IGF_AW_LOOKUPS_MSG'
AND lookup_code =p_lookup_code
AND enabled_flag = 'Y' ;
p_igf_ap_message_table.DELETE;
IF p_isir_rec.selective_service_reg_flag IS NOT NULL THEN
IF NOT Val_Num_2(p_isir_rec.selective_service_reg_flag) THEN
p_status:=FALSE;
p_igf_ap_message_table(indx).field_name:= p_l_to_i_col('SELECTIVE_SERVICE_REG_FLAG');
SELECT TRANSLATE (pv_ssn,'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*_+=-,./?><():; ','1234567890')
SELECT
BATCH_YEAR batch_year,
AWARD_YEAR_STATUS_CODE,
SYS_AWARD_YEAR
FROM
IGF_AP_BATCH_AW_MAP
WHERE
CI_CAL_TYPE = p_ci_cal_type
AND CI_SEQUENCE_NUMBER = p_ci_sequence_number;
SELECT
base_id
FROM
igf_ap_fa_base_rec fa
WHERE
fa.ci_cal_type =p_ci_cal_type
AND fa.ci_sequence_number = p_sequence_number
AND fa.person_id = p_person_id;
SELECT
im.transaction_num
FROM
igf_ap_isir_matched im
WHERE
im.base_id =p_base_id and
im.transaction_num = p_transaction_num and
rownum = 1 ;
SELECT meaning
FROM igf_aw_lookups_view
WHERE lookup_type ='IGF_AW_LOOKUPS_MSG'
AND lookup_code =p_lookup_code
AND enabled_flag = 'Y' ;
SELECT
NSLDS.ROW_ID,
NSLDS.NSLDS_ID,
NSLDS.NSLDS_TRANSACTION_NUM
FROM
IGF_AP_NSLDS_DATA NSLDS
WHERE
BASE_ID = p_base_id;
SELECT rowid,isir_id
FROM igf_ap_isir_matched
WHERE transaction_num=p_transn_num
AND base_id=p_base_id
AND system_record_type='ORIGINAL';
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_ci_cal_type
AND sequence_number = cp_ci_sequence_number ;
SELECT award_year_status_code, sys_award_year
FROM igf_ap_batch_aw_map map
WHERE map.ci_cal_type = p_ci_cal_type
AND map.ci_sequence_number = p_ci_sequence_number ;
SELECT 'SSN' rec_type,
api.pe_person_id person_id
FROM igs_pe_alt_pers_id api,
igs_pe_person_id_typ pit
WHERE api.person_id_type = pit.person_id_type
AND pit.s_person_id_type = 'SSN'
AND SYSDATE between api.start_dt AND NVL(api.end_dt,SYSDATE)
AND api.api_person_id_uf = lv_ssn ;
l_updated VARCHAR2(1):='N';
l_update VARCHAR2(2);
SELECT SYS_AWARD_YEAR
FROM IGF_AP_BATCH_AW_MAP
WHERE CI_CAL_TYPE = p_ci_cal_type
AND CI_SEQUENCE_NUMBER=p_ci_sequence_number;
l_update := NULL;
g_message_table.DELETE;
UPDATE igf_ap_li_isir_ints
SET IMPORT_STATUS_TYPE='E'
WHERE ROWID = c_int_data_rec.ROW_ID ;
l_update := 'N';
l_update := 'Y';
ELSE -- Same transaction num does not exists/ So only Insert is possible
IF c_int_data_rec.import_record_type = 'U' AND l_valid_for_dml = 'Y' THEN
-- Update not possible as no such record exists to update
l_debug_str := l_debug_str || lv_person_number || 'Update not possible as no such record exists to update ';
l_update := 'N';
IF l_update = 'Y' AND l_valid_for_dml ='Y' THEN
-- Update ISIS matched table
c_get_rowid_rec := NULL;
update_row(c_int_data_rec, lv_fa_base_id, c_get_rowid_rec.rowid,c_get_rowid_rec.isir_id);
l_debug_str := l_debug_str || lv_person_number || ' ISIR Record updated ';
insert_row( c_int_data_rec, lv_fa_base_id,pv_isir_id);
l_debug_str := l_debug_str || lv_person_number || 'ISIR Record inserted ';
nslds_insert_row(c_int_data_rec, lv_fa_base_id, pv_isir_id);
l_debug_str := l_debug_str || lv_person_number || ' NSLDS Record inserted ';
nslds_update_row(c_int_data_rec, lv_fa_base_id, c_nslds_data_rec.row_id, c_nslds_data_rec.nslds_id, pv_isir_id );
l_debug_str := l_debug_str || lv_person_number || ' NSLDS Record updated ';
DELETE FROM igf_ap_li_isir_ints
WHERE ROWID = c_int_data_rec.ROW_ID ;
UPDATE igf_ap_li_isir_ints
SET IMPORT_STATUS_TYPE='I'
WHERE ROWID = c_int_data_rec.ROW_ID ;
UPDATE igf_ap_isir_ints_all
SET RECORD_STATUS ='MATCHED'
WHERE ROWID = c_int_data_rec.ROW_ID ;
UPDATE igf_ap_li_isir_ints
SET IMPORT_STATUS_TYPE='E'
WHERE ROWID = c_int_data_rec.ROW_ID ;