[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rnirwani 13-Sep-2004 changed cursor c_intmsn_details to not consider logically deleted records and
also to avoid un-approved intermission records. Bug# 3885804
*******************************************************************************/
PROCEDURE resp_get_ca_exists(
p_person_id IN NUMBER ,
p_ca_sequence_number IN NUMBER ,
p_effective_dt IN DATE ,
p_check_thesis IN BOOLEAN ,
p_check_field_of_study IN BOOLEAN ,
p_check_seo_class_cd IN BOOLEAN ,
p_check_supervisor IN BOOLEAN ,
p_check_milestone IN BOOLEAN ,
p_check_scholarship IN BOOLEAN ,
p_thesis_exists OUT NOCOPY BOOLEAN ,
p_field_of_study_exists OUT NOCOPY BOOLEAN ,
p_seo_class_cd_exists OUT NOCOPY BOOLEAN ,
p_supervisor_exists OUT NOCOPY BOOLEAN ,
p_milestone_exists OUT NOCOPY BOOLEAN ,
p_scholarship_exists OUT NOCOPY BOOLEAN )
AS
BEGIN -- resp_get_ca_exists
-- Description: This module returns output parameters indicating whether
-- or not data exists on IGS_RE_CANDIDATURE detail tables for the specified
-- IGS_RE_CANDIDATURE.person_id /sequence_number.
DECLARE
v_the_dummy_rec VARCHAR2(1);
SELECT 'X'
FROM IGS_RE_THESIS the
WHERE the.person_id = p_person_id AND
the.ca_sequence_number = p_ca_sequence_number AND
the.logical_delete_dt IS NULL;
SELECT 'X'
FROM IGS_RE_CDT_FLD_OF_SY cafos
WHERE cafos.person_id = p_person_id AND
cafos.ca_sequence_number = p_ca_sequence_number;
SELECT 'X'
FROM IGS_RE_CAND_SEO_CLS csc
WHERE csc.person_id = p_person_id AND
csc.ca_sequence_number = p_ca_sequence_number;
SELECT 'X'
FROM IGS_RE_SPRVSR rsup
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = p_ca_sequence_number AND
rsup.start_dt <= p_effective_dt AND
NVL(rsup.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) >= p_effective_dt;
SELECT 'X'
FROM IGS_PR_MILESTONE mil
WHERE mil.person_id = p_person_id AND
mil.ca_sequence_number = p_ca_sequence_number;
SELECT 'X'
FROM IGS_RE_SCHOLARSHIP sch
WHERE sch.person_id = p_person_id AND
sch.ca_sequence_number = p_ca_sequence_number AND
sch.start_dt <= p_effective_dt AND
NVL(sch.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) >= p_effective_dt;
SELECT ca.sca_course_cd,
ca.acai_admission_appl_number,
ca.acai_nominated_course_cd,
ca.acai_sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.sequence_number = p_ca_sequence_number;
SELECT sca.course_attempt_status,
sca.version_number,
sca.commencement_dt
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = cp_course_cd;
SELECT acai.course_cd,
acai.crv_version_number,
acai.adm_outcome_status,
acai.prpsd_commencement_dt
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = cp_adm_appl_number AND
acai.nominated_course_cd = cp_nom_course_cd AND
acai.sequence_number = cp_sequence_number;
SELECT aa.s_admission_process_type
FROM IGS_AD_APPL aa,
IGS_AD_PRCS_CAT_STEP apcs
WHERE aa.person_id = p_person_id AND
aa.admission_appl_number = cp_adm_appl_num AND
aa.admission_cat = apcs.admission_cat AND
aa.s_admission_process_type =
apcs.s_admission_process_type AND
apcs.s_admission_step_type = 'RESEARCH' AND
apcs.mandatory_step_ind = 'Y' AND
apcs.step_group_type <> 'TRACK'; --2402377
SELECT cty.research_type_ind
FROM IGS_PS_VER crv,
IGS_PS_TYPE cty
WHERE crv.course_cd = p_course_cd AND
crv.version_number = p_version_number AND
crv.course_type = cty.course_type;
SELECT sua.cal_type,
sua.ci_sequence_number
FROM IGS_EN_SU_ATTEMPT sua,
IGS_PS_UNIT_VER uv
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_cd = uv.unit_cd AND
sua.version_number = uv.version_number AND
uv.research_unit_ind = 'Y' AND
sua.unit_attempt_status IN (
'ENROLLED',
'COMPLETED',
'DISCONTIN');
SELECT sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_RE_CANDIDATURE ca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
ca.person_id = sca.person_id AND
ca.sca_course_cd = sca.course_cd;
SELECT uv.research_unit_ind
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = p_unit_cd AND
uv.version_number = p_unit_version_number;
SELECT ca.sequence_number,
ca.attendance_percentage,
ca.sca_course_cd,
ca.acai_admission_appl_number,
ca.acai_nominated_course_cd,
ca.acai_sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.sca_course_cd = p_course_cd;
SELECT sca.attendance_type,
sca.commencement_dt
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT cah.hist_start_dt,
cah.hist_end_dt,
cah.attendance_percentage
FROM IGS_RE_CDT_ATT_HIST cah
WHERE cah.person_id = p_person_id AND
cah.ca_sequence_number = v_ca_sequence_number AND
cah.hist_end_dt >= cp_effective_start_dt AND
cah.hist_start_dt <= p_effective_dt
ORDER BY cah.hist_start_dt;
SELECT sci.start_dt,
sci.end_dt
FROM IGS_EN_STDNT_PS_INTM sci,
IGS_EN_INTM_TYPES eit
WHERE sci.person_id = p_person_id AND
sci.course_cd = p_course_cd AND
sci.start_dt <= p_effective_dt AND
sci.end_dt >= cp_effective_start_dt AND
sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
sci.approved = eit.appr_reqd_ind AND
eit.intermission_type = sci.intermission_type
ORDER BY sci.start_dt ASC;
SELECT cah.hist_start_dt,
cah.hist_end_dt,
cah.attendance_percentage
FROM IGS_RE_CDT_ATT_HIST cah
WHERE cah.person_id = p_person_id AND
cah.ca_sequence_number = cp_ca_sequence_number AND
cah.hist_start_dt <= cp_intermit_end_dt AND
cah.hist_start_dt <= p_effective_dt AND
cah.hist_end_dt >= cp_intermit_start_dt
ORDER BY cah.hist_start_dt;
SELECT srcc.effective_strt_dt_alias,
srcc.effective_end_dt_alias
FROM IGS_RE_S_RES_CAL_CON srcc
WHERE srcc.s_control_num = 1;
SELECT NVL(
dai.absolute_val,
IGS_CA_GEN_001.calp_get_alias_val(
dai.dt_alias,
dai.sequence_number,
dai.cal_type,
dai.ci_sequence_number)) AS v_absolute_val
FROM IGS_CA_DA_INST dai
WHERE dai.cal_type = p_cal_type AND
dai.ci_sequence_number = p_ci_sequence_number AND
dai.dt_alias = p_effective_dt_alias;
-- Function selects a date from the teaching period, if zero or multiple
-- values exist it returns null
FOR v_dai_rec IN c_dai LOOP
IF c_dai%ROWCOUNT = 1 THEN
v_alias_val := v_dai_rec.v_absolute_val;
-- Select the start date from the teaching period
v_start_alias_val := resp_get_alias_val(
p_cal_type,
p_ci_sequence_number,
v_effective_strt_dt_alias);
-- Select the end date from the teaching period
v_end_alias_val := resp_get_alias_val(
p_cal_type,
p_ci_sequence_number,
v_effective_end_dt_alias);
SELECT 'x'
FROM IGS_RE_THESIS_EXAM tex
WHERE tex.person_id = p_person_id AND
tex.ca_sequence_number = p_ca_sequence_number AND
tex.the_sequence_number = p_the_sequence_number;
SELECT 'x'
FROM IGS_PR_MILESTONE mil
WHERE mil.person_id = p_person_id AND
mil.ca_sequence_number = p_ca_sequence_number;
p_logical_delete_dt IN DATE ,
p_thesis_result_cd IN VARCHAR2 )
RETURN VARCHAR2 AS
BEGIN -- resp_get_the_status
-- Get the IGS_RE_THESIS status.
-- Values are:
-- PENDING - IGS_RE_THESIS detail has been keyed, but has not yet been submitted
-- SUBMITTED - IGS_RE_THESIS has been submitted and is being processed in some way
-- EXAMINED - IGS_RE_THESIS has been submitted and examined and a final outcome
-- entered.
-- DELETED - IGS_RE_THESIS has been logically deleted and no longer applies to the
-- research
DECLARE
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
SELECT thes.logical_delete_dt,
thes.thesis_result_cd
FROM IGS_RE_THESIS thes
WHERE thes.person_id = p_person_id AND
thes.ca_sequence_number = p_ca_sequence_number AND
thes.sequence_number = p_the_sequence_number;
SELECT 'x'
FROM IGS_RE_THESIS_EXAM tex
WHERE person_id = p_person_id AND
ca_sequence_number = p_ca_sequence_number AND
the_sequence_number = p_the_sequence_number AND
submission_dt IS NOT NULL;
--Select details from the IGS_RE_THESIS table.
OPEN c_the;
v_the_rec.logical_delete_dt := p_logical_delete_dt;
IF v_the_rec.logical_delete_dt IS NOT NULL THEN
RETURN cst_deleted;
--Select detail from the IGS_RE_THESIS examinations section
OPEN c_tex;
-- This modules inserts into IGS_RE_CDT_ATT_HIST when
-- IGS_RE_CANDIDATURE.attendance_percentage is changed. The following is validated:
-- IGS_RE_CANDIDATURE requires attendance history details to be retained.
DECLARE
v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
SELECT cah.hist_end_dt
FROM IGS_RE_CDT_ATT_HIST cah
WHERE cah.person_id = p_person_id AND
cah.ca_sequence_number = p_ca_sequence_number
ORDER BY cah.hist_end_dt DESC;
SELECT NVL(max(cah2.sequence_number),0)+1
FROM IGS_RE_CDT_ATT_HIST cah2
WHERE cah2.person_id = p_person_id AND
cah2.ca_sequence_number = p_ca_sequence_number;
--First history inserted, attendance % should be set the student
-- to the histury table as it is a mandatory column in IGS_RE_CDT_ATT_HIST table
IF v_attendance_percentage IS NULL THEN
p_message_name := 'IGS_RE_ATT_PER_NOT_EXIST';
--First history inserted, start date should be set the student
-- IGS_PS_COURSE attempt Commencement date
IF v_commencement_dt IS NULL THEN
p_message_name := 'IGS_RE_FIRST_HIST_CANT_INSERT';
IGS_RE_CDT_ATT_HIST_PKG.INSERT_ROW( X_ROWID => LV_ROWID,
X_PERSON_ID => p_person_id,
X_CA_SEQUENCE_NUMBER => p_ca_sequence_number,
X_SEQUENCE_NUMBER => v_sequence_number,
X_HIST_START_DT => v_hist_start_dt,
X_HIST_END_DT => TRUNC(SYSDATE) - 1,
X_ATTENDANCE_TYPE => v_attendance_type,
X_ATTENDANCE_PERCENTAGE => v_attendance_percentage,
X_ORG_ID => v_org_id,
X_MODE => 'R');
-- Warn that IGS_RE_CANDIDATURE attendance history has been inserted
p_message_name := 'IGS_RE_CAND_ATT_HIST_INSERTED';
p_old_update_who IN NUMBER ,
p_new_update_who IN NUMBER ,
p_old_update_on IN DATE ,
p_new_update_on IN DATE )
AS
LV_ROWID VARCHAR2(25);
-- Description: Insert IGS_RE_CANDIDATURE history (IGS_RE_CDT_HIST)
v_org_id := IGS_GE_GEN_003.Get_Org_Id;
r_ch.hist_start_dt := p_old_update_on;
r_ch.hist_end_dt := p_new_update_on;
r_ch.hist_who := p_old_update_who;
IGS_RE_CDT_HIST_PKG.INSERT_ROW(
X_ROWID => LV_ROWID,
X_person_id => r_ch.person_id,
X_sequence_number => r_ch.sequence_number,
X_hist_start_dt => r_ch.hist_start_dt,
X_hist_end_dt => r_ch.hist_end_dt,
X_hist_who => r_ch.hist_who,
X_sca_course_cd => r_ch.sca_course_cd,
X_acai_admission_appl_number => r_ch.acai_admission_appl_number,
X_acai_nominated_course_cd => r_ch.acai_nominated_course_cd,
X_acai_sequence_number => r_ch.acai_sequence_number,
X_attendance_percentage => r_ch.attendance_percentage,
X_govt_type_of_activity_cd => r_ch.govt_type_of_activity_cd,
X_max_submission_dt => r_ch.max_submission_dt,
X_min_submission_dt => r_ch.min_submission_dt,
X_research_topic => r_ch.research_topic,
X_industry_links => r_ch.industry_links,
X_ORG_ID => v_org_id,
X_MODE => 'R');
vkarthik 26-Apr-2004 Removed the condition that inserts records from milestone
set only if due date is in the present or future for
EN303 Milestone build Enh#3577974
*******************************************************************************/
LV_ROWID VARCHAR2(25);
-- Insert default milestones against a IGS_RE_CANDIDATURE based on their
-- IGS_PS_COURSE version.
v_org_id := IGS_GE_GEN_003.Get_Org_Id;
v_records_inserted NUMBER;
SELECT 'x'
FROM IGS_PR_MILESTONE mil
WHERE mil.person_id = p_person_id AND
mil.ca_sequence_number = p_ca_sequence_number AND
mil.milestone_type = cp_milestone_type AND
mil.due_dt = cp_due_dt;
SELECT ca.sca_course_cd,
ca.acai_admission_appl_number,
ca.acai_nominated_course_cd,
ca.acai_sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.sequence_number = p_ca_sequence_number;
SELECT sca.version_number,
sca.attendance_type
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = cp_sca_course_cd;
SELECT acai.course_cd,
acai.crv_version_number,
acai.attendance_type
FROM IGS_AD_PS_APPL_INST acai
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = cp_acai_admission_appl_number AND
acai.nominated_course_cd = cp_acai_nominated_course_cd AND
acai.sequence_number = cp_acai_sequence_number;
SELECT mst.milestone_status
FROM IGS_PR_MS_STAT mst
WHERE mst.s_milestone_status = cst_planned
AND mst.closed_ind = 'N'
ORDER BY mst.milestone_status;
SELECT dms.milestone_type,
dms.offset_days
FROM IGS_RE_DFLT_MS_SET dms,
IGS_PR_MILESTONE_TYP mst
WHERE dms.course_cd = cp_course_cd AND
dms.version_number = cp_crv_version_number AND
dms.attendance_type = cp_attendance_type AND
mst.milestone_type = dms.milestone_type AND
mst.closed_ind = 'N'
ORDER BY offset_days;
SELECT IGS_PR_MILESTONE_SEQ_NUM_S.NEXTVAL
FROM DUAL;
v_records_inserted := 0;
-- 4. Select the IGS_PS_COURSE version number and attendance type
-- from the appropriate source.
IF v_sca_course_cd IS NOT NULL THEN
OPEN c_sca (
v_sca_course_cd);
-- 5. Select the planned IGS_PR_MILESTONE status (pick the first)
OPEN c_mst_planned;
IGS_PR_MILESTONE_PKG.INSERT_ROW(
X_ROWID => LV_ROWID,
X_PERSON_ID => p_person_id,
X_ca_sequence_number => p_ca_sequence_number,
X_sequence_number => v_mil_sequence_number,
X_milestone_type => v_milestone_type,
X_milestone_status => v_milestone_status,
X_due_dt => v_commencement_dt + v_offset_days,
X_DESCRIPTION => NULL,
X_ACTUAL_REACHED_DT => NULL,
X_PRECED_SEQUENCE_NUMBER => NULL,
X_OVRD_NTFCTN_IMMINENT_DAYS => NULL,
X_OVRD_NTFCTN_REMINDER_DAYS => NULL,
X_OVRD_NTFCTN_RE_REMINDER_DAYS => NULL,
X_COMMENTS => NULL,
X_ORG_ID => v_org_id,
X_MODE => 'R');
v_records_inserted := v_records_inserted + 1;
IF v_records_inserted = 0 THEN
-- If no records found
p_message_name := 'IGS_RE_NO_DFLT_MILSTN_EXIST';