[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_rows IN VARCHAR2 DEFAULT 'Y'
) IS
--
p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
SELECT user_id,
batch_date,
decode(person_number,'-',null,person_number) person_number,
decode(anonymous_id,'-',null,anonymous_id) anonymous_id,
course_cd,
unit_cd,
cal_type,
ci_sequence_number,
alternate_code,
grade,
mark,
grading_period_cd,
incomp_default_mark,
incomp_default_grade,
incomp_deadline_date,
comments,
error_code,
rowid,
unit_class,
location_cd,
uoo_id,
manual_override_flag,
mark_capped_flag,
release_date
FROM igs_as_ug_interface ugi
WHERE ugi.user_id = p_user_id
AND trunc(ugi.batch_date) = trunc(p_batch_date)
AND NVL(grading_period_cd , '-') <> '-';
SELECT user_id,
batch_date,
decode(person_number,'-',null,person_number) person_number,
decode(anonymous_id,'-',null,anonymous_id) anonymous_id,
course_cd,
unit_cd,
cal_type,
ci_sequence_number ,
alternate_code,
grade,
mark,
grading_period_cd ,
incomp_default_mark,
incomp_default_grade,
incomp_deadline_date,
comments,
error_code,
rowid,
unit_class,
location_cd,
uoo_id,
manual_override_flag,
mark_capped_flag,
release_date
FROM igs_as_ug_interface ugi
WHERE ugi.user_id=p_user_id
AND trunc(ugi.batch_date) = trunc(p_batch_date)
AND (error_code IS NULL
OR error_code IN ('IGS_AS_ASW_GP_GRADE_EXIST', 'IGS_AS_ASW_MARK_GRADE_INVALID'))
AND NVL(grading_period_cd, '-') <> '-';
SELECT sua.uoo_id
FROM igs_en_su_attempt sua
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.unit_cd = cp_unit_cd
AND sua.cal_type = cp_cal_type
AND sua.ci_sequence_number = cp_ci_sequence_number
AND sua.location_cd = cp_location_cd
AND sua.unit_class = cp_unit_class;
SELECT ROWID
FROM igs_as_su_stmptout_all suao
WHERE suao.person_id = cp_person_id
AND suao.course_cd = cp_course_cd
AND suao.uoo_id = cp_uoo_id
AND suao.grading_period_cd = cp_grading_period_cd
AND suao.finalised_outcome_ind = 'N';
SELECT suao.*
FROM igs_as_su_stmptout_all suao
WHERE ROWID = cp_rowid;
' p_delete_rows => '||p_delete_rows|| ';'
UPDATE igs_as_ug_interface
SET error_code = 'IGS_EN_PERSON_NO_RESP'
WHERE ROWID = v_ugi_rec.ROWID;
UPDATE igs_as_ug_interface
SET error_code = v_error_code,
grade = v_grade
WHERE ROWID = v_ugi_rec.ROWID;
igs_as_su_stmptout_pkg.update_row (
x_rowid => v_rowid,
x_person_id => v_person_id,
x_course_cd => v_ugi_rec.course_cd,
x_unit_cd => v_ugi_rec.unit_cd,
x_cal_type => v_cal_type,
x_ci_sequence_number => v_ci_sequence_number,
x_outcome_dt => v_outcome_dt,
x_ci_start_dt => v_ci_start_dt,
x_ci_end_dt => v_ci_end_dt,
x_grading_schema_cd => v_grading_schema_cd,
x_version_number => v_gs_version_number,
x_grade => v_ugi_rec.grade,
x_s_grade_creation_method_type => v_s_grade_creation_method_type,
x_finalised_outcome_ind => v_finalised_outcome_ind,
x_mark => v_ugi_rec.mark,
x_number_times_keyed => v_number_times_keyed,
x_translated_grading_schema_cd => rec_suao_record.translated_grading_schema_cd,
x_translated_version_number => rec_suao_record.translated_version_number,
x_translated_grade => rec_suao_record.translated_grade,
x_translated_dt => rec_suao_record.translated_dt,
x_mode => 'S',
x_grading_period_cd => v_ugi_rec.grading_period_cd,
x_attribute_category => rec_suao_record.attribute_category,
x_attribute1 => rec_suao_record.attribute1,
x_attribute2 => rec_suao_record.attribute2,
x_attribute3 => rec_suao_record.attribute3,
x_attribute4 => rec_suao_record.attribute4,
x_attribute5 => rec_suao_record.attribute5,
x_attribute6 => rec_suao_record.attribute6,
x_attribute7 => rec_suao_record.attribute7,
x_attribute8 => rec_suao_record.attribute8,
x_attribute9 => rec_suao_record.attribute9,
x_attribute10 => rec_suao_record.attribute10,
x_attribute11 => rec_suao_record.attribute11,
x_attribute12 => rec_suao_record.attribute12,
x_attribute13 => rec_suao_record.attribute13,
x_attribute14 => rec_suao_record.attribute14,
x_attribute15 => rec_suao_record.attribute15,
x_attribute16 => rec_suao_record.attribute16,
x_attribute17 => rec_suao_record.attribute17,
x_attribute18 => rec_suao_record.attribute18,
x_attribute19 => rec_suao_record.attribute19,
x_attribute20 => rec_suao_record.attribute20,
x_incomp_deadline_date => v_ugi_rec.incomp_deadline_date,
x_incomp_grading_schema_cd => rec_suao_record.incomp_grading_schema_cd,
x_incomp_version_number => rec_suao_record.incomp_version_number,
x_incomp_default_grade => v_ugi_rec.incomp_default_grade,
x_incomp_default_mark => v_ugi_rec.incomp_default_mark,
x_comments => v_ugi_rec.comments,
x_uoo_id => v_ugi_rec.uoo_id,
x_mark_capped_flag => v_ugi_rec.mark_capped_flag,
x_release_date => v_ugi_rec.release_date,
x_manual_override_flag => v_ugi_rec.manual_override_flag,
x_show_on_academic_histry_flag => rec_suao_record.show_on_academic_histry_flag
);
UPDATE igs_as_ug_interface
SET error_code = errbuf
WHERE rowid = v_ugi_rec.rowid;
igs_as_su_stmptout_pkg.insert_row (
x_rowid => v_rowid,
x_org_id => NULL,
x_person_id => v_person_id,
x_course_cd => v_ugi_rec.course_cd,
x_unit_cd => v_ugi_rec.unit_cd,
x_cal_type => v_cal_type,
x_ci_sequence_number => v_ci_sequence_number,
x_outcome_dt => v_outcome_dt,
x_ci_start_dt => v_ci_start_dt,
x_ci_end_dt => v_ci_end_dt,
x_grading_schema_cd => v_grading_schema_cd,
x_version_number => v_gs_version_number,
x_grade => v_ugi_rec.grade,
x_s_grade_creation_method_type => v_s_grade_creation_method_type,
x_finalised_outcome_ind => v_finalised_outcome_ind,
x_mark => v_ugi_rec.mark,
x_number_times_keyed => v_number_times_keyed,
x_translated_grading_schema_cd => NULL,
x_translated_version_number => NULL,
x_translated_grade => NULL,
x_translated_dt => NULL,
x_mode => 'S',
x_grading_period_cd => v_ugi_rec.grading_period_cd,
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_incomp_deadline_date => v_ugi_rec.incomp_deadline_date,
x_incomp_grading_schema_cd => NULL,
x_incomp_version_number => NULL,
x_incomp_default_grade => v_ugi_rec.incomp_default_grade,
x_incomp_default_mark => v_ugi_rec.incomp_default_mark,
x_comments => v_ugi_rec.comments,
x_uoo_id => v_ugi_rec.uoo_id,
x_mark_capped_flag => v_ugi_rec.mark_capped_flag,
x_release_date => v_ugi_rec.release_date,
x_manual_override_flag => v_ugi_rec.manual_override_flag,
x_show_on_academic_histry_flag => 'Y'
);
UPDATE igs_as_ug_interface
SET error_code = errbuf
WHERE rowid = v_ugi_rec.rowid;
v_request_id := fnd_request.submit_request ('IGS', 'IGSASS24', NULL, NULL, FALSE, p_user_id, p_batch_datetime, p_delete_rows);
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = p_unit_cd
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_ca_da_inst_v
WHERE cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND dt_alias = (SELECT DECODE (
p_grading_period_cd,
'FINAL', final_mgs_start_dt_alias,
'MIDTERM', mid_mgs_start_dt_alias,
'EARLY_FINAL', efinal_mgs_start_dt_alias,
NULL
)
FROM igs_as_cal_conf
WHERE s_control_num = 1)
AND alias_val <= SYSDATE;
SELECT key_grade_derive_ind,
key_allow_invalid_ind,
upld_person_no_exist,
upld_crs_not_enrolled,
upld_unit_not_enrolled,
upld_unit_discont,
upld_grade_invalid,
upld_mark_grade_invalid,
upld_ug_sbmtd_grade_exist,
upld_ug_saved_grade_exist,
key_mark_mndtry_ind,
key_mark_entry_dec_points
FROM igs_as_entry_conf ec
WHERE ec.s_control_num = 1;
SELECT ci.cal_type,
ci.sequence_number,
ci.start_dt,
ci.end_dt
FROM igs_ca_inst_all ci,
igs_ca_type ct,
igs_ca_stat cs
WHERE /* (ci.alternate_code = p_alternate_code
OR p_alternate_code IS NULL
)
AND */ ((ci.cal_type = p_cal_type
AND ci.sequence_number = p_ci_sequence_number
)
OR p_cal_type IS NULL
)
AND ct.cal_type = ci.cal_type
AND ct.s_cal_cat = 'TEACHING'
AND cs.cal_status = ci.cal_status
AND cs.s_cal_status = 'ACTIVE';
SELECT p.party_id
FROM hz_parties p
WHERE p.party_number = p_person_number;
SELECT spa.course_attempt_status
FROM igs_en_stdnt_ps_att spa
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd;
SELECT sua.unit_attempt_status,
NVL (ugs.grading_schema_code, ungs.GRADING_SCHEMA_CODE),
NVL (ugs.grd_schm_version_number, ungs.GRD_SCHM_VERSION_NUMBER)
FROM igs_en_su_attempt sua,
--igs_ps_unit_ofr_opt uoo,
IGS_PS_UNIT_GRD_SCHM ungs,
igs_ps_usec_grd_schm ugs
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_cd = p_unit_cd
AND sua.uoo_id = cp_uoo_id
AND ungs.unit_code = sua.unit_cd
AND ungs.unit_version_number = sua.version_number
AND ungs.default_flag = 'Y'
AND sua.uoo_id = ugs.uoo_id(+)
AND ugs.default_flag(+) = 'Y';
SELECT suao.mark,
suao.grade,
suao.finalised_outcome_ind
FROM igs_as_su_stmptout_all suao
WHERE suao.person_id = p_person_id
AND suao.course_cd = p_course_cd
-- anilk, 22-Apr-2003, Bug# 2829262
AND suao.uoo_id = cp_uoo_id
AND suao.grading_period_cd = p_grading_period_cd;
SELECT gsg.lower_mark_range,
gsg.upper_mark_range,
gsg.s_result_type
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number
AND gsg.grade = p_grade
AND gsg.system_only_ind = 'N';
SELECT gsg.grade
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number
AND gsg.system_only_ind = 'N'
AND p_mark BETWEEN lower_mark_range AND upper_mark_range + 0.999;
SELECT gsh.submission_status
FROM igs_as_gaa_sub_hist gsh
WHERE gsh.uoo_id = cp_uoo_id
AND gsh.grading_period_cd = p_grading_period_cd;
SELECT 'X'
FROM DUAL
WHERE EXISTS ( SELECT 'X'
FROM igs_as_gpc_programs gpr
WHERE gpr.course_cd = p_course_cd
AND gpr.grading_period_cd = p_grading_period_cd)
OR EXISTS ( SELECT 'X'
FROM igs_en_stdnt_ps_att spa,
igs_as_gpc_aca_stndg gas
WHERE spa.person_id = p_person_id
AND spa.course_cd = p_course_cd
AND spa.progression_status = gas.progression_status
AND gas.grading_period_cd = p_grading_period_cd)
OR EXISTS ( SELECT 'X'
FROM igs_pe_prsid_grp_mem pigm,
igs_as_gpc_pe_id_grp gpg
WHERE p_person_id = pigm.person_id
AND pigm.GROUP_ID = gpg.GROUP_ID
AND gpg.grading_period_cd = p_grading_period_cd)
OR EXISTS ( SELECT 'X'
FROM igs_as_gpc_cls_stndg gcs
WHERE gcs.class_standing = igs_pr_get_class_std.get_class_standing (
p_person_id,
p_course_cd,
'N',
SYSDATE,
NULL,
NULL
)
AND gcs.grading_period_cd = p_grading_period_cd)
OR EXISTS ( SELECT 'X'
FROM igs_as_su_setatmpt susa,
igs_as_gpc_unit_sets gus
WHERE susa.person_id = p_person_id
AND susa.course_cd = p_course_cd
AND susa.selection_dt IS NOT NULL
AND (susa.end_dt IS NULL
OR susa.rqrmnts_complete_ind = 'Y'
)
AND susa.unit_set_cd = gus.unit_set_cd
AND gus.grading_period_cd = p_grading_period_cd);
SELECT NVL (MIN (gsg.lower_mark_range), 0) min_lower_mark_range,
NVL (MAX (gsg.upper_mark_range), 1000) max_upper_mark_range
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number;