[Home] [Help]
62: g_legal_status VARCHAR2(6) := 'NEW';
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;
966: IS
967:
968: CURSOR c_dep_exist IS
969: SELECT COUNT('X')
970: FROM igs_sv_depdnt_info
971: WHERE person_id = p_person_id
972: AND depdnt_id = p_depdnt_id
973: AND batch_id < p_batch_id;
974:
4525: Date Created By : Oct 14, 2002
4526:
4527: Purpose : Validate information pertaining to the
4528: dependent information of the student.
4529: (IGS_SV_DEPDNT_INFO)
4530:
4531: Remarks : Return result:
4532: 'S' - record found and validated
4533: 'E' - validation error
6507:
6508: Date Created By : Oct 14, 2002
6509:
6510: Purpose : Get the student dependent information.
6511: (IGS_SV_DEPDNT_INFO)
6512:
6513: Remarks :
6514:
6515: Change History
6517: ------------------------------------------------------------------------
6518:
6519: ******************************************************************/
6520: FUNCTION Get_Dependent_Info (
6521: p_data_rec IN OUT NOCOPY IGS_SV_DEPDNT_INFO%ROWTYPE -- Data record
6522: ) RETURN VARCHAR2 -- 'S' Record found, 'N' - not found. 'U' - Unexpected error
6523: IS
6524:
6525: l_api_name CONSTANT VARCHAR2(25) := 'Get_Dependent_Info';
6542: termination_effect_date,
6543: remarks ,
6544: birth_cntry_resn_code ,
6545: VISA_TYPE
6546: FROM igs_sv_depdnt_info cr
6547: WHERE cr.person_id = p_data_rec.person_id
6548: AND cr.depdnt_id = p_data_rec.depdnt_id
6549: AND cr.batch_id IN
6550: ( SELECT max(prs.batch_id)
6547: WHERE cr.person_id = p_data_rec.person_id
6548: AND cr.depdnt_id = p_data_rec.depdnt_id
6549: AND cr.batch_id IN
6550: ( SELECT max(prs.batch_id)
6551: FROM igs_sv_depdnt_info prs,
6552: igs_sv_persons pr
6553: WHERE prs.person_id = pr.person_id
6554: AND prs.batch_id = pr.batch_id
6555: AND pr.record_status <> 'E'
7627:
7628: Date Created By : Oct 14, 2002
7629:
7630: Purpose : Insert dependent information on the student
7631: (IGS_SV_DEPDNT_INFO).
7632:
7633: Remarks :
7634:
7635: Change History
7686: p_data_rec(l_count).person_id,
7687: l_action,
7688: 'SV_DEPDNT',
7689: 'SEND',
7690: 'IGS_SV_DEPDNT_INFO',
7691: p_data_rec(l_count).depdnt_id);
7692:
7693: INSERT INTO igs_sv_depdnt_info (
7694: batch_id ,
7689: 'SEND',
7690: 'IGS_SV_DEPDNT_INFO',
7691: p_data_rec(l_count).depdnt_id);
7692:
7693: INSERT INTO igs_sv_depdnt_info (
7694: batch_id ,
7695: person_id ,
7696: depdnt_id ,
7697: print_form ,
7771: END Insert_Dependent_Info;
7772:
7773:
7774: PROCEDURE Insert_Dependent_Info (
7775: p_data_rec IN IGS_SV_DEPDNT_INFO%ROWTYPE --g_dependent_rec_type, -- Data record
7776: -- p_records IN NUMBER --Number of dependents found
7777: )
7778: IS
7779:
7819: p_data_rec.person_id,
7820: l_action,
7821: 'SV_DEPDNT',
7822: 'SEND',
7823: 'IGS_SV_DEPDNT_INFO',
7824: p_data_rec.depdnt_id);
7825:
7826: INSERT INTO igs_sv_depdnt_info (
7827: batch_id ,
7822: 'SEND',
7823: 'IGS_SV_DEPDNT_INFO',
7824: p_data_rec.depdnt_id);
7825:
7826: INSERT INTO igs_sv_depdnt_info (
7827: batch_id ,
7828: person_id ,
7829: depdnt_id ,
7830: print_form ,
10573: IS
10574:
10575: l_api_name CONSTANT VARCHAR2(30) := 'Update_Dependent_Info';
10576: l_cur_rec g_dependent_rec_type;
10577: l_prev_rec IGS_SV_DEPDNT_INFO%ROWTYPE;
10578: l_status VARCHAR2(1);
10579: l_count NUMBER(10);
10580: l_current NUMBER(10);
10581: l_rel_type_count NUMBER := 0;
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 ) ;
11295:
11296: DELETE FROM igs_sv_finance_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
13293: l_temp VARCHAR2(1);
13294:
13295: CURSOR c_dep IS
13296: SELECT '1'
13297: FROM IGS_SV_DEPDNT_INFO
13298: WHERE PERSON_ID = p_PersonID and DEPDNT_ID = p_DepPersonID;
13299:
13300:
13301: BEGIN
13334: p_SEVIS_ErrorCode => p_SEVIS_ErrorCode,
13335: p_SEVIS_ErrorElement => p_SEVIS_ErrorElement
13336: );
13337:
13338: UPDATE IGS_SV_DEPDNT_INFO SET DEPDNT_SEVIS_ID = p_DepSevisID WHERE PERSON_ID = p_PersonID and DEPDNT_ID = p_DepPersonID;
13339:
13340: EXCEPTION
13341:
13342: WHEN FND_API.G_EXC_ERROR THEN