[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sca.person_id,
sca.course_cd,
sca.course_rqrmnts_complete_dt,
SYSDATE,
ci.alternate_code,
gr.s_graduand_type,
gr.conferral_dt,
gst.s_graduand_status,
aw.award_title
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_GR_GRADUAND gr,
IGS_GR_STAT gst,
IGS_CA_INST ci,
IGS_PS_AWD aw
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.course_rqrmnt_complete_ind = 'Y' AND
sca.person_id = gr.person_id AND
sca.course_cd = gr.course_cd AND
gr.GRADUAND_STATUS = gst.GRADUAND_STATUS AND
gst.s_graduand_status in ('ELIGIBLE','GRADUATED') AND
gr.award_cd = aw.award_cd AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
((sca.course_rqrmnts_complete_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(sca.course_rqrmnts_complete_dt >
ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)))
UNION
SELECT sca.person_id,
sca.course_cd,
sca.course_rqrmnts_complete_dt,
SYSDATE,
ci.alternate_code,
NULL,
SYSDATE,
NULL,
NULL
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_CA_INST ci
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.course_rqrmnt_complete_ind = 'Y' AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
((sca.course_rqrmnts_complete_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(sca.course_rqrmnts_complete_dt >
ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind))) AND
NOT EXISTS (select '1' from IGS_GR_GRADUAND gr
where gr.person_id = sca.person_id
and gr.course_cd = sca.course_cd)
ORDER BY 3;
SELECT ca.person_id,
ca.sca_course_cd,
th.TITLE,
th.final_title_ind,
ci.alternate_code
FROM IGS_RE_CANDIDATURE ca,
IGS_RE_THESIS th,
IGS_EN_STDNT_PS_ATT sca,
IGS_CA_INST ci
WHERE ca.person_id = p_person_id AND
ca.sca_course_cd = p_course_cd AND
ca.person_id = th.person_id AND
ca.sequence_number = th.ca_sequence_number AND
ca.person_id = sca.person_id AND
ca.sca_course_cd = sca.course_cd AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
((sca.commencement_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(sca.commencement_dt >
ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)))
ORDER BY
sca.commencement_dt;
SELECT susa.person_id,
susa.unit_set_cd,
NVL(susa.override_title, us.title) unit_set_title,
us.UNIT_SET_CAT,
usc.description,
susa.selection_dt,
susa.end_dt,
susa.rqrmnts_complete_dt,
susa.primary_set_ind
FROM IGS_AS_SU_SETATMPT susa,
IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_CAT usc,
IGS_CA_INST ci
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.rqrmnts_complete_ind = 'Y' AND
susa.end_dt IS NULL AND
susa.unit_set_cd = us.unit_set_cd AND
susa.us_version_number = us.version_number AND
us.UNIT_SET_CAT = usc.UNIT_SET_CAT AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
(((susa.selection_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(susa.rqrmnts_complete_dt BETWEEN ci.start_dt AND ci.end_dt)) OR
(susa.selection_dt >
ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)) OR
(susa.rqrmnts_complete_dt >
ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)))
ORDER BY
susa.unit_set_cd,
susa.selection_dt,
susa.rqrmnts_complete_dt;
IGS_AS_PRC_TRANSCRPT.gv_susa_dtl_index).v_selection_dt :=
v_susa_rec.selection_dt;
v_index).v_selection_dt IS NOT NULL THEN
IF p_record_number = 1 THEN
v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
v_index).v_title,'-')
|| ' SELECTED ON ' ||
FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
v_index).v_selection_dt);
|| ' SELECTED ON ' ||
FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
v_index).v_selection_dt);
v_index).v_selection_dt IS NOT NULL THEN
IF p_record_number = 1 THEN
v_out_string := fnd_global.local_chr(10) || NVL(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
v_index).v_title,'-')
|| ' SELECTED ON ' ||
FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
v_index).v_selection_dt);
|| ' SELECTED ON ' ||
FND_DATE.DATE_TO_DISPLAYDATE(IGS_AS_PRC_TRANSCRPT.gt_susa_dtl_table(
v_index).v_selection_dt);
SELECT sut.person_id, sut.course_cd, sut.unit_cd, sut.cal_type,
sut.ci_sequence_number, sut.uoo_id, uv.short_title, uv.title,
NVL (cps.achievable_credit_points, uv.achievable_credit_points),
uv.unit_level,
igs_en_gen_014.enrs_get_acad_alt_cd
(sut.cal_type,
sut.ci_sequence_number
) acad_alternate_code,
igs_ca_gen_001.calp_get_alt_cd
(sut.cal_type,
sut.ci_sequence_number
) teach_alternate_code,
ci.alternate_code,
NVL (suav.override_achievable_cp,
NVL (cps.achievable_credit_points, uv.achievable_credit_points)
) v_cp_achieved,
NVL (suav.override_enrolled_cp,
NVL (cps.enrolled_credit_points, uv.enrolled_credit_points)
) v_cp_achievable
FROM igs_ps_stdnt_unt_trn sut,
igs_ps_stdnt_trn sct,
igs_en_su_attempt_all suav,
igs_ps_usec_cps cps,
igs_ps_unit_ver uv,
igs_ca_inst ci
WHERE sut.person_id = p_person_id
AND sut.course_cd = p_to_course_cd
AND sut.person_id = sct.person_id
AND sut.course_cd = sct.course_cd
AND sut.transfer_course_cd = sct.transfer_course_cd
AND sut.unit_cd = uv.unit_cd
AND suav.person_id = sut.person_id
AND suav.course_cd = sut.transfer_course_cd
AND suav.version_number = uv.version_number
AND suav.uoo_id = sut.uoo_id
AND suav.uoo_id = cps.uoo_id(+)
AND ci.cal_type = p_acad_cal_type
AND ci.sequence_number = p_acad_ci_sequence_number
AND (sut.transfer_dt BETWEEN ci.start_dt AND ci.end_dt)
ORDER BY sut.unit_cd;
SELECT sct.person_id,
sct.transfer_course_cd,
sct.course_cd,
sct.transfer_dt,
ci.alternate_code
FROM IGS_PS_STDNT_TRN sct,
IGS_CA_INST ci
WHERE sct.person_id = p_person_id AND
sct.course_cd = p_course_cd AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
(sct.transfer_Dt BETWEEN ci.start_dt AND ci.end_dt)
ORDER BY
sct.transfer_dt;
SELECT sut.unit_cd
FROM IGS_PS_STDNT_UNT_TRN sut,
IGS_CA_INST ci
WHERE sut.person_id = p_person_id AND
sut.course_cd = p_course_cd AND
sut.transfer_course_cd = cp_from_course_cd AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
(sut.transfer_dt BETWEEN ci.start_dt AND ci.end_dt);
SELECT 'INTERMIT' rec_type,
sci.person_id,
sci.course_cd,
sci.start_dt start_dt,
sci.end_dt end_dt,
ci.alternate_code,
NULL n1,
SYSDATE ,
NULL n2,
NULL n3
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_CA_INST ci
WHERE sci.person_id = p_person_id AND
sci.course_cd = p_course_cd AND
sci.end_dt IS NOT NULL AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
((sci.start_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(sci.start_dt > ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)))
UNION
SELECT 'LAPSED' rec_type,
sca.person_id,
sca.course_cd,
sca.lapsed_dt,
SYSDATE,
ci.alternate_code,
NULL n4,
SYSDATE,
NULL n5,
NULL n6
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_CA_INST ci
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.lapsed_dt IS NOT NULL AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
((sca.lapsed_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(sca.lapsed_dt > ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)))
UNION
SELECT 'DISCONTIN' rec_type,
sca.person_id,
sca.course_cd,
sca.discontinued_dt,
SYSDATE,
ci.alternate_code,
NULL n7,
SYSDATE,
NULL n8,
NULL n9
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_CA_INST ci
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.discontinued_dt IS NOT NULL AND
ci.CAL_TYPE = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number AND
((sca.discontinued_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(sca.discontinued_dt > ci.start_dt AND SUBSTR(IGS_GE_DATE.IGSCHAR(ci.start_dt),1,4) =
IGS_AS_GEN_005.ASSP_VAL_SCA_FINAL (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind)))
ORDER BY 4;
SELECT suav.person_id,
igs_ca_gen_001.calp_get_alt_cd
(suav.cal_type,
suav.ci_sequence_number
) teach_alternate_code,
suav.unit_cd, NVL (suav.alternative_title, uv.title) v_unit_title,
NVL (suav.alternative_title, uv.short_title) v_unit_short_title,
uv.unit_level, suav.cal_type, suav.ci_sequence_number, suav.uoo_id,
suav.ci_start_dt,
NVL (suav.override_achievable_cp,
NVL (cps.achievable_credit_points, uv.achievable_credit_points)
) v_cp_achieved,
NVL (suav.override_enrolled_cp,
NVL (cps.enrolled_credit_points, uv.enrolled_credit_points)
) v_cp_achievable,
suav.unit_attempt_status, suav.administrative_unit_status
FROM igs_en_su_attempt_all suav,
igs_ps_usec_cps cps,
igs_ps_unit_ver uv,
igs_en_stdnt_ps_att sca
WHERE suav.person_id = p_person_id
AND suav.course_cd = p_course_cd
AND sca.person_id = suav.person_id
AND sca.course_cd = suav.course_cd
AND uv.unit_cd = suav.unit_cd
AND uv.version_number = suav.version_number
AND suav.uoo_id = cps.uoo_id(+)
AND suav.unit_attempt_status NOT IN ('UNCONFIRM', 'DUPLICATE')
AND igs_as_gen_001.assp_val_sua_display (suav.person_id,
suav.course_cd,
sca.version_number,
suav.unit_cd,
suav.cal_type,
suav.ci_sequence_number,
suav.unit_attempt_status,
suav.administrative_unit_status,
'Y',
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
suav.uoo_id
) = 'Y'
AND igs_en_gen_014.enrs_get_within_ci (p_acad_cal_type,
p_acad_ci_sequence_number,
suav.cal_type,
suav.ci_sequence_number,
'Y'
) = 'Y'
ORDER BY suav.ci_start_dt, suav.unit_cd;
SELECT 'x'
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER cv
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
cv.course_cd = sca.course_cd AND
cv.version_number = sca.version_number AND
cv.generic_course_ind = 'N';
SELECT 'x'
FROM IGS_PS_STDNT_UNT_TRN sut
WHERE sut.person_id = p_person_id AND
sut.transfer_course_cd = p_course_cd AND
-- anilk, 22-Apr-2003, Bug# 2829262
sut.uoo_id = p_uoo_id;
SELECT asule.person_id,
asule.UNIT_LEVEL,
asule.credit_points
FROM IGS_AV_STND_UNIT_LVL asule
WHERE asule.person_id = p_person_id AND
asule.as_course_cd = p_course_cd AND
asule.s_adv_stnd_granting_status = 'GRANTED'
ORDER BY
asule.UNIT_LEVEL;
SELECT SUBSTR(IGS_GE_DATE.IGSCHAR(start_dt),1,4)
FROM IGS_CA_INST
WHERE CAL_TYPE = p_acad_cal_type AND
sequence_number = p_acad_ci_sequence_number;
SELECT 'x'
FROM dual
WHERE cp_year <= IGS_AS_GEN_005.ASSP_VAL_SCA_COMM (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind);
SELECT asu.person_id,as_course_cd,as_version_number,
asu.unit_cd,asu.version_number,
uv.short_title,
uv.TITLE,
uv.UNIT_LEVEL
FROM IGS_AV_STND_UNIT asu,
IGS_PS_UNIT_VER uv
WHERE asu.person_id = p_person_id AND
asu.as_course_cd = p_course_cd AND
asu.s_adv_stnd_granting_status = 'GRANTED' AND
asu.s_adv_stnd_recognition_type = 'CREDIT' AND
(igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,
asu.unit_cd,asu.version_number,'GRANTED',NULL) ='TRUE') AND
asu.unit_cd = uv.unit_cd AND
asu.version_number = uv.version_number
GROUP BY person_id,as_course_cd,as_version_number,asu.unit_cd,asu.version_number,uv.short_title,
uv.title,uv.unit_level
ORDER BY
asu.unit_cd,
asu.version_number;
SELECT SUBSTR(IGS_GE_DATE.IGSCHAR(start_dt),1,4)
FROM IGS_CA_INST
WHERE CAL_TYPE = p_acad_cal_type AND
sequence_number = p_acad_ci_sequence_number;
SELECT 'x'
FROM dual
WHERE cp_year <= IGS_AS_GEN_005.ASSP_VAL_SCA_COMM (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind);
SELECT adv.total_exmptn_granted
FROM IGS_AV_ADV_STANDING adv
WHERE adv.person_id = p_person_id AND
adv.course_cd = p_course_cd AND
adv.total_exmptn_granted > 0 AND
(EXISTS (SELECT 'x'
FROM IGS_AV_STND_UNIT asu
WHERE asu.person_id = adv.person_id AND
asu.as_course_cd = adv.course_cd AND
asu.s_adv_stnd_granting_status = 'GRANTED' AND
asu.s_adv_stnd_recognition_type = 'CREDIT' AND
(igs_av_val_asu.granted_adv_standing(person_id,as_course_cd,as_version_number,
unit_cd,version_number,'GRANTED',NULL) ='TRUE')) OR
EXISTS (SELECT 'x'
FROM IGS_AV_STND_UNIT_LVL asule
WHERE asule.person_id = adv.person_id AND
asule.as_course_cd = adv.course_cd AND
asule.s_adv_stnd_granting_status = 'GRANTED'));
SELECT SUBSTR(IGS_GE_DATE.IGSCHAR(start_dt),1,4)
FROM IGS_CA_INST
WHERE CAL_TYPE = p_acad_cal_type AND
sequence_number = p_acad_ci_sequence_number;
SELECT 'x'
FROM dual
WHERE cp_year <= IGS_AS_GEN_005.ASSP_VAL_SCA_COMM (
p_person_id,
p_course_cd,
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
p_include_related_crs_ind);
SELECT sca.person_id,
suav.acad_alternate_code,
sca.course_cd,
cv.TITLE,
ci.CAL_TYPE,
ci.sequence_number,
ci.start_dt,
cop.attendance_type,
cop.location_cd,
sca.commencement_dt,
cv.generic_course_ind,
-- anilk, 22-Apr-2003, Bug# 2829262
suav.uoo_id
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_EN_SUA_V suav,
IGS_PS_VER cv,
IGS_PS_OFR_PAT cop,
IGS_CA_INST ci
WHERE sca.person_id = p_person_id AND
sca.person_id = suav.person_id AND
sca.course_cd = suav.course_cd AND
(sca.course_cd = NVL(p_course_cd, suav.course_cd) OR
(p_course_cd IS NULL OR
(p_course_cd IS NOT NULL AND
p_include_related_crs_ind = 'N' AND
sca.course_cd = p_course_cd) OR
(p_course_cd IS NOT NULL AND
p_include_related_crs_ind = 'Y' AND
sca.course_cd IN (SELECT cgm.course_cd
FROM IGS_PS_GRP_MBR cgm,
IGS_PS_GRP cg,
IGS_PS_GRP_TYPE cgt
WHERE cgm.course_group_cd = cg.course_group_cd
AND cg.course_group_type = cgt.course_group_type
AND cgt.s_course_group_type = 'RELATED'
AND p_course_cd IN (SELECT cgm1.course_cd
FROM IGS_PS_GRP_MBR cgm1,
IGS_PS_GRP cg1,
IGS_PS_GRP_TYPE cgt1
WHERE cgm1.course_group_cd = cg1.course_group_cd
AND cg1.course_group_type = cgt1.course_group_type
AND cgt1.s_course_group_type = 'RELATED'))))) AND
EXISTS( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = suav.person_id AND
sua.course_cd = suav.course_cd AND
-- anilk, 22-Apr-2003, Bug# 2829262
sua.uoo_id = suav.uoo_id AND
IGS_AS_GEN_001.ASSP_VAL_SUA_DISPLAY(
sua.person_id,
sua.course_cd,
sca.version_number,
sua.unit_cd,
sua.CAL_TYPE,
sua.ci_sequence_number,
sua.unit_attempt_status,
sua.administrative_unit_status,
'Y',
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
-- anilk, 22-Apr-2003, Bug# 2829262
sua.uoo_id ) = 'Y') AND
cv.course_cd = sca.course_cd AND
cv.version_number = sca.version_number AND
sca.coo_id = cop.coo_id AND
sca.location_cd = cop.location_cd AND
sca.attendance_mode = cop.attendance_mode AND
sca.attendance_type = cop.attendance_type AND
cop.CAL_TYPE = ci.CAL_TYPE AND
cop.ci_sequence_number = ci.sequence_number AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
cop.CAL_TYPE,
cop.ci_sequence_number,
suav.CAL_TYPE,
suav.ci_sequence_number,
'Y') = 'Y'
GROUP BY
sca.person_id,
suav.acad_alternate_code,
sca.course_cd,
cv.TITLE,
ci.CAL_TYPE,
ci.sequence_number,
ci.start_dt,
cop.attendance_type,
cop.location_cd,
sca.commencement_dt,
cv.generic_course_ind
ORDER BY
sca.course_cd,
ci.start_dt;
SELECT sca.person_id,
suav.acad_alternate_code,
sca.course_cd,
cv.TITLE,
ci.CAL_TYPE,
ci.sequence_number,
ci.start_dt,
cop.attendance_type,
cop.location_cd,
sca.commencement_dt,
cv.generic_course_ind,
-- anilk, 22-Apr-2003, Bug# 2829262
suav.uoo_id
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_EN_SUA_V suav,
IGS_PS_VER cv,
IGS_PS_OFR_PAT cop,
IGS_CA_INST ci
WHERE sca.person_id = p_person_id AND
sca.person_id = suav.person_id AND
-- sca.course_cd = NVL(p_course_cd, suav.course_cd) AND
sca.course_cd = suav.course_cd AND
(sca.course_cd = NVL(p_course_cd, suav.course_cd) OR
(p_course_cd IS NULL OR
(p_course_cd IS NOT NULL AND
p_include_related_crs_ind = 'N' AND
sca.course_cd = p_course_cd) OR
(p_course_cd IS NOT NULL AND
p_include_related_crs_ind = 'Y' AND
sca.course_cd IN (SELECT cgm.course_cd
FROM IGS_PS_GRP_MBR cgm,
IGS_PS_GRP cg,
IGS_PS_GRP_TYPE cgt
WHERE cgm.course_group_cd = cg.course_group_cd
AND cg.course_group_type = cgt.course_group_type
AND cgt.s_course_group_type = 'RELATED'
AND p_course_cd IN (SELECT cgm1.course_cd
FROM IGS_PS_GRP_MBR cgm1,
IGS_PS_GRP cg1,
IGS_PS_GRP_TYPE cgt1
WHERE cgm1.course_group_cd = cg1.course_group_cd
AND cg1.course_group_type = cgt1.course_group_type
AND cgt1.s_course_group_type = 'RELATED'))))) AND
EXISTS( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = suav.person_id AND
sua.course_cd = suav.course_cd AND
-- anilk, 22-Apr-2003, Bug# 2829262
sua.uoo_id = suav.uoo_id AND
IGS_AS_GEN_001.ASSP_VAL_SUA_DISPLAY(
sua.person_id,
sua.course_cd,
sca.version_number,
sua.unit_cd,
sua.CAL_TYPE,
sua.ci_sequence_number,
sua.unit_attempt_status,
sua.administrative_unit_status,
'Y', -- finalised indicator
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
-- anilk, 22-Apr-2003, Bug# 2829262
sua.uoo_id ) = 'Y') AND
cv.course_cd = sca.course_cd AND
cv.version_number = sca.version_number AND
sca.coo_id = cop.coo_id AND
sca.location_cd = cop.location_cd AND
sca.attendance_mode = cop.attendance_mode AND
sca.attendance_type = cop.attendance_type AND
cop.CAL_TYPE = ci.CAL_TYPE AND
cop.ci_sequence_number = ci.sequence_number AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
cop.CAL_TYPE,
cop.ci_sequence_number,
suav.CAL_TYPE,
suav.ci_sequence_number,
'Y') = 'Y'
GROUP BY
sca.person_id,
suav.acad_alternate_code,
sca.course_cd,
cv.TITLE,
ci.CAL_TYPE,
ci.sequence_number,
ci.start_dt,
cop.attendance_type,
cop.location_cd,
sca.commencement_dt,
cv.generic_course_ind
ORDER BY
ci.start_dt,
sca.course_cd;
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_attempt_status IN ( 'ENROLLED',
'COMPLETED',
'DUPLICATE') OR
(sua.unit_attempt_status = 'DISCONTIN' AND
IGS_AS_GEN_003.ASSP_GET_TRN_SUA_OUT(
p_person_id,
p_course_cd,
sua.unit_cd,
sua.CAL_TYPE,
sua.ci_sequence_number,
sua.unit_attempt_status,
'Y',
-- anilk, 22-Apr-2003, Bug# 2829262
sua.uoo_id) = 'FAIL')) AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
p_acad_cal_type,
p_acad_ci_sequence_number,
sua.CAL_TYPE,
sua.ci_sequence_number,
'Y') = 'Y' AND
NOT EXISTS ( SELECT 'x'
FROM IGS_PS_STDNT_UNT_TRN sut
WHERE sut.person_id = sua.person_id AND
sut.transfer_course_cd = sua.course_cd AND
-- anilk, 22-Apr-2003, Bug# 2829262
sut.uoo_id = sua.uoo_id);