[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sua.discontinued_dt,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.no_assessment_ind
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 lci.cal_type,
lci.sequence_number
FROM IGS_CA_INST_REL acir,
IGS_CA_INST aci,
IGS_CA_TYPE acat,
IGS_CA_STAT acs,
IGS_CA_INST_REL lcir,
IGS_CA_INST lci,
IGS_CA_TYPE lcat,
IGS_CA_STAT lcs
WHERE acir.sub_cal_type = p_teach_cal_type AND
acir.sub_ci_sequence_number = p_teach_sequence_number AND
aci.cal_type = acir.sup_cal_type AND
aci.sequence_number = acir.sup_ci_sequence_number AND
acat.cal_type = aci.cal_type AND
acat.s_cal_cat = cst_academic AND
acs.cal_status = aci.cal_status AND
acs.s_cal_status = cst_active AND
lcir.sup_cal_type = aci.cal_type AND
lcir.sup_ci_sequence_number = aci.sequence_number AND
lci.cal_type = lcir.sub_cal_type AND
lci.sequence_number = lcir.sub_ci_sequence_number AND
lcat.cal_type = lci.cal_type AND
lcat.s_cal_cat = cst_load AND
lcs.cal_status = lci.cal_status AND
lcs.s_cal_status = cst_active AND
ENRP_GET_LOAD_INCUR(
p_teach_cal_type,
p_teach_sequence_number,
cp_discontinued_dt,
cp_administrative_unit_status,
cp_unit_attempt_status,
cp_no_assessment_ind,
lci.cal_type,
lci.sequence_number,
-- anilk, Audit special fee build
NULL, -- for p_uoo_id
'N'
) = 'Y';
SELECT sca.version_number
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT teach_cal_type, teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = p_load_cal_type
AND load_ci_sequence_number = p_load_sequence_number;
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.discontinued_dt,
sua.uoo_id,
sua.no_assessment_ind
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST_REL cir,
IGS_PS_UNIT_VER uv
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status IN (cst_enrolled,
cst_discontin,
cst_completed,
cst_waitlisted) AND
uv.unit_cd = sua.unit_cd AND
uv.version_number = sua.version_number AND
(p_include_research_ind = 'Y' OR
uv.research_unit_ind = 'N') AND
cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
cir.sub_cal_type = sua.cal_type AND
cir.sub_ci_sequence_number = sua.ci_sequence_number AND
sua.cal_type = cp_cal_type AND
sua.ci_sequence_number = cp_seq_num;
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 census_dt
FROM IGS_GE_S_GEN_CAL_CON sgcc,
IGS_CA_DA_INST dai
WHERE sgcc.s_control_num = 1 AND
dai.cal_type = cp_cal_type AND
dai.ci_sequence_number = cp_ci_sequence_number AND
dai.dt_alias = sgcc.census_dt_alias
ORDER BY 1 ASC; -- use earliest date value
-- 3.1 Call the routine to calculate the EFTSU figure for the selected
-- student unit attempt within the nominated load calendar instance.
-- Passed 2 parameters key_course_cd and key_version_number as
-- part of the dld for career impact on attendance type ccr
v_eftsu_total := v_eftsu_total +
enrp_clc_sua_eftsu(
p_person_id,
p_course_cd,
v_version_number, -- from c_sca
v_sua_cir_rec.unit_cd,
v_sua_cir_rec.version_number,
v_sua_cir_rec.cal_type,
v_sua_cir_rec.ci_sequence_number,
v_sua_cir_rec.uoo_id,
p_load_cal_type,
p_load_sequence_number,
v_sua_cir_rec.override_enrolled_cp,
v_sua_cir_rec.override_eftsu,
p_truncate_ind,
v_sca_total_cp,
p_key_course_cd ,
p_key_version_number,
v_sua_cp ,
-- anilk, Audit special fee build
'N');
SELECT sua.uoo_id,
NVL(sua.no_assessment_ind,'N')
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 uv.research_unit_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_unit_version_number;
SELECT di.funding_index_1,
di.funding_index_2
FROM IGS_PS_DSCP di
WHERE di.discipline_group_cd = p_discipline_group_cd;
SELECT uicl.weftsu_factor,
uv.unit_level,
uv.unit_int_course_level_cd
FROM IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_INT_LVL uicl
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_version_number AND
uicl.unit_int_course_level_cd = uv.unit_int_course_level_cd;
SELECT COUNT(*)
FROM IGS_OR_INSTITUTION ins
WHERE ins.govt_institution_cd = 3030 AND
local_institution_ind = 'Y';
SELECT tr.percentage
FROM IGS_PS_UNIT_OFR_OPT uoo, IGS_PS_TCH_RESP tr
WHERE uoo.uoo_id = p_uoo_id
AND NOT EXISTS ( SELECT unit_cd
FROM IGS_PS_TCH_RESP_OVRD
WHERE uoo_id = uoo.uoo_id )
AND tr.unit_cd = uoo.unit_cd
AND tr.version_number = uoo.version_number
UNION ALL
SELECT tro.percentage
FROM IGS_PS_TCH_RESP_OVRD tro
WHERE tro.uoo_id = p_uoo_id;
SELECT ud.percentage
FROM IGS_PS_UNIT_DSCP ud
WHERE ud.unit_cd = p_unit_cd AND
ud.version_number = p_version_number;
SELECT COUNT(*)
FROM IGS_PS_ANL_LOAD cal
WHERE cal.course_cd = p_course_cd AND
cal.version_number = p_version_number AND
cal.effective_start_dt <= SYSDATE AND
(cal.effective_end_dt IS NULL OR
cal.effective_end_dt >= SYSDATE);
SELECT NVL(crv.std_annual_load, 0)
FROM IGS_PS_VER crv
WHERE crv.course_cd = p_course_cd AND
crv.version_number = p_version_number;
SELECT cal.annual_load_val
FROM IGS_PS_ANL_LOAD_U_LN calul,
IGS_PS_ANL_LOAD cal
WHERE calul.course_cd = p_course_cd AND
calul.crv_version_number = p_version_number AND
calul.effective_start_dt <= SYSDATE AND
calul.unit_cd = p_unit_cd AND
calul.uv_version_number = p_unit_version_number AND
calul.course_cd = cal.course_cd AND
calul.crv_version_number = cal.version_number AND
calul.yr_num = cal.yr_num AND
calul.effective_start_dt = cal.effective_start_dt AND
(cal.effective_end_dt IS NULL OR
cal.effective_end_dt >= SYSDATE)
ORDER BY calul.effective_start_dt DESC,
cal.yr_num;
SELECT cal.annual_load_val
FROM IGS_PS_ANL_LOAD cal
WHERE cal.course_cd = p_course_cd AND
cal.version_number = p_version_number AND
cal.effective_start_dt <= SYSDATE AND
(cal.effective_end_dt IS NULL OR
cal.effective_end_dt >= SYSDATE)
ORDER BY cal.yr_num;
SELECT NVL ( absolute_val,
IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
dai.dt_alias,
dai.sequence_number,
dai.cal_type,
dai.ci_sequence_number)) AS census_dt
FROM IGS_GE_S_GEN_CAL_CON sgcc,
IGS_CA_DA_INST dai
WHERE sgcc.s_control_num = 1 AND
dai.dt_alias = sgcc.census_dt_alias AND
dai.cal_type = p_cal_type AND
dai.ci_sequence_number = p_ci_sequence_number
ORDER BY 1 ASC; -- use earliest value
SELECT CI.cal_type,
CI.sequence_number
FROM IGS_CA_INST_REL CIR,
IGS_CA_INST CI,
IGS_CA_TYPE CT,
IGS_CA_STAT CS
WHERE CT.closed_ind = 'N' AND
CT.s_cal_cat = cst_teaching AND
CS.s_cal_status = cst_active AND
CI.cal_status = CS.cal_status AND
CI.cal_type = CT.cal_type AND
CIR.sup_cal_type = cp_cal_type AND
CIR.sup_ci_sequence_number = cp_sequence_number AND
CIR.sub_cal_type = CI.cal_type AND
CIR.sub_ci_sequence_number = CI.sequence_number;
SELECT SUA.unit_cd,
SUA.version_number,
SUA.cal_type,
SUA.ci_sequence_number,
SUA.override_enrolled_cp,
SUA.override_eftsu,
SUA.unit_attempt_status,
SUA.administrative_unit_status,
SUA.discontinued_dt,
SUA.uoo_id,
SUA.no_assessment_ind
FROM IGS_EN_SU_ATTEMPT SUA
WHERE SUA.person_id = cp_person_id AND
SUA.course_cd = cp_course_cd AND
SUA.cal_type = cp_cal_type AND
SUA.ci_sequence_number = cp_sequence_number AND
SUA.unit_attempt_status IN (cst_enrolled, cst_completed, cst_discontin, cst_waitlisted);
SELECT DLA.percentage,
DLA.second_percentage
FROM IGS_ST_DFT_LOAD_APPO DLA
WHERE DLA.cal_type = cp_acad_cal_type AND
DLA.ci_sequence_number = cp_acad_ci_sequence_number AND
DLA.teach_cal_type = cp_cal_type;
SELECT ATL.attendance_type
FROM IGS_EN_ATD_TYPE_LOAD ATL
WHERE ATL.cal_type = p_load_cal_type AND
ATL.lower_enr_load_range <= p_load_figure AND
ATL.upper_enr_load_range >= p_load_figure;
SELECT ci.start_dt,
ci.end_dt
FROM IGS_ST_DFT_LOAD_APPO dla,
IGS_CA_INST ci
WHERE dla.cal_type = cp_load_cal_type AND
dla.ci_sequence_number = cp_load_sequence_number AND
dla.teach_cal_type = cp_cal_type AND
ci.cal_type = dla.cal_type AND
ci.sequence_number = dla.ci_sequence_number;
SELECT AUSL.load_incurred_ind
FROM IGS_AD_ADM_UT_STT_LD AUSL
WHERE AUSL.cal_type = cp_load_cal_type AND
AUSL.ci_sequence_number = cp_load_sequence_number AND
AUSL.teach_cal_type = cp_cal_type AND
AUSL.administrative_unit_status = cp_administrative_unit_status;
SELECT asses_chrg_for_wlst_stud
FROM IGS_EN_OR_UNIT_WLST
WHERE cal_type = p_load_cal_type AND
closed_flag = 'N' AND
org_unit_cd = (SELECT NVL(uoo.owner_org_unit_cd, uv.owner_org_unit_cd)
FROM igs_ps_unit_ofr_opt uoo,
igs_ps_unit_ver uv
WHERE uoo.uoo_id = p_uoo_id AND
uv.unit_cd = uoo.unit_cd AND
uv.version_number = uoo.version_number);
SELECT include_waitlist_cp_flag
FROM IGS_EN_INST_WL_STPS;
SELECT 'X'
FROM IGS_CA_INST_REL cir1,
IGS_CA_TYPE ct,
IGS_CA_INST_REL cir2
WHERE cir1.sub_cal_type = cp_load_cal_type AND
cir1.sub_ci_sequence_number = cp_load_sequence_number AND
ct.cal_type = cir1.sup_cal_type AND
ct.s_cal_cat = 'ACADEMIC' AND
cir2.sup_cal_type = cir1.sup_cal_type AND
cir2.sup_ci_sequence_number = cir1.sup_ci_sequence_number AND
cir2.sub_cal_type = cp_teach_cal_type AND
cir2.sub_ci_sequence_number = cp_teach_sequence_number;
SELECT course_cd,
version_number
FROM
igs_en_stdnt_ps_att WHERE
key_program='Y' AND
person_id=p_person_id;
SELECT program_cd, program_version
FROM igs_en_spa_terms
WHERE person_id = p_person_id
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number
AND key_program_flag = 'Y';
SELECT party_number
FROM hz_parties
WHERE party_id=p_person_id;
Select sca.course_cd
From igs_en_stdnt_ps_att_all sca,
igs_ps_ver_all pv
WHERE sca.person_id = cp_person_id
AND sca.course_cd = pv.course_cd
AND sca.version_number = pv.version_number
AND (
(NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' AND igs_en_spa_terms_api.get_spat_primary_prg(sca.person_id, sca.course_cd, cp_load_cal_type,cp_load_seq_number)='PRIMARY')
OR
(NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N')
);
SELECT course_cd,
igs_en_spa_terms_api.get_spat_program_version(p_person_id, course_cd,
p_load_cal_type, p_load_seq_number)
FROM igs_en_stdnt_ps_att sca
WHERE person_id = p_person_id AND
--anilk, Bug# 3046897
course_attempt_status IN (cst_enrolled, cst_inactive, cst_discontin, cst_completed) AND
((l_career ='Y' AND
(EXISTS (SELECT 'x' FROM igs_en_spa_terms spat
WHERE spat.person_id = sca.person_id
AND spat.program_cd = sca.course_cd
AND spat.term_cal_type = p_load_cal_type
AND spat.term_sequence_number = p_load_seq_number)
OR
(sca.primary_program_type='PRIMARY' AND
NOT EXISTS (SELECT 'x'
FROM igs_en_spa_terms spat, igs_ps_ver pv1, igs_ps_ver pv2
WHERE spat.person_id = sca.person_id
AND spat.program_cd = pv1.course_cd
AND spat.program_version = pv1.version_number
AND sca.course_cd = pv2.course_cd
AND sca.version_number = pv2.version_number
AND pv1.course_type = pv2.course_type
AND spat.term_cal_type = p_load_cal_type
AND spat.term_sequence_number = p_load_seq_number)
)
)
)OR
(l_career ='N'
));
SELECT teach_cal_type,
teach_ci_sequence_number
FROM IGS_CA_LOAD_TO_TEACH_V lt
WHERE lt.load_cal_type = cp_load_cal_type
AND lt.load_ci_sequence_number = cp_load_seq_num
AND lt.teach_start_dt <= cp_tp_sd_cut_off_date
ORDER BY teach_cal_type ASC,teach_ci_sequence_number ASC;
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.discontinued_dt,
sua.uoo_id,
sua.no_assessment_ind
FROM IGS_EN_SU_ATTEMPT sua,
IGS_EN_STDNT_PS_ATT sca,
IGS_PS_UNIT_VER uv
WHERE
sca.person_id = p_person_id AND
sca.person_id = sua.person_id AND
sca.course_cd = sua.course_cd AND
( EXISTS (SELECT 'x' FROM igs_en_spa_terms spat
WHERE spat.person_id = sca.person_id
AND spat.program_cd = sca.course_cd
AND spat.term_cal_type = p_load_cal_type
AND spat.term_sequence_number = p_load_sequence_number)
OR
(sca.primary_program_type='PRIMARY' AND
NOT EXISTS (SELECT 'x'
FROM igs_en_spa_terms spat, igs_ps_ver pv1, igs_ps_ver pv2
WHERE spat.person_id = sca.person_id
AND spat.program_cd = pv1.course_cd
AND spat.program_version = pv1.version_number
AND sca.course_cd = pv2.course_cd
AND sca.version_number = pv2.version_number
AND pv1.course_type = pv2.course_type
AND spat.term_cal_type = p_load_cal_type
AND spat.term_sequence_number = p_load_sequence_number)
)
) AND
sua.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED','WAITLISTED') AND
uv.unit_cd = sua.unit_cd AND
uv.version_number = sua.version_number AND
(NVL(p_include_research_ind,'N')= 'Y' OR uv.research_unit_ind = 'N') AND
sua.cal_type = cp_teach_cal_type AND
sua.ci_sequence_number = cp_teach_seq_num;
SELECT S_CAL_CAT
FROM igs_ca_type
WHERE cal_type = p_cal_type;
SELECT rel.SUB_CAL_TYPE , rel.SUB_CI_SEQUENCE_NUMBER , NVL(dai.absolute_val, cai.start_dt) load_effective_dt
FROM igs_ca_inst_rel rel,
igs_ca_da_inst dai,
igs_en_cal_conf conf,
igs_ca_inst cai
WHERE
rel.SUB_CAL_TYPE = dai.CAL_TYPE
AND rel.SUB_CI_SEQUENCE_NUMBER = dai.CI_SEQUENCE_NUMBER
AND cai.CAL_TYPE = rel.SUB_CAL_TYPE
AND cai.SEQUENCE_NUMBER = rel.SUB_CI_SEQUENCE_NUMBER
AND dai.DT_ALIAS = conf.LOAD_EFFECT_DT_ALIAS
AND NVL(dai.absolute_val, cai.start_dt) < SYSDATE
AND SUP_CAL_TYPE = p_acad_cal_type
AND SUP_CI_SEQUENCE_NUMBER = p_acad_ci_sequence_number
ORDER BY 3 DESC;
SELECT S_CAL_CAT
FROM igs_ca_type
WHERE cal_type = p_cal_type;
SELECT NVL(SUM(igs_ss_enr_details.get_apor_credits ( uoo_id, override_enrolled_cp,ca.load_cal_type,ca.load_ci_sequence_number)),0) apor_cp
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_TEACH_TO_LOAD_V ca
WHERE sua.unit_attempt_status = 'UNCONFIRM'
AND sua.person_id = cp_n_perosn_id
AND sua.course_cd = cp_c_program
AND sua.ss_source_ind = 'S'
AND sua.cal_type = ca.teach_cal_type
AND sua.ci_sequence_number = ca.teach_ci_sequence_number
AND ca.load_cal_type = cp_c_load_cal
AND ca.load_ci_sequence_number = cp_n_load_seq_num;