[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ese.ese_id
FROM IGS_AS_EXAM_SESSION ese
WHERE exam_cal_type = p_exam_cal_type AND
exam_ci_sequence_number = p_exam_ci_sequence_number AND
dt_alias = p_dt_alias AND
dai_sequence_number = p_dai_sequence_number AND
start_time = p_start_time AND
end_time = p_end_time;
SELECT exam_cal_type,
exam_ci_sequence_number,
dt_alias,
dai_sequence_number,
start_time,
end_time
FROM IGS_AS_EXAM_SESSION ese
WHERE ese_id = p_ese_id;
p_insert_default_ind IN VARCHAR2,
p_grade IN VARCHAR2,
p_finalised_ind IN VARCHAR2,
p_assble_type IN VARCHAR2,
p_no_assmnt_type IN VARCHAR2,
p_org_id IN NUMBER,
--added by lkaki--
p_audit_grade IN VARCHAR2 DEFAULT NULL
) IS
--
p_assess_cal_type igs_ca_inst.cal_type%TYPE;
'p_insert_default_ind => ' || p_insert_default_ind || ';' ||
IF (p_insert_default_ind = 'N'
AND (p_grade IS NULL
AND p_audit_grade IS NULL)) THEN
errbuf := fnd_message.get_string ('IGS', 'IGS_AS_GRD_SPECIFIED');
v_insert_grade igs_as_grd_sch_grade.grade%TYPE;
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.location_cd,
sua.unit_class,
sua.ci_start_dt,
sua.ci_end_dt,
sua.uoo_id,
sua.no_assessment_ind
FROM igs_en_su_attempt_all sua,
igs_ps_unit_ver_all uv,
igs_as_unit_class_all uc
WHERE ((cp_assess_cal_type IS NOT NULL
AND EXISTS (
SELECT 'x'
FROM igs_ca_inst_rel
WHERE sub_cal_type = sua.cal_type
AND sub_ci_sequence_number = sua.ci_sequence_number
AND sup_cal_type = cp_assess_cal_type
AND sup_ci_sequence_number = cp_assess_sequence_number)
)
OR (cp_assess_cal_type IS NULL)
)
AND ((cp_teach_cal_type IS NOT NULL
AND cp_teach_sequence_number IS NOT NULL
AND sua.cal_type = cp_teach_cal_type
AND sua.ci_sequence_number = cp_teach_sequence_number
)
OR (cp_teach_cal_type IS NULL)
)
AND sua.unit_attempt_status = 'ENROLLED'
AND (((cp_no_assessment_type <> '%')
AND ((cp_no_assessment_type = 'A'
AND sua.no_assessment_ind <> 'Y')
OR (cp_no_assessment_type = 'N'
AND sua.no_assessment_ind <> 'N')
)
)
OR (cp_no_assessment_type = '%')
)
AND sua.unit_cd LIKE cp_unit_cd
AND sua.location_cd LIKE cp_location_cd
AND sua.unit_class LIKE cp_unit_class
AND NOT EXISTS (
SELECT 1
FROM igs_as_su_stmptout_all suao
WHERE suao.person_id = sua.person_id
AND suao.course_cd = sua.course_cd
AND suao.uoo_id = sua.uoo_id)
AND uv.unit_cd = sua.unit_cd
AND uv.version_number = sua.version_number
AND uv.owner_org_unit_cd LIKE cp_org_unit_cd
AND (((cp_assessable_type <> '%')
AND ((cp_assessable_type = 'A'
AND uv.assessable_ind = 'Y')
OR (cp_assessable_type = 'N'
AND uv.assessable_ind = 'N')
)
)
OR (cp_assessable_type = '%')
)
AND uc.unit_class = sua.unit_class
AND uc.unit_mode LIKE cp_unit_mode;
SELECT gsg.grade
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema
AND gsg.version_number = cp_gs_version_number
AND gsg.dflt_outstanding_ind = 'Y'
AND gsg.s_result_type <> 'AUDIT'
AND gsg.closed_ind = 'N';
SELECT gsg.grade
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema
AND gsg.version_number = cp_gs_version_number
AND gsg.dflt_outstanding_ind = 'Y'
AND gsg.s_result_type = 'AUDIT'
AND gsg.closed_ind = 'N';
PROCEDURE assp_insertgrade (
p_person_id igs_en_su_attempt.person_id%TYPE,
p_course_cd igs_en_su_attempt.course_cd%TYPE,
p_unit_cd igs_en_su_attempt.unit_cd%TYPE,
p_cal_type igs_en_su_attempt.cal_type%TYPE,
p_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
p_ci_start_dt igs_en_su_attempt.ci_start_dt%TYPE,
p_ci_end_dt igs_en_su_attempt.ci_end_dt%TYPE,
p_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE,
p_s_grade_creation_method_type igs_as_su_stmptout.s_grade_creation_method_type%TYPE,
p_grading_schema_cd igs_as_su_stmptout.grading_schema_cd%TYPE,
p_version_number igs_as_su_stmptout.version_number%TYPE,
p_grade igs_as_su_stmptout.grade%TYPE,
p_finalised_outcome_ind igs_as_su_stmptout.finalised_outcome_ind%TYPE,
p_uoo_id igs_en_su_attempt.uoo_id%TYPE
) IS
--
CURSOR cur_person_detail (cp_person_id NUMBER) IS
SELECT party_number
FROM hz_parties
WHERE party_id = cp_person_id;
g_module_head || 'assp_ins_admin_grds.assp_insertgrade.begin',
'In Params : p_person_id => ' || p_person_id || ';' ||
igs_as_su_stmptout_pkg.insert_row (
x_rowid => x_rowid,
x_org_id => p_org_id,
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_outcome_dt => p_outcome_dt,
x_ci_start_dt => p_ci_start_dt,
x_ci_end_dt => p_ci_end_dt,
x_grading_schema_cd => p_grading_schema_cd,
x_version_number => p_version_number,
x_grade => p_grade,
x_s_grade_creation_method_type => p_s_grade_creation_method_type,
x_finalised_outcome_ind => p_finalised_outcome_ind,
x_mark => NULL,
x_number_times_keyed => NULL,
x_translated_grading_schema_cd => NULL,
x_translated_version_number => NULL,
x_translated_grade => NULL,
x_translated_dt => NULL,
x_mode => 'R',
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_mark_capped_flag => 'N',
x_show_on_academic_histry_flag => 'Y',
x_release_date => NULL,
x_manual_override_flag => 'N',
x_incomp_deadline_date => NULL,
x_incomp_grading_schema_cd => NULL,
x_incomp_version_number => NULL,
x_incomp_default_grade => NULL,
x_incomp_default_mark => NULL,
x_comments => NULL,
x_grading_period_cd => 'FINAL'
);
g_module_head || 'assp_ins_admin_grds.assp_insertgrade.created_outcome',
'Created Outcome for ' || p_person_id || ';' || p_course_cd || ';' || p_uoo_id
g_module_head || 'assp_ins_admin_grds.assp_insertgrade.insert_exception',
'SQLERRM => ' || SQLERRM
);
g_module_head || 'assp_ins_admin_grds.assp_insertgrade.exception',
'SQLERRM => ' || SQLERRM
);
END assp_insertgrade;
SELECT gsg.grade
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema
AND gsg.version_number = cp_gs_version_number
AND gsg.grade = cp_grade;
'Grade to be inserted does not belong to the Grading Schema so skipping the outcome creation'
);
assp_insertgrade (
p_person_id,
p_course_cd,
p_unit_cd,
p_cal_type,
p_ci_sequence_number,
p_ci_start_dt,
p_ci_end_dt,
p_outcome_dt,
p_s_grade_creation_method_type,
p_grading_schema_cd,
p_version_number,
v_grade,
p_finalised_outcome_ind,
p_uoo_id
);
SAVEPOINT s_before_insert;
IF (p_insert_default_ind = 'Y') THEN
IF (v_stu_unit_atmpt_rec.no_assessment_ind = 'N') THEN -- Non-Audit Attempt
v_record_found := FALSE;
v_insert_grade := v_grade;
assp_insertgrade (
v_stu_unit_atmpt_rec.person_id,
v_stu_unit_atmpt_rec.course_cd,
v_stu_unit_atmpt_rec.unit_cd,
v_stu_unit_atmpt_rec.cal_type,
v_stu_unit_atmpt_rec.ci_sequence_number,
v_stu_unit_atmpt_rec.ci_start_dt,
v_stu_unit_atmpt_rec.ci_end_dt,
SYSDATE,
'SYSTEM',
v_grading_schema,
v_gs_version_number,
v_insert_grade,
p_finalised_ind,
v_stu_unit_atmpt_rec.uoo_id
);
v_insert_grade := v_audit_grade;
assp_insertgrade (
v_stu_unit_atmpt_rec.person_id,
v_stu_unit_atmpt_rec.course_cd,
v_stu_unit_atmpt_rec.unit_cd,
v_stu_unit_atmpt_rec.cal_type,
v_stu_unit_atmpt_rec.ci_sequence_number,
v_stu_unit_atmpt_rec.ci_start_dt,
v_stu_unit_atmpt_rec.ci_end_dt,
SYSDATE,
'SYSTEM',
v_grading_schema,
v_gs_version_number,
v_insert_grade,
p_finalised_ind,
v_stu_unit_atmpt_rec.uoo_id
);
ELSIF (p_insert_default_ind = 'N') THEN
IF (v_stu_unit_atmpt_rec.no_assessment_ind = 'N') THEN -- Non-Audit Attempt
assp_findgrade (
v_stu_unit_atmpt_rec.person_id,
v_stu_unit_atmpt_rec.course_cd,
v_stu_unit_atmpt_rec.unit_cd,
v_stu_unit_atmpt_rec.cal_type,
v_stu_unit_atmpt_rec.ci_sequence_number,
v_stu_unit_atmpt_rec.ci_start_dt,
v_stu_unit_atmpt_rec.ci_end_dt,
SYSDATE,
'SYSTEM',
v_grading_schema,
v_gs_version_number,
p_grade,
p_finalised_ind,
i,
v_stu_unit_atmpt_rec.uoo_id
);
ROLLBACK TO s_before_insert;
select IGS_AS_ITEM_ASSESSOR_SEQ_NUM_S.NEXTVAL into V_SEQUENCE_NUMBER FROM DUAL;
IGS_AS_ITEM_ASSESSOR_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_ASS_ID => p_ass_id,
X_PERSON_ID => p_person_id,
X_SEQUENCE_NUMBER => V_SEQUENCE_NUMBER,
X_ASS_ASSESSOR_TYPE => p_ass_assessor_type,
X_PRIMARY_ASSESSOR_IND => p_primary_assessor_ind,
X_ITEM_LIMIT => p_item_limit,
X_LOCATION_CD => p_location_cd,
X_UNIT_MODE => p_unit_mode,
X_UNIT_CLASS => p_unit_class,
X_COMMENTS => p_comments,
X_MODE => 'R'
);
SELECT COUNT(*)
FROM IGS_AS_ITEM_ASSESSOR
WHERE ass_id = cp_ass_id;
SELECT coord_person_id
FROM IGS_PS_UNIT_VER
WHERE unit_cd = cp_unit_cd AND
version_number = cp_version_number;
SELECT ASS_ASSESSOR_TYPE
FROM IGS_AS_ASSESSOR_TYPE
WHERE dflt_ind = cst_yes and
closed_ind = cst_no;
SELECT
suaai.person_id,
suaai.course_cd,
suaai.unit_cd,
suaai.cal_type,
suaai.ci_sequence_number,
suaai.ass_id,
ai.description,
suaai.creation_dt,
suaai.override_due_dt,
sua.version_number,
sua.location_cd,
sua.unit_class,
uc.unit_mode,
suaai.tracking_id,
sua.uoo_id
FROM
igs_as_su_atmpt_itm suaai,
igs_en_su_attempt sua,
igs_as_assessmnt_itm ai,
igs_as_unit_class uc
WHERE
(cp_person_id IS NULL OR suaai.person_id = cp_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.person_id = sua.person_id AND
suaai.course_cd = sua.course_cd AND
suaai.uoo_id = sua.uoo_id AND
sua.course_cd LIKE cp_course_cd AND
sua.unit_cd LIKE cp_unit_cd AND
sua.location_cd LIKE cp_location_cd AND
sua.unit_class LIKE cp_unit_class AND
sua.unit_class = uc.unit_class AND
uc.unit_mode LIKE cp_unit_mode AND
sua.unit_attempt_status = cst_enrolled AND
(
cp_teach_cal_type IS NULL
OR
suaai.cal_type = cp_teach_cal_type
) AND
(
cp_ass_id IS NULL
OR
suaai.ass_id = cp_ass_id
) AND
(
cp_teach_ci_sequence_number IS NULL
OR
suaai.ci_sequence_number = cp_teach_ci_sequence_number
) AND
-- check for teaching calendar being passed is subordinate to the academic calendar passed
igs_en_gen_014.enrs_get_within_ci ( cp_acad_cal_type,
cp_acad_ci_sequence_number,
sua.cal_type,
sua.ci_sequence_number,
'Y'
) = 'Y' AND
suaai.ass_id = ai.ass_id AND
/* If Assessment Item Id is passed then the system assessment item type (s_assessment_type) should be ASSIGNMENT */
igs_as_gen_002.assp_get_ai_s_type(ai.ass_id) = cst_assignment AND
(
(
(cp_reproduce = 'Y' )
AND
(suaai.tracking_id IS NOT NULL)
)
OR
(
(cp_reproduce = 'N')
AND
(suaai.tracking_id IS NOT NULL)
)
AND
(
NOT EXISTS ( SELECT
'X'
FROM
IGS_CO_OU_CO_REF ocr
WHERE
ocr.person_id = suaai.person_id AND
ocr.correspondence_type = cst_coversheet AND
ocr.cal_type = suaai.cal_type AND
ocr.ci_sequence_number = suaai.ci_sequence_number AND
ocr.course_cd = suaai.course_cd AND
ocr.unit_cd = suaai.unit_cd AND
ocr.other_reference = TO_CHAR(suaai.ass_id) || '|' || IGS_GE_DATE.IGSCHARDT(suaai.creation_dt)
)
)
)
ORDER BY suaai.person_id, suaai.unit_cd;
SELECT
uai_due_dt,
uai_reference
FROM
igs_as_uai_sua_v
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
uai_logical_delete_dt IS NULL;
SELECT
title,
surname,
given_names,
preferred_given_name
FROM
igs_pe_person
WHERE
person_id = cp_person_id;
SELECT
pa.addr_line_1,
pa.addr_line_2,
pa.addr_line_3,
pa.addr_line_4,
pa.postal_code,
pa.correspondence_ind
FROM
igs_pe_person_addr pa
WHERE
pa.person_id = cp_person_id
AND
( pa.status = 'A'
AND
(
SYSDATE BETWEEN NVL(pa.start_dt,SYSDATE) AND NVL(pa.end_dt,SYSDATE+1)
)
)
ORDER BY
pa.correspondence_ind DESC;
SELECT
crv.version_number,
crv.short_title
FROM
IGS_EN_STDNT_PS_ATT sca,
IGS_PS_VER crv
WHERE
sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd AND
sca.course_cd = crv.course_cd AND
sca.version_number = crv.version_number;
SELECT
uv.version_number,
uv.short_title
FROM
IGS_EN_SU_ATTEMPT sua,
IGS_PS_UNIT_VER uv
WHERE
sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.uoo_id = cp_uoo_id AND
uv.unit_cd = sua.unit_cd AND
uv.version_number = sua.version_number;
SELECT s_unit_mode
FROM IGS_AS_UNIT_MODE
WHERE UNIT_MODE = cp_unit_mode;
SELECT
acad_alternate_code, -- Year
teach_alternate_code -- Semester
FROM
igs_as_sua_ass_v
WHERE
person_id = cp_person_id AND
course_cd = cp_course_cd AND
uoo_id = cp_uoo_id;
SELECT description
FROM IGS_AS_ASSESSMNT_ITM
WHERE ass_id = cp_ass_id;
Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
SELECT userenv('SESSIONID')
FROM dual;
SELECT ci2.CAL_TYPE,
ci2.sequence_number
FROM IGS_CA_INST ci,
IGS_CA_INST ci2,
IGS_CA_TYPE ct,
IGS_CA_TYPE ct2
WHERE ci.CAL_TYPE = cp_ass_cal_type AND
ci.sequence_number = cp_ass_sequence_number AND
ci.CAL_TYPE = ct.CAL_TYPE AND
ct.S_CAL_CAT = cst_assessment AND
ci2.CAL_TYPE = ct2.CAL_TYPE AND
ct2.S_CAL_CAT = cst_teaching AND
IGS_EN_GEN_014.enrs_get_within_ci(
cp_ass_cal_type,
cp_ass_sequence_number,
ci2.CAL_TYPE,
ci2.sequence_number,
cst_no) = cst_yes;
SELECT uv.owner_org_unit_cd,
uv.owner_ou_start_dt,
uc.UNIT_MODE,
uv.unit_cd,
uv.version_number,
uoo.uoo_id
FROM IGS_PS_UNIT_OFR_OPT uoo,
IGS_PS_UNIT_VER uv,
IGS_AS_UNIT_CLASS uc
WHERE uoo.unit_cd = uv.unit_cd AND
uoo.version_number = uv.version_number AND
uoo.UNIT_CLASS = uc.UNIT_CLASS AND
uoo.CAL_TYPE = cp_teach_cal_type AND
uoo.ci_sequence_number = cp_teach_ci_sequence_number AND
uv.owner_org_unit_cd LIKE cp_owner_org_unit_cd AND
uv.owner_ou_start_dt = NVL(cp_owner_ou_start_dt, uv.owner_ou_start_dt)AND
uc.UNIT_MODE LIKE cp_unit_mode;
SELECT uai.ass_id,
uai.due_dt,
IGS_GE_GEN_001.genp_clc_week_end_dt(uai.due_dt) uai_week_ending_dt
FROM IGS_PS_UNIT_OFR_OPT uoo,
-- IGS_AS_UNIT_CLASS uc,
IGS_AS_UNITASS_ITEM uai,
IGS_AS_ASSESSMNT_ITM ai,
IGS_AS_ASSESSMNT_TYP atyp
WHERE uoo.uoo_id = cp_uoo_id AND
-- uoo.UNIT_CLASS = uc.UNIT_CLASS AND
uoo.unit_cd = uai.unit_cd AND
uoo.version_number = uai.version_number AND
uoo.CAL_TYPE = uai.CAL_TYPE AND
uoo.ci_sequence_number = uai.ci_sequence_number AND
-- uoo.UNIT_CLASS = uc.UNIT_CLASS AND
-- IGS_AS_VAL_UAI.assp_val_sua_uai(
-- uoo.location_cd,
-- uoo.UNIT_CLASS,
-- uc.UNIT_MODE,
-- uai.location_cd,
-- uai.UNIT_CLASS,
-- uai.UNIT_MODE) = cst_true AND
uai.ass_id = ai.ass_id AND
uai.logical_delete_dt IS NULL AND
atyp.ASSESSMENT_TYPE = ai.ASSESSMENT_TYPE AND
NVL(atyp.s_assessment_type, cst_none) = cst_assignment;
SELECT suaai.override_due_dt,
trst.completion_dt
FROM IGS_AS_SU_ATMPT_ITM suaai,
IGS_TR_STEP trst,
IGS_EN_SU_ATTEMPT sua
WHERE sua.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
suaai.ass_id = cp_ass_id AND
suaai.logical_delete_dt IS NULL AND
suaai.tracking_id IS NOT NULL AND
suaai.tracking_id = trst.tracking_id AND
trst.s_tracking_step_type = cst_assign_due AND
IGS_AS_VAL_SUAAI.assp_val_ass_count(sua.unit_attempt_status,
suaai.tracking_id) = cst_yes;
IGS_AS_DUE_DT_SUMRY_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_SESSION_ID => v_session_id,
X_AT_ID => l_AT_ID,
X_UNIT_CD => c_uoo_rec.unit_cd,
X_VERSION_NUMBER => c_uoo_rec.version_number,
X_CAL_TYPE => c_teach_perd_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => c_teach_perd_rec.sequence_number,
X_OWNER_ORG_UNIT_CD => c_uoo_rec.owner_org_unit_cd,
X_OWNER_OU_START_DT => c_uoo_rec.owner_ou_start_dt,
X_UNIT_MODE => c_uoo_rec.UNIT_MODE,
X_ASS_ID => c_uai_rec.ass_id,
X_WEEK_ENDING_DT => v_override_week_ending_dt,
X_BASE_COUNT => NULL,
X_EXPECTED_OVERDUE_COUNT => 1,
X_ONE_WEEK_EXTENSION_COUNT => NULL,
X_TWO_WEEK_EXTENSION_COUNT => NULL,
X_THREE_WEEK_PLUS_EXTNSN_COUNT => NULL,
X_RECEIVED_COUNT => NULL,
X_MODE => 'R'
);
IGS_AS_DUE_DT_SUMRY_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_SESSION_ID => v_session_id,
X_AT_ID => l_AT_ID,
X_UNIT_CD => c_uoo_rec.unit_cd,
X_VERSION_NUMBER => c_uoo_rec.version_number,
X_CAL_TYPE => c_teach_perd_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => c_teach_perd_rec.sequence_number,
X_OWNER_ORG_UNIT_CD => c_uoo_rec.owner_org_unit_cd,
X_OWNER_OU_START_DT => c_uoo_rec.owner_ou_start_dt,
X_UNIT_MODE => c_uoo_rec.UNIT_MODE,
X_ASS_ID => c_uai_rec.ass_id,
X_WEEK_ENDING_DT => v_completion_week_ending_dt,
X_BASE_COUNT => NULL,
X_EXPECTED_OVERDUE_COUNT => 1,
X_ONE_WEEK_EXTENSION_COUNT => NULL,
X_TWO_WEEK_EXTENSION_COUNT => NULL,
X_THREE_WEEK_PLUS_EXTNSN_COUNT => NULL,
X_RECEIVED_COUNT => NULL,
X_MODE => 'R'
);
SELECT venue_cd
FROM IGS_GR_VENUE
WHERE closed_ind = 'N';
SELECT exam_cal_type,
exam_ci_sequence_number,
dt_alias,
dai_sequence_number,
ci_start_dt,
ci_end_dt,
start_time,
end_time,
ese_id
FROM IGS_AS_EXAM_SESSION es
WHERE exam_cal_type = p_exam_cal_type AND
exam_ci_sequence_number = p_exam_ci_sequence_number AND
NOT EXISTS (
SELECT 'x'
FROM IGS_AS_EXMVNU_SESAVL evsa
WHERE evsa.ese_id = es.ese_id AND
evsa.venue_cd = cp_venue_cd
);
IGS_AS_EXMVNU_SESAVL_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_ORG_ID => p_org_id,
X_VENUE_CD => v_ve_rec.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 => NULL,
X_MODE => 'R'
);
SELECT esvv.ese_id,
esvv.venue_cd,
ve.exam_location_cd
FROM IGS_AS_ESE_VENUE_V esvv,
IGS_GR_VENUE ve
WHERE esvv.exam_cal_type = p_exam_cal_type AND
esvv.exam_ci_sequence_number = p_exam_ci_sequence_number AND
esvv.venue_cd = ve.venue_cd;
SELECT 'X'
FROM IGS_AD_LOCATION_REL lr,
IGS_AD_LOCATION loc,
IGS_AD_LOCATION_TYPE lot
WHERE lr.sub_location_cd = cp_exam_location_cd AND
lr.location_cd = loc.location_cd AND
loc.LOCATION_TYPE = lot.LOCATION_TYPE AND
lot.s_location_type = 'CAMPUS';
SELECT els.person_id
FROM IGS_AS_EXM_LOC_SPVSR els,
IGS_GR_VENUE ve
WHERE els.exam_location_cd = ve.exam_location_cd AND
ve.venue_cd = cp_venue_cd;
SELECT esvv.ese_id,
esvv.venue_cd
FROM IGS_AS_ESE_VENUE_V esvv
WHERE esvv.exam_cal_type = p_exam_cal_type AND
esvv.exam_ci_sequence_number = p_exam_ci_sequence_number AND
esvv.venue_cd = p_venue_cd;
SELECT esu.EXAM_SUPERVISOR_TYPE
FROM IGS_AS_EXM_SUPRVISOR esu
WHERE esu.person_id = p_person_id;
SELECT esu.EXAM_SUPERVISOR_TYPE
FROM IGS_AS_EXM_SUPRVISOR esu
WHERE esu.person_id = p_person_id;
SELECT ei.ass_id
FROM IGS_AS_EXAM_INSTANCE ei
WHERE ei.ese_id = p_ese_id AND
ei.venue_cd = p_venue_cd;
IGS_AS_EXM_SES_VN_SP_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_PERSON_ID => p_person_id,
X_EXAM_CAL_TYPE => NULL,
X_EXAM_CI_SEQUENCE_NUMBER => NULL,
X_DT_ALIAS => NULL,
X_DAI_SEQUENCE_NUMBER => NULL,
X_START_TIME => NULL,
X_END_TIME => NULL,
X_VENUE_CD => p_venue_cd,
X_ESE_ID => p_ese_id,
X_EXAM_SUPERVISOR_TYPE => v_exam_supervisor_type,
X_OVERRIDE_START_TIME => NULL,
X_OVERRIDE_END_TIME => NULL,
X_MODE => 'R'
);
IGS_AS_EXM_INS_SPVSR_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_PERSON_ID => p_person_id,
X_ASS_ID => v_ei_rec.ass_id,
X_EXAM_CAL_TYPE => NULL,
X_EXAM_CI_SEQUENCE_NUMBER => NULL,
X_DT_ALIAS => NULL,
X_DAI_SEQUENCE_NUMBER => NULL,
X_START_TIME => NULL,
X_END_TIME => NULL,
X_VENUE_CD => p_venue_cd,
X_ESE_ID => p_ese_id,
X_EXAM_SUPERVISOR_TYPE => v_exam_supervisor_type,
X_OVERRIDE_START_TIME => NULL,
X_OVERRIDE_END_TIME => NULL,
X_MODE => 'R'
);
SELECT cst_exist_ind
FROM IGS_AS_GRD_SCHEMA
WHERE grading_schema_cd = cp_new_grading_schema_cd AND
version_number = cp_new_version_number;
SELECT cst_exist_ind
FROM IGS_AS_GRD_SCHEMA
WHERE grading_schema_cd = cp_old_grading_schema_cd AND
version_number = cp_old_version_number;
SELECT *
FROM IGS_AS_GRD_SCH_GRADE
WHERE grading_schema_cd = cp_old_grading_schema_cd AND
version_number = cp_old_version_number AND
closed_ind = 'N';
IGS_AS_GRD_SCH_GRADE_PKG.INSERT_ROW(
X_ROWID => x_rowid,
X_GRADING_SCHEMA_CD => p_new_grading_schema_cd,
X_VERSION_NUMBER => p_new_version_number,
X_GRADE => v_gsg_rec.grade,
X_FULL_GRADE_NAME => v_gsg_rec.full_grade_name,
X_S_RESULT_TYPE => v_gsg_rec.s_result_type,
X_SHOW_ON_NOTICEBOARD_IND => v_gsg_rec.show_on_noticeboard_ind,
X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_gsg_rec.show_on_official_ntfctn_ind,
X_S_SPECIAL_GRADE_TYPE => NULL,
X_SHOW_IN_NEWSPAPER_IND => v_gsg_rec.show_in_newspaper_ind,
X_SHOW_INTERNALLY_IND => v_gsg_rec.show_internally_ind,
X_SYSTEM_ONLY_IND => v_gsg_rec.system_only_ind,
X_DFLT_OUTSTANDING_IND => v_gsg_rec.dflt_outstanding_ind,
X_EXTERNAL_GRADE => v_gsg_rec.external_grade,
X_LOWER_MARK_RANGE => v_gsg_rec.lower_mark_range,
X_UPPER_MARK_RANGE => v_gsg_rec.upper_mark_range,
X_MIN_PERCENTAGE => v_gsg_rec.min_percentage,
X_MAX_PERCENTAGE => v_gsg_rec.max_percentage,
X_GPA_VAL => v_gsg_rec.gpa_val,
X_RANK => v_gsg_rec.rank,
X_SHOW_IN_EARNED_CRDT_IND => v_gsg_rec.show_in_earned_crdt_ind,
X_INCL_IN_REPEAT_PROCESS_IND => v_gsg_rec.incl_in_repeat_process_ind,
X_ADMIN_ONLY_IND => v_gsg_rec.admin_only_ind,
X_GRADING_PERIOD_CD => v_gsg_rec.grading_period_cd,
X_REPEAT_GRADE => v_gsg_rec.repeat_grade,
X_MODE => 'R',
X_Attribute_Category => v_gsg_rec.Attribute_Category,
X_Attribute1 => v_gsg_rec.Attribute1,
X_Attribute2 => v_gsg_rec.Attribute2,
X_Attribute3 => v_gsg_rec.Attribute3,
X_Attribute4 => v_gsg_rec.Attribute4,
X_Attribute5 => v_gsg_rec.Attribute5,
X_Attribute6 => v_gsg_rec.Attribute6,
X_Attribute7 => v_gsg_rec.Attribute7,
X_Attribute8 => v_gsg_rec.Attribute8,
X_Attribute9 => v_gsg_rec.Attribute9,
X_Attribute10 => v_gsg_rec.Attribute10,
X_Attribute11 => v_gsg_rec.Attribute11,
X_Attribute12 => v_gsg_rec.Attribute12,
X_Attribute13 => v_gsg_rec.Attribute13,
X_Attribute14 => v_gsg_rec.Attribute14,
X_Attribute15 => v_gsg_rec.Attribute15,
X_Attribute16 => v_gsg_rec.Attribute16,
X_Attribute17 => v_gsg_rec.Attribute17,
X_Attribute18 => v_gsg_rec.Attribute18,
X_Attribute19 => v_gsg_rec.Attribute19,
X_Attribute20 => v_gsg_rec.Attribute20,
X_CLOSED_IND => 'N');