eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_SV_BATCH_PROCESS_PKG dependencies on IGS_SV_PERSONS

Line 85: ( person_id igs_sv_persons.person_id%TYPE,

81: reprint_reason igs_pe_ev_form.reprint_reason%TYPE
82: );
83:
84: TYPE t_student_rec IS RECORD
85: ( person_id igs_sv_persons.person_id%TYPE,
86: batch_id igs_sv_persons.batch_id%TYPE,
87: record_number igs_sv_persons.record_number%TYPE,
88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged
89: person_number igs_sv_persons.person_number%TYPE,

Line 86: batch_id igs_sv_persons.batch_id%TYPE,

82: );
83:
84: TYPE t_student_rec IS RECORD
85: ( person_id igs_sv_persons.person_id%TYPE,
86: batch_id igs_sv_persons.batch_id%TYPE,
87: record_number igs_sv_persons.record_number%TYPE,
88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged
89: person_number igs_sv_persons.person_number%TYPE,
90: sevis_user_id igs_sv_batches.sevis_user_id%TYPE,

Line 87: record_number igs_sv_persons.record_number%TYPE,

83:
84: TYPE t_student_rec IS RECORD
85: ( person_id igs_sv_persons.person_id%TYPE,
86: batch_id igs_sv_persons.batch_id%TYPE,
87: record_number igs_sv_persons.record_number%TYPE,
88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged
89: person_number igs_sv_persons.person_number%TYPE,
90: sevis_user_id igs_sv_batches.sevis_user_id%TYPE,
91: batch_type igs_sv_batches.batch_type%TYPE,

Line 88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged

84: TYPE t_student_rec IS RECORD
85: ( person_id igs_sv_persons.person_id%TYPE,
86: batch_id igs_sv_persons.batch_id%TYPE,
87: record_number igs_sv_persons.record_number%TYPE,
88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged
89: person_number igs_sv_persons.person_number%TYPE,
90: sevis_user_id igs_sv_batches.sevis_user_id%TYPE,
91: batch_type igs_sv_batches.batch_type%TYPE,
92: form_id igs_sv_persons.form_id%TYPE,

Line 89: person_number igs_sv_persons.person_number%TYPE,

85: ( person_id igs_sv_persons.person_id%TYPE,
86: batch_id igs_sv_persons.batch_id%TYPE,
87: record_number igs_sv_persons.record_number%TYPE,
88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged
89: person_number igs_sv_persons.person_number%TYPE,
90: sevis_user_id igs_sv_batches.sevis_user_id%TYPE,
91: batch_type igs_sv_batches.batch_type%TYPE,
92: form_id igs_sv_persons.form_id%TYPE,
93: print_form igs_sv_persons.print_form%TYPE,

Line 92: form_id igs_sv_persons.form_id%TYPE,

88: record_status igs_sv_persons.record_status%TYPE, --(N)ew (C)hanged
89: person_number igs_sv_persons.person_number%TYPE,
90: sevis_user_id igs_sv_batches.sevis_user_id%TYPE,
91: batch_type igs_sv_batches.batch_type%TYPE,
92: form_id igs_sv_persons.form_id%TYPE,
93: print_form igs_sv_persons.print_form%TYPE,
94: dep_flag VARCHAR2(1),
95: person_status VARCHAR2(1),
96: changes_found VARCHAR2(1),

Line 93: print_form igs_sv_persons.print_form%TYPE,

89: person_number igs_sv_persons.person_number%TYPE,
90: sevis_user_id igs_sv_batches.sevis_user_id%TYPE,
91: batch_type igs_sv_batches.batch_type%TYPE,
92: form_id igs_sv_persons.form_id%TYPE,
93: print_form igs_sv_persons.print_form%TYPE,
94: dep_flag VARCHAR2(1),
95: person_status VARCHAR2(1),
96: changes_found VARCHAR2(1),
97: issue_status VARCHAR2(1),

Line 111: no_show_flag igs_sv_persons.no_show_flag%TYPE,

107: site_addr_status VARCHAR2(1),
108: legal_status VARCHAR2(1),
109: dep_count NUMBER(3),
110: edu_count NUMBER(3),
111: no_show_flag igs_sv_persons.no_show_flag%TYPE,
112: reprint_reason igs_sv_persons.reprint_rsn_code%TYPE -- prbhardw
113: );
114:
115:

Line 112: reprint_reason igs_sv_persons.reprint_rsn_code%TYPE -- prbhardw

108: legal_status VARCHAR2(1),
109: dep_count NUMBER(3),
110: edu_count NUMBER(3),
111: no_show_flag igs_sv_persons.no_show_flag%TYPE,
112: reprint_reason igs_sv_persons.reprint_rsn_code%TYPE -- prbhardw
113: );
114:
115:
116:

Line 1150: (IGS_SV_PERSONS)

1146: Date Created By : Oct 14, 2002
1147:
1148: Purpose : Validate the information for the block that
1149: is to be used for the Person Info Block
1150: (IGS_SV_PERSONS)
1151:
1152: Remarks : Return result:
1153: 'S' - record found and validated
1154: 'E' - validation error

Line 1165: p_data_rec IN OUT NOCOPY IGS_SV_PERSONS%ROWTYPE -- Data record

1161:
1162: ******************************************************************/
1163: FUNCTION Validate_Issue_Info (
1164: p_person_rec IN t_student_rec,
1165: p_data_rec IN OUT NOCOPY IGS_SV_PERSONS%ROWTYPE -- Data record
1166: ) RETURN VARCHAR2
1167: IS
1168:
1169: l_api_name CONSTANT VARCHAR(30) := 'Validate_Issue_Info';

Line 2538: igs_sv_persons pr

2534: and usg.site_use_type = g_site_addr_usage
2535: and usg.party_site_id in
2536: ( select ad.party_site_id
2537: from igs_sv_addresses ad,
2538: igs_sv_persons pr
2539: where ad.person_id = pr.person_id
2540: and ad.batch_id = pr.batch_id
2541: and pr.record_status <> 'E'
2542: and ad.person_id = p_person_rec.person_id and ad.address_type not in ('F','U')

Line 2571: igs_sv_persons pr

2567: and usg.location_id in
2568: ( select adr1.location_id
2569: from igs_sv_addresses ad,
2570: HZ_LOCATIONS adr1,
2571: igs_sv_persons pr
2572: where ad.person_id = pr.person_id
2573: and ad.batch_id = pr.batch_id
2574: and pr.record_status <> 'E'
2575: and adr1.location_id = ad.party_site_id -- in the party_side_id field of igs_sv_addresses location_id is stored for activity sites

Line 3492: igs_sv_persons pr

3488: WHERE nonimg_form_id = p_person_rec.form_id
3489: AND nonimg_stat_id NOT IN
3490: ( SELECT NVL(form_status_id,0)
3491: FROM igs_sv_prgms_info prg,
3492: igs_sv_persons pr
3493: WHERE prg.person_id = pr.person_id
3494: AND prg.batch_id = pr.batch_id
3495: AND pr.record_status <> 'E'
3496: AND prg.person_id = p_person_rec.person_id

Line 3515: igs_sv_persons pr

3511: WHERE ev_form_id = p_person_rec.form_id
3512: AND ev_form_stat_id NOT IN
3513: ( SELECT NVL(form_status_id,0)
3514: FROM igs_sv_prgms_info prg,
3515: igs_sv_persons pr
3516: WHERE prg.person_id = pr.person_id
3517: AND pr.record_status <> 'E'
3518: AND prg.batch_id = pr.batch_id
3519: AND prg.person_id = p_person_rec.person_id)

Line 3536: igs_sv_persons pr

3532: prgms.prgm_end_date,
3533: prgms.remarks,
3534: prgms.auth_action_code
3535: FROM igs_sv_prgms_info prgms,
3536: igs_sv_persons pr
3537: WHERE prgms.person_id = pr.person_id
3538: AND pr.record_status <> 'E'
3539: AND prgms.person_id = p_person_rec.person_id
3540: AND prgms.prgm_action_type = 'DB'

Line 3545: igs_sv_persons pr

3541: AND prgms.sevis_auth_id = cp_sevis_auth_id
3542: AND prgms.batch_id IN
3543: ( SELECT max(prs.batch_id)
3544: FROM igs_sv_prgms_info prs,
3545: igs_sv_persons pr
3546: WHERE prs.person_id = pr.person_id
3547: AND prs.batch_id = pr.batch_id
3548: AND pr.record_status <> 'E'
3549: AND prs.person_id = p_person_rec.person_id

Line 3559: igs_sv_persons pr

3555: SELECT prgms.prgm_start_date,
3556: prgms.prgm_end_date,
3557: prgms.remarks
3558: FROM igs_sv_prgms_info prgms,
3559: igs_sv_persons pr
3560: WHERE prgms.person_id = pr.person_id
3561: AND prgms.batch_id = pr.batch_id
3562: AND pr.record_status <> 'E'
3563: AND prgms.person_id = p_person_rec.person_id

Line 4840: igs_sv_persons pr

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'
4844: AND prg.person_id = p_person_rec.person_id )

Line 5286: igs_sv_persons pr

5282: nonimg_empl_id = p_data_rec.nonimg_empl_id and
5283: batch_id IN
5284: ( SELECT max(emp.batch_id)
5285: FROM igs_sv_empl_info emp,
5286: igs_sv_persons pr
5287: WHERE emp.person_id = pr.person_id
5288: AND emp.batch_id = pr.batch_id
5289: AND pr.record_status <> 'E'
5290: AND emp.person_id = p_data_rec.person_id

Line 5685: (IGS_SV_PERSONS).

5681:
5682: Date Created By : Oct 14, 2002
5683:
5684: Purpose : Get the person information on the student.
5685: (IGS_SV_PERSONS).
5686:
5687: Remarks :
5688:
5689: Change History

Line 5695: p_data_rec IN OUT NOCOPY IGS_SV_PERSONS%ROWTYPE -- Data record

5691: ------------------------------------------------------------------------
5692:
5693: ******************************************************************/
5694: FUNCTION Get_Issue_Info (
5695: p_data_rec IN OUT NOCOPY IGS_SV_PERSONS%ROWTYPE -- Data record
5696: ) RETURN VARCHAR2 -- 'S' Record found, 'N' - not found. 'U' - Unexpected error
5697: IS
5698:
5699: l_api_name CONSTANT VARCHAR2(25) := 'Get_Issue_Info';

Line 5719: FROM igs_sv_persons cr

5715: cr.ev_create_reason ,
5716: cr.ev_form_number ,
5717: cr.no_show_flag ,
5718: cr.last_session_flag
5719: FROM igs_sv_persons cr
5720: WHERE cr.person_id = p_data_rec.person_id
5721: AND cr.batch_id IN
5722: ( SELECT max(mx.batch_id)
5723: FROM igs_sv_persons mx

Line 5723: FROM igs_sv_persons mx

5719: FROM igs_sv_persons cr
5720: WHERE cr.person_id = p_data_rec.person_id
5721: AND cr.batch_id IN
5722: ( SELECT max(mx.batch_id)
5723: FROM igs_sv_persons mx
5724: WHERE mx.person_id = p_data_rec.person_id
5725: AND mx.record_status <> 'E'
5726: );
5727:

Line 5859: igs_sv_persons pr

5855: WHERE cr.person_id = p_data_rec.person_id
5856: AND cr.batch_id IN
5857: ( SELECT max(prs.batch_id)
5858: FROM igs_sv_bio_info prs,
5859: igs_sv_persons pr
5860: WHERE prs.person_id = pr.person_id
5861: AND prs.batch_id = pr.batch_id
5862: AND pr.record_status <> 'E'
5863: AND prs.person_id = p_data_rec.person_id

Line 5983: igs_sv_persons pr

5979: WHERE cr.person_id = p_data_rec.person_id
5980: AND cr.batch_id IN
5981: ( SELECT max(prs.batch_id)
5982: FROM igs_sv_oth_info prs,
5983: igs_sv_persons pr
5984: WHERE prs.person_id = pr.person_id
5985: AND prs.batch_id = pr.batch_id
5986: AND pr.record_status <> 'E'
5987: AND prs.person_id = p_data_rec.person_id

Line 6110: igs_sv_persons pr

6106: AND cr.party_site_id = p_data_rec.party_site_id
6107: AND cr.batch_id IN
6108: ( SELECT max(prs.batch_id)
6109: FROM igs_sv_addresses prs,
6110: igs_sv_persons pr
6111: WHERE prs.person_id = pr.person_id
6112: AND prs.batch_id = pr.batch_id
6113: AND pr.record_status <> 'E'
6114: AND prs.person_id = p_data_rec.person_id

Line 6247: igs_sv_persons pr

6243: AND cr.prgm_action_type='EP'
6244: AND cr.batch_id IN
6245: ( SELECT max(prs.batch_id)
6246: FROM igs_sv_prgms_info prs,
6247: igs_sv_persons pr
6248: WHERE prs.person_id = pr.person_id
6249: AND prs.batch_id = pr.batch_id
6250: AND pr.record_status <> 'E'
6251: AND prs.person_id = p_data_rec.person_id

Line 6399: igs_sv_persons pr

6395: WHERE cr.person_id = p_data_rec.person_id
6396: AND cr.batch_id IN
6397: ( SELECT max(prs.batch_id)
6398: FROM igs_sv_finance_info prs,
6399: igs_sv_persons pr
6400: WHERE prs.person_id = pr.person_id
6401: AND prs.batch_id = pr.batch_id
6402: AND pr.record_status <> 'E'
6403: AND prs.person_id = p_data_rec.person_id

Line 6552: igs_sv_persons pr

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'
6556: AND prs.person_id = p_data_rec.person_id

Line 6684: igs_sv_persons pr

6680: WHERE cr.person_id = p_data_rec.person_id
6681: AND cr.batch_id IN
6682: ( SELECT max(prs.batch_id)
6683: FROM igs_sv_legal_info prs,
6684: igs_sv_persons pr
6685: WHERE prs.person_id = pr.person_id
6686: AND prs.batch_id = pr.batch_id
6687: AND pr.record_status <> 'E'
6688: AND prs.person_id = p_data_rec.person_id

Line 6780: (IGS_SV_PERSONS).

6776:
6777: Date Created By : Oct 14, 2002
6778:
6779: Purpose : Updating of person issue information block
6780: (IGS_SV_PERSONS).
6781:
6782: Remarks :
6783:
6784: Change History

Line 6790: p_data_rec IN IGS_SV_PERSONS%ROWTYPE -- Data record

6786: ------------------------------------------------------------------------
6787:
6788: ******************************************************************/
6789: PROCEDURE Update_Issue_Info (
6790: p_data_rec IN IGS_SV_PERSONS%ROWTYPE -- Data record
6791: )
6792: IS
6793:
6794: l_api_name CONSTANT VARCHAR(30) := 'Update_Issue_Info';

Line 6804: UPDATE IGS_SV_PERSONS

6800: l_debug_str := 'Entering Update_Issue_Info. p_data_rec.person_id is '||p_data_rec.person_id|| ' and p_data_rec.batch_id is '||p_data_rec.batch_id;
6801: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
6802: END IF;
6803:
6804: UPDATE IGS_SV_PERSONS
6805: SET issuing_reason = p_data_rec.issuing_reason,
6806: curr_session_end_date = p_data_rec.curr_session_end_date ,
6807: next_session_start_date= p_data_rec.next_session_start_date ,
6808: other_reason = p_data_rec.other_reason ,

Line 8255: FROM igs_sv_persons pr

8251: CURSOR c_old_ses_dates IS
8252: SELECT pr.curr_session_end_date,
8253: pr.next_session_start_date,
8254: pr.last_session_flag
8255: FROM igs_sv_persons pr
8256: WHERE pr.person_id = p_person_rec.person_id
8257: AND pr.batch_id IN
8258: ( SELECT max(btch.batch_id)
8259: FROM igs_sv_persons prs,

Line 8259: FROM igs_sv_persons prs,

8255: FROM igs_sv_persons pr
8256: WHERE pr.person_id = p_person_rec.person_id
8257: AND pr.batch_id IN
8258: ( SELECT max(btch.batch_id)
8259: FROM igs_sv_persons prs,
8260: igs_sv_batches btch,
8261: igs_sv_persons pr
8262: WHERE prs.person_id = pr.person_id
8263: AND prs.batch_id = pr.batch_id

Line 8261: igs_sv_persons pr

8257: AND pr.batch_id IN
8258: ( SELECT max(btch.batch_id)
8259: FROM igs_sv_persons prs,
8260: igs_sv_batches btch,
8261: igs_sv_persons pr
8262: WHERE prs.person_id = pr.person_id
8263: AND prs.batch_id = pr.batch_id
8264: AND pr.record_status <> 'E'
8265: AND prs.batch_id = btch.batch_id

Line 8279: l_start_date igs_sv_persons.curr_session_end_date%TYPE;

8275: last_session_flag
8276: FROM igs_pe_nonimg_form
8277: WHERE nonimg_form_id = p_person_rec.form_id;
8278:
8279: l_start_date igs_sv_persons.curr_session_end_date%TYPE;
8280: l_end_date igs_sv_persons.next_session_start_date%TYPE;
8281: l_old_start_date igs_sv_persons.curr_session_end_date%TYPE;
8282: l_old_end_date igs_sv_persons.next_session_start_date%TYPE;
8283: l_old_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;

Line 8280: l_end_date igs_sv_persons.next_session_start_date%TYPE;

8276: FROM igs_pe_nonimg_form
8277: WHERE nonimg_form_id = p_person_rec.form_id;
8278:
8279: l_start_date igs_sv_persons.curr_session_end_date%TYPE;
8280: l_end_date igs_sv_persons.next_session_start_date%TYPE;
8281: l_old_start_date igs_sv_persons.curr_session_end_date%TYPE;
8282: l_old_end_date igs_sv_persons.next_session_start_date%TYPE;
8283: l_old_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;
8284: l_last_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;

Line 8281: l_old_start_date igs_sv_persons.curr_session_end_date%TYPE;

8277: WHERE nonimg_form_id = p_person_rec.form_id;
8278:
8279: l_start_date igs_sv_persons.curr_session_end_date%TYPE;
8280: l_end_date igs_sv_persons.next_session_start_date%TYPE;
8281: l_old_start_date igs_sv_persons.curr_session_end_date%TYPE;
8282: l_old_end_date igs_sv_persons.next_session_start_date%TYPE;
8283: l_old_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;
8284: l_last_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;
8285: l_f_addr_rec g_address_rec_type;

Line 8282: l_old_end_date igs_sv_persons.next_session_start_date%TYPE;

8278:
8279: l_start_date igs_sv_persons.curr_session_end_date%TYPE;
8280: l_end_date igs_sv_persons.next_session_start_date%TYPE;
8281: l_old_start_date igs_sv_persons.curr_session_end_date%TYPE;
8282: l_old_end_date igs_sv_persons.next_session_start_date%TYPE;
8283: l_old_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;
8284: l_last_session_flag igs_pe_nonimg_form.last_session_flag%TYPE;
8285: l_f_addr_rec g_address_rec_type;
8286: l_count NUMBER(10);

Line 8441: UPDATE igs_sv_persons

8437:
8438: END IF;*/
8439: -- Update session dates for the main record required.
8440:
8441: UPDATE igs_sv_persons
8442: SET curr_session_end_date = l_start_date,
8443: next_session_start_date = l_end_date,
8444: last_session_flag = l_last_session_flag ---prbhardw
8445: WHERE person_id = p_person_rec.person_id and

Line 9586: igs_sv_persons pr

9582: WHERE person_id = p_data_rec.person_id AND
9583: batch_id IN
9584: ( SELECT max(prg.batch_id)
9585: FROM igs_sv_prgms_info prg,
9586: igs_sv_persons pr
9587: WHERE prg.person_id = pr.person_id
9588: AND prg.batch_id = pr.batch_id
9589: AND pr.record_status <> 'E'
9590: AND prg.person_id = p_data_rec.person_id

Line 9687: igs_sv_persons pr

9683: WHERE person_id = p_data_rec.person_id AND
9684: batch_id IN
9685: ( SELECT max(prg.batch_id)
9686: FROM igs_sv_prgms_info prg,
9687: igs_sv_persons pr
9688: WHERE prg.person_id = pr.person_id
9689: AND prg.batch_id = pr.batch_id
9690: AND pr.record_status <> 'E'
9691: AND prg.person_id = p_data_rec.person_id

Line 11304: DELETE FROM igs_sv_persons WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );

11300: DELETE FROM igs_sv_oth_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
11301:
11302: DELETE FROM igs_sv_prgms_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
11303:
11304: DELETE FROM igs_sv_persons WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
11305:
11306: DELETE FROM igs_sv_btch_summary WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ); -- prbhardw
11307:
11308: /* Debug */

Line 11400: FUNCTION Get_pre_noshow_status(p_person_id igs_sv_persons.person_id%TYPE, p_batch_id igs_sv_persons.person_id%TYPE)

11396:
11397: END EV_Batch_Process;
11398:
11399:
11400: FUNCTION Get_pre_noshow_status(p_person_id igs_sv_persons.person_id%TYPE, p_batch_id igs_sv_persons.person_id%TYPE)
11401: RETURN VARCHAR2
11402: IS
11403: l_no_show_status VARCHAR2(1);
11404: CURSOR no_show_change(c_person_id igs_sv_persons.person_id%TYPE, c_batch_id igs_sv_persons.person_id%TYPE)

Line 11404: CURSOR no_show_change(c_person_id igs_sv_persons.person_id%TYPE, c_batch_id igs_sv_persons.person_id%TYPE)

11400: FUNCTION Get_pre_noshow_status(p_person_id igs_sv_persons.person_id%TYPE, p_batch_id igs_sv_persons.person_id%TYPE)
11401: RETURN VARCHAR2
11402: IS
11403: l_no_show_status VARCHAR2(1);
11404: CURSOR no_show_change(c_person_id igs_sv_persons.person_id%TYPE, c_batch_id igs_sv_persons.person_id%TYPE)
11405: IS
11406: SELECT old_per.no_show_flag
11407: FROM igs_sv_persons new_per, igs_sv_persons old_per
11408: WHERE new_per.person_id = c_person_id AND

Line 11407: FROM igs_sv_persons new_per, igs_sv_persons old_per

11403: l_no_show_status VARCHAR2(1);
11404: CURSOR no_show_change(c_person_id igs_sv_persons.person_id%TYPE, c_batch_id igs_sv_persons.person_id%TYPE)
11405: IS
11406: SELECT old_per.no_show_flag
11407: FROM igs_sv_persons new_per, igs_sv_persons old_per
11408: WHERE new_per.person_id = c_person_id AND
11409: new_per.batch_id = c_batch_id AND
11410: old_per.batch_id = (SELECT max(batch_id) FROM igs_sv_persons WHERE batch_id < c_batch_id) AND
11411: old_per.person_id = new_per.person_id;

Line 11410: old_per.batch_id = (SELECT max(batch_id) FROM igs_sv_persons WHERE batch_id < c_batch_id) AND

11406: SELECT old_per.no_show_flag
11407: FROM igs_sv_persons new_per, igs_sv_persons old_per
11408: WHERE new_per.person_id = c_person_id AND
11409: new_per.batch_id = c_batch_id AND
11410: old_per.batch_id = (SELECT max(batch_id) FROM igs_sv_persons WHERE batch_id < c_batch_id) AND
11411: old_per.person_id = new_per.person_id;
11412: BEGIN
11413:
11414: OPEN no_show_change(p_person_id,p_batch_id);

Line 11513: l_person_sevis_id IGS_SV_PERSONS.SEVIS_USER_ID%TYPE;

11509:
11510:
11511:
11512: l_batch_id igs_sv_batches.batch_id%TYPE;
11513: l_person_sevis_id IGS_SV_PERSONS.SEVIS_USER_ID%TYPE;
11514: l_issue_rec IGS_SV_PERSONS%ROWTYPE;
11515: l_bio_rec IGS_SV_BIO_INFO%ROWTYPE;
11516: l_oth_rec IGS_SV_OTH_INFO%ROWTYPE;
11517: l_edu_rec IGS_SV_PRGMS_INFO%ROWTYPE;

Line 11514: l_issue_rec IGS_SV_PERSONS%ROWTYPE;

11510:
11511:
11512: l_batch_id igs_sv_batches.batch_id%TYPE;
11513: l_person_sevis_id IGS_SV_PERSONS.SEVIS_USER_ID%TYPE;
11514: l_issue_rec IGS_SV_PERSONS%ROWTYPE;
11515: l_bio_rec IGS_SV_BIO_INFO%ROWTYPE;
11516: l_oth_rec IGS_SV_OTH_INFO%ROWTYPE;
11517: l_edu_rec IGS_SV_PRGMS_INFO%ROWTYPE;
11518: l_fin_rec IGS_SV_FINANCE_INFO %ROWTYPE;

Line 11539: ( SELECT NVL(prs.form_status_id,0) FROM igs_sv_prgms_info prs, igs_sv_persons pr

11535: (SELECT st.ev_form_id FROM igs_pe_ev_form_stat st
11536: WHERE st.action_type IN ('TR','ED')
11537: AND st.ev_form_id = fr.ev_form_id
11538: AND st.ev_form_stat_id IN
11539: ( SELECT NVL(prs.form_status_id,0) FROM igs_sv_prgms_info prs, igs_sv_persons pr
11540: WHERE prs.person_id = pr.person_id AND prs.batch_id = pr.batch_id
11541: AND pr.record_status <> 'E' AND prs.person_id = fr.person_id)
11542: ) AND fr.sevis_school_identifier = p_org_party_id AND
11543: ( (p_dso_id IS NULL AND

Line 11570: FROM igs_sv_prgms_info prs, igs_sv_persons pr

11566: WHERE st.action_type IN ('T','C')
11567: AND st.nonimg_form_id = fr.nonimg_form_id
11568: AND st.nonimg_stat_id IN
11569: ( SELECT NVL(prs.form_status_id,0)
11570: FROM igs_sv_prgms_info prs, igs_sv_persons pr
11571: WHERE prs.person_id = pr.person_id AND prs.batch_id = pr.batch_id
11572: AND pr.record_status <> 'E' AND prs.person_id = fr.person_id
11573: ) )
11574: AND fr.sevis_school_identifier = p_org_party_id AND

Line 11605: FROM igs_sv_persons a,

11601: WHERE party_id = cp_party_id;
11602:
11603: CURSOR c_prev_info( p_id NUMBER, f_id NUMBER) IS
11604: SELECT 1
11605: FROM igs_sv_persons a,
11606: igs_sv_batches b
11607: WHERE person_id = p_id
11608: AND a.batch_id = b.batch_id
11609: AND b.batch_type = p_batch_type

Line 11658: l_record_number igs_sv_persons.record_number%TYPE;

11654:
11655:
11656:
11657: l_batch_status VARCHAR(1) := 'N'; -- batch status (S)uccess, (E)rror,'N'o new students
11658: l_record_number igs_sv_persons.record_number%TYPE;
11659: l_student_rec t_student_rec;
11660: l_status VARCHAR2(1);
11661: l_count NUMBER(10);
11662: l_soa_count NUMBER(5); -- prbhardw CP enhancement

Line 11918: l_debug_str := 'Inserting in igs_sv_persons batch id: '||l_batch_id;

11914: END IF;
11915: /* Debug */
11916: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
11917: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Create_Batch';
11918: l_debug_str := 'Inserting in igs_sv_persons batch id: '||l_batch_id;
11919: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11920: END IF;
11921: INSERT INTO igs_sv_persons (
11922: batch_id ,

Line 11921: INSERT INTO igs_sv_persons (

11917: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Create_Batch';
11918: l_debug_str := 'Inserting in igs_sv_persons batch id: '||l_batch_id;
11919: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11920: END IF;
11921: INSERT INTO igs_sv_persons (
11922: batch_id ,
11923: person_id ,
11924: record_number,
11925: form_id ,

Line 12288: 'IGS_SV_PERSONS',

12284: l_student_rec.person_id,
12285: 'NOSHOW',
12286: 'SV_STATUS',
12287: 'SEND',
12288: 'IGS_SV_PERSONS',
12289: '');
12290: END IF;
12291: ELSE
12292:

Line 13007: UPDATE igs_sv_persons

13003: END IF;
13004:
13005: IF lower(p_Status) <> 'true' THEN
13006:
13007: UPDATE igs_sv_persons
13008: SET record_status = 'E',
13009: sevis_error_code = p_SEVIS_ErrorCode,
13010: sevis_error_element = p_SEVIS_ErrorElement
13011: WHERE batch_id = p_BatchID

Line 13244: update igs_sv_persons set SEVIS_USER_ID = p_sevisID ,

13240: p_SEVIS_ErrorCode => p_SEVIS_ErrorCode,
13241: p_SEVIS_ErrorElement => p_SEVIS_ErrorElement
13242: );
13243:
13244: update igs_sv_persons set SEVIS_USER_ID = p_sevisID ,
13245: SEVIS_ERROR_CODE = p_SEVIS_ErrorCode, SEVIS_ERROR_ELEMENT = p_SEVIS_ErrorElement
13246: where person_id = p_PersonID;
13247:
13248: EXCEPTION

Line 13527: EXISTS (SELECT 1 FROM igs_sv_persons pers

13523: SELECT summ.person_id, summ.tag_code
13524: FROM igs_sv_btch_summary summ
13525: WHERE summ.batch_id = cp_batch_id AND
13526: summ.adm_action_code ='HOLD' AND
13527: EXISTS (SELECT 1 FROM igs_sv_persons pers
13528: WHERE pers.person_id = summ.person_id AND
13529: pers.batch_id = summ.batch_id AND
13530: pers.record_status = 'C' );
13531:

Line 13535: FROM igs_sv_persons pers

13531:
13532: CURSOR c_new_pers(cp_batch_id igs_sv_btch_summary.batch_id%TYPE)
13533: IS
13534: SELECT distinct person_number
13535: FROM igs_sv_persons pers
13536: WHERE pers.batch_id = cp_batch_id AND record_status = 'N'
13537: AND EXISTS (SELECT 1
13538: FROM igs_sv_btch_summary summ
13539: WHERE summ.person_id = pers.person_id AND

Line 13645: FROM igs_sv_btch_summary svbs, hz_parties hz, igs_lookup_values lkp, igs_sv_persons pers

13641:
13642: CURSOR c_get_new_persons(cp_batch_id igs_sv_btch_summary.batch_id%TYPE)
13643: IS
13644: SELECT hz.party_number prsn_num, lkp.meaning info
13645: FROM igs_sv_btch_summary svbs, hz_parties hz, igs_lookup_values lkp, igs_sv_persons pers
13646: WHERE svbs.batch_id = cp_batch_id
13647: AND svbs.person_id = hz.party_id
13648: AND svbs.tag_code = lkp.lookup_code
13649: AND lkp.lookup_type ='IGS_SV_COMP_TREE'

Line 13658: FROM igs_sv_btch_summary svbs, hz_parties hz, igs_lookup_values lkp, igs_sv_persons pers

13654:
13655: CURSOR c_get_updated_persons(cp_batch_id igs_sv_btch_summary.batch_id%TYPE)
13656: IS
13657: SELECT hz.party_number prsn_num, lkp.meaning info
13658: FROM igs_sv_btch_summary svbs, hz_parties hz, igs_lookup_values lkp, igs_sv_persons pers
13659: WHERE svbs.batch_id = cp_batch_id
13660: AND svbs.person_id = hz.party_id
13661: AND svbs.tag_code = lkp.lookup_code
13662: AND lkp.lookup_type ='IGS_SV_COMP_TREE'