[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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, --Modified(added this attribute) by ugummall on 25-SEP-2003 w.r.t FA 126 - Multiple FA Offices
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 FA
WHERE FA.base_id = cp_base_id;
|| insert Pref lender details
|| (reverse chronological order - newest change first)
*/
l_proc_item_str VARCHAR2(50) := NULL;
SELECT sys_award_year,
award_year_status_code
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,
tdint_id,
ci_alternate_code,
person_number,
item_code,
item_add_date,
item_status_code,
item_status_date,
corsp_date,
corsp_count_num,
max_attempt_num,
freq_attempt_num,
reqd_for_application_flag,
inactive_flag,
import_status_type,
import_record_type,
relationship_cd,
ROWID ROW_ID
FROM igf_ap_li_todo_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 hz.party_number person_id
FROM igs_co_interac_hist hist,
hz_parties hz
WHERE hist.cal_type = cp_cal_type
AND hist.ci_sequence_number = cp_seq_number
AND hist.student_id = hz.party_id
AND hz.party_number = cp_person_number
AND rownum = 1;
SELECT todo_number
FROM igf_ap_td_item_mst_all
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number
AND item_code = NVL(cp_item_code,item_code)
AND CAREER_ITEM = 'N'
UNION
SELECT todo_number
FROM igf_ap_td_item_mst_all
WHERE item_code = NVL(cp_item_code,item_code)
AND CAREER_ITEM = 'Y'
);
SELECT item_sequence_number
FROM igf_ap_td_item_inst_v
WHERE base_id = cp_base_id
AND item_code = cp_item_code;
SELECT ROWID ROW_ID
FROM igf_ap_td_item_inst
WHERE base_id = cp_base_id
AND item_sequence_number = cp_item_seq_number;
SELECT system_todo_type_code
FROM igf_ap_td_item_mst_all
WHERE
item_code = cp_item_code AND
system_todo_type_code IS NOT NULL;
SELECT 'X'
FROM igf_sl_cl_recipient
WHERE relationship_cd = cp_rel_cd AND
UPPER(ENABLED) = 'Y';
update_fabase_process(l_old_person_number);
UPDATE igf_ap_li_todo_ints
SET import_status_type = 'E'
WHERE ci_alternate_code = l_get_alternate_code.alternate_code
AND person_number = l_get_persons.person_number
AND batch_num = p_batch_id;
igf_ap_td_item_inst_pkg.delete_row(
x_ROWID => l_get_rowid.ROW_ID
);
igf_ap_td_item_inst_pkg.insert_row(
x_MODE => 'R',
x_BASE_ID => g_baseid_exists.base_id,
x_ROWID => lv_row_id,
x_ITEM_SEQUENCE_NUMBER => l_todo_number,
x_STATUS => l_get_persons.item_status_code,
x_STATUS_DATE => l_get_persons.item_status_date,
x_ADD_DATE => l_get_persons.item_add_date,
x_CORSP_DATE => l_get_persons.corsp_date,
x_CORSP_COUNT => l_get_persons.corsp_count_num,
x_INACTIVE_FLAG => l_get_persons.inactive_flag,
x_FREQ_ATTEMPT => l_get_persons.freq_attempt_num,
x_MAX_ATTEMPT => l_get_persons.max_attempt_num,
x_REQUIRED_FOR_APPLICATION => l_get_persons.reqd_for_application_flag,
x_LEGACY_RECORD_FLAG => 'Y',
x_clprl_id => l_clprl_id
);
l_debug_str := l_debug_str || ' Record Insertion passed';
DELETE FROM igf_ap_li_todo_ints
WHERE ROWID = l_get_persons.ROW_ID;
UPDATE igf_ap_li_todo_ints
SET import_status_type = 'I'
WHERE ROWID = l_get_persons.ROW_ID;
UPDATE igf_ap_li_todo_ints
SET import_status_type = 'E'
WHERE ROWID = l_get_persons.ROW_ID;
update_fabase_process(l_old_person_number);
PROCEDURE update_fabase_process(p_person_number IN VARCHAR2)
IS
/*
|| Created By : museshad
|| Created On : 28-Jul-2005
|| Purpose : Build FA 140
|| Implements the new logic for deriving the FA Base record
|| application status
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- Get person_id
CURSOR c_person_id(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
IS
SELECT person_id
FROM igf_ap_fa_base_rec_all
WHERE base_id = cp_base_id;
SELECT 1
FROM igf_ap_td_item_inst_all tdinst,
igf_ap_td_item_mst_all tdmst
WHERE tdinst.base_id = cp_base_id
AND tdinst.status IN ('INC','REQ','REC')
AND tdinst.required_for_application = 'Y'
AND NVL(tdinst.inactive_flag,'N') <> 'Y'
AND tdinst.item_sequence_number = tdmst.todo_number
AND tdmst.career_item = 'N'
AND ROWNUM < 2
UNION
SELECT 1
FROM igf_ap_td_item_inst_all tdinst,
igf_ap_td_item_mst_all tdmst,
igf_ap_fa_base_rec_all fa
WHERE tdinst.base_id = fa.base_id
AND tdinst.status IN ('INC','REQ','REC')
AND tdinst.required_for_application = 'Y'
AND NVL(tdinst.inactive_flag,'N') <> 'Y'
AND tdinst.item_sequence_number = tdmst.todo_number
AND tdmst.career_item = 'Y'
AND fa.person_id = cp_person_id
AND ROWNUM < 2;
SELECT NVL(fa_process_status,'RECEIVED') fa_process_status
FROM igf_ap_fa_base_rec
WHERE base_id = cp_base_id;
update_fabase_rec('MANUAL_REVIEW');
update_fabase_rec('COMPLETE');
update_fabase_rec('RECEIVED');
END update_fabase_process;
PROCEDURE update_fabase_rec(
p_fa_process_status IN VARCHAR2
) IS
/*
|| Created By : bkkumar
|| Created On : 26-MAY-2003
|| Purpose : This process updates the FA Base Record Application Processing Status
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
|| 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 25-SEP-2003 FA 126 - Multiple FA Offices
|| added new parameter assoc_org_num to
|| igf_ap_fa_base_rec_pkg.update_row call
*/
BEGIN
igf_ap_fa_base_rec_pkg.update_row(
x_Mode => 'R' ,
x_rowid => g_baseid_exists.row_id ,
x_base_id => g_baseid_exists.base_id ,
x_ci_cal_type => g_baseid_exists.ci_cal_type ,
x_person_id => g_baseid_exists.person_id ,
x_ci_sequence_number => g_baseid_exists.ci_sequence_number ,
x_org_id => g_baseid_exists.org_id ,
x_coa_pending => g_baseid_exists.coa_pending ,
x_verification_process_run => g_baseid_exists.verification_process_run ,
x_inst_verif_status_date => g_baseid_exists.inst_verif_status_date ,
x_manual_verif_flag => g_baseid_exists.manual_verif_flag ,
x_fed_verif_status => g_baseid_exists.fed_verif_status ,
x_fed_verif_status_date => g_baseid_exists.fed_verif_status_date ,
x_inst_verif_status => g_baseid_exists.inst_verif_status ,
x_nslds_eligible => g_baseid_exists.nslds_eligible ,
x_ede_correction_batch_id => g_baseid_exists.ede_correction_batch_id ,
x_fa_process_status_date => TRUNC(SYSDATE) ,
x_isir_corr_status => g_baseid_exists.isir_corr_status ,
x_isir_corr_status_date => g_baseid_exists.isir_corr_status_date ,
x_isir_status => g_baseid_exists.isir_status ,
x_isir_status_date => g_baseid_exists.isir_status_date ,
x_coa_code_f => g_baseid_exists.coa_code_f ,
x_coa_code_i => g_baseid_exists.coa_code_i ,
x_coa_f => g_baseid_exists.coa_f ,
x_coa_i => g_baseid_exists.coa_i ,
x_disbursement_hold => g_baseid_exists.disbursement_hold ,
x_fa_process_status => p_fa_process_status ,
x_notification_status => g_baseid_exists.notification_status ,
x_notification_status_date => g_baseid_exists.notification_status_date ,
x_packaging_status => g_baseid_exists.packaging_status ,
x_packaging_status_date => g_baseid_exists.packaging_status_date ,
x_total_package_accepted => g_baseid_exists.total_package_accepted ,
x_total_package_offered => g_baseid_exists.total_package_offered ,
x_admstruct_id => g_baseid_exists.admstruct_id ,
x_admsegment_1 => g_baseid_exists.admsegment_1 ,
x_admsegment_2 => g_baseid_exists.admsegment_2 ,
x_admsegment_3 => g_baseid_exists.admsegment_3 ,
x_admsegment_4 => g_baseid_exists.admsegment_4 ,
x_admsegment_5 => g_baseid_exists.admsegment_5 ,
x_admsegment_6 => g_baseid_exists.admsegment_6 ,
x_admsegment_7 => g_baseid_exists.admsegment_7 ,
x_admsegment_8 => g_baseid_exists.admsegment_8 ,
x_admsegment_9 => g_baseid_exists.admsegment_9 ,
x_admsegment_10 => g_baseid_exists.admsegment_10 ,
x_admsegment_11 => g_baseid_exists.admsegment_11 ,
x_admsegment_12 => g_baseid_exists.admsegment_12 ,
x_admsegment_13 => g_baseid_exists.admsegment_13 ,
x_admsegment_14 => g_baseid_exists.admsegment_14 ,
x_admsegment_15 => g_baseid_exists.admsegment_15 ,
x_admsegment_16 => g_baseid_exists.admsegment_16 ,
x_admsegment_17 => g_baseid_exists.admsegment_17 ,
x_admsegment_18 => g_baseid_exists.admsegment_18 ,
x_admsegment_19 => g_baseid_exists.admsegment_19 ,
x_admsegment_20 => g_baseid_exists.admsegment_20 ,
x_packstruct_id => g_baseid_exists.packstruct_id ,
x_packsegment_1 => g_baseid_exists.packsegment_1 ,
x_packsegment_2 => g_baseid_exists.packsegment_2 ,
x_packsegment_3 => g_baseid_exists.packsegment_3 ,
x_packsegment_4 => g_baseid_exists.packsegment_4 ,
x_packsegment_5 => g_baseid_exists.packsegment_5 ,
x_packsegment_6 => g_baseid_exists.packsegment_6 ,
x_packsegment_7 => g_baseid_exists.packsegment_7 ,
x_packsegment_8 => g_baseid_exists.packsegment_8 ,
x_packsegment_9 => g_baseid_exists.packsegment_9 ,
x_packsegment_10 => g_baseid_exists.packsegment_10 ,
x_packsegment_11 => g_baseid_exists.packsegment_11 ,
x_packsegment_12 => g_baseid_exists.packsegment_12 ,
x_packsegment_13 => g_baseid_exists.packsegment_13 ,
x_packsegment_14 => g_baseid_exists.packsegment_14 ,
x_packsegment_15 => g_baseid_exists.packsegment_15 ,
x_packsegment_16 => g_baseid_exists.packsegment_16 ,
x_packsegment_17 => g_baseid_exists.packsegment_17 ,
x_packsegment_18 => g_baseid_exists.packsegment_18 ,
x_packsegment_19 => g_baseid_exists.packsegment_19 ,
x_packsegment_20 => g_baseid_exists.packsegment_20 ,
x_miscstruct_id => g_baseid_exists.miscstruct_id ,
x_miscsegment_1 => g_baseid_exists.miscsegment_1 ,
x_miscsegment_2 => g_baseid_exists.miscsegment_2 ,
x_miscsegment_3 => g_baseid_exists.miscsegment_3 ,
x_miscsegment_4 => g_baseid_exists.miscsegment_4 ,
x_miscsegment_5 => g_baseid_exists.miscsegment_5 ,
x_miscsegment_6 => g_baseid_exists.miscsegment_6 ,
x_miscsegment_7 => g_baseid_exists.miscsegment_7 ,
x_miscsegment_8 => g_baseid_exists.miscsegment_8 ,
x_miscsegment_9 => g_baseid_exists.miscsegment_9 ,
x_miscsegment_10 => g_baseid_exists.miscsegment_10 ,
x_miscsegment_11 => g_baseid_exists.miscsegment_11 ,
x_miscsegment_12 => g_baseid_exists.miscsegment_12 ,
x_miscsegment_13 => g_baseid_exists.miscsegment_13 ,
x_miscsegment_14 => g_baseid_exists.miscsegment_14 ,
x_miscsegment_15 => g_baseid_exists.miscsegment_15 ,
x_miscsegment_16 => g_baseid_exists.miscsegment_16 ,
x_miscsegment_17 => g_baseid_exists.miscsegment_17 ,
x_miscsegment_18 => g_baseid_exists.miscsegment_18 ,
x_miscsegment_19 => g_baseid_exists.miscsegment_19 ,
x_miscsegment_20 => g_baseid_exists.miscsegment_20 ,
x_prof_judgement_flg => g_baseid_exists.prof_judgement_flg ,
x_nslds_data_override_flg => g_baseid_exists.nslds_data_override_flg ,
x_target_group => g_baseid_exists.target_group ,
x_coa_fixed => g_baseid_exists.coa_fixed ,
x_coa_pell => g_baseid_exists.coa_pell ,
x_profile_status => g_baseid_exists.profile_status ,
x_profile_status_date => g_baseid_exists.profile_status_date ,
x_profile_fc => g_baseid_exists.profile_fc ,
x_manual_disb_hold => g_baseid_exists.manual_disb_hold ,
x_pell_alt_expense => g_baseid_exists.pell_alt_expense,
x_assoc_org_num => g_baseid_exists.assoc_org_num,
x_award_fmly_contribution_type => g_baseid_exists.award_fmly_contribution_type,
x_isir_locked_by => g_baseid_exists.isir_locked_by,
x_adnl_unsub_loan_elig_flag => g_baseid_exists.adnl_unsub_loan_elig_flag,
x_lock_awd_flag => g_baseid_exists.lock_awd_flag,
x_lock_coa_flag => g_baseid_exists.lock_coa_flag
);
END update_fabase_rec;
|| Purpose : Inserts Pref. lender details. This proc. gets called
|| when a completed preferred lender to do item is
|| successfully imported.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- Get Person Id
CURSOR c_get_person_id (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
IS
SELECT person_id
FROM igf_ap_fa_base_rec_all
WHERE base_id = cp_base_id;
SELECT
ROWID row_id,
pref_lender.clprl_id,
pref_lender.relationship_cd,
pref_lender.start_date,
pref_lender.end_date,
pref_lender.person_id
FROM
igf_sl_cl_pref_lenders pref_lender
WHERE
pref_lender.person_id = cp_person_id
AND pref_lender.end_date IS NULL;
igf_sl_cl_pref_lenders_pkg.insert_row (
x_mode => 'R',
x_rowid => l_row_id,
x_clprl_id => p_clprl_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_number,
x_return_status => l_return_status,
x_person_id => l_person_id,
x_start_date => TRUNC(SYSDATE),
x_relationship_cd => p_rel_cd,
x_end_date => NULL
);
igf_sl_cl_pref_lenders_pkg.update_row(
x_mode => 'R',
x_rowid => l_chk_pref_lender.row_id,
x_clprl_id => l_chk_pref_lender.clprl_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_number,
x_return_status => l_return_status,
x_person_id => l_chk_pref_lender.person_id,
x_start_date => l_chk_pref_lender.start_date,
x_relationship_cd => l_chk_pref_lender.relationship_cd,
x_end_date => TRUNC(SYSDATE - 1)
);
igf_sl_cl_pref_lenders_pkg.insert_row (
x_mode => 'R',
x_rowid => l_row_id,
x_clprl_id => p_clprl_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_number,
x_return_status => l_return_status,
x_person_id => l_person_id,
x_start_date => TRUNC(SYSDATE),
x_relationship_cd => p_rel_cd,
x_end_date => NULL
);
SELECT batch_desc
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num;