[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT LOOKUP_CODE
FROM igf_lookups_view
WHERE lookup_type = p_lookup_type
AND tag = p_lookup_code
AND enabled_flag = 'Y';
SELECT base_id,
first_college,
second_college,
third_college,
fourth_college,
fifth_college,
sixth_college
FROM igf_ap_isir_matched
WHERE isir_id = p_ISIR_id;
SELECT batch_year
FROM igf_ap_batch_aw_map_all
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT 'Y'
FROM igf_ap_batch_aw_map
WHERE batch_year = cp_batch_yr;
SELECT 'Y'
FROM igf_lookups_view
WHERE lookup_type = cp_lkup_type
AND lookup_code = cp_lkup_code
AND enabled_flag = 'Y';
SELECT
'Y'
FROM
hz_parties hz,
igs_or_org_alt_ids oli,
igs_or_org_alt_idtyp olt
WHERE oli.org_structure_id = hz.party_number
AND oli.org_alternate_id_type = olt.org_alternate_id_type
AND SYSDATE BETWEEN oli.start_date AND nvl (end_date, SYSDATE)
AND hz.status = 'A'
AND olt.system_id_type = 'FED_SCH_CD'
AND oli.org_alternate_id = cp_school_cd ;
SELECT 'Y'
FROM igf_ap_record_match
WHERE match_code = cp_match_code
AND enabled_flag = 'Y';
PROCEDURE build_selection_criteria
IS
/*
|| Created By : rgangara
|| Created On : 28-JUL-2004
|| Purpose : Builds the dynamic record selection criteria based on parameters.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
log_debug_message(' Beginning Building record Selection criteria ');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.build_selection_criteria.debug', 'Successfully Completed build_selection_criteria procedure.');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_import_pkg.build_selection_criteria.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.BUILD_SELECTION_CRITERIA');
END build_selection_criteria;
UPDATE igf_ap_isir_ints
SET parent_req_id = g_parent_req_number,
sub_req_num = i
WHERE si_id = g_si_id_tab(k);
COMMIT; -- commit the parent request id and sub request number updates to Interface table.
l_sql := 'SELECT si_id, batch_year_num, original_ssn_txt, orig_name_id_txt FROM igf_ap_isir_ints WHERE batch_year_num = ' || g_batch_year ;
build_selection_criteria;
UPDATE igf_ap_isir_ints
SET parent_req_id = g_parent_req_number,
sub_req_num = 1
WHERE si_id = g_si_id_tab(k);
COMMIT; -- commit the parent request id and sub request number updates to Interface table.
PROCEDURE update_matched_ISIR (p_ISIR_id igf_ap_ISIR_matched_all.ISIR_id%TYPE,
p_system_record_type igf_ap_ISIR_matched_all.system_record_type%TYPE,
p_payment_ISIR igf_ap_ISIR_matched_all.payment_ISIR%TYPE,
p_active_ISIR igf_ap_ISIR_matched_all.active_ISIR%TYPE)
IS
/*
|| Created By : brajendr
|| Created On : 08-NOV-2000
|| Purpose : Process which inserts comment codes of the student.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smvk 11-Feb-2003 Bug # 2758812. Added the procedure call igf_gr_gen.update_current_ssn.
|| masehgal 15-Feb-2002 # 2216956 FACR007
|| Added Verif_track_flag
|| (reverse chronological order - newest change first)
*/
CURSOR cur_upd_ISIR IS
SELECT ism.*
FROM igf_ap_ISIR_matched ism
WHERE ism.ISIR_id = p_ISIR_id;
igf_ap_ISIR_matched_pkg.update_row (x_Mode => 'R',
x_rowid => cur_ISIR_rec.row_id,
x_ISIR_id => cur_ISIR_rec.ISIR_id,
x_base_id => cur_ISIR_rec.base_id,
x_batch_year => cur_ISIR_rec.batch_year,
x_transaction_num => cur_ISIR_rec.transaction_num,
x_current_ssn => cur_ISIR_rec.current_ssn,
x_ssn_name_change => cur_ISIR_rec.ssn_name_change,
x_original_ssn => cur_ISIR_rec.original_ssn,
x_orig_name_id => cur_ISIR_rec.orig_name_id,
x_last_name => cur_ISIR_rec.last_name,
x_first_name => cur_ISIR_rec.first_name,
x_middle_initial => cur_ISIR_rec.middle_initial,
x_perm_mail_add => cur_ISIR_rec.perm_mail_add,
x_perm_city => cur_ISIR_rec.perm_city,
x_perm_state => cur_ISIR_rec.perm_state,
x_perm_zip_code => cur_ISIR_rec.perm_zip_code,
x_date_of_birth => cur_ISIR_rec.date_of_birth,
x_phone_number => cur_ISIR_rec.phone_number,
x_driver_license_number => cur_ISIR_rec.driver_license_number,
x_driver_license_state => cur_ISIR_rec.driver_license_state,
x_citizenship_status => cur_ISIR_rec.citizenship_status,
x_alien_reg_number => cur_ISIR_rec.alien_reg_number,
x_s_marital_status => cur_ISIR_rec.s_marital_status,
x_s_marital_status_date => cur_ISIR_rec.s_marital_status_date,
x_summ_enrl_status => cur_ISIR_rec.summ_enrl_status,
x_fall_enrl_status => cur_ISIR_rec.fall_enrl_status,
x_winter_enrl_status => cur_ISIR_rec.winter_enrl_status,
x_spring_enrl_status => cur_ISIR_rec.spring_enrl_status,
x_summ2_enrl_status => cur_ISIR_rec.summ2_enrl_status,
x_fathers_highest_edu_level => cur_ISIR_rec.fathers_highest_edu_level,
x_mothers_highest_edu_level => cur_ISIR_rec.mothers_highest_edu_level,
x_s_state_legal_residence => cur_ISIR_rec.s_state_legal_residence,
x_legal_residence_before_date => cur_ISIR_rec.legal_residence_before_date,
x_s_legal_resd_date => cur_ISIR_rec.s_legal_resd_date,
x_ss_r_u_male => cur_ISIR_rec.ss_r_u_male,
x_selective_service_reg => cur_ISIR_rec.selective_service_reg,
x_degree_certification => cur_ISIR_rec.degree_certification,
x_grade_level_in_college => cur_ISIR_rec.grade_level_in_college,
x_high_school_diploma_ged => cur_ISIR_rec.high_school_diploma_ged,
x_first_bachelor_deg_by_date => cur_ISIR_rec.first_bachelor_deg_by_date,
x_interest_in_loan => cur_ISIR_rec.interest_in_loan,
x_interest_in_stud_employment => cur_ISIR_rec.interest_in_stud_employment,
x_drug_offence_conviction => cur_ISIR_rec.drug_offence_conviction,
x_s_tax_return_status => cur_ISIR_rec.s_tax_return_status,
x_s_type_tax_return => cur_ISIR_rec.s_type_tax_return,
x_s_elig_1040ez => cur_ISIR_rec.s_elig_1040ez,
x_s_adjusted_gross_income => cur_ISIR_rec.s_adjusted_gross_income,
x_s_fed_taxes_paid => cur_ISIR_rec.s_fed_taxes_paid,
x_s_exemptions => cur_ISIR_rec.s_exemptions,
x_s_income_from_work => cur_ISIR_rec.s_income_from_work,
x_spouse_income_from_work => cur_ISIR_rec.spouse_income_from_work,
x_s_toa_amt_from_wsa => cur_ISIR_rec.s_toa_amt_from_wsa,
x_s_toa_amt_from_wsb => cur_ISIR_rec.s_toa_amt_from_wsb,
x_s_toa_amt_from_wsc => cur_ISIR_rec.s_toa_amt_from_wsc,
x_s_investment_networth => cur_ISIR_rec.s_investment_networth,
x_s_busi_farm_networth => cur_ISIR_rec.s_busi_farm_networth,
x_s_cash_savings => cur_ISIR_rec.s_cash_savings,
x_va_months => cur_ISIR_rec.va_months,
x_va_amount => cur_ISIR_rec.va_amount,
x_stud_dob_before_date => cur_ISIR_rec.stud_dob_before_date,
x_deg_beyond_bachelor => cur_ISIR_rec.deg_beyond_bachelor,
x_s_married => cur_ISIR_rec.s_married,
x_s_have_children => cur_ISIR_rec.s_have_children,
x_legal_dependents => cur_ISIR_rec.legal_dependents,
x_orphan_ward_of_court => cur_ISIR_rec.orphan_ward_of_court,
x_s_veteran => cur_ISIR_rec.s_veteran,
x_p_marital_status => cur_ISIR_rec.p_marital_status,
x_father_ssn => cur_ISIR_rec.father_ssn,
x_f_last_name => cur_ISIR_rec.f_last_name,
x_mother_ssn => cur_ISIR_rec.mother_ssn,
x_m_last_name => cur_ISIR_rec.m_last_name,
x_p_num_family_member => cur_ISIR_rec.p_num_family_member,
x_p_num_in_college => cur_ISIR_rec.p_num_in_college,
x_p_state_legal_residence => cur_ISIR_rec.p_state_legal_residence,
x_p_state_legal_res_before_dt => cur_ISIR_rec.p_state_legal_res_before_dt,
x_p_legal_res_date => cur_ISIR_rec.p_legal_res_date,
x_age_older_parent => cur_ISIR_rec.age_older_parent,
x_p_tax_return_status => cur_ISIR_rec.p_tax_return_status,
x_p_type_tax_return => cur_ISIR_rec.p_type_tax_return,
x_p_elig_1040aez => cur_ISIR_rec.p_elig_1040aez,
x_p_adjusted_gross_income => cur_ISIR_rec.p_adjusted_gross_income,
x_p_taxes_paid => cur_ISIR_rec.p_taxes_paid,
x_p_exemptions => cur_ISIR_rec.p_exemptions,
x_f_income_work => cur_ISIR_rec.f_income_work,
x_m_income_work => cur_ISIR_rec.m_income_work,
x_p_income_wsa => cur_ISIR_rec.p_income_wsa,
x_p_income_wsb => cur_ISIR_rec.p_income_wsb,
x_p_income_wsc => cur_ISIR_rec.p_income_wsc,
x_p_investment_networth => cur_ISIR_rec.p_investment_networth,
x_p_business_networth => cur_ISIR_rec.p_business_networth,
x_p_cash_saving => cur_ISIR_rec.p_cash_saving,
x_s_num_family_members => cur_ISIR_rec.s_num_family_members,
x_s_num_in_college => cur_ISIR_rec.s_num_in_college,
x_first_college => cur_ISIR_rec.first_college,
x_first_house_plan => cur_ISIR_rec.first_house_plan,
x_second_college => cur_ISIR_rec.second_college,
x_second_house_plan => cur_ISIR_rec.second_house_plan,
x_third_college => cur_ISIR_rec.third_college,
x_third_house_plan => cur_ISIR_rec.third_house_plan,
x_fourth_college => cur_ISIR_rec.fourth_college,
x_fourth_house_plan => cur_ISIR_rec.fourth_house_plan,
x_fifth_college => cur_ISIR_rec.fifth_college,
x_fifth_house_plan => cur_ISIR_rec.fifth_house_plan,
x_sixth_college => cur_ISIR_rec.sixth_college,
x_sixth_house_plan => cur_ISIR_rec.sixth_house_plan,
x_date_app_completed => cur_ISIR_rec.date_app_completed,
x_signed_by => cur_ISIR_rec.signed_by,
x_preparer_ssn => cur_ISIR_rec.preparer_ssn,
x_preparer_emp_id_number => cur_ISIR_rec.preparer_emp_id_number,
x_preparer_sign => cur_ISIR_rec.preparer_sign,
x_transaction_receipt_date => cur_ISIR_rec.transaction_receipt_date,
x_dependency_override_ind => cur_ISIR_rec.dependency_override_ind,
x_faa_fedral_schl_code => cur_ISIR_rec.faa_fedral_schl_code,
x_faa_adjustment => cur_ISIR_rec.faa_adjustment,
x_input_record_type => cur_ISIR_rec.input_record_type,
x_serial_number => cur_ISIR_rec.serial_number,
x_batch_number => cur_ISIR_rec.batch_number,
x_early_analysis_flag => cur_ISIR_rec.early_analysis_flag,
x_app_entry_source_code => cur_ISIR_rec.app_entry_source_code,
x_eti_destination_code => cur_ISIR_rec.eti_destination_code,
x_reject_override_b => cur_ISIR_rec.reject_override_b,
x_reject_override_n => cur_ISIR_rec.reject_override_n,
x_reject_override_w => cur_ISIR_rec.reject_override_w,
x_assum_override_1 => cur_ISIR_rec.assum_override_1,
x_assum_override_2 => cur_ISIR_rec.assum_override_2,
x_assum_override_3 => cur_ISIR_rec.assum_override_3,
x_assum_override_4 => cur_ISIR_rec.assum_override_4,
x_assum_override_5 => cur_ISIR_rec.assum_override_5,
x_assum_override_6 => cur_ISIR_rec.assum_override_6,
x_dependency_status => cur_ISIR_rec.dependency_status,
x_s_email_address => cur_ISIR_rec.s_email_address,
x_nslds_reason_code => cur_ISIR_rec.nslds_reason_code,
x_app_receipt_date => cur_ISIR_rec.app_receipt_date,
x_processed_rec_type => cur_ISIR_rec.processed_rec_type,
x_hist_correction_for_tran_id => cur_ISIR_rec.hist_correction_for_tran_id,
x_system_generated_indicator => cur_ISIR_rec.system_generated_indicator,
x_dup_request_indicator => cur_ISIR_rec.dup_request_indicator,
x_source_of_correction => cur_ISIR_rec.source_of_correction,
x_p_cal_tax_status => cur_ISIR_rec.p_cal_tax_status,
x_s_cal_tax_status => cur_ISIR_rec.s_cal_tax_status,
x_graduate_flag => cur_ISIR_rec.graduate_flag,
x_auto_zero_efc => cur_ISIR_rec.auto_zero_efc,
x_efc_change_flag => cur_ISIR_rec.efc_change_flag,
x_sarc_flag => cur_ISIR_rec.sarc_flag,
x_simplified_need_test => cur_ISIR_rec.simplified_need_test,
x_reject_reason_codes => cur_ISIR_rec.reject_reason_codes,
x_select_service_match_flag => cur_ISIR_rec.select_service_match_flag,
x_select_service_reg_flag => cur_ISIR_rec.select_service_reg_flag,
x_ins_match_flag => cur_ISIR_rec.ins_match_flag,
x_ins_verification_number => NULL,
x_sec_ins_match_flag => cur_ISIR_rec.sec_ins_match_flag,
x_sec_ins_ver_number => cur_ISIR_rec.sec_ins_ver_number,
x_ssn_match_flag => cur_ISIR_rec.ssn_match_flag,
x_ssa_citizenship_flag => cur_ISIR_rec.ssa_citizenship_flag,
x_ssn_date_of_death => cur_ISIR_rec.ssn_date_of_death,
x_nslds_match_flag => cur_ISIR_rec.nslds_match_flag,
x_va_match_flag => cur_ISIR_rec.va_match_flag,
x_prisoner_match => cur_ISIR_rec.prisoner_match,
x_verification_flag => cur_ISIR_rec.verification_flag,
x_subsequent_app_flag => cur_ISIR_rec.subsequent_app_flag,
x_app_source_site_code => cur_ISIR_rec.app_source_site_code,
x_tran_source_site_code => cur_ISIR_rec.tran_source_site_code,
x_drn => cur_ISIR_rec.drn,
x_tran_process_date => cur_ISIR_rec.tran_process_date,
x_computer_batch_number => cur_ISIR_rec.computer_batch_number,
x_correction_flags => cur_ISIR_rec.correction_flags,
x_highlight_flags => cur_ISIR_rec.highlight_flags,
x_paid_efc => NULL,
x_primary_efc => cur_ISIR_rec.primary_efc,
x_secondary_efc => cur_ISIR_rec.secondary_efc,
x_fed_pell_grant_efc_type => NULL,
x_primary_efc_type => cur_ISIR_rec.primary_efc_type,
x_sec_efc_type => cur_ISIR_rec.sec_efc_type,
x_primary_alternate_month_1 => cur_ISIR_rec.primary_alternate_month_1,
x_primary_alternate_month_2 => cur_ISIR_rec.primary_alternate_month_2,
x_primary_alternate_month_3 => cur_ISIR_rec.primary_alternate_month_3,
x_primary_alternate_month_4 => cur_ISIR_rec.primary_alternate_month_4,
x_primary_alternate_month_5 => cur_ISIR_rec.primary_alternate_month_5,
x_primary_alternate_month_6 => cur_ISIR_rec.primary_alternate_month_6,
x_primary_alternate_month_7 => cur_ISIR_rec.primary_alternate_month_7,
x_primary_alternate_month_8 => cur_ISIR_rec.primary_alternate_month_8,
x_primary_alternate_month_10 => cur_ISIR_rec.primary_alternate_month_10,
x_primary_alternate_month_11 => cur_ISIR_rec.primary_alternate_month_11,
x_primary_alternate_month_12 => cur_ISIR_rec.primary_alternate_month_12,
x_sec_alternate_month_1 => cur_ISIR_rec.sec_alternate_month_1,
x_sec_alternate_month_2 => cur_ISIR_rec.sec_alternate_month_2,
x_sec_alternate_month_3 => cur_ISIR_rec.sec_alternate_month_3,
x_sec_alternate_month_4 => cur_ISIR_rec.sec_alternate_month_4,
x_sec_alternate_month_5 => cur_ISIR_rec.sec_alternate_month_5,
x_sec_alternate_month_6 => cur_ISIR_rec.sec_alternate_month_6,
x_sec_alternate_month_7 => cur_ISIR_rec.sec_alternate_month_7,
x_sec_alternate_month_8 => cur_ISIR_rec.sec_alternate_month_8,
x_sec_alternate_month_10 => cur_ISIR_rec.sec_alternate_month_10,
x_sec_alternate_month_11 => cur_ISIR_rec.sec_alternate_month_11,
x_sec_alternate_month_12 => cur_ISIR_rec.sec_alternate_month_12,
x_total_income => cur_ISIR_rec.total_income,
x_allow_total_income => cur_ISIR_rec.allow_total_income,
x_state_tax_allow => cur_ISIR_rec.state_tax_allow,
x_employment_allow => cur_ISIR_rec.employment_allow,
x_income_protection_allow => cur_ISIR_rec.income_protection_allow,
x_available_income => cur_ISIR_rec.available_income,
x_contribution_from_ai => cur_ISIR_rec.contribution_from_ai,
x_discretionary_networth => cur_ISIR_rec.discretionary_networth,
x_efc_networth => cur_ISIR_rec.efc_networth,
x_asset_protect_allow => cur_ISIR_rec.asset_protect_allow,
x_parents_cont_from_assets => cur_ISIR_rec.parents_cont_from_assets,
x_adjusted_available_income => cur_ISIR_rec.adjusted_available_income,
x_total_student_contribution => cur_ISIR_rec.total_student_contribution,
x_total_parent_contribution => cur_ISIR_rec.total_parent_contribution,
x_parents_contribution => cur_ISIR_rec.parents_contribution,
x_student_total_income => cur_ISIR_rec.student_total_income,
x_sati => cur_ISIR_rec.sati,
x_sic => cur_ISIR_rec.sic,
x_sdnw => cur_ISIR_rec.sdnw,
x_sca => cur_ISIR_rec.sca,
x_fti => cur_ISIR_rec.fti,
x_secti => cur_ISIR_rec.secti,
x_secati => cur_ISIR_rec.secati,
x_secstx => cur_ISIR_rec.secstx,
x_secea => cur_ISIR_rec.secea,
x_secipa => cur_ISIR_rec.secipa,
x_secai => cur_ISIR_rec.secai,
x_seccai => cur_ISIR_rec.seccai,
x_secdnw => cur_ISIR_rec.secdnw,
x_secnw => cur_ISIR_rec.secnw,
x_secapa => cur_ISIR_rec.secapa,
x_secpca => cur_ISIR_rec.secpca,
x_secaai => cur_ISIR_rec.secaai,
x_sectsc => cur_ISIR_rec.sectsc,
x_sectpc => cur_ISIR_rec.sectpc,
x_secpc => cur_ISIR_rec.secpc,
x_secsti => cur_ISIR_rec.secsti,
x_secsic => cur_ISIR_rec.secsic,
x_secsati => cur_ISIR_rec.secsati,
x_secsdnw => cur_ISIR_rec.secsdnw,
x_secsca => cur_ISIR_rec.secsca,
x_secfti => cur_ISIR_rec.secfti,
x_a_citizenship => cur_ISIR_rec.a_citizenship,
x_a_student_marital_status => cur_ISIR_rec.a_student_marital_status,
x_a_student_agi => cur_ISIR_rec.a_student_agi,
x_a_s_us_tax_paid => cur_ISIR_rec.a_s_us_tax_paid,
x_a_s_income_work => cur_ISIR_rec.a_s_income_work,
x_a_spouse_income_work => cur_ISIR_rec.a_spouse_income_work,
x_a_s_total_wsc => cur_ISIR_rec.a_s_total_wsc,
x_a_date_of_birth => cur_ISIR_rec.a_date_of_birth,
x_a_student_married => cur_ISIR_rec.a_student_married,
x_a_have_children => cur_ISIR_rec.a_have_children,
x_a_s_have_dependents => cur_ISIR_rec.a_s_have_dependents,
x_a_va_status => cur_ISIR_rec.a_va_status,
x_a_s_num_in_family => cur_ISIR_rec.a_s_num_in_family,
x_a_s_num_in_college => cur_ISIR_rec.a_s_num_in_college,
x_a_p_marital_status => cur_ISIR_rec.a_p_marital_status,
x_a_father_ssn => cur_ISIR_rec.a_father_ssn,
x_a_mother_ssn => cur_ISIR_rec.a_mother_ssn,
x_a_parents_num_family => cur_ISIR_rec.a_parents_num_family,
x_a_parents_num_college => cur_ISIR_rec.a_parents_num_college,
x_a_parents_agi => cur_ISIR_rec.a_parents_agi,
x_a_p_us_tax_paid => cur_ISIR_rec.a_p_us_tax_paid,
x_a_f_work_income => cur_ISIR_rec.a_f_work_income,
x_a_m_work_income => cur_ISIR_rec.a_m_work_income,
x_a_p_total_wsc => cur_ISIR_rec.a_p_total_wsc,
x_comment_codes => cur_ISIR_rec.comment_codes,
x_sar_ack_comm_code => cur_ISIR_rec.sar_ack_comm_code,
x_pell_grant_elig_flag => cur_ISIR_rec.pell_grant_elig_flag,
x_reprocess_reason_code => cur_ISIR_rec.reprocess_reason_code,
x_duplicate_date => cur_ISIR_rec.duplicate_date,
x_ISIR_transaction_type => cur_ISIR_rec.ISIR_transaction_type,
x_fedral_schl_code_indicator => cur_ISIR_rec.fedral_schl_code_indicator,
x_multi_school_code_flags => cur_ISIR_rec.multi_school_code_flags,
x_dup_ssn_indicator => cur_ISIR_rec.dup_ssn_indicator,
x_payment_ISIR => p_payment_ISIR,
x_receipt_status => 'PROCESSED',
x_system_record_type => p_system_record_type,
x_ISIR_receipt_completed => 'Y' ,
x_verif_track_flag => cur_ISIR_rec.verif_track_flag,
x_active_ISIR => NVL(p_active_ISIR,cur_ISIR_rec.active_ISIR),
x_fafsa_data_verify_flags => cur_ISIR_rec.fafsa_data_verify_flags,
x_reject_override_a => cur_ISIR_rec.reject_override_a,
x_reject_override_c => cur_ISIR_rec.reject_override_c,
x_parent_marital_status_date => cur_ISIR_rec.parent_marital_status_date,
x_legacy_record_flag => NULL,
x_father_first_name_initial => cur_ISIR_rec.father_first_name_initial_txt,
x_father_step_father_birth_dt => cur_ISIR_rec.father_step_father_birth_date,
x_mother_first_name_initial => cur_ISIR_rec.mother_first_name_initial_txt,
x_mother_step_mother_birth_dt => cur_ISIR_rec.mother_step_mother_birth_date,
x_parents_email_address_txt => cur_ISIR_rec.parents_email_address_txt,
x_address_change_type => cur_ISIR_rec.address_change_type,
x_cps_pushed_isir_flag => cur_ISIR_rec.cps_pushed_isir_flag,
x_electronic_transaction_type => cur_ISIR_rec.electronic_transaction_type,
x_sar_c_change_type => cur_ISIR_rec.sar_c_change_type,
x_father_ssn_match_type => cur_ISIR_rec.father_ssn_match_type,
x_mother_ssn_match_type => cur_ISIR_rec.mother_ssn_match_type,
x_reject_override_g_flag => cur_ISIR_rec.reject_override_g_flag,
x_dhs_verification_num_txt => cur_ISIR_rec.dhs_verification_num_txt,
x_data_file_name_txt => cur_ISIR_rec.data_file_name_txt,
x_message_class_txt => l_msg_class,
x_reject_override_3_flag => cur_ISIR_rec.reject_override_3_flag,
x_reject_override_12_flag => cur_ISIR_rec.reject_override_12_flag,
x_reject_override_j_flag => cur_ISIR_rec.reject_override_j_flag,
x_reject_override_k_flag => cur_ISIR_rec.reject_override_k_flag,
x_rejected_status_change_flag => cur_ISIR_rec.rejected_status_change_flag,
x_verification_selection_flag => cur_ISIR_rec.verification_selection_flag
);
igf_gr_gen.update_current_ssn(cur_ISIR_rec.base_id,cur_ISIR_rec.current_ssn,p_c_message);
fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.UPDATE_MATCHED_ISIR');
END update_matched_ISIR;
PROCEDURE update_fabase (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
p_ISIR_corr_status igf_ap_fa_base_rec_all.ISIR_corr_status%TYPE,
p_ISIR_corr_status_date igf_ap_fa_base_rec_all.ISIR_corr_status_date%TYPE)
IS
/*
|| Created By : brajendr
|| Created On : 08-NOV-2000
|| Purpose : Process which inserts comment codes of the student.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
|| removed packaging hold
|| masehgal 25-Sep-2002 FA 104 - To Do Enhancements
|| Added manual_disb_hold in update of Fa Base Rec
|| (reverse chronological order - newest change first)
*/
CURSOR cur_upd_base (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
SELECT fab.*
FROM igf_ap_fa_base_rec fab
WHERE fab.base_id = p_base_id;
ELSIF cur_fbr_rec.fed_verif_status IN ('CORRSENT','NOTVERIFIED', 'NOTSELECTED')
OR cur_fbr_rec.fed_verif_status IS NULL THEN
IF LTRIM(RTRIM(g_verification_flag)) = 'Y' THEN
cur_fbr_rec.fed_verif_status := 'SELECTED';
cur_fbr_rec.fed_verif_status := 'NOTSELECTED';
igf_ap_fa_base_rec_pkg.update_row (x_Mode => 'R',
x_rowid => cur_fbr_rec.row_id,
x_base_id => cur_fbr_rec.base_id,
x_ci_cal_type => cur_fbr_rec.ci_cal_type,
x_person_id => cur_fbr_rec.person_id,
x_ci_sequence_number => cur_fbr_rec.ci_sequence_number,
x_org_id => cur_fbr_rec.org_id,
x_coa_pending => cur_fbr_rec.coa_pending,
x_verification_process_run => cur_fbr_rec.verification_process_run,
x_inst_verif_status_date => cur_fbr_rec.inst_verif_status_date,
x_manual_verif_flag => cur_fbr_rec.manual_verif_flag,
x_fed_verif_status => cur_fbr_rec.fed_verif_status,
x_fed_verif_status_date => l_fed_verif_date,
x_inst_verif_status => cur_fbr_rec.inst_verif_status,
x_nslds_eligible => g_nslds_match_flag,
x_ede_correction_batch_id => cur_fbr_rec.ede_correction_batch_id,
x_fa_process_status_date => TRUNC(SYSDATE),
x_ISIR_corr_status => p_ISIR_corr_status,
x_ISIR_corr_status_date => p_ISIR_corr_status_date,
x_ISIR_status => 'Received-Valid',
x_ISIR_status_date => TRUNC(SYSDATE),
x_coa_code_f => cur_fbr_rec.coa_code_f,
x_coa_code_i => cur_fbr_rec.coa_code_i,
x_coa_f => cur_fbr_rec.coa_f,
x_coa_i => cur_fbr_rec.coa_i,
x_disbursement_hold => cur_fbr_rec.disbursement_hold,
x_fa_process_status => cur_fbr_rec.fa_process_status,
x_notification_status => cur_fbr_rec.notification_status,
x_notification_status_date => cur_fbr_rec.notification_status_date,
x_packaging_status => cur_fbr_rec.packaging_status,
x_packaging_status_date => cur_fbr_rec.packaging_status_date,
x_total_package_accepted => cur_fbr_rec.total_package_accepted,
x_total_package_offered => cur_fbr_rec.total_package_offered,
x_admstruct_id => cur_fbr_rec.admstruct_id,
x_admsegment_1 => cur_fbr_rec.admsegment_1,
x_admsegment_2 => cur_fbr_rec.admsegment_2,
x_admsegment_3 => cur_fbr_rec.admsegment_3,
x_admsegment_4 => cur_fbr_rec.admsegment_4,
x_admsegment_5 => cur_fbr_rec.admsegment_5,
x_admsegment_6 => cur_fbr_rec.admsegment_6,
x_admsegment_7 => cur_fbr_rec.admsegment_7,
x_admsegment_8 => cur_fbr_rec.admsegment_8,
x_admsegment_9 => cur_fbr_rec.admsegment_9,
x_admsegment_10 => cur_fbr_rec.admsegment_10,
x_admsegment_11 => cur_fbr_rec.admsegment_11,
x_admsegment_12 => cur_fbr_rec.admsegment_12,
x_admsegment_13 => cur_fbr_rec.admsegment_13,
x_admsegment_14 => cur_fbr_rec.admsegment_14,
x_admsegment_15 => cur_fbr_rec.admsegment_15,
x_admsegment_16 => cur_fbr_rec.admsegment_16,
x_admsegment_17 => cur_fbr_rec.admsegment_17,
x_admsegment_18 => cur_fbr_rec.admsegment_18,
x_admsegment_19 => cur_fbr_rec.admsegment_19,
x_admsegment_20 => cur_fbr_rec.admsegment_20,
x_packstruct_id => cur_fbr_rec.packstruct_id,
x_packsegment_1 => cur_fbr_rec.packsegment_1,
x_packsegment_2 => cur_fbr_rec.packsegment_2,
x_packsegment_3 => cur_fbr_rec.packsegment_3,
x_packsegment_4 => cur_fbr_rec.packsegment_4,
x_packsegment_5 => cur_fbr_rec.packsegment_5,
x_packsegment_6 => cur_fbr_rec.packsegment_6,
x_packsegment_7 => cur_fbr_rec.packsegment_7,
x_packsegment_8 => cur_fbr_rec.packsegment_8,
x_packsegment_9 => cur_fbr_rec.packsegment_9,
x_packsegment_10 => cur_fbr_rec.packsegment_10,
x_packsegment_11 => cur_fbr_rec.packsegment_11,
x_packsegment_12 => cur_fbr_rec.packsegment_12,
x_packsegment_13 => cur_fbr_rec.packsegment_13,
x_packsegment_14 => cur_fbr_rec.packsegment_14,
x_packsegment_15 => cur_fbr_rec.packsegment_15,
x_packsegment_16 => cur_fbr_rec.packsegment_16,
x_packsegment_17 => cur_fbr_rec.packsegment_17,
x_packsegment_18 => cur_fbr_rec.packsegment_18,
x_packsegment_19 => cur_fbr_rec.packsegment_19,
x_packsegment_20 => cur_fbr_rec.packsegment_20,
x_miscstruct_id => cur_fbr_rec.miscstruct_id,
x_miscsegment_1 => cur_fbr_rec.miscsegment_1,
x_miscsegment_2 => cur_fbr_rec.miscsegment_2,
x_miscsegment_3 => cur_fbr_rec.miscsegment_3,
x_miscsegment_4 => cur_fbr_rec.miscsegment_4,
x_miscsegment_5 => cur_fbr_rec.miscsegment_5,
x_miscsegment_6 => cur_fbr_rec.miscsegment_6,
x_miscsegment_7 => cur_fbr_rec.miscsegment_7,
x_miscsegment_8 => cur_fbr_rec.miscsegment_8,
x_miscsegment_9 => cur_fbr_rec.miscsegment_9,
x_miscsegment_10 => cur_fbr_rec.miscsegment_10,
x_miscsegment_11 => cur_fbr_rec.miscsegment_11,
x_miscsegment_12 => cur_fbr_rec.miscsegment_12,
x_miscsegment_13 => cur_fbr_rec.miscsegment_13,
x_miscsegment_14 => cur_fbr_rec.miscsegment_14,
x_miscsegment_15 => cur_fbr_rec.miscsegment_15,
x_miscsegment_16 => cur_fbr_rec.miscsegment_16,
x_miscsegment_17 => cur_fbr_rec.miscsegment_17,
x_miscsegment_18 => cur_fbr_rec.miscsegment_18,
x_miscsegment_19 => cur_fbr_rec.miscsegment_19,
x_miscsegment_20 => cur_fbr_rec.miscsegment_20,
x_prof_judgement_flg => cur_fbr_rec.prof_judgement_flg,
x_nslds_data_override_flg => cur_fbr_rec.nslds_data_override_flg,
x_target_group => cur_fbr_rec.target_group,
x_coa_fixed => cur_fbr_rec.coa_fixed,
x_profile_status => cur_fbr_rec.profile_status,
x_profile_status_date => cur_fbr_rec.profile_status_date,
x_profile_fc => cur_fbr_rec.profile_fc,
x_coa_pell => cur_fbr_rec.coa_pell,
x_manual_disb_hold => cur_fbr_rec.manual_disb_hold,
x_pell_alt_expense => cur_fbr_rec.pell_alt_expense,
x_assoc_org_num => cur_fbr_rec.assoc_org_num,
x_award_fmly_contribution_type => cur_fbr_rec.award_fmly_contribution_type,
x_packaging_hold => cur_fbr_rec.packaging_hold,
x_isir_locked_by => cur_fbr_rec.isir_locked_by,
x_adnl_unsub_loan_elig_flag => cur_fbr_rec.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => cur_fbr_rec.lock_awd_flag,
x_lock_coa_flag => cur_fbr_rec.lock_coa_flag
);
fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.UPDATE_FABASE');
END update_fabase;
PROCEDURE update_ISIR_corr (p_ISIRc_id igf_ap_ISIR_corr_all.ISIRc_id%TYPE,
p_correction_status igf_ap_ISIR_corr_all.correction_status%TYPE )
IS
/*
|| Created By : skoppula
|| Created On : 03-JUL-2001
|| Purpose : Procedure updates the correction status of the ISIR corrections
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
l_correction_status igf_ap_ISIR_corr.correction_status%TYPE;
SELECT *
FROM igf_ap_ISIR_corr
WHERE ISIRc_id = p_ISIRc_id;
igf_ap_ISIR_corr_pkg.update_row (
x_rowid => cur_ISIR_corr.row_id,
x_ISIRc_id => cur_ISIR_corr.ISIRc_id,
x_ISIR_id => cur_ISIR_corr.ISIR_id,
x_ci_sequence_number => cur_ISIR_corr.ci_sequence_number,
x_ci_cal_type => cur_ISIR_corr.ci_cal_type,
x_sar_field_number => cur_ISIR_corr.sar_field_number,
x_original_value => cur_ISIR_corr.original_value,
x_batch_id => cur_ISIR_corr.batch_id,
x_corrected_value => cur_ISIR_corr.corrected_value,
x_correction_status => NVL(p_correction_status,cur_ISIR_corr.correction_status),
x_mode => 'R');
fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.UPDATE_ISIR_CORR');
END update_ISIR_corr;
SELECT ISIR_id
FROM igf_ap_ISIR_matched
WHERE base_id = p_base_id;
SELECT corr.ISIR_id,
corr.ISIRc_id,
corr.sar_field_number,
sar.sar_field_name column_name,
corr.corrected_value,
corr.correction_status ,
lkup.meaning meaning
FROM igf_ap_batch_aw_map map,
igf_ap_fa_base_rec_all fabase,
igf_ap_ISIR_corr corr,
Igf_fc_sar_cd_mst sar ,
igf_lookups_view lkup
WHERE fabase.base_id = cp_base_id
AND map.ci_cal_type = fabase.ci_cal_type
AND map.ci_sequence_number = fabase.ci_sequence_number
AND sar.sys_award_year = map.sys_award_year
AND corr.isir_id = l_isir_id
AND corr.correction_status <> cp_corr_stat
AND sar.sar_field_number = corr.sar_field_number
AND lkup.lookup_type = cp_lkup_type
AND lkup.lookup_code = sar.sar_field_name ;
lv_stmt := 'SELECT '||lv_column_name ||' FROM igf_ap_ISIR_matched where ISIR_id = :l_isir_id ' ;
update_ISIR_corr (rec_ISIR_corr.ISIRc_id, 'ACKNOWLEDGED');
update_ISIR_corr (rec_ISIR_corr.ISIRc_id, 'READY');
update_fabase(g_base_rec.base_id,g_base_rec.ISIR_corr_status,g_base_rec.ISIR_corr_status_date);
update_matched_ISIR(g_ISIR_rec.ISIR_id,'ORIGINAL','Y','Y');
SELECT given_names,
person_number
FROM igf_ap_fa_con_v
WHERE base_id = g_base_id;
igf_ap_outcorr_wf_pkg.insert_row (
x_rowid => l_rowid,
x_person_number => l_person_number,
x_given_names => l_given_names,
x_transaction_number => g_transaction_num,
x_item_key => 'NEW',
x_ow_id => l_ow_id,
x_mode => 'R');
SELECT given_names,
person_number
FROM igf_ap_fa_con_v
WHERE base_id = g_base_id;
SELECT wf.*,
wf.rowid row_id
FROM igf_ap_outcorr_wf wf
WHERE item_key = 'NEW';
SELECT *
FROM igf_ap_outcorr_wf
WHERE item_key = l_item_key;
IGF_AP_OUTCORR_WF_PKG.update_row(
x_rowid => l_upd_key.row_id,
x_person_number => l_upd_key.person_number,
x_given_names => l_upd_key.given_names,
x_transaction_number => l_upd_key.transaction_number,
x_item_key => l_item_key,
x_ow_id => l_upd_key.ow_id,
x_mode => 'R'
);
SELECT
igf_ap_corr_wf_s.NEXTVAL
FROM DUAL;