1 PACKAGE BODY per_us_hr_utility_pkg AS
2 /* $Header: peushrutil.pkb 120.1 2011/04/05 12:31:03 nvelaga noship $ */
3
4 /* *************************************************************************
5 Name : derive_alien_ethnic_origin
6 Type : Function
7 Return Type : Number
8 Description : This function derives the ethnic origin of a person including
9 Two or More races.
10 Used in IPEDS, AAP and EEO-1 suite of Reports.
11 ************************************************************************* */
12 FUNCTION derive_alien_ethnic_origin(p_person_id NUMBER,
13 p_report_date DATE DEFAULT SYSDATE,
14 p_check_alien VARCHAR2 DEFAULT 'N'
15 )
16 RETURN NUMBER
17 IS
18
19 CURSOR csr_alien(cp_person_id NUMBER, cp_report_date DATE)
20 IS
21 SELECT 0
22 FROM per_people_extra_info pei
23 WHERE pei.person_id = cp_person_id
24 AND pei.information_type = 'PER_US_VISA_DETAILS'
25 AND fnd_date.date_to_canonical(cp_report_date) BETWEEN pei_information7 AND pei_information8
26 AND pei_information9 IN ('04','05','06','07','12');
27
28 CURSOR csr_ethnic_origin(cp_person_id NUMBER)
29 IS
30 SELECT pei.pei_information1,
31 pei.pei_information2,
32 pei.pei_information3,
33 pei.pei_information4,
34 pei.pei_information5,
35 pei.pei_information6,
36 pei.pei_information7
37 FROM per_people_extra_info pei
38 WHERE pei.person_id = cp_person_id
39 AND pei.information_type = 'US_ETHNIC_ORIGIN';
40
41 l_ethnic_origin NUMBER;
42 l_extra_info csr_ethnic_origin%ROWTYPE;
43 l_alien_found VARCHAR2(1);
44
45 BEGIN
46
47 l_ethnic_origin := -1;
48 l_alien_found := 'N';
49
50 IF p_check_alien = 'Y' THEN
51
52 OPEN csr_alien(p_person_id, p_report_date);
53 FETCH csr_alien INTO l_ethnic_origin; -- Non Resident Alien
54
55 IF csr_alien%FOUND THEN
56 l_alien_found := 'Y';
57 END IF;
58
59 CLOSE csr_alien;
60 END IF;
61
62 IF l_alien_found = 'N' THEN
63
64 OPEN csr_ethnic_origin(p_person_id);
65 FETCH csr_ethnic_origin into l_extra_info;
66
67 IF csr_ethnic_origin%FOUND THEN
68
69 IF l_extra_info.pei_information1 = 'Y' THEN -- Hispanic or Latino
70 l_ethnic_origin := 3;
71 ELSIF l_extra_info.pei_information7 = 'Y' THEN -- Two or more Races
72 l_ethnic_origin := 13;
73 ELSIF l_extra_info.pei_information2 = 'Y' THEN -- American Indian or Alaskan Native
74 l_ethnic_origin := 6;
75 ELSIF l_extra_info.pei_information3 = 'Y' THEN -- Asian
76 l_ethnic_origin := 4;
77 ELSIF l_extra_info.pei_information4 = 'Y' THEN -- Black or African American
78 l_ethnic_origin := 2;
79 ELSIF l_extra_info.pei_information5 = 'Y' THEN -- Native Hawaiian or Other Pacific
80 l_ethnic_origin := 5;
81 ELSIF l_extra_info.pei_information6 = 'Y' THEN -- White
82 l_ethnic_origin := 1;
83 ELSE
84 l_ethnic_origin := 99; -- Unkonwn Race
85 END IF;
86 CLOSE csr_ethnic_origin;
87
88 ELSE
89 CLOSE csr_ethnic_origin;
90 l_ethnic_origin := 99; -- Unkonwn Race
91 END IF;
92
93 END IF;
94
95 RETURN l_ethnic_origin;
96 END derive_alien_ethnic_origin;
97
98 /* ************************************************************************
99 Name : derive_single_race
100 Type : Function
101 Return Type : Number
102 Description : This function checks if the person belongs to only one race
103 or not and derives the ethnic origin of the person.
104 Used in EEO4 and EEO5 Reports.
105 ************************************************************************ */
106 FUNCTION derive_single_race(p_person_id NUMBER)
107 RETURN NUMBER
108 IS
109
110 CURSOR csr_ethnic_origin(cp_person_id NUMBER)
111 IS
112 SELECT pei.pei_information1,
113 pei.pei_information2,
114 pei.pei_information3,
115 pei.pei_information4,
116 pei.pei_information5,
117 pei.pei_information6,
118 pei.pei_information7
119 FROM per_people_extra_info pei
120 WHERE pei.person_id = cp_person_id
121 AND pei.information_type = 'US_ETHNIC_ORIGIN';
122
123 l_ethnic_origin NUMBER;
124 l_race_count NUMBER;
125 l_extra_info csr_ethnic_origin%ROWTYPE;
126
127 BEGIN
128
129 l_ethnic_origin := -1;
130 l_race_count := 0;
131
132 OPEN csr_ethnic_origin(p_person_id);
133 FETCH csr_ethnic_origin into l_extra_info;
134
135 IF csr_ethnic_origin%FOUND THEN
136
137 IF l_extra_info.pei_information1 = 'Y' THEN -- Hispanic or Latino
138 l_ethnic_origin := 3;
139 l_race_count := l_race_count + 1;
140 END IF;
141
142 IF l_extra_info.pei_information2 = 'Y' THEN -- American Indian or Alaskan Native
143 l_ethnic_origin := 6;
144 l_race_count := l_race_count + 1;
145 END IF;
146
147 IF l_extra_info.pei_information3 = 'Y' THEN -- Asian
148 l_ethnic_origin := 4;
149 l_race_count := l_race_count + 1;
150 END IF;
151
152 IF l_extra_info.pei_information4 = 'Y' THEN -- Black or African American
153 l_ethnic_origin := 2;
154 l_race_count := l_race_count + 1;
155 END IF;
156
157 IF l_extra_info.pei_information5 = 'Y' THEN -- Native Hawaiian or Other Pacific
158 l_ethnic_origin := 5;
159 l_race_count := l_race_count + 1;
160 END IF;
161
162 IF l_extra_info.pei_information6 = 'Y' THEN -- White
163 l_ethnic_origin := 1;
164 l_race_count := l_race_count + 1;
165 END IF;
166
167 IF l_race_count = 0 THEN
168 l_ethnic_origin := 99; -- Unkonwn Race
169 ELSIF l_race_count > 1 THEN
170 l_ethnic_origin := 13; -- Two or more Races
171 END IF;
172 CLOSE csr_ethnic_origin;
173
174 ELSE
175 CLOSE csr_ethnic_origin;
176 l_ethnic_origin := 99; -- Unkonwn Race
177 END IF;
178
179 RETURN l_ethnic_origin;
180 END derive_single_race;
181
182 END per_us_hr_utility_pkg;
[Home] [Help]