[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
jbegum 25-Jun-2003 BUG#2930935
Modified local function ENRP_CUR_CRITERIA
KNAG.IN 12-APR-2001 Included enrollment credit point
priority in timeslot allocation
as per enh bug 1710227
Nishikant 05AUG2002 Bug#2443771. The cursor cur_total_admted_stdnts got modified to select the students
who enrolled directly in Student Enrollments. A new function calc_cum_gpa_person was
written to calculate the Total GPA for a student ina provided LOAD or TEACHING calendar.
also a new function acad_teach_rel_exist introduced in Spec and Body to be used in a cursor only.
Nishikant 20DEC2002 Bug#2712493. The cursors cur_total_enrled_stdnts and cur_total_admted_stdnts got modified,
in the function enrp_total_students, to select properly the students under 'Enrolled'
and 'Admitted' category.
Nishikant 31MAR2003 The field full_name modified to last_name in the record
type pdata_1 and pdata_2. Bug#2455364.
smaddali 20-sep-2004 Modified enrp_total_students for cursor cur_total_enrled_stdnts bug#3918075
ctyagi 13-Apr-2005 Modified cursor cur_total_admted_stdnts bug#4297791
ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) in enrp_assign_timeslot procedure as a part of bug#4958173.
***************************************************************/
plsql_empty plsql_table_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_prg_type_gr_cd IN VARCHAR2,
p_stdnt_type IN VARCHAR2,
p_cal_type IN VARCHAR2,
p_seq_num IN NUMBER)
RETURN NUMBER AS
plsql_1 plsql_table_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_prsnt_date DATE
) RETURN DATE
AS
BEGIN
IF TO_CHAR(p_prsnt_date,'DY') NOT IN ('SAT','SUN') THEN
-- if present day is working day return the same
RETURN TRUNC(p_prsnt_date);
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_program_type_group_cd IN VARCHAR2,
p_student_type IN VARCHAR2,
p_cal_type IN VARCHAR2,
p_seq_number IN NUMBER,
p_timeslot IN VARCHAR2,
p_ts_start_dt IN DATE,
p_ts_end_dt IN DATE,
p_length_of_time IN VARCHAR2,
p_start_time IN DATE,
p_end_time IN DATE,
p_total_num_students OUT NOCOPY NUMBER,
p_num_ts_sessions OUT NOCOPY NUMBER) AS
total_min_per_day NUMBER := 0;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_start_time IN DATE,
p_end_time IN DATE,
p_start_date IN DATE,
p_end_date IN DATE,
p_length_of_time IN NUMBER)
RETURN plsql_table_3 AS
plsql_5 plsql_table_3;
plsql_5.DELETE(cnt);
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
(reverse chronological order - newest change first)
Who When What
jbegum 25-Jun-2003 BUG#2930935
Modified cursors cur_load_teach_prd,cur_enrolled_cp.
KNAG.IN 12-APR-2001 Included enrollment credit point
priority in timeslot allocation
as per enh bug 1710227
Nishikant 02AUG2002 Bug#2443771. Calculation of GPA for the student was missing.
Created a local procedure calc_cum_gpa_person to calculate cumulative GPA
for a student of all the program attempts.
***************************************************************/
p_plsql_1 IN OUT NOCOPY plsql_table_1,
p_priority_value IN VARCHAR2,
p_preference_code IN VARCHAR2,
p_preference_version IN NUMBER,
p_sequence_number IN NUMBER,
p_pointer IN NUMBER,
p_ts_stup_id IN NUMBER)
RETURN plsql_table_1 IS
CURSOR c_cal_type(p_ts_stup_id NUMBER) IS
SELECT ets.cal_type, ets.sequence_number
FROM igs_en_timeslot_stup ets
WHERE igs_en_timeslot_stup_id = p_ts_stup_id;
SELECT sca.person_id
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver pv
WHERE sca.person_id = p_person_id AND
sca.course_cd = pv.course_cd AND
sca.version_number = pv.version_number AND
pv.course_type = p_course_type;
SELECT sca.person_id
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver pv
WHERE sca.person_id = p_person_id AND
sca.course_cd = pv.course_cd AND
sca.version_number = pv.version_number AND
pv.responsible_org_unit_cd = p_org_unit_cd;
SELECT sca.person_id
FROM igs_en_stdnt_ps_att sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.version_number = p_version_number;
SELECT pgm.person_id
FROM igs_pe_prsid_grp_mem pgm,
igs_pe_persid_group pg
WHERE pgm.person_id = p_person_id AND
pgm.group_id = pg.group_id AND
pg.group_cd = p_group_cd
AND nvl( pgm.START_DATE,SYSDATE)<= SYSDATE
AND nvl( pgm.END_DATE,SYSDATE)>= SYSDATE;
SELECT sca.person_id
FROM igs_en_stdnt_ps_att sca,
igs_ps_stage ps
WHERE sca.person_id = p_person_id AND
sca.course_cd = ps.course_cd AND
sca.version_number = ps.version_number AND
ps.course_stage_type = p_course_stage_type;
SELECT sca.course_cd
FROM igs_en_stdnt_ps_att sca
WHERE sca.person_id = p_person_id and
sca.course_attempt_status = 'ENROLLED';
SELECT sua.unit_cd,sua.version_number,sua.override_enrolled_cp,sua.uoo_id
FROM igs_en_su_attempt sua,
igs_ca_inst_rel carel
WHERE sua.person_id = p_person_id and
sua.unit_attempt_status = 'ENROLLED' and
((sua.cal_type = p_cal_type and
sua.ci_sequence_number = p_sequence_number)OR
(sua.cal_type = carel.sub_cal_type and
sua.ci_sequence_number = carel.sub_ci_sequence_number and
carel.sup_cal_type = p_cal_type and
carel.sup_ci_sequence_number = p_sequence_number));
SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) enrolled_credit_points
FROM igs_ps_unit_ver uv ,
igs_ps_usec_cps cps ,
igs_ps_unit_ofr_opt uoo
WHERE
uoo.uoo_id = cps.uoo_id(+) AND
uoo.unit_cd = uv.unit_cd AND
uoo.version_number = uv.version_number AND
uoo.uoo_id = cp_uoo_id;
SELECT s_cal_cat
FROM igs_ca_type
WHERE cal_type = p_cal_type;
SELECT load_cal_type, load_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE teach_cal_type = p_cal_type
AND teach_ci_sequence_number = p_seq_num
ORDER BY load_start_dt DESC;
SELECT course_cd, version_number, cal_type
FROM IGS_EN_STDNT_PS_ATT sca
WHERE person_id = p_person_id;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_plsql_3 IN OUT NOCOPY plsql_table_1,
p_surname_alpha VARCHAR2)
RETURN plsql_table_1 IS
-- record of plsql_table_1
plsql_tmp_rec pdata_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_plsql_3 IN OUT NOCOPY plsql_table_1,
p_surname_alpha VARCHAR2)
RETURN plsql_table_1 IS
-- record of plsql_table_1
plsql_tmp_rec pdata_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_plsql_3 IN OUT NOCOPY plsql_table_1,
p_surname_alpha VARCHAR2)
RETURN plsql_table_1 IS
-- record of plsql_table_1
plsql_tmp_rec pdata_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
KNAG.IN 12-APR-2001 Included enrollment credit point
priority in timeslot allocation
as per enh bug 1710227
(reverse chronological order - newest change first)
***************************************************************/
p_plsql_3 IN OUT NOCOPY plsql_table_1,
p_surname_alpha VARCHAR2)
RETURN plsql_table_1 IS
-- record of plsql_table_1
plsql_tmp_rec pdata_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
KNAG.IN 12-APR-2001 Included enrollment credit point
priority in timeslot allocation
as per enh bug 1710227
(reverse chronological order - newest change first)
***************************************************************/
p_plsql_2 IN OUT NOCOPY plsql_table_1,
p_surname_alpha IN VARCHAR2,
p_pointer NUMBER) IS
plsql_3 plsql_table_1;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
KNAG.IN 12-APR-2001 Included enrollment credit point
priority in timeslot allocation
as per enh bug 1710227
(reverse chronological order - newest change first)
Nishikant 23JUL2002 Bug#2443771. The cursor cur_max_prty_ord and cur_prty got modified.
Also the value assigned to the variabke max_prty_ord from the cursor
cur_max_prty_ord before using it down the line.
svanukur 08jul2003 checking for NVL of max_prty_ord as part of bug 3039661
***************************************************************/
p_plsql_2 IN OUT NOCOPY plsql_table_1,
p_surname_alpha IN VARCHAR2,
p_ts_stup_id IN NUMBER,
p_pointer IN NUMBER
) IS
CURSOR cur_max_prty_ord IS
SELECT MAX(priority_order)
FROM igs_en_timeslot_prty prt
WHERE prt.igs_en_timeslot_stup_id = p_ts_stup_id; -- Added the where condition by Nishikant - 23JUL2002 - bug#2443771
SELECT prt.priority_value,prf.preference_code,prf.preference_version,prf.sequence_number
FROM igs_en_timeslot_prty prt,
igs_en_timeslot_pref prf
WHERE prt.IGS_EN_TIMESLOT_STUP_ID = p_IGS_EN_TIMESLOT_STUP_ID AND
prt.priority_order = p_order AND
prt.igs_en_timeslot_prty_id = prf.igs_en_timeslot_prty_id
order by prt.priority_order, prf.preference_order;
SELECT priority_value
FROM igs_en_timeslot_prty prt
WHERE priority_order = p_order
AND prt.igs_en_timeslot_stup_id = p_ts_stup_id; -- Added the AND condition by Nishikant - 23JUL2002 - bug#2443771
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
p_plsql_4 IN plsql_table_2,
p_plsql_5 IN plsql_table_3,
p_max_head_count IN NUMBER,
p_igs_en_timeslot_para_id IN NUMBER,
p_length_of_time IN NUMBER) AS
cnt_4 NUMBER := 0;
Igs_En_Timeslot_Rslt_Pkg.insert_row ( x_rowid => l_rowid,
x_igs_en_timeslot_rslt_id => l_igs_en_timeslot_rslt_id,
x_igs_en_timeslot_para_id => p_igs_en_timeslot_para_id,
x_person_id => p_plsql_4(i).person_id,
x_start_dt_time => p_plsql_5(1).start_dt_time,
x_end_dt_time => p_plsql_5(1).end_dt_time,
x_mode => 'R');
Igs_En_Timeslot_Rslt_Pkg.insert_row ( x_rowid => l_rowid,
x_igs_en_timeslot_rslt_id => l_igs_en_timeslot_rslt_id,
x_igs_en_timeslot_para_id => p_igs_en_timeslot_para_id,
x_person_id => p_plsql_4(cnt_4).person_id,
x_start_dt_time => p_plsql_5(i).start_dt_time,
x_end_dt_time => p_plsql_5(i).end_dt_time,
x_mode => 'R');
Igs_En_Timeslot_Rslt_Pkg.insert_row ( x_rowid => l_rowid,
x_igs_en_timeslot_rslt_id => l_igs_en_timeslot_rslt_id,
x_igs_en_timeslot_para_id => p_igs_en_timeslot_para_id,
x_person_id => p_plsql_4(i).person_id,
x_start_dt_time => NULL,
x_end_dt_time => NULL,
x_mode => 'R');
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
KNAG.IN 12-APR-2001 Included enrollment credit point
priority in timeslot allocation
as per enh bug 1710227
(reverse chronological order - newest change first)
Nishikant 23JUL2002 Bug#2443771. The cursor cur_max_prty_ord got modified to include the where condition.
The cursor cur_assign_ran_alpha got modified to select surname_alphabet also. The cursor cur_alpha
which was existing for selecting the surname_alphabet got removed.
svanukur 08jul2003 modified the check for variable max_prty_ord ,if it is null instead of
checking if the cur_max_prty_ord cursor is not found since an aggregate function is used.
as part of bug 3039661
ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL); as a prt of bug#4958173.
SELECT MAX(priority_order)
FROM igs_en_timeslot_prty prt
WHERE prt.igs_en_timeslot_stup_id = p_igs_en_timeslot_stup_id; -- Added the where condition by Nishikant - 23JUL2002 - bug#2443771
SELECT ets.IGS_EN_TIMESLOT_STUP_ID
FROM igs_en_timeslot_stup ets
WHERE ets.PROGRAM_TYPE_GROUP_CD = p_prg_type_gr_cd AND
ets.CAL_TYPE = p_cal_type AND
ets.SEQUENCE_NUMBER = p_seq_num AND
ets.STUDENT_TYPE = p_stud_type;
SELECT prt.priority_value,prf.preference_code,prf.preference_version,prf.sequence_number
FROM igs_en_timeslot_prty prt,
igs_en_timeslot_pref prf
WHERE prt.IGS_EN_TIMESLOT_STUP_ID = p_IGS_EN_TIMESLOT_STUP_ID AND
prt.priority_order = p_order AND
prt.igs_en_timeslot_prty_id = prf.igs_en_timeslot_prty_id
order by prf.preference_order;
SELECT ets.ASSIGN_RANDOMLY, ets.SURNAME_ALPHABET
FROM igs_en_timeslot_stup ets
WHERE ets.PROGRAM_TYPE_GROUP_CD = p_prg_type_gr_cd AND
ets.CAL_TYPE = p_cal_type AND
ets.SEQUENCE_NUMBER = p_seq_num AND
ets.STUDENT_TYPE = p_stud_type;
SELECT etp.ROWID,etp.igs_en_timeslot_para_id,etp.ts_mode
FROM igs_en_timeslot_para etp
WHERE etp.program_type_group_cd = p_prg_type_gr_cd AND
etp.student_type = p_stud_type AND
etp.cal_type = p_cal_type AND
etp.sequence_number = p_seq_num AND
etp.timeslot_calendar = p_timeslot;
SELECT tsr.ROWID row_id
FROM Igs_En_Timeslot_Rslt tsr
WHERE tsr.igs_en_timeslot_para_id = p_igs_en_timeslot_para_id;
Igs_En_Timeslot_Rslt_Pkg.delete_row(x_rowid => cur_rslt_rec.row_id);
Igs_En_Timeslot_Para_Pkg.delete_row(x_rowid => l_rowid);
Igs_En_Timeslot_Para_Pkg.insert_row(x_rowid => l_rowid,
x_igs_en_timeslot_para_id => l_igs_en_timeslot_para_id,
x_program_type_group_cd => p_prg_type_gr_cd,
x_cal_type => p_cal_type,
x_sequence_number => p_seq_num,
x_student_type => p_stud_type,
x_timeslot_calendar => p_timeslot,
x_timeslot_st_time => p_start_time,
x_timeslot_end_time => p_end_time,
x_ts_mode => p_mode,
x_max_head_count => p_max_headcount,
x_length_of_time => p_length_of_time,
x_mode => 'R',
x_org_id => p_orgid);
SELECT 'x' FROM igs_ca_inst_rel cir2
WHERE cir2.sup_cal_type = p_acad_cal_type
AND cir2.sub_cal_type = p_teach_cal_type
AND cir2.sub_ci_sequence_number = p_teach_seq_num;
Purpose : To set the column values before inserting.
Know limitations, enhancements or remarks
Change History
Who When What
Nishikant 20DEC2002 Bug#2712493. The cursors cur_total_enrled_stdnts and cur_total_admted_stdnts got
modified to select properly the students under 'Enrolled' and 'Admitted' category.
Nishikant 23JUL2002 The cursor cur_total_admted_stdnts got modified to consider the students
enrolled directly through the Student Enrollments form.
rnirwani 13-Sep-2004 changed cursor cur_total_enrled_stdnts to not consider logically deleted records. Bug# 3885804
smaddali 20-sep-2004 Modified for cursor cur_total_enrled_stdnts bug#3918075, to add outer join between intermissions and program attempts
stutta 17-Feb-2006 Modified cursor cur_total_admted_stdnts for perf bug#5042384
(reverse chronological order - newest change first)
***************************************************************/
p_prg_type_gr_cd IN VARCHAR2,
p_stdnt_type IN VARCHAR2,
p_cal_type IN VARCHAR2,
p_seq_num IN NUMBER)
RETURN plsql_table_1 AS
--Bug#2712493. The below cursor got modified to select students in "Enrolled" category as below
--Entered through Student Enrollments, having program attempt of status ENROLLED.
--And students having program status INACTIVE and have at least one unit attempt(of any status).
--And students having program attempts of INTERMIT status where the end date of the intermission should
-- be before the start date of the calendar instance provided.
-- smaddali modified for bug#3918075, to move intermissions join to a subquery
CURSOR cur_total_enrled_stdnts(p_start_dt DATE ) IS
SELECT DISTINCT (pe.person_id) person_id ,pe.last_name
FROM igs_en_stdnt_ps_att sca,
igs_pe_person_base_v pe,
igs_ps_ver pv,
igs_ps_type pt
WHERE pe.person_id = sca.person_id AND
pv.course_cd = sca.course_cd AND
pv.course_type = pt.course_type AND
pt.course_type_group_cd = p_prg_type_gr_cd AND
( sca.course_attempt_status = 'ENROLLED'
OR
( sca.course_attempt_status = 'INTERMIT' AND
EXISTS (SELECT 'X' FROM igs_en_stdnt_ps_intm sci, igs_en_intm_types eit WHERE
sci.end_dt < p_start_dt AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
sca.person_id = sci.person_id AND
sca.course_cd = sci.course_cd AND
sci.approved = eit.appr_reqd_ind AND
eit.intermission_type = sci.intermission_type )
)
OR
( sca.course_attempt_status = 'INACTIVE'
AND EXISTS ( SELECT 'X'
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = sca.person_id
AND course_cd = sca.course_cd
)
)
);
SELECT sca.person_id person_id ,(select person_last_name from hz_parties where party_id = sca.person_id) last_name
FROM IGS_EN_STDNT_PS_ATT_ALL sca,
igs_ps_ver_all pv,
igs_ps_type_all pt,
IGS_AD_PS_APPL_INST_all acai,
IGS_AD_APPL_all aav,
IGS_CA_INST_REL cir
WHERE pv.course_cd = sca.course_cd AND
pv.version_number = sca.version_number AND
sca.course_attempt_status IN ('INACTIVE','UNCONFIRM') AND
pv.course_type = pt.course_type AND
pt.course_type_group_cd = p_prg_type_gr_cd AND
acai.person_id = sca.person_id AND
sca.course_cd = acai.course_cd AND
sca.version_number = acai.crv_version_number AND
aav.person_id = acai.person_id AND
aav.admission_appl_number = acai.admission_appl_number AND
aav.adm_cal_type = cir.SUB_CAL_TYPE AND
aav.adm_ci_sequence_number = cir.SUB_CI_SEQUENCE_NUMBER AND
((cir.sup_cal_type = cp_cal_type AND
cir.sup_ci_sequence_number = cp_seq_num) OR
(
(cir.sup_cal_type,cir.sup_ci_sequence_number) IN
( SELECT teach_cal_type,teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = cp_cal_type
AND load_ci_sequence_number = cp_seq_num
)
)
)
UNION
SELECT sca2.person_id person_id ,(select person_last_name from hz_parties where party_id = sca2.person_id) last_name
FROM igs_ps_ver_all pv2,
igs_ps_type_all pt2,
IGS_EN_STDNT_PS_ATT_ALL sca2
WHERE pt2.course_type_group_cd = p_prg_type_gr_cd AND
pv2.course_type = pt2.course_type AND
pv2.course_cd = sca2.course_cd AND
pv2.version_number = sca2.version_number AND
sca2.course_attempt_status = 'INACTIVE' AND
NOT EXISTS
(SELECT 'x' FROM igs_en_su_attempt_all sua
WHERE sua.person_id = sca2.person_id AND
sua.course_cd = sca2.course_cd AND
sua.course_cd = pv2.course_cd) AND
igs_en_timeslots.acad_teach_rel_exist(sca2.cal_type,cp_cal_type,cp_seq_num) = 'TRUE' ;
SELECT start_dt
FROM igs_ca_inst
WHERE cal_type = p_cal_type AND
sequence_number=p_seq_num;