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_AD_IMP_026 dependencies on IGS_PE_VISA

Line 33: CURSOR visa_dtls(cp_interface_run_id igs_pe_visa_int.interface_run_id%TYPE) IS

29: || Description: Change call from GET_WHERE_CLAUSE to GET_WHERE_CLAUSE_API as a part of Literal fix
30: */
31:
32:
33: CURSOR visa_dtls(cp_interface_run_id igs_pe_visa_int.interface_run_id%TYPE) IS
34: SELECT vi.*, i.person_id
35: FROM igs_pe_visa_int vi,
36: igs_ad_interface_all i
37: WHERE vi.interface_id = i.interface_id

Line 35: FROM igs_pe_visa_int vi,

31:
32:
33: CURSOR visa_dtls(cp_interface_run_id igs_pe_visa_int.interface_run_id%TYPE) IS
34: SELECT vi.*, i.person_id
35: FROM igs_pe_visa_int vi,
36: igs_ad_interface_all i
37: WHERE vi.interface_id = i.interface_id
38: AND vi.STATUS = '2'
39: AND vi.interface_run_id = cp_interface_run_id

Line 44: cp_vi_status_1 igs_pe_visa_int.status%TYPE,

40: AND i.interface_run_id = cp_interface_run_id;
41:
42:
43: CURSOR visit_dtls(cp_vh_status_2 igs_pe_vst_hist_int.status%TYPE,
44: cp_vi_status_1 igs_pe_visa_int.status%TYPE,
45: cp_interface_run_id igs_pe_vst_hist_int.interface_run_id%TYPE) IS
46: SELECT vh.*, i.person_id,pev.visa_id,pev.visa_issue_date issue_date,pev.visa_expiry_date expiry_date
47: FROM igs_pe_vst_hist_int vh,
48: igs_ad_interface_all i,

Line 49: igs_pe_visa_int vi,

45: cp_interface_run_id igs_pe_vst_hist_int.interface_run_id%TYPE) IS
46: SELECT vh.*, i.person_id,pev.visa_id,pev.visa_issue_date issue_date,pev.visa_expiry_date expiry_date
47: FROM igs_pe_vst_hist_int vh,
48: igs_ad_interface_all i,
49: igs_pe_visa_int vi,
50: igs_pe_visa pev
51: WHERE vh.interface_visa_id = vi.interface_visa_id
52: AND vi.interface_id = i.interface_id
53: AND pev.person_id = i.person_id

Line 50: igs_pe_visa pev

46: SELECT vh.*, i.person_id,pev.visa_id,pev.visa_issue_date issue_date,pev.visa_expiry_date expiry_date
47: FROM igs_pe_vst_hist_int vh,
48: igs_ad_interface_all i,
49: igs_pe_visa_int vi,
50: igs_pe_visa pev
51: WHERE vh.interface_visa_id = vi.interface_visa_id
52: AND vi.interface_id = i.interface_id
53: AND pev.person_id = i.person_id
54: AND vh.STATUS = cp_vh_status_2

Line 70: p_error_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE,

