[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT creation_date
FROM igs_sv_batches
WHERE batch_id = batch_no;
l_stmt := 'SELECT MAX(BATCH_ID) FROM ' || l_tbl_name || ' WHERE PERSON_ID = :1 AND BATCH_ID < :2 '
|| l_extra_critra;
l_stmt := 'SELECT MAX(BATCH_ID) FROM ' || l_tbl_name || ' WHERE PERSON_ID = :1 AND BATCH_ID < :2 ';
SELECT COUNT (*)
FROM igs_sv_btch_summary
WHERE batch_id = p_batch_id
AND person_id = p_person_id
AND ACTION_CODE IN (SELECT lookup_code
FROM igs_lookup_values
WHERE lookup_type = cp_mutually_info_typ)
AND action_code <> 'NEW' AND TAG_CODE = cp_information_type;
SELECT *
FROM igs_sv_batches
WHERE batch_id = cp_batch_id;
SELECT BATCH_TYPE
FROM igs_sv_batches
WHERE batch_id = cp_batch_id;
select
record_status
from igs_sv_persons
where person_id = cp_person_id and batch_id = cp_batch_id;
UPDATE igs_sv_btch_summary
SET adm_action_code = 'HOLD',
last_update_date = SYSDATE,
last_update_login = fnd_global.user_id
WHERE summary_id = p_summary_id;
FOR c_data_rec IN ( select SUMMARY_ID,
BATCH_ID,
PERSON_ID,
ACTION_CODE ,
TAG_CODE,
ADM_ACTION_CODE ,
OWNER_TABLE_NAME ,
OWNER_TABLE_IDENTIFIER
from igs_sv_btch_summary where batch_id = p_batch_id and person_id = p_person_id )
LOOP
-- my_dbg('Inside IF TAG_CODE : ',c_data_rec.TAG_CODE);
UPDATE igs_sv_btch_summary
SET batch_id = l_batch_id,
adm_action_code = c_data_rec.ADM_ACTION_CODE,
last_update_date = SYSDATE,
last_update_login = fnd_global.user_id
WHERE summary_id = c_data_rec.summary_id;
l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id'|| l_extra_criteria;
l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id';
UPDATE igs_sv_btch_summary
SET batch_id = l_batch_id,
adm_action_code = 'SEND',
last_update_date = SYSDATE,
last_update_login = fnd_global.user_id
WHERE summary_id = p_summary_id;
l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id'
|| l_extra_criteria;
l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id';
SELECT *
FROM igs_sv_batches
WHERE batch_id = cp_batch_id;
SELECT *
FROM igs_sv_persons
WHERE batch_id = cp_batch_id AND person_id = cp_person_id;
SELECT COUNT (*)
FROM igs_sv_btch_summary
WHERE batch_id = cp_batch_id AND person_id = cp_person_id;
SELECT igs_sv_batches_id_s.NEXTVAL INTO l_batch_id from dual;
INSERT INTO igs_sv_batches
(batch_id, schema_version,
sevis_user_id, sevis_school_id,
batch_status, batch_type, creation_date,
created_by, last_updated_by,
last_update_date, last_update_login,
sevis_error_code,
xml_gen_date,
inbound_process_date,
sevis_school_org_id,
sevis_user_person_id)
VALUES (l_batch_id, l_batch_rec.schema_version,
l_batch_rec.sevis_user_id, l_batch_rec.sevis_school_id,
'S', l_batch_rec.batch_type, SYSDATE,
l_batch_rec.created_by, l_batch_rec.last_updated_by,
SYSDATE, l_batch_rec.last_update_login,
l_batch_rec.sevis_error_code,
l_batch_rec.xml_gen_date,
l_batch_rec.inbound_process_date,
l_batch_rec.sevis_school_org_id,
l_batch_rec.sevis_user_person_id);
UPDATE igs_sv_persons
SET batch_id = l_batch_id
WHERE batch_id = p_batch_id AND person_id = p_person_id;
INSERT INTO igs_sv_persons
(batch_id, person_id,
record_number, form_id,
print_form, pdso_sevis_id,
record_status,
person_number,
sevis_user_id,
issuing_reason,
curr_session_end_date,
next_session_start_date,
other_reason,
transfer_from_school,
ev_create_reason,
ev_form_number, creation_date,
created_by, last_updated_by, last_update_date,
last_update_login,
init_prgm_start_date,
sevis_error_code,
sevis_error_element,
no_show_flag, status_code,
last_session_flag,
adjudicated_flag,
REPRINT_RSN_CODE ,
reprint_remarks, remarks,
pdso_sevis_person_id)
VALUES (l_batch_id, l_person_rec.person_id,
l_person_rec.record_number, l_person_rec.form_id,
l_person_rec.print_form, l_person_rec.pdso_sevis_id,
l_person_rec.record_status,
l_person_rec.person_number,
l_person_rec.sevis_user_id,
l_person_rec.issuing_reason,
l_person_rec.curr_session_end_date,
l_person_rec.next_session_start_date,
l_person_rec.other_reason,
l_person_rec.transfer_from_school,
l_person_rec.ev_create_reason,
l_person_rec.ev_form_number, SYSDATE,
fnd_global.user_id, fnd_global.user_id, SYSDATE,
l_person_rec.last_update_login,
l_person_rec.init_prgm_start_date,
l_person_rec.sevis_error_code,
l_person_rec.sevis_error_element,
l_person_rec.no_show_flag, l_person_rec.status_code,
l_person_rec.last_session_flag,
l_person_rec.adjudicated_flag,
l_person_rec.REPRINT_RSN_CODE ,
l_person_rec.reprint_remarks, l_person_rec.remarks,
l_person_rec.pdso_sevis_person_id);
SELECT *
FROM igs_sv_persons
WHERE batch_id = cp_batch_id AND person_id = cp_person_id;
INSERT INTO igs_sv_persons
(batch_id, person_id,
record_number, form_id,
print_form, pdso_sevis_id,
record_status,
person_number,
sevis_user_id,
issuing_reason,
curr_session_end_date,
next_session_start_date,
other_reason,
transfer_from_school,
ev_create_reason,
ev_form_number, creation_date,
created_by, last_updated_by, last_update_date,
last_update_login,
init_prgm_start_date,
sevis_error_code,
sevis_error_element,
no_show_flag, status_code,
last_session_flag,
adjudicated_flag,
REPRINT_RSN_CODE ,
reprint_remarks, remarks)
VALUES (p_new_batch_id, l_person_rec.person_id,
l_person_rec.record_number, l_person_rec.form_id,
l_person_rec.print_form, l_person_rec.pdso_sevis_id,
l_person_rec.record_status,
l_person_rec.person_number,
l_person_rec.sevis_user_id,
l_person_rec.issuing_reason,
l_person_rec.curr_session_end_date,
l_person_rec.next_session_start_date,
l_person_rec.other_reason,
l_person_rec.transfer_from_school,
l_person_rec.ev_create_reason,
l_person_rec.ev_form_number, SYSDATE,
fnd_global.user_id, fnd_global.user_id, SYSDATE,
l_person_rec.last_update_login,
l_person_rec.init_prgm_start_date,
l_person_rec.sevis_error_code,
l_person_rec.sevis_error_element,
l_person_rec.no_show_flag, l_person_rec.status_code,
l_person_rec.last_session_flag,
l_person_rec.adjudicated_flag,
l_person_rec.REPRINT_RSN_CODE ,
l_person_rec.reprint_remarks, l_person_rec.remarks);
SELECT MAX(prgm.BATCH_ID) , btch.creation_date
FROM igs_sv_prgms_info prgm, IGS_SV_BATCHES btch
WHERE prgm.batch_id = btch.batch_id AND
prgm.person_id = p_person_id AND
prgm.sevis_auth_id = p_sevis_auth_id AND
prgm.prgm_action_type = 'DB' AND
btch.batch_status <> 'E'
GROUP BY btch.creation_date;
SELECT prgm_start_date, prgm_end_date
FROM igs_pe_nonimg_form ipnf
WHERE ipnf.person_id = p_person_id AND
ipnf.nonimg_form_id = cp_form_id;
SELECT prgm_start_date prgm_start_date,
prgm_end_date prgm_end_date
FROM igs_pe_nonimg_stat
WHERE nonimg_form_id = cp_form_id
ORDER BY last_update_date DESC;
SELECT nonimg_form_id
FROM igs_pe_nonimg_form
WHERE person_id = p_person_id AND
form_status = 'A';