[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| select active (not closed) unit classes. |
| ijeddy 11/28/2005 Bug 4763207, modified c_suaai_an. |
+======================================================================+*/
--
-- Bug No. 1956374 Procedure assp_val_suaai_ins reference is changed
--
PROCEDURE asss_ins_transcript (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_course_org_unit_cd IN VARCHAR2,
p_course_group_cd IN VARCHAR2,
p_course_cd IN VARCHAR2,
p_course_location_cd IN VARCHAR2,
p_course_attendance_mode IN VARCHAR2,
p_course_award IN VARCHAR2 DEFAULT 'BOTH',
p_course_attempt_status IN VARCHAR2,
p_progression_status IN VARCHAR2,
p_graduand_status IN VARCHAR2,
p_person_id_group IN NUMBER,
p_person_id IN NUMBER,
p_transcript_type IN VARCHAR2,
p_include_fail_grades_ind IN VARCHAR2 DEFAULT 'N',
p_enrolled_units_ind IN VARCHAR2 DEFAULT 'C',
p_exclude_research_units_ind IN VARCHAR2 DEFAULT 'N',
p_exclude_unit_category IN VARCHAR2,
p_extract_course_cd IN VARCHAR2,
p_include_related_crs_ind IN VARCHAR2 DEFAULT 'N',
p_order_by IN VARCHAR2 DEFAULT 'YEAR',
p_external_order_by IN VARCHAR2 DEFAULT 'SURNAME',
p_correspondence_ind IN VARCHAR2 DEFAULT 'N',
p_org_id IN NUMBER
) IS
BEGIN
--
retcode := 0;
v_inserted_flag BOOLEAN DEFAULT FALSE;
SELECT 'x'
FROM igs_as_exmvnu_sesavl
WHERE venue_cd = p_venue_cd
AND exam_cal_type = p_exam_cal_type
AND exam_ci_sequence_number = p_exam_ci_sequence_number;
SELECT exam_cal_type,
exam_ci_sequence_number,
dt_alias,
dai_sequence_number,
start_time,
end_time,
ese_id,
comments
FROM igs_as_exam_session
WHERE exam_cal_type = p_exam_cal_type
AND exam_ci_sequence_number = p_exam_ci_sequence_number;
v_inserted_flag := TRUE;
igs_as_exmvnu_sesavl_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_org_id => l_org_id,
x_venue_cd => p_venue_cd,
x_exam_cal_type => v_es_rec.exam_cal_type,
x_exam_ci_sequence_number => v_es_rec.exam_ci_sequence_number,
x_dt_alias => v_es_rec.dt_alias,
x_dai_sequence_number => v_es_rec.dai_sequence_number,
x_start_time => v_es_rec.start_time,
x_end_time => v_es_rec.end_time,
x_ese_id => v_es_rec.ese_id,
x_comments => v_es_rec.comments
);
IF (v_inserted_flag = FALSE) THEN
-- no sessions found
p_message_name := 'IGS_AS_NOEXAM_SESSIONS_FOUND';
v_select_statuses VARCHAR2 (50);
SELECT 'x'
FROM DUAL
WHERE EXISTS ( SELECT *
FROM igs_as_ins_grd_entry iaige
WHERE iaige.keying_time = cp_keying_time
AND iaige.keying_who = cp_keying_who);
SELECT 'x'
FROM DUAL
WHERE EXISTS ( SELECT *
FROM igs_as_msht_su_atmpt iamsa
WHERE iamsa.sheet_number = cp_mark_sheet);
SELECT p_keying_who,
v_keying_time,
mssua.student_sequence,
mssua.person_id,
pe.person_last_name surname,
mssua.course_cd,
mssua.unit_cd,
sua.version_number,
mssua.cal_type,
mssua.ci_sequence_number,
sua.location_cd,
sua.unit_class,
sua.unit_attempt_status,
NULL n1,
NULL n2,
NULL n3,
NULL n4,
'N'
FROM igs_as_msht_su_atmpt mssua,
hz_parties pe,
igs_en_su_attempt sua
WHERE mssua.sheet_number = p_sheet_number
AND pe.party_id = mssua.person_id
AND sua.person_id(+) = mssua.person_id
AND sua.course_cd(+) = mssua.course_cd
AND sua.uoo_id(+) = mssua.uoo_id;
igs_as_ins_grd_entry_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_keying_who => c_grd_entry_tmp_rec.p_keying_who,
x_keying_time => c_grd_entry_tmp_rec.v_keying_time,
x_student_sequence => c_grd_entry_tmp_rec.student_sequence,
x_person_id => c_grd_entry_tmp_rec.person_id,
x_name => c_grd_entry_tmp_rec.surname,
x_course_cd => c_grd_entry_tmp_rec.course_cd,
x_unit_cd => c_grd_entry_tmp_rec.unit_cd,
x_version_number => c_grd_entry_tmp_rec.version_number,
x_cal_type => c_grd_entry_tmp_rec.cal_type,
x_ci_sequence_number => c_grd_entry_tmp_rec.ci_sequence_number,
x_location_cd => c_grd_entry_tmp_rec.location_cd,
x_unit_class => c_grd_entry_tmp_rec.unit_class,
x_unit_attempt_status => c_grd_entry_tmp_rec.unit_attempt_status,
x_mark => NULL,
x_grading_schema_cd => NULL,
x_gs_version_number => NULL,
x_grade => NULL,
x_specified_grade_ind => NULL
);
v_select_statuses := '''' || cst_enrolled || ''', ''' || cst_completed || '''';
v_select_statuses := '''' || cst_enrolled || ''', ''' || cst_discontin || ''', ''' || cst_completed || '''';
SELECT sua.location_cd,
sua.unit_class,
sua.person_id,
sua.course_cd,
sua.unit_attempt_status,
pe.person_last_name surname,
igs_ge_gen_002.genp_get_initials (pe.person_first_name),
sua.version_number
FROM igs_ps_unit_ofr_opt uoo,
igs_en_su_attempt sua,
hz_parties pe,
igs_as_unit_class ucl
WHERE uoo.unit_cd = p_unit_cd
AND uoo.cal_type = p_cal_type
AND uoo.ci_sequence_number = TO_CHAR (p_sequence_number)
AND uoo.location_cd LIKE v_location_cd
AND uoo.unit_class LIKE v_unit_class
AND ucl.unit_class = uoo.unit_class
AND ucl.unit_mode LIKE v_unit_mode
AND sua.uoo_id = uoo.uoo_id
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED')
AND pe.party_id = sua.person_id
AND ucl.closed_ind = 'N'
ORDER BY pe.party_number;
SELECT sua.location_cd,
sua.unit_class,
sua.person_id,
sua.course_cd,
sua.unit_attempt_status,
pe.person_last_name surname,
igs_ge_gen_002.genp_get_initials (pe.person_first_name),
sua.version_number
FROM igs_ps_unit_ofr_opt uoo,
igs_en_su_attempt sua,
hz_parties pe,
igs_as_unit_class ucl
WHERE uoo.unit_cd = p_unit_cd
AND uoo.cal_type = p_cal_type
AND uoo.ci_sequence_number = TO_CHAR (p_sequence_number)
AND uoo.location_cd LIKE v_location_cd
AND uoo.unit_class LIKE v_unit_class
AND ucl.unit_class = uoo.unit_class
AND ucl.unit_mode LIKE v_unit_mode
AND sua.uoo_id = uoo.uoo_id
AND sua.unit_attempt_status IN ('ENROLLED', 'DISCONTIN', 'COMPLETED')
AND pe.party_id = sua.person_id
AND ucl.closed_ind = 'N'
ORDER BY pe.party_number;
SELECT sua.location_cd,
sua.unit_class,
sua.person_id,
sua.course_cd,
sua.unit_attempt_status,
pe.person_last_name surname,
igs_ge_gen_002.genp_get_initials (pe.person_first_name),
sua.version_number
FROM igs_ps_unit_ofr_opt uoo,
igs_en_su_attempt sua,
hz_parties pe,
igs_as_unit_class ucl
WHERE uoo.unit_cd = cp_unit_cd
AND uoo.cal_type = cp_cal_type
AND uoo.ci_sequence_number = TO_CHAR (cp_sequence_number)
AND uoo.location_cd LIKE cp_location_cd
AND uoo.unit_class LIKE cp_unit_class
AND ucl.unit_class = uoo.unit_class
AND ucl.unit_mode LIKE cp_unit_mode
AND sua.uoo_id = uoo.uoo_id
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED')
AND pe.party_id = sua.person_id
AND ucl.closed_ind = 'N'
ORDER BY pe.person_last_name;
SELECT sua.location_cd,
sua.unit_class,
sua.person_id,
sua.course_cd,
sua.unit_attempt_status,
pe.person_last_name surname,
igs_ge_gen_002.genp_get_initials (pe.person_first_name),
sua.version_number
FROM igs_ps_unit_ofr_opt uoo,
igs_en_su_attempt sua,
hz_parties pe,
igs_as_unit_class ucl
WHERE uoo.unit_cd = cp_unit_cd
AND uoo.cal_type = cp_cal_type
AND uoo.ci_sequence_number = TO_CHAR (cp_sequence_number)
AND uoo.location_cd LIKE cp_location_cd
AND uoo.unit_class LIKE cp_unit_class
AND ucl.unit_class = uoo.unit_class
AND ucl.unit_mode LIKE cp_unit_mode
AND sua.uoo_id = uoo.uoo_id
AND sua.unit_attempt_status IN ('ENROLLED', 'DISCONTIN', 'COMPLETED')
AND pe.party_id = sua.person_id
AND ucl.closed_ind = 'N'
ORDER BY pe.person_last_name;
igs_as_ins_grd_entry_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_keying_who => p_keying_who,
x_keying_time => v_keying_time,
x_student_sequence => v_student_seq,
x_person_id => l_person_id,
x_name => l_name || ', ' || l_initials,
x_course_cd => l_course_cd,
x_unit_cd => p_unit_cd,
x_version_number => l_version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_sequence_number,
x_location_cd => l_location_cd,
x_unit_class => l_unit_class,
x_unit_attempt_status => l_unit_attempt_status,
x_mark => NULL,
x_grading_schema_cd => NULL,
x_gs_version_number => NULL,
x_grade => NULL,
x_specified_grade_ind => 'N'
);
igs_as_ins_grd_entry_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_keying_who => p_keying_who,
x_keying_time => v_keying_time,
x_student_sequence => v_student_seq,
x_person_id => l_person_id,
x_name => l_name || ', ' || l_initials,
x_course_cd => l_course_cd,
x_unit_cd => p_unit_cd,
x_version_number => l_version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_sequence_number,
x_location_cd => l_location_cd,
x_unit_class => l_unit_class,
x_unit_attempt_status => l_unit_attempt_status,
x_mark => NULL,
x_grading_schema_cd => NULL,
x_gs_version_number => NULL,
x_grade => NULL,
x_specified_grade_ind => 'N'
);
igs_as_ins_grd_entry_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_keying_who => p_keying_who,
x_keying_time => v_keying_time,
x_student_sequence => v_student_seq,
x_person_id => l_person_id,
x_name => l_name || ', ' || l_initials,
x_course_cd => l_course_cd,
x_unit_cd => p_unit_cd,
x_version_number => l_version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_sequence_number,
x_location_cd => l_location_cd,
x_unit_class => l_unit_class,
x_unit_attempt_status => l_unit_attempt_status,
x_mark => NULL,
x_grading_schema_cd => NULL,
x_gs_version_number => NULL,
x_grade => NULL,
x_specified_grade_ind => 'N'
);
igs_as_ins_grd_entry_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_keying_who => p_keying_who,
x_keying_time => v_keying_time,
x_student_sequence => v_student_seq,
x_person_id => l_person_id,
x_name => l_name || ', ' || l_initials,
x_course_cd => l_course_cd,
x_unit_cd => p_unit_cd,
x_version_number => l_version_number,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_sequence_number,
x_location_cd => l_location_cd,
x_unit_class => l_unit_class,
x_unit_attempt_status => l_unit_attempt_status,
x_mark => NULL,
x_grading_schema_cd => NULL,
x_gs_version_number => NULL,
x_grade => NULL,
x_specified_grade_ind => 'N'
);
v_update_on igs_as_msht_su_atmpt.last_update_date%TYPE;
v_update_who igs_as_msht_su_atmpt.last_updated_by%TYPE;
SELECT DISTINCT uop.unit_cd,
uop.version_number,
uop.cal_type,
uop.ci_sequence_number,
uop.location_cd,
ucl.unit_mode,
uop.uoo_id
FROM igs_ps_unit_ofr_opt uop,
igs_as_unit_class ucl,
igs_ca_inst ci,
igs_ps_unit_ver uv,
igs_ca_stat cs,
igs_ps_unit_stat us
WHERE uop.unit_cd LIKE p_unit_cd
AND uop.unit_cd = uv.unit_cd
AND uop.version_number = uv.version_number
AND uv.unit_status = us.unit_status
AND uv.owner_org_unit_cd LIKE NVL (p_unit_org_unit_cd, uv.owner_org_unit_cd)
AND us.s_unit_status = 'ACTIVE'
AND uop.location_cd LIKE NVL (p_location_cd, uop.location_cd)
AND ucl.unit_class = uop.unit_class
AND ucl.unit_mode LIKE NVL (p_unit_mode, ucl.unit_mode)
AND ucl.closed_ind = 'N'
AND uop.cal_type = ci.cal_type
AND uop.ci_sequence_number = ci.sequence_number
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ((cp_assess_cal_type IS NULL
OR (cp_assess_cal_type IS NOT NULL
AND igs_en_gen_014.enrs_get_within_ci (
cp_assess_cal_type,
cp_assess_sequence_number,
uop.cal_type,
uop.ci_sequence_number,
'N'
) = 'Y'
)
)
)
AND (cp_teach_cal_type IS NULL
OR (cp_teach_cal_type IS NOT NULL
AND uop.cal_type = cp_teach_cal_type
AND uop.ci_sequence_number = cp_teach_sequence_number
AND uop.call_number LIKE NVL (p_call_number, uop.call_number)
AND ucl.unit_class LIKE NVL (p_unit_class, ucl.unit_class)
)
);
SELECT sheet_number
FROM igs_as_mark_sheet ms
WHERE ms.unit_cd = cp_uop_unit_cd
AND ms.version_number = cp_uop_version_number
AND ms.cal_type = cp_uop_cal_type
AND ms.ci_sequence_number = cp_uop_ci_sequence_number
AND ms.sheet_number <> cp_ms_sequence_number
AND ms.grading_period_cd = p_grading_period_cd;
SELECT mssua.person_id
FROM igs_as_msht_su_atmpt mssua
WHERE mssua.sheet_number = cp_ms_sequence_number;
SELECT mss.sheet_number
FROM igs_as_mark_sheet mss
WHERE mss.sheet_number = cp_ms_sequence_number
FOR UPDATE OF duplicate_ind NOWAIT;
SELECT sheet_number,
person_id,
course_cd,
unit_cd,
cal_type,
ci_sequence_number,
student_sequence,
unit_class,
last_update_date,
last_updated_by
FROM igs_as_msht_su_atmpt mssua2
WHERE mssua2.sheet_number = cp_ms_mark_sheet
AND mssua2.person_id = cp_mssua1_person_id;
SELECT igs_as_mark_sheet_grpseqnum_s.NEXTVAL
FROM DUAL;
SELECT igs_as_mark_sheet_grpseqnum_s.NEXTVAL
FROM DUAL;
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.location_cd,
ucl.unit_mode,
sua.unit_class,
unit_attempt_status
FROM igs_en_su_attempt sua,
igs_as_unit_class ucl
WHERE ucl.unit_class = sua.unit_class
AND sua.uoo_id = cp_uoo_id
AND sua.location_cd = cp_location_cd
AND ucl.unit_mode = cp_unit_mode
AND ucl.closed_ind = 'N'
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DISCONTIN')
AND (p_grading_period_cd = 'FINAL'
OR (EXISTS ( SELECT 'x'
FROM igs_as_gpc_programs gpr
WHERE gpr.course_cd = sua.course_cd)
OR EXISTS ( SELECT 'x'
FROM igs_en_stdnt_ps_att sca,
igs_as_gpc_aca_stndg gas
WHERE sca.person_id = sua.person_id
AND sca.course_cd = sua.course_cd
AND sca.progression_status = gas.progression_status)
OR EXISTS ( SELECT 'x'
FROM igs_pe_prsid_grp_mem pigm,
igs_as_gpc_pe_id_grp gpg
WHERE sua.person_id = pigm.person_id
AND pigm.GROUP_ID = gpg.GROUP_ID)
OR EXISTS ( SELECT 'x'
FROM igs_as_gpc_cls_stndg gcs
WHERE gcs.class_standing =
igs_pr_get_class_std.get_class_standing (
sua.person_id,
sua.course_cd,
'N',
SYSDATE,
sua.cal_type,
sua.ci_sequence_number
))
OR EXISTS ( SELECT 'x'
FROM igs_as_su_setatmpt iass,
igs_as_gpc_unit_sets gus
WHERE iass.person_id = sua.person_id
AND iass.course_cd = sua.course_cd
AND iass.unit_set_cd = gus.unit_set_cd)
)
)
ORDER BY sua.person_id;
SELECT pe.person_last_name surname,
sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.cal_type,
sua.ci_sequence_number,
sua.location_cd,
ucl.unit_mode,
sua.unit_class,
unit_attempt_status
FROM igs_en_su_attempt sua,
igs_as_unit_class ucl,
hz_parties pe
WHERE sua.person_id = pe.party_id
AND ucl.unit_class = sua.unit_class
AND ucl.closed_ind = 'N'
AND sua.uoo_id = cp_uoo_id
AND sua.location_cd = cp_location_cd
AND ucl.unit_mode = cp_unit_mode
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DISCONTIN')
AND (p_grading_period_cd = 'FINAL'
OR (EXISTS ( SELECT 'x'
FROM igs_as_gpc_programs gpr
WHERE gpr.course_cd = sua.course_cd)
OR EXISTS ( SELECT 'x'
FROM igs_en_stdnt_ps_att sca,
igs_as_gpc_aca_stndg gas
WHERE sca.person_id = sua.person_id
AND sca.course_cd = sua.course_cd
AND sca.progression_status = gas.progression_status)
OR EXISTS ( SELECT 'x'
FROM igs_pe_prsid_grp_mem pigm,
igs_as_gpc_pe_id_grp gpg
WHERE sua.person_id = pigm.person_id
AND pigm.GROUP_ID = gpg.GROUP_ID)
OR EXISTS ( SELECT 'x'
FROM igs_as_gpc_cls_stndg gcs
WHERE gcs.class_standing =
igs_pr_get_class_std.get_class_standing (
sua.person_id,
sua.course_cd,
'N',
SYSDATE,
sua.cal_type,
sua.ci_sequence_number
))
OR EXISTS ( SELECT 'x'
FROM igs_as_su_setatmpt iass,
igs_as_gpc_unit_sets gus
WHERE iass.person_id = sua.person_id
AND iass.course_cd = sua.course_cd
AND iass.unit_set_cd = gus.unit_set_cd)
)
)
ORDER BY pe.person_last_name,
sua.person_id;
igs_as_msht_su_atmpt_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid1,
x_sheet_number => v_ms_sequence_number,
x_person_id => v_sua_person_id_rec.person_id,
x_course_cd => v_sua_person_id_rec.course_cd,
x_unit_cd => v_sua_person_id_rec.unit_cd,
x_cal_type => v_sua_person_id_rec.cal_type,
x_ci_sequence_number => v_sua_person_id_rec.ci_sequence_number,
x_location_cd => v_sua_person_id_rec.location_cd,
x_unit_mode => v_sua_person_id_rec.unit_mode,
x_unit_class => v_sua_person_id_rec.unit_class,
x_student_sequence => v_stdnt_seq,
x_uoo_id => lp_uoo_id
);
igs_as_msht_su_atmpt_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid4,
x_sheet_number => v_ms_sequence_number,
x_person_id => v_sua_surname_rec.person_id,
x_course_cd => v_sua_surname_rec.course_cd,
x_unit_cd => v_sua_surname_rec.unit_cd,
x_cal_type => v_sua_surname_rec.cal_type,
x_ci_sequence_number => v_sua_surname_rec.ci_sequence_number,
x_location_cd => v_sua_surname_rec.location_cd,
x_unit_mode => v_sua_surname_rec.unit_mode,
x_unit_class => v_sua_surname_rec.unit_class,
x_student_sequence => v_stdnt_seq,
x_uoo_id => lp_uoo_id
);
igs_as_mark_sheet_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid6,
x_org_id => l_org_id,
x_sheet_number => v_ms_sequence_number,
x_group_sequence_number => v_group_sequence_number,
x_unit_cd => v_uop_rec.unit_cd,
x_version_number => v_uop_rec.version_number,
x_cal_type => v_uop_rec.cal_type,
x_ci_sequence_number => v_uop_rec.ci_sequence_number,
x_location_cd => v_uop_rec.location_cd,
x_unit_mode => v_uop_rec.unit_mode,
x_production_dt => SYSDATE,
x_duplicate_ind => NULL,
x_grading_period_cd => p_grading_period_cd
);
v_update_on,
v_update_who;
UPDATE igs_as_mark_sheet_all
SET duplicate_ind = 'Y'
WHERE CURRENT OF c_mss;
DELETE igs_as_mark_sheet_all
WHERE group_sequence_number = v_group_sequence_number
AND NOT EXISTS ( SELECT sheet_number
FROM igs_as_msht_su_atmpt
WHERE sheet_number = igs_as_mark_sheet_all.sheet_number);
v_select_ktr NUMBER DEFAULT 0;
v_insert_ktr NUMBER DEFAULT 0;
SELECT suaai.ass_id,
suaai.creation_dt
FROM igs_as_su_atmpt_itm suaai
WHERE suaai.person_id = p_person_id
AND suaai.course_cd = p_course_cd
AND suaai.uoo_id = p_uoo_id
AND suaai.logical_delete_dt IS NULL
AND suaai.creation_dt = (SELECT MAX (suaai2.creation_dt)
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.logical_delete_dt IS NULL
AND suaai2.ass_id = suaai.ass_id);
SELECT ass_id
FROM igs_as_spl_cons_appl
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND uoo_id = p_uoo_id
AND ass_id = cp_ass_id;
v_select_ktr := v_select_ktr + 1;
igs_as_spl_cons_appl_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid7,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_unit_cd => p_unit_cd,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number,
x_ass_id => v_suaai_rec.ass_id,
x_creation_dt => v_suaai_rec.creation_dt,
x_received_dt => p_received_dt,
x_spcl_consideration_cat => p_spcl_consideration_cat,
x_sought_outcome => p_sought_outcome,
x_spcl_consideration_outcome => NULL,
x_tracking_id => NULL,
x_estimated_processing_days => p_estimated_processing_days,
x_comments => 'Special consideration application for'
|| ' all assessment items for the unit attempt.',
x_uoo_id => p_uoo_id,
x_notified_date => p_notified_date
);
v_insert_ktr := v_insert_ktr + 1;
IF v_insert_ktr = 0 THEN
IF v_scap_error_flag THEN
IF v_suaai_error_flag THEN
p_message_name := 'IGS_AS_SPLAPPL_NC_ASSITEM_INV';
IF v_insert_ktr < v_select_ktr THEN
IF v_scap_error_flag THEN
IF v_suaai_error_flag THEN
p_message_name := 'IGS_AS_SPLAPPL_NC_APPL_EXISTS';
l_update_flag VARCHAR2 (20) := 'FALSE';
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 'X'
FROM igs_en_su_attempt sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.uoo_id = cp_uoo_id
AND sua.unit_attempt_status = cst_enrolled;
CURSOR c_suaai_deleted (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
SELECT 'x'
FROM igs_as_su_atmpt_itm suaai
WHERE suaai.person_id = p_person_id
AND suaai.course_cd = p_course_cd
AND suaai.uoo_id = cp_uoo_id
AND suaai.ass_id = p_ass_id
AND suaai.logical_delete_dt IS NOT NULL
AND suaai.s_default_ind = 'N'
AND NOT EXISTS ( SELECT 'x'
FROM igs_as_su_atmpt_itm suaai
WHERE suaai.person_id = p_person_id
AND suaai.course_cd = p_course_cd
AND suaai.uoo_id = cp_uoo_id
AND suaai.ass_id = p_ass_id
AND suaai.logical_delete_dt IS NULL);
SELECT crv.course_type
FROM igs_en_stdnt_ps_att sca,
igs_ps_ver crv
WHERE sca.person_id = p_person_id
AND sca.course_cd = p_course_cd
AND sca.course_cd = crv.course_cd
AND sca.version_number = crv.version_number;
SELECT NVL (MAX (suaai.attempt_number), 0) + 1
FROM igs_as_su_atmpt_itm suaai
WHERE suaai.person_id = p_person_id
AND suaai.course_cd = p_course_cd
AND suaai.uoo_id = cp_uoo_id
AND suaai.ass_id = p_ass_id
AND suaai.logical_delete_dt IS NULL;
SELECT suaai.ROWID,
suaai.*,
sag.unit_ass_item_group_id,
sag.us_ass_item_group_id
FROM igs_as_su_atmpt_itm suaai,
igs_as_sua_ai_group sag
WHERE suaai.person_id = p_person_id
AND suaai.course_cd = p_course_cd
AND suaai.uoo_id = cp_uoo_id
AND suaai.ass_id = cp_ass_id
AND (suaai.unit_section_ass_item_id = cp_ass_item_id OR suaai.UNIT_ASS_ITEM_ID = cp_ass_item_id)
AND suaai.sua_ass_item_group_id = sag.sua_ass_item_group_id
/* AND NOT EXISTS (
SELECT 'X'
FROM igs_as_su_atmpt_itm suaai1
WHERE suaai1.person_id = suaai.person_id
AND suaai1.course_cd = suaai.course_cd
AND suaai1.uoo_id = suaai.uoo_id
AND suaai1.unit_section_ass_item_id IS NOT NULL
AND p_ass_id_usec_unit_ind = 'UNIT'
) */;
OPEN c_suaai_deleted (p_uoo_id);
FETCH c_suaai_deleted INTO v_dummy;
IF c_suaai_deleted%FOUND THEN
CLOSE c_suaai_deleted;
'IGS_AS_CANNOT_CREATE_DFLT_AI', -- Cannot create item as it has previously been deleted
'WARNING|' || v_level || '|' || TO_CHAR (NULL) || '|' || TO_CHAR (p_ass_id)
);
CLOSE c_suaai_deleted;
SELECT suv.unit_section_ass_item_id,
suv.us_ass_item_group_id,
suv.midterm_mandatory_type_code,
suv.midterm_weight_qty,
suv.final_mandatory_type_code,
suv.final_weight_qty,
suv.grading_schema_cd,
suv.gs_version_number,
usaig.group_name
FROM igs_as_usecai_sua_v suv,
igs_as_us_ai_group usaig
WHERE suv.person_id = cp_person_id
AND suv.ass_id = cp_ass_id
AND suv.course_cd = cp_course_cd
AND suv.uoo_id = cp_uoo_id
AND usaig.us_ass_item_group_id = cp_group_id
AND suv.us_ass_item_group_id = usaig.us_ass_item_group_id;
SELECT suv.unit_ass_item_id,
suv.unit_ass_item_group_id,
suv.midterm_mandatory_type_code,
suv.midterm_weight_qty,
suv.final_mandatory_type_code,
suv.final_weight_qty,
suv.grading_schema_cd,
suv.gs_version_number,
uaig.group_name
FROM igs_as_uai_sua_v suv,
igs_as_unit_ai_grp uaig
WHERE suv.person_id = cp_person_id
AND suv.ass_id = cp_ass_id
AND suv.course_cd = cp_course_cd
AND suv.uoo_id = cp_uoo_id
AND uaig.unit_ass_item_group_id = cp_group_id
AND suv.unit_ass_item_group_id = uaig.unit_ass_item_group_id; */
SELECT 'Y' ass_item_exists
FROM igs_ps_unitass_item
WHERE uoo_id = cp_uoo_id
AND logical_delete_dt IS NULL;
SELECT suaai.ROWID,
suaai.*
FROM igs_as_su_atmpt_itm suaai
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id
AND unit_ass_item_id IS NOT NULL
AND logical_delete_dt IS NULL;
SELECT usaig.*
FROM igs_as_us_ai_group usaig
WHERE usaig.us_ass_item_group_id = cp_us_ass_item_group_id;
SELECT uaig.*
FROM igs_as_unit_ai_grp uaig
WHERE uaig.unit_ass_item_group_id = cp_unit_ass_item_group_id;
SELECT sua_ass_item_group_id,
us_ass_item_group_id,
unit_ass_item_group_id,
group_name,
rowid
FROM igs_as_sua_ai_group suaaig
WHERE suaaig.unit_ass_item_group_id = cp_group_id
AND suaaig.person_id = cp_person_id
AND suaaig.course_cd = cp_course_cd
AND suaaig.uoo_id = cp_uoo_id
ORDER BY unit_ass_item_group_id, us_ass_item_group_id;
SELECT sua_ass_item_group_id,
us_ass_item_group_id,
unit_ass_item_group_id,
group_name,
rowid
FROM igs_as_sua_ai_group suaaig
WHERE suaaig.us_ass_item_group_id = cp_group_id
AND suaaig.person_id = cp_person_id
AND suaaig.course_cd = cp_course_cd
AND suaaig.uoo_id = cp_uoo_id
FOR UPDATE OF logical_delete_date NOWAIT;
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.person_id = p_person_id
AND suaai.course_cd = p_course_cd
AND suaai.uoo_id = p_uoo_id
AND suaai.unit_ass_item_id IS NOT NULL
AND suaai.logical_delete_dt IS NULL;
UPDATE igs_as_sua_ai_group suaaig
SET suaaig.logical_delete_date = SYSDATE,
suaaig.last_update_date = SYSDATE,
suaaig.last_updated_by = fnd_global.user_id,
suaaig.last_update_login = fnd_global.login_id
WHERE suaaig.person_id = p_person_id
AND suaaig.course_cd = p_course_cd
AND suaaig.uoo_id = p_uoo_id
AND suaaig.unit_ass_item_group_id IS NOT NULL
AND suaaig.logical_delete_date IS NULL;
igs_as_sua_ai_group_pkg.insert_row (
x_rowid => l_rowid8,
x_sua_ass_item_group_id => l_return_pk_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_group_name => rec_usec_aig.group_name,
x_midterm_formula_code => rec_usec_aig.midterm_formula_code,
x_midterm_formula_qty => rec_usec_aig.midterm_formula_qty,
x_midterm_weight_qty => rec_usec_aig.midterm_weight_qty,
x_final_formula_code => rec_usec_aig.final_formula_code,
x_final_formula_qty => rec_usec_aig.final_formula_qty,
x_final_weight_qty => rec_usec_aig.final_weight_qty,
x_unit_ass_item_group_id => NULL,
x_us_ass_item_group_id => rec_usec_aig.us_ass_item_group_id,
x_logical_delete_date => NULL,
x_mode => 'R'
);
igs_as_sua_ai_group_pkg.update_row (
x_rowid => rec_suaig.rowid,
x_sua_ass_item_group_id => rec_suaig.sua_ass_item_group_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_group_name => rec_suaig.group_name,
x_midterm_formula_code => rec_usec_aig.midterm_formula_code,
x_midterm_formula_qty => rec_usec_aig.midterm_formula_qty,
x_midterm_weight_qty => rec_usec_aig.midterm_weight_qty,
x_final_formula_code => rec_usec_aig.final_formula_code,
x_final_formula_qty => rec_usec_aig.final_formula_qty,
x_final_weight_qty => rec_usec_aig.final_weight_qty,
x_unit_ass_item_group_id => NULL,
x_us_ass_item_group_id => rec_usec_aig.us_ass_item_group_id,
x_logical_delete_date => NULL,
x_mode => 'R'
);
igs_as_sua_ai_group_pkg.insert_row (
x_rowid => l_rowid8,
x_sua_ass_item_group_id => l_return_pk_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_group_name => rec_unit_aig.group_name,
x_midterm_formula_code => rec_unit_aig.midterm_formula_code,
x_midterm_formula_qty => rec_unit_aig.midterm_formula_qty,
x_midterm_weight_qty => rec_unit_aig.midterm_weight_qty,
x_final_formula_code => rec_unit_aig.final_formula_code,
x_final_formula_qty => rec_unit_aig.final_formula_qty,
x_final_weight_qty => rec_unit_aig.final_weight_qty,
x_unit_ass_item_group_id => rec_unit_aig.unit_ass_item_group_id,
x_us_ass_item_group_id => NULL,
x_logical_delete_date => NULL,
x_mode => 'R'
);
igs_as_sua_ai_group_pkg.update_row (
x_rowid => rec_suaig.rowid,
x_sua_ass_item_group_id => rec_suaig.sua_ass_item_group_id,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_uoo_id => p_uoo_id,
x_group_name => rec_unit_aig.group_name,
x_midterm_formula_code => rec_unit_aig.midterm_formula_code,
x_midterm_formula_qty => rec_unit_aig.midterm_formula_qty,
x_midterm_weight_qty => rec_unit_aig.midterm_weight_qty,
x_final_formula_code => rec_unit_aig.final_formula_code,
x_final_formula_qty => rec_unit_aig.final_formula_qty,
x_final_weight_qty => rec_unit_aig.final_weight_qty,
x_unit_ass_item_group_id => rec_unit_aig.unit_ass_item_group_id,
x_us_ass_item_group_id => NULL,
x_logical_delete_date => NULL,
x_mode => 'R'
);
(suaai_upd_rec.logical_delete_dt IS NOT NULL)) THEN
CLOSE c_suaai_upd;
UPDATE igs_as_su_atmpt_itm suaai
SET suaai.logical_delete_dt = NULL,
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 ,
suaai.midterm_mandatory_type_code = p_midterm_mandatory_type_code,
suaai.midterm_weight_qty = p_midterm_weight_qty,
suaai.final_mandatory_type_code = p_final_mandatory_type_code,
suaai.final_weight_qty = p_final_weight_qty,
suaai.grading_schema_cd = p_grading_schema_cd,
suaai.gs_version_number = p_gs_version_number
WHERE suaai.rowid = suaai_upd_rec.ROWID;
/* igs_as_su_atmpt_itm_pkg.update_row (
x_mode => 'R',
x_rowid => suaai_upd_rec.ROWID,
x_person_id => suaai_upd_rec.person_id,
x_course_cd => suaai_upd_rec.course_cd,
x_unit_cd => suaai_upd_rec.unit_cd,
x_cal_type => suaai_upd_rec.cal_type,
x_ci_sequence_number => suaai_upd_rec.ci_sequence_number,
x_ass_id => suaai_upd_rec.ass_id,
x_creation_dt => suaai_upd_rec.creation_dt,
x_attempt_number => suaai_upd_rec.attempt_number,
x_outcome_dt => suaai_upd_rec.outcome_dt,
x_override_due_dt => suaai_upd_rec.override_due_dt,
x_tracking_id => suaai_upd_rec.tracking_id,
x_logical_delete_dt => NULL,
x_s_default_ind => suaai_upd_rec.s_default_ind,
x_ass_pattern_id => suaai_upd_rec.ass_pattern_id,
x_grading_schema_cd => suaai_upd_rec.grading_schema_cd,
x_gs_version_number => suaai_upd_rec.gs_version_number,
x_grade => suaai_upd_rec.grade,
x_outcome_comment_code => suaai_upd_rec.outcome_comment_code,
x_mark => suaai_upd_rec.mark,
x_attribute_category => suaai_upd_rec.attribute_category,
x_attribute1 => suaai_upd_rec.attribute1,
x_attribute2 => suaai_upd_rec.attribute2,
x_attribute3 => suaai_upd_rec.attribute3,
x_attribute4 => suaai_upd_rec.attribute4,
x_attribute5 => suaai_upd_rec.attribute5,
x_attribute6 => suaai_upd_rec.attribute6,
x_attribute7 => suaai_upd_rec.attribute7,
x_attribute8 => suaai_upd_rec.attribute8,
x_attribute9 => suaai_upd_rec.attribute9,
x_attribute10 => suaai_upd_rec.attribute10,
x_attribute11 => suaai_upd_rec.attribute11,
x_attribute12 => suaai_upd_rec.attribute12,
x_attribute13 => suaai_upd_rec.attribute13,
x_attribute14 => suaai_upd_rec.attribute14,
x_attribute15 => suaai_upd_rec.attribute15,
x_attribute16 => suaai_upd_rec.attribute16,
x_attribute17 => suaai_upd_rec.attribute17,
x_attribute18 => suaai_upd_rec.attribute18,
x_attribute19 => suaai_upd_rec.attribute19,
x_attribute20 => suaai_upd_rec.attribute20,
x_uoo_id => suaai_upd_rec.uoo_id,
x_unit_section_ass_item_id => suaai_upd_rec.unit_section_ass_item_id,
x_unit_ass_item_id => suaai_upd_rec.unit_ass_item_id,
x_sua_ass_item_group_id => suaai_upd_rec.sua_ass_item_group_id,
x_midterm_mandatory_type_code => p_midterm_mandatory_type_code,
x_midterm_weight_qty => p_midterm_weight_qty,
x_final_mandatory_type_code => p_final_mandatory_type_code,
x_final_weight_qty => p_final_weight_qty,
x_submitted_date => suaai_upd_rec.submitted_date,
x_waived_flag => suaai_upd_rec.waived_flag,
x_penalty_applied_flag => suaai_upd_rec.penalty_applied_flag
);*/
(suaai_upd_rec.logical_delete_dt IS NULL)) THEN
CLOSE c_suaai_upd;
UPDATE igs_as_su_atmpt_itm suaai
SET 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,
suaai.midterm_mandatory_type_code = p_midterm_mandatory_type_code,
suaai.midterm_weight_qty = p_midterm_weight_qty,
suaai.final_mandatory_type_code = p_final_mandatory_type_code,
suaai.final_weight_qty = p_final_weight_qty,
suaai.grading_schema_cd = p_grading_schema_cd,
suaai.gs_version_number = p_gs_version_number
WHERE suaai.rowid = suaai_upd_rec.ROWID;
/* igs_as_su_atmpt_itm_pkg.update_row (
x_mode => 'R',
x_rowid => suaai_upd_rec.ROWID,
x_person_id => suaai_upd_rec.person_id,
x_course_cd => suaai_upd_rec.course_cd,
x_unit_cd => suaai_upd_rec.unit_cd,
x_cal_type => suaai_upd_rec.cal_type,
x_ci_sequence_number => suaai_upd_rec.ci_sequence_number,
x_ass_id => suaai_upd_rec.ass_id,
x_creation_dt => suaai_upd_rec.creation_dt,
x_attempt_number => suaai_upd_rec.attempt_number,
x_outcome_dt => suaai_upd_rec.outcome_dt,
x_override_due_dt => suaai_upd_rec.override_due_dt,
x_tracking_id => suaai_upd_rec.tracking_id,
x_logical_delete_dt => suaai_upd_rec.logical_delete_dt,
x_s_default_ind => suaai_upd_rec.s_default_ind,
x_ass_pattern_id => suaai_upd_rec.ass_pattern_id,
x_grading_schema_cd => p_grading_schema_cd,
x_gs_version_number => p_gs_version_number ,
x_grade => suaai_upd_rec.grade,
x_outcome_comment_code => suaai_upd_rec.outcome_comment_code,
x_mark => suaai_upd_rec.mark,
x_attribute_category => suaai_upd_rec.attribute_category,
x_attribute1 => suaai_upd_rec.attribute1,
x_attribute2 => suaai_upd_rec.attribute2,
x_attribute3 => suaai_upd_rec.attribute3,
x_attribute4 => suaai_upd_rec.attribute4,
x_attribute5 => suaai_upd_rec.attribute5,
x_attribute6 => suaai_upd_rec.attribute6,
x_attribute7 => suaai_upd_rec.attribute7,
x_attribute8 => suaai_upd_rec.attribute8,
x_attribute9 => suaai_upd_rec.attribute9,
x_attribute10 => suaai_upd_rec.attribute10,
x_attribute11 => suaai_upd_rec.attribute11,
x_attribute12 => suaai_upd_rec.attribute12,
x_attribute13 => suaai_upd_rec.attribute13,
x_attribute14 => suaai_upd_rec.attribute14,
x_attribute15 => suaai_upd_rec.attribute15,
x_attribute16 => suaai_upd_rec.attribute16,
x_attribute17 => suaai_upd_rec.attribute17,
x_attribute18 => suaai_upd_rec.attribute18,
x_attribute19 => suaai_upd_rec.attribute19,
x_attribute20 => suaai_upd_rec.attribute20,
x_uoo_id => suaai_upd_rec.uoo_id,
x_unit_section_ass_item_id => suaai_upd_rec.unit_section_ass_item_id,
x_unit_ass_item_id => suaai_upd_rec.unit_ass_item_id,
x_sua_ass_item_group_id => suaai_upd_rec.sua_ass_item_group_id,
x_midterm_mandatory_type_code => p_midterm_mandatory_type_code,
x_midterm_weight_qty => p_midterm_weight_qty,
x_final_mandatory_type_code => p_final_mandatory_type_code,
x_final_weight_qty => p_final_weight_qty,
x_submitted_date => suaai_upd_rec.submitted_date,
x_waived_flag => suaai_upd_rec.waived_flag,
x_penalty_applied_flag => suaai_upd_rec.penalty_applied_flag
);*/
igs_as_su_atmpt_itm_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid8,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_unit_cd => p_unit_cd,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number,
x_ass_id => p_ass_id,
x_creation_dt => v_creation_dt,
x_attempt_number => v_attempt_number,
x_outcome_dt => NULL,
x_override_due_dt => NULL,
x_tracking_id => NULL,
x_logical_delete_dt => NULL,
x_s_default_ind => cst_yes,
x_ass_pattern_id => NULL,
x_grading_schema_cd => p_grading_schema_cd,
x_gs_version_number => p_gs_version_number,
x_grade => NULL,
x_outcome_comment_code => NULL,
x_mark => 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_uoo_id => p_uoo_id,
x_unit_section_ass_item_id => l_us_assessment_id,
x_unit_ass_item_id => l_unit_assessment_id,
x_sua_ass_item_group_id => l_return_pk_id,
x_midterm_mandatory_type_code => p_midterm_mandatory_type_code,
x_midterm_weight_qty => p_midterm_weight_qty,
x_final_mandatory_type_code => p_final_mandatory_type_code,
x_final_weight_qty => p_final_weight_qty,
x_submitted_date => NULL,
x_waived_flag => 'N',
x_penalty_applied_flag => 'N'
);
IF c_suaai_deleted%ISOPEN THEN
CLOSE c_suaai_deleted;
fnd_log.level_exception, 'igs_as_gen_004.assp_ins_suaai_dflt.exception_while_insert_update',
'SQLERRM:' || SQLERRM
);
fnd_log.level_exception, 'igs_as_gen_004.assp_ins_suaai_dflt.final_exception_while_insert_update',
'SQLERRM:' || SQLERRM
);