49: --
50: -- Validation of START_DATE and END_DATE for PER_US_PASSPORT_DETAILS,
51: -- PER_US_VISA_DETAILS, and PER_US_VISIT_HISTORY
52: -- WWBUG 2097669
53: -- Add fnd_date.date_to_canonial to pei_informatin7 and 8
54: CURSOR c1 (p_person_id number,
55: p_information_type varchar2) is
56: SELECT 1
57: FROM per_people_extra_info
56: SELECT 1
57: FROM per_people_extra_info
58: WHERE person_id = p_person_id
59: AND information_type = p_information_type
60: AND fnd_date.canonical_to_date(pei_information7) >
61: fnd_date.canonical_to_date(nvl(pei_information8,'4712/12/31 00:00:00'));
62: --
63: -- Non-duplicate rows on INCOME_CODE of PER_US_PAYROLL_DETAILS
64: CURSOR c2 (p_person_id number,
57: FROM per_people_extra_info
58: WHERE person_id = p_person_id
59: AND information_type = p_information_type
60: AND fnd_date.canonical_to_date(pei_information7) >
61: fnd_date.canonical_to_date(nvl(pei_information8,'4712/12/31 00:00:00'));
62: --
63: -- Non-duplicate rows on INCOME_CODE of PER_US_PAYROLL_DETAILS
64: CURSOR c2 (p_person_id number,
65: p_information_type varchar2,
96: --
97: -- Non-overlap Validation for START_DATE and END_DATE of PER_US_VISIT_HISTORY
98: CURSOR next_visit (p_person_id number,
99: p_information_type varchar2) is
100: SELECT fnd_date.canonical_to_date(pei_information7) start_date,
101: fnd_date.canonical_to_date(nvl(pei_information8,'4712/12/31 00:00:00'))
102: end_date
103: FROM per_people_extra_info
104: WHERE person_id = p_person_id
97: -- Non-overlap Validation for START_DATE and END_DATE of PER_US_VISIT_HISTORY
98: CURSOR next_visit (p_person_id number,
99: p_information_type varchar2) is
100: SELECT fnd_date.canonical_to_date(pei_information7) start_date,
101: fnd_date.canonical_to_date(nvl(pei_information8,'4712/12/31 00:00:00'))
102: end_date
103: FROM per_people_extra_info
104: WHERE person_id = p_person_id
105: AND information_type = p_information_type