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