[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
smvk 09-Jul-2004 Bug # 3676145. Modified the cursors c_suaai, c_sua to select active (not closed) unit classes.
shimitta 21-Feb-2006 Bug# 5042414.
sepalani 16-Aug-2006 Bug# 5469461
*/
FUNCTION assp_mnt_suaai_uap (
p_unit_cd IN VARCHAR2,
p_version_number IN NUMBER,
p_cal_type IN VARCHAR2,
p_ci_sequence_number IN NUMBER,
p_ass_pattern_id IN NUMBER,
p_location_cd IN VARCHAR2,
p_unit_class IN VARCHAR2,
p_unit_mode IN VARCHAR2,
p_s_log_type IN VARCHAR2,
p_key IN VARCHAR2,
p_sle_key IN OUT NOCOPY VARCHAR2,
p_error_count IN OUT NOCOPY NUMBER,
p_warning_count IN OUT NOCOPY NUMBER,
p_message_name OUT NOCOPY VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN FALSE;
SELECT 'x'
FROM igs_as_su_stmptout suao
WHERE suao.person_id = p_person_id
AND suao.course_cd = p_course_cd
AND suao.uoo_id = p_uoo_id
AND suao.outcome_dt < p_outcome_dt
AND suao.translated_dt IS NOT NULL;
SELECT cop.grading_schema_cd,
cop.gs_version_number
FROM igs_en_su_attempt sua,
igs_en_stdnt_ps_att sca,
igs_ps_unit_ofr_opt uoo,
igs_ps_ofr_pat cop
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.uoo_id = p_uoo_id
AND uoo.uoo_id = sua.uoo_id
AND uoo.grading_schema_prcdnce_ind = 'N'
AND sca.person_id = sua.person_id
AND sca.course_cd = sua.course_cd
AND cop.coo_id = sca.coo_id
AND cop.cal_type = cp_acad_cal_type
AND cop.ci_sequence_number = cp_acad_ci_seq_num
AND cop.grading_schema_cd IS NOT NULL
AND cop.gs_version_number IS NOT NULL;
SELECT gsgt.to_grade
FROM igs_as_grd_sch_trn gsgt
WHERE gsgt.grading_schema_cd = p_grading_schema_cd
AND gsgt.version_number = p_version_number
AND gsgt.grade = p_grade
AND gsgt.to_grading_schema_cd = cp_grading_schema_cd
AND gsgt.to_version_number = cp_gs_ver_num;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = p_unit_cd
AND version_number = p_version_number
AND cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND location_cd = p_location_cd
AND unit_class = p_unit_class;
SELECT suaai.ass_id,
suaai.unit_section_ass_item_id,
suaai.unit_ass_item_id
FROM igs_as_su_atmpt_itm suaai,
igs_en_su_attempt_all sua
WHERE suaai.person_id = cp_person_id
AND suaai.course_cd = cp_course_cd
AND suaai.uoo_id = cp_uoo_id
AND 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')
AND suaai.attempt_number = (SELECT MAX (attempt_number)
FROM igs_as_su_atmpt_itm suaai2
WHERE suaai2.person_id = cp_person_id
AND suaai2.course_cd = cp_course_cd
AND suaai2.uoo_id = cp_uoo_id
AND suaai2.ass_id = suaai.ass_id)
AND suaai.s_default_ind = cst_yes
AND suaai.logical_delete_dt IS NULL;
SELECT suv.ass_id
FROM igs_as_uai_sua_v suv
WHERE suv.person_id = cp_person_id
AND suv.course_cd = cp_course_cd
AND suv.uoo_id = cp_uoo_id
AND suv.ass_id = cp_ass_id
AND suv.uai_dflt_item_ind = cst_yes
AND suv.uai_logical_delete_dt IS NULL;
SELECT DISTINCT ass_id,
unit_ass_item_id,
unit_ass_item_group_id,
midterm_mandatory_type_code ,
midterm_weight_qty ,
final_mandatory_type_code ,
final_weight_qty ,
grading_schema_cd ,
gs_version_number
FROM igs_as_unitass_item
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND dflt_item_ind = cst_yes
AND logical_delete_dt IS NULL;
SELECT uai.ass_id
FROM igs_ca_stat cs,
igs_ca_inst ci,
igs_ps_unit_stat ust,
igs_ps_unit_ver uv,
igs_as_unitass_item uai
WHERE cs.s_cal_status <> 'INACTIVE'
AND ci.cal_status = cs.cal_status
AND uai.ci_sequence_number = ci.sequence_number
AND uai.cal_type = ci.cal_type
AND ust.s_unit_status <> 'INACTIVE'
AND uv.unit_status = ust.unit_status
AND uai.version_number = uv.version_number
AND uai.unit_cd = uv.unit_cd
AND uai.ass_id = cp_ass_id
AND uai.logical_delete_dt IS NULL
AND uai.action_dt IS NULL
FOR UPDATE OF uai.action_dt NOWAIT;
UPDATE igs_as_unitass_item uai
SET uai.action_dt = v_sysdate
WHERE CURRENT OF c_uai;
SELECT MIN
(SUBSTR
(igs_en_gen_014.enrs_get_acad_alt_cd (suav.cal_type,
suav.ci_sequence_number
),
1,
10
)
)
FROM igs_en_stdnt_ps_att sca,
igs_en_su_attempt suav,
igs_ps_ofr_pat cop,
igs_ca_inst ci
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd
AND sca.person_id = suav.person_id
AND sca.course_cd = suav.course_cd
AND EXISTS (
SELECT 'X'
FROM igs_en_su_attempt sua
WHERE sua.person_id = suav.person_id
AND sua.course_cd = suav.course_cd
AND sua.uoo_id = suav.uoo_id
AND igs_as_gen_001.assp_val_sua_display
(sua.person_id,
sua.course_cd,
sca.version_number,
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.unit_attempt_status,
sua.administrative_unit_status,
'Y',
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
sua.uoo_id
) = 'Y')
AND sca.coo_id = cop.coo_id
AND sca.location_cd = cop.location_cd
AND sca.attendance_mode = cop.attendance_mode
AND sca.attendance_type = cop.attendance_type
AND cop.cal_type = ci.cal_type
AND cop.ci_sequence_number = ci.sequence_number
AND igs_en_gen_014.enrs_get_within_ci (cop.cal_type,
cop.ci_sequence_number,
suav.cal_type,
suav.ci_sequence_number,
'Y'
) = 'Y';
SELECT MAX (SUBSTR (igs_en_gen_014.enrs_get_acad_alt_cd (suav.cal_type, suav.ci_sequence_number), 1, 10)) acad_alternate_code
FROM igs_en_stdnt_ps_att sca,
igs_en_su_attempt suav,
igs_ps_ofr_pat cop,
igs_ca_inst ci
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd
AND sca.person_id = suav.person_id
AND sca.course_cd = suav.course_cd
AND EXISTS ( SELECT 'X'
FROM igs_en_su_attempt sua
WHERE sua.person_id = suav.person_id
AND sua.course_cd = suav.course_cd
AND sua.uoo_id = suav.uoo_id
AND igs_as_gen_001.assp_val_sua_display (
sua.person_id,
sua.course_cd,
sca.version_number,
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.unit_attempt_status,
sua.administrative_unit_status,
'Y',
p_include_fail_grade_ind,
p_enrolled_units_ind,
p_exclude_research_units_ind,
p_exclude_unit_category,
sua.uoo_id
) = 'Y')
AND sca.coo_id = cop.coo_id
AND sca.location_cd = cop.location_cd
AND sca.attendance_mode = cop.attendance_mode
AND sca.attendance_type = cop.attendance_type
AND cop.cal_type = ci.cal_type
AND cop.ci_sequence_number = ci.sequence_number
AND igs_en_gen_014.enrs_get_within_ci (
cop.cal_type,
cop.ci_sequence_number,
suav.cal_type,
suav.ci_sequence_number,
'Y'
) = 'Y';
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = p_unit_cd
AND version_number = p_version_number
AND cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number;
SELECT sua.person_id,
sua.course_cd,
sua.location_cd,
sua.unit_class,
uc.unit_mode,
sua.uoo_id
FROM igs_en_su_attempt_all sua,
igs_as_unit_class uc
WHERE sua.uoo_id = NVL(p_uoo_id,sua.uoo_id) AND
sua.unit_cd = p_unit_cd
AND sua.cal_type = p_cal_type
AND sua.ci_sequence_number = p_ci_sequence_number
AND sua.version_number = p_version_number
AND sua.unit_attempt_status = cst_enrolled
AND uc.unit_class = sua.unit_class
AND uc.closed_ind = 'N'
AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED');
SELECT 'X'
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 unit_section_ass_item_id IS NOT NULL
AND ROWNUM = 1;
SELECT 'X' record_exists
FROM igs_as_course_type_all
WHERE ass_id = cp_ass_id;
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.unit_cd = p_unit_cd
AND suaai.cal_type = p_cal_type
AND suaai.ci_sequence_number = p_ci_sequence_number
AND suaai.ass_id = p_ass_id
AND suaai.logical_delete_dt IS NULL
AND (suaai.unit_section_ass_item_id = p_ass_item_id
OR suaai.unit_ass_item_id = p_ass_item_id)
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 EXISTS (
SELECT 'X'
FROM igs_en_su_attempt_all sua,
igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE suaai.person_id = sua.person_id
AND suaai.course_cd = sua.course_cd
AND suaai.uoo_id = sua.uoo_id
AND sua.person_id = sca.person_id
AND sua.course_cd = sca.course_cd
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number
AND sua.unit_attempt_status IN ('ENROLLED', 'UNCONFIRM', 'WAITLISTED')
AND EXISTS (
SELECT 'X'
FROM igs_as_course_type_all act
WHERE act.course_type <> crv.course_type
AND act.ass_id = suaai.ass_id
));
deleted and default items assigned for the new unit offering option. This
routine will return false and rollback any alteration if a lock exists when
attempting to logically delete an assessment item.
Known limitations,enhancements,remarks:
Change History
Who When What
DDEY as a part of enhancement Bug # 2162831
********************************************************************************************** */
--
cst_yes VARCHAR2 (1);
SELECT suv.ass_id, unit_section_ass_item_id, us_ass_item_group_id,
midterm_mandatory_type_code, midterm_weight_qty,
final_mandatory_type_code, final_weight_qty, grading_schema_cd,
gs_version_number
FROM igs_ps_unitass_item suv
WHERE suv.uoo_id = p_uoo_id
AND suv.dflt_item_ind = cst_yes
AND suv.logical_delete_dt IS NULL;