[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM IGS_AD_APPL aa
WHERE aa.person_id = p_person_id AND
aa.adm_appl_status IN
(SELECT aas.adm_appl_status
FROM IGS_AD_APPL_STAT aas
WHERE aas.s_adm_appl_status NOT IN
(cst_completed,
cst_withdrawn));
SELECT pen.person_id,
pen.encumbrance_type,
pen.start_dt,
et.s_encumbrance_cat
FROM IGS_PE_PERS_ENCUMB pen,
IGS_FI_ENCMB_TYPE et
WHERE pen.person_id = p_person_id AND
pen.start_dt <= p_effective_dt AND
(pen.expiry_dt IS NULL OR
pen.expiry_dt > p_effective_dt) AND
et.encumbrance_type = pen.encumbrance_type;
SELECT pee.course_cd,
pee.person_id,
pee.encumbrance_type,
pee.pen_start_dt,
pee.s_encmb_effect_type,
pee.pee_start_dt,
pee.sequence_number
FROM IGS_PE_PERSENC_EFFCT pee
WHERE -- child of current pen record
pee.person_id = cp_person_id AND
pee.encumbrance_type = cp_encumbrance_type AND
pee.pen_start_dt = cp_start_dt AND
pee.pee_start_dt <= p_effective_dt AND
(pee.expiry_dt IS NULL OR
pee.expiry_dt > p_effective_dt) AND
(p_course_cd IS NULL OR
pee.course_cd IS NULL OR
pee.course_cd = p_course_cd);
SELECT 'X'
FROM IGS_PE_COURSE_EXCL pce
WHERE -- child of current pee record
pce.person_id = cp_person_id AND
pce.encumbrance_type = cp_encumbrance_type AND
pce.pen_start_dt = cp_pen_start_dt AND
pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
pce.pee_start_dt = cp_pee_start_dt AND
pce.pee_sequence_number = cp_sequence_number AND
pce.pce_start_dt <= p_effective_dt AND
(pce.expiry_dt IS NULL OR
pce.expiry_dt > p_effective_dt) AND
pce.course_cd = p_course_cd;
SELECT 'X'
FROM IGS_PE_CRS_GRP_EXCL pcge
WHERE -- child of current pee record
pcge.person_id = cp_person_id AND
pcge.encumbrance_type = cp_encumbrance_type AND
pcge.pen_start_dt = cp_pen_start_dt AND
pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
pcge.pee_start_dt = cp_pee_start_dt AND
pcge.pee_sequence_number = cp_sequence_number AND
pcge.pcge_start_dt <= p_effective_dt AND
(pcge.expiry_dt IS NULL OR
pcge.expiry_dt > p_effective_dt) AND
EXISTS (
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm
WHERE cgm.course_group_cd = pcge.course_group_cd AND
cgm.course_cd = p_course_cd);
SELECT 'X'
FROM IGS_PE_PERS_UNT_EXCL pue
WHERE -- child of current pee record
pue.person_id = cp_person_id AND
pue.encumbrance_type = cp_encumbrance_type AND
pue.pen_start_dt = cp_pen_start_dt AND
pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
pue.pee_start_dt = cp_pee_start_dt AND
pue.pee_sequence_number = cp_sequence_number AND
pue.pue_start_dt <= p_effective_dt AND
(pue.expiry_dt IS NULL OR
pue.expiry_dt > p_effective_dt);
SELECT 'X'
FROM IGS_PE_UNT_REQUIRMNT pur
WHERE -- child of current pee record
pur.person_id = cp_person_id AND
pur.encumbrance_type = cp_encumbrance_type AND
pur.pen_start_dt = cp_pen_start_dt AND
pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
pur.pee_start_dt = cp_pee_start_dt AND
pur.pee_sequence_number = cp_sequence_number AND
pur.pur_start_dt <= p_effective_dt AND
(pur.expiry_dt IS NULL OR
pur.expiry_dt > p_effective_dt);
SELECT 'X'
FROM IGS_PE_UNT_SET_EXCL puse
WHERE -- child of current pee record
puse.person_id = cp_person_id AND
puse.encumbrance_type = cp_encumbrance_type AND
puse.pen_start_dt = cp_pen_start_dt AND
puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
puse.pee_start_dt = cp_pee_start_dt AND
puse.pee_sequence_number = cp_sequence_number AND
puse.puse_start_dt <= p_effective_dt AND
(puse.expiry_dt IS NULL OR
puse.expiry_dt > p_effective_dt);
SELECT scae.enrolment_cat,
ec.description
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci,
IGS_EN_ENROLMENT_CAT ec
WHERE scae.person_id = p_person_id AND
scae.course_cd =p_course_cd AND
ci.cal_type = scae.cal_type AND
ci.sequence_number =ci_sequence_number AND
ec.enrolment_cat = scae.enrolment_cat
ORDER BY ci.end_dt DESC;
SELECT crv.credit_points_required
FROM IGS_PS_VER crv
WHERE crv.course_cd = p_course_cd AND
crv.version_number = p_version_number;
SELECT SUM( NVL(sua.override_achievable_cp,
NVL( NVL(cps.achievable_credit_points,uv.achievable_credit_points),
NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)
)
)
)
FROM IGS_EN_SU_ATTEMPT sua,
IGS_PS_UNIT_VER uv ,
IGS_PS_USEC_CPS cps
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status = cst_enrolled AND
sua.ci_start_dt <= TRUNC(SYSDATE) AND
sua.uoo_id = cps.uoo_id (+) AND
uv.unit_cd = sua.unit_cd AND
uv.version_number = sua.version_number;
SELECT drc.description
FROM IGS_EN_DCNT_REASONCD drc
WHERE drc.discontinuation_reason_cd = p_discontinuation_reason_cd;
SELECT sci.start_dt
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = p_person_id AND
sci.course_cd = p_course_cd AND
sci.start_dt <= TRUNC(SYSDATE) AND
sci.end_dt >= TRUNC(SYSDATE) AND
sci.approved = eit.appr_reqd_ind AND
eit.intermission_type = sci.intermission_type AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY');
SELECT scah.hist_end_dt
FROM IGS_AS_SC_ATTEMPT_H scah
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
course_attempt_status IS NOT NULL
ORDER BY hist_end_dt DESC; --(use the first record)
SELECT scho.hecs_payment_option,
scho.tax_file_number,
scho.start_dt,
scho.end_dt
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = p_person_id AND
scho.course_cd = p_course_cd AND
scho.start_dt <= SYSDATE AND
(scho.end_dt IS NULL OR
scho.end_dt >= SYSDATE)
ORDER BY start_dt ASC; --(only use the first and earliest record);
SELECT sci.start_dt,
sci.end_dt,
sci.voluntary_ind
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = p_person_id and
sci.course_cd = p_course_cd and
sci.end_dt >= TRUNC(SYSDATE) AND
sci.approved = eit.appr_reqd_ind AND
eit.intermission_type = sci.intermission_type AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
ORDER BY start_dt; --(use the first record)
SELECT sua.version_number,
sua.ci_end_dt,
sua.unit_attempt_status,
sua.location_cd,
sua.unit_class
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.uoo_id = cp_uoo_id;
SELECT sua.cal_type,
sua.ci_sequence_number,
sua.unit_attempt_status,
sua.uoo_id
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_cd = p_unit_cd AND
sua.location_cd = cp_location_cd AND
sua.unit_class = cp_unit_class AND
sua.unit_attempt_status IN (
cst_completed,
cst_duplicate) AND
TRUNC(sua.ci_end_dt) < TRUNC(cp_ci_end_dt);
SELECT uv.repeatable_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_version_number;