[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_block_mode CONSTANT NUMBER(1) := 1; --Insert mode 0:1. If 1 - inserts only changed optional info in the block. , if 0 - all parts of the block
g_update_login NUMBER(15);
g_update_by NUMBER(15);
g_update_count NUMBER(15) := 0; -- prbhardw CP enhancement
g_running_update_batch NUMBER(15); -- prbhardw CP enhancement
PROCEDURE Insert_Summary_Info(
p_batch_id IN igs_sv_btch_summary.batch_id%TYPE,
p_person_id IN igs_sv_btch_summary.person_id%TYPE,
p_action_code IN igs_sv_btch_summary.action_code%TYPE,
p_tag_code IN igs_sv_btch_summary.tag_code%TYPE,
p_adm_action IN igs_sv_btch_summary.adm_action_code%TYPE,
p_owner_table_name IN igs_sv_btch_summary.owner_table_name%TYPE,
p_owner_table_id IN igs_sv_btch_summary.OWNER_TABLE_IDENTIFIER%TYPE
);
SELECT party_id,
party_site_id
FROM ecx_tp_headers
WHERE tp_header_id IN
( SELECT tp_header_id
FROM ecx_tp_details
WHERE ext_process_id IN
( SELECT ext_process_id
FROM ecx_ext_processes
WHERE direction = 'OUT'
AND transaction_id IN
( SELECT transaction_id
FROM ecx_transactions
WHERE transaction_type=l_trans_type
AND transaction_subtype =l_trans_subtype
)
)
);
SELECT sevis_user_id
FROM igs_sv_batches
WHERE batch_id = p_batch_id;
SELECT FND_GLOBAL.CONC_REQUEST_ID INTO l_con_req_id FROM DUAL;
l_parameter_list.DELETE;
Modified the query to select the Active local institution. Added closed_ind = 'N'
while selecting the ORG_ALTERNATE_ID_TYPE
9-DEC-2003 Bug No: 2908378 (Used the profile for local Institution)
------------------------------------------------------------------------
FUNCTION Get_School_Sevis_Id (
p_batch_type IN VARCHAR2
) RETURN VARCHAR2
IS
CURSOR c_alt_id (cp_local_inst hz_parties.party_number%TYPE)
IS
SELECT org_alternate_id
FROM igs_or_org_alt_ids
WHERE org_structure_id = cp_local_inst
AND sysdate BETWEEN NVL(start_date,sysdate-1) and NVL(end_date,sysdate+1)
AND ( ( org_alternate_id_type =
( SELECT org_alternate_id_type
FROM igs_or_org_alt_idtyp
WHERE system_id_type = g_school_sevis_id AND
close_ind = 'N' AND
inst_flag = 'Y'
) AND p_batch_type ='I')
OR ( org_alternate_id_type =
( SELECT org_alternate_id_type
FROM igs_or_org_alt_idtyp
WHERE system_id_type = g_sch_p_sevis_id AND
close_ind = 'N' AND
inst_flag = 'Y'
)
AND p_batch_type ='E')
);
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_code = p_code
AND view_application_id = 8405
AND enabled_flag='Y'
AND language = USERENV('LANG')
AND lookup_type = p_type
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE-1) AND NVL(end_date_active, SYSDATE + 1);
SELECT SUBSTR(meaning,5,2)
FROM fnd_lookup_values
WHERE lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
AND view_application_id = 3
AND lookup_code=p_code
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE-1) AND NVL(end_date_active, SYSDATE + 1);
SELECT lv.lookup_code
FROM fnd_lookup_values lv
WHERE lv.lookup_type = 'SV_MAP_HR_VISA_TYPES'
AND lv.meaning = p_visa_meaning
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE-1) AND NVL(end_date_active, SYSDATE + 1);
SELECT tag
FROM fnd_lookup_values
WHERE lookup_code = p_code
AND view_application_id = 8405
AND enabled_flag = 'Y'
AND language = USERENV('LANG')
AND lookup_type = p_type
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE-1) AND NVL(end_date_active, SYSDATE + 1);
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE pe_person_id = p_person_id
AND person_id_type
IN (SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = g_person_sevis_id)
AND start_dt <= trunc(sysdate)
AND NVL(end_dt,sysdate+1) >= trunc(sysdate);
SELECT tag
FROM fnd_lookup_values
WHERE lookup_code = p_code
AND view_application_id = 8405
AND enabled_flag = 'Y'
AND language = USERENV('LANG')
AND lookup_type = 'SV_US_TERRITORY_CODES'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE-1) AND NVL(end_date_active, SYSDATE + 1);
SELECT issue_reason ,
curr_session_end_date ,
next_session_start_date ,
other_reason,
transfer_from_school,
prgm_start_date,
last_session_flag,
adjudicated_flag
FROM igs_pe_nonimg_form
WHERE nonimg_form_id = p_person_rec.form_id ;
SELECT create_reason ,
prgm_start_date ,
prgm_end_date ,
ev_form_number ,
init_prgm_start_date ,
no_show_flag
FROM igs_pe_ev_form
WHERE ev_form_id = p_person_rec.form_id ;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT hzpp.person_last_name,
hzpp.person_middle_name,
hzpp.person_first_name,
hzpp.person_name_suffix,
hzpp.date_of_birth,
hzpp.gender,
prt.birth_country,
hzc.country_code,
prt.birth_city
FROM hz_person_profiles hzpp,
hz_citizenship hzc,
igs_pe_hz_parties prt
WHERE hzpp.party_id = p_person_rec.person_id
AND prt.party_id = hzpp.party_id
AND hzc.party_id (+) = hzpp.party_id
AND sysdate between hzpp.effective_start_date AND NVL(hzpp.effective_end_date,sysdate+1);
SELECT peva.visa_type
FROM igs_pe_visa peva
WHERE peva.person_id = p_person_rec.person_id
AND SYSDATE BETWEEN peva.visa_issue_date AND peva.visa_expiry_date;
SELECT decode(commuter_ind,'Y','1','0') commuter
FROM igs_pe_nonimg_form
WHERE nonimg_form_id = p_person_rec.form_id;
SELECT perm_res_cntry
FROM igs_pe_eit_perm_res_v
WHERE person_id = p_person_rec.person_id;
SELECT pevf.position_code,
pevf.category_code,
pevf.position_remarks
FROM igs_pe_ev_form pevf
WHERE pevf.person_id = p_person_rec.person_id
AND ev_form_id = p_person_rec.form_id;
SELECT psd.birth_cntry_resn_code
FROM igs_pe_stat_details psd
WHERE psd.person_id = cp_person_id;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT palt.api_person_id_uf,
palt.region_cd
FROM igs_pe_alt_pers_id palt,
igs_pe_person_id_typ typv
WHERE typv.s_person_id_type = cp_system_person_id_type
AND palt.person_id_type = typv.person_id_type
AND palt.pe_person_id = p_person_rec.person_id
AND SYSDATE BETWEEN palt.start_dt AND NVL(palt.end_dt,SYSDATE);
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT adr.party_site_id,
addr_line_1,
addr_line_2,
addr_line_3,
addr_line_4,
city,
state,
province,
country_cd,
postal_code
FROM igs_pe_addr_v adr,
igs_pe_partysiteuse_v usg
WHERE person_id = p_person_rec.person_id
AND ( adr.status = 'A' AND SYSDATE BETWEEN NVL(start_dt,SYSDATE) AND NVL(end_dt, SYSDATE + 1) )
AND usg.party_site_id = adr.party_site_id
AND usg.site_use_type = g_f_addr_usage
AND usg.active = 'A';
p_data_rec(l_counter).created_by := g_update_by;
p_data_rec(l_counter).last_updated_by := g_update_by;
p_data_rec(l_counter).last_update_date := sysdate;
p_data_rec(l_counter).last_update_login := g_update_login;
gmaheswa 12-Nov-2003 Modified c_addr cursor to select active address records,
as part of address related changes .
------------------------------------------------------------------------
******************************************************************/
FUNCTION Validate_Us_Addr_Info (
p_person_rec IN t_student_rec,
p_data_rec IN OUT NOCOPY g_address_rec_type , -- Data record
p_records IN NUMBER -- number of addressees found
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR(30) := 'Validate_Us_Addr_Info';
SELECT adr.party_site_id,
addr_line_1,
addr_line_2,
addr_line_3,
addr_line_4,
city,
state,
province,
country_cd,
postal_code,
adr.identifying_address_flag
FROM igs_pe_addr_v adr,
igs_pe_partysiteuse_v usg
WHERE person_id = p_person_rec.person_id
AND ( adr.status = 'A' AND SYSDATE BETWEEN NVL(start_dt,SYSDATE) AND NVL(end_dt, SYSDATE + 1) )
AND usg.party_site_id = adr.party_site_id
AND usg.site_use_type = g_us_addr_usage
AND usg.active = 'A';
p_data_rec(l_counter).created_by := g_update_by;
p_data_rec(l_counter).last_updated_by := g_update_by;
p_data_rec(l_counter).last_update_date := sysdate;
p_data_rec(l_counter).last_update_login := g_update_login;
Selected LOCATION_ID instead of PARTY_SITE_ID for these cases.
Added p_data_rec(l_counter).party_site_id := c_addr_rec.party_site_id
gmaheswa 12-Nov-2003 Modified c_addr cursor to select active address records,
as part of address related changes .
pkpatel 4-Dec-2003 Bug 3227107 (Used the status column for address)
vskumar 26-May-06 xbuild3 performance fix. deleted the body of the function as no longer used any where.
------------------------------------------------------------------------
******************************************************************/
FUNCTION Validate_Site_Info (
p_person_rec IN t_student_rec,
p_data_rec IN OUT NOCOPY g_address_rec_type , -- Data record
p_records OUT NOCOPY NUMBER -- number of addressees found
) RETURN VARCHAR2
IS
BEGIN
RETURN NULL;
SELECT penf.education_level,
penf.primary_major,
penf.secondary_major,
penf.minor,
penf.length_of_study,
penf.prgm_start_date,
penf.prgm_end_date,
decode(penf.english_reqd,'Y','1','0') english_reqd,
decode(penf.english_reqd_met,'Y','1','0') english_reqd_met,
penf.not_reqd_reason,
penf.educ_lvl_remarks
FROM igs_pe_nonimg_form penf
WHERE penf.person_id = p_person_rec.person_id
AND penf.nonimg_form_id = p_person_rec.form_id;
SELECT peev.position_code,
peev.subject_field_code,
peev.subject_field_remarks,
prgm_start_date,
prgm_end_date
FROM igs_pe_ev_form peev
WHERE peev.person_id = p_person_rec.person_id
AND peev.ev_form_id = p_person_rec.form_id;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT penf.education_level,
penf.primary_major,
penf.secondary_major,
penf.minor,
penf.length_of_study,
penf.prgm_start_date,
penf.prgm_end_date,
decode(penf.english_reqd,'Y','1','0') english_reqd,
decode(penf.english_reqd_met,'Y','1','0') english_reqd_met,
penf.not_reqd_reason,
penf.educ_lvl_remarks
FROM igs_pe_nonimg_form penf
WHERE penf.person_id = p_data_rec.person_id
AND penf.nonimg_form_id = g_nonimg_form_id;
SELECT peev.position_code,
peev.subject_field_code,
peev.subject_field_remarks,
prgm_start_date,
prgm_end_date
FROM igs_pe_ev_form peev
WHERE peev.person_id = p_data_rec.person_id
AND peev.ev_form_id = g_nonimg_form_id;
SELECT nonimg_stat_id,
nonimg_form_id,
to_char(action_date,'YYYY-MM-DD') action_date,
action_type,
to_char(prgm_start_date,'YYYY-MM-DD') prgm_start_date,
to_char(prgm_end_date,'YYYY-MM-DD') prgm_end_date,
remarks,
termination_reason,
print_flag, --prbhardw
cancel_flag
FROM igs_pe_nonimg_stat
WHERE nonimg_form_id = p_person_rec.form_id
AND nonimg_stat_id NOT IN
( SELECT NVL(form_status_id,0)
FROM igs_sv_prgms_info prg,
igs_sv_persons pr
WHERE prg.person_id = pr.person_id
AND prg.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prg.person_id = p_person_rec.person_id
)
ORDER BY action_date;
SELECT ev_form_stat_id ,
ev_form_id ,
to_char(action_date,'YYYY-MM-DD') action_date,
action_type ,
to_char(prgm_start_date,'YYYY-MM-DD') prgm_start_date,
to_char(prgm_end_date,'YYYY-MM-DD') prgm_end_date,
remarks ,
termination_reason ,
end_program_reason
FROM igs_pe_ev_form_stat
WHERE ev_form_id = p_person_rec.form_id
AND ev_form_stat_id NOT IN
( SELECT NVL(form_status_id,0)
FROM igs_sv_prgms_info prg,
igs_sv_persons pr
WHERE prg.person_id = pr.person_id
AND pr.record_status <> 'E'
AND prg.batch_id = pr.batch_id
AND prg.person_id = p_person_rec.person_id)
ORDER BY action_date;
SELECT category_code,
prgm_start_date,
prgm_end_date
FROM igs_pe_ev_form
WHERE ev_form_id = p_person_rec.form_id;
SELECT prgms.authorization_reason,
prgms.prgm_start_date,
prgms.prgm_end_date,
prgms.remarks,
prgms.auth_action_code
FROM igs_sv_prgms_info prgms,
igs_sv_persons pr
WHERE prgms.person_id = pr.person_id
AND pr.record_status <> 'E'
AND prgms.person_id = p_person_rec.person_id
AND prgms.prgm_action_type = 'DB'
AND prgms.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_prgms_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_person_rec.person_id
AND prs.prgm_action_type = 'DB'
);
SELECT prgms.prgm_start_date,
prgms.prgm_end_date,
prgms.remarks
FROM igs_sv_prgms_info prgms,
igs_sv_persons pr
WHERE prgms.person_id = pr.person_id
AND prgms.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prgms.person_id = p_person_rec.person_id
AND prgms.prgm_action_type = 'RF';
SELECT visa_type
FROM IGS_PE_NONIMG_FORM
WHERE nonimg_form_id = p_person_rec.form_id;
SELECT COUNT(1)
FROM igs_pe_nonimg_stat
WHERE nonimg_form_id = p_person_rec.form_id
AND p_term_reason NOT IN
( SELECT lookup_code FROM igs_lookup_values
WHERE lookup_type ='PE_SV_TERMINATE_REASON' AND
enabled_flag ='Y' AND
(tag= p_visa OR tag= 'FM')
);
SELECT
PRGM_END_DATE
FROM
IGS_PE_NONIMG_FORM_V
WHERE
nonimg_form_id = p_person_rec.form_id;
p_data_rec(l_counter).created_by := g_update_by;
p_data_rec(l_counter).last_updated_by := g_update_by;
p_data_rec(l_counter).last_update_date := sysdate;
p_data_rec(l_counter).last_update_login := g_update_login;
p_data_rec(l_counter).created_by := g_update_by;
p_data_rec(l_counter).last_updated_by := g_update_by;
p_data_rec(l_counter).last_update_date := sysdate;
p_data_rec(l_counter).last_update_login := g_update_login;
SELECT acad_term_length,
tuition_amt,
living_exp_amt,
depdnt_exp_amt dependent_exp_amt,
other_exp_amt,
other_exp_desc,
personal_funds_amt,
school_funds_amt,
school_funds_desc,
other_funds_amt,
other_funds_desc,
empl_funds_amt,
remarks
FROM igs_pe_nonimg_form penf
WHERE penf.person_id = p_person_rec.person_id
AND penf.nonimg_form_id = p_person_rec.form_id;
SELECT prgm_sponsor_amt program_sponsor,
govt_org1_amt,
govt_org2_amt,
govt_org1_code,
govt_org2_code,
intl_org1_amt,
intl_org2_amt,
intl_org1_code,
intl_org2_code,
ev_govt_amt,
bi_natnl_com_amt,
other_govt_amt,
personal_funds_amt,
remarks,
NVL(NVL(govt_org2_amt,govt_org1_amt),'0') recvd_us_gvt_funds_ind,
govt_org1_othr_name ,
govt_org2_othr_name ,
intl_org1_othr_name ,
intl_org2_othr_name ,
other_govt_name
FROM igs_pe_ev_form evf
WHERE evf.person_id = p_person_rec.person_id
AND evf.ev_form_id = p_person_rec.form_id;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_login := g_update_login;
p_data_rec.last_update_date := sysdate;
SELECT dep.relationship_id,
rel.object_id depdnt_id,
action_code,
to_char(effective_date,'YYYY-MM-DD') effective_date ,
reason_code,
dep.REMARKS comments,
rel.COMMENTS rel_remarks,
decode (RELATIONSHIP_CODE,'PARENT_OF','02','SPOUSE_OF','01','XX' ) relationship
FROM igs_pe_depd_active dep,
HZ_RELATIONSHIPS rel
WHERE subject_id = p_person_rec.person_id
AND rel.relationship_id = dep.relationship_id
AND (p_person_rec.record_status = 'C' OR dep.action_code ='A') --In the new mode report only about active dependents
AND RELATIONSHIP_CODE IN ('PARENT_OF','SPOUSE_OF' )
AND (dep.relationship_id, effective_date) IN
( SELECT dep1.relationship_id,
MAX(dep1.effective_date)
FROM igs_pe_depd_active dep1
WHERE dep1.relationship_id = rel.relationship_id
GROUP BY dep1.relationship_id)
AND rel.status = 'A';
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = p_id;
SELECT alt.api_person_id
FROM igs_pe_alt_pers_id alt
WHERE
alt.pe_person_id = c_dep_id AND
alt.person_id_type IN (SELECT person_id_type FROM igs_pe_person_id_typ
WHERE s_person_id_type = 'SEVIS_ID') AND
sysdate between alt.start_dt and nvl(alt.end_dt, sysdate+1);
p_data_rec(l_counter).created_by := g_update_by;
p_data_rec(l_counter).last_updated_by := g_update_by;
p_data_rec(l_counter).last_update_date := sysdate;
p_data_rec(l_counter).last_update_login := g_update_login;
SELECT felony_details_id conviction_id,
disp_action_info criminal_remarks
FROM igs_pe_felony_dtls
WHERE person_id = p_person_rec.person_id
AND convict_ind ='Y'
AND felony_details_id NOT IN
( SELECT conviction_id
FROM igs_sv_convictions prg,
igs_sv_persons pr
WHERE prg.person_id = pr.person_id
AND prg.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prg.person_id = p_person_rec.person_id )
ORDER BY crime_date;
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT peva.visa_number,
peva.visa_issuing_post,
peva.visa_expiry_date,
pevv.cntry_entry_form_num,
pevv.port_of_entry,
pevv.visit_start_date,
pevv.remarks,
pspt.passport_number,
pspt.passport_cntry_code,
pspt.passport_expiry_date,
peva.visa_issuing_country,
peva.visa_issue_date
FROM igs_pe_visa peva,
igs_pe_visit_histry pevv,
igs_pe_passport pspt
WHERE peva.person_id = p_person_rec.person_id
AND peva.visa_type IN ('F-1', 'F-2','M-1','M-2')
AND peva.visa_id = pevv.visa_id (+)
AND pspt.passport_id (+) = peva.passport_id
AND peva.visa_expiry_date >= trunc(sysdate);
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT peva.visa_number,
peva.visa_issuing_post,
peva.visa_expiry_date,
pevv.cntry_entry_form_num,
pevv.port_of_entry,
pevv.visit_start_date,
pevv.remarks,
pspt.passport_number,
pspt.passport_cntry_code,
pspt.passport_expiry_date,
peva.visa_issuing_country,
peva.visa_issue_date
FROM igs_pe_visa peva,
igs_pe_visit_histry pevv,
igs_pe_passport pspt
WHERE peva.person_id = p_person_rec.person_id
AND peva.visa_type IN ('J-1', 'J-2')
AND peva.visa_id = pevv.visa_id (+)
AND pspt.passport_id (+) = peva.passport_id
AND peva.visa_expiry_date >= trunc(sysdate);
p_data_rec.created_by := g_update_by;
p_data_rec.last_updated_by := g_update_by;
p_data_rec.last_update_date := sysdate;
p_data_rec.last_update_login := g_update_login;
SELECT nonimg_empl_id,
empl_rec_type,
empl_type,
recommend_empl,
rescind_empl,
remarks,
empl_start_date ,
empl_end_date,
course_relevance,
empl_time,
empl_name,
action_code
FROM igs_sv_empl_info
WHERE
person_id = p_data_rec.person_id and
nonimg_empl_id = p_data_rec.nonimg_empl_id and
batch_id IN
( SELECT max(emp.batch_id)
FROM igs_sv_empl_info emp,
igs_sv_persons pr
WHERE emp.person_id = pr.person_id
AND emp.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND emp.person_id = p_data_rec.person_id
AND emp.nonimg_empl_id = p_data_rec.nonimg_empl_id
)
ORDER BY nonimg_empl_id;
SELECT visa_type
FROM igs_pe_nonimg_form
WHERE person_id = p_person_rec.person_id
AND nonimg_form_id = p_person_rec.form_id;
SELECT nonimg_empl_id,
frm.nonimg_form_id,
decode ( empl_type,'01','O','02','C','F') empl_rec_type,
empl_type,
recommend_empl,
rescind_empl,
em.remarks,
to_char(empl_start_date, 'YYYY-MM-DD') empl_start_date,
to_char(empl_end_date, 'YYYY-MM-DD') empl_end_date,
course_relevance,
empl_time,
empl_party_id,
action_code,
NVL(em.print_flag, 'N') print_flag
FROM igs_pe_nonimg_empl em,
igs_pe_nonimg_form frm
WHERE frm.person_id = cp_person_id
AND frm.nonimg_form_id = em.nonimg_form_id
ORDER BY nonimg_empl_id;
SELECT party_name
FROM hz_parties
WHERE party_id = l_party_id;
SELECT SUBSTR(address1||address2||address3||address4,1,60) addr_line1,
SUBSTR(address1||address2||address3||address4,1,60) addr_line2,
SUBSTR(city,1,60) city ,
SUBSTR(state,1,2) state ,
postal_code
FROM hz_locations lc, hz_party_sites st
WHERE party_id = l_party_id
AND lc.location_id = st.location_id
AND identifying_address_flag ='Y';
p_data_rec(l_counter).created_by := g_update_by;
p_data_rec(l_counter).last_updated_by := g_update_by;
p_data_rec(l_counter).last_update_date := sysdate;
p_data_rec(l_counter).last_update_login := g_update_login;
SELECT cr.batch_id ,
cr.person_id ,
cr.record_number ,
cr.form_id ,
decode(cr.print_form,'Y','1','0') print_form,
cr.record_status ,
cr.person_number ,
cr.sevis_user_id ,
cr.issuing_reason ,
cr.curr_session_end_date ,
cr.next_session_start_date,
cr.other_reason ,
cr.transfer_from_school ,
cr.ev_create_reason ,
cr.ev_form_number ,
cr.no_show_flag ,
cr.last_session_flag
FROM igs_sv_persons cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.batch_id IN
( SELECT max(mx.batch_id)
FROM igs_sv_persons mx
WHERE mx.person_id = p_data_rec.person_id
AND mx.record_status <> 'E'
);
SELECT birth_date ,
birth_cntry_code ,
citizen_cntry_code ,
last_name ,
middle_name ,
first_name ,
suffix ,
gender ,
legal_res_cntry_code ,
position_code ,
commuter ,
remarks ,
birth_cntry_resn_code ,
birth_city
FROM igs_sv_bio_info cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_bio_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
);
SELECT drivers_license ,
drivers_license_state ,
ssn ,
tax_id
FROM igs_sv_oth_info cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_oth_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
);
SELECT action_type ,
address_type ,
address_line1 ,
address_line2 ,
city ,
state ,
postal_code ,
postal_routing_code ,
country_code ,
province ,
stdnt_valid_flag ,
primary_flag ,
activity_site_cd
FROM igs_sv_addresses cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.party_site_id = p_data_rec.party_site_id
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_addresses prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
AND prs.party_site_id = p_data_rec.party_site_id
);
SELECT position_code ,
subject_field_code ,
education_level ,
primary_major ,
secondary_major ,
minor ,
length_of_study ,
prgm_start_date ,
prgm_end_date ,
english_reqd ,
english_reqd_met ,
not_reqd_reason ,
educ_lvl_remarks ,
remarks
FROM igs_sv_prgms_info cr
WHERE cr.person_id = p_data_rec.person_id
--AND cr.prgm_action_type='EP'
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_prgms_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
--AND prs.prgm_action_type = 'EP'
);
SELECT acad_term_length ,
tuition ,
living_exp ,
personal_funds ,
dependent_exp ,
other_exp ,
other_exp_desc ,
school_funds ,
school_funds_desc ,
other_funds ,
other_funds_desc ,
program_sponsor ,
govt_org1 ,
govt_org2 ,
govt_org1_code ,
govt_org2_code ,
intl_org1 ,
intl_org2 ,
intl_org1_code ,
intl_org2_code ,
ev_govt ,
bi_natnl_com ,
other_org ,
remarks ,
govt_org1_othr_name ,
govt_org2_othr_name ,
intl_org1_othr_name ,
intl_org2_othr_name ,
other_govt_name ,
empl_funds
FROM igs_sv_finance_info cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_finance_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
);
SELECT depdnt_action_type ,
depdnt_sevis_id ,
last_name ,
first_name ,
middle_name ,
suffix ,
birth_date ,
gender ,
birth_cntry_code ,
citizen_cntry_code ,
relationship ,
termination_reason ,
relationship_remarks ,
perm_res_cntry_code ,
termination_effect_date,
remarks ,
birth_cntry_resn_code ,
VISA_TYPE
FROM igs_sv_depdnt_info cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.depdnt_id = p_data_rec.depdnt_id
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_depdnt_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
AND prs.depdnt_id = p_data_rec.depdnt_id
);
SELECT psprt_number ,
psprt_issuing_cntry_code,
psprt_exp_date ,
visa_number ,
visa_issuing_post ,
visa_issuing_cntry_code,
visa_expiration_date ,
i94_number ,
port_of_entry ,
date_of_entry ,
remarks ,
visa_issue_date
FROM igs_sv_legal_info cr
WHERE cr.person_id = p_data_rec.person_id
AND cr.batch_id IN
( SELECT max(prs.batch_id)
FROM igs_sv_legal_info prs,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.person_id = p_data_rec.person_id
);
PROCEDURE Update_Issue_Info (
p_data_rec IN IGS_SV_PERSONS%ROWTYPE -- Data record
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Update_Issue_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Issue_Info';
l_debug_str := 'Entering Update_Issue_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
UPDATE IGS_SV_PERSONS
SET issuing_reason = p_data_rec.issuing_reason,
curr_session_end_date = p_data_rec.curr_session_end_date ,
next_session_start_date= p_data_rec.next_session_start_date ,
other_reason = p_data_rec.other_reason ,
transfer_from_school = p_data_rec.transfer_from_school ,
ev_create_reason = p_data_rec.ev_create_reason ,
ev_form_number = p_data_rec.ev_form_number,
init_prgm_start_date = p_data_rec.init_prgm_start_date,
no_show_flag = p_data_rec.no_show_flag,
last_session_flag = p_data_rec.last_session_flag,
adjudicated_flag = p_data_rec.adjudicated_flag
WHERE batch_id = p_data_rec.batch_id
AND person_id = p_data_rec.person_id
AND record_number = p_data_rec.record_number;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Issue_Info';
l_debug_str := 'EXCEPTION in Update_Issue_Info. '||SQLERRM;
END Update_Issue_Info;
Purpose : Insert Bio information block.
(IGS_SV_BIO_INFO).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Bio_Info(
p_data_rec IN IGS_SV_BIO_INFO%ROWTYPE -- Data record
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Bio_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Bio_Info';
l_debug_str := 'Entering Insert_Bio_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
Insert_Summary_Info(p_data_rec.batch_id,
p_data_rec.person_id,
g_person_status,
'SV_BIO',
'SEND',
'IGS_SV_BIO_INFO',
'');
INSERT INTO IGS_SV_BIO_INFO (
batch_id ,
person_id ,
print_form ,
birth_date ,
birth_cntry_code ,
birth_city ,
citizen_cntry_code ,
last_name ,
middle_name ,
first_name ,
suffix ,
gender ,
legal_res_cntry_code ,
position_code ,
category_code ,
remarks ,
commuter ,
visa_type ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
birth_cntry_resn_code
) VALUES
(
p_data_rec.batch_id ,
p_data_rec.person_id ,
p_data_rec.print_form ,
p_data_rec.birth_date ,
p_data_rec.birth_cntry_code ,
p_data_rec.birth_city ,
p_data_rec.citizen_cntry_code ,
p_data_rec.last_name ,
p_data_rec.middle_name ,
p_data_rec.first_name ,
p_data_rec.suffix ,
p_data_rec.gender ,
p_data_rec.legal_res_cntry_code ,
p_data_rec.position_code ,
p_data_rec.category_code ,
p_data_rec.remarks ,
p_data_rec.commuter ,
p_data_rec.visa_type ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login ,
p_data_rec.birth_cntry_resn_code
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Bio_Info';
l_debug_str := 'EXCEPTION in Insert_Bio_Info. '||SQLERRM;
END Insert_Bio_Info;
Purpose : Insert Other information block
(IGS_SV_OTH_INFO)
remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Other_Info (
p_data_rec IN IGS_SV_OTH_INFO%ROWTYPE -- Data record
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Other_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Other_Info';
l_debug_str := 'Entering Insert_Other_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
Insert_Summary_Info(p_data_rec.batch_id,
p_data_rec.person_id,
g_person_status,
'SV_OTHER',
'SEND',
'IGS_SV_OTH_INFO',
'');
INSERT INTO IGS_SV_OTH_INFO (
batch_id ,
person_id ,
print_form ,
drivers_license ,
drivers_license_state ,
ssn ,
tax_id ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login
) VALUES
(p_data_rec.batch_id ,
p_data_rec.person_id ,
p_data_rec.print_form ,
p_data_rec.drivers_license ,
p_data_rec.drivers_license_state ,
p_data_rec.ssn ,
p_data_rec.tax_id ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Other_Info';
l_debug_str := 'EXCEPTION in Insert_Other_Info. '||SQLERRM;
END Insert_Other_Info;
Purpose : Insert site of activity information.
(IGS_SV_ADDRESSES).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Address_Info (
p_addr_type IN VARCHAR, -- Address type bein inserted- US, Foreign, Site of Activity
p_data_rec IN g_address_rec_type , -- Data record
p_records IN NUMBER -- number of addressees found
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Address_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Address_Info';
l_debug_str := 'Entering Insert_Address_Info. Number of records: '||p_records;
l_action := 'DELETE';
Insert_Summary_Info(l_btch_id,
p_data_rec(l_count).person_id,
l_action,
'SV_SOA',
'SEND',
'IGS_SV_ADDRESSES',
p_data_rec(l_count).party_site_id);
Insert_Summary_Info(l_btch_id,
p_data_rec(l_count).person_id,
g_person_status,
'SV_US_ADDR',
'SEND',
'IGS_SV_ADDRESSES',
p_data_rec(l_count).party_site_id);
Insert_Summary_Info(l_btch_id,
p_data_rec(l_count).person_id,
g_person_status,
'SV_F_ADDR',
'SEND',
'IGS_SV_ADDRESSES',
p_data_rec(l_count).party_site_id);
INSERT INTO igs_sv_addresses (
batch_id ,
person_id ,
party_site_id ,
print_form ,
address_type ,
address_line1 ,
address_line2 ,
city ,
state ,
postal_code ,
postal_routing_code ,
country_code ,
province ,
stdnt_valid_flag ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
action_type ,
primary_flag ,
activity_site_cd ,
remarks
) VALUES (
l_btch_id ,
p_data_rec(l_count).person_id ,
p_data_rec(l_count).party_site_id ,
p_data_rec(l_count).print_form ,
p_data_rec(l_count).address_type ,
p_data_rec(l_count).address_line1 ,
p_data_rec(l_count).address_line2 ,
p_data_rec(l_count).city ,
p_data_rec(l_count).state ,
p_data_rec(l_count).postal_code ,
p_data_rec(l_count).postal_routing_code ,
p_data_rec(l_count).country_code ,
p_data_rec(l_count).province ,
p_data_rec(l_count).stdnt_valid_flag ,
p_data_rec(l_count).creation_date ,
p_data_rec(l_count).created_by ,
p_data_rec(l_count).last_updated_by ,
p_data_rec(l_count).last_update_date ,
p_data_rec(l_count).last_update_login ,
p_data_rec(l_count).action_type ,
p_data_rec(l_count).primary_flag ,
p_data_rec(l_count).activity_site_cd ,
l_remarks
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Address_Info';
l_debug_str := 'EXCEPTION in Insert_Address_Info. '||SQLERRM;
END Insert_Address_Info;
Purpose : Insert education information on the student
(IGS_SV_PRGMS_INFO).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Edu_Info (
p_edu_type IN VARCHAR2,
p_data_rec IN IGS_SV_PRGMS_INFO%ROWTYPE,
p_auth_drp_data_rec IN IGS_SV_PRGMS_INFO%ROWTYPE
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Edu_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Edu_Info';
l_debug_str := 'Entering Insert_Edu_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Edu_Info';
Insert_Summary_Info(l_btch_id,
p_data_rec.person_id,
l_action,
l_tag_code,
'SEND',
'IGS_SV_PRGMS_INFO',
p_data_rec.prgm_action_type);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Edu_Info';
Insert_Summary_Info(l_btch_id,
p_data_rec.person_id,
l_action,
'SV_PRGMS',
'SEND',
'IGS_SV_PRGMS_INFO',
p_data_rec.prgm_action_type);
INSERT INTO igs_sv_prgms_info (
batch_id ,
person_id ,
prgm_action_type ,
print_form ,
form_status_id ,
position_code ,
subject_field_code ,
education_level ,
primary_major ,
secondary_major ,
educ_lvl_remarks ,
minor ,
length_of_study ,
prgm_start_date ,
prgm_end_date ,
english_reqd ,
english_reqd_met ,
not_reqd_reason ,
matriculation ,
effective_date ,
authorization_reason ,
termination_reason ,
end_prgm_reason ,
reprint_reason ,
submit_update ,
remarks ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
auth_action_code
) VALUES
( l_btch_id ,
p_data_rec.person_id ,
p_data_rec.prgm_action_type ,
p_data_rec.print_form ,
p_data_rec.form_status_id ,
l_position_code ,
l_subject_field_code ,
l_education_level ,
l_primary_major ,
l_secondary_major ,
l_educ_lvl_remarks ,
l_minor ,
l_length_of_study ,
l_prgm_start_date ,
l_prgm_end_date ,
l_english_reqd ,
l_english_reqd_met ,
l_not_reqd_reason ,
p_data_rec.matriculation ,
p_data_rec.effective_date ,
p_data_rec.authorization_reason ,
p_data_rec.termination_reason ,
p_data_rec.end_prgm_reason ,
p_data_rec.reprint_reason ,
p_data_rec.submit_update ,
l_remarks ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login ,
p_data_rec.auth_action_code
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Edu_Info';
l_debug_str := 'Record inserted';
UPDATE igs_sv_prgms_info
SET authorization_reason =p_auth_drp_data_rec.authorization_reason,
auth_action_code = p_auth_drp_data_rec.auth_action_code,
prgm_start_date = p_auth_drp_data_rec.prgm_start_date,
prgm_end_date = p_auth_drp_data_rec.prgm_end_date,
remarks = p_auth_drp_data_rec.remarks,
prgm_action_type = 'DB'
WHERE batch_id = l_btch_id
AND person_id = p_data_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Edu_Info';
l_debug_str := 'EXCEPTION in Insert_Edu_Info. '||SQLERRM;
END Insert_Edu_Info;
Purpose : Insert Finance information on the student
(IGS_SV_FINANCE_INFO)
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Finance_Info (
p_data_rec IN IGS_SV_FINANCE_INFO %ROWTYPE -- Data record
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Finance_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Finance_Info';
l_debug_str := 'Entering Insert_Finance_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
Insert_Summary_Info(p_data_rec.batch_id,
p_data_rec.person_id,
g_person_status,
'SV_FINANCIAL',
'SEND',
'IGS_SV_FINANCE_INFO',
'');
INSERT INTO igs_sv_finance_info (
batch_id ,
person_id ,
print_form ,
acad_term_length ,
tuition ,
living_exp ,
personal_funds ,
dependent_exp ,
other_exp ,
other_exp_desc ,
school_funds ,
school_funds_desc ,
other_funds ,
other_funds_desc ,
program_sponsor ,
govt_org1 ,
govt_org2 ,
govt_org1_code ,
govt_org2_code ,
intl_org1 ,
intl_org2 ,
intl_org1_code ,
intl_org2_code ,
ev_govt ,
bi_natnl_com ,
other_org ,
recvd_us_gvt_funds ,
remarks ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
govt_org1_othr_name ,
govt_org2_othr_name ,
intl_org1_othr_name ,
intl_org2_othr_name ,
other_govt_name ,
empl_funds
) VALUES
(p_data_rec.batch_id ,
p_data_rec.person_id ,
p_data_rec.print_form ,
p_data_rec.acad_term_length ,
p_data_rec.tuition ,
p_data_rec.living_exp ,
p_data_rec.personal_funds ,
p_data_rec.dependent_exp ,
p_data_rec.other_exp ,
p_data_rec.other_exp_desc ,
p_data_rec.school_funds ,
p_data_rec.school_funds_desc ,
p_data_rec.other_funds ,
p_data_rec.other_funds_desc ,
p_data_rec.program_sponsor ,
p_data_rec.govt_org1 ,
p_data_rec.govt_org2 ,
p_data_rec.govt_org1_code ,
p_data_rec.govt_org2_code ,
p_data_rec.intl_org1 ,
p_data_rec.intl_org2 ,
p_data_rec.intl_org1_code ,
p_data_rec.intl_org2_code ,
p_data_rec.ev_govt ,
p_data_rec.bi_natnl_com ,
p_data_rec.other_org ,
p_data_rec.recvd_us_gvt_funds ,
p_data_rec.remarks ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login ,
p_data_rec.govt_org1_othr_name ,
p_data_rec.govt_org2_othr_name ,
p_data_rec.intl_org1_othr_name ,
p_data_rec.intl_org2_othr_name ,
p_data_rec.other_govt_name ,
p_data_rec.empl_funds
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Finance_Info';
l_debug_str := 'EXCEPTION in Insert_Finance_Info. '||SQLERRM;
END Insert_Finance_Info;
Purpose : Insert dependent information on the student
(IGS_SV_DEPDNT_INFO).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Dependent_Info (
p_data_rec IN g_dependent_rec_type, -- Data record
p_records IN NUMBER --Number of dependents found
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Dependent_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Dependent_Info';
l_debug_str := 'Entering Insert_Dependent_Info.Number of records being inserted: '||p_records;
l_action := 'DELETE';
Insert_Summary_Info(l_btch_id,
p_data_rec(l_count).person_id,
l_action,
'SV_DEPDNT',
'SEND',
'IGS_SV_DEPDNT_INFO',
p_data_rec(l_count).depdnt_id);
INSERT INTO igs_sv_depdnt_info (
batch_id ,
person_id ,
depdnt_id ,
print_form ,
person_number ,
depdnt_action_type ,
depdnt_sevis_id ,
visa_type ,
last_name ,
first_name ,
middle_name ,
suffix ,
birth_date ,
gender ,
birth_cntry_code ,
citizen_cntry_code ,
relationship ,
termination_reason ,
relationship_remarks ,
perm_res_cntry_code ,
termination_effect_date,
remarks ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
birth_cntry_resn_code
) VALUES
(l_btch_id , -- prbhardw CP enhancement
p_data_rec(l_count).person_id ,
p_data_rec(l_count).depdnt_id ,
p_data_rec(l_count).print_form ,
p_data_rec(l_count).person_number ,
p_data_rec(l_count).depdnt_action_type ,
p_data_rec(l_count).depdnt_sevis_id ,
p_data_rec(l_count).visa_type ,
p_data_rec(l_count).last_name ,
p_data_rec(l_count).first_name ,
p_data_rec(l_count).middle_name ,
p_data_rec(l_count).suffix ,
p_data_rec(l_count).birth_date ,
p_data_rec(l_count).gender ,
p_data_rec(l_count).birth_cntry_code ,
p_data_rec(l_count).citizen_cntry_code ,
p_data_rec(l_count).relationship ,
p_data_rec(l_count).termination_reason ,
p_data_rec(l_count).relationship_remarks ,
p_data_rec(l_count).perm_res_cntry_code ,
p_data_rec(l_count).termination_effect_date,
p_data_rec(l_count).remarks ,
p_data_rec(l_count).creation_date ,
p_data_rec(l_count).created_by ,
p_data_rec(l_count).last_updated_by ,
p_data_rec(l_count).last_update_date ,
p_data_rec(l_count).last_update_login ,
p_data_rec(l_count).birth_cntry_resn_code
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Dependent_Info';
l_debug_str := 'EXCEPTION in Insert_Dependent_Info. '||SQLERRM;
END Insert_Dependent_Info;
Purpose : Insert Conviction information on the student.
(IGS_SV_CONVICTIONS).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Convictions_Info (
p_data_rec IN IGS_SV_CONVICTIONS%ROWTYPE
) IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Convictions_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Convictions_Info';
l_debug_str := 'Entering Insert_Convictions_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
Insert_Summary_Info(p_data_rec.batch_id,
p_data_rec.person_id,
g_person_status,
'SV_CONVICTION',
'SEND',
'IGS_SV_CONVICTIONS',
'');
INSERT INTO igs_sv_convictions (
batch_id ,
person_id ,
conviction_id ,
print_form ,
criminal_conviction ,
remarks ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login
) VALUES
(p_data_rec.batch_id ,
p_data_rec.person_id ,
p_data_rec.conviction_id ,
p_data_rec.print_form ,
p_data_rec.criminal_conviction ,
p_data_rec.remarks ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Convictions_Info';
l_debug_str := 'EXCEPTION in Insert_Convictions_Info. '||SQLERRM;
END Insert_Convictions_Info;
Purpose : Insert Legal information on the student.
(IGS_SV_LEGAL_INFO).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Legal_Info (
p_data_rec IN IGS_SV_LEGAL_INFO%ROWTYPE -- Data record
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Legal_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Legal_Info';
l_debug_str := 'Entering Insert_Legal_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
Insert_Summary_Info(p_data_rec.batch_id,
p_data_rec.person_id,
g_person_status,
'SV_LEGAL',
'SEND',
'IGS_SV_LEGAL_INFO',
'');
INSERT INTO igs_sv_legal_info (
batch_id ,
person_id ,
print_form ,
psprt_number ,
psprt_issuing_cntry_code,
psprt_exp_date ,
visa_number ,
visa_issuing_post ,
visa_issuing_cntry_code,
visa_expiration_date ,
i94_number ,
port_of_entry ,
date_of_entry ,
remarks ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
VISA_ISSUE_DATE
) VALUES
(p_data_rec.batch_id ,
p_data_rec.person_id ,
p_data_rec.print_form ,
p_data_rec.psprt_number ,
p_data_rec.psprt_issuing_cntry_code,
p_data_rec.psprt_exp_date ,
p_data_rec.visa_number ,
p_data_rec.visa_issuing_post ,
p_data_rec.visa_issuing_cntry_code,
p_data_rec.visa_expiration_date ,
p_data_rec.i94_number ,
p_data_rec.port_of_entry ,
p_data_rec.date_of_entry ,
p_data_rec.remarks ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login ,
p_data_rec.VISA_ISSUE_DATE
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Legal_Info';
l_debug_str := 'EXCEPTION in Insert_Legal_Info. '||SQLERRM;
END Insert_Legal_Info;
Purpose : Insert employment information on the student
(IGS_SV_EMPL_INFO).
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Empl_Info (
p_data_rec IN IGS_SV_EMPL_INFO%ROWTYPE --Data record
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Empl_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Empl_Info';
l_debug_str := 'Entering Insert_Empl_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
Insert_Summary_Info(l_btch_id,
p_data_rec.person_id,
l_action,
l_tag_code,
'SEND',
'IGS_SV_EMPL_INFO',
p_data_rec.nonimg_empl_id);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
INSERT INTO igs_sv_empl_info (
batch_id ,
person_id ,
nonimg_empl_id ,
empl_rec_type ,
print_form ,
empl_type ,
recommend_empl ,
rescind_empl ,
remarks ,
empl_start_date ,
empl_end_date ,
empl_name ,
empl_time ,
course_relevance ,
empl_addr_line1 ,
empl_addr_line2 ,
city ,
state ,
postal_code ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
action_code
) VALUES
(l_btch_id , --- prbhardw CP enhancement
p_data_rec.person_id ,
p_data_rec.nonimg_empl_id ,
p_data_rec.empl_rec_type ,
p_data_rec.print_form ,
p_data_rec.empl_type ,
p_data_rec.recommend_empl ,
p_data_rec.rescind_empl ,
p_data_rec.remarks ,
p_data_rec.empl_start_date ,
p_data_rec.empl_end_date ,
p_data_rec.empl_name ,
p_data_rec.empl_time ,
p_data_rec.course_relevance ,
p_data_rec.empl_addr_line1 ,
p_data_rec.empl_addr_line2 ,
p_data_rec.city ,
p_data_rec.state ,
p_data_rec.postal_code ,
p_data_rec.creation_date ,
p_data_rec.created_by ,
p_data_rec.last_updated_by ,
p_data_rec.last_update_date ,
p_data_rec.last_update_login ,
p_data_rec.action_code
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Empl_Info';
l_debug_str := 'EXCEPTION in Insert_Empl_Info. '||SQLERRM;
END Insert_Empl_Info;
Purpose : Update registration block information.
remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Registration_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Registration_Info';
SELECT pr.curr_session_end_date,
pr.next_session_start_date,
pr.last_session_flag
FROM igs_sv_persons pr
WHERE pr.person_id = p_person_rec.person_id
AND pr.batch_id IN
( SELECT max(btch.batch_id)
FROM igs_sv_persons prs,
igs_sv_batches btch,
igs_sv_persons pr
WHERE prs.person_id = pr.person_id
AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prs.batch_id = btch.batch_id
AND btch.batch_type = p_person_rec.batch_type
AND prs.person_id = p_person_rec.person_id
AND pr.curr_session_end_date IS NOT NULL
);
SELECT to_char(curr_session_end_date,'YYYY-MM-DD') ,
to_char(next_session_start_date ,'YYYY-MM-DD'),
last_session_flag
FROM igs_pe_nonimg_form
WHERE nonimg_form_id = p_person_rec.form_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'Entering Update_Registration_Info. p_person_rec.person_id is '||p_person_rec.person_id|| ' and p_person_rec.batch_type is '||p_person_rec.batch_type;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'Returning S from Update_Registration_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'Unexpected error in Update_Registration_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Registration_Info.';
Insert_Legal_Info ( p_data_rec => l_cur_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'Returning S from Update_Registration_Info.';
UPDATE igs_sv_persons
SET curr_session_end_date = l_start_date,
next_session_start_date = l_end_date,
last_session_flag = l_last_session_flag ---prbhardw
WHERE person_id = p_person_rec.person_id and
batch_id = p_person_rec.batch_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'Returning S from Update_Registration_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'Returning S from Update_Registration_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_Registration_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Registration_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Registration_Info. '||SQLERRM;
END Update_Registration_Info;
FUNCTION Update_ev_Legal_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_ev_Legal_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_ev_Legal_Info';
l_debug_str := 'Entering Update_ev_Legal_Info. p_person_rec.person_id is '||p_person_rec.person_id|| ' and p_person_rec.batch_type is '||p_person_rec.batch_type;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_ev_Legal_Info';
l_debug_str := 'Returning S from Update_ev_Legal_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_ev_Legal_Info';
l_debug_str := 'Unexpected error in Update_ev_Legal_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_ev_Legal_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_ev_Legal_Info.';
Insert_Legal_Info ( p_data_rec => l_cur_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_ev_Legal_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_ev_Legal_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_ev_Legal_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_ev_Legal_Info. '||SQLERRM;
END Update_ev_Legal_Info;
Purpose : Update registration block information.
remarks :
Change History
Who When What
pkpatel 30-JUN-2003 Bug 2908378
Checked the status of Get_Address_Info properly.
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_EV_Address_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_EV_Address_Info';
SELECT is_valid
FROM igs_pe_ev_form
WHERE ev_form_id = p_person_rec.form_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Entering Update_EV_Address_Info. p_person_rec.form_id is '||p_person_rec.form_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Returning S from Update_EV_Address_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Unexpected error in Update_EV_Address_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Returning S from Update_EV_Address_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_EV_Address_Info.';
Insert_Address_Info ('US', p_data_rec => l_us_addr_rec,p_records => 1);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Returning S from Update_EV_Address_Info. us_addr_status is E';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'ERROR in Update_EV_Address_Info. us_addr_status is U';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Returning S from Update_EV_Address_Info. us_addr_status is N';
l_site_addr_rec(l_cur).batch_id :=NULL; -- delete from insert
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_EV_Address_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'changes_found is Y. Exiting Update_EV_Address_Info.';
Insert_Address_Info ('SOA', p_data_rec => l_site_addr_rec,p_records => l_count);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'Returning S from Update_EV_Address_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_EV_Address_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Address_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_EV_Address_Info. '||SQLERRM;
END Update_EV_Address_Info;
Purpose : Update Personal information block.
Remarks : 'S' - success
'U' - Unexpected error
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Personal_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Personal_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'Entering Update_Personal_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'Returning S from Update_Personal_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Personal_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'IGS_SV_UNEXP_EXCPT_ERR in Update_Personal_Info. bio_status is N';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'FND_API.G_EXC_ERROR in Update_Personal_Info. l_status is U';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'FND_API.G_EXC_ERROR in Update_Personal_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'FND_API.G_EXC_ERROR in Update_Personal_Info. f_addr_status is S';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'FND_API.G_EXC_ERROR in Update_Personal_Info. us_addr_status is S';
Put_Log_Msg('Info is chnaged for the block - do insert',0);
Insert_Bio_Info ( p_data_rec => l_bio_rec);
Insert_Other_Info ( p_data_rec => l_oth_rec);
Insert_Address_Info ('F', p_data_rec => l_f_addr_rec,p_records => 1); --- prbhardw CP enhancement
Insert_Address_Info ( 'US',p_data_rec => l_us_addr_rec,p_records => 1); --- prbhardw CP enhancement
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'Returning S from Update_Personal_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_Personal_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Personal_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Personal_Info. '||SQLERRM;
END Update_Personal_Info;
SELECT prgm_action_type,
prgm_start_date,
prgm_end_date ,
effective_date,
termination_reason,
end_prgm_reason,
remarks
FROM igs_sv_prgms_info
WHERE person_id = p_data_rec.person_id AND
batch_id IN
( SELECT max(prg.batch_id)
FROM igs_sv_prgms_info prg,
igs_sv_persons pr
WHERE prg.person_id = pr.person_id
AND prg.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prg.person_id = p_data_rec.person_id
)
ORDER BY effective_date;
SELECT effective_date,
prgm_action_type,
prgm_start_date,
prgm_end_date,
remarks,
termination_reason,
print_form
FROM igs_sv_prgms_info
WHERE person_id = p_data_rec.person_id AND
batch_id IN
( SELECT max(prg.batch_id)
FROM igs_sv_prgms_info prg,
igs_sv_persons pr
WHERE prg.person_id = pr.person_id
AND prg.batch_id = pr.batch_id
AND pr.record_status <> 'E'
AND prg.person_id = p_data_rec.person_id
)
ORDER BY effective_date;
Purpose : Update program information block
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Program_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Program_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'Entering Update_Program_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'Returning S from Update_Program_Info. p_person_rec.edu_status is E';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. p_person_rec.edu_status is U';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. p_person_rec.edu_status is N.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. l_status is U or N.';
Insert_Edu_Info ( 'EDU', p_data_rec => l_cur_rec, p_auth_drp_data_rec => l_cur_authdrp_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'Returning S from Update_Program_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Unexpected error in Update_Employment_Info. l_status is U.';
ELSE --Remove current person from the insert list
l_cur_prgm_rec(l_current).person_id := NULL;
Insert_Edu_Info ('PRGM', p_data_rec => l_cur_prgm_rec(l_current), p_auth_drp_data_rec => l_cur_authdrp_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'Final Return S from Update_Program_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_Program_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Program_Info. '||SQLERRM;
END Update_Program_Info;
Purpose : Update EV program information block
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_EV_Program_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_EV_Program_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'Entering Update_EV_Program_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'Returning S from Update_Program_Info. p_person_rec.edu_status is E';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. p_person_rec.edu_status is U';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. p_person_rec.edu_status is N.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. l_status is U or N.';
Insert_Edu_Info ( 'EDU', p_data_rec => l_cur_rec, p_auth_drp_data_rec => l_cur_authdrp_rec);
Insert_Edu_Info ( 'PRGM', p_data_rec => l_cur_rec, p_auth_drp_data_rec => l_cur_authdrp_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'Returning S from Update_EV_Program_Info. p_person_rec.edu_status is E.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_EV_Program_Info. p_person_rec.edu_status is U.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Unexpected error in Update_Employment_Info. l_status is U.';
ELSE --Remove current person from the insert list
l_cur_prgm_rec(l_current).person_id := NULL;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'inserting prgm info.';
Insert_Edu_Info ('PRGM', p_data_rec => l_cur_prgm_rec(l_current), p_auth_drp_data_rec => l_cur_authdrp_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'Returning S from Update_EV_Program_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_EV_Program_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_EV_Program_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_EV_Program_Info. '||SQLERRM;
END Update_EV_Program_Info;
Purpose : Update Finance Information block on student
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Finance_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Finance_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'Entering Update_Finance_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'Returning S from Update_Finance_Info. p_person_rec.fin_status is E';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'Raise FND_API.G_EXC_ERROR in Update_Finance_Info. fin_status is U';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'Raise FND_API.G_EXC_ERROR in Update_Finance_Info. fin_status is N';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'Raise FND_API.G_EXC_ERROR in Update_Finance_Info. l_status is U or N';
Insert_Finance_Info ( p_data_rec => l_cur_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'Returning S from Update_Finance_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_Finance_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Finance_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Finance_Info. '||SQLERRM;
END Update_Finance_Info;
Purpose : Update Dependent block information on the student
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Dependent_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Dependent_Info';
SELECT COUNT(1)
FROM igs_sv_depdnt_info cr,
igs_sv_persons pr
WHERE cr.person_id = p_person_rec.person_id
AND pr.record_status <> 'E'
AND cr.person_id = pr.person_id
AND cr.relationship = 'SPOUSE_OF';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Entering Update_Dependent_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Returning S from Update_Dependent_Info. dep_status is E.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Raise FND_API.G_EXC_ERROR in Update_Dependent_Info. dep_status is U.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Returning S from Update_Dependent_Info. dep_status is N.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Raise FND_API.G_EXC_ERROR in Update_Dependent_Info. l_status is U.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Returning S from Update_Dependent_Info. l_status is S.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Update_Dependent_Info. l_prev_rec.depdnt_action_type: '||l_prev_rec.depdnt_action_type||'cur dep action type: '||l_cur_rec(l_current).depdnt_action_type;
l_cur_rec(l_current).depdnt_action_type := 'U'; --update mode for the person
ELSE --Remove current dependent from the insert list
l_cur_rec(l_current).depdnt_id := NULL;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Exiting Update_Dependent_Info. dep_status and record_status is C.';
Insert_Dependent_Info ( p_data_rec => l_cur_rec,p_records => l_count );
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'Returning S from Update_Dependent_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_Dependent_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Dependent_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Dependent_Info. '||SQLERRM;
END Update_Dependent_Info;
Purpose : Update Employment information block
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Employment_Info (
p_person_rec IN OUT NOCOPY t_student_rec -- Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Employment_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Entering Update_Employment_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Returning S from Update_Employment_Info. empl_status is E.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Unexpected error in Update_Employment_Info. empl_status is U.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Returning S from Update_Employment_Info. empl_status is N.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Unexpected error in Update_Employment_Info. l_status is U.';
ELSE --Remove current person from the insert list
l_cur_rec(l_current).person_id := NULL;
Insert_Empl_Info ( p_data_rec => l_cur_rec(l_current));
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'Returning S from Update_Employment_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'ND_API.G_EXC_ERROR: Returning U from Update_Employment_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Employment_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Employment_Info. '||SQLERRM;
END Update_Employment_Info;
Purpose : Update Conviction block information.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
FUNCTION Update_Conviction_Info (
p_person_rec IN OUT NOCOPY t_student_rec --Person record
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Conviction_Info';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'Entering Update_Conviction_Info. p_person_rec.person_id is '||p_person_rec.person_id;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'Returning S from Update_Conviction_Info. conv_status is E.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'Unexpected error in Update_Conviction_Info. conv_status is U.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'Returning S from Update_Conviction_Info. conv_status is N.';
Insert_Convictions_Info ( p_data_rec => l_cur_rec);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'Returning S from Update_Conviction_Info.';
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'FND_API.G_EXC_ERROR: Returning U from Update_Conviction_Info. '||SQLERRM;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Update_Conviction_Info';
l_debug_str := 'EXCEPTION: Returning U from Update_Conviction_Info. '||SQLERRM;
END Update_Conviction_Info;
DELETE FROM igs_sv_addresses WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
DELETE FROM igs_sv_addresses WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
DELETE FROM igs_sv_bio_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_convictions WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_depdnt_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_depdnt_info WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_empl_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_empl_info WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_finance_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_legal_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_oth_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_prgms_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_prgms_info WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
DELETE FROM igs_sv_persons WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
DELETE FROM igs_sv_persons WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
DELETE FROM igs_sv_btch_summary WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ); -- prbhardw
DELETE FROM igs_sv_btch_summary WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ); -- prbhardw
SELECT old_per.no_show_flag
FROM igs_sv_persons new_per, igs_sv_persons old_per
WHERE new_per.person_id = c_person_id AND
new_per.batch_id = c_batch_id AND
old_per.batch_id = (SELECT max(batch_id) FROM igs_sv_persons WHERE batch_id < c_batch_id) AND
old_per.person_id = new_per.person_id;
DELETE FROM igs_sv_batches WHERE batch_id = p_batch_id;
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_SCHOOL_ORG_ID ,
SEVIS_USER_PERSON_ID
)
VALUES
( igs_sv_batches_id_s.nextval,
1,
p_dso_id,
p_org_id,
'S',
p_batch_type,
sysdate,
g_update_by,
g_update_by,
sysdate,
g_update_login,
p_org_party_id,
p_user_party_id
)
RETURNING batch_id INTO l_batch_id;
Added the code for inserting Site of Activity Address
------------------------------------------------------------------------
mmkumar 12-Sep-2005 SEVIS 5 uptake, Added new parameter for Org ID
******************************************************************/
PROCEDURE Create_Batch(
errbuf OUT NOCOPY VARCHAR2, -- Request standard error string
retcode OUT NOCOPY NUMBER , -- Request standard return status
p_batch_type IN VARCHAR2, -- Batch type E(ev), I(international), B (both)
p_validate_only IN VARCHAR2, -- Validate only flag 'Y' 'N'
p_org_id IN VARCHAR2,
p_dso_id IN VARCHAR2,
p_dso_party_id IN NUMBER,
p_org_party_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(25) := 'Create_Batch';
SELECT fr.person_id, min(ev_form_id) form_id, pr.party_number person_number, fr.no_show_flag, fr.reprint_reason reprint_reason
FROM igs_pe_ev_form fr, hz_parties pr, igs_pe_alt_pers_id alt
WHERE pr.party_id = fr.person_id AND fr.form_effective_date <= trunc(sysdate)
AND fr.ev_form_id NOT IN
(SELECT st.ev_form_id FROM igs_pe_ev_form_stat st
WHERE st.action_type IN ('TR','ED')
AND st.ev_form_id = fr.ev_form_id
AND st.ev_form_stat_id IN
( SELECT NVL(prs.form_status_id,0) FROM igs_sv_prgms_info prs, igs_sv_persons pr
WHERE prs.person_id = pr.person_id AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E' AND prs.person_id = fr.person_id)
) AND fr.sevis_school_identifier = p_org_party_id AND
(
p_dso_id IS NULL OR
fr.person_id IN (SELECT rel.object_id FROM hz_relationships rel, igs_pe_alt_pers_id alt
WHERE rel.subject_id = alt.pe_person_id and
alt.api_person_id = p_dso_id and
alt.person_id_type IN (SELECT person_id_type FROM igs_pe_person_id_typ WHERE s_person_id_type = 'SEVIS_ID')
AND rel.DIRECTIONAL_FLAG = 'F' AND sysdate between rel.start_date AND nvl(end_date, sysdate)
AND rel.RELATIONSHIP_CODE = 'NEXT_OF_KIN_OF')
)
GROUP BY pr.party_number,fr.person_id, fr.no_show_flag, fr.reprint_reason;
SELECT fr.person_id, min(nonimg_form_id) form_id, pr.party_number person_number, null no_show_flag, fr.reprint_reason reprint_reason
FROM igs_pe_nonimg_form fr, hz_parties pr
WHERE pr.party_id = fr.person_id
AND fr.form_effective_date <= trunc(sysdate)
AND fr.nonimg_form_id NOT IN
( SELECT st.nonimg_form_id FROM IGS_PE_NONIMG_STAT st
WHERE st.action_type IN ('T','C')
AND st.nonimg_form_id = fr.nonimg_form_id
AND st.nonimg_stat_id IN
( SELECT NVL(prs.form_status_id,0)
FROM igs_sv_prgms_info prs, igs_sv_persons pr
WHERE prs.person_id = pr.person_id AND prs.batch_id = pr.batch_id
AND pr.record_status <> 'E' AND prs.person_id = fr.person_id
) )
AND fr.sevis_school_identifier = p_org_party_id AND
( p_dso_id IS NULL OR
fr.person_id IN (SELECT rel.object_id
FROM hz_relationships rel, igs_pe_alt_pers_id alt
WHERE rel.subject_id = alt.pe_person_id and
alt.api_person_id = p_dso_id and
alt.person_id_type IN (SELECT person_id_type FROM igs_pe_person_id_typ
WHERE s_person_id_type = 'SEVIS_ID') AND
rel.DIRECTIONAL_FLAG = 'F' AND
sysdate between rel.start_date AND nvl(end_date, sysdate) AND
rel.RELATIONSHIP_CODE = 'NEXT_OF_KIN_OF')
)
GROUP BY pr.party_number,fr.person_id, fr.reprint_reason;
SELECT person_party_id
FROM fnd_user
WHERE user_id = g_update_by;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_party_id;
SELECT 1
FROM igs_sv_persons a,
igs_sv_batches b
WHERE person_id = p_id
AND a.batch_id = b.batch_id
AND b.batch_type = p_batch_type
AND a.record_status <> 'E'
AND form_id = f_id;
SELECT batch_id
FROM igs_sv_batches
WHERE batch_status IN ('N') -- prbhardw
AND batch_type = p_batch_type;
SELECT decode(print_form,'Y','1','0') print_form
FROM igs_pe_nonimg_form
WHERE nonimg_form_id = p_form_id
AND p_batch_type = 'I'
UNION
SELECT decode(print_form,'Y','1','0') print_form
FROM igs_pe_ev_form
WHERE ev_form_id = p_form_id
AND p_batch_type = 'E';
SELECT decode(print_flag,'Y','1','0') print_form
FROM igs_pe_nonimg_stat
WHERE nonimg_form_id = p_form_id
AND p_batch_type = 'I'
AND action_type = 'E';
SELECT alt.api_person_id
FROM hz_relationships rel, igs_pe_alt_pers_id alt
WHERE rel.subject_id = c_person_id and
rel.object_id = alt.pe_person_id AND
sysdate between rel.start_date AND nvl(end_date, sysdate) AND
rel.RELATIONSHIP_CODE = 'NEXT_OF_KIN_FOR' AND
alt.person_id_type
IN (SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = 'SEVIS_ID');
g_update_login := FND_GLOBAL.LOGIN_ID;
g_update_by := FND_GLOBAL.USER_ID;
Put_Log_Msg('Inserting batch record',0);
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_SCHOOL_ORG_ID,
SEVIS_USER_PERSON_ID
)
VALUES
( igs_sv_batches_id_s.nextval,
1,
l_person_sevis_id,
p_org_id,
'S',
p_batch_type,
sysdate,
g_update_by,
g_update_by,
sysdate,
g_update_login,
p_org_party_id,
l_sevis_user_person_id
)
RETURNING batch_id INTO l_batch_id;
g_running_update_batch := l_batch_id; -- prbhardw CP enhancement
IF g_create_count = 250 OR g_update_count = 250 THEN
l_batch_id := new_batch(l_batch_id, l_person_sevis_id, p_org_id, p_batch_type,p_org_party_id,l_sevis_user_person_id);
g_running_update_batch := l_batch_id;
g_running_batches(g_running_batches.COUNT + 1) := g_running_update_batch;
FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_UPD'); -- Student is found in Update mode
l_debug_str := 'Inserting in igs_sv_persons batch id: '||l_batch_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,
creation_date,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login,
no_show_flag,
reprint_rsn_code,
PDSO_SEVIS_PERSON_ID
) VALUES (
l_batch_id,
l_student_rec.person_id,
l_student_rec.record_number,
l_student_rec.form_id,
l_student_rec.print_form,
dso_id,
l_student_rec.record_status,
l_student_rec.person_number,
l_student_rec.sevis_user_id,
sysdate,
g_update_by,
g_update_by,
sysdate,
g_update_login,
l_student_rec.no_show_flag,
l_student_rec.reprint_reason,
p_dso_party_id
);
Put_Log_Msg(' Inserting data ',0);
Update_Issue_Info ( p_data_rec => l_issue_rec);
Insert_Bio_Info ( p_data_rec => l_bio_rec);
Insert_Other_Info ( p_data_rec => l_oth_rec);
Insert_Edu_Info ('EDU', p_data_rec => l_edu_rec, p_auth_drp_data_rec => l_cur_authdrp_rec);
Insert_Address_Info ('F', p_data_rec => l_f_addr_rec,p_records => 1); --- prbhardw CP enhancement
Insert_Address_Info ( 'US',p_data_rec => l_us_addr_rec,p_records => 1); --- prbhardw CP enhancement
Insert_Address_Info ('SOA', p_data_rec => l_site_addr_rec,p_records => l_soa_count); --- prbhardw CP enhancement
Insert_Finance_Info ( p_data_rec => l_fin_rec);
Insert_Dependent_Info ( p_data_rec => l_dep_rec,p_records => l_student_rec.dep_count );
Put_Log_Msg(' No insertion, person_status is:'||l_student_rec.person_status ,0);
END IF; -- Insertion ends here
g_update_count := g_update_count +1; -- prbhardw CP enhancement
l_student_rec.record_number := g_update_count; -- prbhardw CP enhancement
IF p_batch_type = 'I' THEN -- If mode update for Non immigrants
-- IF l_student_rec.changes_found <> 'Y' THEN --- commented by prbhardw
-- Call Update_Employment_Info
l_status := Update_Employment_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Employment_Info is not S';
-- Call Update_Registration_Info
l_status := Update_Registration_Info ( p_person_rec => l_student_rec );
l_status := Update_Personal_Info ( p_person_rec => l_student_rec );
-- Call Update_Program_Info
l_status := Update_Program_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Program_Info is not S';
-- Call Update_Finance_Info
l_status := Update_Finance_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Finance_Info is not S';
-- Call Update_Dependent_Info
l_status := Update_Dependent_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Dependent_Info is not S';
-- Call Update_Conviction_Info
l_status := Update_Conviction_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Conviction_Info is not S';
ELSE -- Update for EV student
-------
IF l_student_rec.no_show_flag = 'Y' THEN
IF Get_pre_noshow_status(l_student_rec.person_id,l_student_rec.batch_id) = 'Y' THEN
l_student_rec.changes_found := 'N';
Insert_Summary_Info(l_btch_id,
l_student_rec.person_id,
'NOSHOW',
'SV_STATUS',
'SEND',
'IGS_SV_PERSONS',
'');
l_status := Update_Personal_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Personal_Info is not S';
l_status := Update_ev_legal_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. Update_ev_legal_Info status is not S';
-- Call Update_Finance_Info
l_status := Update_Finance_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Finance_Info is not S';
-- Call Update_EV_Program_Info
l_status := Update_EV_Program_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_EV_Program_Info is not S';
-- Call Update_EV_Address_Info
l_status := Update_EV_Address_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_EV_Address_Info is not S';
-- Call Update_Dependent_Info
l_status := Update_Dependent_Info ( p_person_rec => l_student_rec );
l_debug_str := 'ERROR in Create_Batch. l_status from Update_Dependent_Info is not S';
DELETE FROM igs_sv_batches WHERE batch_id = l_batch_id;
DELETE FROM igs_sv_batches WHERE batch_id = l_batch_id;
DELETE FROM igs_sv_batches WHERE batch_id > l_batch_id;
SELECT batch_id
FROM igs_sv_batches
WHERE batch_status IN ('S','N')
AND batch_type = p_batch_type;
DELETE FROM igs_sv_batches WHERE batch_id = l_batch_id;
DELETE FROM igs_sv_batches WHERE batch_id > l_batch_id;
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = g_person_sevis_id AND
closed_ind = 'N';
UPDATE igs_sv_persons
SET record_status = 'E',
sevis_error_code = p_SEVIS_ErrorCode,
sevis_error_element = p_SEVIS_ErrorElement
WHERE batch_id = p_BatchID
AND person_id = p_person_id;
UPDATE igs_pe_alt_pers_id
SET end_dt = trunc(sysdate)
WHERE pe_person_id = p_person_id
AND person_id_type
IN (SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = g_person_sevis_id)
AND start_dt <= trunc(sysdate)
AND NVL(end_dt,sysdate+1) >= trunc(sysdate) ;
IGS_PE_ALT_PERS_ID_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_PE_PERSON_ID => p_person_id,
X_API_PERSON_ID => p_sevisID,
X_API_PERSON_ID_UF => p_sevisID,
X_PERSON_ID_TYPE => l_alt_id,
X_START_DT => trunc(sysdate),
X_END_DT => NULL,
X_attribute_category => '',
X_attribute1 => '',
X_attribute2 => '',
X_attribute3 => '',
X_attribute4 => '',
X_attribute5 => '',
X_attribute6 => '',
X_attribute7 => '',
X_attribute8 => '',
X_attribute9 => '',
X_attribute10 => '',
X_attribute11 => '',
X_attribute12 => '',
X_attribute13 => '',
X_attribute14 => '',
X_attribute15 => '',
X_attribute16 => '',
X_attribute17 => '',
X_attribute18 => '',
X_attribute19 => '',
X_attribute20 => '',
X_MODE => 'I'
);
SELECT batch_status
FROM igs_sv_batches
WHERE batch_id = p_BatchID;
UPDATE igs_sv_batches SET batch_status = 'P' WHERE batch_id = p_BatchID;
UPDATE igs_sv_batches SET batch_status = 'E', SEVIS_ERROR_CODE = NVL(p_FileErrorCode,NVL(p_FileValidation,'X')) WHERE batch_id = p_BatchID;
update igs_sv_persons set SEVIS_USER_ID = p_sevisID ,
SEVIS_ERROR_CODE = p_SEVIS_ErrorCode, SEVIS_ERROR_ELEMENT = p_SEVIS_ErrorElement
where person_id = p_PersonID;
SELECT '1'
FROM IGS_SV_DEPDNT_INFO
WHERE PERSON_ID = p_PersonID and DEPDNT_ID = p_DepPersonID;
UPDATE IGS_SV_DEPDNT_INFO SET DEPDNT_SEVIS_ID = p_DepSevisID WHERE PERSON_ID = p_PersonID and DEPDNT_ID = p_DepPersonID;
Purpose : Insert batch summary into igs_sv_btch_summary before inserting in interface tables.
Change History
Who When What
------------------------------------------------------------------------
******************************************************************/
PROCEDURE Insert_Summary_Info(
p_batch_id IN igs_sv_btch_summary.batch_id%TYPE,
p_person_id IN igs_sv_btch_summary.person_id%TYPE,
p_action_code IN igs_sv_btch_summary.action_code%TYPE,
p_tag_code IN igs_sv_btch_summary.tag_code%TYPE,
p_adm_action IN igs_sv_btch_summary.adm_action_code%TYPE,
p_owner_table_name IN igs_sv_btch_summary.owner_table_name%TYPE,
p_owner_table_id IN igs_sv_btch_summary.OWNER_TABLE_IDENTIFIER%TYPE
)
IS
l_api_name CONSTANT VARCHAR(30) := 'Insert_Summary_Info';
SELECT max(batch_id) FROM IGS_SV_BTCH_SUMMARY;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Summary_Info';
l_debug_str := 'Entering Insert_Summary_Info. p_data_rec.person_id is '||p_person_id|| ' and batch_id is '||p_batch_id;
INSERT INTO IGS_SV_BTCH_SUMMARY (
summary_id ,
batch_id ,
person_id ,
action_code ,
tag_code ,
adm_action_code ,
creation_date ,
created_by ,
last_updated_by ,
last_update_date ,
last_update_login ,
owner_table_name,
OWNER_TABLE_IDENTIFIER --mmkumar, owner_table_id
) VALUES
(
IGS_SV_BTCH_SUMM_ID_S.nextval ,
p_batch_id ,
p_person_id ,
p_action_code ,
p_tag_code ,
p_adm_action ,
sysdate,
g_update_by,
g_update_by,
sysdate,
g_update_login,
p_owner_table_name,
p_owner_table_id
);
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Summary_Info';
l_debug_str := 'record in Insert_Summary_Info max batch_id: '||l_batch;
l_label := 'igs.plsql.igs_sv_ni_batch_process_pkg.Insert_Summary_Info';
l_debug_str := 'EXCEPTION in Insert_Summary_Info. '||SQLERRM;
END Insert_Summary_Info;
SELECT batch_type
FROM igs_sv_batches
WHERE batch_id = cp_batch_id;
SELECT COUNT(DISTINCT person_id)
FROM igs_sv_btch_summary
WHERE batch_id = cp_batch_id;
SELECT hz.party_number prsn_num, lkp.meaning info
FROM igs_sv_btch_summary svbs, hz_parties hz, IGS_LOOKUP_VALUES lkp
WHERE svbs.batch_id = cp_batch_id
AND svbs.action_code = 'NEW'
AND svbs.person_id = hz.party_id
AND svbs.TAG_CODE = lkp.lookup_code
AND lkp.lookup_type ='IGS_SV_COMP_TREE';
CURSOR c_get_updated_persons(cp_batch_id igs_sv_btch_summary.batch_id%TYPE)
IS
SELECT hz.party_number prsn_num, lkp.meaning info
FROM igs_sv_btch_summary svbs, hz_parties hz, IGS_LOOKUP_VALUES lkp
WHERE svbs.batch_id = cp_batch_id
AND svbs.action_code <> 'NEW'
AND svbs.person_id = hz.party_id
AND svbs.TAG_CODE = lkp.lookup_code
AND lkp.lookup_type ='IGS_SV_COMP_TREE'
AND lkp.enabled_flag = 'Y';
FOR updated_persons IN c_get_updated_persons(g_parallel_batches(i)) LOOP
Put_Log_Msg(' ' || rpad(updated_persons.prsn_num,30,' ') || ' ' || updated_persons.info,0);
FOR updated_persons IN c_get_updated_persons(g_running_batches(i)) LOOP
Put_Log_Msg(' ' || rpad(updated_persons.prsn_num,30,' ') || ' ' || updated_persons.info,0);