1 PACKAGE BODY PER_ZA_USER_HOOK_PKG AS
2 /* $Header: pezauhkp.pkb 120.35.12020000.2 2012/08/16 11:43:47 abdash ship $ */
3 --------------------
4 -- Package variables
5 --------------------
6 g_package varchar2(21) := 'per_za_user_hook_pkg.';
7 --------------------------------------------------------------------------
8 -- validate_race --
9 --------------------------------------------------------------------------
10 -- Description:
11 -- Checks that a valid race has been entered for
12 -- applicants, employees and contingent workers.
13 -- Called from:
14 -- validate_applicant
15 -- validate_employee
16 -- validate_cwk
17 -- validate_person
18 -- Person DDF segments used :
19 -- SEGMENT NAME
20 -- ------- ----
21 -- PER_INFORMATION4 Race
22 --
23 ---------------------------------------------------------------------------
24 -- --
25 ---------------------------------------------------------------------------
26 PROCEDURE validate_race
27 ( p_effective_date in date
28 , p_person_type_id in number default null
29 , p_race_code in varchar2 default null
30 ) IS
31 -- Cursors
32 --
33 CURSOR c_system_person_type
34 IS
35 SELECT typ.system_person_type
36 FROM per_person_types typ
37 WHERE typ.person_type_id = p_person_type_id;
38 -- Local Variables
39 --
40 l_proc varchar2(40) := g_package||'validate_race';
41 l_system_person_type per_person_types.system_person_type%TYPE;
42 BEGIN
43
44 -- The Race segment is mandatory for employees, applicants and
45 -- contingent workers
46 --
47 -- Find the system person type
48 --
49 OPEN c_system_person_type;
50 FETCH c_system_person_type INTO l_system_person_type;
51 CLOSE c_system_person_type;
52
53 -- RACE is required for system person types
54 -- of APL, EMP and CWK
55 --
56 IF l_system_person_type IN ('APL','EMP','CWK') THEN
57 IF hr_multi_message.no_exclusive_error
58 ( p_check_column1 => 'PER_ALL_PEOPLE_F.PER_INFORMATION4'
59 )
60 THEN
61 IF p_race_code IS NULL OR
62 p_race_code = 'N' OR
63 hr_api.not_exists_in_hr_lookups
64 (p_effective_date => p_effective_date
65 ,p_lookup_type => 'ZA_RACE'
66 ,p_lookup_code => p_race_code
67 )
68 THEN
69 fnd_message.set_name('PER', 'HR_ZA_MAND_SEG_RACE');
70 hr_multi_message.add
71 (p_associated_column1 => 'PER_ALL_PEOPLE_F.PER_INFORMATION4'
72 );
73 END IF;
74 END IF;
75 END IF;
76 END validate_race;
77
78 --
79
80 --------------------------------------------------------------------------
81 -- validate_nature --
82 --------------------------------------------------------------------------
83 -- Description:
84 -- Checks that a valid nature has been entered for
85 -- employees and do the other validations based on nature of person
86 -- Called from:
87 -- validate_employee
88 -- validate_person
89 -- Person DDF segments used :
90 -- SEGMENT NAME
91 -- ------- ----
92 -- PER_INFORMATION12 Nature of Person
93 --
94 ---------------------------------------------------------------------------
95 -- --
96 ---------------------------------------------------------------------------
97 PROCEDURE validate_nature
98 ( p_effective_date in date,
99 p_nature in varchar2,
100 p_first_name in varchar2,
101 p_middle_name in varchar2,
102 p_last_name in varchar2,
103 p_empno in varchar2,
104 p_ni in varchar2,
105 p_passport_no in varchar2,
106 p_country_passport in varchar2,
107 p_it_no_value in varchar2,
108 p_dob in date
109 ) IS
110 -- Cursors
111 --
112 l_proc varchar2(40) := g_package||'validate_nature';
113 l_alpha varchar2(52) := '- ,''.';
114 l_invalid_char varchar2(1) := '~';
115 l_profile varchar2(1) := 'N';
116 BEGIN
117
118 hr_utility.set_location('Entering '||l_proc,10);
119
120 --Validate Nature of Person (Code 3020)
121 hr_utility.set_location('Validation Nature of Person -Code 3020',10);
122 if p_nature = '10' then
123 hr_utility.set_location('Nature of Person is M',10);
124 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NATURE_PERSON');
125 fnd_message.set_token('NATURE', 'M');
126 fnd_message.raise_error;
127 elsif p_nature = '09' then
128 hr_utility.set_location('Nature of Person is K',10);
129 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NATURE_PERSON');
130 fnd_message.set_token('NATURE', 'K');
131 fnd_message.raise_error;
132 end if;
133
134 -- Validate Code Employee Surname/Trading Name (Code 3030)
135 -- hr_utility.set_location('Validation EE Surname/Trading Name -Code 3030',15);
136 -- if p_nature in ('D','E','F','G','H') then
137 -- if p_last_name is null then
138 -- hr_utility.set_location('EE Surname is null',15);
139 -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_DEFGH_TRADE');
140 -- fnd_message.raise_error;
141 -- elsif validate_charcter_set(p_last_name,'FREETEXT')= FALSE then
142 -- hr_utility.set_location('EE Surname contains invalid characters',15);
143 -- fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
144 -- fnd_message.set_token('FIELD', 'Employee Trading Name');
145 -- fnd_message.raise_error;
146 -- end if;
147 if p_nature in ('01','02','03','11') then
148 --Validate code 3030
149 --Checking IF the Surname IS Null
150 IF p_last_name IS NULL then
151 hr_utility.set_location('EE Surname is null',16);
152 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ABC_S_F_NAME');
153 -- fnd_message.set_token('FIELD', 'Employee''s Surname');
154 fnd_message.raise_error;
155 END if;
156 /* commented as the numeric are allowed in 3030 as it is free text bug 9507670*/
157 /* uncommented for Aug 2010 reconciliation phase II as numeric are not allowed in 3030 */
158 if p_last_name <> hr_api.g_varchar2 then -- Bug#14361099
159 if nvl(length(translate(p_last_name,'~0123456789','~')),0) <> length(p_last_name) then
160 hr_utility.set_location('EE last name contains invalid characters',15);
161 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
162 fnd_message.set_token('FIELD', 'Employee''s Surname');
163 fnd_message.raise_error;
164 end if;
165 end if; -- Bug#14361099
166 end if;
167
168 --Validate First Two Names ( Code 3040 )
169 hr_utility.set_location('Validation First Two Names -Code 3040',20);
170 if p_nature in ('01','02','03','11') then
171 if p_first_name <> hr_api.g_varchar2 and p_middle_name <> hr_api.g_varchar2 then -- Bug#14361099
172 if p_first_name is null and p_middle_name is null then
173 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ABCN_F_NAME');
174 fnd_message.raise_error;
175 /* uncommented as the numeric are not allowed in first two Names bug 9507670*/
176 elsif nvl(length(translate(p_first_name,'~0123456789','~')),0) <> nvl(length(p_first_name),0)
177 OR nvl(length(translate(p_middle_name,'~0123456789','~')),0) <> nvl(length(p_middle_name),0) then
178 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
179 fnd_message.set_token('FIELD', 'First or Middle Name');
180 fnd_message.raise_error;
181 end if;
182 --First and Middle name contains invalid characters only
183 --characters like ' ',- etc (Invalid characters for Initials
184 --Validate Initials
185
186 if nvl(length(translate(p_first_name,l_invalid_char||l_alpha,l_invalid_char)),0) = 0
187 AND nvl(length(translate(p_middle_name,l_invalid_char||l_alpha,l_invalid_char)),0) = 0
188 AND (p_first_name is not null OR p_middle_name is not null) then
189 fnd_message.set_name('PAY', 'PY_ZA_INVALID_INITIALS');
190 fnd_message.raise_error;
191 end if;
192 end if; -- Bug#14361099
193 end if;
194
195
196 --Validate Identity Number (Code 3060)
197 hr_utility.set_location('Validating Identity Number -Code 3060',22);
198 if p_nature in ('01','03','11') then
199 if p_ni <> hr_api.g_varchar2 then -- Bug#14361099
200 if p_ni is null and p_passport_no is null and p_nature <> '11' then
201 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_ACN_ID_PASSNO');
202 fnd_message.raise_error;
203 end if;
204 if nvl(length(p_ni),13)<>13 then
205 hr_utility.set_location('p_ni:'||p_ni,22);
206 fnd_message.set_name('PER', 'HR_ZA_INVALID_LENGTH');
207 fnd_message.set_token('FIELD', 'ID Number');
208 fnd_message.set_token('LENGTH', '13');
209 fnd_message.set_token('UNITS', 'digits');
210 fnd_message.raise_error;
211 end if;
212 if validate_charcter_set(p_ni,'NUMERIC')= FALSE then
213 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
214 fnd_message.set_token('FIELD', 'ID Number');
215 fnd_message.raise_error;
216 end if;
217 if p_ni is not null then
218 if pay_za_eoy_val.modulus_13_test(p_ni)=0 then
219 fnd_message.set_name('PER', 'HR_ZA_INVALID_NI');
220 fnd_message.raise_error;
221 end if;
222 end if;
223 -- if p_id_dob = 0 then
224 if pay_za_eoy_val.check_id_dob(p_ni,p_dob,'Y') = 0 then
225 fnd_message.set_name('PAY', 'PY_ZA_INVALID_ID_DOB_CORRELAT');
226 fnd_message.raise_error;
227 end if;
228 end if; -- Bug#14361099
229 end if;
230 --Validate Passport Number (Code 3070)
231 hr_utility.set_location('Validating Passport Number -Code 3070',22);
232
233 if p_nature not in ('02','04','05','06','07','08') and p_passport_no is not null then
234 if p_passport_no <> hr_api.g_varchar2 then
235 -- bug 12914879
236 -- if length(p_passport_no)< 7 then
237 if length(p_passport_no)< 6 then
238 -- bug 12914879
239 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
240 fnd_message.set_token('FIELD', 'Passport Number');
241 -- bug 12914879
242 -- fnd_message.set_token('LENGTH', '7');
243 fnd_message.set_token('LENGTH', '6');
244 -- bug 12914879
245 fnd_message.set_token('UNITS', 'characters');
246 fnd_message.raise_error;
247 end if;
248
249 if validate_charcter_set(p_passport_no,'ALPHANUM')= FALSE then
250 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
251 fnd_message.set_token('FIELD', 'Passport Number');
252 fnd_message.raise_error;
253 end if;
254 end if; -- Bug#14361099
255 end if;
256 --Validate Country of Issue ( Code 3075 )
257 --If Nature of Person is B, then country of Issue is archived as ZNC, hence validation not required.
258 hr_utility.set_location('Validating Country of Issue -Code 3075',25);
259 if p_country_passport is null and p_passport_no is not null then -- 9877034 fix removed B and N
260 fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
261 fnd_message.raise_error;
262 elsif p_country_passport is not null then
263 if p_country_passport <> hr_api.g_varchar2 then -- Bug#14361099
264 if validate_charcter_set(p_country_passport,'ALPHA')= FALSE then -- 9877034 fix removed B
265 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
266 fnd_message.set_token('FIELD', 'Country of Passport Issue');
267 fnd_message.raise_error;
268 end if;
269 if length(p_country_passport)> 3 then -- 9877034 fix removed B
270 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
271 fnd_message.set_token('FIELD', 'Country of Passport Issue');
272 fnd_message.set_token('LENGTH', '3');
273 fnd_message.set_token('UNITS', 'characters');
274 fnd_message.raise_error;
275 end if;
276 end if ; -- Bug#14361099
277 end if;
278
279 --Validate code Income Tax Number (Code 3100)
280 hr_utility.set_location('Validating Income Tax Number -Code 3100',27);
281 if p_it_no_value is null and p_nature <> '06' then
282 fnd_message.set_name('PAY', 'PY_ZA_ENTER_IT_TAX_NO');
283 fnd_message.raise_error;
284 elsif p_it_no_value is not null and p_nature <> '06' then
285 --Check the modulus 10 test
286 if p_it_no_value <> hr_api.g_varchar2 then -- Bug#14361099
287 if pay_za_eoy_val.modulus_10_test(p_it_no_value) = 0 then
288 fnd_message.set_name('PAY', 'PY_ZA_INVALID_IT_TAX_NO');
289 fnd_message.raise_error;
290 end if;
291 if p_nature in ('01','02','03','04','11') and substr(p_it_no_value,1,1) not in ('0','1','2','3') then
292 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NAT_IT_TAX_NO');
293 fnd_message.set_token('VALID_NUM', '0, 1, 2 or 3');
294 fnd_message.set_token('VALID_NATURE', 'A, B, C, D, or N');
295 fnd_message.raise_error;
296 elsif p_nature in ('05','07','08') and substr(p_it_no_value,1,1) <> '9' then
297 fnd_message.set_name('PAY', 'PY_ZA_INVALID_NAT_IT_TAX_NO');
298 fnd_message.set_token('VALID_NUM', '9');
299 fnd_message.set_token('VALID_NATURE', 'E, G, or H');
300 fnd_message.raise_error;
301 end if;
302 if validate_charcter_set(p_it_no_value,'NUMERIC')= FALSE then
303 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
304 fnd_message.set_token('FIELD', 'Income Tax Number');
305 fnd_message.raise_error;
306 end if;
307 end if ; -- Bug#14361099
308 end if;
309 hr_utility.set_location('p_empno'|| p_empno, 14);
310
311 -- 11899934
312 -- Commenting out the validation for Employee Number as validation is done separately.
313 --Validate Employee Number (Code 3160)
314 /*
315 if validate_charcter_set(p_empno,'ALPHANUM')= FALSE then
316
317 hr_utility.set_location('p_empno' || p_empno, 15);
318 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
319 fnd_message.set_token('FIELD', 'Employee Number:' || p_empno ||':');
320 fnd_message.raise_error;
321 end if;
322 */
323 -- 11899934
324 hr_utility.set_location('Leaving '||l_proc,200);
325
326 end validate_nature;
327
328 -- 11899934
329
330 --------------------------------------------------------------------------
331 -- validate_employee_num --
332 --------------------------------------------------------------------------
333 -- Description:
334 -- Checks if the employee_num is freetext and
335 -- is not greater than 25 characters.
336 -- Called from:
337 -- validate_employee
338 -- validate_person
339 -- Person fields used : EMPLOYEE_NUM
340 --
341 ---------------------------------------------------------------------------
342 -- --
343 ---------------------------------------------------------------------------
344 procedure validate_emp_num
345 (
346 p_emp_num in varchar2 default null,
347 p_person_type_id in number default null -- Bug#12585688
348 ) IS
349
350 -- Bug#12585688
351
352 -- Cursors
353 CURSOR c_system_person_type
354 IS
355 SELECT typ.system_person_type
356 FROM per_person_types typ
357 WHERE typ.person_type_id = p_person_type_id;
358
359 -- Bug#12585688
360
361 -- Local Variables
362 --
363
364 l_proc varchar2(40) := g_package||'validate_emp_num';
365
366 l_system_person_type per_person_types.system_person_type%TYPE; --Bug#12585688
367
368 --
369 begin
370
371 -- Bug#12585688
372
373 -- Find the system person type
374 --
375 OPEN c_system_person_type;
376 FETCH c_system_person_type INTO l_system_person_type;
377 CLOSE c_system_person_type;
378
379
380 -- Bug#12585688
381
382 -- Check required only for 'EMP'
383 -- The employee number should be free text and shouldn't be
384 -- greater than 25 characters.
385 -- Validate Employee Number (Code 3160)
386
387 hr_utility.set_location('Entering '||l_proc,10);
388 hr_utility.set_location('p_empno'|| p_emp_num, 10);
389 hr_utility.set_location('p_person_type_id'|| p_person_type_id, 15);
390
391 if l_system_person_type = 'EMP' then
392 --
393 if ((p_emp_num is null)
394 or (length(p_emp_num) > 25 or per_za_user_hook_pkg.validate_charcter_set(p_emp_num, 'FREETEXT')= FALSE)) then
395 fnd_message.set_name('PER', 'HR_ZA_INVALID_EMP_NO');
396 fnd_message.raise_error;
397 end if;
398
399 end if;
400
401 hr_utility.set_location('Leaving '||l_proc,20);
402
403 end validate_emp_num;
404
405 -- 11899934
406
407
408 --------------------------------------------------------------------------
409 -- validate_email_id --
410 --------------------------------------------------------------------------
411 -- Description:
412 -- Checks that a valid email_id has been entered for
413 -- applicants, employees and contingent workers.
414 -- Called from:
415 -- validate_applicant
416 -- validate_employee
417 -- validate_cwk
418 -- validate_person
419 -- Person fields used : EMAIL_ADDRESS
420 --
421 ---------------------------------------------------------------------------
422 -- --
423 ---------------------------------------------------------------------------
424 procedure validate_email_id (p_email_id varchar2 ) is
425 l_validate_flag boolean := true ;
426 begin
427 if p_email_id is not null and p_email_id <> hr_api.g_varchar2 then
428 if length(p_email_id) >70 then
429 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
430 fnd_message.set_token('FIELD', 'Email Address');
431 fnd_message.set_token('LENGTH', '70');
432 fnd_message.set_token('UNITS', 'characters');
433 fnd_message.raise_error;
434 elsif instr(p_email_id,'@') <= 0 or instr(p_email_id,'.') <= 0 then
435 fnd_message.set_name('PER', 'HR_ZA_INVALID_CONTACT_EMAIL');
436 fnd_message.set_token('CONTACT', '');
437 fnd_message.raise_error;
438 elsif validate_charcter_set(p_email_id,'FREETEXT') = false then
439 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
440 fnd_message.set_token('FIELD', 'Email Address');
441 fnd_message.raise_error;
442 end if ;
443
444 end if;
445 end validate_email_id ;
446 --------------------------------------------------------------------------
447 -- validate_phone_number --
448 --------------------------------------------------------------------------
449 -- Description:
450 -- Checks that a valid phone_number has been entered for
451 -- applicants, employees and contingent workers.
452 --
453 ---------------------------------------------------------------------------
454 -- --
455 ---------------------------------------------------------------------------
456 procedure validate_phone_no ( p_phone_type in varchar2,
457 p_phone_number in varchar2) is
458 l_phone_int number ;
459 begin
460 hr_utility.trace('p_phone_type='||p_phone_type);
461 hr_utility.trace('p_phone_number='||p_phone_number);
462 if p_phone_number is not null and p_phone_number <> hr_api.g_varchar2 then
463 begin
464 l_phone_int := to_number(p_phone_number);
465 exception
466 when others then
467 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
468 fnd_message.set_token('FIELD', hr_general.decode_lookup('PHONE_TYPE', p_phone_type)||' phone number');
469 fnd_message.raise_error;
470 end;
471
472 if p_phone_type in ('GRE') then
473 if length(p_phone_number)<9 or length(p_phone_number)>11 then
474 fnd_message.set_name('PER', 'HR_ZA_INVALID_PH_NO');
475 fnd_message.set_token('FIELD', 'phone number');
476 fnd_message.raise_error;
477 end if;
478 elsif p_phone_type in ('H1','H2','H3','W1','W2','W3','HF','WF') then
479 if length(p_phone_number)<9 or length(p_phone_number)>11 then
480 fnd_message.set_name('PER', 'HR_ZA_INVALID_PH_NO');
481 fnd_message.set_token('FIELD', hr_general.decode_lookup('PHONE_TYPE', p_phone_type)||' phone number');
482 fnd_message.raise_error;
483 end if;
484 elsif p_phone_type in ('M') then
485 if length(p_phone_number)<10 or length(p_phone_number)>11 then
486 fnd_message.set_name('PER', 'HR_ZA_INVALID_MOBILE');
487 fnd_message.raise_error;
488 end if;
489 end if;
490 end if;
491 end validate_phone_no;
492 -------------------------------------------------------------------------------
493 -- validate_applicant
494 -------------------------------------------------------------------------------
495 PROCEDURE validate_applicant
496 ( p_business_group_id
497 in per_all_people_f.business_group_id%type default null
498 , p_date_received
499 in date
500 , p_person_type_id
501 in per_all_people_f.person_type_id%type default null
502 , p_per_information_category
503 in per_all_people_f.per_information_category%type default null
504 , p_per_information2
505 in per_all_people_f.per_information2%type default null
506 , p_per_information4
507 in per_all_people_f.per_information4%type default null
508 , p_per_information10
509 in per_all_people_f.per_information10%type default null
510 , p_email_address
511 in per_all_people_f.email_address%type default null
512 )
513 AS
514 ------------
515 -- Variables
516 ------------
517 l_proc varchar2(40) := g_package||'validate_applicant';
518 l_person_type_id per_all_people_f.person_type_id%type;
519
520 BEGIN
521 -- hr_utility.trace_on(null,'ZAUHK');
522 hr_utility.trace('validate person');
523 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
524 hr_utility.set_location(l_proc,1);
525
526 -- Check/Find person type id
527 --
528 per_per_bus.chk_person_type
529 ( p_person_type_id => l_person_type_id
530 , p_business_group_id => p_business_group_id
531 , p_expected_sys_type => 'APL'
532 );
533
534 hr_utility.set_location(l_proc,2);
535
536 -- Validate the person's race
537 --
538 validate_race
539 ( p_effective_date => p_date_received
540 , p_person_type_id => l_person_type_id
541 , p_race_code => p_per_information4
542 );
543
544 validate_email_id
545 ( p_email_id => p_email_address);
546
547 if p_per_information2 is not null and p_per_information2 <> hr_api.g_varchar2 then
548 -- bug 12914879
549 -- if length(p_per_information2)< 7 then
550 if length(p_per_information2)< 6 then
551 -- bug 12914879
552 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
553 fnd_message.set_token('FIELD', 'Passport Number');
554 -- bug 12914879
555 -- fnd_message.set_token('LENGTH', '7');
556 fnd_message.set_token('LENGTH', '6');
557 -- bug 12914879
558 fnd_message.set_token('UNITS', 'characters');
559 fnd_message.raise_error;
560 end if;
561
562 -- bug 13006122
563
564 if length(p_per_information2)> 13 then
565 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
566 fnd_message.set_token('FIELD', 'Passport Number');
567 fnd_message.set_token('LENGTH', '13');
568 fnd_message.set_token('UNITS', 'characters');
569 fnd_message.raise_error;
570 end if;
571
572 -- bug 13006122 ends
573
574 if validate_charcter_set(p_per_information2,'ALPHANUM')= FALSE then
575 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
576 fnd_message.set_token('FIELD', 'Passport Number');
577 fnd_message.raise_error;
578 end if;
579 end if;
580
581 -- Removing this validation, as it does not holds true for Nature of Person N
582 -- where country of passport issue is Optional
583 -- If Passport number is provided, then Country of Passport Issue
584 -- must also be provided
585 -- if p_per_information2 is not null and p_per_information10 is null then
586 -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
587 -- fnd_message.raise_error;
588 --end if ;
589
590 if p_per_information10 is not null and p_per_information10 <> hr_api.g_varchar2 then
591 if validate_charcter_set(p_per_information10,'ALPHA') = false then
592 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
593 fnd_message.set_token('FIELD', 'Country of Passport Issue');
594 fnd_message.raise_error;
595 end if;
596
597 if length(p_per_information10) > 3 then
598 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
599 fnd_message.set_token('FIELD', 'Country of Passport Issue');
600 fnd_message.set_token('LENGTH', '3');
601 fnd_message.set_token('UNITS', 'characters');
602 fnd_message.raise_error;
603 end if;
604 end if;
605
606 hr_utility.set_location(l_proc,3);
607 END IF;
608 END validate_applicant;
609 -------------------------------------------------------------------------------
610 -- validate_employee
611 -------------------------------------------------------------------------------
612 PROCEDURE validate_employee
613 ( p_business_group_id
614 in per_all_people_f.business_group_id%type default null
615 , p_hire_date
616 in date
617 , p_person_type_id
618 in per_all_people_f.person_type_id%type default null
619 , p_per_information_category
620 in per_all_people_f.per_information_category%type default null
621 , p_per_information2
622 in per_all_people_f.per_information2%type default null
623 , p_per_information4
624 in per_all_people_f.per_information4%type default null
625 , p_per_information10
626 in per_all_people_f.per_information10%type default null
627 , p_email_address
628 in per_all_people_f.email_address%type default null
629 , p_per_information12
630 in per_all_people_f.per_information12%type default null
631 , p_last_name
632 in per_all_people_f.last_name%type default null
633 , p_first_name
634 in per_all_people_f.first_name%type default null
635 , p_middle_names
636 in per_all_people_f.MIDDLE_NAMES%type default null
637 , p_employee_number
638 in per_all_people_f.EMPLOYEE_NUMBER%type default null
639 , p_national_identifier
640 in per_all_people_f.NATIONAL_IDENTIFIER%type default null
641 , p_per_information1
642 in per_all_people_f.per_information1%type default null
643 , p_date_of_birth
644 in per_all_people_f.date_of_birth%type default null
645 )
646 AS
647
648 CURSOR c_asg_nop
649 IS
650 select pei.AEI_INFORMATION4,papf.person_id
651 from PER_ASSIGNMENT_EXTRA_INFO pei, per_all_assignments_f paf, per_all_people_f papf
652 where pei.assignment_id = paf.assignment_id
653 and paf.person_id = papf.person_id
654 and papf.employee_number = p_employee_number
655 and paf.primary_flag = 'Y'
656 and
657 (
658 SELECT max (paaf1.effective_start_date)
659 FROM per_all_assignments_f paaf1
660 WHERE paaf1.assignment_id = paf.assignment_id
661 ) BETWEEN paf.effective_start_date AND paf.effective_end_date
662 and paf.effective_start_date BETWEEN papf.effective_start_date
663 AND papf.effective_end_date
664 and pei.information_type = 'ZA_SPECIFIC_INFO';
665
666 -- Bug#12674949
667 -- Cursor to obtain info if the employee num is generated
668 -- automatically for the business group
669
670 CURSOR c_org_info
671 IS
672 SELECT ORG_INFORMATION2
673 FROM HR_ORGANIZATION_INFORMATION
674 WHERE ORG_INFORMATION_CONTEXT = 'Business Group Information'
675 AND organization_id = p_business_group_id ;
676
677 -- Bug#12674949
678
679 -- rec_asg_nop c_asg_nop%rowtype;
680 ------------
681 -- Variables
682 ------------
683 l_proc varchar2(40) := g_package||'validate_employee';
684 l_person_type_id per_all_people_f.person_type_id%type;
685 l_asg_nop per_all_people_f.per_information12%type;
686 l_person_id per_all_people_f.person_id%type;
687 l_profile varchar2(1) := 'N';
688 l_auto_generated varchar2(10); -- Bug#12674949
689
690 BEGIN
691 -- hr_utility.trace_on(null,'ZAUHK');
692 -- hr_utility.trace('validate employee');
693 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
694 hr_utility.set_location(l_proc,1);
695
696 -- Check/Find person type id
697 --
698 per_per_bus.chk_person_type
699 ( p_person_type_id => l_person_type_id
700 , p_business_group_id => p_business_group_id
701 , p_expected_sys_type => 'EMP'
702 );
703
704 hr_utility.set_location(l_proc,2);
705
706 -- 11899934
707
708 -- Bug#12674949
709 -- Validation for emp num is skipped only if the emp_num is not passed
710 -- and emp_num is autogenerated for the business group
711
712 OPEN c_org_info;
713 FETCH c_org_info INTO l_auto_generated ;
714 CLOSE c_org_info;
715
716 if not(p_employee_number is null and l_auto_generated = 'A')then
717 -- Validate Employee_Number
718 validate_emp_num
719 ( p_emp_num => p_employee_number,
720 p_person_type_id => l_person_type_id -- Bug#12585688
721 );
722
723 end if;
724
725 -- Bug#12674949
726 -- 11899934
727
728 -- Validate the person's race
729 --
730 validate_race
731 ( p_effective_date => p_hire_date
732 , p_person_type_id => l_person_type_id
733 , p_race_code => p_per_information4
734 );
735
736 -- TYE2010
737 -- Validate email_address
738 validate_email_id
739 ( p_email_id => p_email_address);
740 /* The nature of person will be checked only when the profile is available. Hence removing the condition to check for 12.2 or 12.1.4 releases */
741 /* IF (fnd_release.major_version > 12 or
742 ((fnd_release.major_version = 12 and fnd_release.minor_version >= 2))
743 or
744 (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.point_version >= 4)) THEN */
745
746 -- Validate the person's nature of person
747
748 l_profile := fnd_profile.value('ZA_NOP_PERSON_LEVEL');
749
750 IF (( NVL(l_profile,'N') = 'Y') and p_per_information12 IS NULL) then
751 fnd_message.set_name('PER', 'HR_ZA_ENTER_NOP_PERSON_LEVEL');
752 fnd_message.raise_error;
753 end if;
754
755 -- Bug#12585688
756 -- if p_per_information12 is not null then
757
758 -- Modifying the check condition for Nature of person
759
760 if p_per_information12 is not null and p_per_information12 <> hr_api.g_varchar2 then
761
762 -- Bug#12585688
763
764 validate_nature
765 ( p_effective_date => p_hire_date
766 , p_nature => p_per_information12
767 , p_first_name => p_first_name
768 , p_middle_name => p_middle_names
769 , p_last_name => p_last_name
770 , p_empno => p_employee_number
771 , p_ni => p_national_identifier
772 , p_passport_no => p_per_information2
773 , p_country_passport => p_per_information10
774 , p_it_no_value => p_per_information1
775 , p_dob => p_date_of_birth
776 );
777
778
779 OPEN c_asg_nop;
780 FETCH c_asg_nop INTO l_asg_nop,l_person_id;
781 CLOSE c_asg_nop;
782
783
784 if l_asg_nop <> p_per_information12 then
785 update PER_ASSIGNMENT_EXTRA_INFO
786 set AEI_INFORMATION4 = p_per_information12
787 where assignment_id in (select paaf.assignment_id
788 from per_all_assignments_f paaf
789 where paaf.person_id = l_person_id
790 and paaf.primary_flag ='Y'
791 and
792 (
793 SELECT max (paaf1.effective_start_date)
794 FROM per_all_assignments_f paaf1
795 WHERE paaf1.assignment_id = paaf.assignment_id
796 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
797 and information_type = 'ZA_SPECIFIC_INFO';
798 end if;
799
800 end if;
801
802 -- END IF;
803
804 if p_per_information2 is not null and p_per_information2 <> hr_api.g_varchar2 then
805 -- bug 12914879
806 -- if length(p_per_information2)< 7 then
807 if length(p_per_information2)< 6 then
808 -- bug 12914879
809 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
810 fnd_message.set_token('FIELD', 'Passport Number');
811 -- bug 12914879
812 -- fnd_message.set_token('LENGTH', '7');
813 fnd_message.set_token('LENGTH', '6');
814 -- bug 12914879
815 fnd_message.set_token('UNITS', 'characters');
816 fnd_message.raise_error;
817 end if;
818
819 -- bug 13006122
820
821 if length(p_per_information2)> 13 then
822 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
823 fnd_message.set_token('FIELD', 'Passport Number');
824 fnd_message.set_token('LENGTH', '13');
825 fnd_message.set_token('UNITS', 'characters');
826 fnd_message.raise_error;
827 end if;
828
829 -- bug 13006122 ends
830
831
832 if validate_charcter_set(p_per_information2,'ALPHANUM')= FALSE then
833 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
834 fnd_message.set_token('FIELD', 'Passport Number');
835 fnd_message.raise_error;
836 end if;
837 end if;
838
839 -- Removing this validation, as it does not holds true for Nature of Person N
840 -- where country of passport issue is Optional
841 -- If Passport number is provided, then Country of Passport Issue
842 -- must also be provided
843 --if p_per_information2 is not null and p_per_information10 is null then
844 -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
845 -- fnd_message.raise_error;
846 --end if ;
847
848 if p_per_information10 is not null and p_per_information10 <> hr_api.g_varchar2 then
849 if validate_charcter_set(p_per_information10,'ALPHA') = false then
850 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
851 fnd_message.set_token('FIELD', 'Country of Passport Issue');
852 fnd_message.raise_error;
853 end if;
854
855 if length(p_per_information10) > 3 then
856 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
857 fnd_message.set_token('FIELD', 'Country of Passport Issue');
858 fnd_message.set_token('LENGTH', '3');
859 fnd_message.set_token('UNITS', 'characters');
860 fnd_message.raise_error;
861 end if;
862 end if;
863
864 hr_utility.set_location(l_proc,3);
865 END IF;
866 END validate_employee;
867 -------------------------------------------------------------------------------
868 -- validate_cwk
869 -------------------------------------------------------------------------------
870 PROCEDURE validate_cwk
871 ( p_business_group_id
872 in per_all_people_f.business_group_id%type default null
873 , p_start_date
874 in date
875 , p_person_type_id
876 in per_all_people_f.person_type_id%type default null
877 , p_per_information_category
878 in per_all_people_f.per_information_category%type default null
879 , p_per_information2
880 in per_all_people_f.per_information2%type default null
881 , p_per_information4
882 in per_all_people_f.per_information4%type default null
883 , p_per_information10
884 in per_all_people_f.per_information10%type default null
885 , p_email_address
886 in per_all_people_f.email_address%type default null
887 )
888 AS
889 ------------
890 -- Variables
891 ------------
892 l_proc varchar2(40) := g_package||'validate_cwk';
893 l_person_type_id per_all_people_f.person_type_id%type;
894
895 BEGIN
896 -- hr_utility.trace_on(null,'ZAUHK');
897 hr_utility.trace('validate person');
898 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
899 hr_utility.set_location(l_proc,1);
900
901 -- Check/Find person type id
902 --
903 per_per_bus.chk_person_type
904 ( p_person_type_id => l_person_type_id
905 , p_business_group_id => p_business_group_id
906 , p_expected_sys_type => 'CWK'
907 );
908
909 hr_utility.set_location(l_proc,2);
910
911 -- Validate the person's race
912 --
913 validate_race
914 ( p_effective_date => p_start_date
915 , p_person_type_id => l_person_type_id
916 , p_race_code => p_per_information4
917 );
918
919 -- TYE2010
920 -- Validate email_address
921 validate_email_id
922 ( p_email_id => p_email_address);
923
924 if p_per_information2 is not null and p_per_information2 <> hr_api.g_varchar2 then
925 -- bug 12914879
926 -- if length(p_per_information2)< 7 then
927 if length(p_per_information2)< 6 then
928 -- bug 12914879
929 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
930 fnd_message.set_token('FIELD', 'Passport Number');
931 -- bug 12914879
932 -- fnd_message.set_token('LENGTH', '7');
933 fnd_message.set_token('LENGTH', '6');
934 -- bug 12914879
935 fnd_message.set_token('UNITS', 'characters');
936 fnd_message.raise_error;
937 end if;
938
939 -- bug 13006122
940
941 if length(p_per_information2)> 13 then
942 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
943 fnd_message.set_token('FIELD', 'Passport Number');
944 fnd_message.set_token('LENGTH', '13');
945 fnd_message.set_token('UNITS', 'characters');
946 fnd_message.raise_error;
947 end if;
948
949 -- bug 13006122 ends
950
951
952 if validate_charcter_set(p_per_information2,'ALPHANUM')= FALSE then
953 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
954 fnd_message.set_token('FIELD', 'Passport Number');
955 fnd_message.raise_error;
956 end if;
957 end if;
958
959 -- Removing this validation, as it does not holds true for Nature of Person N
960 -- where country of passport issue is Optional
961 -- If Passport number is provided, then Country of Passport Issue
962 -- must also be provided
963 --if p_per_information2 is not null and p_per_information10 is null then
964 -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
965 -- fnd_message.raise_error;
966 --end if ;
967
968 if p_per_information10 is not null and p_per_information10 <> hr_api.g_varchar2 then
969 if validate_charcter_set(p_per_information10,'ALPHA') = false then
970 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
971 fnd_message.set_token('FIELD', 'Country of Passport Issue');
972 fnd_message.raise_error;
973 end if;
974
975 if length(p_per_information10) > 3 then
976 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
977 fnd_message.set_token('FIELD', 'Country of Passport Issue');
978 fnd_message.set_token('LENGTH', '3');
979 fnd_message.set_token('UNITS', 'characters');
980 fnd_message.raise_error;
981 end if;
982 end if;
983
984 hr_utility.set_location(l_proc,3);
985 END IF;
986 END validate_cwk;
987 -------------------------------------------------------------------------------
988 -- validate_person
989 -------------------------------------------------------------------------------
990 PROCEDURE validate_person
991 ( p_person_id
992 in per_all_people_f.person_id%type
993 , p_effective_date
994 in date
995 , p_person_type_id
996 in per_all_people_f.person_type_id%type
997 , p_per_information_category
998 in per_all_people_f.per_information_category%type
999 , p_per_information2
1000 in per_all_people_f.per_information2%type default null
1001 , p_per_information4
1002 in per_all_people_f.per_information4%type
1003 , p_per_information10
1004 in per_all_people_f.per_information10%type default null
1005 , p_email_address
1006 in per_all_people_f.email_address%type default null
1007 , p_per_information12
1008 in per_all_people_f.per_information12%type default null
1009 , p_last_name
1010 in per_all_people_f.last_name%type default null
1011 , p_first_name
1012 in per_all_people_f.first_name%type default null
1013 , p_middle_names
1014 in per_all_people_f.MIDDLE_NAMES%type default null
1015 , p_employee_number
1016 in per_all_people_f.EMPLOYEE_NUMBER%type default null
1017 , p_national_identifier
1018 in per_all_people_f.NATIONAL_IDENTIFIER%type default null
1019 , p_per_information1
1020 in per_all_people_f.per_information1%type default null
1021 , p_date_of_birth
1022 in per_all_people_f.date_of_birth%type default null
1023 )
1024 AS
1025 ---------
1026 -- Cursor
1027 ---------
1028 CURSOR c_person_person_type
1029 IS
1030 SELECT person_type_id
1031 FROM per_all_people_f
1032 WHERE person_id = p_person_id
1033 AND p_effective_date BETWEEN effective_start_date
1034 AND effective_end_date;
1035 CURSOR c_person_race
1036 IS
1037 SELECT per_information4
1038 FROM per_all_people_f
1039 WHERE person_id = p_person_id
1040 AND p_effective_date BETWEEN effective_start_date
1041 AND effective_end_date;
1042 CURSOR c_person_nop
1043 IS
1044 select per_information12
1045 from per_all_people_f
1046 where person_id = p_person_id
1047 and p_effective_date BETWEEN effective_start_date
1048 AND effective_end_date;
1049
1050 CURSOR c_asg_nop
1051 IS
1052 select AEI_INFORMATION4
1053 from PER_ASSIGNMENT_EXTRA_INFO
1054 where assignment_id in (select paaf.assignment_id
1055 from per_all_assignments_f paaf
1056 where paaf.person_id = p_person_id
1057 and paaf.primary_flag ='Y'
1058 and
1059 (
1060 SELECT max (paaf1.effective_start_date)
1061 FROM per_all_assignments_f paaf1
1062 WHERE paaf1.assignment_id = paaf.assignment_id
1063 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
1064 and information_type = 'ZA_SPECIFIC_INFO';
1065
1066 -- 11899934
1067
1068 CURSOR c_emp_num
1069 IS
1070 select EMPLOYEE_NUMBER
1071 from PER_ALL_PEOPLE_F
1072 where person_id = p_person_id;
1073
1074 -- 11899934
1075
1076 -- Bug#12674949
1077 -- Cursor to obtain info if the employee num is generated
1078 -- automatically for the business group
1079
1080 CURSOR c_org_info
1081 IS
1082 SELECT ORG_INFORMATION2
1083 FROM HR_ORGANIZATION_INFORMATION
1084 WHERE ORG_INFORMATION_CONTEXT = 'Business Group Information'
1085 AND ORGANIZATION_ID = (SELECT BUSINESS_GROUP_ID
1086 FROM PER_ALL_PEOPLE_F
1087 WHERE PERSON_ID = p_person_id
1088 AND p_effective_date BETWEEN effective_start_date
1089 AND effective_end_date); /* Bug 12674949 */
1090
1091 -- Bug#12674949
1092
1093 ------------
1094 -- Variables
1095 ------------
1096 l_proc varchar2(40) := g_package||'validate_person';
1097 l_person_type_id per_all_people_f.person_type_id%type;
1098 l_person_race per_all_people_f.per_information4%type;
1099 l_person_nop per_all_people_f.per_information12%type;
1100 l_alpha varchar2(52) := '- ,''.';
1101 l_invalid_char varchar2(1) := '~';
1102 l_asg_nop per_all_people_f.per_information12%type;
1103 l_profile varchar2(1) := 'N';
1104 l_emp_num varchar2(50); -- 11899934
1105 l_auto_generated varchar2(10); -- Bug#12674949
1106
1107 -- rec_nop_info c_person_nop%rowtype;
1108
1109 BEGIN
1110 -- hr_utility.trace_on(null,'ZAUHK');
1111 -- hr_utility.trace('validate person');
1112 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
1113 hr_utility.set_location(l_proc,1);
1114
1115 -- If the person type id was not passed
1116 --
1117 IF p_person_type_id = hr_api.g_number THEN
1118 hr_utility.set_location(l_proc,2);
1119 -- Find the person's person type
1120 --
1121 OPEN c_person_person_type;
1122 FETCH c_person_person_type INTO l_person_type_id;
1123 CLOSE c_person_person_type;
1124 ELSE
1125 hr_utility.set_location(l_proc,3);
1126 l_person_type_id := p_person_type_id;
1127 END IF;
1128
1129 -- if the race field was not updated
1130 --
1131 IF p_per_information4 = hr_api.g_varchar2 THEN
1132 hr_utility.set_location(l_proc,4);
1133 -- find the person's race
1134 --
1135 OPEN c_person_race;
1136 FETCH c_person_race INTO l_person_race;
1137 CLOSE c_person_race;
1138 ELSE
1139 hr_utility.set_location(l_proc,5);
1140 l_person_race := p_per_information4;
1141 END IF;
1142
1143 /* IF (fnd_release.major_version > 12 or
1144 ((fnd_release.major_version = 12 and fnd_release.minor_version >= 2))
1145 or
1146 (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.point_version >= 4)) THEN */
1147
1148
1149 -- if the nature of person field was not updated
1150 --
1151 IF p_per_information12 is null OR p_per_information12 = hr_api.g_varchar2 THEN
1152 hr_utility.set_location(l_proc,4);
1153 -- find the person's nature of person
1154 --
1155 OPEN c_person_nop;
1156 FETCH c_person_nop INTO l_person_nop;
1157 CLOSE c_person_nop;
1158 ELSE
1159 hr_utility.set_location(l_proc,5);
1160 l_person_nop := p_per_information12;
1161 END IF;
1162
1163 -- END IF;
1164
1165 -- 11899934
1166
1167 -- Validate Employee_Number
1168 if p_employee_number is null then
1169 OPEN c_emp_num;
1170 FETCH c_emp_num INTO l_emp_num;
1171 CLOSE c_emp_num;
1172 else
1173 l_emp_num := p_employee_number ;
1174 end if;
1175
1176 -- Bug#12674949
1177 -- Validation for emp num is skipped only if the emp_num is not passed
1178 -- and emp_num is autogenerated for the business group
1179
1180 OPEN c_org_info;
1181 FETCH c_org_info INTO l_auto_generated ;
1182 CLOSE c_org_info;
1183
1184 if not(l_emp_num is null and l_auto_generated = 'A')then
1185
1186 validate_emp_num
1187 ( p_emp_num => l_emp_num,
1188 p_person_type_id => l_person_type_id -- Bug#12585688
1189 );
1190
1191 end if;
1192
1193 -- Bug#12674949
1194
1195 -- 11899934
1196
1197
1198 -- Validate the person's race
1199 --
1200 validate_race
1201 ( p_effective_date => p_effective_date
1202 , p_person_type_id => l_person_type_id
1203 , p_race_code => l_person_race
1204 );
1205
1206 -- TYE2010
1207 -- Validate email_address
1208 validate_email_id
1209 ( p_email_id => p_email_address);
1210
1211 /* IF (fnd_release.major_version > 12 or
1212 ((fnd_release.major_version = 12 and fnd_release.minor_version >= 2))
1213 or
1214 (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.point_version >= 4)) THEN */
1215
1216 -- Validate the person's nature of person
1217 --
1218 l_profile := fnd_profile.value('ZA_NOP_PERSON_LEVEL');
1219 IF (( NVL(l_profile,'N') = 'Y') and l_person_nop IS NULL) then
1220 fnd_message.set_name('PER', 'HR_ZA_ENTER_NOP_PERSON_LEVEL');
1221 fnd_message.raise_error;
1222 end if;
1223
1224 -- Bug#12585688
1225 -- if p_per_information12 is not null then
1226
1227 -- Modifying the check condition for Nature of person
1228
1229 if l_person_nop is not null and l_person_nop <> hr_api.g_varchar2 then
1230 -- Bug#12585688
1231 validate_nature
1232 ( p_effective_date => p_effective_date
1233 , p_nature => l_person_nop
1234 , p_first_name => p_first_name
1235 , p_middle_name => p_middle_names
1236 , p_last_name => p_last_name
1237 , p_empno => p_employee_number
1238 , p_ni => p_national_identifier
1239 , p_passport_no => p_per_information2
1240 , p_country_passport => p_per_information10
1241 , p_it_no_value => p_per_information1
1242 , p_dob => p_date_of_birth
1243 );
1244
1245 OPEN c_asg_nop;
1246 FETCH c_asg_nop INTO l_asg_nop;
1247 CLOSE c_asg_nop;
1248
1249 if l_asg_nop <> l_person_nop then
1250 update PER_ASSIGNMENT_EXTRA_INFO
1251 set AEI_INFORMATION4 = l_person_nop
1252 where assignment_id in (select paaf.assignment_id
1253 from per_all_assignments_f paaf
1254 where paaf.person_id = p_person_id
1255 and paaf.primary_flag ='Y'
1256 and
1257 (
1258 SELECT max (paaf1.effective_start_date)
1259 FROM per_all_assignments_f paaf1
1260 WHERE paaf1.assignment_id = paaf.assignment_id
1261 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
1262 and information_type = 'ZA_SPECIFIC_INFO';
1263 end if;
1264
1265 end if;
1266
1267 -- END IF;
1268
1269 hr_utility.set_location(l_proc,6);
1270
1271 if p_per_information2 is not null and p_per_information2 <> hr_api.g_varchar2 then
1272 -- bug 12914879
1273 -- if length(p_per_information2)< 7 then
1274 if length(p_per_information2)< 6 then
1275 -- bug 12914879
1276 fnd_message.set_name('PAY', 'PY_ZA_INVALID_MIN_LENGTH');
1277 fnd_message.set_token('FIELD', 'Passport Number');
1278 -- bug 12914879
1279 -- fnd_message.set_token('LENGTH', '7');
1280 fnd_message.set_token('LENGTH', '6');
1281 -- bug 12914879
1282 fnd_message.set_token('UNITS', 'characters');
1283 fnd_message.raise_error;
1284 end if;
1285
1286 -- bug 13006122
1287
1288 if length(p_per_information2)> 13 then
1289 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
1290 fnd_message.set_token('FIELD', 'Passport Number');
1291 fnd_message.set_token('LENGTH', '13');
1292 fnd_message.set_token('UNITS', 'characters');
1293 fnd_message.raise_error;
1294 end if;
1295
1296 -- bug 13006122 ends
1297
1298 if validate_charcter_set(p_per_information2,'ALPHANUM')= FALSE then
1299 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1300 fnd_message.set_token('FIELD', 'Passport Number');
1301 fnd_message.raise_error;
1302 end if;
1303 end if;
1304
1305 -- Removing this validation, as it does not holds true for Nature of Person N
1306 -- where country of passport issue is Optional
1307 -- If Passport number is provided, then Country of Passport Issue
1308 -- must also be provided
1309 --if p_per_information2 is not null and p_per_information10 is null then
1310 -- fnd_message.set_name('PAY', 'PY_ZA_ENTER_PASS_COUNTRY_ISSUE');
1311 -- fnd_message.raise_error;
1312 --end if ;
1313
1314 if p_per_information10 is not null and p_per_information10 <> hr_api.g_varchar2 then
1315 if validate_charcter_set(p_per_information10,'ALPHA') = false then
1316 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1317 fnd_message.set_token('FIELD', 'Country of Passport Issue');
1318 fnd_message.raise_error;
1319 end if;
1320
1321 if length(p_per_information10) > 3 then
1322 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
1323 fnd_message.set_token('FIELD', 'Country of Passport Issue');
1324 fnd_message.set_token('LENGTH', '3');
1325 fnd_message.set_token('UNITS', 'characters');
1326 fnd_message.raise_error;
1327 end if;
1328 end if;
1329
1330 hr_utility.set_location(l_proc,6);
1331 END IF;
1332
1333 END validate_person;
1334
1335 -------------------------------------------------------------------------------
1336 -- validate_asg_extra_info
1337 -------------------------------------------------------------------------------
1338 -- Description:
1339 -- Validates Assignment Extra Information for South Africa.
1340 -- Called from:
1341 -- CREATE_ASSIGNMENT_EXTRA_INFO and UPDATE_ASSIGNMENT_EXTRA_INFO APIs
1342 -- Assignment EIT segments used :
1343 -- SEGMENT NAME
1344 -- ------- ----
1345 -- AEI_ATTRIBUTE2 Employee Trade Name
1346 -- AEI_INFORMATION4 Nature of Person
1347 -- AEI_ATTRIBUTE13 Payment Type
1348 -- AEI_INFORMATION14 SARS Reporting Account Number
1349 ---------------------------------------------------------------------------
1350 -- --
1351 ---------------------------------------------------------------------------
1352 procedure validate_asg_extra_info (P_AEI_INFORMATION_CATEGORY in VARCHAR2
1353 , P_AEI_INFORMATION2 in VARCHAR2
1354 , P_AEI_INFORMATION4 in VARCHAR2
1355 , P_AEI_INFORMATION13 in VARCHAR2
1356 , P_AEI_INFORMATION14 in VARCHAR2
1357 ) as
1358
1359 begin
1360
1361 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
1362 IF P_AEI_INFORMATION_CATEGORY = 'ZA_SPECIFIC_INFO' THEN
1363 -- Employee Trade Name id mandatory if nature of person
1364 -- is in (D,E,F,G,H)
1365 IF P_AEI_INFORMATION4 in ('04','05','06','07','08') THEN
1366 IF P_AEI_INFORMATION2 is null THEN
1367 fnd_message.set_name('PAY', 'PY_ZA_ENTER_NAT_DEFGH_TRADE');
1368 fnd_message.raise_error;
1369 END IF;
1370 END IF;
1371
1372 IF validate_charcter_set(P_AEI_INFORMATION2,'FREETEXT') = false then
1373 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1374 fnd_message.set_token('FIELD', 'Employee Trading Name');
1375 fnd_message.raise_error;
1376 END IF;
1377 -- SARS Reporting Account Name is mandatory if Payment Type is
1378 -- 'Internal Account Transfer'
1379 IF P_AEI_INFORMATION13 = '1' THEN
1380 IF P_AEI_INFORMATION14 is null THEN
1381 fnd_message.set_name('PAY', 'PY_ZA_ENTER_REP_ACC_NO');
1382 fnd_message.raise_error;
1383 END IF;
1384 END IF;
1385
1386 END IF;
1387 END IF;
1388 end validate_asg_extra_info;
1389
1390 procedure create_za_assign_extra_info(P_ASSIGNMENT_ID in number
1391 ,P_AEI_INFORMATION_CATEGORY in VARCHAR2
1392 , P_AEI_INFORMATION2 in VARCHAR2
1393 , P_AEI_INFORMATION4 in VARCHAR2
1394 , P_AEI_INFORMATION13 in VARCHAR2
1395 , P_AEI_INFORMATION14 in VARCHAR2) is
1396
1397
1398 l_person_nop varchar2(10);
1399 l_asg_primary_flag varchar2(10);
1400 begin
1401
1402
1403 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
1404 /* and (fnd_release.major_version > 12 or
1405 ((fnd_release.major_version = 12 and fnd_release.minor_version >= 2))
1406 or
1407 (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.point_version >= 4)) THEN */
1408
1409 IF P_AEI_INFORMATION_CATEGORY = 'ZA_SPECIFIC_INFO' THEN
1410
1411 PER_ZA_USER_HOOK_PKG.validate_asg_extra_info(P_AEI_INFORMATION_CATEGORY => P_AEI_INFORMATION_CATEGORY
1412 ,P_AEI_INFORMATION2 => P_AEI_INFORMATION2
1413 ,P_AEI_INFORMATION4 => P_AEI_INFORMATION4
1414 ,P_AEI_INFORMATION13 => P_AEI_INFORMATION13
1415 ,P_AEI_INFORMATION14 => P_AEI_INFORMATION14 );
1416
1417
1418 /* added for ZA Nature of person */
1419
1420 BEGIN
1421 SELECT paaf.primary_flag
1422 INTO l_asg_primary_flag
1423 FROM per_all_assignments_f paaf
1424 WHERE paaf.assignment_id = P_ASSIGNMENT_ID
1425 AND
1426 (
1427 SELECT max (paaf1.effective_start_date)
1428 FROM per_all_assignments_f paaf1
1429 WHERE paaf1.assignment_id = paaf.assignment_id
1430 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
1431
1432 if l_asg_primary_flag = 'Y' then
1433
1434 SELECT per_information12
1435 INTO l_person_nop
1436 FROM per_all_people_f paf
1437 ,per_all_assignments_f paaf
1438 WHERE paaf.assignment_id = P_ASSIGNMENT_ID
1439 AND paaf.person_id = paf.person_id
1440 AND paaf.primary_flag = 'Y'
1441 AND
1442 (
1443 SELECT max (paaf1.effective_start_date)
1444 FROM per_all_assignments_f paaf1
1445 WHERE paaf1.assignment_id = paaf.assignment_id
1446 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1447 AND paaf.effective_start_date BETWEEN paf.effective_start_date
1448 AND paf.effective_end_date;
1449
1450 if l_person_nop is not NULL and P_AEI_INFORMATION4 <> l_person_nop then
1451 fnd_message.set_name('PER', 'HR_ZA_UPDATE_NOP_PERSON_LEVEL');
1452 fnd_message.raise_error;
1453 end if;
1454 end if;
1455
1456 EXCEPTION
1457 WHEN NO_DATA_FOUND THEN
1458 null; -- can be ignored this and proceed to normal processing
1459 END;
1460
1461 /* Nature of person ends */
1462
1463 END IF;
1464 END IF;
1465 end create_za_assign_extra_info;
1466
1467 procedure update_za_assign_extra_info(p_assignment_extra_info_id in number
1468 ,P_AEI_INFORMATION_CATEGORY in VARCHAR2
1469 , P_AEI_INFORMATION2 in VARCHAR2
1470 , P_AEI_INFORMATION4 in VARCHAR2
1471 , P_AEI_INFORMATION13 in VARCHAR2
1472 , P_AEI_INFORMATION14 in VARCHAR2) is
1473
1474
1475 l_person_nop varchar2(10);
1476 l_asg_primary_flag varchar2(10);
1477 begin
1478
1479
1480 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
1481 /* and (fnd_release.major_version > 12 or
1482 ((fnd_release.major_version = 12 and fnd_release.minor_version >= 2))
1483 or
1484 (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.point_version >= 4)) THEN */
1485
1486 IF P_AEI_INFORMATION_CATEGORY = 'ZA_SPECIFIC_INFO' THEN
1487
1488 PER_ZA_USER_HOOK_PKG.validate_asg_extra_info(P_AEI_INFORMATION_CATEGORY => P_AEI_INFORMATION_CATEGORY
1489 ,P_AEI_INFORMATION2 => P_AEI_INFORMATION2
1490 ,P_AEI_INFORMATION4 => P_AEI_INFORMATION4
1491 ,P_AEI_INFORMATION13 => P_AEI_INFORMATION13
1492 ,P_AEI_INFORMATION14 => P_AEI_INFORMATION14);
1493
1494
1495 /* added for ZA Nature of person */
1496
1497 BEGIN
1498 SELECT paaf.primary_flag
1499 INTO l_asg_primary_flag
1500 FROM per_all_assignments_f paaf
1501 ,per_assignment_extra_info pai
1502 WHERE pai.assignment_extra_info_id = P_ASSIGNMENT_EXTRA_INFO_ID
1503 AND pai.assignment_id = paaf.assignment_id
1504 AND
1505 (
1506 SELECT max (paaf1.effective_start_date)
1507 FROM per_all_assignments_f paaf1
1508 WHERE paaf1.assignment_id = paaf.assignment_id
1509 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
1510
1511 if l_asg_primary_flag = 'Y' then
1512 SELECT paf.per_information12
1513 INTO l_person_nop
1514 FROM per_all_people_f paf
1515 ,per_all_assignments_f paaf
1516 ,per_assignment_extra_info pai
1517 WHERE pai.assignment_extra_info_id = P_ASSIGNMENT_EXTRA_INFO_ID
1518 AND pai.assignment_id = paaf.assignment_id
1519 AND paf.person_id = paaf.person_id
1520 AND
1521 (
1522 SELECT max (paaf1.effective_start_date)
1523 FROM per_all_assignments_f paaf1
1524 WHERE paaf1.assignment_id = paaf.assignment_id
1525 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1526 AND paaf.effective_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
1527
1528 if l_person_nop is not NULL and P_AEI_INFORMATION4 <> l_person_nop then
1529 fnd_message.set_name('PER', 'HR_ZA_UPDATE_NOP_PERSON_LEVEL');
1530 fnd_message.raise_error;
1531 end if;
1532 end if;
1533
1534 EXCEPTION
1535 WHEN NO_DATA_FOUND THEN
1536 null; -- can be ignored this and proceed to normal processing
1537 END;
1538
1539 /* Nature of person end */
1540
1541 END IF;
1542 END IF;
1543 end update_za_assign_extra_info;
1544
1545
1546 -- Generic procedure to be called from
1547 -- validate_person_address and validate_location_address
1548 procedure validate_address (P_STYLE in varchar2
1549 ,P_ADDRESS_TYPE in varchar2 default null
1550 ,P_PRIMARY_FLAG in varchar2 default null
1551 ,P_UNIT_NUMBER in varchar2 default null
1552 ,P_COMPLEX in varchar2 default null
1553 ,P_STREET_NUMBER in varchar2 default null
1554 ,P_STREET_NAME in varchar2 default null
1555 ,P_SUBURB_DISTRICT in varchar2 default null
1556 ,P_TOWN_OR_CITY in varchar2 default null
1557 ,P_POSTAL_CODE in varchar2 default null
1558 ,P_SAME_AS_RES_ADD in varchar2 default null
1559 ,P_ADDRESS_LINE1 in varchar2 default null
1560 ,P_ADDRESS_LINE2 in varchar2 default null
1561 ,P_ADDRESS_LINE3 in varchar2 default null) as
1562 begin
1563 -- Primary address must be with 'South Africa' address style
1564 -- This will be assumed and reported as Postal Address of the person
1565 if P_PRIMARY_FLAG = 'Y' and p_style <> 'ZA' then
1566 fnd_message.set_name('PER','HR_ZA_PRIM_ADD_STYLE');
1567 fnd_message.raise_error;
1568 end if;
1569
1570 if p_style in('ZA_SARS','ZA_GRE') then
1571 -- ZA_SARS means Business / Residential address
1572 -- ZA_GRE means Legal Entity address
1573 hr_utility.trace('Address Type='||P_ADDRESS_TYPE);
1574 hr_utility.trace('Primary Flag='||P_PRIMARY_FLAG);
1575 hr_utility.trace('Unit Number='||P_UNIT_NUMBER);
1576 hr_utility.trace('Complex='||P_COMPLEX);
1577 hr_utility.trace('Street Number='||P_STREET_NUMBER);
1578 hr_utility.trace('Street or Name of Farm='||P_STREET_NAME);
1579 hr_utility.trace('Suburb/District='||P_SUBURB_DISTRICT);
1580 hr_utility.trace('City/Town='||P_TOWN_OR_CITY);
1581 hr_utility.trace('Postal Code='||P_POSTAL_CODE);
1582 hr_utility.trace('Same as Res Address flag='||P_SAME_AS_RES_ADD);
1583
1584 if P_SUBURB_DISTRICT is null and P_TOWN_OR_CITY is null then
1585 fnd_message.set_name('PER', 'HR_ZA_ENTER_DISTRICT_OR_TOWN');
1586 if p_style in('ZA_SARS') then
1587 fnd_message.set_token('LOCATION', 'Person Address');
1588 else
1589 fnd_message.set_token('LOCATION', 'Employer Address');
1590 end if;
1591 fnd_message.raise_error;
1592 end if ;
1593
1594 if validate_charcter_set(P_UNIT_NUMBER,'ALPHANUM') = false then
1595 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1596 fnd_message.set_token('FIELD', 'Unit Number');
1597 fnd_message.raise_error;
1598 elsif validate_charcter_set(P_COMPLEX,'FREETEXT') = false then
1599 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1600 fnd_message.set_token('FIELD', 'Complex');
1601 fnd_message.raise_error;
1602 elsif validate_charcter_set(P_STREET_NUMBER,'ALPHANUM') = false then
1603 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1604 fnd_message.set_token('FIELD', 'Street Number');
1605 fnd_message.raise_error;
1606 elsif validate_charcter_set(P_STREET_NAME,'FREETEXT') = false then
1607 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1608 fnd_message.set_token('FIELD', 'Street or Name of Farm');
1609 fnd_message.raise_error;
1610 elsif validate_charcter_set(P_SUBURB_DISTRICT,'FREETEXT') = false then
1611 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1612 fnd_message.set_token('FIELD', 'Suburb or District');
1613 fnd_message.raise_error;
1614 elsif validate_charcter_set(P_TOWN_OR_CITY,'FREETEXT') = false then
1615 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1616 fnd_message.set_token('FIELD', 'City or Town');
1617 fnd_message.raise_error;
1618 elsif validate_charcter_set(P_POSTAL_CODE,'ALPHANUM') = false then
1619 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1620 fnd_message.set_token('FIELD', 'Postal Code');
1621 fnd_message.raise_error;
1622 end if ;
1623
1624 if p_style = 'ZA_GRE' then
1625 if length(P_POSTAL_CODE)<> 4 then
1626 fnd_message.set_name('PER', 'HR_ZA_INVALID_LENGTH');
1627 fnd_message.set_token('FIELD', 'Postal Code');
1628 fnd_message.set_token('LENGTH', '4');
1629 fnd_message.set_token('UNITS', 'digits');
1630 fnd_message.raise_error;
1631 end if;
1632 end if;
1633 elsif p_style = 'ZA' then
1634 -- Postal Address
1635 if P_SAME_AS_RES_ADD is null or P_SAME_AS_RES_ADD = 'N' then
1636 if P_ADDRESS_LINE1 is null then
1637 fnd_message.set_name('PER', 'HR_ZA_NEW_ENTER_ADDRESS_LINE1');
1638 fnd_message.raise_error;
1639 end if;
1640 if P_POSTAL_CODE is null then
1641 fnd_message.set_name('PER', 'HR_ZA_NEW_ENTER_POSTAL_CODE');
1642 fnd_message.set_token('LOCATION', 'Postal Address');
1643 fnd_message.raise_error;
1644 end if;
1645 end if ;
1646
1647 if validate_charcter_set(P_ADDRESS_LINE1,'FREETEXT') = false then
1648 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1649 fnd_message.set_token('FIELD', 'Address Line 1');
1650 fnd_message.raise_error;
1651 elsif validate_charcter_set(P_ADDRESS_LINE2,'FREETEXT') = false then
1652 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1653 fnd_message.set_token('FIELD', 'Address Line 2');
1654 fnd_message.raise_error;
1655 elsif validate_charcter_set(P_ADDRESS_LINE3,'FREETEXT') = false then
1656 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1657 fnd_message.set_token('FIELD', 'Address Line 3');
1658 fnd_message.raise_error;
1659 elsif validate_charcter_set(P_POSTAL_CODE,'ALPHANUM') = false then
1660 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1661 fnd_message.set_token('FIELD', 'Postal Code');
1662 fnd_message.raise_error;
1663 end if;
1664 end if;
1665 end validate_address;
1666
1667 -------------------------------------------------------------------------------
1668 -- validate_person_address
1669 -------------------------------------------------------------------------------
1670 -- Description:
1671 -- Validates Personal address for Address Style 'South Africa'.
1672 -- Called from:
1673 -- CREATE_PERSON_ADDRESS and UPDATE_PERSON_ADDRESS APIs
1674 -- Segments used :
1675 -- AddressStyle = ZA
1676 -- SEGMENT NAME
1677 -- ------- ----
1678 -- ADDRESS_LINE1 Unit Number
1679 -- ADDRESS_LINE2 Complex
1680 -- ADDRESS_LINE3 Street Number
1681 -- REGION1 Street or Name of Farm
1682 -- REGION2 Suburb/District
1683 -- TOWN_OR_CITY City/Town
1684 -- POSTAL_CODE Postal Code
1685 -- AddressStyle = ZA_POS
1686 -- SEGMENT NAME
1687 -- ------- ----
1688 -- REGION_1 Postal Address same as Residential Address
1689 -- ADDRESS_LINE1 Address Line1
1690 -- ADDRESS_LINE2 Address Line2
1691 -- ADDRESS_LINE3 Address Line3
1692 -- POSTAL_CODE Postal Code
1693 --
1694 ---------------------------------------------------------------------------
1695 -- --
1696 ---------------------------------------------------------------------------
1697 procedure validate_person_address (P_STYLE in varchar2
1698 ,P_ADDRESS_TYPE in varchar2
1699 ,P_PRIMARY_FLAG in varchar2
1700 ,P_ADDRESS_LINE1 in varchar2
1701 ,P_ADDRESS_LINE2 in varchar2
1702 ,P_ADDRESS_LINE3 in varchar2
1703 ,P_TELEPHONE_NUMBER_1 in varchar2
1704 ,P_REGION_1 in varchar2
1705 ,P_REGION_2 in varchar2
1706 ,P_TOWN_OR_CITY in varchar2
1707 ,P_POSTAL_CODE in varchar2) as
1708 begin
1709 if p_style = 'ZA_SARS' then
1710 -- Residential or Business Address
1711 validate_address (P_STYLE => P_STYLE
1712 ,P_ADDRESS_TYPE => P_ADDRESS_TYPE
1713 ,P_UNIT_NUMBER => P_ADDRESS_LINE1
1714 ,P_COMPLEX => P_ADDRESS_LINE2
1715 ,P_STREET_NUMBER => P_ADDRESS_LINE3
1716 ,P_STREET_NAME => P_REGION_1
1717 ,P_SUBURB_DISTRICT => P_REGION_2
1718 ,P_TOWN_OR_CITY => P_TOWN_OR_CITY
1719 ,P_POSTAL_CODE => P_POSTAL_CODE
1720 ,P_PRIMARY_FLAG => P_PRIMARY_FLAG
1721 );
1722 elsif p_style = 'ZA' then
1723 -- Postal Address
1724 validate_address (P_STYLE => P_STYLE
1725 ,P_ADDRESS_TYPE => P_ADDRESS_TYPE
1726 ,P_PRIMARY_FLAG => P_PRIMARY_FLAG
1727 ,P_SAME_AS_RES_ADD => P_REGION_2
1728 ,P_ADDRESS_LINE1 => P_ADDRESS_LINE1
1729 ,P_ADDRESS_LINE2 => P_ADDRESS_LINE2
1730 ,P_ADDRESS_LINE3 => P_ADDRESS_LINE3
1731 ,P_POSTAL_CODE => P_POSTAL_CODE
1732 );
1733 end if;
1734 end validate_person_address;
1735
1736 -------------------------------------------------------------------------------
1737 -- validate_location_extra_info
1738 -------------------------------------------------------------------------------
1739 -- Description:
1740 -- Validates Location EIT address for Address Style 'South Africa - SARS'.
1741 -- Called from:
1742 -- CREATE_LOCATION_EXTRA_INFO and UPDATE_LOCATION_EXTRA_INFO APIs
1743 --
1744 -- AddressStyle = ZA_SARS
1745 -- AddressType = ZA_BUS
1746 -- PrimaryFlag= 'N'
1747 -- P_LEI_ATTRIBUTE_CATEGORY = ZA_SARS_ADDRESS
1748 --
1749 -- Segments used :
1750 --
1751 -- SEGMENT NAME
1752 -- ------- ----
1753 -- P_LEI_INFORMATION1 Unit Number
1754 -- P_LEI_INFORMATION2 Complex
1755 -- P_LEI_INFORMATION3 Street Number
1756 -- P_LEI_INFORMATION4 Street or Name of Farm
1757 -- P_LEI_INFORMATION5 Suburb/District
1758 -- P_LEI_INFORMATION6 City/Town
1759 -- P_LEI_INFORMATION7 Postal Code
1760 --
1761 ---------------------------------------------------------------------------
1762 -- --
1763 ---------------------------------------------------------------------------
1764 procedure validate_location_extra_info (
1765 P_LEI_INFORMATION_CATEGORY in varchar2,
1766 P_LEI_INFORMATION1 in varchar2,
1767 P_LEI_INFORMATION2 in varchar2,
1768 P_LEI_INFORMATION3 in varchar2,
1769 P_LEI_INFORMATION4 in varchar2,
1770 P_LEI_INFORMATION5 in varchar2,
1771 P_LEI_INFORMATION6 in varchar2,
1772 P_LEI_INFORMATION7 in varchar2) as
1773 begin
1774 if P_LEI_INFORMATION_CATEGORY = 'ZA_SARS_ADDRESS' then
1775 validate_address (P_STYLE => 'ZA_SARS'
1776 ,P_ADDRESS_TYPE => 'ZA_BUS'
1777 ,P_UNIT_NUMBER => P_LEI_INFORMATION1
1778 ,P_COMPLEX => P_LEI_INFORMATION2
1779 ,P_STREET_NUMBER => P_LEI_INFORMATION3
1780 ,P_STREET_NAME => P_LEI_INFORMATION4
1781 ,P_SUBURB_DISTRICT => P_LEI_INFORMATION5
1782 ,P_TOWN_OR_CITY => P_LEI_INFORMATION6
1783 ,P_POSTAL_CODE => P_LEI_INFORMATION7
1784 ,P_PRIMARY_FLAG => 'N'
1785 );
1786 end if;
1787 end validate_location_extra_info;
1788 -------------------------------------------------------------------------------
1789 -- validate_org_info
1790 -------------------------------------------------------------------------------
1791 -- Description:
1792 -- Validate Organization Information
1793 -- Called from:
1794 -- CREATE_ORG_INFORMATION and UPDATE_ORG_INFORMATION
1795 --
1796 ---------------------------------------------------------------------------
1797 -- --
1798 ---------------------------------------------------------------------------
1799 procedure validate_org_info (p_org_info_type_code IN VARCHAR2
1800 ,p_org_information1 IN VARCHAR2
1801 ,p_org_information2 IN VARCHAR2
1802 ,p_org_information3 IN VARCHAR2
1803 ,p_org_information4 IN VARCHAR2
1804 ,p_org_information5 IN VARCHAR2
1805 ,p_org_information6 IN VARCHAR2
1806 ,p_org_information7 IN VARCHAR2
1807 ,p_org_information8 IN VARCHAR2
1808 ,p_org_information9 IN VARCHAR2
1809 ,p_org_information10 IN VARCHAR2
1810 ,p_org_information11 IN VARCHAR2
1811 ,p_org_information12 IN VARCHAR2
1812 ,p_org_information13 IN VARCHAR2
1813 ,p_org_information14 IN VARCHAR2 -- 9864910 fix
1814 ,p_org_information15 IN VARCHAR2 -- 9864910 fix
1815 ,p_org_information16 IN VARCHAR2 -- 9864910 fix
1816 ,p_org_information17 IN VARCHAR2 -- 9815318 fix
1817 )as
1818 l_sdl_num hr_organization_information.org_information12%type; -- 9864910 fix
1819 l_uif_num hr_organization_information.org_information6%type; -- 9864910 fix
1820 begin
1821 -- Org Info Type : ZA_GRE_TAX_FILE_ENTITY
1822 if p_org_info_type_code = 'ZA_GRE_TAX_FILE_ENTITY' then
1823 if validate_charcter_set(p_org_information1,'ALPHA') = false then
1824 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1825 fnd_message.set_token('FIELD', 'Contact Person');
1826 fnd_message.raise_error;
1827 end if;
1828
1829 validate_phone_no(p_phone_type => 'GRE'
1830 , p_phone_number => p_org_information2);
1831
1832 validate_email_id (p_email_id => p_org_information3);
1833
1834 validate_address (P_STYLE => 'ZA_GRE'
1835 ,P_PRIMARY_FLAG => null
1836 ,P_UNIT_NUMBER => p_org_information4
1837 ,P_COMPLEX => p_org_information5
1838 ,P_STREET_NUMBER => p_org_information6
1839 ,P_STREET_NAME => p_org_information7
1840 ,P_SUBURB_DISTRICT => p_org_information8
1841 ,P_TOWN_OR_CITY => p_org_information9
1842 ,P_POSTAL_CODE => p_org_information10
1843 );
1844 elsif p_org_info_type_code = 'ZA_LEGAL_ENTITY' then
1845 if validate_charcter_set(p_org_information1,'FREETEXT') = false then
1846 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1847 fnd_message.set_token('FIELD', 'Company Trading or Other Name');
1848 fnd_message.raise_error;
1849 end if;
1850
1851 if validate_charcter_set(p_org_information13,'NUMERIC') = false then
1852 fnd_message.set_name('PER', 'HR_ZA_INVALID_CHAR');
1853 fnd_message.set_token('FIELD', 'Trade Classification');
1854 fnd_message.raise_error;
1855 end if;
1856
1857 if length(p_org_information13) > 4 then
1858 fnd_message.set_name('PER', 'HR_ZA_INVALID_MAX_LENGTH');
1859 fnd_message.set_token('FIELD', 'Trade Classification');
1860 fnd_message.set_token('LENGTH', '4');
1861 fnd_message.set_token('UNITS', 'digits');
1862 fnd_message.raise_error;
1863 end if;
1864
1865 -- 9864910 fix starts
1866 if p_org_information15 = 'Y' and p_org_information12 is null then
1867 fnd_message.set_name('PER', 'HR_ZA_INPUT_REQUIRED');
1868 fnd_message.set_token('FIELD', 'SDL Number');
1869 fnd_message.raise_error;
1870 end if;
1871
1872 if p_org_information16 = 'Y' and p_org_information6 is null then
1873 fnd_message.set_name('PER', 'HR_ZA_INPUT_REQUIRED');
1874 fnd_message.set_token('FIELD', 'UIF Number');
1875 fnd_message.raise_error;
1876 end if;
1877
1878 -- 9815318 fix start
1879 if p_org_information16 = 'Y' and p_org_information17 is null then
1880 fnd_message.set_name('PER', 'HR_ZA_INPUT_REQUIRED');
1881 fnd_message.set_token('FIELD', 'UIF Number (UIF File)');
1882 fnd_message.raise_error;
1883 end if;
1884
1885 -- 9815318 fix end
1886
1887 if p_org_information12 is not null then
1888 l_sdl_num := UPPER(p_org_information12);
1889 if substr(l_sdl_num,1,1) <> 'L' OR length(l_sdl_num) <> 10 then
1890 fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
1891 fnd_message.set_token('FIELD','SDL Number');
1892 fnd_message.set_token('CHAR1','L');
1893 fnd_message.raise_error;
1894 end if;
1895 if substr(l_sdl_num,2,9) <> substr(p_org_information3,2,9)
1896 AND substr(p_org_information3,1,1) = '7' then
1897 fnd_message.set_name('PAY', 'PY_ZA_INVALID_LAST_NINE_CHAR');
1898 fnd_message.set_token('FIELD','SDL Number');
1899 fnd_message.raise_error;
1900 end if;
1901 if pay_za_eoy_val.modulus_10_test(l_sdl_num,'S') =0 then
1902 fnd_message.set_name('PAY', 'PY_ZA_INVALID_SDL_NO');
1903 fnd_message.raise_error;
1904 end if;
1905 end if;
1906
1907 if p_org_information6 is not null then
1908 l_uif_num := UPPER(p_org_information6);
1909 l_uif_num := translate(l_uif_num,
1910 'U0123456789ABCDEFGHIJKLMNOPQRSTVWXYZ- "\/?@&$!#+=;:,''().',
1911 'U0123456789');
1912 if substr(l_uif_num,1,1) <> 'U' OR length(l_uif_num) <> 10 then
1913 fnd_message.set_name('PAY', 'PY_ZA_INVALID_FIRST_CHAR');
1914 fnd_message.set_token('FIELD','UIF Number');
1915 fnd_message.set_token('CHAR1','U');
1916 fnd_message.raise_error;
1917 end if;
1918 if substr(l_uif_num,2,9) <> substr(l_uif_num,2,9)
1919 AND substr(p_org_information3,1,1) = '7' then
1920 fnd_message.set_name('PAY', 'PY_ZA_INVALID_LAST_NINE_CHAR');
1921 fnd_message.set_token('FIELD','UIF Number');
1922 fnd_message.raise_error;
1923 end if;
1924 if pay_za_eoy_val.modulus_10_test(l_uif_num,'U') =0 then
1925 fnd_message.set_name('PAY', 'PY_ZA_INVALID_UIF_NO');
1926 fnd_message.raise_error;
1927 end if;
1928 end if;
1929 -- 9864910 fix ends
1930
1931 end if;
1932 end validate_org_info;
1933
1934 -------------------------------------------------------------------------------
1935 -- validate_charcter_set
1936 -------------------------------------------------------------------------------
1937 -- Description:
1938 -- Validates Character Sets for South Africa.
1939 --
1940 ---------------------------------------------------------------------------
1941 -- --
1942 ---------------------------------------------------------------------------
1943 function validate_charcter_set (p_input_value in varchar2
1944 , p_mode in varchar2 ) return boolean as
1945 /*
1946 l_invalid_char constant varchar2(1) := '~';
1947 l_root_alpha varchar2(52) := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
1948 l_alpha varchar2(60) := l_root_alpha || '- ,''';
1949 l_alphanum varchar2(70) := l_root_alpha || l_num || '- ,''';
1950 l_freetext varchar2(90) := l_root_alpha || l_num || ' -"\/?@&$!#+=;:,''().';
1951 */
1952 l_result boolean := true ;
1953 l_translated varchar2(1024);
1954 l_num varchar2(10) := '0123456789' ;
1955
1956 l_invalid_in_alphanum varchar2(100):= '~`!@#$%^&*()_+=|\[]{}<>":;?/.';
1957 l_invalid_in_alpha varchar2(100):= '~`!@#$%^&*()_+=|\[]{}<>":;?/.0123456789';
1958 begin
1959 hr_utility.trace('Validating value : '||p_input_value||' as '||p_mode);
1960 if p_mode = 'NUMERIC' then
1961 l_translated := translate (p_input_value
1962 , '~' || l_num
1963 , '~');
1964 if l_translated is not null then
1965 l_result := false;
1966 end if ;
1967 elsif p_mode = 'ALPHA' then
1968 l_translated := translate (p_input_value
1969 , l_invalid_in_alpha
1970 , lpad('~',length(l_invalid_in_alpha),'~'));
1971 if instr(l_translated,'~') >0 then
1972 l_result := false;
1973 end if ;
1974 elsif p_mode = 'ALPHANUM' then
1975 l_translated := translate (p_input_value
1976 , l_invalid_in_alphanum
1977 , lpad('~',length(l_invalid_in_alphanum),'~'));
1978 if instr(l_translated,'~') >0 then
1979 l_result := false;
1980 end if ;
1981 elsif p_mode = 'FREETEXT' then
1982 null;
1983 -- we will not validate for freetext
1984 end if;
1985 /*
1986 if p_mode = 'NUMERIC' then
1987 l_translated := translate (p_input_value
1988 , l_invalid_char || l_num
1989 , l_invalid_char);
1990 if l_translated is not null then
1991 l_result := false;
1992 end if ;
1993 elsif p_mode = 'ALPHA' then
1994 l_translated := translate (p_input_value
1995 , l_invalid_char || l_alpha
1996 , l_invalid_char);
1997 if l_translated is not null then
1998 l_result := false;
1999 end if ;
2000 elsif p_mode = 'ALPHANUM' then
2001 l_translated := translate (p_input_value
2002 , l_invalid_char || l_alphanum
2003 , l_invalid_char);
2004 if l_translated is not null then
2005 l_result := false;
2006 end if ;
2007 elsif p_mode = 'FREETEXT' then
2008 l_translated := translate (p_input_value
2009 , l_invalid_char || l_freetext
2010 , l_invalid_char);
2011 if l_translated is not null then
2012 l_result := false;
2013 end if ;
2014 end if;
2015 */
2016
2017 hr_utility.trace('l_translated='||l_translated);
2018 return l_result ;
2019 end validate_charcter_set;
2020
2021 ----------------------------------------------------------------------------
2022 -- validate_create_per_payment
2023 ----------------------------------------------------------------------------
2024 procedure validate_create_per_payment (P_EFFECTIVE_START_DATE IN DATE
2025 ,P_EFFECTIVE_END_DATE IN DATE
2026 ,P_ASSIGNMENT_ID IN NUMBER
2027 ,P_PERSONAL_PAYMENT_METHOD_ID IN NUMBER
2028 ,P_PPM_INFORMATION1 IN VARCHAR2 DEFAULT NULL)
2029 is
2030 l_count NUMBER;
2031 begin
2032 l_count := 0;
2033 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
2034 if P_PPM_INFORMATION1 in ('Y','0','7') then
2035 select count(*)
2036 into l_count
2037 from pay_personal_payment_methods_f
2038 where assignment_id = P_ASSIGNMENT_ID
2039 and personal_payment_method_id <>P_PERSONAL_PAYMENT_METHOD_ID
2040 and effective_start_date <= P_EFFECTIVE_END_DATE and effective_end_date >= P_EFFECTIVE_START_DATE
2041 and PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
2042 and nvl(PPM_INFORMATION1,'N') in ('Y','0','7');
2043
2044 if l_count > 0 then
2045 fnd_message.set_name('PAY','PY_ZA_INV_PERS_PAYM_DDF');
2046 fnd_message.raise_error;
2047 end if;
2048 end if;
2049 end if;
2050
2051 end validate_create_per_payment;
2052
2053 ----------------------------------------------------------------------------
2054 -- validate_update_per_payment
2055 ----------------------------------------------------------------------------
2056 procedure validate_update_per_payment (P_EFFECTIVE_DATE IN DATE
2057 ,P_EFFECTIVE_START_DATE IN DATE
2058 ,P_EFFECTIVE_END_DATE IN DATE
2059 ,P_PERSONAL_PAYMENT_METHOD_ID IN NUMBER
2060 ,P_PPM_INFORMATION1 IN VARCHAR2)
2061 is
2062 l_count NUMBER;
2063 l_assignment_id per_all_assignments_f.assignment_id%type;
2064 begin
2065 l_count := 0;
2066 IF hr_utility.chk_product_install('Oracle Human Resources', 'ZA') THEN
2067 select assignment_id
2068 into l_assignment_id
2069 from pay_personal_payment_methods_f
2070 where personal_payment_method_id = P_PERSONAL_PAYMENT_METHOD_ID
2071 and P_EFFECTIVE_DATE between effective_start_date and effective_end_date;
2072
2073 if P_PPM_INFORMATION1 in ('Y','0','7') then
2074 select count(*)
2075 into l_count
2076 from pay_personal_payment_methods_f
2077 where assignment_id = l_assignment_id
2078 and personal_payment_method_id <>P_PERSONAL_PAYMENT_METHOD_ID
2079 and PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
2080 and effective_start_date <= P_EFFECTIVE_END_DATE and effective_end_date >= P_EFFECTIVE_START_DATE
2081 and PPM_INFORMATION1 in ('Y','0','7');
2082
2083 if l_count > 0 then
2084 fnd_message.set_name('PAY','PY_ZA_INV_PERS_PAYM_DDF');
2085 fnd_message.raise_error;
2086 end if;
2087 end if;
2088 end if;
2089 end validate_update_per_payment;
2090
2091
2092 END per_za_user_hook_pkg;
[Home] [Help]