[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT us.title
FROM igs_as_su_setatmpt susa,
igs_en_unit_set us,
igs_en_unit_set_cat usc
WHERE p_person_id = susa.person_id
AND p_course_cd = susa.course_cd
AND (igs_en_gen_015.get_effective_census_date (
NULL,
NULL,
p_teach_cal_type,
p_teach_ci_sequence_number
) BETWEEN susa.selection_dt
AND NVL (susa.rqrmnts_complete_dt,
NVL (susa.end_dt,
fnd_date.canonical_to_date ('9999/12/31'))))
AND susa.unit_set_cd = us.unit_set_cd
AND us.unit_set_cat = usc.unit_set_cat
AND usc.s_unit_set_cat = 'PRENRL_YR'
ORDER BY susa.selection_dt DESC;
p_delete_rows IN VARCHAR2 DEFAULT 'Y'
) IS
BEGIN
DECLARE
p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
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 unit_class = cp_unit_class
AND location_cd = cp_location_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,
ass_id,
assessment_type,
reference,
grade,
outcome_comment_code,
mark,
error_code,
ROWID,
unit_class,
location_cd,
override_due_dt,
penalty_applied_flag,
waived_flag,
submitted_date,
uoo_id
FROM igs_as_aio_interface
WHERE user_id = p_user_id
AND trunc(batch_date) = trunc(p_batch_date)
AND ass_id IS NOT NULL;
SELECT suaai.*, suaai.rowid
FROM igs_as_su_atmpt_itm suaai
WHERE suaai.person_id = cp_person_id
AND suaai.course_cd = cp_course_cd
AND suaai.uoo_id = cp_uoo_id
AND suaai.ass_id = cp_ass_id
AND igs_as_gen_003.assp_get_ai_ref (suaai.unit_section_ass_item_id, suaai.unit_ass_item_id) = cp_reference
AND suaai.logical_delete_dt IS NULL;
v_insert_flag VARCHAR2 (1);
v_insert_batch VARCHAR2 (1);
' p_delete_rows => '||p_delete_rows|| ';'
v_insert_flag := 'Y';
v_insert_batch := 'Y';
UPDATE igs_as_aio_interface
SET error_code = 'IGS_EN_PERSON_NO_RESP'
WHERE ROWID = v_aio_upld.ROWID;
v_insert_flag,
v_load_flag,
v_aio_upld.unit_class,
v_aio_upld.location_cd,
v_aio_upld.override_due_dt,
v_aio_upld.penalty_applied_flag,
v_aio_upld.waived_flag,
v_aio_upld.submitted_date,
v_aio_upld.uoo_id
);
IF v_insert_flag = 'N' THEN
v_insert_batch := 'N';
UPDATE igs_as_aio_interface
SET error_code = v_error_code,
grade = v_grade
WHERE ROWID = v_aio_upld.ROWID;
IF v_insert_batch = 'Y' THEN
FOR v_aio_upld IN c_upload_outcome_ai LOOP
IF (v_aio_upld.ERROR_CODE IS NULL OR
(v_aio_upld.ERROR_CODE IS NOT NULL AND
v_aio_upld.ERROR_CODE <> 'IGS_EN_PERSON_NO_RESP')) THEN
--
-- Initialize variables here.
--
v_cal_type := v_aio_upld.cal_type;
v_insert_flag,
v_load_flag,
v_aio_upld.unit_class,
v_aio_upld.location_cd,
v_aio_upld.override_due_dt,
v_aio_upld.penalty_applied_flag,
v_aio_upld.waived_flag,
v_aio_upld.submitted_date,
v_aio_upld.uoo_id
);
igs_as_su_atmpt_itm_pkg.update_row (
x_rowid => rec_suaai.rowid,
x_person_id => rec_suaai.person_id,
x_course_cd => rec_suaai.course_cd,
x_unit_cd => rec_suaai.unit_cd,
x_cal_type => rec_suaai.cal_type,
x_ci_sequence_number => rec_suaai.ci_sequence_number,
x_ass_id => rec_suaai.ass_id,
x_creation_dt => rec_suaai.creation_dt,
x_attempt_number => rec_suaai.attempt_number,
x_outcome_dt => SYSDATE,
x_override_due_dt => v_aio_upld.override_due_dt,
x_tracking_id => rec_suaai.tracking_id,
x_logical_delete_dt => rec_suaai.logical_delete_dt,
x_s_default_ind => rec_suaai.s_default_ind,
x_ass_pattern_id => rec_suaai.ass_pattern_id,
x_mode => 'S',
x_grading_schema_cd => rec_suaai.grading_schema_cd,
x_gs_version_number => rec_suaai.gs_version_number,
x_grade => v_aio_upld.grade,
x_outcome_comment_code => 'UPLOAD',
x_mark => v_aio_upld.mark,
x_attribute_category => rec_suaai.attribute_category,
x_attribute1 => rec_suaai.attribute1,
x_attribute2 => rec_suaai.attribute2,
x_attribute3 => rec_suaai.attribute3,
x_attribute4 => rec_suaai.attribute4,
x_attribute5 => rec_suaai.attribute5,
x_attribute6 => rec_suaai.attribute6,
x_attribute7 => rec_suaai.attribute7,
x_attribute8 => rec_suaai.attribute8,
x_attribute9 => rec_suaai.attribute9,
x_attribute10 => rec_suaai.attribute10,
x_attribute11 => rec_suaai.attribute11,
x_attribute12 => rec_suaai.attribute12,
x_attribute13 => rec_suaai.attribute13,
x_attribute14 => rec_suaai.attribute14,
x_attribute15 => rec_suaai.attribute15,
x_attribute16 => rec_suaai.attribute16,
x_attribute17 => rec_suaai.attribute17,
x_attribute18 => rec_suaai.attribute18,
x_attribute19 => rec_suaai.attribute19,
x_attribute20 => rec_suaai.attribute20,
x_uoo_id => rec_suaai.uoo_id,
x_unit_section_ass_item_id => rec_suaai.unit_section_ass_item_id,
x_unit_ass_item_id => rec_suaai.unit_ass_item_id,
x_sua_ass_item_group_id => rec_suaai.sua_ass_item_group_id,
x_midterm_mandatory_type_code => rec_suaai.midterm_mandatory_type_code,
x_midterm_weight_qty => rec_suaai.midterm_weight_qty,
x_final_mandatory_type_code => rec_suaai.final_mandatory_type_code,
x_final_weight_qty => rec_suaai.final_weight_qty,
x_submitted_date => v_aio_upld.submitted_date,
x_waived_flag => v_aio_upld.waived_flag,
x_penalty_applied_flag => v_aio_upld.penalty_applied_flag
);
UPDATE igs_as_aio_interface
SET ERROR_CODE = errbuf
WHERE ROWID = v_aio_upld.ROWID;
END IF; --insert_batch is 'Y' or not
/* Call Reports for generating error report with parameter and after that delete the records from Report only
by calling after report trigger.
ERR_REPORT (p_user_id,p_batch_date,p_delete_rows,p_header_message)*/
/* Extracting WebADI from Concurrent Program LOV */
IF p_grade_creation_method_type <> 'WEBADI' THEN
v_request_id :=
fnd_request.submit_request ('IGS', 'IGSASS25', NULL, NULL, FALSE, p_user_id, p_batch_datetime, p_delete_rows);
p_delete_rows IN VARCHAR2
) IS
p_batch_date DATE := to_date(p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
IS SELECT ugi.error_code from IGS_AS_UG_INTERFACE UGI
WHERE ( (PERSON_number = cp_person_number OR ANONYMOUS_ID = CP_ANONYMOUS_ID)
AND user_id = cp_user_id and trunc(batch_date) = trunc(cp_batch_date)
AND uoo_id = cp_uoo_id and ERROR_CODE IS NOT NULL
);
CURSOR CUR_AIO_NO_ERR IS SELECT aio.* from IGS_AS_AIO_INTERFACE AIO
WHERE user_id = p_user_id and trunc(batch_date) = trunc(p_batch_date)
AND ASS_ID IS NULL
AND ERROR_CODE IS NULL
FOR UPDATE OF ERROR_CODE;
p_delete_rows
);
p_delete_rows
);
UPDATE IGS_AS_AIO_INTERFACE SET ERROR_CODE = v_error_code_toaio WHERE CURRENT OF CUR_AIO_NO_ERR;
p_insert_flag OUT NOCOPY VARCHAR2,
p_load_flag OUT NOCOPY VARCHAR2,
p_unit_class IN VARCHAR2 DEFAULT NULL,
p_location_cd IN VARCHAR2 DEFAULT NULL,
p_override_due_dt IN DATE DEFAULT NULL,
p_penalty_applied_flag IN VARCHAR2 DEFAULT NULL,
p_waived_flag IN VARCHAR2 DEFAULT NULL,
p_submitted_date IN DATE DEFAULT NULL,
p_uoo_id IN NUMBER
) IS
--
v_no_program_status VARCHAR2 (30);
SELECT ci.cal_type,
ci.sequence_number
FROM igs_ca_inst_all ci,
igs_ca_type cat,
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 cat.cal_type = ci.cal_type
AND cat.s_cal_cat = 'TEACHING'
AND cs.cal_status = ci.cal_status
AND cs.s_cal_status = 'ACTIVE';
SELECT party_id
FROM hz_parties hzp
WHERE hzp.party_number = p_person_number;
SELECT ai.ass_id,
gs.grading_schema_cd,
gs.version_number
FROM igs_as_assessmnt_itm ai,
igs_as_grd_schema gs
WHERE (ai.assessment_type = p_assessment_type
OR p_assessment_type IS NULL
)
AND (ai.ass_id = p_ass_id
OR p_ass_id IS NULL
)
AND (EXISTS ( SELECT 'X'
FROM igs_ps_unitass_item uooai,
igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = cp_uoo_id
AND uooai.uoo_id = uoo.uoo_id
AND uooai.ass_id = ai.ass_id
AND uooai.logical_delete_dt IS NULL
AND (uooai.REFERENCE = p_reference
OR p_reference IS NULL
)
AND uooai.grading_schema_cd = gs.grading_schema_cd
AND uooai.gs_version_number = gs.version_number)
OR EXISTS ( SELECT 'X'
FROM igs_as_unitass_item uoai,
igs_ps_unit_ofr_opt uoo
WHERE uoo.uoo_id = cp_uoo_id
AND uoai.unit_cd = uoo.unit_cd
AND uoai.version_number = uoo.version_number
AND uoai.cal_type = uoo.cal_type
AND uoai.ci_sequence_number = uoo.ci_sequence_number
AND uoai.ass_id = ai.ass_id
AND uoai.logical_delete_dt IS NULL
AND (uoai.REFERENCE = p_reference
OR p_reference IS NULL
)
AND uoai.grading_schema_cd = gs.grading_schema_cd
AND uoai.gs_version_number = gs.version_number
AND NOT EXISTS ( SELECT 'X'
FROM igs_ps_unitass_item uooai
WHERE uooai.uoo_id = uoo.uoo_id
AND uooai.logical_delete_dt IS NULL
AND uooai.ass_id = ai.ass_id)));
SELECT uoo_id
FROM igs_en_su_attempt_all
WHERE unit_cd = p_unit_cd
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND person_id = cp_person_id
AND course_cd = p_course_cd
AND unit_class = p_unit_class
AND location_cd = p_location_cd;
SELECT course_attempt_status
FROM igs_en_stdnt_ps_att_all
WHERE person_id = cp_person_id
AND course_cd = p_course_cd;
SELECT unit_attempt_status
FROM igs_en_su_attempt_all
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT 'X'
FROM igs_as_su_atmpt_itm sai
WHERE sai.person_id = cp_person_id
AND sai.course_cd = p_course_cd
AND sai.uoo_id = cp_uoo_id
AND sai.ass_id = cp_ass_id
AND igs_as_gen_003.assp_get_ai_ref (sai.unit_section_ass_item_id, sai.unit_ass_item_id) = cp_reference
AND sai.logical_delete_dt IS NULL;
SELECT sai.outcome_dt outcome_dt
FROM igs_as_su_atmpt_itm sai
WHERE sai.person_id = cp_person_id
AND sai.course_cd = p_course_cd
AND sai.uoo_id = cp_uoo_id
AND sai.ass_id = cp_ass_id
AND igs_as_gen_003.assp_get_ai_ref (sai.unit_section_ass_item_id, sai.unit_ass_item_id) = cp_reference
AND sai.logical_delete_dt IS NULL;
SELECT gsg.lower_mark_range,
gsg.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
AND system_only_ind = 'N'
AND gsg.grade = p_grade;
SELECT *
FROM igs_as_entry_conf
WHERE s_control_num = 1;
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;
p_insert_flag := 'Y';
p_insert_flag := 'N';
p_insert_flag := 'N';
p_insert_flag := 'N';
SELECT upld_person_no_exist
INTO v_upld_person_no_exist
FROM igs_as_entry_conf;
p_insert_flag := 'N';
p_insert_flag := 'N';
p_insert_flag := 'N';
SELECT upld_crs_not_enrolled
INTO v_upld_crs_not_enrolled
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_unit_not_enrolled
INTO v_upld_unit_not_enrolled
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_unit_discont
INTO v_upld_unit_discont
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_unit_not_enrolled
INTO v_upld_unit_not_enrolled
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_asmnt_item_not_exist
INTO v_upld_asmnt_item_not_exist
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_asmnt_item_grd_exist
INTO v_upld_asmnt_grade_exist
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_grade_invalid
INTO v_upld_grade_invalid
FROM igs_as_entry_conf;
p_insert_flag := 'N';
SELECT upld_mark_grade_invalid
INTO v_upld_mark_grade_invalid
FROM igs_as_entry_conf;
p_insert_flag := 'N';
p_insert_flag := 'N';
p_insert_flag := 'N';
p_error_code := 'More then One row retirve for a select statment';
p_insert_flag := 'N';
SELECT person_party_id
FROM fnd_user
WHERE user_id = p_userid;
SELECT 1
FROM igs_ps_usec_tch_resp
WHERE instructor_id = cp_inst_id AND uoo_id = p_uoo_id;
SELECT 1
FROM igs_as_x_usec_classlist_v
WHERE instructor_id = cp_inst_id AND GROUP_ID = p_group_id;