1 PACKAGE BODY HR_AE_VALIDATE_PKG AS
2 /* $Header: peaevald.pkb 120.9 2006/12/22 09:40:36 spendhar noship $ */
3 g_type VARCHAR2(1) := NULL;
4 g_per_type VARCHAR2(1) := NULL;
5 PROCEDURE VALIDATE
6 (p_date in date
7 ,p_person_type_id in number
8 ,p_sex in varchar2
9 ,p_first_name in varchar2 default null
10 ,p_last_name in varchar2 default null
11 ,p_national_identifier in varchar2 default null
12 ,p_title in varchar2 default null
13 ,p_marital_status in varchar2 default null
14 ,p_per_information_category in varchar2 default null
15 ,p_per_information1 in varchar2 default null
16 ,p_per_information2 in varchar2 default null
17 ,p_per_information3 in varchar2 default null
18 ,p_per_information4 in varchar2 default null
19 ,p_per_information5 in varchar2 default null
20 ,p_per_information6 in varchar2 default null
21 ,p_per_information7 in varchar2 default null
22 ,p_per_information8 in varchar2 default null
23 ,p_per_information9 in varchar2 default null
24 ,p_per_information10 in varchar2 default null
25 ,p_per_information11 in varchar2 default null
26 ,p_per_information12 in varchar2 default null
27 ,p_per_information13 in varchar2 default null
28 ,p_per_information14 in varchar2 default null
29 ,p_per_information15 in varchar2 default null
30 ,p_per_information16 in varchar2 default null
31 ,p_per_information17 in varchar2 default null
32 ,p_per_information18 in varchar2 default null
33 ,p_per_information19 in varchar2 default null
34 ,p_per_information20 in varchar2 default null
35 ) IS
36 v_field varchar2(300);
37 v_len_field varchar2(300);
38 l_valid_date varchar2(10);
39 CURSOR c_type IS
40 SELECT 'Y'
41 FROM per_person_types ppt
42 WHERE ppt.system_person_type IN ('EMP','APL')
43 AND ppt.person_type_Id = p_person_type_id;
44 CURSOR c_per_type IS
45 SELECT 'Y'
46 FROM per_person_types ppt
47 WHERE ppt.system_person_type LIKE 'EMP%'
48 AND ppt.person_type_Id = p_person_type_id;
49 BEGIN
50
51 /* Added for GSI Bug 5472781 */
52 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
53 v_field := NULL;
54 v_len_field := NULL;
55 g_type := NULL;
56 g_per_type := NULL;
57 IF p_per_information_category = 'AE' THEN
58
59 --Validate length of name fields
60 IF length(p_first_name) > 60 THEN
61 IF v_len_field IS NULL THEN
62 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','FIRST_M');
63 ELSE
64 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','FIRST_M');
65 END IF;
66 END IF;
67 IF length(p_last_name) > 60 THEN
68 IF v_len_field IS NULL THEN
69 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','LAST_M');
70 ELSE
71 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','LAST_M');
72 END IF;
73 END IF;
74 IF length(p_per_information1) > 60 THEN
75 IF v_len_field IS NULL THEN
76 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','FATHER_M');
77 ELSE
78 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','FATHER_M');
79 END IF;
80 END IF;
81 IF length(p_per_information2) > 60 THEN
82 IF v_len_field IS NULL THEN
83 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','GRANDFATHER_M');
84 ELSE
85 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','GRANDFATHER_M');
86 END IF;
87 END IF;
88 IF length(p_per_information3) > 60 THEN
89 IF v_len_field IS NULL THEN
90 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','MOTHER_M');
91 ELSE
92 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','MOTHER_M');
93 END IF;
94 END IF;
95 IF length(p_per_information4) > 60 THEN
96 IF v_len_field IS NULL THEN
97 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','ALT_FIRST_M');
98 ELSE
99 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','ALT_FIRST_M');
100 END IF;
101 END IF;
102 IF length(p_per_information5) > 60 THEN
103 IF v_len_field IS NULL THEN
104 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','ALT_FATHER_M');
105 ELSE
106 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','ALT_FATHER_M');
107 END IF;
108 END IF;
109 IF length(p_per_information6) > 60 THEN
110 IF v_len_field IS NULL THEN
111 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','ALT_GRANDFATHER_M');
112 ELSE
113 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','ALT_GRANDFATHER_M');
114 END IF;
115 END IF;
116 IF length(p_per_information7) > 60 THEN
117 IF v_len_field IS NULL THEN
118 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','ALT_LAST_M');
119 ELSE
120 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','ALT_LAST_M');
121 END IF;
122 END IF;
123 IF length(p_per_information8) > 60 THEN
124 IF v_len_field IS NULL THEN
125 v_len_field := hr_general.decode_lookup('AE_FORM_LABELS','ALT_MOTHER_M');
126 ELSE
127 v_len_field := v_len_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','ALT_MOTHER_M');
128 END IF;
129 END IF;
130 IF v_len_field IS NOT NULL THEN
131 fnd_message.set_name('PER', 'HR_377418_AE_INVALID_LENGTH');
132 fnd_message.set_token('FIELD',v_len_field, translate => true );
133 hr_utility.raise_error;
134 END IF;
135
136 --Validate not null fields
137 IF g_per_type = 'Y' THEN
138 IF p_first_name IS NULL THEN
139 IF v_field IS NULL THEN
140 v_field := hr_general.decode_lookup('AE_FORM_LABELS','FIRST_NAME');
141 ELSE
142 v_field := v_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','FIRST_NAME');
143 END IF;
144 END IF;
145 END IF;
146 OPEN c_per_type;
147 FETCH c_per_type INTO g_per_type;
148 CLOSE c_per_type;
149
150 /*IF g_per_type IS NOT NULL THEN
151
152 IF p_national_identifier IS NULL THEN
153 IF v_field IS NULL THEN
154 v_field := hr_general.decode_lookup('AE_FORM_LABELS','CIVIL_IDENTIFIER');
155 ELSE
156 v_field := v_field||hr_general.decode_lookup('AE_FORM_LABELS','CIVIL_IDENTIFIER');
157 END IF;
158 END IF;
159 END IF;*/
160
161 OPEN c_type;
162 FETCH c_type INTO g_type;
163 CLOSE c_type;
164 --IF g_type IS NOT NULL THEN
165 IF g_per_type = 'Y' THEN
166 --IF p_nationality IS NULL THEN
167 IF p_per_information18 IS NULL THEN
168 IF v_field IS NULL THEN
169 v_field := hr_general.decode_lookup('AE_FORM_LABELS','NATIONALITY');
170 ELSE
171 v_field := v_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','NATIONALITY');
172 END IF;
173 END IF;
174 END IF;
175 --END IF;
176 IF v_field IS NOT NULL THEN
177 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
178 fnd_message.set_token('NAME',v_field, translate => true );
179 hr_utility.raise_error;
180 END IF;
181
182 DECLARE
183 CURSOR csr_get_loc_nat IS
184 SELECT org_information1
185 FROM hr_organization_information hoi,
186 per_person_types pty
187 WHERE pty.person_type_id = p_person_type_id
188 AND pty.business_group_id = hoi.organization_id
189 AND hoi.org_information_context = 'AE_BG_DETAILS';
190 rec_get_loc_nat csr_get_loc_nat%ROWTYPE;
191 l_local_nationality VARCHAR2(80);
192 BEGIN
193 l_local_nationality := NULL;
194 OPEN csr_get_loc_nat;
195 FETCH csr_get_loc_nat INTO rec_get_loc_nat;
196 l_local_nationality := rec_get_loc_nat.org_information1;
197 CLOSE csr_get_loc_nat;
198 --IF p_nationality <> NVL(l_local_nationality,'*') AND p_per_information9 IS NOT NULL THEN
199 IF p_per_information18 <> NVL(l_local_nationality,'*') AND (p_per_information9 IS NOT NULL AND p_per_information9 <> hr_api.g_varchar2) THEN
200 IF (p_per_information16 IS NOT NULL AND p_per_information16 <> hr_api.g_varchar2) OR (p_per_information17 IS NOT NULL AND p_per_information17 <> hr_api.g_varchar2) THEN
201 hr_utility.set_message(800, 'HR_377410_AE_DAT_REA_INVALID');
202 hr_utility.raise_error;
203 END IF;
204 END IF;
205 END;
206
207 DECLARE
208 l_count NUMBER;
209
210 CURSOR csr_fetch_bg_id IS
211 SELECT distinct pty.business_group_id
212 FROM per_person_types pty,
213 hr_organization_information hoi
214 WHERE pty.person_type_id = p_person_type_id
215 AND pty.business_group_id = hoi.organization_id;
216
217 CURSOR csr_val_mar_status (l_bg_id NUMBER) IS
218 SELECT 'Y'
219 FROM pay_user_column_instances_f i
220 ,pay_user_rows_f r
221 ,pay_user_columns c
222 ,pay_user_tables t
223 WHERE ((i.legislation_code = 'AE' AND i.business_group_id IS NULL)
224 OR (i.business_group_id = l_bg_id AND i.legislation_code IS NULL))
225 AND ((r.legislation_code = 'AE' AND r.business_group_id IS NULL)
226 OR (r.business_group_id = l_bg_id AND r.legislation_code IS NULL))
227 AND c.legislation_code = 'AE'
228 AND t.legislation_code = 'AE'
229 AND UPPER(t.user_table_name) = UPPER('AE_MARITAL_STATUS')
230 AND t.user_table_id = r.user_table_id
231 AND t.user_table_id = c.user_table_id
232 AND r.row_low_range_or_name = p_marital_status
233 AND r.user_row_id = i.user_row_id
234 AND UPPER(c.user_column_name) = UPPER('MARITAL STATUS')
235 AND c.user_column_id = i.user_column_id
236 --AND i.value = p_value
237 AND p_date BETWEEN r.effective_start_date AND r.effective_end_date
238 AND p_date BETWEEN i.effective_start_date AND i.effective_end_date;
239 l_valid VARCHAR2(10);
240 l_bg_id NUMBER;
241 BEGIN
242 l_valid := NULL;
243
244 OPEN csr_fetch_bg_id;
245 FETCH csr_fetch_bg_id INTO l_bg_id;
246 CLOSE csr_fetch_bg_id;
247
248 IF p_marital_status IS NOT NULL AND p_marital_status <> hr_api.g_varchar2 THEN
249 OPEN csr_val_mar_status(l_bg_id);
250 FETCH csr_val_mar_status INTO l_valid;
251 CLOSE csr_val_mar_status;
252 IF l_valid IS NULL THEN
253 hr_utility.set_message(800, 'HR_377405_AE_INVALID_MAR');
254 hr_utility.raise_error;
255 END IF;
256 END IF;
257 END;
258
259 END IF;
260 END IF;
261 END VALIDATE;
262 --Procedure for validating person
263 PROCEDURE PERSON_VALIDATE
264 (p_person_id in number
265 ,p_person_type_id in number
266 ,p_effective_date in date
267 ,p_sex in varchar2
268 ,p_first_name in varchar2 default null
269 ,p_last_name in varchar2 default null
270 ,p_nationality in varchar2 default null
271 ,p_national_identifier in varchar2 default null
272 ,p_title in varchar2 default null
273 ,p_marital_status in varchar2 default null
274 ,p_per_information_category in varchar2 default null
275 ,p_per_information1 in varchar2 default null
276 ,p_per_information2 in varchar2 default null
277 ,p_per_information3 in varchar2 default null
278 ,p_per_information4 in varchar2 default null
279 ,p_per_information5 in varchar2 default null
280 ,p_per_information6 in varchar2 default null
281 ,p_per_information7 in varchar2 default null
282 ,p_per_information8 in varchar2 default null
283 ,p_per_information9 in varchar2 default null
284 ,p_per_information10 in varchar2 default null
285 ,p_per_information11 in varchar2 default null
286 ,p_per_information12 in varchar2 default null
287 ,p_per_information13 in varchar2 default null
288 ,p_per_information14 in varchar2 default null
289 ,p_per_information15 in varchar2 default null
290 ,p_per_information16 in varchar2 default null
291 ,p_per_information17 in varchar2 default null
292 ,p_per_information18 in varchar2 default null
293 ,p_per_information19 in varchar2 default null
294 ,p_per_information20 in varchar2 default null
295 ) IS
296 CURSOR csr_person_type_id IS
297 SELECT person_type_id
298 FROM per_all_people_f
299 WHERE person_id = p_person_id
300 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
301 l_person_type_id NUMBER;
302 l_person_type VARCHAR2(20);
303 CURSOR chk_person_type IS
304 SELECT 'Y'
305 FROM per_person_types ppt
306 WHERE ppt.system_person_type IN ('CWK')
307 AND ppt.person_type_id = p_person_type_id;
308 BEGIN
309 OPEN csr_person_type_id;
310 FETCH csr_person_type_id INTO l_person_type_id;
311 CLOSE csr_person_type_id;
312 l_person_type := NULL;
313 OPEN chk_person_type;
314 FETCH chk_person_type INTO l_person_type;
315 CLOSE chk_person_type;
316 IF NVL(l_person_type,'N') <> 'Y' THEN
317 validate
318 (p_person_type_id => l_person_type_id
319 ,p_date => p_effective_date
320 ,p_sex => p_sex
321 ,p_first_name => p_first_name
322 ,p_last_name => p_last_name
323 ,p_national_identifier => p_national_identifier
324 ,p_title => p_title
325 ,p_marital_status => p_marital_status
326 ,p_per_information_category => p_per_information_category
327 ,p_per_information1 => p_per_information1
328 ,p_per_information2 => p_per_information2
329 ,p_per_information3 => p_per_information3
330 ,p_per_information4 => p_per_information4
331 ,p_per_information5 => p_per_information5
332 ,p_per_information6 => p_per_information6
333 ,p_per_information7 => p_per_information7
334 ,p_per_information8 => p_per_information8
335 ,p_per_information9 => p_per_information9
336 ,p_per_information10 => p_per_information10
337 ,p_per_information11 => p_per_information11
338 ,p_per_information12 => p_per_information12
339 ,p_per_information13 => p_per_information13
340 ,p_per_information14 => p_per_information14
341 ,p_per_information15 => p_per_information15
342 ,p_per_information16 => p_per_information16
343 ,p_per_information17 => p_per_information17
344 ,p_per_information18 => p_per_information18
345 ,p_per_information19 => p_per_information19
346 ,p_per_information20 => p_per_information20);
347 END IF;
348 /* Added for GSI Bug 5472781 */
349 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
350 if g_type IS NOT NULL THEN
351 if p_per_information_category = 'AE' and (p_per_information10 is not null AND p_per_information10 <> hr_api.g_varchar2) then
352 --
353 -- Check that the religion exists in hr_lookups for the
354 -- lookup type 'AE_RELIGION' with an enabled flag set to 'Y' and that
355 -- the effective start date of the person is between start date
356 -- active and end date active in hr_lookups.
357 --
358 if hr_api.not_exists_in_hr_lookups
359 (p_effective_date => p_effective_date
360 ,p_lookup_type => 'AE_RELIGION'
361 ,p_lookup_code => p_per_information10
362 )
363 then
364 --
365 hr_utility.set_message(800, 'HR_377401_AE_INVALID_RELIGION');
366 hr_utility.raise_error;
367 --
368 end if;
369 end if;
370
371 if p_per_information_category = 'AE' and (p_per_information9 is not null AND p_per_information9 <> hr_api.g_varchar2) then
372 --
373 -- Check that the nationality exists in hr_lookups for the
374 -- lookup type 'AE_NATIONALITY' with an enabled flag set to 'Y' and that
375 -- the effective start date of the person is between start date
376 -- active and end date active in hr_lookups.
377 --
378 if hr_api.not_exists_in_hr_lookups
379 (p_effective_date => p_effective_date
380 ,p_lookup_type => 'AE_NATIONALITY'
381 ,p_lookup_code => p_per_information9
382 )
383 then
384 --
385 hr_utility.set_message(800, 'HR_377402_AE_INVALID_PREV_NAT');
386 hr_utility.raise_error;
387 --
388 end if;
389 end if;
390
391 end if;
392 END IF;
393 END PERSON_VALIDATE;
394 --Procedure for validating applicant
395 PROCEDURE APPLICANT_VALIDATE
396 (p_business_group_id in number
397 ,p_person_type_id in number
398 ,p_date_received in date
399 ,p_sex in varchar2
400 ,p_first_name in varchar2 default null
401 ,p_last_name in varchar2 default null
402 ,p_nationality in varchar2 default null
403 ,p_national_identifier in varchar2 default null
404 ,p_title in varchar2 default null
405 ,p_marital_status in varchar2 default null
406 ,p_per_information_category in varchar2 default null
407 ,p_per_information1 in varchar2 default null
408 ,p_per_information2 in varchar2 default null
409 ,p_per_information3 in varchar2 default null
410 ,p_per_information4 in varchar2 default null
411 ,p_per_information5 in varchar2 default null
412 ,p_per_information6 in varchar2 default null
413 ,p_per_information7 in varchar2 default null
414 ,p_per_information8 in varchar2 default null
415 ,p_per_information9 in varchar2 default null
416 ,p_per_information10 in varchar2 default null
417 ,p_per_information11 in varchar2 default null
418 ,p_per_information12 in varchar2 default null
419 ,p_per_information13 in varchar2 default null
420 ,p_per_information14 in varchar2 default null
421 ,p_per_information15 in varchar2 default null
422 ,p_per_information16 in varchar2 default null
423 ,p_per_information17 in varchar2 default null
424 ,p_per_information18 in varchar2 default null
425 ,p_per_information19 in varchar2 default null
426 ,p_per_information20 in varchar2 default null
427 ) IS
428 l_person_type_id NUMBER;
429 BEGIN
430 per_per_bus.chk_person_type
431 (p_person_type_id => l_person_type_id
432 ,p_business_group_id => p_business_group_id
433 ,p_expected_sys_type => 'APL'
434 );
435 validate
436 (p_person_type_id => l_person_type_id
437 ,p_date => p_date_received
438 ,p_sex => p_sex
439 ,p_first_name => p_first_name
440 ,p_last_name => p_last_name
441 ,p_national_identifier => p_national_identifier
442 ,p_title => p_title
443 ,p_marital_status => p_marital_status
444 ,p_per_information_category => p_per_information_category
445 ,p_per_information1 => p_per_information1
446 ,p_per_information2 => p_per_information2
447 ,p_per_information3 => p_per_information3
448 ,p_per_information4 => p_per_information4
449 ,p_per_information5 => p_per_information5
450 ,p_per_information6 => p_per_information6
451 ,p_per_information7 => p_per_information7
452 ,p_per_information8 => p_per_information8
453 ,p_per_information9 => p_per_information9
454 ,p_per_information10 => p_per_information10
455 ,p_per_information11 => p_per_information11
456 ,p_per_information12 => p_per_information12
457 ,p_per_information13 => p_per_information13
458 ,p_per_information14 => p_per_information14
459 ,p_per_information15 => p_per_information15
460 ,p_per_information16 => p_per_information16
461 ,p_per_information17 => p_per_information17
462 ,p_per_information18 => p_per_information18
463 ,p_per_information19 => p_per_information19
464 ,p_per_information20 => p_per_information20);
465
466 /* Added for GSI Bug 5472781 */
467 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
468
469 if g_type IS NOT NULL THEN
470 if p_per_information_category = 'AE' and (p_per_information10 is not null AND p_per_information10 <> hr_api.g_varchar2) then
471 --
472 -- Check that the religion exists in hr_lookups for the
473 -- lookup type 'AE_RELIGION' with an enabled flag set to 'Y' and that
474 -- the effective start date of the person is between start date
475 -- active and end date active in hr_lookups.
476 --
477 if hr_api.not_exists_in_hr_lookups
478 (p_effective_date => p_date_received
479 ,p_lookup_type => 'AE_RELIGION'
480 ,p_lookup_code => p_per_information10
481 )
482 then
483 --
484 hr_utility.set_message(800, 'HR_377401_AE_INVALID_RELIGION');
485 hr_utility.raise_error;
486 --
487 end if;
488 end if;
489
490 if p_per_information_category = 'AE' and (p_per_information9 is not null AND p_per_information9 <> hr_api.g_varchar2) then
491 --
492 -- Check that the nationality exists in hr_lookups for the
493 -- lookup type 'AE_NATIONALITY' with an enabled flag set to 'Y' and that
494 -- the effective start date of the person is between start date
495 -- active and end date active in hr_lookups.
496 --
497 if hr_api.not_exists_in_hr_lookups
498 (p_effective_date => p_date_received
499 ,p_lookup_type => 'AE_NATIONALITY'
500 ,p_lookup_code => p_per_information9
501 )
502 then
503 --
504 hr_utility.set_message(800, 'HR_377402_AE_INVALID_PREV_NAT');
505 hr_utility.raise_error;
506 --
507 end if;
508 end if;
509 end if;
510 END IF;
511 END APPLICANT_VALIDATE;
512 --Procedure for validating employee
513 PROCEDURE EMPLOYEE_VALIDATE
514 (p_business_group_id in number
515 ,p_person_type_id in number
516 ,p_hire_date in date
517 ,p_sex in varchar2
518 ,p_first_name in varchar2 default null
519 ,p_last_name in varchar2 default null
520 ,p_nationality in varchar2 default null
521 ,p_national_identifier in varchar2 default null
522 ,p_title in varchar2 default null
523 ,p_marital_status in varchar2 default null
524 ,p_per_information_category in varchar2 default null
525 ,p_per_information1 in varchar2 default null
526 ,p_per_information2 in varchar2 default null
527 ,p_per_information3 in varchar2 default null
528 ,p_per_information4 in varchar2 default null
529 ,p_per_information5 in varchar2 default null
530 ,p_per_information6 in varchar2 default null
531 ,p_per_information7 in varchar2 default null
532 ,p_per_information8 in varchar2 default null
533 ,p_per_information9 in varchar2 default null
534 ,p_per_information10 in varchar2 default null
535 ,p_per_information11 in varchar2 default null
536 ,p_per_information12 in varchar2 default null
537 ,p_per_information13 in varchar2 default null
538 ,p_per_information14 in varchar2 default null
539 ,p_per_information15 in varchar2 default null
540 ,p_per_information16 in varchar2 default null
541 ,p_per_information17 in varchar2 default null
542 ,p_per_information18 in varchar2 default null
543 ,p_per_information19 in varchar2 default null
544 ,p_per_information20 in varchar2 default null
545 ) IS
546 l_person_type_id number;
547 l_valid_date varchar2(10);
548 BEGIN
549 per_per_bus.chk_person_type
550 (p_person_type_id => l_person_type_id
551 ,p_business_group_id => p_business_group_id
552 ,p_expected_sys_type => 'EMP'
553 );
554
555 validate
556 (p_person_type_id => l_person_type_id
557 ,p_date => p_hire_date
558 ,p_sex => p_sex
559 ,p_first_name => p_first_name
560 ,p_last_name => p_last_name
561 ,p_national_identifier => p_national_identifier
562 ,p_title => p_title
563 ,p_marital_status => p_marital_status
564 ,p_per_information_category => p_per_information_category
565 ,p_per_information1 => p_per_information1
566 ,p_per_information2 => p_per_information2
567 ,p_per_information3 => p_per_information3
568 ,p_per_information4 => p_per_information4
569 ,p_per_information5 => p_per_information5
570 ,p_per_information6 => p_per_information6
571 ,p_per_information7 => p_per_information7
572 ,p_per_information8 => p_per_information8
573 ,p_per_information9 => p_per_information9
574 ,p_per_information10 => p_per_information10
575 ,p_per_information11 => p_per_information11
576 ,p_per_information12 => p_per_information12
577 ,p_per_information13 => p_per_information13
578 ,p_per_information14 => p_per_information14
579 ,p_per_information15 => p_per_information15
580 ,p_per_information16 => p_per_information16
581 ,p_per_information17 => p_per_information17
582 ,p_per_information18 => p_per_information18
583 ,p_per_information19 => p_per_information19
584 ,p_per_information20 => p_per_information20);
585
586 /* Added for GSI Bug 5472781 */
587 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
588
589 if g_type IS NOT NULL THEN
590 if p_per_information_category = 'AE' and (p_per_information10 is not null AND p_per_information10 <> hr_api.g_varchar2) then
591 --
592 -- Check that the religion exists in hr_lookups for the
593 -- lookup type 'AE_RELIGION' with an enabled flag set to 'Y' and that
594 -- the effective start date of the person is between start date
595 -- active and end date active in hr_lookups.
596 --
597 if hr_api.not_exists_in_hr_lookups
598 (p_effective_date => p_hire_date
599 ,p_lookup_type => 'AE_RELIGION'
600 ,p_lookup_code => p_per_information10
601 )
602 then
603 --
604 hr_utility.set_message(800, 'HR_377401_AE_INVALID_RELIGION');
605 hr_utility.raise_error;
606 --
607 end if;
608 end if;
609
610 if p_per_information_category = 'AE' and (p_per_information9 is not null AND p_per_information9 <> hr_api.g_varchar2) then
611 --
612 -- Check that the nationality exists in hr_lookups for the
613 -- lookup type 'AE_NATIONALITY' with an enabled flag set to 'Y' and that
614 -- the effective start date of the person is between start date
615 -- active and end date active in hr_lookups.
616 --
617 if hr_api.not_exists_in_hr_lookups
618 (p_effective_date => p_hire_date
619 ,p_lookup_type => 'AE_NATIONALITY'
620 ,p_lookup_code => p_per_information9
621 )
622 then
623 --
624 hr_utility.set_message(800, 'HR_377402_AE_INVALID_PREV_NAT');
625 hr_utility.raise_error;
626 --
627 end if;
628 end if;
629 end if;
630 END IF;
631 END EMPLOYEE_VALIDATE;
632 --Procedure for validating contact
633 PROCEDURE CONTACT_VALIDATE
634 (p_business_group_id in number
635 ,p_person_type_id in number
636 ,p_start_date in date
637 ,p_sex in varchar2
638 ,p_first_name in varchar2 default null
639 ,p_last_name in varchar2 default null
640 ,p_nationality in varchar2 default null
641 ,p_national_identifier in varchar2 default null
642 ,p_title in varchar2 default null
643 ,p_marital_status in varchar2 default null
644 ,p_per_information_category in varchar2 default null
645 ,p_per_information1 in varchar2 default null
646 ,p_per_information2 in varchar2 default null
647 ,p_per_information3 in varchar2 default null
648 ,p_per_information4 in varchar2 default null
649 ,p_per_information5 in varchar2 default null
650 ,p_per_information6 in varchar2 default null
651 ,p_per_information7 in varchar2 default null
652 ,p_per_information8 in varchar2 default null
653 ,p_per_information9 in varchar2 default null
654 ,p_per_information10 in varchar2 default null
655 ,p_per_information11 in varchar2 default null
656 ,p_per_information12 in varchar2 default null
657 ,p_per_information13 in varchar2 default null
658 ,p_per_information14 in varchar2 default null
659 ,p_per_information15 in varchar2 default null
660 ,p_per_information16 in varchar2 default null
661 ,p_per_information17 in varchar2 default null
662 ,p_per_information18 in varchar2 default null
663 ,p_per_information19 in varchar2 default null
664 ,p_per_information20 in varchar2 default null
665 ) IS
666 l_person_type_id NUMBER;
667 BEGIN
668 per_per_bus.chk_person_type
669 (p_person_type_id => l_person_type_id
670 ,p_business_group_id => p_business_group_id
671 ,p_expected_sys_type => 'OTHER'
672 );
673 validate
674 (p_person_type_id => l_person_type_id
675 ,p_date => p_start_date
676 ,p_sex => p_sex
677 ,p_first_name => p_first_name
678 ,p_last_name => p_last_name
679 ,p_national_identifier => p_national_identifier
680 ,p_title => p_title
681 ,p_marital_status => p_marital_status
682 ,p_per_information_category => p_per_information_category
683 ,p_per_information1 => p_per_information1
684 ,p_per_information2 => p_per_information2
685 ,p_per_information3 => p_per_information3
686 ,p_per_information4 => p_per_information4
687 ,p_per_information5 => p_per_information5
688 ,p_per_information6 => p_per_information6
689 ,p_per_information7 => p_per_information7
690 ,p_per_information8 => p_per_information8
691 ,p_per_information9 => p_per_information9
692 ,p_per_information10 => p_per_information10
693 ,p_per_information11 => p_per_information11
694 ,p_per_information12 => p_per_information12
695 ,p_per_information13 => p_per_information13
696 ,p_per_information14 => p_per_information14
697 ,p_per_information15 => p_per_information15
698 ,p_per_information16 => p_per_information16
699 ,p_per_information17 => p_per_information17
700 ,p_per_information18 => p_per_information18
701 ,p_per_information19 => p_per_information19
702 ,p_per_information20 => p_per_information20);
703
704 /* Added for GSI Bug 5472781 */
705 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
706
707 if g_type IS NOT NULL THEN
708 if p_per_information_category = 'AE' and (p_per_information10 is not null AND p_per_information10 <> hr_api.g_varchar2) then
709 --
710 -- Check that the religion exists in hr_lookups for the
711 -- lookup type 'AE_RELIGION' with an enabled flag set to 'Y' and that
712 -- the effective start date of the person is between start date
713 -- active and end date active in hr_lookups.
714 --
715 if hr_api.not_exists_in_hr_lookups
716 (p_effective_date => p_start_date
717 ,p_lookup_type => 'AE_RELIGION'
718 ,p_lookup_code => p_per_information10
719 )
720 then
721 --
722 hr_utility.set_message(800, 'HR_377401_AE_INVALID_RELIGION');
723 hr_utility.raise_error;
724 --
725 end if;
726 end if;
727
728 if p_per_information_category = 'AE' and (p_per_information9 is not null AND p_per_information9 <> hr_api.g_varchar2) then
729 --
730 -- Check that the nationality exists in hr_lookups for the
731 -- lookup type 'AE_NATIONALITY' with an enabled flag set to 'Y' and that
732 -- the effective start date of the person is between start date
733 -- active and end date active in hr_lookups.
734 --
735 if hr_api.not_exists_in_hr_lookups
736 (p_effective_date => p_start_date
737 ,p_lookup_type => 'AE_NATIONALITY'
738 ,p_lookup_code => p_per_information9
739 )
740 then
741 --
742 hr_utility.set_message(800, 'HR_377402_AE_INVALID_PREV_NAT');
743 hr_utility.raise_error;
744 --
745 end if;
746 end if;
747 end if;
748 END IF;
749 END CONTACT_VALIDATE;
750
751 PROCEDURE CWK_VALIDATE
752 (p_business_group_id in number
753 ,p_person_type_id in number
754 ,p_start_date in date
755 ,p_sex in varchar2
756 ,p_first_name in varchar2 default null
757 ,p_last_name in varchar2 default null
758 ,p_nationality in varchar2 default null
759 ,p_national_identifier in varchar2 default null
760 ,p_title in varchar2 default null
761 ,p_marital_status in varchar2 default null
762 ,p_per_information_category in varchar2 default null
763 ,p_per_information1 in varchar2 default null
764 ,p_per_information2 in varchar2 default null
765 ,p_per_information3 in varchar2 default null
766 ,p_per_information4 in varchar2 default null
767 ,p_per_information5 in varchar2 default null
768 ,p_per_information6 in varchar2 default null
769 ,p_per_information7 in varchar2 default null
770 ,p_per_information8 in varchar2 default null
771 ,p_per_information9 in varchar2 default null
772 ,p_per_information10 in varchar2 default null
773 ,p_per_information11 in varchar2 default null
774 ,p_per_information12 in varchar2 default null
775 ,p_per_information13 in varchar2 default null
776 ,p_per_information14 in varchar2 default null
777 ,p_per_information15 in varchar2 default null
778 ,p_per_information16 in varchar2 default null
779 ,p_per_information17 in varchar2 default null
780 ,p_per_information18 in varchar2 default null
781 ,p_per_information19 in varchar2 default null
782 ,p_per_information20 in varchar2 default null
783 ) IS
784 l_person_type_id NUMBER;
785 BEGIN
786 per_per_bus.chk_person_type
787 (p_person_type_id => l_person_type_id
788 ,p_business_group_id => p_business_group_id
789 ,p_expected_sys_type => 'CWK'
790 );
791 validate
792 (p_person_type_id => l_person_type_id
793 ,p_date => p_start_date
794 ,p_sex => p_sex
795 ,p_first_name => p_first_name
796 ,p_last_name => p_last_name
797 ,p_national_identifier => p_national_identifier
798 ,p_title => p_title
799 ,p_marital_status => p_marital_status
800 ,p_per_information_category => p_per_information_category
801 ,p_per_information1 => p_per_information1
802 ,p_per_information2 => p_per_information2
803 ,p_per_information3 => p_per_information3
804 ,p_per_information4 => p_per_information4
805 ,p_per_information5 => p_per_information5
806 ,p_per_information6 => p_per_information6
807 ,p_per_information7 => p_per_information7
808 ,p_per_information8 => p_per_information8
809 ,p_per_information9 => p_per_information9
810 ,p_per_information10 => p_per_information10
811 ,p_per_information11 => p_per_information11
812 ,p_per_information12 => p_per_information12
813 ,p_per_information13 => p_per_information13
814 ,p_per_information14 => p_per_information14
815 ,p_per_information15 => p_per_information15
816 ,p_per_information16 => p_per_information16
817 ,p_per_information17 => p_per_information17
818 ,p_per_information18 => p_per_information18
819 ,p_per_information19 => p_per_information19
820 ,p_per_information20 => p_per_information20);
821
822 /* Added for GSI Bug 5472781 */
823 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
824
825 if g_type IS NOT NULL THEN
826 if p_per_information_category = 'AE' and (p_per_information10 is not null AND p_per_information10 <> hr_api.g_varchar2) then
827 --
828 -- Check that the religion exists in hr_lookups for the
829 -- lookup type 'AE_RELIGION' with an enabled flag set to 'Y' and that
830 -- the effective start date of the person is between start date
831 -- active and end date active in hr_lookups.
832 --
833 if hr_api.not_exists_in_hr_lookups
834 (p_effective_date => p_start_date
835 ,p_lookup_type => 'AE_RELIGION'
836 ,p_lookup_code => p_per_information10
837 )
838 then
839 --
840 hr_utility.set_message(800, 'HR_377401_AE_INVALID_RELIGION');
841 hr_utility.raise_error;
842 --
843 end if;
844 end if;
845
846 if p_per_information_category = 'AE' and (p_per_information9 is not null AND p_per_information9 <> hr_api.g_varchar2) then
847 --
848 -- Check that the nationality exists in hr_lookups for the
849 -- lookup type 'AE_NATIONALITY' with an enabled flag set to 'Y' and that
850 -- the effective start date of the person is between start date
851 -- active and end date active in hr_lookups.
852 --
853 if hr_api.not_exists_in_hr_lookups
854 (p_effective_date => p_start_date
855 ,p_lookup_type => 'AE_NATIONALITY'
856 ,p_lookup_code => p_per_information9
857 )
858 then
859 --
860 hr_utility.set_message(800, 'HR_377402_AE_INVALID_PREV_NAT');
861 hr_utility.raise_error;
862 --
863 end if;
864 end if;
865 end if;
866 END IF;
867 END CWK_VALIDATE;
868 --
869 -- Procedure for validating contract
870 --
871 PROCEDURE contract_validate
872 (p_effective_date IN DATE
873 ,p_type IN VARCHAR2
874 ,p_duration IN NUMBER DEFAULT NULL
875 ,p_duration_units IN VARCHAR2 DEFAULT NULL
876 ,p_ctr_information_category IN VARCHAR2 DEFAULT NULL
877 ,p_ctr_information1 IN VARCHAR2 DEFAULT NULL
878 ,p_ctr_information2 IN VARCHAR2 DEFAULT NULL
879 ,p_ctr_information3 IN VARCHAR2 DEFAULT NULL
880 ,p_ctr_information4 IN VARCHAR2 DEFAULT NULL
881 ,p_ctr_information5 IN VARCHAR2 DEFAULT NULL) IS
882 --
883 l_field VARCHAR2(300);
884 --
885 BEGIN
886
887 /* Added for GSI Bug 5472781 */
888 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
889 --
890 l_field := NULL;
891 --
892 IF p_ctr_information_category = '' THEN
893 --
894 -- Check that the employment status exists in hr_lookups for the
895 -- lookup type 'AE_EMPLOYMENT_STATUS' with an enabled flag set to 'Y'
896 --
897 IF p_ctr_information1 IS NOT NULL AND p_ctr_information1 <> hr_api.g_varchar2 THEN
898 --
899 IF hr_api.not_exists_in_hr_lookups
900 (p_effective_date => p_effective_date
901 ,p_lookup_type => 'AE_EMPLOYMENT_STATUS'
902 ,p_lookup_code => p_ctr_information1) THEN
903 --
904 hr_utility.set_message(800, 'HR_377414_AE_INVALID_EMP_STAT');
905 hr_utility.raise_error;
906 --
907 END IF;
908 --
909 END IF;
910 --
911 IF p_ctr_information2 IS NOT NULL AND p_ctr_information2 <> hr_api.g_varchar2 THEN
912 --
913 IF (fnd_date.canonical_to_date(p_ctr_information2) < p_effective_date)
914 THEN
915 --
916 hr_utility.set_message(800, 'HR_377415_AE_EXPIRY_INVALID');
917 hr_utility.raise_error;
918 --
919 END IF;
920 --
921 END IF;
922 --
923 END IF;
924 --
925 END IF;
926
927 END CONTRACT_VALIDATE;
928 --
929
930 PROCEDURE validate_address
931 (p_effective_date IN DATE
932 ,p_address_line3 IN VARCHAR2
933 ,p_town_or_city IN VARCHAR2
934 ,p_region_1 IN VARCHAR2) IS
935 CURSOR csr_validate_address
936 (p_user_table_name VARCHAR2
937 ,p_row_low_name VARCHAR2
938 ,p_user_column_name VARCHAR2
939 ,p_value VARCHAR2) IS
940 SELECT 'Y'
941 FROM pay_user_column_instances_f i
942 ,pay_user_rows_f r
943 ,pay_user_columns c
944 ,pay_user_tables t
945 WHERE i.legislation_code = 'AE'
946 AND r.legislation_code = 'AE'
947 AND c.legislation_code = 'AE'
948 AND t.legislation_code = 'AE'
949 AND UPPER(t.user_table_name) = UPPER(p_user_table_name)
950 AND t.user_table_id = r.user_table_id
951 AND t.user_table_id = c.user_table_id
952 AND r.row_low_range_or_name = p_row_low_name
953 AND r.user_row_id = i.user_row_id
954 AND UPPER(c.user_column_name) = UPPER(p_user_column_name)
955 AND c.user_column_id = i.user_column_id
956 AND i.value = p_value
957 AND p_effective_date BETWEEN r.effective_start_date AND r.effective_end_date
958 AND p_effective_date BETWEEN i.effective_start_date AND i.effective_end_date;
959 l_valid VARCHAR2(1);
960 BEGIN
961
962 /* Added for GSI Bug 5472781 */
963 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
964
965 l_valid := NULL;
966 IF (p_town_or_city IS NOT NULL AND p_town_or_city <> hr_api.g_varchar2) AND (p_address_line3 IS NOT NULL AND p_address_line3 <> hr_api.g_varchar2)THEN
967 OPEN csr_validate_address
968 ('AE_CITY_VALIDATION',p_town_or_city,'EMIRATE CODE',p_address_line3);
969 FETCH csr_validate_address INTO l_valid;
970 CLOSE csr_validate_address;
971
972 IF l_valid IS NULL THEN
973 hr_utility.set_message(800, 'HR_377403_AE_INVALID_CITY');
974 hr_utility.raise_error;
975 END IF;
976 END IF;
977 l_valid := NULL;
978 IF (p_region_1 IS NOT NULL AND p_region_1 <> hr_api.g_varchar2) THEN --AND p_town_or_city IS NOT NULL THEN
979 OPEN csr_validate_address
980 ('AE_AREA_VALIDATION',p_region_1,'CITY CODE',p_town_or_city);
981 FETCH csr_validate_address INTO l_valid;
982 CLOSE csr_validate_address;
983 IF l_valid IS NULL THEN
984 hr_utility.set_message(800, 'HR_377404_AE_INVALID_AREA');
985 hr_utility.raise_error;
986 END IF;
987 END IF;
988 END IF;
989 END validate_address;
990
991
992 PROCEDURE create_address_validate
993 (p_style IN VARCHAR2
994 ,p_effective_date IN DATE
995 ,p_address_line3 IN VARCHAR2
996 ,p_town_or_city IN VARCHAR2
997 ,p_region_1 IN VARCHAR2) IS
998
999 BEGIN
1000 /* Added for GSI Bug 5472781 */
1001 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1002
1003 IF p_style = 'AE' THEN
1004 validate_address
1005 (p_effective_date => p_effective_date
1006 ,p_address_line3 => p_address_line3
1007 ,p_town_or_city => p_town_or_city
1008 ,p_region_1 => p_region_1);
1009 END IF;
1010 END IF;
1011
1012 END create_address_validate;
1013
1014 PROCEDURE update_address_validate
1015 (p_address_id IN NUMBER
1016 ,p_effective_date IN DATE
1017 ,p_address_line3 IN VARCHAR2
1018 ,p_town_or_city IN VARCHAR2
1019 ,p_region_1 IN VARCHAR2) IS
1020
1021 CURSOR csr_get_style(l_address_id number) is
1022 SELECT style,person_id
1023 FROM per_addresses
1024 WHERE address_id = l_address_id;
1025 l_style per_addresses.style%TYPE;
1026 l_person_id per_addresses.person_id%TYPE;
1027 --
1028 BEGIN
1029
1030 /* Added for GSI Bug 5472781 */
1031 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1032 --
1033 OPEN csr_get_style(p_address_id);
1034 FETCH csr_get_style INTO l_style,l_person_id;
1035 CLOSE csr_get_style;
1036 IF l_style = 'AE' THEN
1037 validate_address
1038 (p_effective_date => p_effective_date
1039 ,p_address_line3 => p_address_line3
1040 ,p_town_or_city => p_town_or_city
1041 ,p_region_1 => p_region_1);
1042
1043 END IF;
1044
1045 END IF;
1046
1047 END update_address_validate;
1048
1049 PROCEDURE create_location_validate
1050 (p_style IN VARCHAR2
1051 ,p_effective_date IN DATE
1052 ,p_address_line_3 IN VARCHAR2
1053 ,p_town_or_city IN VARCHAR2
1054 ,p_region_1 IN VARCHAR2) IS
1055
1056 BEGIN
1057
1058 /* Added for GSI Bug 5472781 */
1059 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1060 IF p_style = 'AE' THEN
1061 validate_address
1062 (p_effective_date => p_effective_date
1063 ,p_address_line3 => p_address_line_3
1064 ,p_town_or_city => p_town_or_city
1065 ,p_region_1 => p_region_1);
1066 END IF;
1067 END IF;
1068 END create_location_validate;
1069
1070 PROCEDURE update_location_validate
1071 (p_style IN VARCHAR2
1072 ,p_effective_date IN DATE
1073 ,p_address_line_3 IN VARCHAR2
1074 ,p_town_or_city IN VARCHAR2
1075 ,p_region_1 IN VARCHAR2) IS
1076 --
1077 BEGIN
1078
1079 /* Added for GSI Bug 5472781 */
1080 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1081 --
1082 IF p_style = 'AE' THEN
1083 validate_address
1084 (p_effective_date => p_effective_date
1085 ,p_address_line3 => p_address_line_3
1086 ,p_town_or_city => p_town_or_city
1087 ,p_region_1 => p_region_1);
1088
1089 END IF;
1090
1091 END IF;
1092 END update_location_validate;
1093
1094 PROCEDURE update_asg_validate
1095 (p_effective_date IN DATE
1096 ,p_assignment_id IN NUMBER
1097 ,p_segment1 IN VARCHAR2
1098 ,p_segment2 IN VARCHAR2
1099 ,p_segment3 IN VARCHAR2
1100 ,p_segment4 IN VARCHAR2
1101 ,p_segment5 IN VARCHAR2) IS
1102
1103 BEGIN
1104
1105 /* Added for GSI Bug 5472781 */
1106 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1107
1108 IF (p_segment2 IS NOT NULL AND p_segment2 <> hr_api.g_varchar2) AND p_segment3 IS NULL THEN
1109 hr_utility.set_message(800, 'HR_377409_AE_SOC_SEC_REQ');
1110 hr_utility.raise_error;
1111 END IF;
1112
1113 IF p_segment5 IS NOT NULL AND p_segment5 <> hr_api.g_varchar2 THEN
1114 DECLARE
1115 CURSOR csr_chk_qual IS
1116 SELECT qual.person_id
1117 FROM per_qualifications qual,
1118 per_all_assignments_f asg
1119 WHERE qual.person_id = asg.person_id
1120 AND qual.qualification_id = p_segment5
1121 AND asg.assignment_id = p_assignment_id;
1122 rec_chk_qual csr_chk_qual%ROWTYPE;
1123 l_exist NUMBER;
1124 BEGIN
1125 l_exist := NULL;
1126 OPEN csr_chk_qual;
1127 FETCH csr_chk_qual INTO rec_chk_qual;
1128 l_exist := rec_chk_qual.person_id;
1129 CLOSE csr_chk_qual;
1130 IF l_exist IS NULL THEN
1131 hr_utility.set_message(800, 'HR_377408_AE_INVALID_QUAL');
1132 hr_utility.raise_error;
1133 END IF;
1134 END;
1135 END IF;
1136 END IF;
1137 END update_asg_validate ;
1138
1139 PROCEDURE CREATE_DISABILITY_VALIDATE
1140 (p_effective_date IN DATE
1141 ,p_person_id IN NUMBER
1142 ,p_category IN VARCHAR2
1143 ,p_degree IN NUMBER DEFAULT NULL
1144 ,p_dis_information_category IN VARCHAR2 DEFAULT NULL
1145 ,p_dis_information1 IN VARCHAR2 DEFAULT NULL
1146 ,p_dis_information2 IN VARCHAR2 DEFAULT NULL) AS
1147 l_count NUMBER;
1148 BEGIN
1149
1150 /* Added for GSI Bug 5472781 */
1151 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1152
1153 l_count := 0;
1154 IF p_dis_information_category = 'AE' and NVL(p_dis_information1,'N') = 'Y' THEN
1155 /*Check that Relevant to Social Security is not set to Yes for more than 1 disability in the same period*/
1156 SELECT COUNT(*)
1157 INTO l_count
1158 FROM per_disabilities_f
1159 WHERE person_id = p_person_id
1160 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1161 AND dis_information_category = 'AE'
1162 AND dis_information1 = 'Y';
1163 IF l_count > 0 THEN
1164 fnd_message.set_name('PER', 'HR_377411_AE_INVALID_DIS_SSN');
1165 hr_utility.raise_error;
1166 END IF;
1167 END IF;
1168 END IF;
1169 END CREATE_DISABILITY_VALIDATE;
1170
1171 PROCEDURE UPDATE_DISABILITY_VALIDATE
1172 (p_effective_date IN DATE
1173 ,p_disability_id IN NUMBER
1174 ,p_category IN VARCHAR2
1175 ,p_degree IN NUMBER DEFAULT NULL
1176 ,p_dis_information_category IN VARCHAR2 DEFAULT NULL
1177 ,p_dis_information1 IN VARCHAR2 DEFAULT NULL
1178 ,p_dis_information2 IN VARCHAR2 DEFAULT NULL) AS
1179 l_person_id NUMBER;
1180 l_count NUMBER;
1181 BEGIN
1182
1183 /* Added for GSI Bug 5472781 */
1184 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1185
1186 l_count := 0;
1187 SELECT person_id
1188 INTO l_person_id
1189 FROM per_disabilities_f
1190 WHERE disability_id = p_disability_id
1191 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1192 IF p_dis_information_category = 'AE' and NVL(p_dis_information1,'N') = 'Y' THEN
1193 /*Check that Relevant to Social Security is not set to Yes for more than 1 disability in the same period*/
1194 SELECT COUNT(*)
1195 INTO l_count
1196 FROM per_disabilities_f
1197 WHERE person_id = l_person_id
1198 AND disability_id <> p_disability_id
1199 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1200 AND dis_information_category = 'AE'
1201 AND dis_information1 = 'Y';
1202 IF l_count > 0 THEN
1203 fnd_message.set_name('PER', 'HR_377411_AE_INVALID_DIS_SSN');
1204 hr_utility.raise_error;
1205 END IF;
1206 END IF;
1207 END IF;
1208 END UPDATE_DISABILITY_VALIDATE;
1209
1210
1211 -- Procedure added for Personal payment method check
1212
1213 PROCEDURE CREATE_PAYMENT_METHOD_VALIDATE
1214 (P_EFFECTIVE_DATE IN DATE
1215 ,P_ASSIGNMENT_ID IN NUMBER
1216 ,P_ORG_PAYMENT_METHOD_ID IN NUMBER
1217 ,P_PPM_INFORMATION1 IN VARCHAR2 DEFAULT NULL) IS
1218
1219 l_count number;
1220
1221 BEGIN
1222 /* Added for GSI Bug 5472781 */
1223 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1224
1225 l_count := 0;
1226
1227 IF NVL(P_PPM_INFORMATION1,'N') = 'Y' THEN
1228
1229 /* Check if more than one personal payment method does not have "EOS" flag as Yes */
1230
1231 SELECT count(*)
1232 INTO l_count
1233 FROM PAY_PERSONAL_PAYMENT_METHODS_F
1234 WHERE ASSIGNMENT_ID = P_ASSIGNMENT_ID
1235 /* AND ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID */
1236 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1237 AND PPM_INFORMATION_CATEGORY = 'AE_AE DIRECT DEPOSIT AED'
1238 AND PPM_INFORMATION1 = 'Y';
1239
1240 IF l_count >= 1 THEN
1241 fnd_message.set_name('PER', 'HR_377444_AE_INVALID_EOS_FLAG');
1242 hr_utility.raise_error;
1243 END IF;
1244
1245 END IF;
1246
1247 END IF;
1248
1249 END CREATE_PAYMENT_METHOD_VALIDATE;
1250
1251 PROCEDURE UPDATE_PAYMENT_METHOD_VALIDATE
1252 (P_EFFECTIVE_DATE IN DATE
1253 ,P_PERSONAL_PAYMENT_METHOD_ID IN NUMBER
1254 ,P_PPM_INFORMATION1 IN VARCHAR2) IS
1255
1256 l_count number;
1257 l_assignment_id number;
1258
1259 BEGIN
1260 /* Added for GSI Bug 5472781 */
1261 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1262
1263 l_count := 0;
1264
1265 SELECT ASSIGNMENT_ID
1266 INTO l_assignment_id
1267 FROM PAY_PERSONAL_PAYMENT_METHODS_F
1268 WHERE PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
1269 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
1270
1271 IF NVL(P_PPM_INFORMATION1,'N') = 'Y' THEN
1272 /* Check if more than one personal payment method does not have "EOS" flag as Yes */
1273
1274 SELECT COUNT(*)
1275 INTO l_count
1276 FROM PAY_PERSONAL_PAYMENT_METHODS_F
1277 WHERE ASSIGNMENT_ID = l_assignment_id
1278 AND PERSONAL_PAYMENT_METHOD_ID <> P_PERSONAL_PAYMENT_METHOD_ID
1279 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1280 AND PPM_INFORMATION_CATEGORY = 'AE_AE DIRECT DEPOSIT AED'
1281 AND PPM_INFORMATION1 = 'Y';
1282
1283 IF l_count > 0 THEN
1284 fnd_message.set_name('PER', 'HR_377444_AE_INVALID_EOS_FLAG');
1285 hr_utility.raise_error;
1286 END IF;
1287
1288 END IF;
1289
1290
1291 END IF;
1292
1293 END UPDATE_PAYMENT_METHOD_VALIDATE;
1294
1295 --Procedure for validating previous_employer
1296 PROCEDURE PREVIOUS_EMPLOYER_VALIDATE
1297 (p_employer_name IN varchar2 default hr_api.g_varchar2
1298 ,p_effective_date IN date default hr_api.g_date
1299 ,p_pem_information_category IN varchar2 default hr_api.g_varchar2
1300 ,p_pem_information1 IN varchar2 default hr_api.g_varchar2
1301 ) IS
1302
1303 v_field VARCHAR2(300);
1304
1305 BEGIN
1306
1307 /* Added for GSI Bug 5472781 */
1308 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1309
1310 v_field := NULL;
1311 IF p_pem_information_category = 'AE' then
1312 IF p_employer_name is null then
1313 IF v_field IS NULL THEN
1314 v_field := hr_general.decode_lookup('AE_FORM_LABELS','PREVIOUS_EMPLOYER');
1315 ELSE
1316 v_field := v_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','PREVIOUS_EMPLOYER');
1317 END IF;
1318
1319 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
1320 fnd_message.set_token('NAME',v_field, translate => true );
1321 hr_utility.raise_error;
1322 END IF;
1323
1324 IF (p_pem_information1 IS NOT NULL AND p_pem_information1 <> hr_api.g_varchar2) THEN
1325 IF hr_api.not_exists_in_hr_lookups
1326 (p_effective_date => p_effective_date
1327 ,p_lookup_type => 'LEAV_REAS'
1328 ,p_lookup_code => p_pem_information1
1329 )
1330 THEN
1331 hr_utility.set_message(800, 'HR_377417_AE_INVALID_LEAV_REAS');
1332 hr_utility.raise_error;
1333 END IF;
1334 END IF;
1335 END IF;
1336 END IF;
1337 END PREVIOUS_EMPLOYER_VALIDATE;
1338
1339 --
1340 PROCEDURE VALIDATE_CREATE_ORG_INF(
1341 p_effective_date IN DATE
1342 ,p_organization_id IN NUMBER
1343 ,p_org_info_type_code IN VARCHAR2
1344 ,p_org_information1 IN VARCHAR2 DEFAULT null
1345 ,p_org_information2 IN VARCHAR2 DEFAULT null
1346 ,p_org_information3 IN VARCHAR2 DEFAULT null
1347 ,p_org_information4 IN VARCHAR2 DEFAULT null
1348 ,p_org_information5 IN VARCHAR2 DEFAULT null
1349 ,p_org_information6 IN VARCHAR2 DEFAULT null
1350 ,p_org_information7 IN VARCHAR2 DEFAULT null
1351 ,p_org_information8 IN VARCHAR2 DEFAULT null
1352 ,p_org_information9 IN VARCHAR2 DEFAULT null
1353 ,p_org_information10 IN VARCHAR2 DEFAULT null
1354 )
1355
1356 AS
1357 return_value VARCHAR2(100);
1358 invalid_mesg VARCHAR2(100);
1359
1360 v_field VARCHAR2(300);
1361
1362 l_length NUMBER;
1363
1364 BEGIN
1365
1366 /* Added for GSI Bug 5472781 */
1367 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1368
1369 v_field := NULL;
1370 l_length := 0;
1371
1372 IF p_org_info_type_code = 'AE_LEGAL_EMPLOYER_DETAILS' THEN
1373
1374 IF p_org_information1 is null then
1375 IF v_field IS NULL THEN
1376 v_field := hr_general.decode_lookup('AE_FORM_LABELS','SOCIAL_SEC_NUMBER');
1377 ELSE
1378 v_field := v_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','SOCIAL_SEC_NUMBER');
1379 END IF;
1380
1381 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
1382 fnd_message.set_token('NAME',v_field, translate => true );
1383 hr_utility.raise_error;
1384 END IF;
1385
1386 IF (p_org_information1 IS NOT NULL AND p_org_information1 <> hr_api.g_varchar2) THEN
1387
1388 l_length := LENGTH(p_org_information1);
1389
1390 IF(l_length > 12) THEN
1391 fnd_message.set_name('PER', 'HR_377407_AE_INVALID_SOC_SEC');
1392 hr_utility.raise_error;
1393 END IF;
1394 END IF;
1395
1396 END IF;
1397
1398 END IF;
1399
1400 END VALIDATE_CREATE_ORG_INF;
1401 --
1402 PROCEDURE VALIDATE_UPDATE_ORG_INF(
1403 p_effective_date IN DATE
1404 ,p_org_information_id IN NUMBER
1405 ,p_org_info_type_code IN VARCHAR2
1406 ,p_org_information1 IN VARCHAR2 DEFAULT null
1407 ,p_org_information2 IN VARCHAR2 DEFAULT null
1408 ,p_org_information3 IN VARCHAR2 DEFAULT null
1409 ,p_org_information4 IN VARCHAR2 DEFAULT null
1410 ,p_org_information5 IN VARCHAR2 DEFAULT null
1411 ,p_org_information6 IN VARCHAR2 DEFAULT null
1412 ,p_org_information7 IN VARCHAR2 DEFAULT null
1413 ,p_org_information8 IN VARCHAR2 DEFAULT null
1414 ,p_org_information9 IN VARCHAR2 DEFAULT null
1415 ,p_org_information10 IN VARCHAR2 DEFAULT null
1416 )
1417 AS
1418 return_value VARCHAR2(100);
1419 invalid_mesg VARCHAR2(100);
1420 l_length NUMBER;
1421 l_var1 NUMBER;
1422 v_field VARCHAR2(300);
1423
1424 BEGIN
1425
1426 /* Added for GSI Bug 5472781 */
1427 IF hr_utility.chk_product_install('Oracle Human Resources', 'AE') THEN
1428
1429 l_length := 0;
1430 l_var1 := 0;
1431 v_field := NULL;
1432
1433 IF p_org_info_type_code = 'AE_LEGAL_EMPLOYER_DETAILS' THEN
1434
1435 IF p_org_information1 is null then
1436 IF v_field IS NULL THEN
1437 v_field := hr_general.decode_lookup('AE_FORM_LABELS','SOCIAL_SEC_NUMBER');
1438 ELSE
1439 v_field := v_field||', '||hr_general.decode_lookup('AE_FORM_LABELS','SOCIAL_SEC_NUMBER');
1440 END IF;
1441
1442 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
1443 fnd_message.set_token('NAME',v_field, translate => true );
1444 hr_utility.raise_error;
1445 END IF;
1446
1447 IF (p_org_information1 IS NOT NULL AND p_org_information1 <> hr_api.g_varchar2) THEN
1448
1449 l_length := LENGTH(p_org_information1);
1450
1451 IF(l_length > 12) THEN
1452 fnd_message.set_name('PER', 'HR_377407_AE_INVALID_SOC_SEC');
1453 hr_utility.raise_error;
1454 END IF;
1455 END IF;
1456
1457 END IF;
1458
1459 END IF;
1460
1461
1462 END VALIDATE_UPDATE_ORG_INF;
1463 --
1464
1465
1466
1467 END HR_AE_VALIDATE_PKG;
[Home] [Help]