[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Performs all the post insert operations on the table IGS_AS_SU_STMPTOUT_ALL.
Called from the procedure create_unit_outcome
*/
PROCEDURE create_post_unit_outcome (
p_person_id IGS_PE_PERSON.PERSON_ID%TYPE ,
p_cal_type IGS_CA_INST.CAL_TYPE%TYPE ,
p_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
p_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE ,
p_lgcy_suo_rec LGCY_SUO_REC_TYPE
);
|| 5. Insert data into table IGS_AS_SU_STMPTOUT_ALL -- direct DML operation in this procedure
|| 6. Perform post insert operations on table IGS_AS_SU_STMPTOUT_ALL -- call procedure create_post_unit_outcome
||Know limitations, enhancements or remarks
||Change History
||Who When What
||(reverse chronological order - newest change first)
*************************************************************************************************************************/
IS
l_api_name CONSTANT VARCHAR2(30) := 'create_unit_outcome' ;
Insert a record in the table igs_as_su_stmptout_all
*/
gen_log_info('Start of create_unit_outcome.validation 5');
INSERT INTO IGS_AS_SU_STMPTOUT_ALL
(
PERSON_ID ,
COURSE_CD ,
UNIT_CD ,
CAL_TYPE ,
CI_SEQUENCE_NUMBER ,
CI_START_DT ,
CI_END_DT ,
OUTCOME_DT ,
GRADING_SCHEMA_CD ,
VERSION_NUMBER ,
GRADE ,
S_GRADE_CREATION_METHOD_TYPE ,
FINALISED_OUTCOME_IND ,
MARK ,
NUMBER_TIMES_KEYED ,
TRANSLATED_GRADING_SCHEMA_CD ,
TRANSLATED_VERSION_NUMBER ,
TRANSLATED_GRADE ,
TRANSLATED_DT ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
ORG_ID ,
GRADING_PERIOD_CD ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
ATTRIBUTE16 ,
ATTRIBUTE17 ,
ATTRIBUTE18 ,
ATTRIBUTE19 ,
ATTRIBUTE20 ,
INCOMP_DEADLINE_DATE ,
INCOMP_GRADING_SCHEMA_CD ,
INCOMP_VERSION_NUMBER ,
INCOMP_DEFAULT_GRADE ,
INCOMP_DEFAULT_MARK ,
COMMENTS,
UOO_ID,
RELEASE_DATE
)
VALUES
(
l_person_id ,
l_lgcy_suo_rec.program_cd ,
l_lgcy_suo_rec.unit_cd ,
l_cal_type ,
l_sequence_number ,
l_start_dt ,
l_end_dt ,
l_lgcy_suo_rec.outcome_dt ,
l_lgcy_suo_rec.grading_schema_cd ,
l_lgcy_suo_rec.version_number ,
l_lgcy_suo_rec.grade ,
l_lgcy_suo_rec.s_grade_creation_method_type ,
l_lgcy_suo_rec.finalised_outcome_ind ,
l_lgcy_suo_rec.mark ,
l_number_of_times ,
l_translated_grading_schema_cd ,
l_translated_version_number ,
l_translated_grade ,
l_translated_dt ,
nvl(fnd_global.user_id,-1) ,
SYSDATE ,
NVL(fnd_global.user_id,-1) ,
sysdate ,
NVL(fnd_global.login_id,-1) ,
DECODE(fnd_global.conc_request_id,-1,null,fnd_global.conc_request_id) ,
DECODE(fnd_global.conc_request_id,-1,null,fnd_global.prog_appl_id) ,
DECODE(fnd_global.conc_request_id,-1,null,fnd_global.conc_program_id) ,
DECODE(fnd_global.conc_request_id,-1,null,sysdate) ,
igs_ge_gen_003.get_org_id ,
l_lgcy_suo_rec.grading_period_cd ,
l_lgcy_suo_rec.attribute_category ,
l_lgcy_suo_rec.attribute1 ,
l_lgcy_suo_rec.attribute2 ,
l_lgcy_suo_rec.attribute3 ,
l_lgcy_suo_rec.attribute4 ,
l_lgcy_suo_rec.attribute5 ,
l_lgcy_suo_rec.attribute6 ,
l_lgcy_suo_rec.attribute7 ,
l_lgcy_suo_rec.attribute8 ,
l_lgcy_suo_rec.attribute9 ,
l_lgcy_suo_rec.attribute10 ,
l_lgcy_suo_rec.attribute11 ,
l_lgcy_suo_rec.attribute12 ,
l_lgcy_suo_rec.attribute13 ,
l_lgcy_suo_rec.attribute14 ,
l_lgcy_suo_rec.attribute15 ,
l_lgcy_suo_rec.attribute16 ,
l_lgcy_suo_rec.attribute17 ,
l_lgcy_suo_rec.attribute18 ,
l_lgcy_suo_rec.attribute19 ,
l_lgcy_suo_rec.attribute20 ,
l_lgcy_suo_rec.incomp_deadline_date ,
l_lgcy_suo_rec.incomp_grading_schema_cd ,
l_lgcy_suo_rec.incomp_version_number ,
l_lgcy_suo_rec.incomp_default_grade ,
l_lgcy_suo_rec.incomp_default_mark ,
l_lgcy_suo_rec.comments,
l_uoo_id,
l_release_date
);
Perform Post insert activities on the table igs_as_su_stmptout_all
*/
gen_log_info('Start of create_unit_outcome.validation 6');
SELECT
'X'
FROM
igs_as_grd_schema
WHERE
grading_schema_cd = p_lgcy_suo_rec.incomp_grading_schema_cd AND
version_number = p_lgcy_suo_rec.incomp_version_number;
SELECT
'X'
FROM
igs_as_grd_sch_grade ggs
WHERE
grading_schema_cd = p_lgcy_suo_rec.incomp_grading_schema_cd AND
version_number = p_lgcy_suo_rec.incomp_version_number AND
grade = p_lgcy_suo_rec.incomp_default_grade;
SELECT
'X'
FROM
igs_as_grd_sch_grade ggs
WHERE
grading_schema_cd = p_lgcy_suo_rec.incomp_grading_schema_cd AND
version_number = p_lgcy_suo_rec.incomp_version_number AND
grade = p_lgcy_suo_rec.incomp_default_grade AND
p_lgcy_suo_rec.incomp_default_mark BETWEEN
NVL(LOWER_MARK_RANGE,p_lgcy_suo_rec.incomp_default_mark) AND
NVL(UPPER_MARK_RANGE,p_lgcy_suo_rec.incomp_default_mark) ;
SELECT
unit_attempt_status,
uoo_id
FROM
igs_en_su_attempt_all su
WHERE
su.person_id = p_person_id
AND su.course_cd = p_lgcy_suo_rec.program_cd
AND su.unit_cd = p_lgcy_suo_rec.unit_cd
AND su.cal_type = p_cal_type
AND su.ci_sequence_number = p_sequence_number
AND su.location_cd = p_lgcy_suo_rec.location_cd
AND su.unit_class = p_lgcy_suo_rec.unit_class;
SELECT
ugs.grading_schema_code ,
ugs.grd_schm_version_number
FROM igs_ps_usec_grd_schm ugs ,
igs_en_su_attempt_all en
WHERE
ugs.default_flag = 'Y' AND
ugs.uoo_id = en.uoo_id AND
en.person_id = p_person_id AND
en.course_cd = p_lgcy_suo_rec.program_cd AND
en.unit_cd = p_lgcy_suo_rec.unit_cd AND
en.cal_type = p_cal_type AND
en.ci_sequence_number = p_sequence_number AND
en.location_cd = p_lgcy_suo_rec.location_cd AND
en.unit_class = p_lgcy_suo_rec.unit_class;
SELECT
ugs.grading_schema_code ,
ugs.grd_schm_version_number
FROM
igs_ps_unit_grd_schm ugs ,
igs_en_su_attempt_all en
WHERE
ugs.default_flag = 'Y' AND
ugs.unit_code = en.unit_cd AND
ugs.unit_version_number = en.version_number AND
en.person_id = p_person_id AND
en.course_cd = p_lgcy_suo_rec.program_cd AND
en.unit_cd = p_lgcy_suo_rec.unit_cd AND
en.cal_type = p_cal_type AND
en.ci_sequence_number = p_sequence_number AND
en.location_cd = p_lgcy_suo_rec.location_cd AND
en.unit_class = p_lgcy_suo_rec.unit_class;
SELECT
'X'
FROM
igs_as_grd_sch_grade
WHERE
grading_schema_cd = cp_grading_schema_cd AND
version_number = cp_version_number AND
grade = cp_grade;
SELECT
'X'
FROM
igs_as_grd_sch_grade ggs
WHERE
grading_schema_cd = p_lgcy_suo_rec.grading_schema_cd AND
version_number = p_lgcy_suo_rec.version_number AND
s_result_type = 'INCOMP';
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_lgcy_suo_rec.program_cd
AND sua.unit_cd = p_lgcy_suo_rec.unit_cd
AND sua.cal_type = p_cal_type
AND sua.ci_sequence_number = p_sequence_number
AND sua.location_cd = p_lgcy_suo_rec.location_cd
AND sua.unit_class = p_lgcy_suo_rec.unit_class
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_lgcy_suo_rec.grading_schema_cd
AND gsgt.version_number = p_lgcy_suo_rec.version_number
AND gsgt.grade = p_lgcy_suo_rec.grade
AND gsgt.to_grading_schema_cd = cp_grading_schema_cd
AND gsgt.to_version_number = cp_gs_ver_num;
FND_MSG_PUB.DELETE_MSG (l_msg_count);
FND_MSG_PUB.DELETE_MSG (l_msg_count);
FND_MSG_PUB.DELETE_MSG (l_msg_count);
FND_MSG_PUB.DELETE_MSG (l_msg_count);
FND_MSG_PUB.DELETE_MSG (l_msg_count);
||Purpose : The procedure create_post_unit_outcome performs post insert operations on the table IGS_AS_SU_STMPTOUT_ALL
|| Called from the procedure create_unit_outcome
||
||Know limitations, enhancements or remarks
||Change History
||Who When What
||(reverse chronological order - newest change first)
*************************************************************************************************************************/
IS
BEGIN
gen_log_info('Start of PROCEDURE CREATE_POST_UNIT_OUTCOME');
UPDATE
IGS_EN_SU_ATTEMPT_ALL su
SET
unit_attempt_status = 'COMPLETED'
WHERE
su.person_id = p_person_id
AND su.course_cd = p_lgcy_suo_rec.program_cd
AND su.unit_cd = p_lgcy_suo_rec.unit_cd
AND su.cal_type = p_cal_type
AND su.ci_sequence_number = p_sequence_number
AND su.location_cd = p_lgcy_suo_rec.location_cd
AND su.unit_class = p_lgcy_suo_rec.unit_class;