[Home] [Help]
62: g_nonimg_form_id NUMBER(15);
63: TYPE g_address_rec_type IS TABLE OF IGS_SV_ADDRESSES%ROWTYPE INDEX BY BINARY_INTEGER;
64: TYPE g_edu_rec_type IS TABLE OF IGS_SV_PRGMS_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
65: TYPE g_dependent_rec_type IS TABLE OF IGS_SV_DEPDNT_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
66: TYPE g_convictions_rec_type IS TABLE OF IGS_SV_CONVICTIONS%ROWTYPE INDEX BY BINARY_INTEGER;
67: TYPE g_empl_rec_type IS TABLE OF IGS_SV_EMPL_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
68: TYPE g_parallel_batches_tbl IS TABLE OF IGS_SV_BATCHES.BATCH_ID%TYPE INDEX BY BINARY_INTEGER;
69: TYPE g_running_batches_tbl IS TABLE OF IGS_SV_BATCHES.BATCH_ID%TYPE INDEX BY BINARY_INTEGER;
70:
3852: Date Created By : Oct 14, 2002
3853:
3854: Purpose : Validate information pertaining to the
3855: convictions associated to student.
3856: (IGS_SV_CONVICTIONS)
3857:
3858: Remarks : Return result:
3859: 'S' - record found and validated
3860: 'E' - validation error
3867:
3868: ******************************************************************/
3869: FUNCTION Validate_Convictions_Info (
3870: p_person_rec IN t_student_rec,
3871: p_data_rec IN OUT NOCOPY IGS_SV_CONVICTIONS%ROWTYPE -- Data record
3872: ) RETURN VARCHAR2
3873: IS
3874:
3875: l_api_name CONSTANT VARCHAR(30) := 'Validate_Convictions_Info';
3883: WHERE person_id = p_person_rec.person_id
3884: AND convict_ind ='Y'
3885: AND felony_details_id NOT IN
3886: ( SELECT conviction_id
3887: FROM igs_sv_convictions prg,
3888: igs_sv_persons pr
3889: WHERE prg.person_id = pr.person_id
3890: AND prg.batch_id = pr.batch_id
3891: AND pr.record_status <> 'E'
6831:
6832: Date Created By : Oct 14, 2002
6833:
6834: Purpose : Insert Conviction information on the student.
6835: (IGS_SV_CONVICTIONS).
6836:
6837: Remarks :
6838:
6839: Change History
6841: ------------------------------------------------------------------------
6842:
6843: ******************************************************************/
6844: PROCEDURE Insert_Convictions_Info (
6845: p_data_rec IN IGS_SV_CONVICTIONS%ROWTYPE
6846: ) IS
6847:
6848: l_api_name CONSTANT VARCHAR(30) := 'Insert_Convictions_Info';
6849: l_count NUMBER(10);
6859: p_data_rec.person_id,
6860: g_person_status,
6861: 'SV_CONVICTION',
6862: 'SEND',
6863: 'IGS_SV_CONVICTIONS',
6864: '');
6865: INSERT INTO igs_sv_convictions (
6866: batch_id ,
6867: person_id ,
6861: 'SV_CONVICTION',
6862: 'SEND',
6863: 'IGS_SV_CONVICTIONS',
6864: '');
6865: INSERT INTO igs_sv_convictions (
6866: batch_id ,
6867: person_id ,
6868: conviction_id ,
6869: print_form ,
9989:
9990: IS
9991:
9992: l_api_name CONSTANT VARCHAR2(30) := 'Update_Conviction_Info';
9993: l_cur_rec IGS_SV_CONVICTIONS%ROWTYPE;
9994: l_status VARCHAR2(1);
9995: l_prev_rec IGS_SV_CONVICTIONS%ROWTYPE;
9996:
9997: BEGIN
9991:
9992: l_api_name CONSTANT VARCHAR2(30) := 'Update_Conviction_Info';
9993: l_cur_rec IGS_SV_CONVICTIONS%ROWTYPE;
9994: l_status VARCHAR2(1);
9995: l_prev_rec IGS_SV_CONVICTIONS%ROWTYPE;
9996:
9997: BEGIN
9998:
9999: Put_Log_Msg(l_api_name||' begins ',0);
10139: DELETE FROM igs_sv_addresses WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
10140:
10141: DELETE FROM igs_sv_bio_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
10142:
10143: DELETE FROM igs_sv_convictions WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
10144:
10145: DELETE FROM igs_sv_depdnt_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
10146:
10147: DELETE FROM igs_sv_depdnt_info WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;