[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION admp_val_acai_insert(
p_person_id IN NUMBER ,
p_admission_appl_number IN NUMBER ,
p_nominated_course_cd IN VARCHAR2 ,
p_acai_sequence_number IN NUMBER ,
p_course_cd IN VARCHAR2 ,
p_location_cd IN VARCHAR2 ,
p_attendance_mode IN VARCHAR2 ,
p_attendance_type IN VARCHAR2 ,
p_unit_set_cd IN VARCHAR2 ,
p_us_version_number IN NUMBER ,
p_adm_cal_type IN VARCHAR2 ,
p_adm_ci_sequence_number IN NUMBER ,
p_s_admission_process_type IN VARCHAR2 ,
p_pref_limit IN NUMBER ,
p_validate_aa_only IN BOOLEAN ,
p_message_name OUT NOCOPY VARCHAR2,
p_return_type OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
gv_validate_aa_only VARCHAR2(1);
BEGIN -- admp_val_acai_insert
-- Routine to validate the insert of an IGS_AD_PS_APPL_INST record.
DECLARE
cst_completed CONSTANT VARCHAR2(9) := 'COMPLETED';
SELECT 'x'
FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
WHERE aav.person_id = acaiv.person_id AND
aav.admission_appl_number = acaiv.admission_appl_number AND
acaiv.person_id = cp_person_id AND
acaiv.course_cd = cp_course_cd AND
(acaiv.admission_appl_number <> cp_admission_appl_number OR
acaiv.sequence_number <> cp_acai_sequence_number) AND
NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
NVL(acaiv.adm_cal_type, aav.adm_cal_type) = cp_adm_cal_type AND
NVL(acaiv.adm_ci_sequence_number, aav.adm_ci_sequence_number)
= cp_adm_ci_sequence_number AND
-- Check for CANCELLED added for bug 2678766
NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status),'x') <> 'CANCELLED';
SELECT 'x'
FROM IGS_AD_APPL aav, IGS_AD_PS_APPL_INST acaiv
WHERE aav.person_id = acaiv.person_id AND
aav.admission_appl_number = acaiv.admission_appl_number AND
acaiv.person_id = cp_person_id AND
acaiv.course_cd = cp_course_cd AND
(acaiv.admission_appl_number <> cp_admission_appl_number OR
acaiv.sequence_number <> cp_acai_sequence_number) AND
NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
NVL(acaiv.adm_cal_type, aav.adm_cal_type) = cp_adm_cal_type AND
NVL(acaiv.adm_ci_sequence_number, aav.adm_ci_sequence_number)
= cp_adm_ci_sequence_number AND
NVL(aav.choice_number, 0) = NVL(cp_choice_number,0) AND
NVL(aav.alt_appl_id, 0) = NVL(cp_alt_appl_id,0) AND
-- Check for CANCELLED added for bug 2678766
NVL(IGS_AD_GEN_008.ADMP_GET_SAOS(acaiv.adm_outcome_status),'x') <> 'CANCELLED';
SELECT
choice_number, alt_appl_id
FROM
igs_ad_appl aav
WHERE
aav.person_id = cp_person_id
AND aav.admission_appl_number = cp_admission_appl_number;
SELECT
api_person_id
FROM
igs_pe_alt_pers_id
WHERE
sysdate BETWEEN start_dt AND NVL(end_dt, sysdate)
AND person_id_type = 'UCASID'
AND pe_person_id = cp_n_person_id ;
SELECT aa.adm_appl_status
FROM IGS_AD_PS_APPL_INST acaiv,
IGS_AD_APPL aa
WHERE acaiv.person_id = cp_person_id AND
acaiv.nominated_course_cd = cp_course_cd AND
(acaiv.admission_appl_number <> cp_admission_appl_number OR
acaiv.sequence_number <> cp_acai_sequence_number) AND
NVL(acaiv.location_cd,'NULL') = NVL(cp_location_cd,'NULL') AND
NVL(acaiv.attendance_mode,'NULL') = NVL(cp_attendance_mode,'NULL') AND
NVL(acaiv.attendance_type,'NULL') = NVL(cp_attendance_type,'NULL') AND
NVL(acaiv.unit_set_cd, 'NULL') = NVL(cp_unit_set_cd, 'NULL') AND
NVL(acaiv.us_version_number,0) = NVL(cp_us_version_number,0) AND
(NVL(acaiv.adm_cal_type,aa.adm_cal_type) <> cp_adm_cal_type OR
NVL(acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number) <> cp_adm_ci_sequence_number) AND
acaiv.person_id = aa.person_id AND
acaiv.admission_appl_number = aa.admission_appl_number;
SELECT 'x'
FROM IGS_AD_APPL_LTR aal
WHERE aal.person_id = cp_person_id AND
aal.admission_appl_number = cp_admission_appl_number;
SELECT aas.s_adm_appl_status
FROM IGS_AD_APPL aa,
IGS_AD_APPL_STAT aas
WHERE aa.person_id = cp_person_id AND
aa.admission_appl_number = cp_admission_appl_number AND
aas.adm_appl_status = aa.adm_appl_status;
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_insert');
END admp_val_acai_insert;
FUNCTION admp_val_acai_update(
p_adm_appl_status IN VARCHAR2 ,
p_person_id IN NUMBER ,
p_admission_appl_number IN NUMBER ,
p_nominated_course_cd IN VARCHAR2 ,
p_acai_sequence_number IN NUMBER ,
p_message_name OUT NOCOPY VARCHAR2 ,
p_update_non_enrol_detail_ind OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN --admp_val_acai_update
--This module validates update of an IGS_AD_PS_APPL_INST record.
DECLARE
v_s_appl_inst_status IGS_AD_APPL_STAT.s_adm_appl_status%TYPE;
SELECT sca.person_id
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id AND
sca.adm_admission_appl_number IS NOT NULL AND
sca.adm_admission_appl_number = cp_admission_appl_number AND
sca.adm_nominated_course_cd IS NOT NULL AND
sca.adm_nominated_course_cd = cp_nominated_course_cd AND
sca.adm_sequence_number IS NOT NULL AND
sca.adm_sequence_number = cp_acai_sequence_number AND
sca.student_confirmed_ind = 'Y';
SELECT appl_inst_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_acai_sequence_number;
-- Set the default update status indicator.
p_update_non_enrol_detail_ind := 'N';
p_update_non_enrol_detail_ind := 'N';
p_update_non_enrol_detail_ind := 'N';
p_update_non_enrol_detail_ind := 'Y';
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_update');
END admp_val_acai_update;
FUNCTION admp_val_acai_delete(
p_person_id IN NUMBER ,
p_admission_appl_number IN NUMBER ,
p_adm_outcome_status IN VARCHAR2 ,
p_message_name OUT NOCOPY VARCHAR2 ,
p_return_type OUT NOCOPY VARCHAR2 )
RETURN BOOLEAN AS
BEGIN -- admp_val_acai_delete
-- Validate delete of an IGS_AD_PS_APPL_INST record
DECLARE
CURSOR c_aal IS
SELECT 'x'
FROM IGS_AD_APPL_LTR
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT 'x'
FROM IGS_AD_APPL aa,
IGS_AD_APPL_STAT aas
WHERE aa.person_id = p_person_id AND
aa.admission_appl_number = p_admission_appl_number AND
aas.adm_appl_status = aa.adm_appl_status AND
aas.s_adm_appl_status = 'WITHDRAWN';
FND_MESSAGE.SET_TOKEN('NAME', 'IGS_AD_VAL_ACAI.admp_val_acai_delete');
END admp_val_acai_delete;
SELECT sacco.adm_appl_chng_of_pref_dt_alias
FROM IGS_AD_CAL_CONF sacco
WHERE sacco.s_control_num = 1;
SELECT version_number
FROM IGS_PS_VER
WHERE course_cd = p_course_cd;
SELECT course_attempt_status,
fee_cat,
correspondence_cat,
discontinued_dt
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT 'x'
FROM IGS_FI_FEE_CAT_MAP
WHERE admission_cat = p_admission_cat AND
fee_cat = cp_fee_cat;
SELECT 'x'
FROM IGS_CO_CAT_MAP
WHERE admission_cat = p_admission_cat AND
correspondence_cat = cp_correspondence_cat;
SELECT enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
scae.cal_type = ci.cal_type AND
scae.ci_sequence_number = ci.sequence_number
ORDER BY ci.start_dt DESC;
SELECT 'x'
FROM IGS_EN_CAT_MAPPING
WHERE admission_cat = p_admission_cat AND
enrolment_cat = cp_enrolment_cat ;
IF v_sca_rec.course_attempt_status NOT IN ('DELETED', 'UNCONFIRM') THEN
p_return_type := cst_error;
SELECT location_cd,
attendance_mode,
attendance_type
FROM IGS_PS_OFR_OPT
WHERE course_cd = p_course_cd AND
version_number = p_version_number AND
cal_type = p_acad_cal_type AND
delete_flag= 'N';
SELECT 'x'
FROM IGS_PS_OFR_PAT
WHERE course_cd = cp_course_cd AND
version_number = cp_version_number AND
cal_type = cp_acad_cal_type AND
ci_sequence_number = cp_acad_ci_sequence_number AND
location_cd = cp_location_cd AND
attendance_mode = cp_attendance_mode AND
attendance_type = cp_attendance_type AND
offered_ind = 'Y' AND
entry_point_ind = 'Y';
SELECT adm_appl_due_dt_alias,
adm_appl_final_dt_alias
FROM IGS_AD_CAL_CONF
WHERE s_control_num = 1 AND
adm_appl_due_dt_alias IS NOT NULL AND
adm_appl_final_dt_alias IS NOT NULL;
SELECT location_cd,
attendance_mode,
attendance_type
FROM IGS_PS_OFR_PAT_OFERPAT_V
WHERE course_cd = p_course_cd AND
version_number = p_version_number AND
acad_cal_type = p_acad_cal_type AND
acad_ci_sequence_number = p_acad_ci_sequence_number AND
adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_ci_sequence_number AND
admission_cat = p_admission_cat AND
s_admission_process_type = p_s_admission_process_type AND
(p_location_cd IS NULL OR
location_cd = p_location_cd) AND
(p_attendance_mode IS NULL OR
attendance_mode = p_attendance_mode) AND
(p_attendance_type IS NULL OR
attendance_type = p_attendance_type) AND
(IGS_AD_GEN_013.ADMS_GET_COO_CRV(
course_cd,
version_number,
s_admission_process_type,
p_offer_ind) = 'Y') AND
(IGS_AD_GEN_013.ADMS_GET_ACAI_COO (
course_cd,
version_number,
location_cd,
attendance_mode,
attendance_type,
acad_cal_type,
acad_ci_sequence_number,
adm_cal_type,
adm_ci_sequence_number,
admission_cat,
s_admission_process_type,
p_offer_ind,
p_appl_dt,
p_late_appl_allowed,
'N') = 'Y'); -- Deferred application.
SELECT uss.s_unit_set_status,
us.expiry_dt
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_STAT uss
WHERE us.unit_set_cat IN
( SELECT usc.unit_set_cat
FROM igs_en_unit_set_cat usc
WHERE (fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND' ) <> 'Y'
OR usc.s_unit_set_cat = 'PRENRL_YR') )
AND us.UNIT_SET_STATUS = uss.UNIT_SET_STATUS
AND us.unit_set_cd = cp_unit_set_cd
AND us.version_number = cp_us_version_number;
SELECT 1
FROM IGS_PS_OFR_OPT_UNIT_SET_V psusv
WHERE psusv.course_cd = cp_course_cd
AND psusv.crv_version_number = cp_crv_version_number
AND psusv.unit_set_cd = cp_unit_set_cd
AND psusv.us_version_number = cp_us_version_number
AND psusv.cal_type = cp_acad_cal_type
AND psusv.location_cd = NVL(cp_location_cd, psusv.location_cd)
AND psusv.attendance_mode = NVL(cp_attendance_mode, psusv.attendance_mode)
AND psusv.attendance_type = NVL(cp_attendance_type, psusv.attendance_type)
AND NOT EXISTS
(SELECT 1
FROM igs_ps_coo_ad_unit_s psus
WHERE psus.course_cd = psusv.course_cd
AND psus.crv_version_number = psusv.crv_version_number
AND psus.cal_type = psusv.cal_type
AND psus.location_cd = psusv.location_cd
AND psus.attendance_mode = psusv.attendance_mode
AND psus.attendance_type = psusv.attendance_type
AND psus.admission_cat = cp_admission_cat
)
AND psusv.UNIT_SET_STATUS IN
(SELECT unit_set_status
FROM igs_en_unit_set_stat uss
WHERE psusv.unit_set_status = uss.unit_set_status
AND uss.s_unit_set_status <> 'INACTIVE'
)
AND psusv.unit_set_cat IN
(SELECT usc.unit_set_cat
FROM igs_en_unit_set_cat usc
WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
OR usc.s_unit_set_cat = 'PRENRL_YR'))
)
AND psusv.expiry_dt IS NULL
UNION
SELECT 1
FROM igs_ps_coo_ad_unit_s psus,
igs_en_unit_set us
WHERE us.unit_set_cd = psus.unit_set_cd
AND us.version_number = psus.us_version_number
AND psus.unit_set_cd = cp_unit_set_cd
AND psus.us_version_number = cp_us_version_number
AND psus.course_cd = cp_course_cd
AND psus.crv_version_number = cp_crv_version_number
AND psus.cal_type = cp_acad_cal_type
AND psus.location_cd = NVL(cp_location_cd, psus.location_cd)
AND psus.attendance_mode = NVL(cp_attendance_mode, psus.attendance_mode)
AND psus.attendance_type = NVL(cp_attendance_type, psus.attendance_type)
AND psus.admission_cat = cp_admission_cat
AND us.unit_set_status IN
(SELECT unit_set_status
FROM igs_en_unit_set_stat uss
WHERE us.unit_set_status = uss.unit_set_status
AND uss.s_unit_set_status <> 'INACTIVE'
)
AND us.unit_set_cat IN
(SELECT usc.unit_set_cat
FROM igs_en_unit_set_cat usc
WHERE ((fnd_profile.value ('IGS_PS_PRENRL_YEAR_IND') <> 'Y'
OR usc.s_unit_set_cat = 'PRENRL_YR'))
)
AND us.expiry_dt IS NULL;
SELECT coacus.unit_set_cd,
coacus.us_version_number,
coacus.location_cd,
coacus.attendance_mode,
coacus.attendance_type
FROM IGS_PS_COO_AD_UNIT_S coacus
WHERE coacus.course_cd = cp_course_cd AND
coacus.crv_version_number = cp_crv_version_number AND
coacus.cal_type = cp_acad_cal_type AND
coacus.admission_cat = cp_admission_cat AND
coacus.location_cd = cp_location_cd AND
coacus.attendance_mode = cp_attendance_mode AND
coacus.attendance_type = cp_attendance_type;
SELECT 'x'
FROM IGS_PS_OF_UNT_SET_RL cousr
WHERE cousr.course_cd = p_course_cd AND
cousr.crv_version_number = p_crv_version_number AND
cousr.cal_type = p_cal_type AND
cousr.sub_unit_set_cd = p_unit_set_cd AND
cousr.sub_us_version_number = p_us_version_number;
Select 'x'
FROM IGS_CO_CAT_MAP
WHERE correspondence_cat = p_correspondence_cat AND
admission_cat = p_admission_cat;
SELECT cc.closed_ind
FROM IGS_CO_CAT cc
WHERE cc.correspondence_cat = p_correspondence_cat;
Select 'x'
FROM IGS_EN_CAT_MAPPING
WHERE enrolment_cat = p_enrolment_cat AND
admission_cat = p_admission_cat;
SELECT 'x'
FROM IGS_FI_FEE_CAT_MAP
WHERE fee_cat = cp_fee_cat AND
admission_cat = cp_admission_cat;
SELECT 'x'
FROM IGS_AD_CT_HECS_PAYOP
WHERE admission_cat = p_admission_cat AND
HECS_PAYMENT_OPTION = p_hecs_payment_option;
SELECT pti.person_type_code
FROM igs_pe_person_types pt,igs_pe_typ_instances pti
WHERE pti.person_id = p_person_id
AND pti.person_type_code = pt.person_type_code
AND pt.system_type = 'STAFF'
AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
SELECT pti.person_type_code
FROM igs_pe_person_types pt,igs_pe_typ_instances pti
WHERE pti.person_id = p_person_id
AND pti.person_type_code = pt.person_type_code
AND pt.system_type IN ('STAFF', 'FACULTY')
AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
SELECT 'X'
FROM igs_pe_person_base_v base, igs_pe_hz_parties pd
WHERE base.person_id = p_person_id
AND base.person_id = pd.party_id (+)
AND DECODE(base.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') = 'Y';
SELECT pti.person_type_code
FROM igs_pe_person_types pt,igs_pe_typ_instances pti
WHERE pti.person_id = p_person_id
AND pti.person_type_code = pt.person_type_code
AND pt.system_type IN ('STAFF', 'FACULTY' , 'EVALUATOR')
AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
SELECT 'X'
FROM igs_pe_person_base_v base, igs_pe_hz_parties pd
WHERE base.person_id = p_person_id
AND base.person_id = pd.party_id (+)
AND DECODE(base.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') = 'Y';
SELECT acaiv.location_cd,
acaiv.attendance_mode,
acaiv.attendance_type,
acaiv.unit_set_cd,
acaiv.us_version_number,
acaiv.ass_rank,
acaiv.intrntnl_acceptance_advice_num,
acaiv.ass_tracking_id,
acaiv.fee_cat,
acaiv.expected_completion_yr,
acaiv.expected_completion_perd,
acaiv.correspondence_cat,
acaiv.enrolment_cat,
acaiv.funding_source,
acav.basis_for_admission_type,
acav.admission_cd,
acav.course_rank_set,
acav.course_rank_schedule
FROM IGS_AD_PS_APPL_INST acaiv,
IGS_AD_PS_APPL acav
WHERE acaiv.person_id = p_person_id AND
acaiv.admission_appl_number = p_admission_appl_number AND
acaiv.nominated_course_cd = p_nominated_course_cd AND
acaiv.sequence_number = p_acai_sequence_number AND
acav.person_id = acaiv.person_id AND
acav.admission_appl_number = acaiv.admission_appl_number AND
acav.nominated_course_cd = acav.nominated_course_cd;
SELECT sca.fee_cat,
sca.correspondence_cat,
sca.funding_source
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT scae.enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_course_cd;
SELECT 'x'
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
cty.course_type = crv.course_type AND
cty.research_type_ind = 'Y';
SELECT apcs.s_admission_step_type
FROM IGS_AD_PRCS_CAT_STEP apcs
WHERE admission_cat= (SELECT admission_cat
FROM IGS_AD_APPL /* Replaced IGS_AD_APPL_ADMAPPL_V with IGS_AD_APPL Bug 3150054 */
WHERE person_id = p_person_id AND
admission_appl_number=p_admission_appl_number) AND
s_admission_process_type = p_s_admission_process_type AND
mandatory_step_ind = 'Y' AND
step_group_type <> 'TRACK';
SELECT person_id
FROM IGS_ad_appl_eval_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_nominated_course_cd;
SELECT person_id
FROM igs_Ad_appl_pgmapprv_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_nominated_course_cd;
SELECT person_id
FROM igs_ad_unit_sets_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_nominated_course_cd;
SELECT person_id
FROM igs_ad_other_inst
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT person_id
FROM igs_ad_edu_goal_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
nominated_course_cd = p_nominated_course_cd;
SELECT person_id
FROM igs_ad_acad_interest_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT person_id
FROM igs_ad_app_intent_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT person_id
FROM igs_ad_spl_interests_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT person_id
FROM igs_ad_spl_talents_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT person_id
FROM igs_ad_app_req_v
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number;
SELECT person_id
FROM igs_pe_credentials
WHERE person_id = p_person_id;
SELECT person_id
FROM igs_pe_acad_honors_v
WHERE person_id = p_person_id ;
SELECT apply_for_finaid,
app_file_location,
late_adm_fee_status,
enrolment_cat,
fee_cat,
hecs_payment_option,
app_source_id,
correspondence_cat,
funding_source
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;
SELECT person_id
FROM igs_ad_panel_dtls
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_acai_sequence_number AND
final_decision_type = 'FINAL_INTERVIEW';
SELECT person_id
FROM igs_ad_panel_dtls
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_acai_sequence_number AND
final_decision_type = 'FINAL_INTERVIEW' AND
EXISTS (SELECT 'x'
FROM igs_ad_code_classes
WHERE class = 'FINAL_INTERVIEW'
AND name = final_decision_code
AND system_status <> 'PENDING'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES');
SELECT pe.birth_date birth_dt,
pe.title
FROM IGS_PE_PERSON_BASE_V pe
WHERE pe.person_id = p_person_id;
SELECT acaiu.unit_cd,
acaiu.uv_version_number,
acaiu.cal_type,
acaiu.ci_sequence_number,
acaiu.location_cd,
acaiu.unit_class,
acaiu.unit_mode
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.adm_unit_outcome_status = acaiu.adm_unit_outcome_status AND
auos.s_adm_outcome_status = cst_offer;
SELECT fsr.funding_source
FROM IGS_FI_FND_SRC_RSTN fsr
WHERE fsr.course_cd = p_course_cd AND
fsr.version_number = p_version_number AND
fsr.restricted_ind = 'Y';