APPS.IGS_EN_SEVIS dependencies on IGS_EN_SVS_AUTH

Line 41: SELECT 'X' FROM igs_en_svs_auth esa

37: -- Cursor to check whether authorisation exists or not.
38: CURSOR c_authorisation_exists (cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
39: cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
40: cp_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE) IS
41: SELECT 'X' FROM igs_en_svs_auth esa
42: WHERE person_id = cp_person_id
43: AND NVL(esa.end_dt, SYSDATE) >= SYSDATE
44: AND exists (select 'x'
45: from igs_en_svs_auth_cal sac

Line 45: from igs_en_svs_auth_cal sac

41: SELECT 'X' FROM igs_en_svs_auth esa
42: WHERE person_id = cp_person_id
43: AND NVL(esa.end_dt, SYSDATE) >= SYSDATE
44: AND exists (select 'x'
45: from igs_en_svs_auth_cal sac
46: where sac.sevis_auth_id = esa.sevis_auth_id
47: and cal_type = cp_cal_type
48: and ci_sequence_number = cp_ci_sequence_number);
49:

Line 98: FROM igs_en_svs_auth auth,

94:
95: -- Cursor to get the authorized persons for the passed calander.
96: CURSOR c_auth_person(l_cal_type igs_ca_inst_all.cal_type%TYPE, l_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
97: SELECT person_id, SEVIS_AUTHORIZATION_CODE
98: FROM igs_en_svs_auth auth,
99: igs_en_svs_auth_cal sac
100: WHERE auth.sevis_auth_id = sac.sevis_auth_id
101: AND sac.cal_type = l_cal_type
102: AND sac.ci_sequence_number = l_sequence_number;

Line 99: igs_en_svs_auth_cal sac

95: -- Cursor to get the authorized persons for the passed calander.
96: CURSOR c_auth_person(l_cal_type igs_ca_inst_all.cal_type%TYPE, l_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
97: SELECT person_id, SEVIS_AUTHORIZATION_CODE
98: FROM igs_en_svs_auth auth,
99: igs_en_svs_auth_cal sac
100: WHERE auth.sevis_auth_id = sac.sevis_auth_id
101: AND sac.cal_type = l_cal_type
102: AND sac.ci_sequence_number = l_sequence_number;
103:

Line 616: FROM IGS_EN_SVS_AUTH_CAL

612: p_ci_sequence_number IN NUMBER) AS
613:
614: CURSOR c_auth_cal_exists IS
615: SELECT 'X'
616: FROM IGS_EN_SVS_AUTH_CAL
617: WHERE SEVIS_AUTH_ID = p_sevis_auth_id
618: AND cal_type = p_cal_type
619: AND ci_sequence_number = p_ci_sequence_number;
620:

Line 635: igs_en_svs_auth_cal_pkg.insert_row (

631: RETURN;
632:
633: ELSE
634: CLOSE c_auth_cal_exists;
635: igs_en_svs_auth_cal_pkg.insert_row (
636: x_mode => 'R',
637: x_rowid => lv_rowid,
638: x_sevis_auth_id => p_sevis_auth_id,
639: x_cal_type => p_cal_type,

Line 659: l_sevis_authorization_no igs_en_svs_auth.SEVIS_AUTHORIZATION_NO%TYPE;

655: p_cancel_flag IN VARCHAR2) AS
656:
657:
658: lv_rowid VARCHAR2(25) ;
659: l_sevis_authorization_no igs_en_svs_auth.SEVIS_AUTHORIZATION_NO%TYPE;
660:
661: CURSOR c_authorization_exists IS
662: SELECT sevis_auth_id
663: FROM igs_en_svs_auth

Line 663: FROM igs_en_svs_auth

659: l_sevis_authorization_no igs_en_svs_auth.SEVIS_AUTHORIZATION_NO%TYPE;
660:
661: CURSOR c_authorization_exists IS
662: SELECT sevis_auth_id
663: FROM igs_en_svs_auth
664: WHERE person_id = p_person_id
665: AND sevis_authorization_code = p_sevis_authorization_code
666: AND start_dt = p_start_dt
667: AND NVL(cancel_flag,'N') = 'N';

Line 684: igs_en_svs_auth_pkg.insert_row (

680: ELSE
681:
682: CLOSE c_authorization_exists;
683:
684: igs_en_svs_auth_pkg.insert_row (
685: x_mode => 'R',
686: x_rowid => lv_rowid,
687: x_sevis_authorization_code => p_sevis_authorization_code,
688: x_start_dt => p_start_dt,

Line 771: l_prgm_start_date IGS_EN_SVS_AUTH.START_DT%TYPE;

767: l_ci_start_Dt DATE;
768: l_ci_end_dt DATE;
769: l_duration_dt DATE;
770: l_temp_date DATE;
771: l_prgm_start_date IGS_EN_SVS_AUTH.START_DT%TYPE;
772: l_prgm_end_date IGS_EN_SVS_AUTH.END_DT%TYPE;
773:
774: l_interval INTERVAL YEAR TO MONTH;
775:

Line 772: l_prgm_end_date IGS_EN_SVS_AUTH.END_DT%TYPE;

768: l_ci_end_dt DATE;
769: l_duration_dt DATE;
770: l_temp_date DATE;
771: l_prgm_start_date IGS_EN_SVS_AUTH.START_DT%TYPE;
772: l_prgm_end_date IGS_EN_SVS_AUTH.END_DT%TYPE;
773:
774: l_interval INTERVAL YEAR TO MONTH;
775:
776: BEGIN

Line 856: FROM igs_en_svs_auth F,

852: FUNCTION is_auth_records_overlap(p_person_id IN NUMBER) RETURN BOOLEAN IS
853:
854: CURSOR c_chk_for_auth_rec_overlap (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
855: SELECT 'x'
856: FROM igs_en_svs_auth F,
857: igs_en_svs_auth S
858: WHERE f.person_id =cp_person_id
859: AND s.person_id =cp_person_id
860: AND f.ROWID <> s.ROWID

Line 857: igs_en_svs_auth S

853:
854: CURSOR c_chk_for_auth_rec_overlap (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
855: SELECT 'x'
856: FROM igs_en_svs_auth F,
857: igs_en_svs_auth S
858: WHERE f.person_id =cp_person_id
859: AND s.person_id =cp_person_id
860: AND f.ROWID <> s.ROWID
861: AND f.end_dt BETWEEN s.start_dt AND s.end_dt;