[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT us.authorisation_rqrd_ind
FROM IGS_EN_UNIT_SET us
WHERE us.unit_set_cd = p_unit_set_cd AND
us.version_number = p_us_version_number;
SELECT sca.version_number,
sca.cal_type
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT susa.us_version_number
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_parent_unit_set_cd AND
susa.sequence_number = p_parent_sequence_number;
SELECT 'x'
FROM IGS_PS_OF_UNT_SET_RL cousr
WHERE cousr.course_cd = p_course_cd AND
cousr.crv_version_number = cp_version_number AND
cousr.cal_type = cp_cal_type AND
cousr.sub_unit_set_cd = p_unit_set_cd AND
cousr.sub_us_version_number =p_us_version_number AND
cousr.sup_unit_set_cd = p_parent_unit_set_cd AND
cousr.sup_us_version_number = cp_susa_us_version_number;
SELECT 'x'
FROM IGS_PS_OF_UNT_SET_RL cousr
WHERE cousr.course_cd = p_course_cd AND
cousr.crv_version_number = cp_us_version_number AND
cousr.cal_type = cp_cal_type AND
cousr.sub_unit_set_cd = p_unit_set_cd AND
cousr.sub_us_version_number = p_us_version_number;
-- deleted meets the following conditions:
-- - Cannot be deleted if the unit set has been completed.
-- - Cannot be deleted if the unit set has been ended.
-- - Cannot be deleted if it is the parent of another unit set.
-- - Cannot be deleted if it is part of the terms and conditions
-- of the admissions offer for the student.
DECLARE
v_dummy VARCHAR2(1);
SELECT 'X'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.parent_unit_set_cd = p_unit_set_cd AND
susa.parent_sequence_number = p_sequence_number;
SELECT acai.unit_set_cd,
acai.us_version_number
FROM IGS_AD_PS_APPL_INST acai,
IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
acai.person_id = sca.person_id AND
acai.admission_appl_number = sca.adm_admission_appl_number AND
acai.nominated_course_cd = sca.adm_nominated_course_cd AND
acai.sequence_number = sca.adm_sequence_number;
-- Cannot be deleted if the unit set has been completed.
IF (p_rqrmnts_complete_ind = 'Y') THEN
p_message_name := 'IGS_EN_NOTDEL_UNITSET_COMPL';
-- Cannot be deleted if the unit set has been ended.
IF (p_end_dt IS NOT NULL) THEN
p_message_name := 'IGS_EN_NOTDEL_UNITSET_ENDED';
-- Cannot be deleted if it is the parent of another IGS_EN_UNIT_SET.
OPEN c_susa; -- Althogh handled by RI constraints, required for enrp_val_susa.
-- Cannot be deleted if it is part of the terms and conditions
-- of the admissions offer for the student.
-- Determine if unit set is part of the admissions offer
IF igs_as_val_suaap.genp_val_sdtt_sess('ADMP_DEL_SCA_UNCONF') THEN
FOR v_acai_sca_rec IN c_acai_sca LOOP
IF (v_acai_sca_rec.unit_set_cd = p_unit_set_cd AND
v_acai_sca_rec.us_version_number = p_us_version_number) THEN
p_message_name := 'IGS_EN_NOTDEL_UNITSET_COND';
p_selection_dt IN DATE ,
p_end_dt IN DATE ,
p_rqrmnts_complete_dt IN DATE ,
p_message_name OUT NOCOPY VARCHAR2 )
/* -------------------------------------------------------------------------------------------
--Change History:
--Who When What
--svanukur 12-sep-03 Removed the validation of selection date being greater than
-- sysdate as perbug 3106879 to allow selection date to be future dated.
-------------------------------------------------------------------------------------------*/
RETURN BOOLEAN AS
gv_other_detail VARCHAR2(255);
-- . If end_dt and selection_dt set, then end_dt >= selection_dt.
-- . end_dt and rqrmnts_complete_dt cannot both be set.
-- . Selection_dt, end_dt, rqrmnts_complete_dt cannot be future dated.
-- . If rqrmnts_complete_dt and selection_dt set,
-- then rqrmnts_complete_dt >= selection_dt.
DECLARE
v_sysdate DATE;
-- If end_dt and selection_dt set, then end_dt >= selection_dt.
IF p_end_dt IS NOT NULL AND
p_selection_dt IS NOT NULL THEN
IF p_end_dt < p_selection_dt THEN
p_message_name := 'IGS_EN_ENDDT_NOTBE_EARLIER_DT';
-- If rqrmnts_complete_dt and selection_dt set,
-- then rqrmnts_complete_dt >= selection_dt.
IF p_rqrmnts_complete_dt IS NOT NULL AND
p_selection_dt IS NOT NULL THEN
IF p_rqrmnts_complete_dt < p_selection_dt THEN
p_message_name := 'IGS_EN_COMPLDT_GE_CURR_DT';
SELECT 'x'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_unit_set_cd AND
susa.end_dt IS NULL AND
susa.sequence_number <> NVL(p_sequence_number, 0);
SELECT 'x'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_parent_unit_set_cd AND
susa.sequence_number = p_parent_sequence_number AND
susa.end_dt IS NOT NULL;
SELECT acai.unit_set_cd,
acai.us_version_number
FROM IGS_AD_PS_APPL_INST acai,
IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
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;
-- must have had it previously selected.
-- . Cannot be created against a unit set that has previously been completed
-- by the student (irrespective of version).
DECLARE
v_dummy VARCHAR2(1);
SELECT 'X'
FROM IGS_PS_OFR_UNIT_SET cous,
igs_en_unit_set_stat uss1,
IGS_EN_UNIT_SET_ALL US,
IGS_EN_STDNT_PS_ATT spa
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND us.version_number = p_us_version_number
AND us.unit_set_cd = p_unit_set_cd
AND spa.course_cd = cous.course_cd
AND spa.version_number = cous.crv_version_number
AND spa.CAL_TYPE = cous.CAL_TYPE
AND us.unit_set_cd = cous.unit_set_cd
AND us.version_number = cous.us_version_number
AND us.unit_set_status = uss1.unit_set_status
AND uss1.s_unit_set_status ='ACTIVE'
AND NOT EXISTS (SELECT 1
FROM IGS_PS_OF_OPT_UNT_ST coous
WHERE coous.coo_id= spa.coo_id
);
SELECT 'X'
FROM IGS_PS_OF_OPT_UNT_ST coous,
IGS_EN_UNIT_SET_ALL US,
igs_en_unit_set_stat uss1,
IGS_EN_STDNT_PS_ATT spa
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND coous.coo_id = spa.coo_id
AND us.version_number = p_us_version_number
AND us.unit_set_cd = p_unit_set_cd
AND us.unit_set_cd = coous.unit_set_cd
AND us.version_number = coous.us_version_number
AND us.unit_set_status = uss1.unit_set_status
AND uss1.s_unit_set_status = 'ACTIVE';
SELECT 'X'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_unit_set_cd AND
susa.sequence_number<> NVL(p_sequence_number, 0) AND
susa.rqrmnts_complete_ind = 'Y';
SELECT susa.end_dt,
susa.student_confirmed_ind
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_parent_unit_set_cd AND
susa.sequence_number = p_parent_sequence_number;
SELECT susa1.unit_set_cd
FROM IGS_AS_SU_SETATMPT susa1
START WITH susa1.person_id = p_person_id AND
susa1.course_cd = p_course_cd AND
susa1.unit_set_cd = p_parent_unit_set_cd AND
susa1.sequence_number = p_parent_sequence_number
CONNECT BY PRIOR susa1.person_id = susa1.person_id AND
PRIOR susa1.course_cd = susa1.course_cd AND
PRIOR susa1.parent_unit_set_cd = susa1.unit_set_cd AND
PRIOR susa1.parent_sequence_number = susa1.sequence_number;
SELECT susa1.unit_set_cd
FROM IGS_AS_SU_SETATMPT susa1
START WITH susa1.person_id = p_person_id AND
susa1.course_cd = p_course_cd AND
susa1.unit_set_cd = p_unit_set_cd AND
susa1.sequence_number = NVL(p_sequence_number, 0)
CONNECT BY PRIOR susa1.person_id = susa1.person_id AND
PRIOR susa1.course_cd = susa1.course_cd AND
PRIOR susa1.unit_set_cd = susa1.parent_unit_set_cd AND
PRIOR susa1.sequence_number = susa1.parent_sequence_number;
SELECT us.administrative_ind,
usc.rank
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_CAT usc
WHERE us.unit_set_cd = p_unit_set_cd AND
us.version_number = p_us_version_number AND
us.unit_set_cat = usc.unit_set_cat;
SELECT 'x'
FROM IGS_AS_SU_SETATMPT susa,
IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_CAT usc
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = us.unit_set_cd AND
susa.us_version_number = us.version_number AND
us.administrative_ind = 'N' AND
us.unit_set_cat = usc.unit_set_cat AND
usc.rank < cp_rank;
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
SELECT 'x'
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.course_attempt_status IN ( cst_discontin,
cst_lapsed,
cst_deleted);
p_selection_dt IN DATE ,
p_end_dt IN DATE ,
p_rqrmnts_complete_ind IN VARCHAR2,
p_message_name OUT NOCOPY VARCHAR2,
p_legacy IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN -- enrp_val_susa_sci
-- This module validates the confirmation of a IGS_EN_UNIT_SET for a
-- IGS_AS_SU_SETATMPT record. The validations are:
-- - The student confirmed indicator cannot be unset once the
-- end date has been set.
-- - The student confirmed indicator cannot be unset once the
-- requirements complete indicator has been set.
-- - The student confirmed indicator can only be set if the
-- student course attempt status is 'ENROLLED' or 'INACTIVE'.
-- - The student confirmed indicator cannot be set when a parent
-- unit set exists that is unconfirmed.
-- - The student confirmed indicator cannot be set if the student
-- is excluded from the unit set via encumbrances.
DECLARE
cst_enrolled CONSTANT
IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
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 AND
sca.course_attempt_status IN (
cst_enrolled,
cst_inactive);
SELECT 'X'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_parent_unit_set_cd AND
susa.sequence_number = p_parent_sequence_number AND
susa.student_confirmed_ind = 'N';
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 AND
sca.course_attempt_status = 'UNCONFIRM';
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 AND
sca.course_attempt_status IN (
cst_enrolled,
cst_inactive);
p_selection_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2,
p_legacy IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN -- enrp_val_susa_sci_sd
-- This module validates the student_confirmed_ind against the selection_dt
-- for a IGS_AS_SU_SETATMPT record. The validations are:
-- - The selection date must be set if the confirmed indicator is set
-- (and visa versa).
-- - The selection date being unset, then the confirmed indicator must be
-- unset (and visa versa).
DECLARE
BEGIN
p_message_name := NULL;
-- Validate the selection date must be set if the confirmed
-- indicator is set and visa versa.
IF (p_student_confirmed_ind = 'Y' AND
p_selection_dt IS NULL) THEN
p_message_name := 'IGS_EN_UNIT_SET_UNCONF_SETDT';
-- Validate the selection date must be unset if the
-- confirmed indicator is set and visa versa.
IF (p_student_confirmed_ind = 'N' AND
p_selection_dt IS NOT NULL) THEN
p_message_name := 'IGS_EN_UNIT_SET_UNCONF_NOTSET';
-- expiry date set and the student has previously selected in within the same
-- course attempt.
DECLARE
cst_active CONSTANT VARCHAR2(6) := 'ACTIVE';
SELECT uss.s_unit_set_status,
us.expiry_dt
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_STAT uss
WHERE us.unit_set_status = uss.unit_set_status AND
us.unit_set_cd = p_unit_set_cd AND
us.version_number = p_version_number;
SELECT 'x'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_unit_set_cd AND
susa.us_version_number = p_version_number AND
susa.sequence_number <> NVL(p_sequence_number, 0);
-- Determine if the student has previously had the version selected
-- within the specified course
-- NOTE: sequence number comparison is used as this validation is called
-- from an after statement databae trigger in which case, want to ignore the
-- newly created record.
OPEN c_susa;
SELECT us.authorisation_rqrd_ind
FROM IGS_EN_UNIT_SET us
WHERE unit_set_cd = p_unit_set_cd AND
version_number = p_version_number;
p_selection_dt IN DATE ,
p_student_confirmed_ind IN VARCHAR2,
p_end_dt IN DATE ,
p_parent_unit_set_cd IN VARCHAR2 ,
p_parent_sequence_number IN NUMBER ,
p_primary_set_ind IN VARCHAR2,
p_voluntary_end_ind IN VARCHAR2,
p_authorised_person_id IN NUMBER ,
p_authorised_on IN DATE ,
p_override_title IN VARCHAR2 ,
p_rqrmnts_complete_ind IN VARCHAR2,
p_rqrmnts_complete_dt IN DATE ,
p_s_completed_source_type IN VARCHAR2 ,
p_action IN VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 ,
p_message_text OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN -- enrp_val_susa
-- This module validates
DECLARE
v_message_name VARCHAR2(30);
SELECT *
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_unit_set_cd AND
susa.sequence_number = p_sequence_number;
cst_insert CONSTANT VARCHAR2 (6) := 'INSERT';
cst_update CONSTANT VARCHAR2 (6) := 'UPDATE';
cst_delete CONSTANT VARCHAR2 (6) := 'DELETE';
v_inserting BOOLEAN := FALSE;
IF p_action = cst_insert THEN
v_inserting := TRUE;
ELSIF p_action = cst_update THEN
v_updating := TRUE;
ELSIF p_action = cst_delete THEN
v_deleting := TRUE;
-- If updating, select the values of the record prior to update.
IF v_updating THEN
OPEN c_old_susa;
IF v_inserting THEN
-- Validate the the unit set is able to be created.
-- against the student course attempt.
IF Igs_En_Val_Susa.enrp_val_susa_sca(
p_person_id,
p_course_cd,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((NVL(p_authorised_person_id, 0) <>
NVL(v_old_susa_rec.authorised_person_id, 0)) OR
(NVL(p_authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.authorised_on,
IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
IF Igs_En_Val_Susa.enrp_val_susa_auth(
p_unit_set_cd,
p_us_version_number,
p_end_dt,
p_authorised_person_id,
p_authorised_on,
v_message_name,
'N') = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((NVL(p_authorised_person_id, 0) <>
NVL(v_old_susa_rec.authorised_person_id, 0)) OR
(p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind) OR
(NVL(p_authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.authorised_on,
IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
-- Validate that the authorisation fields must be set when
-- the unit set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
-- Check required only when the unit set is confirmed.
IF (p_student_confirmed_ind = 'Y') THEN
IF Igs_En_Val_Susa.enrp_val_susa_us_ath(
p_unit_set_cd,
p_us_version_number,
p_authorised_person_id,
p_authorised_on,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((NVL(p_rqrmnts_complete_ind, 'x')
<> NVL(v_old_susa_rec.rqrmnts_complete_ind, 'x')) OR
(NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.rqrmnts_complete_dt,
IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
IF Igs_En_Val_Susa.enrp_val_susa_cmplt(
p_rqrmnts_complete_dt,
p_rqrmnts_complete_ind,
p_student_confirmed_ind,
v_message_name,
'N') = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((NVL(p_rqrmnts_complete_ind, 'x')
<> NVL(v_old_susa_rec.rqrmnts_complete_ind, 'x')) OR
(NVL(p_s_completed_source_type, 'x')
<> NVL(v_old_susa_rec.s_completed_source_type, 'x')) OR
(NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.rqrmnts_complete_dt,
IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
IF Igs_En_Val_Susa.enrp_val_susa_scst(
p_rqrmnts_complete_dt,
p_rqrmnts_complete_ind,
p_s_completed_source_type,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((NVL(p_selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.selection_dt,
IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
(NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
(NVL(p_rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.rqrmnts_complete_dt,
IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
IF Igs_En_Val_Susa.enrp_val_susa_dts(
p_selection_dt,
p_end_dt,
p_rqrmnts_complete_dt,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
-- Validate that the selection date can only be set/unset when unit set is
-- confirmed/unconfirmed
IF v_inserting OR
(v_updating AND
((p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind) OR
(NVL(p_selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.selection_dt,
IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
IF Igs_En_Val_Susa.enrp_val_susa_sci_sd(
p_student_confirmed_ind,
p_selection_dt,
v_message_name,
'N') = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((p_voluntary_end_ind <> v_old_susa_rec.voluntary_end_ind) OR
(NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
IF Igs_En_Val_Susa.enrp_val_susa_end_vi(
p_voluntary_end_ind,
p_end_dt,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
-- Validate that the unit set version number cannot be updated.
IF (v_updating AND
(p_us_version_number <> v_old_susa_rec.us_version_number)) THEN
p_message_name := 'IGS_EN_UNIT_SET_VERNUM_NOTUPD';
-- Validate that the records can be deleted.
IF Igs_En_Val_Susa.enrp_val_susa_del(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_sequence_number,
p_us_version_number,
p_end_dt,
p_rqrmnts_complete_ind,
'N', -- Indicating not called from trigger.
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_inserting THEN
-- Validate the the unit set is able to be created
-- with the unit set status being valid and the
-- expiry date not set. If set then person must have
-- previously selected it.
IF Igs_En_Val_Susa.enrp_val_susa_us_act(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_sequence_number,
p_us_version_number,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
((NVL(p_parent_unit_set_cd, 'NULL')
<> NVL(v_old_susa_rec.parent_unit_set_cd, 'NULL')) OR
(NVL(p_parent_sequence_number, 0)
<> NVL(v_old_susa_rec.parent_sequence_number, 0)))) THEN
-- Validate if the unit set is to be defined as a subordinate or if
-- relationship specified, that it is valid within the course offering.
IF Igs_En_Val_Susa.enrp_val_susa_cousr(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_us_version_number,
p_parent_unit_set_cd,
p_parent_sequence_number,
cst_error,
v_message_name,
'N') = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(NVL(p_end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(v_old_susa_rec.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
-- Validate the end date, check if the authorisation details
-- need to be set or if more than one open end dated instance
-- of the unit set exists. Also cannot be cleared if parent ended.
-- If part of the admissions offer, authorisation required to end
-- the unit set.
IF Igs_En_Val_Susa.enrp_val_susa_end_dt(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_sequence_number,
p_us_version_number,
p_end_dt,
p_authorised_person_id,
p_authorised_on,
p_parent_unit_set_cd,
p_parent_sequence_number,
cst_error,
v_message_name,
'N') = FALSE THEN
-- Check if warning message returned.
IF v_message_name <> 'IGS_EN_UNITSET_REQ_AUTHORISAT' THEN
p_message_name := v_message_name;
-- (Inserted records cannot have children at that point).
IF v_updating AND
p_end_dt IS NOT NULL THEN
IF Igs_En_Val_Susa.enrp_val_susa_ed_upd(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_sequence_number,
p_end_dt,
p_voluntary_end_ind,
p_authorised_person_id,
p_authorised_on,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF (v_inserting AND p_student_confirmed_ind = 'Y') OR
(v_updating AND
(p_student_confirmed_ind <> v_old_susa_rec.student_confirmed_ind)) THEN
-- Validate that the unit set is not confirmed when the student course
-- attempt is unconfirmed.
-- Also check that not unset one end date or complete date set. Cannot be
-- confirmed and linked to a parent that is unconfirmed. Cannot be
-- confirmed if encumbrances exist.
IF Igs_En_Val_Susa.enrp_val_susa_sci(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_sequence_number,
p_us_version_number,
p_parent_unit_set_cd,
p_parent_sequence_number,
p_student_confirmed_ind,
p_selection_dt,
p_end_dt,
p_rqrmnts_complete_ind,
v_message_name,
'N') = FALSE THEN
p_message_name := v_message_name;
IF v_inserting THEN
v_old_student_confirmed_ind := NULL;
-- with update as unit set cannot have descendant at the point of
-- creation).
IF v_updating AND
p_student_confirmed_ind = 'N' THEN
IF Igs_En_Val_Susa.enrp_val_susa_sci_up(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_sequence_number,
p_student_confirmed_ind,
v_message_name) = FALSE THEN
p_message_name := v_message_name;
IF v_inserting OR
(v_updating AND
(p_primary_set_ind <> v_old_susa_rec.primary_set_ind)) THEN
-- Validate the primary set indicator is only set for
-- non-administrative sets and that there does not already
-- exist a unit set that has a higher rank.
IF Igs_En_Val_Susa.enrp_val_susa_prmry(
p_person_id,
p_course_cd,
p_unit_set_cd,
p_us_version_number,
p_primary_set_ind,
v_message_name,
'N') = FALSE THEN
p_message_name := v_message_name;
v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
SELECT susa.unit_set_cd,
susa.us_version_number,
susa.sequence_number,
susa.authorised_person_id,
susa.authorised_on,
susa.selection_dt,
susa.end_dt,
susa.rqrmnts_complete_dt,
susa.parent_unit_set_cd,
susa.parent_sequence_number,
susa.student_confirmed_ind,
susa.primary_set_ind,
susa.voluntary_end_ind,
susa.override_title,
susa.rqrmnts_complete_ind,
susa.s_completed_source_type
FROM IGS_AS_SU_SETATMPT susa
START WITH susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.parent_unit_set_cd = p_unit_set_cd AND
susa.parent_sequence_number = p_sequence_number
CONNECT BY
PRIOR susa.person_id = susa.person_id AND
PRIOR susa.course_cd = susa.course_cd AND
PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
PRIOR susa.sequence_number = susa.parent_sequence_number;
v_selection_dt,
v_end_dt,
v_rqrmnts_complete_dt,
v_parent_unit_set_cd,
v_parent_sequence_number,
v_student_confirmed_ind,
v_primary_set_ind,
v_voluntary_end_ind,
v_override_title,
v_rqrmnts_complete_ind,
v_s_completed_source_type;
-- Determine if authorised person id required to be updated (That is, if
-- authorise parameter fields null then do not alter fields.)
IF (p_authorised_person_id IS NOT NULL OR
p_authorised_on IS NOT NULL) THEN
v_authorised_person_id := p_authorised_person_id;
-- Validate that able to update the record.
IF Igs_En_Val_Susa.enrp_val_susa(
p_person_id,
p_course_cd,
v_unit_set_cd,
v_sequence_number,
v_us_version_number,
v_selection_dt,
v_student_confirmed_ind,
p_end_dt,
v_parent_unit_set_cd,
v_parent_sequence_number,
v_primary_set_ind,
p_voluntary_end_ind,
v_authorised_person_id,
v_authorised_on,
v_override_title,
v_rqrmnts_complete_ind,
v_rqrmnts_complete_dt,
v_s_completed_source_type,
'UPDATE',
v_message_name,
v_message_text) = FALSE THEN
-- Ignore v_message_text as rules are not used here in any validation.
CLOSE c_susa;
v_selection_dt,
v_end_dt,
v_rqrmnts_complete_dt,
v_parent_unit_set_cd,
v_parent_sequence_number,
v_student_confirmed_ind,
v_primary_set_ind,
v_voluntary_end_ind,
v_override_title,
v_rqrmnts_complete_ind,
v_s_completed_source_type;
SELECT susa.unit_set_cd,
susa.us_version_number,
susa.sequence_number,
susa.authorised_person_id,
susa.authorised_on,
susa.selection_dt,
susa.end_dt,
susa.rqrmnts_complete_dt,
susa.parent_unit_set_cd,
susa.parent_sequence_number,
susa.student_confirmed_ind,
susa.primary_set_ind,
susa.voluntary_end_ind,
susa.override_title,
susa.rqrmnts_complete_ind,
susa.s_completed_source_type
FROM IGS_AS_SU_SETATMPT susa
START WITH susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.parent_unit_set_cd = p_unit_set_cd AND
susa.parent_sequence_number = p_sequence_number
CONNECT BY
PRIOR susa.person_id = susa.person_id AND
PRIOR susa.course_cd = susa.course_cd AND
PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
PRIOR susa.sequence_number = susa.parent_sequence_number;
-- Validate that able to update the record.
IF Igs_En_Val_Susa.enrp_val_susa(
p_person_id,
p_course_cd,
v_susa_rec.unit_set_cd,
v_susa_rec.sequence_number,
v_susa_rec.us_version_number,
NULL, -- selection_dt
'N', -- student_confirmed_ind
v_susa_rec.end_dt,
v_susa_rec.parent_unit_set_cd,
v_susa_rec.parent_sequence_number,
v_susa_rec.primary_set_ind,
v_susa_rec.voluntary_end_ind,
v_susa_rec.authorised_person_id,
v_susa_rec.authorised_on,
v_susa_rec.override_title,
v_susa_rec.rqrmnts_complete_ind,
v_susa_rec.rqrmnts_complete_dt,
v_susa_rec.s_completed_source_type,
'UPDATE',
v_message_name,
v_message_text) = FALSE THEN
-- Ignore v_message_text as rules are not used here in any validation.
p_message_name := v_message_name;