[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM igf_ap_record_match
WHERE match_code = cp_match_code
AND enabled_flag = 'Y';
|| Purpose : For Inserting record into ISIR matched table based on ISIR int record.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
lv_payment_isir VARCHAR2(1);
SELECT im.*
FROM igf_ap_td_item_mst im
WHERE im.system_todo_type_code = 'ISIR' -- for ISIR type only
AND im.ci_cal_type = g_ci_cal_type
AND im.ci_sequence_number = g_ci_sequence_number;
SELECT ii.rowid, ii.*
FROM igf_ap_td_item_inst ii, igf_ap_td_item_mst im
WHERE ii.item_sequence_number = im.todo_number
AND im.system_todo_type_code = 'ISIR'
AND im.ci_cal_type = g_ci_cal_type
AND im.ci_sequence_number = g_ci_sequence_number
AND ii.item_sequence_number = p_item_seq_num
AND ii.base_id = lp_base_id;
igf_ap_td_item_inst_pkg.insert_row (
x_rowid => lv_rowid ,
x_base_id => p_base_id ,
x_item_sequence_number => todo_items_for_isir_rec.todo_number ,
x_status => l_todo_status ,
x_status_date => TRUNC(SYSDATE) ,
x_add_date => TRUNC(SYSDATE) ,
x_corsp_date => NULL ,
x_corsp_count => NULL ,
x_inactive_flag => 'N' ,
x_freq_attempt => todo_items_for_isir_rec.freq_attempt ,
x_max_attempt => todo_items_for_isir_rec.max_attempt ,
x_required_for_application => todo_items_for_isir_rec.required_for_application,
x_mode => 'R' ,
x_legacy_record_flag => NULL,
x_clprl_id => NULL
);
log_debug_message(' Update TODO Items to Status : ' || l_todo_status);
igf_ap_td_item_inst_pkg.update_row (
x_rowid => check_todo_exists_inst_rec.rowid ,
x_base_id => p_base_id ,
x_item_sequence_number => check_todo_exists_inst_rec.item_sequence_number,
x_status => l_todo_status ,
x_status_date => check_todo_exists_inst_rec.status_date ,
x_add_date => check_todo_exists_inst_rec.add_date ,
x_corsp_date => check_todo_exists_inst_rec.corsp_date ,
x_corsp_count => check_todo_exists_inst_rec.corsp_count ,
x_inactive_flag => check_todo_exists_inst_rec.inactive_flag ,
x_freq_attempt => check_todo_exists_inst_rec.freq_attempt ,
x_max_attempt => check_todo_exists_inst_rec.max_attempt ,
x_required_for_application => check_todo_exists_inst_rec.required_for_application,
x_mode => 'R' ,
x_legacy_record_flag => check_todo_exists_inst_rec.legacy_record_flag,
x_clprl_id => check_todo_exists_inst_rec.clprl_id
);
PROCEDURE update_isir_int_record (p_si_id igf_ap_isir_ints.si_id%TYPE,
p_isir_rec_status igf_ap_isir_ints_all.record_status%TYPE,
p_match_code igf_ap_isir_ints.match_code%TYPE)
IS
/*
|| Created By : rgangara
|| Created On : 03-AUG-2004
|| Purpose : For Updating ISIR Interface record status.
|| However, records which are to be updated to 'MATCHED' status could be deleted if
|| the p_del_int_rec User parameter value is 'Y'. Hence this procedure is modified
|| to delete the ISIR from Int table if the rec status is MATCHED and the paremeter is 'Y'.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
BEGIN
IF g_del_success_int_rec = 'Y' AND p_isir_rec_status = 'MATCHED' THEN
DELETE FROM igf_ap_isir_ints_all
WHERE si_id = p_si_id;
log_debug_message('Deleted ISIR Interface record. SI ID : ' || p_si_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.update_isir_int_record.statement','Deleted ISIR Interface record for SI_ID: ' || p_si_id);
UPDATE igf_ap_isir_ints_all
SET record_status = p_isir_rec_status,
match_code = p_match_code,
last_update_date = SYSDATE
WHERE si_id = p_si_id;
log_debug_message('Updated ISIR Interface record. SI ID : ' || p_si_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.update_isir_int_record.statement','Updated ISIR Interface Record Status to ' || p_isir_rec_status || ' for SI_ID: ' || p_si_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.update_isir_int_record.exception','The exception is : ' || SQLERRM );
END update_isir_int_record;
PROCEDURE update_fa_base_rec(p_fabase_rec igf_ap_fa_base_rec%ROWTYPE,
p_isir_verification_flag VARCHAR2)
IS
/*
|| Created By : rgangara
|| Created On : 05-AUG-2004
|| Purpose : For Updating FA BASE record as per the passed in record.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
l_fed_verif_status igf_ap_fa_base_rec.fed_verif_status%TYPE;
log_debug_message(' Beginning Update of FA Base record. BASE ID : ' || p_fabase_rec.base_id);
p_fabase_rec.fed_verif_status IN ('CORRSENT','NOTVERIFIED', 'NOTSELECTED')) THEN
IF p_isir_verification_flag = 'Y' THEN
l_fed_verif_status := 'SELECTED';
l_fed_verif_status := 'NOTSELECTED';
igf_ap_fa_base_rec_pkg.update_row(
x_Mode => 'R' ,
x_rowid => p_fabase_rec.row_id ,
x_base_id => p_fabase_rec.base_id ,
x_ci_cal_type => p_fabase_rec.ci_cal_type ,
x_person_id => p_fabase_rec.person_id ,
x_ci_sequence_number => p_fabase_rec.ci_sequence_number ,
x_org_id => p_fabase_rec.org_id ,
x_coa_pending => p_fabase_rec.coa_pending ,
x_verification_process_run => p_fabase_rec.verification_process_run ,
x_inst_verif_status_date => p_fabase_rec.inst_verif_status_date ,
x_manual_verif_flag => p_fabase_rec.manual_verif_flag ,
x_fed_verif_status => l_fed_verif_status ,
x_fed_verif_status_date => TRUNC(SYSDATE),
x_inst_verif_status => p_fabase_rec.inst_verif_status ,
x_nslds_eligible => NVL(p_fabase_rec.nslds_eligible, g_isir_intrface_rec.nslds_match_type) ,
x_ede_correction_batch_id => p_fabase_rec.ede_correction_batch_id ,
x_fa_process_status_date => p_fabase_rec.fa_process_status_date ,
x_isir_corr_status => p_fabase_rec.isir_corr_status ,
x_isir_corr_status_date => p_fabase_rec.isir_corr_status_date ,
x_isir_status => 'Received-Valid',
x_isir_status_date => TRUNC(SYSDATE) ,
x_coa_code_f => p_fabase_rec.coa_code_f ,
x_coa_code_i => p_fabase_rec.coa_code_i ,
x_coa_f => p_fabase_rec.coa_f ,
x_coa_i => p_fabase_rec.coa_i ,
x_disbursement_hold => p_fabase_rec.disbursement_hold ,
x_fa_process_status => p_fabase_rec.fa_process_status ,
x_notification_status => p_fabase_rec.notification_status ,
x_notification_status_date => p_fabase_rec.notification_status_date ,
x_packaging_status => p_fabase_rec.packaging_status ,
x_packaging_status_date => p_fabase_rec.packaging_status_date ,
x_total_package_accepted => p_fabase_rec.total_package_accepted ,
x_total_package_offered => p_fabase_rec.total_package_offered ,
x_admstruct_id => p_fabase_rec.admstruct_id ,
x_admsegment_1 => p_fabase_rec.admsegment_1 ,
x_admsegment_2 => p_fabase_rec.admsegment_2 ,
x_admsegment_3 => p_fabase_rec.admsegment_3 ,
x_admsegment_4 => p_fabase_rec.admsegment_4 ,
x_admsegment_5 => p_fabase_rec.admsegment_5 ,
x_admsegment_6 => p_fabase_rec.admsegment_6 ,
x_admsegment_7 => p_fabase_rec.admsegment_7 ,
x_admsegment_8 => p_fabase_rec.admsegment_8 ,
x_admsegment_9 => p_fabase_rec.admsegment_9 ,
x_admsegment_10 => p_fabase_rec.admsegment_10 ,
x_admsegment_11 => p_fabase_rec.admsegment_11 ,
x_admsegment_12 => p_fabase_rec.admsegment_12 ,
x_admsegment_13 => p_fabase_rec.admsegment_13 ,
x_admsegment_14 => p_fabase_rec.admsegment_14 ,
x_admsegment_15 => p_fabase_rec.admsegment_15 ,
x_admsegment_16 => p_fabase_rec.admsegment_16 ,
x_admsegment_17 => p_fabase_rec.admsegment_17 ,
x_admsegment_18 => p_fabase_rec.admsegment_18 ,
x_admsegment_19 => p_fabase_rec.admsegment_19 ,
x_admsegment_20 => p_fabase_rec.admsegment_20 ,
x_packstruct_id => p_fabase_rec.packstruct_id ,
x_packsegment_1 => p_fabase_rec.packsegment_1 ,
x_packsegment_2 => p_fabase_rec.packsegment_2 ,
x_packsegment_3 => p_fabase_rec.packsegment_3 ,
x_packsegment_4 => p_fabase_rec.packsegment_4 ,
x_packsegment_5 => p_fabase_rec.packsegment_5 ,
x_packsegment_6 => p_fabase_rec.packsegment_6 ,
x_packsegment_7 => p_fabase_rec.packsegment_7 ,
x_packsegment_8 => p_fabase_rec.packsegment_8 ,
x_packsegment_9 => p_fabase_rec.packsegment_9 ,
x_packsegment_10 => p_fabase_rec.packsegment_10 ,
x_packsegment_11 => p_fabase_rec.packsegment_11 ,
x_packsegment_12 => p_fabase_rec.packsegment_12 ,
x_packsegment_13 => p_fabase_rec.packsegment_13 ,
x_packsegment_14 => p_fabase_rec.packsegment_14 ,
x_packsegment_15 => p_fabase_rec.packsegment_15 ,
x_packsegment_16 => p_fabase_rec.packsegment_16 ,
x_packsegment_17 => p_fabase_rec.packsegment_17 ,
x_packsegment_18 => p_fabase_rec.packsegment_18 ,
x_packsegment_19 => p_fabase_rec.packsegment_19 ,
x_packsegment_20 => p_fabase_rec.packsegment_20 ,
x_miscstruct_id => p_fabase_rec.miscstruct_id ,
x_miscsegment_1 => p_fabase_rec.miscsegment_1 ,
x_miscsegment_2 => p_fabase_rec.miscsegment_2 ,
x_miscsegment_3 => p_fabase_rec.miscsegment_3 ,
x_miscsegment_4 => p_fabase_rec.miscsegment_4 ,
x_miscsegment_5 => p_fabase_rec.miscsegment_5 ,
x_miscsegment_6 => p_fabase_rec.miscsegment_6 ,
x_miscsegment_7 => p_fabase_rec.miscsegment_7 ,
x_miscsegment_8 => p_fabase_rec.miscsegment_8 ,
x_miscsegment_9 => p_fabase_rec.miscsegment_9 ,
x_miscsegment_10 => p_fabase_rec.miscsegment_10 ,
x_miscsegment_11 => p_fabase_rec.miscsegment_11 ,
x_miscsegment_12 => p_fabase_rec.miscsegment_12 ,
x_miscsegment_13 => p_fabase_rec.miscsegment_13 ,
x_miscsegment_14 => p_fabase_rec.miscsegment_14 ,
x_miscsegment_15 => p_fabase_rec.miscsegment_15 ,
x_miscsegment_16 => p_fabase_rec.miscsegment_16 ,
x_miscsegment_17 => p_fabase_rec.miscsegment_17 ,
x_miscsegment_18 => p_fabase_rec.miscsegment_18 ,
x_miscsegment_19 => p_fabase_rec.miscsegment_19 ,
x_miscsegment_20 => p_fabase_rec.miscsegment_20 ,
x_prof_judgement_flg => p_fabase_rec.prof_judgement_flg ,
x_nslds_data_override_flg => p_fabase_rec.nslds_data_override_flg ,
x_target_group => p_fabase_rec.target_group ,
x_coa_fixed => p_fabase_rec.coa_fixed ,
x_coa_pell => p_fabase_rec.coa_pell ,
x_profile_status => p_fabase_rec.profile_status ,
x_profile_status_date => p_fabase_rec.profile_status_date ,
x_profile_fc => p_fabase_rec.profile_fc ,
x_manual_disb_hold => p_fabase_rec.manual_disb_hold ,
x_pell_alt_expense => p_fabase_rec.pell_alt_expense,
x_assoc_org_num => p_fabase_rec.assoc_org_num,
x_award_fmly_contribution_type => p_fabase_rec.award_fmly_contribution_type,
x_packaging_hold => p_fabase_rec.packaging_hold,
x_isir_locked_by => p_fabase_rec.isir_locked_by,
x_adnl_unsub_loan_elig_flag => p_fabase_rec.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => p_fabase_rec.lock_awd_flag,
x_lock_coa_flag => p_fabase_rec.lock_coa_flag
);
log_debug_message(' Successfully Updated FA Base record. BASE ID : ' || p_fabase_rec.base_id || '. Person ID" ' || p_fabase_rec.person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.update_fa_base_rec.debug','Updated FA Base Record for BASE ID: ' || p_fabase_rec.base_id || ', Person ID" ' || p_fabase_rec.person_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.update_fa_base_rec.exception','The exception is : ' || SQLERRM );
END update_fa_base_rec;
PROCEDURE update_isir_matched_rec(p_isir_matched_record igf_ap_isir_matched%ROWTYPE,
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 : rgangara
|| Created On : 05-AUG-2004
|| Purpose : For Updating record in ISIR Matched table and updating based on payment and active isir types.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
BEGIN
log_debug_message(' Beginning Update of ISIR Matched record. ISIR ID : ' || p_isir_matched_record.isir_id);
igf_ap_isir_matched_pkg.update_row(
x_Mode => 'R',
x_rowid => p_isir_matched_record.row_id,
x_isir_id => p_isir_matched_record.isir_id,
x_base_id => p_isir_matched_record.base_id,
x_batch_year => p_isir_matched_record.batch_year,
x_transaction_num => p_isir_matched_record.transaction_num,
x_current_ssn => p_isir_matched_record.current_ssn,
x_ssn_name_change => p_isir_matched_record.ssn_name_change,
x_original_ssn => p_isir_matched_record.original_ssn,
x_orig_name_id => p_isir_matched_record.orig_name_id,
x_last_name => p_isir_matched_record.last_name,
x_first_name => p_isir_matched_record.first_name,
x_middle_initial => p_isir_matched_record.middle_initial,
x_perm_mail_add => p_isir_matched_record.perm_mail_add,
x_perm_city => p_isir_matched_record.perm_city,
x_perm_state => p_isir_matched_record.perm_state,
x_perm_zip_code => p_isir_matched_record.perm_zip_code,
x_date_of_birth => p_isir_matched_record.date_of_birth,
x_phone_number => p_isir_matched_record.phone_number,
x_driver_license_number => p_isir_matched_record.driver_license_number,
x_driver_license_state => p_isir_matched_record.driver_license_state,
x_citizenship_status => p_isir_matched_record.citizenship_status,
x_alien_reg_number => p_isir_matched_record.alien_reg_number,
x_s_marital_status => p_isir_matched_record.s_marital_status,
x_s_marital_status_date => p_isir_matched_record.s_marital_status_date,
x_summ_enrl_status => p_isir_matched_record.summ_enrl_status,
x_fall_enrl_status => p_isir_matched_record.fall_enrl_status,
x_winter_enrl_status => p_isir_matched_record.winter_enrl_status,
x_spring_enrl_status => p_isir_matched_record.spring_enrl_status,
x_summ2_enrl_status => p_isir_matched_record.summ2_enrl_status,
x_fathers_highest_edu_level => p_isir_matched_record.fathers_highest_edu_level,
x_mothers_highest_edu_level => p_isir_matched_record.mothers_highest_edu_level,
x_s_state_legal_residence => p_isir_matched_record.s_state_legal_residence,
x_legal_residence_before_date => p_isir_matched_record.legal_residence_before_date,
x_s_legal_resd_date => p_isir_matched_record.s_legal_resd_date,
x_ss_r_u_male => p_isir_matched_record.ss_r_u_male,
x_selective_service_reg => p_isir_matched_record.selective_service_reg,
x_degree_certification => p_isir_matched_record.degree_certification,
x_grade_level_in_college => p_isir_matched_record.grade_level_in_college,
x_high_school_diploma_ged => p_isir_matched_record.high_school_diploma_ged,
x_first_bachelor_deg_by_date => p_isir_matched_record.first_bachelor_deg_by_date,
x_interest_in_loan => p_isir_matched_record.interest_in_loan,
x_interest_in_stud_employment => p_isir_matched_record.interest_in_stud_employment,
x_drug_offence_conviction => p_isir_matched_record.drug_offence_conviction,
x_s_tax_return_status => p_isir_matched_record.s_tax_return_status,
x_s_type_tax_return => p_isir_matched_record.s_type_tax_return,
x_s_elig_1040ez => p_isir_matched_record.s_elig_1040ez,
x_s_adjusted_gross_income => p_isir_matched_record.s_adjusted_gross_income,
x_s_fed_taxes_paid => p_isir_matched_record.s_fed_taxes_paid,
x_s_exemptions => p_isir_matched_record.s_exemptions,
x_s_income_from_work => p_isir_matched_record.s_income_from_work,
x_spouse_income_from_work => p_isir_matched_record.spouse_income_from_work,
x_s_toa_amt_from_wsa => p_isir_matched_record.s_toa_amt_from_wsa,
x_s_toa_amt_from_wsb => p_isir_matched_record.s_toa_amt_from_wsb,
x_s_toa_amt_from_wsc => p_isir_matched_record.s_toa_amt_from_wsc,
x_s_investment_networth => p_isir_matched_record.s_investment_networth,
x_s_busi_farm_networth => p_isir_matched_record.s_busi_farm_networth,
x_s_cash_savings => p_isir_matched_record.s_cash_savings,
x_va_months => p_isir_matched_record.va_months,
x_va_amount => p_isir_matched_record.va_amount,
x_stud_dob_before_date => p_isir_matched_record.stud_dob_before_date,
x_deg_beyond_bachelor => p_isir_matched_record.deg_beyond_bachelor,
x_s_married => p_isir_matched_record.s_married,
x_s_have_children => p_isir_matched_record.s_have_children,
x_legal_dependents => p_isir_matched_record.legal_dependents,
x_orphan_ward_of_court => p_isir_matched_record.orphan_ward_of_court,
x_s_veteran => p_isir_matched_record.s_veteran,
x_p_marital_status => p_isir_matched_record.p_marital_status,
x_father_ssn => p_isir_matched_record.father_ssn,
x_f_last_name => p_isir_matched_record.f_last_name,
x_mother_ssn => p_isir_matched_record.mother_ssn,
x_m_last_name => p_isir_matched_record.m_last_name,
x_p_num_family_member => p_isir_matched_record.p_num_family_member,
x_p_num_in_college => p_isir_matched_record.p_num_in_college,
x_p_state_legal_residence => p_isir_matched_record.p_state_legal_residence,
x_p_state_legal_res_before_dt => p_isir_matched_record.p_state_legal_res_before_dt,
x_p_legal_res_date => p_isir_matched_record.p_legal_res_date,
x_age_older_parent => p_isir_matched_record.age_older_parent,
x_p_tax_return_status => p_isir_matched_record.p_tax_return_status,
x_p_type_tax_return => p_isir_matched_record.p_type_tax_return,
x_p_elig_1040aez => p_isir_matched_record.p_elig_1040aez,
x_p_adjusted_gross_income => p_isir_matched_record.p_adjusted_gross_income,
x_p_taxes_paid => p_isir_matched_record.p_taxes_paid,
x_p_exemptions => p_isir_matched_record.p_exemptions,
x_f_income_work => p_isir_matched_record.f_income_work,
x_m_income_work => p_isir_matched_record.m_income_work,
x_p_income_wsa => p_isir_matched_record.p_income_wsa,
x_p_income_wsb => p_isir_matched_record.p_income_wsb,
x_p_income_wsc => p_isir_matched_record.p_income_wsc,
x_p_investment_networth => p_isir_matched_record.p_investment_networth,
x_p_business_networth => p_isir_matched_record.p_business_networth,
x_p_cash_saving => p_isir_matched_record.p_cash_saving,
x_s_num_family_members => p_isir_matched_record.s_num_family_members,
x_s_num_in_college => p_isir_matched_record.s_num_in_college,
x_first_college => p_isir_matched_record.first_college,
x_first_house_plan => p_isir_matched_record.first_house_plan,
x_second_college => p_isir_matched_record.second_college,
x_second_house_plan => p_isir_matched_record.second_house_plan,
x_third_college => p_isir_matched_record.third_college,
x_third_house_plan => p_isir_matched_record.third_house_plan,
x_fourth_college => p_isir_matched_record.fourth_college,
x_fourth_house_plan => p_isir_matched_record.fourth_house_plan,
x_fifth_college => p_isir_matched_record.fifth_college,
x_fifth_house_plan => p_isir_matched_record.fifth_house_plan,
x_sixth_college => p_isir_matched_record.sixth_college,
x_sixth_house_plan => p_isir_matched_record.sixth_house_plan,
x_date_app_completed => p_isir_matched_record.date_app_completed,
x_signed_by => p_isir_matched_record.signed_by,
x_preparer_ssn => p_isir_matched_record.preparer_ssn,
x_preparer_emp_id_number => p_isir_matched_record.preparer_emp_id_number,
x_preparer_sign => p_isir_matched_record.preparer_sign,
x_transaction_receipt_date => p_isir_matched_record.transaction_receipt_date,
x_dependency_override_ind => p_isir_matched_record.dependency_override_ind,
x_faa_fedral_schl_code => p_isir_matched_record.faa_fedral_schl_code,
x_faa_adjustment => p_isir_matched_record.faa_adjustment,
x_input_record_type => p_isir_matched_record.input_record_type,
x_serial_number => p_isir_matched_record.serial_number,
x_batch_number => p_isir_matched_record.batch_number,
x_early_analysis_flag => p_isir_matched_record.early_analysis_flag,
x_app_entry_source_code => p_isir_matched_record.app_entry_source_code,
x_eti_destination_code => p_isir_matched_record.eti_destination_code,
x_reject_override_b => p_isir_matched_record.reject_override_b,
x_reject_override_n => p_isir_matched_record.reject_override_n,
x_reject_override_w => p_isir_matched_record.reject_override_w,
x_assum_override_1 => p_isir_matched_record.assum_override_1,
x_assum_override_2 => p_isir_matched_record.assum_override_2,
x_assum_override_3 => p_isir_matched_record.assum_override_3,
x_assum_override_4 => p_isir_matched_record.assum_override_4,
x_assum_override_5 => p_isir_matched_record.assum_override_5,
x_assum_override_6 => p_isir_matched_record.assum_override_6,
x_dependency_status => p_isir_matched_record.dependency_status,
x_s_email_address => p_isir_matched_record.s_email_address,
x_nslds_reason_code => p_isir_matched_record.nslds_reason_code,
x_app_receipt_date => p_isir_matched_record.app_receipt_date,
x_processed_rec_type => p_isir_matched_record.processed_rec_type,
x_hist_correction_for_tran_id => p_isir_matched_record.hist_correction_for_tran_id,
x_system_generated_indicator => p_isir_matched_record.system_generated_indicator,
x_dup_request_indicator => p_isir_matched_record.dup_request_indicator,
x_source_of_correction => p_isir_matched_record.source_of_correction,
x_p_cal_tax_status => p_isir_matched_record.p_cal_tax_status,
x_s_cal_tax_status => p_isir_matched_record.s_cal_tax_status,
x_graduate_flag => p_isir_matched_record.graduate_flag,
x_auto_zero_efc => p_isir_matched_record.auto_zero_efc,
x_efc_change_flag => p_isir_matched_record.efc_change_flag,
x_sarc_flag => p_isir_matched_record.sarc_flag,
x_simplified_need_test => p_isir_matched_record.simplified_need_test,
x_reject_reason_codes => p_isir_matched_record.reject_reason_codes,
x_select_service_match_flag => p_isir_matched_record.select_service_match_flag,
x_select_service_reg_flag => p_isir_matched_record.select_service_reg_flag,
x_ins_match_flag => p_isir_matched_record.ins_match_flag,
x_ins_verification_number => NULL,
x_sec_ins_match_flag => p_isir_matched_record.sec_ins_match_flag,
x_sec_ins_ver_number => p_isir_matched_record.sec_ins_ver_number,
x_ssn_match_flag => p_isir_matched_record.ssn_match_flag,
x_ssa_citizenship_flag => p_isir_matched_record.ssa_citizenship_flag,
x_ssn_date_of_death => p_isir_matched_record.ssn_date_of_death,
x_nslds_match_flag => p_isir_matched_record.nslds_match_flag,
x_va_match_flag => p_isir_matched_record.va_match_flag,
x_prisoner_match => p_isir_matched_record.prisoner_match,
x_verification_flag => p_isir_matched_record.verification_flag,
x_subsequent_app_flag => p_isir_matched_record.subsequent_app_flag,
x_app_source_site_code => p_isir_matched_record.app_source_site_code,
x_tran_source_site_code => p_isir_matched_record.tran_source_site_code,
x_drn => p_isir_matched_record.drn,
x_tran_process_date => p_isir_matched_record.tran_process_date,
x_computer_batch_number => p_isir_matched_record.computer_batch_number,
x_correction_flags => p_isir_matched_record.correction_flags,
x_highlight_flags => p_isir_matched_record.highlight_flags,
x_paid_efc => NULL,
x_primary_efc => p_isir_matched_record.primary_efc,
x_secondary_efc => p_isir_matched_record.secondary_efc,
x_fed_pell_grant_efc_type => NULL,
x_primary_efc_type => p_isir_matched_record.primary_efc_type,
x_sec_efc_type => p_isir_matched_record.sec_efc_type,
x_primary_alternate_month_1 => p_isir_matched_record.primary_alternate_month_1,
x_primary_alternate_month_2 => p_isir_matched_record.primary_alternate_month_2,
x_primary_alternate_month_3 => p_isir_matched_record.primary_alternate_month_3,
x_primary_alternate_month_4 => p_isir_matched_record.primary_alternate_month_4,
x_primary_alternate_month_5 => p_isir_matched_record.primary_alternate_month_5,
x_primary_alternate_month_6 => p_isir_matched_record.primary_alternate_month_6,
x_primary_alternate_month_7 => p_isir_matched_record.primary_alternate_month_7,
x_primary_alternate_month_8 => p_isir_matched_record.primary_alternate_month_8,
x_primary_alternate_month_10 => p_isir_matched_record.primary_alternate_month_10,
x_primary_alternate_month_11 => p_isir_matched_record.primary_alternate_month_11,
x_primary_alternate_month_12 => p_isir_matched_record.primary_alternate_month_12,
x_sec_alternate_month_1 => p_isir_matched_record.sec_alternate_month_1,
x_sec_alternate_month_2 => p_isir_matched_record.sec_alternate_month_2,
x_sec_alternate_month_3 => p_isir_matched_record.sec_alternate_month_3,
x_sec_alternate_month_4 => p_isir_matched_record.sec_alternate_month_4,
x_sec_alternate_month_5 => p_isir_matched_record.sec_alternate_month_5,
x_sec_alternate_month_6 => p_isir_matched_record.sec_alternate_month_6,
x_sec_alternate_month_7 => p_isir_matched_record.sec_alternate_month_7,
x_sec_alternate_month_8 => p_isir_matched_record.sec_alternate_month_8,
x_sec_alternate_month_10 => p_isir_matched_record.sec_alternate_month_10,
x_sec_alternate_month_11 => p_isir_matched_record.sec_alternate_month_11,
x_sec_alternate_month_12 => p_isir_matched_record.sec_alternate_month_12,
x_total_income => p_isir_matched_record.total_income,
x_allow_total_income => p_isir_matched_record.allow_total_income,
x_state_tax_allow => p_isir_matched_record.state_tax_allow,
x_employment_allow => p_isir_matched_record.employment_allow,
x_income_protection_allow => p_isir_matched_record.income_protection_allow,
x_available_income => p_isir_matched_record.available_income,
x_contribution_from_ai => p_isir_matched_record.contribution_from_ai,
x_discretionary_networth => p_isir_matched_record.discretionary_networth,
x_efc_networth => p_isir_matched_record.efc_networth,
x_asset_protect_allow => p_isir_matched_record.asset_protect_allow,
x_parents_cont_from_assets => p_isir_matched_record.parents_cont_from_assets,
x_adjusted_available_income => p_isir_matched_record.adjusted_available_income,
x_total_student_contribution => p_isir_matched_record.total_student_contribution,
x_total_parent_contribution => p_isir_matched_record.total_parent_contribution,
x_parents_contribution => p_isir_matched_record.parents_contribution,
x_student_total_income => p_isir_matched_record.student_total_income,
x_sati => p_isir_matched_record.sati,
x_sic => p_isir_matched_record.sic,
x_sdnw => p_isir_matched_record.sdnw,
x_sca => p_isir_matched_record.sca,
x_fti => p_isir_matched_record.fti,
x_secti => p_isir_matched_record.secti,
x_secati => p_isir_matched_record.secati,
x_secstx => p_isir_matched_record.secstx,
x_secea => p_isir_matched_record.secea,
x_secipa => p_isir_matched_record.secipa,
x_secai => p_isir_matched_record.secai,
x_seccai => p_isir_matched_record.seccai,
x_secdnw => p_isir_matched_record.secdnw,
x_secnw => p_isir_matched_record.secnw,
x_secapa => p_isir_matched_record.secapa,
x_secpca => p_isir_matched_record.secpca,
x_secaai => p_isir_matched_record.secaai,
x_sectsc => p_isir_matched_record.sectsc,
x_sectpc => p_isir_matched_record.sectpc,
x_secpc => p_isir_matched_record.secpc,
x_secsti => p_isir_matched_record.secsti,
x_secsic => p_isir_matched_record.secsic,
x_secsati => p_isir_matched_record.secsati,
x_secsdnw => p_isir_matched_record.secsdnw,
x_secsca => p_isir_matched_record.secsca,
x_secfti => p_isir_matched_record.secfti,
x_a_citizenship => p_isir_matched_record.a_citizenship,
x_a_student_marital_status => p_isir_matched_record.a_student_marital_status,
x_a_student_agi => p_isir_matched_record.a_student_agi,
x_a_s_us_tax_paid => p_isir_matched_record.a_s_us_tax_paid,
x_a_s_income_work => p_isir_matched_record.a_s_income_work,
x_a_spouse_income_work => p_isir_matched_record.a_spouse_income_work,
x_a_s_total_wsc => p_isir_matched_record.a_s_total_wsc,
x_a_date_of_birth => p_isir_matched_record.a_date_of_birth,
x_a_student_married => p_isir_matched_record.a_student_married,
x_a_have_children => p_isir_matched_record.a_have_children,
x_a_s_have_dependents => p_isir_matched_record.a_s_have_dependents,
x_a_va_status => p_isir_matched_record.a_va_status,
x_a_s_num_in_family => p_isir_matched_record.a_s_num_in_family,
x_a_s_num_in_college => p_isir_matched_record.a_s_num_in_college,
x_a_p_marital_status => p_isir_matched_record.a_p_marital_status,
x_a_father_ssn => p_isir_matched_record.a_father_ssn,
x_a_mother_ssn => p_isir_matched_record.a_mother_ssn,
x_a_parents_num_family => p_isir_matched_record.a_parents_num_family,
x_a_parents_num_college => p_isir_matched_record.a_parents_num_college,
x_a_parents_agi => p_isir_matched_record.a_parents_agi,
x_a_p_us_tax_paid => p_isir_matched_record.a_p_us_tax_paid,
x_a_f_work_income => p_isir_matched_record.a_f_work_income,
x_a_m_work_income => p_isir_matched_record.a_m_work_income,
x_a_p_total_wsc => p_isir_matched_record.a_p_total_wsc,
x_comment_codes => p_isir_matched_record.comment_codes,
x_sar_ack_comm_code => p_isir_matched_record.sar_ack_comm_code,
x_pell_grant_elig_flag => p_isir_matched_record.pell_grant_elig_flag,
x_reprocess_reason_code => p_isir_matched_record.reprocess_reason_code,
x_duplicate_date => p_isir_matched_record.duplicate_date,
x_isir_transaction_type => p_isir_matched_record.isir_transaction_type,
x_fedral_schl_code_indicator => p_isir_matched_record.fedral_schl_code_indicator,
x_multi_school_code_flags => p_isir_matched_record.multi_school_code_flags,
x_dup_ssn_indicator => p_isir_matched_record.dup_ssn_indicator,
x_system_record_type => p_isir_matched_record.system_record_type,
x_verif_track_flag => p_isir_matched_record.verif_track_flag,
x_payment_isir => p_payment_isir,
x_receipt_status => p_isir_matched_record.receipt_status,
x_isir_receipt_completed => p_isir_matched_record.isir_receipt_completed,
x_active_isir => p_active_isir ,
x_fafsa_data_verify_flags => p_isir_matched_record.fafsa_data_verify_flags,
x_reject_override_a => p_isir_matched_record.reject_override_a,
x_reject_override_c => p_isir_matched_record.reject_override_c,
x_parent_marital_status_date => p_isir_matched_record.parent_marital_status_date,
x_legacy_record_flag => NULL,
x_father_first_name_initial => p_isir_matched_record.father_first_name_initial_txt,
x_father_step_father_birth_dt => p_isir_matched_record.father_step_father_birth_date,
x_mother_first_name_initial => p_isir_matched_record.mother_first_name_initial_txt,
x_mother_step_mother_birth_dt => p_isir_matched_record.mother_step_mother_birth_date,
x_parents_email_address_txt => p_isir_matched_record.parents_email_address_txt,
x_address_change_type => p_isir_matched_record.address_change_type,
x_cps_pushed_isir_flag => p_isir_matched_record.cps_pushed_isir_flag,
x_electronic_transaction_type => p_isir_matched_record.electronic_transaction_type,
x_sar_c_change_type => p_isir_matched_record.sar_c_change_type,
x_father_ssn_match_type => p_isir_matched_record.father_ssn_match_type,
x_mother_ssn_match_type => p_isir_matched_record.mother_ssn_match_type,
x_reject_override_g_flag => p_isir_matched_record.reject_override_g_flag,
x_dhs_verification_num_txt => p_isir_matched_record.dhs_verification_num_txt,
x_data_file_name_txt => p_isir_matched_record.data_file_name_txt,
x_message_class_txt => p_isir_matched_record.message_class_txt,
x_reject_override_3_flag => p_isir_matched_record.reject_override_3_flag,
x_reject_override_12_flag => p_isir_matched_record.reject_override_12_flag,
x_reject_override_j_flag => p_isir_matched_record.reject_override_j_flag,
x_reject_override_k_flag => p_isir_matched_record.reject_override_k_flag,
x_rejected_status_change_flag => p_isir_matched_record.rejected_status_change_flag,
x_verification_selection_flag => p_isir_matched_record.verification_selection_flag
);
log_debug_message(' Successfully updated Isir Matched record. ' || p_isir_matched_record.isir_id || '. Payment ISIR Flag: ' || p_payment_isir || '. Active ISIR Flag: ' || p_active_isir);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.update_isir_matched_rec.statement','Successfully updated ISIR record for ISIR ID : ' || p_isir_matched_record.isir_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.update_isir_matched_rec(.exception','The exception is : ' || SQLERRM );
END update_isir_matched_rec;
SELECT im.*
FROM igf_ap_isir_matched im
WHERE im.base_id = pn_base_id
AND im.active_isir = 'Y';
update_isir_matched_rec(p_isir_matched_record => old_awd_isir_rec,
p_payment_isir => old_awd_isir_rec.payment_isir, -- retain existing value
p_active_isir => 'N'); -- retain existing value
SELECT im.*
FROM igf_ap_isir_matched im
WHERE im.base_id = pn_base_id
AND im.payment_isir = 'Y';
update_isir_matched_rec(p_isir_matched_record => old_pymt_isir_rec,
p_payment_isir => 'N', -- make it Non payment isir
p_active_isir => old_pymt_isir_rec.active_isir); -- retain existing value
PROCEDURE insert_isir_matched_rec(cp_isir_int_rec igf_ap_isir_ints%ROWTYPE,
p_payment_isir igf_ap_isir_matched_all.payment_isir%TYPE,
p_active_isir igf_ap_isir_matched_all.active_isir%TYPE,
p_base_id NUMBER,
p_out_isir_id OUT NOCOPY NUMBER
) IS
/*
|| Created By : rgangara
|| Created On : 03-AUG-2004
|| Purpose : For Inserting record into ISIR matched table based on ISIR int record.
|| Known limitations, enhancements or remarks :
|| PARAMETERS : p_payment_isir indicates that the New isir matched rec is also a Payment isir
|| p_active_isir indicates that the New isir matched rec is also an Active isir
|| p_out_isir_id OUT parameter returns the ISIR ID of the new rec inserted.
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
l_mesg_class igf_ap_isir_matched_all.message_class_txt%TYPE;
log_debug_message(' Beginning Insert of ISIR Matched record. Base ID: ' || p_base_id || ', Payment ISIR Flag: ' || p_payment_isir);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.insert_isir_matched_rec.debug','Inserting ISIR Matched record: Base ID: ' || p_Base_id || ', Payment: ' || p_payment_isir);
igf_ap_isir_matched_pkg.insert_row(
x_Mode => 'R',
x_rowid => lv_rowid,
x_isir_id => p_out_isir_id, -- value copied to OUT parameter
x_base_id => p_base_id,
x_batch_year => cp_isir_int_rec.batch_year_num,
x_transaction_num => cp_isir_int_rec.transaction_num_txt,
x_current_ssn => cp_isir_int_rec.current_ssn_txt,
x_ssn_name_change => cp_isir_int_rec.ssn_name_change_type,
x_original_ssn => cp_isir_int_rec.original_ssn_txt,
x_orig_name_id => cp_isir_int_rec.orig_name_id_txt,
x_last_name => cp_isir_int_rec.last_name,
x_first_name => cp_isir_int_rec.first_name,
x_middle_initial => cp_isir_int_rec.middle_initial_txt,
x_perm_mail_add => cp_isir_int_rec.perm_mail_address_txt,
x_perm_city => cp_isir_int_rec.perm_city_txt,
x_perm_state => cp_isir_int_rec.perm_state_txt,
x_perm_zip_code => cp_isir_int_rec.perm_zip_cd,
x_date_of_birth => cp_isir_int_rec.birth_date,
x_phone_number => cp_isir_int_rec.phone_number_txt,
x_driver_license_number => cp_isir_int_rec.driver_license_number_txt,
x_driver_license_state => cp_isir_int_rec.driver_license_state_txt,
x_citizenship_status => cp_isir_int_rec.citizenship_status_type,
x_alien_reg_number => cp_isir_int_rec.alien_reg_number_txt,
x_s_marital_status => cp_isir_int_rec.s_marital_status_type,
x_s_marital_status_date => cp_isir_int_rec.s_marital_status_date,
x_summ_enrl_status => cp_isir_int_rec.summ_enrl_status_type,
x_fall_enrl_status => cp_isir_int_rec.fall_enrl_status_type,
x_winter_enrl_status => cp_isir_int_rec.winter_enrl_status_type,
x_spring_enrl_status => cp_isir_int_rec.spring_enrl_status_type,
x_summ2_enrl_status => cp_isir_int_rec.summ2_enrl_status_type,
x_fathers_highest_edu_level => cp_isir_int_rec.fathers_highst_edu_lvl_type,
x_mothers_highest_edu_level => cp_isir_int_rec.mothers_highst_edu_lvl_type,
x_s_state_legal_residence => cp_isir_int_rec.s_state_legal_residence,
x_legal_residence_before_date => cp_isir_int_rec.legal_res_before_year_flag,
x_s_legal_resd_date => cp_isir_int_rec.s_legal_resd_date,
x_ss_r_u_male => cp_isir_int_rec.ss_r_u_male_flag,
x_selective_service_reg => cp_isir_int_rec.selective_service_reg_flag,
x_degree_certification => cp_isir_int_rec.degree_certification_type,
x_grade_level_in_college => cp_isir_int_rec.grade_level_in_college_type,
x_high_school_diploma_ged => cp_isir_int_rec.high_schl_diploma_ged_flag,
x_first_bachelor_deg_by_date => cp_isir_int_rec.first_bachlr_deg_year_flag,
x_interest_in_loan => cp_isir_int_rec.interest_in_loan_flag,
x_interest_in_stud_employment => cp_isir_int_rec.interest_in_stu_employ_flag,
x_drug_offence_conviction => cp_isir_int_rec.drug_offence_convict_type,
x_s_tax_return_status => cp_isir_int_rec.s_tax_return_status_type,
x_s_type_tax_return => cp_isir_int_rec.s_type_tax_return_type,
x_s_elig_1040ez => cp_isir_int_rec.s_elig_1040ez_type,
x_s_adjusted_gross_income => cp_isir_int_rec.s_adjusted_gross_income_amt,
x_s_fed_taxes_paid => cp_isir_int_rec.s_fed_taxes_paid_amt,
x_s_exemptions => cp_isir_int_rec.s_exemptions_amt,
x_s_income_from_work => cp_isir_int_rec.s_income_from_work_amt,
x_spouse_income_from_work => cp_isir_int_rec.spouse_income_from_work_amt,
x_s_toa_amt_from_wsa => cp_isir_int_rec.s_total_from_wsa_amt,
x_s_toa_amt_from_wsb => cp_isir_int_rec.s_total_from_wsb_amt,
x_s_toa_amt_from_wsc => cp_isir_int_rec.s_total_from_wsc_amt,
x_s_investment_networth => cp_isir_int_rec.s_investment_networth_amt,
x_s_busi_farm_networth => cp_isir_int_rec.s_busi_farm_networth_amt,
x_s_cash_savings => cp_isir_int_rec.s_cash_savings_amt,
x_va_months => cp_isir_int_rec.va_months_num,
x_va_amount => cp_isir_int_rec.va_amt,
x_stud_dob_before_date => cp_isir_int_rec.stud_dob_before_year_flag,
x_deg_beyond_bachelor => cp_isir_int_rec.deg_beyond_bachelor_flag,
x_s_married => cp_isir_int_rec.s_married_flag,
x_s_have_children => cp_isir_int_rec.s_have_children_flag,
x_legal_dependents => cp_isir_int_rec.legal_dependents_flag,
x_orphan_ward_of_court => cp_isir_int_rec.orphan_ward_of_court_flag,
x_s_veteran => cp_isir_int_rec.s_veteran_flag,
x_p_marital_status => cp_isir_int_rec.p_marital_status_type,
x_father_ssn => cp_isir_int_rec.father_ssn_txt,
x_f_last_name => cp_isir_int_rec.f_last_name,
x_mother_ssn => cp_isir_int_rec.mother_ssn_txt,
x_m_last_name => cp_isir_int_rec.m_last_name,
x_p_num_family_member => cp_isir_int_rec.p_family_members_num,
x_p_num_in_college => cp_isir_int_rec.p_in_college_num,
x_p_state_legal_residence => cp_isir_int_rec.p_state_legal_residence_txt,
x_p_state_legal_res_before_dt => cp_isir_int_rec.p_legal_res_before_dt_flag,
x_p_legal_res_date => cp_isir_int_rec.p_legal_res_date,
x_age_older_parent => cp_isir_int_rec.age_older_parent_num,
x_p_tax_return_status => cp_isir_int_rec.p_tax_return_status_type,
x_p_type_tax_return => cp_isir_int_rec.p_type_tax_return_type,
x_p_elig_1040aez => cp_isir_int_rec.p_elig_1040aez_type,
x_p_adjusted_gross_income => cp_isir_int_rec.p_adjusted_gross_income_amt,
x_p_taxes_paid => cp_isir_int_rec.p_taxes_paid_amt,
x_p_exemptions => cp_isir_int_rec.p_exemptions_amt,
x_f_income_work => cp_isir_int_rec.f_income_work_amt,
x_m_income_work => cp_isir_int_rec.m_income_work_amt,
x_p_income_wsa => cp_isir_int_rec.p_income_wsa_amt,
x_p_income_wsb => cp_isir_int_rec.p_income_wsb_amt,
x_p_income_wsc => cp_isir_int_rec.p_income_wsc_amt,
x_p_investment_networth => cp_isir_int_rec.p_investment_networth_amt,
x_p_business_networth => cp_isir_int_rec.p_business_networth_amt,
x_p_cash_saving => cp_isir_int_rec.p_cash_saving_amt,
x_s_num_family_members => cp_isir_int_rec.s_family_members_num,
x_s_num_in_college => cp_isir_int_rec.s_in_college_num,
x_first_college => cp_isir_int_rec.first_college_cd,
x_first_house_plan => cp_isir_int_rec.first_house_plan_type,
x_second_college => cp_isir_int_rec.second_college_cd,
x_second_house_plan => cp_isir_int_rec.second_house_plan_type,
x_third_college => cp_isir_int_rec.third_college_cd,
x_third_house_plan => cp_isir_int_rec.third_house_plan_type,
x_fourth_college => cp_isir_int_rec.fourth_college_cd,
x_fourth_house_plan => cp_isir_int_rec.fourth_house_plan_type,
x_fifth_college => cp_isir_int_rec.fifth_college_cd,
x_fifth_house_plan => cp_isir_int_rec.fifth_house_plan_type,
x_sixth_college => cp_isir_int_rec.sixth_college_cd,
x_sixth_house_plan => cp_isir_int_rec.sixth_house_plan_type,
x_date_app_completed => cp_isir_int_rec.app_completed_date,
x_signed_by => cp_isir_int_rec.signed_by_type,
x_preparer_ssn => cp_isir_int_rec.preparer_ssn_txt,
x_preparer_emp_id_number => cp_isir_int_rec.preparer_emp_id_number_txt,
x_preparer_sign => cp_isir_int_rec.preparer_sign_flag,
x_transaction_receipt_date => cp_isir_int_rec.transaction_receipt_date,
x_dependency_override_ind => cp_isir_int_rec.dependency_override_type,
x_faa_fedral_schl_code => cp_isir_int_rec.faa_fedral_schl_cd,
x_faa_adjustment => cp_isir_int_rec.faa_adjustment_type,
x_input_record_type => cp_isir_int_rec.input_record_type,
x_serial_number => cp_isir_int_rec.serial_num,
x_batch_number => cp_isir_int_rec.batch_number_txt,
x_early_analysis_flag => cp_isir_int_rec.early_analysis_flag,
x_app_entry_source_code => cp_isir_int_rec.app_entry_source_type,
x_eti_destination_code => cp_isir_int_rec.eti_destination_cd,
x_reject_override_b => cp_isir_int_rec.reject_override_b_flag,
x_reject_override_n => cp_isir_int_rec.reject_override_n_flag,
x_reject_override_w => cp_isir_int_rec.reject_override_w_flag,
x_assum_override_1 => cp_isir_int_rec.assum_override_1_flag,
x_assum_override_2 => cp_isir_int_rec.assum_override_2_flag,
x_assum_override_3 => cp_isir_int_rec.assum_override_3_flag,
x_assum_override_4 => cp_isir_int_rec.assum_override_4_flag,
x_assum_override_5 => cp_isir_int_rec.assum_override_5_flag,
x_assum_override_6 => cp_isir_int_rec.assum_override_6_flag,
x_dependency_status => cp_isir_int_rec.dependency_status_type,
x_s_email_address => cp_isir_int_rec.s_email_address_txt,
x_nslds_reason_code => cp_isir_int_rec.nslds_reason_cd,
x_app_receipt_date => cp_isir_int_rec.app_receipt_date,
x_processed_rec_type => cp_isir_int_rec.processed_rec_type,
x_hist_correction_for_tran_id => cp_isir_int_rec.hist_corr_for_tran_num,
x_system_generated_indicator => cp_isir_int_rec.sys_generated_indicator_type,
x_dup_request_indicator => cp_isir_int_rec.dup_request_indicator_type,
x_source_of_correction => cp_isir_int_rec.source_of_correction_type,
x_p_cal_tax_status => cp_isir_int_rec.p_cal_tax_status_type,
x_s_cal_tax_status => cp_isir_int_rec.s_cal_tax_status_type,
x_graduate_flag => cp_isir_int_rec.graduate_flag,
x_auto_zero_efc => cp_isir_int_rec.auto_zero_efc_flag,
x_efc_change_flag => cp_isir_int_rec.efc_change_flag,
x_sarc_flag => cp_isir_int_rec.sarc_flag,
x_simplified_need_test => cp_isir_int_rec.simplified_need_test_flag,
x_reject_reason_codes => cp_isir_int_rec.reject_reason_codes_txt,
x_select_service_match_flag => cp_isir_int_rec.select_service_match_type,
x_select_service_reg_flag => cp_isir_int_rec.select_service_reg_type,
x_ins_match_flag => cp_isir_int_rec.ins_match_flag,
x_ins_verification_number => NULL,
x_sec_ins_match_flag => cp_isir_int_rec.sec_ins_match_type,
x_sec_ins_ver_number => cp_isir_int_rec.sec_ins_ver_num,
x_ssn_match_flag => cp_isir_int_rec.ssn_match_type,
x_ssa_citizenship_flag => cp_isir_int_rec.ssa_citizenship_type,
x_ssn_date_of_death => cp_isir_int_rec.ssn_death_date,
x_nslds_match_flag => cp_isir_int_rec.nslds_match_type,
x_va_match_flag => cp_isir_int_rec.va_match_type,
x_prisoner_match => cp_isir_int_rec.prisoner_match_flag,
x_verification_flag => cp_isir_int_rec.verification_flag,
x_subsequent_app_flag => cp_isir_int_rec.subsequent_app_flag,
x_app_source_site_code => cp_isir_int_rec.app_source_site_cd,
x_tran_source_site_code => cp_isir_int_rec.tran_source_site_cd,
x_drn => cp_isir_int_rec.drn_num,
x_tran_process_date => cp_isir_int_rec.tran_process_date,
x_computer_batch_number => cp_isir_int_rec.computer_batch_num,
x_correction_flags => cp_isir_int_rec.correction_flags_txt,
x_highlight_flags => cp_isir_int_rec.highlight_flags_txt,
x_paid_efc => NULL,
x_primary_efc => cp_isir_int_rec.primary_efc_amt,
x_secondary_efc => cp_isir_int_rec.secondary_efc_amt,
x_fed_pell_grant_efc_type => NULL,
x_primary_efc_type => cp_isir_int_rec.primary_efc_type,
x_sec_efc_type => cp_isir_int_rec.sec_efc_type,
x_primary_alternate_month_1 => cp_isir_int_rec.primary_alt_month_1_amt,
x_primary_alternate_month_2 => cp_isir_int_rec.primary_alt_month_2_amt,
x_primary_alternate_month_3 => cp_isir_int_rec.primary_alt_month_3_amt,
x_primary_alternate_month_4 => cp_isir_int_rec.primary_alt_month_4_amt,
x_primary_alternate_month_5 => cp_isir_int_rec.primary_alt_month_5_amt,
x_primary_alternate_month_6 => cp_isir_int_rec.primary_alt_month_6_amt,
x_primary_alternate_month_7 => cp_isir_int_rec.primary_alt_month_7_amt,
x_primary_alternate_month_8 => cp_isir_int_rec.primary_alt_month_8_amt,
x_primary_alternate_month_10 => cp_isir_int_rec.primary_alt_month_10_amt,
x_primary_alternate_month_11 => cp_isir_int_rec.primary_alt_month_11_amt,
x_primary_alternate_month_12 => cp_isir_int_rec.primary_alt_month_12_amt,
x_sec_alternate_month_1 => cp_isir_int_rec.sec_alternate_month_1_amt,
x_sec_alternate_month_2 => cp_isir_int_rec.sec_alternate_month_2_amt,
x_sec_alternate_month_3 => cp_isir_int_rec.sec_alternate_month_3_amt,
x_sec_alternate_month_4 => cp_isir_int_rec.sec_alternate_month_4_amt,
x_sec_alternate_month_5 => cp_isir_int_rec.sec_alternate_month_5_amt,
x_sec_alternate_month_6 => cp_isir_int_rec.sec_alternate_month_6_amt,
x_sec_alternate_month_7 => cp_isir_int_rec.sec_alternate_month_7_amt,
x_sec_alternate_month_8 => cp_isir_int_rec.sec_alternate_month_8_amt,
x_sec_alternate_month_10 => cp_isir_int_rec.sec_alternate_month_10_amt,
x_sec_alternate_month_11 => cp_isir_int_rec.sec_alternate_month_11_amt,
x_sec_alternate_month_12 => cp_isir_int_rec.sec_alternate_month_12_amt,
x_total_income => cp_isir_int_rec.total_income_amt,
x_allow_total_income => cp_isir_int_rec.allow_total_income_amt,
x_state_tax_allow => cp_isir_int_rec.state_tax_allow_amt,
x_employment_allow => cp_isir_int_rec.employment_allow_amt,
x_income_protection_allow => cp_isir_int_rec.income_protection_allow_amt,
x_available_income => cp_isir_int_rec.available_income_amt,
x_contribution_from_ai => cp_isir_int_rec.contribution_from_ai_amt,
x_discretionary_networth => cp_isir_int_rec.discretionary_networth_amt,
x_efc_networth => cp_isir_int_rec.efc_networth_amt,
x_asset_protect_allow => cp_isir_int_rec.asset_protect_allow_amt,
x_parents_cont_from_assets => cp_isir_int_rec.parents_cont_from_assets_amt,
x_adjusted_available_income => cp_isir_int_rec.adjusted_avail_income_amt,
x_total_student_contribution => cp_isir_int_rec.total_student_contrib_amt,
x_total_parent_contribution => cp_isir_int_rec.total_parent_contrib_amt,
x_parents_contribution => cp_isir_int_rec.parents_contribution_amt,
x_student_total_income => cp_isir_int_rec.student_total_income_amt,
x_sati => cp_isir_int_rec.sati_amt,
x_sic => cp_isir_int_rec.sic_amt,
x_sdnw => cp_isir_int_rec.sdnw_amt,
x_sca => cp_isir_int_rec.sca_amt,
x_fti => cp_isir_int_rec.fti_amt,
x_secti => cp_isir_int_rec.secti_amt,
x_secati => cp_isir_int_rec.secati_amt,
x_secstx => cp_isir_int_rec.secstx_amt,
x_secea => cp_isir_int_rec.secea_amt,
x_secipa => cp_isir_int_rec.secipa_amt,
x_secai => cp_isir_int_rec.secai_amt,
x_seccai => cp_isir_int_rec.seccai_amt,
x_secdnw => cp_isir_int_rec.secdnw_amt,
x_secnw => cp_isir_int_rec.secnw_amt,
x_secapa => cp_isir_int_rec.secapa_amt,
x_secpca => cp_isir_int_rec.SECPCA_AMT,
x_secaai => cp_isir_int_rec.secaai_amt,
x_sectsc => cp_isir_int_rec.sectsc_amt,
x_sectpc => cp_isir_int_rec.sectpc_amt,
x_secpc => cp_isir_int_rec.secpc_amt,
x_secsti => cp_isir_int_rec.secsti_amt,
x_secsic => cp_isir_int_rec.secsic_amt,
x_secsati => cp_isir_int_rec.secsati_amt,
x_secsdnw => cp_isir_int_rec.secsdnw_amt,
x_secsca => cp_isir_int_rec.secsca_amt,
x_secfti => cp_isir_int_rec.secfti_amt,
x_a_citizenship => cp_isir_int_rec.a_citizenship_flag,
x_a_student_marital_status => cp_isir_int_rec.a_studnt_marital_status_flag,
x_a_student_agi => cp_isir_int_rec.a_student_agi_amt,
x_a_s_us_tax_paid => cp_isir_int_rec.a_s_us_tax_paid_amt,
x_a_s_income_work => cp_isir_int_rec.a_s_income_work_amt,
x_a_spouse_income_work => cp_isir_int_rec.a_spouse_income_work_amt,
x_a_s_total_wsc => cp_isir_int_rec.a_s_total_wsc_amt,
x_a_date_of_birth => cp_isir_int_rec.a_date_of_birth_flag,
x_a_student_married => cp_isir_int_rec.a_student_married_flag,
x_a_have_children => cp_isir_int_rec.a_have_children_flag,
x_a_s_have_dependents => cp_isir_int_rec.a_s_have_dependents_flag,
x_a_va_status => cp_isir_int_rec.a_va_status_flag,
x_a_s_num_in_family => cp_isir_int_rec.a_s_in_family_num,
x_a_s_num_in_college => cp_isir_int_rec.a_s_in_college_num,
x_a_p_marital_status => cp_isir_int_rec.a_p_marital_status_flag,
x_a_father_ssn => cp_isir_int_rec.a_father_ssn_txt,
x_a_mother_ssn => cp_isir_int_rec.a_mother_ssn_txt,
x_a_parents_num_family => cp_isir_int_rec.a_parents_family_num,
x_a_parents_num_college => cp_isir_int_rec.a_parents_college_num,
x_a_parents_agi => cp_isir_int_rec.a_parents_agi_amt,
x_a_p_us_tax_paid => cp_isir_int_rec.a_p_us_tax_paid_amt,
x_a_f_work_income => cp_isir_int_rec.a_f_work_income_amt,
x_a_m_work_income => cp_isir_int_rec.a_m_work_income_amt,
x_a_p_total_wsc => cp_isir_int_rec.a_p_total_wsc_amt,
x_comment_codes => cp_isir_int_rec.comment_codes_txt,
x_sar_ack_comm_code => cp_isir_int_rec.sar_ack_comm_codes_txt,
x_pell_grant_elig_flag => cp_isir_int_rec.pell_grant_elig_flag,
x_reprocess_reason_code => cp_isir_int_rec.reprocess_reason_cd,
x_duplicate_date => cp_isir_int_rec.duplicate_date,
x_isir_transaction_type => cp_isir_int_rec.isir_transaction_type,
x_fedral_schl_code_indicator => cp_isir_int_rec.fedral_schl_type,
x_multi_school_code_flags => cp_isir_int_rec.multi_school_cd_flags_txt,
x_dup_ssn_indicator => cp_isir_int_rec.dup_ssn_indicator_flag,
x_system_record_type => 'ORIGINAL',
x_verif_track_flag => cp_isir_int_rec.verif_track_type,
x_payment_isir => p_payment_isir,
x_receipt_status => 'MATCHED',
x_isir_receipt_completed => 'N',
x_active_isir => p_active_isir,
x_fafsa_data_verify_flags => cp_isir_int_rec.fafsa_data_verification_txt,
x_reject_override_a => cp_isir_int_rec.reject_override_a_flag,
x_reject_override_c => cp_isir_int_rec.reject_override_c_flag,
x_parent_marital_status_date => cp_isir_int_rec.parent_marital_status_date,
x_legacy_record_flag => NULL,
x_father_first_name_initial => cp_isir_int_rec.fathr_first_name_initial_txt,
x_father_step_father_birth_dt => cp_isir_int_rec.fathr_step_father_birth_date,
x_mother_first_name_initial => cp_isir_int_rec.mothr_first_name_initial_txt,
x_mother_step_mother_birth_dt => cp_isir_int_rec.mothr_step_mother_birth_date,
x_parents_email_address_txt => cp_isir_int_rec.parents_email_address_txt,
x_address_change_type => cp_isir_int_rec.address_change_type,
x_cps_pushed_isir_flag => cp_isir_int_rec.cps_pushed_isir_flag,
x_electronic_transaction_type => cp_isir_int_rec.electronic_transaction_type,
x_sar_c_change_type => cp_isir_int_rec.sar_c_change_type,
x_father_ssn_match_type => cp_isir_int_rec.father_ssn_match_type,
x_mother_ssn_match_type => cp_isir_int_rec.mother_ssn_match_type,
x_reject_override_g_flag => cp_isir_int_rec.reject_override_g_flag,
x_dhs_verification_num_txt => cp_isir_int_rec.dhs_verification_num_txt,
x_data_file_name_txt => cp_isir_int_rec.data_file_name_txt,
x_message_class_txt => l_mesg_class,
x_reject_override_3_flag => cp_isir_int_rec.reject_override_3_flag,
x_reject_override_12_flag => cp_isir_int_rec.reject_override_12_flag,
x_reject_override_j_flag => cp_isir_int_rec.reject_override_j_flag,
x_reject_override_k_flag => cp_isir_int_rec.reject_override_k_flag,
x_rejected_status_change_flag => cp_isir_int_rec.rejected_status_change_flag,
x_verification_selection_flag => cp_isir_int_rec.verification_selection_flag
);
log_debug_message(' SUccessfully Inserted ISIR Matched record. ISIR ID : ' || p_out_isir_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.insert_isir_matched_rec.statement','Successfully inserted new ISIR record into ISIR Matched table. ISIR ID : ' || p_out_isir_id );
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.insert_isir_matched_rec.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','igf_ap_matching_process_pkg.insert_isir_matched_rec');
END insert_isir_matched_rec;
PROCEDURE insert_nslds_data_rec(cp_isir_intrface_rec igf_ap_isir_ints%ROWTYPE,
p_isir_id igf_ap_isir_matched_all.isir_id%TYPE,
p_base_id NUMBER,
p_out_nslds_id OUT NOCOPY NUMBER
)
IS
/*
|| Created By : rgangara
|| Created On : 03-AUG-2004
|| Purpose : For Inserting record into NSLDS data table based on ISIR int record.
|| Parameters :
|| 1. p_out_nslds_id is OUT parameter returning the NSLDS id of the inserted record
|| 2. p_isir_id is the isir_id of the corresponding record in isir matched table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
lv_rowid VARCHAR2(30);
log_debug_message(' Beginning Insert of NSLDS record for ISIR ID : ' || p_isir_id || '. Base ID : ' || p_base_id);
igf_ap_nslds_data_pkg.insert_row(
x_Mode => 'R',
x_rowid => lv_rowid,
x_nslds_id => p_out_nslds_id,
x_isir_id => p_isir_id,
x_base_id => p_base_id,
x_transaction_num_txt => cp_isir_intrface_rec.transaction_num_txt,
x_nslds_transaction_num => cp_isir_intrface_rec.nslds_transaction_num,
x_nslds_database_results_f => cp_isir_intrface_rec.nslds_database_results_type,
x_nslds_f => cp_isir_intrface_rec.nslds_flag,
x_nslds_pell_overpay_f => cp_isir_intrface_rec.nslds_pell_overpay_type,
x_nslds_pell_overpay_contact => cp_isir_intrface_rec.nslds_pell_overpay_cont_txt,
x_nslds_seog_overpay_f => cp_isir_intrface_rec.nslds_seog_overpay_type,
x_nslds_seog_overpay_contact => cp_isir_intrface_rec.nslds_seog_overpay_cont_txt,
x_nslds_perkins_overpay_f => cp_isir_intrface_rec.nslds_perkins_overpay_type,
x_nslds_perkins_overpay_cntct => cp_isir_intrface_rec.nslds_perk_ovrpay_cntct_txt,
x_nslds_defaulted_loan_f => cp_isir_intrface_rec.nslds_defaulted_loan_flag,
x_nslds_dischged_loan_chng_f => cp_isir_intrface_rec.nslds_dischgd_loan_chng_flag,
x_nslds_satis_repay_f => cp_isir_intrface_rec.nslds_satis_repay_flag,
x_nslds_act_bankruptcy_f => cp_isir_intrface_rec.nslds_act_bankruptcy_flag,
x_nslds_agg_subsz_out_prin_bal => cp_isir_intrface_rec.nslds_agg_subsz_out_pbal_amt,
x_nslds_agg_unsbz_out_prin_bal => cp_isir_intrface_rec.nslds_agg_unsbz_out_pbal_amt,
x_nslds_agg_comb_out_prin_bal => cp_isir_intrface_rec.nslds_agg_comb_out_pbal_amt,
x_nslds_agg_cons_out_prin_bal => cp_isir_intrface_rec.nslds_agg_cons_out_pbal_amt,
x_nslds_agg_subsz_pend_dismt => cp_isir_intrface_rec.nslds_agg_subsz_pnd_disb_amt,
x_nslds_agg_unsbz_pend_dismt => cp_isir_intrface_rec.nslds_agg_unsbz_pnd_disb_amt,
x_nslds_agg_comb_pend_dismt => cp_isir_intrface_rec.nslds_agg_comb_pend_disb_amt,
x_nslds_agg_subsz_total => cp_isir_intrface_rec.nslds_agg_subsz_total_amt,
x_nslds_agg_unsbz_total => cp_isir_intrface_rec.nslds_agg_unsbz_total_amt,
x_nslds_agg_comb_total => cp_isir_intrface_rec.nslds_agg_comb_total_amt,
x_nslds_agg_consd_total => cp_isir_intrface_rec.nslds_agg_consd_total_amt,
x_nslds_perkins_out_bal => cp_isir_intrface_rec.nslds_perkins_out_bal_amt,
x_nslds_perkins_cur_yr_dismnt => cp_isir_intrface_rec.nslds_perkin_cur_yr_disb_amt,
x_nslds_default_loan_chng_f => cp_isir_intrface_rec.nslds_default_loan_chng_flag,
x_nslds_discharged_loan_f => cp_isir_intrface_rec.nslds_discharged_loan_type,
x_nslds_satis_repay_chng_f => cp_isir_intrface_rec.nslds_satis_repay_chng_flag,
x_nslds_act_bnkrupt_chng_f => cp_isir_intrface_rec.nslds_act_bnkrupt_chng_flag,
x_nslds_overpay_chng_f => cp_isir_intrface_rec.nslds_overpay_chng_flag,
x_nslds_agg_loan_chng_f => cp_isir_intrface_rec.nslds_agg_loan_chng_flag,
x_nslds_perkins_loan_chng_f => cp_isir_intrface_rec.nslds_perkins_loan_chng_flag,
x_nslds_pell_paymnt_chng_f => cp_isir_intrface_rec.nslds_pell_paymnt_chng_flag,
x_nslds_addtnl_pell_f => cp_isir_intrface_rec.nslds_addtnl_pell_flag,
x_nslds_addtnl_loan_f => cp_isir_intrface_rec.nslds_addtnl_loan_flag,
x_direct_loan_mas_prom_nt_f => cp_isir_intrface_rec.direct_loan_mas_prom_nt_type,
x_nslds_pell_seq_num_1 => cp_isir_intrface_rec.nslds_pell_1_seq_num,
x_nslds_pell_verify_f_1 => cp_isir_intrface_rec.nslds_pell_1_verify_f_txt,
x_nslds_pell_efc_1 => cp_isir_intrface_rec.nslds_pell_1_efc_amt,
x_nslds_pell_school_code_1 => cp_isir_intrface_rec.nslds_pell_1_school_num,
x_nslds_pell_transcn_num_1 => cp_isir_intrface_rec.nslds_pell_1_transcn_num,
x_nslds_pell_last_updt_dt_1 => cp_isir_intrface_rec.nslds_pell_1_last_updt_date,
x_nslds_pell_scheduled_amt_1 => cp_isir_intrface_rec.nslds_pell_1_scheduled_amt,
x_nslds_pell_amt_paid_todt_1 => cp_isir_intrface_rec.nslds_pell_1_paid_todt_amt,
x_nslds_pell_remng_amt_1 => cp_isir_intrface_rec.nslds_pell_1_remng_amt,
x_nslds_pell_pc_schd_awd_us_1 => cp_isir_intrface_rec.nslds_pell_1_pc_scwd_use_amt,
x_nslds_pell_award_amt_1 => cp_isir_intrface_rec.nslds_pell_1_award_amt,
x_nslds_pell_seq_num_2 => cp_isir_intrface_rec.nslds_pell_2_seq_num,
x_nslds_pell_verify_f_2 => cp_isir_intrface_rec.nslds_pell_2_verify_f_txt,
x_nslds_pell_efc_2 => cp_isir_intrface_rec.nslds_pell_2_efc_amt,
x_nslds_pell_school_code_2 => cp_isir_intrface_rec.nslds_pell_2_school_num,
x_nslds_pell_transcn_num_2 => cp_isir_intrface_rec.nslds_pell_2_transcn_num,
x_nslds_pell_last_updt_dt_2 => cp_isir_intrface_rec.nslds_pell_2_last_updt_date,
x_nslds_pell_scheduled_amt_2 => cp_isir_intrface_rec.nslds_pell_2_scheduled_amt,
x_nslds_pell_amt_paid_todt_2 => cp_isir_intrface_rec.nslds_pell_2_paid_todt_amt,
x_nslds_pell_remng_amt_2 => cp_isir_intrface_rec.nslds_pell_2_remng_amt,
x_nslds_pell_pc_schd_awd_us_2 => cp_isir_intrface_rec.nslds_pell_2_pc_scwd_use_amt,
x_nslds_pell_award_amt_2 => cp_isir_intrface_rec.nslds_pell_2_award_amt,
x_nslds_pell_seq_num_3 => cp_isir_intrface_rec.nslds_pell_3_seq_num,
x_nslds_pell_verify_f_3 => cp_isir_intrface_rec.nslds_pell_3_verify_f_txt,
x_nslds_pell_efc_3 => cp_isir_intrface_rec.nslds_pell_3_efc_amt,
x_nslds_pell_school_code_3 => cp_isir_intrface_rec.nslds_pell_3_school_num,
x_nslds_pell_transcn_num_3 => cp_isir_intrface_rec.nslds_pell_3_transcn_num,
x_nslds_pell_last_updt_dt_3 => cp_isir_intrface_rec.nslds_pell_3_last_updt_date,
x_nslds_pell_scheduled_amt_3 => cp_isir_intrface_rec.nslds_pell_3_scheduled_amt,
x_nslds_pell_amt_paid_todt_3 => cp_isir_intrface_rec.nslds_pell_3_paid_todt_amt,
x_nslds_pell_remng_amt_3 => cp_isir_intrface_rec.nslds_pell_3_remng_amt,
x_nslds_pell_pc_schd_awd_us_3 => cp_isir_intrface_rec.nslds_pell_3_pc_scwd_use_amt,
x_nslds_pell_award_amt_3 => cp_isir_intrface_rec.nslds_pell_3_award_amt,
x_nslds_loan_seq_num_1 => cp_isir_intrface_rec.nslds_loan_1_seq_num,
x_nslds_loan_type_code_1 => cp_isir_intrface_rec.nslds_loan_1_type,
x_nslds_loan_chng_f_1 => cp_isir_intrface_rec.nslds_loan_1_chng_flag,
x_nslds_loan_prog_code_1 => cp_isir_intrface_rec.nslds_loan_1_prog_cd,
x_nslds_loan_net_amnt_1 => cp_isir_intrface_rec.nslds_loan_1_net_amt,
x_nslds_loan_cur_st_code_1 => cp_isir_intrface_rec.nslds_loan_1_cur_st_cd,
x_nslds_loan_cur_st_date_1 => cp_isir_intrface_rec.nslds_loan_1_cur_st_date,
x_nslds_loan_agg_pr_bal_1 => cp_isir_intrface_rec.nslds_loan_1_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_1 => cp_isir_intrface_rec.nslds_loan_1_out_pr_bal_date,
x_nslds_loan_begin_dt_1 => cp_isir_intrface_rec.nslds_loan_1_begin_date,
x_nslds_loan_end_dt_1 => cp_isir_intrface_rec.nslds_loan_1_end_date,
x_nslds_loan_ga_code_1 => cp_isir_intrface_rec.nslds_loan_1_ga_cd,
x_nslds_loan_cont_type_1 => cp_isir_intrface_rec.nslds_loan_1_cont_type,
x_nslds_loan_schol_code_1 => cp_isir_intrface_rec.nslds_loan_1_schol_cd,
x_nslds_loan_cont_code_1 => cp_isir_intrface_rec.nslds_loan_1_cont_cd,
x_nslds_loan_grade_lvl_1 => cp_isir_intrface_rec.nslds_loan_1_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_1 => cp_isir_intrface_rec.nslds_loan_1_x_unsbz_ln_type,
x_nslds_loan_capital_int_f_1 => cp_isir_intrface_rec.nslds_loan_1_captal_int_flag,
x_nslds_loan_seq_num_2 => cp_isir_intrface_rec.nslds_loan_2_seq_num,
x_nslds_loan_type_code_2 => cp_isir_intrface_rec.nslds_loan_2_type,
x_nslds_loan_chng_f_2 => cp_isir_intrface_rec.nslds_loan_2_chng_flag,
x_nslds_loan_prog_code_2 => cp_isir_intrface_rec.nslds_loan_2_prog_cd,
x_nslds_loan_net_amnt_2 => cp_isir_intrface_rec.nslds_loan_2_net_amt,
x_nslds_loan_cur_st_code_2 => cp_isir_intrface_rec.nslds_loan_2_cur_st_cd,
x_nslds_loan_cur_st_date_2 => cp_isir_intrface_rec.nslds_loan_2_cur_st_date,
x_nslds_loan_agg_pr_bal_2 => cp_isir_intrface_rec.nslds_loan_2_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_2 => cp_isir_intrface_rec.nslds_loan_2_out_pr_bal_date,
x_nslds_loan_begin_dt_2 => cp_isir_intrface_rec.nslds_loan_2_begin_date,
x_nslds_loan_end_dt_2 => cp_isir_intrface_rec.nslds_loan_2_end_date,
x_nslds_loan_ga_code_2 => cp_isir_intrface_rec.nslds_loan_2_ga_cd,
x_nslds_loan_cont_type_2 => cp_isir_intrface_rec.nslds_loan_2_cont_type,
x_nslds_loan_schol_code_2 => cp_isir_intrface_rec.nslds_loan_2_schol_cd,
x_nslds_loan_cont_code_2 => cp_isir_intrface_rec.nslds_loan_2_cont_cd,
x_nslds_loan_grade_lvl_2 => cp_isir_intrface_rec.nslds_loan_2_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_2 => cp_isir_intrface_rec.nslds_loan_2_x_unsbz_ln_type,
x_nslds_loan_capital_int_f_2 => cp_isir_intrface_rec.nslds_loan_2_captal_int_flag,
x_nslds_loan_seq_num_3 => cp_isir_intrface_rec.nslds_loan_3_seq_num,
x_nslds_loan_type_code_3 => cp_isir_intrface_rec.nslds_loan_3_type,
x_nslds_loan_chng_f_3 => cp_isir_intrface_rec.nslds_loan_3_chng_flag,
x_nslds_loan_prog_code_3 => cp_isir_intrface_rec.nslds_loan_3_prog_cd,
x_nslds_loan_net_amnt_3 => cp_isir_intrface_rec.nslds_loan_3_net_amt,
x_nslds_loan_cur_st_code_3 => cp_isir_intrface_rec.nslds_loan_3_cur_st_cd,
x_nslds_loan_cur_st_date_3 => cp_isir_intrface_rec.nslds_loan_3_cur_st_date,
x_nslds_loan_agg_pr_bal_3 => cp_isir_intrface_rec.nslds_loan_3_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_3 => cp_isir_intrface_rec.nslds_loan_3_out_pr_bal_date,
x_nslds_loan_begin_dt_3 => cp_isir_intrface_rec.nslds_loan_3_begin_date,
x_nslds_loan_end_dt_3 => cp_isir_intrface_rec.nslds_loan_3_end_date,
x_nslds_loan_ga_code_3 => cp_isir_intrface_rec.nslds_loan_3_ga_cd,
x_nslds_loan_cont_type_3 => cp_isir_intrface_rec.nslds_loan_3_cont_type,
x_nslds_loan_schol_code_3 => cp_isir_intrface_rec.nslds_loan_3_schol_cd,
x_nslds_loan_cont_code_3 => cp_isir_intrface_rec.nslds_loan_3_cont_cd,
x_nslds_loan_grade_lvl_3 => cp_isir_intrface_rec.nslds_loan_3_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_3 => cp_isir_intrface_rec.nslds_loan_3_x_unsbz_ln_type,
x_nslds_loan_capital_int_f_3 => cp_isir_intrface_rec.nslds_loan_3_captal_int_flag,
x_nslds_loan_seq_num_4 => cp_isir_intrface_rec.nslds_loan_4_seq_num,
x_nslds_loan_type_code_4 => cp_isir_intrface_rec.nslds_loan_4_type,
x_nslds_loan_chng_f_4 => cp_isir_intrface_rec.nslds_loan_4_chng_flag,
x_nslds_loan_prog_code_4 => cp_isir_intrface_rec.nslds_loan_4_prog_cd,
x_nslds_loan_net_amnt_4 => cp_isir_intrface_rec.nslds_loan_4_net_amt,
x_nslds_loan_cur_st_code_4 => cp_isir_intrface_rec.nslds_loan_4_cur_st_cd,
x_nslds_loan_cur_st_date_4 => cp_isir_intrface_rec.nslds_loan_4_cur_st_date,
x_nslds_loan_agg_pr_bal_4 => cp_isir_intrface_rec.nslds_loan_4_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_4 => cp_isir_intrface_rec.nslds_loan_4_out_pr_bal_date,
x_nslds_loan_begin_dt_4 => cp_isir_intrface_rec.nslds_loan_4_begin_date,
x_nslds_loan_end_dt_4 => cp_isir_intrface_rec.nslds_loan_4_end_date,
x_nslds_loan_ga_code_4 => cp_isir_intrface_rec.nslds_loan_4_ga_cd,
x_nslds_loan_cont_type_4 => cp_isir_intrface_rec.nslds_loan_4_cont_type,
x_nslds_loan_schol_code_4 => cp_isir_intrface_rec.nslds_loan_4_schol_cd,
x_nslds_loan_cont_code_4 => cp_isir_intrface_rec.nslds_loan_4_cont_cd,
x_nslds_loan_grade_lvl_4 => cp_isir_intrface_rec.nslds_loan_4_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_4 => cp_isir_intrface_rec.nslds_loan_4_x_unsbz_ln_type,
x_nslds_loan_capital_int_f_4 => cp_isir_intrface_rec.nslds_loan_4_captal_int_flag,
x_nslds_loan_seq_num_5 => cp_isir_intrface_rec.nslds_loan_5_seq_num,
x_nslds_loan_type_code_5 => cp_isir_intrface_rec.nslds_loan_5_type,
x_nslds_loan_chng_f_5 => cp_isir_intrface_rec.nslds_loan_5_chng_flag,
x_nslds_loan_prog_code_5 => cp_isir_intrface_rec.nslds_loan_5_prog_cd,
x_nslds_loan_net_amnt_5 => cp_isir_intrface_rec.nslds_loan_5_net_amt,
x_nslds_loan_cur_st_code_5 => cp_isir_intrface_rec.nslds_loan_5_cur_st_cd,
x_nslds_loan_cur_st_date_5 => cp_isir_intrface_rec.nslds_loan_5_cur_st_date,
x_nslds_loan_agg_pr_bal_5 => cp_isir_intrface_rec.nslds_loan_5_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_5 => cp_isir_intrface_rec.nslds_loan_5_out_pr_bal_date,
x_nslds_loan_begin_dt_5 => cp_isir_intrface_rec.nslds_loan_5_begin_date,
x_nslds_loan_end_dt_5 => cp_isir_intrface_rec.nslds_loan_5_end_date,
x_nslds_loan_ga_code_5 => cp_isir_intrface_rec.nslds_loan_5_ga_cd,
x_nslds_loan_cont_type_5 => cp_isir_intrface_rec.nslds_loan_5_cont_type,
x_nslds_loan_schol_code_5 => cp_isir_intrface_rec.nslds_loan_5_schol_cd,
x_nslds_loan_cont_code_5 => cp_isir_intrface_rec.nslds_loan_5_cont_cd,
x_nslds_loan_grade_lvl_5 => cp_isir_intrface_rec.nslds_loan_5_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_5 => cp_isir_intrface_rec.nslds_loan_5_x_unsbz_ln_type,
x_nslds_loan_capital_int_f_5 => cp_isir_intrface_rec.nslds_loan_5_captal_int_flag,
x_nslds_loan_seq_num_6 => cp_isir_intrface_rec.nslds_loan_6_seq_num,
x_nslds_loan_type_code_6 => cp_isir_intrface_rec.nslds_loan_6_type,
x_nslds_loan_chng_f_6 => cp_isir_intrface_rec.nslds_loan_6_chng_flag,
x_nslds_loan_prog_code_6 => cp_isir_intrface_rec.nslds_loan_6_prog_cd,
x_nslds_loan_net_amnt_6 => cp_isir_intrface_rec.nslds_loan_6_net_amt,
x_nslds_loan_cur_st_code_6 => cp_isir_intrface_rec.nslds_loan_6_cur_st_cd,
x_nslds_loan_cur_st_date_6 => cp_isir_intrface_rec.nslds_loan_6_cur_st_date,
x_nslds_loan_agg_pr_bal_6 => cp_isir_intrface_rec.nslds_loan_6_agg_pr_bal_amt,
x_nslds_loan_out_pr_bal_dt_6 => cp_isir_intrface_rec.nslds_loan_6_out_pr_bal_date,
x_nslds_loan_begin_dt_6 => cp_isir_intrface_rec.nslds_loan_6_begin_date,
x_nslds_loan_end_dt_6 => cp_isir_intrface_rec.nslds_loan_6_end_date,
x_nslds_loan_ga_code_6 => cp_isir_intrface_rec.nslds_loan_6_ga_cd,
x_nslds_loan_cont_type_6 => cp_isir_intrface_rec.nslds_loan_6_cont_type,
x_nslds_loan_schol_code_6 => cp_isir_intrface_rec.nslds_loan_6_schol_cd,
x_nslds_loan_cont_code_6 => cp_isir_intrface_rec.nslds_loan_6_cont_cd,
x_nslds_loan_grade_lvl_6 => cp_isir_intrface_rec.nslds_loan_6_grade_lvl_txt,
x_nslds_loan_xtr_unsbz_ln_f_6 => cp_isir_intrface_rec.nslds_loan_6_x_unsbz_ln_type,
x_nslds_loan_capital_int_f_6 => cp_isir_intrface_rec.nslds_loan_6_captal_int_flag,
x_nslds_loan_last_d_amt_1 => cp_isir_intrface_rec.nslds_loan_1_last_disb_amt,
x_nslds_loan_last_d_date_1 => cp_isir_intrface_rec.nslds_loan_1_last_disb_date,
x_nslds_loan_last_d_amt_2 => cp_isir_intrface_rec.nslds_loan_2_last_disb_amt,
x_nslds_loan_last_d_date_2 => cp_isir_intrface_rec.nslds_loan_2_last_disb_date,
x_nslds_loan_last_d_amt_3 => cp_isir_intrface_rec.nslds_loan_3_last_disb_amt,
x_nslds_loan_last_d_date_3 => cp_isir_intrface_rec.nslds_loan_3_last_disb_date,
x_nslds_loan_last_d_amt_4 => cp_isir_intrface_rec.nslds_loan_4_last_disb_amt,
x_nslds_loan_last_d_date_4 => cp_isir_intrface_rec.nslds_loan_4_last_disb_date,
x_nslds_loan_last_d_amt_5 => cp_isir_intrface_rec.nslds_loan_5_last_disb_amt,
x_nslds_loan_last_d_date_5 => cp_isir_intrface_rec.nslds_loan_5_last_disb_date,
x_nslds_loan_last_d_amt_6 => cp_isir_intrface_rec.nslds_loan_6_last_disb_amt,
x_nslds_loan_last_d_date_6 => cp_isir_intrface_rec.nslds_loan_6_last_disb_date,
x_dlp_master_prom_note_flag => cp_isir_intrface_rec.dlp_master_prom_note_type,
x_subsidized_loan_limit_type => cp_isir_intrface_rec.subsidized_loan_limit_type,
x_combined_loan_limit_type => cp_isir_intrface_rec.combined_loan_limit_type
);
log_debug_message(' Successfully Inserted NSLDS record. NSLDS ID : ' || p_out_nslds_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.insert_nslds_data_rec.statement','Inserted data into NSLDS data table. NSLDS ID : ' || p_out_nslds_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.insert_nslds_data_rec.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','igf_ap_matching_process_pkg.insert_nslds_data_rec');
END insert_nslds_data_rec;
IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW (
X_ROWID => lv_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'
);
PROCEDURE insert_fa_base_record( pn_person_id NUMBER,
pn_base_id OUT NOCOPY NUMBER)
IS
/*
|| Created By : rgangara
|| Created On : 17-AUG-2004
|| Purpose : Inserts a new FA base record for the person.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| ridas 14-Feb-2006 Bug #5021084. Removed trunc function from cursor SSN_CUR
|| rajagupt 6-Oct-2005 Bug#4068548 - added a new cursor ssn_cur
|| (reverse chronological order - newest change first)
*/
-- 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);
log_debug_message(' Beginning Insert of FA Base Record. Person ID ' || pn_person_id);
l_fed_verif_status := 'SELECTED';
l_fed_verif_status := 'NOTSELECTED';
igf_ap_fa_base_rec_pkg.insert_row(
x_Mode => 'R',
x_rowid => lv_rowid,
x_base_id => pn_base_id,
x_ci_cal_type => g_ci_cal_type,
x_person_id => pn_person_id,
x_ci_sequence_number => g_ci_sequence_number,
x_org_id => NULL,
x_coa_pending => NULL,
x_verification_process_run => 'N',
x_inst_verif_status_date => NULL,
x_manual_verif_flag => NULL,
x_fed_verif_status => l_fed_verif_status,
x_fed_verif_status_date => TRUNC(SYSDATE),
x_inst_verif_status => NULL,
x_nslds_eligible => g_isir_intrface_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 => 'Received-Valid', -- Bug 3169500
x_isir_status_date => TRUNC(SYSDATE),
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 => l_award_fmly_contr_type,
x_packaging_hold => NULL,
x_isir_locked_by => NULL,
x_adnl_unsub_loan_elig_flag => 'N',
x_lock_awd_flag => 'N',
x_lock_coa_flag => 'N'
);
log_debug_message(' Successfully inserted FA Base Record. Base ID: ' || pn_base_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.insert_fa_base_record.statement','Inserted FA Base Record. BASE ID : ' || pn_base_id || ', Person ID:' || pn_person_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.insert_fa_base_record.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','igf_ap_matching_process_pkg.insert_fa_base_record');
END insert_fa_base_record;
PROCEDURE update_prsn_match_rec_status(p_apm_id NUMBER,
p_rec_status VARCHAR2)
IS
/*
|| Created By : rgangara
|| Created On : 17-AUG-2004
|| Purpose : Generic procedure for Updating records in Person Match and Match Details table.
|| Generally record status is updated.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| (reverse chronological order - newest change first)
*/
-- Person match
CURSOR cur_person_match (cp_apm_id NUMBER) IS
SELECT apm.rowid row_id,
apm.*
FROM igf_ap_person_match_all apm
WHERE apm.apm_id = cp_apm_id
FOR UPDATE NOWAIT;
SELECT amd.rowid row_id,
amd.*
FROM igf_ap_match_details amd
WHERE amd.apm_id = cp_apm_id
FOR UPDATE NOWAIT;
log_debug_message(' Beginning update_prsn_match_rec_status proc');
igf_ap_person_match_pkg.update_row(
x_rowid => person_match_rec.row_id ,
x_apm_id => person_match_rec.apm_id ,
x_css_id => person_match_rec.css_id ,
x_si_id => person_match_rec.si_id ,
x_record_type => person_match_rec.record_type,
x_date_run => person_match_rec.date_run ,
x_ci_sequence_number => person_match_rec.ci_sequence_number,
x_ci_cal_type => person_match_rec.ci_cal_type ,
x_record_status => p_rec_status,
x_mode => 'R'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.update_prsn_match_rec_status.statement',' Updated Record Status for APM ID : ' || person_match_rec.apm_id);
igf_ap_match_details_pkg.update_row(
x_mode => 'R',
x_rowid => match_details_rec.row_id,
x_amd_id => match_details_rec.amd_id,
x_apm_id => match_details_rec.apm_id,
x_person_id => match_details_rec.person_id ,
x_ssn_match => match_details_rec.ssn_match ,
x_given_name_match => match_details_rec.given_name_match,
x_surname_match => match_details_rec.surname_match ,
x_dob_match => match_details_rec.dob_match ,
x_address_match => match_details_rec.address_match ,
x_city_match => match_details_rec.city_match ,
x_zip_match => match_details_rec.zip_match ,
x_match_score => match_details_rec.match_score ,
x_record_status => p_rec_status ,
x_ssn_txt => match_details_rec.ssn_txt , -- update
x_given_name_txt => match_details_rec.given_name_txt ,
x_sur_name_txt => match_details_rec.sur_name_txt ,
x_birth_date => match_details_rec.birth_date ,
x_address_txt => match_details_rec.address_txt ,
x_city_txt => match_details_rec.city_txt ,
x_zip_txt => match_details_rec.zip_txt ,
x_gender_txt => match_details_rec.gender_txt ,
x_email_id_txt => match_details_rec.email_id_txt ,
x_gender_match => match_details_rec.gender_match ,
x_email_id_match => match_details_rec.email_id_match
);
log_debug_message(' Updated Match Details record status to : ' || p_rec_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.update_prsn_match_rec_status.statement',' Updated Record Status for Person ID : ' || match_details_rec.person_id || ' AMD ID: ' || match_details_rec.amd_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.update_prsn_match_rec_status.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','igf_ap_matching_process_pkg.update_prsn_match_rec_status');
END update_prsn_match_rec_status;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT fed_verif_status
FROM igf_ap_fa_base_rec_all
WHERE base_id = pn_base_id;
l_parameter_list_t.delete;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_person_id;
l_parameter_list_t.delete;
|| Purpose : For Inserting new Primary Email Address if none exists Else Insert a new Email Address as Non Primary
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| museshad 29-Sep-2005 Bug 4291874.
|| If the Person record has an email id and if the ISIR
|| being imported has a different email id, do not add
|| the new email address to the person record and log a
|| message. If the email id in the ISIR matches with that
|| in the Person record, then don't log any message.
|| Implemented this. Note, this is applicable only for
|| the first ISIR getting imported. If an ISIR is already
|| imported and a new ISIR comes in with the above
|| scenario then a Notification event is raised.
|| (reverse chronological order - newest change first)
*/
CURSOR cur_chk_email_addr (cp_person_id hz_parties.party_id%TYPE)
IS
SELECT 'Y' is_email_exists, email_address
FROM hz_parties
WHERE party_id = cp_person_id
AND email_address IS NOT NULL;
SELECT object_version_number,
contact_point_id
FROM hz_contact_points
WHERE owner_table_id = cp_person_id
AND contact_point_type = cp_contact_point_type
AND NVL(primary_flag,'X') = cp_primary_flag;
log_debug_message(' Beginning create/update email address. Person ID : ' || p_person_id);
'Person id ' ||p_person_id|| ' does not have email. Inserting email address into Person record from ISIR');
PROCEDURE update_person_info (pn_isir_id igf_ap_isir_matched.isir_id%TYPE)
IS
/*
|| Created By : rgangara
|| Created On : 17-AUG-2004
|| Purpose : To identify changes to Person Demographic data by comparing OSS Person data with incoming ISIR data.
|| This would only be invoked when the Incoming ISIR Transaction No. is > Existing Max transaction No.
|| NOTE: This procedure does not change any existing demographic data but only sends a WF Notification.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| ridas 14-Feb-2006 Bug #5021084. Added SYSDATE condition in cursor ALT_PERSON_ID_CUR.
|| museshad 07-Oct-2005 Bug 4291874.
|| If the Person does not have email in hzparties
|| and if the ISIR has an email, then update the
|| Person record with the email. Note, a change
|| notification event is NOT raised for this.
|| (reverse chronological order - newest change first)
*/
CURSOR isir_cur (cp_isir_id igf_ap_isir_matched.isir_id%TYPE) IS
SELECT ssn_name_change_type,
first_name,
last_name,
current_ssn_txt,
s_email_address_txt,
perm_mail_address_txt,
perm_city_txt,
perm_state_txt,
perm_zip_cd,
birth_date,
ss_r_u_male_flag
FROM igf_ap_isir_ints
WHERE si_id = cp_isir_id;
SELECT person_number,
api_person_id,
birth_dt,
sex
FROM igs_pe_person
WHERE person_id = ln_person_id;
SELECT person_first_name,
person_last_name,
address1,
city,
postal_code,
state,
email_address
FROM hz_parties
WHERE party_id = ln_person_id;
SELECT rowid row_id,
altp.*
FROM igs_pe_alt_pers_id altp
WHERE altp.pe_person_id = ln_person_id
AND altp.api_person_id = ln_api_person_id
AND SYSDATE BETWEEN altp.start_dt and NVL(altp.end_dt, SYSDATE);
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = cp_pers_id_type ;
SELECT tag
FROM igf_lookups_view
WHERE lookup_type = cp_lkup_type
AND lookup_code = cp_lkup_cd
AND enabled_flag = 'Y';
log_debug_message(' Beginning Person Update info Proc. Person ID : ' || g_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.person_update_info.debug','Checking for any person data changes for person : ' || ln_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.person_update_info.debug','SSN Type Alternate Person ID record for the person is already End Dated. SSN No.: ' || person_rec.api_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.person_update_info.debug','Active Alternate Person ID record found for the person. SSN No.:' || person_rec.api_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.person_update_info.debug','Comparing OSS data with ISIR data to identify changes to demographic data');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.person_update_info.debug','Person Demographic data changed: ' || l_demo_data_changed);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.person_update_info.debug','Person Demographic data changed and hence send notification: ');
log_debug_message('Completed update_person_info Proc. Person data changed ? : ' || l_demo_data_changed);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.update_person_info.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','igf_ap_matching_process_pkg.update_person_info');
END update_person_info;
SELECT 'X'
FROM igf_sl_school_codes_v
WHERE system_id_type= cp_system_id_type
AND alternate_identifier = cp_fed_school;
SELECT p.party_number person_number,
p.person_first_name given_names
FROM hz_parties p
WHERE p.party_id = p_party_id
AND p.party_type = p_party_type;
SELECT isir_id
FROM igf_ap_ISIR_matched isir
WHERE base_id = ln_base_id
AND payment_isir = 'Y' ;
SELECT 'x'
FROM igf_ap_ISIR_matched isir, igf_ap_isir_corr_all corr
WHERE isir.isir_id = corr.isir_id
AND isir.base_id = ln_base_id;
igf_ap_outcorr_wf_pkg.insert_row (
x_rowid => lv_rowid,
x_person_number => l_cur_per_rec.person_number,
x_given_names => l_cur_per_rec.given_names,
x_transaction_number => l_transaction_num,
x_item_key => 'NEW',
x_ow_id => l_ow_id,
x_mode => 'R');
PROCEDURE delete_person_match_rec(p_si_id igf_ap_person_match_all.si_id%TYPE,
p_apm_id igf_ap_person_match_all.apm_id%TYPE)
IS
/*
|| Created By : rgangara
|| Created On : 06-AUG-2004
|| Purpose : Deletes records from Match details and then from Person match tables.
|| This proc can be called by passing either SI_ID or APM_ID. If apm_id is NULL then process on SI_ID.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- to get the apm ID based on si_id
CURSOR cur_prsn_match_siid(cp_si_id NUMBER) IS
SELECT pm.apm_id
FROM igf_ap_person_match pm
WHERE pm.si_id = cp_si_id;
SELECT pm.row_id, pm.apm_id
FROM igf_ap_person_match pm
WHERE pm.apm_id = cp_apm_id ;
SELECT amd.amd_id
FROM igf_ap_match_details amd
WHERE amd.apm_id = cp_apm_id;
log_debug_message(' Beginning delete_person_match_rec Proc for SI_ID: ' || p_si_id || '. APM_ID : ' || p_apm_id);
DELETE FROM igf_ap_match_details
WHERE amd_id = g_amd_id_tab(k);
log_debug_message(' Deleted Match Details records for APM ID : ' || l_apm_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.delete_person_match_rec.debug','Deleted Match Details records for APM ID: ' || l_apm_id);
igf_ap_person_match_pkg.delete_row(prsn_match_rec.row_id);
log_debug_message(' Deleted Person Match record for APM ID : ' || l_apm_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.delete_person_match_rec.debug','Deleted Person Match record. APM ID: ' || prsn_match_rec.apm_id);
log_debug_message(' Successfully deleted Person match and Match details data for APM ID: ' || l_apm_id);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.delete_person_match_rec.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','igf_ap_matching_process_pkg.delete_person_match_rec' );
END delete_person_match_rec;
SELECT inq_cal_type
FROM igs_ad_cal_conf;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = cp_sys_type
AND closed_ind = 'N' ;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.create_admission_rec.debug','Before Inserting into igr_inquiry');
igr_inquiry_pkg.insert_row(
X_MODE => ''R'',
X_ROWID => x_rowid,
X_PERSON_ID => :1,
X_ENQUIRY_APPL_NUMBER => l_enquiry_appl_number,
X_SALES_LEAD_ID => l_sales_lead_id,
X_ACAD_CAL_TYPE => :2,
X_ACAD_CI_SEQUENCE_NUMBER => :3,
X_ADM_CAL_TYPE => :4,
X_ADM_CI_SEQUENCE_NUMBER => :5,
X_s_ENQUIRY_STATUS => l_enquiry_status,
X_ENQUIRY_DT => TRUNC(SYSDATE),
X_INQUIRY_METHOD_CODE => :6,
X_REGISTERING_PERSON_ID => NULL,
X_OVERRIDE_PROCESS_IND => ''N'',
X_INDICATED_MAILING_DT => NULL,
X_LAST_PROCESS_DT => NULL,
X_COMMENTS => NULL,
X_ORG_ID => igs_ge_gen_003.get_org_id,
X_INQ_ENTRY_LEVEL_ID => NULL,
X_EDU_GOAL_ID => NULL,
X_PARTY_ID => NULL,
X_HOW_KNOWUS_ID => NULL,
X_WHO_INFLUENCED_ID => NULL,
X_SOURCE_PROMOTION_ID => NULL,
X_PERSON_TYPE_CODE => NULL,
X_FUNNEL_STATUS => 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_ret_status => :7,
x_msg_data => :8,
x_msg_count => :9,
x_action => ''Import'',
x_enabled_flag => ''Y'',
x_pkg_reduct_ind => ''Y''
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.create_admission_rec.debug','After Create Inquiry. Before inserting into igs_pe_typ_instances_pkg');
igs_pe_typ_instances_pkg.insert_row(
X_ROWID => l_rowid,
x_PERSON_ID => p_person_id,
x_COURSE_CD => NULL,
x_TYPE_INSTANCE_ID => ln_typ_id,
x_PERSON_TYPE_CODE => l_person_type,
x_CC_VERSION_NUMBER => NULL,
x_FUNNEL_STATUS => NULL,
x_ADMISSION_APPL_NUMBER => NULL,
x_NOMINATED_COURSE_CD => NULL,
x_NCC_VERSION_NUMBER => NULL,
x_SEQUENCE_NUMBER => NULL,
x_START_DATE => TRUNC(SYSDATE),
x_END_DATE => NULL,
x_CREATE_METHOD => 'CREATE_ENQ_APPL_INSTANCE',
x_ENDED_BY => NULL,
x_END_METHOD => NULL,
X_MODE => 'R',
X_ORG_ID => igs_ge_gen_003.get_org_id
);
log_debug_message('Inserted record into igs_pe_typ_instances. ID : ' || ln_typ_id);
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = cp_pers_id_type ;
SELECT tag
FROM igf_lookups_view
WHERE lookup_type = p_lkup_type
AND lookup_code = p_lkup_code
AND enabled_flag = 'Y';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.create_person_record.debug','Inserting Into Person table.');
igs_pe_person_pkg.insert_row(
x_MSG_COUNT => ln_msg_count,
x_MSG_DATA => lv_msg_data,
x_RETURN_STATUS => lv_return_status,
x_ROWID => lv_row_id,
x_PERSON_ID => pn_person_id,
x_PERSON_NUMBER => ln_person_number,
x_SURNAME => INITCAP(g_isir_intrface_rec.last_name),
x_MIDDLE_NAME => g_isir_intrface_rec.middle_initial_txt,
x_GIVEN_NAMES => INITCAP(g_isir_intrface_rec.first_name),
x_SEX => UPPER(lv_sex),
x_TITLE => NULL,
x_STAFF_MEMBER_IND => NULL,
x_DECEASED_IND => NULL,
x_SUFFIX => NULL,
x_PRE_NAME_ADJUNCT => NULL,
x_ARCHIVE_EXCLUSION_IND => NULL,
x_ARCHIVE_DT => NULL,
x_PURGE_EXCLUSION_IND => NULL,
x_PURGE_DT => NULL,
x_DECEASED_DATE => NULL,
x_PROOF_OF_INS => NULL,
x_PROOF_OF_IMMU => NULL,
x_BIRTH_DT => TRUNC(g_isir_intrface_rec.birth_date),
x_SALUTATION => NULL,
x_ORACLE_USERNAME => NULL,
x_PREFERRED_GIVEN_NAME => NULL, -- modified to NULL for Reg Updates 0607 (bug 5086053)
x_EMAIL_ADDR => g_isir_intrface_rec.s_email_address_txt,
x_LEVEL_OF_QUAL_ID => NULL,
x_MILITARY_SERVICE_REG => NULL,
x_VETERAN => NULL,
x_hz_parties_ovn => l_object_version_number,
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_ATTRIBUTE21 => NULL,
x_ATTRIBUTE22 => NULL,
x_ATTRIBUTE23 => NULL,
x_ATTRIBUTE24 => NULL,
x_PERSON_ID_TYPE => lv_person_id_type,
x_API_PERSON_ID => format_SSN(g_isir_intrface_rec.current_ssn_txt)
);
pd_last_update_date DATE;
l_last_update_date DATE;
l_site_use_last_update_date DATE;
l_profile_last_update_date DATE;
igs_pe_person_addr_pkg.insert_row(
P_ACTION => 'R',
P_ROWID => lv_row_id,
P_LOCATION_ID => lv_location_id,
P_START_DT => NULL,
P_END_DT => NULL,
P_COUNTRY => 'US',
P_ADDRESS_STYLE => NULL,
P_ADDR_LINE_1 => INITCAP( g_isir_intrface_rec.perm_mail_address_txt),
P_ADDR_LINE_2 => NULL,
P_ADDR_LINE_3 => NULL,
P_ADDR_LINE_4 => NULL,
P_DATE_LAST_VERIFIED => NULL,
P_CORRESPONDENCE => NULL,
P_CITY => INITCAP(g_isir_intrface_rec.perm_city_txt),
P_STATE => g_isir_intrface_rec.perm_state_txt,
P_PROVINCE => NULL,
P_COUNTY => NULL,
P_POSTAL_CODE => g_isir_intrface_rec.perm_zip_cd,
P_ADDRESS_LINES_PHONETIC => NULL,
P_DELIVERY_POINT_CODE => NULL,
P_OTHER_DETAILS_1 => NULL,
P_OTHER_DETAILS_2 => NULL,
P_OTHER_DETAILS_3 => NULL,
L_RETURN_STATUS => lv_return_status,
L_MSG_DATA => lv_msg_data,
P_PARTY_ID => pn_person_id,
P_PARTY_SITE_ID => ln_party_site_id,
P_PARTY_TYPE => NULL,
P_LAST_UPDATE_DATE => pd_last_update_date,
P_PARTY_SITE_OVN => l_party_site_ovn,
P_LOCATION_OVN => l_location_ovn,
P_STATUS => 'A'
);
p_action => 'INSERT',
p_rowid => l_rowid,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => ln_party_site_id,
p_site_use_type => l_party_site_use,
p_status => 'A',
p_return_status => l_return_status ,
p_msg_data => l_msg_data,
p_last_update_date => l_last_update_date,
p_site_use_last_update_date => l_site_use_last_update_date,
p_profile_last_update_date => l_profile_last_update_date,
p_hz_party_site_use_ovn => lv_object_version_number
);
|| Since the record is matched, update the status to MATCHED and create records in other tables.
|| NOTE: This procedure gets executed only when the Pell match type is 'U'
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| museshad 12-Apr-2006 Bug 5096864. Added code to update the FA Base record details
|| with the newly created ISIR info.
*/
CURSOR chk_isir_exist (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
IS
SELECT 'x'
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id AND
ROWNUM = 1;
SELECT fa.*
FROM igf_ap_fa_base_rec fa
WHERE fa.base_id = cp_base_id;
insert_fa_base_record( pn_person_id => p_person_id, pn_base_id => lv_base_id); -- OUT parameter
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.auto_fa_rec.debug','ISIR does not exist for base_id= ' ||lv_base_id|| '. ISIR matched record will be inserted.');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.auto_fa_rec.debug','Calling update_fa_base_rec() to update FA Base record with the ISIR details.');
update_fa_base_rec( p_fabase_rec => lv_cur_fabase_rec,
p_isir_verification_flag => g_isir_intrface_rec.verification_flag);
insert_isir_matched_rec(cp_isir_int_rec => g_isir_intrface_rec,
p_payment_isir => l_pymt_isir_flag,
p_active_isir => l_pymt_isir_flag, -- In this case, Active Flag same as Payment Flag
p_base_id => lv_base_id,
p_out_isir_id => lv_isir_id
);
insert_nslds_data_rec(cp_isir_intrface_rec => g_isir_intrface_rec,
p_isir_id => lv_isir_id,
p_base_id => lv_base_id,
p_out_nslds_id => lv_nslds_id
);
IGF_AP_BATCH_VER_PRC_PKG.update_process_status(
p_base_id => lv_base_id,
p_fed_verif_status => NULL);
delete_person_match_rec(p_si_id => NULL, p_apm_id => p_apm_id);
update_isir_int_record(g_isir_intrface_rec.si_id, 'MATCHED', g_match_code); -- update ISIR int rec status
g_matched_rec := g_matched_rec + 1; -- update count
|| This procedure only updates record status to REVIEW and does not create any records in any other table.
|| NOTE: This procedure gets executed only when the Pell match type is 'U'
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.rvw_fa_rec.debug','Review FA Rec processing for APM ID: ' || p_apm_id);
update_prsn_match_rec_status(p_apm_id => p_apm_id,
p_rec_status => 'REVIEW');
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'REVIEW',
p_match_code => g_match_code);
g_review_count := g_review_count + 1 ; -- update count
|| This procedure only updates record status to UNMATCHED and hence does not create any records in any other table.
|| NOTE: This procedure gets executed only when the Pell match type is 'U'
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
lv_person_id igf_ap_person_v.person_id%TYPE ;
); -- igs_pe_person_pkg.insert_row();
create_person_addr_record(lv_person_id); -- igs_pe_addr_pkg.insert_row();
insert_fa_base_record( pn_person_id => lv_person_id, pn_base_id => lv_base_id); -- OUT parameter
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.unmatched_rec.debug','Before Inserting ISIR Matched record ');
insert_isir_matched_rec(cp_isir_int_rec => g_isir_intrface_rec,
p_payment_isir => l_pymt_isir_flag,
p_active_isir => l_pymt_isir_flag, -- In this case, Active Flag same as Payment Flag
p_base_id => lv_base_id,
p_out_isir_id => lv_isir_id -- OUT parameter
);
insert_nslds_data_rec(cp_isir_intrface_rec => g_isir_intrface_rec,
p_isir_id => lv_isir_id,
p_base_id => lv_base_id,
p_out_nslds_id => lv_nslds_id -- OUT parameter
) ;
IGF_AP_BATCH_VER_PRC_PKG.update_process_status(
p_base_id => lv_base_id,
p_fed_verif_status => NULL);
delete_person_match_rec(p_si_id => NULL, p_apm_id => p_apm_id);
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'MATCHED',
p_match_code => g_match_code);
update_prsn_match_rec_status(p_apm_id => p_apm_id,
p_rec_status => 'UNMATCHED');
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'UNMATCHED',
p_match_code => g_match_code);
|| Purpose : Matches attributes as per record match setup and inserts a record in match details table
after deriving the total score.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR cur_lookups(cp_lkup_type VARCHAR2, cp_lkup_cd VARCHAR2) IS
SELECT tag
FROM igf_lookups_view
WHERE lookup_type = cp_lkup_type
AND lookup_code = cp_lkup_cd
AND enabled_flag = 'Y';
SELECT ad.rowid row_id, ad.*
FROM igf_ap_match_details ad
WHERE apm_id = cp_apm_id
AND person_id = cp_person_id;
log_debug_message(' INSERTING Match Details record .... : ');
igf_ap_match_details_pkg.insert_row(
x_mode => 'R',
x_rowid => chk_match_dtls_exists_rec.row_id,
x_amd_id => chk_match_dtls_exists_rec.amd_id,
x_apm_id => p_apm_id,
x_person_id => p_person_id ,
x_ssn_match => l_ssn_match ,
x_given_name_match => l_given_name_match,
x_surname_match => l_surname_match ,
x_dob_match => l_dob_match ,
x_address_match => l_address_match ,
x_city_match => l_city_match ,
x_zip_match => l_zip_match ,
x_match_score => l_match_score ,
x_record_status => g_isir_intrface_rec.record_status,
x_ssn_txt => p_match_dtls_rec.ssn_txt ,
x_given_name_txt => p_match_dtls_rec.given_name_txt ,
x_sur_name_txt => p_match_dtls_rec.sur_name_txt ,
x_birth_date => p_match_dtls_rec.birth_date ,
x_address_txt => p_match_dtls_rec.address_txt ,
x_city_txt => p_match_dtls_rec.city_txt ,
x_zip_txt => p_match_dtls_rec.zip_txt ,
x_gender_txt => p_match_dtls_rec.gender_txt ,
x_email_id_txt => p_match_dtls_rec.email_id_txt ,
x_gender_match => l_gender_match ,
x_email_id_match => l_email_id_match
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.calculate_match_score.debug','Inserted match details record. AMD_ID: ' || chk_match_dtls_exists_rec.amd_id);
log_debug_message(' Inserted Match Details record. AMD ID: ' || chk_match_dtls_exists_rec.amd_id || ' Person ID: ' || p_person_id);
igf_ap_match_details_pkg.update_row(
x_mode => 'R',
x_rowid => chk_match_dtls_exists_rec.row_id ,
x_amd_id => chk_match_dtls_exists_rec.amd_id ,
x_apm_id => chk_match_dtls_exists_rec.apm_id ,
x_person_id => chk_match_dtls_exists_rec.person_id ,
x_ssn_match => l_ssn_match , -- update
x_given_name_match => chk_match_dtls_exists_rec.given_name_match,
x_surname_match => chk_match_dtls_exists_rec.surname_match ,
x_dob_match => chk_match_dtls_exists_rec.dob_match ,
x_address_match => chk_match_dtls_exists_rec.address_match ,
x_city_match => chk_match_dtls_exists_rec.city_match ,
x_zip_match => chk_match_dtls_exists_rec.zip_match ,
x_match_score => (chk_match_dtls_exists_rec.match_score - chk_match_dtls_exists_rec.ssn_match + l_ssn_match),
x_record_status => chk_match_dtls_exists_rec.record_status ,
x_ssn_txt => p_match_dtls_rec.ssn_txt , -- update
x_given_name_txt => chk_match_dtls_exists_rec.given_name_txt ,
x_sur_name_txt => chk_match_dtls_exists_rec.sur_name_txt ,
x_birth_date => chk_match_dtls_exists_rec.birth_date ,
x_address_txt => chk_match_dtls_exists_rec.address_txt ,
x_city_txt => chk_match_dtls_exists_rec.city_txt ,
x_zip_txt => chk_match_dtls_exists_rec.zip_txt ,
x_gender_txt => chk_match_dtls_exists_rec.gender_txt ,
x_email_id_txt => chk_match_dtls_exists_rec.email_id_txt ,
x_gender_match => chk_match_dtls_exists_rec.gender_match ,
x_email_id_match => chk_match_dtls_exists_rec.email_id_match
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.calculate_match_score.debug','Updated match details record. APM ID: ' || p_apm_id || ' AMD ID: ' || chk_match_dtls_exists_rec.amd_id);
|| and inserts the matched person records and attribute values into match details table.
|| This procedure gets executed only when the pell match type is 'U' (unidentified isir)
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- table definition
TYPE RecTab IS TABLE OF VARCHAR2(30);
SELECT ssn_txt
FROM igf_ap_match_details ad
WHERE apm_id = p_apm_id
AND person_id = p_person_id;
SELECT rec_type,
person_id,
prsn_ssn,
firstname,
lastname,
address,
city,
postal_code,
email_address,
date_of_birth,
gender
BULK COLLECT INTO
t_rec_tab,
t_pid_tab,
t_prsn_ssn,
t_first_name,
t_last_name,
t_address,
t_city,
t_postal_code,
t_email_address,
t_dob_tab,
t_gender
FROM
(
-- SSN matching records
SELECT 'OSS' rec_type,
hz.party_id person_id,
api.api_person_id_uf prsn_ssn, --Unformatted SSN value
hz.person_first_name firstname,
hz.person_last_name lastname,
hz.address1 address,
hz.city city,
hz.postal_code postal_code,
hz.email_address email_address,
hp.date_of_birth date_of_birth,
hp.gender gender
FROM ( SELECT apii.pe_person_id, apii.api_person_id_uf
FROM igs_pe_alt_pers_id apii, igs_pe_person_id_typ pit
WHERE apii.person_id_type = pit.person_id_type
AND pit.s_person_id_type = 'SSN'
AND SYSDATE BETWEEN apii.start_dt AND NVL (apii.end_dt, SYSDATE)) api,
hz_parties hz,
hz_person_profiles hp
WHERE hz.party_id = api.pe_person_id(+)
AND hz.party_id = hp.party_id
AND hp.effective_end_date IS NULL
AND (api.api_person_id_uf = lv_ssn
-- First Name
OR (UPPER(hz.person_first_name) = UPPER(lv_fname) AND l_fname_exact_match = 'Y')
OR (UPPER(hz.person_first_name) LIKE UPPER(lv_fname) AND l_fname_exact_match = 'N')
-- Last Name
OR (UPPER(hz.person_last_name) = UPPER(lv_lname) AND l_lname_exact_match = 'Y')
OR (UPPER(hz.person_last_name) LIKE UPPER(lv_lname) AND l_lname_exact_match = 'N')
)
UNION
--Source of SSN from HRMS
SELECT 'HRM' rec_type,
ppf.party_id person_id, -- party id maps to HZ_parties.party_id
remove_spl_chr(ppf.national_identifier) prsn_ssn,
hz.person_first_name firstname,
hz.person_last_name lastname,
hz.address1 address,
hz.city city,
hz.postal_code postal_code,
hz.email_address email_address,
hp.date_of_birth date_of_birth,
hp.gender gender
FROM per_all_people_f ppf,
per_business_groups_perf pbg,
per_person_types ppt,
hz_parties hz,
hz_person_profiles hp
WHERE IGS_EN_GEN_001.Check_HRMS_Installed = 'Y'
AND pbg.legislation_code = 'US'
AND ppt.system_person_type = 'EMP'
AND ppt.person_type_id = ppf.person_type_id
AND pbg.business_group_id = ppf.business_group_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.party_id = hz.party_id
AND hz.party_id = hp.party_id
AND hp.effective_end_date IS NULL
AND remove_spl_chr(ppf.national_identifier) = lv_ssn
) v_dataset ORDER BY 2 ;
SELECT *
FROM igf_ap_person_match
WHERE si_id = cp_si_id;
SELECT person_id,
match_score
FROM igf_ap_match_details
WHERE apm_id = cp_apm_id
ORDER BY match_score DESC;
SELECT base_id
FROM igf_ap_fa_con_v
WHERE person_id = p_person_id
AND ci_cal_type = p_cal_type
AND ci_sequence_number = p_seq_num ;
SELECT 'x'
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id AND
ROWNUM = 1;
igf_ap_person_match_pkg.insert_row(
x_rowid => lv_rowid ,
x_apm_id => ln_apm_id,
x_css_id => NULL,
x_si_id => g_isir_intrface_rec.si_id ,
x_record_type => 'ISIR' ,
x_date_run => TRUNC(SYSDATE),
x_ci_sequence_number => g_ci_sequence_number ,
x_ci_cal_type => g_ci_cal_type ,
x_record_status => 'NEW' ,
x_mode => 'R');
log_debug_message(' Inserted New Person Match record. APM ID: ' || ln_apm_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.perform_record_matching.debug','Inserted record into Person Match table with APM_ID: ' || ln_apm_id);
igf_ap_person_match_pkg.update_row(
x_rowid => person_match_rec.row_id ,
x_apm_id => person_match_rec.apm_id,
x_css_id => person_match_rec.css_id,
x_si_id => person_match_rec.si_id ,
x_record_type => person_match_rec.record_type,
x_date_run => TRUNC(SYSDATE),
x_ci_sequence_number => person_match_rec.ci_sequence_number,
x_ci_cal_type => person_match_rec.ci_cal_type ,
x_record_status => 'NEW' ,
x_mode => 'R');
log_debug_message(' Updated Person Match record. APM ID: ' || ln_apm_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.perform_record_matching.debug','Updated record in Person Match table with APM_ID: ' || ln_apm_id);
update_prsn_match_rec_status(p_apm_id => ln_apm_id,
p_rec_status => 'UNMATCHED');
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'UNMATCHED',
p_match_code => g_match_code);
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND payment_isir = 'Y';
SELECT transaction_num
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND system_record_type = 'CORRECTION';
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND transaction_num = cp_trans_num
AND system_record_type <> 'CORRECTION';
SELECT isir.*
FROM igf_ap_isir_matched isir
WHERE isir_id = ln_isir_id ;
SELECT row_id, isir_id
FROM igf_ap_isir_matched
WHERE base_id = cp_base_id
AND NVL(system_record_type,'*') = 'CORRECTION';
SELECT corr.*,
sar.sar_field_name column_name
FROM igf_ap_batch_aw_map map,
igf_ap_ISIR_corr corr,
igf_fc_sar_cd_mst sar,
igf_ap_isir_matched isir
WHERE map.ci_cal_type = g_ci_cal_type
AND map.ci_sequence_number = g_ci_sequence_number
AND corr.isir_id = isir.isir_id
AND isir.base_id = cp_base_id
AND corr.ci_cal_type = map.ci_cal_type
AND corr.ci_sequence_number= map.ci_sequence_number
AND corr.correction_status <> cp_corr_stat
AND sar.sys_award_year = map.sys_award_year
AND sar.sar_field_number = corr.sar_field_number ;
SELECT corr.*
FROM igf_ap_ISIR_corr corr
WHERE corr.isir_id = cp_isir_id ;
lv_corr_tab(n).column_name := 'SELECTIVE_SERVICE_REG';
lv_corr_tab(n).column_value := g_isir_intrface_rec.selective_service_reg_flag ;
lv_corr_tab(n).column_name := 'SELECT_SERVICE_MATCH_FLAG';
lv_corr_tab(n).column_value := g_isir_intrface_rec.select_service_match_type ;
lv_corr_tab(n).column_name := 'SELECT_SERVICE_REG_FLAG';
lv_corr_tab(n).column_value := g_isir_intrface_rec.select_service_reg_type ;
igf_ap_ISIR_corr_pkg.update_row (
x_rowid => isir_corr_rec.row_id,
x_ISIRc_id => isir_corr_rec.ISIRc_id,
x_ISIR_id => NVL(p_new_isir_id,isir_corr_rec.isir_id),
x_ci_sequence_number => isir_corr_rec.ci_sequence_number,
x_ci_cal_type => isir_corr_rec.ci_cal_type,
x_sar_field_number => isir_corr_rec.sar_field_number,
x_original_value => isir_corr_rec.original_value,
x_batch_id => isir_corr_rec.batch_id,
x_corrected_value => isir_corr_rec.corrected_value,
x_correction_status => 'ACKNOWLEDGED',
x_mode => 'R');
igf_ap_ISIR_corr_pkg.update_row (
x_rowid => isir_corr_rec.row_id,
x_ISIRc_id => isir_corr_rec.ISIRc_id,
x_ISIR_id => NVL(p_new_isir_id,isir_corr_rec.isir_id),
x_ci_sequence_number => isir_corr_rec.ci_sequence_number,
x_ci_cal_type => isir_corr_rec.ci_cal_type,
x_sar_field_number => isir_corr_rec.sar_field_number,
x_original_value => isir_corr_rec.original_value,
x_batch_id => isir_corr_rec.batch_id,
x_corrected_value => isir_corr_rec.corrected_value,
x_correction_status => 'READY',
x_mode => 'R');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.validate_correction_school.debug','Corrections updated...');
igf_ap_ISIR_corr_pkg.update_row (
x_rowid => l_cur_isir_corr_pymnt.row_id,
x_ISIRc_id => l_cur_isir_corr_pymnt.ISIRc_id,
x_ISIR_id => p_new_isir_id,
x_ci_sequence_number => l_cur_isir_corr_pymnt.ci_sequence_number,
x_ci_cal_type => l_cur_isir_corr_pymnt.ci_cal_type,
x_sar_field_number => l_cur_isir_corr_pymnt.sar_field_number,
x_original_value => l_cur_isir_corr_pymnt.original_value,
x_batch_id => l_cur_isir_corr_pymnt.batch_id,
x_corrected_value => l_cur_isir_corr_pymnt.corrected_value,
x_correction_status => l_cur_isir_corr_pymnt.correction_status,
x_mode => 'R');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.process_corrections.debug','Updated ISIR Information for Correction Items to New Payment ISIR');
igf_ap_isir_matched_pkg.delete_row(l_cor_isir.row_id); -- delete the correction type isir.
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.process_corrections.debug','Deleted existing Correction ISIR ID: ' || l_cor_isir.isir_id);
update_isir_matched_rec(p_isir_matched_record => cur_new_isir_rec,
p_payment_isir => cur_new_isir_rec.payment_isir,
p_active_isir => 'Y');
|| This process Inserts a Non Payment ISIR, NSLDS data, TODO Processing.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
*/
lv_isir_id NUMBER;
insert_isir_matched_rec(cp_isir_int_rec => g_isir_intrface_rec,
p_payment_isir => 'N',
p_active_isir => 'N',
p_base_id => g_base_id,
p_out_isir_id => lv_isir_id -- OUT parameter returns value of isir id.
);
insert_nslds_data_rec(cp_isir_intrface_rec => g_isir_intrface_rec,
p_isir_id => lv_isir_id,
p_base_id => g_base_id,
p_out_nslds_id => l_nslds_id
);
IGF_AP_BATCH_VER_PRC_PKG.update_process_status(
p_base_id => g_base_id,
p_fed_verif_status => NULL);
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'MATCHED',
p_match_code => NULL);
SELECT fa.*
FROM igf_ap_fa_base_rec fa
WHERE fa.base_id = pn_base_id FOR UPDATE NOWAIT ;
SELECT im.*
FROM igf_ap_isir_matched im
WHERE im.base_id = pn_base_id
AND im.payment_isir = 'Y';
SELECT party_number person_number
FROM hz_parties
WHERE party_id = ln_person_id;
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND active_isir = 'Y';
SELECT
'X'
FROM
IGF_SL_LOR_ALL LOR, IGF_SL_LOANS_ALL LOAN,
IGF_AW_AWARD_ALL AWD, igf_ap_isir_matched_all m
WHERE
m.base_id = awd.base_id and
awd.award_id = loan.award_id and
loan.loan_id = lor.loan_id and
m.base_id = cp_base_id;
insert_isir_matched_rec(cp_isir_int_rec => g_isir_intrface_rec,
p_payment_isir => lv_payment_isir,
p_active_isir => 'N', -- Non active isir. May get updated based on correction processing
p_base_id => g_base_id,
p_out_isir_id => lv_isir_id -- OUT parameter returns value of isir id.
);
insert_nslds_data_rec(cp_isir_intrface_rec => g_isir_intrface_rec,
p_isir_id => lv_isir_id,
p_base_id => g_base_id,
p_out_nslds_id => lv_nslds_id); -- OUT parameter returning ID.
update_fa_base_rec(p_fabase_rec => lv_cur_fabase_rec,
p_isir_verification_flag => g_isir_intrface_rec.verification_flag);
IGF_AP_BATCH_VER_PRC_PKG.update_process_status(
p_base_id => g_base_id,
p_fed_verif_status => NULL);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.process_new_isir_rec.debug','Incoming Transaction No. > Existing Max Trans and hence Invoking Update Person Info Procedure');
update_person_info(g_isir_intrface_rec.si_id);
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'MATCHED',
p_match_code => NULL);
g_matched_rec := g_matched_rec + 1; -- update matched rec count
SELECT person_id
FROM igf_ap_fa_base_rec
WHERE base_id = pn_base_id;
delete_person_match_rec(p_si_id => g_isir_intrface_rec.si_id,
p_apm_id => NULL);
g_dup_rec := NVL(g_dup_rec ,0)+ 1; -- update Duplicate rec count
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'MATCHED',
p_match_code => NULL);
update_isir_int_record (p_si_id => g_isir_intrface_rec.si_id,
p_isir_rec_status => 'REVIEW',
p_match_code => NULL);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_matching_process_pkg.process_correction_isir.statement','Updated ISIR Interface record status to REVIEW');
SELECT MAX(iim.transaction_num) max_id,
iim.base_id
FROM igf_ap_isir_matched iim
WHERE iim.original_ssn = cp_original_ssn
AND iim.orig_name_id = cp_orig_name_id
AND iim.system_record_type = cp_sys_rec_type
AND iim.batch_year = cp_batch_year
GROUP BY iim.base_id ;
SELECT 1
FROM igf_ap_isir_matched iim
WHERE iim.original_ssn = cp_original_ssn
AND iim.orig_name_id = cp_orig_name_id
AND iim.system_record_type = cp_sys_rec_type
AND iim.batch_year = cp_batch_year
AND iim.transaction_num = cp_transaction_num
AND rownum = 1;
SELECT isir_locked_by
FROM igf_ap_fa_base_rec
WHERE base_id = pn_base_id;
SELECT transaction_num
FROM igf_ap_isir_matched
WHERE base_id = pn_base_id
AND payment_isir = 'Y';
g_bad_rec := g_bad_rec + 1; -- update bad rec count
|| Purpose : Does the matching process and updates isir interface, isir matched table, base record table etc.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
|| tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
SELECT ci_cal_type,ci_sequence_number
FROM igf_ap_batch_aw_map
WHERE batch_year = cp_batch_yr;
SELECT iia.*
FROM igf_ap_isir_ints iia
WHERE si_id = cp_si_id;
SELECT iia.*
FROM igf_ap_isir_ints iia
WHERE parent_req_id = cp_parent_req_id
AND sub_req_num = cp_sub_req_num
ORDER BY si_id;
SELECT NVL(format_mask,'999999999')
FROM igs_pe_person_id_typ
WHERE s_person_id_type = 'SSN';
SELECT ifb.*
FROM igf_ap_fa_base_rec ifb,
igf_ap_batch_aw_map ibm
WHERE ifb.person_id = pn_person_id
AND ibm.ci_sequence_number = ifb.ci_sequence_number
AND ibm.ci_cal_type = ifb.ci_cal_type
AND ibm.batch_year = ln_batch_year;
SELECT iii.si_id,
iii.last_name,
iii.birth_date,
iii.batch_year_num,
iii.record_status
FROM igf_ap_isir_ints iii,
igf_ap_batch_aw_map ibm
WHERE original_ssn_txt = pn_ssn
AND iii.last_name = pv_last_name
AND iii.birth_date = pd_date_of_birth
AND ibm.ci_sequence_number = pn_ci_sequence_number
AND ibm.ci_cal_type = pv_ci_cal_type
AND iii.batch_year_num = ibm.batch_year
AND (iii.record_status = 'UNMATCHED' OR iii.record_status = 'NEW');
update_isir_intrface( cur_isir_intrface_rec.si_id, 'MATCHED');
SELECT iii.*
FROM igf_ap_isir_ints iii
WHERE iii.si_id = pn_si_id;
insert_isir_matched_rec(cp_isir_int_rec => cur_isir_intrface_rec,
p_payment_isir => 'N',
p_active_isir => 'N',
p_base_id => pn_base_id,
p_out_isir_id => pn_isir_id -- OUT parameter returns isir id
) ;
PROCEDURE update_isir_intrface(pn_si_id NUMBER,
pv_record_status VARCHAR2)
IS
/*
|| Created By : brajendr
|| Created On : 24-NOV-2000
|| Purpose : Update the record status to 'Matched / Unmatched' for all the successful/ non successfull persons.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| masehgal 15-feb-2002 # 2216956 FACR007
|| Addded Verif_track_flag
|| (reverse chronological order - newest change first)
*/
CURSOR cur_isir_intrface ( pn_si_id NUMBER) IS
SELECT rowid row_id, iii.si_id
FROM igf_ap_isir_ints_all iii
WHERE si_id = pn_si_id FOR UPDATE NOWAIT ;
UPDATE igf_ap_isir_ints_all
SET record_status = pv_record_status
WHERE si_id = cur_isir_intrface_rec.si_id;
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_matching_process_pkg.update_isir_intrface.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','IGF_AP_MATCHING_PROCESS_PKG.UPDATE_ISIR_INTRFACE');
END update_isir_intrface;
SELECT iii.*
FROM igf_ap_isir_ints iii
WHERE si_id = pn_si_id;
SELECT nslds.rowid row_id,
nslds.nslds_id
FROM igf_ap_nslds_data nslds
WHERE base_id = cp_base_id
AND transaction_num_txt = cp_trans_num
FOR UPDATE NOWAIT ;
insert_nslds_data_rec(cp_isir_intrface_rec => cur_isir_intrface_rec,
p_isir_id => pn_isir_id,
p_base_id => pn_base_id,
p_out_nslds_id => ln_nslds_id);
SELECT COUNT(*)
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE pti.person_id = p_person_id
AND pti.person_type_code = pt.person_type_code
AND SYSDATE BETWEEN pti.start_date and NVL(pti.end_date,SYSDATE)
AND pt.system_type IN ('PROSPECT','APPLICANT','STUDENT')
AND NVL(pt.closed_ind,'N') = 'N' ;
SELECT iia.*
FROM igf_ap_isir_ints iia
WHERE si_id = cp_si_id;
SELECT ci_sequence_number,ci_cal_type
FROM igf_ap_batch_aw_map
WHERE batch_year = cp_batch_year;
SELECT *
FROM igf_ap_person_match
WHERE si_id = cp_si_id;
SELECT md.rowid row_id
FROM igf_ap_match_details md
WHERE apm_id = cp_apm_id;
log_debug_message('Inserting a record intp person match table');
igf_ap_person_match_pkg.insert_row(
x_rowid => lv_rowid ,
x_apm_id => ln_apm_id,
x_css_id => NULL,
x_si_id => g_isir_intrface_rec.si_id ,
x_record_type => 'ISIR' ,
x_date_run => TRUNC(SYSDATE),
x_ci_sequence_number => g_ci_sequence_number ,
x_ci_cal_type => g_ci_cal_type ,
x_record_status => g_isir_intrface_rec.record_status ,
x_mode => 'R');
igf_ap_match_details_pkg.delete_row(match_details_rec.row_id);
update_isir_int_record (p_si_id => p_si_id,
p_isir_rec_status => g_isir_intrface_rec.record_status, -- retain existing status
p_match_code => g_match_code -- new match code to be updated
);
log_debug_message(' Interface record status Updated');
SELECT iia.*
FROM igf_ap_isir_ints iia
WHERE si_id = cp_si_id;
SELECT apm_id,
ci_cal_type,
ci_sequence_number
FROM igf_ap_person_match
WHERE si_id = cp_si_id;
SELECT base_id
FROM igf_ap_fa_base_rec br, igf_ap_batch_aw_map am
WHERE person_id = cp_person_id
AND br.ci_cal_type = am.ci_cal_type
AND br.ci_sequence_number = am.ci_sequence_number
AND am.batch_year = cp_batch_year;
SELECT 'Y'
FROM igf_ap_isir_matched
WHERE base_id = cp_base_id
AND transaction_num = cp_transaction_num;
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 BETWEEN start_dt AND NVL(end_dt,SYSDATE);
SELECT iia.*
FROM igf_ap_isir_ints iia
WHERE si_id = cp_si_id;
SELECT *
FROM igf_ap_person_match
WHERE si_id = cp_si_id;