[Home] [Help]
PACKAGE BODY: APPS.GHR_PEI_FLEX_DDF
Source
1 Package Body ghr_pei_flex_ddf as
2 /* $Header: ghpeiddf.pkb 120.4.12010000.2 2008/08/05 15:09:39 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_pei_flex_ddf.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-------------------------------< ddf >------------------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 /*
15 procedure ddf
16 (p_rec in pe_pei_shd.g_rec_type) is
17 */
18 PROCEDURE chk_race_ethnic_info(
19 p_person_id number
20 ,p_pei_information3 varchar2
21 ,p_pei_information4 varchar2
22 ,p_pei_information5 varchar2
23 ,p_pei_information6 varchar2
24 ,p_pei_information7 varchar2
25 ,p_pei_information8 varchar2);
26 procedure ddf
27 (
28 p_person_extra_info_id in number ,
29 p_person_id in number ,
30 p_information_type in varchar2 ,
31 p_request_id in number ,
32 p_program_application_id in number ,
33 p_program_id in number ,
34 p_program_update_date in date ,
35 p_pei_attribute_category in varchar2 ,
36 p_pei_attribute1 in varchar2 ,
37 p_pei_attribute2 in varchar2 ,
38 p_pei_attribute3 in varchar2 ,
39 p_pei_attribute4 in varchar2 ,
40 p_pei_attribute5 in varchar2 ,
41 p_pei_attribute6 in varchar2 ,
42 p_pei_attribute7 in varchar2 ,
43 p_pei_attribute8 in varchar2 ,
44 p_pei_attribute9 in varchar2 ,
45 p_pei_attribute10 in varchar2 ,
46 p_pei_attribute11 in varchar2 ,
47 p_pei_attribute12 in varchar2 ,
48 p_pei_attribute13 in varchar2 ,
49 p_pei_attribute14 in varchar2 ,
50 p_pei_attribute15 in varchar2 ,
51 p_pei_attribute16 in varchar2 ,
52 p_pei_attribute17 in varchar2 ,
53 p_pei_attribute18 in varchar2 ,
54 p_pei_attribute19 in varchar2 ,
55 p_pei_attribute20 in varchar2 ,
56 p_pei_information_category in varchar2 ,
57 p_pei_information1 in varchar2 ,
58 p_pei_information2 in varchar2 ,
59 p_pei_information3 in varchar2 ,
60 p_pei_information4 in varchar2 ,
61 p_pei_information5 in varchar2 ,
62 p_pei_information6 in varchar2 ,
63 p_pei_information7 in varchar2 ,
64 p_pei_information8 in varchar2 ,
65 p_pei_information9 in varchar2 ,
66 p_pei_information10 in varchar2 ,
67 p_pei_information11 in varchar2 ,
68 p_pei_information12 in varchar2 ,
69 p_pei_information13 in varchar2 ,
70 p_pei_information14 in varchar2 ,
71 p_pei_information15 in varchar2 ,
72 p_pei_information16 in varchar2 ,
73 p_pei_information17 in varchar2 ,
74 p_pei_information18 in varchar2 ,
75 p_pei_information19 in varchar2 ,
76 p_pei_information20 in varchar2 ,
77 p_pei_information21 in varchar2 ,
78 p_pei_information22 in varchar2 ,
79 p_pei_information23 in varchar2 ,
80 p_pei_information24 in varchar2 ,
81 p_pei_information25 in varchar2 ,
82 p_pei_information26 in varchar2 ,
83 p_pei_information27 in varchar2 ,
84 p_pei_information28 in varchar2 ,
85 p_pei_information29 in varchar2 ,
86 p_pei_information30 in varchar2
87 ) is
88
89 --
90 l_proc varchar2(72) := g_package||'ddf';
91 l_error exception;
92 l_date_from date;
93 --
94 Begin
95 hr_utility.set_location('Entering:'||l_proc, 5);
96
97 if ghr_utility.is_ghr = 'TRUE' then
98 -- Check GHR Workflow Routing Groups information ???
99 --
100 if p_information_type Is Not Null then
101 if p_information_type = 'GHR_US_PER_WF_ROUTING_GROUPS' then
102 ghr_pei_flex_ddf.chk_routing_group_info
103 (p_person_extra_info_id => p_person_extra_info_id
104 ,p_information_type => p_information_type
105 ,p_person_id => p_person_id
106 ,p_pei_information3 => p_pei_information3
107 ,p_pei_information4 => p_pei_information4
108 ,p_pei_information5 => p_pei_information5
109 ,p_pei_information6 => p_pei_information6
110 ,p_pei_information7 => p_pei_information7
111 ,p_pei_information8 => p_pei_information8
112 ,p_pei_information9 => p_pei_information9
113 ,p_pei_information10 => p_pei_information10
114 );
115 -- Validate OGHR roles (INSERT)
116 ghr_pei_flex_ddf.chk_oghr_roles
117 (p_person_extra_info_id => p_person_extra_info_id
118 ,p_information_type => p_information_type
119 ,p_person_id => p_person_id
120 ,p_pei_information3 => p_pei_information3
121 ,p_pei_information4 => p_pei_information4
122 ,p_pei_information5 => p_pei_information5
123 ,p_pei_information6 => p_pei_information6
124 ,p_pei_information7 => p_pei_information7
125 ,p_pei_information8 => p_pei_information8
126 ,p_pei_information9 => p_pei_information9
127 ,p_pei_information10 => p_pei_information10
128 );
129 elsif p_information_type IN ('GHR_US_PER_BENEFIT_INFO','GHR_US_PER_SCD_INFORMATION') THEN
130 ghr_ben_validation.validate_create_personei(
131 p_person_extra_info_id => p_person_extra_info_id ,
132 p_information_type => p_information_type,
133 p_person_id => p_person_id
134 ) ;
135 elsif p_information_type = 'GHR_US_PER_ETHNICITY_RACE' THEN
136 chk_race_ethnic_info(
137 p_person_id => p_person_id
138 ,p_pei_information3 => p_pei_information3
139 ,p_pei_information4 => p_pei_information4
140 ,p_pei_information5 => p_pei_information5
141 ,p_pei_information6 => p_pei_information6
142 ,p_pei_information7 => p_pei_information7
143 ,p_pei_information8 => p_pei_information8);
144 end if;
145 end if;
146 --
147 end if; -- ghr_utility.is_ghr
148 hr_utility.set_location(' Leaving:'||l_proc, 10);
149 exception
150 when l_error then
151 hr_utility.set_message(999, 'HR_9999_FLEX_INV_INFO_ARG');
152 hr_utility.raise_error;
153 hr_utility.set_location(' Leaving:'||l_proc, 10);
154 end ddf;
155 --
156 --
157 -- ----------------------------------------------------------------------------
158 -- |-------------------------< chk_routing_group_info >--------------------------|
159 -- ----------------------------------------------------------------------------
160 --
161 -- Desciption:
162 -- This procedures validates that the if the person is :
163 -- Already assigned to this routing group.
164 -- If he the Reviewer/ Requestor/ Authorizer/ Approver combination is valid.
165 -- If the Person is already assigned a Defualt routing group.
166 -- Pre-conditions :
167 -- p_person_id is valid
168 --
169 -- In Parameters :
170 -- p_information_type
171 -- p_person_id
172 -- p_pei_information3
173 -- p_pei_information4
174 -- p_pei_information5
175 -- p_pei_information6
176 -- p_pei_information7
177 -- p_pei_information8
178 -- p_pei_information9
179 -- p_pei_information10
180 --
181 -- Post Success :
182 -- Processing continues if the Person is not already a member of the routing
183 -- group.
184 -- If his roles of Reviewer/ Requestor/ Authorizer/ Approver/ Personnelist combination is valid.
185 -- If his Default Routing group is valid
186 --
187 --
188 -- Post Failure :
189 -- An application error will be raised and processing is terminated if the
190 -- Person has a duplicate Routing group.
191 -- An application error will also be raised and processing is terminated if
192 -- his roles of Reviewer/ Requestor/ Authorizer/ Approver/ Personnelist combination is invalid.
193 -- If he is already a Defaulted to a routing group.
194 --
195 -- Access Status :
196 -- Internal Row Handler Use only.
197 --
198 -- {End of Comments}
199 --
200 -- ---------------------------------------------------------------------------
201 Procedure chk_routing_group_info
202 (p_person_extra_info_id in per_people_extra_info.person_extra_info_id%TYPE
203 ,p_information_type in per_people_extra_info.information_type%TYPE
204 ,p_person_id in per_people_extra_info.person_id%TYPE
205 ,p_pei_information3 in per_people_extra_info.pei_information3%TYPE
206 ,p_pei_information4 in per_people_extra_info.pei_information4%TYPE
207 ,p_pei_information5 in per_people_extra_info.pei_information5%TYPE
208 ,p_pei_information6 in per_people_extra_info.pei_information6%TYPE
209 ,p_pei_information7 in per_people_extra_info.pei_information7%TYPE
210 ,p_pei_information8 in per_people_extra_info.pei_information8%TYPE
211 ,p_pei_information9 in per_people_extra_info.pei_information9%TYPE
212 ,p_pei_information10 in per_people_extra_info.pei_information10%TYPE
213 ) is
214 --
215 -- Declare local variables
216 --
217 l_proc varchar2(72) := g_package||'chk_routing_group_info';
218 l_count Number(15);
219 --
220 Begin
221 --
222 SELECT count(*)
223 INTO l_count
224 FROM per_people_extra_info
225 WHERE information_type = p_information_type
226 AND person_id = p_person_id
227 AND pei_information3 = p_pei_information3
228 AND person_extra_info_id <> nvl(p_person_extra_info_id,-9999);
229 --
230 --
231 hr_utility.set_location('Entering:'||l_proc, 5);
232 --
233 -- Check to see the Routing group is not duplicate
234 --
235 if l_count > 0 then
236 --
237 hr_utility.set_location(l_proc, 35);
238 --
239 hr_utility.set_message(8301, 'GHR_38047_DUP_ROUTING_GRP_DDF');
240 hr_utility.raise_error;
241 end if;
242 --
243 --
244 --
245 if p_pei_information10 in ('Y','y') then
246 SELECT count(*)
247 INTO l_count
248 FROM per_people_extra_info
249 WHERE information_type = p_information_type
250 AND person_id = p_person_id
251 AND pei_information10 = p_pei_information10
252 AND person_extra_info_id <> nvl(p_person_extra_info_id,-9999);
253 --
254 -- Check to see the Default Routing group is not duplicate
255 --
256 if l_count > 0 then
257 --
258 hr_utility.set_location(l_proc, 40);
259 --
260 hr_utility.set_message(8301, 'GHR_38096_DUP_PRIM_LIST_DDF');
261 hr_utility.raise_error;
262 end if;
263 end if;
264 --
265 --
266 --
267 hr_utility.set_location(' Leaving:'||l_proc, 45);
268 --
269 End chk_routing_group_info;
270
271 Procedure chk_oghr_roles
272 (p_person_extra_info_id in per_people_extra_info.person_extra_info_id%TYPE
273 ,p_information_type in per_people_extra_info.information_type%TYPE
274 ,p_person_id in per_people_extra_info.person_id%TYPE
275 ,p_pei_information3 in per_people_extra_info.pei_information3%TYPE
276 ,p_pei_information4 in per_people_extra_info.pei_information4%TYPE
277 ,p_pei_information5 in per_people_extra_info.pei_information5%TYPE
278 ,p_pei_information6 in per_people_extra_info.pei_information6%TYPE
279 ,p_pei_information7 in per_people_extra_info.pei_information7%TYPE
280 ,p_pei_information8 in per_people_extra_info.pei_information8%TYPE
281 ,p_pei_information9 in per_people_extra_info.pei_information9%TYPE
282 ,p_pei_information10 in per_people_extra_info.pei_information10%TYPE
283 ) is
284 --
285 -- Declare local variables
286 --
287 l_proc varchar2(72) := g_package||'chk_oghr_roles';
288 l_count Number(15);
289 --
290 Begin
291 --
292 if p_pei_information9 in ('Y','y') then
293 if p_pei_information5 in ('Y','y') then
294 hr_utility.set_message(8301, 'GHR_38041_INV_COMB_REV_REQ');
295 hr_utility.raise_error;
296 end if;
297 if p_pei_information6 in ('Y','y') then
298 hr_utility.set_message(8301, 'GHR_38042_INV_COMB_REV_AUTH');
299 hr_utility.raise_error;
300 end if;
301 if p_pei_information7 in ('Y','y') then
302 hr_utility.set_message(8301, 'GHR_38043_INV_COMB_REV_APP');
303 hr_utility.raise_error;
304 end if;
305 end if;
306 -- You cannot be approver without being a Personnelist
307 if p_pei_information8 in ('Y','y') then
308 if p_pei_information7 in ('N','n') then
309 hr_utility.set_message(8301, 'GHR_INV_COMB_APP_PERL');
310 hr_utility.raise_error;
311 end if;
312 end if;
313 --
314 hr_utility.set_location('Entering:'||l_proc, 5);
315 --
316 End chk_oghr_roles;
317
318 -- -- Bug 4724337 Race or National Origin changes
319 PROCEDURE chk_race_ethnic_info(
320 p_person_id number
321 ,p_pei_information3 varchar2
322 ,p_pei_information4 varchar2
323 ,p_pei_information5 varchar2
324 ,p_pei_information6 varchar2
325 ,p_pei_information7 varchar2
326 ,p_pei_information8 varchar2) IS
327 CURSOR c_rno(c_person_id per_all_people_f.person_id%type) IS
328 SELECT pei_information5 race
329 FROM per_people_extra_info
330 WHERE person_id = c_person_id
331 AND information_type = 'GHR_US_PER_GROUP1';
332 l_race per_people_extra_info.pei_information5%type;
333 l_ethnicity varchar2(250);
334
335 BEGIN
336 l_ethnicity := NULL;
337 IF p_pei_information3 IS NOT NULL OR
338 p_pei_information4 IS NOT NULL OR
339 p_pei_information5 IS NOT NULL OR
340 p_pei_information6 IS NOT NULL OR
341 p_pei_information7 IS NOT NULL OR
342 p_pei_information8 IS NOT NULL THEN
343 l_ethnicity := NVL(p_pei_information3,' ') || NVL(p_pei_information4,' ') ||
344 NVL(p_pei_information5,' ') || NVL(p_pei_information6,' ') ||
345 NVL(p_pei_information7,' ') || NVL(p_pei_information8,' ');
346 END IF;
347 -- 165.00.3
348 IF l_ethnicity IS NOT NULL AND INSTR(l_ethnicity,'1') < 1 THEN
349 -- Throw error message
350 hr_utility.set_message(8301, 'GHR_38988_ALL_PROCEDURE_FAIL');
351 hr_utility.raise_error;
352 END IF;
353
354 IF l_ethnicity IS NOT NULL AND INSTR(l_ethnicity,' ') > 1 THEN
355 -- Throw error message
356 hr_utility.set_message(8301, 'GHR_38988_ALL_PROCEDURE_FAIL');
357 hr_utility.raise_error;
358 END IF;
359
360 FOR l_cur_rno IN c_rno(p_person_id) LOOP
361 l_race := l_cur_rno.race;
362 END LOOP;
363
364 -- 165.05.3
365 IF l_race IS NULL AND l_ethnicity IS NULL THEN
366 hr_utility.set_message(8301, 'GHR_38989_ALL_PROCEDURE_FAIL');
367 hr_utility.raise_error;
368 END IF;
369 END chk_race_ethnic_info;
370 ---- Bug 4724337 Race or National Origin changes
371 --
372 --
373 --
374 end ghr_pei_flex_ddf;