[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT annual_instruction_time
FROM igs_ps_ver_all
WHERE course_cd = p_course_cd
AND version_number = p_version_number;
SELECT term_instruction_time
FROM igs_en_psv_term_it
WHERE cal_type = p_cal_type
AND sequence_number = p_seq_number
AND course_cd = p_course_cd
AND version_number = p_version_number;
SELECT
term_instruction_time
FROM
igs_ca_inst_all
WHERE
cal_type = p_cal_type AND
sequence_number = p_seq_number;
SELECT
adm.course_cd key_prog,
adm.crv_version_number key_prog_ver
FROM
igs_ad_ps_appl_inst_all adm,
igs_ad_ou_stat s_adm_st,
igf_ap_fa_base_rec_all fabase
WHERE
adm.person_id = fabase.person_id AND
fabase.base_id = p_base_id AND
adm.adm_outcome_status = s_adm_st.adm_outcome_status AND
s_adm_st.s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
adm.course_cd IS NOT NULL AND
1 = (SELECT COUNT(person_id)
FROM igs_ad_ps_appl_inst_all adm1, igs_ad_ou_stat s_adm_st1
WHERE
adm1.person_id = adm.person_id AND
adm1.adm_outcome_status = s_adm_st1.adm_outcome_status AND
s_adm_st1.s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
adm1.course_cd IS NOT NULL);
|| museshad 06-Mar-2006 Bug 5006587. Build FA 162 - COD Reg Updates.
|| Added override for the newly introduced column
|| term_start_offset_num
|| CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
*/
CURSOR c_get_rep_pell_id(
l_cal_type igs_ca_inst.cal_type%TYPE,
l_seq_num igs_ca_inst.sequence_number%TYPE,
l_att_pell_id igf_gr_pell_setup_all.rep_pell_id%TYPE
)
IS
SELECT rep.reporting_pell_cd
FROM igf_gr_attend_pell gap, igf_gr_report_pell rep
WHERE gap.ci_cal_type = l_cal_type
AND gap.ci_sequence_number = l_seq_num
AND gap.attending_pell_cd = l_att_pell_id
AND gap.rcampus_id = rep.rcampus_id;
SELECT pell.*
FROM igf_gr_pell_setup_all pell
WHERE pell.ci_cal_type = l_ci_cal_type
AND pell.ci_sequence_number = l_ci_sequence_number
AND pell.rep_pell_id = l_reporting_pell_id
AND pell.course_cd = l_course_cd
AND pell.version_number = l_version_number;
SELECT pell.*
FROM igf_gr_pell_setup_all pell
WHERE pell.ci_cal_type = l_ci_cal_type
AND pell.ci_sequence_number = l_ci_sequence_number
AND pell.rep_pell_id = l_reporting_pell_id
AND pell.course_cd IS NULL
AND pell.version_number IS NULL;
SELECT rep.rep_entity_id_txt
FROM igf_gr_attend_pell gap, igf_gr_report_pell rep
WHERE gap.ci_cal_type = l_cal_type
AND gap.ci_sequence_number = l_seq_num
AND gap.atd_entity_id_txt = l_atd_entity_id
AND gap.rcampus_id = rep.rcampus_id;
SELECT pell.*
FROM igf_gr_pell_setup_all pell
WHERE pell.ci_cal_type = l_ci_cal_type
AND pell.ci_sequence_number = l_ci_sequence_number
AND pell.rep_entity_id_txt = l_rep_entity_id
AND pell.course_cd IS NULL
AND pell.version_number IS NULL;
SELECT pell.*
FROM igf_gr_pell_setup_all pell
WHERE pell.ci_cal_type = l_ci_cal_type
AND pell.ci_sequence_number = l_ci_sequence_number
AND pell.rep_entity_id_txt = l_rep_entity_id
AND pell.course_cd = l_course_cd
AND pell.version_number = l_version_number;
|| CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
*/
CURSOR c_lt_ht_coa (l_base_id NUMBER)
IS
SELECT
NVL(SUM(aci.pell_coa_amount),0) reg_pell_coa,
NVL(SUM(aci.alt_pell_amount),0) alt_pell_coa
FROM igf_aw_coa_items aci,
igf_aw_item ai
WHERE aci.base_id = l_base_id
AND aci.item_code = ai.item_code
AND ai.item_category_code IN ('TUITION','BOOKS','FEES','TRANSPORTATION','SUPPLIES','DEPENDENT_CARE');
SELECT
NVL(SUM(aci.pell_coa_amount),0) reg_pell_coa,
NVL(SUM(aci.alt_pell_amount),0) alt_pell_coa
FROM igf_aw_coa_items aci
WHERE aci.base_id = l_base_id;
|| CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
*/
p_ld_start_dt DATE;
SELECT
summ_enrl_status
FROM igf_ap_isir_matched isir
WHERE
isir.base_id = l_base_id AND
isir.active_isir = 'Y' ;
SELECT
pell.pell_att_code attendance_type
FROM
igs_en_stdnt_ps_att en,
igf_ap_fa_base_rec fa,
igf_ap_attend_map pell
WHERE
fa.base_id = l_base_id AND
fa.person_id = en.person_id AND
en.key_program = 'Y' AND
pell.cal_type = fa.ci_cal_type AND
pell.sequence_number = fa.ci_sequence_number AND
en.attendance_type = pell.attendance_type ;
SELECT
person_id ,
ci_cal_type,
ci_sequence_number
FROM
igf_ap_fa_base_rec_all fa
WHERE
fa.base_id = l_base_id;
SELECT
pell.pell_att_code attendance_type
FROM
igf_ap_attend_map pell
WHERE
pell.cal_type = l_cal_type AND
pell.sequence_number = l_sequence_number AND
pell.attendance_type = l_att_type;
SELECT 'X'
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id;
SELECT pell.pell_att_code attendance_type
FROM igf_ap_fa_ant_data ant,
igf_ap_attend_map pell,
igf_ap_fa_base_rec_all fa
WHERE fa.base_id = cp_base_id
AND ant.ld_cal_type = cp_ld_cal_type
AND ant.ld_sequence_number = cp_ld_sequence_number
AND pell.attendance_type = ant.attendance_type
AND pell.cal_type = fa.ci_cal_type
AND pell.sequence_number = fa.ci_sequence_number
AND fa.base_id = ant.base_id;
SELECT crngd.pell_amount amount
FROM igf_gr_reg_amts crngd,
igf_ap_batch_aw_map_all batch
WHERE crngd.enrollment_stat_code = l_enrl_stat
AND batch.ci_cal_type = l_ci_cal_type
AND batch.ci_sequence_number = l_ci_sequence_number
AND batch.sys_award_year = crngd.sys_awd_yr
AND (l_coa BETWEEN crngd.coa_range_start AND crngd.coa_range_end)
AND (l_efc BETWEEN crngd.efc_range_start AND crngd.efc_range_end);
SELECT alt.pell_amount amount
FROM igf_gr_alt_amts alt,
igf_ap_batch_aw_map_all batch
WHERE alt.enrollment_stat_code = l_enrl_stat
AND alt.sys_awd_yr = batch.sys_award_year
AND batch.ci_cal_type = l_ci_cal_type
AND batch.ci_sequence_number = l_ci_sequence_number
AND (l_coa BETWEEN alt.coa_range_start AND alt.coa_range_end)
AND (l_altexp BETWEEN alt.exp_range_start AND alt.exp_range_end)
AND (l_efc BETWEEN alt.efc_range_start AND alt.efc_range_end);
SELECT COUNT (*) num_disb
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND terms.ld_cal_type = NVL(cp_ld_cal_type,terms.ld_cal_type)
AND terms.ld_sequence_number = NVL(cp_ld_sequence_number,terms.ld_sequence_number);
/* Update the Accepted amount */
IF p_pkg_awd_status = 'ACCEPTED' THEN
p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
/* Update the Accepted amount */
IF p_pkg_awd_status = 'ACCEPTED' THEN
p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
/* Update the Accepted amount */
IF p_pkg_awd_status = 'ACCEPTED' THEN
p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
/* Update the Accepted amount */
IF p_pkg_awd_status = 'ACCEPTED' THEN
p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
|| CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
*/
-- Get the Academic History of the Student to check if He is
-- Eligible for Pell.
CURSOR c_acad_hist(
l_base_id NUMBER
) IS
SELECT acad.degree_earned,
ptype.fin_aid_program_type
FROM igs_ad_acad_history_v acad,
igs_ps_type_all ptype,
igf_ap_fa_base_rec fa,
igs_ps_degrees dc
WHERE fa.base_id = l_base_id
AND acad.person_id = fa.person_id
AND acad.degree_earned = dc.degree_cd
AND dc.program_type = ptype.course_type
AND UPPER(ptype.fin_aid_program_type) IN ('BACHELORS','PROFESSIONAL');
SELECT NVL(pell_grant_elig_flag, 'N') pell_grant_elig_flag,
transaction_num
FROM igf_ap_isir_matched ism
WHERE ism.base_id = l_base_id
AND ism.active_isir = 'Y';
SELECT COUNT(*) Total_terms FROM (
SELECT
terms.ld_cal_type,
terms.ld_sequence_number
FROM
igf_aw_dp_terms terms,
igf_aw_coa_itm_terms coa
WHERE
coa.base_id = l_base_id
AND terms.adplans_id = l_adplans_id
AND terms.ld_cal_type = coa.ld_cal_type
AND terms.ld_sequence_number = coa.ld_sequence_number
GROUP by terms.ld_cal_type,terms.ld_sequence_number
);
SELECT COUNT(*) Total_terms FROM (
SELECT
terms.ld_cal_type,
terms.ld_sequence_number
FROM
igf_aw_dp_terms terms
WHERE
terms.adplans_id = l_adplans_id
GROUP by terms.ld_cal_type,terms.ld_sequence_number
);
SELECT NVL (igf_aw_packaging.get_date_instance (
l_base_id,
teach.date_offset_cd,
terms.ld_cal_type,
terms.ld_sequence_number
),
teach.start_date) disb_dt,
terms.ld_cal_type,
terms.ld_sequence_number,
teach.tp_cal_type,
teach.tp_sequence_number,
teach.tp_perct_num,
teach.attendance_type_code,
teach.credit_points_num,
teach.date_offset_cd
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach
WHERE terms.adplans_id = l_adplans_id
AND teach.adterms_id = terms.adterms_id
AND (terms.ld_cal_type, terms.ld_sequence_number) IN (
SELECT coa.ld_cal_type,
coa.ld_sequence_number
FROM igf_aw_coa_itm_terms coa
WHERE coa.base_id = l_base_id)
ORDER BY 1;
SELECT
fa.person_id,
fa.ci_cal_type,
fa.ci_sequence_number,
fa.pell_alt_expense
FROM igf_ap_fa_base_rec fa
WHERE fa.base_id = l_base_id ;
SELECT
fm.disb_exp_da,
fm.disb_verf_da,
fm.show_on_bill,
fm.pckg_awd_stat
FROM igf_aw_fund_mast fm
WHERE
fm.fund_id = l_fund_id ;
SELECT dai.absolute_val ofst_absolute_val,
dai.derived_val ofst_derived_val
FROM igs_ca_da_inst_ofst ofst,
igs_ca_da_inst_v dai,
igs_ca_da_inst rel
WHERE rel.dt_alias = cp_ofst_da
AND rel.cal_type = cp_tp_cal_type
AND rel.ci_sequence_number = cp_ci_Sequence
AND ofst.dt_alias = cp_cur_da
AND ofst.offset_dt_alias = cp_ofst_da
AND ofst.offset_dai_sequence_number = rel.sequence_number
AND dai.dt_alias = ofst.dt_alias
AND dai.sequence_number = ofst.dai_sequence_number;
SELECT
dp.adplans_id,
dp.awd_dist_plan_cd,
dp.awd_dist_plan_cd_desc,
dp.dist_plan_method_code
FROM
igf_aw_awd_dist_plans dp
WHERE
dp.adplans_id = l_plan_id;
SELECT ptype.fin_aid_program_type prog_type
FROM
igs_en_stdnt_ps_att en,
igf_ap_fa_base_rec fa,
igs_ps_ver pver,
igs_ps_type_v ptype
WHERE
fa.base_id = l_base_id AND
en.person_id = fa.person_id AND
en.course_cd = pver.course_cd AND
en.version_number = pver.version_number AND
pver.course_type = ptype.course_type AND
UPPER(en.course_attempt_status) = 'COMPLETED' AND
UPPER(ptype.fin_aid_program_type) <> 'PRE-BACHELORS';
SELECT
ant_data.program_cd key_prog,
prog.version_number key_prog_ver
FROM
igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach,
igf_ap_fa_ant_data ant_data,
igs_ps_ver prog
WHERE
terms.adplans_id = cp_plan_id AND
teach.adterms_id = terms.adterms_id AND
ant_data.ld_cal_type = terms.ld_cal_type AND
ant_data.ld_sequence_number = terms.ld_sequence_number AND
ant_data.base_id = cp_base_id AND
ant_data.program_cd = prog.course_cd AND
prog.course_status = 'ACTIVE' AND
ant_data.program_cd IS NOT NULL AND
(terms.ld_cal_type,terms.ld_sequence_number) IN
(SELECT coa.ld_cal_type, coa.ld_sequence_number
FROM igf_aw_coa_itm_terms coa
WHERE coa.base_id = cp_base_id)
ORDER BY
igf_aw_packaging.get_term_start_date(cp_base_id, terms.ld_cal_type, terms.ld_sequence_number) ASC,
prog.version_number DESC;
SELECT
terms.ld_cal_type,
terms.ld_sequence_number,
COUNT(terms.ld_cal_type) tp_count
FROM
igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods
WHERE
teach_periods.adterms_id = terms.adterms_id AND
terms.adplans_id = cp_plan_id AND
(terms.ld_cal_type,
terms.ld_sequence_number) IN (
SELECT coa.ld_cal_type,
coa.ld_sequence_number
FROM igf_aw_coa_itm_terms coa
WHERE coa.base_id = cp_base_id
)
GROUP BY terms.ld_cal_type, terms.ld_sequence_number;
SELECT roundoff_fact
FROM igf_aw_fund_mast_all
WHERE fund_id = cp_fund_id;
|| ugummall 17-DEC-2003 BUG# 3252832 FA-131 Cod updates
|| when c_pell_elig cursor not found used a message.
|| CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
*/
CURSOR c_acad_hist(
l_base_id NUMBER
) IS
SELECT acad.degree_earned,
ptype.fin_aid_program_type
FROM igs_ad_acad_history_v acad,
igs_ps_type_all ptype,
igf_ap_fa_base_rec fa,
igs_ps_degrees dc
WHERE fa.base_id = l_base_id
AND acad.person_id = fa.person_id
AND acad.degree_earned = dc.degree_cd
AND dc.program_type = ptype.course_type
AND UPPER(ptype.fin_aid_program_type) IN ('BACHELORS','PROFESSIONAL');
SELECT NVL(ism.pell_grant_elig_flag, 'N') pell_grant_elig_flag
FROM igf_ap_isir_matched ism
WHERE ism.base_id = l_base_id
AND ism.active_isir = 'Y' ;
SELECT ptype.FIN_AID_PROGRAM_TYPE prog_type
FROM
igs_en_stdnt_ps_att en,
igf_ap_fa_base_rec fa,
igs_ps_ver pver,
igs_ps_type_v ptype
WHERE
fa.base_id = l_base_id AND
en.person_id = fa.person_id AND
en.course_cd = pver.course_cd AND
en.version_number = pver.version_number AND
pver.course_type = ptype.course_type AND
UPPER(en.course_attempt_status) = 'COMPLETED' AND
UPPER(ptype.fin_aid_program_type) <> 'PRE-BACHELORS';
|| CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
*/
CURSOR c_base( l_base_id igf_ap_fa_base_rec.base_id%TYPE
) IS
SELECT
fa.person_id,
fa.ci_cal_type,
fa.ci_sequence_number,
fa.pell_alt_expense
FROM igf_ap_fa_base_rec fa
WHERE fa.base_id = l_base_id ;
SELECT
ant_data.program_cd key_prog,
prog.version_number key_prog_ver
FROM
igf_ap_fa_ant_data ant_data,
igs_ps_ver prog
WHERE
ant_data.ld_cal_type = cp_ld_cal_type AND
ant_data.ld_sequence_number = cp_ld_sequence_number AND
ant_data.base_id = cp_base_id AND
ant_data.program_cd = prog.course_cd AND
prog.course_status = 'ACTIVE' AND
ant_data.program_cd IS NOT NULL AND
ROWNUM = 1
ORDER BY prog.version_number DESC;
SELECT LTRIM(RTRIM(roundoff_fact)) roundoff_fact
FROM
igf_aw_fund_mast_all fmast,
igf_ap_fa_base_rec_all fabase
WHERE
fabase.base_id = cp_base_id AND
fabase.ci_cal_type = fmast.ci_cal_type AND
fabase.ci_sequence_number = fmast.ci_sequence_number AND
UPPER(fmast.fund_code) = 'PELL';
SELECT
RFMS.PELL_AMOUNT
FROM
igf_gr_rfms rfms
WHERE
rfms.base_id = l_base_id;
SELECT
fa.coa_pell pell_coa
FROM
igf_ap_fa_base_rec fa
WHERE
fa.base_id = l_base_id;
SELECT
ant_data.program_cd key_prog,
prog.version_number key_prog_ver
FROM
igf_aw_awd_ld_cal_v awd_year_terms,
igf_ap_fa_ant_data ant_data,
igs_ps_ver prog
WHERE
ant_data.ld_cal_type = awd_year_terms.ld_cal_type AND
ant_data.ld_sequence_number = awd_year_terms.ld_sequence_number AND
ant_data.base_id = cp_base_id and
ant_data.program_cd = prog.course_cd AND
prog.course_status = 'ACTIVE' AND
ant_data.program_cd IS NOT NULL
ORDER BY
igf_aw_packaging.get_term_start_date(cp_base_id, awd_year_terms.ld_cal_type, awd_year_terms.ld_sequence_number) ASC,
prog.version_number DESC;