[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
FROM IGS_AS_SU_SETATMPT susa,
IGS_GR_GRADUAND gr
WHERE gr.person_id = p_person_id AND
gr.create_dt = p_create_dt AND
gr.grd_cal_type = p_grd_cal_type AND
gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
susa.person_id = gr.person_id AND
susa.course_cd = gr.course_cd AND
susa.primary_set_ind = 'Y' AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL;
SELECT awc.grd_cal_type,
awc.grd_ci_sequence_number,
awc.ceremony_number,
awc.award_course_cd,
awc.award_crs_version_number,
awc.award_cd,
lr.dflt_ind
FROM IGS_GR_GRADUAND gr,
IGS_GR_AWD_CEREMONY awc,
IGS_EN_STDNT_PS_ATT sca,
IGS_GR_CRMN gc,
IGS_GR_VENUE ve,
IGS_AD_LOCATION_REL lr,
IGS_CA_DA_INST_V daiv
WHERE gr.person_id = p_person_id AND
gr.create_dt = p_create_dt AND
gr.grd_cal_type = p_grd_cal_type AND
gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
sca.person_id = gr.person_id AND
sca.course_cd = gr.course_cd AND
sca.location_cd = lr.location_cd AND
gc.grd_cal_type = gr.grd_cal_type AND
gc.grd_ci_sequence_number = gr.grd_ci_sequence_number AND
gc.venue_cd = ve.venue_cd AND
ve.exam_location_cd = lr.sub_location_cd AND
gc.closing_dt_alias = daiv.dt_alias AND
gc.closing_dai_sequence_number = daiv.sequence_number AND
gc.grd_cal_type = daiv.cal_type AND
gc.grd_ci_sequence_number = daiv.ci_sequence_number AND
TRUNC(SYSDATE) < TRUNC(daiv.alias_val) AND
awc.grd_cal_type = gc.grd_cal_type AND
awc.grd_ci_sequence_number = gc.grd_ci_sequence_number AND
awc.ceremony_number = gc.ceremony_number AND
awc.award_course_cd = gr.award_course_cd AND
awc.award_crs_version_number = gr.award_crs_version_number AND
awc.award_cd = gr.award_cd AND
awc.closed_ind = 'N'
ORDER BY awc.grd_cal_type,
awc.grd_ci_sequence_number,
awc.ceremony_number,
awc.award_course_cd,
awc.award_crs_version_number,
awc.award_cd;
SELECT acusg.grd_cal_type,
acusg.grd_ci_sequence_number,
acusg.ceremony_number,
acusg.award_course_cd,
acusg.award_crs_version_number,
acusg.award_cd,
acusg.us_group_number
FROM IGS_GR_AWD_CRM_US_GP acusg
WHERE acusg.grd_cal_type = cp_grd_cal_type AND
acusg.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
acusg.ceremony_number = cp_ceremony_number AND
acusg.award_course_cd = cp_award_course_cd AND
acusg.award_crs_version_number = cp_award_crs_version_number AND
acusg.award_cd = cp_award_cd AND
acusg.closed_ind = 'N' AND
NOT EXISTS
(SELECT susa.unit_set_cd,
susa.us_version_number
FROM IGS_AS_SU_SETATMPT susa,
IGS_GR_GRADUAND gr
WHERE gr.person_id = p_person_id AND
gr.create_dt = p_create_dt AND
gr.grd_cal_type = p_grd_cal_type AND
gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
susa.person_id = gr.person_id AND
susa.course_cd = gr.course_cd AND
susa.primary_set_ind = 'Y' AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL
MINUS
SELECT acus.unit_set_cd,
acus.us_version_number
FROM IGS_GR_AWD_CRM_UT_ST acus
WHERE acus.grd_cal_type = acusg.grd_cal_type AND
acus.grd_ci_sequence_number = acusg.grd_ci_sequence_number AND
acus.ceremony_number = acusg.ceremony_number AND
acus.award_course_cd = acusg.award_course_cd AND
acus.award_crs_version_number = acusg.award_crs_version_number AND
acus.award_cd = acusg.award_cd AND
acus.us_group_number = acusg.us_group_number) AND
NOT EXISTS
(SELECT acus.unit_set_cd,
acus.us_version_number
FROM IGS_GR_AWD_CRM_UT_ST acus
WHERE acus.grd_cal_type = acusg.grd_cal_type AND
acus.grd_ci_sequence_number = acusg.grd_ci_sequence_number AND
acus.ceremony_number = acusg.ceremony_number AND
acus.award_course_cd = acusg.award_course_cd AND
acus.award_crs_version_number = acusg.award_crs_version_number AND
acus.award_cd = acusg.award_cd AND
acus.us_group_number = acusg.us_group_number
MINUS
SELECT susa.unit_set_cd,
susa.us_version_number
FROM IGS_AS_SU_SETATMPT susa,
IGS_GR_GRADUAND gr
WHERE gr.person_id = p_person_id AND
gr.create_dt = p_create_dt AND
gr.grd_cal_type = p_grd_cal_type AND
gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
susa.person_id = gr.person_id AND
susa.course_cd = gr.course_cd AND
susa.primary_set_ind = 'Y' AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL)
ORDER BY acusg.grd_cal_type,
acusg.grd_ci_sequence_number,
acusg.ceremony_number,
acusg.award_course_cd,
acusg.award_crs_version_number,
acusg.award_cd,
acusg.us_group_number;
SELECT (ASCII(UPPER(pe.last_name)) - 65)
FROM IGS_PE_PERSON_BASE_V pe
WHERE pe.person_id = p_person_id;
SELECT COUNT(*)
FROM IGS_GR_AWD_CRMN gac
WHERE gac.grd_cal_type = t_acusg_match(cp_row_num).grd_cal_type AND
gac.grd_ci_sequence_number =
t_acusg_match(cp_row_num).grd_ci_sequence_number AND
gac.ceremony_number = t_acusg_match(cp_row_num).ceremony_number AND
gac.award_course_cd = t_acusg_match(cp_row_num).award_course_cd AND
gac.award_crs_version_number =
t_acusg_match(cp_row_num).award_crs_version_number AND
gac.award_cd = t_acusg_match(cp_row_num).award_cd AND
NVL(gac.us_group_number, 0) =
NVL(t_acusg_match(cp_row_num).us_group_number, 0);
-- 9. Internal procedure to insert graduand_award_ceremony records.
-- It is passed the row number for the appropriate record in the
-- t_acusg_match PL/SQL table.
DECLARE
lv_rowid VARCHAR2(25);
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = p_person_id;
IGS_GR_AWD_CRMN_PKG.INSERT_ROW(
X_ROWID => lv_rowid,
X_GAC_ID => lv_id,
X_GRADUAND_SEAT_NUMBER => NULL,
X_NAME_PRONUNCIATION => p_name_pronunciation,
X_NAME_ANNOUNCED => p_name_announced,
X_ACADEMIC_DRESS_RQRD_IND => p_academic_dress_rqrd_ind,
X_ACADEMIC_GOWN_SIZE => p_academic_gown_size,
X_ACADEMIC_HAT_SIZE => p_academic_hat_size,
X_GUEST_TICKETS_REQUESTED => p_guest_tickets_requested,
X_GUEST_TICKETS_ALLOCATED => p_guest_tickets_allocated,
X_GUEST_SEATS => p_guest_seats,
X_FEES_PAID_IND => p_fees_paid_ind,
X_SPECIAL_REQUIREMENTS => p_special_requirements,
X_COMMENTS => NULL,
X_PERSON_ID => p_person_id,
X_CREATE_DT => p_create_dt,
X_GRD_CAL_TYPE => t_acusg_match(p_row_num).grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER => t_acusg_match(p_row_num).grd_ci_sequence_number,
X_CEREMONY_NUMBER => t_acusg_match(p_row_num).ceremony_number,
X_AWARD_COURSE_CD => t_acusg_match(p_row_num).award_course_cd,
X_AWARD_CRS_VERSION_NUMBER => t_acusg_match(p_row_num).award_crs_version_number,
X_AWARD_CD => t_acusg_match(p_row_num).award_cd,
X_US_GROUP_NUMBER => t_acusg_match(p_row_num).us_group_number,
X_ORDER_IN_PRESENTATION => NULL,
X_MODE => 'R');
-- 6. If there is only one matching ceremony insert the record there.
-- If there is more than one matching ceremony call a function to resolve
-- the stalemate.
IF gv_t_acusg_cntr = 1 THEN
grdpl_ins_gac_record(gv_t_acusg_cntr);
SELECT gac.person_id,
gac.create_dt,
gac.name_pronunciation,
gac.name_announced,
gac.academic_dress_rqrd_ind,
gac.academic_gown_size,
gac.academic_hat_size,
gac.guest_tickets_requested,
gac.guest_tickets_allocated,
gac.guest_seats,
gac.fees_paid_ind,
gac.special_requirements,
gac.grd_cal_type,
gac.grd_ci_sequence_number,
gac.ceremony_number,
gac.award_course_cd,
gac.award_crs_version_number,
gac.award_cd
FROM IGS_GR_AWD_CRMN gac
WHERE gac.grd_cal_type = p_grd_cal_type AND
gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
gac.ceremony_number = p_ceremony_number AND
NVL(gac.award_course_cd,'NULL')
= NVL(p_award_course_cd,'NULL') AND
NVL(gac.award_crs_version_number,0)
= NVL(p_award_crs_version_number,0) AND
gac.award_cd = p_award_cd AND
gac.us_group_number = p_us_group_number;
SELECT rowid,gac.*
FROM IGS_GR_AWD_CRMN gac
WHERE gac.person_id = cp_person_id AND
gac.create_dt = cp_create_dt AND
gac.grd_cal_type = cp_grd_cal_type AND
gac.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
gac.award_cd = cp_award_cd
FOR UPDATE OF gac.person_id NOWAIT;
SELECT rowid, gach.person_id
FROM IGS_GR_AWD_CRMN_HIST gach
WHERE gach.person_id = cp_gac_person_id AND
gach.create_dt = cp_gac_create_dt AND
gach.grd_cal_type = cp_grd_cal_type AND
gach.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
gach.ceremony_number = cp_ceremony_number AND
gach.award_cd = cp_award_cd
FOR UPDATE OF gach.person_id NOWAIT;
-- 3.Delete the existing graduand_award_ceremony record
IF (c_gac_del%FOUND) THEN
IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
X_ROWID => v_gac_del.rowid);
p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW(
X_ROWID => v_gach_rec.rowid);
p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
-- records and calls GENP_PRC_ACUSG_CLOSE to delete associated
-- graduand_award_ceremony records and attempt to re-allocate them to
-- another ceremony. After all the graduand_award_ceremony records are
-- removed the award_ceremony_us_group record is deleted. The process
-- then finds any graduand_award_ceremony records associated with the
-- award_ceremony, deletes them and calls GENP_PRC_GAC_CRMNY to attempt
-- to allocate them to another ceremony.
DECLARE
v_loop_flag BOOLEAN := FALSE;
SELECT acusg.us_group_number
FROM IGS_GR_AWD_CRM_US_GP acusg
WHERE acusg.grd_cal_type = p_grd_cal_type AND
acusg.grd_ci_sequence_number = p_grd_ci_sequence_number AND
acusg.ceremony_number = p_ceremony_number AND
NVL(acusg.award_course_cd, 'NULL') = NVL(p_award_course_cd, 'NULL') AND
NVL(acusg.award_crs_version_number, 0)
= NVL(p_award_crs_version_number, 0) AND
acusg.award_cd = p_award_cd;
SELECT gac.person_id,
gac.create_dt,
gac.name_pronunciation,
gac.name_announced,
gac.academic_dress_rqrd_ind,
gac.academic_gown_size,
gac.academic_hat_size,
gac.guest_tickets_requested,
gac.guest_tickets_allocated,
gac.guest_seats,
gac.fees_paid_ind,
gac.special_requirements,
gac.grd_cal_type,
gac.grd_ci_sequence_number,
gac.ceremony_number,
gac.award_course_cd,
gac.award_crs_version_number,
gac.award_cd
FROM IGS_GR_AWD_CRMN gac
WHERE gac.grd_cal_type = p_grd_cal_type AND
gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
gac.ceremony_number = p_ceremony_number AND
gac.award_course_cd = p_award_course_cd AND
gac.award_crs_version_number = p_award_crs_version_number AND
gac.award_cd = p_award_cd AND
gac.us_group_number IS NULL;
SELECT rowid, gac.*
FROM IGS_GR_AWD_CRMN gac
WHERE gac.person_id = cp_person_id AND
gac.create_dt = cp_create_dt AND
gac.grd_cal_type = cp_grd_cal_type AND
gac.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
gac.award_cd = cp_award_cd
FOR UPDATE OF gac.person_id NOWAIT;
SELECT rowid, gach.person_id
FROM IGS_GR_AWD_CRMN_HIST gach
WHERE gach.person_id = cp_gac_person_id AND
gach.create_dt = cp_gac_create_dt AND
gach.grd_cal_type = cp_grd_cal_type AND
gach.grd_ci_sequence_number = cp_grd_ci_sequence_number AND
gach.ceremony_number = cp_ceremony_number AND
gach.award_cd = cp_award_cd
FOR UPDATE OF gach.person_id NOWAIT;
-- for the award_ceremony_us_group record and then delete it.
IF NOT grdp_prc_acusg_close(
p_grd_cal_type,
p_grd_ci_sequence_number,
p_ceremony_number,
p_award_course_cd,
p_award_crs_version_number,
p_award_cd,
v_acusg_rec.us_group_number,
p_resolve_stalemate_type,
p_ignore_unit_sets_ind,
p_message_name) THEN
v_loop_flag := TRUE;
-- 3.Delete the existing graduand_award_ceremony record
IF (c_gac_del%FOUND) THEN
IGS_GR_AWD_CRMN_PKG.DELETE_ROW(
X_ROWID => v_gac_del.rowid);
p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';
IGS_GR_AWD_CRMN_HIST_PKG.DELETE_ROW(
X_ROWID => v_gach_rec.rowid);
p_message_name := 'IGS_GR_CANNOT_DELETE_AWD_CERM';