[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT scpca.stream_num,
scpca.show_cause_length,
scpca.appeal_length
FROM IGS_PR_S_CRV_PRG_CAL scpca
WHERE scpca.course_cd = p_course_cd AND
scpca.version_number = p_version_number AND
scpca.prg_cal_type = p_prg_cal_type;
SELECT sopca.stream_num,
sopca.show_cause_length,
sopca.appeal_length
FROM IGS_PR_S_OU_PRG_CAL sopca
WHERE sopca.org_unit_cd = cp_org_unit_cd AND
sopca.ou_start_dt = cp_ou_start_dt AND
sopca.prg_cal_type = p_prg_cal_type;
SELECT spca.stream_num,
spca.show_cause_length,
spca.appeal_length
FROM IGS_PR_S_PRG_CAL spca
WHERE spca.s_control_num = 1 AND
spca.prg_cal_type = p_prg_cal_type;
SELECT 'X'
FROM IGS_PR_S_CRV_PRG_CON scpc
WHERE scpc.course_cd = p_course_cd AND
scpc.version_number = p_version_number;
SELECT 'X'
FROM IGS_PR_S_CRV_PRG_CAL scpca
WHERE scpca.course_cd = p_course_cd AND
scpca.version_number = p_version_number;
SELECT sopc.org_unit_cd,
sopc.ou_start_dt
FROM IGS_PR_S_OU_PRG_CONF sopc
WHERE IGS_PR_GEN_001.PRGP_GET_CRV_CMT (
p_course_cd,
p_version_number,
sopc.org_unit_cd,
sopc.ou_start_dt) = 'Y';
SELECT sopca.org_unit_cd,
sopca.ou_start_dt
FROM IGS_PR_S_OU_PRG_CAL sopca
WHERE IGS_PR_GEN_001.PRGP_GET_CRV_CMT (
p_course_cd,
p_version_number,
sopca.org_unit_cd,
sopca.ou_start_dt) = 'Y';
SELECT sprgc.apply_start_dt_alias,
sprgc.apply_end_dt_alias,
sprgc.end_benefit_dt_alias,
sprgc.end_penalty_dt_alias,
sprgc.show_cause_cutoff_dt_alias,
sprgc.appeal_cutoff_dt_alias,
sprgc.show_cause_ind,
sprgc.apply_before_show_ind,
sprgc.appeal_ind,
sprgc.apply_before_appeal_ind,
sprgc.count_sus_in_time_ind,
sprgc.count_exc_in_time_ind,
sprgc.calculate_wam_ind,
sprgc.calculate_gpa_ind,
sprgc.outcome_check_type
FROM IGS_PR_S_PRG_CONF sprgc
WHERE sprgc.s_control_num = 1;
SELECT sopc.apply_start_dt_alias,
sopc.apply_end_dt_alias,
sopc.end_benefit_dt_alias,
sopc.end_penalty_dt_alias,
sopc.show_cause_cutoff_dt_alias,
sopc.appeal_cutoff_dt_alias,
sopc.show_cause_ind,
sopc.apply_before_show_ind,
sopc.appeal_ind,
sopc.apply_before_appeal_ind,
sopc.count_sus_in_time_ind,
sopc.count_exc_in_time_ind,
sopc.calculate_wam_ind,
sopc.calculate_gpa_ind,
sopc.outcome_check_type
FROM IGS_PR_S_OU_PRG_CONF sopc
WHERE sopc.org_unit_cd = cp_org_unit_cd AND
sopc.ou_start_dt = cp_ou_start_dt;
SELECT scpc.apply_start_dt_alias,
scpc.apply_end_dt_alias,
scpc.end_benefit_dt_alias,
scpc.end_penalty_dt_alias,
scpc.show_cause_cutoff_dt_alias,
scpc.appeal_cutoff_dt_alias,
scpc.show_cause_ind,
scpc.apply_before_show_ind,
scpc.appeal_ind,
scpc.apply_before_appeal_ind,
scpc.count_sus_in_time_ind,
scpc.count_exc_in_time_ind,
scpc.calculate_wam_ind,
scpc.calculate_gpa_ind,
scpc.outcome_check_type
FROM IGS_PR_S_CRV_PRG_CON scpc
WHERE scpc.course_cd = p_course_cd AND
scpc.version_number = p_version_number;
v_insert_todo BOOLEAN DEFAULT FALSE;
SELECT sca.version_number
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT spc.prg_cal_type,
spc.prg_ci_sequence_number
FROM IGS_PR_STDNT_PR_CK spc
WHERE spc.person_id = p_person_id AND
spc.course_cd = p_course_cd;
v_insert_todo := TRUE;
IF v_insert_todo THEN
-- Insert todo entry
v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO (
p_person_id,
cst_prg_check,
NULL,
'Y');
SELECT scpm.value
FROM IGS_PR_SDT_PS_PR_MSR scpm
WHERE scpm.person_id = p_person_id AND
scpm.course_cd = p_course_cd AND
scpm.prg_cal_type = p_prg_cal_type AND
scpm.prg_ci_sequence_number = p_prg_sequence_number AND
scpm.s_prg_measure_type = cp_prg_measure_type
ORDER BY scpm.calculation_dt DESC;
SELECT cir.sub_cal_type cal_type, cir.sub_ci_sequence_number ci_sequence_number
FROM IGS_CA_INST ci ,
IGS_CA_INST_REL cir,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = cp_prg_cal_type
AND cir.sup_ci_sequence_number = cp_prg_sequence_number
AND ci.cal_type = cir.sub_cal_type
AND ci.sequence_number = cir.sub_ci_sequence_number
AND cat.cal_type = ci.cal_type
AND cat.s_cal_cat = 'LOAD'
AND cs.CAL_STATUS = ci.CAL_STATUS
AND cs.s_CAL_STATUS = 'ACTIVE';
IGS_PR_SDT_PS_PR_MSR_PKG .INSERT_ROW (
X_ROWID => lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_PRG_CAL_TYPE => p_prg_cal_type,
X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
X_S_PRG_MEASURE_TYPE => 'COURSE-GPA',
X_CALCULATION_DT => SYSDATE,
X_VALUE =>v_course_gpa,
X_MODE => 'R' );
IGS_PR_SDT_PS_PR_MSR_PKG.INSERT_ROW (
X_ROWID => lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_PRG_CAL_TYPE => p_prg_cal_type,
X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
X_S_PRG_MEASURE_TYPE => 'PERIOD-GPA',
X_CALCULATION_DT => SYSDATE,
X_VALUE =>v_period_gpa,
X_MODE => 'R' );
/* INSERT INTO IGS_PR_SDT_PS_PR_MSR(
person_id,
course_cd,
prg_cal_type,
prg_ci_sequence_number,
s_prg_measure_type,
calculation_dt,
value)
VALUES(
p_person_id,
p_course_cd,
p_prg_cal_type,
p_prg_sequence_number,
'COURSE-WAM',
SYSDATE,
v_course_wam); */
IGS_PR_SDT_PS_PR_MSR_PKG .INSERT_ROW (
X_ROWID => lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_PRG_CAL_TYPE => p_prg_cal_type,
X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
X_S_PRG_MEASURE_TYPE => 'COURSE-WAM',
X_CALCULATION_DT => SYSDATE,
X_VALUE =>v_course_wam,
X_MODE => 'R' );
INSERT INTO IGS_PR_SDT_PS_PR_MSR(
person_id,
course_cd,
prg_cal_type,
prg_ci_sequence_number,
s_prg_measure_type,
calculation_dt,
value)
VALUES(
p_person_id,
p_course_cd,
p_prg_cal_type,
p_prg_sequence_number,
'PERIOD-WAM',
SYSDATE,
v_period_wam); */
IGS_PR_SDT_PS_PR_MSR_PKG .INSERT_ROW (
X_ROWID => lv_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_course_cd,
X_PRG_CAL_TYPE => p_prg_cal_type,
X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
X_S_PRG_MEASURE_TYPE => 'PERIOD-WAM',
X_CALCULATION_DT => SYSDATE,
X_VALUE =>v_period_wam,
X_MODE => 'R' );
p_new_LAST_UPDATED_BY IN VARCHAR2 ,
p_old_LAST_UPDATED_BY IN VARCHAR2 ,
p_new_LAST_UPDATE_DATE IN DATE ,
p_old_LAST_UPDATE_DATE IN DATE ,
p_new_comments IN VARCHAR2 ,
p_old_comments IN VARCHAR2 ,
p_new_show_cause_comments IN VARCHAR2 ,
p_old_show_cause_comments IN VARCHAR2 ,
p_new_appeal_comments IN VARCHAR2 ,
p_old_appeal_comments IN VARCHAR2 )
IS
gv_other_detail VARCHAR2(255);
IF p_new_LAST_UPDATED_BY <> p_old_LAST_UPDATED_BY THEN
r_spoh.LAST_UPDATED_BY := p_old_LAST_UPDATED_BY;
IF p_new_LAST_UPDATE_DATE <> p_old_LAST_UPDATE_DATE THEN
r_spoh.LAST_UPDATE_DATE := p_old_LAST_UPDATE_DATE;
r_spoh.hist_start_dt := p_old_LAST_UPDATE_DATE;
r_spoh.hist_end_dt := p_new_LAST_UPDATE_DATE;
r_spoh.hist_who := p_old_LAST_UPDATED_BY;
INSERT INTO IGS_PR_STU_OU_HIST (
person_id,
course_cd,
sequence_number,
hist_start_dt,
hist_end_dt,
hist_who,
prg_cal_type,
prg_ci_sequence_number,
rule_check_dt,
progression_rule_cat,
pra_sequence_number,
pro_pra_sequence_number,
pro_sequence_number,
progression_outcome_type,
duration,
duration_type,
decision_status,
decision_dt,
decision_org_unit_cd,
decision_ou_start_dt,
applied_dt,
expiry_dt,
show_cause_expiry_dt,
show_cause_dt,
show_cause_outcome_dt,
show_cause_outcome_type,
appeal_expiry_dt,
appeal_dt,
appeal_outcome_dt,
appeal_outcome_type,
encmb_course_group_cd,
restricted_enrolment_cp,
restricted_attendance_type,
comments,
show_cause_comments,
appeal_comments)
VALUES (
r_spoh.person_id,
r_spoh.course_cd,
r_spoh.sequence_number,
r_spoh.hist_start_dt,
r_spoh.hist_end_dt,
r_spoh.hist_who,
r_spoh.prg_cal_type,
r_spoh.prg_ci_sequence_number,
r_spoh.rule_check_dt,
r_spoh.progression_rule_cat,
r_spoh.pra_sequence_number,
r_spoh.pro_pra_sequence_number,
r_spoh.pro_sequence_number,
r_spoh.progression_outcome_type,
r_spoh.duration,
r_spoh.duration_type,
r_spoh.decision_status,
r_spoh.decision_dt,
r_spoh.decision_org_unit_cd,
r_spoh.decision_ou_start_dt,
r_spoh.applied_dt,
r_spoh.expiry_dt,
r_spoh.show_cause_expiry_dt,
r_spoh.show_cause_dt,
r_spoh.show_cause_outcome_dt,
r_spoh.show_cause_outcome_type,
r_spoh.appeal_expiry_dt,
r_spoh.appeal_dt,
r_spoh.appeal_outcome_dt,
r_spoh.appeal_outcome_type,
r_spoh.encmb_course_group_cd,
r_spoh.restricted_enrolment_cp,
r_spoh.restricted_attendance_type,
r_spoh.comments,
r_spoh.show_cause_comments,
r_spoh.appeal_comments);
IGS_PR_STU_OU_HIST_PKG.INSERT_ROW (
X_ROWID => lv_rowid,
x_PERSON_ID => r_spoh.person_id,
x_COURSE_CD => r_spoh.course_cd,
x_SEQUENCE_NUMBER => r_spoh.sequence_number,
x_HIST_START_DT => r_spoh.hist_start_dt,
x_APPEAL_COMMENTS => r_spoh.appeal_comments,
x_APPEAL_DT => r_spoh.appeal_dt,
x_APPEAL_EXPIRY_DT => r_spoh.appeal_expiry_dt,
x_APPEAL_OUTCOME_DT => r_spoh.appeal_outcome_dt,
x_APPEAL_OUTCOME_TYPE => r_spoh.appeal_outcome_type,
x_APPLIED_DT => r_spoh.applied_dt,
x_COMMENTS => r_spoh.comments,
x_DECISION_DT => r_spoh.decision_dt,
x_DECISION_ORG_UNIT_CD => r_spoh.decision_org_unit_cd,
x_DECISION_OU_START_DT =>r_spoh.decision_ou_start_dt,
x_DECISION_STATUS => r_spoh.decision_status,
x_DURATION => r_spoh.duration,
x_DURATION_TYPE => r_spoh.duration_type,
x_ENCMB_COURSE_GROUP_CD => r_spoh.encmb_course_group_cd,
x_EXPIRY_DT => r_spoh.expiry_dt,
x_HIST_END_DT => r_spoh.hist_end_dt,
x_HIST_WHO => r_spoh.hist_who,
x_PRA_SEQUENCE_NUMBER => r_spoh.pra_sequence_number,
x_PRG_CAL_TYPE => r_spoh.prg_cal_type,
x_PRG_CI_SEQUENCE_NUMBER => r_spoh.prg_ci_sequence_number,
x_PROGRESSION_OUTCOME_TYPE => r_spoh.progression_outcome_type,
x_PROGRESSION_RULE_CAT => r_spoh.progression_rule_cat,
x_PRO_PRA_SEQUENCE_NUMBER => r_spoh.pro_pra_sequence_number,
x_PRO_SEQUENCE_NUMBER => r_spoh.pro_sequence_number,
x_RESTRICTED_ATTENDANCE_TYPE => r_spoh.restricted_attendance_type,
x_RESTRICTED_ENROLMENT_CP => r_spoh.restricted_enrolment_cp,
x_RULE_CHECK_DT => r_spoh.rule_check_dt,
x_SHOW_CAUSE_COMMENTS => r_spoh.show_cause_comments,
x_SHOW_CAUSE_DT => r_spoh.show_cause_dt,
x_SHOW_CAUSE_EXPIRY_DT => r_spoh.show_cause_expiry_dt,
x_SHOW_CAUSE_OUTCOME_DT => r_spoh.show_cause_outcome_dt,
x_SHOW_CAUSE_OUTCOME_TYPE =>r_spoh.show_cause_outcome_type,
X_MODE => 'R',
X_ORG_ID => l_org_id );
SELECT IGS_PR_RU_APPL_SEQ_NUM_S.nextval
FROM DUAL;
/* INSERT INTO s_scratch_pad (
sequence_number,
creation_dt,
key,
message_number,
text)
VALUES (
v_ssp_sequence_number,
p_creation_dt,
p_key,
p_s_message_num,
p_text); */
IGS_PR_S_SCRATCH_PAD_PKG.INSERT_ROW (
X_ROWID =>lv_rowid,
x_SEQUENCE_NUMBER =>v_ssp_sequence_number,
x_CREATION_DT =>p_creation_dt,
x_KEY =>p_key,
x_MESSAGE_NAME =>p_s_message_name,
x_TEXT =>p_text,
X_MODE =>'R',
X_ORG_ID => l_org_id );