[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(ass.field_number) field, ass.association_code, ass.oss_seq, ass.hesa_seq
FROM igs_he_usr_rtn_clas urc,
igs_he_usr_rt_cl_fld fld,
igs_he_sys_rt_cl_ass ass
WHERE urc.user_return_subclass = p_user_return_subclass
AND fld.user_return_subclass = urc.user_return_subclass
AND ass.system_return_class_type = urc.system_return_class_type
AND fld.field_number = ass.field_number
AND fld.include_flag = 'Y'
AND ass.oss_seq IS NOT NULL
AND ass.hesa_seq IS NOT NULL
GROUP BY ass.association_code, ass.oss_seq, ass.hesa_seq
ORDER BY field;
SELECT ass.field_number
FROM igs_he_usr_rtn_clas urc,
igs_he_usr_rt_cl_fld fld,
igs_he_sys_rt_cl_ass ass
WHERE urc.user_return_subclass = p_user_return_subclass
AND fld.user_return_subclass = urc.user_return_subclass
AND ass.system_return_class_type = urc.system_return_class_type
AND fld.field_number = ass.field_number
AND fld.include_flag = 'Y'
AND ass.association_code = cp_association_code
ORDER BY ass.field_number;
SELECT association_type, main_source, secondary_source, condition, display_title
FROM igs_he_code_ass_val
WHERE association_code = cp_association_code
AND sequence = cp_sequence;
l_stmt := ' SELECT DISTINCT value' ||
' FROM igs_he_code_values ' ||
' WHERE value NOT IN ';
l_stmt := ' SELECT DISTINCT ' || l_assoc_type.secondary_source ||
' FROM ' || l_assoc_type.main_source ||
' WHERE ' || l_assoc_type.secondary_source || ' NOT IN ';
l_stmt := l_stmt || ' (SELECT map' || l_assoc.oss_seq ||
' FROM igs_he_code_map_val ' ||
' WHERE association_code = :ASS_CODE)';
INSERT INTO IGS_HE_VERIFY_DATA_T (association_code, fields_affected, display_title, oss_value, creation_date, created_by, last_update_date, last_updated_by)
VALUES (l_assoc.association_code, l_affected_fields, l_assoc_type.display_title, l_unmapped_value, sysdate, -1, sysdate, -1);
SELECT DECODE(count(award_cd), 0, 'N', 'Y')
FROM igs_ps_award
WHERE course_cd = cp_course_cd
AND version_number = cp_version_number
AND closed_ind = 'N';
SELECT DISTINCT sca.person_id,
pe.party_number person_number,
sca.course_cd,
sca.version_number,
sca.location_cd ,
sca.attendance_mode,
sca.attendance_type,
sca.cal_type sca_cal_type,
sca.commencement_dt ,
sca.discontinued_dt,
sca.course_rqrmnts_complete_dt,
sca.course_attempt_status,
hspa.student_inst_number,
DECODE(hspa.hesa_st_spa_id, NULL, 'N', 'Y') spa_flag,
DECODE(hspa.student_inst_number, NULL, 'N', 'Y') sin_flag,
DECODE(hspa.student_qual_aim, NULL, 'N', 'Y') spa_qualaim_flag,
enawd.complete_ind,
enawd.conferral_date
FROM igs_en_stdnt_ps_att_all sca,
igs_he_st_spa_all hspa,
igs_he_st_prog_all hprog,
igs_en_spa_awd_aim enawd,
hz_parties pe
WHERE sca.person_id = hspa.person_id (+)
AND sca.course_cd = hspa.course_cd (+)
AND sca.course_cd = hprog.course_cd (+)
AND sca.version_number = hprog.version_number (+)
AND NVL(hprog.exclude_flag, 'N') = 'N'
AND NVL(hspa.exclude_flag, 'N') = 'N'
AND NVL(sca.future_dated_trans_flag,'N') IN ('N','S')
AND sca.student_confirmed_ind = 'Y'
AND hspa.person_id = enawd.person_id(+)
AND hspa.course_cd = enawd.course_cd(+)
AND sca.person_id = pe.party_id
AND ( ( sca.commencement_dt <= g_he_submsn_header.enrolment_end_date
AND ( sca.discontinued_dt IS NULL OR sca.discontinued_dt >= g_he_submsn_header.enrolment_start_date )
AND (sca.course_rqrmnts_complete_dt IS NULL OR
sca.course_rqrmnts_complete_dt >= g_he_submsn_header.enrolment_start_date
)
)
OR -- Added for HE309
-- check whether award conferral dates are defined first at program level
-- or program type level, otherwise hesa submission reporting periods
enawd.complete_ind = 'Y' AND
(enawd.conferral_date BETWEEN cp_awd_conf_start_dt AND cp_awd_conf_end_dt)
)
ORDER BY sca.person_id, hspa.student_inst_number, discontinued_dt DESC,
course_rqrmnts_complete_dt DESC, sca.commencement_dt DESC ;
SELECT DISTINCT
susa.unit_set_cd,
susa.us_version_number,
DECODE(husa.hesa_en_susa_id, NULL, 'N','Y') susa_flag
FROM igs_as_su_setatmpt susa,
igs_he_en_susa husa,
igs_en_unit_set us,
igs_en_unit_set_cat susc,
igs_en_spa_awd_aim enawd,
igs_en_stdnt_ps_att_all sca
WHERE susa.person_id = sca.person_id
AND susa.course_cd = sca.course_cd
AND sca.person_id = enawd.person_id(+)
AND sca.course_cd = enawd.course_cd(+)
AND susa.unit_set_cd = husa.unit_set_cd(+)
AND susa.us_version_number = husa.us_version_number(+)
AND susa.person_id = husa.person_id(+)
AND susa.course_cd = husa.course_cd(+)
AND susa.sequence_number = husa.sequence_number(+)
AND susa.unit_set_cd = us.unit_set_cd
AND susa.us_version_number = us.version_number
AND us.unit_set_cat = susc.unit_set_cat
AND susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susc.s_unit_set_cat = 'PRENRL_YR'
-- the program attempt is overlapping with the submmission period and the yop is also overlapping with the submission period
AND ( ( sca.commencement_dt <= cp_enrl_end_dt AND
(sca.discontinued_dt IS NULL OR sca.discontinued_dt >= cp_enrl_start_dt ) AND
(sca.course_rqrmnts_complete_dt IS NULL OR sca.course_rqrmnts_complete_dt >= cp_enrl_start_dt ) AND
susa.selection_dt <= cp_enrl_end_dt AND
(susa.end_dt IS NULL OR susa.end_dt >= cp_enrl_start_dt ) AND
(susa.rqrmnts_complete_dt IS NULL OR susa.rqrmnts_complete_dt >= cp_enrl_start_dt)
)
OR
-- jchin bug 5213152
-- the program attempt is completed before the submmission period start and award is conferred in the submission period and
-- the yop is completed before the award conferral date
( susa.rqrmnts_complete_dt < cp_enrl_start_dt AND
sca.course_rqrmnts_complete_dt <= cp_enrl_end_dt AND
enawd.complete_ind = 'Y' AND
enawd.conferral_date BETWEEN cp_awd_conf_start_dt AND cp_awd_conf_end_dt
)
) ;
SELECT DECODE(count(course_cd), 0, 'N', 'Y') prog_flag
FROM igs_he_st_prog_all
WHERE course_cd = cp_course_cd
AND version_number = cp_version_number;
SELECT DECODE(count(crv_version_number), 0, 'N', 'Y') poous_flag
FROM igs_he_poous_all
WHERE crv_version_number = cp_crv_version_number
AND course_cd = cp_course_cd
AND cal_type = cp_cal_type
AND location_cd = cp_location_cd
AND attendance_mode = cp_attendance_mode
AND attendance_type = cp_attendance_type
AND unit_set_cd = cp_unit_set_cd
AND us_version_number = cp_us_version_number;
SELECT course_type
FROM igs_ps_ver_all
WHERE course_cd = cp_course_cd
AND version_number = cp_version_number;
SELECT api_person_id,person_id_type, LENGTH(api_person_id) api_length
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type IN ('HUSID', 'UCASID', 'GTTRID', 'NMASID', 'SWASID')
AND Start_Dt <= cp_enrl_end_dt
AND (End_Dt IS NULL OR End_Dt >= cp_enrl_start_dt )
AND (End_Dt IS NULL OR Start_Dt <> End_Dt)
ORDER BY person_id_type, Start_Dt DESC ;
SELECT 'X'
FROM igs_en_su_attempt_all
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_attempt_status IN ('ENROLLED', 'COMPLETED','DISCONTIN','DUPLICATE')
AND enrolled_dt <= p_enrolment_end_date;
INSERT INTO IGS_HE_VERIFY_DATA_T(person_id, creation_date, created_by, last_update_date, last_updated_by)
VALUES (l_std_inst.person_id, sysdate, 1, sysdate, 1);
INSERT INTO IGS_HE_VERIFY_DATA_T
(person_id,
course_cd,
crv_version_number,
cal_type,
location_cd,
attendance_mode,
attendance_type,
unit_set_cd,
us_version_number,
spa_flag,
qualaim_flag,
sin_flag,
susa_flag,
poous_flag,
prog_flag,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES
(l_verify_data.person_id,
l_verify_data.course_cd,
l_verify_data.crv_version_number,
l_verify_data.cal_type,
l_verify_data.location_cd,
l_verify_data.attendance_mode,
l_verify_data.attendance_type,
l_verify_data.unit_set_cd,
l_verify_data.us_version_number,
l_verify_data.spa_flag,
l_verify_data.qualaim_flag,
l_verify_data.sin_flag,
l_verify_data.susa_flag,
l_verify_data.poous_flag,
l_verify_data.prog_flag,
sysdate,
1,
sysdate,
1);
INSERT INTO IGS_HE_VERIFY_DATA_T
(unit_cd, u_version_number, unit_flag, creation_date, created_by, last_update_date, last_updated_by)
(SELECT DISTINCT
ua.unit_cd,
ua.version_number,
DECODE(hunt.hesa_st_unt_vs_id, NULL, 'N', 'Y') unit_flag, sysdate, 1, sysdate, 1
FROM igs_en_su_attempt_all ua,
igs_he_verify_data_t t,
igs_he_st_unt_vs_all hunt
WHERE t.person_id = ua.person_id
AND ua.unit_cd = hunt.unit_cd (+)
AND ua.version_number = hunt.version_number(+)
AND ua.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND ua.ci_start_dt BETWEEN g_he_submsn_header.enrolment_start_date AND g_he_submsn_header.enrolment_end_date
AND hunt.hesa_st_unt_vs_id IS NULL -- only get modules with no HESA details
UNION
SELECT DISTINCT
ua.unit_cd,
ua.version_number,
DECODE(hunt.hesa_st_unt_vs_id, NULL, 'N', 'Y') unit_flag, sysdate, 1, sysdate, 1
FROM igs_en_su_attempt_all ua,
igs_he_verify_data_t t,
igs_he_st_unt_vs_all hunt,
igs_as_su_stmptout_all uao
WHERE t.person_id = ua.person_id
AND ua.unit_cd = hunt.unit_cd (+)
AND ua.version_number = hunt.version_number(+)
AND uao.person_id = ua.person_id
AND uao.course_cd = ua.course_cd
AND uao.uoo_id = ua.uoo_id
AND uao.finalised_outcome_ind = 'Y'
AND ua.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
AND uao.outcome_dt BETWEEN g_he_submsn_header.enrolment_start_date AND g_he_submsn_header.enrolment_end_date
AND hunt.hesa_st_unt_vs_id IS NULL -- only get modules with no HESA details
);
DELETE FROM IGS_HE_VERIFY_DATA_T
WHERE person_id IS NOT NULL;
SELECT COUNT(DISTINCT unit_cd || u_version_number)
INTO l_count
FROM igs_he_verify_data_t
WHERE unit_flag = 'N';
SELECT COUNT(DISTINCT course_cd || crv_version_number)
INTO l_count
FROM igs_he_verify_data_t
WHERE prog_flag = 'N';
SELECT COUNT(DISTINCT course_cd || crv_version_number || cal_type || location_cd || attendance_mode || attendance_type || unit_set_cd || us_version_number)
INTO l_count
FROM igs_he_verify_data_t
WHERE poous_flag = 'N';
SELECT COUNT(DISTINCT person_id || course_cd)
INTO l_count
FROM igs_he_verify_data_t
WHERE spa_flag = 'N' OR qualaim_flag = 'N' OR sin_flag = 'N';
SELECT COUNT(DISTINCT person_id || course_cd || unit_set_cd || us_version_number)
INTO l_count
FROM igs_he_verify_data_t
WHERE susa_flag = 'N';
SELECT rtn.submission_name,
rtn.user_return_subclass,
rtn.return_name,
rtn.record_id,
shd.enrolment_start_date,
shd.enrolment_end_date,
shd.offset_days,
NVL(shd.apply_to_atmpt_st_dt,'N') apply_to_atmpt_st_dt
FROM igs_he_submsn_header shd,
igs_he_submsn_return rtn
WHERE rtn.sub_rtn_id = p_sub_rtn_id
AND rtn.submission_name = shd.submission_name;
DELETE FROM IGS_HE_VERIFY_DATA_T;