1 PACKAGE BODY hr_cn_api AS
2 /* $Header: hrcnapi.pkb 120.3 2006/10/31 06:44:34 abhjain noship $ */
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 ---------------------------------------------------------------------------
568 PROCEDURE check_cin
569 ( p_business_group_id NUMBER,
570 p_national_identIFier VARCHAR2,
571 p_person_type_id NUMBER,
572 p_expatriate_indicator VARCHAR2,
573 p_effective_date DATE,
574 p_person_id NUMBER
575 )
576 IS
577
578 l_proc VARCHAR2(50);
579 l_system_person_type per_person_types.system_person_type%TYPE;
580
581 BEGIN
582
583 l_proc := g_package||'check_cin';
584
585 IF p_person_type_id IS NULL THEN
586 --
587 -- This can happen only IF the procedure is called through EMPLOYEE/APPLICANT API.
588 -- But in such a case p_person_type_id will always be EMP/APL which is allowable
589 -- So only check for expat indicator
590 --
591 IF p_expatriate_indicator ='N' AND p_national_identIFier IS NULL THEN
592 hr_api.mandatory_arg_error
593 (p_api_name => l_proc,
594 p_argument => 'P_CITIZEN_IDENTIFICATION_NUMBER',
595 p_argument_value => p_national_identIFier
596 );
597 END IF;
598 --
599 ELSE
600 --
601 -- This happens only IF the procedure is called through PERSON API.
602 -- We have ensured in CHECK_PERSON that p_person_type_id would have a valid NOT NULL value
603 --
604 l_system_person_type := hr_person_type_usage_info.getsystempersontype (p_person_type_id);
605 --
606 -- ModIFied the 'LIKE' clause to read as 'IN' with explicit values. 10-Mar-2003 statkar.
607 --
608
609 IF hr_cn_api.chk_person_type(l_system_person_type)
610 THEN
611 --
612 IF p_expatriate_indicator ='N' AND p_national_identIFier IS NULL THEN
613 hr_api.mandatory_arg_error
614 (p_api_name => l_proc,
615 p_argument => 'P_CITIZEN_IDENTIFICATION_NUMBER',
616 p_argument_value => p_national_identIFier
617 );
618 END IF;
619 --
620 END IF;
621 END IF;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 RAISE;
626 END check_cin;
627
628
629 --
630 --------------------------------------------------------------------------
631 -- --
632 -- Name : CHECK_TAX_DEPENDENCE --
633 -- Type : Procedure --
634 -- Access : Public --
635 -- Description : Procedure to validate the tax depENDence on --
636 -- the exemption indicator. --
637 -- Exemption Indicator Tax Percentage --
638 -- N Should be NULL --
639 -- Y Should be valid % --
640 -- Parameters : --
641 -- IN : p_tax_exemption_indicator VARCHAR2 --
642 -- : p_percentage VARCHAR2 --
643 -- OUT : p_return_number number --
644 -- --
645 -- Change History : --
646 --------------------------------------------------------------------------
647 -- Rev# Date Userid Description --
648 --------------------------------------------------------------------------
649 -- 1.0 29/11/02 saikrish Created this procedure --
650 -- 1.1 20/01/03 saikrish Removed the check for value of --
651 -- Tax Exemption indicator as Y (Bug 2747251)--
652 --------------------------------------------------------------------------
653 PROCEDURE check_tax_depENDence
654 ( p_tax_exemption_indicator IN VARCHAR2
655 ,p_percentage IN NUMBER
656 ) IS
657
658 l_proc VARCHAR2(72);
659
660 BEGIN
661
662 l_proc := g_package||'check_tax_depENDence';
663
664 --
665 -- Validations IF the p_tax_exemption_indicator is NULL or 'N'
666 --
667 IF (p_tax_exemption_indicator IS NULL) or (p_tax_exemption_indicator = 'N') THEN
668 IF p_percentage IS NULL THEN
669 RETURN;
670 ELSE
671 hr_utility.set_message(800,'HR_374605_INV_TAX_DEPENDENCE');
672 hr_utility.raise_error;
673 END IF;
674 END IF;
675
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 RAISE;
680 END check_tax_depENDence;
681 --
682 ----------------------------------------------------------------------------
683 -- --
684 -- Name : CHECK_BUS_GRP --
685 -- Type : PROCEDURE --
686 -- Access : Public --
687 -- Description : Procedure to validate the Business Group --
688 -- --
689 -- Parameters : --
690 -- IN : p_business_group_id NUMBER --
691 -- p_legislation_code VARCHAR2 --
692 -- Change History : --
693 --------------------------------------------------------------------------
694 -- Rev# Date Userid Description --
695 --------------------------------------------------------------------------
696 -- 1.0 01/03/03 statkar Created this procedure --
697 --------------------------------------------------------------------------
698
699 PROCEDURE check_bus_grp (p_business_group_id IN NUMBER
700 ,p_legislation_code IN VARCHAR2
701 )
702 IS
703
704 CURSOR csr_bg IS
705 SELECT legislation_code
706 FROM per_business_groups pbg
707 WHERE pbg.business_group_id = p_business_group_id;
708 --
709 l_legislation_code per_business_groups.legislation_code%type;
710 BEGIN
711
712 OPEN csr_bg;
713 --
714 FETCH csr_bg
715 INTO l_legislation_code;
716 --
717 IF csr_bg%NOTFOUND THEN
718 CLOSE csr_bg;
719 hr_utility.set_message(800, 'HR_7208_API_BUS_GRP_INVALID');
720 hr_utility.raise_error;
721 END IF;
722 CLOSE csr_bg;
723 --
724 IF l_legislation_code <> p_legislation_code THEN
725 hr_utility.set_message(800, 'HR_7961_PER_BUS_GRP_INVALID');
726 hr_utility.set_message_token('LEG_CODE','CN');
727 hr_utility.raise_error;
728 END IF;
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF csr_bg%ISOPEN THEN
732 CLOSE csr_bg;
733 END IF;
734 RAISE;
735
736 END check_bus_grp;
737
738
739 ----------------------------------------------------------------------------
740 -- --
741 -- Name : CHECK_PERSON --
742 -- Type : PROCEDURE --
743 -- Access : Public --
744 -- Description : Procedure to validate the Business Group --
745 -- --
746 -- Parameters : --
747 -- IN : p_person_id NUMBER --
748 -- p_legislation_code VARCHAR2 --
749 -- Change History : --
750 --------------------------------------------------------------------------
751 -- Rev# Date Userid Description --
752 --------------------------------------------------------------------------
753 -- 1.0 01/03/03 statkar Created this procedure --
754 ---------------------------------------------------------------------------
755 PROCEDURE check_person (p_person_id IN NUMBER
756 ,p_legislation_code IN VARCHAR2
757 ,p_effective_date IN DATE
758 )
759 IS
760 l_legislation_code per_business_groups.legislation_code%type;
761 --
762 CURSOR csr_emp_leg
763 (c_person_id per_people_f.person_id%TYPE,
764 c_effective_date DATE
765 )
766 IS
767 select bgp.legislation_code
768 from per_people_f per,
769 per_business_groups bgp
770 where per.business_group_id = bgp.business_group_id
771 and per.person_id = c_person_id
772 and c_effective_date between per.effective_start_date and per.effective_END_date;
773
774 BEGIN
775
776 OPEN csr_emp_leg(p_person_id, trunc(p_effective_date));
777 FETCH csr_emp_leg into l_legislation_code;
778 IF csr_emp_leg%notfound THEN
779 CLOSE csr_emp_leg;
780 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
781 hr_utility.raise_error;
782 END IF;
783 CLOSE csr_emp_leg;
784
785 --
786 -- Check that the legislation of the specIFied business group is 'CN'.
787 --
788 IF l_legislation_code <> p_legislation_code THEN
789 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
790 hr_utility.set_message_token('LEG_CODE','CN');
791 hr_utility.raise_error;
792 END IF;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 IF csr_emp_leg%ISOPEN THEN
797 CLOSE csr_emp_leg;
798 END IF;
799 RAISE;
800
801 END check_person;
802
803 ----------------------------------------------------------------------------
804 -- --
805 -- Name : CHECK_ADDRESS --
806 -- Type : PROCEDURE --
807 -- Access : Public --
808 -- Description : Procedure to validate the Business Group --
809 -- --
810 -- Parameters : --
811 -- IN : p_address_id NUMBER --
812 -- p_address_style VARCHAR2 --
813 -- Change History : --
814 --------------------------------------------------------------------------
815 -- Rev# Date Userid Description --
816 --------------------------------------------------------------------------
817 -- 1.0 01/03/03 statkar Created this procedure --
818 ---------------------------------------------------------------------------
819 PROCEDURE check_address (p_address_id IN NUMBER
820 ,p_address_style IN VARCHAR2
821 )
822 IS
823
824 l_style per_addresses.style%TYPE;
825 --
826 CURSOR csr_add_style IS
827 SELECT style
828 FROM per_addresses
829 WHERE address_id = p_address_id;
830 --
831 BEGIN
832
833 OPEN csr_add_style;
834 FETCH csr_add_style
835 INTO l_style;
836 IF csr_add_style%notfound THEN
837 CLOSE csr_add_style;
838 --
839 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
840 hr_utility.raise_error;
841 END IF;
842 --
843 CLOSE csr_add_style;
844 --
845 IF l_style <> p_address_style THEN
846 hr_utility.set_message(801, 'HR_52505_INV_ADDRESS_STYLE');
847 hr_utility.raise_error;
848 END IF;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 IF csr_add_style%ISOPEN THEN
853 CLOSE csr_add_style;
854 END IF;
855 RAISE;
856 END check_address;
857
858
859 ----------------------------------------------------------------------------
860 -- --
861 -- Name : CHECK_ASSIGNMENT --
862 -- Type : PROCEDURE --
863 -- Access : Public --
864 -- Description : Procedure to validate the Assignment --
865 -- --
866 -- Parameters : --
867 -- IN : p_assignment_id NUMBER --
868 -- p_legislation_code VARCHAR2 --
869 -- p_effective_date DATE --
870 -- Change History : --
871 --------------------------------------------------------------------------
872 -- Rev# Date Userid Description --
873 --------------------------------------------------------------------------
874 -- 1.0 01/03/03 statkar Created this procedure --
875 ---------------------------------------------------------------------------
876 PROCEDURE check_assignment
877 (p_assignment_id IN NUMBER
878 ,p_legislation_code IN VARCHAR2
879 ,p_effective_date IN DATE
880 )
881 IS
882 --
883 CURSOR legsel (p_assignment_id IN NUMBER, p_effective_date IN DATE) IS
884 SELECT pbg.legislation_code
885 FROM per_business_groups pbg,
886 per_assignments_f asg
887 WHERE pbg.business_group_id = asg.business_group_id
888 AND asg.assignment_id = p_assignment_id
889 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_END_date;
890 --
891 l_legislation_code per_business_groups.legislation_code%type;
892 --
893 BEGIN
894
895 OPEN legsel(p_assignment_id, trunc(p_effective_date));
896 FETCH legsel
897 INTO l_legislation_code;
898 --
899 -- bug 2748967 modIFications start
900 IF legsel%FOUND AND l_legislation_code = p_legislation_code THEN
901 CLOSE legsel;
902 RETURN;
903 END IF;
904 -- bug 2748967 modIFications END
905
906 IF legsel%notfound THEN
907 CLOSE legsel;
908 hr_utility.set_message(800, 'HR_7348_PPM_ASSIGNMENT_INVALID');
909 hr_utility.raise_error;
910 END IF;
911
912 IF legsel%found AND l_legislation_code <> p_legislation_code THEN
913 CLOSE legsel;
914 hr_utility.set_message(801, 'HR_374601_BUS_GRP_INVALID');
915 hr_utility.raise_error;
916 END IF;
917
918 --
919 CLOSE legsel;
920 EXCEPTION
921 WHEN OTHERS THEN
922 IF legsel%ISOPEN THEN
923 CLOSE legsel;
924 END IF;
925 RAISE;
926
927 END check_assignment;
928
929 ----------------------------------------------------------------------------
930 -- --
931 -- Name : CHECK_PAYMENT_METHOD --
932 -- Type : PROCEDURE --
933 -- Access : Public --
934 -- Description : Procedure to validate the Payment Method --
935 -- --
936 -- Parameters : --
937 -- IN : p_payment_method_id NUMBER --
938 -- p_legislation_code VARCHAR2 --
939 -- p_effective_date DATE --
940 -- Change History : --
941 --------------------------------------------------------------------------
942 -- Rev# Date Userid Description --
943 --------------------------------------------------------------------------
944 -- 1.0 01/03/03 statkar Created this procedure --
945 ---------------------------------------------------------------------------
946 PROCEDURE check_payment_method
947 ( p_personal_payment_method_id IN NUMBER
948 ,p_effective_date IN DATE
949 , p_legislation_code IN VARCHAR2
950 )
951 IS
952
953 --
954 CURSOR legsel IS
955 SELECT pbg.legislation_code
956 FROM per_business_groups pbg,
957 pay_personal_payment_methods_f ppm
958 WHERE pbg.business_group_id = ppm.business_group_id
959 AND ppm.personal_payment_method_id = p_personal_payment_method_id
960 AND p_effective_date BETWEEN ppm.effective_start_date AND ppm.effective_END_date;
961 --
962 l_legislation_code per_business_groups.legislation_code%type;
963
964 BEGIN
965
966 OPEN legsel;
967 FETCH legsel
968 INTO l_legislation_code;
969 --
970 IF legsel%notfound THEN
971 CLOSE legsel;
972 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
973 hr_utility.raise_error;
974 END IF;
975
976 IF legsel%found AND l_legislation_code <> p_legislation_code THEN
977 CLOSE legsel;
978 hr_utility.set_message(801, 'HR_374601_BUS_GRP_INVALID');
979 hr_utility.raise_error;
980 END IF;
981 --
982 CLOSE legsel;
983 --
984 EXCEPTION
985 WHEN OTHERS THEN
986 IF legsel%ISOPEN THEN
987 CLOSE legsel;
988 END IF;
989 RAISE;
990
991 END check_payment_method;
992
993 --------------------------------------------------------------------------
994 -- --
995 -- Name : GET_PAY_MESSAGE --
996 -- Type : FUNCTION --
997 -- Access : Public --
998 -- Description : Function to construct the message for FF --
999 -- This function is used to obtain a message. --
1000 -- The token parameters must be of the form --
1001 -- 'TOKEN_NAME:TOKEN_VALUE' i.e. --
1002 -- If you want to set the value of a token called --
1003 -- FUNCTION to CN_PHF_CALCULATION the token parameter --
1004 -- would be 'FUNCTION:CN_PHF_CALCULATION' --
1005 -- --
1006 -- Parameters : --
1007 -- IN : p_message_name VARCHAR2 --
1008 -- p_token1 VARCHAR2 --
1009 -- p_token2 VARCHAR2 --
1010 -- p_token3 VARCHAR2 --
1011 -- p_token4 VARCHAR2 --
1012 -- RETURN : VARCHAR2 --
1013 -- --
1014 -- Change History : --
1015 --------------------------------------------------------------------------
1016 -- Rev# Date Userid Description --
1017 --------------------------------------------------------------------------
1018 -- 1.0 20-May-03 statkar Created this function --
1019 -- 1.1 09-JUL-03 sshankar Added fifth parameter p_token4, --
1020 -- Bug 3038642 --
1021 -- 1.2 14-Jul-03 statkar Bug 3047273. Changed the application_id --
1022 -- from 801 to 800 --
1023 -- 1.3 21-Oct-03 saikrish Bug 3198695. Truncated token value length --
1024 -- to 77 characters due to issues in psuedo --
1025 -- translated environment --
1026 ---------------------------------------------------------------------------
1027 FUNCTION get_pay_message
1028 (p_message_name IN VARCHAR2
1029 ,p_token1 IN VARCHAR2 DEFAULT NULL
1030 ,p_token2 IN VARCHAR2 DEFAULT NULL
1031 ,p_token3 IN VARCHAR2 DEFAULT NULL
1032 ,p_token4 IN VARCHAR2 DEFAULT NULL -- Bug 3038642
1033 )
1034 RETURN VARCHAR2
1035
1036 IS
1037 l_message VARCHAR2(2000);
1038 l_token_name VARCHAR2(20);
1039 l_token_value VARCHAR2(80);
1040 l_colon_position NUMBER;
1041 l_proc VARCHAR2(50);
1042 --
1043 BEGIN
1044
1045 l_proc := 'hr_cn_api.get_pay_message';
1046
1047 --
1048 set_location('Entered '||l_proc,5);
1049 set_location('. Message Name: '||p_message_name,40);
1050 --
1051 -- Bug 3047273 Changed to 800 from 801
1052 --
1053 hr_utility.set_message(800,p_message_name);
1054 --
1055 -- Truncated the token value to 77 characters, bug 3198695
1056 --
1057 IF p_token1 IS NOT NULL THEN
1058 /* Obtain token 1 name and value */
1059 l_colon_position := INSTR(p_token1,':');
1060 l_token_name := SUBSTR(p_token1,1,l_colon_position-1);
1061 l_token_value := SUBSTR(SUBSTR(p_token1,l_colon_position+1,LENGTH(p_token1)) ,1,77);
1062 hr_utility.set_message_token(l_token_name,l_token_value);
1063 set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
1064 END IF;
1065
1066 --
1067 -- Truncated the token value to 77 characters, bug 3198695
1068 --
1069 IF p_token2 IS NOT NULL THEN
1070 /* Obtain token 2 name and value */
1071 l_colon_position := INSTR(p_token2,':');
1072 l_token_name := SUBSTR(p_token2,1,l_colon_position-1);
1073 l_token_value := SUBSTR(SUBSTR(p_token2,l_colon_position+1,LENGTH(p_token2)) ,1,77);
1074 hr_utility.set_message_token(l_token_name,l_token_value);
1075 set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
1076 END IF;
1077
1078 --
1079 -- Truncated the token value to 77 characters, bug 3198695
1080 --
1081 IF p_token3 IS NOT NULL THEN
1082 /* Obtain token 3 name and value */
1083 l_colon_position := INSTR(p_token3,':');
1084 l_token_name := SUBSTR(p_token3,1,l_colon_position-1);
1085 l_token_value := SUBSTR(SUBSTR(p_token3,l_colon_position+1,LENGTH(p_token3)) ,1,77);
1086 hr_utility.set_message_token(l_token_name,l_token_value);
1087 set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
1088 END IF;
1089
1090 --
1091 -- Added code to accomdate fourth parameter.
1092 -- Modified by sshankar.
1093 -- Bug 3038642
1094 -- Start Bug 3038642
1095 --
1096 --
1097 -- Truncated the token value to 77 characters, bug 3198695
1098 --
1099 IF p_token4 IS NOT NULL THEN
1100 /* Obtain token 4 name and value */
1101 l_colon_position := INSTR(p_token4,':');
1102 l_token_name := SUBSTR(p_token4,1,l_colon_position-1);
1103 l_token_value := SUBSTR(SUBSTR(p_token4,l_colon_position+1,LENGTH(p_token4)) ,1,77);
1104 hr_utility.set_message_token(l_token_name,l_token_value);
1105 set_location('. Token4: '||l_token_name||'. Value: '||l_token_value,80);
1106 END IF;
1107
1108 --
1109 -- End Bug 3038642
1110 --
1111
1112 l_message := SUBSTRB(hr_utility.get_message,1,250);
1113
1114 set_location('leaving '||l_proc,100);
1115 RETURN l_message;
1116
1117 END get_pay_message;
1118
1119 --------------------------------------------------------------------------
1120 -- --
1121 -- Name : GET_USER_TABLE_VALUE --
1122 -- Type : FUNCTION --
1123 -- Access : Public --
1124 -- Description : Function to fetch the user table value --
1125 -- --
1126 -- Parameters : --
1127 -- IN : p_business_group_id NUMBER --
1128 -- p_table_name VARCHAR2 --
1129 -- p_column_name VARCHAR2 --
1130 -- p_row_name VARCHAR2 --
1131 -- p_row_value VARCHAR2 --
1132 -- RETURN : VARCHAR2 --
1133 -- OUT : p_message VARCHAR2 --
1134 -- --
1135 -- Change History : --
1136 --------------------------------------------------------------------------
1137 -- Rev# Date Userid Description --
1138 --------------------------------------------------------------------------
1139 -- 1.0 20-May-03 statkar Created this function --
1140 ---------------------------------------------------------------------------
1141 FUNCTION get_user_table_value
1142 (p_business_group_id IN NUMBER
1143 ,p_table_name IN VARCHAR2
1144 ,p_column_name IN VARCHAR2
1145 ,p_row_name IN VARCHAR2
1146 ,p_row_value IN VARCHAR2
1147 ,p_effective_date IN DATE
1148 ,p_message OUT NOCOPY VARCHAR2
1149 )
1150 RETURN VARCHAR2
1151 IS
1152 l_value pay_user_column_instances_f.value%TYPE;
1153
1154 BEGIN
1155
1156 l_value := hruserdt.get_table_value
1157 ( p_bus_group_id => p_business_group_id
1158 ,p_table_name => p_table_name
1159 ,p_col_name => p_column_name
1160 ,p_row_value => p_row_value
1161 ,p_effective_date => p_effective_date
1162 );
1163
1164 p_message := 'SUCCESS';
1165 RETURN l_value;
1166
1167 EXCEPTION
1168 WHEN NO_DATA_FOUND THEN
1169 p_message := hr_cn_api.get_pay_message
1170 (
1171 p_message_name => 'HR_374614_TAX_DETAILS_MISSING'
1172 ,p_token1 => 'TABLE:'||p_table_name
1173 ,p_token2 => 'VARIABLE:'||p_row_name
1174 ,p_token3 => 'VALUE:'||p_row_value
1175 );
1176 RETURN NULL;
1177
1178 END get_user_table_value;
1179
1180 --------------------------------------------------------------------------
1181 -- --
1182 -- Name : GET_DFF_TL_VALUE --
1183 -- Type : FUNCTION --
1184 -- Access : Public --
1185 -- Description : Function to fetch the translated value --
1186 -- --
1187 -- Parameters : --
1188 -- IN : p_column_name VARCHAR2 --
1189 -- p_dff VARCHAR2 --
1190 -- p_dff_context_code VARCHAR2 --
1191 -- RETURN : VARCHAR2 --
1192 -- --
1193 -- Change History : --
1194 --------------------------------------------------------------------------
1195 -- Rev# Date Userid Description --
1196 --------------------------------------------------------------------------
1197 -- 1.0 03-Jul-03 saikrish Created this function --
1198 -- 1.1 09-Mar-06 rpalli Modified cursor cur_prompt to resolve --
1199 -- R12 perf issues. Bug 5043303 --
1200 --------------------------------------------------------------------------
1201 FUNCTION get_dff_tl_value(p_column_name IN VARCHAR2
1202 ,p_dff IN VARCHAR2
1203 ,p_dff_context_code IN VARCHAR2
1204 )
1205 RETURN VARCHAR2 IS
1206
1207 CURSOR cur_prompt IS
1208 select t.form_left_prompt
1209 from fnd_descr_flex_col_usage_tl t,
1210 fnd_descr_flex_column_usages b
1211 where t.application_id in (800, 801)
1212 and b.application_id = t.application_id
1213 and t.descriptive_flexfield_name = b.descriptive_flexfield_name
1214 and b.descriptive_flexfield_name = p_dff
1215 and b.descriptive_flex_context_code = t.descriptive_flex_context_code
1216 and b.descriptive_flex_context_code = p_dff_context_code
1217 and b.application_column_name = t.application_column_name
1218 and b.end_user_column_name = p_column_name
1219 and t.language = userenv('LANG');
1220
1221 l_prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
1222
1223 BEGIN
1224
1225 OPEN cur_prompt;
1226 FETCH cur_prompt INTO l_prompt;
1227 CLOSE cur_prompt;
1228
1229 RETURN l_prompt;
1230
1231 END get_dff_tl_value;
1232
1233 --------------------------------------------------------------------------
1234 -- --
1235 -- Name : RAISE_MESSAGE --
1236 -- Type : PROCEDURE --
1237 -- Access : Public --
1238 -- Description : Procedure to raise the error message --
1239 -- --
1240 -- Parameters : --
1241 -- IN : p_application_id NUMBER --
1242 -- p_message_name VARCHAR2 --
1243 -- p_token_name HR_CN_API.CHAR_TAB_TYPE --
1244 -- p_token_value HR_CN_API.CHAR_TAB_TYPE --
1245 -- --
1246 -- Change History : --
1247 --------------------------------------------------------------------------
1248 -- Rev# Date Userid Description --
1249 --------------------------------------------------------------------------
1250 -- 1.0 15-Mar-05 snekkala Created this procedure --
1251 --------------------------------------------------------------------------
1252 PROCEDURE raise_message(p_application_id IN NUMBER
1253 ,p_message_name IN VARCHAR2
1254 ,p_token_name IN OUT NOCOPY hr_cn_api.char_tab_type
1255 ,p_token_value IN OUT NOCOPY hr_cn_api.char_tab_type
1256 )IS
1257 cnt NUMBER;
1258 BEGIN
1259
1260 IF p_message_name IS NOT NULL AND p_message_name <> 'SUCCESS' THEN
1261 cnt:= p_token_name.count;
1262 hr_utility.set_message(p_application_id, p_message_name);
1263 FOR i IN 1..cnt
1264 LOOP
1265 hr_utility.set_message_token(p_token_name(i),p_token_value(i));
1266 END LOOP;
1267 hr_utility.raise_error;
1268 END IF;
1269
1270 END raise_message;
1271
1272 --------------------------------------------------------------------------
1273 -- --
1274 -- Name : get_class_tl_name --
1275 -- Type : FUNCTION --
1276 -- Access : Public --
1277 -- Description : Function to raise the error message --
1278 -- --
1279 -- Parameters : --
1280 -- IN : p_classification_name VARCHAR2 --
1281 -- RETURN : VARCHAR2 --
1282 -- --
1283 -- Change History : --
1284 --------------------------------------------------------------------------
1285 -- Rev# Date Userid Description --
1286 --------------------------------------------------------------------------
1287 -- 1.0 12-May-06 lnagaraj Created this procedure --
1288 --------------------------------------------------------------------------
1289
1290 FUNCTION get_class_tl_name(p_classification_name IN VARCHAR2)
1291 RETURN VARCHAR2
1292 IS
1293 l_trans_name pay_element_classifications_tl.classification_name%TYPE;
1294
1295 CURSOR csr_translated_value
1296 IS
1297 SELECT pectl.classification_name
1298 FROM pay_element_classifications_tl pectl,
1299 pay_element_classifications pec
1300 WHERE pec.classification_id = pectl.classification_id
1301 AND pectl.language =USERENV('LANG')
1302 AND pec.classification_name = DECODE(p_classification_name,'Voluntary Dedn','Voluntary Deductions',p_classification_name)
1303 AND pec.legislation_code='CN';
1304
1305 BEGIN
1306 OPEN csr_translated_value;
1307 FETCH csr_translated_value INTO l_trans_name;
1308 CLOSE csr_translated_value;
1309
1310 RETURN l_trans_name ;
1311
1312 END get_class_tl_name;
1313
1314
1315 BEGIN
1316
1317 g_package := 'hr_cn_api.';
1318
1319 END hr_cn_api;
[Home] [Help]