[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
knaraset 02-May-03 Modified the function advp_upd_sua_advstnd to pass uoo_id to internal function enrpl_delete_sua_recs
as part of MUS build bug 2829262
rvivekan 09-sep-2003 Modified the behaviour of repeatable_ind column in igs_ps_unit_ver table. PSP integration build #3052433
stutta 27-Oct-2003 Modified funcion advp_upd_sua_advstnd by removing calls to functions IGS_EN_VAL_SUA.enrp_val_sca_supunit,
IGS_EN_VAL_SUA.enrp_val_sca_subunit as part of build #3052438
nalkumar 10-Dec-2003 Bug# 3270446 RECR50 Build; Obsoleted the IGS_AV_STND_UNIT.CREDIT_PERCENTAGE column.
SELECT 'X'
FROM IGS_AV_STND_UNIT asu
WHERE asu.person_id = p_person_id AND
asu.as_course_cd = p_course_cd AND
asu.s_adv_stnd_granting_status <> cst_approved;
SELECT 'X'
FROM IGS_AV_STND_UNIT_LVL asul
WHERE asul.person_id = p_person_id AND
asul.as_course_cd = p_course_cd AND
asul.s_adv_stnd_granting_status <> cst_approved;
SELECT rowid,av_stnd_unit_id
FROM IGS_AV_STND_UNIT_ALL asua
WHERE asua.person_id = p_person_id AND
asua.as_course_cd = p_course_cd ;
SELECT rowid
FROM IGS_AV_ADV_STANDING_ALL asal
WHERE asal.person_id = p_person_id AND
asal.course_cd = p_course_cd;
SELECT rowid,av_stnd_unit_lvl_id
FROM IGS_AV_STND_UNIT_LVL_ALL aslvl
WHERE aslvl.person_id = p_person_id AND
aslvl.as_course_cd = p_course_cd;
SELECT rowid
FROM IGS_AV_STD_UNT_BASIS_ALL asba
WHERE asba.av_stnd_unit_id = cp_unit_id;
SELECT rowid
FROM IGS_AV_STD_ULVLBASIS_ALL asbl
WHERE asbl.av_stnd_unit_lvl_id = cp_unit_lvl_id;
SELECT rowid
FROM IGS_AV_STND_ALT_UNIT asau
WHERE asau.av_stnd_unit_id = cp_alt_unit_id;
p_message_name := 'IGS_AV_CANNOT_DELETE';
p_message_name := 'IGS_AV_CANNOT_DELETE';
igs_av_std_unt_basis_pkg.DELETE_ROW (
X_ROWID => v_unit_basis_all.rowid );
fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit basis for'||v_unit_all.av_stnd_unit_id );
igs_av_stnd_alt_unit_pkg.DELETE_ROW (
X_ROWID => v_alt_unt_all.rowid );
igs_av_stnd_unit_pkg.DELETE_ROW (
X_ROWID => v_unit_all.rowid );
fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit');
igs_av_std_ulvlbasis_pkg.DELETE_ROW (
X_ROWID => v_ulvlbasis_all.rowid );
fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit level basis'||v_unit_lvl_all.av_stnd_unit_lvl_id);
igs_av_stnd_unit_lvl_pkg.DELETE_ROW (
X_ROWID => v_unit_lvl_all.rowid );
fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit level');
igs_av_adv_standing_pkg.DELETE_ROW (
X_ROWID => v_adv_all.rowid );
fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted from IGS_AV_ADV_STANDING_ALL');
SELECT SUM(NVL(asu.achievable_credit_points,0)) advance_standing_credits,
SUM(NVL(puv.achievable_Credit_points,puv.enrolled_Credit_points)) enrolled_cp ,
asu.unit_cd,asu.version_number
FROM igs_av_stnd_unit asu,igs_ps_unit_ver puv WHERE
asu.person_id = p_person_id AND
asu.as_course_cd = p_course_cd AND
puv.unit_cd = asu.unit_cd AND
puv.version_number = asu.version_number AND
asu.s_adv_stnd_granting_status = 'GRANTED' AND
asu.s_adv_stnd_recognition_type = 'CREDIT' AND
(p_effective_dt IS NULL OR asu.granted_dt <= TRUNC(p_effective_dt))
GROUP BY asu.unit_cd,asu.version_number;
SELECT NVL(puv.achievable_Credit_points,puv.enrolled_Credit_points) enrolled_credits
FROM igs_av_stnd_unit asu,igs_ps_unit_ver puv
WHERE asu.person_id = p_person_id
AND asu.as_course_cd = p_course_cd
AND asu.unit_cd = cp_unit_cd
AND asu.version_number = cp_version_number
AND asu.s_adv_stnd_granting_status = 'GRANTED'
AND asu.s_adv_stnd_recognition_type = 'CREDIT'
AND (p_effective_dt IS NULL OR asu.granted_dt <= TRUNC(p_effective_dt))
/* AND credit_percentage = 100 */
AND puv.unit_cd = asu.unit_cd
AND puv.version_number = asu.version_number;
SELECT SUM(credit_points)
FROM IGS_AV_STND_UNIT_LVL asul
WHERE asul.person_id = p_person_id AND
asul.as_course_cd = p_course_cd AND
asul.s_adv_stnd_granting_status = cst_granted AND
(p_effective_dt IS NULL OR
asul.granted_dt <= TRUNC(p_effective_dt));
SELECT COUNT (rowid)
FROM igs_as_grd_sch_grade
WHERE grading_schema_cd = grschcd
AND version_number = grschvno
AND grade = grd;
SELECT person_id
FROM IGS_AV_STND_UNIT_ALL
WHERE s_adv_stnd_granting_status = cst_approved AND
s_adv_stnd_recognition_type = cst_credit
UNION
SELECT person_id
FROM IGS_AV_STND_UNIT_LVL_ALL
WHERE s_adv_stnd_granting_status = cst_approved;
SELECT suc.exemption_institution_cd,
SUM (suc.credit)
FROM (SELECT asu.person_id person_id,
asu.as_course_cd course_cd,
asu.as_version_number version_number,
asu.exemption_institution_cd exemption_institution_cd,
uv.achievable_credit_points credit
FROM igs_av_stnd_unit_all asu,
igs_ps_unit_ver_all uv
WHERE asu.unit_cd = uv.unit_cd
AND asu.version_number = uv.version_number
AND asu.s_adv_stnd_recognition_type = 'CREDIT'
AND asu.s_adv_stnd_granting_status = 'GRANTED'
UNION ALL
SELECT asule.person_id,
asule.as_course_cd,
asule.as_version_number,
asule.exemption_institution_cd,
asule.credit_points credit
FROM igs_av_stnd_unit_lvl_all asule
WHERE asule.s_adv_stnd_granting_status = 'GRANTED') suc
WHERE suc.person_id = p_person_id
AND suc.course_cd = p_course_cd
AND suc.version_number = p_version_number
GROUP BY suc.exemption_institution_cd
ORDER BY SUM (suc.credit) DESC;
SELECT 'x'
FROM igs_pe_hz_parties ihp
where ihp.inst_org_ind = 'I'
AND ihp.oi_govt_institution_cd IS NOT NULL
AND ihp.oss_org_unit_cd = cp_exemption_institution_cd
UNION ALL
SELECT 'x'
FROM igs_lookup_values lk
WHERE lk.lookup_type = 'OR_INST_EXEMPTIONS'
AND lk.enabled_flag = 'Y'
AND lk.lookup_code = cp_exemption_institution_cd;
SELECT 'x'
FROM hz_parties hp,
igs_pe_hz_parties ihp
WHERE ihp.oss_org_unit_cd = cp_exemption_institution_cd --swaghmar bug# 4377816
AND hp.party_id = ihp.party_id
AND ihp.inst_org_ind = 'I'
AND ihp.oi_os_ind = 'Y';
SELECT *
FROM IGS_AV_ADV_STANDING
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
version_number = p_version_number
FOR UPDATE OF exemption_institution_cd NOWAIT;
UPDATE IGS_AV_ADV_STANDING
SET exemption_institution_cd = v_major_exmpt_inst
WHERE CURRENT OF c_advanced_standing;
v_update_flag BOOLEAN DEFAULT TRUE;
SELECT 'x'
FROM IGS_GE_S_LOG
WHERE s_log_type = cp_s_log_type AND
creation_dt = cp_creation_dt;
SELECT *
FROM IGS_AV_STND_UNIT
WHERE
s_adv_stnd_granting_status = cst_approved AND
s_adv_stnd_recognition_type = cst_credit AND
person_id = cp_person_id
ORDER BY
person_id,
as_course_cd,
as_version_number,
approved_dt desc,
granted_dt desc;
SELECT *
FROM IGS_AV_STND_UNIT_LVL
WHERE
s_adv_stnd_granting_status = cst_approved AND
person_id = cp_person_id
ORDER BY
person_id,
as_course_cd,
as_version_number,
approved_dt desc,
granted_dt desc
FOR UPDATE NOWAIT;
v_update_flag := FALSE;
v_update_flag := TRUE;
IF (v_update_flag = TRUE) THEN
-- Update IGS_AV_STND_UNIT
-- *****************************************************************************************
DECLARE
/* Cursor to select rowid and all columns of the table */
CURSOR Cur_IGS_AV_STND_UNIT IS
SELECT rowid, IGS_AV_STND_UNIT.*
FROM IGS_AV_STND_UNIT
WHERE person_id = p_person_id
AND as_course_cd = v_asu_rec.as_course_cd
AND as_version_number = v_asu_rec.as_version_number
AND unit_cd = v_asu_rec.unit_cd
AND version_number = v_asu_rec.version_number;
SELECT party_number
FROM hz_parties
WHERE party_id = p_person_id;
/* For the column to be updated, modify the record variable value fetched */
IGS_AV_STND_UNIT_rec.granted_dt := v_granted_dt;
IGS_AV_STND_UNIT_PKG.Update_Row (
X_Rowid => IGS_AV_STND_UNIT_rec.rowid,
X_PERSON_ID => IGS_AV_STND_UNIT_rec.PERSON_ID,
X_AS_COURSE_CD => IGS_AV_STND_UNIT_rec.AS_COURSE_CD,
X_AS_VERSION_NUMBER => IGS_AV_STND_UNIT_rec.AS_VERSION_NUMBER,
X_S_ADV_STND_TYPE => IGS_AV_STND_UNIT_rec.S_ADV_STND_TYPE,
X_UNIT_CD => IGS_AV_STND_UNIT_rec.UNIT_CD,
X_VERSION_NUMBER => IGS_AV_STND_UNIT_rec.VERSION_NUMBER,
X_S_ADV_STND_GRANTING_STATUS => IGS_AV_STND_UNIT_rec.S_ADV_STND_GRANTING_STATUS,
X_CREDIT_PERCENTAGE => NULL,
X_S_ADV_STND_RECOGNITION_TYPE => IGS_AV_STND_UNIT_rec.S_ADV_STND_RECOGNITION_TYPE,
X_APPROVED_DT => IGS_AV_STND_UNIT_rec.APPROVED_DT,
X_AUTHORISING_PERSON_ID => IGS_AV_STND_UNIT_rec.AUTHORISING_PERSON_ID,
X_CRS_GROUP_IND => IGS_AV_STND_UNIT_rec.CRS_GROUP_IND,
X_EXEMPTION_INSTITUTION_CD => IGS_AV_STND_UNIT_rec.EXEMPTION_INSTITUTION_CD,
X_GRANTED_DT => IGS_AV_STND_UNIT_rec.granted_dt,
X_EXPIRY_DT => IGS_AV_STND_UNIT_rec.EXPIRY_DT,
X_CANCELLED_DT => IGS_AV_STND_UNIT_rec.CANCELLED_DT,
X_REVOKED_DT => IGS_AV_STND_UNIT_rec.REVOKED_DT,
X_COMMENTS => IGS_AV_STND_UNIT_rec.COMMENTS,
X_AV_STND_UNIT_ID => IGS_AV_STND_UNIT_rec.AV_STND_UNIT_ID,
X_CAL_TYPE => IGS_AV_STND_UNIT_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => IGS_AV_STND_UNIT_rec.CI_SEQUENCE_NUMBER,
X_INSTITUTION_CD => IGS_AV_STND_UNIT_rec.INSTITUTION_CD,
X_UNIT_DETAILS_ID => IGS_AV_STND_UNIT_rec.UNIT_DETAILS_ID,
X_TST_RSLT_DTLS_ID => IGS_AV_STND_UNIT_rec.TST_RSLT_DTLS_ID,
X_GRADING_SCHEMA_CD => IGS_AV_STND_UNIT_rec.GRADING_SCHEMA_CD,
X_GRD_SCH_VERSION_NUMBER => IGS_AV_STND_UNIT_rec.GRD_SCH_VERSION_NUMBER,
X_GRADE => IGS_AV_STND_UNIT_rec.GRADE,
X_ACHIEVABLE_CREDIT_POINTS => IGS_AV_STND_UNIT_rec.ACHIEVABLE_CREDIT_POINTS,
X_MODE => 'R');
v_update_flag := TRUE;
/* Cursor to select rowid and all columns of the table */
CURSOR Cur_IGS_AV_STND_UNIT_lvl IS
SELECT rowid, IGS_AV_STND_UNIT_lvl.*
FROM IGS_AV_STND_UNIT_lvl
WHERE person_id = p_person_id
AND as_course_cd = v_asul_rec.as_course_cd
AND as_version_number = v_asul_rec.as_version_number
AND unit_level = v_asul_rec.unit_level
AND crs_group_ind = v_asul_rec.crs_group_ind
AND exemption_institution_cd = v_asul_rec.exemption_institution_cd;
/* For the column to be updated, modify the record variable value fetched */
IGS_AV_STND_UNIT_lvl_rec.granted_dt := v_granted_dt;
IGS_AV_STND_UNIT_LVL_PKG.update_row(
X_Rowid => IGS_AV_STND_UNIT_LVL_rec.rowid,
X_PERSON_ID => IGS_AV_STND_UNIT_LVL_rec.PERSON_ID ,
X_AS_COURSE_CD => IGS_AV_STND_UNIT_LVL_rec.AS_COURSE_CD ,
X_AS_VERSION_NUMBER => IGS_AV_STND_UNIT_LVL_rec.AS_VERSION_NUMBER ,
X_S_ADV_STND_TYPE => IGS_AV_STND_UNIT_LVL_rec.S_ADV_STND_TYPE ,
X_UNIT_LEVEL => IGS_AV_STND_UNIT_LVL_rec.UNIT_LEVEL ,
X_CRS_GROUP_IND => IGS_AV_STND_UNIT_LVL_rec.CRS_GROUP_IND ,
X_EXEMPTION_INSTITUTION_CD => IGS_AV_STND_UNIT_LVL_rec.EXEMPTION_INSTITUTION_CD ,
X_S_ADV_STND_GRANTING_STATUS => IGS_AV_STND_UNIT_LVL_rec.S_ADV_STND_GRANTING_STATUS ,
X_CREDIT_POINTS => IGS_AV_STND_UNIT_LVL_rec.CREDIT_POINTS ,
X_APPROVED_DT => IGS_AV_STND_UNIT_LVL_rec.APPROVED_DT ,
X_AUTHORISING_PERSON_ID => IGS_AV_STND_UNIT_LVL_rec.AUTHORISING_PERSON_ID ,
X_GRANTED_DT => IGS_AV_STND_UNIT_LVL_rec.GRANTED_DT ,
X_EXPIRY_DT => IGS_AV_STND_UNIT_LVL_rec.EXPIRY_DT ,
X_CANCELLED_DT => IGS_AV_STND_UNIT_LVL_rec.CANCELLED_DT ,
X_REVOKED_DT => IGS_AV_STND_UNIT_LVL_rec.REVOKED_DT ,
X_COMMENTS => IGS_AV_STND_UNIT_LVL_rec.COMMENTS ,
X_AV_STND_UNIT_LVL_ID => IGS_AV_STND_UNIT_LVL_rec.AV_STND_UNIT_LVL_ID ,
X_CAL_TYPE => IGS_AV_STND_UNIT_LVL_rec.CAL_TYPE ,
X_CI_SEQUENCE_NUMBER => IGS_AV_STND_UNIT_LVL_rec.CI_SEQUENCE_NUMBER ,
X_INSTITUTION_CD => IGS_AV_STND_UNIT_LVL_rec.INSTITUTION_CD ,
X_UNIT_DETAILS_ID => IGS_AV_STND_UNIT_LVL_rec.UNIT_DETAILS_ID ,
X_TST_RSLT_DTLS_ID => IGS_AV_STND_UNIT_LVL_rec.TST_RSLT_DTLS_ID ,
X_MODE => 'R' ,
X_QUAL_DETS_ID => IGS_AV_STND_UNIT_LVL_rec.QUAL_DETS_ID -- Added column to tbh call w.r.t to ARCR032 (Bug# 2233334)
);
SELECT rowid , adv.*
FROM IGS_AV_ADV_STANDING adv
WHERE adv.person_id = cp_person_id AND
adv.course_cd = cp_course_cd AND
adv.version_number = cp_version_number AND
adv.exemption_institution_cd = p_exemption_institution_cd;
IGS_AV_ADV_STANDING_PKG.Update_Row(
X_Rowid => v_adv_stnd.rowid,
X_PERSON_ID => v_adv_stnd.person_id,
X_COURSE_CD => v_adv_stnd.course_cd,
X_VERSION_NUMBER => v_adv_stnd.version_number ,
X_TOTAL_EXMPTN_APPROVED => v_total_exmptn_approved,
X_TOTAL_EXMPTN_GRANTED => v_total_exmptn_granted ,
X_TOTAL_EXMPTN_PERC_GRNTD => NVL(v_total_exmptn_perc_grntd,0) ,
X_EXEMPTION_INSTITUTION_CD => v_adv_stnd.EXEMPTION_INSTITUTION_CD ,
X_MODE => 'R');
SELECT sua.unit_attempt_status,
sua.cal_type,
sua.ci_sequence_number,
sua.ci_start_dt,
sua.enrolled_dt,
uv.repeatable_ind,
sua.uoo_id
FROM IGS_EN_SU_ATTEMPT sua,
IGS_PS_UNIT_VER uv
WHERE sua.person_id = cp_person_id AND
sua.course_cd = cp_course_cd AND
sua.unit_cd = cp_unit_cd AND
sua.version_number = cp_version_number AND
uv.unit_cd = sua.unit_cd AND
uv.version_number = sua.version_number
FOR UPDATE NOWAIT
ORDER BY DECODE(sua.unit_attempt_status,
'DISCONTIN',1,
'UNCONFIRM',2,
'INVALID',3,
'COMPLETED',4,
'ENROLLED',5);
SELECT *
FROM IGS_EN_SU_ATTEMPT sub_sua
WHERE sub_sua.person_id = cp_person_id AND
sub_sua.course_cd = cp_course_cd AND
sub_sua.sup_unit_cd = cp_sup_unit_cd AND
sub_sua.sup_version_number = cp_sup_vers_num
FOR UPDATE NOWAIT;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = cp_cal_type and
daiv.ci_sequence_number = cp_ci_seq_num and
daiv.dt_alias = sgcc.census_dt_alias and
sgcc.s_control_num = 1
ORDER BY
daiv.alias_val DESC;
SELECT party_number
FROM hz_parties
WHERE party_id = p_person_id;
FUNCTION enrpl_delete_sua_recs (
p_del_person_id IN IGS_EN_SU_ATTEMPT.person_id%TYPE,
p_del_course_cd IN IGS_EN_SU_ATTEMPT.course_cd%TYPE,
p_del_granted_dt IN DATE,
p_del_unit_cd IN IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
p_del_vers_num IN IGS_EN_SU_ATTEMPT.version_number%TYPE,
p_del_cal_type IN IGS_EN_SU_ATTEMPT.cal_type%TYPE,
p_del_ci_seq_num IN IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
p_del_ci_start_dt IN IGS_EN_SU_ATTEMPT.ci_start_dt%TYPE,
p_del_enrolled_dt IN IGS_EN_SU_ATTEMPT.enrolled_dt%TYPE,
p_del_unit_atmpt_status IN IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE,
p_del_sub_unit IN BOOLEAN,
p_del_message_num OUT NOCOPY NUMBER,
p_del_uoo_id IN IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
RETURN BOOLEAN IS
BEGIN
DECLARE
v_other_detail VARCHAR2(255);
Fnd_Message.Set_Token('NAME','IGS_AV_GEN_001.ENRPL_DELETE_SUA_RECS');
END enrpl_delete_sua_recs;
IF (enrpl_delete_sua_recs(
p_person_id,
p_course_cd,
p_granted_dt,
gv_unit_cd,
gv_version_number,
gv_cal_type,
gv_ci_sequence_number,
gv_ci_start_dt,
gv_enrolled_dt,
gv_unit_attempt_status,
gv_sub_unit,
gv_message_num,
gv_uoo_id) = FALSE) THEN
-- Rollback any changes to student_unit_attempts
ROLLBACK to sp_discontinue_sua;
IF (enrpl_delete_sua_recs(
p_person_id,
p_course_cd,
p_granted_dt,
gv_unit_cd,
gv_version_number,
gv_cal_type,
gv_ci_sequence_number,
gv_ci_start_dt,
gv_enrolled_dt,
gv_unit_attempt_status,
gv_sub_unit,
gv_message_num,
gv_uoo_id) = FALSE) THEN
-- Rollback any changes to student_unit_attempts
ROLLBACK to sp_discontinue_sua;
SELECT person_id
FROM IGS_AV_STND_UNIT_ALL
WHERE s_adv_stnd_granting_status = 'APPROVED' AND
NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE
UNION
SELECT person_id
FROM IGS_AV_STND_UNIT_LVL_ALL
WHERE s_adv_stnd_granting_status = 'APPROVED' AND
NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE;
SELECT rowid , IGS_AV_STND_UNIT.*
FROM igs_av_stnd_unit
WHERE s_adv_stnd_granting_status = 'APPROVED' AND
NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE AND
person_id = cp_person_id AND
s_adv_stnd_recognition_type <> 'PRECLUSION' /* Added as per Bug# 2441175 */
ORDER BY
person_id,
as_course_cd,
as_version_number
FOR UPDATE OF s_adv_stnd_granting_status NOWAIT;
SELECT rowid,IGS_AV_STND_UNIT_LVL.*
FROM IGS_AV_STND_UNIT_LVL
WHERE s_adv_stnd_granting_status = 'APPROVED' AND
NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE AND
person_id = cp_person_id
ORDER BY
person_id,
as_course_cd,
as_version_number
FOR UPDATE OF s_adv_stnd_granting_status NOWAIT;
IGS_AV_STND_UNIT_PKG.update_row(
X_Rowid => v_asu_rec.rowid ,
X_PERSON_ID => v_asu_rec.PERSON_ID ,
X_AS_COURSE_CD => v_asu_rec.AS_COURSE_CD ,
X_AS_VERSION_NUMBER => v_asu_rec.AS_VERSION_NUMBER ,
X_S_ADV_STND_TYPE => v_asu_rec.S_ADV_STND_TYPE ,
X_UNIT_CD => v_asu_rec.UNIT_CD ,
X_VERSION_NUMBER => v_asu_rec.VERSION_NUMBER ,
X_S_ADV_STND_GRANTING_STATUS => 'EXPIRED' ,
/* X_CREDIT_PERCENTAGE => v_asu_rec.CREDIT_PERCENTAGE , */
X_S_ADV_STND_RECOGNITION_TYPE => v_asu_rec.S_ADV_STND_RECOGNITION_TYPE ,
X_APPROVED_DT => v_asu_rec.APPROVED_DT ,
X_AUTHORISING_PERSON_ID => v_asu_rec.AUTHORISING_PERSON_ID ,
X_CRS_GROUP_IND => v_asu_rec.CRS_GROUP_IND ,
X_EXEMPTION_INSTITUTION_CD => v_asu_rec.EXEMPTION_INSTITUTION_CD ,
X_GRANTED_DT => v_asu_rec.GRANTED_DT ,
X_EXPIRY_DT => v_asu_rec.EXPIRY_DT ,
X_CANCELLED_DT => v_asu_rec.CANCELLED_DT ,
X_REVOKED_DT => v_asu_rec.REVOKED_DT ,
X_COMMENTS => v_asu_rec.COMMENTS ,
X_AV_STND_UNIT_ID => v_asu_rec.AV_STND_UNIT_ID ,
X_CAL_TYPE => v_asu_rec.CAL_TYPE ,
X_CI_SEQUENCE_NUMBER => v_asu_rec.CI_SEQUENCE_NUMBER ,
X_INSTITUTION_CD => v_asu_rec.INSTITUTION_CD ,
X_UNIT_DETAILS_ID => v_asu_rec.UNIT_DETAILS_ID ,
X_TST_RSLT_DTLS_ID => v_asu_rec.TST_RSLT_DTLS_ID ,
X_GRADING_SCHEMA_CD => v_asu_rec.GRADING_SCHEMA_CD ,
X_GRD_SCH_VERSION_NUMBER => v_asu_rec.GRD_SCH_VERSION_NUMBER ,
X_GRADE => v_asu_rec.GRADE ,
X_ACHIEVABLE_CREDIT_POINTS => v_asu_rec.ACHIEVABLE_CREDIT_POINTS ,
X_MODE => 'R');
IGS_AV_STND_UNIT_LVL_PKG.update_row(
X_Rowid => v_asul_rec.rowid ,
X_PERSON_ID => v_asul_rec.PERSON_ID ,
X_AS_COURSE_CD => v_asul_rec.AS_COURSE_CD ,
X_AS_VERSION_NUMBER => v_asul_rec.AS_VERSION_NUMBER ,
X_S_ADV_STND_TYPE => v_asul_rec.S_ADV_STND_TYPE ,
X_UNIT_LEVEL => v_asul_rec.UNIT_LEVEL ,
X_CRS_GROUP_IND => v_asul_rec.CRS_GROUP_IND ,
X_EXEMPTION_INSTITUTION_CD => v_asul_rec.EXEMPTION_INSTITUTION_CD ,
X_S_ADV_STND_GRANTING_STATUS => 'EXPIRED' ,
X_CREDIT_POINTS => v_asul_rec.CREDIT_POINTS ,
X_APPROVED_DT => v_asul_rec.APPROVED_DT ,
X_AUTHORISING_PERSON_ID => v_asul_rec.AUTHORISING_PERSON_ID ,
X_GRANTED_DT => v_asul_rec.GRANTED_DT ,
X_EXPIRY_DT => v_asul_rec.EXPIRY_DT ,
X_CANCELLED_DT => v_asul_rec.CANCELLED_DT ,
X_REVOKED_DT => v_asul_rec.REVOKED_DT ,
X_COMMENTS => v_asul_rec.COMMENTS ,
X_AV_STND_UNIT_LVL_ID => v_asul_rec.AV_STND_UNIT_LVL_ID ,
X_CAL_TYPE => v_asul_rec.CAL_TYPE ,
X_CI_SEQUENCE_NUMBER => v_asul_rec.CI_SEQUENCE_NUMBER ,
X_INSTITUTION_CD => v_asul_rec.INSTITUTION_CD ,
X_UNIT_DETAILS_ID => v_asul_rec.UNIT_DETAILS_ID ,
X_TST_RSLT_DTLS_ID => v_asul_rec.TST_RSLT_DTLS_ID ,
X_MODE => 'R' ,
X_QUAL_DETS_ID => v_asul_rec.QUAL_DETS_ID -- Added column to tbh call w.r.t to ARCR032 (Bug# 2233334)
);
select AV_STND_UNIT_ID from IGS_AV_STND_UNIT_ALL unt where
person_id =p_person_id and
as_course_cd = p_course_cd and
as_version_number = p_version_number and
not exists ( select 1 from IGS_AV_STD_UNT_BASIS_ALL basis where basis.AV_STND_UNIT_ID= unt.AV_STND_UNIT_ID) ;
select AV_STND_UNIT_LVL_ID from IGS_AV_STND_UNIT_LVL_ALL ulvl where
person_id =p_person_id and
as_course_cd = p_course_cd and
as_version_number = p_version_number and
not exists ( select 1 from IGS_AV_STD_ULVLBASIS_ALL basis where basis.AV_STND_UNIT_LVL_ID= ulvl.AV_STND_UNIT_LVL_ID) ;
Igs_Av_Std_Unt_Basis_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Av_Stnd_Unit_Id => V_AV_UNT.AV_STND_UNIT_ID,
X_Basis_Course_Type => null,
X_Basis_Year => null,
X_Basis_Completion_Ind => null
,X_ORG_ID => FND_PROFILE.VALUE('ORG_ID')
);
Igs_Av_Std_Ulvlbasis_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid ,
X_Av_Stnd_Unit_Lvl_Id => V_AV_UNT.AV_STND_UNIT_LVL_ID,
X_Basis_Course_Type => null,
X_Basis_Year => null,
X_Basis_Completion_Ind => null
,X_ORG_ID => FND_PROFILE.VALUE('ORG_ID')
);
SELECT qualification_recency
FROM IGS_PS_VER
WHERE course_cd = p_course_cd AND
version_number = p_version_number;
SELECT 'X'
FROM igs_en_su_attempt
WHERE person_id = p_person_id
AND ((unit_attempt_status IN ('ENROLLED', 'DISCONTIN','COMPLETED','INVALID','UNCONFIRM'))
OR (unit_attempt_status = 'WAITLISTED' AND FND_PROFILE.VALUE('IGS_EN_VAL_WLST') ='Y'))
AND (unit_cd, version_number) IN
(SELECT unit_cd,
version_number
FROM igs_ps_unit_ver
WHERE (unit_cd = cp_unit_cd AND version_number = cp_version_number)
OR rpt_fmly_id =
( SELECT psu.rpt_fmly_id
FROM igs_ps_unit_ver psu,
igs_ps_rpt_fmly rep
WHERE psu.unit_cd = cp_unit_cd
AND psu.version_number = cp_version_number
AND psu.rpt_fmly_id = rep.rpt_fmly_id
AND NVL(rep.closed_ind,'N') = 'N' ));
SELECT repeatable_ind
FROM igs_ps_unit_ver
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number;
SELECT *
FROM igs_av_adv_standing_all
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND version_number = p_version_number;
SELECT nvl(SUM (cp) , 0)
FROM (SELECT SUM (achievable_credit_points) cp
FROM igs_av_stnd_unit_all unt
WHERE unt.exemption_institution_cd = p_exemption_institution_cd
AND unt.person_id = p_person_id
AND p_course_cd = unt.as_course_cd
AND p_version_number = unt.as_version_number
AND unt.s_adv_stnd_granting_status = 'APPROVED'
UNION ALL
SELECT SUM (credit_points) cp
FROM igs_av_stnd_unit_lvl_all unt
WHERE unt.exemption_institution_cd = p_exemption_institution_cd
AND unt.person_id = p_person_id
AND p_course_cd = unt.as_course_cd
AND p_version_number = unt.as_version_number
AND unt.s_adv_stnd_granting_status = 'APPROVED');
SELECT nvl(SUM (cp),0)
FROM (SELECT SUM (achievable_credit_points) cp
FROM igs_av_stnd_unit_all unt
WHERE unt.exemption_institution_cd = p_exemption_institution_cd
AND unt.person_id = p_person_id
AND p_course_cd = unt.as_course_cd
AND p_version_number = unt.as_version_number
AND unt.s_adv_stnd_granting_status = 'GRANTED'
UNION ALL
SELECT SUM (credit_points) cp
FROM igs_av_stnd_unit_lvl_all unt
WHERE unt.exemption_institution_cd = p_exemption_institution_cd
AND unt.person_id = p_person_id
AND p_course_cd = unt.as_course_cd
AND p_version_number = unt.as_version_number
AND unt.s_adv_stnd_granting_status = 'GRANTED');
UPDATE igs_av_adv_standing_all
SET total_exmptn_approved = l_app_val,
total_exmptn_granted = l_grnt_val
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND version_number = p_version_number
AND exemption_institution_cd = l_adv_stnd.exemption_institution_cd;
SELECT SUM
(igs_as_calc_award_mark.get_earned_cp (he.person_id,
he.course_cd,
he.unit_cd,
he.version_number,
he.unit_attempt_status,
he.cal_type,
he.ci_sequence_number,
he.uoo_id,
NULL,
NULL
)
)
FROM igs_en_sua_year_v he
WHERE he.person_id = personid
AND he.course_cd = coursecd
AND he.unit_set_cd = unitsetcd
AND he.us_version_number = usverno;
SELECT SUM (a.achievable_credit_points)
FROM igs_av_stnd_unit_all a, igs_pe_hz_parties ipz
WHERE a.s_adv_stnd_granting_status = 'GRANTED'
AND a.s_adv_stnd_recognition_type = 'CREDIT'
AND a.exemption_institution_cd(+) = ipz.oss_org_unit_cd
AND (a.cal_type, a.ci_sequence_number) IN (
SELECT ca.load_cal_type, ca.load_ci_sequence_number
FROM igs_en_sua_year_v susa, igs_ca_teach_to_load_v ca
WHERE susa.person_id = a.person_id
AND susa.course_cd = a.as_course_cd
AND susa.cal_type = ca.teach_cal_type
AND susa.ci_sequence_number = ca.teach_ci_sequence_number
AND susa.unit_set_cd = unitsetcd
AND susa.us_version_number = usverno)
AND ((personid = person_id) AND (coursecd = as_course_cd));
SELECT SUM (a.credit_points)
FROM igs_av_stnd_unit_lvl_all a, igs_pe_hz_parties ipz
WHERE a.s_adv_stnd_granting_status = 'GRANTED'
AND a.exemption_institution_cd(+) = ipz.oss_org_unit_cd
AND (personid = person_id)
AND (coursecd = as_course_cd);