[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| igf_ap_fa_base_rec tables are updated to change the correction_status.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| bkkumar 07-May-2004 Bug 3598933 Added the fnd logging messages
|| veramach 29-Apr-2004 bug 3598067
|| Changed gv_Trans_Data_Source_Or_Type's value to '1C' rather than 'IC'
|| ugummall 31-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
|| 1. Added 5 new parameters to prepare_file.
|| 2. Removed cursors get_school and dest_num_cur and their references.
|| 3. Modified cursor match_isirs to select only those records whose
|| associated org unit's federal school code match with passed in code.
|| cdcruz 17-Sep-2003 # 3085558 FA121-Verification Worksheet.
|| HOld check added
|| masehgal 25-Sep-2002 FA 104 -To Do Enhancements
|| Added manual_disb_hold in update of Fa Base Rec
|| (reverse chronological order - newest change first)
*/
gn_isir_id igf_ap_isir_corr.isir_id%TYPE;
SELECT isir_id, sar_field_number, original_value, corrected_value
FROM igf_ap_isir_corr
WHERE correction_status = cp_corr_status
AND isir_id = gn_isir_id;
SELECT 'x'
FROM igf_ap_isir_corr
WHERE
correction_status = 'HOLD' AND
isir_id = gn_isir_id and
rownum = 1;
SELECT isirm.isir_id, isirm.s_email_address, isirm.transaction_num, isirm.original_ssn,
isirm.orig_name_id, isirm.first_name, isirm.last_name, isirm.base_id, PE.party_number person_number
FROM igf_ap_isir_matched_all isirm,
igf_ap_fa_base_rec_all far,
hz_parties pe
WHERE isirm.base_id = far.base_id
AND far.person_id = pe.party_Id
AND isirm.system_record_type = 'ORIGINAL'
AND isirm.base_id = NVL(p_base_id, isirm.base_id)
AND far.ci_cal_type = gv_cal_type
AND far.ci_sequence_number = gn_sequence_number
AND isirm.isir_id IN (SELECT DISTINCT c.isir_id
FROM igf_ap_isir_corr_all c
WHERE c.correction_status = 'READY' );
SELECT
reject_override_3_flag,
reject_override_12_flag,
reject_override_a,
reject_override_b,
reject_override_c,
reject_override_g_flag,
reject_override_j_flag,
reject_override_k_flag,
reject_override_n,
reject_override_w,
assum_override_1,
assum_override_2,
assum_override_3,
assum_override_4,
assum_override_5,
assum_override_6
FROM igf_ap_isir_matched
WHERE base_id = gn_baseid
AND system_record_type = cp_rec_type ;
SELECT batch_year
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = gv_cal_type
AND ci_sequence_number = gn_sequence_number;
SELECT
reject_override_3_flag,
reject_override_12_flag,
reject_override_a,
reject_override_b,
reject_override_c,
reject_override_g_flag,
reject_override_j_flag,
reject_override_k_flag,
reject_override_n,
reject_override_w,
assum_override_1,
assum_override_2,
assum_override_3,
assum_override_4,
assum_override_5,
assum_override_6
FROM igf_ap_isir_matched_all
WHERE base_id = gn_baseid
AND system_record_type = 'ORIGINAL'
AND payment_isir = 'Y';
|| masehgal 14-May-2003 # 2885882 FACR113 SAR Updates
|| Precessing based on SAR Names rather than SAR Numbers
|| (reverse chronological order - newest change first)
*/
CURSOR cur_get_sar_name (cp_base_id igf_ap_fa_base_rec.base_id%TYPE ,
l_sar_number igf_fc_sar_cd_mst.sar_field_number%TYPE ) IS
SELECT sar.sar_field_name
FROM igf_ap_batch_aw_map map,
igf_ap_fa_base_rec_all fabase ,
igf_fc_sar_cd_mst sar
WHERE fabase.base_id = cp_base_id
AND map.ci_cal_type = fabase.ci_cal_type
AND map.ci_sequence_number = fabase.ci_sequence_number
AND sar.sys_award_year = map.sys_award_year
AND sar.sar_field_number = l_sar_number ;
PROCEDURE update_corr
IS
/*
|| Created By : Sridhar
|| Created On : 25-NOV-2000
|| Purpose : Updates the status of the records written into output file to batched
|| in IGF_AP_ISIR_CORR and IGF_AP_FA_BASE_REC tables.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| ugummall 26-SEP-2003 FA 126 - Multiple FA Offices.
|| added new parameter assoc_org_num to TBH call
|| igf_ap_fa_base_rec_pkg.update_row w.r.t. FA 126
||
|| masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
|| removed packaging hold
|| masehgal 25-Sep-2002 FA 104 -To Do Enhancements
|| Added manual_disb_hold in update of Fa Base Rec
|| rbezawad 22-Jun-2001 igf_ap_fa_base_rec_pkg.update_row call modified by
|| passing gv_batchnum to parameter x_ede_correction_batch_id
|| w.r.t. Bug ID: 1821811
|| (reverse chronological order - newest change first)
*/
CURSOR corr_rec ( cp_corr_stat VARCHAR2) IS
SELECT corr.*
FROM igf_ap_isir_corr corr
WHERE isir_id = gn_isir_id
AND correction_status = cp_corr_stat ;
SELECT f.*
FROM igf_ap_fa_base_rec f
WHERE base_id = gn_baseid;
igf_ap_isir_corr_pkg.update_row(
x_mode => 'R',
x_rowid => pn_rec.row_id,
x_isirc_id => pn_rec.isirc_id,
x_isir_id => pn_rec.isir_id,
x_ci_sequence_number => pn_rec.ci_sequence_number,
x_ci_cal_type => pn_rec.ci_cal_type,
x_sar_field_number => pn_rec.sar_field_number,
x_original_value => pn_rec.original_value,
x_batch_id => gv_batchnum,
x_corrected_value => pn_rec.corrected_value,
x_correction_status => 'BATCHED'
);
igf_ap_fa_base_rec_pkg.update_row(
x_mode => 'R',
x_rowid => base_rec.row_id,
x_base_id => base_rec.base_id,
x_ci_cal_type => base_rec.ci_cal_type,
x_person_id => base_rec.person_id,
x_ci_sequence_number => base_rec.ci_sequence_number,
x_org_id => base_rec.org_id,
x_coa_pending => base_rec.coa_pending,
x_verification_process_run => base_rec.verification_process_run,
x_inst_verif_status_date => base_rec.inst_verif_status_date,
x_manual_verif_flag => base_rec.manual_verif_flag,
x_fed_verif_status => 'CORRSENT' ,
x_fed_verif_status_date => base_rec.fed_verif_status_date,
x_inst_verif_status => base_rec.inst_verif_status,
x_nslds_eligible => base_rec.nslds_eligible,
x_ede_correction_batch_id => gv_batchnum, --Modified by rbezawad on 22-Jun-2001 w.r.t. Bug ID: 1821811
x_fa_process_status_date => base_rec.fa_process_status_date,
x_isir_corr_status => corr_stat,
x_isir_corr_status_date => lv_curdate,
x_isir_status => base_rec.isir_status,
x_isir_status_date => base_rec.isir_status_date,
x_coa_code_f => base_rec.coa_code_f,
x_coa_code_i => base_rec.coa_code_i,
x_coa_f => base_rec.coa_f,
x_coa_i => base_rec.coa_i,
x_disbursement_hold => base_rec.disbursement_hold,
x_fa_process_status => base_rec.fa_process_status,
x_notification_status => base_rec.notification_status,
x_notification_status_date => base_rec.notification_status_date,
x_packaging_status => base_rec.packaging_status,
x_packaging_status_date => base_rec.packaging_status_date,
x_total_package_accepted => base_rec.total_package_accepted,
x_total_package_offered => base_rec.total_package_offered,
x_admstruct_id => base_rec.admstruct_id,
x_admsegment_1 => base_rec.admsegment_1,
x_admsegment_2 => base_rec.admsegment_2,
x_admsegment_3 => base_rec.admsegment_3,
x_admsegment_4 => base_rec.admsegment_4,
x_admsegment_5 => base_rec.admsegment_5,
x_admsegment_6 => base_rec.admsegment_6,
x_admsegment_7 => base_rec.admsegment_7,
x_admsegment_8 => base_rec.admsegment_8,
x_admsegment_9 => base_rec.admsegment_9,
x_admsegment_10 => base_rec.admsegment_10,
x_admsegment_11 => base_rec.admsegment_11,
x_admsegment_12 => base_rec.admsegment_12,
x_admsegment_13 => base_rec.admsegment_13,
x_admsegment_14 => base_rec.admsegment_14,
x_admsegment_15 => base_rec.admsegment_15,
x_admsegment_16 => base_rec.admsegment_16,
x_admsegment_17 => base_rec.admsegment_17,
x_admsegment_18 => base_rec.admsegment_18,
x_admsegment_19 => base_rec.admsegment_19,
x_admsegment_20 => base_rec.admsegment_20,
x_packstruct_id => base_rec.packstruct_id,
x_packsegment_1 => base_rec.packsegment_1,
x_packsegment_2 => base_rec.packsegment_2,
x_packsegment_3 => base_rec.packsegment_3,
x_packsegment_4 => base_rec.packsegment_4,
x_packsegment_5 => base_rec.packsegment_5,
x_packsegment_6 => base_rec.packsegment_6,
x_packsegment_7 => base_rec.packsegment_7,
x_packsegment_8 => base_rec.packsegment_8,
x_packsegment_9 => base_rec.packsegment_9,
x_packsegment_10 => base_rec.packsegment_10,
x_packsegment_11 => base_rec.packsegment_11,
x_packsegment_12 => base_rec.packsegment_12,
x_packsegment_13 => base_rec.packsegment_13,
x_packsegment_14 => base_rec.packsegment_14,
x_packsegment_15 => base_rec.packsegment_15,
x_packsegment_16 => base_rec.packsegment_16,
x_packsegment_17 => base_rec.packsegment_17,
x_packsegment_18 => base_rec.packsegment_18,
x_packsegment_19 => base_rec.packsegment_19,
x_packsegment_20 => base_rec.packsegment_20,
x_miscstruct_id => base_rec.miscstruct_id,
x_miscsegment_1 => base_rec.miscsegment_1,
x_miscsegment_2 => base_rec.miscsegment_2,
x_miscsegment_3 => base_rec.miscsegment_3,
x_miscsegment_4 => base_rec.miscsegment_4,
x_miscsegment_5 => base_rec.miscsegment_5,
x_miscsegment_6 => base_rec.miscsegment_6,
x_miscsegment_7 => base_rec.miscsegment_7,
x_miscsegment_8 => base_rec.miscsegment_8,
x_miscsegment_9 => base_rec.miscsegment_9,
x_miscsegment_10 => base_rec.miscsegment_10,
x_miscsegment_11 => base_rec.miscsegment_11,
x_miscsegment_12 => base_rec.miscsegment_12,
x_miscsegment_13 => base_rec.miscsegment_13,
x_miscsegment_14 => base_rec.miscsegment_14,
x_miscsegment_15 => base_rec.miscsegment_15,
x_miscsegment_16 => base_rec.miscsegment_16,
x_miscsegment_17 => base_rec.miscsegment_17,
x_miscsegment_18 => base_rec.miscsegment_18,
x_miscsegment_19 => base_rec.miscsegment_19,
x_miscsegment_20 => base_rec.miscsegment_20,
x_prof_judgement_flg => base_rec.prof_judgement_flg,
x_nslds_data_override_flg => base_rec.nslds_data_override_flg,
x_target_group => base_rec.target_group,
x_coa_fixed => base_rec.coa_fixed,
x_profile_status => base_rec.profile_status,
x_profile_status_date => base_rec.profile_status_date,
x_profile_fc => base_rec.profile_fc,
x_coa_pell => base_rec.coa_pell,
x_tolerance_amount => base_rec.tolerance_amount,
x_manual_disb_hold => base_rec.manual_disb_hold,
x_pell_alt_expense => base_rec.pell_alt_expense,
x_assoc_org_num => base_rec.assoc_org_num,
x_award_fmly_contribution_type => base_rec.award_fmly_contribution_type,
x_isir_locked_by => base_rec.isir_locked_by,
x_adnl_unsub_loan_elig_flag => base_rec.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => base_rec.lock_awd_flag,
x_lock_coa_flag => base_rec.lock_coa_flag
);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.update_corr.exception','The exception is : ' || SQLERRM );
fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.UPDATE_CORR');
END update_corr;
SELECT COUNT(*)
FROM igf_ap_isir_corr corr,
igf_ap_isir_matched isir
WHERE corr.correction_status = cp_corr_stat
AND corr.isir_id = isir.isir_id
AND isir.system_record_type = 'ORIGINAL'
AND isir.batch_year = gv_batch_year ;
SELECT s_email_address
FROM igf_ap_isir_matched_all
WHERE system_record_type='CORRECTION'
AND base_id = cp_base_id;
update_corr;