[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE pt.person_type_code = pti.person_type_code
AND system_type IN ('NONIMG_STUDENT')
AND person_id = p_person_id
AND NVL(end_date, SYSDATE) >= SYSDATE;
SELECT 'X' FROM igs_en_svs_auth esa
WHERE person_id = cp_person_id
AND NVL(esa.end_dt, SYSDATE) >= SYSDATE
AND exists (select 'x'
from igs_en_svs_auth_cal sac
where sac.sevis_auth_id = esa.sevis_auth_id
and cal_type = cp_cal_type
and ci_sequence_number = cp_ci_sequence_number);
SELECT person_id, SEVIS_AUTHORIZATION_CODE
FROM igs_en_svs_auth auth,
igs_en_svs_auth_cal sac
WHERE auth.sevis_auth_id = sac.sevis_auth_id
AND sac.cal_type = l_cal_type
AND sac.ci_sequence_number = l_sequence_number;
SELECT course_cd
FROM igs_en_stdnt_ps_att
WHERE person_id = l_person_id;
SELECT party_number, person_first_name||' '||person_middle_name||' '||person_last_name full_name
FROM hz_parties
WHERE party_id = l_person_id;
SELECT alternate_code, start_dt, end_dt
FROM igs_ca_inst_all
WHERE cal_type = l_cal_type
AND sequence_number = l_sequence_number;
SELECT s_cal_cat
FROM igs_ca_type
WHERE cal_type = l_cal_type;
SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = 'IGS_EN_SEVIS_AUTH_CODES'
AND lookup_code = l_lookup_code;
SELECT api_person_id
FROM igs_pe_person_id_type_v
WHERE pe_person_id = l_person_id;
SELECT load_cal_type,load_ci_sequence_number
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE teach_cal_type = p_cal_type AND
teach_ci_sequence_number = p_sequence_number AND
load_end_dt >= TRUNC(SYSDATE)
ORDER BY load_start_dt asc;
SELECT attendance_type FROM igs_en_atd_type_load
WHERE cal_type = l_cal_type
AND lower_enr_load_range = (SELECT MAX(lower_enr_load_range)
FROM igs_en_atd_type_load
WHERE cal_type = l_cal_type);
SELECT 'X'
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE pt.person_type_code = pti.person_type_code
AND pt.system_type = 'NONIMG_STUDENT'
AND pti.person_id = p_person_id
AND NVL(pti.end_date, SYSDATE) >= SYSDATE;
SELECT gn.note_text,
pn.start_date,
pn.end_date,
pn.pe_note_type
FROM igs_pe_pers_note pn,
igs_ge_note gn,
igs_ca_inst ci
WHERE ci.cal_type = p_cal_type
AND ci.sequence_number = p_seq_num
AND pn.person_id = p_person_id
AND pn.pe_note_type = 'RET_FULL_LOAD'
AND pn.reference_number = gn.reference_number
AND ci.start_dt <= pn.start_date
AND NVL (pn.end_date, ci.end_dt) <= ci.end_dt
ORDER BY pn.start_date DESC;
SELECT attendance_type
FROM igs_en_atd_type_load
WHERE cal_type = p_cal_type
AND lower_enr_load_range = ( SELECT MAX(lower_enr_load_range)
FROM igs_en_atd_type_load
WHERE cal_type = p_cal_type );
SELECT visa_type
FROM igs_pe_nonimg_form
WHERE person_id = p_person_id
AND form_status = 'A';
SELECT visa_type
FROM igs_pe_visa_v
WHERE person_id = p_person_id
AND visa_issue_date <= sysdate
AND sysdate <= visa_expiry_date ;
SELECT add_months(TRUNC(p_start_date ),cp_no_of_months) final_dt
FROM DUAL;
PROCEDURE insert_auth_cal_rec(
p_sevis_auth_id IN NUMBER,
p_cal_type IN VARCHAR2,
p_ci_sequence_number IN NUMBER) AS
CURSOR c_auth_cal_exists IS
SELECT 'X'
FROM IGS_EN_SVS_AUTH_CAL
WHERE SEVIS_AUTH_ID = p_sevis_auth_id
AND cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number;
igs_en_svs_auth_cal_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
x_sevis_auth_id => p_sevis_auth_id,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number);
END insert_auth_cal_rec;
PROCEDURE insert_authorization_rec(
p_sevis_authorization_code IN VARCHAR2,
p_start_dt IN DATE,
p_end_dt IN DATE,
p_comments IN VARCHAR2,
p_sevis_auth_id IN OUT NOCOPY NUMBER,
p_sevis_authorization_no IN OUT NOCOPY NUMBER,
p_person_id IN NUMBER,
p_cancel_flag IN VARCHAR2) AS
lv_rowid VARCHAR2(25) ;
SELECT sevis_auth_id
FROM igs_en_svs_auth
WHERE person_id = p_person_id
AND sevis_authorization_code = p_sevis_authorization_code
AND start_dt = p_start_dt
AND NVL(cancel_flag,'N') = 'N';
igs_en_svs_auth_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
x_sevis_authorization_code => p_sevis_authorization_code,
x_start_dt => p_start_dt,
x_end_dt => p_end_dt,
x_comments => p_comments,
x_sevis_auth_id => p_sevis_auth_id,
x_sevis_authorization_no => l_sevis_authorization_no,
x_person_id => p_person_id,
x_cancel_flag => p_cancel_flag);
END insert_authorization_rec;
insert_authorization_rec(
p_sevis_authorization_code ,
p_start_dt ,
p_end_dt ,
p_comments ,
p_sevis_auth_id ,
p_sevis_authorization_no ,
p_person_id ,
p_cancel_flag);
insert_auth_cal_rec(p_sevis_auth_id, p_cal_type, p_ci_sequence_number);
insert_auth_cal_rec(p_sevis_auth_id, p_cal_type, p_ci_sequence_number);
SELECT start_dt, end_dt
FROM IGS_CA_INST
WHERE cal_type = p_cal_type
AND sequence_number = p_ci_sequence_number;
SELECT 'x'
FROM igs_en_svs_auth F,
igs_en_svs_auth S
WHERE f.person_id =cp_person_id
AND s.person_id =cp_person_id
AND f.ROWID <> s.ROWID
AND f.end_dt BETWEEN s.start_dt AND s.end_dt;