[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_uoo2 to select active (not closed) unit classes.
*/
FUNCTION enrp_get_fee_student(
p_person_id IN NUMBER ,
p_course_cd IN VARCHAR2 ,
p_hecs_payment_option IN VARCHAR2 )
RETURN NUMBER AS
BEGIN
DECLARE
cst_fee_paying_not CONSTANT NUMBER := 1;
SELECT govt_hecs_payment_option
FROM IGS_FI_HECS_PAY_OPTN
WHERE IGS_FI_HECS_PAY_OPTN.hecs_payment_option = p_hecs_payment_option;
SELECT govt_course_type
FROM IGS_EN_STDNT_PS_ATT,
IGS_PS_VER,
IGS_PS_TYPE
WHERE IGS_EN_STDNT_PS_ATT.person_id = p_person_id AND
IGS_EN_STDNT_PS_ATT.course_cd = p_course_cd AND
IGS_PS_VER.course_cd = IGS_EN_STDNT_PS_ATT.course_cd AND
IGS_PS_VER.version_number = IGS_EN_STDNT_PS_ATT.version_number AND
IGS_PS_TYPE.course_type = IGS_PS_VER.course_type;
SELECT sua.cal_type,
sua.ci_sequence_number,
ci.cal_type parent_cal_type,
ci.sequence_number parent_sequence_number
FROM IGS_CA_INST aci,
IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST_REL cir,
IGS_CA_INST ci
WHERE
aci.cal_type = p_acad_cal_type AND
aci.sequence_number = p_acad_sequence_number AND
sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
cir.sub_cal_type = sua.cal_type AND
cir.sub_ci_sequence_number = sua.ci_sequence_number AND
ci.cal_type = cir.sup_cal_type AND
ci.sequence_number = cir.sup_ci_sequence_number AND
(cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number);
SELECT 'X'
FROM IGS_AV_STND_UNIT_LVL asul
WHERE asul.person_id = p_person_id AND
asul.as_course_cd = p_course_cd AND
asul.s_adv_stnd_granting_status IN (cst_granted,
cst_approved);
SELECT 'X'
FROM IGS_PE_UNT_REQUIRMNT pur,
IGS_PE_PERSENC_EFFCT pee
WHERE pur.person_id = p_person_id AND
pur.pur_start_dt <= SYSDATE AND
NVL(pur.expiry_dt, igs_ge_date.igsdate('9999/01/01')) > SYSDATE AND
pee.person_id = pur.person_id AND
pee.encumbrance_type = pur.encumbrance_type AND
pee.pen_start_dt = pur.pen_start_dt AND
pee.s_encmb_effect_type = pur.s_encmb_effect_type AND
pee.pee_start_dt = pur.pee_start_dt AND
pee.sequence_number = pur.pee_sequence_number AND
(pee.course_cd IS NULL OR
pee.course_cd = p_course_cd) AND
NOT EXISTS (
SELECT 'X'
FROM IGS_PS_PAT_STUDY_UNT posu,
IGS_PS_PAT_STUDY_PRD posp
WHERE posu.course_cd = p_course_cd AND
posu.version_number = p_version_number AND
posu.cal_type = p_acad_cal_type AND
posu.pos_sequence_number = p_pos_sequence_number AND
NVL(posu.unit_cd, NULL) = pur.unit_cd AND
posp.course_cd = posu.course_cd AND
posp.version_number = posu.version_number AND
posp.cal_type = posu.cal_type AND
posp.pos_sequence_number = posu.pos_sequence_number AND
posp.sequence_number = posu.posp_sequence_number AND
posp.acad_period_num = p_acad_period_num);
SELECT posu.unit_cd,
posp.teach_cal_type
FROM IGS_PS_PAT_STUDY_UNT posu,
IGS_PS_PAT_STUDY_PRD posp
WHERE posu.course_cd = p_course_cd AND
posu.version_number = p_version_number AND
posu.cal_type = p_acad_cal_type AND
posu.pos_sequence_number = p_pos_sequence_number AND
posu.unit_cd IS NOT NULL AND
posp.course_cd = posu.course_cd AND
posp.version_number = posu.version_number AND
posp.cal_type = posu.cal_type AND
posp.pos_sequence_number = posu.pos_sequence_number AND
posp.sequence_number = posu.posp_sequence_number AND
posp.acad_period_num < p_acad_period_num;
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_cd = cp_posu_unit_cd AND
sua.cal_type = cp_posp_teach_cal_type AND
(sua.unit_attempt_status = cst_enrolled OR
(sua.unit_attempt_status = cst_completed AND
EXISTS (
SELECT 'X'
FROM IGS_AS_SUAO_V suaov,
IGS_AS_GRD_SCH_GRADE gsg
WHERE suaov.person_id = sua.person_id AND
suaov.course_cd = sua.course_cd AND
suaov.uoo_id = sua.uoo_id AND
gsg.grading_schema_cd = suaov.grading_schema_cd AND
gsg.version_number = suaov.version_number AND
gsg.grade = suaov.grade AND
gsg.s_result_type IN (cst_pass,cst_incomp))));
SELECT teach_cal_type
FROM IGS_PS_PAT_STUDY_PRD posp
WHERE posp.course_cd = p_course_cd AND
posp.version_number = p_version_number AND
posp.cal_type = p_acad_cal_type AND
posp.pos_sequence_number = p_pos_sequence_number AND
posp.acad_period_num = p_acad_period_num;
SELECT tci.cal_type,
tci.sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_INST tci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
cir.sub_cal_type = cp_teach_cal_type AND
tci.cal_type = cir.sub_cal_type AND
tci.sequence_number = cir.sub_ci_sequence_number AND
cat.cal_type = tci.cal_type AND
cat.s_cal_cat = 'TEACHING' AND
cs.cal_status = tci.cal_status AND
cs.s_cal_status = cst_active
ORDER BY tci.start_dt DESC;
SELECT uoo_id
FROM IGS_PS_UNIT_OFR_OPT uoo,
IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_STAT us
WHERE uoo.unit_cd = p_unit_cd AND
uoo.cal_type = p_cal_type AND
uoo.ci_sequence_number = p_sequence_number AND
uoo.location_cd = p_location_cd AND
uoo.unit_class = p_unit_class AND
uoo.offered_ind = 'Y' AND
uv.unit_cd = uoo.unit_cd AND
uv.version_number = uoo.version_number AND
uv.expiry_dt IS NULL AND
us.unit_status = uv.unit_status AND
us.s_unit_status = 'ACTIVE';
SELECT uoo.uoo_id,
um.s_unit_mode
FROM IGS_PS_UNIT_OFR_OPT uoo,
IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_STAT us,
IGS_AS_UNIT_CLASS uc,
IGS_AS_UNIT_MODE um
WHERE uoo.unit_cd = p_unit_cd AND
uoo.cal_type = p_cal_type AND
uoo.ci_sequence_number = p_sequence_number AND
uoo.location_cd = NVL(p_location_cd, p_crs_location_cd) AND
(p_unit_class IS NULL OR
uoo.unit_class = p_unit_class) AND
uoo.offered_ind = 'Y' and
uoo.unit_cd = uv.unit_cd AND
uoo.version_number = uv.version_number AND
uv.expiry_dt IS NULL and
us.unit_status = uv.unit_status AND
us.s_unit_status = 'ACTIVE' AND
uoo.unit_class = uc.unit_class AND
uc.closed_ind = 'N' AND
uc.unit_mode = um.unit_mode;
SELECT SUBSTR(IGS_EN_GEN_014.enrs_get_acad_alt_cd(sua_v.cal_type,sua_v.ci_sequence_number),1,10)
FROM IGS_EN_SU_ATTEMPT sua_v,
IGS_CA_INST ci
WHERE sua_v.person_id = p_person_id AND
sua_v.enrolled_dt IS NOT NULL AND
sua_v.cal_type = ci.cal_type AND
sua_v.ci_sequence_number = ci.sequence_number
ORDER BY ci.start_dt,
ci.end_dt ;
SELECT SUBSTR(IGS_EN_GEN_014.enrs_get_acad_alt_cd(sua_v.cal_type,sua_v.ci_sequence_number),1,10)
FROM IGS_EN_SU_ATTEMPT sua_v,
IGS_CA_INST ci
WHERE sua_v.person_id = p_person_id AND
sua_v.enrolled_dt IS NOT NULL AND
sua_v.cal_type = ci.cal_type AND
sua_v.ci_sequence_number = ci.sequence_number
ORDER BY ci.start_dt desc,
ci.end_dt desc ;