[Home] [Help]
PACKAGE BODY: APPS.PER_CN_EMP_LEG_HOOK
Source
1 PACKAGE BODY per_cn_emp_leg_hook AS
2 /* $Header: pecnlhpp.pkb 120.1 2006/02/06 01:46:25 rpalli noship $ */
3 --
4 --
5 g_trace BOOLEAN DEFAULT false;
6 --
7 --
8 --
9 --
10 --------------------------------------------------------------------------
11 -- --
12 -- Name : CHECK_INT_EMPLOYEE --
13 -- Type : Procedure --
14 -- Access : Private --
15 -- Description : Procedure is the driver procedure for the validation--
16 -- of the employee. --
17 -- Parameters : --
18 -- IN : p_hukou_type VARCHAR2 --
19 -- p_hukou_location VARCHAR2 --
20 -- p_highest_education_level VARCHAR2 --
21 -- p_number_of_children VARCHAR2 --
22 -- p_expatriate_indicator VARCHAR2 --
23 -- p_health_status VARCHAR2 --
24 -- p_tax_exemption_indicator VARCHAR2 --
25 -- p_percentage VARCHAR2 --
26 -- p_race_ethnic_origin VARCHAR2 --
27 -- p_business_group_id NUMBER --
28 -- p_national_identifier VARCHAR --
29 -- p_person_type_id NUMBER, --
30 -- p_effective_date DATE, --
31 -- p_person_id NUMBER --
32 -- --
33 -- Change History : --
34 --------------------------------------------------------------------------
35 -- Rev# Date Userid Description --
36 --------------------------------------------------------------------------
37 -- 1.0 29/11/02 saikrish Created this procedure --
38 -- 1.1 20/12/02 statkar Made changes as per ver 115.0 --
39 -- 1.2 09/01/03 statkar Made changes as per ver 115.1 --
40 -- 1.3 10/01/03 statkar Changed the name to CHECK_INT_EMPLOYEE --
41 -- and made procedure PRIVATE --
42 -- 1.4 31/01/03 statkar Added code to remove validation for --
43 -- defaulting values. Bug 2676440 --
44 -- Added validation for PTU. --
45 -- 1.5 05/02/03 statkar Added check for additional PTU (2782045) --
46 -- 1.6 14/04/03 statkar Removed mandatory checks for certain PTU --
47 -- Bug 2902744 --
48 -- 1.7 07/08/03 saikrish Removed p_given_han_yu_pin_yin_name, --
49 -- p_family_han_yu_pin_yin_name. Removed call--
50 -- hr_cn_api.check_name_dependence(3075230) --
51 -- 1.8 19/09/03 statkar Added the hr_utility.chk_product_install --
52 -- check for installed CN leg (3145322) --
53 -- 1.9 24/09/03 saikrish Changed the checks for person_type(2902659)-
54 -- 1.10 07/01/04 bramajey Bug 3342105 Changes. --
55 -- 1.11 06/02/06 rpalli Bug 4756920 Changes. Hukou Type and Hukou --
56 -- Location should be made conditionally --
57 -- mandatory --
58 --------------------------------------------------------------------------
59 PROCEDURE check_int_employee( p_business_group_id IN NUMBER
60 ,p_national_identifier IN VARCHAR2
61 ,p_person_type_id IN NUMBER
62 ,p_effective_date IN DATE
63 ,p_person_id IN NUMBER
64 ,p_hukou_type IN VARCHAR2
65 ,p_hukou_location IN VARCHAR2
66 ,p_highest_education_level IN VARCHAR2
67 ,p_number_of_children IN VARCHAR2
68 ,p_expatriate_indicator IN VARCHAR2
69 ,p_health_status IN VARCHAR2
70 ,p_tax_exemption_indicator IN VARCHAR2
71 ,p_percentage IN VARCHAR2
72 ,p_race_ethnic_origin IN VARCHAR2)
73 IS
74
75 --Declare local varialbles
76 l_return_number NUMBER(1);
77 l_proc VARCHAR2(72) := g_package||'check_int_employee';
78 --
79 -- Changed the cursor definition for bug 4756920.
80 --
81 CURSOR csr_ptu (p_person_type_id IN NUMBER, p_business_group_id IN NUMBER)
82 IS SELECT system_person_type
83 FROM per_person_types
84 WHERE business_group_id = p_business_group_id
85 AND person_type_id = p_person_type_id ;
86
87 l_person_type per_person_types.system_person_type%TYPE;
88
89 BEGIN
90 --
91 IF hr_cn_employee_api.g_trace OR hr_cn_applicant_api.g_trace THEN
92 g_trace:=true;
93 END IF;
94 --
95 -- Bug 3145322 Check the leg-specific validations only if the legislation
96 -- is installed
97 --
98 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'CN') THEN
99 hr_utility.trace ('CN Legislation not installed. Not performing the validations');
100 hr_cn_api.set_location(g_trace,'Leaving: '||l_proc, 15);
101 RETURN;
102 END IF;
103
104 --
105 --
106 -- Get the PT
107 -- Changed for bug 2902659, person type will contain system person type based on
108 -- the person type id of person type usages.
109 --
110 OPEN csr_ptu (p_person_type_id, p_business_group_id);
111 FETCH csr_ptu INTO l_person_type;
112 IF csr_ptu%NOTFOUND THEN
113 CLOSE csr_ptu;
114 hr_api.mandatory_arg_error
115 (p_api_name => l_proc,
116 p_argument => 'P_PERSON_TYPE_ID',
117 p_argument_value => p_person_type_id
118 );
119 ELSE
120 CLOSE csr_ptu;
121 END IF;
122
123
124
125 --
126 -- Check for the mandatory arguments
127 --
128 --
129 IF hr_cn_api.chk_person_type (l_person_type)
130 THEN
131
132
133 -- Bug 3342105 Changes.
134 -- Moved the Expat Indicator and CIN check to this IF block
135
136 hr_cn_api.set_location(g_trace,l_proc,10);
137 hr_api.mandatory_arg_error
138 (p_api_name => l_proc,
139 p_argument => 'P_EXPATRIATE_INDICATOR',
140 p_argument_value => p_expatriate_indicator
141 );
142
143 IF p_expatriate_indicator ='N' THEN
144 hr_cn_api.set_location(g_trace,l_proc,20);
145 hr_api.mandatory_arg_error
146 (p_api_name => l_proc,
147 p_argument => 'P_HUKOU_TYPE',
148 p_argument_value => p_hukou_type
149 );
150
151 hr_cn_api.set_location(g_trace,l_proc,30);
152 hr_api.mandatory_arg_error
153 (p_api_name => l_proc,
154 p_argument => 'P_HUKOU_LOCATION',
155 p_argument_value => p_hukou_location
156 );
157 END IF;
158 --
159 -- Validate the CIN based on EXPATRIATE_INDICATOR and PERSON_TYPE
160 -- Bug 2737913 -- Ver 115.1 -- statkar
161 -- Bug 2782045 -- In place of p_person_type_id, we call check_cin
162 -- with l_person_type for ease in checking.
163 -- Bug 2902659 -- Tested p_national_identifier for NULL condition
164 --
165 IF (p_national_identifier <> hr_api.g_varchar2) OR (p_national_identifier IS NULL) THEN
166 hr_cn_api.check_cin
167 ( p_business_group_id => p_business_group_id,
168 p_national_identifier => p_national_identifier,
169 p_person_type_id => p_person_type_id,
170 p_expatriate_indicator => p_expatriate_indicator,
171 p_effective_date => p_effective_date,
172 p_person_id => p_person_id
173 );
174 END IF;
175 END IF;
176
177 --
178 -- Check for the valid lookup values
179 --
180 hr_cn_api.set_location(g_trace,l_proc,40);
181
182 IF p_hukou_type IS NOT NULL and p_hukou_type <> hr_api.g_varchar2 THEN
183 hr_cn_api.check_lookup (
184 p_lookup_type => 'CN_HUKOU_TYPE',
185 p_argument => 'P_HUKOU_TYPE',
186 p_argument_value => p_hukou_type
187 );
188 END IF;
189
190 hr_cn_api.set_location(g_trace,l_proc,50);
191 IF p_hukou_location IS NOT NULL and p_hukou_location <> hr_api.g_varchar2 THEN
192 hr_cn_api.check_lookup (
193 p_lookup_type => 'CN_HUKOU_LOCN',
194 p_argument => 'P_HUKOU_LOCATION',
195 p_argument_value => p_hukou_location
196 );
197 END IF;
198
199 hr_cn_api.set_location(g_trace,l_proc,60);
200 IF p_expatriate_indicator <> hr_api.g_varchar2 THEN
201 hr_cn_api.check_lookup (
202 p_lookup_type => 'YES_NO',
203 p_argument => 'P_EXPATRIATE_INDICATOR',
204 p_argument_value => p_expatriate_indicator
205 );
206 END IF;
207
208 hr_cn_api.set_location(g_trace,l_proc,70);
209 IF p_race_ethnic_origin is not null AND p_race_ethnic_origin <> hr_api.g_varchar2
210 THEN
211 hr_cn_api.check_lookup (
212 p_lookup_type => 'CN_RACE',
213 p_argument => 'P_RACE_ETHNIC_ORGIN',
214 p_argument_value => p_race_ethnic_origin
215 );
216 END IF;
217
218 hr_cn_api.set_location(g_trace,l_proc,80);
219 IF p_highest_education_level is not null AND p_highest_education_level <> hr_api.g_varchar2
220 THEN
221 hr_cn_api.check_lookup (
222 p_lookup_type => 'CN_HIGH_EDU_LEVEL',
223 p_argument => 'P_HIGHEST_EDUCATION_LEVEL',
224 p_argument_value => p_highest_education_level
225 );
226 END IF;
227
228 hr_cn_api.set_location(g_trace,l_proc,90);
229 IF p_health_status is not null AND p_health_status <> hr_api.g_varchar2
230 THEN
231 hr_cn_api.check_lookup (
232 p_lookup_type => 'CN_HEALTH_STATUS',
233 p_argument => 'P_HEALTH_STATUS',
234 p_argument_value => p_health_status
235 );
236 END IF;
237
238 hr_cn_api.set_location(g_trace,l_proc,100);
239 IF p_tax_exemption_indicator is not null AND p_tax_exemption_indicator <> hr_api.g_varchar2
240 THEN
241 hr_cn_api.check_lookup (
242 p_lookup_type => 'YES_NO',
243 p_argument => 'P_TAX_EXEMPTION_INDICATOR',
244 p_argument_value => p_tax_exemption_indicator
245 );
246 END IF;
247
248
249 --
250 -- Validation for Number of Children
251 --
252 hr_cn_api.set_location(g_trace,l_proc,110);
253 IF p_number_of_children is not null AND p_number_of_children <> hr_api.g_varchar2
254 THEN
255 --
256 IF hr_cn_api.is_number(p_number_of_children) THEN
257 --
258 hr_cn_api.set_location(g_trace,l_proc,120);
259 IF NOT hr_cn_api.is_positive_integer(to_number(p_number_of_children)) THEN
260 --
261 hr_cn_api.set_location(g_trace,l_proc,130);
262 hr_utility.set_message(800,'HR_374602_INVALID_VALUE');
263 hr_utility.set_message_token('VALUE', p_number_of_children);
264 hr_utility.set_message_token('FIELD','P_NUMBER_OF_CHILDREN');
265 hr_utility.raise_error;
266 END IF;
267 --
268 ELSE
269 --
270 hr_cn_api.set_location(g_trace,l_proc,140);
271 hr_utility.set_message(800,'HR_374602_INVALID_VALUE');
272 hr_utility.set_message_token('VALUE', p_number_of_children);
273 hr_utility.set_message_token('FIELD','P_NUMBER_OF_CHILDREN');
274 hr_utility.raise_error;
275 --
276 END IF;
277 --
278 END IF;
279
280
281 --
282 -- Check for the tax dependence.
283 --
284 hr_cn_api.set_location(g_trace,l_proc,150);
285 hr_cn_api.check_tax_dependence(p_tax_exemption_indicator
286 ,p_percentage
287 );
288
289 --
290 -- Validation for Tax Percentage
291 --
292 hr_cn_api.set_location (g_trace, l_proc, 160);
293 IF NOT hr_cn_api.is_number(p_percentage)
294 THEN
295 hr_utility.set_message(800,'HR_374602_INVALID_VALUE');
296 hr_utility.set_message_token('VALUE', p_percentage);
297 hr_utility.set_message_token('FIELD','P_PERCENTAGE');
298 hr_utility.raise_error;
299 ELSIF NOT hr_cn_api.is_valid_percentage(to_number(p_percentage))
300 THEN
301 hr_utility.set_message('PER','HR_374603_INVALID_RANGE');
302 hr_utility.set_message_token('NUMBER','P_PERCENTAGE');
303 hr_utility.set_message_token('LOW','0');
304 hr_utility.set_message_token('HIGH','100');
305 hr_utility.raise_error;
306 ELSIF length(substr(p_percentage,instr(p_percentage,'.',1)+1)) > 3
307 -- Bug 2748530 changes start
308 -- Check for decimal length
309 --
310 THEN
311 hr_utility.set_message('PER','HR_374607_INVALID_FORMAT');
312 hr_utility.set_message_token('FIELD','P_PERCENTAGE');
313 hr_utility.set_message_token('FORMAT','99.999');
314 hr_utility.raise_error;
315 END IF;
316 --
317 -- Bug 2748530 changes end
318 --
319 hr_cn_api.set_location(g_trace,'Leaving:'||l_proc,220);
320
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 RAISE;
325 END check_int_employee;
326
330 -- --
327
328 --
329 --------------------------------------------------------------------------
331 -- Name : CHECK_EMPLOYEE --
332 -- Type : Procedure --
333 -- Access : Public --
334 -- Description : Procedure is the driver procedure for the validation--
335 -- of the employee. --
336 -- This procedure is the hook procedure for the --
337 -- employee. --
338 -- Parameters : --
339 -- IN : p_per_information4 VARCHAR2 --
340 -- p_per_information5 VARCHAR2 --
341 -- p_per_information6 VARCHAR2 --
342 -- p_per_information7 VARCHAR2 --
343 -- p_per_information8 VARCHAR2 --
344 -- p_per_information10 VARCHAR2 --
345 -- p_per_information11 VARCHAR2 --
346 -- p_per_information12 VARCHAR2 --
347 -- p_per_information17 VARCHAR2 --
348 -- p_business_group_id NUMBER --
349 -- p_national_identifier VARCHAR --
350 -- p_person_type_id NUMBER, --
351 -- p_effective_date DATE, --
352 -- --
353 -- Change History : --
354 --------------------------------------------------------------------------
355 -- Rev# Date Userid Description --
356 --------------------------------------------------------------------------
357 -- 1.0 29/11/02 saikrish Created this procedure --
358 -- 1.1 09/01/03 statkar Made changes as per ver 115.1 --
359 -- 1.2 10/01/03 statkar Changed proc to call CHECK_INT_EMPLOYEE --
360 -- 1.3 07/08/03 saikrish Removed p_per_information14, --
361 -- p_per_information15 (3075230) --
362 --------------------------------------------------------------------------
363 PROCEDURE check_employee (p_business_group_id IN NUMBER
364 ,p_national_identifier IN VARCHAR2
365 ,p_person_type_id IN NUMBER
366 ,p_hire_date IN DATE
367 ,p_per_information4 IN VARCHAR2
368 ,p_per_information5 IN VARCHAR2
369 ,p_per_information6 IN VARCHAR2
370 ,p_per_information7 IN VARCHAR2
371 ,p_per_information8 IN VARCHAR2
372 ,p_per_information10 IN VARCHAR2
373 ,p_per_information11 IN VARCHAR2
374 ,p_per_information12 IN VARCHAR2
375 ,p_per_information17 IN VARCHAR2)
376 IS
377
378 --Declare local varialbles
379 l_proc VARCHAR2(72) := g_package||'check_employee';
380
381 BEGIN
382 --
383 IF hr_cn_employee_api.g_trace OR hr_cn_applicant_api.g_trace THEN
384 g_trace:=true;
385 END IF;
386 --
387 hr_cn_api.set_location(g_trace,'Entering:'|| l_proc,10);
388 --
389 -- Bug 3075230 Removed p_given_han_yu_pin_yin_name,p_family_han_yu_pin_yin_name
390 --
391 check_int_employee
392 (p_business_group_id => p_business_group_id
393 ,p_national_identifier => p_national_identifier
394 ,p_person_type_id => p_person_type_id
395 ,p_effective_date => p_hire_date
396 ,p_person_id => NULL
397 ,p_hukou_type => p_per_information4
398 ,p_hukou_location => p_per_information5
399 ,p_highest_education_level => p_per_information6
400 ,p_number_of_children => p_per_information7
401 ,p_expatriate_indicator => p_per_information8
402 ,p_health_status => p_per_information10
403 ,p_tax_exemption_indicator => p_per_information11
404 ,p_percentage => p_per_information12
405 ,p_race_ethnic_origin => p_per_information17 );
406
407 hr_cn_api.set_location(g_trace,'Leaving:'||l_proc,200);
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 RAISE;
412
413 END check_employee;
414 --
415
416
417 --
418 --------------------------------------------------------------------------
419 -- --
420 -- Name : CHECK_APPLICANT --
421 -- Type : Procedure --
422 -- Access : Public --
423 -- Description : Procedure is the driver procedure for the validation--
424 -- of the applicant. --
428 -- IN : p_per_information4 VARCHAR2 --
425 -- This procedure is the hook procedure for the --
426 -- applicant. --
427 -- Parameters : --
429 -- p_per_information5 VARCHAR2 --
430 -- p_per_information6 VARCHAR2 --
431 -- p_per_information7 VARCHAR2 --
432 -- p_per_information8 VARCHAR2 --
433 -- p_per_information10 VARCHAR2 --
434 -- p_per_information11 VARCHAR2 --
435 -- p_per_information12 VARCHAR2 --
436 -- p_per_information17 VARCHAR2 --
437 -- p_business_group_id NUMBER --
438 -- p_national_identifier VARCHAR --
439 -- p_person_type_id NUMBER, --
440 -- p_effective_date DATE, --
441 -- --
442 -- Change History : --
443 --------------------------------------------------------------------------
444 -- Rev# Date Userid Description --
445 --------------------------------------------------------------------------
446 -- 1.0 29/11/02 saikrish Created this procedure --
447 -- 1.1 09/01/03 statkar Made changes as per ver 115.1 --
448 -- 1.2 10/01/03 statkar Changed proc to call CHECK_INT_EMPLOYEE --
449 -- 1.3 07/08/03 saikrish Removed p_per_information14, --
450 -- p_per_information15 (3075230) --
451 --------------------------------------------------------------------------
452 PROCEDURE check_applicant(p_business_group_id IN NUMBER
453 ,p_national_identifier IN VARCHAR2
454 ,p_person_type_id IN NUMBER
455 ,p_date_received IN DATE
456 ,p_per_information4 IN VARCHAR2
457 ,p_per_information5 IN VARCHAR2
458 ,p_per_information6 IN VARCHAR2
459 ,p_per_information7 IN VARCHAR2
460 ,p_per_information8 IN VARCHAR2
461 ,p_per_information10 IN VARCHAR2
462 ,p_per_information11 IN VARCHAR2
463 ,p_per_information12 IN VARCHAR2
464 ,p_per_information17 IN VARCHAR2)
465 IS
466
467 --Declare local varialbles
468 l_proc VARCHAR2(72) := g_package||'check_applicant';
469
470 BEGIN
471 --
472 IF hr_cn_employee_api.g_trace OR hr_cn_applicant_api.g_trace THEN
473 g_trace:=true;
474 END IF;
475 --
476 hr_cn_api.set_location(g_trace,'Entering:'|| l_proc,10);
477
478 --
479 -- Bug 3075230 Removed p_given_han_yu_pin_yin_name,p_family_han_yu_pin_yin_name
480 --
481 check_int_employee(p_business_group_id => p_business_group_id
482 ,p_national_identifier => p_national_identifier
483 ,p_person_type_id => p_person_type_id
484 ,p_effective_date => p_date_received
485 ,p_person_id => NULL
486 ,p_hukou_type => p_per_information4
487 ,p_hukou_location => p_per_information5
488 ,p_highest_education_level => p_per_information6
489 ,p_number_of_children => p_per_information7
490 ,p_expatriate_indicator => p_per_information8
491 ,p_health_status => p_per_information10
492 ,p_tax_exemption_indicator => p_per_information11
493 ,p_percentage => p_per_information12
494 ,p_race_ethnic_origin => p_per_information17 );
495
496 hr_cn_api.set_location(g_trace,'Leaving:'||l_proc,200);
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 RAISE;
501
502 END check_applicant;
503 --
504
505 --------------------------------------------------------------------------
506 -- --
507 -- Name : CHECK_PERSON --
508 -- Type : Procedure --
509 -- Access : Public --
510 -- Description : Procedure is the driver procedure for the validation--
511 -- of the applicant. --
512 -- This procedure is the hook procedure for the --
513 -- applicant. --
514 -- Parameters : --
515 -- IN : p_per_information4 VARCHAR2 --
516 -- p_per_information5 VARCHAR2 --
517 -- p_per_information6 VARCHAR2 --
518 -- p_per_information7 VARCHAR2 --
522 -- p_per_information12 VARCHAR2 --
519 -- p_per_information8 VARCHAR2 --
520 -- p_per_information10 VARCHAR2 --
521 -- p_per_information11 VARCHAR2 --
523 -- p_per_information17 VARCHAR2 --
524 -- p_national_identifier VARCHAR --
525 -- p_person_type_id NUMBER, --
526 -- p_effective_date DATE, --
527 -- p_person_id NUMBER --
528 -- --
529 -- Change History : --
530 --------------------------------------------------------------------------
531 -- Rev# Date Userid Description --
532 --------------------------------------------------------------------------
533 -- 1.0 09/01/03 statkar Created this procedure --
534 -- 1.2 10/01/03 statkar Changed proc to call CHECK_INT_EMPLOYEE --
535 -- 1.1 31/01/03 statkar Added a new cursor to fetch old value --
536 -- Bug 2767440 --
537 -- 1.3 04/11/03 statkar Bug 2900110 changes for Expat Indicator --
538 -- 1.4 04/11/03 statkar Bug 2900110 added NOT NULL for Expat --
539 -- 1.5 07/08/03 saikrish Removed p_per_information14, --
540 -- p_per_information15 (3075230) --
541 -- 1.6 19/09/03 statkar Added the hr_utility.chk_product_install --
542 -- check for installed CN leg (3145322) --
543 -- 1.7 30/09/03 saikrish Retrieved national_identifier (2902659) --
544 --------------------------------------------------------------------------
545 PROCEDURE check_person (p_national_identifier IN VARCHAR2
546 ,p_person_type_id IN NUMBER
547 ,p_effective_date IN DATE
548 ,p_person_id IN NUMBER
549 ,p_per_information4 IN VARCHAR2
550 ,p_per_information5 IN VARCHAR2
551 ,p_per_information6 IN VARCHAR2
552 ,p_per_information7 IN VARCHAR2
553 ,p_per_information8 IN VARCHAR2
554 ,p_per_information10 IN VARCHAR2
555 ,p_per_information11 IN VARCHAR2
556 ,p_per_information12 IN VARCHAR2
557 ,p_per_information17 IN VARCHAR2)
558 IS
559
560 --Declare local varialbles
561 l_proc VARCHAR2(72) := g_package||'check_person';
562 l_business_group_id per_all_people_f.business_group_id%TYPE;
563
564 l_tax_exemption_indicator per_all_people_f.per_information11%TYPE;
565 l_expatriate_indicator per_all_people_f.per_information8%TYPE;
566 l_percentage per_all_people_f.per_information12%TYPE;
567 l_person_type_id per_all_people_f.person_type_id%TYPE;
568 l_national_identifier per_all_people_f.national_identifier%TYPE;
569
570 CURSOR csr_bg_per (p_person_id per_all_people_f.person_id%type,
571 p_effective_date in DATE) IS
572 SELECT business_group_id
573 FROM per_all_people_f pap
574 WHERE pap.person_id = p_person_id
575 AND p_effective_date BETWEEN pap.effective_start_date and pap.effective_end_date;
576 --
577 -- Bug 2900110: Added the segment per_information8 to the list
578 --
579 --
580 -- Bug 3075230: Removed per_information14,per_information15
581 --
582 -- Bug 2902659: Added national_identifier
583 CURSOR csr_per IS SELECT person_type_id, per_information11, per_information12, nvl(per_information8,'N')
584 ,national_identifier
585 FROM per_all_people_f
586 WHERE person_id = p_person_id
587 AND p_effective_date BETWEEN effective_start_date
588 AND effective_end_date;
589
590 BEGIN
591 --
592 IF hr_cn_employee_api.g_trace OR hr_cn_applicant_api.g_trace THEN
593 g_trace:=true;
594 END IF;
595 --
596 hr_cn_api.set_location(g_trace,'Entering:'|| l_proc,10);
597 --
598 -- Bug 3145322 Check the leg-specific validations only if the legislation
599 -- is installed
600 --
601 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'CN') THEN
602 hr_utility.trace ('CN Legislation not installed. Not performing the validations');
603 hr_cn_api.set_location(g_trace,'Leaving: '||l_proc, 15);
604 RETURN;
605 END IF;
606
607 OPEN csr_bg_per (p_person_id, p_effective_date) ;
608 FETCH csr_bg_per INTO l_business_group_id;
609 IF csr_bg_per%NOTFOUND THEN
610 CLOSE csr_bg_per;
611 hr_utility.set_message(800,'HR_7961_PER_BUS_GRP_INVALID');
612 hr_utility.set_message_token('LEG_CODE','CN');
613 hr_utility.raise_error;
614 END IF;
615 CLOSE csr_bg_per;
616
617 OPEN csr_per;
618 -- Removed l_family_hypy_name, l_given_hypy_name w.r.t bug 3075230
619 FETCH csr_per INTO l_person_type_id, l_tax_exemption_indicator, l_percentage, l_expatriate_indicator
620 ,l_national_identifier;
621 CLOSE csr_per;
622
623 IF p_per_information11 <> hr_api.g_varchar2 THEN
624 l_tax_exemption_indicator := p_per_information11;
625 END IF;
626
627 IF p_per_information12 <> hr_api.g_varchar2 THEN
628 l_percentage := p_per_information12;
629 END IF;
630
631 IF p_national_identifier <> hr_api.g_varchar2 THEN
632 l_national_identifier := p_national_identifier;
633 END IF;
634
635
636 --
637 -- Bug 2900110: Added the following IF..END IF clause for Expat Indicator
638 --
639 IF p_per_information8 <> hr_api.g_varchar2 and p_per_information8 IS NOT NULL THEN
640 l_expatriate_indicator := p_per_information8;
641 END IF;
642
643 --
644 -- Validation to be carried out only for certain PTs
645 --
646 hr_api.mandatory_arg_error
647 (p_api_name => l_proc,
648 p_argument => 'P_PERSON_TYPE_ID',
649 p_argument_value => p_person_type_id
650 );
651
652 IF p_person_type_id <> hr_api.g_number
653 THEN
654 l_person_type_id := p_person_type_id;
655 END IF;
656
657 --
658 -- Bug 3075230 removed p_given_han_yu_pin_yin_name,p_family_han_yu_pin_yin_name
659 --
660 check_int_employee(p_business_group_id => l_business_group_id
661 ,p_national_identifier => l_national_identifier
662 ,p_person_type_id => l_person_type_id
663 ,p_effective_date => p_effective_date
664 ,p_person_id => p_person_id
665 ,p_hukou_type => p_per_information4
666 ,p_hukou_location => p_per_information5
667 ,p_highest_education_level => p_per_information6
668 ,p_number_of_children => p_per_information7
669 ,p_expatriate_indicator => l_expatriate_indicator -- Bug 2900110
670 ,p_health_status => p_per_information10
671 ,p_tax_exemption_indicator => l_tax_exemption_indicator
672 ,p_percentage => l_percentage
673 ,p_race_ethnic_origin => p_per_information17 );
674
675 hr_cn_api.set_location(g_trace,'Leaving:'||l_proc,200);
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 RAISE;
680
681 END check_person;
682
683
684 END per_cn_emp_leg_hook;