[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igs_ps_usec_ref_cd refcd,
igs_ps_usec_ref usecref,
igs_ge_ref_cd_type rct
WHERE usecref.uoo_id = cp_uoo_id
AND usecref.unit_section_reference_id = refcd.unit_section_reference_id
AND refcd.reference_code_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'SUMMEAS'
UNION
SELECT 'X'
FROM igs_ps_unit_ref_cd urc, igs_ge_ref_cd_type rct
WHERE urc.unit_cd = cp_unit_cd
AND urc.version_number = cp_version_number
AND urc.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'SUMMEAS'
AND NOT EXISTS ( SELECT refcd.reference_code_type
FROM igs_ps_usec_ref_cd refcd,
igs_ps_usec_ref usecref,
igs_ps_unit_ofr_opt_all opt,
igs_ge_ref_cd_type rct
WHERE usecref.uoo_id = cp_uoo_id
AND usecref.unit_section_reference_id =
refcd.unit_section_reference_id
AND refcd.reference_code_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'SUMMEAS')
UNION
SELECT 'X'
FROM igs_ps_us_req_ref_cd refcd,
igs_ps_usec_ref usecref,
igs_ps_unit_ofr_opt_all opt,
igs_ge_ref_cd_type rct
WHERE usecref.uoo_id = cp_uoo_id
AND usecref.unit_section_reference_id = refcd.unit_section_reference_id
AND refcd.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'SUMMEAS'
UNION
SELECT 'X'
FROM igs_ps_unitreqref_cd urc, igs_ge_ref_cd_type rct
WHERE urc.unit_cd = cp_unit_cd
AND urc.version_number = cp_version_number
AND urc.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'SUMMEAS'
AND NOT EXISTS ( SELECT refcd.reference_cd_type
FROM igs_ps_us_req_ref_cd refcd,
igs_ps_usec_ref usecref,
igs_ps_unit_ofr_opt_all opt,
igs_ge_ref_cd_type rct
WHERE usecref.uoo_id = cp_uoo_id
AND usecref.unit_section_reference_id =
refcd.unit_section_reference_id
AND refcd.reference_cd_type = rct.reference_cd_type
AND rct.s_reference_cd_type = 'SUMMEAS');
SELECT upper_mark_range
FROM igs_as_grd_sch_grade
WHERE grading_schema_cd = cp_grading_schema_cd
AND version_number = cp_gs_version_number
AND grade = cp_grade;
SELECT version_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = cp_uoo_id;
SELECT NVL(uc.achievable_credit_points,
NVL(uv.achievable_credit_points,
NVL(p_override_enrolled_cp,
NVL(uc.enrolled_credit_points,uv.enrolled_credit_points
)
)
)
) sua_cp
FROM igs_ps_unit_ver uv,
igs_ps_unit_ofr_opt uoo,
igs_ps_usec_cps uc
WHERE uoo.uoo_id = cp_uoo_id
AND uoo.uoo_id = uc.uoo_id(+)
AND uv.unit_cd = cp_unit_cd
AND uv.version_number = cp_version_number;
SELECT SUM(
get_earned_cp
(
sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.unit_attempt_status,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
sua.override_achievable_cp,
sua.override_enrolled_cp
)
) avail_cp
FROM igs_en_su_attempt_all sua,
igs_ps_unit_lvl_all ul ,
igs_ps_unit_ver_all uv
WHERE sua.person_id = cp_person_id
AND sua.course_cd = p_course_cd
AND NVL(sua.core_indicator_code, 'ELECTIVE') = cp_core_ind_code --Core indicator filter
AND sua.unit_cd = uv.unit_cd
AND sua.version_number = uv.version_number
AND sua.course_cd = ul.course_cd(+)
AND sua.unit_cd = ul.unit_cd(+)
AND sua.version_number = ul.version_number(+)
AND NVL(UL.UNIT_LEVEL, UV.UNIT_LEVEL) = cp_unit_level
AND chk_if_excluded_unit (sua.uoo_id,sua.unit_cd,sua.version_number) = 'TRUE';
SELECT version_number
FROM igs_en_stdnt_ps_att
WHERE person_id = cp_person_id AND course_cd = cp_course_cd;
SELECT total_unit_level_credits, mark_config_id, selection_method_code
FROM igs_pr_ul_mark_cnfg umc
WHERE umc.unit_level = cp_unit_level
AND ( ( umc.course_cd = cp_course_cd
AND umc.version_number = cp_version_number
)
OR ( umc.course_cd IS NULL
AND umc.version_number IS NULL
AND NOT EXISTS ( SELECT 1
FROM igs_pr_ul_mark_cnfg umc_in
WHERE umc_in.unit_level = cp_unit_level
AND umc_in.course_cd = cp_course_cd
AND umc_in.version_number =
cp_version_number)
)
);
l_selection_method_code igs_pr_ul_mark_cnfg.selection_method_code%TYPE;
SELECT SUM (
get_earned_cp (
sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.unit_attempt_status,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
sua.override_achievable_cp,
sua.override_enrolled_cp
)
) total_cp
FROM igs_en_su_attempt_all sua,
igs_ps_unit_lvl_all ul,
igs_ps_unit_ver_all uv
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_course_cd
AND sua.unit_cd = uv.unit_cd
AND sua.version_number = uv.version_number
AND sua.course_cd = ul.course_cd(+)
AND sua.unit_cd = ul.unit_cd(+)
AND sua.version_number = ul.version_number(+)
AND NVL (ul.unit_level, uv.unit_level) = cp_unit_level
AND chk_if_excluded_unit (
sua.uoo_id,
sua.unit_cd,
sua.version_number
) = cp_include;
SELECT core_indicator_code, total_credits, required_flag,
priority_num, unit_selection_code
FROM igs_pr_ul_mark_dtl
WHERE mark_config_id = cp_mark_config_id
ORDER BY priority_num ASC;
p1_unit_selection_code igs_pr_ul_mark_dtl.unit_selection_code%TYPE;
p2_unit_selection_code igs_pr_ul_mark_dtl.unit_selection_code%TYPE;
p3_unit_selection_code igs_pr_ul_mark_dtl.unit_selection_code%TYPE;
SELECT core_indicator_code, total_credits, required_flag,
priority_num, unit_selection_code
FROM igs_pr_ul_mark_dtl
WHERE mark_config_id = cp_mark_config_id
ORDER BY priority_num ASC;
SELECT NVL(SUM (NVL (ulvl.unit_level_mark, 0)),0) avstdmarks,
SUM (NVL (ulvl.credit_points, 0)) avstdcp
FROM igs_av_stnd_unit_lvl_all ulvl, igs_av_adv_standing_all advstd
WHERE ulvl.person_id = advstd.person_id
AND ulvl.as_course_cd = advstd.course_cd
AND ulvl.as_version_number = advstd.version_number
AND ulvl.exemption_institution_cd = advstd.exemption_institution_cd
AND advstd.person_id = cp_person_id
AND advstd.course_cd = cp_course_cd
AND ulvl.unit_level = cp_unit_level
AND ulvl.s_adv_stnd_granting_status = 'GRANTED';
:= ' SELECT sua.unit_cd, '
|| ' sua.uoo_id, '
|| ' NVL(stmpt.mark,igs_as_calc_award_mark.get_mark '
|| ' (stmpt.grading_schema_cd, '
|| ' stmpt.version_number,stmpt.grade)) mark, '
|| ' stmpt.grade, '
|| ' igs_as_calc_award_mark.get_earned_cp '
|| ' (stmpt.person_id,stmpt.course_cd,sua.unit_cd, '
|| ' sua.version_number,sua.unit_attempt_status, '
|| ' sua.cal_type,sua.ci_sequence_number, '
|| ' sua.uoo_id,sua.override_achievable_cp, '
|| ' sua.override_enrolled_cp ) earned_cp, '
|| ' NVL(ul.wam_weighting,NVL( lvl.wam_weighting,1)) wam_weight '
|| ' FROM igs_as_su_stmptout stmpt, igs_en_su_attempt sua ,IGS_PS_UNIT_LVL_ALL UL , IGS_PS_UNIT_VER_ALL UV , IGS_PS_UNIT_LEVEL_ALL LVL '
|| ' WHERE stmpt.person_id = :1 AND stmpt.course_cd = :2 '
|| ' AND stmpt.person_id = sua.person_id '
|| ' AND stmpt.course_cd = sua.course_cd '
|| ' AND stmpt.uoo_id = sua.uoo_id '
|| ' AND NVL(sua.core_indicator_code, ''ELECTIVE'') = :3 AND '
|| ' SUA.UNIT_CD = UV.UNIT_CD AND'
|| ' SUA.VERSION_NUMBER = UV.VERSION_NUMBER AND'
|| ' SUA.COURSE_CD = UL.COURSE_CD(+) AND'
|| ' SUA.UNIT_CD = UL.UNIT_CD(+) AND'
|| ' SUA.VERSION_NUMBER = UL.VERSION_NUMBER(+) AND'
|| ' NVL(UL.UNIT_LEVEL, UV.UNIT_LEVEL) = :4 AND '
|| ' LVL.UNIT_LEVEL= UV.UNIT_LEVEL'
|| ' AND MARK IS NOT NULL '
|| 'AND sua.uoo_id = stmpt.uoo_id'
|| ' AND NVL(stmpt.mark,igs_as_calc_award_mark.get_mark '
|| ' (stmpt.grading_schema_cd, '
|| ' stmpt.version_number,stmpt.grade)) IS NOT NULL '
|| ' AND stmpt.outcome_dt = ( SELECT max(outcome_dt) '
|| ' FROM igs_as_su_stmptout suao '
|| ' WHERE suao.person_id = stmpt.person_id '
|| ' AND suao.course_cd =stmpt.course_cd '
|| ' AND suao.outcome_dt = stmpt.outcome_dt '
|| ' AND suao.grading_period_cd = stmpt. grading_period_cd '
|| ' AND suao.uoo_id= stmpt.uoo_id ) '
|| ' AND igs_as_calc_award_mark.chk_if_excluded_unit (sua.uoo_id,sua.unit_cd,sua.version_number) = ''TRUE'' ';
:= ' SELECT sua.unit_cd, '
|| ' sua.uoo_id, '
|| ' NVL(suao.mark,igs_as_calc_award_mark.get_mark '
|| ' (suao.grading_schema_cd,suao.version_number,suao.grade)) mark, '
|| ' suao.grade, '
|| ' NVL(ul.wam_weighting,NVL( lvl.wam_weighting,1)) wam_weight, '
|| ' igs_as_calc_award_mark.get_earned_cp '
|| ' (suao.person_id,suao.course_cd,sua.unit_cd, '
|| ' sua.version_number,sua.unit_attempt_status, '
|| ' sua.cal_type,sua.ci_sequence_number, '
|| ' sua.uoo_id,sua.override_achievable_cp, '
|| ' sua.override_enrolled_cp ) earned_cp '
|| ' FROM igs_as_su_stmptout_all suao, igs_en_su_attempt_all sua, '
|| ' igs_ps_unit_lvl_all ul, igs_ps_unit_ver_all uv , '
|| ' igs_ps_unit_level_all lvl '
|| ' WHERE suao.person_id = :1 AND suao.course_cd = :2 '
|| ' AND suao.person_id= sua.person_id AND suao.course_cd = sua.course_cd '
|| ' AND suao.uoo_id = sua.uoo_id AND NVL(sua.core_indicator_code, ''ELECTIVE'') = :3 '
|| ' AND sua.unit_cd = uv.unit_cd AND sua.version_number = uv.version_number '
|| ' AND sua.course_cd = ul.course_cd(+) AND sua.unit_cd = ul.unit_cd(+) '
|| ' AND sua.version_number = ul.version_number(+) '
|| ' AND NVL(ul.unit_level,uv.unit_level) = :4 '
|| ' AND lvl.unit_level = uv.unit_level '
|| ' AND NVL(suao.mark,igs_as_calc_award_mark.get_mark '
|| ' (suao.grading_schema_cd,suao.version_number,suao.grade)) IS NOT NULL '
|| ' AND suao.outcome_dt = ( SELECT max(outcome_dt) '
|| ' FROM igs_as_su_stmptout_all suao2 '
|| ' WHERE suao2.person_id = suao.person_id '
|| ' AND suao2.course_cd = suao.course_cd '
|| ' AND suao2.grading_period_cd = suao.grading_period_cd '
|| ' AND suao2.uoo_id= suao.uoo_id ) '
|| ' AND igs_as_calc_award_mark.chk_if_excluded_unit (sua.uoo_id,sua.unit_cd,sua.version_number) = ''TRUE'' ';
SELECT sua.unit_cd, sua.uoo_id,
NVL (
suao.mark,
igs_as_calc_award_mark.get_mark (
suao.grading_schema_cd,
suao.version_number,
suao.grade
)
) mark,
suao.grade,
NVL (ul.wam_weighting, NVL (lvl.wam_weighting, 1)) wam_weight,
igs_as_calc_award_mark.get_earned_cp (
suao.person_id,
suao.course_cd,
sua.unit_cd,
sua.version_number,
sua.unit_attempt_status,
sua.cal_type,
sua.ci_sequence_number,
sua.uoo_id,
sua.override_achievable_cp,
sua.override_enrolled_cp
)
earned_cp
FROM igs_as_su_stmptout_all suao,
igs_en_su_attempt_all sua,
igs_ps_unit_lvl_all ul,
igs_ps_unit_ver_all uv,
igs_ps_unit_level_all lvl
WHERE suao.person_id = cp_person_id
AND suao.course_cd = cp_course_cd
AND suao.person_id = sua.person_id
AND suao.course_cd = sua.course_cd
AND suao.uoo_id = sua.uoo_id
AND sua.unit_cd = uv.unit_cd
AND sua.version_number = uv.version_number
AND sua.course_cd = ul.course_cd(+)
AND sua.unit_cd = ul.unit_cd(+)
AND sua.version_number = ul.version_number(+)
AND NVL (ul.unit_level, uv.unit_level) = cp_unit_level
AND lvl.unit_level = uv.unit_level
AND NVL (
suao.mark,
igs_as_calc_award_mark.get_mark (
suao.grading_schema_cd,
suao.version_number,
suao.grade
)
) IS NOT NULL
AND suao.outcome_dt = (SELECT MAX (outcome_dt)
FROM igs_as_su_stmptout_all suao2
WHERE suao2.person_id = suao.person_id
AND suao2.course_cd = suao.course_cd
AND suao2.grading_period_cd =
suao.grading_period_cd
AND suao2.uoo_id = suao.uoo_id)
AND chk_if_excluded_unit (
sua.uoo_id,
sua.unit_cd,
sua.version_number
) = 'TRUE';
l_selection_method_code;
1. Select the Total Unit Level Credits for a particular unit level
2. Select the various core unit indicators setups
3. Arrive at the CPs to be selected from each unit level
4. Select the student unit attempt outcomes based on the the setups from 1, 2 and 3
5. Calculate the unit level mark
*/
-- 1. Select the Total Unit Level Credits for a particular unit level
-- this has been done at the time of setup check up
IF l_total_unit_lvl_cp_config IS NULL
THEN
-- obtain the sum of earned cp
-- The below cursor query basically tests for CP
-- at the sua attempt level. If this is not present,
-- then setup is checked for the unit section level.
-- If this is also not present then, the unit level
-- CP is taken into consideration
OPEN c_total_unt_lvl_cp_alt (
p_unit_level,
p_course_cd,
p_person_id,
'TRUE'
);
p1_unit_selection_code := rec_ul_mark_dtl.unit_selection_code;
p2_unit_selection_code := rec_ul_mark_dtl.unit_selection_code;
p3_unit_selection_code := rec_ul_mark_dtl.unit_selection_code;
This step is required only if the selection method is based on Credit Points.
If it is priority based, this step has to be skipped.
What is being done in this step?
In this step, the following is calculated : How much CP should be taken from
the students outcome table at each level. For example, if the setup states
that priority 1 should contribute, say, 72 cps , priority two should contribute
36 and priority 3 should contribute 12, but due to a shortfall in the students
CPs at a priority, the other priorities should compensate for this shortfall.
The shortfall in CPs should come from priority 1 if available, if not then it
should be compensated by priority 2, priority 3 depending on availibilty. Before
the suas are actually selected, how much each core indicator unit attempt should
contribute is being calculated here
*/
-- check whether the selection criteria is credit points or priority
-- based on selection_method_code as obtained by above cursor c_total_unt_lvl_cp
IF l_selection_method_code = 'CREDITS'
THEN
-- store the sum of earned cp for each priority for the
-- given core_indicator code
p1_avail_cp := get_avail_cp (
p_person_id,
p_course_cd,
p1_core_indicator_code,
p_unit_level
);
END IF; -- IF l_selection_method_code = 'CREDITS' THEN
IF l_selection_method_code = 'PRIORITY'
THEN
FOR rec_cnfg_dtls IN c_cnfg_dtls (l_mark_config_id)
LOOP
-- decide the order by clause
IF rec_cnfg_dtls.unit_selection_code = 'BEST_MARK'
THEN
l_stmt_priority_based :=
l_stmt_priority_based_orig
|| ' ORDER BY mark desc ';
ELSE -- else of IF l_selection_method_code = 'PRIORITY' THEN i.e. CREDITS
FOR rec_cnfg_dtls IN c_cnfg_dtls (l_mark_config_id)
LOOP
-- decide the order by clause
IF rec_cnfg_dtls.unit_selection_code = 'BEST_MARK'
THEN
l_stmt_cp_based :=
l_stmt_cp_based_orig
|| ' ORDER BY mark desc ';
END IF; -- end of IF l_selection_method_code = 'CREDITS' THEN
SELECT gsg.grade, gs.grading_schema_cd, gs.version_number
FROM igs_as_grd_schema gs,
igs_as_grd_sch_grade gsg,
igs_ps_awd pa
WHERE gs.grading_schema_cd = gsg.grading_schema_cd
AND gs.version_number = gsg.version_number
AND gs.grading_schema_type = 'HONORS'
AND pa.award_cd = cp_award_cd
AND pa.grading_schema_cd = gsg.grading_schema_cd
AND pa.gs_version_number = gsg.version_number
AND NVL(gsg.lower_mark_range, 1) <= NVL((cp_sum), NVL(gsg.lower_mark_range, 1))
AND NVL(gsg.upper_mark_range, 1) >= NVL((cp_sum), NVL(gsg.upper_mark_range, 1));
SELECT gsg.grade, gs.grading_schema_cd, gs.version_number
FROM igs_as_grd_schema gs,
igs_as_grd_sch_grade gsg
WHERE gs.grading_schema_cd = gsg.grading_schema_cd
AND gs.version_number = gsg.version_number
AND gs.grading_schema_type = 'HONORS'
AND gs.start_dt <= SYSDATE
AND nvl(gs.end_dt,SYSDATE) >= SYSDATE
AND gsg.lower_mark_range <= (cp_sum)
AND gsg.upper_mark_range >= (cp_sum);
SELECT unit_level, weighted_average
FROM igs_ps_awd_hnr_base
WHERE award_cd = cp_award_cd
AND unit_level IS NOT NULL
UNION
SELECT NVL(ul.unit_level, uv.unit_level) AS unit_level, 1 AS weighted_average
FROM igs_en_su_attempt_all sua ,
igs_ps_unit_ver_all uv, igs_ps_unit_lvl_all ul
WHERE
sua.unit_cd = uv.unit_cd AND
sua.version_number = uv.version_number AND
sua.course_cd = ul.course_cd(+) AND
sua.unit_cd = ul.unit_cd(+) AND
sua.version_number = ul.version_number(+) AND
sua.person_id = p_person_id and sua.course_cd = p_course_cd AND
NOT EXISTS (SELECT 1 FROM igs_ps_awd_hnr_base hb WHERE NVL(ul.unit_level, uv.unit_level) = hb.unit_level);
SELECT stat_type,
s_stat_element,
timeframe
FROM igs_ps_awd_hnr_base
WHERE award_cd = cp_award_cd
AND stat_type IS NOT NULL;
SELECT load_cal_type,
load_ci_sequence_number
FROM igs_ca_teach_to_load_v
WHERE (teach_cal_type,teach_ci_sequence_number)
IN( SELECT cal_type, ci_sequence_number
FROM (SELECT cal_type, ci_sequence_number
FROM igs_en_su_attempt
WHERE course_cd = cp_course_cd
AND person_id = cp_person_id
AND unit_attempt_status IN ('COMPLETED','DUPLICATE','ENROLLED')
ORDER BY ci_start_dt DESC)
WHERE rownum = 1)
ORDER BY load_start_dt DESC;
SELECT spaa.rowid row_id, spaa.*
FROM igs_en_spa_awd_aim spaa
WHERE spaa.AWARD_CD = cp_awd_cd AND
spaa.AWARD_MARK IS NULL AND
spaa.AWARD_GRADE IS NULL;
SELECT pa.grading_schema_cd, pa.gs_version_number
FROM igs_ps_awd pa
WHERE pa.award_cd = p_award_cd;
SELECT gsg.grade, gs.grading_schema_cd, gs.version_number,
gsg.lower_mark_range, gsg.upper_mark_range
FROM igs_as_grd_schema gs, igs_as_grd_sch_grade gsg
WHERE gs.grading_schema_cd = gsg.grading_schema_cd
AND gs.version_number = gsg.version_number
AND gs.grading_schema_type = 'HONORS'
AND gs.grading_schema_cd = cp_grd_sch
AND gs.version_number = cp_grd_ver
AND gsg.GRADE = cp_grade;
l_total_spa_updated_rec NUMBER := 0;
FND_MSG_PUB.DELETE_MSG(l_msg_index);
fnd_file.put_line (fnd_file.LOG, ' Failed to Update ' || spaa_rec.person_id || ' - ' || spaa_rec.course_cd || ' - ' || P_AWARD_CD );
ELSE -- The mark and honors level were calculated successfully. Now update the SPAA record.
fnd_file.put_line (fnd_file.LOG,'-------------------------***----------------------');
igs_en_spa_awd_aim_pkg.update_row(
x_rowid => spaa_rec.row_id,
x_person_id => spaa_rec.person_id,
x_course_cd => spaa_rec.course_cd,
x_award_cd => spaa_rec.award_cd,
x_start_dt => spaa_rec.start_dt,
x_end_dt => spaa_rec.end_dt,
x_complete_ind => spaa_rec.complete_ind,
x_honours_level => spaa_rec.honours_level,
x_conferral_date => spaa_rec.conferral_date,
x_award_mark => lawdmark,
x_award_grade => NVL(lrowawd_grd.grade, lawadrdgrade),
x_grading_schema_cd => NVL(lrowawd_grd.grading_schema_cd, l_grading_schema_cd),
x_gs_version_number => NVL(lrowawd_grd.version_number, l_gs_version_number));
fnd_file.put_line (fnd_file.LOG,'Updated ' ||spaa_rec.person_id || ': ' || spaa_rec.course_cd || ': ' || spaa_rec.award_cd || ': With award grade :' || NVL(lrowawd_grd.grade, lawadrdgrade) );
l_total_spa_updated_rec := l_total_spa_updated_rec + 1;
fnd_file.put_line (fnd_file.LOG, 'Total program attempt award records selected : ' || l_total_spa_rec);
fnd_file.put_line (fnd_file.LOG, 'Total program attempt award records updated successfully : ' || l_total_spa_updated_rec);
FND_MSG_PUB.DELETE_MSG(l_msg_index);
FND_MSG_PUB.DELETE_MSG(l_msg_index);