[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT spat.term_record_id
FROM IGS_EN_SPA_TERMS spat, igs_ps_ver cv1
WHERE spat.person_id = cp_person_id
AND spat.term_cal_type = cp_term_cal_type
AND spat.term_sequence_number = cp_term_sequence_number
AND cv1.course_cd = spat.program_cd
AND cv1.version_number = spat.program_version
AND
( (
NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y'
AND cv1.course_type = (SELECT cv2.course_type
FROM IGS_PS_VER cv2
WHERE cv2.course_cd = cp_program_cd
AND cv2.version_number = cp_program_version)
)
OR
( NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N'
AND spat.program_cd = cp_program_cd
)
);
SELECT 'x'
FROM igs_ca_inst_rel
WHERE sub_cal_type = p_term_rec.term_cal_type
AND sub_ci_sequence_number = p_term_rec.term_sequence_number
AND sup_cal_type = p_term_rec.acad_cal_type;
SELECT 'x'
FROM igs_ps_ofr_opt
WHERE coo_id = p_term_rec.coo_id
AND location_cd = p_term_rec.location_cd
AND version_number = p_term_rec.program_version
AND attendance_type = p_term_rec.attendance_type
AND attendance_mode = p_term_rec.attendance_mode
AND cal_type = p_term_rec.acad_cal_type
AND course_cd = p_term_rec.program_cd;
select program_cd from igs_en_spa_terms where person_id = p_person_id
and key_program_flag = 'Y'
and term_cal_type = p_term_cal_type
and term_sequence_number = p_term_sequence_number;
SELECT program_cd, acad_cal_type
FROM igs_en_spa_terms spat, igs_ca_inst ca1
WHERE person_id = p_person_id
AND spat.term_cal_type = ca1.cal_type
AND spat.term_sequence_number = ca1.sequence_number
AND spat.key_program_flag = 'Y'
ORDER BY ca1.start_dt ASC;
select program_cd, acad_cal_type from igs_en_spa_terms spat, igs_ca_inst ca1, igs_ca_inst ca2
where key_program_flag = 'Y'
and person_id = p_person_id
and ca1.cal_type = term_cal_type
and ca1.sequence_number = term_sequence_number
and ca2.cal_type = p_term_cal_type
and ca2.sequence_number = p_term_sequence_number
and ca1.start_dt < ca2.start_dt order by ca1.start_dt desc;
select course_cd, cal_type from igs_en_stdnt_ps_att where person_id = p_person_id and key_program ='Y';
SELECT 'x'
FROM igs_ca_inst_rel
WHERE sub_cal_type = p_term_cal_type
and sub_ci_sequence_number = p_term_sequence_number
and sup_cal_type = cp_acad_cal_type;
SELECT * from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.*
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = P_TERM_CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = P_TERM_SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT person_id,
course_cd,
version_number,
cal_type,
key_program,
location_cd,
attendance_mode,
attendance_type,
fee_cat,
coo_id,
IGS_PR_CLASS_STD_ID
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT version_number,
location_cd,
attendance_type,
attendance_mode,
cal_type
FROM igs_ps_ofr_opt
WHERE coo_id = p_coo_id;
create_update_term_rec( -- can it be check_and_create
p_person_id => p_person_id,
p_program_cd => l_key_program,
p_term_cal_type => p_term_cal_type,
p_term_sequence_number => p_term_sequence_number,
p_ripple_frwrd => FALSE,
p_update_rec => FALSE,
p_message_name => l_message_name);
p_insert_rec OUT NOCOPY BOOLEAN,
p_term_record_id OUT NOCOPY NUMBER) AS
/* -----------------------------------------------------------------------
Created By : Susmitha Tutta
Date Created By : 16-Mar-2004
Purpose : Checks whether a term record exists and returns
term_record_id and p_insert_rec = FALSE if exists.
If term doesn't exist returns p_insert_rec = TRUE.
Change History
Who When What
----------------------------------------------------------------------*/
vc_career_model_enabled VARCHAR2(1);
SELECT spat.term_record_id
FROM IGS_EN_SPA_TERMS spat, igs_ps_ver cv1
WHERE spat.person_id = cp_person_id
AND spat.term_cal_type = cp_term_cal_type
AND spat.term_sequence_number = cp_term_sequence_number
AND cv1.course_cd = spat.program_cd
AND cv1.version_number = spat.program_version
AND
( (
NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y'
AND cv1.course_type = (SELECT cv2.course_type
FROM IGS_PS_VER cv2
WHERE cv2.course_cd = cp_program_cd
AND cv2.version_number = cp_program_version)
)
OR
( NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N'
AND spat.program_cd = cp_program_cd
)
);
p_insert_rec := TRUE;
p_insert_rec := FALSE;
p_update_rec IN BOOLEAN DEFAULT FALSE,
p_program_changed IN BOOLEAN DEFAULT FALSE
) AS
vc_row_id VARCHAR2(25);
v_insert_rec BOOLEAN;
SELECT spat.rowid, spat.program_cd, spat.acad_cal_type
FROM IGS_EN_SPA_TERMS spat
WHERE spat.term_record_id = cp_term_record_id;
SELECT person_id
FROM IGS_EN_PLAN_UNITS
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_term_cal_type
AND term_ci_sequence_number = p_term_ci_sequence
AND cart_error_flag = 'N';
v_insert_rec := TRUE;
v_insert_rec := FALSE;
IF v_insert_rec=TRUE THEN
-- call table handler to insert new term record details
l_term_record_id := NULL;
IGS_EN_SPA_TERMS_PKG.insert_row(
x_rowid => l_rowid,
x_term_record_id => l_term_record_id,
x_person_id => p_term_rec.person_id,
x_program_cd => p_term_rec.program_cd,
x_program_version => p_term_rec.program_version,
x_acad_cal_type => p_term_rec.acad_cal_type,
x_term_cal_type => p_term_rec.term_cal_type,
x_term_sequence_number => p_term_rec.term_sequence_number,
x_key_program_flag => p_term_rec.key_program_flag,
x_location_cd => p_term_rec.location_cd,
x_attendance_mode => p_term_rec.attendance_mode,
x_attendance_type => p_term_rec.attendance_type,
x_fee_cat => p_term_rec.fee_cat,
x_coo_id => p_term_rec.coo_id,
x_class_standing_id => p_term_rec.class_standing_id,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10 => null,
x_attribute11 => null,
x_attribute12 => null,
x_attribute13 => null,
x_attribute14 => null,
x_attribute15 => null,
x_attribute16 => null,
x_attribute17 => null,
x_attribute18 => null,
x_attribute19 => null,
x_attribute20 => null,
x_mode => 'R',
x_plan_sht_status => NVL(p_term_rec.plan_sht_status, 'NONE')
);
ELSIF p_update_rec=TRUE THEN
-- in career mode, check if the primary program is changing,
-- in case the primary program is chaning then change the program code
-- and other related parameters as well in the term records.
-- If the primary program is not changing in the current updated
-- then do not ripple forward the changes to other programs in the
-- same career.
-- After the program transfer build, the only place from where
-- the primary program can be switched in the program transfer
-- page. Hence we check if the call to the term API was initialized
-- from the page/program transfer API. in that case the program
-- code would be rippled forward otherwise it wont.
-- To identify if the call has be initialized from the program transfer
-- the logic would use a global variable.
OPEN cur_spat(l_term_record_id);
IGS_EN_SPA_TERMS_PKG.update_row(
x_rowid => vc_cur_spat_rec.rowid,
x_term_record_id => p_term_rec.term_record_id,
x_person_id => p_term_rec.person_id,
x_program_cd => p_term_rec.program_cd,
x_program_version => p_term_rec.program_version,
x_acad_cal_type => p_term_rec.acad_cal_type,
x_term_cal_type => p_term_rec.term_cal_type,
x_term_sequence_number => p_term_rec.term_sequence_number,
x_key_program_flag => p_term_rec.key_program_flag,
x_location_cd => p_term_rec.location_cd,
x_attendance_mode => p_term_rec.attendance_mode,
x_attendance_type => p_term_rec.attendance_type,
x_fee_cat => p_term_rec.fee_cat,
x_coo_id => p_term_rec.coo_id,
x_class_standing_id => p_term_rec.class_standing_id,
x_attribute_category => p_term_rec.attribute_category,
x_attribute1 => p_term_rec.attribute1,
x_attribute2 => p_term_rec.attribute2,
x_attribute3 => p_term_rec.attribute3,
x_attribute4 => p_term_rec.attribute4,
x_attribute5 => p_term_rec.attribute5,
x_attribute6 => p_term_rec.attribute6,
x_attribute7 => p_term_rec.attribute7,
x_attribute8 => p_term_rec.attribute8,
x_attribute9 => p_term_rec.attribute9,
x_attribute10 => p_term_rec.attribute10,
x_attribute11 => p_term_rec.attribute11,
x_attribute12 => p_term_rec.attribute12,
x_attribute13 => p_term_rec.attribute13,
x_attribute14 => p_term_rec.attribute14,
x_attribute15 => p_term_rec.attribute15,
x_attribute16 => p_term_rec.attribute16,
x_attribute17 => p_term_rec.attribute17,
x_attribute18 => p_term_rec.attribute18,
x_attribute19 => p_term_rec.attribute19,
x_attribute20 => p_term_rec.attribute20,
x_mode => 'R',
x_plan_sht_status => l_plan_sht_status
);
SELECT spat.*
FROM IGS_EN_SPA_TERMS spat,
IGS_CA_INST_REL cr,
IGS_CA_INST_REL cr2,
IGS_CA_INST ci,
IGS_PS_VER cv
WHERE cr.sup_cal_type = cp_acad_cal_type
AND cr.sub_cal_type = p_term_cal_type
AND cr.sub_ci_sequence_number = p_term_sequence_number
AND cr.sup_cal_type = cr2.sup_cal_type
AND cr2.sub_cal_type = spat.term_cal_type
AND cr2.sub_ci_sequence_number = spat.term_sequence_number
AND spat.person_id = p_person_id
AND ci.cal_type = cr2.sub_cal_type
AND ci.sequence_number = cr2.sub_ci_sequence_number
AND exists (SELECT 'x'
FROM IGS_CA_INST cii
WHERE cal_type = p_term_cal_type
AND sequence_number = p_term_sequence_number
AND ci.start_dt >= cii.start_dt)
AND ci.sequence_number <> p_term_sequence_number
AND cv.course_cd = spat.program_cd
AND cv.version_number = spat.program_version
AND (
(
cp_chk_othr_prms = 'Y' AND
cv.course_type = (SELECT course_type
FROM IGS_PS_VER cv2
WHERE cv2.course_cd = p_program_cd
AND cv2.version_number = cp_program_version)
)
OR
( cp_chk_othr_prms <> 'Y' AND spat.program_cd = p_program_cd));
p_update_rec => TRUE,
p_program_changed => p_program_changed);
SELECT ci.cal_type, ci.sequence_number, ci.start_dt,
ci.alternate_code, ci.description
FROM igs_ca_inst ci,
igs_ca_inst endterm,
igs_ca_inst beginterm,
igs_ca_type ct,
igs_ca_stat cs,
igs_ca_inst_rel cr,
igs_ca_inst_rel cr2,
igs_ca_type ct2
WHERE ci.cal_type = cr.sub_cal_type
AND ci.sequence_number = cr.sub_ci_sequence_number
AND cr.sup_cal_type = ct2.cal_type
AND ct2.s_cal_cat = 'ACADEMIC'
AND cr.sup_cal_type = cr2.sup_cal_type
AND beginterm.cal_type = cr2.sub_cal_type
AND beginterm.sequence_number = cr2.sub_ci_sequence_number
AND ci.start_dt >= beginterm.start_dt
AND ci.start_dt < endterm.start_dt
AND endterm.cal_type = cp_next_cal_type
AND endterm.sequence_number = cp_next_sequence_number
AND beginterm.cal_type = cp_context_cal_type
AND beginterm.sequence_number = cp_context_sequence_number
AND ci.sequence_number <> endterm.sequence_number
AND ci.sequence_number <> beginterm.sequence_number
AND ct.cal_type = ci.cal_type
AND ct.s_cal_cat = 'LOAD'
AND cs.cal_status = ci.cal_status
AND cs.s_cal_status = 'ACTIVE'
ORDER BY ci.start_dt ASC;
v_backward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
CI2.SEQUENCE_NUMBER,
CI.CAL_TYPE,
CI.SEQUENCE_NUMBER,
SPT.PERSON_ID,
SPT.PROGRAM_CD,
SPT.PROGRAM_VERSION,
SPT.COO_ID,
SPT.ACAD_CAL_TYPE,
SPT.KEY_PROGRAM_FLAG,
SPT.LOCATION_CD,
SPT.ATTENDANCE_MODE,
SPT.ATTENDANCE_TYPE,
SPT.FEE_CAT,
SPT.CLASS_STANDING_ID
FROM IGS_EN_SPA_TERMS SPT,
IGS_PS_VER CV,
IGS_CA_INST CI,
IGS_CA_INST CI2
WHERE SPT.PERSON_ID = :1
AND SPT.PROGRAM_CD = CV.COURSE_CD
AND SPT.PROGRAM_VERSION = CV.VERSION_NUMBER
AND CV.COURSE_TYPE IN (SELECT CV2.COURSE_TYPE
FROM IGS_PS_VER CV2
WHERE CV2.COURSE_CD = :2
AND CV2.VERSION_NUMBER = :3)
AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
AND CI.CAL_TYPE = :4
AND CI.SEQUENCE_NUMBER = :5
AND SPT.ACAD_CAL_TYPE = :6
AND CI2.START_DT < CI.START_DT
AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
ORDER BY CI2.START_DT DESC';
v_backward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
CI2.SEQUENCE_NUMBER,
CI.CAL_TYPE,
CI.SEQUENCE_NUMBER,
SPT.PERSON_ID,
SPT.PROGRAM_CD,
SPT.PROGRAM_VERSION,
SPT.COO_ID,
SPT.ACAD_CAL_TYPE,
SPT.KEY_PROGRAM_FLAG,
SPT.LOCATION_CD,
SPT.ATTENDANCE_MODE,
SPT.ATTENDANCE_TYPE,
SPT.FEE_CAT,
SPT.CLASS_STANDING_ID
FROM IGS_EN_SPA_TERMS SPT,
IGS_CA_INST CI,
IGS_CA_INST CI2
WHERE SPT.PERSON_ID = :1
AND SPT.PROGRAM_CD = :2
AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
AND CI.CAL_TYPE = :3
AND CI.SEQUENCE_NUMBER = :4
AND SPT.ACAD_CAL_TYPE = :6
AND CI2.START_DT < CI.START_DT
AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
ORDER BY CI2.START_DT DESC';
p_update_rec => FALSE);
and updates them if necessary.
Change History
Who When What
stutta 31-Dec-2004 Modified c_forward_gap to pickup only records whose
start date is < the next calendar( not <= next calendar)
This is to avoid the same calendar being consider a
future and past calendar.
----------------------------------------------------------------------*/
TYPE t_ref_cur IS REF CURSOR;
SELECT ci.cal_type, ci.sequence_number,
ci.start_dt, ci.alternate_code, ci.description
FROM IGS_CA_INST ci,
IGS_CA_INST ci2,
IGS_CA_INST ci3,
IGS_CA_TYPE ct,
IGS_CA_STAT cs,
IGS_CA_INST_REL cr,
IGS_CA_INST_REL cr2,
IGS_CA_TYPE ct2
WHERE ci.cal_type = cr.sub_cal_type
AND ci.sequence_number = cr.sub_ci_sequence_number
AND cr.sup_cal_type = ct2.cal_type
AND ct2.s_cal_cat = 'ACADEMIC'
AND cr.sup_cal_type = cr2.sup_cal_type
AND ci2.cal_type = cr2.sub_cal_type
AND ci2.sequence_number = cr2.sub_ci_sequence_number
AND ci.start_dt < ci3.start_dt
AND ci.start_dt >= ci2.start_dt
AND ci2.cal_type = cp_curr_cal_type
AND ci2.sequence_number = cp_curr_sequence_number
AND ci3.cal_type = cp_next_cal_type
AND ci3.sequence_number = cp_next_sequence_number
AND ci.sequence_number <> ci2.sequence_number
AND ci.sequence_number <> ci3.sequence_number
AND ct.cal_type = ci.cal_type
AND ct.s_cal_cat = 'LOAD'
AND cs.cal_status = ci.cal_status
AND cs.s_cal_status = 'ACTIVE'
ORDER BY ci.start_dt ASC;
SELECT *
FROM IGS_EN_SPA_TERMS
WHERE person_id = p_term_rec.person_id
AND program_cd <> cp_program_cd
AND term_cal_type = cp_term_cal_type
AND term_sequence_number = cp_term_sequence_number
AND acad_cal_type = p_term_rec.acad_cal_type
AND key_program_flag = 'Y';
v_forward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
CI2.SEQUENCE_NUMBER,
CI.CAL_TYPE,
CI.SEQUENCE_NUMBER
FROM IGS_EN_SPA_TERMS SPT,
IGS_PS_VER CV,
IGS_CA_INST CI,
IGS_CA_INST CI2
WHERE SPT.PERSON_ID = :1
AND SPT.PROGRAM_CD = CV.COURSE_CD
AND SPT.PROGRAM_VERSION = CV.VERSION_NUMBER
AND CV.COURSE_TYPE IN (SELECT CV2.COURSE_TYPE
FROM IGS_PS_VER CV2
WHERE CV2.COURSE_CD = :2
AND CV2.VERSION_NUMBER = :3)
AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
AND CI.CAL_TYPE = :4
AND CI.SEQUENCE_NUMBER = :5
AND SPT.ACAD_CAL_TYPE = :6
AND CI2.START_DT >= CI.START_DT
AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
ORDER BY CI2.START_DT ASC';
v_forward_gap_exists_stmt := 'SELECT CI2.CAL_TYPE,
CI2.SEQUENCE_NUMBER,
CI.CAL_TYPE,
CI.SEQUENCE_NUMBER
FROM IGS_EN_SPA_TERMS SPT,
IGS_CA_INST CI,
IGS_CA_INST CI2
WHERE SPT.PERSON_ID = :1
AND SPT.PROGRAM_CD = :2
AND CI2.CAL_TYPE = SPT.TERM_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = SPT.TERM_SEQUENCE_NUMBER
AND CI.CAL_TYPE = :3
AND CI.SEQUENCE_NUMBER = :4
AND SPT.ACAD_CAL_TYPE = :5
AND CI2.START_DT >= CI.START_DT
AND CI2.SEQUENCE_NUMBER <> CI.SEQUENCE_NUMBER
ORDER BY CI2.START_DT ASC';
p_update_rec => TRUE);
PROCEDURE create_update_term_rec(
p_person_id IN NUMBER ,
p_program_cd IN VARCHAR2,
p_term_cal_type IN VARCHAR2,
p_term_sequence_NUMBER IN NUMBER,
p_ripple_frwrd IN boolean,
p_update_rec IN BOOLEAN,
p_message_name OUT NOCOPY VARCHAR2,
p_coo_id IN NUMBER DEFAULT -1,
p_key_program_flag IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_fee_cat IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_class_standing_id IN NUMBER DEFAULT -1,
p_plan_sht_status IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_program_changed IN BOOLEAN DEFAULT FALSE
)
AS
cursor c_valid_term IS
SELECT 'x' FROM IGS_CA_INST ca, IGS_CA_TYPE ct
WHERE ca.cal_type = p_term_cal_type
AND ca.sequence_number = p_term_sequence_number
AND ca.cal_type = ct.cal_type
and ct.s_cal_cat = 'LOAD';
l_insert_rec BOOLEAN;
l_insert_rec := TRUE;
l_insert_rec := FALSE;
IF l_insert_rec THEN
backward_gap_fill(new_term_rec);
p_update_rec => TRUE,
p_program_changed => p_program_changed);
p_update_rec => TRUE,
p_program_changed => p_program_changed);
END create_update_term_rec;
SELECT spat.rowid, spat.*
FROM igs_en_spa_terms spat, igs_ca_inst ca1, igs_ca_inst ca2
WHERE ca1.cal_type = spat.term_cal_type
AND ca1.sequence_number = spat.term_sequence_number
AND ca2.cal_type = p_term_cal_type
AND ca2.sequence_number = p_term_sequence_number
AND ca1.start_dt >= ca2.start_dt
and spat.person_id = p_person_id
AND spat.key_program_flag = 'Y';
SELECT spat.rowid, spat.program_cd, term_cal_type, term_sequence_number
FROM igs_en_spa_terms spat, igs_ca_inst ca, igs_ca_inst_rel cir
WHERE spat.person_id = p_person_id
AND ca.cal_type = spat.term_cal_type
AND ca.sequence_number = spat.term_sequence_number
AND cir.sub_cal_type = p_term_cal_type
AND cir.sub_ci_sequence_number = p_term_sequence_number
AND cir.sup_cal_type = spat.acad_cal_type
ORDER BY ca.start_dt DESC;
SELECT spat.rowid, spat.term_record_id
FROM igs_en_spa_terms spat
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT spat.rowid,spat.*
FROM IGS_EN_SPA_TERMS spat,
IGS_CA_INST_REL cr,
IGS_CA_INST_REL cr2,
IGS_CA_INST ci,
IGS_PS_VER cv
WHERE cr.sub_cal_type = p_term_cal_type
AND cr.sub_ci_sequence_number = p_term_sequence_number
AND cr.sup_cal_type = cr2.sup_cal_type
AND cr2.sub_cal_type = spat.term_cal_type
AND cr2.sub_ci_sequence_number = spat.term_sequence_number
AND spat.person_id = p_person_id
AND ci.cal_type = cr2.sub_cal_type
AND ci.sequence_number = cr2.sub_ci_sequence_number
AND ci.start_dt >=
(SELECT start_dt
FROM IGS_CA_INST
WHERE cal_type = cr.sub_cal_type
AND sequence_number = cr.sub_ci_sequence_number)
AND ci.sequence_number <> p_term_sequence_number
AND cv.course_cd = spat.program_cd
AND cv.version_number = spat.program_version
AND spat.program_cd = p_program_cd;
IGS_EN_SPA_TERMS_PKG.update_row(
x_rowid => rec_future_key_terms.rowid,
x_term_record_id => rec_future_key_terms.term_record_id,
x_person_id => rec_future_key_terms.person_id,
x_program_cd => rec_future_key_terms.program_cd,
x_program_version => rec_future_key_terms.program_version,
x_acad_cal_type => rec_future_key_terms.acad_cal_type,
x_term_cal_type => rec_future_key_terms.term_cal_type,
x_term_sequence_number => rec_future_key_terms.term_sequence_number,
x_key_program_flag => 'N',
x_location_cd => rec_future_key_terms.location_cd,
x_attendance_mode => rec_future_key_terms.attendance_mode,
x_attendance_type => rec_future_key_terms.attendance_type,
x_fee_cat => rec_future_key_terms.fee_cat,
x_coo_id => rec_future_key_terms.coo_id,
x_class_standing_id => rec_future_key_terms.class_standing_id,
x_attribute_category => rec_future_key_terms.attribute_category,
x_attribute1 => rec_future_key_terms.attribute1,
x_attribute2 => rec_future_key_terms.attribute2,
x_attribute3 => rec_future_key_terms.attribute3,
x_attribute4 => rec_future_key_terms.attribute4,
x_attribute5 => rec_future_key_terms.attribute5,
x_attribute6 => rec_future_key_terms.attribute6,
x_attribute7 => rec_future_key_terms.attribute7,
x_attribute8 => rec_future_key_terms.attribute8,
x_attribute9 => rec_future_key_terms.attribute9,
x_attribute10 => rec_future_key_terms.attribute10,
x_attribute11 => rec_future_key_terms.attribute11,
x_attribute12 => rec_future_key_terms.attribute12,
x_attribute13 => rec_future_key_terms.attribute13,
x_attribute14 => rec_future_key_terms.attribute14,
x_attribute15 => rec_future_key_terms.attribute15,
x_attribute16 => rec_future_key_terms.attribute16,
x_attribute17 => rec_future_key_terms.attribute17,
x_attribute18 => rec_future_key_terms.attribute18,
x_attribute19 => rec_future_key_terms.attribute19,
x_attribute20 => rec_future_key_terms.attribute20,
x_mode => 'R',
x_plan_sht_status => rec_future_key_terms.plan_sht_status
);
IGS_EN_SPA_TERMS_PKG.update_row(
x_rowid => l_rowid,
x_term_record_id => l_term_id,
x_person_id => l_term_rec.person_id,
x_program_cd => l_term_rec.program_cd,
x_program_version => l_term_rec.program_version,
x_acad_cal_type => l_term_rec.acad_cal_type,
x_term_cal_type => l_term_rec.term_cal_type,
x_term_sequence_number => l_term_rec.term_sequence_number,
x_key_program_flag => 'Y',
x_location_cd => l_term_rec.location_cd,
x_attendance_mode => l_term_rec.attendance_mode,
x_attendance_type => l_term_rec.attendance_type,
x_fee_cat => l_term_rec.fee_cat,
x_coo_id => l_term_rec.coo_id,
x_class_standing_id => l_term_rec.class_standing_id,
x_attribute_category => l_term_rec.attribute_category,
x_attribute1 => l_term_rec.attribute1,
x_attribute2 => l_term_rec.attribute2,
x_attribute3 => l_term_rec.attribute3,
x_attribute4 => l_term_rec.attribute4,
x_attribute5 => l_term_rec.attribute5,
x_attribute6 => l_term_rec.attribute6,
x_attribute7 => l_term_rec.attribute7,
x_attribute8 => l_term_rec.attribute8,
x_attribute9 => l_term_rec.attribute9,
x_attribute10 => l_term_rec.attribute10,
x_attribute11 => l_term_rec.attribute11,
x_attribute12 => l_term_rec.attribute12,
x_attribute13 => l_term_rec.attribute13,
x_attribute14 => l_term_rec.attribute14,
x_attribute15 => l_term_rec.attribute15,
x_attribute16 => l_term_rec.attribute16,
x_attribute17 => l_term_rec.attribute17,
x_attribute18 => l_term_rec.attribute18,
x_attribute19 => l_term_rec.attribute19,
x_attribute20 => l_term_rec.attribute20,
x_mode => 'R',
x_plan_sht_status => NVL(l_term_rec.plan_sht_status,'NONE')
);
IGS_EN_SPA_TERMS_PKG.update_row(
x_rowid => rec_dest_fut_terms.rowid,
x_term_record_id => rec_dest_fut_terms.term_record_id,
x_person_id => rec_dest_fut_terms.person_id,
x_program_cd => rec_dest_fut_terms.program_cd,
x_program_version => rec_dest_fut_terms.program_version,
x_acad_cal_type => rec_dest_fut_terms.acad_cal_type,
x_term_cal_type => rec_dest_fut_terms.term_cal_type,
x_term_sequence_number => rec_dest_fut_terms.term_sequence_number,
x_key_program_flag => 'Y',
x_location_cd => rec_dest_fut_terms.location_cd,
x_attendance_mode => rec_dest_fut_terms.attendance_mode,
x_attendance_type => rec_dest_fut_terms.attendance_type,
x_fee_cat => rec_dest_fut_terms.fee_cat,
x_coo_id => rec_dest_fut_terms.coo_id,
x_class_standing_id => rec_dest_fut_terms.class_standing_id,
x_attribute_category => rec_dest_fut_terms.attribute_category,
x_attribute1 => rec_dest_fut_terms.attribute1,
x_attribute2 => rec_dest_fut_terms.attribute2,
x_attribute3 => rec_dest_fut_terms.attribute3,
x_attribute4 => rec_dest_fut_terms.attribute4,
x_attribute5 => rec_dest_fut_terms.attribute5,
x_attribute6 => rec_dest_fut_terms.attribute6,
x_attribute7 => rec_dest_fut_terms.attribute7,
x_attribute8 => rec_dest_fut_terms.attribute8,
x_attribute9 => rec_dest_fut_terms.attribute9,
x_attribute10 => rec_dest_fut_terms.attribute10,
x_attribute11 => rec_dest_fut_terms.attribute11,
x_attribute12 => rec_dest_fut_terms.attribute12,
x_attribute13 => rec_dest_fut_terms.attribute13,
x_attribute14 => rec_dest_fut_terms.attribute14,
x_attribute15 => rec_dest_fut_terms.attribute15,
x_attribute16 => rec_dest_fut_terms.attribute16,
x_attribute17 => rec_dest_fut_terms.attribute17,
x_attribute18 => rec_dest_fut_terms.attribute18,
x_attribute19 => rec_dest_fut_terms.attribute19,
x_attribute20 => rec_dest_fut_terms.attribute20,
x_mode => 'R',
x_plan_sht_status => rec_dest_fut_terms.plan_sht_status
);
SELECT fee_cat from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.fee_cat
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT fee_cat
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT class_standing_id from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.class_standing_id
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT IGS_PR_CLASS_STD_ID
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT coo_id from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.coo_id
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT coo_id
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT attendance_type from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.attendance_type
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT attendance_type
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT attendance_mode from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.attendance_mode
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT attendance_mode
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT location_cd from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.location_cd
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT location_cd
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
SELECT program_version from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.program_version
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;
PROCEDURE delete_terms_for_program(
p_person_id IN NUMBER,
p_program_cd IN VARCHAR2) AS
l_term_cal_type IGS_CA_INST.cal_type%TYPE;
select spat.rowid, spat.person_id, spat.program_cd, spat.term_cal_type,spat.term_sequence_number, spat.fee_cat
from igs_en_spa_terms spat, igs_ca_inst c1, igs_ca_inst c2
where person_id = p_person_id
and program_cd = p_program_cd
and term_cal_type = c1.cal_type
and term_sequence_number = c1.sequence_number
and cp_term_cal_type = c2.cal_type
and cp_term_seq_num = c2.sequence_number
and c1.start_dt >= c2.start_dt
for update nowait;
igs_en_spa_terms_pkg.delete_row(rec_future_terms.rowid);
SELECT description
FROM igs_en_atd_type
WHERE attendance_type = cp_att_type;
SELECT description
FROM igs_en_atd_mode
WHERE attendance_mode = cp_att_mode;
SELECT description
FROM igs_ad_location
WHERE location_cd = cp_loc;
SELECT 'x'
FROM igs_en_spa_terms spat
WHERE
spat.person_id = p_person_id AND
spat.program_cd = p_program_cd AND
spat.term_cal_type = p_term_cal_type AND
spat.term_sequence_number = p_term_sequence_number;
SELECT ps.course_type
FROM igs_ps_ver ps,
igs_en_stdnt_ps_att spa
WHERE
spa.course_cd = p_program_cd AND
spa.person_id = p_person_id AND
spa.course_cd = ps.course_cd AND
spa.version_number = ps.version_number;
SELECT 'x'
FROM igs_en_spa_terms spat
WHERE spat.person_id = p_person_id AND
p_program_type = (SELECT course_type FROM igs_ps_ver
WHERE course_cd = spat.program_cd
AND version_number = spat.program_version)
AND spat.term_cal_type = p_term_cal_type
AND spat.term_sequence_number = p_term_sequence_number;
SELECT start_dt
FROM IGS_CA_INST
WHERE cal_type = cp_cal_type
AND sequence_number = cp_ci_Sequence_number;
SELECT
SPAT.PROGRAM_CD
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = cp_person_id AND
cp_program_type = (SELECT course_type
FROM igs_ps_ver cv
WHERE spat.program_cd = cv.course_cd
AND spat.program_version = cv.version_number) AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI2.START_DT < cp_start_dt
ORDER BY CI2.START_DT DESC;
SELECT primary_program_type
FROM igs_en_stdnt_ps_att
WHERE
person_id = p_person_id AND
course_cd = p_program_cd;
SELECT DISTINCT term_cal_type, term_sequence_number
FROM IGS_EN_SPA_TERMS
WHERE person_id = p_person_id;
SELECT COUNT(key_program_flag)
FROM IGS_EN_SPA_TERMS
WHERE person_id = p_person_id
AND term_cal_type = cp_term_cal_type
AND term_sequence_number = cp_term_sequence_number
AND key_program_flag = 'Y';
rvangala 17-Feb-2004 Added formatting in select clause to pick
results from cur_c1, Bug #3441941
----------------------------------------------------------------------*/
-- cursor to fetch immediate previous term for current term, for the
-- given academic calendar
CURSOR cur_c1(p_cur_term_cal IN VARCHAR2,
p_cur_term_seq_num IN NUMBER) IS
SELECT RPAD(ci2.sequence_number,6,' ') || ci2.cal_Type
FROM igs_ca_inst ci2,
igs_ca_inst_rel cir,
igs_ca_type ct,
igs_ca_inst ci1,
igs_ca_stat cs
WHERE
ci2.cal_type = cir.sub_cal_type AND
ci2.sequence_number = cir.sub_ci_sequence_number AND
cir.sup_cal_type = p_cal_type AND
ci2.cal_type = ct.cal_type AND
ct.s_cal_cat = 'LOAD' AND
cs.cal_status = ci1.cal_status AND
cs.s_cal_status = 'ACTIVE' AND
ci1.cal_type = p_cur_term_cal AND
ci1.sequence_number = p_cur_term_seq_num AND
ci2.start_dt < ci1.start_dt
ORDER BY ci2.start_dt DESC;
rvangala 17-Feb-2004 Added formatting in select clause to pick
results from cur_c1, Bug #3441941
----------------------------------------------------------------------*/
-- cursor to fetch immediate next term for current term, for the
-- given academic calendar
CURSOR cur_c1(p_cur_term_cal IN VARCHAR2,
p_cur_term_seq_num IN NUMBER) IS
SELECT RPAD(ci2.sequence_number,6,' ') || ci2.cal_type
FROM igs_ca_inst ci2,
igs_ca_inst_rel cir,
igs_ca_type ct,
igs_ca_inst ci1,
igs_ca_stat cs
WHERE
ci2.cal_type = cir.sub_cal_type AND
ci2.sequence_number = cir.sub_ci_sequence_number AND
cir.sup_cal_type = p_cal_type AND
ci2.cal_type = ct.cal_type AND
ct.s_cal_cat = 'LOAD' AND
cs.cal_status = ci1.cal_status AND
cs.s_cal_status = 'ACTIVE' AND
ci1.cal_type = p_cur_term_cal AND
ci1.sequence_number = p_cur_term_seq_num AND
ci2.start_dt > ci1.start_dt
ORDER BY ci2.start_dt;
SELECT acad_cal_type from igs_en_spa_terms
WHERE person_id = p_person_id
AND program_cd = p_program_cd
AND term_cal_type = p_term_cal_type
AND term_sequence_number = p_term_sequence_number;
SELECT
SPAT.acad_cal_type
FROM
IGS_EN_SPA_TERMS SPAT,
IGS_CA_INST CI1,
IGS_CA_INST CI2
WHERE
SPAT.PERSON_ID = p_person_id AND
spat.program_cd = p_program_cd AND
SPAT.TERM_CAL_TYPE = CI2.CAL_TYPE AND
SPAT.TERM_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER AND
CI1.CAL_TYPE = p_term_cal_type AND
CI1.SEQUENCE_NUMBER = p_term_sequence_number AND
CI1.START_DT > CI2.START_DT AND
SPAT.ACAD_CAL_TYPE IN (SELECT SUP_CAL_TYPE
FROM IGS_CA_INST_REL
WHERE SUB_CAL_TYPE = CI1.CAL_TYPE
AND SUB_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER)
ORDER BY CI2.START_DT DESC;
SELECT cal_type
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id
AND course_cd = p_program_cd;