[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT closed_ind
FROM IGS_AD_ENT_QF_STAT
WHERE adm_entry_qual_status = cp_adm_entry_qual_status;
SELECT closed_ind
FROM IGS_AD_DOC_STAT
WHERE adm_doc_status = cp_adm_doc_status;
SELECT 'x'
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
NVL(sca.adm_nominated_course_cd, 'NULL') =
NVL(p_nominated_course_cd, 'NULL') AND
NVL(sca.adm_admission_appl_number, -1) = NVL(p_admission_appl_number, -1) AND
NVL(sca.adm_sequence_number, -1) = NVL(p_acai_sequence_number, -1) AND
sca.student_confirmed_ind = 'Y';
SELECT closed_ind
FROM IGS_AD_OFRDFRMT_STAT
WHERE adm_offer_dfrmnt_status = cp_adm_offer_dfrmnt_status;
SELECT closed_ind
FROM IGS_AD_FEE_STAT
WHERE adm_fee_status = p_adm_fee_status;
SELECT coo.location_cd,
coo.attendance_mode,
coo.attendance_type
FROM IGS_PS_OFR_OPT coo
WHERE coo.course_cd = p_course_cd AND
coo.version_number = p_crv_version_number AND
coo.cal_type = p_acad_cal_type AND
coo.delete_flag = 'N';
SELECT sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.adm_admission_appl_number IS NOT NULL AND
sca.adm_admission_appl_number = p_admission_appl_number AND
sca.adm_nominated_course_cd IS NOT NULL AND
sca.adm_nominated_course_cd = p_nominated_course_cd AND
sca.adm_sequence_number IS NOT NULL AND
sca.adm_sequence_number = p_acai_sequence_number AND
sca.student_confirmed_ind = 'Y';
SELECT aors.closed_ind
FROM IGS_AD_OFR_RESP_STAT aors
WHERE aors.adm_offer_resp_status = p_adm_offer_resp_status;
cst_deleted CONSTANT VARCHAR2(7) := 'DELETED';
SELECT 'x'
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.adm_admission_appl_number IS NOT NULL AND
sca.adm_admission_appl_number = p_admission_appl_number AND
sca.adm_nominated_course_cd IS NOT NULL AND
sca.adm_nominated_course_cd = p_nominated_course_cd AND
sca.adm_sequence_number IS NOT NULL AND
sca.adm_sequence_number = p_acai_sequence_number AND
sca.course_attempt_status NOT IN (cst_deleted, cst_unconfirmed);
SELECT s_admission_process_type
FROM igs_ad_appl
WHERE person_id = cp_person_id
AND admission_appl_number = cp_admission_appl_number;
-- Cannot withdraw or void and unless an offer is made first. In case the update is made from the cancel
-- reconsideration job, this restriction should not be there.
p_message_name := 'IGS_AD_OUTCOME_STATUS_CHG';
SELECT aos.closed_ind
FROM IGS_AD_OU_STAT aos
WHERE aos.adm_outcome_status = p_adm_outcome_status;
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
SELECT 'x'
FROM IGS_AD_APPL_LTR aal
WHERE aal.person_id = p_person_id AND
aal.admission_appl_number = p_admission_appl_number AND
aal.correspondence_type = cst_outcome_lt;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acai,
IGS_AD_OU_STAT aos
WHERE acai.person_id = p_person_id AND
acai.admission_appl_number = p_admission_appl_number AND
(acai.nominated_course_cd <> p_nominated_course_cd OR
acai.sequence_number <> p_acai_sequence_number) AND
acai.adm_outcome_status = aos.adm_outcome_status AND
aos.s_adm_outcome_status <> cst_pending;
SELECT 'x'
FROM IGS_AD_PS_APLINSTUNT acaiu,
IGS_AD_UNIT_OU_STAT auos
WHERE acaiu.person_id = p_person_id AND
acaiu.admission_appl_number = p_admission_appl_number AND
acaiu.nominated_course_cd = p_nominated_course_cd AND
acaiu.acai_sequence_number = p_acai_sequence_number AND
auos.s_adm_outcome_status = cst_pending AND
acaiu.adm_unit_outcome_status = auos.adm_unit_outcome_status;
SELECT sca.course_attempt_status
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd AND
sca.adm_admission_appl_number = p_admission_appl_number AND
sca.adm_nominated_course_cd = p_nominated_course_cd AND
sca.adm_sequence_number = p_acai_sequence_number;
SELECT cty.research_type_ind
FROM IGS_PS_VER crv,
IGS_PS_TYPE cty
WHERE crv.course_cd = p_course_cd AND
crv.version_number = p_crv_version_number AND
crv.course_type = cty.course_type;
SELECT aos.s_adm_outcome_status,
aors.s_adm_offer_resp_status,
aods.s_adm_offer_dfrmnt_status
FROM IGS_AD_PS_APLINSTHST acaih,
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors,
IGS_AD_OFRDFRMT_STAT aods
WHERE acaih.person_id = p_person_id AND
acaih.admission_appl_number = p_admission_appl_number AND
acaih.nominated_course_cd = p_nominated_course_cd AND
acaih.sequence_number = p_acai_sequence_number AND
acaih.adm_outcome_status = aos.adm_outcome_status (+) AND
acaih.adm_offer_resp_status = aors.adm_offer_resp_status (+)AND
acaih.adm_offer_dfrmnt_status = aods.adm_offer_dfrmnt_status (+)
ORDER BY acaih.hist_start_dt DESC;
SELECT 'x'
FROM IGS_AD_PS_APPL
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_nominated_course_cd AND
req_for_reconsideration_ind = 'Y';
-- Select the first record only.
-- (This is the most recent history record inserted. This history record
-- will have been
-- inserted as a part of reconsideration or deferment processing.)
-- Determine if deferment is being processed
FOR v_recent_his_rec IN c_recent_his LOOP
IF c_recent_his%ROWCOUNT = 1 THEN
IF v_recent_his_rec.s_adm_offer_resp_status IS NOT NULL AND
v_recent_his_rec.s_adm_offer_resp_status = cst_deferral AND
v_recent_his_rec.s_adm_offer_dfrmnt_status IS NOT NULL AND
v_recent_his_rec.s_adm_offer_dfrmnt_status = cst_approved THEN
v_defermt_processing := TRUE;
-- letter has already been inserted (the letter will need to be deleted).
p_message_name := 'IGS_AD_ST_NOTBE_PENDING_DEL';
-- admission application (exclude this instance in select)
OPEN c_acai_aos;
cst_deleted,
cst_lapsed) THEN
CLOSE c_sca;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors
WHERE acaiv.person_id = p_person_id AND
(acaiv.admission_appl_number <> p_admission_appl_number OR
acaiv.nominated_course_cd <> p_nominated_course_cd OR
acaiv.sequence_number <> p_acai_sequence_number) AND
acaiv.course_cd = p_course_cd AND
acaiv.adm_cal_type = p_adm_cal_type AND
acaiv.adm_ci_sequence_number = p_adm_ci_sequence_number AND
aos.s_adm_outcome_status IN (cst_offer, cst_cond_offer) AND
aors.s_adm_offer_resp_status NOT IN (cst_rejected, cst_not_applic) AND
acaiv.adm_outcome_status = aos.adm_outcome_status AND
acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
SELECT count(*)
FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
IGS_AD_APPL aa,
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors
WHERE acaiv.person_id = p_person_id AND
(acaiv.admission_appl_number <> p_admission_appl_number OR
acaiv.nominated_course_cd <> p_nominated_course_cd OR
acaiv.sequence_number <> p_acai_sequence_number) AND
acaiv.adm_cal_type = p_adm_cal_type AND
acaiv.adm_ci_sequence_number = p_adm_ci_sequence_number AND
aa.admission_cat = p_admission_cat AND
aa.s_admission_process_type = p_s_admission_process_type AND
aos.s_adm_outcome_status IN (cst_offer, cst_cond_offer) AND
aors.s_adm_offer_resp_status NOT IN (cst_rejected, cst_not_applic) AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number AND
-- aa.acad_cal_type = acaiv.acad_cal_type AND -- Commented this line as the join is not required Bug 3150054
-- aa.acad_ci_sequence_number = acaiv.acad_ci_sequence_number AND -- Commented this line as the join is not required Bug 3150054
aos.adm_outcome_status = acaiv.adm_outcome_status AND
acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
SELECT 'x'
FROM IGS_AD_PS_APPL_INST acaiv, /* References to IGS_AD_PS_APPL_INST_APLINST_V replaced with IGS_AD_PS_APPL_INST Bug 3150054 */
IGS_AD_APPL aa,
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors
WHERE acaiv.person_id = p_person_id AND
(acaiv.admission_appl_number <> p_admission_appl_number OR
acaiv.nominated_course_cd <> p_nominated_course_cd OR
acaiv.sequence_number <> p_acai_sequence_number) AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number AND
(aa.admission_cat <> p_admission_cat OR
aa.s_admission_process_type <> p_s_admission_process_type) AND
acaiv.adm_cal_type = p_adm_cal_type AND
acaiv.adm_ci_sequence_number = p_adm_ci_sequence_number AND
aos.s_adm_outcome_status IN (
cst_offer,
cst_cond_offer) AND
aors.s_adm_offer_resp_status NOT IN (
cst_rejected,
cst_not_applic) AND
acaiv.adm_outcome_status = aos.adm_outcome_status AND
acaiv.adm_offer_resp_status = aors.adm_offer_resp_status;
FUNCTION admp_val_aos_update(
p_person_id IN NUMBER ,
p_admission_appl_number IN NUMBER ,
p_adm_outcome_status IN VARCHAR2 ,
p_old_adm_outcome_status IN VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 ,
p_return_type OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN -- admp_val_aos_update
-- Validate update of the admission outcome status.
-- On update of the admission outcome status warn if outcome letter
-- correspondence is yet to be issued and phrases relating to the old status
-- are attached
DECLARE
v_aal_seq_no IGS_AD_APPL_LTR.sequence_number%TYPE;
SELECT aal.sequence_number
FROM IGS_AD_APPL_LTR aal,
IGS_AD_APPL_LTR_PHR aalp
WHERE aalp.person_id = p_person_id AND
aalp.admission_appl_number = p_admission_appl_number AND
aalp.correspondence_type = 'OUTCOME-LT' AND
aal.person_id = aalp.person_id AND
aal.admission_appl_number = aalp.admission_appl_number AND
aal.correspondence_type = aalp.correspondence_type AND
aal.sequence_number = aalp.aal_sequence_number;
-- Check if the outcome status has been updated
IF p_adm_outcome_status <> p_old_adm_outcome_status THEN
-- Warn on change of outcome status if outcome letter correspondence
-- is yet to be issued and has phrases relating to the old status attached
OPEN c_seq_no;
Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aos_update');
END admp_val_aos_update;
SELECT closed_ind
FROM IGS_AD_CNDNL_OFRSTAT
WHERE adm_cndtnl_offer_status =cp_adm_cndtnl_offer_status;
FUNCTION admp_val_aods_update(
p_person_id IN NUMBER ,
p_admission_appl_number IN NUMBER ,
p_nominated_course_cd IN VARCHAR2 ,
p_sequence_number IN NUMBER ,
p_offer_deferment_status IN VARCHAR2,
p_message_name OUT NOCOPY VARCHAR2)
RETURN BOOLEAN AS
BEGIN
-- hreddych #2602077 SF Integration Added the FUNCTION admp_val_aods_update
-- This function validates the change in the offer deferment status
-- The offer deferment status can be moved to CONFIRM only when the
-- status is APPROVED.
DECLARE
CURSOR cur_offer_dfrmnt_status(p_person_id igs_ad_ps_appl_inst.person_id%TYPE ,
p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
p_nominated_course_cd igs_ad_ps_appl_inst.nominated_course_cd%TYPE ,
p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE) IS
SELECT adm_offer_dfrmnt_status
FROM igs_ad_ps_appl_inst
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_nominated_course_cd AND
sequence_number = p_sequence_number ;
Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ACAI_STATUS.admp_val_aods_update');
END admp_val_aods_update;
SELECT ais.closed_ind
FROM igs_ad_appl_stat ais
WHERE ais.adm_appl_status = p_appl_inst_status;