[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| c_sua_uai_v, c_todo and c_uai to select
| active (not closed) unit classes.
+======================================================================+*/
--
g_module_head VARCHAR2(30) := 'igs_as_gen_007';
SELECT suaai.person_id,
suaai.course_cd,
suaai.unit_cd,
suaai.cal_type,
suaai.ci_sequence_number,
suaai.ass_id,
suaai.creation_dt,
suaai.override_due_dt,
sua.version_number,
sua.uoo_id
FROM igs_as_su_atmpt_itm suaai,
igs_en_su_attempt sua,
igs_as_assessmnt_itm ai,
igs_as_unit_class ucl
WHERE suaai.person_id = NVL (p_person_id, suaai.person_id)
AND suaai.logical_delete_dt IS NULL
AND suaai.attempt_number = (SELECT MAX (attempt_number)
FROM igs_as_su_atmpt_itm suaai2
WHERE suaai2.person_id = suaai.person_id
AND suaai2.course_cd = suaai.course_cd
AND suaai2.uoo_id = suaai.uoo_id
AND suaai2.ass_id = suaai.ass_id)
AND suaai.tracking_id IS NULL
AND suaai.person_id = sua.person_id
AND suaai.course_cd = sua.course_cd
AND suaai.uoo_id = sua.uoo_id
AND sua.course_cd LIKE p_course_cd
AND sua.unit_cd LIKE p_unit_cd
AND sua.location_cd LIKE p_location_cd
AND sua.unit_class LIKE p_unit_class
AND sua.unit_class = ucl.unit_class
AND ucl.unit_mode LIKE p_unit_mode
AND ucl.closed_ind = 'N'
AND sua.unit_attempt_status = cst_enrolled
AND suaai.cal_type = NVL (p_teach_perd_cal_type, suaai.cal_type)
AND suaai.ass_id = NVL (p_ass_id, suaai.ass_id)
AND suaai.ci_sequence_number = NVL (p_teach_perd_sequence_number, suaai.ci_sequence_number)
AND igs_en_gen_014.enrs_get_within_ci (
p_acad_perd_cal_type,
p_acad_perd_sequence_number,
sua.cal_type,
sua.ci_sequence_number,
'Y'
) = 'Y'
AND suaai.ass_id = ai.ass_id
AND igs_as_gen_002.assp_get_ai_s_type (ai.ass_id) = 'ASSIGNMENT';
SELECT uai.due_dt,
uai.REFERENCE,
uai.location_cd,
uai.unit_class,
uai.unit_mode
FROM igs_en_su_attempt sua,
igs_as_unitass_item uai,
igs_as_unit_class uc
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.uoo_id = cp_uoo_id
AND uai.ass_id = cp_ass_id
AND uai.logical_delete_dt IS NULL
AND sua.unit_cd = uai.unit_cd
AND sua.version_number = uai.version_number
AND sua.cal_type = uai.cal_type
AND sua.ci_sequence_number = uai.ci_sequence_number
AND sua.unit_class = uc.unit_class
AND uc.closed_ind = 'N'
AND igs_as_val_uai.assp_val_sua_ai_acot (uai.ass_id, sua.person_id, sua.course_cd) = 'TRUE';
SELECT ROWID,
igs_as_su_atmpt_itm.*
FROM igs_as_su_atmpt_itm
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id
AND ass_id = cp_ass_id
AND creation_dt = cp_creation_dt
FOR UPDATE OF tracking_id NOWAIT;
SELECT 'x'
FROM igs_ge_s_log_entry
WHERE s_log_type = 'ASS3610'
AND creation_dt = cp_creation_dt
AND KEY = cp_key
AND text = cp_text;
SELECT start_dt,
business_days_ind
FROM igs_tr_item
WHERE tracking_id = cp_tracking_id;
SELECT MAX (action_dt)
FROM igs_tr_step_v
WHERE tracking_id = cp_tracking_id;
SELECT ROWID,
igs_tr_item.*
FROM igs_tr_item
WHERE tracking_id = v_tracking_id
FOR UPDATE OF business_days_ind NOWAIT;
igs_as_su_atmpt_itm_pkg.update_row (
x_rowid => c_lock_suaai_rec.ROWID,
x_person_id => c_lock_suaai_rec.person_id,
x_course_cd => c_lock_suaai_rec.course_cd,
x_unit_cd => c_lock_suaai_rec.unit_cd,
x_cal_type => c_lock_suaai_rec.cal_type,
x_ci_sequence_number => c_lock_suaai_rec.ci_sequence_number,
x_ass_id => c_lock_suaai_rec.ass_id,
x_creation_dt => c_lock_suaai_rec.creation_dt,
x_attempt_number => c_lock_suaai_rec.attempt_number,
x_outcome_dt => c_lock_suaai_rec.outcome_dt,
x_override_due_dt => c_lock_suaai_rec.override_due_dt,
x_tracking_id => v_tracking_id,
x_logical_delete_dt => c_lock_suaai_rec.logical_delete_dt,
x_s_default_ind => c_lock_suaai_rec.s_default_ind,
x_ass_pattern_id => NULL,
x_mode => 'R',
x_grading_schema_cd => c_lock_suaai_rec.grading_schema_cd,
x_gs_version_number => c_lock_suaai_rec.gs_version_number,
x_grade => c_lock_suaai_rec.grade,
x_outcome_comment_code => c_lock_suaai_rec.outcome_comment_code,
x_mark => c_lock_suaai_rec.mark,
x_attribute_category => c_lock_suaai_rec.attribute_category,
x_attribute1 => c_lock_suaai_rec.attribute1,
x_attribute2 => c_lock_suaai_rec.attribute2,
x_attribute3 => c_lock_suaai_rec.attribute3,
x_attribute4 => c_lock_suaai_rec.attribute4,
x_attribute5 => c_lock_suaai_rec.attribute5,
x_attribute6 => c_lock_suaai_rec.attribute6,
x_attribute7 => c_lock_suaai_rec.attribute7,
x_attribute8 => c_lock_suaai_rec.attribute8,
x_attribute9 => c_lock_suaai_rec.attribute9,
x_attribute10 => c_lock_suaai_rec.attribute10,
x_attribute11 => c_lock_suaai_rec.attribute11,
x_attribute12 => c_lock_suaai_rec.attribute12,
x_attribute13 => c_lock_suaai_rec.attribute13,
x_attribute14 => c_lock_suaai_rec.attribute14,
x_attribute15 => c_lock_suaai_rec.attribute15,
x_attribute16 => c_lock_suaai_rec.attribute16,
x_attribute17 => c_lock_suaai_rec.attribute17,
x_attribute18 => c_lock_suaai_rec.attribute18,
x_attribute19 => c_lock_suaai_rec.attribute19,
x_attribute20 => c_lock_suaai_rec.attribute20,
x_uoo_id => c_lock_suaai_rec.uoo_id,
x_unit_section_ass_item_id => c_lock_suaai_rec.unit_section_ass_item_id,
x_unit_ass_item_id => c_lock_suaai_rec.unit_ass_item_id,
x_sua_ass_item_group_id => c_lock_suaai_rec.sua_ass_item_group_id,
x_midterm_mandatory_type_code => c_lock_suaai_rec.midterm_mandatory_type_code,
x_midterm_weight_qty => c_lock_suaai_rec.midterm_weight_qty,
x_final_mandatory_type_code => c_lock_suaai_rec.final_mandatory_type_code,
x_final_weight_qty => c_lock_suaai_rec.final_weight_qty,
x_submitted_date => c_lock_suaai_rec.submitted_date,
x_waived_flag => c_lock_suaai_rec.waived_flag,
x_penalty_applied_flag => c_lock_suaai_rec.penalty_applied_flag
);
igs_tr_item_pkg.update_row (
x_rowid => v_tri_upd_rec.ROWID,
x_tracking_id => v_tri_upd_rec.tracking_id,
x_tracking_status => v_tri_upd_rec.tracking_status,
x_tracking_type => v_tri_upd_rec.tracking_type,
x_source_person_id => v_tri_upd_rec.source_person_id,
x_start_dt => v_tri_upd_rec.start_dt,
x_target_days => v_target_days,
x_sequence_ind => v_tri_upd_rec.sequence_ind,
x_business_days_ind => v_tri_upd_rec.business_days_ind,
x_originator_person_id => v_tri_upd_rec.originator_person_id,
x_s_created_ind => v_tri_upd_rec.s_created_ind,
x_completion_due_dt => v_tri_upd_rec.completion_due_dt,
x_override_offset_clc_ind => v_tri_upd_rec.override_offset_clc_ind,
x_publish_ind => v_tri_upd_rec.publish_ind,
x_mode => 'R' --v_tri_upd_rec.mode
);
SELECT uai.REFERENCE
INTO v_uai_reference
FROM igs_en_su_attempt sua,
igs_as_unitass_item uai,
igs_as_unit_class uc
WHERE sua.person_id = v_suaai_rec.person_id
AND sua.course_cd = v_suaai_rec.course_cd
AND sua.uoo_id = v_suaai_rec.uoo_id
AND uai.ass_id = v_suaai_rec.ass_id
AND uai.logical_delete_dt IS NULL
AND sua.unit_cd = uai.unit_cd
AND sua.version_number = uai.version_number
AND sua.cal_type = uai.cal_type
AND sua.ci_sequence_number = uai.ci_sequence_number
AND sua.unit_class = uc.unit_class
AND uc.closed_ind = 'N'
AND igs_as_val_uai.assp_val_sua_ai_acot (uai.ass_id, sua.person_id, sua.course_cd) = 'TRUE';
p_new_update_who IN VARCHAR2,
p_new_update_on IN DATE,
p_old_grading_schema_cd IN VARCHAR2,
p_old_version_number IN NUMBER,
p_old_grade IN VARCHAR2,
p_old_s_grade_crtn_mthd_tp IN VARCHAR2,
p_old_finalised_outcome_ind IN VARCHAR2,
p_old_mark IN NUMBER,
p_old_number_times_keyed IN NUMBER,
p_old_trnsltd_grdng_schema_cd IN VARCHAR2,
p_old_trnsltd_version_number IN NUMBER,
p_old_translated_grade IN VARCHAR2,
p_old_translated_dt IN DATE,
p_old_update_who IN VARCHAR2,
p_old_update_on IN DATE,
p_uoo_id IN NUMBER
) IS
gv_other_detail VARCHAR2 (255);
v_suaoh_rec.hist_start_dt := p_old_update_on;
v_suaoh_rec.hist_end_dt := p_new_update_on;
v_suaoh_rec.hist_who := p_old_update_who;
igs_as_su_atmptout_h_pkg.insert_row (
x_rowid => x_rowid,
x_org_id => l_org_id,
x_person_id => v_suaoh_rec.person_id,
x_course_cd => v_suaoh_rec.course_cd,
x_unit_cd => v_suaoh_rec.unit_cd,
x_cal_type => v_suaoh_rec.cal_type,
x_ci_sequence_number => v_suaoh_rec.ci_sequence_number,
x_outcome_dt => v_suaoh_rec.outcome_dt,
x_hist_start_dt => v_suaoh_rec.hist_start_dt,
x_hist_end_dt => v_suaoh_rec.hist_end_dt,
x_hist_who => v_suaoh_rec.hist_who,
x_grading_schema_cd => v_suaoh_rec.grading_schema_cd,
x_version_number => v_suaoh_rec.version_number,
x_grade => v_suaoh_rec.grade,
x_s_grade_creation_method_type => v_suaoh_rec.s_grade_creation_method_type,
x_finalised_outcome_ind => v_suaoh_rec.finalised_outcome_ind,
x_mark => v_suaoh_rec.mark,
x_number_times_keyed => v_suaoh_rec.number_times_keyed,
x_translated_grading_schema_cd => v_suaoh_rec.translated_grading_schema_cd,
x_translated_version_number => v_suaoh_rec.translated_version_number,
x_translated_grade => v_suaoh_rec.translated_grade,
x_translated_dt => v_suaoh_rec.translated_dt,
x_mode => 'R',
x_uoo_id => p_uoo_id,
x_mark_capped_flag => v_suaoh_rec.mark_capped_flag,
x_show_on_academic_histry_flag => v_suaoh_rec.show_on_academic_histry_flag,
x_release_date => v_suaoh_rec.release_date,
x_manual_override_flag => v_suaoh_rec.manual_override_flag
);
cst_ass_insert CONSTANT VARCHAR2 (10) := 'ASS_INSERT';
v_delete_todo BOOLEAN;
SELECT st.person_id,
str.s_student_todo_type,
str.sequence_number,
str.reference_number,
str.course_cd,
str.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.location_cd,
sua.unit_class,
uc.unit_mode,
st.todo_dt,
sua.uoo_id
FROM igs_pe_std_todo st,
igs_pe_std_todo_ref str,
igs_en_su_attempt_all sua,
igs_as_unit_class uc,
igs_ca_inst_rel cir
WHERE ((NVL (p_person_id, 9999999999) = 9999999999)
OR (st.person_id = p_person_id)
)
AND st.logical_delete_dt IS NULL
AND st.todo_dt <= SYSDATE
AND st.s_student_todo_type IN (cst_ass_insert, cst_ass_status, cst_ass_change)
AND st.person_id = str.person_id
AND st.s_student_todo_type = str.s_student_todo_type
AND st.sequence_number = str.sequence_number
AND str.logical_delete_dt IS NULL
AND str.course_cd LIKE p_course_cd
AND str.unit_cd LIKE p_unit_cd
AND str.uoo_id = NVL (p_uoo_id, str.uoo_id)
AND ((NVL (p_teach_perd_cal_type, 'x') = 'x')
OR (str.cal_type = p_teach_perd_cal_type)
)
AND ((NVL (p_teach_perd_sequence_number, 0) = 0)
OR (str.ci_sequence_number = p_teach_perd_sequence_number)
)
AND sua.person_id = str.person_id
AND sua.course_cd = str.course_cd
AND sua.uoo_id = str.uoo_id
AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
AND cir.sup_cal_type = p_acad_perd_cal_type
AND cir.sup_ci_sequence_number = p_acad_perd_sequence_number
AND cir.sub_cal_type = sua.cal_type
AND cir.sub_ci_sequence_number = sua.ci_sequence_number
AND uc.unit_class = sua.unit_class
AND uc.closed_ind = 'N'
ORDER BY st.person_id,
str.unit_cd,
st.todo_dt;
SELECT uai.ass_id
FROM igs_ps_unitass_item uai
WHERE uai.uoo_id = cp_uoo_id
AND uai.logical_delete_dt IS NULL;
v_delete_todo := TRUE;
IF v_todo_rec.s_student_todo_type IN (cst_ass_change, cst_ass_insert)
AND v_delete_todo = TRUE THEN
--
-- Check if any active Assessment Items are setup at Unit Section level
-- If Yes, then attach the Unit Section Assessment Item Groups and
-- Unit Section Assessment Items to the student
-- If No, then attach the Unit Assessment Item Groups and Unit
-- Assessment Items to the student
--
OPEN c_usc_ass (v_todo_rec.uoo_id);
v_delete_todo := FALSE;
v_delete_todo := FALSE;
IF v_delete_todo = TRUE THEN
--
-- Logically delete the IGS_PE_STD_TODO_REF table and determine if it was the
-- last IGS_PE_STD_TODO_REF item for the IGS_PE_STD_TODO entry. If so, then
-- logically delete the IGS_PE_STD_TODO entry.
-- If a lock occurs on the item, rollback the whole event for
-- this todo item. (Will also need a commit so that it can be restartable.)
--
IF igs_ge_gen_003.genp_upd_str_lgc_del (
v_todo_rec.person_id,
v_todo_rec.s_student_todo_type,
v_todo_rec.sequence_number,
v_todo_rec.reference_number,
v_message_name
) = FALSE THEN
--
-- Log the exception to the system log table.
--
igs_ge_ins_sle.genp_set_log_entry (
cst_ass3212,
v_key,
v_sle_key,
'IGS_AS_UNABLE_LOGDEL_STUD_TOD', -- Error, unable to logically delele item.
cst_error_stdnt_todo
);
SELECT usai.rowid row_id,
uai.unit_cd,
uai.version_number,
uai.cal_type,
uai.ci_sequence_number,
usai.uoo_id,
usai.unit_section_ass_item_id ass_item_id,
usai.us_ass_item_group_id group_id,
usai.ass_id ass_id,
usai.ass_id assessment_id,
usai.sequence_number,
uai.location_cd,
uai.unit_class,
uc.unit_mode,
usai.logical_delete_dt,
'USEC' record_ind,
usai.midterm_mandatory_type_code,
usai.midterm_weight_qty,
usai.final_mandatory_type_code,
usai.final_weight_qty,
usai.grading_schema_cd,
usai.gs_version_number
FROM igs_ps_unitass_item usai,
igs_as_assessmnt_itm ai,
igs_ps_unit_ofr_opt uai,
igs_as_unit_class uc,
igs_ca_inst_rel cir
WHERE usai.uoo_id = uai.uoo_id
AND uai.unit_class = uc.unit_class
AND uc.closed_ind = 'N'
AND usai.ass_id = ai.ass_id
AND (p_teach_perd_cal_type IS NULL
OR uai.cal_type = p_teach_perd_cal_type
)
AND (p_teach_perd_sequence_number IS NULL
OR uai.ci_sequence_number = p_teach_perd_sequence_number
)
AND uai.unit_cd LIKE p_unit_cd
AND cir.sup_cal_type = p_acad_perd_cal_type
AND cir.sup_ci_sequence_number = p_acad_perd_sequence_number
AND cir.sub_cal_type = uai.cal_type
AND cir.sub_ci_sequence_number = uai.ci_sequence_number
AND (p_version_number IS NULL
OR uai.version_number = p_version_number
)
AND (p_assessment_type IS NULL
OR ai.assessment_type LIKE p_assessment_type
)
AND (p_ass_id IS NULL
OR usai.ass_id = p_ass_id
)
AND usai.action_dt <= SYSDATE
AND EXISTS ( SELECT 'X'
FROM igs_ps_unit_ver uv,
igs_ps_unit_stat us
WHERE uv.unit_cd = uai.unit_cd
AND uv.version_number = uai.version_number
AND uv.unit_status = us.unit_status
AND us.s_unit_status = 'ACTIVE')
UNION ALL
SELECT uai.rowid row_id,
uai.unit_cd,
uai.version_number,
uai.cal_type,
uai.ci_sequence_number,
TO_NUMBER (NULL) uoo_id,
uai.unit_ass_item_id ass_item_id,
uai.unit_ass_item_group_id group_id,
uai.ass_id,
uai.ass_id assessment_id,
uai.sequence_number,
NULL,
NULL,
NULL,
uai.logical_delete_dt,
'UNIT' record_ind,
uai.midterm_mandatory_type_code,
uai.midterm_weight_qty,
uai.final_mandatory_type_code,
uai.final_weight_qty,
uai.grading_schema_cd,
uai.gs_version_number
FROM igs_as_unitass_item uai,
igs_as_assessmnt_itm ai,
igs_ca_inst_rel cir
WHERE uai.ass_id = ai.ass_id
AND (p_teach_perd_cal_type IS NULL
OR uai.cal_type = p_teach_perd_cal_type
)
AND (p_teach_perd_sequence_number IS NULL
OR uai.ci_sequence_number = p_teach_perd_sequence_number
)
AND uai.unit_cd LIKE p_unit_cd
AND cir.sup_cal_type = p_acad_perd_cal_type
AND cir.sup_ci_sequence_number = p_acad_perd_sequence_number
AND cir.sub_cal_type = uai.cal_type
AND cir.sub_ci_sequence_number = uai.ci_sequence_number
AND (p_version_number IS NULL
OR uai.version_number = p_version_number
)
AND (p_assessment_type IS NULL
OR ai.assessment_type LIKE p_assessment_type
)
AND (p_ass_id IS NULL
OR uai.ass_id = p_ass_id
)
AND uai.action_dt <= SYSDATE
AND EXISTS ( SELECT 'X'
FROM igs_ps_unit_ver uv,
igs_ps_unit_stat us
WHERE uv.unit_cd = uai.unit_cd
AND uv.version_number = uai.version_number
AND uv.unit_status = us.unit_status
AND us.s_unit_status = 'ACTIVE')
ORDER BY unit_cd,
version_number,
cal_type,
ci_sequence_number,
group_id,
assessment_id;
PROCEDURE asspl_prc_update_uai (
p_unit_cd igs_as_unitass_item.unit_cd%TYPE,
p_version_number igs_as_unitass_item.version_number%TYPE,
p_cal_type igs_as_unitass_item.cal_type%TYPE,
p_ci_sequence_number igs_as_unitass_item.ci_sequence_number%TYPE,
p_ass_id igs_as_unitass_item.ass_id%TYPE,
p_sequence_number igs_as_unitass_item.sequence_number%TYPE,
p_session_id igs_ge_s_log.KEY%TYPE,
p_log_dt IN OUT NOCOPY DATE
) IS
gv_other_detail VARCHAR2 (255);
BEGIN -- asspl_prc_update_uai
-- Select the IGS_AS_UNITASS_ITEM table for update NOWAIT and set
-- the action date to null.
-- If a lock occurs, then commit the processing anyway but report on
-- the exception. No need to rollback as processing completed. If the
-- item and action date is processed again then no changes will occur
-- but there will be processing done for nothing.
DECLARE
e_resource_busy EXCEPTION;
SELECT ROWID,
uai.*
FROM igs_as_unitass_item uai
WHERE uai.unit_cd = p_unit_cd
AND uai.version_number = p_version_number
AND uai.cal_type = p_cal_type
AND uai.ci_sequence_number = p_ci_sequence_number
AND uai.ass_id = p_ass_id
AND uai.sequence_number = p_sequence_number
FOR UPDATE OF action_dt NOWAIT;
igs_as_unitass_item_pkg.update_row (
x_rowid => v_uai_upd_rec.ROWID,
x_unit_ass_item_id => v_uai_upd_rec.unit_ass_item_id,
x_unit_cd => v_uai_upd_rec.unit_cd,
x_version_number => v_uai_upd_rec.version_number,
x_cal_type => v_uai_upd_rec.cal_type,
x_ci_sequence_number => v_uai_upd_rec.ci_sequence_number,
x_ass_id => v_uai_upd_rec.ass_id,
x_sequence_number => v_uai_upd_rec.sequence_number,
x_ci_start_dt => v_uai_upd_rec.ci_start_dt,
x_ci_end_dt => v_uai_upd_rec.ci_end_dt,
x_unit_class => v_uai_upd_rec.unit_class,
x_unit_mode => v_uai_upd_rec.unit_mode,
x_location_cd => v_uai_upd_rec.location_cd,
x_due_dt => v_uai_upd_rec.due_dt,
x_reference => v_uai_upd_rec.REFERENCE,
x_dflt_item_ind => v_uai_upd_rec.dflt_item_ind,
x_logical_delete_dt => v_uai_upd_rec.logical_delete_dt,
x_action_dt => NULL,
x_exam_cal_type => v_uai_upd_rec.exam_cal_type,
x_exam_ci_sequence_number => v_uai_upd_rec.exam_ci_sequence_number,
x_mode => 'R',
x_grading_schema_cd => v_uai_upd_rec.grading_schema_cd,
x_gs_version_number => v_uai_upd_rec.gs_version_number,
x_release_date => v_uai_upd_rec.release_date,
x_description => v_uai_upd_rec.description,
x_unit_ass_item_group_id => v_uai_upd_rec.unit_ass_item_group_id,
x_midterm_mandatory_type_code => v_uai_upd_rec.midterm_mandatory_type_code,
x_midterm_weight_qty => v_uai_upd_rec.midterm_weight_qty,
x_final_mandatory_type_code => v_uai_upd_rec.final_mandatory_type_code,
x_final_weight_qty => v_uai_upd_rec.final_weight_qty
);
fnd_message.set_token ('NAME', 'IGS_AS_GEN_007.asspl_prc_update_uai');
END asspl_prc_update_uai;
SELECT ROWID,
pai.*
FROM igs_ps_unitass_item pai
WHERE pai.uoo_id = p_uoo_id
AND pai.ass_id = p_ass_id
AND pai.sequence_number = p_sequence_number
FOR UPDATE OF action_dt NOWAIT;
igs_ps_unitass_item_pkg.update_row (
x_rowid => v_uai_upd_usec_rec.ROWID,
x_unit_section_ass_item_id => v_uai_upd_usec_rec.unit_section_ass_item_id,
x_uoo_id => v_uai_upd_usec_rec.uoo_id,
x_ass_id => v_uai_upd_usec_rec.ass_id,
x_sequence_number => v_uai_upd_usec_rec.sequence_number,
x_ci_start_dt => v_uai_upd_usec_rec.ci_start_dt,
x_ci_end_dt => v_uai_upd_usec_rec.ci_end_dt,
x_due_dt => v_uai_upd_usec_rec.due_dt,
x_reference => v_uai_upd_usec_rec.REFERENCE,
x_dflt_item_ind => v_uai_upd_usec_rec.dflt_item_ind,
x_logical_delete_dt => v_uai_upd_usec_rec.logical_delete_dt,
x_action_dt => NULL,
x_exam_cal_type => v_uai_upd_usec_rec.exam_cal_type,
x_exam_ci_sequence_number => v_uai_upd_usec_rec.exam_ci_sequence_number,
x_mode => 'R',
x_grading_schema_cd => v_uai_upd_usec_rec.grading_schema_cd,
x_gs_version_number => v_uai_upd_usec_rec.gs_version_number,
x_release_date => v_uai_upd_usec_rec.release_date,
x_description => v_uai_upd_usec_rec.description,
x_us_ass_item_group_id => v_uai_upd_usec_rec.us_ass_item_group_id,
x_midterm_mandatory_type_code => v_uai_upd_usec_rec.midterm_mandatory_type_code,
x_midterm_weight_qty => v_uai_upd_usec_rec.midterm_weight_qty,
x_final_mandatory_type_code => v_uai_upd_usec_rec.final_mandatory_type_code,
x_final_weight_qty => v_uai_upd_usec_rec.final_weight_qty
);
IF v_uai_rec.logical_delete_dt IS NOT NULL THEN
--
-- Perform processing to logically delete any assessment items for
-- students in the unit. If locking error occurs then set a flag
-- to roll back processing associated with the Unit Assessment Item
-- record so that all can be processed again at a later date.
-- Report on the exception.
-- Logically delete the associated suaai record for the Unit Assessment Item.
--
UPDATE igs_as_su_atmpt_itm suaai
SET suaai.logical_delete_dt = SYSDATE,
suaai.last_update_date = SYSDATE,
suaai.last_updated_by = fnd_global.user_id,
suaai.last_update_login = fnd_global.login_id,
suaai.request_id = fnd_global.conc_request_id,
suaai.program_id = fnd_global.conc_program_id,
suaai.program_application_id = fnd_global.prog_appl_id,
suaai.program_update_date = SYSDATE
WHERE suaai.uoo_id = NVL (v_uai_rec.uoo_id, suaai.uoo_id)
AND suaai.cal_type = v_uai_rec.cal_type
AND suaai.ci_sequence_number = v_uai_rec.ci_sequence_number
AND suaai.ass_id = v_uai_rec.ass_id
AND suaai.unit_cd = v_uai_rec.unit_cd
AND suaai.logical_delete_dt IS NULL
AND suaai.attempt_number = (SELECT MAX (suaai2.attempt_number)
FROM igs_as_su_atmpt_itm suaai2
WHERE suaai2.person_id = suaai.person_id
AND suaai2.course_cd = suaai.course_cd
AND suaai2.uoo_id = suaai.uoo_id
AND suaai2.ass_id = suaai.ass_id
AND (suaai2.unit_section_ass_item_id = suaai.unit_section_ass_item_id
OR suaai2.unit_ass_item_id = suaai.unit_ass_item_id))
AND (suaai.unit_section_ass_item_id = v_uai_rec.ass_item_id
OR suaai.unit_ass_item_id = v_uai_rec.ass_item_id)
AND EXISTS (
SELECT 'X'
FROM igs_en_su_attempt_all sua
WHERE sua.person_id = suaai.person_id
AND sua.course_cd = suaai.course_cd
AND sua.uoo_id = suaai.uoo_id
AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
);
ELSE -- uai.logical_delete_dt is NULL
--
-- Perform processing to insert/update the students assessment items.
-- Will first need to verify if the assessment item exists for a student,
-- that it is still valid. If not then logically delete it.
-- Will need to attempt to insert the item as it may not have existed
-- for the student in the first place.
-- If locking error occurs then set a flag to roll back processing
-- associated with the IGS_AS_UNITASS_ITEM record so that all can be
-- processed again at a later date. Report on the exception.
--
IF NOT igs_as_gen_005.assp_mnt_suaai_uai (
v_uai_rec.unit_cd,
v_uai_rec.version_number,
v_uai_rec.cal_type,
v_uai_rec.ci_sequence_number,
v_uai_rec.ass_id,
v_uai_rec.location_cd,
v_uai_rec.unit_class,
v_uai_rec.unit_mode,
cst_ass3213,
v_key,
v_sle_key,
v_error_count,
v_warning_count,
v_message_name,
v_uai_rec.record_ind,
v_uai_rec.ass_item_id ,
v_uai_rec.group_id,
v_uai_rec.midterm_mandatory_type_code ,
v_uai_rec.midterm_weight_qty ,
v_uai_rec.final_mandatory_type_code ,
v_uai_rec.final_weight_qty ,
v_uai_rec.grading_schema_cd ,
v_uai_rec.gs_version_number,
v_uai_rec.uoo_id
) THEN
-- Locking error has occurred, initialise the logging
-- structure so that the exception report does not
-- report on processing that will be rolled back.
-- Initialise the logging structure.
igs_ge_ins_sle.genp_set_log_cntr;
END IF; --uai.logical_delete_dt is NOT NULL
UPDATE igs_ps_unitass_item
SET action_dt = NULL,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
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 rowid = v_uai_rec.row_id;
UPDATE igs_as_unitass_item
SET action_dt = NULL,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
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 rowid = v_uai_rec.row_id;
cst_ass_insert CONSTANT VARCHAR2 (15) := 'ASS_INSERT';
v_s_student_todo_type := cst_ass_insert;
SELECT igs_pe_std_todo_ref_rf_num_s.NEXTVAL
INTO l_val
FROM DUAL;
igs_pe_std_todo_ref_pkg.insert_row (
x_rowid => lv_rowid,
x_person_id => p_person_id,
x_s_student_todo_type => v_s_student_todo_type,
x_sequence_number => v_return_val,
x_reference_number => l_val,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number,
x_course_cd => p_course_cd,
x_unit_cd => p_unit_cd,
x_other_reference => NULL,
x_logical_delete_dt => NULL,
x_mode => 'R',
x_uoo_id => p_uoo_id
);