[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fund_code fdesc
FROM igf_aw_fund_mast
WHERE fund_id = p_fund_id;
SELECT upper_enr_load_range
FROM igs_en_atd_type_load
WHERE cal_type = p_load_cal_type
AND attendance_type = p_min_att_type;
SELECT
fabaserec.NSLDS_DATA_OVERRIDE_FLG,
match.nslds_match_flag
FROM
igf_ap_isir_matched match ,
igf_ap_fa_base_rec_all fabaserec
WHERE
match.base_id = p_base_id and
match.active_isir = 'Y' and
fabaserec.base_id =p_base_id;
SELECT active
FROM igf_sl_loans_all
WHERE loan_id = p_loan_id;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = p_person_id;
SELECT base_id
FROM igf_ap_fa_base_rec
WHERE person_id = p_person_id AND ci_cal_type = p_cal_type
AND ci_sequence_number = p_sequence_number;
SELECT fund_code
FROM igf_aw_fund_mast_all
WHERE fund_id = p_fund_id;
SELECT fcat.fed_fund_code
FROM igf_aw_fund_mast_all fmast,igf_aw_fund_cat_all fcat
WHERE fmast.fund_id = p_fund_id
AND fmast.fund_code = fcat.fund_code;
SELECT group_cd
FROM igs_pe_persid_group_all
WHERE GROUP_ID = p_per_grp_id;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = p_lookup_type
AND lookup_code = p_trans_type;
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type = 'IGF_GE_PARAMETERS'
AND lookup_code IN (
'AWARD_YEAR',
'FUND_CODE',
'PERSON_NUMBER',
'LOAN_ID',
'TRANS_TYPE',
'PERSON_ID_GROUP'
);
SELECT funding_method
FROM igf_sl_dl_setup_all
WHERE p_ci_cal_type = ci_cal_type
AND p_ci_sequence_number = ci_sequence_number;
SELECT disb_date
FROM igf_aw_awd_disb_all
WHERE p_award_id = award_id AND p_disb_num = disb_num;
SELECT funding_method
FROM igf_gr_pell_setup_all
WHERE p_pell_seq_id = pell_seq_id;
SELECT disb_date
FROM igf_aw_awd_disb_all
WHERE p_award_id = award_id AND p_disb_num = disb_num;
SELECT lor.crdt_decision_status status, lor.credit_override override
FROM igf_sl_lor_all lor, igf_sl_loans_all loan
WHERE loan.loan_id = lor.loan_id AND loan.award_id = p_award_id;
SELECT manual_disb_hold, fa_process_status
FROM igf_ap_fa_base_rec
WHERE base_id = cp_base_id;
SELECT ver_app_stat_override
FROM igf_aw_fund_mast
WHERE fund_id = cp_fund_id;
SELECT fed_verif_status
FROM igf_ap_fa_base_rec fab
WHERE fab.base_id = p_base_id
AND fab.fed_verif_status IN (
'ACCURATE',
'CALCULATED',
'NOTVERIFIED',
'NOTSELECTED',
'REPROCESSED',
'TOLERANCE',
'WAIVED'
);
SELECT 'x'
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_td_map_all fndtd,
igf_ap_td_item_inst_all tdinst,
igf_ap_td_item_mst_all tdmst
WHERE fmast.fund_id = cp_fund_id
AND tdinst.base_id = cp_base_id
AND fndtd.fund_id = fmast.fund_id
AND fndtd.item_sequence_number = tdinst.item_sequence_number
AND fndtd.item_sequence_number = tdmst.todo_number
AND NVL(tdmst.career_item,'N') = 'N'
AND tdinst.status <> cp_status
AND tdinst.inactive_flag <> cp_inactive
UNION
SELECT 'x'
FROM igf_aw_fund_mast_all fmast,
igf_aw_fund_td_map_all fndtd,
igf_ap_td_item_inst_v tdinst,
igf_ap_td_item_mst_all tdmst,
igf_ap_fa_base_rec_all fa
WHERE fmast.fund_id = cp_fund_id
AND fa.base_id = cp_base_id
AND fa.person_id = tdinst.person_id
AND fndtd.fund_id = fmast.fund_id
AND fndtd.item_sequence_number = tdinst.item_sequence_number
AND fndtd.item_sequence_number = tdmst.todo_number
AND NVL(tdmst.career_item,'N') = 'Y'
AND tdinst.status <> cp_status
AND tdinst.inactive_flag <> cp_inactive;
PROCEDURE insert_pays_prg_uts(
p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
p_acad_cal_type igs_ca_inst_all.cal_type%TYPE,
p_acad_ci_seq_num igs_ca_inst_all.sequence_number%TYPE
)
AS
--------------------------------------------------------------------------------------------
--
-- Created By : sjadhav
-- Date Created By : Jan 07,2002
-- Purpose : This routine inserts Pays Only Programs, Units into
-- temporary table igf_db_pays_prg_t
--
--------------------------------------------------------------------------------------------
-- Get all the teaching periods for the academic calendar instance
CURSOR cur_get_acad_tp(
p_acad_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
p_acad_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE
)
IS
SELECT sub_cal_type tp_cal_type,
sub_ci_sequence_number tp_sequence_number
FROM igs_ca_inst_rel cr_1, igs_ca_type ct_1, igs_ca_type ct_2
WHERE ct_1.cal_type = cr_1.sup_cal_type
AND ct_1.s_cal_cat = 'ACADEMIC'
AND ct_2.cal_type = cr_1.sub_cal_type
AND ct_2.s_cal_cat = 'TEACHING'
AND cr_1.sup_cal_type = p_acad_ci_cal_type
AND cr_1.sup_ci_sequence_number = p_acad_ci_sequence_number;
SELECT pg.course_cd prg_course_cd, pg.version_number prg_ver_num,
su.unit_cd unit_course_cd, su.version_number unit_ver_num
FROM igs_en_su_attempt su, igs_en_stdnt_ps_att pg
WHERE su.person_id = p_person_id AND pg.person_id = su.person_id
AND su.unit_attempt_status IN
('COMPLETED', 'ENROLLED', 'DUPLICATE')
AND su.cal_type = p_tp_cal_type
AND su.ci_sequence_number = p_tp_sequence_number
AND pg.cal_type = p_acad_cal_type
AND pg.course_cd(+) = su.course_cd;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','p_acad_cal_type:' || p_acad_cal_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','p_acad_ci_seq_num:' || p_acad_ci_seq_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','igf_gr_gen.get_person_id(' || p_base_id || '):'|| igf_gr_gen.get_person_id(p_base_id));
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','get_acad_tp_rec.tp_cal_type:' || get_acad_tp_rec.tp_cal_type);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','get_acad_tp_rec.tp_sequence_number:'|| get_acad_tp_rec.tp_sequence_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.program_cd:' || dbpays_rec.program_cd);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.prg_ver_num:' || dbpays_rec.prg_ver_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.unit_cd:' || dbpays_rec.unit_cd);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','dbpays_rec.unit_ver_num:' || dbpays_rec.unit_ver_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.insert_pays_prg_uts.debug','inserting to igf_db_pays_prg_t');
igf_db_pays_prg_t_pkg.insert_row(
x_rowid => l_rowid, x_dbpays_id => dbpays_rec.dbpays_id,
x_base_id => dbpays_rec.base_id,
x_program_cd => dbpays_rec.program_cd,
x_prg_ver_num => dbpays_rec.prg_ver_num,
x_unit_cd => dbpays_rec.unit_cd,
x_unit_ver_num => dbpays_rec.unit_ver_num, x_mode => 'R'
);
'NAME', 'IGF_SL_REL_DISB.INSERT_PAYS_PRG_UTS ' || SQLERRM
);
END insert_pays_prg_uts;
SELECT program_cd, prg_ver_num
FROM igf_db_pays_prg_t
WHERE base_id = p_base_id
INTERSECT
SELECT course_cd, version_number
FROM igf_aw_fund_prg_v fprg
WHERE fprg.fund_id = p_fund_id;
SELECT course_cd, version_number
FROM igf_aw_fund_prg_v fprg
WHERE fprg.fund_id = p_fund_id;
SELECT unit_cd, unit_ver_num
FROM igf_db_pays_prg_t
WHERE base_id = p_base_id
INTERSECT
SELECT unit_cd, version_number
FROM igf_aw_fund_unit_v funit
WHERE funit.fund_id = p_fund_id;
SELECT unit_cd, version_number
FROM igf_aw_fund_unit_v funit
WHERE funit.fund_id = p_fund_id;
SELECT fee_class
FROM igf_aw_fund_feeclas
WHERE fund_id = p_fund_id;
SELECT fee_class
FROM igf_aw_fund_feeclas
WHERE fund_id = p_fund_id
INTERSECT
SELECT fee_class
FROM igs_fi_inv_igf_v
WHERE fee_cal_type = p_fee_cal_type
AND fee_ci_sequence_number = p_fee_ci_sequence_number
AND person_id = p_person_id;
PROCEDURE delete_pays
AS
--------------------------------------------------------------------------------------------
--
-- Created By : pssahni
-- Date Created By : 04-Oct-2004
-- Purpose : This routine truncates pays only data used in
-- previous run of eligibility checks
--
--------------------------------------------------------------------------------------------
CURSOR cur_pays_prg
IS
SELECT db.ROWID row_id, db.*
FROM igf_db_pays_prg_t db;
igf_db_pays_prg_t_pkg.delete_row(pays_prg_rec.row_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_disb.delete_pays.debug','deleted from igf_db_pays_prg_t');
'NAME', 'IGF_SL_REL_DISB.DELETE_PAYS ' || SQLERRM
);
END delete_pays;
SELECT loan_number
FROM igf_sl_loans
WHERE p_loan_id = loan_id;
'NAME', 'IGF_SL_REL_DISB.DELETE_PAYS ' || SQLERRM
);
SELECT start_dt, end_dt
FROM igs_ca_inst_all
WHERE p_ld_seq_num = sequence_number;
SELECT ROWID row_id, adisb.*
FROM igf_aw_awd_disb_all adisb
WHERE adisb.award_id = p_award_id
AND adisb.disb_num = p_disb_num
FOR UPDATE OF elig_status NOWAIT;
igf_aw_awd_disb_pkg.update_row(
x_rowid => get_adisb_rec.row_id,
x_award_id => get_adisb_rec.award_id,
x_disb_num => get_adisb_rec.disb_num,
x_tp_cal_type => get_adisb_rec.tp_cal_type,
x_tp_sequence_number => get_adisb_rec.tp_sequence_number,
x_disb_gross_amt => get_adisb_rec.disb_gross_amt,
x_fee_1 => get_adisb_rec.fee_1,x_fee_2 => get_adisb_rec.fee_2,
x_disb_net_amt => get_adisb_rec.disb_net_amt,
x_disb_date => get_adisb_rec.disb_date,
x_trans_type => get_adisb_rec.trans_type,
x_elig_status => get_adisb_rec.elig_status,
x_elig_status_date => get_adisb_rec.elig_status_date,
x_affirm_flag => get_adisb_rec.affirm_flag,
x_hold_rel_ind => get_adisb_rec.hold_rel_ind,
x_manual_hold_ind => get_adisb_rec.manual_hold_ind,
x_disb_status => get_adisb_rec.disb_status,
x_disb_status_date => get_adisb_rec.disb_status_date,
x_late_disb_ind => get_adisb_rec.late_disb_ind,
x_fund_dist_mthd => get_adisb_rec.fund_dist_mthd,
x_prev_reported_ind => get_adisb_rec.prev_reported_ind,
x_fund_release_date => get_adisb_rec.fund_release_date,
x_fund_status => get_adisb_rec.fund_status,
x_fund_status_date => get_adisb_rec.fund_status_date,
x_fee_paid_1 => get_adisb_rec.fee_paid_1,
x_fee_paid_2 => get_adisb_rec.fee_paid_2,
x_cheque_number => get_adisb_rec.cheque_number,
x_ld_cal_type => get_adisb_rec.ld_cal_type,
x_ld_sequence_number => get_adisb_rec.ld_sequence_number,
x_disb_accepted_amt => get_adisb_rec.disb_accepted_amt,
x_disb_paid_amt => get_adisb_rec.disb_paid_amt,
x_rvsn_id => get_adisb_rec.rvsn_id,
x_int_rebate_amt => get_adisb_rec.int_rebate_amt,
x_force_disb => get_adisb_rec.force_disb,
x_min_credit_pts => get_adisb_rec.min_credit_pts,
x_disb_exp_dt => get_adisb_rec.disb_exp_dt,
x_verf_enfr_dt => get_adisb_rec.verf_enfr_dt,
x_fee_class => get_adisb_rec.fee_class,
x_show_on_bill => get_adisb_rec.show_on_bill,
x_attendance_type_code => get_adisb_rec.attendance_type_code,
x_base_attendance_type_code => get_adisb_rec.base_attendance_type_code,
x_mode => 'R',
x_called_from => lv_called_from,
x_payment_prd_st_date => get_adisb_rec.payment_prd_st_date,
x_change_type_code => get_adisb_rec.change_type_code,
x_fund_return_mthd_code => get_adisb_rec.fund_return_mthd_code,
x_direct_to_borr_flag => get_adisb_rec.direct_to_borr_flag
);
SELECT adisb.*, awd.base_id, fmast.ci_cal_type,
fmast.ci_sequence_number, fmast.fund_id, fmast.fund_code,
cat.fed_fund_code
FROM igf_aw_awd_disb_all adisb, igf_aw_fund_mast_all fmast,
igf_aw_award_all awd, igf_aw_fund_cat_all cat
WHERE adisb.award_id = awd.award_id
AND fmast.fund_code = cat.fund_code
AND fmast.fund_id = awd.fund_id AND awd.base_id = p_base_id
AND awd.fund_id = p_fund_id
AND cat.fed_fund_code IN ('PELL', 'DLP', 'DLS', 'DLU')
AND adisb.award_id = NVL(p_award_id, adisb.award_id)
AND adisb.disb_num = NVL(p_disb_num, adisb.disb_num)
AND (
(adisb.trans_type = 'A') OR
(p_trans_type = 'P' AND adisb.trans_type = 'P')
)
AND (adisb.hold_rel_ind = 'FALSE' OR adisb.hold_rel_ind IS NULL)
ORDER BY awd.fund_id, awd.award_id, adisb.disb_num;
SELECT adisb.*, awd.base_id, fmast.ci_cal_type,
fmast.ci_sequence_number, fmast.fund_id, fmast.fund_code,
cat.fed_fund_code
FROM igf_aw_awd_disb_all adisb, igf_aw_fund_mast_all fmast,
igf_aw_award_all awd, igf_aw_fund_cat_all cat
WHERE adisb.award_id = awd.award_id
AND fmast.fund_code = cat.fund_code
AND fmast.fund_id = awd.fund_id AND awd.base_id = p_base_id
AND awd.fund_id = p_fund_id
AND cat.fed_fund_code IN ('ALT', 'FLP', 'FLS', 'FLU')
AND adisb.award_id = NVL(p_award_id, adisb.award_id)
AND adisb.disb_num = NVL(p_disb_num, adisb.disb_num)
AND (adisb.hold_rel_ind = 'H' OR adisb.hold_rel_ind IS NULL)
AND adisb.trans_type = p_trans_type
ORDER BY awd.fund_id, awd.award_id, adisb.disb_num;
SELECT adisb.*, awd.base_id, fmast.ci_cal_type,
fmast.ci_sequence_number, fmast.fund_id, fmast.fund_code,
cat.fed_fund_code
FROM igf_aw_awd_disb_all adisb, igf_aw_fund_mast_all fmast,
igf_aw_award_all awd, igf_aw_fund_cat_all cat
WHERE adisb.award_id = awd.award_id
AND fmast.fund_code = cat.fund_code
AND fmast.fund_id = awd.fund_id AND awd.base_id = p_base_id
AND awd.fund_id = p_fund_id
AND cat.fed_fund_code IN ('ALT', 'FLP', 'FLS', 'FLU')
AND adisb.award_id = NVL(p_award_id, adisb.award_id)
AND adisb.disb_num = NVL(p_disb_num, adisb.disb_num)
AND (adisb.hold_rel_ind = 'H' OR adisb.hold_rel_ind IS NULL)
AND adisb.disb_date <= TRUNC(SYSDATE)
AND adisb.trans_type IN ('A', 'P')
ORDER BY awd.fund_id, awd.award_id, adisb.disb_num;
SELECT NVL(fed_verif_status, '*') fed_verif_status
FROM igf_ap_fa_base_rec
WHERE base_id = p_base_id;
SELECT loan_id, loan_number, loan_status, active, loan_chg_status
FROM igf_sl_loans_all
WHERE award_id = p_award_id AND loan_id = NVL(loan_id, p_loan_id);
SELECT orig_action_code, origination_id
FROM igf_gr_rfms_all
WHERE award_id = p_award_id;
SELECT cat.fed_fund_code
FROM igf_aw_fund_cat_all cat, igf_aw_fund_mast_all fmast
WHERE fmast.fund_code = cat.fund_code AND fmast.fund_id = p_fund_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','calling delete_pays');
delete_pays();
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','called delete_pays');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','calling insert_pays_prg_uts');
insert_pays_prg_uts(awd_disb_rec.base_id, l_acad_cal_type, l_acad_seq_num);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_rel_disb.process_student.debug','called insert_pays_prg_uts');
SELECT alternate_code
FROM igs_ca_inst_all
WHERE cal_type = cp_cal_type AND sequence_number = cp_seq_number;
SELECT group_cd --Code of a person ID group.
--A person ID group also has a unique system generated sequencenumber or group ID
FROM igs_pe_persid_group
WHERE GROUP_ID = p_per_grp_id;
SELECT DISTINCT awd.base_id base_id, fcat.fed_fund_code
fed_fund_code
FROM igf_aw_award awd, igf_aw_fund_mast fmast,
igf_aw_fund_cat fcat
WHERE awd.fund_id = p_fund_id
AND awd.fund_id = fmast.fund_id
AND fmast.fund_code = fcat.fund_code
AND awd.award_status = 'ACCEPTED';
SELECT award_id
FROM igf_sl_loans_all
WHERE loan_id = p_loan_id;
SELECT cat.fed_fund_code fed_fund_code
FROM igf_aw_fund_cat_all cat, igf_aw_fund_mast_all mast
WHERE mast.fund_code = cat.fund_code AND mast.fund_id = p_fund_id;
OPEN cur_per_grp FOR ' SELECT party_id FROM hz_parties WHERE party_id IN ('
|| l_list || ') ' USING p_per_grp_id;
OPEN cur_per_grp FOR ' SELECT party_id FROM hz_parties WHERE party_id IN ('
|| l_list || ') ';