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