[Home] [Help]
61: g_person_status VARCHAR2(6) := 'NEW';
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;
3601: Date Created By : Oct 14, 2002
3602:
3603: Purpose : Validate information pertaining to the
3604: dependent information of the student.
3605: (IGS_SV_DEPDNT_INFO)
3606:
3607: Remarks : Return result:
3608: 'S' - record found and validated
3609: 'E' - validation error
5549:
5550: Date Created By : Oct 14, 2002
5551:
5552: Purpose : Get the student dependent information.
5553: (IGS_SV_DEPDNT_INFO)
5554:
5555: Remarks :
5556:
5557: Change History
5559: ------------------------------------------------------------------------
5560:
5561: ******************************************************************/
5562: FUNCTION Get_Dependent_Info (
5563: p_data_rec IN OUT NOCOPY IGS_SV_DEPDNT_INFO%ROWTYPE -- Data record
5564: ) RETURN VARCHAR2 -- 'S' Record found, 'N' - not found. 'U' - Unexpected error
5565: IS
5566:
5567: l_api_name CONSTANT VARCHAR2(25) := 'Get_Dependent_Info';
5584: termination_effect_date,
5585: remarks ,
5586: birth_cntry_resn_code ,
5587: VISA_TYPE
5588: FROM igs_sv_depdnt_info cr
5589: WHERE cr.person_id = p_data_rec.person_id
5590: AND cr.depdnt_id = p_data_rec.depdnt_id
5591: AND cr.batch_id IN
5592: ( SELECT max(prs.batch_id)
5589: WHERE cr.person_id = p_data_rec.person_id
5590: AND cr.depdnt_id = p_data_rec.depdnt_id
5591: AND cr.batch_id IN
5592: ( SELECT max(prs.batch_id)
5593: FROM igs_sv_depdnt_info prs,
5594: igs_sv_persons pr
5595: WHERE prs.person_id = pr.person_id
5596: AND prs.batch_id = pr.batch_id
5597: AND pr.record_status <> 'E'
6685:
6686: Date Created By : Oct 14, 2002
6687:
6688: Purpose : Insert dependent information on the student
6689: (IGS_SV_DEPDNT_INFO).
6690:
6691: Remarks :
6692:
6693: Change History
6742: p_data_rec(l_count).person_id,
6743: l_action,
6744: 'SV_DEPDNT',
6745: 'SEND',
6746: 'IGS_SV_DEPDNT_INFO',
6747: p_data_rec(l_count).depdnt_id);
6748:
6749: INSERT INTO igs_sv_depdnt_info (
6750: batch_id ,
6745: 'SEND',
6746: 'IGS_SV_DEPDNT_INFO',
6747: p_data_rec(l_count).depdnt_id);
6748:
6749: INSERT INTO igs_sv_depdnt_info (
6750: batch_id ,
6751: person_id ,
6752: depdnt_id ,
6753: print_form ,
9448: IS
9449:
9450: l_api_name CONSTANT VARCHAR2(30) := 'Update_Dependent_Info';
9451: l_cur_rec g_dependent_rec_type;
9452: l_prev_rec IGS_SV_DEPDNT_INFO%ROWTYPE;
9453: l_status VARCHAR2(1);
9454: l_count NUMBER(10);
9455: l_current NUMBER(10);
9456: l_rel_type_count NUMBER := 0;
9456: l_rel_type_count NUMBER := 0;
9457:
9458: CURSOR c_spouse_of_rel IS
9459: SELECT COUNT(1)
9460: FROM igs_sv_depdnt_info cr,
9461: igs_sv_persons pr
9462: WHERE cr.person_id = p_person_rec.person_id
9463: AND pr.record_status <> 'E'
9464: AND cr.person_id = pr.person_id
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 ) ;
10148:
10149: DELETE FROM igs_sv_empl_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
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 ) ;
10148:
10149: DELETE FROM igs_sv_empl_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
10150:
10151: DELETE FROM igs_sv_empl_info WHERE batch_id > p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
12116: l_temp VARCHAR2(1);
12117:
12118: CURSOR c_dep IS
12119: SELECT '1'
12120: FROM IGS_SV_DEPDNT_INFO
12121: WHERE PERSON_ID = p_PersonID and DEPDNT_ID = p_DepPersonID;
12122:
12123:
12124: BEGIN
12157: p_SEVIS_ErrorCode => p_SEVIS_ErrorCode,
12158: p_SEVIS_ErrorElement => p_SEVIS_ErrorElement
12159: );
12160:
12161: UPDATE IGS_SV_DEPDNT_INFO SET DEPDNT_SEVIS_ID = p_DepSevisID WHERE PERSON_ID = p_PersonID and DEPDNT_ID = p_DepPersonID;
12162:
12163: EXCEPTION
12164:
12165: WHEN FND_API.G_EXC_ERROR THEN