[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : When the user tries to finalize the units he has selected
for enrolment, program level validations have to be carried
on before the user is actuall enroled. These function's are
meant for calling from the Self-Service applications
Known limitations,
enhancements,
remarks :
Change History
Who When What
******************************************************************/
-- Declare global variables
g_person_type igs_pe_usr_arg.person_type%TYPE;
SELECT system_type
FROM igs_pe_person_types
WHERE person_type_code = p_person_type;
OPEN cur_program_steps FOR SELECT eru.s_enrolment_step_type,
eru.notification_flag notification_flag,
eru.s_rule_call_cd,
eru.rul_sequence_number
FROM igs_en_cpd_ext eru,
igs_lookups_view lkup
WHERE eru.s_enrolment_step_type = lkup.lookup_code AND
eru.enrolment_cat = p_enrollment_category AND
(eru.s_student_comm_type = p_comm_type OR
eru.s_student_comm_type = 'ALL' ) AND
eru.enr_method_type = p_method_type AND
lkup.lookup_type = 'ENROLMENT_STEP_TYPE_EXT' AND
lkup.step_group_type = 'PROGRAM'
ORDER BY eru.step_order_num;
OPEN cur_program_steps FOR SELECT eru.s_enrolment_step_type,
DECODE(uact.deny_warn,'WARN','WARN',eru.notification_flag) notification_flag,
eru.s_rule_call_cd,
eru.rul_sequence_number
FROM igs_en_cpd_ext eru,
igs_pe_usr_aval_all uact,
igs_lookups_view lkup
WHERE eru.s_enrolment_step_type = lkup.lookup_code AND
eru.enrolment_cat = p_enrollment_category AND
eru.enr_method_type = p_method_type AND
(eru.s_student_comm_type = p_comm_type OR
eru.s_student_comm_type = 'ALL' ) AND
lkup.lookup_type = 'ENROLMENT_STEP_TYPE_EXT' AND
lkup.step_group_type = 'PROGRAM' AND
eru.s_enrolment_step_type = uact.validation(+) AND
uact.person_type (+) = p_person_type AND
NVL(uact. override_ind,'N') = 'N'
ORDER BY eru.step_order_num;
SELECT unit_cd, version_number, cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT OVERRIDE_ENROLLED_CP
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND uoo_id = p_uoo_id;
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND uoo_id = p_uoo_id;
OPEN cur_program_steps FOR SELECT eru.rul_sequence_number rul_sequence_number
FROM igs_en_cpd_ext eru,
igs_lookups_view lkup
WHERE eru.s_enrolment_step_type = lkup.lookup_code AND
eru.s_enrolment_step_type = 'UNIT_EXCL' AND
eru.enrolment_cat = g_enrollment_category AND
(eru.s_student_comm_type = g_comm_type
OR eru.s_student_comm_type = 'ALL' ) AND
eru.enr_method_type = g_method_type AND
lkup.lookup_type = 'ENROLMENT_STEP_TYPE_EXT' AND
lkup.step_group_type = 'PROGRAM'
ORDER BY eru.step_order_num;
OPEN cur_program_steps FOR SELECT eru.rul_sequence_number rul_sequence_number
FROM igs_en_cpd_ext eru,
igs_pe_usr_aval_all uact,
igs_lookups_view lkup
WHERE eru.s_enrolment_step_type = lkup.lookup_code AND
lkup.lookup_type = 'ENROLMENT_STEP_TYPE_EXT' AND
eru.s_enrolment_step_type = 'UNIT_EXCL' AND
eru.enrolment_cat = g_enrollment_category AND
eru.enr_method_type = g_method_type AND
(eru.s_student_comm_type = g_comm_type
OR eru.s_student_comm_type = 'ALL' ) AND
lkup.step_group_type = 'PROGRAM' AND
eru.s_enrolment_step_type = uact.validation(+) AND
uact.person_type (+) = g_person_type AND
NVL(uact. override_ind,'N') = 'N'
ORDER BY eru.step_order_num;
SELECT unit_cd, version_number, cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT ecpd.config_min_cp_valdn, ecpd.enforce_date_alias
FROM igs_en_cat_prc_dtl ecpd, igs_en_cpd_ext ecpe
WHERE ecpe.s_enrolment_step_type IN ( 'FATD_TYPE' , 'FMIN_CRDT' ) AND
ecpe.enrolment_cat = p_enrollment_category AND
ecpe.enr_method_type = p_method_type AND
(ecpe.s_student_comm_type = p_comm_type
OR ecpe.s_student_comm_type = 'ALL' ) AND
ecpd.enrolment_cat = ecpe.enrolment_cat AND
ecpd.enr_method_type = ecpe.enr_method_type AND
ecpd.s_student_comm_type = ecpe.s_student_comm_type AND
ecpd.config_min_cp_valdn <> 'NONE';
SELECT MIN(di.alias_val)
FROM igs_ca_da_inst_v di
WHERE di.cal_type = p_load_calendar_type AND
di.ci_sequence_number = p_load_cal_sequence_number AND
di.dt_alias = l_p_date_alias ;
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_program_cd
AND uoo_id = p_uoo_id;
if called from Drop Unit section / Update Unit section CP / Transfer unit section.-Bug#2737263.If this parameter has a value and a Min CP
config exists for 'When First Reach Min CP' , then DENY/WARN is determined programatically based on whether the Att Typ has already been rchd.
Nishikant 17OCT2002 Enrl Elgbl and Validation Build. Bug#2616692.
The Logic modified to check first whether Min CP is Overriden at Load Calendar level
then call eval_min_cp and set DENY or WARN messages accordingly.
ayedubat 6-JUN-2002 Replaced the function call,Igs_En_Gen_015.get_academic_cal with
Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd to get the academic calendar of the given
load calendar rather than current academic calendar for the bug fix: 2381603
myoganat 16-JUN-2003 Bug# 2855870 Added cursor c_assessment to check for an audit attempt and if it is, the function
will return TRUE.
******************************************************************/
-- cursor for getting all the program offering option of all the active program attempts
-- modified the WHERE clause to add condition course_cd = p_course_cd and removed course_attempt_status condition.
-- Cursor to fetch the Date Alias value.
CURSOR cur_get_alias_val( l_p_date_alias igs_ca_da_inst_v.dt_alias%TYPE ) IS
SELECT MIN(di.alias_val)
FROM igs_ca_da_inst_v di
WHERE di.cal_type = p_load_calendar_type AND
di.ci_sequence_number = p_load_cal_sequence_number AND
di.dt_alias = l_p_date_alias ;
SELECT ecpd.config_min_cp_valdn, ecpd.enforce_date_alias
FROM igs_en_cat_prc_dtl ecpd, igs_en_cpd_ext ecpe
WHERE ecpe.s_enrolment_step_type IN ( 'FATD_TYPE' , 'FMIN_CRDT' ) AND
ecpe.enrolment_cat = p_enrollment_category AND
ecpe.enr_method_type = p_method_type AND
(ecpe.s_student_comm_type = p_comm_type
OR ecpe.s_student_comm_type = 'ALL' ) AND
ecpd.enrolment_cat = ecpe.enrolment_cat AND
ecpd.enr_method_type = ecpe.enr_method_type AND
ecpd.s_student_comm_type = ecpe.s_student_comm_type AND
ecpd.config_min_cp_valdn <> 'NONE';
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id;
SELECT attendance_type
FROM IGS_PS_OFR_OPT
WHERE coo_id = p_coo_id;
SELECT sup_ci_sequence_number
FROM igs_ca_inst_rel
WHERE sub_cal_type = p_load_cal AND
sub_ci_sequence_number = p_load_ci_sequence_number AND
sup_cal_type = p_acad_cal;
SELECT notification_flag
FROM igs_en_cpd_ext
WHERE enrolment_cat = p_enrl_cat
AND enr_method_type = p_enr_meth_type
AND s_enrolment_step_type = 'FMIN_CRDT'
AND (s_student_comm_type = p_s_stdnt_comm_type OR
s_student_comm_type = 'ALL');
SELECT DISTINCT uoo_id
FROM igs_en_su_attempt sua,
igs_ca_load_to_teach_v ltt
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'ENROLLED'
AND sua.cal_type = ltt.teach_cal_type
AND sua.ci_sequence_number = ltt.teach_ci_sequence_number
AND ltt.load_cal_type = p_load_cal
AND ltt.load_ci_sequence_number = p_load_ci_sequence_number ;
SELECT IGS_EN_WF_BE002_S.nextval seq_val
FROM DUAL;
SELECT unit_cd, version_number, cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT MIN(restricted_enrolment_cp) restricted_enrolment_cp
FROM igs_pe_persenc_effct
WHERE person_id = p_person_id
AND s_encmb_effect_type = 'RSTR_GE_CP'
AND pee_start_dt <= cp_effective_date
AND (expiry_dt IS NULL OR expiry_dt >= cp_effective_date);
SELECT min_cp_per_calendar
FROM igs_ps_ver
WHERE course_cd = cp_primary_cd
AND version_number = cp_version_number;
SELECT min_cp_per_term
FROM igs_en_config_enr_cp
WHERE course_cd = p_program_cd AND
version_number = p_program_version AND
cal_type = p_load_calendar_type;
SELECT start_dt
FROM igs_ca_inst
WHERE cal_type = p_load_calendar_type AND
sequence_number = p_load_cal_sequence_number ;
SELECT unit_cd, version_number, cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT MAX(restricted_enrolment_cp) restricted_enrolment_cp
FROM igs_pe_persenc_effct
WHERE person_id = p_person_id
AND s_encmb_effect_type = 'RSTR_LE_CP'
AND pee_start_dt <= cp_effective_date
AND (expiry_dt IS NULL OR expiry_dt >= cp_effective_date);
SELECT max_cp_per_teaching_period
FROM igs_ps_ver
WHERE course_cd = cp_primary_cd
AND version_number = cp_version_number;
SELECT max_cp_per_term
FROM igs_en_config_enr_cp
WHERE course_cd = p_program_cd AND
version_number = p_program_version AND
cal_type = p_load_calendar_type;
SELECT start_dt
FROM igs_ca_inst
WHERE cal_type = p_load_calendar_type AND
sequence_number = p_load_cal_sequence_number ;
SELECT unit_cd, version_number, cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT cop.max_cross_faculty_cp,
cop.max_cross_mode_cp,
cop.max_cross_location_cp
FROM IGS_PS_OFR_PAT cop
WHERE cop.coo_id = cp_sca_coo_id AND
cop.cal_type = cp_cal_type AND
cop.ci_sequence_number = cp_ci_sequence_number;
SELECT DISTINCT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.location_cd,
sua.unit_class,
sua.no_assessment_ind
FROM igs_en_su_attempt sua,
igs_ca_load_to_teach_v ltt
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.cal_type = ltt.teach_cal_type AND
sua.ci_sequence_number = ltt.teach_ci_sequence_number AND
ltt.load_cal_type = cp_load_cal_type AND
ltt.load_ci_sequence_number = cp_load_ci_sequence_number AND
((IGS_EN_PRC_LOAD.ENRP_GET_LOAD_INCUR(
sua.cal_type,
sua.ci_sequence_number,
sua.discontinued_dt,
sua.administrative_unit_status,
sua.unit_attempt_status,
sua.no_assessment_ind,
cp_load_cal_type,
cp_load_ci_sequence_number,
-- anilk, Audit special fee build
NULL, -- for p_uoo_id
'N') = 'Y') OR
/* added this for bug 3037043, as unit status would still waitlist when called from auto enroll process*/
(sua.uoo_id = p_uoo_id AND
sua.unit_attempt_status = 'WAITLISTED') ) AND
NVL(sua.no_assessment_ind ,'N') = 'N' ;
SELECT uop.owner_org_unit_cd,
ou.start_dt
FROM igs_ps_unit_ofr_opt_all uop,
igs_or_inst_org_base_v ou
WHERE uop.uoo_id = cp_uoo_id AND
uop.owner_org_unit_cd = ou.party_number AND
ou.inst_org_ind = 'O' ;
SELECT um.s_unit_mode
FROM IGS_AS_UNIT_MODE um,
IGS_AS_UNIT_CLASS ucl
WHERE ucl.unit_class = cp_sua_unit_class AND
ucl.closed_ind = 'N' AND
ucl.unit_mode = um.unit_mode;
SELECT am.govt_attendance_mode
FROM IGS_EN_ATD_MODE am
WHERE am.attendance_mode = cp_sca_attendance_mode;
SELECT cow.course_cd,
cow.version_number,
cow.org_unit_cd,
cow.ou_start_dt
FROM IGS_PS_OWN cow
WHERE cow.course_cd = cp_sca_course_cd AND
cow.version_number = cp_sca_version_number AND
((cow.org_unit_cd = cp_uv_owner_org_unit_cd AND
cow.ou_start_dt = cp_uv_owner_ou_start_dt) OR
(IGS_OR_GEN_001.ORGP_GET_WITHIN_OU(
cow.org_unit_cd,
cow.ou_start_dt,
cp_uv_owner_org_unit_cd,
cp_uv_owner_ou_start_dt,
'N') = 'Y'));
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id;