[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_insert_todo BOOLEAN DEFAULT FALSE;
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 cir.sup_cal_type,
cir.sup_ci_sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE ct,
IGS_CA_STAT cs
WHERE cir.sub_cal_type = p_cal_type AND
cir.sub_ci_sequence_number = p_ci_sequence_number AND
ci.cal_type = cir.sup_cal_type AND
ci.sequence_number = cir.sup_ci_sequence_number AND
ct.cal_type = ci.cal_type AND
ct.s_cal_cat = cst_progress AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active AND
EXISTS
(SELECT 'X'
FROM IGS_PR_STDNT_PR_CK spc
WHERE spc.person_id = p_person_id AND
spc.course_cd = p_course_cd AND
spc.prg_cal_type = cir.sup_cal_type AND
spc.prg_ci_sequence_number = cir.sup_ci_sequence_number)
ORDER BY ci.start_dt DESC;
v_insert_todo := TRUE;
IF v_insert_todo THEN
-- Insert todo entry
v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO (
p_person_id,
cst_prg_check,
NULL,
'Y');
SELECT load_cal_type, load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = p_teach_cal_type
AND teach_ci_sequence_number = p_teach_ci_sequence_number
ORDER BY LOAD_START_DT ASC;
SELECT spo.person_id,
spo.course_cd,
spo.sequence_number,
spo.pro_pra_sequence_number,
spo.prg_cal_type,
spo.prg_ci_sequence_number,
spo.progression_outcome_type,
spo.duration,
spo.duration_type,
spo.decision_status,
spo.decision_dt,
spo.show_cause_expiry_dt,
spo.show_cause_dt,
spo.show_cause_outcome_dt,
spo.appeal_expiry_dt,
spo.appeal_dt,
spo.appeal_outcome_dt,
spo.expiry_dt,
spo.encmb_course_group_cd,
pot.s_progression_outcome_type,
sca.version_number,
sca.commencement_dt,
sca.course_attempt_status,
sca.discontinuation_reason_cd
FROM IGS_PR_STDNT_PR_OU spo,
IGS_PR_OU_TYPE pot,
IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE (
p_spo_person_id IS NULL
AND
-- Decision Status is approved and applied_dt is null/'0001/01/01
-- or
-- Decision Status is removed /cancelled and applied date is not null
(
(spo.decision_status = cst_approved AND
(
spo.applied_dt IS NULL OR
spo.applied_dt = igs_ge_date.igsdate('0001/01/01')
)
)
OR
( spo.decision_status IN (
cst_removed,
cst_cancelled
)
AND
spo.applied_dt IS NOT NULL
)
)
AND
(
p_course_cd IS NULL OR
spo.course_cd LIKE p_course_cd
)
AND
(
p_progression_status IS NULL OR
sca.progression_status = p_progression_status
)
AND
pot.progression_outcome_type = spo.progression_outcome_type
AND
sca.person_id = spo.person_id
AND
sca.course_cd = spo.course_cd
AND
crv.course_cd = sca.course_cd
AND
crv.version_number = sca.version_number
AND
-- group_id has not been specified or if specified then take out NOCOPY person_id's for that group_id
-- such that the person has a valid student program attempt record as given in table igs_en_stdnt_ps_att
(
p_group_id IS NULL
OR
sca.person_id IN (
SELECT person_id
FROM IGS_PE_PIGM_PIDGRP_MEM_V
WHERE group_id = p_group_id AND
person_id = sca.person_id
)
)
AND
(
p_prg_cal_type IS NULL
OR
(
spo.prg_cal_type = p_prg_cal_type
AND
spo.prg_ci_sequence_number = p_prg_sequence_number
)
)
AND
(
p_course_type IS NULL OR
crv.course_type = p_course_type
)
AND
(
p_attendance_mode IS NULL OR
sca.attendance_mode = p_attendance_mode
)
AND
(
p_org_unit_cd IS NULL
OR
IGS_PR_GEN_001.PRGP_get_crv_cmt
(
sca.course_cd,
sca.version_number,
p_org_unit_cd,
p_ou_start_dt
) = 'Y'
)
AND
(
p_enrolment_cat IS NULL
OR
EXISTS
(
SELECT 'X'
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci1
WHERE sca.person_id = scae.person_id AND
sca.course_cd = scae.course_cd AND
scae.enrolment_cat = p_enrolment_cat AND
ci1.cal_type = scae.cal_type AND
ci1.sequence_number = scae.ci_sequence_number AND
ci1.end_dt =
(
SELECT MAX(ci2.end_dt)
FROM IGS_AS_SC_ATMPT_ENR scae2,
IGS_CA_INST ci2
WHERE
scae2.person_id = scae.person_id AND
scae2.course_cd = scae.course_cd AND
ci2.cal_type = scae2.cal_type AND
ci2.sequence_number = scae2.ci_sequence_number
)
)
)
AND
(
p_location_cd IS NULL OR
sca.location_cd = p_location_cd
)
)
OR
(
p_spo_person_id IS NOT NULL
AND
spo.person_id = p_spo_person_id
AND
spo.course_cd = p_spo_course_cd
AND
spo.sequence_number = p_spo_sequence_number
AND
pot.progression_outcome_type = spo.progression_outcome_type
AND
sca.person_id = spo.person_id
AND
sca.course_cd = spo.course_cd
AND
crv.course_cd = sca.course_cd
AND
crv.version_number = sca.version_number
)
ORDER BY spo.person_id,
spo.course_cd,
DECODE (spo.decision_status, 'APPROVED', 9,
1),
spo.prg_cal_type,
spo.prg_ci_sequence_number;
SELECT s_discontinuation_reason_type
FROM IGS_EN_DCNT_REASONCD dr
WHERE dr.discontinuation_reason_cd = cp_discontinuation_reason_cd AND
dr.s_discontinuation_reason_type = cst_progress;
SELECT /*+INDEX(spo spo_pk)*/
spo.sequence_number,
spo.expiry_dt
FROM IGS_PR_STDNT_PR_OU spo,
IGS_PR_OU_TYPE pot
WHERE
(EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_PS spc
WHERE spc.person_id = cp_person_id AND
spc.course_cd = cp_course_cd AND
spc.spo_sequence_number = cp_sequence_number AND
spc.course_cd = cp_course_cd
) OR
(cp_spo_encmb_course_group_cd IS NOT NULL AND
EXISTS (
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm
WHERE cgm.course_group_cd = cp_spo_encmb_course_group_cd AND
cgm.course_cd = cp_course_cd AND
cgm.version_number = cp_sca_version_number))) AND
spo.person_id = cp_person_id AND
spo.sequence_number <> cp_sequence_number AND
pot.progression_outcome_type = spo.progression_outcome_type AND
pot.s_progression_outcome_type IN (
cst_suspension,
cst_exclusion,
cst_expulsion) AND
spo.applied_dt IS NOT NULL AND
(EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_PS spc
WHERE spc.person_id = spo.person_id AND
spc.course_cd = spo.course_cd AND
spc.spo_sequence_number = spo.sequence_number AND
spc.course_cd = cp_course_cd
) OR
(spo.encmb_course_group_cd IS NOT NULL AND
EXISTS (
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm
WHERE cgm.course_group_cd = spo.encmb_course_group_cd AND
cgm.course_cd = cp_course_cd AND
cgm.version_number = cp_sca_version_number)));
SELECT sca.*,
sca.ROWID
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd
FOR UPDATE NOWAIT;
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.course_attempt_status,
sca.commencement_dt
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_spo_person_id AND
sca.course_attempt_status IN (
cst_enrolled,
cst_inactive,
cst_lapsed,
cst_intermit) AND
(EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_PS spc
WHERE spc.person_id = cp_spo_person_id AND
spc.spo_course_cd = cp_spo_course_cd AND
spc.spo_sequence_number = cp_spo_sequence_number AND
spc.course_cd = sca.course_cd) OR
(cp_spo_encmb_course_group_cd IS NOT NULL AND
EXISTS (
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm
WHERE cgm.course_group_cd = cp_spo_encmb_course_group_cd AND
cgm.course_cd = sca.course_cd AND
cgm.version_number = sca.version_number)
));
SELECT
susa.*,
susa.ROWID,
DECODE(acai.acai_ind, 'Y', 'Y', 'N') acai_ind
FROM
IGS_EN_STDNT_PS_ATT sca,
IGS_AS_SU_SETATMPT susa,
( SELECT /*+INDEX(sca sca_pk)*/
sca.person_id,
sca.course_cd,
'Y' acai_ind
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_AD_PS_APPL_INST acai
WHERE sca.person_id = acai.person_id AND
sca.adm_admission_appl_number = acai.admission_appl_number AND
sca.adm_nominated_course_cd = acai.nominated_course_cd AND
sca.adm_sequence_number = acai.sequence_number) acai
WHERE sca.person_id = cp_spo_person_id AND
sca.course_cd = cp_spo_course_cd AND
sca.person_id = susa.person_id AND
sca.course_cd = susa.course_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL AND
susa.rqrmnts_complete_ind = 'N' AND
EXISTS (
SELECT 'X'
FROM IGS_PR_SDT_PR_UNT_ST spus
WHERE spus.person_id = sca.person_id AND
spus.course_cd = sca.course_cd AND
spus.spo_sequence_number = cp_spo_sequence_number AND
spus.unit_set_cd = susa.unit_set_cd) AND
acai.person_id (+)= susa.person_id AND
acai.course_cd (+)= susa.course_cd ;
SELECT /*+INDEX(pra1 pra_spo_fk_i)*/
pra1.progression_rule_cat,
pra1.sequence_number
FROM IGS_PR_RU_APPL pra1
WHERE pra1.spo_person_id = cp_spo_person_id AND
pra1.spo_course_cd = cp_spo_course_cd AND
pra1.spo_sequence_number = cp_spo_sequence_number AND
pra1.s_relation_type = cst_spo AND
pra1.logical_delete_dt IS NULL AND
NOT EXISTS (
SELECT 'X'
FROM IGS_PR_RU_APPL pra2
WHERE pra2.sca_person_id = cp_spo_person_id AND
pra2.sca_course_cd = cp_spo_course_cd AND
NVL(pra2.spo_person_id, 0) = cp_spo_person_id AND
NVL(pra2.spo_course_cd, 'NULL') = cp_spo_course_cd AND
NVL(pra2.spo_sequence_number,0) = cp_spo_sequence_number AND
pra2.s_relation_type = cst_sca AND
pra2.logical_delete_dt IS NULL AND
NVL(pra2.reference_cd, pra2.progression_rule_cd)
= NVL(pra1.reference_cd, pra1.progression_rule_cd));
SELECT /*+INDEX(pra pra_spo_fk_i)*/
prct.*,
prct.ROWID
FROM IGS_PR_RU_APPL pra,
IGS_PR_RU_CA_TYPE prct
WHERE pra.progression_rule_cat = prct.progression_rule_cat AND
pra.sequence_number = prct.pra_sequence_number AND
pra.logical_delete_dt IS NULL AND
pra.s_relation_type = cst_sca AND
pra.sca_person_id = cp_spo_person_id AND
pra.sca_course_cd = cp_spo_course_cd AND
pra.spo_person_id = cp_spo_person_id AND
pra.spo_course_cd = cp_spo_course_cd AND
pra.spo_sequence_number = cp_spo_sequence_number ; -- commented by Prajeesh
SELECT ci1.sequence_number
FROM IGS_CA_INST ci1,
IGS_CA_STAT cs
WHERE ci1.cal_type = cp_prg_cal_type AND
cs.CAL_STATUS = ci1.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active AND
ci1.start_dt >
(SELECT ci2.start_dt
FROM IGS_CA_INST ci2
WHERE ci2.cal_type = cp_spo_cal_type AND
ci2.sequence_number = cp_spo_sequence_number)
ORDER BY ci1.start_dt ASC;
SELECT pra.*,
pra.ROWID
FROM IGS_PR_RU_APPL pra
WHERE pra.spo_person_id = cp_spo_person_id AND
pra.spo_course_cd = cp_spo_course_cd AND
pra.spo_sequence_number = cp_spo_sequence_number AND
pra.sca_person_id = cp_spo_person_id AND
pra.sca_course_cd = cp_spo_course_cd AND
pra.logical_delete_dt IS NULL AND
pra.s_relation_type = cst_sca ;
SELECT pra1.*,
pra1.ROWID
FROM IGS_PR_RU_APPL pra1
WHERE pra1.sca_person_id = cp_spo_person_id AND
pra1.sca_course_cd = cp_spo_course_cd AND
NVL(pra1.spo_person_id, 0) = cp_spo_person_id AND
NVL(pra1.spo_course_cd, 'NULL') = cp_spo_course_cd AND
NVL(pra1.spo_sequence_number,0) = cp_spo_sequence_number AND
pra1.s_relation_type = cst_sca AND
pra1.logical_delete_dt IS NULL AND
NOT EXISTS (
SELECT 'X'
FROM IGS_PR_RU_APPL pra2
WHERE pra2.spo_person_id = cp_spo_person_id AND
pra2.spo_course_cd = cp_spo_course_cd AND
pra2.spo_sequence_number = cp_spo_sequence_number AND
pra2.s_relation_type = cst_spo AND
pra2.progression_rule_cat = pra1.progression_rule_cat AND
NVL(pra2.progression_rule_cd, nvl(pra2.reference_cd, 'X')) = NVL(pra1.progression_rule_cd, NVL(pra1.reference_cd, 'X')) AND
pra2.logical_delete_dt IS NULL) ;
SELECT spa.award_cd
FROM igs_pr_stdnt_pr_awd spa
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND spa.spo_sequence_number = cp_spo_sequence_number;
SELECT
sua.*, sua.ROWID
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_spo_person_id AND
sua.course_cd = cp_spo_course_cd AND
sua.unit_attempt_status = cst_enrolled AND
sua.unit_cd IN
(SELECT spu.unit_cd
FROM IGS_PR_STDNT_PR_UNIT spu
WHERE spu.person_id = cp_spo_person_id AND
spu.course_cd = cp_spo_course_cd AND
spu.spo_sequence_number = cp_spo_sequence_number AND
spu.s_unit_type = cst_excluded);
SELECT 'X'
FROM IGS_AS_SU_STMPTOUT suao
WHERE suao.person_id = cp_spo_person_id AND
suao.course_cd = cp_spo_course_cd AND
suao.uoo_id = cp_sua_uoo_id;
--rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
--added as part of Prevent Dropping Core Units. Enh Bug# 3052432
BEGIN -- prgpl_ins_log_entry
-- create a log entry
DECLARE
v_key IGS_GE_s_log.KEY%TYPE;
SELECT sca.course_cd sca_course_cd,
sca.version_number,
spo.*,
spo.ROWID
FROM IGS_PR_STDNT_PR_OU spo,
IGS_EN_STDNT_PS_ATT sca
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.sequence_number = p_sequence_number AND
spo.person_id = sca.person_id AND
spo.course_cd = sca.course_cd ;
UPDATE IGS_PR_STDNT_PR_OU
SET applied_dt = SYSDATE,
expiry_dt = v_expiry_dt,
show_cause_expiry_dt = v_spo_rec.show_cause_expiry_dt,
appeal_expiry_dt = v_spo_rec.appeal_expiry_dt
WHERE CURRENT OF c_spo;
IGS_PR_STDNT_PR_OU_PKG.UPDATE_ROW(
X_ROWID => v_spo_rec.ROWID,
X_PERSON_ID => v_spo_rec.PERSON_ID,
X_COURSE_CD => v_spo_rec.COURSE_CD,
X_SEQUENCE_NUMBER => v_spo_rec.SEQUENCE_NUMBER,
X_PRG_CAL_TYPE => v_spo_rec.PRG_CAL_TYPE,
X_PRG_CI_SEQUENCE_NUMBER => v_spo_rec.PRG_CI_SEQUENCE_NUMBER,
X_RULE_CHECK_DT => v_spo_rec.RULE_CHECK_DT,
X_PROGRESSION_RULE_CAT => v_spo_rec.PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER => v_spo_rec.PRA_SEQUENCE_NUMBER,
X_PRO_SEQUENCE_NUMBER => v_spo_rec.PRO_SEQUENCE_NUMBER,
X_PROGRESSION_OUTCOME_TYPE => v_spo_rec.PROGRESSION_OUTCOME_TYPE,
X_DURATION => v_spo_rec.DURATION,
X_DURATION_TYPE => v_spo_rec.DURATION_TYPE,
X_DECISION_STATUS => v_spo_rec.DECISION_STATUS,
X_DECISION_DT => v_spo_rec.DECISION_DT,
X_DECISION_ORG_UNIT_CD => v_spo_rec.DECISION_ORG_UNIT_CD,
X_DECISION_OU_START_DT => v_spo_rec.DECISION_OU_START_DT,
X_APPLIED_DT => SYSDATE,
X_SHOW_CAUSE_EXPIRY_DT => v_spo_rec.SHOW_CAUSE_EXPIRY_DT,
--the record variable above has been updated in the code before the update call
X_SHOW_CAUSE_DT => v_spo_rec.SHOW_CAUSE_DT,
X_SHOW_CAUSE_OUTCOME_DT => v_spo_rec.SHOW_CAUSE_OUTCOME_DT,
X_SHOW_CAUSE_OUTCOME_TYPE => v_spo_rec.SHOW_CAUSE_OUTCOME_TYPE,
X_APPEAL_EXPIRY_DT => v_spo_rec.APPEAL_EXPIRY_DT,
--the record variable above has been updated in the code before the update call
X_APPEAL_DT => v_spo_rec.APPEAL_DT,
X_APPEAL_OUTCOME_DT => v_spo_rec.APPEAL_OUTCOME_DT,
X_APPEAL_OUTCOME_TYPE => v_spo_rec.APPEAL_OUTCOME_TYPE,
X_ENCMB_COURSE_GROUP_CD => v_spo_rec.ENCMB_COURSE_GROUP_CD,
X_RESTRICTED_ENROLMENT_CP => v_spo_rec.RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE => v_spo_rec.RESTRICTED_ATTENDANCE_TYPE,
X_COMMENTS => v_spo_rec.COMMENTS,
X_SHOW_CAUSE_COMMENTS => v_spo_rec.SHOW_CAUSE_COMMENTS,
X_APPEAL_COMMENTS => v_spo_rec.APPEAL_COMMENTS,
X_EXPIRY_DT => v_expiry_dt,
X_PRO_PRA_SEQUENCE_NUMBER => v_spo_rec.PRO_PRA_SEQUENCE_NUMBER,
X_MODE => 'R'
);
/* UPDATE IGS_PR_STDNT_PR_OU
SET applied_dt = SYSDATE,
show_cause_expiry_dt = v_spo_rec.show_cause_expiry_dt,
appeal_expiry_dt = v_spo_rec.appeal_expiry_dt
WHERE CURRENT OF c_spo;
IGS_PR_STDNT_PR_OU_PKG.UPDATE_ROW(
X_ROWID => v_spo_rec.ROWID,
X_PERSON_ID => v_spo_rec.PERSON_ID,
X_COURSE_CD => v_spo_rec.COURSE_CD,
X_SEQUENCE_NUMBER => v_spo_rec.SEQUENCE_NUMBER,
X_PRG_CAL_TYPE => v_spo_rec.PRG_CAL_TYPE,
X_PRG_CI_SEQUENCE_NUMBER => v_spo_rec.PRG_CI_SEQUENCE_NUMBER,
X_RULE_CHECK_DT => v_spo_rec.RULE_CHECK_DT,
X_PROGRESSION_RULE_CAT => v_spo_rec.PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER => v_spo_rec.PRA_SEQUENCE_NUMBER,
X_PRO_SEQUENCE_NUMBER => v_spo_rec.PRO_SEQUENCE_NUMBER,
X_PROGRESSION_OUTCOME_TYPE => v_spo_rec.PROGRESSION_OUTCOME_TYPE,
X_DURATION => v_spo_rec.DURATION,
X_DURATION_TYPE => v_spo_rec.DURATION_TYPE,
X_DECISION_STATUS => v_spo_rec.DECISION_STATUS,
X_DECISION_DT => v_spo_rec.DECISION_DT,
X_DECISION_ORG_UNIT_CD => v_spo_rec.DECISION_ORG_UNIT_CD,
X_DECISION_OU_START_DT => v_spo_rec.DECISION_OU_START_DT,
X_APPLIED_DT => SYSDATE,
X_SHOW_CAUSE_EXPIRY_DT => v_spo_rec.SHOW_CAUSE_EXPIRY_DT,
--the record variable above has been updated in the code before the update call
X_SHOW_CAUSE_DT => v_spo_rec.SHOW_CAUSE_DT,
X_SHOW_CAUSE_OUTCOME_DT => v_spo_rec.SHOW_CAUSE_OUTCOME_DT,
X_SHOW_CAUSE_OUTCOME_TYPE => v_spo_rec.SHOW_CAUSE_OUTCOME_TYPE,
X_APPEAL_EXPIRY_DT => v_spo_rec.APPEAL_EXPIRY_DT,
--the record variable above has been updated in the code before the update call
X_APPEAL_DT => v_spo_rec.APPEAL_DT,
X_APPEAL_OUTCOME_DT => v_spo_rec.APPEAL_OUTCOME_DT,
X_APPEAL_OUTCOME_TYPE => v_spo_rec.APPEAL_OUTCOME_TYPE,
X_ENCMB_COURSE_GROUP_CD => v_spo_rec.ENCMB_COURSE_GROUP_CD,
X_RESTRICTED_ENROLMENT_CP => v_spo_rec.RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE => v_spo_rec.RESTRICTED_ATTENDANCE_TYPE,
X_COMMENTS => v_spo_rec.COMMENTS,
X_SHOW_CAUSE_COMMENTS => v_spo_rec.SHOW_CAUSE_COMMENTS,
X_APPEAL_COMMENTS => v_spo_rec.APPEAL_COMMENTS,
X_EXPIRY_DT => v_spo_rec.expiry_dt,
X_PRO_PRA_SEQUENCE_NUMBER => v_spo_rec.PRO_PRA_SEQUENCE_NUMBER,
X_MODE => 'R'
);
UPDATE IGS_PR_STDNT_PR_OU
SET applied_dt = NULL,
expiry_dt = NULL
WHERE CURRENT OF c_spo;
IGS_PR_STDNT_PR_OU_PKG.UPDATE_ROW(
X_ROWID => v_spo_rec.ROWID,
X_PERSON_ID => v_spo_rec.PERSON_ID,
X_COURSE_CD => v_spo_rec.COURSE_CD,
X_SEQUENCE_NUMBER => v_spo_rec.SEQUENCE_NUMBER,
X_PRG_CAL_TYPE => v_spo_rec.PRG_CAL_TYPE,
X_PRG_CI_SEQUENCE_NUMBER => v_spo_rec.PRG_CI_SEQUENCE_NUMBER,
X_RULE_CHECK_DT => v_spo_rec.RULE_CHECK_DT,
X_PROGRESSION_RULE_CAT => v_spo_rec.PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER => v_spo_rec.PRA_SEQUENCE_NUMBER,
X_PRO_SEQUENCE_NUMBER => v_spo_rec.PRO_SEQUENCE_NUMBER,
X_PROGRESSION_OUTCOME_TYPE => v_spo_rec.PROGRESSION_OUTCOME_TYPE,
X_DURATION => v_spo_rec.DURATION,
X_DURATION_TYPE => v_spo_rec.DURATION_TYPE,
X_DECISION_STATUS => v_spo_rec.DECISION_STATUS,
X_DECISION_DT => v_spo_rec.DECISION_DT,
X_DECISION_ORG_UNIT_CD => v_spo_rec.DECISION_ORG_UNIT_CD,
X_DECISION_OU_START_DT => v_spo_rec.DECISION_OU_START_DT,
X_APPLIED_DT => NULL,
X_SHOW_CAUSE_EXPIRY_DT => v_orig_SHOW_CAUSE_EXPIRY_DT,
X_SHOW_CAUSE_DT => v_spo_rec.SHOW_CAUSE_DT,
X_SHOW_CAUSE_OUTCOME_DT => v_spo_rec.SHOW_CAUSE_OUTCOME_DT,
X_SHOW_CAUSE_OUTCOME_TYPE => v_spo_rec.SHOW_CAUSE_OUTCOME_TYPE,
X_APPEAL_EXPIRY_DT => v_orig_APPEAL_EXPIRY_DT,
X_APPEAL_DT => v_spo_rec.APPEAL_DT,
X_APPEAL_OUTCOME_DT => v_spo_rec.APPEAL_OUTCOME_DT,
X_APPEAL_OUTCOME_TYPE => v_spo_rec.APPEAL_OUTCOME_TYPE,
X_ENCMB_COURSE_GROUP_CD => v_spo_rec.ENCMB_COURSE_GROUP_CD,
X_RESTRICTED_ENROLMENT_CP => v_spo_rec.RESTRICTED_ENROLMENT_CP,
X_RESTRICTED_ATTENDANCE_TYPE => v_spo_rec.RESTRICTED_ATTENDANCE_TYPE,
X_COMMENTS => v_spo_rec.COMMENTS,
X_SHOW_CAUSE_COMMENTS => v_spo_rec.SHOW_CAUSE_COMMENTS,
X_APPEAL_COMMENTS => v_spo_rec.APPEAL_COMMENTS,
X_EXPIRY_DT => NULL,
X_PRO_PRA_SEQUENCE_NUMBER => v_spo_rec.PRO_PRA_SEQUENCE_NUMBER,
X_MODE => 'R'
);
IGS_GR_SPECIAL_AWARD_PKG.INSERT_ROW(
X_ROWID => lv_ROWID,
X_PERSON_ID => v_spo_rec.PERSON_ID,
X_COURSE_CD => v_spo_rec.COURSE_CD,
X_AWARD_CD => v_spa_AWARD_CD,
X_AWARD_DT => SYSDATE,
X_CEREMONY_ANNOUNCED_IND => 'Y',
X_COMMENTS => 'Created from Progression',
X_MODE => 'R',
X_ORG_ID => lv_org_id
);
UPDATE IGS_AS_SU_SETATMPT
SET end_dt = v_spo_rec.decision_dt,
voluntary_end_ind = 'N'
WHERE CURRENT OF c_susa;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW(
X_ROWID => v_susa_rec.ROWID,
X_PERSON_ID => v_susa_rec.PERSON_ID,
X_COURSE_CD => v_susa_rec.COURSE_CD,
X_UNIT_SET_CD => v_susa_rec.UNIT_SET_CD,
X_SEQUENCE_NUMBER => v_susa_rec.SEQUENCE_NUMBER,
X_US_VERSION_NUMBER => v_susa_rec.US_VERSION_NUMBER,
X_SELECTION_DT => v_susa_rec.SELECTION_DT,
X_STUDENT_CONFIRMED_IND => v_susa_rec.STUDENT_CONFIRMED_IND,
X_END_DT => v_spo_rec.decision_dt,
X_PARENT_UNIT_SET_CD => v_susa_rec.PARENT_UNIT_SET_CD,
X_PARENT_SEQUENCE_NUMBER => v_susa_rec.PARENT_SEQUENCE_NUMBER,
X_PRIMARY_SET_IND => v_susa_rec.PRIMARY_SET_IND,
X_VOLUNTARY_END_IND => 'N',
X_AUTHORISED_PERSON_ID => v_susa_rec.AUTHORISED_PERSON_ID,
X_AUTHORISED_ON => v_susa_rec.AUTHORISED_ON,
X_OVERRIDE_TITLE => v_susa_rec.OVERRIDE_TITLE,
X_RQRMNTS_COMPLETE_IND => v_susa_rec.RQRMNTS_COMPLETE_IND,
X_RQRMNTS_COMPLETE_DT => v_susa_rec.RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_susa_rec.S_COMPLETED_SOURCE_TYPE,
X_CATALOG_CAL_TYPE => v_susa_rec.CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM => v_susa_rec.CATALOG_SEQ_NUM,
X_ATTRIBUTE_CATEGORY => v_susa_rec.ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => v_susa_rec.ATTRIBUTE1 ,
X_ATTRIBUTE2 => v_susa_rec.ATTRIBUTE2 ,
X_ATTRIBUTE3 => v_susa_rec.ATTRIBUTE3 ,
X_ATTRIBUTE4 => v_susa_rec.ATTRIBUTE4 ,
X_ATTRIBUTE5 => v_susa_rec.ATTRIBUTE5 ,
X_ATTRIBUTE6 => v_susa_rec.ATTRIBUTE6 ,
X_ATTRIBUTE7 => v_susa_rec.ATTRIBUTE7 ,
X_ATTRIBUTE8 => v_susa_rec.ATTRIBUTE8 ,
X_ATTRIBUTE9 => v_susa_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10=> v_susa_rec.ATTRIBUTE10,
X_ATTRIBUTE11=> v_susa_rec.ATTRIBUTE11,
X_ATTRIBUTE12=> v_susa_rec.ATTRIBUTE12,
X_ATTRIBUTE13=> v_susa_rec.ATTRIBUTE13,
X_ATTRIBUTE14=> v_susa_rec.ATTRIBUTE14,
X_ATTRIBUTE15=> v_susa_rec.ATTRIBUTE15,
X_ATTRIBUTE16=> v_susa_rec.ATTRIBUTE16,
X_ATTRIBUTE17=> v_susa_rec.ATTRIBUTE17,
X_ATTRIBUTE18=> v_susa_rec.ATTRIBUTE18,
X_ATTRIBUTE19=> v_susa_rec.ATTRIBUTE19,
X_ATTRIBUTE20=> v_susa_rec.ATTRIBUTE20,
X_MODE => 'R'
);
igs_en_sua_api.update_unit_attempt (
X_ROWID => v_sua_rec.rowid,
X_PERSON_ID => v_sua_rec.person_id,
X_COURSE_CD => v_sua_rec.course_cd,
X_UNIT_CD => v_sua_rec.unit_cd,
X_CAL_TYPE => v_sua_rec.cal_type,
X_CI_SEQUENCE_NUMBER => v_sua_rec.ci_sequence_number,
X_VERSION_NUMBER => v_sua_rec.version_number,
X_LOCATION_CD => v_sua_rec.location_cd,
X_UNIT_CLASS => v_sua_rec.unit_class,
X_CI_START_DT => v_sua_rec.ci_start_dt,
X_CI_END_DT => v_sua_rec.ci_end_dt,
X_UOO_ID => v_sua_rec.uoo_id,
X_ENROLLED_DT => v_sua_rec.enrolled_dt,
X_UNIT_ATTEMPT_STATUS => 'DROPPED',
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_rec.administrative_unit_status,
X_DISCONTINUED_DT => nvl(trunc(v_sua_rec.discontinued_dt), trunc(SYSDATE)),
X_RULE_WAIVED_DT =>v_sua_rec.rule_waived_dt,
X_RULE_WAIVED_PERSON_ID =>v_sua_rec.rule_waived_person_id,
X_NO_ASSESSMENT_IND => v_sua_rec.no_assessment_ind,
X_SUP_UNIT_CD => v_sua_rec.sup_unit_cd,
X_SUP_VERSION_NUMBER => v_sua_rec.sup_version_number,
X_EXAM_LOCATION_CD => v_sua_rec.exam_location_cd,
X_ALTERNATIVE_TITLE => v_sua_rec.alternative_title,
X_OVERRIDE_ENROLLED_CP => v_sua_rec.override_enrolled_cp,
X_OVERRIDE_EFTSU => v_sua_rec.override_eftsu,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_rec.override_achievable_cp,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_rec.override_outcome_due_dt,
X_OVERRIDE_CREDIT_REASON => v_sua_rec.override_credit_reason,
X_ADMINISTRATIVE_PRIORITY => v_sua_rec.administrative_priority,
X_WAITLIST_DT => v_sua_rec.waitlist_dt,
X_DCNT_REASON_CD => v_sua_rec.dcnt_reason_cd,
X_MODE => 'R',
X_GS_VERSION_NUMBER => v_sua_rec.gs_version_number,
X_ENR_METHOD_TYPE => v_sua_rec.enr_method_type,
X_FAILED_UNIT_RULE => v_sua_rec.failed_unit_rule,
X_CART => v_sua_rec.cart,
X_RSV_SEAT_EXT_ID => v_sua_rec.rsv_seat_ext_id,
X_ORG_UNIT_CD => v_sua_rec.org_unit_cd,
X_GRADING_SCHEMA_CODE => v_sua_rec.grading_schema_code,
X_SESSION_ID => v_sua_rec.session_id,
X_DEG_AUD_DETAIL_ID => v_sua_rec.deg_aud_detail_id,
X_SUBTITLE => v_sua_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => v_sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => v_sua_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => v_sua_rec.attribute_category,
X_ATTRIBUTE1 => v_sua_rec.attribute1,
X_ATTRIBUTE2 => v_sua_rec.attribute2,
X_ATTRIBUTE3 => v_sua_rec.attribute3,
X_ATTRIBUTE4 => v_sua_rec.attribute4,
X_ATTRIBUTE5 => v_sua_rec.attribute5,
X_ATTRIBUTE6 => v_sua_rec.attribute6,
X_ATTRIBUTE7 => v_sua_rec.attribute7,
X_ATTRIBUTE8 => v_sua_rec.attribute8,
X_ATTRIBUTE9 => v_sua_rec.attribute9,
X_ATTRIBUTE10 => v_sua_rec.attribute10,
X_ATTRIBUTE11 => v_sua_rec.attribute11,
X_ATTRIBUTE12 => v_sua_rec.attribute12,
X_ATTRIBUTE13 => v_sua_rec.attribute13,
X_ATTRIBUTE14 => v_sua_rec.attribute14,
X_ATTRIBUTE15 => v_sua_rec.attribute15,
X_ATTRIBUTE16 => v_sua_rec.attribute16,
X_ATTRIBUTE17 => v_sua_rec.attribute17,
X_ATTRIBUTE18 => v_sua_rec.attribute18,
X_ATTRIBUTE19 => v_sua_rec.attribute19,
X_ATTRIBUTE20 => v_sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => v_sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM=> v_sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => v_sua_rec.core_indicator_code
);
ELSE -- Discontinue ; not delete
UPDATE IGS_EN_SU_ATTEMPT
SET discontinued_dt = v_spo_rec.decision_dt,
administrative_unit_status = v_administrative_unit_status
WHERE CURRENT OF c_sua;
igs_en_sua_api.update_unit_attempt(
X_ROWID => v_sua_rec.ROWID,
X_PERSON_ID => v_sua_rec.PERSON_ID,
X_COURSE_CD => v_sua_rec.COURSE_CD,
X_UNIT_CD => v_sua_rec.UNIT_CD,
X_CAL_TYPE => v_sua_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_sua_rec.CI_SEQUENCE_NUMBER,
X_VERSION_NUMBER => v_sua_rec.VERSION_NUMBER,
X_LOCATION_CD => v_sua_rec.LOCATION_CD,
X_UNIT_CLASS => v_sua_rec.UNIT_CLASS,
X_CI_START_DT => v_sua_rec.CI_START_DT,
X_CI_END_DT => v_sua_rec.CI_END_DT,
X_UOO_ID => v_sua_rec.UOO_ID,
X_ENROLLED_DT => v_sua_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => v_sua_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => v_administrative_unit_status,
X_DISCONTINUED_DT => v_spo_rec.decision_dt,
X_RULE_WAIVED_DT => v_sua_rec.RULE_WAIVED_DT,
X_RULE_WAIVED_PERSON_ID => v_sua_rec.RULE_WAIVED_PERSON_ID,
X_NO_ASSESSMENT_IND => v_sua_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => v_sua_rec.SUP_UNIT_CD,
X_SUP_VERSION_NUMBER => v_sua_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => v_sua_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => v_sua_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => v_sua_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => v_sua_rec.OVERRIDE_EFTSU,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => v_sua_rec.OVERRIDE_CREDIT_REASON,
X_ADMINISTRATIVE_PRIORITY => v_sua_rec.ADMINISTRATIVE_PRIORITY,
X_WAITLIST_DT => v_sua_rec.WAITLIST_DT,
X_DCNT_REASON_CD => v_sua_rec.DCNT_REASON_CD,
X_MODE => 'R',
X_GS_VERSION_NUMBER => v_sua_rec.GS_VERSION_NUMBER,
X_ENR_METHOD_TYPE => v_sua_rec.ENR_METHOD_TYPE,
X_FAILED_UNIT_RULE => v_sua_rec.FAILED_UNIT_RULE,
X_CART => v_sua_rec.CART,
X_RSV_SEAT_EXT_ID => v_sua_rec.RSV_SEAT_EXT_ID,
X_ORG_UNIT_CD => v_sua_rec.ORG_UNIT_CD,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => v_sua_rec.SESSION_ID,
X_GRADING_SCHEMA_CODE => v_sua_rec.GRADING_SCHEMA_CODE,
X_SUBTITLE => v_sua_rec.SUBTITLE,
X_DEG_AUD_DETAIL_ID => v_sua_rec.DEG_AUD_DETAIL_ID,
X_STUDENT_CAREER_TRANSCRIPT => v_sua_rec.STUDENT_CAREER_TRANSCRIPT,
X_STUDENT_CAREER_STATISTICS => v_sua_rec.STUDENT_CAREER_STATISTICS ,
X_ATTRIBUTE_CATEGORY => v_sua_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sua_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sua_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sua_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sua_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sua_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sua_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sua_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sua_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sua_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sua_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sua_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sua_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sua_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sua_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sua_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sua_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sua_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sua_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sua_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sua_rec.ATTRIBUTE20,
X_WAITLIST_MANUAL_IND => v_sua_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
X_WLST_PRIORITY_WEIGHT_NUM => v_sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => v_sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => v_sua_rec.core_indicator_code
);
UPDATE IGS_PR_RU_CA_TYPE
SET start_sequence_number = v_ci_sequence_number
WHERE CURRENT OF c_pra_prct;
IGS_PR_RU_CA_TYPE_PKG.UPDATE_ROW(
X_ROWID => v_pra_prct_rec.ROWID,
X_PROGRESSION_RULE_CAT => v_pra_prct_rec.PROGRESSION_RULE_CAT,
X_PRA_SEQUENCE_NUMBER => v_pra_prct_rec.PRA_SEQUENCE_NUMBER,
X_PRG_CAL_TYPE => v_pra_prct_rec.PRG_CAL_TYPE,
X_START_SEQUENCE_NUMBER => v_ci_sequence_number,
X_END_SEQUENCE_NUMBER => v_pra_prct_rec.END_SEQUENCE_NUMBER,
X_START_EFFECTIVE_PERIOD => v_pra_prct_rec.START_EFFECTIVE_PERIOD,
X_NUM_OF_APPLICATIONS => v_pra_prct_rec.NUM_OF_APPLICATIONS,
X_MODE => 'R'
);
UPDATE IGS_PR_RU_APPL
SET logical_delete_dt = SYSDATE
WHERE CURRENT OF c_pra_upd2;
IGS_PR_RU_APPL_pkg.UPDATE_ROW(
X_ROWID => v_pra_upd_rec.ROWID,
X_PROGRESSION_RULE_CAT => v_pra_upd_rec.PROGRESSION_RULE_CAT,
X_SEQUENCE_NUMBER => v_pra_upd_rec.SEQUENCE_NUMBER,
X_S_RELATION_TYPE => v_pra_upd_rec.S_RELATION_TYPE,
X_PROGRESSION_RULE_CD => v_pra_upd_rec.PROGRESSION_RULE_CD,
X_REFERENCE_CD => v_pra_upd_rec.REFERENCE_CD,
X_RUL_SEQUENCE_NUMBER => v_pra_upd_rec.RUL_SEQUENCE_NUMBER,
X_ATTENDANCE_TYPE => v_pra_upd_rec.ATTENDANCE_TYPE,
X_OU_ORG_UNIT_CD => v_pra_upd_rec.OU_ORG_UNIT_CD,
X_OU_START_DT => v_pra_upd_rec.OU_START_DT,
X_COURSE_TYPE => v_pra_upd_rec.COURSE_TYPE,
X_CRV_COURSE_CD => v_pra_upd_rec.CRV_COURSE_CD,
X_CRV_VERSION_NUMBER => v_pra_upd_rec.CRV_VERSION_NUMBER,
X_SCA_PERSON_ID => v_pra_upd_rec.SCA_PERSON_ID,
X_SCA_COURSE_CD => v_pra_upd_rec.SCA_COURSE_CD,
X_PRO_PROGRESSION_RULE_CAT => v_pra_upd_rec.PRO_PROGRESSION_RULE_CAT,
X_PRO_PRA_SEQUENCE_NUMBER => v_pra_upd_rec.PRO_PRA_SEQUENCE_NUMBER,
X_PRO_SEQUENCE_NUMBER => v_pra_upd_rec.PRO_SEQUENCE_NUMBER,
X_SPO_PERSON_ID => v_pra_upd_rec.SPO_PERSON_ID,
X_SPO_COURSE_CD => v_pra_upd_rec.SPO_COURSE_CD,
X_SPO_SEQUENCE_NUMBER => v_pra_upd_rec.SPO_SEQUENCE_NUMBER,
X_LOGICAL_DELETE_DT => SYSDATE,
X_MESSAGE => v_pra_upd_rec.MESSAGE,
X_MODE => 'R',
X_MIN_CP => v_pra_upd_rec.MIN_CP,
X_MAX_CP => v_pra_upd_rec.MAX_CP,
X_IGS_PR_CLASS_STD_ID => v_pra_upd_rec.IGS_PR_CLASS_STD_ID
);
UPDATE IGS_EN_STDNT_PS_ATT
SET discontinued_dt = NULL,
discontinuation_reason_cd = NULL
WHERE CURRENT OF c_sca1;
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_rec.ROWID,
X_PERSON_ID => v_sca_rec.PERSON_ID,
X_COURSE_CD => v_sca_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_sca_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => v_sca_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => v_sca_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_sca_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => v_sca_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => v_sca_rec.ADM_SEQUENCE_NUMBER,
X_VERSION_NUMBER => v_sca_rec.VERSION_NUMBER,
X_CAL_TYPE => v_sca_rec.CAL_TYPE,
X_LOCATION_CD => v_sca_rec.LOCATION_CD,
X_ATTENDANCE_MODE => v_sca_rec.ATTENDANCE_MODE,
X_ATTENDANCE_TYPE => v_sca_rec.ATTENDANCE_TYPE,
X_COO_ID => v_sca_rec.COO_ID,
X_STUDENT_CONFIRMED_IND => v_sca_rec.STUDENT_CONFIRMED_IND,
X_COMMENCEMENT_DT => v_sca_rec.COMMENCEMENT_DT,
X_COURSE_ATTEMPT_STATUS => v_sca_rec.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => v_sca_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_sca_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => NULL,
X_DISCONTINUATION_REASON_CD => NULL,
X_LAPSED_DT => v_sca_rec.LAPSED_DT,
X_FUNDING_SOURCE => v_sca_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => v_sca_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => v_sca_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => v_sca_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
X_LAST_DATE_OF_ATTENDANCE => v_sca_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => v_sca_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => v_sca_rec.IGS_PR_CLASS_STD_ID,
X_PRIMARY_PROGRAM_TYPE => v_sca_rec.PRIMARY_PROGRAM_TYPE,
X_PRIMARY_PROG_TYPE_SOURCE => v_sca_rec.PRIMARY_PROG_TYPE_SOURCE,
X_CATALOG_CAL_TYPE => v_sca_rec.CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM => v_sca_rec.CATALOG_SEQ_NUM,
X_KEY_PROGRAM => v_sca_rec.KEY_PROGRAM,
X_MANUAL_OVR_CMPL_DT_IND => v_sca_rec.manual_ovr_cmpl_dt_ind,
X_OVERRIDE_CMPL_DT => v_sca_rec.OVERRIDE_CMPL_DT,
X_ATTRIBUTE_CATEGORY => v_sca_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sca_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_rec.ATTRIBUTE19,
x_ATTRIBUTE20 => v_sca_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_rec.future_dated_trans_flag
);
UPDATE IGS_PR_RU_APPL
SET logical_delete_dt = SYSDATE
WHERE CURRENT OF c_pra_upd1;
IGS_PR_RU_APPL_PKG.UPDATE_ROW(
X_ROWID => v_pra_upd_rec.ROWID,
X_PROGRESSION_RULE_CAT => v_pra_upd_rec.PROGRESSION_RULE_CAT,
X_SEQUENCE_NUMBER => v_pra_upd_rec.SEQUENCE_NUMBER,
X_S_RELATION_TYPE => v_pra_upd_rec.S_RELATION_TYPE,
X_PROGRESSION_RULE_CD => v_pra_upd_rec.PROGRESSION_RULE_CD,
X_REFERENCE_CD => v_pra_upd_rec.REFERENCE_CD,
X_RUL_SEQUENCE_NUMBER => v_pra_upd_rec.RUL_SEQUENCE_NUMBER,
X_ATTENDANCE_TYPE => v_pra_upd_rec.ATTENDANCE_TYPE,
X_OU_ORG_UNIT_CD => v_pra_upd_rec.OU_ORG_UNIT_CD,
X_OU_START_DT => v_pra_upd_rec.OU_START_DT,
X_COURSE_TYPE => v_pra_upd_rec.COURSE_TYPE,
X_CRV_COURSE_CD => v_pra_upd_rec.CRV_COURSE_CD,
X_CRV_VERSION_NUMBER => v_pra_upd_rec.CRV_VERSION_NUMBER,
X_SCA_PERSON_ID => v_pra_upd_rec.SCA_PERSON_ID,
X_SCA_COURSE_CD => v_pra_upd_rec.SCA_COURSE_CD,
X_PRO_PROGRESSION_RULE_CAT => v_pra_upd_rec.PRO_PROGRESSION_RULE_CAT,
X_PRO_PRA_SEQUENCE_NUMBER => v_pra_upd_rec.PRO_PRA_SEQUENCE_NUMBER,
X_PRO_SEQUENCE_NUMBER => v_pra_upd_rec.PRO_SEQUENCE_NUMBER,
X_SPO_PERSON_ID => v_pra_upd_rec.SPO_PERSON_ID,
X_SPO_COURSE_CD => v_pra_upd_rec.SPO_COURSE_CD,
X_SPO_SEQUENCE_NUMBER => v_pra_upd_rec.SPO_SEQUENCE_NUMBER,
X_LOGICAL_DELETE_DT => SYSDATE,
X_MESSAGE => v_pra_upd_rec.MESSAGE,
X_MODE => 'R',
X_MIN_CP => v_pra_upd_rec.MIN_CP,
X_MAX_CP => v_pra_upd_rec.MAX_CP,
X_IGS_PR_CLASS_STD_ID => v_pra_upd_rec.IGS_PR_CLASS_STD_ID
);
SELECT ci.cal_type,
ci.sequence_number,
ci.alternate_code,
IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD(
ci.cal_type,
ci.sequence_number) acad_alternate_code
FROM IGS_CA_INST ci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE (p_prg_cal_type IS NULL OR
(ci.cal_type = p_prg_cal_type AND
ci.sequence_number = p_prg_sequence_number)) AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = cst_progress AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active AND
EXISTS (
SELECT 'X'
FROM IGS_CA_DA_INST dai1
WHERE ci.cal_type = dai1.cal_type AND
ci.sequence_number = dai1.ci_sequence_number AND
(
EXISTS ( SELECT 1 FROM IGS_PR_S_PRG_CONF spc1
WHERE dai1.dt_alias = spc1.apply_start_dt_alias)
OR
EXISTS (SELECT 1 FROM IGS_PR_S_OU_PRG_CONF sopc1
WHERE dai1.dt_alias = sopc1.apply_start_dt_alias)
OR
EXISTS (SELECT 1 FROM IGS_PR_S_CRV_PRG_CON scpc1
WHERE dai1.dt_alias = scpc1.apply_start_dt_alias)) AND
IGS_CA_GEN_001.CALP_GET_ALIAS_VAL (
dai1.dt_alias,
dai1.sequence_number,
ci.cal_type,
ci.sequence_number
) <= TRUNC(SYSDATE)) AND
EXISTS (
SELECT 'X'
FROM IGS_CA_DA_INST dai2
WHERE ci.cal_type = dai2.cal_type AND
ci.sequence_number = dai2.ci_sequence_number AND
(
EXISTS ( SELECT 1 FROM IGS_PR_S_PRG_CONF spc2
WHERE dai2.dt_alias = spc2.apply_end_dt_alias)
OR
EXISTS (SELECT 1 FROM IGS_PR_S_OU_PRG_CONF sopc2
WHERE dai2.dt_alias = sopc2.apply_end_dt_alias)
OR
EXISTS (SELECT 1 FROM IGS_PR_S_CRV_PRG_CON scpc2
WHERE dai2.dt_alias = scpc2.apply_end_dt_alias) )
AND
NVL( IGS_CA_GEN_001.CALP_GET_ALIAS_VAL (
dai2.dt_alias,
dai2.sequence_number,
ci.cal_type,
ci.sequence_number
), SYSDATE) >= TRUNC(SYSDATE)
) ;
SELECT scae.enrolment_cat enrolment_cat
FROM igs_as_sc_atmpt_enr scae,
igs_ca_inst_all ci
WHERE scae.person_id = cp_person_id
AND scae.course_cd = cp_course_cd
AND scae.cal_type = ci.cal_type
AND scae.ci_sequence_number = ci.sequence_number
ORDER BY ci.end_dt DESC;
stmt_str := 'SELECT sca.person_id, sca.course_cd, sca.version_number ';
'SELECT 1 ' ||
'FROM igs_pr_stdnt_pr_ck spc ' ||
'WHERE sca.person_id = spc.person_id ' ||
'AND sca.course_cd = spc.course_cd ' ||
'AND spc.prg_cal_type = ''' || p_ci_cal_type || ''' ' ||
'AND spc.prg_ci_sequence_number = ' || NVL (p_ci_sequence_number, 0) || ') ' ||
'AND sca.person_id = sua.person_id ' ||
'AND sca.course_cd = sua.course_cd ' ||
'AND sua.unit_attempt_status NOT IN (''UNCONFIRM'', ''INVALID'') ' ||
'AND cir.sup_cal_type = ''' || p_ci_cal_type || ''' ' ||
'AND cir.sup_ci_sequence_number = ' || NVL (p_ci_sequence_number, 0) || ' ' ||
'AND sua.cal_type = cir.sub_cal_type ' ||
'AND sua.ci_sequence_number = cir.sub_ci_sequence_number ';
SELECT DISTINCT st.person_id,
st.s_student_todo_type,
st.sequence_number,
str.course_cd,
sca.version_number
FROM IGS_PE_STD_TODO st,
IGS_PE_STD_TODO_REF str,
IGS_EN_STDNT_PS_ATT sca
WHERE st.s_student_todo_type = cst_prg_check AND
st.logical_delete_dt IS NULL AND
st.person_id = str.person_id AND
st.s_student_todo_type = str.s_student_todo_type AND
st.sequence_number = str.sequence_number AND
sca.person_id = st.person_id AND
sca.course_cd = str.course_cd AND
str.course_cd LIKE NVL(p_course_cd, str.course_cd) AND
NVL(p_location_cd, sca.location_cd)
= sca.location_cd AND
(p_org_unit_cd IS NULL OR
p_ou_start_dt IS NULL OR
IGS_PR_GEN_001.prgp_get_crv_cmt (
sca.course_cd,
sca.version_number,
p_org_unit_cd,
p_ou_start_dt) = 'Y') AND
(p_group_id IS NULL OR
sca.person_id IN (
SELECT person_id
FROM IGS_PE_PIGM_PIDGRP_MEM_V
WHERE group_id = p_group_id)) AND
(p_attendance_mode IS NULL OR
sca.attendance_mode = p_attendance_mode) AND
(p_progression_status IS NULL OR
sca.progression_status = p_progression_status) AND
(p_enrolment_cat IS NULL OR
EXISTS (
SELECT 'X'
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci1
WHERE sca.person_id = scae.person_id AND
sca.course_cd = scae.course_cd AND
scae.enrolment_cat = p_enrolment_cat AND
ci1.cal_type = scae.cal_type AND
ci1.sequence_number = scae.ci_sequence_number AND
ci1.end_dt =
(SELECT MAX(ci2.end_dt)
FROM IGS_AS_SC_ATMPT_ENR scae2,
IGS_CA_INST ci2
WHERE scae2.person_id = scae.person_id AND
scae2.course_cd = scae.course_cd AND
ci2.cal_type = scae2.cal_type AND
ci2.sequence_number = scae2.ci_sequence_number)));
SELECT st.*,
st.ROWID
FROM IGS_PE_STD_TODO st
WHERE st.person_id = cp_person_id AND
st.s_student_todo_type = cp_s_student_todo_type AND
st.sequence_number = cp_sequence_number
FOR UPDATE NOWAIT;
SELECT crv.course_type
FROM IGS_PS_VER crv
WHERE crv.course_cd = cp_course_cd AND
crv.version_number = cp_version_number;
SELECT str.*,
str.ROWID
FROM IGS_PE_STD_TODO_REF str
WHERE str.person_id = cp_person_id AND
str.s_student_todo_type = cp_s_student_todo_type AND
str.sequence_number = cp_sequence_number AND
str.course_cd = cp_course_cd AND
str.logical_delete_dt IS NULL
FOR UPDATE NOWAIT;
SELECT 'x'
FROM IGS_PE_STD_TODO_REF str
WHERE str.person_id = cp_person_id AND
str.s_student_todo_type = cp_s_student_todo_type AND
str.sequence_number = cp_sequence_number AND
str.logical_delete_dt IS NULL;
SELECT spc.prg_cal_type,
spc.prg_ci_sequence_number
FROM IGS_PR_STDNT_PR_CK spc
WHERE spc.person_id = cp_person_id AND
spc.course_cd = cp_course_cd;
SELECT spc.prg_cal_type,
spc.prg_ci_sequence_number
FROM IGS_PR_STDNT_PR_CK spc,
IGS_CA_INST ci1
WHERE spc.person_id = cp_person_id AND
spc.course_cd = cp_course_cd AND
ci1.cal_type = spc.prg_cal_type AND
ci1.sequence_number = spc.prg_ci_sequence_number AND
ci1.start_dt >=
-- On or after the first progression period linked to the student unit
-- attempt
(SELECT MIN(ci2.start_dt)
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci2,
IGS_CA_STAT cs,
IGS_CA_TYPE cat
WHERE cir.sub_cal_type = cp_cal_type AND
cir.sub_ci_sequence_number = cp_ci_sequence_number AND
ci2.cal_type = cir.sup_cal_type AND
ci2.sequence_number = cir.sup_ci_sequence_number AND
cat.cal_type = ci2.cal_type AND
cat.s_cal_cat = cst_progress AND
cs.CAL_STATUS = ci2.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active);
/* UPDATE IGS_PE_STD_TODO_REF
SET logical_delete_dt = SYSDATE
WHERE CURRENT OF c_str;
igs_pe_std_todo_ref_pkg.update_row (
x_rowid => v_str_rec.ROWID,
x_person_id => v_str_rec.person_id,
x_s_student_todo_type => v_str_rec.s_student_todo_type,
x_sequence_number => v_str_rec.sequence_number,
x_reference_number => v_str_rec.reference_number,
x_cal_type => v_str_rec.cal_type,
x_ci_sequence_number => v_str_rec.ci_sequence_number,
x_course_cd => v_str_rec.course_cd,
x_unit_cd => v_str_rec.unit_cd,
x_other_reference => v_str_rec.other_reference,
x_logical_delete_dt => SYSDATE,
x_mode => 'R',
x_uoo_id => v_str_rec.uoo_id
);
UPDATE IGS_PE_STD_TODO
SET logical_delete_dt = SYSDATE
WHERE CURRENT OF c_st_lck;
IGS_PE_STD_TODO_PKG.UPDATE_ROW(
X_ROWID => v_st_lck_rec.ROWID,
X_PERSON_ID => v_st_lck_rec.PERSON_ID,
X_S_STUDENT_TODO_TYPE => v_st_lck_rec.S_STUDENT_TODO_TYPE,
X_SEQUENCE_NUMBER => v_st_lck_rec.SEQUENCE_NUMBER,
X_TODO_DT => v_st_lck_rec.TODO_DT,
X_LOGICAL_DELETE_DT => SYSDATE,
X_MODE => 'R'
);
v_sprc_insert_count INTEGER DEFAULT 0;
SELECT sca.course_attempt_status,
sca.version_number,
crv.course_type
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
crv.course_cd = sca.course_cd AND
crv.version_number = sca.version_number;
SELECT IGS_PR_SPO_SEQ_NUM_S.NEXTVAL
FROM DUAL;
SELECT crv.responsible_org_unit_cd,
crv.responsible_ou_start_dt
FROM IGS_PS_VER crv
WHERE crv.course_cd = p_course_cd AND
crv.version_number = p_sca_version_number;
SELECT poc.course_cd
FROM IGS_PR_OU_PS poc
WHERE poc.progression_rule_cat = p_progression_rule_cat AND
poc.pra_sequence_number = p_original_pra_sequence_number AND
poc.pro_sequence_number = p_pro_sequence_number;
SELECT 'X'
FROM IGS_PR_OU_TYPE pot,
IGS_FI_ENC_DFLT_EFT etde
WHERE pot.progression_outcome_type = p_progression_outcome_type AND
pot.s_progression_outcome_type IN (
cst_suspension,
cst_exclusion) AND
pot.encumbrance_type = etde.encumbrance_type AND
etde.s_encmb_effect_type IN (
cst_sus_course,
cst_exc_course);
SELECT popu.unit_cd,
popu.s_unit_type
FROM IGS_PR_OU_UNIT popu
WHERE popu.progression_rule_cat = p_progression_rule_cat AND
popu.pra_sequence_number = p_original_pra_sequence_number AND
popu.pro_sequence_number = p_pro_sequence_number;
SELECT pous.unit_set_cd,
pous.us_version_number
FROM IGS_PR_OU_UNIT_SET pous
WHERE pous.progression_rule_cat = p_progression_rule_cat AND
pous.pra_sequence_number = p_original_pra_sequence_number AND
pous.pro_sequence_number = p_pro_sequence_number;
SELECT poa.award_cd
FROM IGS_PR_OU_AWD poa
WHERE poa.progression_rule_cat = p_progression_rule_cat AND
poa.pra_sequence_number = p_original_pra_sequence_number AND
poa.pro_sequence_number = p_pro_sequence_number;
SELECT pfnd.fund_code
FROM IGS_PR_OU_FND pfnd
WHERE pfnd.progression_rule_cat = p_progression_rule_cat AND
pfnd.pra_sequence_number = p_original_pra_sequence_number AND
pfnd.pro_sequence_number = p_pro_sequence_number;
SELECT pra.progression_rule_cat,
pra.sequence_number
FROM IGS_PR_RU_APPL pra
WHERE pra.s_relation_type = cst_pro AND
pra.pro_pra_sequence_number = p_original_pra_sequence_number AND
pra.pro_sequence_number = p_pro_sequence_number AND
pra.logical_delete_dt IS NULL;
SELECT 'X'
FROM IGS_PS_UNIT_VER uv,
IGS_PS_UNIT_STAT us
WHERE uv.unit_cd = cp_unit_cd AND
uv.expiry_dt IS NULL AND
us.unit_status = uv.unit_status AND
us.s_unit_status = cst_active;
igs_pr_stdnt_pr_ou_pkg.insert_row (
x_rowid => lv_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_sequence_number => v_spo_sequence_number,
x_prg_cal_type => p_prg_cal_type,
x_prg_ci_sequence_number => p_prg_sequence_number,
x_rule_check_dt => p_rule_check_dt,
x_progression_rule_cat => p_progression_rule_cat,
x_pra_sequence_number => p_pra_sequence_number,
x_pro_sequence_number => p_pro_sequence_number,
x_progression_outcome_type => p_progression_outcome_type,
x_duration => p_duration,
x_duration_type => p_duration_type,
x_decision_status => v_decision_status,
x_decision_dt => v_decision_dt,
x_decision_org_unit_cd => v_decision_org_unit_cd,
x_decision_ou_start_dt => v_decision_ou_start_dt,
x_applied_dt => v_appeal_expiry_dt,
x_show_cause_expiry_dt => v_show_cause_expiry_dt,
x_show_cause_dt => NULL,
x_show_cause_outcome_dt => NULL,
x_show_cause_outcome_type => NULL,
x_appeal_expiry_dt => v_appeal_expiry_dt,
x_appeal_dt => NULL,
x_appeal_outcome_dt => NULL,
x_appeal_outcome_type => NULL,
x_encmb_course_group_cd => p_encmb_course_group_cd,
x_restricted_enrolment_cp => p_restricted_enrolment_cp,
x_restricted_attendance_type => p_restricted_attendance_type,
x_comments => NULL,
x_show_cause_comments => NULL,
x_appeal_comments => NULL,
x_expiry_dt => NULL,
x_pro_pra_sequence_number => p_original_pra_sequence_number, --NULL, -- Modified by Prajeesh as no value was passed and primary key violation happening
x_mode => 'R',
x_org_id => lv_org_id
);
IGS_PR_STDNT_PR_PS_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PERSON_ID =>p_person_id,
X_SPO_COURSE_CD => p_course_cd,
X_SPO_SEQUENCE_NUMBER => v_spo_sequence_number,
X_COURSE_CD =>v_poc_rec.course_cd,
X_MODE =>'R');
IGS_PR_STDNT_PR_PS_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PERSON_ID =>p_person_id,
X_SPO_COURSE_CD => p_course_cd,
X_SPO_SEQUENCE_NUMBER => v_spo_sequence_number,
X_COURSE_CD => p_course_cd,
X_MODE =>'R'
);
IGS_PR_STDNT_PR_UNIT_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PERSON_ID =>p_person_id,
X_COURSE_CD =>p_course_cd,
X_SPO_SEQUENCE_NUMBER =>v_spo_sequence_number,
X_UNIT_CD => v_popu_rec.unit_cd,
X_S_UNIT_TYPE => v_popu_rec.s_unit_type,
X_MODE =>'R'
);
IGS_PR_SDT_PR_UNT_ST_PKG.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => P_COURSE_CD,
X_SPO_SEQUENCE_NUMBER =>v_spo_sequence_number,
X_UNIT_SET_CD =>v_pous_rec.unit_set_cd,
X_VERSION_NUMBER =>v_pous_rec.us_version_number,
X_MODE => 'R'
);
IGS_PR_STDNT_PR_AWD_PKG.INSERT_ROW (
X_ROWID =>lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => P_COURSE_CD,
X_SPO_SEQUENCE_NUMBER =>v_spo_sequence_number,
X_AWARD_CD =>v_poa_rec.AWARD_cd,
X_MODE => 'R'
);
igs_pr_stdnt_pr_fnd_pkg.insert_row (
X_ROWID =>lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_SPO_SEQUENCE_NUMBER => v_spo_sequence_number,
X_FUND_CODE => v_pfnd_rec.fund_code,
X_MODE => 'R'
);
SELECT praov.sequence_number,
praov.number_of_failures,
praov.progression_outcome_type,
praov.apply_automatically_ind,
praov.prg_rule_repeat_fail_type,
praov.duration,
praov.duration_type,
praov.encmb_course_group_cd,
praov.restricted_enrolment_cp,
praov.restricted_attendance_type,
praov.original_pra_sequence_number
FROM IGS_PR_RULE_OUT_V praov
WHERE praov.progression_rule_cat = p_progression_rule_cat AND
praov.pra_sequence_number = p_pra_sequence_number AND
-- anilk, bug#2784198
praov.logical_delete_dt IS NULL AND
-- this condition has been modifed, when the positive outcome ind
-- was added. For a postive outcome category there will be no
-- rule repeat fail type and number of failure values
-- since there are no failures for a positive outcome.
-- Hence addition check has been added for the above case
-- by checking if the repeat fail type is null and outcome ind = 'Y'
-- otherwise if the repeat fail type is not null then the existing
-- check holds good.
(
(praov.prg_rule_repeat_fail_type IS NOT NULL AND
praov.prg_rule_repeat_fail_type = cp_consecrpt)
OR
(praov.prg_rule_repeat_fail_type IS NULL
AND praov.POSITIVE_OUTCOME_IND = 'Y')
) AND
NVL(praov.number_of_failures, cp_consecutive_failures) <= cp_consecutive_failures
ORDER BY praov.number_of_failures DESC;
SELECT spo.*,
spo.ROWID
FROM IGS_PR_STDNT_PR_OU spo
WHERE spo.person_id = p_person_id AND
spo.course_cd = p_course_cd AND
spo.prg_cal_type = p_prg_cal_type AND
spo.prg_ci_sequence_number = p_prg_sequence_number AND
spo.progression_rule_cat = p_progression_rule_cat AND
spo.pra_sequence_number = p_pra_sequence_number AND
spo.decision_status IN (
cst_pending,
cst_approved) AND
NVL(spo.expiry_dt,igs_ge_date.igsdate('9999/01/01')) > TRUNC(SYSDATE)
FOR UPDATE NOWAIT;
SELECT spc.ROWID
FROM IGS_PR_STDNT_PR_PS spc
WHERE spc.person_id = p_person_id AND
spc.spo_course_cd = p_course_cd AND
spc.spo_sequence_number = cp_sequence_number
FOR UPDATE NOWAIT;
SELECT spus.ROWID
FROM IGS_PR_SDT_PR_UNT_ST spus
WHERE spus.person_id = p_person_id AND
spus.course_cd = p_course_cd AND
spus.spo_sequence_number = cp_sequence_number
FOR UPDATE NOWAIT;
SELECT spaw.ROWID
FROM IGS_PR_STDNT_PR_AWD spaw
WHERE spaw.person_id = p_person_id AND
spaw.course_cd = p_course_cd AND
spaw.spo_sequence_number = cp_sequence_number
FOR UPDATE NOWAIT;
SELECT spu.ROWID
FROM IGS_PR_STDNT_PR_UNIT spu
WHERE spu.person_id = p_person_id AND
spu.course_cd = p_course_cd AND
spu.spo_sequence_number = cp_sequence_number
FOR UPDATE NOWAIT;
SELECT pra.progression_rule_cat,
pra.sequence_number, pra.ROWID
FROM IGS_PR_RU_APPL pra
WHERE pra.spo_person_id = p_person_id AND
pra.spo_course_cd = p_course_cd AND
pra.spo_sequence_number = cp_sequence_number AND
pra.s_relation_type = cst_spo
FOR UPDATE NOWAIT;
SELECT prct.ROWID
FROM IGS_PR_RU_CA_TYPE prct
WHERE prct.progression_rule_cat = cp_progression_rule_cat AND
prct.pra_sequence_number = cp_pra_sequence_number
FOR UPDATE NOWAIT;
SELECT pro.*
FROM IGS_PR_RU_OU pro
WHERE pro.progression_rule_cat = cp_progression_rule_cat AND
pro.pra_sequence_number = cp_pra_sequence_number
FOR UPDATE NOWAIT;
SELECT poc.progression_rule_cat, poc.ROWID
FROM IGS_PR_OU_PS poc
WHERE poc.progression_rule_cat = cp_progression_rule_cat AND
poc.pra_sequence_number = cp_pra_sequence_number AND
poc.pro_sequence_number = cp_pro_sequence_number
FOR UPDATE NOWAIT;
SELECT pous.ROWID
FROM IGS_PR_OU_UNIT_SET pous
WHERE pous.progression_rule_cat = cp_progression_rule_cat AND
pous.pra_sequence_number = cp_pra_sequence_number AND
pous.pro_sequence_number = cp_pro_sequence_number
FOR UPDATE NOWAIT;
SELECT poa.ROWID
FROM IGS_PR_OU_AWD poa
WHERE poa.progression_rule_cat = cp_progression_rule_cat AND
poa.pra_sequence_number = cp_pra_sequence_number AND
poa.pro_sequence_number = cp_pro_sequence_number
FOR UPDATE NOWAIT;
SELECT popu.ROWID
FROM IGS_PR_OU_UNIT popu
WHERE popu.progression_rule_cat = cp_progression_rule_cat AND
popu.pra_sequence_number = cp_pra_sequence_number AND
popu.pro_sequence_number = cp_pro_sequence_number
FOR UPDATE NOWAIT;
SELECT sfnd.rowid
FROM igs_pr_stdnt_pr_fnd sfnd
WHERE sfnd.person_id = p_person_id AND
sfnd.course_cd = p_course_cd AND
sfnd.spo_sequence_number = cp_spo_sequence_number
FOR UPDATE NOWAIT;
SELECT pfnd.ROWID
FROM IGS_PR_OU_FND pfnd
WHERE pfnd.progression_rule_cat = cp_progression_rule_cat AND
pfnd.pra_sequence_number = cp_pra_sequence_number AND
pfnd.pro_sequence_number = cp_pro_sequence_number
FOR UPDATE NOWAIT;
IGS_PR_STDNT_PR_PS_PKG.DELETE_ROW(
v_spc_rec.ROWID
);
IGS_PR_SDT_PR_UNT_ST_PKG.DELETE_ROW(
v_spus_rec.ROWID
);
IGS_PR_STDNT_PR_AWD_PKG.DELETE_ROW(
v_spaw_rec.ROWID
);
IGS_PR_STDNT_PR_UNIT_PKG.DELETE_ROW(
v_spu_rec.ROWID
);
igs_pr_stdnt_pr_fnd_pkg.delete_row(v_sfnd_rec.ROWID);
IGS_PR_RU_CA_TYPE_PKG.DELETE_ROW(
v_prct_rec.ROWID
);
IGS_PR_OU_PS_PKG.DELETE_ROW(
v_poc_rec.ROWID
);
IGS_PR_OU_UNIT_SET_PKG.DELETE_ROW(
v_pous_rec.ROWID
);
IGS_PR_OU_AWD_PKG.DELETE_ROW(v_poa_rec.ROWID);
IGS_PR_OU_UNIT_PKG.DELETE_ROW(
v_popu_rec.ROWID
);
IGS_PR_OU_FND_PKG.DELETE_ROW (v_pfnd_rec.ROWID);
IGS_PR_RU_OU_PKG.UPDATE_ROW(
X_ROWID => v_pro_rec.ROW_ID ,
X_PROGRESSION_RULE_CAT => v_pro_rec.PROGRESSION_RULE_CAT ,
X_PRA_SEQUENCE_NUMBER => v_pro_rec.PRA_SEQUENCE_NUMBER,
X_SEQUENCE_NUMBER => v_pro_rec.SEQUENCE_NUMBER,
X_NUMBER_OF_FAILURES => v_pro_rec.NUMBER_OF_FAILURES ,
X_PROGRESSION_OUTCOME_TYPE => v_pro_rec.PROGRESSION_OUTCOME_TYPE ,
X_APPLY_AUTOMATICALLY_IND => v_pro_rec.APPLY_AUTOMATICALLY_IND ,
X_PRG_RULE_REPEAT_FAIL_TYPE => v_pro_rec.PRG_RULE_REPEAT_FAIL_TYPE ,
X_OVERRIDE_SHOW_CAUSE_IND => v_pro_rec.OVERRIDE_SHOW_CAUSE_IND ,
X_OVERRIDE_APPEAL_IND => v_pro_rec.OVERRIDE_APPEAL_IND ,
X_DURATION => v_pro_rec.DURATION ,
X_DURATION_TYPE => v_pro_rec.DURATION_TYPE ,
X_RANK => v_pro_rec.RANK ,
X_ENCMB_COURSE_GROUP_CD => v_pro_rec.ENCMB_COURSE_GROUP_CD ,
X_RESTRICTED_ENROLMENT_CP => v_pro_rec.RESTRICTED_ENROLMENT_CP ,
X_RESTRICTED_ATTENDANCE_TYPE => v_pro_rec.RESTRICTED_ATTENDANCE_TYPE ,
X_COMMENTS => v_pro_rec.COMMENTS ,
-- anilk, bug#2784198
X_LOGICAL_DELETE_DT => FND_DATE.DATE_TO_CANONICAL(SYSDATE)
);
IGS_PR_RU_APPL_PKG.DELETE_ROW(v_pra_rec.ROWID);
IGS_PR_STDNT_PR_OU_PKG.DELETE_ROW(
v_spo_rec.ROWID
);
/* UPDATE IGS_PR_STDNT_PR_OU spo
SET spo.decision_status = cst_removed
WHERE CURRENT OF c_spo;
IGS_PR_STDNT_PR_OU_PKG.UPDATE_ROW(
x_rowid => v_spo_rec.ROWID,
x_person_id => v_spo_rec.person_id,
x_course_cd => v_spo_rec.course_cd,
x_sequence_number => v_spo_rec.sequence_number,
x_prg_cal_type => v_spo_rec.prg_cal_type,
x_prg_ci_sequence_number => v_spo_rec.prg_ci_sequence_number,
x_rule_check_dt => v_spo_rec.rule_check_dt,
x_progression_rule_cat => v_spo_rec.progression_rule_cat,
x_pra_sequence_number => v_spo_rec.pra_sequence_number,
x_pro_sequence_number => v_spo_rec.pro_sequence_number,
x_progression_outcome_type => v_spo_rec.progression_outcome_type,
x_duration => v_spo_rec.duration,
x_duration_type => v_spo_rec.duration_type,
x_decision_status => cst_removed,
x_decision_dt => v_spo_rec.decision_dt,
x_decision_org_unit_cd => v_spo_rec.decision_org_unit_cd,
x_decision_ou_start_dt => v_spo_rec.decision_ou_start_dt,
x_applied_dt => v_spo_rec.applied_dt,
x_show_cause_expiry_dt => v_spo_rec.show_cause_expiry_dt,
x_show_cause_dt => v_spo_rec.show_cause_dt,
x_show_cause_outcome_dt => v_spo_rec.show_cause_outcome_dt,
x_show_cause_outcome_type => v_spo_rec.show_cause_outcome_type,
x_appeal_expiry_dt => v_spo_rec.appeal_expiry_dt,
x_appeal_dt => v_spo_rec.appeal_dt,
x_appeal_outcome_dt => v_spo_rec.appeal_outcome_dt,
x_appeal_outcome_type => v_spo_rec.appeal_outcome_type,
x_encmb_course_group_cd => v_spo_rec.encmb_course_group_cd,
x_restricted_enrolment_cp => v_spo_rec.restricted_enrolment_cp,
x_restricted_attendance_type => v_spo_rec.restricted_attendance_type,
x_comments => v_spo_rec.comments,
x_show_cause_comments => v_spo_rec.show_cause_comments,
x_appeal_comments => v_spo_rec.appeal_comments,
x_expiry_dt => v_spo_rec.expiry_dt,
x_pro_pra_sequence_number => v_spo_rec.pro_pra_sequence_number,
x_mode => 'R'
);
SELECT cir.sub_cal_type,
cir.sub_ci_sequence_number
FROM IGS_CA_INST ci,
IGS_CA_INST_REL cir,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_prg_cal_type AND
cir.sup_ci_sequence_number = p_prg_sequence_number AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = cst_load AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active;
SELECT cir.sub_cal_type,
cir.sub_ci_sequence_number
FROM IGS_CA_INST ci,
IGS_CA_INST_REL cir,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_prg_cal_type AND
cir.sup_ci_sequence_number = p_prg_sequence_number AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = cst_load AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active;
SELECT cstd.class_standing
FROM igs_pr_class_std cstd
WHERE cstd.igs_pr_class_std_id = p_igs_pr_class_std_id;
SELECT cir.sub_cal_type,
cir.sub_ci_sequence_number
FROM IGS_CA_INST ci,
IGS_CA_INST_REL cir,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_prg_cal_type AND
cir.sup_ci_sequence_number = p_prg_sequence_number AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
cat.cal_type = ci.cal_type AND
cat.s_cal_cat = cst_load AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_CAL_STATUS = cst_active;
SELECT pr.rul_sequence_number
FROM IGS_PR_RULE pr
WHERE pr.progression_rule_cat = cp_progression_rule_cat AND
pr.progression_rule_cd = cp_progression_rule_cd;
SELECT sprc.passed_ind
FROM IGS_PR_SDT_PR_RU_CK sprc
WHERE sprc.person_id = p_person_id AND
sprc.course_cd = p_course_cd AND
sprc.prg_cal_type = p_prg_cal_type AND
sprc.prg_ci_sequence_number = p_prg_sequence_number AND
sprc.progression_rule_cat = cp_progression_rule_cat AND
sprc.pra_sequence_number = cp_pra_sequence_number
ORDER BY sprc.rule_check_dt DESC; -- For latest record first
v_sprc_insert_count := v_sprc_insert_count + 1;
IF v_sprc_insert_count = 1 THEN
/*INSERT INTO IGS_PR_STDNT_PR_CK (
person_id,
course_cd,
prg_cal_type,
prg_ci_sequence_number,
rule_check_dt,
s_prg_check_type)
VALUES (
p_person_id,
p_course_cd,
p_prg_cal_type,
p_prg_sequence_number,
v_rule_check_dt,
DECODE(
p_application_type,
cst_initial, cst_system,
cst_todo, cst_todo,
cst_manual, cst_manual));*/
SELECT DECODE(
p_application_type,
cst_initial, cst_system,
cst_todo, cst_todo,
cst_manual, cst_manual)FROM DUAL;
IGS_PR_STDNT_PR_CK_PKG.INSERT_ROW (
X_ROWID =>LV_ROWID,
X_PERSON_ID =>p_person_id,
X_COURSE_CD =>p_course_cd,
X_PRG_CAL_TYPE =>p_prg_cal_type,
X_PRG_CI_SEQUENCE_NUMBER =>p_prg_sequence_number,
X_RULE_CHECK_DT =>v_rule_check_dt,
X_S_PRG_CHECK_TYPE =>v_decode_val2,
X_MODE =>'R'
);
SELECT DECODE (
v_passed_ind,
'N', v_message_text,
'Y', NULL)FROM DUAL;
IGS_PR_SDT_PR_RU_CK_PKG.INSERT_ROW (
X_ROWID => lv_rowid,
x_PERSON_ID =>p_person_id,
x_COURSE_CD =>p_course_cd,
x_PRG_CAL_TYPE =>p_prg_cal_type,
x_PRG_CI_SEQUENCE_NUMBER =>p_prg_sequence_number,
x_RULE_CHECK_DT =>v_rule_check_dt,
x_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
x_PRA_SEQUENCE_NUMBER =>p_pra_sequence_number,
x_PASSED_IND =>v_passed_ind,
x_rule_message_text => v_decode_val1,
X_MODE => 'R',
X_ORG_ID => l_org_id);
SELECT 'X'
FROM igs_ca_inst ci, igs_ca_inst ci1
WHERE ci.cal_type = cp_prg_cal_type
AND ci.sequence_number = cp_prg_sequence_number
AND ci1.cal_type = cp_prg_cal_type
AND ci1.sequence_number = cp_start_sequence_number
AND ( (cp_check_type = cst_start AND ci1.start_dt > ci.start_dt)
OR (cp_check_type = cst_end AND ci1.start_dt <= ci.start_dt)
);
SELECT pra.progression_rule_cat, pra.s_relation_type
FROM igs_pr_ru_appl pra, igs_pr_ru_ca_type_v prctv
WHERE pra.s_relation_type IN (cst_sca, cst_crv, cst_ou, cst_cty)
AND pra.progression_rule_cat = prctv.progression_rule_cat
AND pra.sequence_number = prctv.pra_sequence_number
AND prctv.prg_cal_type = cp_cal_type
AND pra.logical_delete_dt IS NULL
ORDER BY pra.progression_rule_cat,
DECODE (pra.s_relation_type,
cst_sca, 1,
cst_crv, 2,
cst_ou, 3,
cst_cty, 4);
SELECT pra.progression_rule_cat, pra.sequence_number, pra.s_relation_type,
pra.progression_rule_cd, pra.rul_sequence_number, pra.attendance_type,
pra.reference_cd, pra.igs_pr_class_std_id, pra.min_cp, pra.max_cp,
prctv.start_sequence_number, prctv.end_sequence_number,
prctv.start_effective_period, prctv.num_of_applications,
pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd,
pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt,
pra.course_type
FROM igs_pr_ru_appl pra, igs_pr_ru_ca_type_v prctv
WHERE pra.s_relation_type = cst_sca
AND pra.progression_rule_cat = cp_prg_rule_cat
AND pra.logical_delete_dt IS NULL
AND pra.sca_person_id = cp_person_id
AND pra.sca_course_cd = cp_course_cd
AND pra.progression_rule_cat = prctv.progression_rule_cat
AND pra.sequence_number = prctv.pra_sequence_number
AND prctv.prg_cal_type = cp_cal_type;
SELECT pra.progression_rule_cat, pra.sequence_number, pra.s_relation_type,
pra.progression_rule_cd, pra.rul_sequence_number, pra.attendance_type,
pra.reference_cd, pra.igs_pr_class_std_id, pra.min_cp, pra.max_cp,
prctv.start_sequence_number, prctv.end_sequence_number,
prctv.start_effective_period, prctv.num_of_applications,
pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd,
pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt,
pra.course_type
FROM igs_pr_ru_appl pra, igs_pr_ru_ca_type_v prctv
WHERE pra.s_relation_type = cst_crv
AND pra.progression_rule_cat = cp_prg_rule_cat
AND pra.logical_delete_dt IS NULL
AND pra.crv_course_cd = cp_course_cd
AND pra.crv_version_number = cp_version_number
AND pra.progression_rule_cat = prctv.progression_rule_cat
AND pra.sequence_number = prctv.pra_sequence_number
AND prctv.prg_cal_type = cp_cal_type;
SELECT pra.progression_rule_cat, pra.sequence_number, pra.s_relation_type,
pra.progression_rule_cd, pra.rul_sequence_number, pra.attendance_type,
pra.reference_cd, pra.igs_pr_class_std_id, pra.min_cp, pra.max_cp,
prctv.start_sequence_number, prctv.end_sequence_number,
prctv.start_effective_period, prctv.num_of_applications,
pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd,
pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt,
pra.course_type
FROM igs_pr_ru_appl pra, igs_pr_ru_ca_type_v prctv
WHERE pra.s_relation_type = cst_ou
AND pra.progression_rule_cat = cp_prg_rule_cat
AND pra.logical_delete_dt IS NULL
AND igs_pr_gen_001.prgp_get_crv_cmt (
cp_course_cd,
cp_version_number,
pra.ou_org_unit_cd,
pra.ou_start_dt
) = 'Y'
AND pra.progression_rule_cat = prctv.progression_rule_cat
AND pra.sequence_number = prctv.pra_sequence_number
AND prctv.prg_cal_type = cp_cal_type
AND ( EXISTS ( SELECT 'x'
FROM igs_ps_own cow
WHERE cow.course_cd = cp_course_cd
AND cow.version_number = cp_version_number
AND cow.percentage = 100)
OR NOT EXISTS ( SELECT 'x'
FROM igs_pr_ru_appl pra1,
igs_pr_ru_ca_type_v prctv1
WHERE pra1.s_relation_type = 'OU'
AND pra1.logical_delete_dt IS NULL
AND pra1.progression_rule_cat =
pra.progression_rule_cat
AND pra1.sequence_number <>
pra.sequence_number
AND ( pra1.ou_org_unit_cd <>
pra.ou_org_unit_cd
OR pra1.ou_start_dt <> pra.ou_start_dt
)
AND prctv1.progression_rule_cat =
pra1.progression_rule_cat
AND prctv1.pra_sequence_number =
pra1.sequence_number
AND prctv1.prg_cal_type = prctv.prg_cal_type)
OR EXISTS (
SELECT 'x'
FROM igs_ps_ver crv1
WHERE crv1.course_cd = cp_course_cd
AND crv1.version_number = cp_version_number
AND ( ( crv1.responsible_org_unit_cd =
pra.ou_org_unit_cd
AND crv1.responsible_ou_start_dt =
pra.ou_start_dt
)
OR igs_or_gen_001.orgp_get_within_ou (
pra.ou_org_unit_cd,
pra.ou_start_dt,
crv1.responsible_org_unit_cd,
crv1.responsible_ou_start_dt,
'N'
) = 'Y'
))
);
SELECT pra.progression_rule_cat, pra.sequence_number, pra.s_relation_type,
pra.progression_rule_cd, pra.rul_sequence_number, pra.attendance_type,
pra.reference_cd, pra.igs_pr_class_std_id, pra.min_cp, pra.max_cp,
prctv.start_sequence_number, prctv.end_sequence_number,
prctv.start_effective_period, prctv.num_of_applications,
pra.sca_person_id, pra.sca_course_cd, pra.crv_course_cd,
pra.crv_version_number, pra.ou_org_unit_cd, pra.ou_start_dt,
pra.course_type
FROM igs_pr_ru_appl pra, igs_pr_ru_ca_type_v prctv, igs_ps_ver crv
WHERE pra.s_relation_type = cst_cty
AND pra.progression_rule_cat = cp_prg_rule_cat
AND pra.logical_delete_dt IS NULL
AND crv.course_type = pra.course_type
AND crv.course_cd = cp_course_cd
AND crv.version_number = cp_version_number
AND pra.progression_rule_cat = prctv.progression_rule_cat
AND pra.sequence_number = prctv.pra_sequence_number
AND prctv.prg_cal_type = cp_cal_type;
SELECT spo.*, spo.ROWID
FROM IGS_PR_STDNT_PR_OU spo
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
sequence_number = p_sequence_number
FOR UPDATE NOWAIT;
UPDATE IGS_PR_STDNT_PR_OU
SET applied_dt = cst_applied_dt
WHERE CURRENT OF c_spo_upd;
igs_pr_stdnt_pr_ou_pkg.update_row(
x_rowid => v_spo_upd_rec.ROWID,
x_person_id => v_spo_upd_rec.person_id,
x_course_cd => v_spo_upd_rec.course_cd,
x_sequence_number => v_spo_upd_rec.sequence_number,
x_prg_cal_type => v_spo_upd_rec.prg_cal_type,
x_prg_ci_sequence_number => v_spo_upd_rec.prg_ci_sequence_number,
x_rule_check_dt => v_spo_upd_rec.rule_check_dt,
x_progression_rule_cat => v_spo_upd_rec.progression_rule_cat,
x_pra_sequence_number => v_spo_upd_rec.pra_sequence_number,
x_pro_sequence_number => v_spo_upd_rec.pro_sequence_number,
x_progression_outcome_type => v_spo_upd_rec.progression_outcome_type,
x_duration => v_spo_upd_rec.duration,
x_duration_type => v_spo_upd_rec.duration_type,
x_decision_status => v_spo_upd_rec.decision_status,
x_decision_dt => v_spo_upd_rec.decision_dt,
x_decision_org_unit_cd => v_spo_upd_rec.decision_org_unit_cd,
x_decision_ou_start_dt => v_spo_upd_rec.decision_ou_start_dt,
x_applied_dt => cst_applied_dt,
x_show_cause_expiry_dt => v_spo_upd_rec.show_cause_expiry_dt,
x_show_cause_dt => v_spo_upd_rec.show_cause_dt,
x_show_cause_outcome_dt => v_spo_upd_rec.show_cause_outcome_dt,
x_show_cause_outcome_type => v_spo_upd_rec.show_cause_outcome_type,
x_appeal_expiry_dt => v_spo_upd_rec.appeal_expiry_dt,
x_appeal_dt => v_spo_upd_rec.appeal_dt,
x_appeal_outcome_dt => v_spo_upd_rec.appeal_outcome_dt,
x_appeal_outcome_type => v_spo_upd_rec.appeal_outcome_type,
x_encmb_course_group_cd => v_spo_upd_rec.encmb_course_group_cd,
x_restricted_enrolment_cp => v_spo_upd_rec.restricted_enrolment_cp,
x_restricted_attendance_type => v_spo_upd_rec.restricted_attendance_type,
x_comments => v_spo_upd_rec.comments,
x_show_cause_comments => v_spo_upd_rec.show_cause_comments,
x_appeal_comments => v_spo_upd_rec.appeal_comments,
x_expiry_dt => v_spo_upd_rec.expiry_dt,
x_pro_pra_sequence_number => v_spo_upd_rec.pro_pra_sequence_number,
x_mode => 'R'
);
v_sca_status_updated BOOLEAN;
SELECT spo.person_id,
spo.course_cd,
spo.sequence_number,
spo.expiry_dt
FROM IGS_PR_STDNT_PR_OU spo,
IGS_PR_OU_TYPE pot
WHERE spo.decision_status = cst_approved AND
spo.applied_dt IS NOT NULL AND
spo.expiry_dt IS NULL AND
spo.duration IS NOT NULL AND
pot.progression_outcome_type = spo.progression_outcome_type AND
pot.s_progression_outcome_type IN
(cst_suspension, cst_probation, cst_manual);
SELECT spo.*, spo.ROWID
FROM IGS_PR_STDNT_PR_OU spo
WHERE spo.person_id = cp_person_id AND
spo.course_cd = cp_course_cd AND
spo.sequence_number = cp_sequence_number
FOR UPDATE NOWAIT;
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.progression_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.progression_status = cst_goodstand AND
sca.course_attempt_status NOT IN ('UNCONFIRM','DELETED','COMPLETED') AND
EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_OU spo,
IGS_PR_OU_TYPE pot
WHERE pot.progression_outcome_type = spo.progression_outcome_type AND
spo.person_id = sca.person_id AND
spo.course_cd = sca.course_cd AND
((spo.decision_status = cst_pending AND
spo.course_cd = sca.course_cd)
OR
(spo.decision_status = cst_approved AND
pot.s_progression_outcome_type <> cst_nopenalty AND
IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
spo.person_id,
spo.course_cd,
spo.sequence_number,
spo.expiry_dt) <> cst_expired AND
(
pot.s_progression_outcome_type IN (cst_probation,cst_manual) OR
EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_PS spc
WHERE spo.person_id = spc.person_id AND
spo.course_cd = spc.spo_course_cd AND
spo.sequence_number = spc.spo_sequence_number AND
spc.course_cd = sca.course_cd) OR
(spo.encmb_course_group_cd IS NOT NULL AND
EXISTS (
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm
WHERE cgm.course_group_cd = spo.encmb_course_group_cd AND
cgm.course_cd = sca.course_cd AND
cgm.version_number = sca.version_number))))));
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.progression_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.progression_status = cst_undconsid AND
sca.course_attempt_status NOT IN ('UNCONFIRM','DELETED','COMPLETED') AND
NOT EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_OU spo
WHERE sca.person_id = spo.person_id AND
sca.course_cd = spo.course_cd AND
spo.decision_status = cst_pending);
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.progression_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.progression_status = cst_showcause AND
sca.course_attempt_status NOT IN ('UNCONFIRM','DELETED','COMPLETED') AND
NOT EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_OU spo
WHERE sca.person_id = spo.person_id AND
sca.course_cd = spo.course_cd AND
spo.decision_status = cst_approved AND
spo.show_cause_expiry_dt IS NOT NULL AND
((spo.show_cause_dt IS NOT NULL AND
spo.show_cause_outcome_dt IS NULL) OR
(spo.show_cause_expiry_dt > TRUNC(SYSDATE))));
SELECT sca.person_id,
sca.course_cd,
sca.version_number,
sca.progression_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.progression_status IN (
cst_probation,
cst_suspension,
cst_exclusion,
cst_expulsion) AND
sca.course_attempt_status NOT IN ('UNCONFIRM','DELETED','COMPLETED') AND
NOT EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_OU spo,
IGS_PR_OU_TYPE pot
WHERE pot.progression_outcome_type = spo.progression_outcome_type AND
spo.person_id = sca.person_id AND
spo.course_cd = sca.course_cd AND
spo.decision_status = cst_approved AND
(spo.decision_status = cst_approved AND
pot.s_progression_outcome_type <> cst_nopenalty AND
IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
spo.person_id,
spo.course_cd,
spo.sequence_number,
spo.expiry_dt) <> cst_expired AND
(
pot.s_progression_outcome_type IN (cst_probation,cst_manual) OR
EXISTS (
SELECT 'X'
FROM IGS_PR_STDNT_PR_PS spc
WHERE spo.person_id = spc.person_id AND
spo.course_cd = spc.spo_course_cd AND
spo.sequence_number = spc.spo_sequence_number AND
spc.course_cd = sca.course_cd) OR
(spo.encmb_course_group_cd IS NOT NULL AND
EXISTS (
SELECT 'X'
FROM IGS_PS_GRP_MBR cgm
WHERE cgm.course_group_cd = spo.encmb_course_group_cd AND
cgm.course_cd = sca.course_cd AND
cgm.version_number = sca.version_number)))));
SELECT PARTY_NUMBER FROM HZ_PARTIES
WHERE PARTY_ID = cp_party_id;
UPDATE IGS_PR_STDNT_PR_OU
SET expiry_dt = v_expiry_dt,
applied_dt = igs_ge_date.igsdate('0001/01/01')
WHERE CURRENT OF c_spo_upd;
igs_pr_stdnt_pr_ou_pkg.update_row(
x_rowid => v_spo_upd_rec.ROWID,
x_person_id => v_spo_upd_rec.person_id,
x_course_cd => v_spo_upd_rec.course_cd,
x_sequence_number => v_spo_upd_rec.sequence_number,
x_prg_cal_type => v_spo_upd_rec.prg_cal_type,
x_prg_ci_sequence_number => v_spo_upd_rec.prg_ci_sequence_number,
x_rule_check_dt => v_spo_upd_rec.rule_check_dt,
x_progression_rule_cat => v_spo_upd_rec.progression_rule_cat,
x_pra_sequence_number => v_spo_upd_rec.pra_sequence_number,
x_pro_sequence_number => v_spo_upd_rec.pro_sequence_number,
x_progression_outcome_type => v_spo_upd_rec.progression_outcome_type,
x_duration => v_spo_upd_rec.duration,
x_duration_type => v_spo_upd_rec.duration_type,
x_decision_status => v_spo_upd_rec.decision_status,
x_decision_dt => v_spo_upd_rec.decision_dt,
x_decision_org_unit_cd => v_spo_upd_rec.decision_org_unit_cd,
x_decision_ou_start_dt => v_spo_upd_rec.decision_ou_start_dt,
x_applied_dt => igs_ge_date.igsdate('0001/01/01'),
x_show_cause_expiry_dt => v_spo_upd_rec.show_cause_expiry_dt,
x_show_cause_dt => v_spo_upd_rec.show_cause_dt,
x_show_cause_outcome_dt => v_spo_upd_rec.show_cause_outcome_dt,
x_show_cause_outcome_type => v_spo_upd_rec.show_cause_outcome_type,
x_appeal_expiry_dt => v_spo_upd_rec.appeal_expiry_dt,
x_appeal_dt => v_spo_upd_rec.appeal_dt,
x_appeal_outcome_dt => v_spo_upd_rec.appeal_outcome_dt,
x_appeal_outcome_type => v_spo_upd_rec.appeal_outcome_type,
x_encmb_course_group_cd => v_spo_upd_rec.encmb_course_group_cd,
x_restricted_enrolment_cp => v_spo_upd_rec.restricted_enrolment_cp,
x_restricted_attendance_type => v_spo_upd_rec.restricted_attendance_type,
x_comments => v_spo_upd_rec.comments,
x_show_cause_comments => v_spo_upd_rec.show_cause_comments,
x_appeal_comments => v_spo_upd_rec.appeal_comments,
x_expiry_dt => v_expiry_dt,--v_spo_upd_rec.expiry_dt, -- Modified by Prajeesh as part of the bug
x_pro_pra_sequence_number => v_spo_upd_rec.pro_pra_sequence_number,
x_mode => 'R'
);
v_sca_status_updated := IGS_PR_GEN_006.IGS_PR_upd_sca_status (
v_sca_rec.person_id,
v_sca_rec.course_cd,
v_sca_rec.progression_status,
v_sca_rec.version_number,
v_message_name);
v_sca_status_updated := IGS_PR_GEN_006.IGS_PR_upd_sca_status (
v_sca_rec.person_id,
v_sca_rec.course_cd,
v_sca_rec.progression_status,
v_sca_rec.version_number,
v_message_name);
v_sca_status_updated := IGS_PR_GEN_006.IGS_PR_upd_sca_status (
v_sca_rec.person_id,
v_sca_rec.course_cd,
v_sca_rec.progression_status,
v_sca_rec.version_number,
v_message_name);
v_sca_status_updated := IGS_PR_GEN_006.IGS_PR_upd_sca_status (
v_sca_rec.person_id,
v_sca_rec.course_cd,
v_sca_rec.progression_status,
v_sca_rec.version_number,
v_message_name);