[Home] [Help]
PACKAGE BODY: APPS.IGS_SV_UTIL
Source
1 PACKAGE BODY IGS_SV_UTIL AS
2 /* $Header: IGSSV02B.pls 120.7 2006/07/27 07:36:19 svadde noship $ */
3
4 /******************************************************************
5
6 Copyright (c) 2006 Oracle Corporation, Redwood Shores, CA, USA
7 All rights reserved.
8
9 Created By : SreeKrishna Vadde
10
11 Date Created By : Wednesday, January 04, 2006
12
13 Purpose : This is a utility package for all sevis related operations
14
15
16 remarks : None
17
18 Change History
19
20 Who When What
21 -----------------------------------------------------------
22 ******************************************************************/
23 PROCEDURE get_prev_btch_dtls (
24 p_key_code IN VARCHAR2,
25 p_person_id IN NUMBER,
26 p_cur_batch_id IN NUMBER,
27 p_extra_param IN VARCHAR2,
28 x_prev_batch_id OUT NOCOPY NUMBER,
29 x_prev_btch_prcs_dt OUT NOCOPY DATE
30 )
31 /******************************************************************
32 Created By : SreeKrishna Vadde
33
34 Date Created By : Thursday, January 05, 2006
35
36 Purpose : It returns the previously processed batch id and it's process date.
37
38 Change History
39 Who When What
40 ------------------------------------------------------------------------
41 vskumar 31-May-2006 Bug 5245394. Xbuild3 performance fix. used bind parameters in the query.
42 ******************************************************************/
43 IS
44 batch_no_crsr bath_crsr;
45 l_prev_btch_id NUMBER;
46 l_tbl_name VARCHAR2 (30);
47 l_extra_critra VARCHAR2 (50) := '';
48 l_stmt VARCHAR2 (400);
49
50 CURSOR proc_dt_crsr (batch_no NUMBER)
51 IS
52 SELECT creation_date
53 FROM igs_sv_batches
54 WHERE batch_id = batch_no;
55 BEGIN
56 get_tbl_extra_params (
57 p_key_code,
58 p_extra_param,
59 l_tbl_name,
60 l_extra_critra
61 );
62
63 IF l_extra_critra IS NOT NULL THEN
64 l_stmt := 'SELECT MAX(BATCH_ID) FROM ' || l_tbl_name || ' WHERE PERSON_ID = :1 AND BATCH_ID < :2 '
65 || l_extra_critra;
66 OPEN batch_no_crsr FOR l_stmt USING p_person_id, p_cur_batch_id, p_extra_param;
67 ELSE
68 l_stmt := 'SELECT MAX(BATCH_ID) FROM ' || l_tbl_name || ' WHERE PERSON_ID = :1 AND BATCH_ID < :2 ';
69 OPEN batch_no_crsr FOR l_stmt USING p_person_id, p_cur_batch_id;
70 END IF;
71
72
73 FETCH batch_no_crsr INTO x_prev_batch_id;
74 CLOSE batch_no_crsr;
75 OPEN proc_dt_crsr (x_prev_batch_id);
76 FETCH proc_dt_crsr INTO x_prev_btch_prcs_dt;
77 CLOSE proc_dt_crsr;
78 END get_prev_btch_dtls;
79
80 PROCEDURE get_tbl_extra_params (
81 p_key_code IN VARCHAR2,
82 p_extra_param IN VARCHAR2,
83 x_tbl_name OUT NOCOPY VARCHAR2,
84 x_extra_critra OUT NOCOPY VARCHAR2
85 )
86 /******************************************************************
87 Created By : SreeKrishna Vadde
88
89 Date Created By : Thursday, January 05, 2006
90
91 Purpose : It returns the table name and criteria for perticuler key.
92
93 Internal
94
95 Change History
96 Who When What
97 vskumar 31-May-2006 Bug 5245394. Xbuild3 performance fix. used bind parameters in the query.
98 ------------------------------------------------------------------------
99
100 ******************************************************************/
101 IS
102 BEGIN
103 IF (p_key_code = 'SV_BIO') THEN
104 x_tbl_name := 'IGS_SV_BIO_INFO';
105 ELSIF (p_key_code = 'SV_CONVICTION') THEN
106 x_tbl_name := 'IGS_SV_CONVICTIONS';
107 x_extra_critra := ' and CONVICTION_ID = :p_extra_param ';
108 ELSIF (p_key_code = 'SV_DEPDNT') THEN
109 x_tbl_name := 'IGS_SV_DEPDNT_INFO';
110 x_extra_critra := ' and DEPDNT_ID = :p_extra_param ';
111 ELSIF ( p_key_code = 'SV_CPT_EMPL'
112 OR p_key_code = 'SV_OFF_EMPL'
113 OR p_key_code = 'SV_OPT_EMPL'
114 OR p_key_code = 'SV_EMPL'
115 ) THEN
116 x_tbl_name := 'IGS_SV_EMPL_INFO';
117 x_extra_critra := ' and NONIMG_EMPL_ID = :p_extra_param';
118 ELSIF (p_key_code = 'SV_FINANCIAL') THEN
119 x_tbl_name := 'IGS_SV_FINANCE_INFO';
120 ELSIF (p_key_code = 'SV_LEGAL') THEN
121 x_tbl_name := 'IGS_SV_LEGAL_INFO';
122 ELSIF (p_key_code = 'SV_OTHER') THEN
123 x_tbl_name := 'IGS_SV_OTH_INFO';
124 ELSIF ( p_key_code = 'SV_PRGMS'
125 OR p_key_code = 'SV_STATUS'
126 ) THEN
127 x_tbl_name := 'IGS_SV_PRGMS_INFO';
128 ELSIF (p_key_code = 'SV_SOA') THEN
129 x_tbl_name := 'IGS_SV_ADDRESSES';
130 x_extra_critra := ' and PARTY_SITE_ID = :p_extra_param';
131 ELSIF (p_key_code = 'SV_F_ADDR') THEN
132 x_tbl_name := 'IGS_SV_ADDRESSES';
133 ELSIF (p_key_code = 'SV_US_ADDR') THEN
134 x_tbl_name := 'IGS_SV_ADDRESSES';
135 ELSIF (p_key_code = 'SV_AUTH_DROP') THEN
136 x_tbl_name := 'IGS_SV_PRGMS_INFO';
137 x_extra_critra := ' and SEVIS_AUTH_ID = :p_extra_param';
138 END IF;
139 END get_tbl_extra_params;
140
141 FUNCTION ismutuallyexclusive (
142 p_person_id NUMBER,
143 p_batch_id NUMBER,
144 p_operation VARCHAR2,
145 p_information_type VARCHAR2
146 )
147 /******************************************************************
148 Created By : Manoj Kumar
149
150 Date Created By : Thursday, January 05, 2006
151
152 Purpose : Checks weather this record is mutually exclusive with any record in the current batch
153
154
155 Change History
156 Who When What
157 ------------------------------------------------------------------------
158
159 ******************************************************************/
160 RETURN BOOLEAN
161 IS
162 CURSOR c_check(cp_information_type VARCHAR2, cp_mutually_info_typ VARCHAR2)
163 IS
164 SELECT COUNT (*)
165 FROM igs_sv_btch_summary
166 WHERE batch_id = p_batch_id
167 AND person_id = p_person_id
168 AND ACTION_CODE IN (SELECT lookup_code
169 FROM igs_lookup_values
170 WHERE lookup_type = cp_mutually_info_typ)
171 AND action_code <> 'NEW' AND TAG_CODE = cp_information_type;
172
173 l_count NUMBER := 0;
174 BEGIN
175 OPEN c_check(p_information_type,p_information_type || '_MUT_EXCL_OPR' );
176 FETCH c_check INTO l_count;
177 CLOSE c_check;
178
179 IF l_count > 0 THEN
180 RETURN TRUE ;
181 ELSE
182 RETURN FALSE ;
183 END IF;
184 EXCEPTION
185 WHEN OTHERS THEN
186 null;
187 END;
188
189 PROCEDURE change_record_status (
190 p_person_id IN NUMBER,
191 p_batch_id IN NUMBER,
192 p_info_key IN VARCHAR2,
193 p_extra_param IN VARCHAR2,
194 p_change_data IN VARCHAR2,
195 p_summary_id IN NUMBER
196 )
197 /******************************************************************
198 Created By : SreeKrishna Vadde
199
200 Date Created By : Thursday, January 05, 2006
201
202 Purpose : Used to put and remove hold for a perticuler record
203
204 Change History
205 Who When What
206 vskumar 31-May-2006 Bug 5245394.Xbuild3 performance related fix. Replaced query and used bind parameters.
207 ------------------------------------------------------------------------
208
209 ******************************************************************/
210 IS
211 l_gen_xml_flg BOOLEAN := TRUE ;
212 l_batch_id igs_sv_batches.batch_id%TYPE;
213 l_tmp_btch_id igs_sv_batches.batch_id%TYPE;
214 l_batch_rec igs_sv_batches%ROWTYPE;
215 l_person_rec igs_sv_persons%ROWTYPE;
216 l_tbl_name VARCHAR2 (30);
217 l_extra_criteria VARCHAR2 (100);
218 l_rec_count NUMBER;
219 l_query VARCHAR2(2000);
220 l_pers_status igs_sv_persons.record_status%TYPE;
221 l_batch_status igs_sv_batches.BATCH_TYPE%TYPE;
222
223 CURSOR batch_crsr (cp_batch_id NUMBER)
224 IS
225 SELECT *
226 FROM igs_sv_batches
227 WHERE batch_id = cp_batch_id;
228
229 CURSOR c_batch_type (cp_batch_id NUMBER)
230 IS
231 SELECT BATCH_TYPE
232 FROM igs_sv_batches
233 WHERE batch_id = cp_batch_id;
234
235 CURSOR pers_rec_status (cp_person_id NUMBER,cp_batch_id NUMBER)
236 IS
237 select
238 record_status
239 from igs_sv_persons
240 where person_id = cp_person_id and batch_id = cp_batch_id;
241
242 BEGIN
243 SAVEPOINT batch_sav_pnt;
244
245 IF p_change_data = 'HOLD' THEN
246 UPDATE igs_sv_btch_summary
247 SET adm_action_code = 'HOLD',
248 last_update_date = SYSDATE,
249 last_update_login = fnd_global.user_id
250 WHERE summary_id = p_summary_id;
251
252 RETURN;
253 END IF;
254
255 OPEN batch_crsr (p_batch_id);
256 FETCH batch_crsr INTO l_batch_rec;
257 CLOSE batch_crsr;
258
259 IF l_batch_rec.batch_status = 'S' THEN
260 l_batch_id := l_batch_rec.batch_id;
261 l_gen_xml_flg := FALSE ;
262 --
263 /* ELSIF l_batch_rec.batch_status = 'X' THEN
264 l_batch_id := l_batch_rec.batch_id;
265 --*/
266 ELSE
267 --select igs_sv_batches_id_s.NEXTVAL into :i from dual;
268 l_batch_id := open_new_batch(p_person_id,p_batch_id,'HOLD');
269 END IF;
270
271 -- my_dbg('Before First Cursor - l_batch_rec.batch_status : ', l_batch_rec.batch_status);
272
273 OPEN pers_rec_status (p_person_id,p_batch_id);
274 FETCH pers_rec_status INTO l_pers_status;
275 CLOSE pers_rec_status;
276
277 -- my_dbg('After First Cursor - l_pers_status : ',l_pers_status);
278
279 IF ( l_pers_status = 'N' AND l_batch_rec.batch_status <> 'S' ) THEN
280 -- my_dbg('After New And Not S ','Came after');
281 OPEN c_batch_type (p_batch_id);
282 FETCH c_batch_type INTO l_batch_status;
283 CLOSE c_batch_type;
284
285 -- my_dbg('After New And Not S - l_batch_status : ',l_batch_status);
286
287 IF( (l_batch_status = 'I' AND (p_info_key = 'SV_BIO' OR p_info_key = 'SV_F_ADDR' OR p_info_key = 'SV_PRGMS' OR p_info_key = 'SV_FINANCIAL' ) )
288 OR (l_batch_status = 'E' AND (p_info_key = 'SV_BIO' OR p_info_key = 'SV_PRGMS' OR p_info_key = 'SV_US_ADDR' OR p_info_key = 'SV_SOA' OR p_info_key = 'SV_FINANCIAL' ) ) )
289 THEN
290
291 -- my_dbg('Inside Last IF : ',p_info_key);
292
293 FOR c_data_rec IN ( select SUMMARY_ID,
294 BATCH_ID,
295 PERSON_ID,
296 ACTION_CODE ,
297 TAG_CODE,
298 ADM_ACTION_CODE ,
299 OWNER_TABLE_NAME ,
300 OWNER_TABLE_IDENTIFIER
301 from igs_sv_btch_summary where batch_id = p_batch_id and person_id = p_person_id )
302
303 LOOP
304 -- my_dbg('Inside IF TAG_CODE : ',c_data_rec.TAG_CODE);
305
306 UPDATE igs_sv_btch_summary
307 SET batch_id = l_batch_id,
308 adm_action_code = c_data_rec.ADM_ACTION_CODE,
309 last_update_date = SYSDATE,
310 last_update_login = fnd_global.user_id
311 WHERE summary_id = c_data_rec.summary_id;
312 -- my_dbg('After UPDATE c_data_rec.summary_id : ',c_data_rec.summary_id);
313 get_tbl_extra_params (
314 c_data_rec.tag_code,
315 c_data_rec.owner_table_identifier,
316 l_tbl_name,
317 l_extra_criteria
318 );
319
320 -- my_dbg('After get_tbl_extra_params l_tbl_name : ',l_tbl_name);
321 -- my_dbg('After get_tbl_extra_params l_extra_criteria : ',l_extra_criteria);
322
323 IF l_extra_criteria IS NOT NULL THEN
324 l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id'|| l_extra_criteria;
325 EXECUTE IMMEDIATE l_query USING l_batch_id , p_batch_id, p_person_id, c_data_rec.owner_table_identifier;
326 ELSE
327 l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id';
328 EXECUTE IMMEDIATE l_query USING l_batch_id , p_batch_id, p_person_id;
329 END IF;
330
331 END LOOP;
332 END IF;
333 END IF;
334
335 UPDATE igs_sv_btch_summary
336 SET batch_id = l_batch_id,
337 adm_action_code = 'SEND',
338 last_update_date = SYSDATE,
339 last_update_login = fnd_global.user_id
340 WHERE summary_id = p_summary_id;
341
342 get_tbl_extra_params (
343 p_info_key,
344 p_extra_param,
345 l_tbl_name,
346 l_extra_criteria
347 );
348
349 IF l_extra_criteria IS NOT NULL THEN
350 l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id'
351 || l_extra_criteria;
352 EXECUTE IMMEDIATE l_query USING l_batch_id , p_batch_id, p_person_id, p_extra_param;
353 ELSE
354 l_query := 'UPDATE ' || l_tbl_name ||' set BATCH_ID = :l_batch_id where BATCH_ID = :p_batch_id and person_id = :p_person_id';
355 EXECUTE IMMEDIATE l_query USING l_batch_id , p_batch_id, p_person_id;
356 END IF;
357
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 ROLLBACK TO batch_sav_pnt;
362
363 IF fnd_log.test (fnd_log.level_statement, 'igs.plsql.IGS_SV_UTIL')
364 THEN
365 fnd_log.string_with_context (
366 fnd_log.level_statement,
367 'igs.plsql.igs_sv_util.change_record_status',
368 'Exception in unhold_record. ' || SQLERRM,NULL,NULL,NULL,NULL,NULL,NULL
369 );
370 END IF;
371 END;
372
373
374 FUNCTION open_new_batch(p_person_id number, p_batch_id number, p_caller varchar2)
375 return number is
376 /******************************************************************
377 Created By : SreeKrishna Vadde
378
379 Date Created By : Thursday, January 05, 2006
380
381 Purpose : Creates a new batch with same info as the current abtch and returns the new batch id
382
383 Internal
384
385 Change History
386 Who When What
387 ------------------------------------------------------------------------
388
389 ******************************************************************/
390 l_batch_id igs_sv_batches.batch_id%TYPE;
391 l_batch_rec igs_sv_batches%ROWTYPE;
392 l_person_rec igs_sv_persons%ROWTYPE;
393 l_rec_count NUMBER;
394
395 CURSOR batch_crsr (cp_batch_id NUMBER)
396 IS
397 SELECT *
398 FROM igs_sv_batches
399 WHERE batch_id = cp_batch_id;
400
401 CURSOR person_crsr (cp_person_id NUMBER, cp_batch_id NUMBER)
402 IS
403 SELECT *
404 FROM igs_sv_persons
405 WHERE batch_id = cp_batch_id AND person_id = cp_person_id;
406
407 CURSOR chk_rec_count_crsr (cp_person_id NUMBER, cp_batch_id NUMBER)
408 IS
409 SELECT COUNT (*)
410 FROM igs_sv_btch_summary
411 WHERE batch_id = cp_batch_id AND person_id = cp_person_id;
412
413 BEGIN
414 OPEN batch_crsr (p_batch_id);
415 FETCH batch_crsr INTO l_batch_rec;
416 CLOSE batch_crsr;
417
418 SELECT igs_sv_batches_id_s.NEXTVAL INTO l_batch_id from dual;
419
420 INSERT INTO igs_sv_batches
421 (batch_id, schema_version,
422 sevis_user_id, sevis_school_id,
423 batch_status, batch_type, creation_date,
424 created_by, last_updated_by,
425 last_update_date, last_update_login,
426 sevis_error_code,
427 xml_gen_date,
428 inbound_process_date,
429 sevis_school_org_id,
430 sevis_user_person_id)
431 VALUES (l_batch_id, l_batch_rec.schema_version,
432 l_batch_rec.sevis_user_id, l_batch_rec.sevis_school_id,
433 'S', l_batch_rec.batch_type, SYSDATE,
434 l_batch_rec.created_by, l_batch_rec.last_updated_by,
435 SYSDATE, l_batch_rec.last_update_login,
436 l_batch_rec.sevis_error_code,
437 l_batch_rec.xml_gen_date,
438 l_batch_rec.inbound_process_date,
439 l_batch_rec.sevis_school_org_id,
440 l_batch_rec.sevis_user_person_id);
441
442 OPEN person_crsr (p_person_id, p_batch_id);
443 FETCH person_crsr INTO l_person_rec;
444 CLOSE person_crsr;
445 OPEN chk_rec_count_crsr (p_person_id, p_batch_id);
446 FETCH chk_rec_count_crsr INTO l_rec_count;
447 CLOSE chk_rec_count_crsr;
448
449 IF (l_rec_count = 1 and p_caller <> 'CONN_JOB')
450 THEN
451 UPDATE igs_sv_persons
452 SET batch_id = l_batch_id
453 WHERE batch_id = p_batch_id AND person_id = p_person_id;
454 ELSE
455 INSERT INTO igs_sv_persons
456 (batch_id, person_id,
457 record_number, form_id,
458 print_form, pdso_sevis_id,
459 record_status,
460 person_number,
461 sevis_user_id,
462 issuing_reason,
463 curr_session_end_date,
464 next_session_start_date,
465 other_reason,
466 transfer_from_school,
467 ev_create_reason,
468 ev_form_number, creation_date,
469 created_by, last_updated_by, last_update_date,
470 last_update_login,
471 init_prgm_start_date,
472 sevis_error_code,
473 sevis_error_element,
474 no_show_flag, status_code,
475 last_session_flag,
476 adjudicated_flag,
477 REPRINT_RSN_CODE ,
478 reprint_remarks, remarks,
479 pdso_sevis_person_id)
480 VALUES (l_batch_id, l_person_rec.person_id,
481 l_person_rec.record_number, l_person_rec.form_id,
482 l_person_rec.print_form, l_person_rec.pdso_sevis_id,
483 l_person_rec.record_status,
484 l_person_rec.person_number,
485 l_person_rec.sevis_user_id,
486 l_person_rec.issuing_reason,
487 l_person_rec.curr_session_end_date,
488 l_person_rec.next_session_start_date,
489 l_person_rec.other_reason,
490 l_person_rec.transfer_from_school,
491 l_person_rec.ev_create_reason,
492 l_person_rec.ev_form_number, SYSDATE,
493 fnd_global.user_id, fnd_global.user_id, SYSDATE,
494 l_person_rec.last_update_login,
495 l_person_rec.init_prgm_start_date,
496 l_person_rec.sevis_error_code,
497 l_person_rec.sevis_error_element,
498 l_person_rec.no_show_flag, l_person_rec.status_code,
499 l_person_rec.last_session_flag,
500 l_person_rec.adjudicated_flag,
501 l_person_rec.REPRINT_RSN_CODE ,
502 l_person_rec.reprint_remarks, l_person_rec.remarks,
503 l_person_rec.pdso_sevis_person_id);
504 END IF;
505
506 return l_batch_id;
507 END open_new_batch;
508
509
510 procedure create_Person_Rec(p_person_id number, p_old_batch_id number, p_new_batch_id number)
511 is
512 /******************************************************************
513 Created By : SreeKrishna Vadde
514
515 Date Created By : Thursday, January 05, 2006
516
517 Purpose : Creates a new batch with same info as the current abtch and returns the new batch id
518
519 Internal
520
521 Change History
522 Who When What
523 ------------------------------------------------------------------------
524
525 ******************************************************************/
526 l_batch_id igs_sv_batches.batch_id%TYPE;
527 l_batch_rec igs_sv_batches%ROWTYPE;
528 l_person_rec igs_sv_persons%ROWTYPE;
529 l_rec_count NUMBER;
530
531
532 CURSOR person_crsr (cp_person_id NUMBER, cp_batch_id NUMBER)
533 IS
534 SELECT *
535 FROM igs_sv_persons
536 WHERE batch_id = cp_batch_id AND person_id = cp_person_id;
537
538 BEGIN
539
540 OPEN person_crsr (p_person_id, p_old_batch_id);
541 FETCH person_crsr INTO l_person_rec;
542 CLOSE person_crsr;
543
544 INSERT INTO igs_sv_persons
545 (batch_id, person_id,
546 record_number, form_id,
547 print_form, pdso_sevis_id,
548 record_status,
549 person_number,
550 sevis_user_id,
551 issuing_reason,
552 curr_session_end_date,
553 next_session_start_date,
554 other_reason,
555 transfer_from_school,
556 ev_create_reason,
557 ev_form_number, creation_date,
558 created_by, last_updated_by, last_update_date,
559 last_update_login,
560 init_prgm_start_date,
561 sevis_error_code,
562 sevis_error_element,
563 no_show_flag, status_code,
564 last_session_flag,
565 adjudicated_flag,
566 REPRINT_RSN_CODE ,
567 reprint_remarks, remarks)
568 VALUES (p_new_batch_id, l_person_rec.person_id,
569 l_person_rec.record_number, l_person_rec.form_id,
570 l_person_rec.print_form, l_person_rec.pdso_sevis_id,
571 l_person_rec.record_status,
572 l_person_rec.person_number,
573 l_person_rec.sevis_user_id,
574 l_person_rec.issuing_reason,
575 l_person_rec.curr_session_end_date,
576 l_person_rec.next_session_start_date,
577 l_person_rec.other_reason,
578 l_person_rec.transfer_from_school,
579 l_person_rec.ev_create_reason,
580 l_person_rec.ev_form_number, SYSDATE,
581 fnd_global.user_id, fnd_global.user_id, SYSDATE,
582 l_person_rec.last_update_login,
583 l_person_rec.init_prgm_start_date,
584 l_person_rec.sevis_error_code,
585 l_person_rec.sevis_error_element,
586 l_person_rec.no_show_flag, l_person_rec.status_code,
587 l_person_rec.last_session_flag,
588 l_person_rec.adjudicated_flag,
589 l_person_rec.REPRINT_RSN_CODE ,
590 l_person_rec.reprint_remarks, l_person_rec.remarks);
591
592 END create_Person_Rec;
593
594
595 FUNCTION GET_BTCH_PROCESS_DT(
596 p_person_id igs_sv_prgms_info.person_id%TYPE,
597 p_sevis_auth_id igs_sv_prgms_info.sevis_auth_id%TYPE
598 ) RETURN DATE IS
599 /******************************************************************
600 Created By : Manoj Kumar
601
602 Date Created By : Thursday, January 05, 2006
603
604 Purpose : It returns Process date
605
606 Internal
607
608 Change History
609 Who When What
610 ------------------------------------------------------------------------
611
612 ******************************************************************/
613 CURSOR c_btch_date IS
614 SELECT MAX(prgm.BATCH_ID) , btch.creation_date
615 FROM igs_sv_prgms_info prgm, IGS_SV_BATCHES btch
616 WHERE prgm.batch_id = btch.batch_id AND
617 prgm.person_id = p_person_id AND
618 prgm.sevis_auth_id = p_sevis_auth_id AND
619 prgm.prgm_action_type = 'DB' AND
620 btch.batch_status <> 'E'
621 GROUP BY btch.creation_date;
622 l_btch_date c_btch_date%ROWTYPE;
623 BEGIN
624 OPEN c_btch_date;
625 FETCH c_btch_date INTO l_btch_date;
626 IF c_btch_date%ROWCOUNT > 0 THEN
627 CLOSE c_btch_date;
628 RETURN l_btch_date.creation_date;
629 END IF;
630 CLOSE c_btch_date;
631 RETURN NULL;
632 EXCEPTION
633 WHEN OTHERS THEN
634 null;
635 END GET_BTCH_PROCESS_DT;
636
637
638 PROCEDURE GET_PROGRAM_DATES(
639 p_person_id IN igs_pe_nonimg_form.person_id%TYPE,
640 p_prgm_end_date OUT NOCOPY igs_pe_nonimg_form.prgm_end_date%TYPE,
641 p_prgm_start_date OUT NOCOPY igs_pe_nonimg_form.prgm_start_date%TYPE
642 ) IS
643 /******************************************************************
644 Created By : Preeti Bhardwaj
645
646 Date Created By : Monday, April 10, 2006
647
648 Purpose : It returns program dates
649
650 Internal
651
652 Change History
653 Who When What
654 ------------------------------------------------------------------------
655
656 ******************************************************************/
657 CURSOR c_prg_dates(cp_form_id igs_pe_nonimg_form.nonimg_form_id%TYPE) IS
658 SELECT prgm_start_date, prgm_end_date
659 FROM igs_pe_nonimg_form ipnf
660 WHERE ipnf.person_id = p_person_id AND
661 ipnf.nonimg_form_id = cp_form_id;
662
663
664 CURSOR c_action_date(cp_form_id igs_pe_nonimg_form.nonimg_form_id%TYPE) IS
665 SELECT prgm_start_date prgm_start_date,
666 prgm_end_date prgm_end_date
667 FROM igs_pe_nonimg_stat
668 WHERE nonimg_form_id = cp_form_id
669 ORDER BY last_update_date DESC;
670
671 CURSOR c_form_id IS
672 SELECT nonimg_form_id
673 FROM igs_pe_nonimg_form
674 WHERE person_id = p_person_id AND
675 form_status = 'A';
676
677 l_prgm_end_date DATE;
678 l_prgm_start_date DATE;
679 l_temp_end_date DATE;
680 l_temp_start_date DATE;
681 l_form_id igs_pe_nonimg_form.nonimg_form_id%TYPE := 0;
682
683 BEGIN
684 OPEN c_form_id;
685 FETCH c_form_id INTO l_form_id;
686 CLOSE c_form_id;
687
688 FOR c_action_date_rec IN c_action_date(l_form_id) LOOP
689 IF c_action_date_rec.prgm_end_date IS NOT NULL AND l_prgm_end_date IS NULL THEN
690 l_prgm_end_date := c_action_date_rec.prgm_end_date;
691 END IF;
692 IF c_action_date_rec.prgm_start_date IS NOT NULL AND l_prgm_start_date IS NULL THEN
693 l_prgm_start_date := c_action_date_rec.prgm_start_date;
694 END IF;
695 IF l_prgm_start_date IS NOT NULL AND l_prgm_end_date IS NOT NULL THEN
696 EXIT;
697 END IF;
698
699 END LOOP;
700
701 OPEN c_prg_dates(l_form_id);
702 FETCH c_prg_dates INTO l_temp_start_date, l_temp_end_date;
703 CLOSE c_prg_dates;
704
705 IF l_prgm_start_date IS NULL THEN
706 l_prgm_start_date := l_temp_start_date;
707 END IF;
708 IF l_prgm_end_date IS NULL THEN
709 l_prgm_end_date := l_temp_end_date;
710 END IF;
711 p_prgm_end_date := l_prgm_end_date;
712 p_prgm_start_date := l_prgm_start_date;
713 EXCEPTION
714 WHEN OTHERS THEN
715 null;
716 END GET_PROGRAM_DATES;
717
718 END igs_sv_util;