[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| asbala 19-nov-2003 3026594: FA128 - ISIR Federal Updates 04- 05,
|| modified signature of igf_ap_isir_matched_pkg
|| ugummall 25-SEP-2003 FA 126 Multiple FA Offices
|| added new parameter assoc_org_num to
|| igf_ap_fa_base_rec_pkg.update_row call
|| and added the same to cursor c_baseid_exists
|| (reverse chronological order - newest change first)
*/
l_proc_item_str VARCHAR2(50) := NULL;
SELECT sys_award_year,
batch_year,
award_year_status_code,
css_academic_year
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_number;
SELECT batch_num,
award_process_status_code,
ci_alternate_code,
person_number,
award_process_status_date,
award_notify_status,
award_notify_status_date,
override_nslds_flag,
professional_judgment_flag,
disburse_verification_hold,
import_status_type,
ROWID ROW_ID
FROM igf_ap_li_fab_ints
WHERE ci_alternate_code = cp_alternate_code
AND batch_num = cp_batch_id
AND import_status_type IN ('U','R')
ORDER BY person_number;
SELECT ROWID row_id,
base_id,
ci_cal_type,
person_id,
ci_sequence_number,
org_id ,
bbay ,
current_enrolled_hrs ,
special_handling,
coa_pending,
sap_evaluation_date,
sap_selected_flag,
state_sap_status,
verification_process_run,
inst_verif_status_date,
manual_verif_flag,
fed_verif_status,
fed_verif_status_date,
inst_verif_status,
nslds_eligible,
ede_correction_batch_id,
fa_process_status_date,
isir_corr_status,
isir_corr_status_date,
isir_status,
isir_status_date,
profile_status,
profile_status_date,
profile_fc,
pell_eligible,
award_adjusted,
change_pending,
coa_code_f,
coa_fixed,
coa_code_i,
coa_f,
coa_i ,
coa_pell ,
disbursement_hold ,
enrolment_status ,
enrolment_status_date ,
fa_process_status ,
federal_sap_status ,
grade_level ,
grade_level_date ,
grade_level_type ,
inst_sap_status ,
last_packaged ,
notification_status ,
notification_status_date ,
packaging_hold ,
nslds_data_override_flg ,
packaging_status ,
prof_judgement_flg ,
packaging_status_date ,
qa_sampling ,
target_group ,
todo_code ,
total_package_accepted ,
total_package_offered ,
transcript_available ,
tolerance_amount ,
transfered ,
total_aid ,
admstruct_id,
admsegment_1 ,
admsegment_2 ,
admsegment_3 ,
admsegment_4 ,
admsegment_5 ,
admsegment_6,
admsegment_7,
admsegment_8,
admsegment_9,
admsegment_10,
admsegment_11,
admsegment_12,
admsegment_13,
admsegment_14,
admsegment_15,
admsegment_16,
admsegment_17,
admsegment_18,
admsegment_19,
admsegment_20,
packstruct_id,
packsegment_1,
packsegment_2,
packsegment_3,
packsegment_4,
packsegment_5,
packsegment_6,
packsegment_7,
packsegment_8,
packsegment_9,
packsegment_10,
packsegment_11,
packsegment_12,
packsegment_13,
packsegment_14,
packsegment_15,
packsegment_16,
packsegment_17,
packsegment_18,
packsegment_19,
packsegment_20,
miscstruct_id ,
miscsegment_1,
miscsegment_2 ,
miscsegment_3 ,
miscsegment_4,
miscsegment_5,
miscsegment_6,
miscsegment_7,
miscsegment_8,
miscsegment_9,
miscsegment_10,
miscsegment_11,
miscsegment_12,
miscsegment_13,
miscsegment_14,
miscsegment_15,
miscsegment_16,
miscsegment_17,
miscsegment_18,
miscsegment_19,
miscsegment_20,
request_id,
program_application_id,
program_id ,
program_update_date,
manual_disb_hold,
pell_alt_expense,
assoc_org_num,
award_fmly_contribution_type,
isir_locked_by,
adnl_unsub_loan_elig_flag,
lock_awd_flag,
lock_coa_flag
FROM igf_ap_fa_base_rec_all
WHERE base_id = cp_base_id;
SELECT base_id
FROM igf_aw_award_all
WHERE base_id = cp_base_id
AND rownum = 1;
SELECT api_person_id,api_person_id_uf, end_dt
FROM igs_pe_alt_pers_id
WHERE pe_person_id=cp_person_id
AND person_id_type like 'SSN'
AND SYSDATE < = NVL(end_dt,SYSDATE);
igf_ap_fa_base_rec_pkg.update_row(
x_Mode => 'R' ,
x_rowid => l_baseid_exists.row_id ,
x_base_id => l_baseid_exists.base_id ,
x_ci_cal_type => l_baseid_exists.ci_cal_type ,
x_person_id => l_baseid_exists.person_id ,
x_ci_sequence_number => l_baseid_exists.ci_sequence_number ,
x_org_id => l_baseid_exists.org_id ,
x_coa_pending => l_baseid_exists.coa_pending ,
x_verification_process_run => l_baseid_exists.verification_process_run ,
x_inst_verif_status_date => l_baseid_exists.inst_verif_status_date ,
x_manual_verif_flag => l_baseid_exists.manual_verif_flag ,
x_fed_verif_status => l_baseid_exists.fed_verif_status ,
x_fed_verif_status_date => l_baseid_exists.fed_verif_status_date ,
x_inst_verif_status => l_baseid_exists.inst_verif_status ,
x_nslds_eligible => l_baseid_exists.nslds_eligible ,
x_ede_correction_batch_id => l_baseid_exists.ede_correction_batch_id ,
x_fa_process_status_date => l_baseid_exists.fa_process_status_date ,
x_isir_corr_status => l_baseid_exists.isir_corr_status ,
x_isir_corr_status_date => l_baseid_exists.isir_corr_status_date ,
x_isir_status => l_baseid_exists.isir_status ,
x_isir_status_date => l_baseid_exists.isir_status_date ,
x_coa_code_f => l_baseid_exists.coa_code_f ,
x_coa_code_i => l_baseid_exists.coa_code_i ,
x_coa_f => l_baseid_exists.coa_f ,
x_coa_i => l_baseid_exists.coa_i ,
x_disbursement_hold => l_baseid_exists.disbursement_hold ,
x_fa_process_status => l_baseid_exists.fa_process_status ,
x_notification_status => NULL, --l_get_records.award_notify_status ,
x_notification_status_date => NULL, --l_get_records.award_notify_status_date ,
x_packaging_status => l_process_status ,
x_packaging_status_date => l_process_date ,
x_total_package_accepted => l_baseid_exists.total_package_accepted ,
x_total_package_offered => l_baseid_exists.total_package_offered ,
x_admstruct_id => l_baseid_exists.admstruct_id ,
x_admsegment_1 => l_baseid_exists.admsegment_1 ,
x_admsegment_2 => l_baseid_exists.admsegment_2 ,
x_admsegment_3 => l_baseid_exists.admsegment_3 ,
x_admsegment_4 => l_baseid_exists.admsegment_4 ,
x_admsegment_5 => l_baseid_exists.admsegment_5 ,
x_admsegment_6 => l_baseid_exists.admsegment_6 ,
x_admsegment_7 => l_baseid_exists.admsegment_7 ,
x_admsegment_8 => l_baseid_exists.admsegment_8 ,
x_admsegment_9 => l_baseid_exists.admsegment_9 ,
x_admsegment_10 => l_baseid_exists.admsegment_10 ,
x_admsegment_11 => l_baseid_exists.admsegment_11 ,
x_admsegment_12 => l_baseid_exists.admsegment_12 ,
x_admsegment_13 => l_baseid_exists.admsegment_13 ,
x_admsegment_14 => l_baseid_exists.admsegment_14 ,
x_admsegment_15 => l_baseid_exists.admsegment_15 ,
x_admsegment_16 => l_baseid_exists.admsegment_16 ,
x_admsegment_17 => l_baseid_exists.admsegment_17 ,
x_admsegment_18 => l_baseid_exists.admsegment_18 ,
x_admsegment_19 => l_baseid_exists.admsegment_19 ,
x_admsegment_20 => l_baseid_exists.admsegment_20 ,
x_packstruct_id => l_baseid_exists.packstruct_id ,
x_packsegment_1 => l_baseid_exists.packsegment_1 ,
x_packsegment_2 => l_baseid_exists.packsegment_2 ,
x_packsegment_3 => l_baseid_exists.packsegment_3 ,
x_packsegment_4 => l_baseid_exists.packsegment_4 ,
x_packsegment_5 => l_baseid_exists.packsegment_5 ,
x_packsegment_6 => l_baseid_exists.packsegment_6 ,
x_packsegment_7 => l_baseid_exists.packsegment_7 ,
x_packsegment_8 => l_baseid_exists.packsegment_8 ,
x_packsegment_9 => l_baseid_exists.packsegment_9 ,
x_packsegment_10 => l_baseid_exists.packsegment_10 ,
x_packsegment_11 => l_baseid_exists.packsegment_11 ,
x_packsegment_12 => l_baseid_exists.packsegment_12 ,
x_packsegment_13 => l_baseid_exists.packsegment_13 ,
x_packsegment_14 => l_baseid_exists.packsegment_14 ,
x_packsegment_15 => l_baseid_exists.packsegment_15 ,
x_packsegment_16 => l_baseid_exists.packsegment_16 ,
x_packsegment_17 => l_baseid_exists.packsegment_17 ,
x_packsegment_18 => l_baseid_exists.packsegment_18 ,
x_packsegment_19 => l_baseid_exists.packsegment_19 ,
x_packsegment_20 => l_baseid_exists.packsegment_20 ,
x_miscstruct_id => l_baseid_exists.miscstruct_id ,
x_miscsegment_1 => l_baseid_exists.miscsegment_1 ,
x_miscsegment_2 => l_baseid_exists.miscsegment_2 ,
x_miscsegment_3 => l_baseid_exists.miscsegment_3 ,
x_miscsegment_4 => l_baseid_exists.miscsegment_4 ,
x_miscsegment_5 => l_baseid_exists.miscsegment_5 ,
x_miscsegment_6 => l_baseid_exists.miscsegment_6 ,
x_miscsegment_7 => l_baseid_exists.miscsegment_7 ,
x_miscsegment_8 => l_baseid_exists.miscsegment_8 ,
x_miscsegment_9 => l_baseid_exists.miscsegment_9 ,
x_miscsegment_10 => l_baseid_exists.miscsegment_10 ,
x_miscsegment_11 => l_baseid_exists.miscsegment_11 ,
x_miscsegment_12 => l_baseid_exists.miscsegment_12 ,
x_miscsegment_13 => l_baseid_exists.miscsegment_13 ,
x_miscsegment_14 => l_baseid_exists.miscsegment_14 ,
x_miscsegment_15 => l_baseid_exists.miscsegment_15 ,
x_miscsegment_16 => l_baseid_exists.miscsegment_16 ,
x_miscsegment_17 => l_baseid_exists.miscsegment_17 ,
x_miscsegment_18 => l_baseid_exists.miscsegment_18 ,
x_miscsegment_19 => l_baseid_exists.miscsegment_19 ,
x_miscsegment_20 => l_baseid_exists.miscsegment_20 ,
x_prof_judgement_flg => l_get_records.professional_judgment_flag ,
x_nslds_data_override_flg => l_get_records.override_nslds_flag ,
x_target_group => l_baseid_exists.target_group ,
x_coa_fixed => l_baseid_exists.coa_fixed ,
x_coa_pell => l_baseid_exists.coa_pell ,
x_profile_status => l_baseid_exists.profile_status ,
x_profile_status_date => l_baseid_exists.profile_status_date ,
x_profile_fc => l_baseid_exists.profile_fc ,
x_manual_disb_hold => l_get_records.disburse_verification_hold,
x_pell_alt_expense => l_baseid_exists.pell_alt_expense,
x_assoc_org_num => l_baseid_exists.assoc_org_num,
x_award_fmly_contribution_type => l_baseid_exists.award_fmly_contribution_type,
x_isir_locked_by => l_baseid_exists.isir_locked_by,
x_adnl_unsub_loan_elig_flag => l_baseid_exists.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => l_baseid_exists.lock_awd_flag,
x_lock_coa_flag => l_baseid_exists.lock_coa_flag
);
igf_ap_fa_base_rec_pkg.insert_row(
x_Mode => 'R' ,
x_rowid => l_row_id ,
x_base_id => l_base_id ,
x_ci_cal_type => l_cal_type ,
x_person_id => lv_person_id ,
x_ci_sequence_number => l_seq_number ,
x_org_id => NULL,
x_coa_pending => NULL,
x_verification_process_run => NULL,
x_inst_verif_status_date => NULL,
x_manual_verif_flag => NULL,
x_fed_verif_status => NULL,
x_fed_verif_status_date => NULL,
x_inst_verif_status => NULL,
x_nslds_eligible => NULL,
x_ede_correction_batch_id => NULL,
x_fa_process_status_date => NULL,
x_isir_corr_status => NULL,
x_isir_corr_status_date => NULL,
x_isir_status => NULL,
x_isir_status_date => NULL,
x_coa_code_f => NULL,
x_coa_code_i => NULL,
x_coa_f => NULL,
x_coa_i => NULL,
x_disbursement_hold => NULL,
x_fa_process_status => NULL,
x_notification_status => NULL, --l_get_records.award_notify_status ,
x_notification_status_date => NULL, --l_get_records.award_notify_status_date ,
x_packaging_status => l_process_status ,
x_packaging_status_date => l_process_date ,
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 => l_get_records.professional_judgment_flag ,
x_nslds_data_override_flg => l_get_records.override_nslds_flag ,
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 => l_get_records.disburse_verification_hold,
x_pell_alt_expense => NULL,
x_assoc_org_num => NULL,
x_award_fmly_contribution_type => '1',
x_isir_locked_by => NULL,
x_adnl_unsub_loan_elig_flag => 'N',
x_lock_awd_flag => 'N',
x_lock_coa_flag => 'N'
);
UPDATE igf_ap_li_fab_ints
SET import_status_type = 'E',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE ROWID = l_get_records.ROW_ID;
DELETE FROM igf_ap_li_fab_ints
WHERE ROWID = l_get_records.ROW_ID;
UPDATE igf_ap_li_fab_ints
SET import_status_type = 'I',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE ROWID = l_get_records.ROW_ID;
SELECT batch_desc
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num;
fnd_file.put_line(fnd_file.log,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DELETE_FLAG'),50) || ' : ' || l_yes_no);