[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT acaiu.ROWID, acaiu.*
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
acaiu.adm_unit_outcome_status = auos.adm_unit_outcome_status AND
auos.s_adm_outcome_status IN ('PENDING', 'OFFER')
FOR UPDATE OF person_id NOWAIT;
IGS_AD_PS_APLINSTUNT_Pkg.Delete_Row (
v_acaiu_rec.RowId
);
IGS_AD_PS_APLINSTUNT_Pkg.Update_Row (
X_Mode => 'R',
X_RowId => v_acaiu_rec.RowId,
X_Person_Id => v_acaiu_rec.Person_Id,
X_Admission_Appl_Number => v_acaiu_rec.Admission_Appl_Number,
X_Nominated_Course_Cd => v_acaiu_rec.Nominated_Course_Cd,
X_Acai_Sequence_Number => v_acaiu_rec.Acai_Sequence_Number,
X_Unit_Cd => v_acaiu_rec.Unit_Cd,
X_Uv_Version_Number => v_acaiu_rec.Uv_Version_Number,
X_Cal_Type => NULL,
X_Ci_Sequence_Number => NULL,
X_Location_Cd => NULL,
X_Unit_Class => NULL,
X_Unit_Mode => NULL,
X_Adm_Unit_Outcome_Status => v_adm_unit_outcome_status,
X_Ass_Tracking_Id => v_acaiu_rec.Ass_Tracking_Id,
X_Rule_Waived_Dt => v_acaiu_rec.Rule_Waived_Dt,
X_Rule_Waived_Person_Id => v_acaiu_rec.Rule_Waived_Person_Id,
X_Sup_Unit_Cd => v_acaiu_rec.Sup_Unit_Cd,
X_Sup_Uv_Version_Number => v_acaiu_rec.Sup_Uv_Version_Number,
X_adm_ps_appl_inst_unit_id => v_acaiu_rec.adm_ps_appl_inst_unit_id
);
SELECT a.enrolment_cat,b.acad_cal_type,b.acad_ci_sequence_number
FROM igs_ad_ps_appl_inst a, igs_ad_appl b
WHERE a.person_id = cp_person_id
AND a.admission_appl_number = cp_admission_appl_number
AND a.nominated_course_cd = cp_nominated_course_cd
AND a.sequence_number=cp_sequence_number
AND a.person_id = b.person_id
AND a.admission_appl_number = b.admission_appl_number;
SELECT 'X'
FROM IGS_EN_SU_ATTEMPT_ALL
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd;
PROCEDURE update_per_stats (
p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE DEFAULT NULL,
p_acptd_or_reopnd_ind IN VARCHAR2 DEFAULT NULL
)
IS
--Query to check if the current admission calendar is associated with exactly 1 load calendar
CURSOR check_adm_load_rel
IS
SELECT count(*)
FROM
igs_ca_inst_rel car , -- calendar relations table
igs_ca_inst_all ca , -- calendar instances table
igs_ca_type cat, -- calendar types table
igs_ad_appl_all apl
WHERE
apl.person_id = p_person_id -- current person id
AND apl.admission_appl_number = p_admission_appl_number -- current adm appl number
AND car.sup_cal_type = apl.adm_cal_type
AND car.sup_ci_sequence_number = apl.adm_ci_sequence_number
AND ca.cal_type = car.sub_cal_type
AND ca.sequence_number = car.sub_ci_sequence_number
AND ca.cal_type = cat.cal_type
AND cat.s_cal_cat = 'LOAD' ;
SELECT ca.cal_type load_cal_type ,
ca.sequence_number load_cal_seq_num
FROM
igs_ca_inst_rel car , -- calendar relations table
igs_ca_inst_all ca , -- calendar instances table
igs_ca_type cat -- calendar types table
WHERE
ca.cal_type = car.sub_cal_type
AND ca.sequence_number = car.sub_ci_sequence_number
AND ca.cal_type = cat.cal_type
AND cat.s_cal_cat = 'LOAD'
AND car.sup_cal_type = cp_adm_cal_type
AND car.sup_ci_sequence_number = cp_adm_ci_sequence_number;
SELECT apl.adm_cal_type,
apl.adm_ci_sequence_number ,
ca.start_dt
FROM igs_ad_appl_all apl ,
igs_ad_ps_appl_inst_all inst,
igs_ca_inst_all ca
WHERE apl.person_id = P_PERSON_ID
AND apl.person_id = inst.person_id
AND apl.admission_appl_number = inst.admission_appl_number
AND igs_ad_gen_008.admp_get_saors(inst.adm_offer_resp_status) = 'ACCEPTED'
AND ca.cal_type =apl.adm_cal_type
AND ca.sequence_number = apl.adm_ci_sequence_number
ORDER BY ca.start_dt asc,inst.actual_response_dt asc;
SELECT
status
FROM
user_objects
WHERE
object_name = 'IGS_AD_UHK_PSTATS_PKG' AND
object_type = 'PACKAGE BODY';
SELECT psv.*
FROM igs_pe_stat_v psv
WHERE psv.person_id = p_person_id;
v_party_last_update_date hz_person_profiles.last_update_date%TYPE;
igs_pe_stat_pkg.update_row(
x_action => 'UPDATE',
x_rowid => cv_pe_stat.row_id,
x_person_id => cv_pe_stat.person_id,
x_ethnic_origin_id => cv_pe_stat.ethnic_origin_id,
x_marital_status => cv_pe_stat.marital_status,
x_marital_stat_effect_dt => cv_pe_stat.marital_status_effective_date,
x_ann_family_income => cv_pe_stat.ann_family_income,
x_number_in_family => cv_pe_stat.number_in_family,
x_content_source_type => cv_pe_stat.content_source_type,
x_internal_flag => cv_pe_stat.internal_flag,
x_person_number => cv_pe_stat.person_number,
x_effective_start_date => cv_pe_stat.effective_start_date,
x_effective_end_date => cv_pe_stat.effective_end_date,
x_ethnic_origin => cv_pe_stat.ethnic_origin,
x_religion => cv_pe_stat.religion,
x_next_to_kin => cv_pe_stat.next_to_kin,
x_next_to_kin_meaning => cv_pe_stat.next_to_kin_meaning,
x_place_of_birth => cv_pe_stat.place_of_birth,
x_socio_eco_status => cv_pe_stat.socio_eco_status,
x_socio_eco_status_desc => cv_pe_stat.socio_eco_status_desc,
x_further_education => cv_pe_stat.further_education,
x_further_education_desc => cv_pe_stat.further_education_desc,
x_in_state_tuition => cv_pe_stat.in_state_tuition,
x_tuition_st_date => cv_pe_stat.tuition_st_date,
x_tuition_end_date => cv_pe_stat.tuition_end_date,
x_person_initials => cv_pe_stat.person_initials,
x_primary_contact_id => cv_pe_stat.primary_contact_id,
x_personal_income => cv_pe_stat.personal_income,
x_head_of_household_flag => cv_pe_stat.head_of_household_flag,
x_content_source_number => cv_pe_stat.content_source_number,
x_hz_parties_ovn => cv_pe_stat.object_version_number,
x_attribute_category => cv_pe_stat.attribute_category,
x_attribute1 => cv_pe_stat.attribute1,
x_attribute2 => cv_pe_stat.attribute2,
x_attribute3 => cv_pe_stat.attribute3,
x_attribute4 => cv_pe_stat.attribute4,
x_attribute5 => cv_pe_stat.attribute5,
x_attribute6 => cv_pe_stat.attribute6,
x_attribute7 => cv_pe_stat.attribute7,
x_attribute8 => cv_pe_stat.attribute8,
x_attribute9 => cv_pe_stat.attribute9,
x_attribute10 => cv_pe_stat.attribute10,
x_attribute11 => cv_pe_stat.attribute11,
x_attribute12 => cv_pe_stat.attribute12,
x_attribute13 => cv_pe_stat.attribute13,
x_attribute14 => cv_pe_stat.attribute14,
x_attribute15 => cv_pe_stat.attribute15,
x_attribute16 => cv_pe_stat.attribute16,
x_attribute17 => cv_pe_stat.attribute17,
x_attribute18 => cv_pe_stat.attribute18,
x_attribute19 => cv_pe_stat.attribute19,
x_attribute20 => cv_pe_stat.attribute20,
x_global_attribute_category => cv_pe_stat.global_attribute_category,
x_global_attribute1 => cv_pe_stat.global_attribute1,
x_global_attribute2 => cv_pe_stat.global_attribute2,
x_global_attribute3 => cv_pe_stat.global_attribute3,
x_global_attribute4 => cv_pe_stat.global_attribute4,
x_global_attribute5 => cv_pe_stat.global_attribute5,
x_global_attribute6 => cv_pe_stat.global_attribute6,
x_global_attribute7 => cv_pe_stat.global_attribute7,
x_global_attribute8 => cv_pe_stat.global_attribute8,
x_global_attribute9 => cv_pe_stat.global_attribute9,
x_global_attribute10=> cv_pe_stat.global_attribute10,
x_global_attribute11 => cv_pe_stat.global_attribute11,
x_global_attribute12 => cv_pe_stat.global_attribute12,
x_global_attribute13 => cv_pe_stat.global_attribute13,
x_global_attribute14 => cv_pe_stat.global_attribute14,
x_global_attribute15 => cv_pe_stat.global_attribute15,
x_global_attribute16 => cv_pe_stat.global_attribute16,
x_global_attribute17 => cv_pe_stat.global_attribute17,
x_global_attribute18 => cv_pe_stat.global_attribute18,
x_global_attribute19 => cv_pe_stat.global_attribute19,
x_global_attribute20 => cv_pe_stat.global_attribute20,
x_party_last_update_date => v_party_last_update_date,
x_person_profile_id => lv_perosn_profile_id,
x_matr_cal_type => cv_pe_stat.matr_cal_type,
x_matr_sequence_number => cv_pe_stat.matr_sequence_number,
x_init_cal_type => cv_pe_stat.init_cal_type,
x_init_sequence_number => cv_pe_stat.init_sequence_number,
x_recent_cal_type => cv_pe_stat.recent_cal_type,
x_recent_sequence_number => cv_pe_stat.recent_sequence_number,
x_catalog_cal_type => cv_pe_stat.catalog_cal_type,
x_catalog_sequence_number => cv_pe_stat.catalog_sequence_number,
z_return_status => v_return_status,
z_msg_count => v_msg_count,
z_msg_data => v_msg_data
);
END update_per_stats;