[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT sys_date_type,date_alias
FROM
(SELECT sys_date_type,date_alias
FROM igs_ca_Da_configs
WHERE (res_cal_cat1 = cp_cal_cat OR
res_cal_cat2 = cp_cal_cat) AND
one_per_cal_flag = 'Y'
UNION ALL
SELECT a.sys_date_type,b.date_alias
FROM igs_ca_Da_ovd_vals b,igs_ca_Da_configs a
WHERE a.sys_date_type = b.sys_date_type
AND a.one_per_cal_flag = 'Y'
AND ( a.res_cal_cat1 =cp_cal_cat OR
a.res_cal_cat2 =cp_cal_cat)
);
SELECT distinct sys_date_type,date_alias
FROM
(SELECT sys_date_type,date_alias
FROM igs_ca_Da_configs
WHERE one_per_cal_flag = 'Y'
UNION ALL
SELECT a.sys_date_type,b.date_alias
FROM igs_ca_Da_ovd_vals b,
igs_ca_Da_configs a
WHERE a.sys_date_type = b.sys_date_type
AND a.one_per_cal_flag = 'Y'
);
SELECT description
FROM igs_ca_inst
WHERE cal_type = cp_cal_type AND
sequence_number = cp_seq_num;
SELECT count(*)
FROM igs_ca_da_inst
WHERE dt_alias = cp_dt_alias AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_seq_num;
SELECT srcc.effective_strt_dt_alias,
srcc.effective_end_dt_alias
FROM IGS_RE_S_RES_CAL_CON srcc
WHERE s_control_num = 1;
SELECT dai.CAL_TYPE,
cat.S_CAL_CAT
FROM IGS_CA_DA_INST dai,
IGS_CA_TYPE cat
WHERE dai.DT_ALIAS IN (
cp_effective_strt_dt_alias,
cp_effective_end_dt_alias) AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
p_acad_cal_type,
p_acad_sequence_number,
dai.CAL_TYPE,
dai.ci_sequence_number, 'N') = 'Y' AND
cat.CAL_TYPE = dai.CAL_TYPE AND
cat.S_CAL_CAT <> cst_teaching;
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_STAT cs,
IGS_CA_TYPE cat
WHERE IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number, 'Y') = 'Y' AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status IN (cst_active,cst_planned) AND
cat.CAL_TYPE = ci.CAL_TYPE AND
cat.S_CAL_CAT = cst_teaching;
SELECT NVL (
dai.absolute_val,
IGS_CA_GEN_001.CALP_GET_ALIAS_VAL (
dai.DT_ALIAS,
dai.sequence_number,
dai.CAL_TYPE,
dai.ci_sequence_number)) AS alias_val,
dai.DT_ALIAS
FROM IGS_CA_DA_INST dai
WHERE dai.CAL_TYPE = cp_cal_type AND
dai.ci_sequence_number = cp_sequence_number AND
dai.DT_ALIAS IN (
cp_effective_strt_dt_alias,
cp_effective_end_dt_alias)
ORDER BY alias_val ASC;
SELECT count(*)
FROM IGS_GE_S_GEN_CAL_CON sgcc,
IGS_CA_DA_INST dai
WHERE dai.CAL_TYPE = cp_cal_type AND
dai.ci_sequence_number = cp_sequence_number AND
dai.DT_ALIAS = sgcc.census_dt_alias AND
sgcc.s_control_num = 1;
SELECT uop.unit_cd
FROM IGS_PS_UNIT_OFR_PAT uop,
IGS_PS_UNIT_VER uv
WHERE uop.CAL_TYPE = cp_cal_type AND
uop.ci_sequence_number = cp_sequence_number AND
uv.unit_cd = uop.unit_cd AND
uv.version_number = uop.version_number AND
uv.research_unit_ind = 'Y' AND
uop.delete_flag = 'N';
SELECT NVL (
dai.absolute_val,
IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
dai.DT_ALIAS,
dai.sequence_number,
dai.CAL_TYPE,
dai.ci_sequence_number)) AS alias_val,
dai.DT_ALIAS,
dai.CAL_TYPE,
dai.ci_sequence_number
FROM IGS_CA_DA_INST dai,
IGS_CA_INST ci,
IGS_CA_STAT cs,
IGS_CA_TYPE cat
WHERE dai.DT_ALIAS IN (
cp_effective_strt_dt_alias,
cp_effective_end_dt_alias) AND
ci.CAL_TYPE = dai.CAL_TYPE AND
ci.sequence_number = dai.ci_sequence_number AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status IN (cst_active,cst_planned) AND
cat.CAL_TYPE = ci.CAL_TYPE AND
cat.S_CAL_CAT = cst_teaching
ORDER BY ci.start_dt ASC,
ci.end_dt ASC,
DECODE (
dai.DT_ALIAS, cp_effective_strt_dt_alias, 1,
cp_effective_end_dt_alias, 2);
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat,
IGS_CA_INST ci,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
cat.CAL_TYPE = cir.sub_cal_type AND
cat.S_CAL_CAT = cst_load AND
ci.CAL_TYPE = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status IN (cst_active,cst_planned);
SELECT 'X'
FROM IGS_ST_DFT_LOAD_APPO dla,
IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_STAT cs
WHERE dla.CAL_TYPE = cp_cal_type AND
dla.ci_sequence_number = cp_sequence_number AND
cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
cir.sub_cal_type = dla.teach_cal_type AND
ci.CAL_TYPE = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status IN (cst_active,cst_planned) AND
EXISTS (
SELECT 'X'
FROM IGS_RE_S_RES_CAL_CON srcc,
IGS_CA_DA_INST dai
WHERE srcc.s_control_num = 1 AND
dai.CAL_TYPE = ci.CAL_TYPE AND
dai.ci_sequence_number = ci.sequence_number AND
dai.DT_ALIAS IN (
srcc.effective_strt_dt_alias,
srcc.effective_end_dt_alias));
SELECT SUM(NVL(cir.load_research_percentage, 0))
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_STAT cs,
IGS_CA_TYPE cat
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
ci.CAL_TYPE = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status IN (cst_active,cst_planned) AND
cat.CAL_TYPE = ci.CAL_TYPE AND
cat.S_CAL_CAT = cst_load;
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_STAT cs,
IGS_CA_TYPE cat
WHERE IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number, 'N') = 'Y' AND
cat.S_CAL_CAT = cst_admission AND
cs.s_cal_status IN (cst_active, cst_planned) AND
ci.CAL_TYPE = cat.CAL_TYPE AND
ci.CAL_STATUS = cs.CAL_STATUS;
SELECT cir.sup_cal_type,
cir.sup_ci_sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = cp_sub_cal_type AND
cir.sub_ci_sequence_number = cp_sub_sequence_number AND
cat.S_CAL_CAT = cst_academic AND
cir.sup_cal_type = cat.CAL_TYPE;
SELECT cir2.sub_cal_type,
cir2.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir2,
IGS_CA_TYPE cat
WHERE cir2.sup_cal_type = cp_sub_cal_type AND
cir2.sup_ci_sequence_number = cp_sub_sequence_number AND
cat.S_CAL_CAT = cst_enrolment AND
cir2.sub_cal_type = cat.CAL_TYPE;
SELECT 'x'
FROM IGS_CA_INST_REL cir
WHERE cir.sub_cal_type = cp_sub_cal_type AND
cir.sub_ci_sequence_number = cp_sub_sequence_number AND
cir.sup_cal_type = cp_acad_cal_type AND
cir.sup_ci_sequence_number = cp_acad_sequence_number;
SELECT 'x'
FROM IGS_CA_INST_REL cir
WHERE cir.sub_cal_type = cp_sub_cal_type AND
cir.sub_ci_sequence_number = cp_sub_sequence_number AND
cir.sup_cal_type = cp_acad_cal_type AND
cir.sup_ci_sequence_number <> cp_acad_sequence_number;
SELECT cir.sup_cal_type,
cir.sup_ci_sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = cp_sub_cal_type AND
cir.sub_ci_sequence_number = cp_sub_sequence_number AND
cat.S_CAL_CAT = cst_teaching AND
cir.sup_cal_type = cat.CAL_TYPE;
SELECT 'x'
FROM IGS_AD_PERD_AD_CAT apac
WHERE apac.adm_cal_type = cp_adm_cal_type AND
apac.adm_ci_sequence_number = cp_adm_ci_sequence_number;
SELECT 'x'
FROM IGS_CA_DA_INST_V daiv,
IGS_AD_CAL_CONF sacc
WHERE daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.DT_ALIAS = sacc.initialise_adm_perd_dt_alias AND
sacc.s_control_num = 1;
SELECT 'x'
FROM IGS_CA_DA_INST_V daiv,
IGS_AD_CAL_CONF sacc
WHERE daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.DT_ALIAS = sacc.adm_appl_encmb_chk_dt_alias AND
sacc.s_control_num = 1;
SELECT 'x'
FROM IGS_CA_DA_INST_V daiv,
IGS_AD_CAL_CONF sacc
WHERE daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.DT_ALIAS = sacc.adm_appl_course_strt_dt_alias AND
sacc.s_control_num = 1;
SELECT COUNT(*)
FROM IGS_CA_DA_INST_V daiv,
IGS_AD_CAL_CONF sacc
WHERE daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.DT_ALIAS = sacc.adm_appl_due_dt_alias AND
sacc.s_control_num = 1 AND
NOT EXISTS (
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT apcood
WHERE apcood.adm_cal_type = cp_cal_type AND
apcood.adm_ci_sequence_number = cp_ci_sequence_number AND
apcood.DT_ALIAS = daiv.CAL_TYPE AND
apcood.dai_sequence_number = daiv.sequence_number);
SELECT COUNT(*)
FROM IGS_CA_DA_INST_V daiv,
IGS_AD_CAL_CONF sacc
WHERE daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.DT_ALIAS = sacc.adm_appl_final_dt_alias AND
sacc.s_control_num = 1 AND
NOT EXISTS (
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT apcood
WHERE apcood.adm_cal_type = cp_cal_type AND
apcood.adm_ci_sequence_number = cp_ci_sequence_number AND
apcood.DT_ALIAS = daiv.CAL_TYPE AND
apcood.dai_sequence_number = daiv.sequence_number);
SELECT COUNT(*)
FROM IGS_CA_DA_INST_V daiv,
IGS_AD_CAL_CONF sacc
WHERE daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.DT_ALIAS = sacc.adm_appl_offer_resp_dt_alias AND
sacc.s_control_num = 1 AND
NOT EXISTS (
SELECT 'x'
FROM IGS_AD_PECRS_OFOP_DT apcood
WHERE apcood.adm_cal_type = cp_cal_type AND
apcood.adm_ci_sequence_number = cp_ci_sequence_number AND
apcood.DT_ALIAS = daiv.CAL_TYPE AND
apcood.dai_sequence_number = daiv.sequence_number);
SELECT cir5.sub_cal_type,
cir5.sub_ci_sequence_number
FROM igs_ca_inst_rel cir5,
igs_ca_type cat
WHERE cir5.sup_cal_type = cp_c_sup_cal_type
AND cir5.sup_ci_sequence_number = cp_n_sup_sequence_number
AND cat.s_cal_cat = cst_load
AND cir5.sub_cal_type = cat.cal_type;
SELECT count(car.sub_ci_sequence_number)
FROM igs_ca_inst_rel car,
igs_ca_inst_all ca,
igs_ca_type cat
WHERE
car.SUP_CI_SEQUENCE_NUMBER= p_sequence_number AND
car.SUP_CAL_type = p_cal_type AND
ca.cal_type = car.sub_cal_type AND
ca.sequence_number = car.sub_ci_sequence_number AND
ca.cal_type = cat.cal_type AND
cat.s_cal_cat = 'LOAD' ;
-- Select admission periods
FOR v_cict_rec IN c_cict LOOP
-- Check that all admission period have at least one direct superior
-- academic period.
v_cal_type := v_cict_rec.CAL_TYPE;
SELECT load_effect_dt_alias,
commencement_dt_alias,
commence_cutoff_dt_alias,
enr_form_due_dt_alias,
enr_pckg_prod_dt_alias,
effect_enr_strt_dt_alias
FROM IGS_EN_CAL_CONF
WHERE s_control_num = 1;
SELECT 'x'
FROM IGS_CA_DA_INST_V daiv,
IGS_CA_TYPE ct
WHERE daiv.DT_ALIAS = cp_dt_alias AND
daiv.CAL_TYPE = ct.CAL_TYPE AND
ct.S_CAL_CAT <> cp_s_cal_cat;
SELECT 'x'
FROM IGS_CA_DA_INST_V daiv,
IGS_CA_TYPE ct
WHERE daiv.DT_ALIAS = cp_dt_alias AND
daiv.CAL_TYPE = ct.CAL_TYPE AND
ct.S_CAL_CAT <> cp_s_cal_cat AND
ct.S_CAL_CAT <> cp_s_cal_cat1;
SELECT ci.cal_type, ci.sequence_number, ct.S_CAL_CAT
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_STAT cs
WHERE IGS_EN_GEN_014.ENRS_GET_WITHIN_CI (
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number, 'N') = 'Y' AND
cs.s_cal_status IN (cst_active,cst_planned) AND
ct.S_CAL_CAT = cst_enrolment AND
cs.CAL_STATUS = ci.CAL_STATUS AND
ct.CAL_TYPE = ci.CAL_TYPE;
SELECT cir.sup_cal_type, cir.sup_ci_sequence_number, ct2.S_CAL_CAT
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_TYPE ct2,
IGS_CA_STAT cs,
IGS_CA_INST_REL cir
WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
ct.S_CAL_CAT = cst_enrolment AND
ci.CAL_TYPE = ct.CAL_TYPE AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cir.sup_cal_type = cp_cal_type AND
cir.sup_ci_sequence_number = cp_sequence_number AND
ct2.CAL_TYPE = cir.sub_cal_type;
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cs.s_cal_status IN ('ACTIVE','PLANNED') AND
cat.S_CAL_CAT = 'LOAD' AND
cat.CAL_TYPE = ci.CAL_TYPE AND
cs.CAL_STATUS = ci.CAL_STATUS;
SELECT cat.S_CAL_CAT
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = cp_sub_cal_type AND
cir.sub_ci_sequence_number = cp_sub_ci_sequence_number AND
ci.CAL_TYPE = cir.sup_cal_type AND
ci.sequence_number = cir.sup_ci_sequence_number AND
cat.CAL_TYPE = ci.CAL_TYPE;
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cs.s_cal_status IN ('ACTIVE','PLANNED') AND
cat.S_CAL_CAT = 'LOAD' AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cat.CAL_TYPE = ci.CAL_TYPE AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number,
'N') = 'Y';
SELECT ci.alternate_code,ci.description,ci.start_dt,ci.end_dt
FROM IGS_CA_INST ci,
IGS_CA_INST ci2,
IGS_CA_TYPE cat,
IGS_CA_STAT cs,
IGS_CA_INST_REL car
WHERE
ci2.cal_type = cp_cal_type AND
ci2.sequence_number = cp_seq_num AND
cs.s_cal_status IN ('ACTIVE','PLANNED') AND
cat.S_CAL_CAT = 'LOAD' AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cat.CAL_TYPE = ci.CAL_TYPE AND
car.SUP_CAL_TYPE = p_acad_cal_type AND
car.SUP_CI_SEQUENCE_NUMBER = p_acad_sequence_number AND
car.SUB_CAL_TYPE = ci.CAL_TYPE AND
car.SUB_CI_SEQUENCE_NUMBER = ci.sequence_number AND
(ci.rowid <> ci2.rowid) AND
(
(ci2.start_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(ci2.end_dt BETWEEN ci.start_dt AND ci.end_dt) OR
(ci2.start_dt <= ci.start_dt AND ci2.end_dt >= ci.end_dt)
);
SELECT cir.sub_cal_type
FROM IGS_CA_INST_REL cir
WHERE cir.sup_cal_type = cp_cal_type AND
cir.sup_ci_sequence_number = cp_sequence_number;
SELECT cir.sub_cal_type
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = cp_cal_type AND
cir.sub_ci_sequence_number = cp_sequence_number AND
(cir.sup_cal_type <> p_acad_cal_type OR
cir.sup_ci_sequence_number <> p_acad_sequence_number) AND
cat.CAL_TYPE = cir.sup_cal_type AND
cat.S_CAL_CAT = 'ACADEMIC';
SELECT count(*)
FROM IGS_EN_CAL_CONF secc,
IGS_CA_DA_INST dai
WHERE secc.s_control_num = 1 AND
dai.CAL_TYPE = cp_cal_type AND
dai.ci_sequence_number = cp_sequence_number AND
dai.DT_ALIAS = secc.load_effect_dt_alias;
SELECT dla.teach_cal_type,
dla.second_percentage
FROM IGS_ST_DFT_LOAD_APPO dla
WHERE dla.CAL_TYPE = cp_cal_type AND
dla.ci_sequence_number = cp_sequence_number;
SELECT count(*)
FROM IGS_CA_INST ci,
IGS_CA_STAT cs
WHERE CAL_TYPE = cp_teach_cal_type AND
cs.s_cal_status IN ('ACTIVE','PLANNED') AND
ci.CAL_STATUS = cs.CAL_STATUS AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number,
'N') = 'Y';
SELECT att.ATTENDANCE_TYPE
FROM IGS_EN_ATD_TYPE att
WHERE att.closed_ind = 'N' AND
((att.lower_enr_load_range IS NOT NULL AND
att.lower_enr_load_range <> 0) OR
(att.upper_enr_load_range IS NOT NULL AND
att.upper_enr_load_range <> 0));
SELECT atl.CAL_TYPE -- not used
FROM IGS_EN_ATD_TYPE_LOAD atl
WHERE atl.ATTENDANCE_TYPE = cp_attendance_type AND
atl.CAL_TYPE = cp_cal_type;
SELECT atl.lower_enr_load_range,
atl.upper_enr_load_range
FROM IGS_EN_ATD_TYPE_LOAD atl,
IGS_EN_ATD_TYPE att
WHERE atl.CAL_TYPE = cp_cal_type AND
att.closed_ind = 'N' AND
att.ATTENDANCE_TYPE = atl.ATTENDANCE_TYPE
ORDER BY att.lower_enr_load_range;
SELECT cir6.sup_cal_type,
cir6.sup_ci_sequence_number
FROM igs_ca_inst_rel cir6,
igs_ca_type cat
WHERE cir6.sub_cal_type = cp_c_sub_cal_type
AND cir6.sub_ci_sequence_number = cp_n_sub_sequence_number
AND cat.S_CAL_CAT = 'AWARD'
AND cir6.sup_cal_type = cat.cal_type;
SELECT cir9.sup_cal_type,
cir9.sup_ci_sequence_number
FROM igs_ca_inst_rel cir9,
igs_ca_type cat
WHERE cir9.sub_cal_type = cp_c_sub_cal_type
AND cir9.sub_ci_sequence_number = cp_n_sub_sequence_number
AND cat.S_CAL_CAT = 'ADMISSION'
AND cir9.sup_cal_type = cat.cal_type;
SELECT start_dt,
end_dt
FROM IGS_CA_INST
WHERE CAL_TYPE = cp_acad_cal_type AND
sequence_number = cp_acad_sequence_number;
SELECT cir.sub_cal_type, cir.sub_ci_sequence_number, ct1.S_CAL_CAT
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct1,
IGS_CA_TYPE ct2,
IGS_CA_STAT cs,
IGS_CA_INST_REL cir
WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
ct2.S_CAL_CAT = cst_teaching AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cir.sub_cal_type = ci.CAL_TYPE AND
cir.sub_ci_sequence_number = ci.sequence_number AND
cir.sup_cal_type = ct1.CAL_TYPE AND
ct2.CAL_TYPE = ci.CAL_TYPE AND
ct1.S_CAL_CAT NOT IN ( cst_academic ) AND
(IGS_EN_GEN_014.ENRS_GET_WITHIN_CI( p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number,
'Y') = 'Y' OR
NOT EXISTS (
SELECT 'x'
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = ci.CAL_TYPE AND
cir.sub_ci_sequence_number = ci.sequence_number AND
cir.sup_cal_type = ci.CAL_TYPE AND
cir.sup_ci_sequence_number = ci.sequence_number AND
ci.CAL_TYPE = cat.CAL_TYPE AND
cat.S_CAL_CAT = 'ACADEMIC' ));
SELECT ci.CAL_TYPE,
ci.sequence_number,
cs.s_cal_status -- Bug:2697221 cal status selected to check whether
-- the status is planned or active for calculating load apportionment
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_STAT cs
WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
cs.CAL_STATUS = ci.CAL_STATUS AND
ct.S_CAL_CAT = cst_teaching AND
ct.CAL_TYPE = ci.CAL_TYPE AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number,
'Y' ) = 'Y';
SELECT cir.sub_cal_type,
cat2.S_CAL_CAT
FROM IGS_CA_INST_REL cir,
IGS_CA_TYPE cat1,
IGS_CA_TYPE cat2
WHERE cir.sup_cal_type = cat1.CAL_TYPE AND
cir.sub_cal_type = cat2.CAL_TYPE AND
cir.sup_cal_type = cp_cal_type AND
cir.sup_ci_sequence_number = cp_sequence_number;
SELECT 'x'
FROM dual
WHERE EXISTS (
SELECT *
FROM IGS_GE_S_GEN_CAL_CON sgcc,
IGS_CA_DA_INST_V daiv
WHERE sgcc.s_control_num = 1 AND
daiv.CAL_TYPE = cp_cal_type AND
daiv.ci_sequence_number = cp_sequence_number AND
daiv.DT_ALIAS = sgcc.census_dt_alias AND
daiv.alias_val BETWEEN cp_acad_start_dt AND cp_acad_end_dt);
SELECT ci.*
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_STAT cs
WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
ct.S_CAL_CAT = cst_academic AND
ci.CAL_TYPE = ct.CAL_TYPE AND
cs.CAL_STATUS = ci.CAL_STATUS AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
ci.CAL_TYPE,
ci.sequence_number,
cp_cal_type,
cp_sequence_number,
'Y' ) = 'Y';
SELECT dla.*
FROM IGS_ST_DFT_LOAD_APPO dla
WHERE teach_cal_type = cp_teach_cal_type AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
cp_acad_cal_type,
cp_acad_ci_sequence_number,
dla.CAL_TYPE,
dla.ci_sequence_number,
'Y' ) = 'Y';
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_STAT cs
WHERE cs.s_cal_status IN (cst_active,cst_planned) AND
ci.CAL_STATUS = cs.CAL_STATUS AND
ct.S_CAL_CAT = cst_teaching AND
ct.CAL_TYPE = ci.CAL_TYPE AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number,
'Y') = 'Y'; --'N'; ssawhney. this should never be N.
SELECT DISTINCT ADMINISTRATIVE_UNIT_STATUS
FROM IGS_CA_DA_INST_V daiv,
IGS_PS_UNIT_DISC_CRT uddc
WHERE daiv.CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_sequence_number AND
daiv.DT_ALIAS = uddc.unit_discont_dt_alias AND
uddc.delete_ind = 'N';
SELECT ci.CAL_TYPE,
ci.sequence_number
FROM IGS_ST_DFT_LOAD_APPO dla,
IGS_CA_INST ci
WHERE teach_cal_type = cp_cal_type AND
ci.CAL_TYPE = dla.CAL_TYPE AND
ci.sequence_number = dla.ci_sequence_number AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
p_acad_cal_type,
p_acad_sequence_number,
ci.CAL_TYPE,
ci.sequence_number,
'N') = 'Y';
SELECT 'x'
FROM dual
WHERE EXISTS (
SELECT *
FROM IGS_AD_ADM_UT_STT_LD
WHERE ADMINISTRATIVE_UNIT_STATUS = cp_uddc_aus AND
CAL_TYPE = cp_load_ci_cal_type AND
ci_sequence_number = cp_load_ci_sequence_number AND
teach_cal_type = cp_teach_ci_cal_type);
-- Select teaching calendars which have subordinates of any type
FOR v_chk_tch_sub_rec IN c_chk_tch_sub(
v_chk_teach_ci_rec.CAL_TYPE,
v_chk_teach_ci_rec.sequence_number ) LOOP
v_chk_tch_sub_rec_found := TRUE;
SELECT ci.cal_type,
ci.sequence_number,
ci.start_dt,
ci.end_dt
FROM igs_ca_inst ci
WHERE ci.cal_type = p_c_acad_cal_type
AND ci.sequence_number = p_n_acad_sequence_number;
SELECT ci.cal_type,
ci.sequence_number
FROM igs_ca_inst ci,
igs_ca_stat cs,
igs_ca_type cat
WHERE cat.s_cal_cat = 'AWARD'
AND cs.s_cal_status IN ('ACTIVE','PLANNED')
AND ci.cal_type = cat.cal_type
AND ci.cal_status = cs.cal_status
AND (TRUNC(ci.start_dt) >= TRUNC(p_d_start_dt) OR
TRUNC(ci.end_dt) <= TRUNC(p_d_end_dt)) ;
SELECT cir8.sub_cal_type,
cir8.sub_ci_sequence_number
FROM igs_ca_inst_rel cir8,
igs_ca_type cat
WHERE cir8.sup_cal_type = cp_c_sup_cal_type
AND cir8.sup_ci_sequence_number = cp_n_sup_sequence_number
AND cat.s_cal_cat = 'LOAD'
AND cir8.sub_cal_type = cat.cal_type;