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