1 PACKAGE BODY hr_no_utility AS
2 /* $Header: hrnoutil.pkb 120.9.12020000.3 2012/07/04 23:16:04 amnaraya ship $ */
3 --
4 --
5 --
6 -- Function to Formate the Full Name for Norway
7 --
8 g_package VARCHAR2(30);
9
10 FUNCTION per_no_full_name(
11 p_first_name IN VARCHAR2
12 ,p_middle_name IN VARCHAR2
13 ,p_last_name IN VARCHAR2
14 ,p_known_as IN VARCHAR2
15 ,p_title IN VARCHAR2
16 ,p_suffix IN VARCHAR2
17 ,p_pre_name_adjunct IN VARCHAR2
18 ,p_per_information1 IN VARCHAR2
19 ,p_per_information2 IN VARCHAR2
20 ,p_per_information3 IN VARCHAR2
21 ,p_per_information4 IN VARCHAR2
22 ,p_per_information5 IN VARCHAR2
23 ,p_per_information6 IN VARCHAR2
24 ,p_per_information7 IN VARCHAR2
25 ,p_per_information8 IN VARCHAR2
26 ,p_per_information9 IN VARCHAR2
27 ,p_per_information10 IN VARCHAR2
28 ,p_per_information11 IN VARCHAR2
29 ,p_per_information12 IN VARCHAR2
30 ,p_per_information13 IN VARCHAR2
31 ,p_per_information14 IN VARCHAR2
32 ,p_per_information15 IN VARCHAR2
33 ,p_per_information16 IN VARCHAR2
34 ,p_per_information17 IN VARCHAR2
35 ,p_per_information18 IN VARCHAR2
36 ,p_per_information19 IN VARCHAR2
37 ,p_per_information20 IN VARCHAR2
38 ,p_per_information21 IN VARCHAR2
39 ,p_per_information22 IN VARCHAR2
40 ,p_per_information23 IN VARCHAR2
41 ,p_per_information24 IN VARCHAR2
42 ,p_per_information25 IN VARCHAR2
43 ,p_per_information26 IN VARCHAR2
44 ,p_per_information27 IN VARCHAR2
45 ,p_per_information28 IN VARCHAR2
46 ,p_per_information29 IN VARCHAR2
47 ,p_per_information30 IN VARCHAR2
48 ) RETURN VARCHAR2 is
49
50 --
51 --
52 --
53 -- Local Variable
54 --
55 l_full_name VARCHAR2(240);
56 --
57 --
58
59 BEGIN
60 --
61 --
62 -- Construct the full name which has the following format:
63 --
64 -- <first name> <middle name> <last name>
65 --
66
67
68 SELECT SUBSTR(LTRIM(RTRIM(
69 RTRIM(p_first_name)
70 ||DECODE(p_middle_name,NULL, '', ' ' || LTRIM(RTRIM(p_middle_name)))
71 ||' '||LTRIM(p_last_name))
72 ), 1, 240)
73 INTO l_full_name
74 FROM dual;
75
76 --
77 --
78 -- Return Full name
79 --
80
81 RETURN l_full_name;
82 --
83 END per_no_full_name;
84
85
86 --
87 --
88 --
89 -- Function to Formate the Full Name for Norway
90 --
91 FUNCTION per_no_order_name(
92 p_first_name IN VARCHAR2
93 ,p_middle_name IN VARCHAR2
94 ,p_last_name IN VARCHAR2
95 ,p_known_as IN VARCHAR2
96 ,p_title IN VARCHAR2
97 ,p_suffix IN VARCHAR2
98 ,p_pre_name_adjunct IN VARCHAR2
99 ,p_per_information1 IN VARCHAR2
100 ,p_per_information2 IN VARCHAR2
101 ,p_per_information3 IN VARCHAR2
102 ,p_per_information4 IN VARCHAR2
103 ,p_per_information5 IN VARCHAR2
104 ,p_per_information6 IN VARCHAR2
105 ,p_per_information7 IN VARCHAR2
106 ,p_per_information8 IN VARCHAR2
107 ,p_per_information9 IN VARCHAR2
108 ,p_per_information10 IN VARCHAR2
109 ,p_per_information11 IN VARCHAR2
110 ,p_per_information12 IN VARCHAR2
111 ,p_per_information13 IN VARCHAR2
112 ,p_per_information14 IN VARCHAR2
113 ,p_per_information15 IN VARCHAR2
114 ,p_per_information16 IN VARCHAR2
115 ,p_per_information17 IN VARCHAR2
116 ,p_per_information18 IN VARCHAR2
117 ,p_per_information19 IN VARCHAR2
118 ,p_per_information20 IN VARCHAR2
119 ,p_per_information21 IN VARCHAR2
120 ,p_per_information22 IN VARCHAR2
121 ,p_per_information23 IN VARCHAR2
122 ,p_per_information24 IN VARCHAR2
123 ,p_per_information25 IN VARCHAR2
124 ,p_per_information26 IN VARCHAR2
125 ,p_per_information27 IN VARCHAR2
126 ,p_per_information28 IN VARCHAR2
127 ,p_per_information29 IN VARCHAR2
128 ,p_per_information30 IN VARCHAR2)
129 RETURN VARCHAR2 IS
130 --
131 --
132 --
133 -- Local Variable
134 --
135 l_order_name VARCHAR2(240);
136 --
137 --
138 BEGIN
139 --
140 --
141 -- Construct the order name which has the following format:
142 --
143 -- <last name> <first name>
144 --
145 --
146
147 SELECT SUBSTR(TRIM(p_last_name)||' '||TRIM(p_first_name), 1, 240)
148 INTO l_order_name
149 FROM dual;
150
151 --
152 --
153 -- Return the Order Name
154 --
155
156 RETURN l_order_name;
157 --
158 --
159 --
160 END per_no_order_name;
161
162
163
164 FUNCTION validate_account_number
165 (p_account_number IN VARCHAR2) RETURN NUMBER IS
166 l_i NUMBER;
167 l_rem NUMBER;
168 l_strlen NUMBER;
169 l_valid NUMBER;
170 l_account_number VARCHAR2(15);
171 BEGIN
172 -- Account no length should be 11 characters.
173 --
174 IF LENGTH(p_account_number) <> 11 THEN
175 RETURN 1;
176 END IF;
177
178 -- Ensure the Account Number consists only of digits.
179 --
180 l_strlen:= LENGTH(p_account_number);
181 FOR i IN 1..l_strlen
182 LOOP
183 IF (SUBSTR(p_account_number,i,1) < '0' OR SUBSTR(p_account_number,i,1) > '9') then
184 l_valid :=1;
185 END IF;
186
187 END LOOP;
188 IF l_valid =1 THEN
189 RETURN 1 ;
190 END IF;
191 -- Using Modulus 11 Validation
192 --
193 l_i := 0;
194 l_i := l_i + substr(p_account_number, 1, 1) * 5;
195 l_i := l_i + substr(p_account_number, 2, 1) * 4;
196 l_i := l_i + substr(p_account_number, 3, 1) * 3;
197 l_i := l_i + substr(p_account_number, 4, 1) * 2;
198 l_i := l_i + substr(p_account_number, 5, 1) * 7;
199 l_i := l_i + substr(p_account_number, 6, 1) * 6;
200 l_i := l_i + substr(p_account_number, 7, 1) * 5;
201 l_i := l_i + substr(p_account_number, 8, 1) * 4;
202 l_i := l_i + substr(p_account_number, 9, 1) * 3;
203 l_i := l_i + substr(p_account_number, 10, 1) * 2;
204 l_rem := mod( l_i, 11 );
205 IF l_rem = 0 THEN
206 RETURN 0 ;
207 ELSE
208 IF 11- l_rem = substr(p_account_number, 11, 1) THEN
209 RETURN 0 ;
210 ELSE
211 RETURN 1 ;
212 END IF;
213 END IF;
214
215 END;
216
217
218 ----
219 -- Function added for IBAN Validation
220 ----
221 FUNCTION validate_iban_acc(p_account_no VARCHAR2)RETURN NUMBER IS
222 BEGIN
223 IF IBAN_VALIDATION_PKG.validate_iban_acc(p_account_no) = 1 then
224 RETURN 1;
225 else
226 RETURN 0;
227 END IF;
228 END validate_iban_acc;
229
230 ----
231 -- This function will get called from the bank keyflex field segments
232 ----
233 FUNCTION validate_account_entered
234 (p_acc_no IN VARCHAR2,
235 p_is_iban_acc IN varchar2 ) RETURN NUMBER IS
236 --
237 l_ret NUMBER ;
238 begin
239 -- hr_utility.trace_on(null,'ACCVAL');
240 l_ret :=0;
241 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
242 hr_utility.set_location('p_account_number ' || p_acc_no,1);
243
244 IF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'N') then
245 l_ret := validate_account_number(p_acc_no);
246 hr_utility.set_location('l_ret ' || l_ret,1);
247 RETURN l_ret;
248 ELSIF (p_acc_no IS NOT NULL AND p_is_iban_acc = 'Y') then
249 l_ret := validate_iban_acc(p_acc_no);
250 hr_utility.set_location('l_ret ' || l_ret,3);
251 RETURN l_ret;
252 ELSIF (p_acc_no IS NULL AND p_is_iban_acc IS NULL) then
253 hr_utility.set_location('Both Account Nos Null',4);
254 RETURN 1;
255 ELSE
256 hr_utility.set_location('l_ret: 3 ' ,5);
257 RETURN 3;
258 END if;
259 End validate_account_entered;
260
261
262
263 FUNCTION chk_valid_date (p_nat_id IN VARCHAR2)
264 RETURN NUMBER
265 IS
266 l_date DATE;
267 l_day varchar2(2);
268 l_century NUMBER;
269 BEGIN
270
271 -- Identify the century form NI Number
272 IF TO_NUMBER(SUBSTR(p_nat_id,8,2)) < 50 THEN
273 l_century := 19;
274
275 ELSIF TO_NUMBER(SUBSTR(p_nat_id,8,2)) >= 50 AND TO_NUMBER(SUBSTR(p_nat_id,8,2)) < 75 THEN
276 IF TO_NUMBER(SUBSTR(p_nat_id,8,2)) >= 50 THEN
277 l_century := 18;
278 END IF;
279 ELSIF TO_NUMBER(SUBSTR(p_nat_id,8,2)) >= 50 THEN
280 l_century := 20;
281 END IF;
282
283
284 -- Identify the date form NI Number
285 IF TO_NUMBER(substr(p_nat_id,1,2)) > 31 THEN
286 l_day := TO_CHAR(TO_NUMBER(substr(p_nat_id,1,2)) - 40);
287 IF to_number(l_day) < 10 THEN
288 l_day := '0' || l_day;
289 END IF;
290 ELSE
291 l_day := substr(p_nat_id,1,2);
292 END IF;
293
294 -- check for validity of date
295 l_date:=to_date(l_day || substr(p_nat_id,3,2) || to_char(l_century) || substr(p_nat_id,5,2),'DDMMYYYY');
296 RETURN 1;
297 EXCEPTION
298 WHEN others THEN
299 RETURN 0;
300 END;
301
302
303 -- Function : get_employment_information
304 -- Parameters : assignment_id - p_assignment_id,
305 -- employment information code - l_information_code.
306 -- Description : The function returns the employment information based on the assignment id
307 -- and the information code parameters. The information is first searced for at
308 -- the assignment level through the HR_Organization level , Local Unit level ,
309 -- Legal Employer Level to the Business group level.
310 --
311 -- The values for p_emp_information_code can be
312 -- JOB_STATUS for Job Status
313 -- COND_OF_EMP for Condition of Employment
314 -- PART_FULL_TIME for Full/Part Time
315 -- SHIFT_WORK for Shift Work
316 -- PAYROLL_PERIOD for Payroll Period
317 -- AGREED_WORKING_HOURS for Agreed working hours
318
319 FUNCTION get_employment_information (
320 p_assignment_id IN number,
321 p_emp_information_code IN varchar2 )
322 RETURN VARCHAR2 IS
323
324 -- local variables declaration --
325 l_scl_id NUMBER(5);
326 l_organization_id Number(15);
327 l_is_hr_org varchar2(150);
328 l_information varchar2(150);
329 l_local_unit number(15);
330 l_legal_employer number(15);
331 l_org_id number(15);
332 l_bg_id number(15);
333 l_information_code varchar2(50);
334
335 cursor get_scl_id is
336 select SOFT_CODING_KEYFLEX_ID
337 from PER_ALL_ASSIGNMENTS_F
338 where assignment_id = p_assignment_id;
339
340 cursor get_org_id is
341 select ORGANIZATION_ID
342 from PER_ALL_ASSIGNMENTS_F
343 where assignment_id = p_assignment_id;
344
345 cursor get_info_from_scl is
346 select lookups.meaning
347 from HR_SOFT_CODING_KEYFLEX scl, hr_lookups lookups
348 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id
349 and lookups.lookup_type=l_information_code
350 and lookups.enabled_flag = 'Y'
351 and lookups.lookup_code = decode(l_information_code,'NO_JOB_STATUS',scl.segment5,
352 'NO_COND_OF_EMP',scl.segment6,
353 'NO_PART_FULL_TIME',scl.segment7,
354 'NO_SHIFT_WORK',scl.segment8,
355 'NO_PAYROLL_PERIOD',scl.segment9,
356 'NO_AGREED_WORKING_HOURS',scl.segment10,null);
357
358 cursor get_info_from_org is
359 select lookups.meaning
360 from hr_organization_units hou, hr_organization_information hoi , hr_lookups lookups
361 where hou.organization_id = l_organization_id
362 and hou.organization_id = hoi.organization_id
363 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS'
364 and lookups.lookup_type = l_information_code
365 and lookups.enabled_flag = 'Y'
366 and lookups.lookup_code = decode(l_information_code,'NO_JOB_STATUS',hoi.org_information1,
367 'NO_COND_OF_EMP',hoi.org_information2,
368 'NO_PART_FULL_TIME',hoi.org_information3,
369 'NO_SHIFT_WORK',hoi.org_information4,
370 'NO_PAYROLL_PERIOD',hoi.org_information5,
371 'NO_AGREED_WORKING_HOURS',hoi.org_information6,null);
372
373 cursor is_hr_org is
374 select nvl(hoi.org_information1,'NO_DATA')
375 from hr_organization_units hou , hr_organization_information hoi
376 where hou.organization_id = l_organization_id
377 and hou.organization_id = hoi.organization_id
378 and hoi.org_information_context = 'CLASS'
379 and hoi.org_information1 = 'HR_ORG';
380
381 cursor get_local_unit is
382 select segment2
383 from hr_soft_coding_keyflex
384 where soft_coding_keyflex_id = l_scl_id;
385
386
387 cursor get_info_from_local_unit is
388 select lookups.meaning
389 from hr_organization_information hoi , hr_lookups lookups
390 where hoi.organization_id = l_org_id
391 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS'
392 and lookups.lookup_type = l_information_code
393 and lookups.enabled_flag = 'Y'
394 and lookups.lookup_code = decode(l_information_code,'NO_JOB_STATUS',hoi.org_information1,
395 'NO_COND_OF_EMP',hoi.org_information2,
396 'NO_PART_FULL_TIME',hoi.org_information3,
397 'NO_SHIFT_WORK',hoi.org_information4,
398 'NO_PAYROLL_PERIOD',hoi.org_information5,
399 'NO_AGREED_WORKING_HOURS',hoi.org_information6,null);
400
401 cursor get_legal_employer is
402 select hoi2.organization_id
403 from hr_organization_information hoi1 , hr_organization_information hoi2
404 where hoi1.org_information1 = to_char(l_local_unit) and hoi1.org_information_context = 'NO_LOCAL_UNITS'
405 and hoi2.org_information_context = 'CLASS' and hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
406 and hoi2.organization_id = hoi1.organization_id;
407
408
409 cursor get_bg_id is
410 select business_group_id
411 from hr_organization_units
412 where organization_id = l_organization_id;
413
414 cursor get_info_from_scl_awh is
415 select scl.segment10
416 from HR_SOFT_CODING_KEYFLEX scl
417 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id;
418
419
420 cursor get_info_from_org_awh is
421 select hoi.org_information6
422 from hr_organization_units hou, hr_organization_information hoi
423 where hou.organization_id = l_organization_id
424 and hou.organization_id = hoi.organization_id
425 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS';
426
427
428 cursor get_info_from_local_unit_awh is
429 select hoi.org_information6
430 from hr_organization_information hoi
431 where hoi.organization_id = l_org_id
432 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS';
433
434
435
436 begin
437
438 if l_information_code not in ('JOB_STATUS','COND_OF_EMP',
439 'PART_FULL_TIME','SHIFT_WORK','PAYROLL_PERIOD','AGREED_WORKING_HOURS') then
440 return null;
441 end if;
442
443 l_information_code := 'NO_'||p_emp_information_code;
444
445 ---------------------------------------------------------------------------------
446 -- To return information other than Agreed working hours --
447 ---------------------------------------------------------------------------------
448
449 if l_information_code <> 'NO_AGREED_WORKING_HOURS' then
450 --------------------------------------
451 --Try at the Assignment Level --
452 --------------------------------------
453
454 -- get scl id --
455 open get_scl_id;
456 fetch get_scl_id into l_scl_id;
457 close get_scl_id;
458
459 if l_scl_id is not null then
460 -- get information at assignment level --
461 open get_info_from_scl;
462 fetch get_info_from_scl into l_information;
463 close get_info_from_scl;
464 if l_information is not null then
465 return l_information;
466 end if;
467 end if;
468
469 --------------------------------------
470 --Try at the HR_ORG Level --
471 --------------------------------------
472
473 -- get organization_id --
474 open get_org_id;
475 fetch get_org_id into l_organization_id;
476 close get_org_id;
477
478 -- organization id cannot be null --
479 -- check if the organization is HR_ORG --
480 open is_hr_org;
481 fetch is_hr_org into l_is_hr_org;
482 if is_hr_org%NOTFOUND then
483 l_is_hr_org := 'NO_INFO';
484 end if;
485
486 -- get information at the HR Organization level --
487 if l_is_hr_org <> 'NO_INFO' then
488 open get_info_from_org;
489 fetch get_info_from_org into l_information;
490 close get_info_from_org;
491
492 if l_information is not null then
493 return l_information;
494 end if;
495 end if;
496
497 --------------------------------------
498 --Try at the Local Unit Level --
499 --------------------------------------
500 -- get local unit id --
501 open get_local_unit;
502 fetch get_local_unit into l_local_unit;
503 close get_local_unit;
504
505 -- get information at local unit level --
506 l_org_id := l_local_unit;
507 open get_info_from_local_unit;
508 fetch get_info_from_local_unit into l_information;
509 close get_info_from_local_unit;
510
511 if l_information is not null then
512 return l_information;
513 end if;
514
515 ------------------------------------------
516 --Try at the Legal Employer Level --
517 ------------------------------------------
518 -- get legal employer id --
519 open get_legal_employer;
520 fetch get_legal_employer into l_legal_employer;
521 close get_legal_employer;
522
523 -- the cursor for local unit can be reused--
524 l_org_id := l_legal_employer;
525 open get_info_from_local_unit;
526 fetch get_info_from_local_unit into l_information;
527 close get_info_from_local_unit;
528
529 if l_information is not null then
530 return l_information;
531 end if;
532
533 ------------------------------------------
534 --Try at the Business Group Level --
535 ------------------------------------------
536 -- get bg id --
537 open get_bg_id;
538 fetch get_bg_id into l_bg_id;
539 close get_bg_id;
540
541 -- search at bg level--
542 -- the value in l_organization_id will no longer be necessary --
543 -- storing bg_id in l_organization_id --
544
545 l_organization_id := l_bg_id;
546 open get_info_from_org;
547 fetch get_info_from_org into l_information;
548 close get_info_from_org;
549
550 if l_information is not null then
551 return l_information;
552 end if;
553
554 -- return null if the emp information is not present at any level --
555 return null;
556
557 ----------------------------------------------------------------------------------
558 -- To Return Agreed Working Hours Information --
559 ----------------------------------------------------------------------------------
560 elsif l_information_code = 'NO_AGREED_WORKING_HOURS' then
561
562 --------------------------------------
563 --Try at the Assignment Level --
564 --------------------------------------
565
566 -- get scl id --
567 open get_scl_id;
568 fetch get_scl_id into l_scl_id;
569 close get_scl_id;
570
571 if l_scl_id is not null then
572 -- get information at assignment level --
573 open get_info_from_scl_awh;
574 fetch get_info_from_scl_awh into l_information;
575 close get_info_from_scl_awh;
576 if l_information is not null then
577 return l_information;
578 end if;
579 end if;
580
581 --------------------------------------
582 --Try at the HR_ORG Level --
583 --------------------------------------
584
585 -- get organization_id --
586 open get_org_id;
587 fetch get_org_id into l_organization_id;
588 close get_org_id;
589
590 -- organization id cannot be null --
591 -- check if the organization is HR_ORG --
592 open is_hr_org;
593 fetch is_hr_org into l_is_hr_org;
594 if is_hr_org%NOTFOUND then
595 l_is_hr_org := 'NO_INFO';
596 end if;
597
598 -- get information at the HR Organization level --
599 if l_is_hr_org <> 'NO_INFO' then
600 open get_info_from_org_awh;
601 fetch get_info_from_org_awh into l_information;
602 close get_info_from_org_awh;
603
604 if l_information is not null then
605 return l_information;
606 end if;
607 end if;
608
609 --------------------------------------
610 --Try at the Local Unit Level --
611 --------------------------------------
612 -- get local unit id --
613 open get_local_unit;
614 fetch get_local_unit into l_local_unit;
615 close get_local_unit;
616
617 -- get information at local unit level --
618 l_org_id := l_local_unit;
619 open get_info_from_local_unit_awh;
620 fetch get_info_from_local_unit_awh into l_information;
621 close get_info_from_local_unit_awh;
622
623 if l_information is not null then
624 return l_information;
625 end if;
626
627 ------------------------------------------
628 --Try at the Legal Employer Level --
629 ------------------------------------------
630 -- get legal employer id --
631 open get_legal_employer;
632 fetch get_legal_employer into l_legal_employer;
633 close get_legal_employer;
634
635 -- the cursor for local unit can be reused--
636 l_org_id := l_legal_employer;
637 open get_info_from_local_unit_awh;
638 fetch get_info_from_local_unit_awh into l_information;
639 close get_info_from_local_unit_awh;
640
641 if l_information is not null then
642 return l_information;
643 end if;
644
645 ------------------------------------------
646 --Try at the Business Group Level --
647 ------------------------------------------
648 -- get bg id --
649 open get_bg_id;
650 fetch get_bg_id into l_bg_id;
651 close get_bg_id;
652
653 -- search at bg level--
654 -- the value in l_organization_id will no longer be necessary --
655 -- storing bg_id in l_organization_id --
656
657 l_organization_id := l_bg_id;
658 open get_info_from_org_awh;
659 fetch get_info_from_org_awh into l_information;
660 close get_info_from_org_awh;
661
662 if l_information is not null then
663 return l_information;
664 end if;
665
666 -- return null if the emp information is not present at any level --
667 return null;
668
669 end if;
670
671 END get_employment_information;
672
673 --
674 FUNCTION Get_LOAN_START_DATE
675 ( p_EE_ID pay_element_entries_f.ELEMENT_ENTRY_ID%TYPE,
676 p_date_earned DATE )
677 RETURN DATE
678 IS
679 l_Date DATE;
680 BEGIN
681 BEGIN
682 select min(EFFECTIVE_START_DATE)
683 INTO l_Date
684 from pay_element_entries_f
685 where element_entry_id=p_EE_ID;
686
687 EXCEPTION
688 WHEN NO_DATA_FOUND
689 THEN
690
691 l_Date := fnd_date.canonical_to_date('4712/12/31');
692 END;
693
694 RETURN l_Date;
695 END Get_LOAN_START_DATE;
696 --
697
698 -- function for Norway BIK to get element entry effective start date
699
700 FUNCTION Get_EE_EFF_START_DATE
701 ( p_EE_ID pay_element_entries_f.ELEMENT_ENTRY_ID%TYPE,
702 p_date_earned DATE )
703 RETURN DATE
704 IS
705 l_Date DATE;
706 BEGIN
707 BEGIN
708 select EFFECTIVE_START_DATE
709 INTO l_Date
710 from pay_element_entries_f
711 where element_entry_id=p_EE_ID
712 and p_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
713 EXCEPTION
714 WHEN NO_DATA_FOUND
715 THEN
716
717 l_Date := fnd_date.canonical_to_date('4712/12/31');
718 END;
719
720 RETURN l_Date;
721 END Get_EE_EFF_START_DATE;
722 --
723
724
725 -- function for Norway BIK to get element entry effective end date
726
727 FUNCTION Get_EE_EFF_END_DATE
728 ( p_EE_ID pay_element_entries_f.ELEMENT_ENTRY_ID%TYPE,
729 p_date_earned DATE )
730 RETURN DATE
731 IS
732 l_Date DATE;
733 BEGIN
734 BEGIN
735 select EFFECTIVE_END_DATE
736 INTO l_Date
737 from pay_element_entries_f
738 where element_entry_id=p_EE_ID
739 and p_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
740
741 EXCEPTION
742 WHEN NO_DATA_FOUND
743 THEN
744
745 l_Date := fnd_date.canonical_to_date('4712/12/31');
746 END;
747
748 RETURN l_Date;
749 END Get_EE_EFF_END_DATE;
750
751 --
752
753 -- function for Norway BIK Company Cars to get vehile information using Vehicle Repository
754
755 FUNCTION get_vehicle_info
756 ( p_assignment_id per_all_assignments_f.assignment_id%TYPE,
757 p_date_earned DATE,
758 p_list_price OUT NOCOPY pqp_vehicle_repository_f.LIST_PRICE%TYPE,
759 p_reg_number OUT NOCOPY pqp_vehicle_repository_f.REGISTRATION_NUMBER%TYPE,
760 p_reg_date OUT NOCOPY pqp_vehicle_repository_f.INITIAL_REGISTRATION%TYPE
761 )
762 return NUMBER
763 IS
764 l_value NUMBER;
765 BEGIN
766 BEGIN
767
768 select pvr.LIST_PRICE
769 ,pvr.REGISTRATION_NUMBER
770 ,pvr.INITIAL_REGISTRATION
771 INTO p_list_price
772 ,p_reg_number
773 ,p_reg_date
774 from pqp_vehicle_allocations_f pva
775 ,pqp_vehicle_repository_f pvr
776 where pva.assignment_id = p_assignment_id
777 and pvr.vehicle_repository_id = pva.vehicle_repository_id
778 and p_date_earned between pva.EFFECTIVE_START_DATE and pva.EFFECTIVE_END_DATE
779 and p_date_earned between pvr.EFFECTIVE_START_DATE and pvr.EFFECTIVE_END_DATE;
780
781 l_value :=1;
782
783 EXCEPTION
784 WHEN NO_DATA_FOUND
785 THEN
786 l_value :=0;
787
788 END;
789
790 RETURN l_value;
791 END get_vehicle_info;
792 --
793
794 -- function for Norway BIK Company Cars to get number of periods and months
795
796 /* For BIK , to get number of pay periods and the number of months
797 in the current payroll year for Company Cars */
798
799 FUNCTION get_num_of_periods_n_months
800 ( p_payroll_id IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE ,
801 p_start_date IN DATE,
802 p_end_date IN DATE,
803 p_curr_pay_start_date IN DATE,
804 p_curr_per_pay_date IN DATE,
805 p_num_of_periods OUT NOCOPY VARCHAR2,
806 p_num_of_months OUT NOCOPY VARCHAR2
807 )
808 RETURN NUMBER
809 IS
810 l_num_of_periods NUMBER;
811 l_num_of_months NUMBER;
812 l_start_date DATE;
813 l_end_date DATE; -- bug 14106546
814 l_val NUMBER;
815
816
817 BEGIN
818 BEGIN
819
820 l_start_date := p_start_date;
821 l_end_date := p_end_date;
822 l_val := 1;
823 l_num_of_periods := 1;
824 l_num_of_months := 0;
825
826 /* if the element was created in a year before the current payroll period pay date year */
827 IF to_number(to_char(l_start_date,'yyyy')) < to_number(to_char(p_curr_per_pay_date,'yyyy'))
828 THEN
829 /* then set the starting of element to the begining of the current payroll period pay date year */
830 l_start_date := to_date('01-01-'||to_char(p_curr_per_pay_date,'RRRR'),'DD-MM-RRRR' );
831 END IF;
832
833 /* p_start_date is the original element entry effective start date */
834 /* l_start_date is the modified element entry effective start date when the payroll changes year*/
835
836 IF to_number(to_char(l_end_date,'yyyy')) > to_number(to_char(p_curr_per_pay_date,'yyyy'))
837 THEN
838 /* then set the starting of element to the begining of the current payroll period pay date year */
839 l_end_date := to_date('31-12-'||to_char(p_curr_per_pay_date,'RRRR'),'DD-MM-RRRR' );
840 END IF;
841
842 Select COUNT(*)
843 INTO l_num_of_periods
844 from PER_TIME_PERIODS
845 where PAYROLL_ID=p_payroll_id
846 and REGULAR_PAYMENT_DATE <> p_start_date
847 and REGULAR_PAYMENT_DATE between l_start_date and p_end_date
848 and REGULAR_PAYMENT_DATE <= to_date('31-12-' || to_char(p_curr_pay_start_date,'RRRR'),'DD-MM-RRRR');
849
850 -- l_num_of_months := 12-(to_number(to_char(l_start_date,'mm')))+1 ;
851 l_num_of_months := (to_number(to_char(l_end_date,'mm')))-(to_number(to_char(l_start_date,'mm')))+1 ; -- bug 14106546
852
853 EXCEPTION
854 WHEN NO_DATA_FOUND
855 THEN
856 l_num_of_periods := 1;
857 l_num_of_months := 0;
858 l_val := 0;
859 END;
860
861 p_num_of_periods := to_char(l_num_of_periods);
862 p_num_of_months := to_char(l_num_of_months);
863
864 Return l_val;
865 END get_num_of_periods_n_months;
866 --
867
868 /* For BIK , to get number of pay periods with pay date
869 in the current payroll year for Preferential Loans */
870
871 FUNCTION get_num_of_periods
872 ( p_payroll_id IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE ,
873 p_curr_per_pay_date IN DATE
874 )
875 RETURN NUMBER
876 IS
877 l_num_of_periods NUMBER;
878
879 BEGIN
880 BEGIN
881
882 Select COUNT(*)
883 INTO l_num_of_periods
884 from PER_TIME_PERIODS
885 where PAYROLL_ID=p_payroll_id
886 and to_char(REGULAR_PAYMENT_DATE,'mm-yyyy') = to_char(p_curr_per_pay_date,'mm-yyyy');
887
888 EXCEPTION
889 WHEN NO_DATA_FOUND
890 THEN
891 l_num_of_periods := 1;
892 END;
893
894 Return l_num_of_periods;
895 END get_num_of_periods;
896 --
897
898
899 /* For BIK , to get the regular payment date
900 for the current payroll period */
901
902 FUNCTION get_regular_pay_date
903 ( p_payroll_id IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE ,
904 p_Curr_Pay_Start_Date IN DATE
905 )
906 RETURN DATE
907 IS
908 l_regular_pay_date DATE;
909
910 BEGIN
911 BEGIN
912
913 Select REGULAR_PAYMENT_DATE
914 INTO l_regular_pay_date
915 from PER_TIME_PERIODS
916 where PAYROLL_ID=p_payroll_id
917 and START_DATE = p_Curr_Pay_Start_Date;
918
919 EXCEPTION
920 WHEN NO_DATA_FOUND
921 THEN
922 l_regular_pay_date := fnd_date.canonical_to_date('4712/12/31');
923 END;
924
925 Return l_regular_pay_date;
926 END get_regular_pay_date;
927 --
928
929
930 /* Function to get the message text */
931
932 FUNCTION get_msg_text
933 ( p_applid IN NUMBER,
934 p_msg_name IN VARCHAR2
935 )
936 RETURN varchar2
937 IS
938
939 l_msg_text varchar2(2000);
940
941 BEGIN
942 BEGIN
943
944 FND_MESSAGE.SET_NAME(hr_general.get_application_short_name(p_applid),p_msg_name);
945 l_msg_text := FND_MESSAGE.GET;
946
947 END;
948
949 Return l_msg_text;
950 END get_msg_text;
951 --
952 ------------------------------------------------------------------------
953 -- Function GET_TABLE_VALUE
954 ------------------------------------------------------------------------
955 FUNCTION get_table_value
956 (p_Date_Earned IN DATE
957 ,p_table_name IN VARCHAR2
958 ,p_column_name IN VARCHAR2
959 ,p_return_type IN VARCHAR2) RETURN NUMBER
960 IS
961 CURSOR csr_get_user_table_id IS
962 SELECT user_table_id
963 FROM pay_user_tables
964 WHERE legislation_code = 'NO'
965 AND UPPER(user_table_name) = UPPER(p_table_name);
966
967 CURSOR csr_get_column_id (l_user_table_id NUMBER) IS
968 SELECT user_column_id
969 FROM pay_user_columns
970 WHERE legislation_code = 'NO'
971 AND UPPER(user_column_name) = UPPER(p_column_name)
972 AND user_table_id = l_user_table_id;
973
974 -- Modifying CURSOR csr_get_row_id , commenting the use of fnd_date.canonical_to_date
975
976 /*
977 CURSOR csr_get_row_id (l_user_table_id NUMBER) IS
978 SELECT user_row_id
979 FROM pay_user_rows_f
980 WHERE legislation_code = 'NO'
981 AND UPPER(row_low_range_or_name) = UPPER(p_return_type)
982 AND user_table_id = l_user_table_id
983 AND fnd_date.canonical_to_date(p_Date_Earned) BETWEEN effective_start_date AND effective_end_date;
984 */
985
986 CURSOR csr_get_row_id (l_user_table_id NUMBER) IS
987 SELECT user_row_id
988 FROM pay_user_rows_f
989 WHERE legislation_code = 'NO'
990 AND UPPER(row_low_range_or_name) = UPPER(p_return_type)
991 AND user_table_id = l_user_table_id
992 AND p_Date_Earned BETWEEN effective_start_date AND effective_end_date;
993
994 -- Modifying CURSOR csr_get_user_table_value , commenting the use of fnd_date.canonical_to_date
995
996 /*
997 CURSOR csr_get_user_table_value (l_user_column_id NUMBER, l_user_row_id NUMBER) IS
998 SELECT value
999 FROM pay_user_column_instances_f
1000 WHERE legislation_code = 'NO'
1001 AND user_column_id = l_user_column_id
1002 AND user_row_id = l_user_row_id
1003 AND fnd_date.canonical_to_date(p_Date_Earned) BETWEEN effective_start_date AND effective_end_date;
1004 */
1005
1006 /*
1007 CURSOR csr_get_user_table_value (l_user_column_id NUMBER, l_user_row_id NUMBER) IS
1008 SELECT value
1009 FROM pay_user_column_instances_f
1010 WHERE legislation_code = 'NO'
1011 AND user_column_id = l_user_column_id
1012 AND user_row_id = l_user_row_id
1013 AND p_Date_Earned BETWEEN effective_start_date AND effective_end_date;
1014 */
1015
1016 -- Bug Fix 5943303 and 5943317
1017 -- A numeric value from a varchar2 column is being returned to a number variable without any conversion.
1018 -- Using fnd_number.canonical_to_number on column value.
1019
1020 CURSOR csr_get_user_table_value (l_user_column_id NUMBER, l_user_row_id NUMBER) IS
1021 SELECT fnd_number.canonical_to_number(value)
1022 FROM pay_user_column_instances_f
1023 WHERE legislation_code = 'NO'
1024 AND user_column_id = l_user_column_id
1025 AND user_row_id = l_user_row_id
1026 AND p_Date_Earned BETWEEN effective_start_date AND effective_end_date;
1027
1028
1029 l_user_table_id NUMBER;
1030 l_user_column_id NUMBER;
1031 l_user_row_id NUMBER;
1032
1033 -- l_ret_val NUMBER(15,2);
1034
1035 -- Bug Fix 5943303 and 5943317
1036 l_ret_val NUMBER;
1037
1038 l_proc VARCHAR2(72) ;
1039
1040 BEGIN
1041 g_package := 'pay_no_travel_expenses';
1042 l_proc := g_package||'.get_table_value';
1043
1044 --
1045
1046 -- Get the User Table ID
1047 OPEN csr_get_user_table_id;
1048 FETCH csr_get_user_table_id INTO l_user_table_id;
1049 CLOSE csr_get_user_table_id;
1050
1051 -- Get the Column ID
1052 OPEN csr_get_column_id(l_user_table_id);
1053 FETCH csr_get_column_id INTO l_user_column_id;
1054 CLOSE csr_get_column_id;
1055
1056 -- Get the Row ID
1057 OPEN csr_get_row_id(l_user_table_id);
1058 FETCH csr_get_row_id INTO l_user_row_id;
1059 CLOSE csr_get_row_id;
1060
1061 -- Get the value
1062 OPEN csr_get_user_table_value(l_user_column_id,l_user_row_id);
1063 FETCH csr_get_user_table_value INTO l_ret_val;
1064 CLOSE csr_get_user_table_value;
1065
1066
1067 RETURN nvl(l_ret_val,0);
1068
1069 END get_table_value;
1070 PROCEDURE CREATE_NO_DEI_INFO
1071 (P_PERSON_ID IN NUMBER DEFAULT NULL,
1072 P_ISSUED_DATE IN DATE DEFAULT NULL,
1073 P_DATE_FROM IN DATE,
1074 P_DATE_TO IN DATE,
1075 P_DOCUMENT_NUMBER IN VARCHAR2 DEFAULT NULL,
1076 P_DOCUMENT_TYPE_ID IN NUMBER
1077 )is
1078
1079 l_exists varchar2(1);
1080 cursor csr_doc_exists is
1081 select null from hr_document_extra_info
1082 where person_id = p_person_id
1083 and document_type_id = p_document_type_id
1084 and (date_from between p_date_from and p_date_to or
1085 date_to between p_date_from and p_date_to or
1086 p_date_from between date_from and date_to);
1087
1088 /*CURSOR CHECK_OVERLAP_DEI_INFO IS
1089 SELECT 1 FROM HR_DOCUMENT_EXTRA_INFO WHERE
1090 DOCUMENT_TYPE_ID=P_DOCUMENT_TYPE_ID AND
1091 (P_DATE_FROM< DATE_TO AND P_DATE_TO > DATE_FROM );*/
1092 BEGIN
1093 IF P_ISSUED_DATE IS NULL THEN
1094 HR_UTILITY.SET_MESSAGE(800,'HR_376898_NO_DEI_DATE_REQD');
1095 hr_utility.raise_error;
1096 END IF;
1097
1098 open csr_doc_exists;
1099 fetch csr_doc_exists into l_exists;
1100 if csr_doc_exists%FOUND then
1101 hr_utility.set_message(800,'HR_376897_NO_OVERLAP_DEI_INFO');
1102 hr_utility.raise_error;
1103 end if;
1104 close csr_doc_exists;
1105
1106 END;
1107
1108
1109 PROCEDURE UPDATE_NO_DEI_INFO
1110 (P_PERSON_ID IN NUMBER DEFAULT NULL,
1111 P_ISSUED_DATE IN DATE DEFAULT NULL,
1112 P_DATE_FROM IN DATE,
1113 P_DATE_TO IN DATE,
1114 P_DOCUMENT_NUMBER IN VARCHAR2 DEFAULT NULL,
1115 P_DOCUMENT_EXTRA_INFO_ID IN NUMBER,
1116 P_DOCUMENT_TYPE_ID IN NUMBER
1117 )IS
1118 l_exists varchar2(1);
1119 cursor csr_doc_exists is
1120 select null from hr_document_extra_info
1121 where person_id = p_person_id
1122 and document_type_id = p_document_type_id
1123 and (date_from between p_date_from and p_date_to or
1124 date_to between p_date_from and p_date_to or
1125 p_date_from between date_from and date_to)
1126 and document_extra_info_id <> p_document_extra_info_id;
1127
1128
1129
1130 /*CURSOR CHECK_OVERLAP_DEI_INFO IS
1131 SELECT 1 FROM HR_DOCUMENT_EXTRA_INFO WHERE
1132 DOCUMENT_TYPE_ID=P_DOCUMENT_TYPE_ID AND
1133 (P_DATE_FROM< DATE_TO AND P_DATE_TO > DATE_FROM );*/
1134 BEGIN
1135 IF P_ISSUED_DATE IS NULL THEN
1136 HR_UTILITY.SET_MESSAGE(800,'HR_376898_NO_DEI_DATE_REQD');
1137 HR_UTILITY.RAISE_ERROR;
1138 END IF;
1139
1140 open csr_doc_exists;
1141 fetch csr_doc_exists into l_exists;
1142 if csr_doc_exists%FOUND then
1143 hr_utility.set_message(800,'HR_376897_NO_OVERLAP_DEI_INFO');
1144 hr_utility.raise_error;
1145 end if;
1146 close csr_doc_exists;
1147
1148 END;
1149
1150
1151 --------------------------------------------------------------------------
1152 -- --
1153 -- Name : get_IANA_charset --
1154 -- Type : Function --
1155 -- Access : Public --
1156 -- Description : Function to IANA charset equivalent of --
1157 -- NLS_CHARACTERSET --
1158 -- Parameters : --
1159 -- IN : N/A --
1160 -- OUT : N/A --
1161 -- RETURN : VARCHAR2 --
1162 -- --
1163 --------------------------------------------------------------------------
1164 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
1165 CURSOR csr_get_iana_charset IS
1166 SELECT tag
1167 FROM fnd_lookup_values
1168 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1169 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
1170 INSTR(USERENV('LANGUAGE'), '.') + 1)
1171 AND language = 'US';
1172
1173 lv_iana_charset fnd_lookup_values.tag%type;
1174 BEGIN
1175 OPEN csr_get_iana_charset;
1176 FETCH csr_get_iana_charset INTO lv_iana_charset;
1177 CLOSE csr_get_iana_charset;
1178
1179 hr_utility.trace('IANA Charset = '||lv_iana_charset);
1180 RETURN (lv_iana_charset);
1181 END get_IANA_charset;
1182
1183
1184 --Function to display messages after payroll run.
1185 FUNCTION get_message(p_product IN VARCHAR2, p_message_name IN VARCHAR2, p_token1 IN VARCHAR2 DEFAULT NULL, p_token2 IN VARCHAR2 DEFAULT NULL, p_token3 IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS l_message VARCHAR2(2000);
1186 l_token_name VARCHAR2(20);
1187 l_token_value VARCHAR2(80);
1188 l_colon_position NUMBER;
1189 --l_proc varchar2(72) := g_package||'.get_message';
1190 --
1191 BEGIN
1192 --
1193 --hr_utility.set_location('Entered '||l_proc,5);
1194 hr_utility.set_location('. Message Name: ' || p_message_name, 40);
1195 fnd_message.set_name(p_product, p_message_name);
1196
1197 IF p_token1 IS NOT NULL THEN
1198
1199 /* Obtain token 1 name and value */ l_colon_position := instr(p_token1, ':');
1200 l_token_name := SUBSTR(p_token1, 1, l_colon_position -1);
1201 l_token_value := SUBSTR(p_token1, l_colon_position + 1, LENGTH(p_token1));
1202 fnd_message.set_token(l_token_name, l_token_value);
1203 hr_utility.set_location('. Token1: ' || l_token_name || '. Value: ' || l_token_value, 50);
1204 END IF;
1205
1206 IF p_token2 IS NOT NULL THEN
1207
1208 /* Obtain token 2 name and value */ l_colon_position := instr(p_token2, ':');
1209 l_token_name := SUBSTR(p_token2, 1, l_colon_position -1);
1210 l_token_value := SUBSTR(p_token2, l_colon_position + 1, LENGTH(p_token2));
1211 fnd_message.set_token(l_token_name, l_token_value);
1212 hr_utility.set_location('. Token2: ' || l_token_name || '. Value: ' || l_token_value, 60);
1213 END IF;
1214
1215 IF p_token3 IS NOT NULL THEN
1216
1217 /* Obtain token 3 name and value */ l_colon_position := instr(p_token3, ':');
1218 l_token_name := SUBSTR(p_token3, 1, l_colon_position -1);
1219 l_token_value := SUBSTR(p_token3, l_colon_position + 1, LENGTH(p_token3));
1220 fnd_message.set_token(l_token_name, l_token_value);
1221 hr_utility.set_location('. Token3: ' || l_token_name || '. Value: ' || l_token_value, 70);
1222 END IF;
1223
1224 l_message := SUBSTR(fnd_message.GET, 1, 254);
1225 --hr_utility.set_location('leaving '||l_proc,100);
1226 RETURN l_message;
1227 END get_message;
1228
1229
1230 ---------------------------------------------------------------------------
1231 -- Function : get_global_value
1232 -- Function returns the global value for the given date.
1233 ---------------------------------------------------------------------------
1234
1235 FUNCTION get_global_value (l_global_name VARCHAR2 , l_date DATE ) RETURN VARCHAR2 IS
1236
1237 CURSOR get_global_value(l_global_name VARCHAR2 , l_date date) IS
1238 SELECT GLOBAL_VALUE
1239 FROM ff_globals_f
1240 WHERE global_name = l_global_name
1241 AND LEGISLATION_CODE = 'NO'
1242 AND BUSINESS_GROUP_ID IS NULL
1243 AND l_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
1244
1245
1246 l_value ff_globals_f.global_value%TYPE;
1247
1248 BEGIN
1249
1250 OPEN get_global_value(l_global_name , l_date);
1251 FETCH get_global_value INTO l_value;
1252 CLOSE get_global_value;
1253
1254 RETURN l_value;
1255
1256 EXCEPTION
1257
1258 WHEN others THEN
1259 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
1260 raise;
1261
1262 END get_global_value;
1263
1264
1265
1266 --
1267 -- End of the Package
1268 END hr_no_utility;
[Home] [Help]