[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igs_en_std_warnings
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND message_icon = cp_msg_icon
AND step_type <> 'DROP';
SELECT 'X'
FROM igs_en_std_warnings
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND message_icon <> 'D'
AND step_type <> 'DROP';
p_selected_uoo_ids IN VARCHAR2,
p_message OUT NOCOPY VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT ROWID,uoo_id
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND cart_error_flag = 'Y';
SELECT 'X'
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_status <> 'DROPPED';
l_sel_uoo_ids := p_selected_uoo_ids;
IGS_EN_PLAN_UNITS_PKG.DELETE_ROW(x_rowid => l_ps_err_rec.ROWID);
PROCEDURE update_warnings_table (p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER,
p_calling_obj IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT DISTINCT uoo_id
FROM igs_en_std_warnings
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND message_icon = 'D'
AND step_type <> 'DROP';
SELECT 'X'
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_Status <> 'DROPPED';
SELECT 'X'
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND cart_error_flag = 'Y';
SELECT ROWID,warn.*
FROM igs_en_std_warnings warn
WHERE warn.person_id = p_person_id
AND warn.course_cd = p_course_cd
AND warn.term_cal_type = p_load_cal_type
AND warn.term_ci_sequence_number = p_load_sequence_number
AND warn.message_icon = 'D'
AND warn.step_type <> 'DROP'
AND warn.uoo_id = cp_uoo_id;
IGS_EN_STD_WARNINGS_PKG.UPDATE_ROW (
x_rowid => l_warn_rec.rowid,
x_warning_id => l_warn_rec.warning_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => dist.uoo_id,
x_term_cal_type => p_load_cal_type,
x_term_ci_sequence_number => p_load_sequence_number,
x_message_for => l_warn_rec.message_for,
x_message_icon => 'I',
x_message_name => l_warn_rec.message_name,
x_message_text => l_message1||' '||l_warn_rec.message_text||' '||l_message2,
x_message_action => l_warn_rec.message_action,
x_destination => l_warn_rec.destination,
x_p_parameters => l_warn_rec.p_parameters,
x_step_type => l_warn_rec.step_type,
x_session_id => igs_en_add_units_api.g_ss_session_id,
x_mode => 'R' );
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.update_warnings_table');
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.update_warnings_table :',SQLERRM);
END update_warnings_table;
SELECT unit_cd||'/'||unit_class unit_sec
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = cp_uoo_id;
SELECT person_type_code
FROM igs_pe_person_types
WHERE system_type = 'OTHER';
PROCEDURE delete_ss_warnings (p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER,
p_uoo_id IN NUMBER,
p_message_for IN VARCHAR2,
p_delete_steps IN VARCHAR2
) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT ROWID
FROM IGS_EN_STD_WARNINGS
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND ((p_uoo_id IS NOT NULL AND uoo_id = p_uoo_id) OR
message_for = p_message_for)
AND step_type <> 'DROP';
IF p_delete_steps IS NULL THEN
OPEN c_ss_warn;
IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => l_rowid);
l_stmt := 'SELECT ROWID
FROM IGS_EN_STD_WARNINGS
WHERE person_id = :p_person_id
AND course_cd = :p_course_cd
AND term_cal_type = :p_load_cal_type
AND term_ci_sequence_number = :p_load_sequence_number
AND step_type IN ( ''' || p_delete_steps ||''')
AND uoo_id = :p_uooo_id' ;
l_stmt := 'SELECT ROWID
FROM IGS_EN_STD_WARNINGS
WHERE person_id = :p_person_id
AND course_cd = :p_course_cd
AND term_cal_type = :p_load_cal_type
AND term_ci_sequence_number = :p_load_sequence_number
AND step_type IN (''' || p_delete_steps || ''')';
IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => l_rowid);
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.delete_ss_warnings :',SQLERRM);
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.delete_ss_warnings');
END delete_ss_warnings;
p_selected_units IN VARCHAR2
) RETURN BOOLEAN AS
-------------------------------------------------------------------------------------------
--Created by : Basanth Kumar D, Oracle IDC
--Date created: 29-JUL-2005
-- Purpose : returns true if unit attempt can be created else false
-- If the unit is not a subordinate then returns true
-- if the unit is a subordiante and selected by system and superior has taken a seat then returns true
-- else returns false
-- if the unit is a subordiante and selected by user and superior exists in 'UNCONFIRM','INVALID','WAITLISTED','ENROLLED'
-- statuses then returns true else false
--Change History:
--Who When What
-------------------------------------------------------------------------------------------
-- cursor to check wheter a unit is subordinate
CURSOR cur_chk_unit_is_sub(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)IS
SELECT sup_uoo_id
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id
AND sup_uoo_id IS NOT NULL
AND relation_type = 'SUBORDINATE';
SELECT 'X' FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_status IN ('UNCONFIRM','INVALID','WAITLISTED','ENROLLED');
IF instr(p_selected_units,','||p_uoo_id||',') = 0 THEN
-- if subordinate has been automatically selected by the system, check if the superior has taken a seat/waitlisted.
-- if superior has not taken a seat then donot select this subordinate.
-- check if superior has taken a seat or not.
l_sup_created := FALSE;
delete_ss_warnings( p_person_id,
p_course_cd,
p_load_cal_type,
p_load_sequence_number,
p_uoo_id,
NULL,
'PROGRAM'',''UNIT');
END IF; -- IF instr(p_selected_units,','||p_uoo_id||',') = 0
SELECT 'x'
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id
AND cart_error_flag = p_cart_error_flag;
IGS_EN_PLAN_UNITS_PKG.insert_row(
x_rowid => l_row_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_term_cal_type => p_load_cal_type,
x_term_ci_sequence_number => p_load_sequence_number,
x_no_assessment_ind => p_assessment_ind, --- need to check
x_sup_uoo_id => p_sup_uoo_id,--unit_dtls_rec,
x_override_enrolled_cp => p_override_enrolled_cp,--unit_dtls_rec,
x_grading_schema_code => p_grading_schema_code,--unit_dtls_rec,
x_gs_version_number => p_gs_version_number,--unit_dtls_rec,
x_core_indicator_code => NULL,--unit_dtls_rec,
x_alternative_title => NULL,--unit_dtls_rec,
x_cart_error_flag => p_cart_error_flag,
x_session_id => igs_en_add_units_api.g_ss_session_id,
x_mode => 'R'
);
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = p_person_id;
SELECT ROWID
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id
AND cart_error_flag = 'Y';
igs_ss_en_wrappers.insert_into_enr_worksheet
(p_person_number => l_person_number,
p_course_cd => p_course_cd ,
p_uoo_id => p_uoo_id,
p_waitlist_ind => 'N',
p_session_id => igs_en_add_units_api.g_ss_session_id,
p_return_status => p_return_status,
p_message => p_message,
p_cal_type => p_load_cal_type,
p_ci_sequence_number => p_load_sequence_number,
p_audit_requested => p_audit_requested,
p_enr_method => p_enr_method,
p_override_cp => p_override_cp,
p_subtitle => NULL,
p_gradsch_cd => p_gradsch_cd,
p_gs_version_num => p_gs_version_number,
p_core_indicator_code => l_core_indicator_code,
p_calling_obj => p_calling_obj);
IGS_EN_PLAN_UNITS_PKG.delete_row(x_rowid => l_rowid);
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = p_person_id;
SELECT ROWID
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id
AND cart_error_flag = 'Y';
igs_ss_en_wrappers.insert_into_enr_worksheet
(p_person_number => l_person_number,
p_course_cd => p_course_cd ,
p_uoo_id => p_uoo_id,
p_waitlist_ind => p_waitlist_ind,
p_session_id => igs_en_add_units_api.g_ss_session_id,
p_return_status => p_return_status,
p_message => p_message,
p_cal_type => p_load_cal_type,
p_ci_sequence_number => p_load_sequence_number,
p_audit_requested => p_audit_requested,
p_enr_method => p_enr_method,
p_override_cp => p_override_cp,
p_subtitle => NULL,
p_gradsch_cd => p_gradsch_cd,
p_gs_version_num => p_gs_version_number,
p_core_indicator_code => l_core_indicator_code,
p_calling_obj => p_calling_obj);
IGS_EN_PLAN_UNITS_PKG.delete_row(x_rowid => l_rowid);
SELECT sup_uoo_id
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = cp_uoo_id
AND relation_type = 'SUBORDINATE';
SELECT spl_perm_request_id
FROM igs_en_spl_perm
WHERE student_person_id = p_person_id
AND uoo_id = p_uoo_id
AND request_type = cp_request_type;
SELECT 'Y'
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id ;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id = p_person_id;
SELECT sup_uoo_id
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = cp_uoo_id
AND relation_type = 'SUBORDINATE';
igs_ss_en_wrappers.insert_into_enr_worksheet(
p_person_number => l_person_number,
p_course_cd => p_course_cd ,
p_uoo_id => p_uoo_id,
p_waitlist_ind => 'Y',
p_session_id => igs_en_add_units_api.g_ss_session_id,
p_return_status => l_return_status,
p_message => p_message,
p_cal_type => p_load_cal_type,
p_ci_sequence_number => p_load_sequence_number,
p_audit_requested => p_audit_requested,
p_enr_method => p_enr_method,
p_override_cp => p_override_cp,
p_subtitle => NULL,
p_gradsch_cd => p_gradsch_cd,
p_gs_version_num => p_gs_version_number,
p_core_indicator_code =>l_core_indicator_code,
p_calling_obj => p_calling_obj);
SELECT relation_type
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT uoo_id,no_assessment_ind,grading_schema_code,gs_version_number,
override_enrolled_cp
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND( (p_calling_obj in('PLAN','SUBMITPLAN') and cart_error_flag = 'N')OR
(p_calling_obj <> 'PLAN' and cart_error_flag = 'Y'));
SELECT sup_uoo_id
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id
AND relation_type = 'SUBORDINATE';
SELECT uoo_id,default_enroll_flag
FROM igs_ps_unit_ofr_opt
WHERE sup_uoo_id = cp_uoo_id
AND relation_type = 'SUBORDINATE' ;
SELECT 'X'
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_status NOT IN ('DROPPED');
SELECT system_type
FROM igs_pe_person_types
WHERE person_type_code = g_person_type;
SELECT unit_cd||'/'||unit_class
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_status <> 'DROPPED';
SELECT ofr.unit_cd, ofr.version_number, ofr.cal_type, ofr.ci_sequence_number,
ofr.location_cd, ofr.unit_class, ca.start_dt, ca.end_dt
FROM igs_ps_unit_ofr_opt ofr, igs_ca_inst_all ca
WHERE ofr.uoo_id = cp_uoo_id
AND ofr.cal_type = ca.cal_type
AND ofr.ci_sequence_number = ca.sequence_number;
SELECT sua.rowid
FROM IGS_EN_SU_ATTEMPT_ALL SUA
WHERE sua.person_id=cp_person_id
AND sua.course_cd=cp_course_cd
AND sua.uoo_id=cp_uoo_id
AND sua.unit_attempt_status='DROPPED';
l_step_def_query := 'SELECT eru.*
FROM igs_en_cpd_ext eru, igs_lookups_view lkv
WHERE eru.s_enrolment_step_type =lkv.lookup_code
AND lkv.lookup_type = ''ENROLMENT_STEP_TYPE_EXT''
AND lkv.step_group_type = ''UNIT''
AND eru.enrolment_cat = :1
AND eru.enr_method_type = :2
AND ( eru.s_student_comm_type = :3 OR
eru.s_student_comm_type = ''ALL'' )
AND eru.s_enrolment_step_type = ''CART_MAX''';
l_step_def_query := 'SELECT eru.*
FROM igs_en_cpd_ext eru, igs_pe_usr_aval_all uact, igs_lookups_view lkv
WHERE eru.s_enrolment_step_type =lkv.lookup_code
AND lkv.lookup_type = ''ENROLMENT_STEP_TYPE_EXT''
AND lkv.step_group_type = ''UNIT''
AND eru.s_enrolment_step_type = uact.validation(+)
AND uact.person_type(+) = :1
AND NVL(uact.override_ind,''N'') = ''N''
AND eru.enrolment_cat = :2
AND eru.enr_method_type = :3
AND ( eru.s_student_comm_type = :4 OR
eru.s_student_comm_type = ''ALL'')
AND eru.s_enrolment_step_type = ''CART_MAX''';
INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
PERSON_ID,
COURSE_CD,
UNIT_CD,
VERSION_NUMBER,
CAL_TYPE,
CI_SEQUENCE_NUMBER,
LOCATION_CD,
UNIT_CLASS,
CI_START_DT,
CI_END_DT,
UOO_ID,
ENROLLED_DT,
UNIT_ATTEMPT_STATUS,
ADMINISTRATIVE_UNIT_STATUS,
DISCONTINUED_DT,
RULE_WAIVED_DT,
RULE_WAIVED_PERSON_ID,
NO_ASSESSMENT_IND,
SUP_UNIT_CD,
SUP_VERSION_NUMBER,
EXAM_LOCATION_CD,
ALTERNATIVE_TITLE,
OVERRIDE_ENROLLED_CP,
OVERRIDE_EFTSU,
OVERRIDE_ACHIEVABLE_CP,
OVERRIDE_OUTCOME_DUE_DT,
OVERRIDE_CREDIT_REASON,
ADMINISTRATIVE_PRIORITY,
WAITLIST_DT,
DCNT_REASON_CD,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
org_id,
GS_VERSION_NUMBER,
ENR_METHOD_TYPE ,
FAILED_UNIT_RULE ,
CART ,
RSV_SEAT_EXT_ID ,
ORG_UNIT_CD ,
GRADING_SCHEMA_CODE ,
subtitle,
session_id,
deg_aud_detail_id,
student_career_transcript,
student_career_statistics,
waitlist_manual_ind ,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
WLST_PRIORITY_WEIGHT_NUM,
WLST_PREFERENCE_WEIGHT_NUM,
CORE_INDICATOR_CODE,
UPD_AUDIT_FLAG,
SS_SOURCE_IND
)
VALUES (p_person_id,
p_course_cd,
l_unit_dtsl_rec.unit_cd, -- unit_cd
l_unit_dtsl_rec.version_number, -- unit ver no
l_unit_dtsl_rec.cal_type, -- cal_type
l_unit_dtsl_rec.ci_sequence_number, -- cal seq num
l_unit_dtsl_rec.location_cd,
l_unit_dtsl_rec.unit_class,
l_unit_dtsl_rec.start_dt,
l_unit_dtsl_rec.end_dt,
p_unit_params(i).uoo_id,
NULL, -- enrolled date
'UNCONFIRM',
NULL, -- ADMINISTRATIVE_UNIT_STATUS
NULL, -- DISCONTINUED_DT
NULL, -- RULE_WAIVED_DT,
NULL, -- RULE_WAIVED_PERSON_ID,
p_unit_params(i).ass_ind, -- NO_ASSESSMENT_IND,
NULL, -- SUP_UNIT_CD,
NULL, -- SUP_VERSION_NUMBER,
NULL, -- EXAM_LOCATION_CD
NULL, -- ALTERNATIVE_TITLE,
p_unit_params(i).override_enrolled_cp, -- OVERRIDE_ENROLLED_CP,
NULL, --- OVERRIDE_EFTSU,
NULL, -- OVERRIDE_ACHIEVABLE_CP,
NULL, -- OVERRIDE_OUTCOME_DUE_DT,
NULL, -- OVERRIDE_CREDIT_REASON,
NULL, -- ADMINISTRATIVE_PRIORITY,
NULL, -- WAITLIST_DT,
NULL, -- DCNT_REASON_CD,
SYSDATE, -- CREATION_DATE,
NVL(FND_GLOBAL.USER_ID,-1),-- CREATED_BY,
SYSDATE, -- LAST_UPDATE_DATE,
NVL(FND_GLOBAL.USER_ID,-1), -- LAST_UPDATED_BY,
NVL(FND_GLOBAL.LOGIN_ID,-1), -- LAST_UPDATE_LOGIN,
NULL, -- REQUEST_ID,
NULL, --PROGRAM_ID,
NULL, --PROGRAM_APPLICATION_ID,
NULL, --PROGRAM_UPDATE_DATE,
NULL, -- org_id,
p_unit_params(i).gs_version_number, -- GS_VERSION_NUMBER,
NULL, -- ENR_METHOD_TYPE ,
NULL, --FAILED_UNIT_RULE ,
NULL, --CART ,
NULL, -- RSV_SEAT_EXT_ID ,
NULL, -- ORG_UNIT_CD ,
p_unit_params(i).grading_schema_cd, -- GRADING_SCHEMA_CODE ,
NULL, -- subtitle,
igs_en_add_units_api.g_ss_session_id, -- session_id,
NULL, -- deg_aud_detail_id,
NULL, -- student_career_transcript,
NULL, -- student_career_statistics,
NULL, -- waitlist_manual_ind ,--Bug ID: 2554109 added by adhawan
NULL, -- ATTRIBUTE_CATEGORY,
NULL, -- ATTRIBUTE1,
NULL, -- ATTRIBUTE2,
NULL, -- ATTRIBUTE3,
NULL, -- ATTRIBUTE4,
NULL, -- ATTRIBUTE5,
NULL, -- ATTRIBUTE6,
NULL, -- ATTRIBUTE7,
NULL, -- ATTRIBUTE8,
NULL, -- ATTRIBUTE9,
NULL, -- ATTRIBUTE10,
NULL, -- ATTRIBUTE11,
NULL, -- ATTRIBUTE12,
NULL, -- ATTRIBUTE13,
NULL, -- ATTRIBUTE14,
NULL, -- ATTRIBUTE15,
NULL, -- ATTRIBUTE16,
NULL, -- ATTRIBUTE17,
NULL, -- ATTRIBUTE18,
NULL, -- ATTRIBUTE19,
NULL, -- ATTRIBUTE20,
NULL, -- WLST_PRIORITY_WEIGHT_NUM,
NULL, -- WLST_PREFERENCE_WEIGHT_NUM,
NULL, -- CORE_INDICATOR_CODE
'N', -- upd_audit_ind
'N'); -- ss_source_ind
-- unit attempt was attempted and dropped previously, so update that record
CLOSE c_sua;
UPDATE IGS_EN_SU_ATTEMPT_ALL SET
VERSION_NUMBER =l_unit_dtsl_rec.version_number,
LOCATION_CD = l_unit_dtsl_rec.location_cd,
UNIT_CLASS = l_unit_dtsl_rec.unit_class,
CI_START_DT = l_unit_dtsl_rec.start_dt,
CI_END_DT = l_unit_dtsl_rec.end_dt,
ENROLLED_DT = NULL,
UNIT_ATTEMPT_STATUS = 'UNCONFIRM',
ADMINISTRATIVE_UNIT_STATUS = NULL,
DISCONTINUED_DT = NULL,
RULE_WAIVED_DT = NULL,
RULE_WAIVED_PERSON_ID = NULL,
NO_ASSESSMENT_IND = p_unit_params(i).ass_ind,
SUP_UNIT_CD = NULL,
SUP_VERSION_NUMBER = NULL,
EXAM_LOCATION_CD = NULL,
ALTERNATIVE_TITLE = NULL,
OVERRIDE_ENROLLED_CP = p_unit_params(i).override_enrolled_cp,
OVERRIDE_EFTSU = NULL,
OVERRIDE_ACHIEVABLE_CP = NULL,
OVERRIDE_OUTCOME_DUE_DT = NULL,
OVERRIDE_CREDIT_REASON = NULL,
ADMINISTRATIVE_PRIORITY = NULL,
WAITLIST_DT = NULL,
DCNT_REASON_CD = NULL,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = NVL(FND_GLOBAL.USER_ID,-1),
LAST_UPDATE_LOGIN = NVL(FND_GLOBAL.LOGIN_ID,-1),
REQUEST_ID = NULL,
PROGRAM_ID = NULL,
PROGRAM_APPLICATION_ID = NULL,
PROGRAM_UPDATE_DATE = NULL,
GS_VERSION_NUMBER = p_unit_params(i).gs_version_number,
ENR_METHOD_TYPE = NULL,
FAILED_UNIT_RULE = NULL,
CART = NULL,
RSV_SEAT_EXT_ID = NULL,
ORG_UNIT_CD = NULL,
GRADING_SCHEMA_CODE = p_unit_params(i).grading_schema_cd,
SUBTITLE = NULL,
SESSION_ID = igs_en_add_units_api.g_ss_session_id,
DEG_AUD_DETAIL_ID = NULL,
STUDENT_CAREER_TRANSCRIPT = NULL,
STUDENT_CAREER_STATISTICS = NULL,
WAITLIST_MANUAL_IND = NULL,
ATTRIBUTE_CATEGORY = NULL,
ATTRIBUTE1 = NULL,
ATTRIBUTE2 = NULL,
ATTRIBUTE3 = NULL,
ATTRIBUTE4 = NULL,
ATTRIBUTE5 = NULL,
ATTRIBUTE6 = NULL,
ATTRIBUTE7 = NULL,
ATTRIBUTE8 = NULL,
ATTRIBUTE9 = NULL,
ATTRIBUTE10 = NULL,
ATTRIBUTE11 = NULL,
ATTRIBUTE12 = NULL,
ATTRIBUTE13 = NULL,
ATTRIBUTE14 = NULL,
ATTRIBUTE15 = NULL,
ATTRIBUTE16 = NULL,
ATTRIBUTE17 = NULL,
ATTRIBUTE18 = NULL,
ATTRIBUTE19 = NULL,
ATTRIBUTE20 = NULL,
WLST_PRIORITY_WEIGHT_NUM = NULL,
WLST_PREFERENCE_WEIGHT_NUM = NULL,
CORE_INDICATOR_CODE = NULL,
UPD_AUDIT_FLAG = 'N' ,
SS_SOURCE_IND = 'N'
WHERE ROWID = l_rowid;
SELECT ofr.unit_cd, ofr.version_number, ofr.cal_type, ofr.ci_sequence_number,
ofr.location_cd, ofr.unit_class, ca.start_dt, ca.end_dt
FROM igs_ps_unit_ofr_opt ofr, igs_ca_inst_all ca
WHERE ofr.uoo_id = cp_uoo_id
AND ofr.cal_type = ca.cal_type
AND ofr.ci_sequence_number = ca.sequence_number;
SELECT DISTINCT sua.uoo_id, sua.sup_unit_cd, no_assessment_ind,unit_Attempt_Status,ss_source_ind
FROM igs_en_su_attempt sua,igs_ca_teach_to_load_v load
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'UNCONFIRM'
AND NVL(sua.ss_source_ind ,'N') <> 'S'
AND sua.cal_type = load.teach_cal_type
AND sua.ci_sequence_number = load.teach_ci_sequence_number
AND load_cal_type = p_load_cal_type
AND load_ci_sequence_number = p_load_sequence_number
ORDER BY sua.sup_unit_cd DESC;
SELECT sua.*
FROM igs_en_su_attempt sua
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id;
SELECT cal_type, ci_sequence_number,unit_cd,unit_class
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT daiv.alias_val,
ci.start_dt,
ci.end_dt
FROM IGS_CA_DA_INST_V daiv,
IGS_CA_INST ci,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = p_cal_type AND
daiv.ci_sequence_number = p_ci_sequence_number AND
daiv.dt_alias = sgcc.census_dt_alias AND
sgcc.s_control_num = 1 AND
daiv.cal_type = ci.cal_type AND
daiv.ci_sequence_number = ci.sequence_number;
IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW (
X_ROWID => l_rowid ,
X_PERSON_ID => p_person_id ,
X_COURSE_CD => p_course_cd ,
X_UNIT_CD => l_unit_rec.unit_cd ,
X_CAL_TYPE => l_unit_rec.cal_type ,
X_CI_SEQUENCE_NUMBER => l_unit_rec.ci_sequence_number ,
X_VERSION_NUMBER => l_unit_rec.version_number ,
X_LOCATION_CD => l_unit_rec.location_cd ,
X_UNIT_CLASS => l_unit_rec.unit_class ,
X_CI_START_DT => l_unit_rec.start_dt ,
X_CI_END_DT => l_unit_rec.end_dt ,
X_UOO_ID => p_unit_params(i).uoo_id ,
X_ENROLLED_DT => NULL ,
X_UNIT_ATTEMPT_STATUS => 'PLANNED' ,
X_ADMINISTRATIVE_UNIT_STATUS => NULL ,
X_DISCONTINUED_DT => NULL ,
X_RULE_WAIVED_DT => NULL ,
X_RULE_WAIVED_PERSON_ID => NULL ,
X_NO_ASSESSMENT_IND => NVL(p_unit_params(i).ass_ind,'N') , -- value passed to indicate that audit is requeted or not
X_SUP_UNIT_CD => NULL ,
X_SUP_VERSION_NUMBER => NULL ,
X_EXAM_LOCATION_CD => NULL ,
X_ALTERNATIVE_TITLE => NULL ,
X_OVERRIDE_ENROLLED_CP => p_unit_params(i).override_enrolled_cp ,
X_OVERRIDE_EFTSU => NULL ,
X_OVERRIDE_ACHIEVABLE_CP => NULL , -- selective values passed based on whether audit is requeted or not
X_OVERRIDE_OUTCOME_DUE_DT => NULL ,
X_OVERRIDE_CREDIT_REASON => NULL ,
X_ADMINISTRATIVE_PRIORITY => NULL ,
X_WAITLIST_DT => NULL ,
X_DCNT_REASON_CD => NULL ,
X_MODE => 'R' ,
X_ORG_ID => NULL ,
X_GS_VERSION_NUMBER => p_unit_params(i).gs_version_number ,
X_ENR_METHOD_TYPE => p_enr_method_type ,
X_FAILED_UNIT_RULE => NULL ,
X_CART => NULL ,
X_RSV_SEAT_EXT_ID => NULL ,
X_ORG_UNIT_CD => NULL ,
-- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
X_SESSION_ID => igs_en_add_units_api.g_ss_session_id ,
-- Added the column grading schema as a part pf the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => p_unit_params(i).grading_schema_cd ,
--Added the column Deg_Aud_Detail_Id as part of Degree Audit Interface build. (Bug# 2033208) - pradhakr
X_DEG_AUD_DETAIL_ID => NULL,
X_STUDENT_CAREER_TRANSCRIPT => NULL,
X_STUDENT_CAREER_STATISTICS => NULL,
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_WAITLIST_MANUAL_IND => NULL, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.,
X_WLST_PRIORITY_WEIGHT_NUM => NULL,
X_WLST_PREFERENCE_WEIGHT_NUM => NULL,
X_CORE_INDICATOR_CODE => l_core_indicator_code,
X_UPD_AUDIT_FLAG => 'N',
X_SS_SOURCE_IND => 'P'
);
IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW(
X_ROWID => l_sua_rec.row_id ,
X_PERSON_ID => l_sua_rec.person_id ,
X_COURSE_CD => l_sua_rec.course_cd ,
X_UNIT_CD => l_sua_rec.unit_cd ,
X_CAL_TYPE => l_sua_rec.cal_type ,
X_CI_SEQUENCE_NUMBER => l_sua_rec.ci_sequence_number ,
X_VERSION_NUMBER => l_sua_rec.version_number ,
X_LOCATION_CD => l_sua_rec.location_cd ,
X_UNIT_CLASS => l_sua_rec.unit_class ,
X_CI_START_DT => l_sua_rec.ci_start_dt ,
X_CI_END_DT => l_sua_rec.ci_end_dt ,
X_UOO_ID => l_sua_rec.uoo_id ,
X_ENROLLED_DT => SYSDATE ,
X_UNIT_ATTEMPT_STATUS => 'ENROLLED' ,
X_ADMINISTRATIVE_UNIT_STATUS => l_sua_rec.administrative_unit_status ,
X_DISCONTINUED_DT => l_sua_rec.discontinued_dt ,
X_RULE_WAIVED_DT => l_sua_rec.rule_waived_dt ,
X_RULE_WAIVED_PERSON_ID => l_sua_rec.rule_waived_person_id ,
X_NO_ASSESSMENT_IND => l_sua_rec.no_assessment_ind ,
X_SUP_UNIT_CD => l_sua_rec.sup_unit_cd ,
X_SUP_VERSION_NUMBER => l_sua_rec.sup_version_number ,
X_EXAM_LOCATION_CD => l_sua_rec.exam_location_cd ,
X_ALTERNATIVE_TITLE => l_sua_rec.alternative_title ,
X_OVERRIDE_ENROLLED_CP => l_sua_rec.override_enrolled_cp ,
X_OVERRIDE_EFTSU => l_sua_rec.override_eftsu ,
X_OVERRIDE_ACHIEVABLE_CP => l_sua_rec.override_achievable_cp ,
X_OVERRIDE_OUTCOME_DUE_DT => l_sua_rec.override_outcome_due_dt ,
X_OVERRIDE_CREDIT_REASON => l_sua_rec.override_credit_reason ,
X_ADMINISTRATIVE_PRIORITY => l_sua_rec.administrative_priority ,
X_WAITLIST_DT => l_sua_rec.waitlist_dt ,
X_DCNT_REASON_CD => l_sua_rec.dcnt_reason_cd ,
X_MODE => 'R' ,
X_GS_VERSION_NUMBER => l_sua_rec.gs_version_number ,
X_ENR_METHOD_TYPE => l_sua_rec.enr_method_type ,
X_FAILED_UNIT_RULE => l_sua_rec.failed_unit_rule ,
X_CART => 'N' ,
X_RSV_SEAT_EXT_ID => l_sua_rec.rsv_seat_ext_id ,
X_ORG_UNIT_CD => l_sua_rec.org_unit_cd ,
X_SESSION_ID => l_sua_rec.session_id ,
X_GRADING_SCHEMA_CODE => l_sua_rec.grading_schema_code ,
X_DEG_AUD_DETAIL_ID => l_sua_rec.deg_aud_detail_id ,
X_STUDENT_CAREER_TRANSCRIPT => l_sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => l_sua_rec.student_career_statistics,
x_waitlist_manual_ind => l_sua_rec.waitlist_manual_ind ,
X_ATTRIBUTE_CATEGORY => l_sua_rec.attribute_category,
X_ATTRIBUTE1 => l_sua_rec.attribute1,
X_ATTRIBUTE2 => l_sua_rec.attribute2,
X_ATTRIBUTE3 => l_sua_rec.attribute3,
X_ATTRIBUTE4 => l_sua_rec.attribute4,
X_ATTRIBUTE5 => l_sua_rec.attribute5,
X_ATTRIBUTE6 => l_sua_rec.attribute6,
X_ATTRIBUTE7 => l_sua_rec.attribute7,
X_ATTRIBUTE8 => l_sua_rec.attribute8,
X_ATTRIBUTE9 => l_sua_rec.attribute9,
X_ATTRIBUTE10 => l_sua_rec.attribute10,
X_ATTRIBUTE11 => l_sua_rec.attribute11,
X_ATTRIBUTE12 => l_sua_rec.attribute12,
X_ATTRIBUTE13 => l_sua_rec.attribute13,
X_ATTRIBUTE14 => l_sua_rec.attribute14,
X_ATTRIBUTE15 => l_sua_rec.attribute15,
X_ATTRIBUTE16 => l_sua_rec.attribute16,
X_ATTRIBUTE17 => l_sua_rec.attribute17,
X_ATTRIBUTE18 => l_sua_rec.attribute18,
X_ATTRIBUTE19 => l_sua_rec.attribute19,
X_ATTRIBUTE20 => l_sua_rec.attribute20,
-- WLST_PRIORITY_WEIGHT_NUM and WLST_PREFERENCE_WEIGHT_NUM added by ptandon 1-SEP-2003. Enh Bug# 3052426
X_WLST_PRIORITY_WEIGHT_NUM => l_sua_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_sua_rec.wlst_preference_weight_num,
-- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
X_CORE_INDICATOR_CODE => l_sua_rec.core_indicator_code,
X_UPD_AUDIT_FLAG => l_sua_rec.UPD_AUDIT_FLAG ,
X_SS_SOURCE_IND => l_sua_rec.SS_SOURCE_IND
);
p_selected_units IN VARCHAR2,
p_enrollment_category IN VARCHAR2,
p_enr_method_type IN VARCHAR2,
p_comm_type IN VARCHAR2,
p_succ_suas OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_calling_obj IN VARCHAR2) AS
-------------------------------------------------------------------------------------------
--Created by : Basanth Kumar D, Oracle IDC
--Date created: 29-JUL-2005
--Purpose :
-- This procedure creates unconfirm unit attempts for the passed in list of uoo_ids.
-- The API tries to reserve seats in all cases except when the special/audit persmission step
-- is deny or when no seats are available.
--Change History:
--Who When What
-------------------------------------------------------------------------------------------
-- cursor to check wheter a unit attempt exists
CURSOR chk_sua_exists (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE)IS
SELECT 'X'
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_status <> 'DROPPED';
p_selected_units
);
SELECT DISTINCT sua.uoo_id, sua.sup_unit_cd, sua.no_assessment_ind
FROM igs_en_su_attempt sua, igs_ca_teach_to_load_v lod
WHERE sua.person_id = p_person_Id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'UNCONFIRM'
AND lod.teach_cal_type = sua.cal_type
AND lod.teach_ci_sequence_number = sua.ci_sequence_number
AND lod.load_cal_type = p_load_cal_type
AND lod.load_ci_sequence_number = p_load_sequence_number
AND sua.SS_SOURCE_IND = 'S'
ORDER BY sua.sup_unit_cd DESC;
SELECT DISTINCT sua.uoo_id, sua.sup_unit_cd
FROM igs_en_su_attempt sua, igs_ca_teach_to_load_v lod
WHERE sua.person_id = p_person_Id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status = 'UNCONFIRM'
AND sua.SS_SOURCE_IND <> 'S'
AND lod.teach_cal_type = sua.cal_type
AND lod.teach_ci_sequence_number = sua.ci_sequence_number
AND lod.load_cal_type = p_load_cal_type
AND lod.load_ci_sequence_number = p_load_sequence_number
ORDER BY sua.sup_unit_cd DESC;
SELECT unit_attempt_status,unit_cd,version_number
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = cp_uoo_id;
'SELECT SUA.uoo_id
FROM igs_en_su_attempt sua, igs_ca_load_to_teach_v load
WHERE person_id = :p_person_id
AND course_cd = :p_course_cd
AND unit_attempt_status IN (''ENROLLED'',''INVALID'')
AND cal_type = teach_cal_type
AND ci_sequence_number = teach_ci_sequence_number
AND load_cal_type = :p_load_cal_type
AND load_ci_sequence_number = :p_load_sequence_number
AND (uoo_id NOT IN('|| NVL(l_uoo_ids,'-999') ||'))' USING p_person_id,p_course_cd,p_load_cal_type,p_load_sequence_number;
PROCEDURE delete_cart_error_records(p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER
) AS
-------------------------------------------------------------------------------------------
--Created by : Chandrasekhar Kasu, Oracle IDC
--Date created: 17-OCT-2005
-- Purpose : creates student unit attempt.
--Change History:
--Who When What
--ckasu 18-OCT-2005 created as a part of bug#4674099 inorder to delete the cart error
-- records present in Planning sheet table
-------------------------------------------------------------------------------------------
CURSOR c_get_rowid IS
SELECT ROWID
FROM igs_en_plan_units
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND cart_error_flag = 'Y';
SELECT ROWID
FROM IGS_EN_STD_WARNINGS
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND step_type <> 'DROP';
IGS_EN_PLAN_UNITS_PKG.delete_row(x_rowid => c_get_plan_err_rec.ROWID);
IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => c_ss_warn_rec.rowid);
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.delete_plan_error_records');
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.delete_plan_error_records :',SQLERRM);
END delete_cart_error_records;
PROCEDURE add_selected_units (
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_course_version IN NUMBER,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN NUMBER,
p_uoo_ids IN VARCHAR2,
p_calling_obj IN VARCHAR2,
p_validate_person_step IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2,
p_deny_warn OUT NOCOPY VARCHAR2,
p_ss_session_id IN NUMBER) AS
-------------------------------------------------------------------------------------------
--Created by : Basanth Kumar D, Oracle IDC
--Date created: 29-JUL-2005
--Purpose : This procedure gets uooids passed reordered (superior/subordinate/others units)
-- Then it gets the perm/waitlsit deatils of the units
-- For 'PLAN' it validates the steps required for the passed units
-- For others it creates unocnfirm units which are later updated to enroll units
-- Finally depending on the calling object, return status and p_deny_warn values
-- it is rolled back to diff savepoints if required.
--Change History:
--Who When What
-------------------------------------------------------------------------------------------
l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
delete_ss_warnings(p_person_id,
p_course_cd,
p_load_cal_type,
p_load_sequence_number,
NULL, -- uoo_id
NULL, -- message_for
l_steps); -- steps to be considered while delete warnings records agsinst the person context
delete_ss_warnings(p_person_id,
p_course_cd,
p_load_cal_type,
p_load_sequence_number,
NULL, -- uoo_id
NULL, -- message_for
l_steps); -- -- steps to be considered while delete warnings records agsinst the person context
update_warnings_table(p_person_id,
p_course_cd ,
p_load_cal_type,
p_load_sequence_number,
p_calling_obj);
delete_cart_error_records(p_person_id,
p_course_cd,
p_load_cal_type,
p_load_sequence_number);
update_warnings_table(p_person_id,
p_course_cd ,
p_load_cal_type,
p_load_sequence_number,
p_calling_obj);
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.add_selected_units :',SQLERRM);
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_ADD_UNITS_API.add_selected_units');
END add_selected_units;
PROCEDURE delete_unrelated_warnings(p_person_id IN VARCHAR2,
p_course_cd IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_sequence_number IN VARCHAR2,
p_delete_message_count OUT NOCOPY NUMBER
) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT ROWID
FROM IGS_EN_STD_WARNINGS
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND term_cal_type = p_load_cal_type
AND term_ci_sequence_number = p_load_sequence_number
AND step_type <> 'PERSON';
p_delete_message_count := 0;
IGS_EN_STD_WARNINGS_PKG.delete_row(x_rowid => c_get_warnings_rec.ROWID);
p_delete_message_count := p_delete_message_count + 1;
FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_add_units_api.delete_unrelated_warnings :',SQLERRM);
FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_ADD_UNITS_API.delete_unrelated_warnings');
END delete_unrelated_warnings;