[Home] [Help]
63: g_nonimg_form_id NUMBER(15);
64: TYPE g_address_rec_type IS TABLE OF IGS_SV_ADDRESSES%ROWTYPE INDEX BY BINARY_INTEGER;
65: TYPE g_edu_rec_type IS TABLE OF IGS_SV_PRGMS_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
66: TYPE g_dependent_rec_type IS TABLE OF IGS_SV_DEPDNT_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
67: TYPE g_convictions_rec_type IS TABLE OF IGS_SV_CONVICTIONS%ROWTYPE INDEX BY BINARY_INTEGER;
68: TYPE g_empl_rec_type IS TABLE OF IGS_SV_EMPL_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
69: TYPE g_parallel_batches_tbl IS TABLE OF IGS_SV_BATCHES.BATCH_ID%TYPE INDEX BY BINARY_INTEGER;
70: TYPE g_running_batches_tbl IS TABLE OF IGS_SV_BATCHES.BATCH_ID%TYPE INDEX BY BINARY_INTEGER;
71:
4804: Date Created By : Oct 14, 2002
4805:
4806: Purpose : Validate information pertaining to the
4807: convictions associated to student.
4808: (IGS_SV_CONVICTIONS)
4809:
4810: Remarks : Return result:
4811: 'S' - record found and validated
4812: 'E' - validation error
4819:
4820: ******************************************************************/
4821: FUNCTION Validate_Convictions_Info (
4822: p_person_rec IN t_student_rec,
4823: p_data_rec IN OUT NOCOPY IGS_SV_CONVICTIONS%ROWTYPE -- Data record
4824: ) RETURN VARCHAR2
4825: IS
4826:
4827: l_api_name CONSTANT VARCHAR(30) := 'Validate_Convictions_Info';
4835: WHERE person_id = p_person_rec.person_id
4836: AND convict_ind ='Y'
4837: AND felony_details_id NOT IN
4838: ( SELECT conviction_id
4839: FROM igs_sv_convictions prg,
4840: igs_sv_persons pr
4841: WHERE prg.person_id = pr.person_id
4842: AND prg.batch_id = pr.batch_id
4843: AND pr.record_status <> 'E'
7907:
7908: Date Created By : Oct 14, 2002
7909:
7910: Purpose : Insert Conviction information on the student.
7911: (IGS_SV_CONVICTIONS).
7912:
7913: Remarks :
7914:
7915: Change History
7917: ------------------------------------------------------------------------
7918:
7919: ******************************************************************/
7920: PROCEDURE Insert_Convictions_Info (
7921: p_data_rec IN IGS_SV_CONVICTIONS%ROWTYPE
7922: ) IS
7923:
7924: l_api_name CONSTANT VARCHAR(30) := 'Insert_Convictions_Info';
7925: l_count NUMBER(10);
7935: p_data_rec.person_id,
7936: g_person_status,
7937: 'SV_CONVICTION',
7938: 'SEND',
7939: 'IGS_SV_CONVICTIONS',
7940: '');
7941: INSERT INTO igs_sv_convictions (
7942: batch_id ,
7943: person_id ,
7937: 'SV_CONVICTION',
7938: 'SEND',
7939: 'IGS_SV_CONVICTIONS',
7940: '');
7941: INSERT INTO igs_sv_convictions (
7942: batch_id ,
7943: person_id ,
7944: conviction_id ,
7945: print_form ,
11138:
11139: IS
11140:
11141: l_api_name CONSTANT VARCHAR2(30) := 'Update_Conviction_Info';
11142: l_cur_rec IGS_SV_CONVICTIONS%ROWTYPE;
11143: l_status VARCHAR2(1);
11144: l_prev_rec IGS_SV_CONVICTIONS%ROWTYPE;
11145:
11146: BEGIN
11140:
11141: l_api_name CONSTANT VARCHAR2(30) := 'Update_Conviction_Info';
11142: l_cur_rec IGS_SV_CONVICTIONS%ROWTYPE;
11143: l_status VARCHAR2(1);
11144: l_prev_rec IGS_SV_CONVICTIONS%ROWTYPE;
11145:
11146: BEGIN
11147:
11148: Put_Log_Msg(l_api_name||' begins ',0);
11286: DELETE FROM igs_sv_addresses WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
11287:
11288: DELETE FROM igs_sv_bio_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
11289:
11290: DELETE FROM igs_sv_convictions WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
11291:
11292: DELETE FROM igs_sv_depdnt_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
11293:
11294: DELETE FROM igs_sv_empl_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;