[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'Y'
From IGS_EN_STDNT_PS_ATT_ALL
Where person_id = p_person_id and
course_cd = p_dest_program_cd and
version_number = p_dest_prog_ver and
course_attempt_status = 'DISCONTIN';
PROCEDURE update_destination_prgm(
p_person_id IN NUMBER,
p_src_course_cd IN VARCHAR2,
p_course_cd IN VARCHAR2,
p_new_dest_key_flag IN OUT NOCOPY VARCHAR2,
p_stdnt_confrm_ind IN VARCHAR2,
p_dest_fut_dt_trans_flag IN VARCHAR2,
p_dest_commence_dt IN DATE,
p_tran_across_careers IN BOOLEAN,
p_term_cal_type IN VARCHAR2,
p_term_seq_num IN NUMBER
) AS
-------------------------------------------------------------------------------------------
--Created by : Chandrasekhar Kasu, Oracle IDC
--Date created: 20-Nov-2004
-- Purpose : This Procedure is used to update the destination program during transfer
--Change History:
--Who When What
--stutta 10-DEC-2004 Removed setting/unsetting of global. It is set in program_trasfer
-- _api, before the call to this procedure. Calculating the program
-- attempt status and sending it in all ps_att update row call. Bug #4046782
-- bdeviset 21-Mar-2005 Modified update_destination_program procedure for Bug# 4248338, 4248367.
-- As start date is disabled in transfer page assinging the dest program
-- comm dt to src program comm dt so that the user encounters no errors
-- while transferring units from src to dest.
-- stutta 26-Sep-2005 Added call to create_update_term_rec for bug 4588264
--------------------------------------------------------------------------------------------
CURSOR c_get_stdnt_ps_att_dtls IS
SELECT *
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT discontinuation_reason_cd
FROM IGS_EN_DCNT_REASONCD
WHERE dcnt_program_ind = 'Y' AND
closed_ind = 'N' AND
sys_dflt_ind = 'Y' AND
s_discontinuation_reason_type = 'TRANSFER';
SELECT commencement_dt
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_src_course_cd;
p_logical_delete_dt => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt );
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => l_stdnt_ps_attempt_dtls_rec.row_id,
X_PERSON_ID => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
X_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => l_stdnt_ps_attempt_dtls_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => l_stdnt_ps_attempt_dtls_rec.fee_cat,
X_CORRESPONDENCE_CAT => l_stdnt_ps_attempt_dtls_rec.correspondence_cat,
X_SELF_HELP_GROUP_IND => l_stdnt_ps_attempt_dtls_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt,
X_ADM_ADMISSION_APPL_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_admission_appl_number,
X_ADM_NOMINATED_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.adm_nominated_course_cd,
X_ADM_SEQUENCE_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_sequence_number,
X_VERSION_NUMBER => l_stdnt_ps_attempt_dtls_rec.version_number,
X_CAL_TYPE => l_stdnt_ps_attempt_dtls_rec.cal_type,
X_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.location_cd,
X_ATTENDANCE_MODE => l_stdnt_ps_attempt_dtls_rec.attendance_mode,
X_ATTENDANCE_TYPE => l_stdnt_ps_attempt_dtls_rec.attendance_type,
X_COO_ID => l_stdnt_ps_attempt_dtls_rec.coo_id,
X_STUDENT_CONFIRMED_IND => p_stdnt_confrm_ind,
X_COMMENCEMENT_DT => l_stdnt_ps_attempt_dtls_rec.commencement_dt,
X_COURSE_ATTEMPT_STATUS => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
X_PROGRESSION_STATUS => l_stdnt_ps_attempt_dtls_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => l_stdnt_ps_attempt_dtls_rec.provisional_ind,
X_DISCONTINUED_DT => NULL,
X_DISCONTINUATION_REASON_CD => NULL,
X_LAPSED_DT => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
X_FUNDING_SOURCE => l_stdnt_ps_attempt_dtls_rec.funding_source,
X_EXAM_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.nominated_completion_yr,
X_NOMINATED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => l_stdnt_ps_attempt_dtls_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => l_stdnt_ps_attempt_dtls_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => l_stdnt_ps_attempt_dtls_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => l_stdnt_ps_attempt_dtls_rec.OVERRIDE_TIME_LIMITATION,
x_last_date_of_attendance => l_stdnt_ps_attempt_dtls_rec.last_date_of_attendance,
x_dropped_by => l_stdnt_ps_attempt_dtls_rec.dropped_by,
X_IGS_PR_CLASS_STD_ID => l_stdnt_ps_attempt_dtls_rec.igs_pr_class_std_id,
x_primary_program_type => l_stdnt_ps_attempt_dtls_rec.primary_program_type,
x_primary_prog_type_source => l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
x_catalog_cal_type => l_stdnt_ps_attempt_dtls_rec.catalog_cal_type,
x_catalog_seq_num => l_stdnt_ps_attempt_dtls_rec.catalog_seq_num,
x_key_program => l_stdnt_ps_attempt_dtls_rec.key_program,
x_override_cmpl_dt => l_stdnt_ps_attempt_dtls_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => l_stdnt_ps_attempt_dtls_rec.manual_ovr_cmpl_dt_ind,
X_MODE => 'R',
X_ATTRIBUTE_CATEGORY => l_stdnt_ps_attempt_dtls_rec.attribute_category,
X_ATTRIBUTE1 => l_stdnt_ps_attempt_dtls_rec.attribute1,
X_ATTRIBUTE2 => l_stdnt_ps_attempt_dtls_rec.attribute2,
X_ATTRIBUTE3 => l_stdnt_ps_attempt_dtls_rec.attribute3,
X_ATTRIBUTE4 => l_stdnt_ps_attempt_dtls_rec.attribute4,
X_ATTRIBUTE5 => l_stdnt_ps_attempt_dtls_rec.attribute5,
X_ATTRIBUTE6 => l_stdnt_ps_attempt_dtls_rec.attribute6,
X_ATTRIBUTE7 => l_stdnt_ps_attempt_dtls_rec.attribute7,
X_ATTRIBUTE8 => l_stdnt_ps_attempt_dtls_rec.attribute8,
X_ATTRIBUTE9 => l_stdnt_ps_attempt_dtls_rec.attribute9,
X_ATTRIBUTE10 => l_stdnt_ps_attempt_dtls_rec.attribute10,
X_ATTRIBUTE11 => l_stdnt_ps_attempt_dtls_rec.attribute11,
X_ATTRIBUTE12 => l_stdnt_ps_attempt_dtls_rec.attribute12,
X_ATTRIBUTE13 => l_stdnt_ps_attempt_dtls_rec.attribute13,
X_ATTRIBUTE14 => l_stdnt_ps_attempt_dtls_rec.attribute14,
X_ATTRIBUTE15 => l_stdnt_ps_attempt_dtls_rec.attribute15,
X_ATTRIBUTE16 => l_stdnt_ps_attempt_dtls_rec.attribute16,
X_ATTRIBUTE17 => l_stdnt_ps_attempt_dtls_rec.attribute17,
X_ATTRIBUTE18 => l_stdnt_ps_attempt_dtls_rec.attribute18,
X_ATTRIBUTE19 => l_stdnt_ps_attempt_dtls_rec.attribute19,
X_ATTRIBUTE20 => l_stdnt_ps_attempt_dtls_rec.attribute20,
X_FUTURE_DATED_TRANS_FLAG => p_dest_fut_dt_trans_flag);
igs_en_spa_terms_api.create_update_term_rec(p_person_id => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
p_program_cd => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
p_term_cal_type => P_TERM_CAL_TYPE,
p_term_sequence_number => p_term_seq_num,
p_key_program_flag => l_key_program_flag,
p_program_changed => TRUE,
p_ripple_frwrd => TRUE,
p_message_name => l_message_name,
p_update_rec => TRUE);
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.update_destination_prgm');
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.update_destination_prgm :',SQLERRM);
END update_destination_prgm;
SELECT key_program,commencement_dt
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_src_program_cd;
SELECT begin_trans_dt_alias
FROM IGS_EN_CAL_CONF
WHERE s_control_num = 1;
SELECT ALIAS_VAL
FROM IGS_CA_DA_INST_V
WHERE dt_alias = c_begin_dt_alias AND
cal_type = p_term_cal_type AND
ci_sequence_number = p_term_seq_num;
SELECT scae.cal_type, scae.ci_sequence_number, scae.enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae, IGS_CA_INST ci
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_src_program_cd AND
ci.cal_type = scae.cal_type AND
ci.sequence_number = scae.ci_sequence_number
ORDER BY ci.start_dt DESC;
SELECT cir.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_seq_num AND
cir.sub_cal_type = c_enr_cal_type;
SELECT *
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = c_person_id AND
course_cd = c_program_cd;
SELECT person_id
FROM igs_fi_inv_int
WHERE person_id = p_person_id
AND course_cd = p_source_program_cd
AND transaction_type IN ('ASSESSMENT', 'RETENTION', 'SPECIAL')
AND invoice_amount_due > 0;
SELECT person_id
FROM igs_fi_inv_int
WHERE person_id = p_person_id
AND course_cd IS NULL
AND invoice_amount_due > 0;
SELECT pue.pue_start_dt,
pue.expiry_dt
FROM IGS_PE_PERSENC_EFFCT pee,
IGS_PE_PERS_UNT_EXCL pue
WHERE pee.person_id = p_person_id AND
pee.s_encmb_effect_type = 'EXC_CRS_U' AND
pee.course_cd = p_course_cd AND
pue.person_id = pee.person_id AND
pue.encumbrance_type = pee.encumbrance_type AND
pue.pen_start_dt = pee.pen_start_dt AND
pue.s_encmb_effect_type = pee.s_encmb_effect_type AND
pue.pee_start_dt = pee.pee_start_dt AND
pue.pee_sequence_number = pee.sequence_number AND
pue.unit_cd = p_unit_cd;
igs_en_spa_terms_api.create_update_term_rec(p_person_id => p_person_id,
p_program_cd => p_dest_program_cd,
p_term_cal_type => p_term_cal_type,
p_term_sequence_NUMBER => p_term_seq_num,
p_key_program_flag => l_key_program_flag,
p_ripple_frwrd => TRUE,
p_program_changed => TRUE,
p_message_name => p_message_name,
p_update_rec => TRUE);
SELECT course_type
FROM IGS_PS_VER
WHERE course_cd = c_program_cd AND
version_number = c_program_ver;
SELECT unit_cd,uoo_id
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id AND
course_cd = p_source_program_cd AND
unit_attempt_status IN ('ENROLLED','WAITLISTED','INVALID');
SELECT sua.unit_cd,sua.uoo_id, uoo.sup_uoo_id
FROM IGS_EN_SU_ATTEMPT sua, IGS_PS_UNIT_OFR_OPT uoo
WHERE person_id = p_person_id AND
course_cd = p_source_program_cd AND
uoo.uoo_id = sua.uoo_id AND
uoo.sup_uoo_id IS NOT NULL AND
sua.unit_attempt_status <> 'DROPPED';
l_sub_not_selected BOOLEAN := FALSE;
l_sub_not_selected := TRUE;
IF p_show_warning = 'Y' AND l_sub_not_selected AND NOT p_drop THEN
FND_MESSAGE.SET_NAME('IGS','IGS_EN_TRN_SUB_NO_SEL_DROP'); -- subordinates units of all superiors unit selected for transfer will be dropped.
SELECT enr_method_type
FROM IGS_EN_METHOD_TYPE
WHERE transfer_flag = 'Y' AND
closed_ind ='N';
SELECT 'x'
FROM IGS_AV_ADV_STANDING
WHERE person_id = c_person_id AND
course_cd = c_source_program_cd AND
version_number = c_source_prog_ver;
SELECT uoo_id
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 IN ('ENROLLED','WAITLISTED','INVALID');
SELECT cal_type
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd ;
p_uoo_ids_to_transfer IN VARCHAR2, -- concatenation of selected uoo_id,core_ind; coming from page
p_uoo_ids_passed_transfer OUT NOCOPY VARCHAR2, -- units which were successfully transfered among the selected ones from the page
p_uoo_ids_having_errors OUT NOCOPY VARCHAR2,
p_uooids_str OUT NOCOPY VARCHAR2, -- all selected uoo_ids passed from the page in format uoo_id1,uoo_id2
p_dest_fut_dt_trans_flag IN VARCHAR2,
p_show_warning IN VARCHAR2,
p_drop IN BOOLEAN,
p_return_status IN OUT NOCOPY VARCHAR2
) AS
-------------------------------------------------------------------------------------------
-- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
-- Purpose : This procedure validates and transfers unit attempts in source program to the
-- destination program.
--Change History:
--Who When What
--ckasu 02-DEC-2004 modified as a part of Bug#4044329
-- smaddali 16-dec-04 Modified for bug#4063726
-- bdeviset 21-Mar-2006 After calling enrp_val_sua_cnfrm_p,while setting message
-- used nvl(l_message_name1,l_message_name2) instead of l_message_name1
-- as we need to consider l_message_name2 if l_message_name1 is null
-- Bug# 5070403
-------------------------------------------------------------------------------------------
l_strtpoint NUMBER;
SELECT *
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id AND
course_cd = p_source_program_cd AND
uoo_id = c_uoo_id ;
SELECT 'x'
FROM IGS_PS_UNIT_VER uv
WHERE uv.unit_cd = c_unit_cd AND
uv.version_number = c_unit_version_number AND
uv.research_unit_ind = 'Y';
SELECT description
FROM igs_ca_inst
WHERE cal_type = c_teach_cal_type AND
sequence_number = c_teach_cal_seq_num ;
l_uooids_str, -- add subordinate units also when superior is selected.
p_drop,
p_show_warning);
SELECT cal_type,ci_sequence_number
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = cp_uoo_id;
SELECT load_cal_type,load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = cp_teach_cal_type AND
teach_ci_sequence_number = cp_teach_cal_seq_no
ORDER BY LOAD_START_DT;
SELECT enr_method_type
FROM IGS_EN_METHOD_TYPE
WHERE transfer_flag = 'Y'
AND closed_ind ='N';
p_unit_sets_not_selected IN VARCHAR2,
p_unit_sets_having_errors OUT NOCOPY VARCHAR2,
p_show_warning IN VARCHAR2,
p_return_status IN OUT NOCOPY VARCHAR2
) AS
-------------------------------------------------------------------------------------------
-- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
-- Purpose : This procedure validates and transfers the unitsets againts the source program
-- to the destination program.
--Change History:
--Who When What
-------------------------------------------------------------------------------------------
l_strtpoint NUMBER;
SELECT *
FROM IGS_AS_SU_SETATMPT
WHERE person_id = c_person_id AND
course_cd = c_course_cd AND
unit_set_cd = c_unit_set_cd;
SELECT *
FROM IGS_AS_SU_SETATMPT
WHERE person_id = c_person_id AND
course_cd = c_course_cd AND
unit_set_cd = c_unit_set_cd AND
sequence_number = c_sequence_number ;
l_unitset_att_rec.selection_dt ,
l_unitset_att_rec.student_confirmed_ind ,
l_unitset_att_rec.end_dt ,
l_unitset_att_rec.parent_unit_set_cd ,
l_unitset_att_rec.parent_sequence_number ,
l_unitset_att_rec.primary_set_ind ,
l_unitset_att_rec.voluntary_end_ind,
l_unitset_att_rec.authorised_person_id ,
l_unitset_att_rec.authorised_on,
l_unitset_att_rec.override_title,
l_unitset_att_rec.rqrmnts_complete_ind,
l_unitset_att_rec.rqrmnts_complete_dt,
l_unitset_att_rec.s_completed_source_type,
'INSERT',
l_message_name,
l_message_text);
PROCEDURE update_source_prgm (
p_person_id IN NUMBER,
p_source_program_cd IN VARCHAR2,
p_source_prog_ver IN NUMBER,
p_dest_program_cd IN VARCHAR2,
p_trans_approval_dt IN DATE,
p_trans_actual_dt IN DATE,
p_dest_fut_dt_trans_flag IN VARCHAR2,
p_discontinue_source IN VARCHAR2,
p_tran_across_careers IN BOOLEAN,
p_src_career_type IN VARCHAR2
) AS
-------------------------------------------------------------------------------------------
-- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
-- Purpose : This procedure updated the source program when it passes all validations
--Change History:
--Who When What
--stutta 10-DEC-2004 Unsetting skip_before_after_dml( allowing recurrsion) if discontinue_source
-- is Y for a immediate transfer. This is the only case when skip_before_after
-- _dml is unset during the entire process of program transfer. Calculate
-- program attempt status depending on whether source is becoming PRIMARY/SECONDARY.
-- Pass the program attempt status to update row call. Bug#4046782
--somasekar 13-apr-2005 bug# 4179106 modified to check the transfer status with 'S'
-- instead of 'N'
-------------------------------------------------------------------------------------------
l_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
SELECT course_type
FROM IGS_PS_VER
WHERE COURSE_CD = p_source_program_cd
AND VERSION_NUMBER = p_source_prog_ver;
SELECT *
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_source_program_cd;
SELECT discontinuation_reason_cd
FROM IGS_EN_DCNT_REASONCD
WHERE dcnt_program_ind = 'Y' AND
closed_ind = 'N' AND
sys_dflt_ind = 'Y' AND
s_discontinuation_reason_type = 'TRANSFER';
SELECT 'x'
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_src_career_type AND
sca.course_cd <> p_source_program_cd AND
sca.course_attempt_status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT');
p_logical_delete_dt => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt );
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => l_stdnt_ps_attempt_dtls_rec.row_id,
X_PERSON_ID => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
X_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => l_stdnt_ps_attempt_dtls_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => l_stdnt_ps_attempt_dtls_rec.fee_cat,
X_CORRESPONDENCE_CAT => l_stdnt_ps_attempt_dtls_rec.correspondence_cat,
X_SELF_HELP_GROUP_IND => l_stdnt_ps_attempt_dtls_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt,
X_ADM_ADMISSION_APPL_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_admission_appl_number,
X_ADM_NOMINATED_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.adm_nominated_course_cd,
X_ADM_SEQUENCE_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_sequence_number,
X_VERSION_NUMBER => l_stdnt_ps_attempt_dtls_rec.version_number,
X_CAL_TYPE => l_stdnt_ps_attempt_dtls_rec.cal_type,
X_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.location_cd,
X_ATTENDANCE_MODE => l_stdnt_ps_attempt_dtls_rec.attendance_mode,
X_ATTENDANCE_TYPE => l_stdnt_ps_attempt_dtls_rec.attendance_type,
X_COO_ID => l_stdnt_ps_attempt_dtls_rec.coo_id,
X_STUDENT_CONFIRMED_IND => l_stdnt_ps_attempt_dtls_rec.student_confirmed_ind,
X_COMMENCEMENT_DT => l_stdnt_ps_attempt_dtls_rec.commencement_dt,
X_COURSE_ATTEMPT_STATUS => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
X_PROGRESSION_STATUS => l_stdnt_ps_attempt_dtls_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => l_stdnt_ps_attempt_dtls_rec.provisional_ind,
X_DISCONTINUED_DT => l_stdnt_ps_attempt_dtls_rec.discontinued_dt,
X_DISCONTINUATION_REASON_CD => l_stdnt_ps_attempt_dtls_rec.discontinuation_reason_cd,
X_LAPSED_DT => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
X_FUNDING_SOURCE => l_stdnt_ps_attempt_dtls_rec.funding_source,
X_EXAM_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.nominated_completion_yr,
X_NOMINATED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.NOMINATED_COMPLETION_PERD,
X_RULE_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => l_stdnt_ps_attempt_dtls_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => l_stdnt_ps_attempt_dtls_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => l_stdnt_ps_attempt_dtls_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => l_stdnt_ps_attempt_dtls_rec.OVERRIDE_TIME_LIMITATION,
x_last_date_of_attendance => l_stdnt_ps_attempt_dtls_rec.last_date_of_attendance,
x_dropped_by => l_stdnt_ps_attempt_dtls_rec.dropped_by,
X_IGS_PR_CLASS_STD_ID => l_stdnt_ps_attempt_dtls_rec.igs_pr_class_std_id,
x_primary_program_type => l_stdnt_ps_attempt_dtls_rec.primary_program_type,
x_primary_prog_type_source => l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
x_catalog_cal_type => l_stdnt_ps_attempt_dtls_rec.catalog_cal_type,
x_catalog_seq_num => l_stdnt_ps_attempt_dtls_rec.catalog_seq_num,
x_key_program => l_stdnt_ps_attempt_dtls_rec.key_program,
x_override_cmpl_dt => l_stdnt_ps_attempt_dtls_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => l_stdnt_ps_attempt_dtls_rec.manual_ovr_cmpl_dt_ind,
X_MODE => 'R',
X_ATTRIBUTE_CATEGORY => l_stdnt_ps_attempt_dtls_rec.attribute_category,
X_ATTRIBUTE1 => l_stdnt_ps_attempt_dtls_rec.attribute1,
X_ATTRIBUTE2 => l_stdnt_ps_attempt_dtls_rec.attribute2,
X_ATTRIBUTE3 => l_stdnt_ps_attempt_dtls_rec.attribute3,
X_ATTRIBUTE4 => l_stdnt_ps_attempt_dtls_rec.attribute4,
X_ATTRIBUTE5 => l_stdnt_ps_attempt_dtls_rec.attribute5,
X_ATTRIBUTE6 => l_stdnt_ps_attempt_dtls_rec.attribute6,
X_ATTRIBUTE7 => l_stdnt_ps_attempt_dtls_rec.attribute7,
X_ATTRIBUTE8 => l_stdnt_ps_attempt_dtls_rec.attribute8,
X_ATTRIBUTE9 => l_stdnt_ps_attempt_dtls_rec.attribute9,
X_ATTRIBUTE10 => l_stdnt_ps_attempt_dtls_rec.attribute10,
X_ATTRIBUTE11 => l_stdnt_ps_attempt_dtls_rec.attribute11,
X_ATTRIBUTE12 => l_stdnt_ps_attempt_dtls_rec.attribute12,
X_ATTRIBUTE13 => l_stdnt_ps_attempt_dtls_rec.attribute13,
X_ATTRIBUTE14 => l_stdnt_ps_attempt_dtls_rec.attribute14,
X_ATTRIBUTE15 => l_stdnt_ps_attempt_dtls_rec.attribute15,
X_ATTRIBUTE16 => l_stdnt_ps_attempt_dtls_rec.attribute16,
X_ATTRIBUTE17 => l_stdnt_ps_attempt_dtls_rec.attribute17,
X_ATTRIBUTE18 => l_stdnt_ps_attempt_dtls_rec.attribute18,
X_ATTRIBUTE19 => l_stdnt_ps_attempt_dtls_rec.attribute19,
X_ATTRIBUTE20 => l_stdnt_ps_attempt_dtls_rec.attribute20,
X_FUTURE_DATED_TRANS_FLAG => l_stdnt_ps_attempt_dtls_rec.future_dated_trans_flag);
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.update_source_prgm');
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.update_source_prgm :',SQLERRM);
END update_source_prgm;
p_uoo_ids_not_selected IN VARCHAR2,
p_uoo_ids_having_errors OUT NOCOPY VARCHAR2,
p_unit_sets_to_transfer IN VARCHAR2,
p_unit_sets_not_selected IN VARCHAR2,
p_unit_sets_having_errors OUT NOCOPY VARCHAR2,
p_transfer_av IN VARCHAR2,
p_transfer_re IN VARCHAR2 ,
p_discontinue_source IN VARCHAR2 ,
p_show_warning IN VARCHAR2,
p_call_from IN VARCHAR2,
p_process_mode IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_data OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER
) AS
-- NOTE: Parameters p_unit_sets_not_selected, p_uoo_ids_not_selected are not being used at the moment.
-- they had been introduced earlier but are not being removed in anticipation of future need.
-------------------------------------------------------------------------------------------
-- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
-- Purpose : This procedure validates holds,debt ,unit attempt transfer,unit set transfer by
-- invoking appropriate procedures and updates the source and destination program
-- respectively
--Change History:
--Who When What
--ckasu 02-DEC-2004 modified as a part of Bug#4044329
--stutta 10-DEC-2004 Setting global skip_before_after_dml to TRUE before update
-- update destination, and unsetting it after update source. Bug #4046782
-- smaddali 16-dec-04 modified for bug#4063726
--bdeviset 22-Dec-2004 Modifed so as to update the transfer record when the transfer deatils
-- are modified and added extra params status_date and status_flag for inserting
-- transfer record as part Bug#4083015.
--amuthu 23-DEC-2004 In the career mode if the source program of a transfer is secondary
-- then an error message would be shown to the user.
-- If the source program is primary and unconfrimed then it can be transfered
-- only within the career, otherwise an error message would be shown.
--bdeviset 31-DEC-2004 Bug# 4097481.Added a call to is_sua_enroll_eff_fut_term.
--smaddali 5-jan-05 Bug#4103437 , modified logic for updating program transfer record
--somasekar 13-apr-2005 bug# 4179106 modified to check the transfer status with 'S'
-- instead of 'N'
-- ckasu 08-DEC-2005 passed SYSDATE for update_source instead of p_actual_date param
-- as part of bug#4869869
-------------------------------------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'PROGRAM_TRANSFER_APIS';
SELECT key_program ,student_confirmed_ind,commencement_dt, course_attempt_status
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = c_person_id and
course_cd = c_program_cd;
SELECT student_confirmed_ind, primary_program_type
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = c_person_id and
course_cd = c_program_cd;
SELECT cty.research_type_ind
FROM IGS_PS_VER crv,
IGS_PS_TYPE cty
WHERE crv.course_cd = c_course_cd AND
crv.version_number = c_course_ver AND
crv.course_type = cty.course_type ;
SELECT rowid, transfer_dt, comments
FROM igs_ps_stdnt_trn
WHERE person_id = c_person_id
AND course_cd = c_course_cd
AND transfer_course_cd = c_transfer_course_cd
AND STATUS_FLAG = 'U';
SELECT transfer_course_cd
FROM IGS_AD_PS_APPL
WHERE person_id = cp_person_id
AND nominated_course_cd = cp_course_cd
AND transfer_course_cd = cp_transfer_course_cd;
SELECT transfer_dt
FROM igs_ps_stdnt_trn
WHERE person_id = c_person_id
AND course_cd = c_course_cd
AND transfer_course_cd = c_transfer_course_cd
AND status_flag <> 'C';
l_unit_sets_to_transfer := arrange_selected_unitsets(p_person_id,
p_source_program_cd,
p_unit_sets_to_transfer);
update_destination_prgm(p_person_id,
p_source_program_cd,
p_dest_program_cd,
l_new_dest_key_prgm_flag,
l_dest_std_confrm_ind,
l_dest_fut_dt_trans_flag,
l_dest_commence_dt,
l_tran_across_careers,
p_term_cal_type ,
p_term_seq_num);
igs_ps_stdnt_trn_pkg.update_row(
x_rowid => l_trans_rowid,
x_person_id => p_person_id,
x_course_cd => p_dest_program_cd,
x_transfer_course_cd => p_source_program_cd,
x_TRANSFER_DT => l_trans_date,
x_COMMENTS => l_trans_comments,
X_APPROVED_DATE => p_trans_approval_dt,
X_EFFECTIVE_TERM_CAL_TYPE => p_term_cal_type,
X_EFFECTIVE_TERM_SEQUENCE_NUM => p_term_seq_num,
X_DISCONTINUE_SOURCE_FLAG => p_discontinue_source,
X_UOOIDS_TO_TRANSFER => p_uoo_ids_to_transfer,
X_SUSA_TO_TRANSFER => l_unit_sets_to_transfer,
X_TRANSFER_ADV_STAND_FLAG => p_transfer_av,
X_STATUS_DATE => l_status_date,
X_STATUS_FLAG => l_trans_status
);
p_unit_sets_not_selected,
p_unit_sets_having_errors,
p_show_warning,
p_return_status);
update_source_prgm (p_person_id,
p_source_program_cd,
p_source_prog_ver,
p_dest_program_cd,
p_trans_approval_dt,
l_status_date,
-- passing l_status_date = SYSDATE to p_trans_actual_dt parameter.
-- This parameter is used while discontinuing the source program and has be >= Transfer date
l_dest_fut_dt_trans_flag,
p_discontinue_source,
l_tran_across_careers,
l_src_career_type);
FND_MSG_PUB.DELETE_MSG(l_msg_index);
FUNCTION arrange_selected_unitsets(
p_person_id IN NUMBER,
p_program_cd IN VARCHAR2,
p_unit_sets_to_transfer IN VARCHAR2
) RETURN VARCHAR2 IS
-------------------------------------------------------------------------------------------
-- Created by : chanchal tyagi, Oracle Student Systems Oracle IDC
--Change History:
--Who When What
--ctyagi 25-Nov-2005 changed the order of unitset_attempt string
-------------------------------------------------------------------------------------------
CURSOR c_parent_exist (c_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,
c_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE,
c_unitset_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
c_seq_no IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE)
IS
SELECT 'x' FROM igs_as_su_setatmpt
WHERE person_id = c_person_id
AND COURSE_CD = c_program_cd
AND UNIT_SET_CD = c_unitset_cd
AND SEQUENCE_NUMBER= c_seq_no
AND PARENT_UNIT_SET_CD IS NOT NULL;
select susa.unit_set_cd || ',' || susa.sequence_number AS unitcd_seqno
from igs_as_su_setatmpt susa
where susa.person_id = c_person_id
AND SUSA.COURSE_CD= c_program_cd
AND level >= 2
START WITH
susa.person_id = c_person_id AND
susa.course_cd = c_program_cd AND
susa.unit_set_cd = c_unitset_cd AND
susa.sequence_number = c_seq_no
CONNECT BY
PRIOR susa.person_id = susa.person_id AND
PRIOR susa.course_cd = susa.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;
l_final_selected_unitset VARCHAR2(4000);
l_final_selected_unitset := l_final_selected_unitset || l_unitset_seqno_and_prmind || ';' ;
l_cindex := INSTR(l_final_selected_unitset,';',1,l_nth_occurence);
l_final_selected_unitset := l_final_selected_unitset || l_token_str || ';';
IF NVL (LENGTH(l_final_selected_unitset),0) < LENGTH(p_unit_sets_to_transfer) THEN
l_strtpoint := 0;
l_sub_token1 := InStr(l_final_selected_unitset,l_unitset_seqno_and_prmind,1,1);
l_final_selected_unitset := l_final_selected_unitset || l_unitset_seqno_and_prmind || ';' ;
IF NVL (LENGTH(l_final_selected_unitset),0) = LENGTH(p_unit_sets_to_transfer) THEN
exit ;
return l_final_selected_unitset;
END arrange_selected_unitsets;