1 PACKAGE BODY hr_cn_api AS
2 /* $Header: hrcnapi.pkb 120.5 2011/07/01 10:32:33 jmarupil ship $ */
3
4 --------------------------------------------------------------------------
5 -- --
6 -- Name : SET_LOCATION --
7 -- Type : PROCEDURE --
8 -- Access : Public --
9 -- Description : Procedure to set the location based on the trace --
10 -- --
11 -- Parameters : --
12 -- IN : p_message VARCHAR2 --
13 -- p_step number --
14 -- p_trace VARCHAR2 --
15 -- --
16 -- Change History : --
17 --------------------------------------------------------------------------
18 -- Rev# Date Userid Description --
19 --------------------------------------------------------------------------
20 -- 1.0 30/12/02 statkar Created this function --
21 --------------------------------------------------------------------------
22 PROCEDURE set_location (p_trace IN BOOLEAN
23 ,p_message IN VARCHAR2
24 ,p_step IN INTEGER
25 )
26 IS
27 BEGIN
28
29 IF p_trace THEN
30 set_location(p_message, p_step);
31 END IF;
32
33 END set_location;
34
35 ----------------------------------------------------------------------------
36 -- --
37 -- Name : SET_LOCATION --
38 -- Type : PROCEDURE --
39 -- Access : Public --
40 -- Description : Procedure to set the location irrespective of trace --
41 -- --
42 -- Parameters : --
43 -- IN : p_message VARCHAR2 --
44 -- p_step number --
45 -- --
46 -- Change History : --
47 --------------------------------------------------------------------------
48 -- Rev# Date Userid Description --
49 --------------------------------------------------------------------------
50 -- 1.0 30/12/02 statkar Created this function --
51 ---------------------------------------------------------------------------
52 PROCEDURE set_location (p_message IN VARCHAR2
53 ,p_step IN INTEGER
54 )
55 IS
56 BEGIN
57
58 hr_utility.set_location(p_message, p_step);
59
60 END set_location;
61
62 ----------------------------------------------------------------------------
63 -- --
64 -- Name : CHECK_LOOKUP --
65 -- Type : Procedure --
66 -- Access : Public --
67 -- Description : Function to validate the lookupcode in lookuptype --
68 -- Function will return true in case the lookupcode is --
69 -- found in the lookuptype.Used in the check_employee. --
70 -- Parameters : --
71 -- IN : p_value VARCHAR2 --
72 -- p_lookup_name VARCHAR2 --
73 -- RETURN : Boolean --
74 -- --
75 -- Change History : --
76 --------------------------------------------------------------------------
77 -- Rev# Date Userid Description --
78 --------------------------------------------------------------------------
79 -- 1.0 01/03/03 statkar Created this function --
80 ---------------------------------------------------------------------------
81 PROCEDURE check_lookup (
82 p_lookup_type IN VARCHAR2,
83 p_argument IN VARCHAR2,
84 p_argument_value IN VARCHAR2
85 )
86 IS
87
88 l_lookup_code hr_lookups.lookup_code%TYPE;
89 --
90 CURSOR csr_lookup(p_argument_value VARCHAR2,p_lookup_type VARCHAR2) IS
91 SELECT lookup_code
92 FROM hr_lookups hrl
93 WHERE hrl.lookup_type = p_lookup_type
94 AND hrl.lookup_code = p_argument_value
95 AND enabled_flag = 'Y';
96
97 BEGIN
98
99 --
100 -- Validation of the lookup value based on the lookup type
101 --
102
103 OPEN csr_lookup(p_argument_value, p_lookup_type);
104 FETCH csr_lookup INTO l_lookup_code;
105
106 IF csr_lookup%NOTFOUND THEN
107 CLOSE csr_lookup;
108 hr_utility.set_message(800,'HR_374602_INVALID_VALUE');
109 hr_utility.set_message_token('VALUE',p_argument_value);
110 hr_utility.set_message_token('FIELD',p_argument);
111 hr_utility.raise_error;
112 END IF;
113
114 CLOSE csr_lookup;
115
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 IF csr_lookup%ISOPEN THEN
120 CLOSE csr_lookup;
121 END IF;
122 RAISE;
123
124 END check_lookup;
125
126 --
127 --------------------------------------------------------------------------
128 -- --
129 -- Name : IS_NUMBER --
130 -- Type : Function --
131 -- Access : Public --
132 -- Description : Procedure to check IF a value is numeric --
133 -- Parameters : --
134 -- IN : p_value VARCHAR2 --
135 -- --
136 -- Change History : --
137 --------------------------------------------------------------------------
138 -- Rev# Date Userid Description --
139 --------------------------------------------------------------------------
140 -- 1.0 30/12/02 statkar Created this procedure --
141 ---------------------------------------------------------------------------
142
143 FUNCTION is_number
144 (p_value in VARCHAR2)
145 RETURN BOOLEAN
146 IS
147 l_number_value number;
148 BEGIN
149 --
150
151 IF(p_value is NULL) THEN
152 RETURN TRUE;
153 ELSE
154 Begin
155 l_number_value := to_number(p_value);
156
157 Exception
158 WHEN VALUE_ERROR THEN
159 RETURN FALSE;
160 End;
161 RETURN TRUE;
162 END IF;
163 --
164 END is_number;
165
166 --
167 --------------------------------------------------------------------------
168 -- --
169 -- Name : IS_POSITIVE_INTEGER --
170 -- Type : Function --
171 -- Access : Public --
172 -- Description : Function to validate the char as positive integer --
173 -- Parameters : --
174 -- IN : p_value VARCHAR2 --
175 -- RETURN : Boolean --
176 -- --
177 -- Change History : --
178 --------------------------------------------------------------------------
179 -- Rev# Date Userid Description --
180 --------------------------------------------------------------------------
181 -- 1.0 29/11/02 saikrish Created this function --
182 -- 1.1 21/1/03 bramajey changed the condition to p_value < 0 to --
183 -- allow the code flow to check for decimal --
184 --------------------------------------------------------------------------
185 FUNCTION is_positive_integer
186 (p_value IN NUMBER
187 )
188 RETURN BOOLEAN IS
189
190 BEGIN
191
192 --
193 -- No validations IF the p_value is NULL
194 --
195 IF p_value IS NULL THEN
196 RETURN TRUE;
197 END IF;
198
199 --
200 -- Check IF the number is positive
201 --
202
203 IF p_value < 0 THEN
204 RETURN FALSE;
205 ELSE
206 --
207 -- Checking for decimal.
208 --
209
210 IF INSTR(p_value,'.') = 0 THEN
211 RETURN TRUE;
212 ELSE
213 RETURN FALSE;
214 END IF;
215 --
216 END IF;
217 EXCEPTION
218 WHEN OTHERS THEN
219 RAISE;
220
221 END is_positive_integer;
222 --
223 --------------------------------------------------------------------------
224 -- --
225 -- Name : IS_VALID_PERCENTAGE --
226 -- Type : Function --
227 -- Access : Public --
228 -- Description : Function to validate the char as positive percentage--
229 -- Parameters : --
230 -- IN : p_value VARCHAR2 --
231 -- RETURN : Boolean --
232 -- --
233 -- Change History : --
234 --------------------------------------------------------------------------
235 -- Rev# Date Userid Description --
236 --------------------------------------------------------------------------
237 -- 1.0 29/11/02 saikrish Created this function --
238 -- 1.1 21/1/03 bramajey Removed the check for decimal places --
239 -- --
240 --------------------------------------------------------------------------
241 FUNCTION is_valid_percentage
242 (p_value IN NUMBER
243 )
244 RETURN BOOLEAN IS
245
246 BEGIN
247
248 --
249 -- No validations IF the p_value is NULL
250 --
251 IF p_value IS NULL THEN
252 RETURN TRUE;
253 END IF;
254
255 --
256 -- Checking for valid range.
257 --
258
259 IF ((p_value >= 0) and (p_value <=100)) THEN
260 RETURN TRUE;
261 ELSE
262 RETURN FALSE;
263 END IF;
264 --
265 EXCEPTION
266 WHEN OTHERS THEN
267 RAISE;
268
269 END is_valid_percentage;
270 --
271 --
272 --------------------------------------------------------------------------
273 -- Name : IS_VALID_POSTAL_CODE --
274 -- Type : Function --
275 -- Access : Private --
276 -- Description : The function validates the postal code ,checks to --
277 -- see IF the postal code is a 6 digit value and that --
278 -- all digits are numbers,IF so returns true else false--
279 -- Parameters : --
280 -- IN : p_value_to_be_checked IN VARCHAR2 --
281 -- RETURN : Boolean --
282 -- --
283 -- Change History : --
284 --------------------------------------------------------------------------
285 -- Rev# Date Userid Description --
286 --------------------------------------------------------------------------
287 -- 1.0 29/11/02 vinaraya Created this function --
288 -- 1.1 30/10/03 bramajey Changed value of l_low_range from 100000 --
289 -- to 1. --
290 -- Changed type of IN parameter from NUMBER --
291 -- to VARCHAR2. (Bug 3226285) --
292 --------------------------------------------------------------------------
293 FUNCTION is_valid_postal_code
294 (p_value IN VARCHAR2
295 )
296 RETURN BOOLEAN IS
297 --
298 -- Bug 3226285
299 l_low_range NUMBER;
300 l_high_range NUMBER;
301
302 BEGIN
303
304 l_low_range := 1;
305 l_high_range := 999999;
306
307 --
308 -- No validations IF the p_value is NULL
309 --
310 IF p_value IS NULL THEN
311 RETURN TRUE;
312 END IF;
313
314 --
315 -- Check IF the range is valid
316 --
317 -- Bug 3226285
318 -- added is_number conidtion
319 IF is_number(p_value) AND (p_value BETWEEN l_low_range AND l_high_range) AND LENGTH(p_value) = 6 THEN
320 RETURN TRUE;
321 ELSE
322 RETURN FALSE;
323 END IF;
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 RAISE;
328 END is_valid_postal_code;
329
330 --------------------------------------------------------------------------
331 -- --
332 -- Name : CHK_PERSON_TYPE --
333 -- Type : Function --
334 -- Access : Public --
335 -- Description : Returns true/false IF p_code is a valid Person Type --
336 -- Parameters : --
337 -- IN : p_code VARCHAR2 --
338 -- OUT : N/A --
339 -- RETURN : VARCHAR2 --
340 -- --
341 -- Change History : --
342 --------------------------------------------------------------------------
343 -- Rev# Date Userid Description --
344 --------------------------------------------------------------------------
345 -- 1.0 14/04/03 statkar Created this function --
346 -- 1.1 07/01/04 bramajey Bug 3342105 Changes. --
347 --------------------------------------------------------------------------
348 FUNCTION chk_person_type (p_code in VARCHAR2)
349 RETURN BOOLEAN
350 IS
351 TYPE t_pt_tbl IS TABLE OF HR_LOOKUPS.LOOKUP_CODE%TYPE index by binary_integer;
352 l_person_type t_pt_tbl;
353 l_loop_count NUMBER ;
354
355 BEGIN
356
357 -- Change here in case any new PTs to be included
358
359 -- Bug 3342105
360 -- Removed PTs APL and EX_APL
361
362 l_person_type(1) := 'EMP';
363 l_person_type(2) := 'EX_EMP';
364 l_person_type(3) := 'APL_EX_EMP';
365 l_person_type(4) := 'EMP_APL';
366 l_person_type(5) := 'EX_EMP_APL';
367 l_person_type(6) := 'CWK';
368
369 l_loop_count := 6;
370
371 -- Changes above this only.
372
373 FOR i IN 1..l_loop_count
374 LOOP
375 IF l_person_type(i) = p_code THEN
376 RETURN TRUE;
377 END IF;
378 END LOOP;
379
380 RETURN FALSE;
381
382 END chk_person_type;
383
384 --------------------------------------------------------------------------
385 -- --
386 -- Name : CHECK_ORGANIZATION --
387 -- Type : Procedure --
388 -- Access : Public --
389 -- Description : The function checks IF the organization id --
390 -- belongs to the business group specIFied for the --
391 -- legislation --
392 -- Parameters : --
393 -- IN : p_organization_id IN NUMBER --
394 -- : p_business_group_id IN NUMBER --
395 -- p_legislation_code IN NUMBER --
396 -- p_effective_date IN DATE --
397 -- RETURN : Boolean --
398 -- --
399 --------------------------------------------------------------------------
400 -- 1.0 30/12/02 statkar Created this function --
401 -- 1.1 16/01/03 statkar bug 2748967 added check for effective date --
402 ---------------------------------------------------------------------------
403
404 PROCEDURE check_organization
405 (p_organization_id IN NUMBER
406 ,p_business_group_id IN NUMBER
407 ,p_legislation_code IN VARCHAR2
408 -- Bug 2748967 changes start
409 ,p_effective_date IN DATE
410 -- Bug 2748967 changes END
411 )
412 IS
413
414 CURSOR csr_org_id (p_business_group_id number, p_organization_id in number, p_effective_date in date) IS
415 SELECT hou.organization_id
416 FROM hr_organization_units hou
417 WHERE hou.business_group_id = p_business_group_id
418 AND hou.organization_id = p_organization_id
419 AND p_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
420
421 l_org_id hr_organization_units.organization_id%type;
422
423 BEGIN
424
425 check_bus_grp (p_business_group_id, p_legislation_code);
426
427 OPEN csr_org_id (p_business_group_id, p_organization_id, p_effective_date);
428 FETCH csr_org_id into l_org_id;
429 IF csr_org_id%NOTFOUND THEN
430 CLOSE csr_org_id;
431 hr_utility.set_message(800,'HR_374604_INVALID_ORG_CLASS');
432 hr_utility.set_message_token('ORG',p_organization_id);
433 hr_utility.raise_error;
434 END IF;
435 CLOSE csr_org_id;
436 EXCEPTION
437 WHEN OTHERS THEN
438 IF csr_org_id%ISOPEN THEN
439 CLOSE csr_org_id;
440 END IF;
441 RAISE;
442 END check_organization;
443 --
444 --------------------------------------------------------------------------
445 -- --
446 -- Name : CHECK_ORG_CLASS --
447 -- Type : Procedure --
448 -- Access : Public --
449 -- Description : The function checks IF org classIFication is as per --
450 -- the classIFication passed as a parameter --
451 -- Parameters : --
452 -- IN : p_organization_id IN NUMBER --
453 -- : p_classIFication IN VARCHAR2 --
454 -- --
455 -- Change History : --
456 --------------------------------------------------------------------------
457 -- Rev# Date Userid Description --
458 --------------------------------------------------------------------------
459 -- 1.0 30/12/02 statkar Created this function --
460 ---------------------------------------------------------------------------
461 PROCEDURE check_org_class
462 (p_organization_id IN NUMBER
463 ,p_classIFication IN VARCHAR2
464 )
465 IS
466
467 CURSOR csr_org_class (p_organization_id number, p_classfication VARCHAR2) IS
468 SELECT hrg.org_information_id
469 FROM hr_organization_information hrg
470 WHERE hrg.organization_id = p_organization_id
471 AND hrg.org_information_context = p_classIFication;
472
473 l_org_info_id hr_organization_information.org_information_id%type;
474
475 BEGIN
476
477 OPEN csr_org_class(p_organization_id, p_classIFication);
478 FETCH csr_org_class into l_org_info_id;
479 IF csr_org_class%NOTFOUND THEN
480 CLOSE csr_org_class;
481 hr_utility.set_message(800,'HR_374604_INVALID_ORG_CLASS');
482 hr_utility.set_message_token('ORG',p_organization_id);
483 hr_utility.raise_error;
484 END IF;
485 CLOSE csr_org_class;
486 EXCEPTION
487 WHEN OTHERS THEN
488 IF csr_org_class%ISOPEN THEN
489 CLOSE csr_org_class;
490 END IF;
491 RAISE;
492
493 END check_org_class;
494 --
495 --------------------------------------------------------------------------
496 -- --
497 -- Name : CHECK_ORG_TYPE --
498 -- Type : Procedure --
499 -- Access : Public --
500 -- Description : The function checks IF org type is as per --
501 -- the type passed as a parameter --
502 -- Parameters : --
503 -- IN : p_organization_id IN NUMBER --
504 -- : p_type IN VARCHAR2 --
505 -- --
506 -- Change History : --
507 --------------------------------------------------------------------------
508 -- Rev# Date Userid Description --
509 --------------------------------------------------------------------------
510 -- 1.0 15/01/03 statkar Created this function --
511 ---------------------------------------------------------------------------
512 PROCEDURE check_org_type
513 (p_organization_id IN NUMBER
514 ,p_type IN VARCHAR2
515 )
516 IS
517
518 CURSOR csr_org_type (p_organization_id number, p_type VARCHAR2) IS
519 SELECT hrg.org_information_id
520 FROM hr_organization_information hrg
521 WHERE hrg.organization_id = p_organization_id
522 AND hrg.org_information1 = p_type
523 AND hrg.org_information2 = 'Y';
524
525 l_org_info_id hr_organization_information.org_information_id%type;
526
527 BEGIN
528
529 OPEN csr_org_type(p_organization_id, p_type);
530 FETCH csr_org_type into l_org_info_id;
531 IF csr_org_type%NOTFOUND THEN
532 CLOSE csr_org_type;
533 hr_utility.set_message(800,'HR_374604_INVALID_ORG_CLASS');
534 hr_utility.set_message_token('ORG',p_organization_id);
535 hr_utility.raise_error;
536 END IF;
537 CLOSE csr_org_type;
538 EXCEPTION
539 WHEN OTHERS THEN
540 IF csr_org_type%ISOPEN THEN
541 CLOSE csr_org_type;
542 END IF;
543 RAISE;
544
545 END check_org_type;
546
547 --
548 --------------------------------------------------------------------------
549 -- --
550 -- Name : CHECK_CIN --
551 -- Type : Procedure --
552 -- Access : Public --
553 -- Description : Procedure to validate the citizen identIFication num--
554 -- : CIN should be mandatory in case of Chinese EMP/APL --
555 -- Parameters : --
556 -- IN : p_business_group_id NUMBER --
557 -- p_national_identIFier VARCHAR2, --
558 -- p_person_type_id NUMBER, --
559 -- p_expatriate_indicator VARCHAR2, --
560 -- p_effective_date DATE, --
561 -- p_person_id NUMBER --
562 -- --
563 ---------------------------------------------------------------------------
564 -- 1.0 09/01/03 statkar Created this function --
565 -- 1.1 05/02/03 statkar Additional PTU checks (Bug 2782045) --
566 -- 1.2 14/03/03 statkar Bug 2902744 changes for PTU --
567 -- 1.3 01/07/11 jmarupil Bug 8615627 Added a condition to suppress --
568 -- national identifier check for global --
569 -- deployment --
570 ---------------------------------------------------------------------------
571 PROCEDURE check_cin
572 ( p_business_group_id NUMBER,
573 p_national_identIFier VARCHAR2,
574 p_person_type_id NUMBER,
575 p_expatriate_indicator VARCHAR2,
576 p_effective_date DATE,
577 p_person_id NUMBER
578 )
579 IS
580
581 l_proc VARCHAR2(50);
582 l_system_person_type per_person_types.system_person_type%TYPE;
583
584 BEGIN
585
586 l_proc := g_package||'check_cin';
587
588 IF p_person_type_id IS NULL THEN
589 --
590 -- This can happen only IF the procedure is called through EMPLOYEE/APPLICANT API.
591 -- But in such a case p_person_type_id will always be EMP/APL which is allowable
592 -- So only check for expat indicator
593 --
594 IF p_expatriate_indicator ='N' AND p_national_identIFier IS NULL THEN
595 hr_api.mandatory_arg_error
596 (p_api_name => l_proc,
597 p_argument => 'P_CITIZEN_IDENTIFICATION_NUMBER',
598 p_argument_value => p_national_identIFier
599 );
600 END IF;
601 --
602 ELSE
603 --
604 -- This happens only IF the procedure is called through PERSON API.
605 -- We have ensured in CHECK_PERSON that p_person_type_id would have a valid NOT NULL value
606 --
607 l_system_person_type := hr_person_type_usage_info.getsystempersontype (p_person_type_id);
608 --
609 -- ModIFied the 'LIKE' clause to read as 'IN' with explicit values. 10-Mar-2003 statkar.
610 --
611
612 IF hr_cn_api.chk_person_type(l_system_person_type)
613 THEN
614 --Bug 8615627 :suppressed the china national identifier check during global deployment
615 --
616 if per_per_bus.g_global_transfer_in_process is null
617 or per_per_bus.g_global_transfer_in_process = false then
618
619 IF p_expatriate_indicator ='N' AND p_national_identIFier IS NULL THEN
620 hr_api.mandatory_arg_error
621 (p_api_name => l_proc,
622 p_argument => 'P_CITIZEN_IDENTIFICATION_NUMBER',
623 p_argument_value => p_national_identIFier
624 );
625 END IF;
626 end if;
627 --
628 END IF;
629 END IF;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 RAISE;
634 END check_cin;
635
636
637 --
638 --------------------------------------------------------------------------
639 -- --
640 -- Name : CHECK_TAX_DEPENDENCE --
641 -- Type : Procedure --
642 -- Access : Public --
643 -- Description : Procedure to validate the tax depENDence on --
644 -- the exemption indicator. --
645 -- Exemption Indicator Tax Percentage --
646 -- N Should be NULL --
647 -- Y Should be valid % --
648 -- Parameters : --
649 -- IN : p_tax_exemption_indicator VARCHAR2 --
650 -- : p_percentage VARCHAR2 --
651 -- OUT : p_return_number number --
652 -- --
653 -- Change History : --
654 --------------------------------------------------------------------------
655 -- Rev# Date Userid Description --
656 --------------------------------------------------------------------------
657 -- 1.0 29/11/02 saikrish Created this procedure --
658 -- 1.1 20/01/03 saikrish Removed the check for value of --
659 -- Tax Exemption indicator as Y (Bug 2747251)--
660 --------------------------------------------------------------------------
661 PROCEDURE check_tax_depENDence
662 ( p_tax_exemption_indicator IN VARCHAR2
663 ,p_percentage IN NUMBER
664 ) IS
665
666 l_proc VARCHAR2(72);
667
668 BEGIN
669
670 l_proc := g_package||'check_tax_depENDence';
671
672 --
673 -- Validations IF the p_tax_exemption_indicator is NULL or 'N'
674 --
675 IF (p_tax_exemption_indicator IS NULL) or (p_tax_exemption_indicator = 'N') THEN
676 IF p_percentage IS NULL THEN
677 RETURN;
678 ELSE
679 hr_utility.set_message(800,'HR_374605_INV_TAX_DEPENDENCE');
680 hr_utility.raise_error;
681 END IF;
682 END IF;
683
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 RAISE;
688 END check_tax_depENDence;
689 --
690 ----------------------------------------------------------------------------
691 -- --
692 -- Name : CHECK_BUS_GRP --
693 -- Type : PROCEDURE --
694 -- Access : Public --
695 -- Description : Procedure to validate the Business Group --
696 -- --
697 -- Parameters : --
698 -- IN : p_business_group_id NUMBER --
699 -- p_legislation_code VARCHAR2 --
700 -- Change History : --
701 --------------------------------------------------------------------------
702 -- Rev# Date Userid Description --
703 --------------------------------------------------------------------------
704 -- 1.0 01/03/03 statkar Created this procedure --
705 --------------------------------------------------------------------------
706
707 PROCEDURE check_bus_grp (p_business_group_id IN NUMBER
708 ,p_legislation_code IN VARCHAR2
709 )
710 IS
711
712 CURSOR csr_bg IS
713 SELECT legislation_code
714 FROM per_business_groups pbg
715 WHERE pbg.business_group_id = p_business_group_id;
716 --
717 l_legislation_code per_business_groups.legislation_code%type;
718 BEGIN
719
720 OPEN csr_bg;
721 --
722 FETCH csr_bg
723 INTO l_legislation_code;
724 --
725 IF csr_bg%NOTFOUND THEN
726 CLOSE csr_bg;
727 hr_utility.set_message(800, 'HR_7208_API_BUS_GRP_INVALID');
728 hr_utility.raise_error;
729 END IF;
730 CLOSE csr_bg;
731 --
732 IF l_legislation_code <> p_legislation_code THEN
733 hr_utility.set_message(800, 'HR_7961_PER_BUS_GRP_INVALID');
734 hr_utility.set_message_token('LEG_CODE','CN');
735 hr_utility.raise_error;
736 END IF;
737 EXCEPTION
738 WHEN OTHERS THEN
739 IF csr_bg%ISOPEN THEN
740 CLOSE csr_bg;
741 END IF;
742 RAISE;
743
744 END check_bus_grp;
745
746
747 ----------------------------------------------------------------------------
748 -- --
749 -- Name : CHECK_PERSON --
750 -- Type : PROCEDURE --
751 -- Access : Public --
752 -- Description : Procedure to validate the Business Group --
753 -- --
754 -- Parameters : --
755 -- IN : p_person_id NUMBER --
756 -- p_legislation_code VARCHAR2 --
757 -- Change History : --
758 --------------------------------------------------------------------------
759 -- Rev# Date Userid Description --
760 --------------------------------------------------------------------------
761 -- 1.0 01/03/03 statkar Created this procedure --
762 ---------------------------------------------------------------------------
763 PROCEDURE check_person (p_person_id IN NUMBER
764 ,p_legislation_code IN VARCHAR2
765 ,p_effective_date IN DATE
766 )
767 IS
768 l_legislation_code per_business_groups.legislation_code%type;
769 --
770 CURSOR csr_emp_leg
771 (c_person_id per_people_f.person_id%TYPE,
772 c_effective_date DATE
773 )
774 IS
775 select bgp.legislation_code
776 from per_people_f per,
777 per_business_groups bgp
778 where per.business_group_id = bgp.business_group_id
779 and per.person_id = c_person_id
780 and c_effective_date between per.effective_start_date and per.effective_END_date;
781
782 BEGIN
783
784 OPEN csr_emp_leg(p_person_id, trunc(p_effective_date));
785 FETCH csr_emp_leg into l_legislation_code;
786 IF csr_emp_leg%notfound THEN
787 CLOSE csr_emp_leg;
788 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
789 hr_utility.raise_error;
790 END IF;
791 CLOSE csr_emp_leg;
792
793 --
794 -- Check that the legislation of the specIFied business group is 'CN'.
795 --
796 IF l_legislation_code <> p_legislation_code THEN
797 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
798 hr_utility.set_message_token('LEG_CODE','CN');
799 hr_utility.raise_error;
800 END IF;
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 IF csr_emp_leg%ISOPEN THEN
805 CLOSE csr_emp_leg;
806 END IF;
807 RAISE;
808
809 END check_person;
810
811 ----------------------------------------------------------------------------
812 -- --
813 -- Name : CHECK_ADDRESS --
814 -- Type : PROCEDURE --
815 -- Access : Public --
816 -- Description : Procedure to validate the Business Group --
817 -- --
818 -- Parameters : --
819 -- IN : p_address_id NUMBER --
820 -- p_address_style VARCHAR2 --
821 -- Change History : --
822 --------------------------------------------------------------------------
823 -- Rev# Date Userid Description --
824 --------------------------------------------------------------------------
825 -- 1.0 01/03/03 statkar Created this procedure --
826 ---------------------------------------------------------------------------
827 PROCEDURE check_address (p_address_id IN NUMBER
828 ,p_address_style IN VARCHAR2
829 )
830 IS
831
832 l_style per_addresses.style%TYPE;
833 --
834 CURSOR csr_add_style IS
835 SELECT style
836 FROM per_addresses
837 WHERE address_id = p_address_id;
838 --
839 BEGIN
840
841 OPEN csr_add_style;
842 FETCH csr_add_style
843 INTO l_style;
844 IF csr_add_style%notfound THEN
845 CLOSE csr_add_style;
846 --
847 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
848 hr_utility.raise_error;
849 END IF;
850 --
851 CLOSE csr_add_style;
852 --
853 IF l_style <> p_address_style THEN
854 hr_utility.set_message(801, 'HR_52505_INV_ADDRESS_STYLE');
855 hr_utility.raise_error;
856 END IF;
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 IF csr_add_style%ISOPEN THEN
861 CLOSE csr_add_style;
862 END IF;
863 RAISE;
864 END check_address;
865
866
867 ----------------------------------------------------------------------------
868 -- --
869 -- Name : CHECK_ASSIGNMENT --
870 -- Type : PROCEDURE --
871 -- Access : Public --
872 -- Description : Procedure to validate the Assignment --
873 -- --
874 -- Parameters : --
875 -- IN : p_assignment_id NUMBER --
876 -- p_legislation_code VARCHAR2 --
877 -- p_effective_date DATE --
878 -- Change History : --
879 --------------------------------------------------------------------------
880 -- Rev# Date Userid Description --
881 --------------------------------------------------------------------------
882 -- 1.0 01/03/03 statkar Created this procedure --
883 ---------------------------------------------------------------------------
884 PROCEDURE check_assignment
885 (p_assignment_id IN NUMBER
886 ,p_legislation_code IN VARCHAR2
887 ,p_effective_date IN DATE
888 )
889 IS
890 --
891 CURSOR legsel (p_assignment_id IN NUMBER, p_effective_date IN DATE) IS
892 SELECT pbg.legislation_code
893 FROM per_business_groups pbg,
894 per_assignments_f asg
895 WHERE pbg.business_group_id = asg.business_group_id
896 AND asg.assignment_id = p_assignment_id
897 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_END_date;
898 --
899 l_legislation_code per_business_groups.legislation_code%type;
900 --
901 BEGIN
902
903 OPEN legsel(p_assignment_id, trunc(p_effective_date));
904 FETCH legsel
905 INTO l_legislation_code;
906 --
907 -- bug 2748967 modIFications start
908 IF legsel%FOUND AND l_legislation_code = p_legislation_code THEN
909 CLOSE legsel;
910 RETURN;
911 END IF;
912 -- bug 2748967 modIFications END
913
914 IF legsel%notfound THEN
915 CLOSE legsel;
916 hr_utility.set_message(800, 'HR_7348_PPM_ASSIGNMENT_INVALID');
917 hr_utility.raise_error;
918 END IF;
919
920 IF legsel%found AND l_legislation_code <> p_legislation_code THEN
921 CLOSE legsel;
922 hr_utility.set_message(801, 'HR_374601_BUS_GRP_INVALID');
923 hr_utility.raise_error;
924 END IF;
925
926 --
927 CLOSE legsel;
928 EXCEPTION
929 WHEN OTHERS THEN
930 IF legsel%ISOPEN THEN
931 CLOSE legsel;
932 END IF;
933 RAISE;
934
935 END check_assignment;
936
937 ----------------------------------------------------------------------------
938 -- --
939 -- Name : CHECK_PAYMENT_METHOD --
940 -- Type : PROCEDURE --
941 -- Access : Public --
942 -- Description : Procedure to validate the Payment Method --
943 -- --
944 -- Parameters : --
945 -- IN : p_payment_method_id NUMBER --
946 -- p_legislation_code VARCHAR2 --
947 -- p_effective_date DATE --
948 -- Change History : --
949 --------------------------------------------------------------------------
950 -- Rev# Date Userid Description --
951 --------------------------------------------------------------------------
952 -- 1.0 01/03/03 statkar Created this procedure --
953 ---------------------------------------------------------------------------
954 PROCEDURE check_payment_method
955 ( p_personal_payment_method_id IN NUMBER
956 ,p_effective_date IN DATE
957 , p_legislation_code IN VARCHAR2
958 )
959 IS
960
961 --
962 CURSOR legsel IS
963 SELECT pbg.legislation_code
964 FROM per_business_groups pbg,
965 pay_personal_payment_methods_f ppm
966 WHERE pbg.business_group_id = ppm.business_group_id
967 AND ppm.personal_payment_method_id = p_personal_payment_method_id
968 AND p_effective_date BETWEEN ppm.effective_start_date AND ppm.effective_END_date;
969 --
970 l_legislation_code per_business_groups.legislation_code%type;
971
972 BEGIN
973
974 OPEN legsel;
975 FETCH legsel
976 INTO l_legislation_code;
977 --
978 IF legsel%notfound THEN
979 CLOSE legsel;
980 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
981 hr_utility.raise_error;
982 END IF;
983
984 IF legsel%found AND l_legislation_code <> p_legislation_code THEN
985 CLOSE legsel;
986 hr_utility.set_message(801, 'HR_374601_BUS_GRP_INVALID');
987 hr_utility.raise_error;
988 END IF;
989 --
990 CLOSE legsel;
991 --
992 EXCEPTION
993 WHEN OTHERS THEN
994 IF legsel%ISOPEN THEN
995 CLOSE legsel;
996 END IF;
997 RAISE;
998
999 END check_payment_method;
1000
1001 --------------------------------------------------------------------------
1002 -- --
1003 -- Name : GET_PAY_MESSAGE --
1004 -- Type : FUNCTION --
1005 -- Access : Public --
1006 -- Description : Function to construct the message for FF --
1007 -- This function is used to obtain a message. --
1008 -- The token parameters must be of the form --
1009 -- 'TOKEN_NAME:TOKEN_VALUE' i.e. --
1010 -- If you want to set the value of a token called --
1011 -- FUNCTION to CN_PHF_CALCULATION the token parameter --
1012 -- would be 'FUNCTION:CN_PHF_CALCULATION' --
1013 -- --
1014 -- Parameters : --
1015 -- IN : p_message_name VARCHAR2 --
1016 -- p_token1 VARCHAR2 --
1017 -- p_token2 VARCHAR2 --
1018 -- p_token3 VARCHAR2 --
1019 -- p_token4 VARCHAR2 --
1020 -- RETURN : VARCHAR2 --
1021 -- --
1022 -- Change History : --
1023 --------------------------------------------------------------------------
1024 -- Rev# Date Userid Description --
1025 --------------------------------------------------------------------------
1026 -- 1.0 20-May-03 statkar Created this function --
1027 -- 1.1 09-JUL-03 sshankar Added fifth parameter p_token4, --
1028 -- Bug 3038642 --
1029 -- 1.2 14-Jul-03 statkar Bug 3047273. Changed the application_id --
1030 -- from 801 to 800 --
1031 -- 1.3 21-Oct-03 saikrish Bug 3198695. Truncated token value length --
1032 -- to 77 characters due to issues in psuedo --
1033 -- translated environment --
1034 ---------------------------------------------------------------------------
1035 FUNCTION get_pay_message
1036 (p_message_name IN VARCHAR2
1037 ,p_token1 IN VARCHAR2 DEFAULT NULL
1038 ,p_token2 IN VARCHAR2 DEFAULT NULL
1039 ,p_token3 IN VARCHAR2 DEFAULT NULL
1040 ,p_token4 IN VARCHAR2 DEFAULT NULL -- Bug 3038642
1041 )
1042 RETURN VARCHAR2
1043
1044 IS
1045 l_message VARCHAR2(2000);
1046 l_token_name VARCHAR2(20);
1047 l_token_value VARCHAR2(80);
1048 l_colon_position NUMBER;
1049 l_proc VARCHAR2(50);
1050 --
1051 BEGIN
1052
1053 l_proc := 'hr_cn_api.get_pay_message';
1054
1055 --
1056 set_location('Entered '||l_proc,5);
1057 set_location('. Message Name: '||p_message_name,40);
1058 --
1059 -- Bug 3047273 Changed to 800 from 801
1060 --
1061 hr_utility.set_message(800,p_message_name);
1062 --
1063 -- Truncated the token value to 77 characters, bug 3198695
1064 --
1065 IF p_token1 IS NOT NULL THEN
1066 /* Obtain token 1 name and value */
1067 l_colon_position := INSTR(p_token1,':');
1068 l_token_name := SUBSTR(p_token1,1,l_colon_position-1);
1069 l_token_value := SUBSTR(SUBSTR(p_token1,l_colon_position+1,LENGTH(p_token1)) ,1,77);
1070 hr_utility.set_message_token(l_token_name,l_token_value);
1071 set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
1072 END IF;
1073
1074 --
1075 -- Truncated the token value to 77 characters, bug 3198695
1076 --
1077 IF p_token2 IS NOT NULL THEN
1078 /* Obtain token 2 name and value */
1079 l_colon_position := INSTR(p_token2,':');
1080 l_token_name := SUBSTR(p_token2,1,l_colon_position-1);
1081 l_token_value := SUBSTR(SUBSTR(p_token2,l_colon_position+1,LENGTH(p_token2)) ,1,77);
1082 hr_utility.set_message_token(l_token_name,l_token_value);
1083 set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
1084 END IF;
1085
1086 --
1087 -- Truncated the token value to 77 characters, bug 3198695
1088 --
1089 IF p_token3 IS NOT NULL THEN
1090 /* Obtain token 3 name and value */
1091 l_colon_position := INSTR(p_token3,':');
1092 l_token_name := SUBSTR(p_token3,1,l_colon_position-1);
1093 l_token_value := SUBSTR(SUBSTR(p_token3,l_colon_position+1,LENGTH(p_token3)) ,1,77);
1094 hr_utility.set_message_token(l_token_name,l_token_value);
1095 set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
1096 END IF;
1097
1098 --
1099 -- Added code to accomdate fourth parameter.
1100 -- Modified by sshankar.
1101 -- Bug 3038642
1102 -- Start Bug 3038642
1103 --
1104 --
1105 -- Truncated the token value to 77 characters, bug 3198695
1106 --
1107 IF p_token4 IS NOT NULL THEN
1108 /* Obtain token 4 name and value */
1109 l_colon_position := INSTR(p_token4,':');
1110 l_token_name := SUBSTR(p_token4,1,l_colon_position-1);
1111 l_token_value := SUBSTR(SUBSTR(p_token4,l_colon_position+1,LENGTH(p_token4)) ,1,77);
1112 hr_utility.set_message_token(l_token_name,l_token_value);
1113 set_location('. Token4: '||l_token_name||'. Value: '||l_token_value,80);
1114 END IF;
1115
1116 --
1117 -- End Bug 3038642
1118 --
1119
1120 l_message := SUBSTRB(hr_utility.get_message,1,250);
1121
1122 set_location('leaving '||l_proc,100);
1123 RETURN l_message;
1124
1125 END get_pay_message;
1126
1127 --------------------------------------------------------------------------
1128 -- --
1129 -- Name : GET_USER_TABLE_VALUE --
1130 -- Type : FUNCTION --
1131 -- Access : Public --
1132 -- Description : Function to fetch the user table value --
1133 -- --
1134 -- Parameters : --
1135 -- IN : p_business_group_id NUMBER --
1136 -- p_table_name VARCHAR2 --
1137 -- p_column_name VARCHAR2 --
1138 -- p_row_name VARCHAR2 --
1139 -- p_row_value VARCHAR2 --
1140 -- RETURN : VARCHAR2 --
1141 -- OUT : p_message VARCHAR2 --
1142 -- --
1143 -- Change History : --
1144 --------------------------------------------------------------------------
1145 -- Rev# Date Userid Description --
1146 --------------------------------------------------------------------------
1147 -- 1.0 20-May-03 statkar Created this function --
1148 ---------------------------------------------------------------------------
1149 FUNCTION get_user_table_value
1150 (p_business_group_id IN NUMBER
1151 ,p_table_name IN VARCHAR2
1152 ,p_column_name IN VARCHAR2
1153 ,p_row_name IN VARCHAR2
1154 ,p_row_value IN VARCHAR2
1155 ,p_effective_date IN DATE
1156 ,p_message OUT NOCOPY VARCHAR2
1157 )
1158 RETURN VARCHAR2
1159 IS
1160 l_value pay_user_column_instances_f.value%TYPE;
1161
1162 BEGIN
1163
1164 l_value := hruserdt.get_table_value
1165 ( p_bus_group_id => p_business_group_id
1166 ,p_table_name => p_table_name
1167 ,p_col_name => p_column_name
1168 ,p_row_value => p_row_value
1169 ,p_effective_date => p_effective_date
1170 );
1171
1172 p_message := 'SUCCESS';
1173 RETURN l_value;
1174
1175 EXCEPTION
1176 WHEN NO_DATA_FOUND THEN
1177 p_message := hr_cn_api.get_pay_message
1178 (
1179 p_message_name => 'HR_374614_TAX_DETAILS_MISSING'
1180 ,p_token1 => 'TABLE:'||p_table_name
1181 ,p_token2 => 'VARIABLE:'||p_row_name
1182 ,p_token3 => 'VALUE:'||p_row_value
1183 );
1184 RETURN NULL;
1185
1186 END get_user_table_value;
1187
1188 --------------------------------------------------------------------------
1189 -- --
1190 -- Name : GET_DFF_TL_VALUE --
1191 -- Type : FUNCTION --
1192 -- Access : Public --
1193 -- Description : Function to fetch the translated value --
1194 -- --
1195 -- Parameters : --
1196 -- IN : p_column_name VARCHAR2 --
1197 -- p_dff VARCHAR2 --
1198 -- p_dff_context_code VARCHAR2 --
1199 -- RETURN : VARCHAR2 --
1200 -- --
1201 -- Change History : --
1202 --------------------------------------------------------------------------
1203 -- Rev# Date Userid Description --
1204 --------------------------------------------------------------------------
1205 -- 1.0 03-Jul-03 saikrish Created this function --
1206 -- 1.1 09-Mar-06 rpalli Modified cursor cur_prompt to resolve --
1207 -- R12 perf issues. Bug 5043303 --
1208 --------------------------------------------------------------------------
1209 FUNCTION get_dff_tl_value(p_column_name IN VARCHAR2
1210 ,p_dff IN VARCHAR2
1211 ,p_dff_context_code IN VARCHAR2
1212 )
1213 RETURN VARCHAR2 IS
1214
1215 CURSOR cur_prompt IS
1216 select t.form_left_prompt
1217 from fnd_descr_flex_col_usage_tl t,
1218 fnd_descr_flex_column_usages b
1219 where t.application_id in (800, 801)
1220 and b.application_id = t.application_id
1221 and t.descriptive_flexfield_name = b.descriptive_flexfield_name
1222 and b.descriptive_flexfield_name = p_dff
1223 and b.descriptive_flex_context_code = t.descriptive_flex_context_code
1224 and b.descriptive_flex_context_code = p_dff_context_code
1225 and b.application_column_name = t.application_column_name
1226 and b.end_user_column_name = p_column_name
1227 and t.language = userenv('LANG');
1228
1229 l_prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
1230
1231 BEGIN
1232
1233 OPEN cur_prompt;
1234 FETCH cur_prompt INTO l_prompt;
1235 CLOSE cur_prompt;
1236
1237 RETURN l_prompt;
1238
1239 END get_dff_tl_value;
1240
1241 --------------------------------------------------------------------------
1242 -- --
1243 -- Name : RAISE_MESSAGE --
1244 -- Type : PROCEDURE --
1245 -- Access : Public --
1246 -- Description : Procedure to raise the error message --
1247 -- --
1248 -- Parameters : --
1249 -- IN : p_application_id NUMBER --
1250 -- p_message_name VARCHAR2 --
1251 -- p_token_name HR_CN_API.CHAR_TAB_TYPE --
1252 -- p_token_value HR_CN_API.CHAR_TAB_TYPE --
1253 -- --
1254 -- Change History : --
1255 --------------------------------------------------------------------------
1256 -- Rev# Date Userid Description --
1257 --------------------------------------------------------------------------
1258 -- 1.0 15-Mar-05 snekkala Created this procedure --
1259 --------------------------------------------------------------------------
1260 PROCEDURE raise_message(p_application_id IN NUMBER
1261 ,p_message_name IN VARCHAR2
1262 ,p_token_name IN OUT NOCOPY hr_cn_api.char_tab_type
1263 ,p_token_value IN OUT NOCOPY hr_cn_api.char_tab_type
1264 )IS
1265 cnt NUMBER;
1266 BEGIN
1267
1268 IF p_message_name IS NOT NULL AND p_message_name <> 'SUCCESS' THEN
1269 cnt:= p_token_name.count;
1270 hr_utility.set_message(p_application_id, p_message_name);
1271 FOR i IN 1..cnt
1272 LOOP
1273 hr_utility.set_message_token(p_token_name(i),p_token_value(i));
1274 END LOOP;
1275 hr_utility.raise_error;
1276 END IF;
1277
1278 END raise_message;
1279
1280 --------------------------------------------------------------------------
1281 -- --
1282 -- Name : get_class_tl_name --
1283 -- Type : FUNCTION --
1284 -- Access : Public --
1285 -- Description : Function to raise the error message --
1286 -- --
1287 -- Parameters : --
1288 -- IN : p_classification_name VARCHAR2 --
1289 -- RETURN : VARCHAR2 --
1290 -- --
1291 -- Change History : --
1292 --------------------------------------------------------------------------
1293 -- Rev# Date Userid Description --
1294 --------------------------------------------------------------------------
1295 -- 1.0 12-May-06 lnagaraj Created this procedure --
1296 --------------------------------------------------------------------------
1297
1298 FUNCTION get_class_tl_name(p_classification_name IN VARCHAR2)
1299 RETURN VARCHAR2
1300 IS
1301 l_trans_name pay_element_classifications_tl.classification_name%TYPE;
1302
1303 CURSOR csr_translated_value
1304 IS
1305 SELECT pectl.classification_name
1306 FROM pay_element_classifications_tl pectl,
1307 pay_element_classifications pec
1308 WHERE pec.classification_id = pectl.classification_id
1309 AND pectl.language =USERENV('LANG')
1310 AND pec.classification_name = DECODE(p_classification_name,'Voluntary Dedn','Voluntary Deductions',p_classification_name)
1311 AND pec.legislation_code='CN';
1312
1313 BEGIN
1314 OPEN csr_translated_value;
1315 FETCH csr_translated_value INTO l_trans_name;
1316 CLOSE csr_translated_value;
1317
1318 RETURN l_trans_name ;
1319
1320 END get_class_tl_name;
1321
1322
1323 BEGIN
1324
1325 g_package := 'hr_cn_api.';
1326
1327 END hr_cn_api;
[Home] [Help]