[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fabase.*
FROM igf_ap_fa_base_rec fabase,
hz_parties hz
WHERE hz.party_id = fabase.person_id
AND hz.party_number = p_person_number
AND fabase.ci_cal_type = g_ci_cal_type
AND fabase.ci_sequence_number = g_ci_sequence_number ;
p_delete_flag IN VARCHAR2 ) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : Logs all the Input Parameters
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
SELECT batch_desc, batch_type
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num ;
l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
SELECT 1
FROM igf_ap_inst_ver_item_all
WHERE base_id = cp_base_id
AND isir_map_col = cp_sar_num ;
SELECT 1
FROM igf_ap_isir_corr
WHERE isir_id = cp_pay_isir_id
AND sar_field_number = cp_sar_num ;
PROCEDURE delete_ver_items ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
p_sar_num IN igf_ap_inst_ver_item_all.isir_map_col%TYPE ) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : deletion of Ver Items
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR del_ver_items( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
cp_sar_num igf_ap_inst_ver_item_all.isir_map_col%TYPE ) IS
SELECT rowid
FROM igf_ap_inst_ver_item_all
WHERE base_id = cp_base_id
AND isir_map_col = cp_sar_num ;
igf_ap_inst_ver_item_pkg.delete_row( x_rowid => lv_rowid.rowid);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.delete_ver_items.exception','Unhandled Exception :'||SQLERRM);
fnd_message.set_token('NAME','IGF_AP_LG_VER_IMP.DELETE_VER_TERMS');
END delete_ver_items ;
|| rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
|| added new parameter award_fmly_contribution_type to
|| igf_ap_fa_base_rec_pkg.update_row
|| ugummall 26-SEP-2003 FA 126 - Multiple FA Offices.
|| added new parameter assoc_org_num to TBH call of
|| igf_ap_fa_base_rec_pkg w.r.t. FA 126
||
|| (reverse chronological order - newest change first)
*/
BEGIN
-- get base rec values
-- update fa base rec with new verif status ...
igf_ap_fa_base_rec_pkg.update_row(
x_rowid => g_fabase.row_id,
x_base_id => g_fabase.base_id,
x_ci_cal_type => g_fabase.ci_cal_type,
x_person_id => g_fabase.person_id,
x_ci_sequence_number => g_fabase.ci_sequence_number,
x_org_id => g_fabase.org_id,
x_coa_pending => g_fabase.coa_pending,
x_verification_process_run => g_fabase.verification_process_run,
x_inst_verif_status_date => g_fabase.inst_verif_status_date,
x_manual_verif_flag => g_fabase.manual_verif_flag,
x_fed_verif_status => p_fed_verif_status,
x_fed_verif_status_date => TRUNC(SYSDATE),
x_inst_verif_status => g_fabase.inst_verif_status,
x_nslds_eligible => g_fabase.nslds_eligible,
x_ede_correction_batch_id => g_fabase.ede_correction_batch_id,
x_fa_process_status_date => g_fabase.fa_process_status_date,
x_isir_corr_status => g_fabase.isir_corr_status,
x_isir_corr_status_date => g_fabase.isir_corr_status_date,
x_isir_status => g_fabase.isir_status,
x_isir_status_date => g_fabase.isir_status_date,
x_coa_code_f => g_fabase.coa_code_f,
x_coa_code_i => g_fabase.coa_code_i,
x_coa_f => g_fabase.coa_f,
x_coa_i => g_fabase.coa_i,
x_disbursement_hold => g_fabase.disbursement_hold,
x_fa_process_status => g_fabase.fa_process_status,
x_notification_status => g_fabase.notification_status,
x_notification_status_date => g_fabase.notification_status_date,
x_packaging_status => g_fabase.packaging_status,
x_packaging_status_date => g_fabase.packaging_status_date,
x_total_package_accepted => g_fabase.total_package_accepted,
x_total_package_offered => g_fabase.total_package_offered,
x_admstruct_id => g_fabase.admstruct_id,
x_admsegment_1 => g_fabase.admsegment_1,
x_admsegment_2 => g_fabase.admsegment_2,
x_admsegment_3 => g_fabase.admsegment_3,
x_admsegment_4 => g_fabase.admsegment_4,
x_admsegment_5 => g_fabase.admsegment_5,
x_admsegment_6 => g_fabase.admsegment_6,
x_admsegment_7 => g_fabase.admsegment_7,
x_admsegment_8 => g_fabase.admsegment_8,
x_admsegment_9 => g_fabase.admsegment_9,
x_admsegment_10 => g_fabase.admsegment_10,
x_admsegment_11 => g_fabase.admsegment_11,
x_admsegment_12 => g_fabase.admsegment_12,
x_admsegment_13 => g_fabase.admsegment_13,
x_admsegment_14 => g_fabase.admsegment_14,
x_admsegment_15 => g_fabase.admsegment_15,
x_admsegment_16 => g_fabase.admsegment_16,
x_admsegment_17 => g_fabase.admsegment_17,
x_admsegment_18 => g_fabase.admsegment_18,
x_admsegment_19 => g_fabase.admsegment_19,
x_admsegment_20 => g_fabase.admsegment_20,
x_packstruct_id => g_fabase.packstruct_id,
x_packsegment_1 => g_fabase.packsegment_1,
x_packsegment_2 => g_fabase.packsegment_2,
x_packsegment_3 => g_fabase.packsegment_3,
x_packsegment_4 => g_fabase.packsegment_4,
x_packsegment_5 => g_fabase.packsegment_5,
x_packsegment_6 => g_fabase.packsegment_6,
x_packsegment_7 => g_fabase.packsegment_7,
x_packsegment_8 => g_fabase.packsegment_8,
x_packsegment_9 => g_fabase.packsegment_9,
x_packsegment_10 => g_fabase.packsegment_10,
x_packsegment_11 => g_fabase.packsegment_11,
x_packsegment_12 => g_fabase.packsegment_12,
x_packsegment_13 => g_fabase.packsegment_13,
x_packsegment_14 => g_fabase.packsegment_14,
x_packsegment_15 => g_fabase.packsegment_15,
x_packsegment_16 => g_fabase.packsegment_16,
x_packsegment_17 => g_fabase.packsegment_17,
x_packsegment_18 => g_fabase.packsegment_18,
x_packsegment_19 => g_fabase.packsegment_19,
x_packsegment_20 => g_fabase.packsegment_20,
x_miscstruct_id => g_fabase.miscstruct_id,
x_miscsegment_1 => g_fabase.miscsegment_1,
x_miscsegment_2 => g_fabase.miscsegment_2,
x_miscsegment_3 => g_fabase.miscsegment_3,
x_miscsegment_4 => g_fabase.miscsegment_4,
x_miscsegment_5 => g_fabase.miscsegment_5,
x_miscsegment_6 => g_fabase.miscsegment_6,
x_miscsegment_7 => g_fabase.miscsegment_7,
x_miscsegment_8 => g_fabase.miscsegment_8,
x_miscsegment_9 => g_fabase.miscsegment_9,
x_miscsegment_10 => g_fabase.miscsegment_10,
x_miscsegment_11 => g_fabase.miscsegment_11,
x_miscsegment_12 => g_fabase.miscsegment_12,
x_miscsegment_13 => g_fabase.miscsegment_13,
x_miscsegment_14 => g_fabase.miscsegment_14,
x_miscsegment_15 => g_fabase.miscsegment_15,
x_miscsegment_16 => g_fabase.miscsegment_16,
x_miscsegment_17 => g_fabase.miscsegment_17,
x_miscsegment_18 => g_fabase.miscsegment_18,
x_miscsegment_19 => g_fabase.miscsegment_19,
x_miscsegment_20 => g_fabase.miscsegment_20,
x_prof_judgement_flg => g_fabase.prof_judgement_flg,
x_nslds_data_override_flg => g_fabase.nslds_data_override_flg ,
x_target_group => g_fabase.target_group,
x_coa_fixed => g_fabase.coa_fixed,
x_coa_pell => g_fabase.coa_pell,
x_profile_status => g_fabase.profile_status,
x_profile_status_date => g_fabase.profile_status_date,
x_profile_fc => g_fabase.profile_fc,
x_tolerance_amount => g_fabase.tolerance_amount,
x_manual_disb_hold => g_fabase.manual_disb_hold,
x_mode => 'R',
x_pell_alt_expense => g_fabase.pell_alt_expense,
x_assoc_org_num => g_fabase.assoc_org_num,
x_award_fmly_contribution_type => g_fabase.award_fmly_contribution_type,
x_isir_locked_by => g_fabase.isir_locked_by,
x_adnl_unsub_loan_elig_flag => g_fabase.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => g_fabase.lock_awd_flag,
x_lock_coa_flag => g_fabase.lock_coa_flag
);
p_delete_flag IN VARCHAR2 ) IS
/*
|| Created By : masehgal
|| Created On : 28-May-2003
|| Purpose : Main - called from submitted request
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
|| igf_ap_inst_ver_item_pkg.insert_row
*/
l_prof_set VARCHAR2(1) ;
SELECT award_year_status_code, sys_award_year
FROM igf_ap_batch_aw_map map
WHERE map.ci_cal_type = g_ci_cal_type
AND map.ci_sequence_number = g_ci_sequence_number ;
SELECT *
FROM igf_ap_li_vern_ints
WHERE ci_alternate_code = cp_alternate_code
AND batch_num = cp_batch_num
AND import_status_type IN ('R','U')
ORDER BY person_number ;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_ci_cal_type
AND sequence_number = cp_ci_sequence_number ;
SELECT sar_field_number
FROM igf_fc_sar_cd_mst
WHERE sys_award_year = cp_sys_award_year
AND sar_field_name = cp_sar_name ;
SELECT isir_id, active_isir, payment_isir
FROM igf_ap_isir_matched
WHERE base_id = cp_base_id
AND (active_isir = 'Y' OR payment_isir = 'Y' ) ;
log_input_params( p_batch_num, l_alternate_code , p_delete_flag);
IF l_fed_verif_status IN ('SELECTED','NOTSELECTED') THEN
IF l_doc_null_flag THEN
l_new_fed_verif_status := 'WITHOUTDOC' ;
l_new_fed_verif_status := 'SELECTED' ;
FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_SELECTED');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Federal Verification Status updated in Procedure main');
UPDATE igf_ap_li_vern_ints
SET import_status_type = 'E'
WHERE batch_num = p_batch_num
AND person_number = person_rec.person_number ;
UPDATE igf_ap_li_vern_ints
SET import_status_type = 'E'
WHERE verint_id = person_rec.verint_id ;
delete_ver_items ( l_fa_base_id, l_sar_num );
igf_ap_inst_ver_item_pkg.insert_row (
x_rowid => lv_rowid ,
x_base_id => l_fa_base_id ,
x_udf_vern_item_seq_num => NULL ,
x_item_value => TRIM(person_rec.sar_field_value_txt) ,
x_waive_flag => NULL ,
x_incl_in_tolerance => NULL ,
x_isir_map_col => l_sar_num ,
x_legacy_record_flag => 'Y' ,
x_use_blank_flag => NULL,
x_mode => 'R'
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Inserted Verification record in Procedure main');
IF p_delete_flag = 'Y' THEN
DELETE FROM igf_ap_li_vern_ints
WHERE verint_id = person_rec.verint_id ;
UPDATE igf_ap_li_vern_ints
SET import_status_type = 'I'
WHERE verint_id = person_rec.verint_id ;
lv_stmt := 'SELECT ' || person_rec.sar_field_label_code || ' FROM igf_ap_isir_matched WHERE isir_id = :l_isir_id' ;
END LOOP ; -- person selection loop
IF l_fed_verif_status IN ('SELECTED','NOTSELECTED') THEN
IF l_doc_null_flag THEN
l_new_fed_verif_status := 'WITHOUTDOC' ;
l_new_fed_verif_status := 'SELECTED' ;
FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_SELECTED');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Federal Verification Status updated for last person in Procedure main');