[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_end_day, p_end_month, p_selection_date added in the procedure enrp_ins_btch_prenrl.
Nishikant 16DEC2002 ENCR030(UK Enh) - Bug#2708430. One more parameter p_completion_date added in the procedure
enrp_ins_btch_prenrl.
prraj 11-Dec-2003 Replaced reference to view IGS_EN_NSTD_USEC_DL_V
with base table IGS_EN_NSTD_USEC_DL Bug# 2750716
knaraset 05-Mar-2003 Modified the date comparison in function enrp_get_ua_del_alwd,
such that it returns N, when the first unit discontinuation date is
less than or equal to the given effective date. Bug 2833794
myoganat 23-May-2003 Created cursor cur_no_assesment_ind in ENRP_VAL_CHG_CP to
check for audit attempt - Bug #2855870
svenkata 6-Jun-03 Modified the routine enrp_get_var_window to check for Variation cutoff override at
Person Type level. Bug 2829272.
amuthu 04-JUN-2003 added new parameter p_progress_status to enrp_ins_btch_prenrl as part of bug 2829265
Also added the same parameter in the call to IGS_EN_GEN_10.enrp_ins_sret/snew_prenrl
kkillams 16-06-2003 Three new parameters are added to the enrp_ins_btch_prenrl procedure as part of bug 2829270
amuthu 03-JUL-2003 Added logic to filter Advance and repeating students before call the call to
-- enrp_ins_snew_prenrl and enrp_ins_sret_prenrl instead of checking it within
-- the above mentioned procedures.
rvivekan 29-JUL-2003 Modified several message_name variables from varchar2(30) to varchar2(2000) as
a part of bug#3045405
vkarthik 21-Jan-2004 Removed recursive search from the function enrp_get_within_ci for checking if
the passed calendars are related anywhere in the hierarchy and replaced it with
direct search as part of Bug 3083153
snambaka 14-Mar-2005 Truncated the Effective Dates passed to the Procedure enrp_get_var_window
to ensure the standard validation between the Dates with and without time stamps.
Bug :3930440
ckasu 20-Feb-2006 modified cursor c_sca of enrp_ins_btch_prenrl procedure as a part of bug#5049068
-------------------------------------------------------------------------------------------*/
FUNCTION enrp_get_pr_outcome(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2
) RETURN VARCHAR2 ;
SELECT nvl(daiv.absolute_val,
IGS_CA_GEN_001.calp_set_alias_value(daiv.absolute_val,
IGS_CA_GEN_002.cals_clc_dt_from_dai(daiv.ci_sequence_number,
daiv.CAL_TYPE,
daiv.DT_ALIAS, daiv.sequence_number) ) ) alias_val
FROM igs_pe_usr_adisc_all pua,
igs_ca_da_inst daiv
WHERE daiv.cal_type = p_cal_type
AND daiv.ci_sequence_number = p_ci_sequence_number
AND daiv.dt_alias = pua.disc_dt_alias
AND pua.person_type = cp_person_type
ORDER BY 1;
SELECT usec_disc_dl_date alias_val
FROM igs_en_usec_disc_dl
WHERE uoo_id = p_uoo_id
ORDER BY usec_disc_dl_date, administrative_unit_status;
SELECT daiv.alias_val
FROM igs_ps_unit_disc_crt uddc,
igs_ca_da_inst_v daiv
WHERE uddc.delete_ind = 'N'
AND daiv.cal_type = p_cal_type
AND daiv.ci_sequence_number = p_ci_sequence_number
AND daiv.dt_alias = uddc.unit_discont_dt_alias
ORDER BY daiv.alias_val;
SELECT suao.grade,
suao.grading_schema_cd,
suao.version_number,
suao.finalised_outcome_ind
FROM igs_as_su_stmptout suao
WHERE suao.person_id = cp_person_id AND
suao.course_cd = cp_course_cd AND
suao.uoo_id = cp_uoo_id
ORDER BY suao.outcome_dt;
SELECT gsg.s_result_type
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd AND
gsg.version_number = cp_version_number AND
gsg.grade = cp_grade;
SELECT usec_disc_dl_date alias_val,administrative_unit_status
FROM igs_en_usec_disc_dl
WHERE uoo_id = p_uoo_id
ORDER BY usec_disc_dl_date;
SELECT igs_ca_gen_001.calp_set_alias_value(dai.absolute_val,
igs_ca_gen_002.cals_clc_dt_from_dai(dai.ci_sequence_number,
dai.cal_type,
dai.dt_alias,
dai.sequence_number
)
) alias_val,
uddc.administrative_unit_status,
uddc.dflt_ind
FROM igs_ca_da_inst dai,
igs_ps_unit_disc_crt uddc
WHERE dai.cal_type = p_cal_type AND
dai.ci_sequence_number = p_ci_sequence_number AND
dai.dt_alias = uddc.unit_discont_dt_alias
ORDER BY 1;
SELECT ct.govt_course_type
FROM igs_ps_ver crv,
igs_ps_type ct
WHERE crv.course_cd = p_course_cd AND
crv.version_number = p_version_number AND
crv.course_type = ct.course_type;
SELECT susa.override_title
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;
SELECT cous.override_title,
us.title
FROM igs_en_unit_set us,
igs_ps_ofr_unit_set cous
WHERE us.unit_set_cd = p_unit_set_cd AND
us.version_number = p_us_version_number AND
cous.unit_set_cd(+) = us.unit_set_cd AND
cous.us_version_number(+) = us.version_number AND
cous.course_cd(+) = p_course_cd AND
cous.crv_version_number(+) = p_version_number AND
cous.cal_type(+) = p_cal_type;
Purpose : This function is used to determine if the effective date of an insert, update or
delete of a student unit attempts is within the enrollments variation window
Known limitations,
enhancements,
remarks :
Change History
Who When What
vchappid 04-Jul-01 this function was considering the date aliases defined at the institutional
level only. Now it is changed to consider date aliases/dates at unit section level,
institutional level in the same hierarchy
kkillams 27-Feb-03 Modified cur_dai_v Cursor, replaced * with alias_val w.r.t. bug 2749648
svenkata 6-Jun-03 Added new validation to check if the Variation cutt off has been overriden at the Person Type level.Bug 2829272
snambaka 14-Mar-05 Truncated the p_effective_dt and l_cur_en_nstd_usec_dl.enr_dl_date
to ensure date validation without timestamp to avaoid inconsistency.
******************************************************************/
-- Determine if the effective date of an insert, update or delete
-- of a student IGS_PS_UNIT attempt is within the enrollments variation window.
-- This is determined by an enrollments variation cutoff date within the
-- teaching period calendar instance of the student IGS_PS_UNIT attempt.
-- cursor for getting the dates that are defined
-- at the unit level for the logged on person and for the uoo_id passed
CURSOR cur_en_nstd_usec_dl
IS
SELECT enr_dl_date
FROM igs_en_nstd_usec_dl
WHERE uoo_id = p_uoo_id
AND function_name='VARIATION_CUTOFF'
ORDER BY enr_dl_date;
SELECT variation_cutoff_dt_alias
FROM IGS_EN_CAL_CONF
WHERE s_control_num = 1;
SELECT alias_val
FROM igs_ca_da_inst_v
WHERE cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND dt_alias = cp_dt_alias
ORDER BY alias_val DESC;
SELECT 'x'
FROM IGS_PE_USR_AVAL
WHERE person_type = p_person_type AND
validation = 'OVR_VAR_CUT_OFF' AND
override_ind = 'Y' ;
SELECT sup_cal_type,
sup_ci_sequence_number
FROM igs_ca_inst_rel
WHERE
sup_cal_type = cp_sup_cal_ty AND
sup_ci_sequence_number = cp_sup_ci_seq_num AND
sub_cal_type = cp_sub_cal_ty AND
sub_ci_sequence_number = cp_sub_ci_seq_num;
p_selection_date IN DATE,
--Below parameter added as part of ENCR030(UK Enh) - Bug#2708430
p_completion_date IN DATE ,
p_log_creation_dt OUT NOCOPY DATE,
p_progress_stat IN VARCHAR2,
p_dflt_enr_method IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_ci_seq_num IN NUMBER)
AS
/* HISTORY
WHO WHEN WHAT
ayedubat 25-MAY-2002 Changed the cursor,c_acaiv to replace the view,IGS_AD_PS_APPL_INST_APLINST_V
with the base table,IGS_AD_PS_APPL_INST and also replaced the function calls
Igs_En_Gen_002.enrp_get_acai_offer and Igs_En_Gen_014.ENRS_GET_WITHIN_CI as aprt of the bug fix: 2384449
Nishikant 11JUN2002 Bug#2392277. The cursor c_sca modified to add a condition to check whether any of the unit code parameter is provided or not.
If provided then the pre enrollment process will consider all the unit code(s) provided for the persons
evenif they have already enrolled into any unit in the provided enrollment period.
Nishikant 04OCT2002 UK Enhancement build. Bug#2580731. Five new parameters p_start_day, p_start_month, p_end_day, p_end_month, p_selection_date
added to the procedure. Also the cursor c_sca modified to call local Function get_commence_date_range.
Nishikant 16DEC2002 ENCR030(UK Enh), Bug#2708430. One more parameter p_completion date added to the signature.
ptandon 23-JUN-2003 The Cursor c_sca was modified to replace call to the IGS_EN_GEN_014.ENRS_GET_WITHIN_CI routine with
direct joins to the calendar instance tables. Bug# 3004806.
svanukur 02-jul-2003 The cursors c_sca and c_acaiv were modified to include only the active members of a groupid
as per bug# 3030782
rvivekan 29-JUL-2003 Modified several message_name variables from varchar2(30) to varchar2(2000) as
a part of bug#3045405
ckasu 20-Feb-2006 modified cursor c_sca as a part of performance bug#5049068
*/
BEGIN -- enrp_ins_btch_prenrl
-- This routine will pre-enrol a group of students in
-- ?batch? mode as specified by the parameters passed to it.
-- The routine will process both new and returning students,
-- with the logic being the following:
-- New Students:
-- * Loop through IGS_AD_PS_APPL_INST_APLINST_V records matching the academic period
-- and the specified IGS_PS_COURSE offering option parameters (eg. IGS_PS_COURSE code,
-- IGS_AD_LOCATION, etc).
-- * Call the ENRP_INS_SNEW_PRENRL routine to perform the pre-enrolment on each
-- student.
-- Returning Students:
-- * Loop through IGS_EN_STDNT_PS_ATT records with a status of ENROLLED,
-- INACTIVE, INTERMIT or UNCONFIRM matching the specified IGS_PS_COURSE offering
-- option parameters.
-- * Call the ENRP_INS_SRET_PRENRL routine to perform the pre-enrolment on
-- each student.
-- The output from the processing is logged to the IGS_GE_S_LOG table, which produces
-- an- exception report.
-- Following is a description of the parameters:
-- p_course_cd; the IGS_PS_COURSE on which to match students. Can be %.
SELECT sca.person_id,
sca.course_cd
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv,
IGS_PS_STAT cs
WHERE sca.course_cd LIKE p_course_cd AND
sca.cal_type = p_acad_cal_type AND
sca.location_cd LIKE p_location_cd AND
sca.attendance_type LIKE p_attendance_type AND
sca.attendance_mode LIKE p_attendance_mode AND
sca.course_attempt_status IN (
cst_enrolled,
cst_inactive,
cst_intermit) AND
(p_person_group_id IS NULL OR
EXISTS (SELECT 'x'
FROM IGS_PE_PRSID_GRP_MEM gm
WHERE gm.group_id = p_person_group_id AND
gm.person_id = sca.person_id AND
(gm.end_date IS NULL OR gm.end_date >= trunc(sysdate))AND
(gm.start_date IS NULL OR gm.start_date <= trunc(sysdate)))) AND
crv.course_cd = sca.course_cd AND
crv.version_number = sca.version_number AND
crv.course_status = cs.course_status AND
cs.s_course_status = cst_active AND
crv.course_type like p_course_type AND
(crv.responsible_org_unit_cd LIKE p_responsible_org_unit_cd OR
EXISTS (SELECT 'x'
FROM IGS_OR_INST_ORG_BASE_V ou,
IGS_OR_STATUS os
WHERE ou.PARTY_NUMBER LIKE p_responsible_org_unit_cd AND
ou.org_status = os.org_status AND
ou.inst_org_ind = 'O' AND
os.s_org_status = cst_active AND
IGS_OR_GEN_001.ORGP_GET_WITHIN_OU(
ou.PARTY_NUMBER,
ou.start_dt,
crv.responsible_org_unit_cd,
crv.responsible_ou_start_dt,
'N') = 'Y')) AND
-- IGS_GE_NOTE: this section of the query deals with determining if the student has
-- already been pre-enrolled and whether they may require a IGS_PS_UNIT pre-enrolment
(
NOT EXISTS (
SELECT person_id
FROM IGS_AS_SC_ATMPT_ENR
WHERE person_id = sca.person_id AND
course_cd = sca.course_cd AND
cal_type = p_enr_cal_type AND
ci_sequence_number = p_enr_sequence_number) OR
-- The bellow condition is added by Nishikant - bug#2392277 - 11JUN2002.
-- Its checking whether any of the unit code parameter is provided or not.
( p_unit1_unit_cd is not null OR
p_unit2_unit_cd is not null OR
p_unit3_unit_cd is not null OR
p_unit4_unit_cd is not null OR
p_unit5_unit_cd is not null OR
p_unit6_unit_cd is not null OR
p_unit7_unit_cd is not null OR
p_unit8_unit_cd is not null OR
p_unit9_unit_cd is not null OR
p_unit10_unit_cd is not null OR
p_unit11_unit_cd is not null OR
p_unit12_unit_cd is not null ) OR
( ( p_units_indicator = 'Y' OR p_units_indicator = 'CORE_ONLY') AND
EXISTS (
SELECT course_cd
FROM IGS_PS_PAT_OF_STUDY pos
WHERE course_cd = sca.course_cd AND
version_number = sca.version_number) AND
NOT EXISTS (
SELECT person_id
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST_REL cr
WHERE sua.person_id = sca.person_id AND
sua.course_cd = sca.course_cd AND
sua.cal_type = cr.sub_cal_type AND
sua.ci_sequence_number = cr.sub_ci_sequence_number AND
cr.sup_cal_type = p_acad_cal_type AND
cr.sup_ci_sequence_number= p_acad_sequence_number)
))
-- The Below Function call added as part of the UK Enhancement. Enh bug#2580731 - 04OCT2002
AND get_commence_date_range(
p_start_day,
p_start_month,
p_end_day,
p_end_month,
sca.commencement_dt) = 'TRUE';
SELECT scae.enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci
WHERE scae.person_id = cp_person_id AND
scae.course_cd = cp_course_cd AND
ci.cal_type = scae.cal_type AND
ci.sequence_number = scae.ci_sequence_number AND
ci.start_dt <= cp_start_dt;
SELECT acai.person_id,
acai.course_cd,
acai.admission_appl_number,
acai.nominated_course_cd,
acai.sequence_number
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_APPL aa,
IGS_PS_VER crv,
IGS_PS_STAT cs
WHERE
acai.course_cd LIKE p_course_cd AND
acai.location_cd LIKE p_location_cd AND
acai.attendance_mode LIKE p_attendance_mode AND
acai.attendance_type LIKE p_attendance_type AND
aa.person_id = acai.person_id AND
aa.admission_appl_number = acai.admission_appl_number AND
aa.admission_cat LIKE p_admission_cat AND
aa.acad_cal_type = p_acad_cal_type AND
aa.acad_ci_sequence_number = p_acad_sequence_number AND
(( p_adm_cal_type IS NULL AND
p_adm_sequence_number IS NULL) OR
( NVL(acai.adm_cal_type,aa.adm_cal_type) = p_adm_cal_type AND
NVL(acai.adm_ci_sequence_number,aa.adm_ci_sequence_number) = p_adm_sequence_number) ) AND
EXISTS( SELECT 'X'
FROM IGS_AD_OU_STAT os,
IGS_AD_OFR_RESP_STAT rs
WHERE os.adm_outcome_status = acai.adm_outcome_status AND
rs.adm_offer_resp_status = acai.adm_offer_resp_status AND
IGS_AD_GEN_008.Admp_Get_Saos(acai.adm_outcome_status) IN ('OFFER','COND-OFFER') AND
IGS_AD_GEN_008.Admp_Get_Saors(acai.adm_offer_resp_status) NOT IN ('LAPSED','REJECTED')) AND
(p_person_group_id is null OR
EXISTS (SELECT 'x'
FROM IGS_PE_PRSID_GRP_MEM gm
WHERE gm.group_id = p_person_group_id AND
gm.person_id = acai.person_id AND
(gm.end_date IS NULL OR gm.end_date >= trunc(sysdate))AND
(gm.start_date IS NULL OR gm.start_date <= trunc(sysdate)) )) AND
crv.course_cd = acai.course_cd AND
crv.version_number = acai.crv_version_number AND
crv.course_status = cs.course_status AND
cs.s_course_status = cst_active AND
crv.course_type LIKE p_course_type AND
(crv.responsible_org_unit_cd LIKE p_responsible_org_unit_cd OR
EXISTS (SELECT 'x'
FROM IGS_OR_UNIT ou,
IGS_OR_STATUS os
WHERE ou.org_unit_cd LIKE p_responsible_org_unit_cd AND
ou.org_status = os.org_status AND
os.s_org_status = cst_active AND
IGS_OR_GEN_001.ORGP_GET_WITHIN_OU(
ou.org_unit_cd,
ou.start_dt,
crv.responsible_org_unit_cd,
crv.responsible_ou_start_dt,'N') = 'Y' ) ) AND
-- IGS_GE_NOTE: this section of the query deals with determining if the student has
-- already been pre-enrolled and whether they may require a IGS_PS_UNIT pre-enrolment
(NOT EXISTS (
SELECT person_id
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = acai.person_id AND
course_cd = acai.course_cd AND
adm_admission_appl_number = acai.admission_appl_number AND
adm_nominated_course_cd = acai.nominated_course_cd AND
adm_sequence_number = acai.sequence_number) OR
(p_unit1_unit_cd is not null OR
p_unit2_unit_cd is not null OR
p_unit3_unit_cd is not null OR
p_unit4_unit_cd is not null OR
p_unit5_unit_cd is not null OR
p_unit6_unit_cd is not null OR
p_unit7_unit_cd is not null OR
p_unit8_unit_cd is not null OR
p_unit9_unit_cd is not null OR
p_unit10_unit_cd is not null OR
p_unit11_unit_cd is not null OR
p_unit12_unit_cd is not null) OR
( ( p_units_indicator = 'Y' OR p_units_indicator = 'CORE_ONLY') AND
EXISTS (
SELECT course_cd
FROM IGS_PS_PAT_OF_STUDY pos
WHERE course_cd = acai.course_cd AND
version_number = acai.crv_version_number ) AND
NOT EXISTS (
SELECT person_id
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_INST_REL cr
WHERE sua.person_id = acai.person_id AND
sua.course_cd = acai.course_cd AND
sua.cal_type = cr.sub_cal_type AND
sua.ci_sequence_number = cr.sub_ci_sequence_number AND
cr.sup_cal_type = p_acad_cal_type AND
cr.SUP_CI_SEQUENCE_NUMBER= p_acad_sequence_number) ))
ORDER BY
acai.person_id,
acai.course_cd,
acai.offer_dt DESC;
SELECT start_dt
FROM IGS_CA_INST eci
WHERE cal_type = p_enr_cal_type AND
sequence_number = p_enr_sequence_number;
SELECT sca.course_cd,susa.unit_set_cd
FROM IGS_AS_SU_SETATMPT susa,
IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id =cp_person_id
AND sca.course_cd =cp_course_cd
AND susa.person_id =sca.person_id
AND susa.course_cd =sca.course_cd
AND susa.unit_set_cd=cp_unit_set_cd
AND susa.selection_dt IS NOT NULL
AND susa.rqrmnts_complete_dt IS NULL
AND susa.end_dt IS NULL;
SELECT rel.sub_cal_type, rel.sub_ci_sequence_number FROM igs_ca_inst_rel rel,
igs_ca_inst ci,
igs_ca_type cal
WHERE rel.sup_cal_type = p_acad_cal_type
AND rel.sup_ci_sequence_number = p_acad_seq_num
AND rel.sub_cal_type = ci.cal_type
AND rel.sub_ci_sequence_number = ci.sequence_number
AND rel.sub_cal_type = cal.cal_type
AND cal.s_cal_cat = 'LOAD'
AND cal.closed_ind = 'N'
ORDER BY ci.start_dt;
NVL(p_selection_date,'')||'/'||
-- The Below parameter completion_date added as part of ENCR030(UK Enh) - Bug#2708430 - 16DEC2002
NVL(p_completion_date,'')||'/'||
p_dflt_enr_method||'/'||
p_load_cal_type||'/'||
TO_CHAR(p_load_ci_seq_num),
v_log_creation_dt
);
p_selection_date, --Added as part of the UK Enh Buid- Bug#2580731
p_completion_date, --Added as part of ENCR030(UK Enh) - Bug#2708430 - 16DEC2002
p_progress_stat,
l_enr_method,
l_load_cal_type,
l_load_seq_num
) THEN
exception_total := exception_total + 1;
p_selection_date, --Added as part of the UK Enh Buid- Bug#2580731
p_completion_date, --Added as part of ENCR030(UK Enh) - Bug#2708430 - 18DEC2002
p_progress_stat,
l_enr_method,
l_load_cal_type,
l_load_seq_num
) THEN
exception_total := exception_total + 1;
SELECT person_party_id person_id
FROM fnd_user
WHERE user_name = cp_user_name;
SELECT pti.person_type_code
FROM igs_pe_typ_instances pti,
igs_pe_person_types pt
WHERE pti.person_id = cp_person_id AND
pti.person_type_code = pt.person_type_code AND
pt.system_type = 'SS_ENROLL_STAFF' AND
TRUNC(SYSDATE) BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE)
order by rank asc;
SELECT person_type_code
FROM igs_pe_typ_instances_all
WHERE person_id = cp_person_id AND
course_cd = NVL(p_course_cd,course_cd) AND
end_date IS NULL AND
person_type_code IN ( SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'STUDENT');
SELECT fun.function_name
FROM fnd_form_functions fun
WHERE fun.type = 'JSP' AND
fun.function_name IN ('IGS_SS_ADMIN_HOME','IGS_SS_STUDENT_HOME') AND
fun.FUNCTION_ID IN (SELECT menu.FUNCTION_ID
FROM fnd_menu_entries menu
CONNECT BY menu.menu_id = PRIOR menu.SUB_MENU_ID
START WITH menu.menu_id = (SELECT resp.MENU_ID
FROM fnd_responsibility resp
WHERE responsibility_id = fnd_global.RESP_ID
AND resp.application_id = 8405));
SELECT COUNT(1) num_grade_schemas
FROM igs_ps_usec_grd_schm
WHERE uoo_id = p_uoo_id;
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT COUNT(1) num_grade_schemas
FROM igs_ps_unit_grd_schm
WHERE unit_code = cp_unit_code
AND unit_version_number = cp_version_number;
SELECT nvl(dai.absolute_val,
IGS_CA_GEN_001.calp_set_alias_value(dai.absolute_val,
IGS_CA_GEN_002.cals_clc_dt_from_dai(dai.ci_sequence_number, dai.CAL_TYPE,
dai.DT_ALIAS, dai.sequence_number) ) ) alias_val
FROM IGS_PE_USR_ARG_ALL pua,
IGS_CA_DA_INST dai
WHERE pua.person_type = cp_person_type
AND dai.dt_alias = pua.grad_sch_dt_alias
AND dai.cal_type = p_cal_type
AND dai.ci_sequence_number = p_ci_sequence_number
ORDER BY 1;
SELECT enr_dl_date alias_val
FROM igs_en_nstd_usec_dl
WHERE function_name = 'GRADING_SCHEMA'
AND uoo_id = p_uoo_id
ORDER BY 1;
SELECT dai.alias_val alias_val
FROM igs_ca_da_inst_v dai, igs_en_cal_conf ecc
WHERE dai.cal_type = p_cal_type
AND dai.ci_sequence_number = p_ci_sequence_number
AND dai.dt_alias = ecc.grading_schema_dt_alias
AND ecc.s_control_num =1
ORDER BY 1;
Purpose : This func determines if credit points can be updated
Known limitations,
enhancements,
remarks :
Change History
Who When What
kkillams 27-02-2003 Modified cur_ps_unit_ofr cursor, * replaced with unit_cd and version_number w.r.t. bug 2749648
myoganat 23-05-2003 Created cursor cur_no_assesment_ind to check for
audit attempts #2855870
******************************************************************/
l_dummy VARCHAR2(100);
SELECT unit_cd, version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
IS SELECT points_override_ind
FROM igs_ps_unit_ver_v
WHERE unit_cd = p_unit_cd
AND version_number = p_unit_ver_num;
SELECT nvl(dai.absolute_val,
IGS_CA_GEN_001.calp_set_alias_value(dai.absolute_val,
IGS_CA_GEN_002.cals_clc_dt_from_dai(dai.ci_sequence_number, dai.CAL_TYPE,
dai.DT_ALIAS, dai.sequence_number) ) ) alias_val
FROM igs_ca_da_inst dai,igs_pe_usr_arg_all pua
WHERE pua.person_type = cp_person_type
AND dai.dt_alias = pua.grad_sch_dt_alias
AND dai.cal_type = p_cal_type
AND dai.ci_sequence_number = p_ci_sequence_number
ORDER BY 1;
SELECT enr_dl_date alias_val
FROM igs_en_nstd_usec_dl
WHERE function_name = 'GRADING_SCHEMA'
AND uoo_id = p_uoo_id
ORDER BY 1;
SELECT dai.alias_val alias_val
FROM igs_ca_da_inst_v dai, igs_en_cal_conf ecc
WHERE dai.cal_type = p_cal_type
AND dai.ci_sequence_number = p_ci_sequence_number
AND dai.dt_alias = ecc.grading_schema_dt_alias
AND ecc.s_control_num =1
ORDER BY 1;
SELECT system_type
FROM igs_pe_person_types
WHERE person_type_code = cp_person_type;
SELECT no_assessment_ind
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND uoo_id = p_uoo_id
AND cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number;
IF fnd_profile.value('IGS_EN_UPDATE_CP_GS')='Y' THEN
RETURN 'N';
SELECT discontinuation_reason_cd
FROM IGS_EN_DCNT_REASONCD dr
WHERE dr.s_discontinuation_reason_type = p_s_discont_reason_type AND
dr.sys_dflt_ind = 'Y' AND
dr.closed_ind = 'N';
SELECT susa.selection_dt
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.selection_dt IS NOT NULL AND
susa.end_dt IS NULL AND
susa.rqrmnts_complete_dt IS NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR' ;
SELECT susa.selection_dt
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.rqrmnts_complete_dt IS NOT NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR'
ORDER BY susa.rqrmnts_complete_dt desc ;
CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
SELECT pou.decision_dt, pout.s_progression_outcome_type
FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
WHERE pou.person_id = p_person_id AND
pou.course_cd = p_course_cd AND
pou.decision_status = 'APPROVED' AND
pou.decision_dt IS NOT NULL AND
pou.decision_dt > cp_select_dt AND
pou.progression_outcome_type = pout.progression_outcome_type
ORDER BY pou.decision_dt desc ;
v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
v_selection_dt := NULL;
FETCH c_active_us INTO v_selection_dt;
FETCH c_last_us INTO v_selection_dt ;
OPEN c_prog_outcome(v_selection_dt) ;