66: AND pi.interface_run_id = cp_interface_run_id
67: AND i.interface_run_id = cp_interface_run_id;
68:
69: FUNCTION validate_visa(visa_rec IN visa_dtls%ROWTYPE,
70: p_error_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE,
71: p_mode IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN IS
72:
73: CURSOR birth_dt_cur(cp_person_id igs_ad_interface.person_id%TYPE) IS
74: SELECT birth_date birth_dt

Line 255: UPDATE igs_pe_visa_int

251: l_error := NULL;
252: p_error_code := l_error;
253:
254: IF p_mode IS NULL THEN
255: UPDATE igs_pe_visa_int
256: SET status = '1',
257: error_code = l_error
258: WHERE interface_visa_id = visa_rec.interface_visa_id;
259: END IF;

Line 291: UPDATE igs_pe_visa_int

287: p_error_code := l_error;
288:
289: IF l_error = 'E555' THEN
290: IF p_mode IS NULL THEN
291: UPDATE igs_pe_visa_int
292: SET status = '4',
293: error_code = l_error
294: WHERE interface_visa_id = visa_rec.interface_visa_id;
295:

Line 298: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');

294: WHERE interface_visa_id = visa_rec.interface_visa_id;
295:
296: -- CALL LOG DETAIL
297: IF l_enable_log = 'Y' THEN
298: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
299: END IF;
300: END IF;
301: RETURN TRUE;
302: ELSE

Line 304: UPDATE igs_pe_visa_int

300: END IF;
301: RETURN TRUE;
302: ELSE
303: IF p_mode IS NULL THEN
304: UPDATE igs_pe_visa_int
305: SET status = '3',
306: error_code = l_error
307: WHERE interface_visa_id = visa_rec.interface_visa_id;
308:

Line 311: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');

307: WHERE interface_visa_id = visa_rec.interface_visa_id;
308:
309: -- CALL LOG DETAIL
310: IF l_enable_log = 'Y' THEN
311: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
312: END IF;
313: END IF;
314: RETURN FALSE;
315: END IF;

Line 319: UPDATE igs_pe_visa_int

315: END IF;
316:
317: WHEN OTHERS THEN
318: IF p_mode IS NULL THEN
319: UPDATE igs_pe_visa_int
320: SET status = '3',
321: error_code = l_error
322: WHERE interface_visa_id = visa_rec.interface_visa_id;
323: END IF;

Line 349: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');

345: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
346: END IF;
347:
348: IF l_enable_log = 'Y' THEN
349: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
350: END IF;
351: RETURN FALSE;
352: END Validate_Visa;
353:

Line 387: CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,

383: || Added a new procedure that will be called by the Visa Public API.
384: || Changes as part of Bug # 3847525
385: */
386:
387: CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,
388: cp_visa_type igs_pe_visa.visa_type%TYPE ,
389: cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS
390: SELECT rowid,vi.*
391: FROM IGS_PE_VISA vi

Line 388: cp_visa_type igs_pe_visa.visa_type%TYPE ,

384: || Changes as part of Bug # 3847525
385: */
386:
387: CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,
388: cp_visa_type igs_pe_visa.visa_type%TYPE ,
389: cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS
390: SELECT rowid,vi.*
391: FROM IGS_PE_VISA vi
392: WHERE person_id = cp_person_id AND

Line 389: cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS

385: */
386:
387: CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,
388: cp_visa_type igs_pe_visa.visa_type%TYPE ,
389: cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS
390: SELECT rowid,vi.*
391: FROM IGS_PE_VISA vi
392: WHERE person_id = cp_person_id AND
393: visa_type = cp_visa_type AND

Line 391: FROM IGS_PE_VISA vi

387: CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,
388: cp_visa_type igs_pe_visa.visa_type%TYPE ,
389: cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS
390: SELECT rowid,vi.*
391: FROM IGS_PE_VISA vi
392: WHERE person_id = cp_person_id AND
393: visa_type = cp_visa_type AND
394: visa_issue_date = cp_visa_issue_date; -- end_date IS NULL check removed
395:

Line 422: l_visaid IGS_PE_VISA.VISA_ID%TYPE;

418: PROCEDURE crt_pe_visa(visa_rec IN visa_dtls%ROWTYPE)
419: AS
420:
421: l_rowid ROWID := NULL;
422: l_visaid IGS_PE_VISA.VISA_ID%TYPE;
423: l_error VARCHAR2(30);
424: l_message_name VARCHAR2(30);
425: l_app VARCHAR2(50);
426:

Line 430: IGS_PE_VISA_PKG.INSERT_ROW(

426:
427: BEGIN
428: SAVEPOINT before_insert;
429:
430: IGS_PE_VISA_PKG.INSERT_ROW(
431: X_ROWID => l_rowid,
432: X_VISA_ID => l_visaid,
433: X_PERSON_ID => visa_rec.person_id,
434: X_VISA_TYPE => visa_rec.VISA_TYPE ,

Line 471: UPDATE igs_pe_visa_int

467:
468: -- IF SUCCESSFUL INSERT THEN
469:
470: l_error := NULL;
471: UPDATE igs_pe_visa_int
472: SET status = '1',
473: error_code = NULL
474: WHERE interface_visa_id = visa_rec.interface_visa_id;
475:

Line 482: IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN

478:
479: ROLLBACK TO before_insert;
480: FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
481:
482: IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN
483: l_error:='E558';
484: ELSIF l_message_name = 'IGS_PE_VIS_ASOC_PASS_EXP' THEN
485: l_error:='E287';
486: ELSE

Line 511: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');

507:
508: END IF;
509:
510: IF l_enable_log = 'Y' THEN
511: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
512: END IF;
513:
514:
515: UPDATE igs_pe_visa_int

Line 515: UPDATE igs_pe_visa_int

511: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error,'IGS_PE_VISA_INT');
512: END IF;
513:
514:
515: UPDATE igs_pe_visa_int
516: SET status = '3',
517: error_code = l_error
518: WHERE interface_visa_id = visa_rec.interface_visa_id;
519:

Line 528: l_status igs_pe_visa_int.status%TYPE;

524: dup_visa_rec IN chk_duplicate%ROWTYPE)
525: AS
526:
527: l_error VARCHAR2(30);
528: l_status igs_pe_visa_int.status%TYPE;
529: l_exists VARCHAR2(1) := NULL;
530: l_message_name VARCHAR2(30);
531: l_app VARCHAR2(50);
532:

Line 550: UPDATE igs_pe_visa_int

546: FETCH visit_histry_date INTO l_exists;
547: CLOSE visit_histry_date;
548:
549: IF l_exists IS NOT NULL THEN
550: UPDATE igs_pe_visa_int
551: SET status = '3',
552: error_code = 'E559'
553: WHERE interface_visa_id = visa_rec.interface_visa_id;
554:

Line 556: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E559','IGS_PE_VISA_INT');

552: error_code = 'E559'
553: WHERE interface_visa_id = visa_rec.interface_visa_id;
554:
555: IF l_enable_log = 'Y' THEN
556: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E559','IGS_PE_VISA_INT');
557: END IF;
558: ELSE
559: IGS_PE_VISA_PKG.UPDATE_ROW (
560: X_ROWID => dup_visa_rec.rowid,

Line 559: IGS_PE_VISA_PKG.UPDATE_ROW (

555: IF l_enable_log = 'Y' THEN
556: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E559','IGS_PE_VISA_INT');
557: END IF;
558: ELSE
559: IGS_PE_VISA_PKG.UPDATE_ROW (
560: X_ROWID => dup_visa_rec.rowid,
561: X_VISA_ID => dup_visa_rec.visa_id,
562: X_PERSON_ID => NVL(visa_rec.person_id,dup_visa_rec.person_id),
563: X_VISA_TYPE => NVL(visa_rec.visa_type,dup_visa_rec.visa_type),

Line 597: UPDATE igs_pe_visa_int

593: X_ATTRIBUTE20 => NVL(visa_rec.attribute20,dup_visa_rec.attribute20) ,
594: X_visa_issuing_country => NVL(visa_rec.visa_issuing_country,dup_visa_rec.visa_issuing_country) ,
595: X_MODE => 'R');
596:
597: UPDATE igs_pe_visa_int
598: SET status = '1',
599: error_code = NULL,
600: match_ind = '18'
601: WHERE interface_visa_id = visa_rec.interface_visa_id;

Line 608: IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN

604: EXCEPTION
605: WHEN OTHERS THEN
606: ROLLBACK TO before_update;
607: FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
608: IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN
609: UPDATE igs_pe_visa_int
610: SET status = '3',
611: error_code = 'E558'
612: WHERE interface_visa_id = visa_rec.interface_visa_id;

Line 609: UPDATE igs_pe_visa_int

605: WHEN OTHERS THEN
606: ROLLBACK TO before_update;
607: FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
608: IF l_message_name = 'IGS_PE_VISA_DATE_OVERLAP' THEN
609: UPDATE igs_pe_visa_int
610: SET status = '3',
611: error_code = 'E558'
612: WHERE interface_visa_id = visa_rec.interface_visa_id;
613:

Line 615: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E558','IGS_PE_VISA_INT');

611: error_code = 'E558'
612: WHERE interface_visa_id = visa_rec.interface_visa_id;
613:
614: IF l_enable_log = 'Y' THEN
615: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E558','IGS_PE_VISA_INT');
616: END IF;
617:
618: ELSIF l_message_name = 'IGS_PE_VIS_ASOC_PASS_EXP' THEN
619: UPDATE igs_pe_visa_int

Line 619: UPDATE igs_pe_visa_int

615: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E558','IGS_PE_VISA_INT');
616: END IF;
617:
618: ELSIF l_message_name = 'IGS_PE_VIS_ASOC_PASS_EXP' THEN
619: UPDATE igs_pe_visa_int
620: SET status = '3',
621: error_code = 'E287'
622: WHERE interface_visa_id = visa_rec.interface_visa_id;
623:

Line 625: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E287','IGS_PE_VISA_INT');

621: error_code = 'E287'
622: WHERE interface_visa_id = visa_rec.interface_visa_id;
623:
624: IF l_enable_log = 'Y' THEN
625: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E287','IGS_PE_VISA_INT');
626: END IF;
627:
628: ELSE
629: UPDATE igs_pe_visa_int

Line 629: UPDATE igs_pe_visa_int

625: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E287','IGS_PE_VISA_INT');
626: END IF;
627:
628: ELSE
629: UPDATE igs_pe_visa_int
630: SET status = '3',
631: error_code = 'E014'
632: WHERE interface_visa_id = visa_rec.interface_visa_id;
633:

Line 657: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E014','IGS_PE_VISA_INT');

653: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
654: END IF;
655:
656: IF l_enable_log = 'Y' THEN
657: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,'E014','IGS_PE_VISA_INT');
658: END IF;
659:
660: END IF;
661:

Line 692: UPDATE igs_pe_visa_int

688: l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
689:
690: IF l_rule = 'E' OR l_rule = 'I' THEN
691:
692: UPDATE igs_pe_visa_int
693: SET status='3',
694: error_code = 'E695'
695: WHERE
696: interface_run_id=l_interface_run_id

Line 702: UPDATE igs_pe_visa_int vi

698: AND match_ind IS NOT NULL;
699:
700: IF l_rule = 'E' THEN
701:
702: UPDATE igs_pe_visa_int vi
703: SET status='1', match_ind='19'
704: WHERE interface_run_id=l_interface_run_id
705: AND STATUS = '2'
706: AND EXISTS( SELECT vs.rowid

Line 707: FROM igs_pe_visa vs,

703: SET status='1', match_ind='19'
704: WHERE interface_run_id=l_interface_run_id
705: AND STATUS = '2'
706: AND EXISTS( SELECT vs.rowid
707: FROM igs_pe_visa vs,
708: igs_ad_interface_all ad
709: WHERE ad.interface_id = vi.interface_id AND
710: ad.interface_run_id = l_interface_run_id AND
711: vs.person_id = ad.person_id AND

Line 718: UPDATE igs_pe_visa_int

714: END IF;
715:
716: ELSIF l_rule = 'R' THEN
717:
718: UPDATE igs_pe_visa_int
719: SET status = '1'
720: WHERE
721: interface_run_id=l_interface_run_id
722: AND status = '2'

Line 725: UPDATE igs_pe_visa_int

721: interface_run_id=l_interface_run_id
722: AND status = '2'
723: AND match_ind IN ('18','19','22','23');
724:
725: UPDATE igs_pe_visa_int
726: SET status = '3',
727: error_code = 'E695'
728: WHERE
729: interface_run_id=l_interface_run_id

Line 733: UPDATE igs_pe_visa_int vi

729: interface_run_id=l_interface_run_id
730: AND status = '2'
731: AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
732:
733: UPDATE igs_pe_visa_int vi
734: SET status='1',
735: match_ind = '23'
736: WHERE
737: interface_run_id=l_interface_run_id

Line 741: FROM igs_pe_visa vs,

737: interface_run_id=l_interface_run_id
738: AND status = '2'
739: AND match_ind IS NULL
740: AND EXISTS( SELECT vs.rowid
741: FROM igs_pe_visa vs,
742: igs_ad_interface_all ad
743: WHERE ad.interface_id = vi.interface_id AND
744: ad.interface_run_id = l_interface_run_id AND
745: vs.visa_type = UPPER(vi.visa_type) AND

Line 779: UPDATE igs_pe_visa_int vi

775: ((UPPER(vs.attribute19) = UPPER(vi.attribute19)) OR ((vs.attribute19 IS NULL) AND (vi.attribute19 IS NULL))) AND
776: ((UPPER(vs.attribute20) = UPPER(vi.attribute20)) OR ((vs.attribute20 IS NULL) AND (vi.attribute20 IS NULL))) AND
777: ((UPPER(vs.visa_issuing_country) = UPPER(vi.visa_issuing_country)) OR ((vs.visa_issuing_country IS NULL) AND (vi.visa_issuing_country IS NULL))));
778:
779: UPDATE igs_pe_visa_int vi
780: SET status = '3',
781: match_ind='20',
782: dup_visa_id = (SELECT visa_id
783: FROM igs_pe_visa vs,

Line 783: FROM igs_pe_visa vs,

779: UPDATE igs_pe_visa_int vi
780: SET status = '3',
781: match_ind='20',
782: dup_visa_id = (SELECT visa_id
783: FROM igs_pe_visa vs,
784: igs_ad_interface_all ad
785: WHERE ad.interface_id = vi.interface_id AND
786: ad.interface_run_id = l_interface_run_id AND
787: vs.person_id = ad.person_id AND

Line 795: FROM igs_pe_visa vs,

791: interface_run_id=l_interface_run_id AND
792: status = '2' AND
793: match_ind IS NULL AND
794: EXISTS (SELECT vs.rowid
795: FROM igs_pe_visa vs,
796: igs_ad_interface_all ad
797: WHERE ad.interface_id = vi.interface_id AND
798: ad.interface_run_id = l_interface_run_id AND
799: vs.person_id = ad.person_id AND

Line 843: UPDATE igs_pe_visa_int

839: END IF;
840: END IF;
841:
842: IF l_error_code = 'E555' THEN
843: UPDATE igs_pe_visa_int
844: SET status = '4',
845: error_code = l_error_code
846: WHERE interface_visa_id = visa_rec.interface_visa_id;
847:

Line 851: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error_code,'IGS_PE_VISA_INT');

847:
848: -- CALL LOG DETAIL
849:
850: IF l_enable_log = 'Y' THEN
851: igs_ad_imp_001.logerrormessage(visa_rec.interface_visa_id,l_error_code,'IGS_PE_VISA_INT');
852: END IF;
853: END IF;
854:
855: IF l_processed_records = 100 THEN

Line 863: FUNCTION validate_visa_pub(api_visa_rec IGS_PE_VISAPASS_PUB.visa_rec_type,

859:
860: END LOOP;
861: END prc_pe_visa;
862:
863: FUNCTION validate_visa_pub(api_visa_rec IGS_PE_VISAPASS_PUB.visa_rec_type,
864: p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS
865:
866: l_visa_rec visa_dtls%ROWTYPE;
867: l_return_value BOOLEAN;

Line 864: p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS

860: END LOOP;
861: END prc_pe_visa;
862:
863: FUNCTION validate_visa_pub(api_visa_rec IGS_PE_VISAPASS_PUB.visa_rec_type,
864: p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS
865:
866: l_visa_rec visa_dtls%ROWTYPE;
867: l_return_value BOOLEAN;
868:

Line 1027: FUNCTION validate_passport_pub(api_pass_rec IGS_PE_VISAPASS_PUB.passport_rec_type,

1023:
1024: RETURN FALSE;
1025: END Validate_Passport;
1026:
1027: FUNCTION validate_passport_pub(api_pass_rec IGS_PE_VISAPASS_PUB.passport_rec_type,
1028: p_err_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1029:
1030: l_pass_rec pass_dtls%ROWTYPE;
1031: l_return_value BOOLEAN;

Line 1568: cp_visa_id igs_pe_visa.visa_id%TYPE,

1564: person_id = cp_person_id;
1565:
1566:
1567: CURSOR valid_entry_date(cp_person_id igs_ad_interface.person_id%TYPE,
1568: cp_visa_id igs_pe_visa.visa_id%TYPE,
1569: cp_visit_start_date igs_pe_visa.visa_issue_date%TYPE) IS
1570: SELECT 'Y' FROM IGS_PE_VISA
1571: WHERE person_id = cp_person_id AND
1572: visa_id = cp_visa_id AND

Line 1569: cp_visit_start_date igs_pe_visa.visa_issue_date%TYPE) IS

1565:
1566:
1567: CURSOR valid_entry_date(cp_person_id igs_ad_interface.person_id%TYPE,
1568: cp_visa_id igs_pe_visa.visa_id%TYPE,
1569: cp_visit_start_date igs_pe_visa.visa_issue_date%TYPE) IS
1570: SELECT 'Y' FROM IGS_PE_VISA
1571: WHERE person_id = cp_person_id AND
1572: visa_id = cp_visa_id AND
1573: cp_visit_start_date BETWEEN visa_issue_date AND visa_expiry_date;

Line 1570: SELECT 'Y' FROM IGS_PE_VISA

1566:
1567: CURSOR valid_entry_date(cp_person_id igs_ad_interface.person_id%TYPE,
1568: cp_visa_id igs_pe_visa.visa_id%TYPE,
1569: cp_visit_start_date igs_pe_visa.visa_issue_date%TYPE) IS
1570: SELECT 'Y' FROM IGS_PE_VISA
1571: WHERE person_id = cp_person_id AND
1572: visa_id = cp_visa_id AND
1573: cp_visit_start_date BETWEEN visa_issue_date AND visa_expiry_date;
1574:

Line 1710: FUNCTION validate_visit_histry_pub(api_visit_rec IGS_PE_VISAPASS_PUB.visit_hstry_rec_type,

1706:
1707: RETURN FALSE;
1708: END Validate_visit_histry;
1709:
1710: FUNCTION validate_visit_histry_pub(api_visit_rec IGS_PE_VISAPASS_PUB.visit_hstry_rec_type,
1711: p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS
1712:
1713: l_visit_rec visit_dtls%ROWTYPE;
1714: l_return_value BOOLEAN;

Line 1711: p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS

1707: RETURN FALSE;
1708: END Validate_visit_histry;
1709:
1710: FUNCTION validate_visit_histry_pub(api_visit_rec IGS_PE_VISAPASS_PUB.visit_hstry_rec_type,
1711: p_err_code OUT NOCOPY igs_pe_visa_int.error_code%TYPE) RETURN BOOLEAN IS
1712:
1713: l_visit_rec visit_dtls%ROWTYPE;
1714: l_return_value BOOLEAN;
1715:

Line 1716: CURSOR visit_visa_dtls(cp_visa_id igs_pe_visa.visa_id%TYPE) IS

1712:
1713: l_visit_rec visit_dtls%ROWTYPE;
1714: l_return_value BOOLEAN;
1715:
1716: CURSOR visit_visa_dtls(cp_visa_id igs_pe_visa.visa_id%TYPE) IS
1717: SELECT person_id,visa_issue_date issue_date,visa_expiry_date expiry_date
1718: FROM igs_pe_visa
1719: WHERE visa_id = cp_visa_id;
1720:

Line 1718: FROM igs_pe_visa

1714: l_return_value BOOLEAN;
1715:
1716: CURSOR visit_visa_dtls(cp_visa_id igs_pe_visa.visa_id%TYPE) IS
1717: SELECT person_id,visa_issue_date issue_date,visa_expiry_date expiry_date
1718: FROM igs_pe_visa
1719: WHERE visa_id = cp_visa_id;
1720:
1721: visit_visa_rec visit_visa_dtls%ROWTYPE;
1722:

Line 2068: FROM igs_pe_visa_int vi

2064: FROM igs_pe_visit_histry vs
2065: WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
2066: UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
2067: AND EXISTS( SELECT vi.rowid
2068: FROM igs_pe_visa_int vi
2069: WHERE vi.interface_visa_id = vh.interface_visa_id AND
2070: vi.status = '1');
2071: END IF;
2072:

Line 2081: FROM igs_pe_visa_int vi

2077: WHERE interface_run_id=l_interface_run_id
2078: AND status = '2'
2079: AND match_ind IN ('18','19','22','23')
2080: AND EXISTS( SELECT vi.rowid
2081: FROM igs_pe_visa_int vi
2082: WHERE vi.interface_visa_id = vh.interface_visa_id AND
2083: vi.status = '1');
2084:
2085: UPDATE igs_pe_vst_hist_int vh

Line 2092: FROM igs_pe_visa_int vi

2088: WHERE interface_run_id=l_interface_run_id
2089: AND status = '2'
2090: AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25')
2091: AND EXISTS( SELECT vi.rowid
2092: FROM igs_pe_visa_int vi
2093: WHERE vi.interface_visa_id = vh.interface_visa_id AND
2094: vi.status = '1');
2095:
2096: UPDATE igs_pe_vst_hist_int vh

Line 2103: FROM igs_pe_visa_int vi

2099: WHERE interface_run_id=l_interface_run_id
2100: AND status = '2'
2101: AND match_ind IS NULL
2102: AND EXISTS( SELECT vi.rowid
2103: FROM igs_pe_visa_int vi
2104: WHERE vi.interface_visa_id = vh.interface_visa_id AND
2105: vi.status = '1')
2106: AND EXISTS( SELECT vs.rowid
2107: FROM igs_pe_visit_histry vs ,

Line 2108: igs_pe_visa pev

2104: WHERE vi.interface_visa_id = vh.interface_visa_id AND
2105: vi.status = '1')
2106: AND EXISTS( SELECT vs.rowid
2107: FROM igs_pe_visit_histry vs ,
2108: igs_pe_visa pev
2109: WHERE vs.visa_id = pev.visa_id AND
2110: vs.port_of_entry = UPPER(vh.port_of_entry) AND
2111: UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) AND
2112: TRUNC(vs.visit_start_date) = TRUNC(vh.visit_start_date) AND

Line 2147: FROM igs_pe_visa_int vsi

2143: UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
2144: WHERE interface_run_id=l_interface_run_id AND
2145: status = '2' AND
2146: EXISTS( SELECT vsi.rowid
2147: FROM igs_pe_visa_int vsi
2148: WHERE vsi.interface_visa_id = vh.interface_visa_id AND
2149: vsi.status = '1') AND
2150: match_ind IS NULL AND
2151: EXISTS (SELECT rowid