[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT api.api_person_id
FROM IGS_PE_ALT_PERS_ID api,
IGS_PE_PERSON_ID_TYP pit
WHERE api.pe_person_id = p_person_id AND
pit.person_id_type = api.person_id_type AND
pit.s_person_id_type = cst_tac;
SELECT ceprc.course_cd,
ceprc.version_number,
ceprc.cal_type,
ceprc.location_cd,
ceprc.attendance_mode,
ceprc.attendance_type,
ceprc.coo_id,
rct.s_reference_cd_type,
ceprc.unit_set_cd,
ceprc.us_version_number
FROM IGS_PS_ENT_PT_REF_CD ceprc,
IGS_GE_REF_CD_TYPE rct,
IGS_PS_VER crv,
IGS_PS_STAT cs
WHERE rct.s_reference_cd_type = 'OTHER' AND
ceprc.reference_cd_type = rct.reference_cd_type AND
ceprc.reference_cd = p_reference_cd AND
ceprc.cal_type = p_acad_cal_type AND
crv.course_cd = ceprc.course_cd AND
crv.version_number = ceprc.version_number AND
crv.expiry_dt IS NULL AND
cs.course_status = crv.course_status AND
cs.s_course_status = cst_active AND
(ceprc.unit_set_cd IS NULL OR
EXISTS (
SELECT 'x'
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_STAT uss
WHERE us.unit_set_cd = ceprc.unit_set_cd AND
us.version_number = ceprc.us_version_number AND
expiry_dt IS NULL AND
uss.unit_set_status = us.unit_set_status AND
uss.s_unit_set_status = cst_active));
SELECT api.pe_person_id
FROM IGS_PE_ALT_PERS_ID api,
IGS_PE_PERSON_ID_TYP pit
WHERE pit.s_person_id_type = 'TAC' AND
api.person_id_type = pit.person_id_type AND
api.api_person_id = p_tac_person_id;
SELECT party_id person_id
FROM hz_parties
WHERE person_last_name = p_surname AND
person_first_name = cp_given_names;
SELECT ceprc.course_cd,
ceprc.version_number,
ceprc.location_cd,
ceprc.attendance_mode,
ceprc.attendance_type
FROM IGS_PS_ENT_PT_REF_CD ceprc,
IGS_GE_REF_CD_TYPE rct,
IGS_PS_VER crv,
IGS_PS_STAT cs
WHERE rct.s_reference_cd_type = 'OTHER' AND
ceprc.reference_cd_type = rct.reference_cd_type AND
ceprc.reference_cd = p_tac_course_cd AND
crv.course_cd = ceprc.course_cd AND
crv.version_number = ceprc.version_number AND
crv.expiry_dt IS NULL AND
cs.course_status = crv.course_status AND
cs.s_course_status = cst_active AND
(ceprc.unit_set_cd IS NULL OR
EXISTS (
SELECT 'x'
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_STAT uss
WHERE us.unit_set_cd = ceprc.unit_set_cd AND
us.version_number = ceprc.us_version_number AND
expiry_dt IS NULL AND
uss.unit_set_status = us.unit_set_status AND
uss.s_unit_set_status = cst_active))
ORDER BY rct.s_reference_cd_type DESC;
SELECT aors.s_adm_offer_resp_status,
aods.s_adm_offer_dfrmnt_status
FROM igs_ad_appl aa,
igs_ad_ps_appl_inst acaiv, /* Replaced IGS_AD_PS_APPL_INST_APLINST_V with IGS_AD_PS_APPL_INST. Bug 3150054 */
igs_ad_ofr_resp_stat aors,
igs_ad_ofrdfrmt_stat aods
WHERE acaiv.person_id = cp_matched_id AND
aa.acad_cal_type = p_acad_cal_type AND
aa.acad_ci_sequence_number = p_acad_ci_sequence_number AND
acaiv.course_cd = cp_course_cd AND
acaiv.crv_version_number = cp_version_number AND
acaiv.location_cd = cp_location_cd AND
acaiv.attendance_mode = cp_attendance_mode AND
acaiv.attendance_type = cp_attendance_type AND
aors.adm_offer_resp_status = acaiv.adm_offer_resp_status AND
aods.adm_offer_dfrmnt_status = acaiv.adm_offer_dfrmnt_status AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number;
SELECT aors.s_adm_offer_resp_status,
aods.s_adm_offer_dfrmnt_status,
acaiv.course_cd,
ceprc.reference_cd
FROM igs_ad_appl aa,
IGS_AD_PS_APPL_INST acaiv, /* Replaced IGS_AD_PS_APPL_INST_APLINST_V with IGS_AD_PS_APPL_INST. Bug 3150054 */
IGS_AD_OFR_RESP_STAT aors,
IGS_AD_OFRDFRMT_STAT aods,
IGS_PS_ENT_PT_REF_CD ceprc,
IGS_GE_REF_CD_TYPE rct
WHERE acaiv.person_id = cp_matched_id AND
aa.acad_cal_type = p_acad_cal_type AND
aa.acad_ci_sequence_number = p_acad_ci_sequence_number AND
aods.adm_offer_dfrmnt_status = acaiv.adm_offer_dfrmnt_status AND
aors.adm_offer_resp_status = acaiv.adm_offer_resp_status AND
ceprc.course_cd = acaiv.course_cd AND
ceprc.version_number = acaiv.crv_version_number AND
ceprc.location_cd = acaiv.location_cd AND
ceprc.attendance_mode = acaiv.attendance_mode AND
ceprc.attendance_type = acaiv.attendance_type AND
ceprc.reference_cd_type = rct.reference_cd_type AND
rct.s_reference_cd_type = 'OTHER' AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number
ORDER BY rct.s_reference_cd_type DESC;
SELECT govt_attendance_type
FROM IGS_EN_ATD_TYPE
WHERE attendance_type = v_attendance_type;
SELECT am.govt_attendance_mode
FROM IGS_EN_STDNT_PS_ATT sca,
IGS_EN_ATD_MODE am
WHERE sca.course_cd = cp_course_cd AND
sca.person_id = cp_person_id AND
am.attendance_mode = sca.attendance_mode;
IF (v_enrol_status IN ('DELETED','UNCONFIRM','LAPSED','INACTIVE',NULL)) THEN
p_offer_response := cst_accept;
SELECT acaiuv.unit_cd,
acaiuv.uv_version_number,
acaiuv.location_cd,
acaiuv.unit_class,
acaiuv.unit_mode,
acaiuv.teach_alternate_code,
uv.short_title,
acaiuv.adm_unit_outcome_status
FROM IGS_AD_PS_APLINSTUNT_APLUNIT_V acaiuv,
IGS_PS_UNIT_VER uv
WHERE acaiuv.uv_version_number = uv.version_number AND
acaiuv.unit_cd = uv.unit_cd AND
acaiuv.person_id = p_person_id AND
acaiuv.admission_appl_number = p_admission_appl_number AND
acaiuv.nominated_course_cd = p_nominated_course_cd AND
acaiuv.acai_sequence_number = p_acai_sequence_number
ORDER BY acaiuv.unit_cd,
acaiuv.uv_version_number,
acaiuv.cal_type,
acaiuv.ci_sequence_number,
acaiuv.location_cd,
acaiuv.unit_class,
acaiuv.unit_mode;
SELECT ca.alternate_code acad_alternate_code /* Replace IGS_AD_APPL_ADMAPPL_V with IGS_AD_APPL and IGS_CA_INST tables Bug: 3150054 */
FROM igs_ad_appl aav,
igs_ca_inst ca
WHERE aav.person_id = p_person_id AND
aav.admission_appl_number = p_admission_appl_number AND
aav.acad_cal_type = ca.cal_type AND
aav.acad_ci_sequence_number = ca.sequence_number;
-- This module validates and inserts a record into the IGS_AD_APPL_LTR
-- table
DECLARE
v_sequence_num IGS_AD_APPL_LTR.sequence_number%TYPE;
SELECT NVL(MAX(sequence_number),0) + 1
FROM IGS_AD_APPL_LTR
WHERE person_id = p_person_id AND
admission_appl_number = p_admission_appl_number AND
correspondence_type = p_correspondence_type;
-- Insert new record
IGS_AD_APPL_Ltr_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Admission_Appl_Number => p_admission_appl_number,
X_Correspondence_Type => p_correspondence_type,
X_Sequence_Number => v_sequence_num,
X_Composed_Ind => 'Y',
X_Letter_Reference_Number => Null,
X_Spl_Sequence_Number => Null
);
p_new_update_who IN IGS_AD_APPL_ALL.last_updated_by%TYPE ,
p_old_update_who IN IGS_AD_APPL_ALL.last_updated_by%TYPE ,
p_new_update_on IN IGS_AD_APPL_ALL.last_update_date%TYPE ,
p_old_update_on IN IGS_AD_APPL_ALL.last_update_date%TYPE )
IS
gv_other_detail VARCHAR2(255);
v_aah_rec.hist_start_dt := p_old_update_on;
v_aah_rec.hist_end_dt := p_new_update_on;
v_aah_rec.hist_who := p_old_update_who;
IGS_AD_APPL_Hist_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => v_aah_rec.person_id,
X_Admission_Appl_Number => v_aah_rec.admission_appl_number,
X_Hist_Start_Dt => v_aah_rec.hist_start_dt,
X_Hist_End_Dt => v_aah_rec.hist_end_dt,
X_Hist_Who => v_aah_rec.hist_who,
X_Appl_Dt => v_aah_rec.appl_dt,
X_Acad_Cal_Type => v_aah_rec.acad_cal_type,
X_Acad_Ci_Sequence_Number => v_aah_rec.acad_ci_sequence_number,
X_Adm_Cal_Type => v_aah_rec.acad_cal_type,
X_Adm_Ci_Sequence_Number => v_aah_rec.adm_ci_sequence_number,
X_Admission_Cat => v_aah_rec.admission_cat,
X_S_Admission_Process_Type => v_aah_rec.s_admission_process_type,
X_Adm_Appl_Status => v_aah_rec.adm_appl_status,
X_Adm_Fee_Status => v_aah_rec.adm_fee_status,
X_Tac_Appl_Ind => v_aah_rec.tac_appl_ind,
X_Org_Id => l_org_id
);
p_new_update_who IN VARCHAR2 ,
p_old_update_who IN VARCHAR2 ,
p_new_update_on IN DATE ,
p_old_update_on IN DATE )
IS
gv_other_detail VARCHAR2(255);
-- insert into history table
-- set the mandatory columns
v_acaiuh_rec.person_id := p_person_id;
v_acaiuh_rec.hist_start_dt := p_old_update_on;
v_acaiuh_rec.hist_end_dt := p_new_update_on;
v_acaiuh_rec.hist_who := p_old_update_who;
IGS_AD_PS_APINTUNTHS_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => v_acaiuh_rec.person_id,
X_Admission_Appl_Number => v_acaiuh_rec.admission_appl_number,
X_Nominated_Course_Cd => v_acaiuh_rec.nominated_course_cd,
X_Acai_Sequence_Number => v_acaiuh_rec.acai_sequence_number,
X_Unit_Cd => v_acaiuh_rec.unit_cd,
X_Hist_Start_Dt => v_acaiuh_rec.hist_start_dt,
X_Hist_End_Dt => v_acaiuh_rec.hist_end_dt,
X_Hist_Who => v_acaiuh_rec.hist_who,
X_Uv_Version_Number => v_acaiuh_rec.uv_version_number,
X_Cal_Type => v_acaiuh_rec.cal_type,
X_Ci_Sequence_Number => v_acaiuh_rec.ci_sequence_number,
X_Location_Cd => v_acaiuh_rec.location_cd,
X_Unit_Class => v_acaiuh_rec.unit_class,
X_Unit_Mode => v_acaiuh_rec.unit_mode,
X_Adm_Unit_Outcome_Status => v_acaiuh_rec.adm_unit_outcome_status,
X_Ass_Tracking_Id => v_acaiuh_rec.ass_tracking_id,
X_Rule_Waived_Dt => v_acaiuh_rec.rule_waived_dt,
X_Rule_Waived_Person_Id => v_acaiuh_rec.rule_waived_person_id,
X_Sup_Unit_Cd => v_acaiuh_rec.sup_unit_cd,
X_Sup_Uv_Version_Number => v_acaiuh_rec.sup_uv_version_number,
X_Org_Id => l_org_id,
X_adm_ps_appl_inst_unit_id => v_acaiuh_rec.adm_ps_appl_inst_unit_id,
X_adm_ps_appl_inst_unithist_id => v_acaiuh_rec.adm_ps_appl_inst_unit_hist_id
);