1 PACKAGE BODY igs_en_sevis AS
2 /* $Header: IGSEN97B.pls 120.5 2006/06/19 11:54:53 amuthu noship $ */
3
4
5 FUNCTION enrf_chk_sevis_auth_req ( p_person_id NUMBER,
6 p_cal_type VARCHAR2,
7 p_ci_sequence_number NUMBER,
8 p_elgb_step VARCHAR2
9 ) RETURN BOOLEAN IS
10 /*
11 || Created By :
12 || Created On : 08-MAR-2006
13 || Purpose : Initialises the columns, Checks Constraints, Calls the
14 || Trigger Handlers for the table, before any DML operation.
15 || Known limitations, enhancements or remarks :
16 || Change History :
17 || Who When What
18 || (reverse chronological order - newest change first)
19 || ckasu 13-JUN-2006 modified as a part of bug 5300119 inorder to
20 not consider SEVIS functionality for Exchange visitor Person Type.
21 by modifying the cursor c_sevis_person_type.
22 ckasu 13-JUN-2006 modified as a prt of bug 5248531 inorder to not consider non -imgrant
23 student as SEVIS student even when his start date is greater than sysdate
24 */
25
26 -- Cursor to check whether active sevis person type exists for that person
27 -- modified by ckasu as a part of bug 5300119
28 CURSOR c_sevis_person_type IS
29 SELECT 'X'
30 FROM igs_pe_typ_instances_all pti,
31 igs_pe_person_types pt
32 WHERE pt.person_type_code = pti.person_type_code
33 AND system_type IN ('NONIMG_STUDENT')
34 AND person_id = p_person_id
35 AND NVL(end_date, SYSDATE) >= SYSDATE;
36
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
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
50 l_authorisation_exists c_authorisation_exists%ROWTYPE;
51 l_sevis_person_type c_sevis_person_type%ROWTYPE;
52
53 BEGIN
54
55 -- Check whether sevis profile is set or not
56 IF FND_PROFILE.VALUE('IGS_SV_ENABLED') = 'N' THEN
57 RETURN FALSE;
58 ELSE
59 IF p_elgb_step NOT IN ('FATD_TYPE','FMIN_CRDT') THEN
60 RETURN FALSE;
61 ELSE
62
63 -- Check whether the person has active sevis person type
64 OPEN c_sevis_person_type;
65 FETCH c_sevis_person_type INTO l_sevis_person_type;
66
67 IF c_sevis_person_type%NOTFOUND THEN
68 RETURN FALSE;
69 ELSE
70
71 -- Check whether active authorisation exists for that person
72 OPEN c_authorisation_exists(p_person_id, p_cal_type, p_ci_sequence_number);
73 FETCH c_authorisation_exists INTO l_authorisation_exists;
74
75 IF c_authorisation_exists%FOUND THEN
76 RETURN FALSE;
77 ELSE
78 RETURN TRUE;
79 END IF;
80 CLOSE c_authorisation_exists;
81
82 END IF;
83 CLOSE c_sevis_person_type;
84 END IF;
85 END IF;
86
87 END enrf_chk_sevis_auth_req;
88
89
90 PROCEDURE stud_ret_to_ft_load ( p_begin_cal_inst IN VARCHAR2,
91 p_return_cal_inst IN VARCHAR2,
92 p_log_creation_dt OUT NOCOPY DATE) is
93
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;
103
104
105 -- Cursor to get the course code for the authorised persons
106 CURSOR c_sca (l_person_id hz_parties.party_id%TYPE) IS
107 SELECT course_cd
108 FROM igs_en_stdnt_ps_att
109 WHERE person_id = l_person_id;
110
111 -- Get person related details
112 CURSOR c_person_name (l_person_id hz_parties.party_id%TYPE) IS
113 SELECT party_number, person_first_name||' '||person_middle_name||' '||person_last_name full_name
114 FROM hz_parties
115 WHERE party_id = l_person_id;
116
117 CURSOR c_cal_type(l_cal_type igs_ca_inst_all.cal_type%TYPE, l_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
118 SELECT alternate_code, start_dt, end_dt
119 FROM igs_ca_inst_all
120 WHERE cal_type = l_cal_type
121 AND sequence_number = l_sequence_number;
122
123 CURSOR c_cal_cat(l_cal_type igs_ca_inst_all.cal_type%TYPE) IS
124 SELECT s_cal_cat
125 FROM igs_ca_type
126 WHERE cal_type = l_cal_type;
127
128
129 -- Get the sevis meaning for the passed sevis lookup code
130 CURSOR c_sevis_meaning (l_lookup_code igs_lookups_view.lookup_code%TYPE) IS
131 SELECT meaning
132 FROM igs_lookups_view
133 WHERE lookup_type = 'IGS_EN_SEVIS_AUTH_CODES'
134 AND lookup_code = l_lookup_code;
135
136 -- Get the preferred alternate person id
137 CURSOR c_api_person(l_person_id hz_parties.party_id%TYPE) IS
138 SELECT api_person_id
139 FROM igs_pe_person_id_type_v
140 WHERE pe_person_id = l_person_id;
141
142 -- Cursor to get the corresponding load calander for the passed teaching calander
143 CURSOR cur_teach_to_load(p_cal_type IGS_CA_INST.cal_type%TYPE,
144 p_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
145 SELECT load_cal_type,load_ci_sequence_number
146 FROM IGS_CA_TEACH_TO_LOAD_V
147 WHERE teach_cal_type = p_cal_type AND
148 teach_ci_sequence_number = p_sequence_number AND
149 load_end_dt >= TRUNC(SYSDATE)
150 ORDER BY load_start_dt asc;
151
152
153 -- Cursor to get the attendance type for the maximum enrollment load
154 CURSOR c_att_type(l_cal_type igs_ca_inst_all.cal_type%TYPE) IS
155 SELECT attendance_type FROM igs_en_atd_type_load
156 WHERE cal_type = l_cal_type
157 AND lower_enr_load_range = (SELECT MAX(lower_enr_load_range)
158 FROM igs_en_atd_type_load
159 WHERE cal_type = l_cal_type);
160
161 rec_teach_to_load cur_teach_to_load%ROWTYPE;
162 v_begin_cal_type igs_ca_inst_all.cal_type%TYPE;
163 v_begin_ci_seq_number igs_ca_inst_all.sequence_number%TYPE;
164 v_begin_term_cal_type igs_ca_inst_all.cal_type%TYPE;
165 v_begin_term_ci_seq_number igs_ca_inst_all.sequence_number%TYPE;
166 v_return_cal_type igs_ca_inst_all.cal_type%TYPE;
167 v_return_ci_seq_number igs_ca_inst_all.sequence_number%TYPE;
168 l_attendance_type igs_en_stdnt_ps_att.attendance_type%TYPE;
169 l_credit_points igs_en_su_attempt.override_achievable_cp%TYPE;
170 l_fte igs_en_su_attempt.override_achievable_cp%TYPE;
171 l_person_name_row c_person_name%ROWTYPE;
172 l_cal_type_row c_cal_type%ROWTYPE;
173 l_sevis_meaning igs_lookups_view.meaning%TYPE;
174 v_creation_dt DATE;
175 l_api_person_id igs_pe_alt_pers_id_v.api_person_id%TYPE;
176 l_description igs_ca_inst_all.description%TYPE;
177 l_cal_cat igs_ca_type.s_cal_cat%TYPE;
178 l_att_type igs_en_atd_type_load.attendance_type%TYPE;
179
180
181 BEGIN
182
183 v_begin_cal_type := RTRIM(SUBSTR(p_begin_cal_inst,101,10));
184 v_begin_ci_seq_number := TO_NUMBER(RTRIM(SUBSTR(p_begin_cal_inst,112,6)));
185
186 IF p_return_cal_inst is null then
187
188 -- Get the current term calander if it is not passed as paramter.
189 Igs_As_Ss_Doc_Request.get_as_current_term (
190 v_return_cal_type ,
191 v_return_ci_seq_number ,
192 l_description
193 );
194
195 ELSE
196
197 v_return_cal_type := RTRIM(SUBSTR(p_return_cal_inst,101,10));
198 v_return_ci_seq_number := TO_NUMBER(RTRIM(SUBSTR(p_return_cal_inst,112,6)));
199
200 OPEN c_cal_cat(v_return_cal_type);
201 FETCH c_cal_cat INTO l_cal_cat;
202 CLOSE c_cal_cat;
203
204 -- Get the load calander type and sequence number for the passed
205 -- teaching calander.
206 IF l_cal_cat = 'TEACHING' THEN
207
208 OPEN cur_teach_to_load(v_return_cal_type, v_return_ci_seq_number);
209 FETCH cur_teach_to_load INTO rec_teach_to_load;
210 CLOSE cur_teach_to_load;
211
212 v_return_cal_type := rec_teach_to_load.load_cal_type;
213 v_return_ci_seq_number := rec_teach_to_load.load_ci_sequence_number;
214
215 END IF;
216
217 END IF;
218
219 IGS_GE_GEN_003.GENP_INS_LOG (
220 'EN-RET-FT',
221 p_begin_cal_inst||','||p_return_cal_inst||','||p_log_creation_dt,
222 v_creation_dt
223 );
224
225 p_log_creation_dt := v_creation_dt;
226
227 -- Check whether the passed begin calander is teaching or not.
228 -- If it is teaching then get the corresponding load calander
229 -- and calculate the attendance type.
230
231 OPEN c_cal_cat(v_begin_cal_type);
232 FETCH c_cal_cat INTO l_cal_cat;
233 CLOSE c_cal_cat;
234
235 IF l_cal_cat = 'TEACHING' THEN
236
237 OPEN cur_teach_to_load(v_begin_cal_type, v_begin_ci_seq_number);
238 FETCH cur_teach_to_load INTO rec_teach_to_load;
239 CLOSE cur_teach_to_load;
240
241 v_begin_term_cal_type := rec_teach_to_load.load_cal_type;
242 v_begin_term_ci_seq_number := rec_teach_to_load.load_ci_sequence_number;
243
244 ELSE
245
246 v_begin_term_cal_type := v_begin_cal_type;
247 v_begin_term_ci_seq_number := v_begin_ci_seq_number;
248
249 END IF;
250
251
252 -- Loop thru all the authorised persons
253 FOR i IN c_auth_person(v_begin_cal_type, v_begin_ci_seq_number) LOOP
254
255 -- Calulate the attendance type for the passed calander.
256 igs_en_prc_load.enrp_get_inst_latt(
257 i.person_id ,
258 v_begin_term_cal_type ,
259 v_begin_term_ci_seq_number,
260 l_attendance_type,
261 l_credit_points,
262 l_fte
263 );
264
265 -- If the attendance type is not equal to Full Time then get the attendance type
266 -- of the return cal type and if it is equal to Full time then log the details
267 -- of that person in the log table, which will be displayed in the report.
268
269 OPEN c_att_type(v_begin_term_cal_type);
270 FETCH c_att_type INTO l_att_type;
271 CLOSE c_att_type;
272
273 IF l_attendance_type <> l_att_type THEN
274
275 igs_en_prc_load.enrp_get_inst_latt(
276 i.person_id ,
277 v_return_cal_type ,
278 v_return_ci_seq_number,
279 l_attendance_type,
280 l_credit_points,
281 l_fte
282 );
283
284
285 OPEN c_att_type(v_return_cal_type);
286 FETCH c_att_type INTO l_att_type;
287 CLOSE c_att_type;
288
289 -- Check if the derived attendance type is equal to Full Time or not.
290 IF l_attendance_type = l_att_type THEN
291
292 OPEN c_person_name(i.person_id);
293 FETCH c_person_name INTO l_person_name_row;
294 CLOSE c_person_name;
295
296 OPEN c_cal_type(v_return_cal_type, v_return_ci_seq_number);
297 FETCH c_cal_type INTO l_cal_type_row;
298 CLOSE c_cal_type;
299
300 -- To get the sevis meaning
301 OPEN c_sevis_meaning(i.SEVIS_AUTHORIZATION_CODE);
302 FETCH c_sevis_meaning INTO l_sevis_meaning;
303 CLOSE c_sevis_meaning;
304
305 -- Get the alternate person id
306 OPEN c_api_person(i.person_id);
307 FETCH c_api_person INTO l_api_person_id;
308 CLOSE c_api_person;
309
310
311 -- Loop thru all the Student Program attempt
312 FOR sca IN c_sca(i.person_id) LOOP
313
314 -- Log all the parametes and other details in log entry table.
315 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY( 'EN-RET-FT',
316 p_log_creation_dt ,
317 to_char(i.person_id) || ',' ||
318 UPPER(l_person_name_row.party_number) || ',' ||
319 sca.course_cd || ',' ||
320 UPPER(l_person_name_row.full_name)|| ',' ||
321 l_api_person_id || ',' ||
322 UPPER(l_sevis_meaning) || ',' ||
323 v_return_cal_type || ',' ||
324 to_char(v_return_ci_seq_number) || ',' ||
325 l_cal_type_row.alternate_code || ',' ||
326 to_char(l_cal_type_row.start_dt)||','||
327 to_char(l_cal_type_row.end_dt),
328 '',
329 NULL);
330 END LOOP;
331
332 END IF;
333
334 END IF;
335
336 END LOOP;
337
338 END stud_ret_to_ft_load;
339
340 FUNCTION enrf_get_sevis_auth_details(
341 p_person_id IN NUMBER,
342 p_auth_code OUT NOCOPY VARCHAR2,
343 p_auth_start_dt OUT NOCOPY DATE,
344 p_auth_end_dt OUT NOCOPY DATE,
345 p_comments OUT NOCOPY VARCHAR2 )
346 RETURN BOOLEAN AS
347 /*
348 || Created By : nbehera
349 || Created On : 28NOV2002
350 || Purpose : Obsolete
351 || Known limitations, enhancements or remarks :
352 || Change History :
353 || Who When What
354 || (reverse chronological order - newest change first)
355 */
356
357 BEGIN
358 --Initializing NULL to all the OUT parameters
359 p_auth_code := NULL;
360 p_auth_start_dt := NULL;
361 p_auth_end_dt := NULL;
362 p_comments := NULL;
363
364 --------------------------------------------------------------
365 --function is obsolete
366 --------------------------------------------------------------
367 RETURN FALSE;
368 --------------------------------------------------------------
369 END enrf_get_sevis_auth_details;
370
371 FUNCTION enrf_get_ret_ft_note_details(
372 p_person_id IN NUMBER,
373 p_note_text OUT NOCOPY VARCHAR2,
374 p_note_start_dt OUT NOCOPY DATE,
375 p_note_end_dt OUT NOCOPY DATE,
376 p_note_type OUT NOCOPY VARCHAR2 )
377 RETURN BOOLEAN AS
378 /*
379 || Created By : nbehera
380 || Created On : 03DEC2002
381 || Purpose : Person ID would be passed to this API. Based on the Person ID, the API
382 || must retrieve the Return to Full Time Load Notes and the related data from
383 || the Person Notes table and pass it back to the calling procedure/function.
384 || Known limitations, enhancements or remarks :
385 || Change History :
386 || Who When What
387 || (reverse chronological order - newest change first)
388
389 */
390
391 --Cursor to get whether the person is of International Student Type
392 CURSOR c_int_per_type IS
393 SELECT 'X'
394 FROM igs_pe_typ_instances_all pti,
395 igs_pe_person_types pt
396 WHERE pt.person_type_code = pti.person_type_code
397 AND pt.system_type = 'NONIMG_STUDENT'
398 AND pti.person_id = p_person_id
399 AND NVL(pti.end_date, SYSDATE) >= SYSDATE;
400 l_int_per_type VARCHAR2(1);
401
402 --Cursor to retrieve the Active Note details for the person,
403 --Where Person note is of type Return to Full Time Load.
404 --Start date of the person note should be more or same as the Term start date,
405 --And if End date exists for the person note then it should be less or same as
406 --the End date of the term.
407 CURSOR c_ret_to_ft_note_dtls ( p_cal_type igs_ca_inst_all.cal_type%TYPE,
408 p_seq_num igs_ca_inst_all.sequence_number%TYPE ) IS
409 SELECT gn.note_text,
410 pn.start_date,
411 pn.end_date,
412 pn.pe_note_type
413 FROM igs_pe_pers_note pn,
414 igs_ge_note gn,
415 igs_ca_inst ci
416 WHERE ci.cal_type = p_cal_type
417 AND ci.sequence_number = p_seq_num
418 AND pn.person_id = p_person_id
419 AND pn.pe_note_type = 'RET_FULL_LOAD'
420 AND pn.reference_number = gn.reference_number
421 AND ci.start_dt <= pn.start_date
422 AND NVL (pn.end_date, ci.end_dt) <= ci.end_dt
423 ORDER BY pn.start_date DESC;
424 l_ret_to_ft_note_dtls c_ret_to_ft_note_dtls%ROWTYPE;
425
426 --Cursor to get the Full Time Attendance Type
427 --This is the way to get the Attendance Type for the Maximum lower enrollment load range
428 --in the provided load calendar, Which will be treated as the Full Time Attendance Type
429 CURSOR c_att_type ( p_cal_type igs_ca_inst_all.cal_type%TYPE ) IS
430 SELECT attendance_type
431 FROM igs_en_atd_type_load
432 WHERE cal_type = p_cal_type
433 AND lower_enr_load_range = ( SELECT MAX(lower_enr_load_range)
434 FROM igs_en_atd_type_load
435 WHERE cal_type = p_cal_type );
436 l_attendance_type igs_en_atd_type_load.attendance_type%TYPE;
437
438 l_cal_type igs_ca_inst_all.cal_type%TYPE;
439 l_sequence_number igs_ca_inst_all.sequence_number%TYPE;
440 l_description igs_ca_inst_all.description%TYPE;
441 l_att_type igs_en_atd_type_load.attendance_type%TYPE;
442 l_credit_points NUMBER;
443 l_fte NUMBER;
444
445
446 BEGIN
447 --Initializing NULL to all the OUT parameters
448 p_note_text := NULL;
449 p_note_start_dt := NULL;
450 p_note_end_dt := NULL;
451 p_note_type := NULL;
452
453 --If the IN parameter p_person_id is NULL then return FALSE
454 IF p_person_id IS NULL THEN
455 RETURN FALSE;
456 END IF;
457
458 --Check to see if there is an active International Person Type Associated with the Person ID
459 OPEN c_int_per_type;
460 FETCH c_int_per_type INTO l_int_per_type;
461 IF c_int_per_type%NOTFOUND THEN
462 --If not any International Person Type Associated with the Person ID then return FALSE
463 CLOSE c_int_per_type;
464 RETURN FALSE;
465 END IF;
466 CLOSE c_int_per_type;
467
468 --Derive the Current Term Calendar using the assessment procedure
469 igs_as_ss_doc_request.get_as_current_term(
470 p_cal_type => l_cal_type,
471 p_sequence_number => l_sequence_number,
472 p_description => l_description );
473 IF l_cal_type IS NULL AND l_sequence_number IS NULL THEN
474 --If the above procedure returns Cal Type and Sequence Number as NULL then return false
475 RETURN FALSE;
476 END IF;
477
478 --Call to the below procedure, to get the attendance type for the Person in the Current Term Calendar
479 igs_en_prc_load.enrp_get_inst_latt (
480 p_person_id => p_person_id,
481 p_load_cal_type => l_cal_type,
482 p_load_seq_number => l_sequence_number,
483 p_attendance => l_att_type,
484 p_credit_points => l_credit_points,
485 p_fte => l_fte );
486
487 --Get the attendance type from the cursor which will be treated as Full Time Attendance Type
488 OPEN c_att_type ( l_cal_type );
489 FETCH c_att_type INTO l_attendance_type;
490
491 --If the above cursor doesn't retrieve any record then return FALSE
492 IF c_att_type%NOTFOUND THEN
493 CLOSE c_att_type;
494 RETURN FALSE;
495 END IF;
496 CLOSE c_att_type;
497
498 --If the Attendance Type for the person is not same as the FT Attendance Type for the Load Calendar
499 --Then Return FALSE. Else Get the Note details from the cursor. If more than one notes exist then
500 --Return the note details of the latest start date.
501 IF l_attendance_type <> l_att_type THEN
502 RETURN FALSE;
503 ELSE
504 OPEN c_ret_to_ft_note_dtls ( l_cal_type, l_sequence_number );
505 FETCH c_ret_to_ft_note_dtls INTO l_ret_to_ft_note_dtls;
506
507 --If note details not found for the person then return FALSE
508 IF c_ret_to_ft_note_dtls%NOTFOUND THEN
509 CLOSE c_ret_to_ft_note_dtls;
510 RETURN FALSE;
511 END IF;
512 CLOSE c_ret_to_ft_note_dtls;
513
514 END IF;
515
516 --Return the Note Details for the person
517 p_note_text := l_ret_to_ft_note_dtls.note_text;
518 p_note_start_dt := l_ret_to_ft_note_dtls.start_date;
519 p_note_end_dt := l_ret_to_ft_note_dtls.end_date;
520 p_note_type := l_ret_to_ft_note_dtls.pe_note_type;
521 RETURN TRUE;
522
523 END enrf_get_ret_ft_note_details;
524
525
526 FUNCTION get_visa_type(p_person_id IN NUMBER,
527 p_no_of_months OUT NOCOPY NUMBER ) RETURN VARCHAR2 AS
528 l_person_id NUMBER;
529
530 CURSOR c_nonimig_visa_type IS
531 SELECT visa_type
532 FROM igs_pe_nonimg_form
533 WHERE person_id = p_person_id
534 AND form_status = 'A';
535
536 CURSOR c_visa_type IS
537 SELECT visa_type
538 FROM igs_pe_visa_v
539 WHERE person_id = p_person_id
540 AND visa_issue_date <= sysdate
541 AND sysdate <= visa_expiry_date ;
542
543 l_visa_type igs_pe_visa_v.visa_type%TYPE;
544
545 BEGIN
546
547 l_visa_type := NULL;
548 OPEN c_nonimig_visa_type;
549 FETCH c_nonimig_visa_type INTO l_visa_type;
550 IF c_nonimig_visa_type%NOTFOUND THEN
551
552 FOR l_visa_type_rec in c_visa_type LOOP
553
554 IF l_visa_type_rec.visa_type = 'F-1' THEN
555 l_visa_type := l_visa_type_rec.visa_type;
556 EXIT;
557 ELSIF l_visa_type_rec .visa_type = 'M-1' THEN
558 l_visa_type := l_visa_type_rec.visa_type;
559 END IF;-- end of IF ELSEIF l_visa_type_rec .visa_type = 'F-1'
560
561 END LOOP;-- end of For l_visa_type_rec in c_visa_type Loop
562
563 END IF; -- end of c_nonimig_visa_type%NOTFOUND THEN
564 CLOSE c_nonimig_visa_type;
565
566 IF l_visa_type = 'M-1' THEN
567 p_no_of_months := 5;
568 ELSIF l_visa_type = 'F-1' THEN
569 p_no_of_months := 12;
570 END IF;
571 RETURN l_visa_type;
572
573 END get_visa_type;
574
575
576
577 FUNCTION is_auth_rec_duration_exceeds(
578 p_person_id IN NUMBER,
579 p_start_date IN DATE,
580 p_end_date IN DATE,
581 p_no_of_months OUT NOCOPY NUMBER) RETURN BOOLEAN AS
582
583 CURSOR c_get_auth_rec_dur(cp_no_of_months NUMBER) IS
584 SELECT add_months(TRUNC(p_start_date ),cp_no_of_months) final_dt
585 FROM DUAL;
586 c_get_auth_rec_dur_rec c_get_auth_rec_dur%ROWTYPE;
587 l_visa_type IGS_PE_VISA_V.VISA_TYPE%TYPE;
588 l_status BOOLEAN;
589
590 BEGIN
591
592 l_status := FALSE;
593 l_visa_type := get_visa_type(p_person_id, p_no_of_months);
594
595 OPEN c_get_auth_rec_dur(p_no_of_months);
596 FETCH c_get_auth_rec_dur INTO c_get_auth_rec_dur_rec;
597
598 IF TRUNC(p_end_date) > c_get_auth_rec_dur_rec.final_dt THEN
599 l_status := TRUE;
600
601 END IF;
602
603 RETURN l_status;
604
605 CLOSE c_get_auth_rec_dur;
606
607 END is_auth_rec_duration_exceeds;
608
609 PROCEDURE insert_auth_cal_rec(
610 p_sevis_auth_id IN NUMBER,
611 p_cal_type IN VARCHAR2,
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
621 l_dummy VARCHAR2(1);
622 lv_rowid VARCHAR2(25);
623
624 BEGIN
625
626 OPEN c_auth_cal_exists;
627 FETCH c_auth_cal_exists INTO l_dummy;
628
629 IF c_auth_cal_exists%FOUND THEN
630 CLOSE c_auth_cal_exists;
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,
640 x_ci_sequence_number => p_ci_sequence_number);
641
642 END If;
643
644
645 END insert_auth_cal_rec;
646
647 PROCEDURE insert_authorization_rec(
648 p_sevis_authorization_code IN VARCHAR2,
649 p_start_dt IN DATE,
650 p_end_dt IN DATE,
651 p_comments IN VARCHAR2,
652 p_sevis_auth_id IN OUT NOCOPY NUMBER,
653 p_sevis_authorization_no IN OUT NOCOPY NUMBER,
654 p_person_id IN NUMBER,
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
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';
668
669
670 BEGIN
671
672 OPEN c_authorization_exists;
673 FETCH c_authorization_exists INTO p_sevis_auth_id;
674
675 IF c_authorization_exists%FOUND THEN
676
677 CLOSE c_authorization_exists;
678 RETURN;
679
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,
689 x_end_dt => p_end_dt,
690 x_comments => p_comments,
691 x_sevis_auth_id => p_sevis_auth_id,
692 x_sevis_authorization_no => l_sevis_authorization_no,
693 x_person_id => p_person_id,
694 x_cancel_flag => p_cancel_flag);
695
696 END IF;
697
698 END insert_authorization_rec;
699
700
701 PROCEDURE create_auth_cal_row (
702 p_sevis_authorization_code IN VARCHAR2,
703 p_start_dt IN DATE,
704 p_end_dt IN DATE,
705 p_comments IN VARCHAR2,
706 p_sevis_auth_id IN OUT NOCOPY NUMBER,
707 p_sevis_authorization_no IN OUT NOCOPY NUMBER,
708 p_person_id IN NUMBER,
709 p_cal_type IN VARCHAR2,
710 p_ci_sequence_number IN NUMBER,
711 p_cancel_flag IN VARCHAR2) AS
712
713 BEGIN
714
715 IF p_person_id IS NULL OR
716 p_sevis_authorization_code IS NULL OR
717 p_cal_type IS NULL OR
718 p_ci_sequence_number IS NULL OR
719 p_start_dt IS NULL OR
720 p_end_dt IS NULL THEN
721
722 Fnd_Message.Set_Name('IGS' , 'IGS_GE_INSUFFICIENT_PARAMETER');
723 IGS_GE_MSG_STACK.ADD;
724 App_Exception.Raise_Exception;
725
726 END IF;
727
728
729
730 IF p_sevis_auth_id IS NULL THEN
731 -- authorization does not exist create it first.
732
733 insert_authorization_rec(
734 p_sevis_authorization_code ,
735 p_start_dt ,
736 p_end_dt ,
737 p_comments ,
738 p_sevis_auth_id ,
739 p_sevis_authorization_no ,
740 p_person_id ,
741 p_cancel_flag);
742
743 insert_auth_cal_rec(p_sevis_auth_id, p_cal_type, p_ci_sequence_number);
744 ELSE
745 -- authorization exists create the calendar record.
746
747 insert_auth_cal_rec(p_sevis_auth_id, p_cal_type, p_ci_sequence_number);
748
749 END IF;
750
751 END create_auth_cal_row;
752
753 PROCEDURE enrp_sevis_auth_dflt_dt(p_person_id IN NUMBER,
754 p_cal_type IN VARCHAR2,
755 p_ci_sequence_number IN NUMBER,
756 p_dflt_auth_start_dt OUT NOCOPY DATE,
757 p_dflt_auth_end_dt OUT NOCOPY DATE) IS
758 CURSOR c_cal_dates IS
759 SELECT start_dt, end_dt
760 FROM IGS_CA_INST
761 WHERE cal_type = p_cal_type
762 AND sequence_number = p_ci_sequence_number;
763
764
765 l_no_of_months NUMBER;
766 l_visa_type IGS_PE_VISA_V.VISA_TYPE%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
776 BEGIN
777
778 OPEN c_cal_dates;
779 FETCH c_cal_Dates INTO l_ci_start_dt, l_ci_end_dt;
780 CLOSE c_cal_dates;
781
782 l_visa_type := get_visa_type(p_person_id, l_no_of_months);
783 l_interval := NUMTOYMINTERVAL(l_no_of_months,'month');
784
785 igs_sv_util.get_program_dates(p_person_id,l_prgm_end_date,l_prgm_start_date);
786
787
788 IF l_prgm_start_date IS NOT NULL AND l_prgm_end_date IS NOT NULL
789 AND trunc(l_prgm_start_date) >= trunc(sysdate) THEN
790
791 IF trunc(l_prgm_end_date) < trunc(l_ci_end_dt) OR
792 trunc(l_prgm_start_date) >= trunc(l_ci_end_dt) THEN
793 p_dflt_auth_start_dt := NULL ;
794 p_dflt_auth_end_dt := NULL;
795 RETURN;
796 END IF;
797
798 IF months_between(trunc(l_ci_end_dt),trunc(l_prgm_start_date)) <= l_no_of_months THEN
799 p_dflt_auth_start_dt := trunc(l_prgm_start_date) ;
800 p_dflt_auth_end_dt := trunc(l_ci_end_dt);
801 ELSE
802 l_temp_date := l_ci_end_dt - l_interval;
803 IF trunc(l_temp_date) >= trunc(l_prgm_start_date) THEN
804 p_dflt_auth_start_dt := l_temp_date ;
805 p_dflt_auth_end_dt := l_ci_end_dt;
806 END IF;
807 END IF;
808
809 ELSE
810
811 IF (l_prgm_end_date IS NOT NULL AND trunc(l_prgm_end_date) < trunc(l_ci_end_dt))
812 OR (trunc(sysdate) >= trunc(l_ci_end_dt) )THEN
813 p_dflt_auth_start_dt := NULL ;
814 p_dflt_auth_end_dt := NULL;
815 RETURN;
816 END IF;
817
818 IF months_between(trunc(l_ci_end_dt),trunc(sysdate)) <= l_no_of_months THEN
819 p_dflt_auth_start_dt := trunc(sysdate) ;
820 p_dflt_auth_end_dt := trunc(l_ci_end_dt);
821 ELSE
822 l_temp_date := l_ci_end_dt - l_interval;
823 IF trunc(l_temp_date) >= trunc(sysdate) THEN
824 p_dflt_auth_start_dt := trunc(l_temp_date);
825 p_dflt_auth_end_dt := trunc(l_ci_end_dt);
826 ELSE
827 p_dflt_auth_start_dt := NULL ;
828 p_dflt_auth_end_dt := NULL;
829 RETURN;
830 END IF;
831 END IF;
832
833 END IF;
834
835 IF p_dflt_auth_start_dt IS NOT NULL and p_dflt_auth_end_dt IS NOT NULL THEN
836 IF is_auth_rec_duration_exceeds(
837 p_person_id,
838 p_dflt_auth_start_dt,
839 p_dflt_auth_end_dt,
840 l_no_of_months) THEN
841
842 p_dflt_auth_start_dt := NULL ;
843 p_dflt_auth_end_dt := NULL;
844 RETURN;
845 END IF;
846 END IF;
847
848 END enrp_sevis_auth_dflt_dt;
849
850
851
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
861 AND f.end_dt BETWEEN s.start_dt AND s.end_dt;
862
863 l_val VARCHAR2(1);
864
865 BEGIN
866
867 OPEN c_chk_for_auth_rec_overlap(p_person_id);
868 FETCH c_chk_for_auth_rec_overlap INTO l_val;
869 IF c_chk_for_auth_rec_overlap%FOUND THEN
870 CLOSE c_chk_for_auth_rec_overlap;
871 RETURN TRUE;
872 END IF;
873 CLOSE c_chk_for_auth_rec_overlap;
874 RETURN FALSE;
875 END is_auth_records_overlap;
876
877 END igs_en_sevis;
[Home] [Help]