[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_version_number AND
uv.research_unit_ind <> 'N';
SELECT ca.sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
(ca.sca_course_cd IS NOT NULL AND
ca.sca_course_cd = p_course_cd);
SELECT rsup.start_dt
FROM IGS_RE_SPRVSR rsup
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = cp_ca_sequence_number AND
rsup.start_dt <= v_teach_end_dt AND
(rsup.end_dt IS NULL OR
rsup.end_dt >= v_teach_start_dt)
ORDER BY rsup.start_dt ASC;
SELECT commencement_dt from igs_en_stdnt_ps_att_all
WHERE person_id = p_person_id
AND course_cd = p_course_cd;
SELECT SUM(NVL(rsup.supervision_percentage, 0)),
SUM(NVL(rsup.funding_percentage, 0))
FROM IGS_RE_SPRVSR rsup
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = pl_ca_sequence_number AND
rsup.start_dt <= pl_check_dt AND
(rsup.end_dt IS NULL OR
rsup.end_dt >= pl_check_dt);
SELECT IGS_EN_GEN_003.Get_Staff_Ind( rsup.person_id) person_type
FROM IGS_RE_SPRVSR rsup
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = pl_ca_sequence_number AND
rsup.start_dt <= pl_check_dt AND
(rsup.end_dt IS NULL OR
rsup.end_dt >= pl_check_dt);
SELECT 'x'
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
(ca.sca_course_cd IS NULL OR
ca.sca_course_cd = p_course_cd);
SELECT DISTINCT cir.sup_cal_type acad_cal_type, cir.sup_ci_sequence_number acad_ci_sequence_number
FROM igs_ca_inst_rel cir, igs_en_su_attempt_all sua
WHERE sua.cal_type = cir.sub_cal_type
AND sua.ci_sequence_number = cir.sub_ci_sequence_number
AND sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND cir.sup_cal_type = NVL(p_acad_cal_type,cir.sup_cal_type)
AND cir.sup_ci_sequence_number = NVL(p_acad_ci_sequence_number,cir.sup_ci_sequence_number);
SELECT
sua2.cal_type sua_cal_type,
sua2.ci_sequence_number sua_ci_sequence_number,
sua2.discontinued_dt,
sua2.administrative_unit_status,
sua2.unit_attempt_status,
sua2.no_assessment_ind,
loadcal.cal_type ci_cal_type,
loadcal.sequence_number ci_sequence_number
FROM
IGS_EN_SU_ATTEMPT_all sua1,
IGS_EN_SU_ATTEMPT_all sua2 ,
IGS_PS_UNIT_VER_ALL uv1,
IGS_PS_UNIT_VER_ALL uv2,
IGS_CA_INST_ALL loadcal,
IGS_CA_INST_REL acadterm,
IGS_CA_TYPE cat2,
IGS_CA_STAT cs2 ,
IGS_ST_DFT_LOAD_APPO l2t,
IGS_ST_DFT_LOAD_APPO l2tsua2
WHERE sua1.person_id = p_person_id
AND sua1.course_cd = p_course_cd
AND sua1.unit_attempt_status = cst_enrolled
AND sua2.uoo_id <> sua1.uoo_id
AND sua1.person_id = sua2.person_id
AND sua1.course_cd = sua2.course_cd
AND sua2.unit_attempt_status IN (cst_enrolled,cst_completed,cst_discontin)
AND uv1.unit_cd = sua1.unit_cd
AND uv1.version_number = sua1.version_number
AND uv2.unit_cd = sua2.unit_cd
AND uv2.version_number =sua2.version_number
AND uv1.research_unit_ind = 'Y'
AND uv2.research_unit_ind = 'Y'
AND EXISTS (SELECT 'x' FROM IGS_CA_INST_REL acadteach
WHERE acadteach.sup_cal_type = cp_acad_cal_type
AND acadteach.sup_ci_sequence_number = cp_acad_seq_num
AND acadteach.sub_cal_type = sua1.cal_type
AND acadteach.sub_ci_sequence_number = sua1.ci_sequence_number)
AND EXISTS (SELECT 'x' FROM IGS_CA_INST_REL acadteach
WHERE acadteach.sup_cal_type = cp_acad_cal_type
AND acadteach.sup_ci_sequence_number = cp_acad_seq_num
AND acadteach.sub_cal_type = sua2.cal_type
AND acadteach.sub_ci_sequence_number = sua2.ci_sequence_number)
AND acadterm.sup_cal_type = cp_acad_cal_type
AND acadterm.sup_ci_sequence_number = cp_acad_seq_num
AND loadcal.cal_type = acadterm.sub_cal_type
AND loadcal.sequence_number = acadterm.sub_ci_sequence_number
AND cat2.cal_type = loadcal.cal_type
AND cat2.s_cal_cat = cst_load
-- Check they are active
AND cs2.cal_status = loadcal.cal_status
AND cs2.s_cal_status = cst_active
AND l2t.cal_type = loadcal.cal_type
AND l2t.ci_sequence_number = loadcal.sequence_number
AND l2t.teach_cal_type = sua1.cal_type
AND l2tsua2.cal_type = l2t.cal_type
AND l2tsua2.ci_sequence_number = l2t.ci_sequence_number
AND l2tsua2.teach_cal_type = sua2.cal_type;
SELECT daiv.alias_val,
ci.start_dt,
ci.end_dt
FROM IGS_CA_DA_INST_V daiv,
IGS_CA_INST ci,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = p_cal_type AND
daiv.ci_sequence_number = p_ci_sequence_number AND
daiv.dt_alias = sgcc.census_dt_alias AND
sgcc.s_control_num = 1 AND
daiv.cal_type = ci.cal_type AND
daiv.ci_sequence_number = ci.sequence_number;
FUNCTION enrp_val_sua_update(
p_person_id IN NUMBER ,
p_course_cd IN VARCHAR2 ,
p_unit_cd IN VARCHAR2 ,
p_cal_type IN VARCHAR2 ,
p_ci_sequence_number IN NUMBER ,
p_unit_attempt_status IN VARCHAR2 ,
p_new_version_number IN NUMBER ,
p_new_location_cd IN VARCHAR2 ,
p_new_unit_class IN VARCHAR2 ,
p_new_enrolled_dt IN DATE ,
p_new_discontinued_dt IN DATE ,
p_new_admin_unit_status IN VARCHAR2 ,
p_new_rule_waived_dt IN DATE ,
p_new_rule_waived_person_id IN NUMBER ,
p_new_no_assessment_ind IN VARCHAR2 ,
p_new_sup_unit_cd IN VARCHAR2 ,
p_new_sup_version_number IN NUMBER ,
p_new_exam_location_cd IN VARCHAR2 ,
p_old_version_number IN NUMBER ,
p_old_location_cd IN VARCHAR2 ,
p_old_unit_class IN VARCHAR2 ,
p_old_enrolled_dt IN DATE ,
p_old_discontinued_dt IN DATE ,
p_old_admin_unit_status IN VARCHAR2 ,
p_old_rule_waived_dt IN DATE ,
p_old_rule_waived_person_id IN NUMBER ,
p_old_no_assessment_ind IN VARCHAR2 ,
p_old_sup_unit_cd IN VARCHAR2 ,
p_old_sup_version_number IN NUMBER ,
p_old_exam_location_cd IN VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2,
p_uoo_id IN NUMBER)
RETURN BOOLEAN AS
-------------------------------------------------------------------------------------------
--Change History:
--Who When What
--kkillams 28-04-2003 Added new parameter p_uoo_id to the function
-- Modified the c_sut cursor where clause due to change in pk
-- of student unit attempt table w.r.t. bug number 2829262
-- ckasu 21-Dec-2004 modified procedure inorder to as a part of bug# 4080883
-- ctyagi 29-Sept-2005 modified cursor c_sut for bug# 4524765
-------------------------------------------------------------------------------------------
BEGIN -- enrp_val_sua_update
-- This modules validates the update of IGS_EN_SU_ATTEMPT in relation to
-- unit_attempt_status.
DECLARE
CURSOR c_sut IS
SELECT 'X'
FROM IGS_PS_STDNT_UNT_TRN sut1
WHERE
sut1.person_id = p_person_id AND
sut1.transfer_course_cd = p_course_cd AND
sut1.uoo_id = p_uoo_id and
sut1.transfer_dt = ( SELECT max(sut2.transfer_dt)
FROM IGS_PS_STDNT_UNT_TRN sut2
where sut2.person_id = sut1.person_id
and sut2.transfer_course_cd = sut1.transfer_course_cd
and sut2.uoo_id = sut1.uoo_id)
and sut1.transfer_dt > (SELECT NVL(max(sut3.transfer_dt),(sut1.transfer_dt-1))
FROM IGS_PS_STDNT_UNT_TRN sut3
where sut3.person_id = sut1.person_id
and sut3.course_cd = sut1.transfer_course_cd
and sut3.uoo_id = sut1.uoo_id);
SELECT sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT sua.unit_attempt_status
FROM igs_en_su_attempt sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_update');
END enrp_val_sua_update;
SELECT ci.end_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_cal_type AND
ci.sequence_number = p_ci_sequence_number;
SELECT sca.commencement_dt
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT title_override_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_version_number;
SELECT 'x'
FROM IGS_AV_STND_UNIT asu
WHERE asu.person_id = p_person_id AND
asu.as_course_cd = p_course_cd AND
asu.unit_cd = p_unit_cd AND
((asu.s_adv_stnd_recognition_type = cst_credit AND
igs_av_val_asu.granted_adv_standing(p_person_id,p_course_cd,NULL,
p_unit_cd,NULL,'BOTH',NULL) ='TRUE')
OR
(asu.s_adv_stnd_granting_status IN (cst_approved,cst_granted) AND
asu.s_adv_stnd_recognition_type = cst_preclusion));
SELECT sca.version_number
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd;
SELECT unit_cd,version_number,s_adv_stnd_recognition_type
FROM IGS_AV_STND_UNIT asu
WHERE asu.person_id = cp_person_id AND
asu.as_course_cd = cp_course_cd AND
asu.as_version_number = cp_crs_vers_num AND
asu.unit_cd = cp_unit_cd AND
asu.version_number = cp_un_vers_num AND
asu.s_adv_stnd_recognition_type IN (cst_credit,
cst_preclusion) AND
asu.s_adv_stnd_granting_status IN (cst_approved, cst_granted)
GROUP BY unit_cd,version_number,s_adv_stnd_recognition_type;
SELECT repeatable_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = cp_unit_cd AND
uv.version_number = cp_version_number;
SELECT sgcc.census_dt_alias
FROM IGS_GE_S_GEN_CAL_CON sgcc
WHERE sgcc.s_control_num = 1;
SELECT sci.start_dt,
sci.end_dt,
sci.cond_return_flag , sci.logical_delete_date
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = cp_person_id AND
sci.course_cd = cp_course_cd 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');
cp_logical_del_dt IGS_EN_STDNT_PS_INTM.logical_delete_date%TYPE) IS
SELECT daiv.dt_alias
FROM IGS_CA_DA_INST_V daiv
WHERE daiv.cal_type = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_seq_num AND
daiv.dt_alias = cp_census_dt AND
daiv.alias_val >= cp_start_dt AND
(daiv.alias_val <= cp_end_dt OR
(daiv.alias_val > cp_end_dt AND
cp_cond_ret_ind = 'Y' AND
EXISTS ( SELECT 'x'
FROM IGS_EN_SPI_RCONDS
WHERE person_id =p_person_id
AND course_cd =p_course_cd
AND start_dt =cp_start_dt
AND logical_delete_date =cp_logical_del_dt
AND status_code IN('FAILED','PENDING')
)
)
);
v_sci_details.logical_delete_date) LOOP
-- set that a record was found
v_rec_found := TRUE;
Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
SELECT closed_ind,
unit_attempt_status
FROM IGS_AD_ADM_UNIT_STAT
WHERE administrative_unit_status = p_administrative_unit_status;
SELECT DISTINCT gs.grading_schema_cd,
gs.version_number
FROM IGS_AS_GRD_SCHEMA gs,
IGS_AS_GRD_SCH_GRADE gsg,
IGS_AD_ADM_UT_STA_GD ausg
WHERE gs.start_dt <= TRUNC(p_effective_dt) AND
(gs.end_dt IS NULL OR
gs.end_dt >= TRUNC(p_effective_dt)) AND
gsg.grading_schema_cd = gs.grading_schema_cd AND
gsg.version_number = gs.version_number AND
ausg.grade = gsg.grade AND
ausg.administrative_unit_status = p_administrative_unit_status;
SELECT unit_attempt_status
FROM IGS_AD_ADM_UNIT_STAT
WHERE administrative_unit_status = p_administrative_unit_status;
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id;
SELECT 'X'
FROM IGS_AD_ADM_UT_STA_GD ausg
WHERE ausg.administrative_unit_status = cp_admin_unit_status;
SELECT COUNT(*)
FROM igs_ad_adm_ut_sta_gd ausg,
igs_as_grd_schema gs,
igs_as_grd_sch_grade gsg
WHERE ausg.administrative_unit_status = cp_admin_unit_status AND
gs.grading_schema_cd = ausg.grading_schema_cd AND
gs.version_number = ausg.version_number AND
TRUNC(gs.start_dt) <= TRUNC(cp_effective_dt) AND
TRUNC(NVL(gs.end_dt, cp_effective_dt)) >= TRUNC(cp_effective_dt) AND
ausg.grading_schema_cd = gsg.grading_schema_cd AND
ausg.version_number = gsg.version_number AND
ausg.GRADE = gsg.grade AND
(
( gsg.s_result_type = 'AUDIT' AND cp_no_assessment_ind ='Y')
OR
( gsg.s_result_type <> 'AUDIT' AND cp_no_assessment_ind <> 'Y')
);
SELECT ausg.grading_schema_cd,
ausg.version_number,
ausg.grade
FROM igs_ad_adm_ut_sta_gd ausg,
igs_as_grd_schema gs,
igs_as_grd_sch_grade gsg
WHERE ausg.administrative_unit_status = cp_admin_unit_status AND
gs.grading_schema_cd = ausg.grading_schema_cd AND
gs.version_number = ausg.version_number AND
gs.start_dt <= TRUNC(cp_effective_dt) AND
NVL(gs.end_dt, TRUNC(cp_effective_dt)) >= TRUNC(cp_effective_dt) AND
ausg.grading_schema_cd = gsg.grading_schema_cd AND
ausg.version_number = gsg.version_number AND
ausg.GRADE = gsg.grade AND
(
( gsg.s_result_type = 'AUDIT' AND cp_no_assessment_ind ='Y')
OR
( gsg.s_result_type <> 'AUDIT' AND cp_no_assessment_ind <> 'Y')
);
SELECT ausg.grading_schema_cd,
ausg.version_number,
ausg.grade
FROM IGS_EN_SU_ATTEMPT sua,
IGS_PS_UNIT_OFR_OPT uoo,
IGS_AD_ADM_UT_STA_GD ausg,
IGS_AS_GRD_SCHEMA gs,
igs_as_grd_sch_grade gsg
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id AND
sua.unit_cd = uoo.unit_cd AND
sua.version_number = uoo.version_number AND
sua.cal_type = uoo.cal_type AND
sua.ci_sequence_number = uoo.ci_sequence_number AND
sua.location_cd = uoo.location_cd AND
sua.unit_class = uoo.unit_class AND
uoo.grading_schema_cd = ausg.grading_schema_cd AND
ausg.administrative_unit_status = cp_admin_unit_status AND
gs.grading_schema_cd = ausg.grading_schema_cd AND
gs.version_number = ausg.version_number AND
gs.start_dt <= cp_effective_dt AND
NVL(gs.end_dt, cp_effective_dt) >= cp_effective_dt AND
ausg.grading_schema_cd = gsg.grading_schema_cd AND
ausg.version_number = gsg.version_number AND
ausg.GRADE = gsg.grade AND
(
( gsg.s_result_type = 'AUDIT' AND sua.no_assessment_ind ='Y')
OR
( gsg.s_result_type <> 'AUDIT' AND sua.no_assessment_ind <> 'Y')
);
SELECT unit_attempt_status
FROM IGS_AD_ADM_UNIT_STAT
WHERE administrative_unit_status = p_administrative_unit_status;
SELECT us.s_unit_status
FROM IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_STAT us
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_version_number AND
us.unit_status = uv.unit_status;
SELECT course_attempt_status
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT *
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd;
FUNCTION enrp_val_sua_delete(
p_person_id IN NUMBER ,
p_course_cd IN VARCHAR2 ,
p_unit_cd IN VARCHAR2 ,
p_form_trigger_ind IN VARCHAR2 ,
p_unit_attempt_status IN VARCHAR2 ,
p_cal_type IN VARCHAR2 ,
p_ci_sequence_number IN NUMBER ,
p_discontinued_dt IN DATE ,
p_effective_dt IN DATE ,
p_message_name OUT NOCOPY VARCHAR2,
p_uoo_id IN NUMBER)
-------------------------------------------------------------------------------------------
--Change History:
--Who When What
--kkillams 28-04-2003 Removed the cur_uoo_IGS_EN_SU_ATTEMPT cursor and it's references
-- and modified the c_sut cursor where clause w.r.t. bug number 2829262
-------------------------------------------------------------------------------------------
RETURN BOOLEAN AS
gv_other_details VARCHAR2(255);
BEGIN -- enrp_val_sua_delete
-- Validate the deletion of a IGS_EN_SU_ATTEMPT
DECLARE
cst_duplicate IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'DUPLICATE';
SELECT course_rqrmnt_complete_ind
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT 'x'
FROM IGS_PS_STDNT_UNT_TRN
WHERE person_id = p_person_id AND
transfer_course_cd = p_course_cd AND
uoo_id = p_uoo_id;
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_delete');
END enrp_val_sua_delete;
FUNCTION enrp_val_sua_insert(
p_person_id IN NUMBER ,
p_course_cd IN VARCHAR2 ,
p_unit_attempt_status IN VARCHAR2 ,
p_message_name OUT NOCOPY varchar2 )
RETURN BOOLEAN AS
gv_other_details VARCHAR2(255);
SELECT sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
p_message_name := 'IGS_EN_UNCONF_SUA_INSERTED';
END enrp_val_sua_insert;
SELECT sca.version_number,
sca.coo_id,
sca.commencement_dt
FROM IGS_EN_STDNT_PS_ATT sca
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = p_unit_cd
AND version_number = p_uv_version_number
AND cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND location_cd = p_location_cd
AND unit_class = p_unit_class;
IF NOT IGS_EN_VAL_SUA.enrp_val_sua_insert(
p_person_id,
p_course_cd,
cst_enrolled,
v_message_name) THEN
p_fail_type := cst_course;
SELECT cal_type,ci_sequence_number
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
uoo_id = cp_uoo_id;
SELECT load_cal_type,load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = cp_teach_cal_type AND
teach_ci_sequence_number = cp_teach_cal_seq_num ;
SELECT uv.points_override_ind,
uv.points_min,
uv.points_max,
uv.points_increment
FROM IGS_PS_UNIT_VER uv
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number;
SELECT usv.minimum_credit_points,
usv.maximum_credit_points,
usv.variable_increment
FROM IGS_PS_USEC_CPS usv
WHERE usv.uoo_id = p_uoo_id;
SELECT IGS_PS_UNIT_STAT.s_unit_status
FROM IGS_PS_UNIT_VER,
IGS_PS_UNIT_STAT
WHERE IGS_PS_UNIT_VER.unit_cd = p_unit_cd AND
IGS_PS_UNIT_VER.version_number = p_version_number AND
IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;
SELECT offered_ind
FROM IGS_PS_UNIT_OFR_OPT
WHERE unit_cd = p_unit_cd AND
version_number = p_version_number AND
cal_type = p_cal_type AND
ci_sequence_number = p_ci_sequence_number AND
location_cd = p_location_cd AND
unit_class = p_unit_class;
SELECT *
FROM IGS_PS_OFR_OPT
WHERE IGS_PS_OFR_OPT.coo_id = cp_coo_id
AND IGS_PS_OFR_OPT.delete_flag = 'N';
SELECT *
FROM IGS_PS_OFR_OPT
WHERE IGS_PS_OFR_OPT.coo_id = cp_coo_id
AND IGS_PS_OFR_OPT.delete_flag = 'N';
SELECT unit_mode
FROM IGS_AS_UNIT_CLASS
WHERE IGS_AS_UNIT_CLASS.unit_class = cp_unit_class AND IGS_AS_UNIT_CLASS.closed_ind = 'N';
SELECT s_unit_mode
FROM IGS_AS_UNIT_MODE
WHERE IGS_AS_UNIT_MODE.unit_mode = cp_unit_mode;
SELECT govt_attendance_mode
FROM IGS_EN_ATD_MODE
WHERE IGS_EN_ATD_MODE.attendance_mode = cp_attend_mode;
SELECT UNIQUE(daiv.alias_val) alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.dt_alias = sgcc.census_dt_alias AND
sgcc.s_control_num = 1;
SELECT sua.course_cd,
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.unit_cd = p_unit_cd AND
sua.version_number = p_uv_version_number AND
sua.location_cd = cp_location_cd AND
sua.unit_class = cp_unit_class AND
(sua.course_cd <> p_course_cd OR
sua.cal_type <> p_cal_type OR
sua.ci_sequence_number <> p_ci_sequence_number) AND
sua.unit_attempt_status IN (cst_enrolled,
cst_completed,
cst_discontin);
SELECT sua.location_cd,
sua.unit_class
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
SELECT 'x'
FROM IGS_PS_STDNT_TRN sct
WHERE sct.person_id = p_person_id AND
sct.course_cd = p_course_cd AND
sct.transfer_course_cd = cp_course_cd;
SELECT uv.repeatable_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_uv_version_number;
SELECT 1 FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = p_uoo_id
AND relation_type = 'SUPERIOR' ;
SELECT * FROM IGS_PS_UNIT_OFR_OPT
WHERE sup_uoo_id = p_uoo_id AND
default_enroll_flag = 'Y' AND
relation_type = 'SUBORDINATE';
SELECT party_number from hz_parties
WHERE party_id = p_person_id;
sub_stmt := 'SELECT uoo_id FROM IGS_PS_UNIT_OFR_OPT WHERE sup_uoo_id = :p_uoo_id AND
relation_type = ''SUBORDINATE'' AND uoo_id in ('||p_enr_uoo_ids||')';
sub_stmt1 := 'SELECT uoo_id FROM IGS_PS_UNIT_OFR_OPT WHERE sup_uoo_id = :p_uoo_id AND
relation_type = ''SUBORDINATE'' AND uoo_id = :1';
igs_ss_en_wrappers.insert_into_enr_worksheet(
p_person_number =>l_person_number,
p_course_cd => p_course_cd,
p_uoo_id => sub_usecs_rec.uoo_id,
p_waitlist_ind => l_waitlist_ind,
p_session_id => NULL,
p_return_status => l_return_status,
p_message => l_err_message,
p_cal_type => p_load_cal_type,
p_ci_sequence_number => p_load_seq_num,
p_audit_requested => 'N',
p_enr_method => p_enrollment_method,
p_override_cp => null,
p_subtitle => null,
p_gradsch_cd => null,
p_gs_version_num => null,
p_calling_obj =>'JOB'
);
SELECT 1 FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = p_uoo_id
AND relation_type = 'SUPERIOR' ;
SELECT sua.uoo_id sub_uoo_id
FROM IGS_EN_SU_ATTEMPT sua, IGS_PS_UNIT_OFR_OPT uoo
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = uoo.uoo_id AND
uoo.sup_uoo_id = p_uoo_id AND
uoo.relation_type = 'SUBORDINATE' ;
SELECT sca.coo_id
FROM igs_en_stdnt_ps_att sca
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd;
SELECT same_teaching_period
FROM igs_ps_unit_ver uv
WHERE unit_cd = cp_unit_cd AND
uv.version_number =cp_version_number;
SELECT 'x'
FROM igs_en_su_attempt
WHERE person_id=cp_person_id AND
course_cd=cp_course_cd AND
unit_cd=cp_unit_cd AND
cal_type=cp_cal_type AND
ci_sequence_number=cp_ci_sequence_number AND
unit_attempt_status NOT IN ('DROPPED','DISCONTIN') AND
uoo_id<> cp_uoo_id;
SELECT unit_cd,version_number,cal_type,ci_sequence_number,not_multiple_section_flag
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id;
SELECT 'x'
FROM igs_en_su_attempt sua,
igs_ps_unit_ofr_opt opt
WHERE sua.person_id=cp_person_id AND
sua.course_cd=cp_course_cd AND
sua.unit_cd=cp_unit_cd AND
sua.cal_type=cp_cal_type AND
sua.ci_sequence_number = cp_ci_sequence_number AND
unit_attempt_status NOT IN ('DROPPED','DISCONTIN') AND
sua.uoo_id<> cp_uoo_id AND
sua.uoo_id=opt.uoo_id AND
opt.not_multiple_section_flag=cp_not_multiple_section_flag;