[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT crd.grd_cal_type,
crd.grd_ci_sequence_number,
sca.person_id,
sca.course_cd,
sca.version_number,
sca.commencement_dt,
spaa.award_cd
FROM igs_en_stdnt_ps_att sca ,
igs_en_spa_awd_aim spaa,
igs_ps_ver crv ,
igs_gr_crmn_round crd ,
igs_gr_crm_round_prd crdp,
igs_ca_da_inst_v dai1,
igs_ca_da_inst_v dai2
WHERE (p_grd_cal_type IS NULL OR
(crd.grd_cal_type = p_grd_cal_type AND
crd.grd_ci_sequence_number = p_grd_ci_sequence_number)) AND
dai1.cal_type = crd.grd_cal_type AND
dai1.ci_sequence_number = crd.grd_ci_sequence_number AND
dai1.dt_alias = crd.start_dt_alias AND
dai1.sequence_number = crd.start_dai_sequence_number AND
dai1.alias_val IS NOT NULL AND
dai2.cal_type = crd.grd_cal_type AND
dai2.ci_sequence_number = crd.grd_ci_sequence_number AND
dai2.dt_alias = crd.end_dt_alias AND
dai2.sequence_number = crd.end_dai_sequence_number AND
dai2.alias_val IS NOT NULL AND
TRUNC(SYSDATE) BETWEEN NVL(dai1.alias_val, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
NVL(dai2.alias_val, IGS_GE_DATE.IGSDATE('1900/01/01'))AND
crdp.grd_cal_type(+) = crd.grd_cal_type AND
crdp.grd_ci_sequence_number(+) = crd.grd_ci_sequence_number AND
(p_course_cd IS NULL OR
sca.course_cd = p_course_cd) AND
(p_crs_location_cd IS NULL OR
sca.location_cd = p_crs_location_cd) AND
sca.person_id = spaa.person_id(+) AND
sca.course_cd = spaa.course_cd(+) AND
(p_restrict_rqrmnt_complete = 'N' OR
sca.course_rqrmnt_complete_ind = 'Y') AND
((p_nominated_completion = 'Y' AND
sca.nominated_completion_yr = NVL(crdp.completion_year, 1900) AND
sca.nominated_completion_perd = NVL(crdp.completion_period, 'NULL')) OR
(p_derived_completion = 'Y' AND
sca.derived_completion_yr = NVL(crdp.completion_year, 1900) AND
sca.derived_completion_perd = NVL(crdp.completion_period, ' ')) OR
(NVL(sca.course_rqrmnts_complete_dt, IGS_GE_DATE.IGSDATE('9998/01/01'))
BETWEEN NVL(crd.completion_start_date, IGS_GE_DATE.IGSDATE('9999/01/01')) AND
NVL(crd.completion_end_date, IGS_GE_DATE.IGSDATE('9999/01/01'))) OR
(NVL(spaa.conferral_date, IGS_GE_DATE.IGSDATE('9998/01/01'))
BETWEEN NVL(crd.conferral_start_date, IGS_GE_DATE.IGSDATE('9999/01/01')) AND
NVL(crd.conferral_end_date, IGS_GE_DATE.IGSDATE('9999/01/01')))) AND
sca.course_attempt_status IN (
cst_completed,
cst_enrolled ,
cst_inactive ,
cst_intermit ,
cst_lapsed ) AND
crv.course_cd = sca.course_cd AND
crv.version_number = sca.version_number AND
crv.graduate_students_ind = 'Y' AND
( p_award_cd IS NULL OR
spaa.AWARD_CD = p_award_cd )
ORDER BY sca.person_id;
SELECT 'x'
FROM igs_gr_graduand gr,
IGS_GR_STAT gst
WHERE gr.person_id = cp_person_id AND
gr.course_cd = cp_course_cd AND
gr.award_cd = cp_award_cd AND
gr.graduand_status = gst.graduand_status AND
(gst.s_graduand_status IN (
cst_graduated,
cst_surrender) OR
gr.s_graduand_type IN (
cst_articulate,
cst_declined));
SELECT 'x'
FROM igs_en_spa_awd_aim
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT gst.closed_ind
FROM IGS_GR_STAT gst
WHERE gst.graduand_status = cp_graduand_status AND
gst.s_graduand_status = cp_s_graduand_status;
SELECT gas.closed_ind
FROM IGS_GR_APRV_STAT gas
WHERE gas.graduand_appr_status = p_graduand_appr_status AND
gas.s_graduand_appr_status IN (
cst_waiting,
cst_approved);
SELECT spaaa.award_cd,
spaaa.start_dt,
spaaa.complete_ind,
spaaa.conferral_date
FROM igs_en_spa_awd_aim spaaa
WHERE spaaa.person_id = p_person_id
AND spaaa.course_cd = p_course_cd
AND spaaa.award_cd = p_award_cd
AND ((spaaa.end_dt is NULL OR spaaa.complete_ind = 'Y')
OR(spaaa.conferral_date IS NOT NULL))
AND NOT EXISTS (
SELECT 'x'
FROM IGS_GR_GRADUAND gr
WHERE gr.person_id = p_person_id
AND gr.course_cd = p_course_cd
AND gr.grd_cal_type = p_grd_cal_type
AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
AND gr.award_course_cd = p_course_cd
AND gr.award_crs_version_number = p_version_number
AND gr.award_cd = spaaa.award_cd)
ORDER BY spaaa.start_dt DESC,
spaaa.complete_ind DESC;
SELECT 'X'
FROM igs_en_spa_awd_aim spaaa
WHERE spaaa.person_id = p_person_id
AND spaaa.course_cd = p_course_cd
AND spaaa.award_cd = p_award_cd
AND NOT EXISTS (
SELECT 'x'
FROM igs_gr_graduand gr
WHERE gr.person_id = p_person_id
AND gr.course_cd = p_course_cd
AND gr.grd_cal_type = p_grd_cal_type
AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
AND gr.award_course_cd = p_course_cd
AND gr.award_crs_version_number = p_version_number
AND gr.award_cd = spaaa.award_cd);
v_records_update NUMBER DEFAULT 0;
SELECT sca.course_rqrmnt_complete_ind
FROM igs_en_stdnt_ps_att sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
IGS_GR_GRADUAND_PKG.INSERT_ROW(
X_ROWID => lv_rowid,
X_PERSON_ID => p_person_id,
X_CREATE_DT => lv_create_dt,
X_GRD_CAL_TYPE => p_grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER => p_grd_ci_sequence_number,
X_COURSE_CD => p_course_cd,
X_AWARD_COURSE_CD => p_course_cd,
X_AWARD_CRS_VERSION_NUMBER => p_version_number,
X_AWARD_CD => v_spaaa_rec.award_cd,
X_GRADUAND_STATUS => v_graduand_status,
X_GRADUAND_APPR_STATUS => v_approval_status,
X_S_GRADUAND_TYPE => NULL,
X_GRADUATION_NAME => IGS_GR_GEN_001.grdp_get_grad_name(p_person_id),
X_PROXY_AWARD_IND => NULL,
X_PROXY_AWARD_PERSON_ID => NULL,
X_PREVIOUS_QUALIFICATIONS => NULL,
X_CONVOCATION_MEMBERSHIP_IND => NULL,
X_SUR_FOR_COURSE_CD => NULL,
X_SUR_FOR_CRS_VERSION_NUMBER => NULL,
X_SUR_FOR_AWARD_CD => NULL,
X_COMMENTS => NULL,
X_MODE => 'R',
X_ORG_ID => l_org_id,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL
);
v_records_update := v_records_update + 1;
IF v_records_update > 0 THEN
RETURN;
SELECT 'X'
FROM igs_en_spa_awd_aim spaaa
WHERE spaaa.person_id = p_person_id
AND spaaa.course_cd = p_course_cd
AND spaaa.award_cd = p_award_cd
AND EXISTS (
SELECT 'x'
FROM igs_gr_graduand gr
WHERE gr.person_id = p_person_id
AND gr.course_cd = p_course_cd
AND gr.grd_cal_type = p_grd_cal_type
AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
AND gr.award_course_cd = p_course_cd
AND gr.award_crs_version_number = p_version_number
AND gr.award_cd = spaaa.award_cd);
IF v_records_update = 0 AND NVL(l_hold,'N') <> 'Y' THEN
OPEN cur_spaa_gr_dif;
SELECT caw.award_cd,
grd.grading_schema_cd,
grd.gs_version_number
FROM igs_ps_award caw,
igs_ps_awd grd
WHERE caw.course_cd = v_crd_sca_rec.course_cd AND
caw.version_number = v_crd_sca_rec.version_number AND
caw.default_ind = 'Y' AND
caw.closed_ind = 'N' AND
caw.award_cd = grd.award_cd(+);
igs_en_spa_awd_aim_pkg.insert_row(
X_ROWID => l_row_id,
X_PERSON_ID => v_crd_sca_rec.person_id,
X_COURSE_CD => v_crd_sca_rec.course_cd,
X_AWARD_CD => rec_def_awds.award_cd,
X_START_DT => v_crd_sca_rec.commencement_dt,
X_END_DT => NULL,
X_COMPLETE_IND => 'N',
X_CONFERRAL_DATE => NULL,
X_MODE => 'R',
X_AWARD_MARK => NULL,
X_AWARD_GRADE => NULL,
X_GRADING_SCHEMA_CD => rec_def_awds.grading_schema_cd,
X_GS_VERSION_NUMBER => rec_def_awds.gs_version_number);
p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
AS
l_org_id NUMBER(15);
-- Insert IGS_GR_GRADUAND history(IGS_GR_GRADUAND_HIST)
DECLARE
v_gr_rec IGS_GR_GRADUAND_HIST%ROWTYPE;
-- (with the exception of the last_update_date and last_updated_by columns)
-- then create a graduand_history record with the old values
-- (p_old_). Do not set the last_updated_by and last_update_date
-- columns when creating the history recor
IF p_new_grd_cal_type <> p_old_grd_cal_type THEN
v_gr_rec.grd_cal_type := p_old_grd_cal_type;
-- Insert history rec.
IF v_create_history THEN
v_gr_rec.person_id := p_person_id;
v_gr_rec.hist_start_dt := p_old_update_on;
v_gr_rec.hist_end_dt := p_new_update_on;
v_gr_rec.hist_who := p_old_update_who;
IGS_GR_GRADUAND_HIST_PKG.INSERT_ROW(
X_ROWID => lv_rowid,
X_PERSON_ID => v_gr_rec.person_id,
X_CREATE_DT => v_gr_rec.create_dt,
X_HIST_START_DT => v_gr_rec.hist_start_dt,
X_HIST_END_DT => v_gr_rec.hist_end_dt,
X_HIST_WHO => v_gr_rec.hist_who,
X_GRD_CAL_TYPE => v_gr_rec.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER => v_gr_rec.grd_ci_sequence_number,
X_COURSE_CD => v_gr_rec.course_cd,
X_AWARD_COURSE_CD => v_gr_rec.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER => v_gr_rec.award_crs_version_number,
X_AWARD_CD => v_gr_rec.award_cd,
X_GRADUAND_STATUS => v_gr_rec.graduand_status,
X_GRADUAND_APPR_STATUS => v_gr_rec.graduand_appr_status,
X_S_GRADUAND_TYPE => v_gr_rec.s_graduand_type,
X_GRADUATION_NAME => v_gr_rec.graduation_name,
X_PROXY_AWARD_IND => v_gr_rec.proxy_award_ind,
X_PROXY_AWARD_PERSON_ID => v_gr_rec.proxy_award_person_id,
X_PREVIOUS_QUALIFICATIONS => v_gr_rec.previous_qualifications,
X_CONVOCATION_MEMBERSHIP_IND => v_gr_rec.convocation_membership_ind,
X_SUR_FOR_COURSE_CD => v_gr_rec.sur_for_course_cd,
X_SUR_FOR_CRS_VERSION_NUMBER => v_gr_rec.sur_for_crs_version_number,
X_SUR_FOR_AWARD_CD => v_gr_rec.sur_for_award_cd,
X_COMMENTS => v_gr_rec.comments,
X_MODE => 'R',
X_ORG_ID => l_org_id
);
SELECT awc.ceremony_number,
awc.award_course_cd,
awc.award_crs_version_number,
awc.award_cd
FROM IGS_GR_AWD_CEREMONY awc
WHERE awc.grd_cal_type = p_grd_cal_type AND
awc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
awc.closed_ind = 'Y';
SELECT 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 = p_grd_cal_type AND
acusg.grd_ci_sequence_number = p_grd_ci_sequence_number AND
acusg.closed_ind = 'Y';
SELECT gr.person_id,
gr.create_dt,
gr.s_graduand_type,
gr.grd_cal_type,
gr.grd_ci_sequence_number
FROM IGS_GR_GRADUAND gr,
IGS_GR_STAT gst,
IGS_EN_STDNT_PS_ATT sca
WHERE gr.grd_cal_type = p_grd_cal_type AND
gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
gr.graduand_status = gst.graduand_status AND
gst.s_graduand_status <> cst_surrender AND
gst.graduand_status like p_graduand_status AND
gr.s_graduand_type IN ( cst_attending,
cst_unknown,
cst_deferred) AND
gr.person_id = sca.person_id AND
gr.course_cd = sca.course_cd AND
sca.location_cd like p_location_cd AND
NOT EXISTS
(SELECT 'X'
FROM IGS_GR_AWD_CRMN gac
WHERE gac.person_id = gr.person_id AND
gac.create_dt = gr.create_dt);
SELECT rowid, gr.*
FROM IGS_GR_GRADUAND_ALL gr
WHERE gr.person_id = cp_person_id AND
gr.create_dt = cp_create_dt
FOR UPDATE OF s_graduand_type NOWAIT;
-- IGS_GR_AWD_CEREMONY record and then delete it.
IF IGS_GR_PRC_GAC.grdp_prc_awc_close(
p_grd_cal_type,
p_grd_ci_sequence_number,
v_awc_rec.ceremony_number,
v_awc_rec.award_course_cd,
v_awc_rec.award_crs_version_number,
v_awc_rec.award_cd,
p_resolve_stalemate_type,
p_ignore_unit_sets_ind,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
-- IGS_GR_AWD_CRM_US_GP record and then delete it.
IF IGS_GR_PRC_GAC.grdp_prc_acusg_close(
p_grd_cal_type,
p_grd_ci_sequence_number,
v_acusg_rec.ceremony_number,
v_acusg_rec.award_course_cd,
v_acusg_rec.award_crs_version_number,
v_acusg_rec.award_cd,
v_acusg_rec.us_group_number,
p_resolve_stalemate_type,
p_ignore_unit_sets_ind,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
-- 4.2 If the IGS_GR_GRADUAND has been deferred into this IGS_GR_CRMN_ROUND update the
-- s_graduand_type from DEFERRED to UNKOWN. DO NOT COMMIT THIS UPDATE.
IF v_gr_rec.s_graduand_type = cst_deferred THEN
BEGIN
OPEN c_gr_upd(
v_gr_rec.person_id,
v_gr_rec.create_dt);
-- 3.Delete the existing IGS_GR_AWD_CRMN record
IF (c_gr_upd%FOUND) THEN
IGS_GR_GRADUAND_PKG.UPDATE_ROW(
X_ROWID => v_gr_del.rowid,
X_PERSON_ID => v_gr_del.person_id,
X_CREATE_DT => v_gr_del.create_dt,
X_GRD_CAL_TYPE => v_gr_del.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER => v_gr_del.grd_ci_sequence_number,
X_COURSE_CD => v_gr_del.course_cd,
X_AWARD_COURSE_CD => v_gr_del.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER => v_gr_del.award_crs_version_number,
X_AWARD_CD => v_gr_del.award_cd,
X_GRADUAND_STATUS => v_gr_del.graduand_status,
X_GRADUAND_APPR_STATUS => v_gr_del.graduand_appr_status,
X_S_GRADUAND_TYPE => cst_unknown,
X_GRADUATION_NAME => v_gr_del.graduation_name,
X_PROXY_AWARD_IND => v_gr_del.proxy_award_ind,
X_PROXY_AWARD_PERSON_ID => v_gr_del.proxy_award_person_id,
X_PREVIOUS_QUALIFICATIONS => v_gr_del.previous_qualifications,
X_CONVOCATION_MEMBERSHIP_IND => v_gr_del.convocation_membership_ind,
X_SUR_FOR_COURSE_CD => v_gr_del.sur_for_course_cd,
X_SUR_FOR_CRS_VERSION_NUMBER => v_gr_del.sur_for_crs_version_number,
X_SUR_FOR_AWARD_CD => v_gr_del.sur_for_award_cd,
X_COMMENTS => v_gr_del.comments,
X_MODE => 'R',
X_ATTRIBUTE_CATEGORY => v_gr_del.attribute_category,
X_ATTRIBUTE1 => v_gr_del.attribute1,
X_ATTRIBUTE2 => v_gr_del.attribute2,
X_ATTRIBUTE3 => v_gr_del.attribute3,
X_ATTRIBUTE4 => v_gr_del.attribute4,
X_ATTRIBUTE5 => v_gr_del.attribute5,
X_ATTRIBUTE6 => v_gr_del.attribute6,
X_ATTRIBUTE7 => v_gr_del.attribute7,
X_ATTRIBUTE8 => v_gr_del.attribute8,
X_ATTRIBUTE9 => v_gr_del.attribute9,
X_ATTRIBUTE10 => v_gr_del.attribute10,
X_ATTRIBUTE11 => v_gr_del.attribute11,
X_ATTRIBUTE12 => v_gr_del.attribute12,
X_ATTRIBUTE13 => v_gr_del.attribute13,
X_ATTRIBUTE14 => v_gr_del.attribute14,
X_ATTRIBUTE15 => v_gr_del.attribute15,
X_ATTRIBUTE16 => v_gr_del.attribute16,
X_ATTRIBUTE17 => v_gr_del.attribute17,
X_ATTRIBUTE18 => v_gr_del.attribute18,
X_ATTRIBUTE19 => v_gr_del.attribute19,
X_ATTRIBUTE20 => v_gr_del.attribute20
);
Fnd_Message.Set_Name('IGS', 'IGS_GR_CANNOT_UPDATE_GRAD_REC');
-- this change would have been commited on the insert of the
-- IGS_GR_AWD_CRMN record.
IF v_gr_rec.s_graduand_type = cst_deferred THEN
ROLLBACK;
SELECT gst.closed_ind
FROM IGS_GR_STAT gst
WHERE gst.graduand_status = cp_graduand_status AND
gst.s_graduand_status = cp_s_graduand_status;
SELECT '1'
FROM IGS_EN_SPA_AWD_AIM spaaa
WHERE spaaa.person_id = cp_person_id
AND spaaa.course_cd = cp_course_cd
AND spaaa.award_cd = cp_award_cd
AND spaaa.complete_ind='Y';
SELECT gr.rowid,
gr.person_id,
gr.create_dt,
gr.grd_cal_type,
gr.grd_ci_sequence_number,
gr.course_cd,
gr.award_course_cd,
gr.award_crs_version_number,
gr.award_cd,
gr.graduand_status,
gr.graduand_appr_status,
gr.s_graduand_type,
gr.graduation_name,
gr.proxy_award_ind,
gr.proxy_award_person_id,
gr.previous_qualifications,
gr.convocation_membership_ind,
gr.sur_for_course_cd,
gr.sur_for_crs_version_number,
gr.sur_for_award_cd,
gr.comments,
gst.s_graduand_status,
gr.attribute_category,
gr.attribute1,
gr.attribute2,
gr.attribute3,
gr.attribute4,
gr.attribute5,
gr.attribute6,
gr.attribute7,
gr.attribute8,
gr.attribute9,
gr.attribute10,
gr.attribute11,
gr.attribute12,
gr.attribute13,
gr.attribute14,
gr.attribute15,
gr.attribute16,
gr.attribute17,
gr.attribute18,
gr.attribute19,
gr.attribute20
FROM IGS_GR_GRADUAND gr,
IGS_GR_STAT gst
WHERE gr.graduand_status = gst.graduand_status AND
gst.s_graduand_status IN
('POTENTIAL',
'ELIGIBLE')
FOR UPDATE OF gr.graduand_status NOWAIT;
IGS_GR_GRADUAND_PKG.UPDATE_ROW(
X_ROWID => v_gr_gst_rec.rowid,
X_PERSON_ID => v_gr_gst_rec.person_id,
X_CREATE_DT => v_gr_gst_rec.create_dt,
X_GRD_CAL_TYPE => v_gr_gst_rec.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER => v_gr_gst_rec.grd_ci_sequence_number,
X_COURSE_CD => v_gr_gst_rec.course_cd,
X_AWARD_COURSE_CD => v_gr_gst_rec.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER => v_gr_gst_rec.award_crs_version_number,
X_AWARD_CD => v_gr_gst_rec.award_cd,
X_GRADUAND_STATUS => v_graduand_status,
X_GRADUAND_APPR_STATUS => v_approval_status,
X_S_GRADUAND_TYPE => v_gr_gst_rec.s_graduand_type,
X_GRADUATION_NAME => v_gr_gst_rec.graduation_name,
X_PROXY_AWARD_IND => v_gr_gst_rec.proxy_award_ind,
X_PROXY_AWARD_PERSON_ID => v_gr_gst_rec.proxy_award_person_id,
X_PREVIOUS_QUALIFICATIONS => v_gr_gst_rec.previous_qualifications,
X_CONVOCATION_MEMBERSHIP_IND => v_gr_gst_rec.convocation_membership_ind,
X_SUR_FOR_COURSE_CD => v_gr_gst_rec.sur_for_course_cd,
X_SUR_FOR_CRS_VERSION_NUMBER => v_gr_gst_rec.sur_for_crs_version_number,
X_SUR_FOR_AWARD_CD => v_gr_gst_rec.sur_for_award_cd,
X_COMMENTS => v_gr_gst_rec.comments,
X_MODE => 'R',
X_ATTRIBUTE_CATEGORY => v_gr_gst_rec.attribute_category,
X_ATTRIBUTE1 => v_gr_gst_rec.attribute1,
X_ATTRIBUTE2 => v_gr_gst_rec.attribute2,
X_ATTRIBUTE3 => v_gr_gst_rec.attribute3,
X_ATTRIBUTE4 => v_gr_gst_rec.attribute4,
X_ATTRIBUTE5 => v_gr_gst_rec.attribute5,
X_ATTRIBUTE6 => v_gr_gst_rec.attribute6,
X_ATTRIBUTE7 => v_gr_gst_rec.attribute7,
X_ATTRIBUTE8 => v_gr_gst_rec.attribute8,
X_ATTRIBUTE9 => v_gr_gst_rec.attribute9,
X_ATTRIBUTE10 => v_gr_gst_rec.attribute10,
X_ATTRIBUTE11 => v_gr_gst_rec.attribute11,
X_ATTRIBUTE12 => v_gr_gst_rec.attribute12,
X_ATTRIBUTE13 => v_gr_gst_rec.attribute13,
X_ATTRIBUTE14 => v_gr_gst_rec.attribute14,
X_ATTRIBUTE15 => v_gr_gst_rec.attribute15,
X_ATTRIBUTE16 => v_gr_gst_rec.attribute16,
X_ATTRIBUTE17 => v_gr_gst_rec.attribute17,
X_ATTRIBUTE18 => v_gr_gst_rec.attribute18,
X_ATTRIBUTE19 => v_gr_gst_rec.attribute19,
X_ATTRIBUTE20 => v_gr_gst_rec.attribute20);
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
cst_update_of_clause
CONSTANT VARCHAR2(40) := 'FOR UPDATE OF gac.person_id NOWAIT';
v_gac_upd_select VARCHAR2(2000);
SELECT gc.ceremony_number
FROM IGS_GR_CRMN gc
WHERE gc.grd_cal_type = p_grd_cal_type AND
gc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
gc.ceremony_number LIKE p_ceremony_number;
SELECT /*+ INDEX(awc awc_uk*/
'X'
FROM IGS_GR_AWD_CEREMONY awc
WHERE awc.grd_cal_type = p_grd_cal_type AND
awc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
awc.ceremony_number = cp_ceremony_number AND
awc.closed_ind = 'Y' AND
EXISTS (SELECT 'X'
FROM IGS_GR_AWD_CRMN gac
WHERE gac.grd_cal_type = awc.grd_cal_type AND
gac.grd_ci_sequence_number = awc.grd_ci_sequence_number AND
gac.ceremony_number = awc.ceremony_number AND
((gac.award_course_cd IS NULL AND
awc.award_course_cd IS NULL) OR
gac.award_course_cd = awc.award_course_cd) AND
((gac.award_crs_version_number IS NULL AND
awc.award_crs_version_number IS NULL) OR
awc.award_crs_version_number = gac.award_crs_version_number) AND
awc.award_cd = gac.award_cd);
SELECT /*+ INDEX(acusg acusg_pk)*/
'X'
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 = cp_ceremony_number AND
acusg.closed_ind = 'Y' AND
EXISTS
(SELECT 'X'
FROM IGS_GR_AWD_CRMN gac
WHERE gac.grd_cal_type = acusg.grd_cal_type AND
gac.grd_ci_sequence_number = acusg.grd_ci_sequence_number AND
gac.ceremony_number = acusg.ceremony_number AND
gac.award_course_cd = acusg.award_course_cd AND
gac.award_crs_version_number = acusg.award_crs_version_number AND
gac.award_cd = acusg.award_cd AND
gac.us_group_number = acusg.us_group_number);
SELECT rowid, gac.*
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 = cp_ceremony_number
FOR UPDATE OF gac.order_in_presentation NOWAIT;
SELECT gac.order_in_presentation
FROM IGS_GR_AWD_CRMN gac
WHERE gac.person_id = cp_person_id AND
gac.grd_cal_type = p_grd_cal_type AND
gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
gac.ceremony_number = cp_ceremony_number AND
(((gac.award_course_cd IS NULL AND
cp_award_course_cd IS NULL) OR
gac.award_course_cd <> cp_award_course_cd) OR
((gac.award_crs_version_number IS NULL AND
cp_award_crs_version_number IS NULL) OR
cp_award_crs_version_number <> gac.award_crs_version_number) OR
gac.award_cd <> cp_award_cd) AND
gac.order_in_presentation IS NOT NULL;
SELECT max(order_in_presentation)
FROM igs_gr_awd_crmn
WHERE grd_cal_type = p_grd_cal_type
AND grd_ci_sequence_number = p_grd_ci_sequence_number
AND ceremony_number = p_ceremony_number;
-- If the concurrent process is executed in 'Reallocate' mode then update
-- all the order in presentation column to NULL. This will allows to
-- assign a fresh order for the graduants who satify the given criteria.
-- Added the IF condition as part of order in Presentation DLD.
-- pradhakr; 14-Sep-2002; Bug# 2578638
IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
X_ROWID =>v_gac_del_rec.rowid,
X_GAC_ID =>v_gac_del_rec.GAC_ID,
X_GRADUAND_SEAT_NUMBER =>v_gac_del_rec.graduand_seat_number,
X_NAME_PRONUNCIATION =>v_gac_del_rec.name_pronunciation,
X_NAME_ANNOUNCED =>v_gac_del_rec.name_announced,
X_ACADEMIC_DRESS_RQRD_IND =>v_gac_del_rec.academic_dress_rqrd_ind,
X_ACADEMIC_GOWN_SIZE =>v_gac_del_rec.academic_gown_size,
X_ACADEMIC_HAT_SIZE =>v_gac_del_rec.academic_hat_size,
X_GUEST_TICKETS_REQUESTED =>v_gac_del_rec.guest_tickets_requested,
X_GUEST_TICKETS_ALLOCATED =>v_gac_del_rec.guest_tickets_allocated,
X_GUEST_SEATS =>v_gac_del_rec.guest_seats,
X_FEES_PAID_IND =>v_gac_del_rec.fees_paid_ind,
X_SPECIAL_REQUIREMENTS =>v_gac_del_rec.special_requirements,
X_COMMENTS =>v_gac_del_rec.COMMENTS,
X_PERSON_ID =>v_gac_del_rec.person_id,
X_CREATE_DT =>v_gac_del_rec.create_dt,
X_GRD_CAL_TYPE =>v_gac_del_rec.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER =>v_gac_del_rec.grd_ci_sequence_number,
X_CEREMONY_NUMBER =>v_gac_del_rec.ceremony_number,
X_AWARD_COURSE_CD =>v_gac_del_rec.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER =>v_gac_del_rec.award_crs_version_number,
X_AWARD_CD =>v_gac_del_rec.award_cd,
X_US_GROUP_NUMBER =>v_gac_del_rec.us_group_number,
X_ORDER_IN_PRESENTATION => NULL,
X_MODE => 'R');
Fnd_Message.Set_Name('IGS', 'IGS_GR_AWD_CERM_CANNOT_UPDATE');
v_gac_upd_select :=
'SELECT ' ||
'gac.person_id, ' ||
'gac.create_dt, ' ||
'gac.award_course_cd, ' ||
'gac.award_crs_version_number, '||
'gac.award_cd, ' ||
'gac.grd_cal_type, ' ||
'gac.grd_ci_sequence_number, '||
'gac.ceremony_number ' ||
'FROM IGS_GR_AWD_CRMN gac, ' ||
'IGS_GR_GRADUAND gr, ' ||
'IGS_GR_STAT gst, ' ||
'IGS_PE_PERSON pe, ' ||
'IGS_GR_AWD_CEREMONY awc, ' ||
'IGS_GR_AWD_CRM_US_GP acusg, '||
'IGS_GR_APRV_STAT gas ' ||
'WHERE gac.grd_cal_type=''' || p_grd_cal_type || ''' AND ' ||
'gac.grd_ci_sequence_number=' || TO_CHAR(p_grd_ci_sequence_number) || ' AND '||
'gac.ceremony_number=' || TO_CHAR(v_gc_rec.ceremony_number) || ' AND '||
'gac.person_id=gr.person_id AND ' ||
'gac.create_dt=gr.create_dt AND ' ||
'gac.person_id=pe.person_id AND ' ||
'gr.graduand_status=gst.graduand_status AND ' ||
'(gst.s_graduand_status=''' || cst_eligible || ''' OR ' ||
'gst.s_graduand_status=''' || cst_graduated || ''') AND ' ||
'gr.graduand_appr_status=gas.graduand_appr_status AND ' ||
'gas.s_graduand_appr_status=''' || cst_approved || ''' AND ' ||
'gr.s_graduand_type = ''' || cst_attending || ''' AND ' || /* Added to fix Bug# 3294453 */
'gac.grd_cal_type=awc.grd_cal_type AND ' ||
'gac.grd_ci_sequence_number=awc.grd_ci_sequence_number AND ' ||
'gac.ceremony_number=awc.ceremony_number AND ' ||
'((gac.award_course_cd IS NULL AND ' ||
'awc.award_course_cd IS NULL) OR ' ||
'gac.award_course_cd=awc.award_course_cd) AND '||
'((gac.award_crs_version_number IS NULL AND ' ||
'awc.award_crs_version_number IS NULL) OR ' ||
'awc.award_crs_version_number=gac.award_crs_version_number) AND ' ||
'gac.award_cd=awc.award_cd AND ' ||
'gac.grd_cal_type=acusg.grd_cal_type(+) AND ' ||
'gac.grd_ci_sequence_number=acusg.grd_ci_sequence_number(+) AND ' ||
'gac.ceremony_number=acusg.ceremony_number(+) AND ' ||
'gac.award_course_cd=acusg.award_course_cd(+) AND ' ||
'gac.award_crs_version_number=acusg.award_crs_version_number(+) AND ' ||
'gac.award_cd=acusg.award_cd(+) AND ' ||
'gac.us_group_number=acusg.us_group_number(+) ';
-- If job is running in 'Allocate' Mode then - select the Graduands
-- who has 'Order in Presentation' as NULL.
-- pradhakr; 15-Sep-2002; Bug# 2578638
v_gac_upd_select := v_gac_upd_select || 'AND gac.order_in_presentation IS NULL ' || 'ORDER BY ';
v_gac_upd_select := v_gac_upd_select || 'ORDER BY ';
--7.1 Construct the order by clause for the select statement:
v_order_by_clause := 'awc.order_in_ceremony ASC';
v_gac_upd_select || ' ' || v_order_by_clause || ' ' || cst_update_of_clause,
DBMS_SQL.NATIVE);
BEGIN -- local block for update error trapping
SAVEPOINT sp_gac_upd;
SELECT rowid, gac.*
FROM IGS_GR_AWD_CRMN gac
WHERE person_id = v_gac_upd_rec.person_id AND
create_dt = v_gac_upd_rec.create_dt AND
NVL(award_course_cd, 'NULL' )
= NVL(v_gac_upd_rec.award_course_cd, 'NULL') AND
NVL(award_crs_version_number, 0 )
= NVL(v_gac_upd_rec.award_crs_version_number, 0 ) AND
award_cd = v_gac_upd_rec.award_cd AND
grd_cal_type = v_gac_upd_rec.grd_cal_type AND
grd_ci_sequence_number = v_gac_upd_rec.grd_ci_sequence_number AND
ceremony_number = v_gac_upd_rec.ceremony_number;
IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
X_ROWID =>gac_rec.rowid,
X_GAC_ID =>gac_rec.GAC_ID,
X_GRADUAND_SEAT_NUMBER =>gac_rec.graduand_seat_number,
X_NAME_PRONUNCIATION =>gac_rec.name_pronunciation,
X_NAME_ANNOUNCED =>gac_rec.name_announced,
X_ACADEMIC_DRESS_RQRD_IND =>gac_rec.academic_dress_rqrd_ind,
X_ACADEMIC_GOWN_SIZE =>gac_rec.academic_gown_size,
X_ACADEMIC_HAT_SIZE =>gac_rec.academic_hat_size,
X_GUEST_TICKETS_REQUESTED =>gac_rec.guest_tickets_requested,
X_GUEST_TICKETS_ALLOCATED =>gac_rec.guest_tickets_allocated,
X_GUEST_SEATS =>gac_rec.guest_seats,
X_FEES_PAID_IND =>gac_rec.fees_paid_ind,
X_SPECIAL_REQUIREMENTS =>gac_rec.special_requirements,
X_COMMENTS =>gac_rec.COMMENTS,
X_PERSON_ID =>gac_rec.person_id,
X_CREATE_DT =>gac_rec.create_dt,
X_GRD_CAL_TYPE =>gac_rec.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER =>gac_rec.grd_ci_sequence_number,
X_CEREMONY_NUMBER =>gac_rec.ceremony_number,
X_AWARD_COURSE_CD =>gac_rec.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER =>gac_rec.award_crs_version_number,
X_AWARD_CD =>gac_rec.award_cd,
X_US_GROUP_NUMBER =>gac_rec.us_group_number,
X_ORDER_IN_PRESENTATION => gac_rec.order_in_presentation,
X_MODE => 'R');
SELECT rowid, gac.*
FROM IGS_GR_AWD_CRMN gac
WHERE person_id = v_gac_upd_rec.person_id AND
create_dt = v_gac_upd_rec.create_dt AND
NVL(award_course_cd, 'NULL' )
= NVL(v_gac_upd_rec.award_course_cd, 'NULL') AND
NVL(award_crs_version_number, 0 )
= NVL(v_gac_upd_rec.award_crs_version_number, 0 ) AND
award_cd = v_gac_upd_rec.award_cd AND
grd_cal_type = v_gac_upd_rec.grd_cal_type AND
grd_ci_sequence_number = v_gac_upd_rec.grd_ci_sequence_number AND
ceremony_number = v_gac_upd_rec.ceremony_number;
IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
X_ROWID =>gac_rec.rowid,
X_GAC_ID =>gac_rec.GAC_ID,
X_GRADUAND_SEAT_NUMBER =>gac_rec.graduand_seat_number,
X_NAME_PRONUNCIATION =>gac_rec.name_pronunciation,
X_NAME_ANNOUNCED =>gac_rec.name_announced,
X_ACADEMIC_DRESS_RQRD_IND =>gac_rec.academic_dress_rqrd_ind,
X_ACADEMIC_GOWN_SIZE =>gac_rec.academic_gown_size,
X_ACADEMIC_HAT_SIZE =>gac_rec.academic_hat_size,
X_GUEST_TICKETS_REQUESTED =>gac_rec.guest_tickets_requested,
X_GUEST_TICKETS_ALLOCATED =>gac_rec.guest_tickets_allocated,
X_GUEST_SEATS =>gac_rec.guest_seats,
X_FEES_PAID_IND =>gac_rec.fees_paid_ind,
X_SPECIAL_REQUIREMENTS =>gac_rec.special_requirements,
X_COMMENTS =>gac_rec.COMMENTS,
X_PERSON_ID =>gac_rec.person_id,
X_CREATE_DT =>gac_rec.create_dt,
X_GRD_CAL_TYPE =>gac_rec.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER =>gac_rec.grd_ci_sequence_number,
X_CEREMONY_NUMBER =>gac_rec.ceremony_number,
X_AWARD_COURSE_CD =>gac_rec.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER =>gac_rec.award_crs_version_number,
X_AWARD_CD =>gac_rec.award_cd,
X_US_GROUP_NUMBER =>gac_rec.us_group_number,
X_ORDER_IN_PRESENTATION => gac_rec.order_in_presentation,
X_MODE => 'R');
SELECT rowid, gac.*
FROM IGS_GR_AWD_CRMN gac
WHERE person_id = v_gac_upd_rec.person_id AND
create_dt = v_gac_upd_rec.create_dt AND
NVL(award_course_cd, 'NULL' )
= NVL(v_gac_upd_rec.award_course_cd, 'NULL') AND
NVL(award_crs_version_number, 0 )
= NVL(v_gac_upd_rec.award_crs_version_number, 0 ) AND
award_cd = v_gac_upd_rec.award_cd AND
grd_cal_type = v_gac_upd_rec.grd_cal_type AND
grd_ci_sequence_number = v_gac_upd_rec.grd_ci_sequence_number AND
ceremony_number = v_gac_upd_rec.ceremony_number;
IGS_GR_AWD_CRMN_PKG.UPDATE_ROW(
X_ROWID =>gac_rec.rowid,
X_GAC_ID =>gac_rec.GAC_ID,
X_GRADUAND_SEAT_NUMBER =>gac_rec.graduand_seat_number,
X_NAME_PRONUNCIATION =>gac_rec.name_pronunciation,
X_NAME_ANNOUNCED =>gac_rec.name_announced,
X_ACADEMIC_DRESS_RQRD_IND =>gac_rec.academic_dress_rqrd_ind,
X_ACADEMIC_GOWN_SIZE =>gac_rec.academic_gown_size,
X_ACADEMIC_HAT_SIZE =>gac_rec.academic_hat_size,
X_GUEST_TICKETS_REQUESTED =>gac_rec.guest_tickets_requested,
X_GUEST_TICKETS_ALLOCATED =>gac_rec.guest_tickets_allocated,
X_GUEST_SEATS =>gac_rec.guest_seats,
X_FEES_PAID_IND =>gac_rec.fees_paid_ind,
X_SPECIAL_REQUIREMENTS =>gac_rec.special_requirements,
X_COMMENTS =>gac_rec.COMMENTS,
X_PERSON_ID =>gac_rec.person_id,
X_CREATE_DT =>gac_rec.create_dt,
X_GRD_CAL_TYPE =>gac_rec.grd_cal_type,
X_GRD_CI_SEQUENCE_NUMBER =>gac_rec.grd_ci_sequence_number,
X_CEREMONY_NUMBER =>gac_rec.ceremony_number,
X_AWARD_COURSE_CD =>gac_rec.award_course_cd,
X_AWARD_CRS_VERSION_NUMBER =>gac_rec.award_crs_version_number,
X_AWARD_CD =>gac_rec.award_cd,
X_US_GROUP_NUMBER =>gac_rec.us_group_number,
X_ORDER_IN_PRESENTATION => gac_rec.order_in_presentation,
X_MODE => 'R');
FND_MESSAGE.SET_NAME('IGS', 'IGS_GR_AWD_CERM_CANNOT_UPDATE');