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
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'));
64: CURSOR c2 (p_person_id number,
65: p_information_type varchar2,
66: p_pei_information5 varchar2) is
67: SELECT count(*)
68: FROM per_people_extra_info
69: WHERE person_id = p_person_id
70: AND information_type = p_information_type
71: AND pei_information5 = p_pei_information5
72: HAVING count(*) > 1;
74: -- Unique CURRENT Visa row of PER_US_VISA_DETAILS
75: CURSOR c3 (p_person_id number,
76: p_information_type varchar2) is
77: SELECT count(*)
78: FROM per_people_extra_info
79: WHERE person_id = p_person_id
80: AND information_type = p_information_type
81: AND pei_information10 = 'Y'
82: HAVING count(*) > 1;
86: p_information_type varchar2,
87: p_pei_information5 varchar2,
88: p_pei_information6 varchar2) is
89: SELECT count(*)
90: FROM per_people_extra_info
91: WHERE person_id = p_person_id
92: AND information_type = p_information_type
93: AND pei_information5 = p_pei_information5
94: AND pei_information6 = p_pei_information6
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
106: ORDER BY start_date asc;
107: --