[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT qualification_recency
FROM IGS_PS_VER
WHERE course_cd = p_course_cd AND
version_number = p_version_number;
l_parameter_list_t.delete;
SELECT unit, term_details_id, unit_details_id, cp_attempted, cp_earned, grade, unit_grade_points
FROM igs_ad_term_unitdtls
WHERE unit_details_id = NVL(cp_unit_details_id, unit_details_id)
AND unit = NVL(cp_unit, unit)
AND term_details_id = NVL(cp_term_details_id, term_details_id);
SELECT term_details_id, transcript_id
FROM igs_ad_term_details
WHERE transcript_id = NVL(cp_transcript_id, transcript_id) AND
term_details_id = NVL(cp_term_details_id, term_details_id);
SELECT DISTINCT rslt.unit_details_id FROM (
SELECT DISTINCT unit_details_id
FROM igs_av_stnd_unit_all
WHERE person_id = cp_person_id
AND exemption_institution_cd = cp_exemption_institution_cd
AND unit_details_id IS NOT NULL
UNION ALL
SELECT DISTINCT unit_details_id
FROM igs_av_stnd_unit_lvl_all
WHERE person_id = cp_person_id
AND exemption_institution_cd = cp_exemption_institution_cd
AND unit_details_id IS NOT NULL) rslt;
SELECT ''||(RPAD(NVL(nt.unit, nbsp), 10))||' ' new_unit,
''||(RPAD(NVL(td.term, nbsp), 30))||' ' term_completed,
''||(LPAD(NVL(TO_CHAR(nt.cp_earned), nbsp), 7))||' ' cp_earned,
''||(RPAD(NVL(nt.grade, nbsp),10))||' ' grade
FROM igs_ad_term_unitdtls nt, igs_ad_term_details td
WHERE nt.term_details_id = cp_new_tid
AND td.term_details_id = nt.term_details_id
AND NOT EXISTS
(SELECT 'x'
FROM igs_ad_term_unitdtls ot
WHERE ot.term_details_id IN (select term_details_id FROM igs_ad_term_details WHERE transcript_id = cp_old_transcript_id)
AND ot.unit = nt.unit);
SELECT ''||(RPAD(NVL(av.cal_type, nbsp), 12))||' ' cal_type,
''||(RPAD(NVL(av.unit_cd, nbsp), 15))||' ' unit,
''||(RPAD(NVL(av.s_adv_stnd_recognition_type, nbsp), 30))||' ' ece_type,
''||(LPAD(NVL(TO_CHAR(av.achievable_credit_points), nbsp), 9))||' ' credit_points,
''||(RPAD(NVL(av.s_adv_stnd_granting_status, nbsp), 30))||' ' adv_status,
''||(RPAD(NVL(TO_CHAR(av.approved_dt), nbsp), 13))||' ' approved_dt,
''||(RPAD(NVL(av.exemption_institution_cd, nbsp), 30))||' ' exemption_institution_cd,
''||(RPAD(NVL(atu.unit, nbsp), 10))||' ' new_unit,
''||(RPAD(NVL(TO_CHAR(atu.cp_earned), nbsp), 7))||' ' new_cp_earned,
''||(RPAD(NVL(atu.grade, nbsp), 10))||' ' new_grade,
'UNIT' lvl,
av.av_stnd_unit_id pk
FROM igs_av_stnd_unit_all av,
igs_ad_term_unitdtls atu
WHERE av.person_id = cp_person_id
AND av.unit_details_id = cp_unit_details_id
AND atu.unit_details_id = av.unit_details_id
UNION ALL
SELECT ''||(RPAD(NVL(avl.cal_type, '& '), 12))||' ' cal_type,
''||(RPAD(NVL(avl.unit_level, nbsp), 15))||' ' unit,
''||(RPAD(nbsp, 30))||' ' ece_type,
''||(LPAD(NVL(TO_CHAR(avl.credit_points), nbsp),9))||' ' credit_points,
''||(RPAD(NVL(avl.s_adv_stnd_granting_status, nbsp), 30))||' ' adv_status,
''||(RPAD(NVL(TO_CHAR(avl.approved_dt), nbsp), 13))||' ' approved_dt,
''||(RPAD(NVL(avl.exemption_institution_cd, nbsp), 30))||' ' exemption_institution_cd,
''||(RPAD(NVL(atu1.unit, nbsp), 10))||' ' new_unit,
''||(RPAD(NVL(TO_CHAR(atu1.cp_earned), nbsp), 7))||' ' new_cp_earned,
''||(RPAD(NVL(atu1.grade, nbsp), 10))||' ' new_grade,
'UNIT LEVEL' lvl,
avl.av_stnd_unit_lvl_id pk
FROM igs_av_stnd_unit_lvl_all avl,
igs_ad_term_unitdtls atu1
WHERE avl.person_id = cp_person_id
AND avl.unit_details_id = cp_unit_details_id
AND atu1.unit_details_id = avl.unit_details_id;
SELECT unit.rowid,
unit.*
FROM igs_av_stnd_unit_all unit
WHERE unit.av_stnd_unit_id = cp_av_stnd_unit_id
FOR UPDATE OF unit_details_id;
SELECT unl_lvl.rowid,
unl_lvl.*
FROM igs_av_stnd_unit_lvl_all unl_lvl
WHERE unl_lvl.av_stnd_unit_lvl_id = cp_av_stnd_unit_lvl_id
FOR UPDATE OF unit_details_id;
SELECT ''||(RPAD(NVL(td.term, nbsp), 30))||' ' term_completed
FROM igs_ad_term_details td
WHERE td.term_details_id = cp_term_details_id;
SELECT alt.rowid
FROM igs_av_stnd_alt_unit alt
WHERE alt.av_stnd_unit_id = cp_av_stnd_unit_id;
SELECT bas.rowid
FROM igs_av_std_unt_basis_all bas
WHERE bas.av_stnd_unit_id = cp_av_stnd_unit_id;
SELECT lvl.rowid
FROM igs_av_std_ulvlbasis_all lvl
WHERE lvl.av_stnd_unit_lvl_id = cp_av_stnd_unit_lvl_id;
SELECT institution_code
FROM igs_ad_acad_history_v
WHERE education_id = cp_education_id;
l_adv_deleted VARCHAR2(1);
l_adv_deleted := 'N';
l_adv_deleted := 'Y';
IF l_adv_deleted = 'Y' OR l_grade_changed IN ('Y','U') THEN
FOR rec_del_adv_dtls IN cur_del_adv_dtls(p_person_id,
rec_get_old_unit_details.unit_details_id) LOOP
IF l_adv_deleted = 'Y' THEN
--Populate the Unit Details...
l_del_unit_details_body := l_del_unit_details_body||''||rec_del_adv_dtls.cal_type||rec_del_adv_dtls.unit||
rec_del_adv_dtls.ece_type||rec_del_adv_dtls.credit_points||rec_del_adv_dtls.adv_status||
rec_del_adv_dtls.approved_dt||' ';
igs_av_stnd_alt_unit_pkg.delete_row(X_ROWID => rec_get_alt_unit.rowid);
igs_av_std_unt_basis_pkg.delete_row(X_ROWID => rec_get_bas_dtl.rowid);
igs_av_stnd_unit_pkg.delete_row(X_ROWID => rec_get_adv_unit_dtls.rowid);
igs_av_std_unt_basis_pkg.delete_row(X_ROWID => rec_get_bas_dtl.rowid);
igs_av_stnd_unit_pkg.delete_row(X_ROWID => rec_get_adv_unit_dtls.rowid);
igs_av_std_ulvlbasis_pkg.delete_row(X_ROWID => rec_get_bas_lvl.rowid);
igs_av_stnd_unit_lvl_pkg.delete_row(X_ROWID => rec_get_adv_unit_lvl_dtls.rowid);
igs_av_stnd_unit_pkg.update_row(
X_ROWID => rec_get_adv_unit_dtls.rowid ,
X_PERSON_ID => rec_get_adv_unit_dtls.person_id ,
X_AS_COURSE_CD => rec_get_adv_unit_dtls.as_course_cd ,
X_AS_VERSION_NUMBER => rec_get_adv_unit_dtls.as_version_number ,
X_S_ADV_STND_TYPE => rec_get_adv_unit_dtls.s_adv_stnd_type ,
X_UNIT_CD => rec_get_adv_unit_dtls.unit_cd ,
X_VERSION_NUMBER => rec_get_adv_unit_dtls.version_number ,
X_S_ADV_STND_GRANTING_STATUS => rec_get_adv_unit_dtls.s_adv_stnd_granting_status ,
X_CREDIT_PERCENTAGE => NULL ,
X_S_ADV_STND_RECOGNITION_TYPE => rec_get_adv_unit_dtls.s_adv_stnd_recognition_type,
X_APPROVED_DT => rec_get_adv_unit_dtls.approved_dt ,
X_AUTHORISING_PERSON_ID => rec_get_adv_unit_dtls.authorising_person_id ,
X_CRS_GROUP_IND => rec_get_adv_unit_dtls.crs_group_ind ,
X_EXEMPTION_INSTITUTION_CD => rec_get_adv_unit_dtls.exemption_institution_cd ,
X_GRANTED_DT => rec_get_adv_unit_dtls.granted_dt ,
X_EXPIRY_DT => rec_get_adv_unit_dtls.expiry_dt ,
X_CANCELLED_DT => rec_get_adv_unit_dtls.cancelled_dt ,
X_REVOKED_DT => rec_get_adv_unit_dtls.revoked_dt ,
X_COMMENTS => rec_get_adv_unit_dtls.comments ,
X_AV_STND_UNIT_ID => rec_get_adv_unit_dtls.av_stnd_unit_id ,
X_CAL_TYPE => rec_get_adv_unit_dtls.cal_type ,
X_CI_SEQUENCE_NUMBER => rec_get_adv_unit_dtls.ci_sequence_number ,
X_INSTITUTION_CD => rec_get_adv_unit_dtls.institution_cd ,
X_UNIT_DETAILS_ID => rec_get_new_unit_details.unit_details_id , --Change the unit_details_id.
X_TST_RSLT_DTLS_ID => rec_get_adv_unit_dtls.tst_rslt_dtls_id ,
X_GRADING_SCHEMA_CD => rec_get_adv_unit_dtls.grading_schema_cd ,
X_GRD_SCH_VERSION_NUMBER => rec_get_adv_unit_dtls.grd_sch_version_number ,
X_GRADE => rec_get_adv_unit_dtls.grade ,
X_ACHIEVABLE_CREDIT_POINTS => rec_get_adv_unit_dtls.achievable_credit_points ,
X_DEG_AUD_DETAIL_ID => rec_get_adv_unit_dtls.deg_aud_detail_id ,
X_MODE => 'R'
);
igs_av_stnd_unit_lvl_pkg.update_row(
X_ROWID => rec_get_adv_unit_lvl_dtls.rowid ,
X_PERSON_ID => rec_get_adv_unit_lvl_dtls.person_id ,
X_AS_COURSE_CD => rec_get_adv_unit_lvl_dtls.as_course_cd ,
X_AS_VERSION_NUMBER => rec_get_adv_unit_lvl_dtls.as_version_number ,
X_S_ADV_STND_TYPE => rec_get_adv_unit_lvl_dtls.s_adv_stnd_type ,
X_UNIT_LEVEL => rec_get_adv_unit_lvl_dtls.unit_level ,
X_CRS_GROUP_IND => rec_get_adv_unit_lvl_dtls.crs_group_ind ,
X_EXEMPTION_INSTITUTION_CD => rec_get_adv_unit_lvl_dtls.exemption_institution_cd ,
X_S_ADV_STND_GRANTING_STATUS => rec_get_adv_unit_lvl_dtls.s_adv_stnd_granting_status,
X_CREDIT_POINTS => rec_get_adv_unit_lvl_dtls.credit_points ,
X_APPROVED_DT => rec_get_adv_unit_lvl_dtls.approved_dt ,
X_AUTHORISING_PERSON_ID => rec_get_adv_unit_lvl_dtls.authorising_person_id ,
X_GRANTED_DT => rec_get_adv_unit_lvl_dtls.granted_dt ,
X_EXPIRY_DT => rec_get_adv_unit_lvl_dtls.expiry_dt ,
X_CANCELLED_DT => rec_get_adv_unit_lvl_dtls.cancelled_dt ,
X_REVOKED_DT => rec_get_adv_unit_lvl_dtls.revoked_dt ,
X_COMMENTS => rec_get_adv_unit_lvl_dtls.comments ,
X_AV_STND_UNIT_LVL_ID => rec_get_adv_unit_lvl_dtls.av_stnd_unit_lvl_id ,
X_CAL_TYPE => rec_get_adv_unit_lvl_dtls.cal_type ,
X_CI_SEQUENCE_NUMBER => rec_get_adv_unit_lvl_dtls.ci_sequence_number ,
X_INSTITUTION_CD => rec_get_adv_unit_lvl_dtls.institution_cd ,
X_UNIT_DETAILS_ID => rec_get_new_unit_details.unit_details_id , --Change the unit_details_id.
X_TST_RSLT_DTLS_ID => rec_get_adv_unit_lvl_dtls.tst_rslt_dtls_id ,
X_DEG_AUD_DETAIL_ID => rec_get_adv_unit_lvl_dtls.deg_aud_detail_id ,
X_QUAL_DETS_ID => rec_get_adv_unit_lvl_dtls.qual_dets_id ,
X_MODE => 'R'
);