[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM igs_ad_apphist_int
WHERE interface_run_id = p_interface_run_id
AND status = '2'
ORDER BY person_id,admission_appl_number;
SELECT
acad_cal_type,
acad_ci_sequence_number,
adm_cal_type,
adm_ci_sequence_number,
admission_cat,
s_admission_process_type
FROM
igs_ad_appl
WHERE
person_id = cp_person_id AND
admission_appl_number = cp_adm_appl_number;
UPDATE igs_ad_apphist_int in_rec
SET error_code = cst_ec_val_E686
,error_text = cst_et_val_E686
, status ='3'
WHERE STATUS = '2'
AND interface_run_id = p_interface_run_id
AND EXISTS ( SELECT 1
FROM igs_ad_appl_hist
WHERE person_id = in_rec.person_id
AND admission_appl_number = in_rec.admission_appl_number);
UPDATE igs_ad_apphist_int in_rec
SET error_code = cst_ec_val_E709
,error_text = cst_et_val_E709
, status ='3'
WHERE STATUS = '2'
AND interface_run_id = p_interface_run_id
AND NOT EXISTS ( SELECT 1
FROM igs_ad_appl
WHERE person_id = in_rec.person_id
AND admission_appl_number = in_rec.admission_appl_number);
igs_ad_appl_hist_pkg.insert_row (
x_rowid => l_rowid,
x_org_id => NULL,
x_person_id => appl_hist_rec.person_id,
x_admission_appl_number => appl_hist_rec.admission_appl_number,
x_hist_start_dt => appl_hist_rec.hist_start_dt,
x_hist_end_dt => appl_hist_rec.hist_end_dt,
x_hist_who => fnd_global.user_id,
x_appl_dt => TRUNC(appl_hist_rec.appl_dt),
x_acad_cal_type => appl_dtls_rec.acad_cal_type,
x_acad_ci_sequence_number => appl_dtls_rec.acad_ci_sequence_number,
x_adm_cal_type => appl_dtls_rec.adm_cal_type,
x_adm_ci_sequence_number => appl_dtls_rec.adm_ci_sequence_number,
x_admission_cat => appl_dtls_rec.admission_cat,
x_s_admission_process_type => appl_dtls_rec.s_admission_process_type,
x_adm_appl_status => appl_hist_rec.adm_appl_status,
x_adm_fee_status => appl_hist_rec.adm_fee_status,
x_tac_appl_ind => appl_hist_rec.tac_appl_ind,
x_mode => 'R');
UPDATE igs_ad_apphist_int
SET status =cst_s_val_1
WHERE interface_apphist_id = appl_hist_rec.interface_apphist_id;
UPDATE igs_ad_apphist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE interface_apphist_id = appl_hist_rec.interface_apphist_id;
UPDATE igs_ad_apphist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE person_id = appl_hist_rec.person_id
AND status =cst_s_val_2
AND admission_appl_number = appl_hist_rec.admission_appl_number
AND interface_apphist_id <> appl_hist_rec.interface_apphist_id;
UPDATE igs_ad_apphist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE interface_apphist_id = appl_hist_rec.interface_apphist_id;
UPDATE igs_ad_apphist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE person_id = appl_hist_rec.person_id
AND status =cst_s_val_2
AND admission_appl_number = appl_hist_rec.admission_appl_number
AND interface_apphist_id <> appl_hist_rec.interface_apphist_id;
SELECT
'X'
FROM igs_ad_ps_aplinsthst
WHERE person_id = cp_person_id
AND admission_appl_number = cp_adm_appl_number
AND nominated_course_cd = cp_nominated_course_cd
AND sequence_number = cp_sequence_number
AND hist_start_dt = cp_hist_start_dt;
SELECT
apl.acad_cal_type,
apl.acad_ci_sequence_number,
aplinst.adm_cal_type,
aplinst.adm_ci_sequence_number,
apl.admission_cat,
apl.s_admission_process_type,
aplinst.course_cd,
aplinst.crv_version_number,
aplinst.late_adm_fee_status,
aplinst.correspondence_cat
FROM
igs_ad_appl apl,
igs_ad_ps_appl_inst aplinst
WHERE
apl.person_id = aplinst.person_id
AND apl.admission_appl_number = aplinst.admission_appl_number
AND apl.person_id = cp_person_id
AND apl.admission_appl_number = cp_adm_appl_number
AND aplinst.nominated_course_cd = cp_nominated_course_cd
AND aplinst.sequence_number = cp_sequence_number;
SELECT *
FROM igs_ad_insthist_int
WHERE interface_run_id = p_interface_run_id
AND status = '2'
ORDER BY person_id,admission_appl_number,nominated_course_cd,sequence_number;
SELECT
'X'
FROM
hz_parties
WHERE
party_id = p_applinst_hist_rec.adm_otcm_stat_auth_per_number;
UPDATE igs_ad_insthist_int in_rec
SET error_code = cst_ec_val_E689
, status ='3'
, error_text = cst_et_val_E689
WHERE STATUS = '2'
AND interface_run_id = p_interface_run_id
AND EXISTS ( SELECT 1 FROM igs_ad_ps_aplinsthst
WHERE person_id = in_rec.person_id
AND admission_appl_number = in_rec.admission_appl_number
AND nominated_course_cd = in_rec.nominated_course_cd
AND sequence_number = in_rec.sequence_number );
UPDATE igs_ad_insthist_int in_rec
SET error_code = cst_ec_val_E710
, status ='3'
, error_text = cst_et_val_E710
WHERE STATUS = '2'
AND interface_run_id = p_interface_run_id
AND NOT EXISTS ( SELECT 1
FROM igs_ad_ps_appl_inst
WHERE person_id = in_rec.person_id
AND admission_appl_number = in_rec.admission_appl_number
AND nominated_course_cd = in_rec.nominated_course_cd
AND sequence_number = in_rec.sequence_number );
igs_ad_ps_aplinsthst_pkg.insert_row(
x_rowid => l_rowid,
x_org_id => null ,
x_person_id => applinst_hist_rec.person_id,
x_admission_appl_number => applinst_hist_rec.admission_appl_number,
x_nominated_course_cd => applinst_hist_rec.nominated_course_cd,
x_sequence_number => applinst_hist_rec.sequence_number,
x_hist_start_dt => applinst_hist_rec.hist_start_dt,
x_applicant_acptnce_cndtn => applinst_hist_rec.applicant_acptnce_cndtn,
x_cndtnl_offer_cndtn => applinst_hist_rec.cndtnl_offer_cndtn,
x_hist_end_dt => applinst_hist_rec.hist_end_dt,
x_hist_who => fnd_global.user_id,
x_hist_offer_round_number => null,
x_adm_cal_type => applinst_dtls_rec.adm_cal_type,
x_adm_ci_sequence_number => applinst_dtls_rec.adm_ci_sequence_number,
x_course_cd => applinst_dtls_rec.course_cd,
x_crv_version_number => applinst_dtls_rec.crv_version_number,
x_location_cd => applinst_hist_rec.location_cd,
x_attendance_mode => applinst_hist_rec.attendance_mode,
x_attendance_type => applinst_hist_rec.attendance_type,
x_unit_set_cd => applinst_hist_rec.unit_set_cd,
x_us_version_number => applinst_hist_rec.us_version_number,
x_preference_number => applinst_hist_rec.preference_number,
x_adm_doc_status => applinst_hist_rec.adm_doc_status,
x_adm_entry_qual_status => applinst_hist_rec.adm_entry_qual_status ,
x_late_adm_fee_status => applinst_dtls_rec.late_adm_fee_status,
x_adm_outcome_status => applinst_hist_rec.adm_outcome_status ,
x_adm_otcm_status_auth_per_id => applinst_hist_rec.adm_otcm_stat_auth_per_number,
x_adm_outcome_status_auth_dt => TRUNC(applinst_hist_rec.adm_outcome_status_auth_dt),
x_adm_outcome_status_reason => applinst_hist_rec.adm_outcome_status_reason ,
x_offer_dt => TRUNC(applinst_hist_rec.offer_dt) ,
x_offer_response_dt => TRUNC(applinst_hist_rec.offer_resp_date) ,
x_prpsd_commencement_dt => TRUNC(applinst_hist_rec.prpsd_commencement_dt) ,
x_adm_cndtnl_offer_status => applinst_hist_rec.adm_cndtnl_offer_status ,
x_cndtnl_offer_satisfied_dt => TRUNC(applinst_hist_rec.cndtnl_offer_satisfied_dt) ,
x_cndtnl_ofr_must_be_stsfd_ind => applinst_hist_rec.cndtnl_offer_must_be_stsfd_ind,
x_adm_offer_resp_status => applinst_hist_rec.adm_offer_resp_status ,
x_actual_response_dt => TRUNC(applinst_hist_rec.actual_response_dt) ,
x_adm_offer_dfrmnt_status => applinst_hist_rec.adm_offer_dfrmnt_status ,
x_deferred_adm_cal_type => NULL ,
x_deferred_adm_ci_sequence_num => NULL,
x_deferred_tracking_id => applinst_hist_rec.deferred_tracking_id ,
x_ass_rank => applinst_hist_rec.ass_rank,
x_secondary_ass_rank => applinst_hist_rec.secondary_ass_rank,
x_intrntnl_accept_advice_num => applinst_hist_rec.intrntnl_acceptance_advice_num,
x_ass_tracking_id => applinst_hist_rec.ass_tracking_id,
x_fee_cat => applinst_hist_rec.fee_cat,
x_hecs_payment_option => applinst_hist_rec.hecs_payment_option,
x_expected_completion_yr => applinst_hist_rec.expected_completion_yr,
x_expected_completion_perd => applinst_hist_rec.expected_completion_perd,
x_correspondence_cat => applinst_dtls_rec.correspondence_cat,
x_enrolment_cat => applinst_hist_rec.enrolment_cat,
x_funding_source => applinst_hist_rec.funding_source,
x_mode => 'R');
UPDATE igs_ad_insthist_int
SET status =cst_s_val_1
WHERE interface_insthist_id = applinst_hist_rec.interface_insthist_id;
UPDATE igs_ad_insthist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE interface_insthist_id = applinst_hist_rec.interface_insthist_id;
UPDATE igs_ad_insthist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE person_id = applinst_hist_rec.person_id
AND status =cst_s_val_2
AND admission_appl_number = applinst_hist_rec.admission_appl_number
AND nominated_course_cd = applinst_hist_rec.nominated_course_cd
AND sequence_number = applinst_hist_rec.sequence_number
AND interface_insthist_id <> applinst_hist_rec.interface_insthist_id;
UPDATE igs_ad_insthist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE interface_insthist_id = applinst_hist_rec.interface_insthist_id;
UPDATE igs_ad_insthist_int
SET status = cst_s_val_3
, error_code = l_error_code
, error_text = l_error_text
WHERE person_id = applinst_hist_rec.person_id
AND status =cst_s_val_2
AND admission_appl_number = applinst_hist_rec.admission_appl_number
AND nominated_course_cd = applinst_hist_rec.nominated_course_cd
AND sequence_number = applinst_hist_rec.sequence_number
AND interface_insthist_id <> applinst_hist_rec.interface_insthist_id;