1 PACKAGE BODY hr_sa_validate_pkg AS
2 /* $Header: pesavald.pkb 120.7 2007/02/20 06:41:08 spendhar noship $ */
3 g_type VARCHAR2(1) := NULL;
4 PROCEDURE validate
5 (p_person_type_id in number
6 ,p_sex in varchar2
7 ,p_first_name in varchar2 default null
8 ,p_nationality in varchar2 default null
9 ,p_national_identifier in varchar2 default null
10 ,p_title in varchar2 default null
11 ,p_per_information_category in varchar2 default null
12 ,p_per_information1 in varchar2 default null
13 ,p_per_information2 in varchar2 default null
14 ,p_per_information3 in varchar2 default null
15 ,p_per_information4 in varchar2 default null
16 ,p_per_information5 in varchar2 default null
17 ,p_per_information6 in varchar2 default null
18 ,p_per_information7 in varchar2 default null
19 ,p_per_information8 in varchar2 default null
20 ,p_per_information9 in varchar2 default null
21 ,p_per_information10 in varchar2 default null
22 ) IS
23 v_field varchar2(300) := NULL;
24 l_valid_date varchar2(10);
25 CURSOR c_type IS
26 SELECT /*+ INDEX(ppt,PER_PERSON_TYPES_PK) */ 'Y'
27 FROM per_person_types ppt
28 WHERE ppt.system_person_type IN ('CWK','EMP','APL')
29 AND ppt.person_type_Id = p_person_type_id;
30 CURSOR c_emp_type IS
31 SELECT 'Y'
32 FROM per_person_types
33 WHERE system_person_type LIKE 'EMP%'
34 AND person_type_Id = p_person_type_id;
35 l_emp_type varchar2(10);
36 BEGIN
37
38 /* Added for GSI Bug 5472781 */
39 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
40
41 l_emp_type:= NULL;
42 g_type := NULL;
43 IF p_per_information_category = 'SA' THEN
44 OPEN c_emp_type;
45 FETCH c_emp_type INTO l_emp_type;
46 CLOSE c_emp_type;
47 --Validate not null fields
48 /*IF p_title IS NULL THEN
49 v_field := hr_general.decode_lookup('SA_FORM_LABELS','TITLE');
50 END IF; */ /* To Fix Bug 4432530*/
51 IF l_emp_type = 'Y' THEN -- To Fix Bug 4432530
52 IF p_first_name IS NULL THEN
53 IF v_field IS NULL THEN
54 v_field := hr_general.decode_lookup('SA_FORM_LABELS','M_FIRST_NAME');
55 ELSE
56 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','M_FIRST_NAME');
57 END IF;
58 END IF;
59 END IF; -- For EMP check
60 /* IF p_per_information1 IS NULL THEN
61 IF v_field IS NULL THEN
62 v_field := hr_general.decode_lookup('SA_FORM_LABELS','M_FATHER_NAME');
63 ELSE
64 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','M_FATHER_NAME');
65 END IF;
66 END IF; */ --Removed as per enhancement bug 3580573
67 --Validate grandfather is not null if nationality is saudi
68 IF UPPER(p_nationality) = FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') THEN
69 /*IF p_per_information2 IS NULL THEN
70 IF v_field IS NULL THEN
71 v_field := hr_general.decode_lookup('SA_FORM_LABELS','M_GRANDFATHER_NAME');
72 ELSE
73 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','M_GRANDFATHER_NAME');
74 END IF;
75 END IF;*/
76 IF p_national_identifier IS NULL THEN
77 IF v_field IS NULL THEN
78 v_field := hr_general.decode_lookup('SA_FORM_LABELS','CIVIL_IDENTITY');
79 ELSE
80 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','CIVIL_IDENTITY');
81 END IF;
82 END IF;
83 END IF;
84 /* IF l_emp_type = 'Y' THEN
85 IF p_per_information3 IS NULL THEN
86 IF v_field IS NULL THEN
87 v_field := hr_general.decode_lookup('SA_FORM_LABELS','M_ALT_FIRST_NAME');
88 ELSE
89 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','M_ALT_FIRST_NAME');
90 END IF;
91 END IF; */ --Removed as per bug 4150446
92 /* IF p_per_information4 IS NULL THEN
93 IF v_field IS NULL THEN
94 v_field := hr_general.decode_lookup('SA_FORM_LABELS','M_ALT_FATHER_NAME');
95 ELSE
96 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','M_ALT_FATHER_NAME');
97 END IF;
98 END IF; */ --Removed as per enhancement bug 3580573
99 /* IF p_per_information6 IS NULL THEN
100 IF v_field IS NULL THEN
101 v_field := hr_general.decode_lookup('SA_FORM_LABELS','M_ALT_FAMILY_NAME');
102 ELSE
103 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','M_ALT_FAMILY_NAME');
104 END IF;
105 END IF;
106 END IF;*/ --Removed as per bug 4150446
107 OPEN c_type;
108 FETCH c_type INTO g_type;
109 CLOSE c_type;
110 IF g_type IS NOT NULL THEN
111 /*IF p_per_information7 IS NULL THEN
112 IF v_field IS NULL THEN
113 v_field := hr_general.decode_lookup('SA_FORM_LABELS','RELIGION');
114 ELSE
115 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','RELIGION');
116 END IF;
117 END IF;*/
118 IF l_emp_type = 'Y' THEN -- To Fix Bug 4438655
119 IF p_nationality IS NULL THEN
120 IF v_field IS NULL THEN
121 v_field := hr_general.decode_lookup('SA_FORM_LABELS','NATIONALITY');
122 ELSE
123 v_field := v_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','NATIONALITY');
124 END IF;
125 END IF;
126 END IF; -- To Fix Bug 4438655
127 END IF;
128 IF v_field IS NOT NULL THEN
129 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
130 fnd_message.set_token('NAME',v_field, translate => true );
131 hr_utility.raise_error;
132 END IF;
133 --Validate the gender/title combination.
134 IF p_sex IS NOT NULL THEN
135 IF (p_sex = 'M'
136 AND UPPER(p_title) IN ('PRINCESS','MISS','MRS.','MS.'))
137 OR
138 (p_sex = 'F'
139 AND UPPER(p_title) IN ('HH','PRINCE','MR.')) THEN
140 -- Error: Values for Sex and Title are inconsistent.
141 fnd_message.set_name('PAY', 'HR_6527_PERSON_SEX_AND_TITLE');
142 hr_utility.raise_error;
143 END IF;
144 END IF;
145 --Validate that the Hijrah Birth Date is a valid hijrah date
146 IF p_per_information8 is not null THEN
147 hr_sa_hijrah_functions.validate_date(p_per_information8,l_valid_date);
148 END IF;
149 END IF;
150 END IF;
151 END VALIDATE;
152 --Procedure for validating person
153 PROCEDURE person_validate
154 (p_person_id in number
155 ,p_person_type_id in number
156 ,p_effective_date in date
157 ,p_sex in varchar2
158 ,p_first_name in varchar2 default null
159 ,p_nationality in varchar2 default null
160 ,p_national_identifier in varchar2 default null
161 ,p_title in varchar2 default null
162 ,p_per_information_category in varchar2 default null
163 ,p_per_information1 in varchar2 default null
164 ,p_per_information2 in varchar2 default null
165 ,p_per_information3 in varchar2 default null
166 ,p_per_information4 in varchar2 default null
167 ,p_per_information5 in varchar2 default null
168 ,p_per_information6 in varchar2 default null
169 ,p_per_information7 in varchar2 default null
170 ,p_per_information8 in varchar2 default null
171 ,p_per_information9 in varchar2 default null
172 ,p_per_information10 in varchar2 default null
173 ) IS
174 CURSOR csr_person_type_id IS
175 SELECT person_type_id
176 FROM per_all_people_f
177 WHERE person_id = p_person_id
178 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
179 l_person_type_id NUMBER;
180 BEGIN
181 OPEN csr_person_type_id;
182 FETCH csr_person_type_id INTO l_person_type_id;
183 CLOSE csr_person_type_id;
184 validate
185 (p_person_type_id => l_person_type_id
186 ,p_sex => p_sex
187 ,p_first_name => p_first_name
188 ,p_nationality => p_nationality
189 ,p_national_identifier => p_national_identifier
190 ,p_title => p_title
191 ,p_per_information_category => p_per_information_category
192 ,p_per_information1 => p_per_information1
193 ,p_per_information2 => p_per_information2
194 ,p_per_information3 => p_per_information3
195 ,p_per_information4 => p_per_information4
196 ,p_per_information5 => p_per_information5
197 ,p_per_information6 => p_per_information6
198 ,p_per_information7 => p_per_information7
199 ,p_per_information8 => p_per_information8
200 ,p_per_information9 => p_per_information9
201 ,p_per_information10 => p_per_information10);
202
203 /* Added for GSI Bug 5472781 */
204 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
205
206 if g_type IS NOT NULL THEN
207 if p_per_information_category = 'SA' and p_per_information7 is not null then
208 --
209 -- Check that the religion exists in hr_lookups for the
210 -- lookup type 'SA_RELIGION' with an enabled flag set to 'Y' and that
211 -- the effective start date of the person is between start date
212 -- active and end date active in hr_lookups.
213 --
214 if hr_api.not_exists_in_hr_lookups
215 (p_effective_date => p_effective_date
216 ,p_lookup_type => 'SA_RELIGION'
217 ,p_lookup_code => p_per_information7
218 )
219 then
220 --
221 hr_utility.set_message(800, 'HR_374803_SA_INVALID_RELIGION');
222 hr_utility.raise_error;
223 --
224 end if;
225 end if;
226 end if;
227 END IF;
228 END person_validate;
229 --Procedure for validating applicant
230 PROCEDURE applicant_validate
231 (p_business_group_id in number
232 ,p_person_type_id in number
233 ,p_date_received in date
234 ,p_sex in varchar2
235 ,p_first_name in varchar2 default null
236 ,p_nationality in varchar2 default null
237 ,p_national_identifier in varchar2 default null
238 ,p_title in varchar2 default null
239 ,p_per_information_category in varchar2 default null
240 ,p_per_information1 in varchar2 default null
241 ,p_per_information2 in varchar2 default null
242 ,p_per_information3 in varchar2 default null
243 ,p_per_information4 in varchar2 default null
244 ,p_per_information5 in varchar2 default null
245 ,p_per_information6 in varchar2 default null
246 ,p_per_information7 in varchar2 default null
247 ,p_per_information8 in varchar2 default null
248 ,p_per_information9 in varchar2 default null
249 ,p_per_information10 in varchar2 default null
250 ) IS
251 l_person_type_id NUMBER;
252 BEGIN
253 per_per_bus.chk_person_type
254 (p_person_type_id => l_person_type_id
255 ,p_business_group_id => p_business_group_id
256 ,p_expected_sys_type => 'APL'
257 );
258 validate
259 (p_person_type_id => l_person_type_id
260 ,p_sex => p_sex
261 ,p_first_name => p_first_name
262 ,p_nationality => p_nationality
263 ,p_national_identifier => p_national_identifier
264 ,p_title => p_title
265 ,p_per_information_category => p_per_information_category
266 ,p_per_information1 => p_per_information1
267 ,p_per_information2 => p_per_information2
268 ,p_per_information3 => p_per_information3
269 ,p_per_information4 => p_per_information4
270 ,p_per_information5 => p_per_information5
271 ,p_per_information6 => p_per_information6
272 ,p_per_information7 => p_per_information7
273 ,p_per_information8 => p_per_information8
274 ,p_per_information9 => p_per_information9
275 ,p_per_information10 => p_per_information10);
276
277 /* Added for GSI Bug 5472781 */
278 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
279
280 if g_type IS NOT NULL THEN
281 if p_per_information_category = 'SA' and p_per_information7 is not null then
282 --
283 -- Check that the religion exists in hr_lookups for the
284 -- lookup type 'SA_RELIGION' with an enabled flag set to 'Y' and that
285 -- the effective start date of the person is between start date
286 -- active and end date active in hr_lookups.
287 --
288 if hr_api.not_exists_in_hr_lookups
289 (p_effective_date => p_date_received
290 ,p_lookup_type => 'SA_RELIGION'
291 ,p_lookup_code => p_per_information7
292 )
293 then
294 --
295 hr_utility.set_message(800, 'HR_374803_SA_INVALID_RELIGION');
296 hr_utility.raise_error;
297 --
298 end if;
299 end if;
300 end if;
301 END IF;
302 END applicant_validate;
303 --Procedure for validating employee
304 PROCEDURE employee_validate
305 (p_business_group_id in number
306 ,p_person_type_id in number
307 ,p_hire_date in date
308 ,p_sex in varchar2
309 ,p_first_name in varchar2 default null
310 ,p_nationality in varchar2 default null
311 ,p_national_identifier in varchar2 default null
312 ,p_title in varchar2 default null
313 ,p_per_information_category in varchar2 default null
314 ,p_per_information1 in varchar2 default null
315 ,p_per_information2 in varchar2 default null
316 ,p_per_information3 in varchar2 default null
317 ,p_per_information4 in varchar2 default null
318 ,p_per_information5 in varchar2 default null
319 ,p_per_information6 in varchar2 default null
320 ,p_per_information7 in varchar2 default null
321 ,p_per_information8 in varchar2 default null
322 ,p_per_information9 in varchar2 default null
323 ,p_per_information10 in varchar2 default null
324 ) IS
325 l_person_type_id number;
326 l_valid_date varchar2(10);
327 BEGIN
328 per_per_bus.chk_person_type
329 (p_person_type_id => l_person_type_id
330 ,p_business_group_id => p_business_group_id
331 ,p_expected_sys_type => 'EMP'
332 );
333 validate
334 (p_person_type_id => l_person_type_id
335 ,p_sex => p_sex
336 ,p_first_name => p_first_name
337 ,p_nationality => p_nationality
338 ,p_national_identifier => p_national_identifier
339 ,p_title => p_title
340 ,p_per_information_category => p_per_information_category
341 ,p_per_information1 => p_per_information1
342 ,p_per_information2 => p_per_information2
343 ,p_per_information3 => p_per_information3
344 ,p_per_information4 => p_per_information4
345 ,p_per_information5 => p_per_information5
346 ,p_per_information6 => p_per_information6
347 ,p_per_information7 => p_per_information7
348 ,p_per_information8 => p_per_information8
349 ,p_per_information9 => p_per_information9
350 ,p_per_information10 => p_per_information10);
351
352 /* Added for GSI Bug 5472781 */
353 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
354
355 if g_type IS NOT NULL THEN
356 if p_per_information_category = 'SA' and p_per_information7 is not null then
357 --
358 -- Check that the religion exists in hr_lookups for the
359 -- lookup type 'SA_RELIGION' with an enabled flag set to 'Y' and that
360 -- the effective start date of the person is between start date
361 -- active and end date active in hr_lookups.
362 --
363 if hr_api.not_exists_in_hr_lookups
364 (p_effective_date => p_hire_date
365 ,p_lookup_type => 'SA_RELIGION'
366 ,p_lookup_code => p_per_information7
367 )
368 then
369 --
370 hr_utility.set_message(800, 'HR_374803_SA_INVALID_RELIGION');
371 hr_utility.raise_error;
372 --
373 end if;
374 end if;
375 --Validate that the Hijrah Hire Date is a valid hijrah date
376 if p_per_information_category = 'SA' and p_per_information9 is not null then
377 hr_sa_hijrah_functions.validate_date(p_per_information9,l_valid_date);
378 end if;
379 end if;
380 END IF;
381 END employee_validate;
382 --Procedure for validating contact/cwk
383 PROCEDURE contact_cwk_validate
384 (p_business_group_id in number
385 ,p_person_type_id in number
386 ,p_start_date in date
387 ,p_sex in varchar2
388 ,p_first_name in varchar2 default null
389 ,p_nationality in varchar2 default null
390 ,p_national_identifier in varchar2 default null
391 ,p_title in varchar2 default null
392 ,p_per_information_category in varchar2 default null
393 ,p_per_information1 in varchar2 default null
394 ,p_per_information2 in varchar2 default null
395 ,p_per_information3 in varchar2 default null
396 ,p_per_information4 in varchar2 default null
397 ,p_per_information5 in varchar2 default null
398 ,p_per_information6 in varchar2 default null
399 ,p_per_information7 in varchar2 default null
400 ,p_per_information8 in varchar2 default null
401 ,p_per_information9 in varchar2 default null
402 ,p_per_information10 in varchar2 default null
403 ) IS
404 l_person_type_id NUMBER;
405 BEGIN
406 per_per_bus.chk_person_type
407 (p_person_type_id => l_person_type_id
408 ,p_business_group_id => p_business_group_id
409 ,p_expected_sys_type => 'OTHER'
410 );
411 validate
412 (p_person_type_id => l_person_type_id
413 ,p_sex => p_sex
414 ,p_first_name => p_first_name
415 ,p_nationality => p_nationality
416 ,p_national_identifier => p_national_identifier
417 ,p_title => p_title
418 ,p_per_information_category => p_per_information_category
419 ,p_per_information1 => p_per_information1
420 ,p_per_information2 => p_per_information2
421 ,p_per_information3 => p_per_information3
422 ,p_per_information4 => p_per_information4
423 ,p_per_information5 => p_per_information5
424 ,p_per_information6 => p_per_information6
425 ,p_per_information7 => p_per_information7
426 ,p_per_information8 => p_per_information8
427 ,p_per_information9 => p_per_information9
428 ,p_per_information10 => p_per_information10);
429
430 /* Added for GSI Bug 5472781 */
431 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
432
433 if g_type IS NOT NULL THEN
434 if p_per_information_category = 'SA' and p_per_information7 is not null then
435 --
436 -- Check that the religion exists in hr_lookups for the
437 -- lookup type 'SA_RELIGION' with an enabled flag set to 'Y' and that
438 -- the effective start date of the person is between start date
439 -- active and end date active in hr_lookups.
440 --
441 if hr_api.not_exists_in_hr_lookups
442 (p_effective_date => p_start_date
443 ,p_lookup_type => 'SA_RELIGION'
444 ,p_lookup_code => p_per_information7
445 )
446 then
447 --
448 hr_utility.set_message(800, 'HR_374803_SA_INVALID_RELIGION');
449 hr_utility.raise_error;
450 --
451 end if;
452 end if;
453 end if;
454 END IF;
455 END contact_cwk_validate;
456 --Procedure for validating previous_employer
457 PROCEDURE previous_employer_validate
458 (p_employer_name IN varchar2 default hr_api.g_varchar2
459 ,p_start_date IN date default hr_api.g_date
460 ,p_pem_information_category IN varchar2 default hr_api.g_varchar2
461 ,p_pem_information1 IN varchar2 default hr_api.g_varchar2
462 ) IS
463 BEGIN
464
465 /* Added for GSI Bug 5472781 */
466 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
467
468 if p_pem_information_category = 'SA' then
469 if p_employer_name is null then
470 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
471 fnd_message.set_token('NAME',p_employer_name, translate => true );
472 hr_utility.raise_error;
473 end if;
474 end if;
475 END IF;
476 END previous_employer_validate;
477 --Procedure for validating contract
478 PROCEDURE contract_validate
479 (p_effective_date in date
480 ,p_type in varchar2
481 ,p_duration in number default null
482 ,p_duration_units in varchar2 default null
483 ,p_contractual_job_title in varchar2 default null
484 ,p_ctr_information_category in varchar2 default null
485 ,p_ctr_information1 in varchar2 default null
486 ,p_ctr_information2 in varchar2 default null
487 ,p_ctr_information3 in varchar2 default null
488 ,p_ctr_information4 in varchar2 default null
489 ,p_ctr_information5 in varchar2 default null
490 ) is
491 l_field VARCHAR2(300) := NULL;
492 BEGIN
493
494 /* Added for GSI Bug 5472781 */
495 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
496
497 IF p_ctr_information_category = 'SA' THEN
498 IF p_type = 'SPECIFIED_PERIOD' THEN
499 IF p_duration IS NULL THEN
500 l_field := hr_general.decode_lookup('SA_FORM_LABELS','DURATION');
501 END IF;
502 IF p_duration_units IS NULL THEN
503 IF l_field IS NULL THEN
504 l_field := hr_general.decode_lookup('SA_FORM_LABELS','DURATION_UNITS');
505 ELSE
506 l_field := l_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','DURATION_UNITS');
507 END IF;
508 END IF;
509 END IF;
510 IF p_contractual_job_title IS NULL THEN
511 IF l_field IS NULL THEN
512 l_field := hr_general.decode_lookup('SA_FORM_LABELS','PROFESSION');
513 ELSE
514 l_field := l_field||', '||hr_general.decode_lookup('SA_FORM_LABELS','PROFESSION');
515 END IF;
516 END IF;
517 IF l_field IS NOT NULL THEN
518 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
519 fnd_message.set_token('NAME',l_field, translate => true );
520 hr_utility.raise_error;
521 END IF;
522 -- Check that the employment status exists in hr_lookups for the
523 -- lookup type 'SA_EMPLOYMENT_STATUS' with an enabled flag set to 'Y'
524 --
525 IF p_ctr_information1 IS NOT NULL THEN
526 IF hr_api.not_exists_in_hr_lookups
527 (p_effective_date => p_effective_date
528 ,p_lookup_type => 'SA_EMPLOYMENT_STATUS'
529 ,p_lookup_code => p_ctr_information1
530 )
531 THEN
532 --
533 hr_utility.set_message(800, 'HR_374804_SA_INVALID_EMP_STAT');
534 hr_utility.raise_error;
535 --
536 END IF;
537 END IF;
538 END IF;
539 END IF;
540 END contract_validate;
541 PROCEDURE periods_of_service_validate
542 (p_period_of_service_id IN number
543 ,p_pds_information_category IN varchar2 default hr_api.g_varchar2
544 ,p_pds_information1 IN varchar2 default hr_api.g_varchar2
545 ) is
546 l_hijrah_date varchar2(10);
547 l_valid_date varchar2(10);
548 BEGIN
549 /* Added for GSI Bug 5472781 */
550 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
551 IF p_pds_information_category = 'SA' THEN
552 l_hijrah_date := p_pds_information1;
553 hr_sa_hijrah_functions.validate_date(l_hijrah_date,l_valid_date);
554 END IF;
555 END IF;
556 END periods_of_service_validate;
557
558 PROCEDURE person_eit_validate
559 (p_pei_information_category IN varchar2 default hr_api.g_varchar2
560 ,p_pei_information1 IN varchar2 default hr_api.g_varchar2
561 ,p_pei_information2 IN varchar2 default hr_api.g_varchar2
562 ,p_pei_information3 IN varchar2 default hr_api.g_varchar2
563 ,p_pei_information4 IN varchar2 default hr_api.g_varchar2
564 ,p_pei_information5 IN varchar2 default hr_api.g_varchar2
565 ,p_pei_information6 IN varchar2 default hr_api.g_varchar2
566 ,p_pei_information7 IN varchar2 default hr_api.g_varchar2
567 ,p_pei_information8 IN varchar2 default hr_api.g_varchar2
568 ,p_pei_information9 IN varchar2 default hr_api.g_varchar2
569 ,p_pei_information10 IN varchar2 default hr_api.g_varchar2
570 ) is
571 l_valid_date1 varchar2(10);
572 l_valid_date2 varchar2(10);
573 BEGIN
574 /* Added for GSI Bug 5472781 */
575 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
576 IF p_pei_information_category = 'SA_IQAMA' and p_pei_information3 is not null THEN
577 hr_sa_hijrah_functions.validate_date(p_pei_information3,l_valid_date1);
578 END IF;
579 IF p_pei_information_category = 'SA_IQAMA' and p_pei_information4 is not null THEN
580 hr_sa_hijrah_functions.validate_date(p_pei_information4,l_valid_date2);
581 END IF;
582 END IF;
583 END person_eit_validate;
584
585
586
587 PROCEDURE create_person_eit_validate
588 (p_person_id IN number
589 ,p_pei_information_category IN varchar2 default hr_api.g_varchar2
590 ,p_pei_information1 IN varchar2 default hr_api.g_varchar2
591 ,p_pei_information2 IN varchar2 default hr_api.g_varchar2
592 ,p_pei_information3 IN varchar2 default hr_api.g_varchar2
593 ,p_pei_information4 IN varchar2 default hr_api.g_varchar2
594 ,p_pei_information5 IN varchar2 default hr_api.g_varchar2
595 ,p_pei_information6 IN varchar2 default hr_api.g_varchar2
596 ,p_pei_information7 IN varchar2 default hr_api.g_varchar2
597 ,p_pei_information8 IN varchar2 default hr_api.g_varchar2
598 ,p_pei_information9 IN varchar2 default hr_api.g_varchar2
599 ,p_pei_information10 IN varchar2 default hr_api.g_varchar2
600 ) is
601 l_valid_date1 varchar2(10);
602 l_valid_date2 varchar2(10);
603 l_nationality varchar2(30);
604 CURSOR csr_get_nationality (l_person_id number) IS
605 Select nationality
606 From per_all_people_f ppf , fnd_sessions fnd
607 Where ppf.person_id = l_person_id
608 and fnd.session_id = userenv('sessionid')
609 And fnd.effective_date between ppf.effective_start_date and ppf.effective_end_date;
610 BEGIN
611
612 /* Added for GSI Bug 5472781 */
613 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
614
615 IF p_pei_information_category = 'SA_IQAMA' and p_pei_information3 is not null THEN
616 hr_sa_hijrah_functions.validate_date(p_pei_information3,l_valid_date1);
617 END IF;
618 IF p_pei_information_category = 'SA_IQAMA' and p_pei_information4 is not null THEN
619 hr_sa_hijrah_functions.validate_date(p_pei_information4,l_valid_date2);
620 END IF;
621 IF p_pei_information_category = 'SA_HAFIZA' and p_pei_information1 is not null THEN
622 OPEN csr_get_nationality(p_person_id);
623 FETCH csr_get_nationality into l_nationality;
624 CLOSE csr_get_nationality ;
625 if upper(l_nationality) <> FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') THEN
626 hr_utility.set_message(800, 'HR_374813_SA_INVALID_HAFIZA');
627 hr_utility.raise_error;
628 end if;
629 END IF;
630 END IF;
631 END create_person_eit_validate;
632
633
634 PROCEDURE update_person_eit_validate
635 (p_person_extra_info_id IN number
636 ,p_pei_information_category IN varchar2 default hr_api.g_varchar2
637 ,p_pei_information1 IN varchar2 default hr_api.g_varchar2
638 ,p_pei_information2 IN varchar2 default hr_api.g_varchar2
639 ,p_pei_information3 IN varchar2 default hr_api.g_varchar2
640 ,p_pei_information4 IN varchar2 default hr_api.g_varchar2
641 ,p_pei_information5 IN varchar2 default hr_api.g_varchar2
642 ,p_pei_information6 IN varchar2 default hr_api.g_varchar2
643 ,p_pei_information7 IN varchar2 default hr_api.g_varchar2
644 ,p_pei_information8 IN varchar2 default hr_api.g_varchar2
645 ,p_pei_information9 IN varchar2 default hr_api.g_varchar2
646 ,p_pei_information10 IN varchar2 default hr_api.g_varchar2
647 ) is
648 l_valid_date1 varchar2(10);
649 l_valid_date2 varchar2(10);
650 l_nationality varchar2(30);
651 l_person_id number;
652 CURSOR csr_get_pid (l_person_extra_info_id number) IS
653 Select person_id
654 From per_people_extra_info
655 Where person_extra_info_id = l_person_extra_info_id;
656 CURSOR csr_get_nationality (l_person_id number) IS
657 Select nationality
658 From per_all_people_f ppf , fnd_sessions fnd
659 Where ppf.person_id = l_person_id
660 and fnd.session_id = userenv('sessionid')
661 And fnd.effective_date between ppf.effective_start_date and ppf.effective_end_date;
662 BEGIN
663
664 /* Added for GSI Bug 5472781 */
665 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
666
667 IF p_pei_information_category = 'SA_IQAMA' and p_pei_information3 is not null THEN
668 hr_sa_hijrah_functions.validate_date(p_pei_information3,l_valid_date1);
669 END IF;
670 IF p_pei_information_category = 'SA_IQAMA' and p_pei_information4 is not null THEN
671 hr_sa_hijrah_functions.validate_date(p_pei_information4,l_valid_date2);
672 END IF;
673 IF p_pei_information_category = 'SA_HAFIZA' and p_pei_information1 is not null THEN
674 OPEN csr_get_pid(p_person_extra_info_id);
675 FETCH csr_get_pid into l_person_id;
676 CLOSE csr_get_pid;
677 OPEN csr_get_nationality(l_person_id);
678 FETCH csr_get_nationality into l_nationality;
679 CLOSE csr_get_nationality ;
680 if upper(l_nationality) <> FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') THEN
681 hr_utility.set_message(800, 'HR_374813_SA_HAFIZA_INVALID');
682 hr_utility.raise_error;
683 end if;
684 END IF;
685 END IF;
686 END update_person_eit_validate;
687
688 PROCEDURE assignment_annuities_validate
689 (p_segment3 IN VARCHAR2
690 ,p_effective_date IN DATE
691 ,p_assignment_id IN NUMBER ) IS
692 l_nationality varchar2(40);
693 l_annuities varchar2(40);
694 l_person_id number;
695 CURSOR csr_nationality IS
696 SELECT NATIONALITY
697 FROM per_all_people_f
698 WHERE person_id = l_person_id
699 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
700 BEGIN
701
702 /* Added for GSI Bug 5472781 */
703 IF hr_utility.chk_product_install('Oracle Human Resources', 'SA') THEN
704
705 SELECT person_id
706 INTO l_person_id
707 FROM per_all_assignments_f
708 WHERE assignment_id = p_assignment_id
709 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
710 open csr_nationality;
711 fetch csr_nationality into l_nationality;
712 close csr_nationality;
713 IF p_segment3 = 'Y' and UPPER(l_nationality) <> FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') THEN
714 hr_utility.set_message(800, 'HR_374811_SA_INVALID_ANNUITIES');
715 hr_utility.raise_error;
716 END IF;
717 END IF;
718 END assignment_annuities_validate;
719 END hr_sa_validate_pkg;
[Home] [Help]