[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT course_type
FROM IGS_PS_VER
WHERE course_cd = c_program_cd AND
version_number = c_program_ver;
FND_MSG_PUB.DELETE_MSG(l_msg_index);
SELECT create_dt
FROM IGS_GR_GRADUAND gua
WHERE gua.person_id = p_person_id
AND gua.course_cd = p_course_cd;
SELECT rowid
FROM IGS_GR_GRADUAND gua
WHERE gua.person_id = p_person_id
AND gua.create_dt = cp_create_dt
FOR UPDATE OF gua.LAST_UPDATE_DATE NOWAIT ;
SELECT gach.gach_id
FROM IGS_GR_AWD_CRMN_HIST gach
WHERE gach.person_id = p_person_id
AND gach.create_dt = cp_create_dt;
SELECT rowid
FROM IGS_GR_AWD_CRMN_HIST gach
WHERE gach.gach_id = cp_gach_id
FOR UPDATE OF gach.LAST_UPDATE_DATE NOWAIT ;
SELECT gac.gac_id
FROM IGS_GR_AWD_CRMN gac
WHERE gac.person_id = p_person_id
AND gac.create_dt = cp_create_dt;
SELECT rowid
FROM IGS_GR_AWD_CRMN gac
WHERE gac.gac_id = cp_gac_id
FOR UPDATE OF gac.LAST_UPDATE_DATE NOWAIT ;
IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW( X_ROWID => c_gach_del_rec.ROWID );
IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
X_ROWID => c_gac_del_rec.ROWID );
IGS_GR_GRADUAND_PKG.DELETE_ROW(
X_ROWID => v_gua_del_exists.rowid );
SELECT award_cd
FROM IGS_EN_SPA_AWD_AIM esaa
WHERE esaa.person_id = p_person_id
AND esaa.course_cd = p_course_cd;
SELECT rowid
FROM IGS_EN_SPA_AWD_AIM esaa
WHERE esaa.person_id = p_person_id
AND esaa.course_cd = p_course_cd
AND esaa.award_cd = cp_award_cd
FOR UPDATE OF esaa.LAST_UPDATE_DATE NOWAIT ;
IGS_EN_SPA_AWD_AIM_PKG.DELETE_ROW(X_ROWID => v_esaa_del_exists.rowid );
SELECT award_cd,award_dt
FROM IGS_GR_SPECIAL_AWARD gsa
WHERE gsa.person_id = p_person_id
AND gsa.course_cd = p_course_cd;
SELECT rowid
FROM IGS_GR_SPECIAL_AWARD gsa
WHERE gsa.person_id = p_person_id
AND gsa.course_cd = p_course_cd
AND gsa.award_cd = cp_award_cd
AND gsa.award_dt = cp_award_dt
FOR UPDATE OF gsa.LAST_UPDATE_DATE NOWAIT ;
IGS_GR_SPECIAL_AWARD_PKG.DELETE_ROW(X_ROWID => v_gsa_del_exists.rowid );
SELECT he_spa_cc_id
FROM IGS_HE_ST_SPA_CC hssc
WHERE hssc.person_id = p_person_id
AND hssc.course_cd = p_course_cd;
SELECT rowid
FROM IGS_HE_ST_SPA_CC hssc
WHERE hssc.he_spa_cc_id = cp_he_spa_cc_id
FOR UPDATE OF hssc.LAST_UPDATE_DATE NOWAIT ;
IGS_HE_ST_SPA_CC_PKG.DELETE_ROW(X_ROWID => v_hssc_del_exists.ROWID );
SELECT hesa_st_spa_id,person_id,course_cd
FROM IGS_HE_ST_SPA hssa
WHERE hssa.person_id = p_person_id
AND hssa.course_cd = p_course_cd;
SELECT rowid
FROM IGS_HE_ST_SPA hssa
WHERE hssa.hesa_st_spa_id = cp_hesa_st_spa_id
FOR UPDATE OF hssa.LAST_UPDATE_DATE NOWAIT ;
SELECT hesa_st_spau_id
FROM IGS_HE_ST_SPA_UT hssua
WHERE hssua.person_id = cp_person_id
AND hssua.course_cd = cp_course_cd ;
SELECT rowid
FROM IGS_HE_ST_SPA_UT hssua
WHERE hssua.hesa_st_spau_id = cp_hesa_st_spau_id
FOR UPDATE OF hssua.LAST_UPDATE_DATE NOWAIT ;
IGS_HE_ST_SPA_UT_ALL_PKG.DELETE_ROW(
X_ROWID => v_hssua_del_rec.ROWID );
IGS_HE_ST_SPA_ALL_PKG.DELETE_ROW(
X_ROWID => v_hssa_del_exists.rowid );
SELECT progression_rule_cat,sequence_number
FROM IGS_PR_RU_APPL pra
WHERE pra.sca_person_id = p_person_id
AND pra.sca_course_cd = p_course_cd;
SELECT rowid,pra.*
FROM IGS_PR_RU_APPL pra
WHERE pra.progression_rule_cat = cp_progression_rule_cat
AND pra.sequence_number = cp_sequence_number
FOR UPDATE OF pra.LAST_UPDATE_DATE NOWAIT ;
IGS_PR_RU_APPL_PKG.DELETE_ROW (X_ROWID => v_pra_upd_exists.rowid) ;
SELECT exit_course_cd,exit_version_number
FROM IGS_PS_STDNT_APV_ALT psaa
WHERE psaa.person_id = p_person_id
AND psaa.course_cd = p_course_cd;
SELECT rowid
FROM IGS_PS_STDNT_APV_ALT psaa
WHERE psaa.person_id = p_person_id
AND psaa.course_cd = p_course_cd
AND psaa.exit_course_cd = cp_exit_course_cd
AND psaa.exit_version_number = cp_exit_version_number
FOR UPDATE OF psaa.LAST_UPDATE_DATE NOWAIT ;
IGS_PS_STDNT_APV_ALT_PKG.DELETE_ROW(X_ROWID => v_psaa_del_exists.rowid);
SELECT susa.unit_set_cd,susa.sequence_number
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id
AND susa.course_cd = p_course_cd
START WITH
susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.parent_unit_set_cd IS NULL
CONNECT BY
PRIOR susa.person_id = p_person_id AND
PRIOR susa.course_cd = p_course_cd AND
PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
PRIOR susa.sequence_number = susa.parent_sequence_number
ORDER BY LEVEL DESC;
SELECT ROWID, susa.*
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 = cp_unit_set_cd
AND susa.sequence_number = cp_sequence_number
FOR UPDATE OF
susa.LAST_UPDATE_DATE NOWAIT;
SELECT hesa_en_susa_id
FROM IGS_HE_EN_SUSA hes
WHERE hes.person_id = p_person_id
AND hes.course_cd = p_course_cd
AND hes.unit_set_cd = cp_unit_set_cd
AND hes.sequence_number = cp_sequence_number;
SELECT rowid
FROM IGS_HE_EN_SUSA hes
WHERE hes.hesa_en_susa_id = cp_hesa_en_susa_id
FOR UPDATE OF hes.LAST_UPDATE_DATE NOWAIT ;
SELECT he_susa_cc_id
FROM IGS_HE_EN_SUSA_CC hesc
WHERE hesc.person_id = p_person_id
AND hesc.course_cd = p_course_cd
AND hesc.unit_set_cd = cp_unit_set_cd
AND hesc.sequence_number = cp_sequence_number;
SELECT rowid
FROM IGS_HE_EN_SUSA_CC hesc
WHERE hesc.he_susa_cc_id = cp_he_susa_cc_id
FOR UPDATE OF hesc.LAST_UPDATE_DATE NOWAIT ;
IGS_GE_S_DSB_TAB_TRG_PKG.INSERT_ROW(
X_ROWID => L_ROWID ,
X_TABLE_NAME =>'ADMP_DEL_SCA_UNCONF',
X_SESSION_ID => userenv('SESSIONID'),
x_mode => 'R'
);
IGS_HE_EN_SUSA_PKG.DELETE_ROW( X_ROWID => v_hes_del_rec.ROWID );
IGS_HE_EN_SUSA_CC_PKG.DELETE_ROW( X_ROWID => v_hesc_del_rec.ROWID );
IGS_AS_SU_SETATMPT_PKG.DELETE_ROW ( X_ROWID => V_SUSA_DEL_EXISTS.ROWID );
SELECT scho.start_dt
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = p_person_id
AND scho.course_cd = p_course_cd;
SELECT ROWID, scho.*
FROM IGS_EN_STDNTPSHECSOP scho
WHERE scho.person_id = p_person_id
AND scho.course_cd = p_course_cd
AND scho.start_dt = cp_start_dt
FOR UPDATE OF
scho.LAST_UPDATE_DATE NOWAIT;
IGS_EN_STDNTPSHECSOP_PKG.DELETE_ROW ( X_ROWID => V_SCHO_DEL_EXISTS.ROWID );
SELECT ROWID, scae.*
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE scae.person_id = p_person_id
AND scae.course_cd = p_course_cd
FOR UPDATE OF scae.LAST_UPDATE_DATE NOWAIT;
IGS_AS_SC_ATMPT_ENR_PKG.DELETE_ROW(v_scae_rec.rowid);
SELECT ROWID, scan.*
FROM IGS_AS_SC_ATMPT_NOTE scan
WHERE scan.person_id = p_person_id
AND scan.course_cd = p_course_cd
FOR UPDATE OF scan.reference_number NOWAIT;
IGS_AS_SC_ATMPT_NOTE_PKG.DELETE_ROW(v_scan_rec.rowid);
SELECT rowid, ca.*
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id
AND ca.sca_course_cd = p_course_cd
FOR UPDATE OF ca.sca_course_cd NOWAIT;
IGS_RE_CANDIDATURE_PKG.DELETE_ROW(X_ROWID => V_CA_REC.ROWID);
IGS_RE_CANDIDATURE_PKG.UPDATE_ROW(
X_ROWID => V_CA_REC.ROWID,
X_PERSON_ID => V_CA_REC.PERSON_ID,
X_SEQUENCE_NUMBER => V_CA_REC.SEQUENCE_NUMBER,
X_SCA_COURSE_CD => NULL,
X_ACAI_ADMISSION_APPL_NUMBER => V_CA_REC.ACAI_ADMISSION_APPL_NUMBER,
X_ACAI_NOMINATED_COURSE_CD => V_CA_REC.ACAI_NOMINATED_COURSE_CD,
X_ACAI_SEQUENCE_NUMBER => V_CA_REC.ACAI_SEQUENCE_NUMBER,
X_ATTENDANCE_PERCENTAGE => V_CA_REC.ATTENDANCE_PERCENTAGE,
X_GOVT_TYPE_OF_ACTIVITY_CD => V_CA_REC.GOVT_TYPE_OF_ACTIVITY_CD,
X_MAX_SUBMISSION_DT => V_CA_REC.MAX_SUBMISSION_DT,
X_MIN_SUBMISSION_DT => V_CA_REC.MIN_SUBMISSION_DT,
X_RESEARCH_TOPIC => V_CA_REC.RESEARCH_TOPIC,
X_INDUSTRY_LINKS => V_CA_REC.INDUSTRY_LINKS
);
SELECT ROWID
FROM IGS_AV_ADV_STANDING av
WHERE av.person_id = p_person_id
AND av.course_cd = p_course_cd
AND av.version_number = p_version_num;
IGS_AV_ADV_STANDING_PKG.DELETE_ROW( c_av_rec.ROWID);
SELECT rowid
FROM IGS_FI_FEE_AS_RT fi_fee
WHERE fi_fee.person_id = p_person_id
AND fi_fee.course_cd = p_course_cd;
IGS_FI_FEE_AS_RT_PKG.DELETE_ROW(v_del_fi_fee.rowid);
SELECT rowid
FROM IGS_EN_STDNT_PS_INTM intm
WHERE intm.person_id = p_person_id
AND intm.course_cd = p_course_cd;
IGS_EN_STDNT_PS_INTM_PKG.DELETE_ROW(v_del_ps_intm.rowid);
SELECT rowid
FROM IGS_PS_STDNT_TRN trnsf
WHERE trnsf.person_id = p_person_id
AND trnsf.course_cd = p_course_cd
AND trnsf.effective_term_cal_type = p_term_cal_type
AND trnsf.effective_term_sequence_num = p_term_sequence_number
AND trnsf.status_flag = 'C';
IGS_PS_STDNT_TRN_PKG.DELETE_ROW(v_del_ps_trnsf.rowid);
SELECT rowid
FROM igs_pr_cohinst_rank pr_cohinst
WHERE pr_cohinst.person_id = p_person_id
AND pr_cohinst.course_cd = p_course_cd;
igs_pr_cohinst_rank_pkg.DELETE_ROW(v_del_pr_cohinst.rowid);
SELECT rowid
FROM igs_as_anon_id_ps as_anon_id_ps
WHERE as_anon_id_ps.person_id = p_person_id
AND as_anon_id_ps.course_cd = p_course_cd;
igs_as_anon_id_ps_pkg.DELETE_ROW(v_del_as_anon_id_ps.rowid);
Select suar.rowid
From IGS_AS_SUA_REF_CDS suar
Where suar.person_id = cp_person_id
And suar.course_cd = cp_course_cd
And suar.uoo_id = cp_uoo_id;
igs_as_sua_ref_cds_pkg.delete_row(v_cur_sua_ref_cds.rowid);
SELECT rowid
FROM igs_as_anon_id_us as_anon_id_us
WHERE as_anon_id_us.person_id = p_person_id
AND as_anon_id_us.course_cd = p_course_cd
AND as_anon_id_us.uoo_id = p_uoo_id;
igs_as_anon_id_us_pkg.DELETE_ROW(v_del_as_anon_id_us.rowid);
SELECT uoo_id
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_dest_course_cd;
SELECT rowid
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_dest_course_cd
AND sua.uoo_id = cp_uoo_id
FOR UPDATE OF sua.LAST_UPDATE_DATE NOWAIT;
IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW( X_ROWID => v_sua_del_exists.rowid );
SELECT uoo_id
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_dest_course_cd;
SELECT rowid,sua.*
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_dest_course_cd
AND sua.uoo_id = cp_uoo_id
AND sua.unit_attempt_status <> cst_dropped
FOR UPDATE OF sua.LAST_UPDATE_DATE NOWAIT;
IGS_EN_SUA_API.update_unit_attempt(
X_ROWID => v_sua_upd_exists.ROWID,
X_PERSON_ID => v_sua_upd_exists.PERSON_ID,
X_COURSE_CD => v_sua_upd_exists.COURSE_CD ,
X_UNIT_CD => v_sua_upd_exists.UNIT_CD,
X_CAL_TYPE => v_sua_upd_exists.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_sua_upd_exists.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => v_sua_upd_exists.VERSION_NUMBER ,
X_LOCATION_CD => v_sua_upd_exists.LOCATION_CD,
X_UNIT_CLASS => v_sua_upd_exists.UNIT_CLASS ,
X_CI_START_DT => v_sua_upd_exists.CI_START_DT,
X_CI_END_DT => v_sua_upd_exists.CI_END_DT,
X_UOO_ID => v_sua_upd_exists.UOO_ID ,
X_ENROLLED_DT => v_sua_upd_exists.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_dropped, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_upd_exists.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => v_sua_upd_exists.administrative_PRIORITY,
X_DISCONTINUED_DT => nvl(v_sua_upd_exists.discontinued_dt,trunc(SYSDATE)),
X_DCNT_REASON_CD => v_sua_upd_exists.DCNT_REASON_CD,
X_RULE_WAIVED_DT => v_sua_upd_exists.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => v_sua_upd_exists.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => v_sua_upd_exists.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => v_sua_upd_exists.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => v_sua_upd_exists.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => v_sua_upd_exists.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => v_sua_upd_exists.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => v_sua_upd_exists.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => v_sua_upd_exists.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_upd_exists.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_upd_exists.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => v_sua_upd_exists.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => v_sua_upd_exists.waitlist_dt,
X_MODE => 'R',
X_GS_VERSION_NUMBER => v_sua_upd_exists.gs_version_number,
X_ENR_METHOD_TYPE => v_sua_upd_exists.enr_method_type,
X_FAILED_UNIT_RULE => v_sua_upd_exists.FAILED_UNIT_RULE,
X_CART => v_sua_upd_exists.CART,
X_RSV_SEAT_EXT_ID => v_sua_upd_exists.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => v_sua_upd_exists.org_unit_cd ,
X_SESSION_ID => v_sua_upd_exists.session_id,
X_GRADING_SCHEMA_CODE => v_sua_upd_exists.grading_schema_code,
X_DEG_AUD_DETAIL_ID => v_sua_upd_exists.deg_aud_detail_id,
X_SUBTITLE => v_sua_upd_exists.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => v_sua_upd_exists.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => v_sua_upd_exists.student_career_statistics,
X_ATTRIBUTE_CATEGORY => v_sua_upd_exists.attribute_category,
X_ATTRIBUTE1 => v_sua_upd_exists.attribute1,
X_ATTRIBUTE2 => v_sua_upd_exists.attribute2,
X_ATTRIBUTE3 => v_sua_upd_exists.attribute3,
X_ATTRIBUTE4 => v_sua_upd_exists.attribute4,
X_ATTRIBUTE5 => v_sua_upd_exists.attribute5,
X_ATTRIBUTE6 => v_sua_upd_exists.attribute6,
X_ATTRIBUTE7 => v_sua_upd_exists.attribute7,
X_ATTRIBUTE8 => v_sua_upd_exists.attribute8,
X_ATTRIBUTE9 => v_sua_upd_exists.attribute9,
X_ATTRIBUTE10 => v_sua_upd_exists.attribute10,
X_ATTRIBUTE11 => v_sua_upd_exists.attribute11,
X_ATTRIBUTE12 => v_sua_upd_exists.attribute12,
X_ATTRIBUTE13 => v_sua_upd_exists.attribute13,
X_ATTRIBUTE14 => v_sua_upd_exists.attribute14,
X_ATTRIBUTE15 => v_sua_upd_exists.attribute15,
X_ATTRIBUTE16 => v_sua_upd_exists.attribute16,
X_ATTRIBUTE17 => v_sua_upd_exists.attribute17,
X_ATTRIBUTE18 => v_sua_upd_exists.attribute18,
X_ATTRIBUTE19 => v_sua_upd_exists.attribute19,
X_ATTRIBUTE20 => v_sua_upd_exists.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_upd_exists.waitlist_manual_ind,
X_WLST_PRIORITY_WEIGHT_NUM => v_sua_upd_exists.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => v_sua_upd_exists.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => v_sua_upd_exists.core_indicator_code
);
SELECT rowid
FROM igs_as_su_stmptout_all stmptout
WHERE stmptout.person_id = cp_person_id
AND stmptout.course_cd = cp_course_cd
AND stmptout.uoo_id = cp_uoo_id;
IGS_AS_SU_STMPTOUT_PKG.DELETE_ROW(v_del_as_stmptout.rowid);
SELECT rowid
FROM IGS_PS_STDNT_UNT_TRN unttrn
WHERE unttrn.person_id = cp_person_id
AND unttrn.course_cd = cp_course_cd
AND unttrn.uoo_id = cp_uoo_id;
IGS_PS_STDNT_UNT_TRN_PKG.DELETE_ROW(v_del_ps_stdnt_unt_trn.rowid);
SELECT rowid
FROM igs_as_sua_ses_atts sua_ses_atts
WHERE sua_ses_atts.person_id = cp_person_id
AND sua_ses_atts.course_cd = cp_course_cd
AND sua_ses_atts.uoo_id = cp_uoo_id;
IGS_AS_SUA_SES_ATTS_pkg.DELETE_ROW(v_del_as_sua_ses_atts.rowid,
'R' ,
v_RETURN_STATUS,
v_MSG_DATA,
v_MSG_COUNT
);
SELECT rowid
FROM IGS_AS_MSHT_SU_ATMPT as_msht_su_atmpt
WHERE as_msht_su_atmpt.person_id = cp_person_id
AND as_msht_su_atmpt.course_cd = cp_course_cd
AND as_msht_su_atmpt.uoo_id = cp_uoo_id;
IGS_AS_MSHT_SU_ATMPT_PKG.DELETE_ROW(v_del_as_msht_su_atmpt.rowid);
|| Purpose : Processing future dated transfer records in cleanup/delete mode.
|| In cleanup mode deletes most of the child records for destination program
|| and unconfirms it.It also updates the term records against destination
|| program with source program details.
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| stutta 30-Dec-2004 Creating/deleting/updating term records while
|| cleanup and resetting globals.
|| somasekar 13-apr-2005 bug# 4179106 modified to set the future date
|| transfer Cancelles status to 'C'
------------------------------------------------------------------------------*/
PROCEDURE cleanup_dest_program(p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
p_dest_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
p_term_cal_type IGS_CA_INST.cal_type%TYPE,
p_term_sequence_number IGS_CA_INST.sequence_number%TYPE,
p_mode VARCHAR2)
IS
Type term_rec IS RECORD
(
term_cal_type igs_en_spa_terms.term_cal_type%TYPE,
term_seq_num igs_en_spa_terms.term_sequence_number%TYPE,
key_program_flag igs_en_spa_terms.key_program_flag%TYPE
);
SELECT rowid,sct.*
FROM IGS_PS_STDNT_TRN sct
WHERE sct.effective_term_cal_type = cp_term_cal_type
AND sct.effective_term_sequence_num = cp_term_sequence_number
AND sct.person_id = cp_person_id
AND sct.course_cd = cp_dest_course_cd
AND ( sct.status_flag = 'U' and p_mode IN ('CLEANUP')
OR sct.status_flag = 'C' and p_mode IN ('DELETE'));
SELECT sca.rowid,sca.*
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id
AND sca.course_cd = cp_course_cd;
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.unit_attempt_status NOT IN ('DROPPED','UNCONFIRM');
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.unit_attempt_status = 'DROPPED';
SELECT spat.rowid, spat.key_program_flag, spat.term_cal_type,
spat.term_sequence_number
FROM IGS_EN_SPA_TERMS spat, IGS_CA_INST ci1, IGS_CA_INST ci2
WHERE spat.person_id = cp_person_id
AND spat.program_cd = cp_program_cd
AND spat.term_cal_type = ci2.cal_type
AND spat.term_sequence_number = ci2.sequence_number
AND ci1.cal_type = cp_cal_type
AND ci1.sequence_number = cp_sequence_number
AND ci1.start_dt <= ci2.start_dt
ORDER BY ci2.start_dt;
SELECT spat.key_program_flag
FROM IGS_EN_SPA_TERMS spat, IGS_CA_INST ci1, IGS_CA_INST ci2
WHERE spat.person_id = cp_person_id
AND spat.program_cd = cp_program_cd
AND spat.term_cal_type = ci2.cal_type
AND spat.term_sequence_number = ci2.sequence_number
AND ci1.cal_type = cp_cal_type
AND ci1.sequence_number = cp_sequence_number
AND ci1.start_dt > ci2.start_dt
ORDER BY ci2.start_dt DESC;
SELECT 'x'
FROM igs_ca_inst_rel
WHERE sup_cal_type = cp_acad
AND sub_cal_type = cp_term
AND sub_ci_sequence_number = cp_term_seq;
SELECT term_cal_type, term_sequence_number
FROM igs_en_spa_terms spat, igs_ca_inst ca1, igs_ca_inst ca2
WHERE ca1.cal_type = p_term_cal_type
AND ca1.sequence_number = p_term_sequence_number
AND spat.person_id = p_person_id
AND spat.program_cd = cp_src_course_cd
AND ca2.cal_type = spat.term_cal_type
AND ca2.sequence_number = spat.term_sequence_number
AND ca2.start_dt >= ca1.start_dt
ORDER BY ca2.start_dt ASC;
BEGIN -----------------Begining of cleanup/delete
SAVEPOINT sp_unable_to_clnup_del;
IGS_PS_STDNT_TRN_PKG.update_row(
X_ROWID => v_spa_clnup_rec.rowid,
X_PERSON_ID => v_spa_clnup_rec.person_id,
X_COURSE_CD => v_spa_clnup_rec.course_cd,
X_TRANSFER_COURSE_CD => v_spa_clnup_rec.transfer_course_cd,
X_TRANSFER_DT => v_spa_clnup_rec.transfer_dt,
X_COMMENTS => v_spa_clnup_rec.comments,
X_APPROVED_DATE => v_spa_clnup_rec.approved_date,
X_EFFECTIVE_TERM_CAL_TYPE => v_spa_clnup_rec.effective_term_cal_type,
X_EFFECTIVE_TERM_SEQUENCE_NUM => v_spa_clnup_rec.effective_term_sequence_num,
X_DISCONTINUE_SOURCE_FLAG => v_spa_clnup_rec.discontinue_source_flag,
X_UOOIDS_TO_TRANSFER => v_spa_clnup_rec.uooids_to_transfer,
X_SUSA_TO_TRANSFER => v_spa_clnup_rec.susa_to_transfer,
X_TRANSFER_ADV_STAND_FLAG => v_spa_clnup_rec.transfer_adv_stand_flag,
X_STATUS_DATE => l_status_date,
X_STATUS_FLAG => l_trans_status
);
IGS_EN_SPA_TERMS_PKG.DELETE_ROW(v_spat_recd.rowid);
igs_en_spa_terms_api.CREATE_UPDATE_TERM_REC(
P_PERSON_ID => p_person_id,
P_PROGRAM_CD => v_src_course_cd,
P_TERM_CAL_TYPE => l_term_cal_type,
P_TERM_SEQUENCE_NUMBER => l_term_seq_num,
P_KEY_PROGRAM_FLAG => l_key_program_flag, -- using key flag from terms table
p_ripple_frwrd => v_ripple,
p_message_name => v_message_name,
p_update_rec => TRUE
);
igs_en_spa_terms_api.CREATE_UPDATE_TERM_REC(
P_PERSON_ID => p_person_id,
P_PROGRAM_CD => v_src_course_cd,
P_TERM_CAL_TYPE => terms(i).term_cal_type,
P_TERM_SEQUENCE_NUMBER => terms(i).term_seq_num,
P_KEY_PROGRAM_FLAG => l_key_program_flag, -- using key flag from terms table
p_ripple_frwrd => v_ripple,
p_message_name => v_message_name,
p_update_rec => TRUE
);
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_dest_rec.ROWID,
X_PERSON_ID => v_sca_dest_rec.PERSON_ID,
X_COURSE_CD => v_sca_dest_rec.COURSE_CD,
X_VERSION_NUMBER => v_sca_dest_rec.VERSION_NUMBER,
X_CAL_TYPE => v_sca_dest_rec.CAL_TYPE,
X_LOCATION_CD => v_sca_dest_rec.LOCATION_CD,
X_ATTENDANCE_MODE => v_sca_dest_rec.ATTENDANCE_MODE,
X_ATTENDANCE_TYPE => v_sca_dest_rec.ATTENDANCE_TYPE,
X_COO_ID => v_sca_dest_rec.COO_ID,
X_STUDENT_CONFIRMED_IND => l_stdnt_conf_ind,
X_COMMENCEMENT_DT => l_comm_date,
X_COURSE_ATTEMPT_STATUS => l_course_att_status,
X_PROGRESSION_STATUS => v_sca_dest_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_dest_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_dest_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_sca_dest_rec.PROVISIONAL_IND,
X_DISCONTINUED_DT => v_sca_dest_rec.DISCONTINUED_DT,
X_DISCONTINUATION_REASON_CD => v_sca_dest_rec.DISCONTINUATION_REASON_CD,
X_LAPSED_DT => v_sca_dest_rec.LAPSED_DT,
X_FUNDING_SOURCE => v_sca_dest_rec.FUNDING_SOURCE,
X_EXAM_LOCATION_CD => v_sca_dest_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_dest_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_dest_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_dest_rec.NOMINATED_COMPLETION_YR,
X_NOMINATED_COMPLETION_PERD => v_sca_dest_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => v_sca_dest_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_dest_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_dest_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_dest_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_dest_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_dest_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_dest_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_dest_rec.OVERRIDE_TIME_LIMITATION,
X_ADVANCED_STANDING_IND => v_sca_dest_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_sca_dest_rec.FEE_CAT,
X_CORRESPONDENCE_CAT => v_sca_dest_rec.CORRESPONDENCE_CAT,
X_SELF_HELP_GROUP_IND => v_sca_dest_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_dest_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_sca_dest_rec.ADM_ADMISSION_APPL_NUMBER,
X_ADM_NOMINATED_COURSE_CD => v_sca_dest_rec.ADM_NOMINATED_COURSE_CD,
X_ADM_SEQUENCE_NUMBER => v_sca_dest_rec.ADM_SEQUENCE_NUMBER,
X_LAST_DATE_OF_ATTENDANCE => v_sca_dest_rec.LAST_DATE_OF_ATTENDANCE,
X_DROPPED_BY => v_sca_dest_rec.DROPPED_BY,
X_IGS_PR_CLASS_STD_ID => v_sca_dest_rec.IGS_PR_CLASS_STD_ID ,
X_PRIMARY_PROGRAM_TYPE => v_sca_dest_rec.primary_program_type,
X_PRIMARY_PROG_TYPE_SOURCE => v_sca_dest_rec.PRIMARY_PROG_TYPE_SOURCE,
X_CATALOG_CAL_TYPE => v_sca_dest_rec.CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM => v_sca_dest_rec.CATALOG_SEQ_NUM,
X_KEY_PROGRAM => v_sca_dest_rec.key_program,
X_MANUAL_OVR_CMPL_DT_IND => v_sca_dest_rec.MANUAL_OVR_CMPL_DT_IND,
X_OVERRIDE_CMPL_DT => v_sca_dest_rec.OVERRIDE_CMPL_DT,
X_MODE => 'R' ,
X_ATTRIBUTE_CATEGORY => v_sca_dest_rec.ATTRIBUTE_CATEGORY,
X_FUTURE_DATED_TRANS_FLAG => 'C',
X_ATTRIBUTE1 => v_sca_dest_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_dest_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_dest_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_dest_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_dest_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_dest_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_dest_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_dest_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_dest_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_dest_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_dest_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_dest_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_dest_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_dest_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_dest_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_dest_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_dest_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_dest_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_dest_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sca_dest_rec.ATTRIBUTE20);
IF p_mode = 'DELETE' THEN
-- Delete transfer record
del_ps_trnsf( p_person_id,
p_dest_course_cd,
p_term_cal_type,
p_term_sequence_number);
IGS_EN_STDNT_PS_ATT_PKG.DELETE_ROW(v_sca_dest_rec.rowid);
END IF; -- End of Delete mode
|| Purpose : Processing future dated transfer records in process/cleanup/delete mode.
|| In cleanup/delete modes it calls cleanup_dest_program.
|| In process mode it discontinues the source program including the units
|| and unsets the future dated transfer flag for destination program
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||
------------------------------------------------------------------------------*/
PROCEDURE process_fut_dt_trans(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_term_cal_comb IN VARCHAR2,
p_mode IN VARCHAR2,
p_ignore_warnings IN VARCHAR2,
p_drop_enrolled IN VARCHAR2
)
IS
-- Cursor to fetch the records to process
CURSOR c_spa_clnup(cp_term_cal_type IGS_CA_INST.cal_type%TYPE,
cp_term_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
SELECT sct.*
FROM IGS_PS_STDNT_TRN sct
WHERE effective_term_cal_type = cp_term_cal_type
AND effective_term_sequence_num = cp_term_sequence_number
AND ( sct.status_flag = 'U' and p_mode IN ('PROCESS','CLEANUP')
OR sct.status_flag = 'C' and p_mode IN ('DELETE'));
SELECT sca.rowid,sca.*
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id
AND sca.course_cd = cp_course_cd;
SELECT BEGIN_TRANS_DT_ALIAS,CLEAN_TRANS_DT_ALIAS
FROM IGS_EN_CAL_CONF
WHERE s_control_num = 1;
SELECT alias_val
FROM igs_ca_da_inst_v
WHERE cal_type = cp_term_cal_type
AND ci_sequence_number = cp_term_sequence_number
AND dt_alias = cp_cleanup_dt_alias;
SELECT alias_val
FROM igs_ca_da_inst_v
WHERE cal_type = cp_term_cal_type
AND ci_sequence_number = cp_term_sequence_number
AND dt_alias = cp_begin_pt_dt_alias;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = cp_person_id;
SELECT end_dt
FROM igs_ca_inst
WHERE cal_type = cp_term_cal
AND sequence_number = cp_term_seq_num;
SELECT 'x'
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 = cp_acad_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 = cp_cur_term_cal AND
ci1.sequence_number = cp_cur_term_seq_num AND
ci2.start_dt < ci1.start_dt AND
ci2.start_dt > cp_transfer_dt
AND EXISTS (SELECT 'x' from igs_en_spa_terms spat
WHERE spat.person_id = cp_person_id
AND spat.program_cd = cp_dest_course_cd
AND spat.term_cal_type = ci2.cal_type
AND spat.term_sequence_number = ci2.sequence_number)
ORDER BY ci2.start_dt DESC;
SELECT uoo_id,core_indicator_code,unit_attempt_status
FROM IGS_EN_SU_ATTEMPT sua
WHERE person_id = cp_person_id
AND course_cd = cp_src_course_cd
AND not exists (SELECT 'x'
FROM igs_en_su_attempt sua2
WHERE person_id = cp_person_id
AND course_cd= cp_dest_course_cd
AND unit_attempt_status <> 'DROPPED'
AND sua.uoo_id = sua2.uoo_id)
AND unit_Attempt_status not in ('DROPPED','UNCONFIRM');
SELECT 'X'
FROM IGS_PS_STDNT_TRN sct
WHERE effective_term_cal_type = cp_term_cal_type
AND effective_term_sequence_num = cp_term_sequence_number
AND sct.person_id = cp_person_id
AND sct.course_cd = cp_dest_course_cd
AND sct.status_flag = 'C';
IF p_mode IN ('CLEANUP','DELETE') THEN
BEGIN
-- Call the cleanup destination program with p_mode as cleanup/delete
IGS_EN_FUTURE_DT_TRANS.cleanup_dest_program(
p_person_id => v_clnup_rec.person_id,
p_dest_course_cd => v_clnup_rec.course_cd,
p_term_cal_type => l_term_cal_type,
p_term_sequence_number => l_term_sequence_number,
p_mode => p_mode
);
IF p_mode = 'DELETE' THEN
-- If no transfer record is found then log the delete successful message
IF c_sct%NOTFOUND THEN
FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_DEL_DEST_PROG');
p_uoo_ids_not_selected => NULL,
p_uoo_ids_having_errors => l_uoo_ids_having_errors,
p_unit_sets_to_transfer => v_clnup_rec.SUSA_TO_TRANSFER,
p_unit_sets_not_selected => NULL,
p_unit_sets_having_errors => l_uoo_ids_having_errors,
p_transfer_av => v_clnup_rec.TRANSFER_ADV_STAND_FLAG,
p_transfer_re => 'N',
p_discontinue_source => v_clnup_rec.DISCONTINUE_SOURCE_FLAG,
p_show_warning => l_show_warning,
p_call_from => 'PROCESS',
p_process_mode => l_process_mode,
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_msg_count => l_msg_count
);
END IF; -- If condition for cleanup/delete process modes
SELECT rowid
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.uoo_id = cp_uoo_id
FOR UPDATE OF sua.LAST_UPDATE_DATE NOWAIT;
IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW( X_ROWID => v_sua_del_exists.rowid );