[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT scae.cal_type,
scae.ci_sequence_number,
scae.enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_course_cd AND
scae.cal_type = ci.cal_type AND
scae.ci_sequence_number = ci.sequence_number
ORDER BY ci.start_dt DESC;
SELECT ci1.cal_type,
ci1.sequence_number
FROM IGS_CA_INST ci1,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cat.cal_type = ci1.cal_type AND
cat.s_cal_cat = cst_academic AND
ci1.cal_status = cs.cal_status AND
cs.s_cal_status = cst_active AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
ci1.cal_type,
ci1.sequence_number,
p_load_cal_type,
p_load_sequence_number,
'Y') = 'Y';
SELECT um.s_unit_mode
FROM IGS_EN_SU_ATTEMPT sua,
IGS_AS_UNIT_CLASS ucl,
IGS_AS_UNIT_MODE um
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_attempt_status IN (
cst_enrolled,
cst_completed,
cst_discontin) AND
IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
cp_ci_cal_type,
cp_ci_sequence_number,
sua.cal_type,
sua.ci_sequence_number,
'Y') = 'Y' AND
IGS_EN_PRC_LOAD.enrp_get_load_incur(
sua.cal_type,
sua.ci_sequence_number,
sua.discontinued_dt,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.no_assessment_ind,
p_load_cal_type,
p_load_sequence_number,
NULL,
-- anilk, Audit special fee build
'N') = 'Y' AND
ucl.unit_class = sua.unit_class AND
ucl.closed_ind = 'N' AND
um.unit_mode = ucl.unit_mode;
SELECT SCA.cal_type
FROM IGS_EN_STDNT_PS_ATT SCA
WHERE SCA.person_id = cp_person_id AND
SCA.course_cd = cp_course_cd;
SELECT SECC.load_effect_dt_alias
FROM IGS_EN_CAL_CONF SECC
WHERE SECC.s_control_num = 1;
SELECT CI.cal_type,
CI.sequence_number
FROM IGS_CA_INST CI,
IGS_CA_STAT CS
WHERE CI.cal_type = cp_cal_type AND
CI.start_dt <= cp_effective_dt AND
CI.end_dt >= cp_effective_dt AND
CS.cal_status = CI.cal_status AND
CS.s_cal_status = cst_active
ORDER BY CI.start_dt desc;
SELECT CI.cal_type,
CI.sequence_number,
CI.start_dt,
CI.end_dt
FROM IGS_CA_TYPE CT,
IGS_CA_INST CI,
IGS_CA_STAT CS
WHERE CT.closed_ind = 'N' AND
CS.s_cal_status = cst_active AND
CI.cal_status = CS.cal_status AND
CT.s_cal_cat = cst_load AND
CI.cal_type = CT.cal_type AND
(IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(cp_cal_type,
cp_sequence_number,
CI.cal_type,
CI.sequence_number,
'N') = 'Y')
ORDER BY CI.start_dt asc;
SELECT DAIV.alias_val
FROM IGS_CA_DA_INST_V DAIV
WHERE DAIV.cal_type = cp_cal_type AND
DAIV.ci_sequence_number = cp_ci_sequence_number AND
DAIV.dt_alias = cp_load_effect_dt_alias;
Fnd_Message.Set_name('FND','FORM_RECORD_DELETED');
SELECT commence_cutoff_dt_alias
FROM IGS_EN_CAL_CONF
WHERE s_control_num = 1;
SELECT IGS_EN_SU_ATTEMPT.cal_type,
IGS_EN_SU_ATTEMPT.ci_sequence_number,
IGS_CA_INST.start_dt
FROM IGS_EN_SU_ATTEMPT,
IGS_CA_INST
WHERE IGS_EN_SU_ATTEMPT.person_id = cp_person_id AND
IGS_EN_SU_ATTEMPT.course_cd = cp_course_cd AND
IGS_EN_SU_ATTEMPT.cal_type = IGS_CA_INST.cal_type AND
IGS_EN_SU_ATTEMPT.ci_sequence_number = IGS_CA_INST.sequence_number
ORDER BY IGS_CA_INST.start_dt;
SELECT IGS_CA_GEN_001.calp_set_alias_value
(
absolute_val,
IGS_CA_GEN_002.cals_clc_dt_from_dai
(
ci_sequence_number,
CAL_TYPE,
DT_ALIAS,
sequence_number
)
) alias_val
FROM IGS_CA_DA_INST
WHERE cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number AND
dt_alias = cp_dt_alias
ORDER BY alias_val;
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
SELECT DECODE(Pbv.DATE_OF_DEATH,NULL,NVL(PE.DECEASED_IND,'N'),'Y') DECEASED_IND
FROM IGS_PE_HZ_PARTIES pe,
IGS_PE_PERSON_BASE_V pbv
WHERE pe.party_id = p_person_id AND
pbv.person_id = pe.party_id;
SELECT ci.start_dt,
ci.end_dt
FROM IGS_CA_INST ci
WHERE ci.cal_type = p_acad_cal_type AND
ci.sequence_number = p_acad_ci_sequence_number;
SELECT sgcc.census_dt_alias
FROM IGS_GE_S_GEN_CAL_CON sgcc
WHERE sgcc.s_control_num = 1;
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_ci_sequence_number AND
cat.cal_type = cir.sub_cal_type AND
cat.closed_ind = 'N' AND
cat.s_cal_cat = cst_teaching AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
ci.cal_type = cat.cal_type AND
ci.cal_status = cs.cal_status AND
cs.s_cal_status = cst_active;
SELECT IGS_CA_GEN_001.calp_set_alias_value
(
absolute_val,
IGS_CA_GEN_002.cals_clc_dt_from_dai
(
ci_sequence_number,
CAL_TYPE,
DT_ALIAS,
sequence_number
)
) alias_val
FROM IGS_CA_DA_INST dai
WHERE dai.cal_type = cp_cal_type AND
dai.ci_sequence_number = cp_ci_sequence_number AND
dai.dt_alias = cp_dt_alias;
SELECT sca.course_attempt_status,
sca.discontinued_dt,
sca.lapsed_dt,
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 cop.offered_ind
FROM IGS_PS_OFR_PAT cop
WHERE cop.coo_id = cp_coo_id AND
cop.ci_sequence_number = cp_ci_sequence_number;
SELECT sci.end_dt
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = p_person_id AND
sci.course_cd = p_course_cd AND
eit.intermission_type(+) = sci.intermission_type AND
((eit.appr_reqd_ind = 'Y' AND sci.approved = 'Y') OR (eit.appr_reqd_ind = 'N'))
AND sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
ORDER BY sci.start_dt;
SELECT acaiv.offer_dt,
acaiv.ADM_CNDTNL_OFFER_STATUS,
acaiv.cndtnl_offer_must_be_stsfd_ind
FROM
(
SELECT
acai.person_id PERSON_ID,
aa.acad_cal_type ACAD_CAL_TYPE,
DECODE(acai.adm_cal_type, NULL, aa.acad_ci_sequence_number,
IGS_CA_GEN_001.calp_get_sup_inst (
aa.acad_cal_type,
acai.adm_cal_type,
acai.adm_ci_sequence_number))ACAD_CI_SEQUENCE_NUMBER,
acai.course_cd COURSE_CD,
acai.adm_outcome_status ADM_OUTCOME_STATUS,
acai.offer_dt OFFER_DT,
acai.adm_cndtnl_offer_status ADM_CNDTNL_OFFER_STATUS,
acai.cndtnl_offer_must_be_stsfd_ind CNDTNL_OFFER_MUST_BE_STSFD_IND,
acai.adm_offer_resp_status ADM_OFFER_RESP_STATUS
FROM
IGS_AD_PS_APPL_INST acai,
IGS_AD_APPL aa,
IGS_CA_INST ci,
IGS_AD_PS_APPL aca,
IGS_PS_VER crv
WHERE
aa.person_id = acai.person_id AND
aa.admission_appl_number = acai.admission_appl_number AND
ci.cal_type (+) = acai.deferred_adm_cal_type AND
ci.sequence_number (+) = acai.deferred_adm_ci_sequence_num AND
aca.person_id = acai.person_id AND
aca.admission_appl_number = acai.admission_appl_number AND
aca.nominated_course_cd = acai.nominated_course_cd AND
crv.course_cd = acai.course_cd AND
crv.version_number = acai.crv_version_number
) acaiv
WHERE acaiv.person_id = p_person_id AND
acaiv.course_cd = p_course_cd AND
IGS_EN_GEN_002.enrp_get_acai_offer(acaiv.ADM_OUTCOME_STATUS,
acaiv.ADM_OFFER_RESP_STATUS) = 'Y' AND
acaiv.acad_cal_type = p_acad_cal_type AND
acaiv.acad_ci_sequence_number = p_acad_ci_sequence_number
ORDER BY acaiv.offer_dt DESC; -- use latest offer date
cst_deleted) THEN
OPEN c_acaiv;
ELSIF v_course_status = cst_deleted THEN
IF v_acaiv_offer_dt IS NULL THEN
p_message_name := 'IGS_EN_STUD_INELIGIBLE_RE_ENR';
SELECT sua.cal_type,
sua.ci_sequence_number,
ci.alternate_code
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST ci
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.cal_type = ci.cal_type AND
sua.ci_sequence_number = ci.sequence_number AND
(sua.unit_attempt_status = 'ENROLLED' OR
sua.unit_attempt_status = 'COMPLETED')
ORDER BY ci.start_dt;
p_logical_delete_dt IN DATE )
RETURN VARCHAR2 AS
/*
|| Created By :
|| Created On :
|| Purpose : This procedure process the Application
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| pkpatel 09-SEP-2001 Bug no.1960126 :For Academic Record Maintenance
|| Modified the defination of Cursor 'c_sci' to include
|| the logic for INtermission Type Approval
|| (reverse chronological order - newest change first)
-- rnirwani 13-Sep-2004 changed cursor c_intmsn_details to not consider logically deleted records and
-- also to avoid un-approved intermission records. Bug# 3885804
-- smaddali 10-mar-06 Modified cursor c_sci for build EN324 - bug#5091858
*/
BEGIN -- enrp_get_sca_status
-- Get the IGS_PS_COURSE attempt status of a nominated student IGS_PS_COURSE attempt.
-- This routine checks attributes of the students enrolment to ascertain
-- what their enrolled student IGS_PS_COURSE attempt status should be.
DECLARE
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
v_logical_delete_dt IGS_EN_STDNT_PS_ATT.logical_delete_dt%TYPE;
SELECT sca.course_attempt_status,
sca.student_confirmed_ind,
sca.discontinued_dt,
sca.lapsed_dt,
sca.course_rqrmnt_complete_ind,
sca.logical_delete_dt
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT 'x'
FROM sys.dual
WHERE EXISTS (
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 = cst_enrolled AND
sua.ci_start_dt <= SYSDATE);
SELECT 'X'
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = p_person_id AND
sci.course_cd = p_course_cd AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
eit.intermission_type = sci.intermission_type AND
((eit.appr_reqd_ind = 'Y' AND sci.approved = 'Y') OR
(eit.appr_reqd_ind = 'N')) AND
sci.start_dt <= trunc(SYSDATE) AND
( sci.end_dt >= trunc(SYSDATE) OR
( sci.end_dt < trunc(SYSDATE) AND
sci.cond_return_flag = 'Y' AND
EXISTS (select 'x' from igs_en_spi_rconds rc
where sci.person_id = rc.person_id
and sci.course_cd = rc.course_cd
and sci.start_dt = rc.start_dt
and sci.logical_delete_date = rc.logical_delete_date
and status_code IN ('FAILED','PENDING')
)
)
);
SELECT ci.cal_type,
scae.ci_sequence_number,
scae.enr_form_due_dt
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_course_cd AND
ci.cal_type = scae.cal_type AND
ci.sequence_number = scae.ci_sequence_number
ORDER BY ci.end_dt DESC;
SELECT 'x'
FROM sys.dual
WHERE EXISTS (
SELECT 'x'
FROM IGS_EN_CAL_CONF secc,
IGS_CA_DA_INST_V daiv
WHERE secc.s_control_num = 1 AND
secc.enr_form_due_dt_alias IS NOT NULL AND
daiv.cal_type = cp_cal_type AND
daiv.ci_sequence_number = cp_ci_sequence_number AND
daiv.dt_alias = secc.enr_form_due_dt_alias AND
daiv.alias_val >= SYSDATE);
v_logical_delete_dt;
v_logical_delete_dt := p_logical_delete_dt;
IF v_logical_delete_dt IS NOT NULL THEN
RETURN cst_deleted;
SELECT sct.transfer_course_cd
FROM IGS_PS_STDNT_TRN sct
WHERE sct.person_id = p_person_id AND
(sct.course_cd = p_course_cd OR
sct.transfer_course_cd = p_course_cd)
ORDER BY transfer_dt desc;