[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE igs_en_blk_sua_ints
SET ERROR_TXT=l_message,
status_flag=DECODE(p_level,'E','E','W1',status_flag,'S'), -- W1 is grp level warning..changing to S here will prevent c_bulk_suas from picking this upload
last_updated_by=fnd_global.user_id, last_update_login=fnd_global.login_id, last_update_date=SYSDATE,
request_id=fnd_global.conc_request_id, program_id=fnd_global.conc_program_id ,
program_application_id=fnd_global.prog_appl_id, program_update_date=SYSDATE
WHERE batch_id=p_batch_id
AND status_flag IN ('U','R','S')
AND person_number =p_person_number
AND program_cd = NVL(p_program_cd ,program_cd)
AND program_ver_num = NVL(p_program_ver,program_ver_num)
AND ((p_load_alt_code IS NULL) OR
((p_load_alt_code IS NOT NULL)AND
alternate_cd IN ( SELECT teach_alternate_code FROM igs_ca_load_to_teach_v
WHERE load_alternate_code=p_load_alt_code)));
UPDATE igs_en_blk_sua_ints SET ERROR_TXT=l_message , STATUS_FLAG=l_message_table(i).type,
last_updated_by = fnd_global.user_id, last_update_login =fnd_global.login_id,last_update_date=SYSDATE,
request_id=fnd_global.conc_request_id, program_id=fnd_global.conc_program_id ,program_application_id=fnd_global.prog_appl_id,
program_update_date=SYSDATE
WHERE upload_id=l_message_table(i).upload_id;
SELECT *
FROM IGS_EN_SU_ATTEMPT
WHERE person_id=cp_person_id
AND course_cd=cp_course_cd
AND uoo_id=cp_uoo_id;
igs_en_sua_api.update_unit_attempt (
X_ROWID => l_sua.row_id ,
X_PERSON_ID => l_sua.person_id ,
X_COURSE_CD => l_sua.course_cd ,
X_UNIT_CD => l_sua.unit_cd ,
X_CAL_TYPE => l_sua.cal_type ,
X_CI_SEQUENCE_NUMBER => l_sua.ci_sequence_number ,
X_VERSION_NUMBER => l_sua.version_number ,
X_LOCATION_CD => l_sua.location_cd ,
X_UNIT_CLASS => l_sua.unit_class ,
X_CI_START_DT => l_sua.ci_Start_dt ,
X_CI_END_DT => l_sua.ci_end_dt ,
X_UOO_ID => l_sua.uoo_id,
X_ENROLLED_DT => l_sua.enrolled_dt ,
X_UNIT_ATTEMPT_STATUS => l_sua.unit_attempt_status ,
X_ADMINISTRATIVE_UNIT_STATUS => l_sua.administrative_unit_status ,
X_DISCONTINUED_DT => l_sua.discontinued_dt,
X_RULE_WAIVED_DT => l_sua.rule_waived_dt ,
X_RULE_WAIVED_PERSON_ID => l_sua.rule_waived_person_id ,
X_NO_ASSESSMENT_IND => l_sua.no_assessment_ind ,
X_SUP_UNIT_CD => l_sua.sup_unit_cd ,
X_SUP_VERSION_NUMBER => l_sua.sup_version_number ,
X_EXAM_LOCATION_CD => l_sua.exam_location_cd ,
X_ALTERNATIVE_TITLE => l_sua.alternative_title ,
X_OVERRIDE_ENROLLED_CP => l_sua.override_enrolled_cp ,
X_OVERRIDE_EFTSU => l_sua.override_eftsu ,
X_OVERRIDE_ACHIEVABLE_CP => l_sua.override_achievable_cp ,
X_OVERRIDE_OUTCOME_DUE_DT => l_sua.override_outcome_due_dt ,
X_OVERRIDE_CREDIT_REASON => l_sua.override_credit_reason ,
X_ADMINISTRATIVE_PRIORITY => l_sua.administrative_priority ,
X_WAITLIST_DT => l_sua.waitlist_dt ,
X_DCNT_REASON_CD => l_sua.dcnt_reason_cd ,
X_MODE => 'R' ,
X_GS_VERSION_NUMBER => l_sua.gs_version_number ,
X_ENR_METHOD_TYPE => l_sua.enr_method_type ,
X_FAILED_UNIT_RULE => l_sua.failed_unit_rule ,
X_CART => l_sua.cart ,
X_RSV_SEAT_EXT_ID => l_sua.rsv_seat_ext_id ,
X_ORG_UNIT_CD => l_sua.org_unit_cd ,
X_GRADING_SCHEMA_CODE => l_sua.grading_schema_code,
X_SUBTITLE => l_sua.subtitle ,
X_SESSION_ID => l_sua.session_id ,
X_DEG_AUD_DETAIL_ID => l_sua.deg_aud_detail_id ,
X_STUDENT_CAREER_TRANSCRIPT => l_sua.student_career_transcript ,
X_STUDENT_CAREER_STATISTICS => l_sua.student_career_statistics ,
X_WAITLIST_MANUAL_IND => l_sua.waitlist_manual_ind ,
X_ATTRIBUTE_CATEGORY => l_sua.attribute_category ,
X_ATTRIBUTE1 => l_sua.attribute1 ,
X_ATTRIBUTE2 => l_sua.attribute2 ,
X_ATTRIBUTE3 => l_sua.attribute3 ,
X_ATTRIBUTE4 => l_sua.attribute4 ,
X_ATTRIBUTE5 => l_sua.attribute5 ,
X_ATTRIBUTE6 => l_sua.attribute6 ,
X_ATTRIBUTE7 => l_sua.attribute7 ,
X_ATTRIBUTE8 => l_sua.attribute8 ,
X_ATTRIBUTE9 => l_sua.attribute9 ,
X_ATTRIBUTE10 => l_sua.attribute10 ,
X_ATTRIBUTE11 => l_sua.attribute11 ,
X_ATTRIBUTE12 => l_sua.attribute12 ,
X_ATTRIBUTE13 => l_sua.attribute13 ,
X_ATTRIBUTE14 => l_sua.attribute14 ,
X_ATTRIBUTE15 => l_sua.attribute15 ,
X_ATTRIBUTE16 => l_sua.attribute16 ,
X_ATTRIBUTE17 => l_sua.attribute17 ,
X_ATTRIBUTE18 => l_sua.attribute18 ,
X_ATTRIBUTE19 => l_sua.attribute19 ,
X_ATTRIBUTE20 => l_sua.attribute20 ,
X_WLST_PRIORITY_WEIGHT_NUM => l_sua.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => l_sua.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => p_core_ind ); -- Set core indicator to new value from interface table
SELECT DISTINCT person_number
FROM igs_en_blk_sua_ints
WHERE batch_id=cp_batch_id
AND status_flag IN ('U','R')
ORDER BY person_number;
SELECT DISTINCT program_cd, program_ver_num
FROM igs_en_blk_sua_ints
WHERE batch_id=cp_batch_id
AND person_number=cp_person_number
AND status_flag IN ('U','R');
SELECT DISTINCT ttol.load_cal_type cal_type, ttol.load_ci_sequence_number
seq_num, ttol.load_alternate_code alt_cd,ttol.load_start_dt
FROM igs_en_blk_sua_ints sui, igs_ca_teach_to_load_v ttol
WHERE batch_id=cp_batch_id
AND person_number=cp_person_number
AND status_flag IN ('U','R')
AND program_cd=cp_program_cd
AND program_ver_num=cp_program_ver_num
AND ttol.teach_alternate_code=sui.alternate_cd
AND ttol.load_start_dt=
(SELECT MIN(cttol.load_start_dt) FROM igs_ca_teach_to_load_v cttol
WHERE cttol.teach_cal_type=ttol.teach_cal_type
AND cttol.teach_ci_sequence_number=ttol.teach_ci_sequence_number)
ORDER BY ttol.load_start_dt;
SELECT teach_cal_type,
teach_ci_sequence_number,
teach_alternate_code
FROM igs_ca_teach_to_load_v ttol
WHERE ttol.load_cal_type=cp_load_cal_type
AND ttol.load_ci_sequence_number=cp_load_seq_num;
SELECT sui.upload_id,
sui.unit_cd,
sui.unit_ver_num,
sui.location_cd,
sui.unit_class,
sui.alternate_cd,
sui.status_flag,
sui.audit_flag,
sui.grading_sch_cd,
sui.grading_sch_ver_num,
sui.variable_cr_point,
sui.unit_sec_sub_title ,
sui.alternate_cd alt_cd,
sui.core_indicator_code,
sui.update_core_flag,
enrp_get_uoo_info(sui.unit_cd,sui.unit_ver_num,cp_teach_cal_type,cp_teach_seq_num,sui.location_cd,sui.unit_class) uoo_info
FROM igs_en_blk_sua_ints sui
WHERE batch_id=cp_batch_id
AND person_number=cp_person_number
AND status_flag IN ('U','R')
AND program_cd=cp_program_cd
AND program_ver_num=cp_program_ver_num
AND sui.alternate_cd = cp_teach_alternate_cd
ORDER BY uoo_info DESC;
SELECT course_attempt_status, primary_program_type FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id
AND course_cd = cp_program_cd
AND version_number= cp_program_ver_num;
SELECT 'Y' FROM igs_en_su_attempt
WHERE person_id =cp_person_id
AND course_cd = cp_program_cd
AND uoo_id = cp_uoo_id
AND unit_attempt_status IN ('ENROLLED','UNCONFIRM','WAITLISTED');
SELECT sui.upload_id, sui.alternate_cd alt_cd
FROM igs_en_blk_sua_ints sui,igs_ca_inst_all cal
WHERE sui.batch_id=cp_batch_id
AND status_flag IN ('U','R')
AND cal.alternate_code(+)=sui.alternate_cd
AND cal.alternate_code IS NULL;
SELECT sui.upload_id, sui.alternate_cd alt_cd,cal.cal_type,cal.sequence_number
FROM igs_en_blk_sua_ints sui,
igs_ca_inst cal
WHERE sui.alternate_cd=cal.alternate_code
AND batch_id=cp_batch_id
AND status_flag IN ('U','R')
AND NOT EXISTS(SELECT load_cal_type,load_ci_sequence_number
FROM igs_ca_teach_to_load_v ttol
WHERE cal.cal_type=ttol.teach_cal_type
AND cal.sequence_number=ttol.teach_ci_sequence_number);
SELECT batch_desc
FROM igs_en_bat_sua_ints
WHERE batch_id=cp_batch_id;
CURSOR c_delete_bat (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE) IS
SELECT '1'
FROM igs_en_blk_sua_ints
WHERE batch_id=cp_batch_id;
enrpl_log_msg(p_level =>'W1', --W1 is group level warning before the sua is inserted..thus status should not be changed
p_message =>l_error_message,
p_batch_id => p_batch_id,
p_person_number =>l_bulk_persons.person_number,
p_program_cd =>l_bulk_programs.program_cd,
p_program_ver =>l_bulk_programs.program_ver_num,
p_load_alt_code =>l_bulk_load.alt_cd);
IF l_abort_loop=FALSE AND l_bulk_suas.update_core_flag='Y' THEN
IF FALSE=igs_lookups_view_pkg.get_pk_for_validation ('IGS_PS_CORE_IND', l_bulk_suas.core_indicator_code) THEN
enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_CORE_IND_INVALID');
IF l_bulk_suas.update_core_flag='Y' THEN
IF NVL(FND_PROFILE.VALUE('IGS_EN_CORE_VAL'),'N') = 'Y' THEN
--If profile is set then Derive core indicator from POS and log warning if it is being overriden
l_core_indicator_code:=Igs_En_Gen_009.enrp_check_usec_core (p_person_id =>l_person_id,
p_program_cd =>l_bulk_programs.program_cd,
p_uoo_id =>l_uoo_id);
enrpl_unit_msg('S',l_bulk_suas.upload_id,'IGS_EN_SUA_UPDATE_SUCC');
IF l_bulk_suas.update_core_flag='Y' THEN
IF l_core_indicator_code <> l_bulk_suas.core_indicator_code AND NVL(FND_PROFILE.VALUE('IGS_EN_CORE_VAL'),'N') = 'Y' THEN
enrpl_unit_msg('W',l_bulk_suas.upload_id,'IGS_EN_POS_MATCH');
IF l_abort_loop=FALSE THEN --Core indicator populated appropriately, proceed to insert sua
SAVEPOINT bulk_sua_upload;
igs_ss_en_wrappers.insert_into_enr_worksheet(p_person_number =>l_bulk_persons.person_number,
p_course_cd =>l_bulk_programs.program_cd,
p_uoo_id =>l_uoo_id,
p_waitlist_ind =>l_waitlist_ind,
p_session_id =>NULL,
p_return_status =>l_ret_status,
p_message =>l_error_message,
p_cal_type =>l_bulk_load.cal_type,
p_ci_sequence_number =>l_bulk_load.seq_num,
p_audit_requested =>l_bulk_suas.audit_flag,
p_enr_method =>l_enr_method,
p_override_cp =>l_bulk_suas.variable_cr_point,
p_subtitle =>l_bulk_suas.unit_sec_sub_title,
p_gradsch_cd =>l_bulk_suas.grading_sch_cd,
p_gs_version_num =>l_bulk_suas.grading_sch_ver_num,
p_core_indicator_code =>l_core_indicator_code,
p_calling_obj =>'JOB');
DELETE FROM igs_en_blk_sua_ints WHERE batch_id=p_batch_id AND status_flag='S';
OPEN c_delete_bat (p_batch_id);
FETCH c_delete_bat INTO l_dummy;
IF c_delete_bat%NOTFOUND THEN
DELETE FROM igs_en_bat_sua_ints WHERE batch_id=p_batch_id;
CLOSE c_delete_bat ;
SELECT NVL(relation_type,'NONE')||'*'||uoo_id||'*'||unit_section_status||'*'||NVL(auditable_ind,'N')||'*'||sup_uoo_id uoo_info
-- Gives all required uoo information
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = cp_unit_cd AND version_number = cp_unit_ver
AND cal_type = cp_cal_type AND ci_sequence_number = cp_ci_sequence_number
AND location_cd = cp_location_cd AND unit_class = cp_unit_class;
SELECT unit_cd
FROM igs_ps_unit_ofr_opt
WHERE uoo_id=cp_uoo_id;