1 PACKAGE BODY hr_no_utility AS
2 /* $Header: hrnoutil.pkb 120.0.12000000.2 2007/03/21 16:22:39 sugarg 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 chk_valid_date (p_nat_id IN VARCHAR2)
220 RETURN NUMBER
221 IS
222 l_date DATE;
223 l_day varchar2(2);
224 l_century NUMBER;
225 BEGIN
226
227 -- Identify the century form NI Number
228 IF TO_NUMBER(SUBSTR(p_nat_id,8,2)) < 50 THEN
229 l_century := 19;
230
231 ELSIF TO_NUMBER(SUBSTR(p_nat_id,8,2)) >= 50 AND TO_NUMBER(SUBSTR(p_nat_id,8,2)) < 75 THEN
232 IF TO_NUMBER(SUBSTR(p_nat_id,8,2)) >= 50 THEN
233 l_century := 18;
234 END IF;
235 ELSIF TO_NUMBER(SUBSTR(p_nat_id,8,2)) >= 50 THEN
236 l_century := 20;
237 END IF;
238
239
240 -- Identify the date form NI Number
241 IF TO_NUMBER(substr(p_nat_id,1,2)) > 31 THEN
242 l_day := TO_CHAR(TO_NUMBER(substr(p_nat_id,1,2)) - 40);
243 IF to_number(l_day) < 10 THEN
244 l_day := '0' || l_day;
245 END IF;
246 ELSE
247 l_day := substr(p_nat_id,1,2);
248 END IF;
249
250 -- check for validity of date
251 l_date:=to_date(l_day || substr(p_nat_id,3,2) || to_char(l_century) || substr(p_nat_id,5,2),'DDMMYYYY');
252 RETURN 1;
253 EXCEPTION
254 WHEN others THEN
255 RETURN 0;
256 END;
257
258
259 -- Function : get_employment_information
260 -- Parameters : assignment_id - p_assignment_id,
261 -- employment information code - l_information_code.
262 -- Description : The function returns the employment information based on the assignment id
263 -- and the information code parameters. The information is first searced for at
264 -- the assignment level through the HR_Organization level , Local Unit level ,
265 -- Legal Employer Level to the Business group level.
266 --
267 -- The values for p_emp_information_code can be
268 -- JOB_STATUS for Job Status
269 -- COND_OF_EMP for Condition of Employment
270 -- PART_FULL_TIME for Full/Part Time
271 -- SHIFT_WORK for Shift Work
272 -- PAYROLL_PERIOD for Payroll Period
273 -- AGREED_WORKING_HOURS for Agreed working hours
274
275 FUNCTION get_employment_information (
276 p_assignment_id IN number,
277 p_emp_information_code IN varchar2 )
278 RETURN VARCHAR2 IS
279
280 -- local variables declaration --
281 l_scl_id NUMBER(5);
282 l_organization_id Number(15);
283 l_is_hr_org varchar2(150);
284 l_information varchar2(150);
285 l_local_unit number(15);
286 l_legal_employer number(15);
287 l_org_id number(15);
288 l_bg_id number(15);
289 l_information_code varchar2(50);
290
291 cursor get_scl_id is
292 select SOFT_CODING_KEYFLEX_ID
293 from PER_ALL_ASSIGNMENTS_F
294 where assignment_id = p_assignment_id;
295
296 cursor get_org_id is
297 select ORGANIZATION_ID
298 from PER_ALL_ASSIGNMENTS_F
299 where assignment_id = p_assignment_id;
300
301 cursor get_info_from_scl is
302 select lookups.meaning
303 from HR_SOFT_CODING_KEYFLEX scl, hr_lookups lookups
304 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id
305 and lookups.lookup_type=l_information_code
306 and lookups.enabled_flag = 'Y'
307 and lookups.lookup_code = decode(l_information_code,'NO_JOB_STATUS',scl.segment5,
308 'NO_COND_OF_EMP',scl.segment6,
309 'NO_PART_FULL_TIME',scl.segment7,
310 'NO_SHIFT_WORK',scl.segment8,
311 'NO_PAYROLL_PERIOD',scl.segment9,
312 'NO_AGREED_WORKING_HOURS',scl.segment10,null);
313
314 cursor get_info_from_org is
315 select lookups.meaning
316 from hr_organization_units hou, hr_organization_information hoi , hr_lookups lookups
317 where hou.organization_id = l_organization_id
318 and hou.organization_id = hoi.organization_id
319 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS'
320 and lookups.lookup_type = l_information_code
321 and lookups.enabled_flag = 'Y'
322 and lookups.lookup_code = decode(l_information_code,'NO_JOB_STATUS',hoi.org_information1,
323 'NO_COND_OF_EMP',hoi.org_information2,
324 'NO_PART_FULL_TIME',hoi.org_information3,
325 'NO_SHIFT_WORK',hoi.org_information4,
326 'NO_PAYROLL_PERIOD',hoi.org_information5,
327 'NO_AGREED_WORKING_HOURS',hoi.org_information6,null);
328
329 cursor is_hr_org is
330 select nvl(hoi.org_information1,'NO_DATA')
331 from hr_organization_units hou , hr_organization_information hoi
332 where hou.organization_id = l_organization_id
333 and hou.organization_id = hoi.organization_id
334 and hoi.org_information_context = 'CLASS'
335 and hoi.org_information1 = 'HR_ORG';
336
337 cursor get_local_unit is
338 select segment2
339 from hr_soft_coding_keyflex
340 where soft_coding_keyflex_id = l_scl_id;
341
342
343 cursor get_info_from_local_unit is
344 select lookups.meaning
345 from hr_organization_information hoi , hr_lookups lookups
346 where hoi.organization_id = l_org_id
347 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS'
348 and lookups.lookup_type = l_information_code
349 and lookups.enabled_flag = 'Y'
350 and lookups.lookup_code = decode(l_information_code,'NO_JOB_STATUS',hoi.org_information1,
351 'NO_COND_OF_EMP',hoi.org_information2,
352 'NO_PART_FULL_TIME',hoi.org_information3,
353 'NO_SHIFT_WORK',hoi.org_information4,
354 'NO_PAYROLL_PERIOD',hoi.org_information5,
355 'NO_AGREED_WORKING_HOURS',hoi.org_information6,null);
356
357 cursor get_legal_employer is
358 select hoi2.organization_id
359 from hr_organization_information hoi1 , hr_organization_information hoi2
360 where hoi1.org_information1 = to_char(l_local_unit) and hoi1.org_information_context = 'NO_LOCAL_UNITS'
361 and hoi2.org_information_context = 'CLASS' and hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
362 and hoi2.organization_id = hoi1.organization_id;
363
364
365 cursor get_bg_id is
366 select business_group_id
367 from hr_organization_units
368 where organization_id = l_organization_id;
369
370 cursor get_info_from_scl_awh is
371 select scl.segment10
372 from HR_SOFT_CODING_KEYFLEX scl
373 where scl.SOFT_CODING_KEYFLEX_ID = l_scl_id;
374
375
376 cursor get_info_from_org_awh is
377 select hoi.org_information6
378 from hr_organization_units hou, hr_organization_information hoi
379 where hou.organization_id = l_organization_id
380 and hou.organization_id = hoi.organization_id
381 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS';
382
383
384 cursor get_info_from_local_unit_awh is
385 select hoi.org_information6
386 from hr_organization_information hoi
387 where hoi.organization_id = l_org_id
388 and hoi.org_information_context = 'NO_EMPLOYMENT_DEFAULTS';
389
390
391
392 begin
393
394 if l_information_code not in ('JOB_STATUS','COND_OF_EMP',
395 'PART_FULL_TIME','SHIFT_WORK','PAYROLL_PERIOD','AGREED_WORKING_HOURS') then
396 return null;
397 end if;
398
399 l_information_code := 'NO_'||p_emp_information_code;
400
401 ---------------------------------------------------------------------------------
402 -- To return information other than Agreed working hours --
403 ---------------------------------------------------------------------------------
404
405 if l_information_code <> 'NO_AGREED_WORKING_HOURS' then
406 --------------------------------------
407 --Try at the Assignment Level --
408 --------------------------------------
409
410 -- get scl id --
411 open get_scl_id;
412 fetch get_scl_id into l_scl_id;
413 close get_scl_id;
414
415 if l_scl_id is not null then
416 -- get information at assignment level --
417 open get_info_from_scl;
421 return l_information;
418 fetch get_info_from_scl into l_information;
419 close get_info_from_scl;
420 if l_information is not null then
422 end if;
423 end if;
424
425 --------------------------------------
426 --Try at the HR_ORG Level --
427 --------------------------------------
428
429 -- get organization_id --
430 open get_org_id;
431 fetch get_org_id into l_organization_id;
432 close get_org_id;
433
434 -- organization id cannot be null --
435 -- check if the organization is HR_ORG --
436 open is_hr_org;
437 fetch is_hr_org into l_is_hr_org;
438 if is_hr_org%NOTFOUND then
439 l_is_hr_org := 'NO_INFO';
440 end if;
441
442 -- get information at the HR Organization level --
443 if l_is_hr_org <> 'NO_INFO' then
444 open get_info_from_org;
445 fetch get_info_from_org into l_information;
446 close get_info_from_org;
447
448 if l_information is not null then
449 return l_information;
450 end if;
451 end if;
452
453 --------------------------------------
454 --Try at the Local Unit Level --
455 --------------------------------------
456 -- get local unit id --
457 open get_local_unit;
458 fetch get_local_unit into l_local_unit;
459 close get_local_unit;
460
461 -- get information at local unit level --
462 l_org_id := l_local_unit;
463 open get_info_from_local_unit;
464 fetch get_info_from_local_unit into l_information;
465 close get_info_from_local_unit;
466
467 if l_information is not null then
468 return l_information;
469 end if;
470
471 ------------------------------------------
472 --Try at the Legal Employer Level --
473 ------------------------------------------
474 -- get legal employer id --
475 open get_legal_employer;
476 fetch get_legal_employer into l_legal_employer;
477 close get_legal_employer;
478
479 -- the cursor for local unit can be reused--
480 l_org_id := l_legal_employer;
481 open get_info_from_local_unit;
482 fetch get_info_from_local_unit into l_information;
483 close get_info_from_local_unit;
484
485 if l_information is not null then
486 return l_information;
487 end if;
488
489 ------------------------------------------
490 --Try at the Business Group Level --
491 ------------------------------------------
492 -- get bg id --
493 open get_bg_id;
494 fetch get_bg_id into l_bg_id;
495 close get_bg_id;
496
497 -- search at bg level--
498 -- the value in l_organization_id will no longer be necessary --
499 -- storing bg_id in l_organization_id --
500
501 l_organization_id := l_bg_id;
502 open get_info_from_org;
503 fetch get_info_from_org into l_information;
504 close get_info_from_org;
505
506 if l_information is not null then
507 return l_information;
508 end if;
509
510 -- return null if the emp information is not present at any level --
511 return null;
512
513 ----------------------------------------------------------------------------------
514 -- To Return Agreed Working Hours Information --
515 ----------------------------------------------------------------------------------
516 elsif l_information_code = 'NO_AGREED_WORKING_HOURS' then
517
518 --------------------------------------
519 --Try at the Assignment Level --
520 --------------------------------------
521
522 -- get scl id --
523 open get_scl_id;
524 fetch get_scl_id into l_scl_id;
525 close get_scl_id;
526
527 if l_scl_id is not null then
528 -- get information at assignment level --
529 open get_info_from_scl_awh;
530 fetch get_info_from_scl_awh into l_information;
531 close get_info_from_scl_awh;
532 if l_information is not null then
533 return l_information;
534 end if;
535 end if;
536
537 --------------------------------------
538 --Try at the HR_ORG Level --
539 --------------------------------------
540
541 -- get organization_id --
542 open get_org_id;
543 fetch get_org_id into l_organization_id;
544 close get_org_id;
545
546 -- organization id cannot be null --
547 -- check if the organization is HR_ORG --
548 open is_hr_org;
549 fetch is_hr_org into l_is_hr_org;
550 if is_hr_org%NOTFOUND then
551 l_is_hr_org := 'NO_INFO';
552 end if;
553
554 -- get information at the HR Organization level --
555 if l_is_hr_org <> 'NO_INFO' then
556 open get_info_from_org_awh;
557 fetch get_info_from_org_awh into l_information;
558 close get_info_from_org_awh;
559
560 if l_information is not null then
561 return l_information;
562 end if;
563 end if;
564
565 --------------------------------------
566 --Try at the Local Unit Level --
567 --------------------------------------
568 -- get local unit id --
569 open get_local_unit;
570 fetch get_local_unit into l_local_unit;
571 close get_local_unit;
572
573 -- get information at local unit level --
574 l_org_id := l_local_unit;
578
575 open get_info_from_local_unit_awh;
576 fetch get_info_from_local_unit_awh into l_information;
577 close get_info_from_local_unit_awh;
579 if l_information is not null then
580 return l_information;
581 end if;
582
583 ------------------------------------------
584 --Try at the Legal Employer Level --
585 ------------------------------------------
586 -- get legal employer id --
587 open get_legal_employer;
588 fetch get_legal_employer into l_legal_employer;
589 close get_legal_employer;
590
591 -- the cursor for local unit can be reused--
592 l_org_id := l_legal_employer;
593 open get_info_from_local_unit_awh;
594 fetch get_info_from_local_unit_awh into l_information;
595 close get_info_from_local_unit_awh;
596
597 if l_information is not null then
598 return l_information;
599 end if;
600
601 ------------------------------------------
602 --Try at the Business Group Level --
603 ------------------------------------------
604 -- get bg id --
605 open get_bg_id;
606 fetch get_bg_id into l_bg_id;
607 close get_bg_id;
608
609 -- search at bg level--
610 -- the value in l_organization_id will no longer be necessary --
611 -- storing bg_id in l_organization_id --
612
613 l_organization_id := l_bg_id;
614 open get_info_from_org_awh;
615 fetch get_info_from_org_awh into l_information;
616 close get_info_from_org_awh;
617
618 if l_information is not null then
619 return l_information;
620 end if;
621
622 -- return null if the emp information is not present at any level --
623 return null;
624
625 end if;
626
627 END get_employment_information;
628
629
630
631 -- function for Norway BIK to get element entry effective start date
632
633 FUNCTION Get_EE_EFF_START_DATE
634 ( p_EE_ID pay_element_entries_f.ELEMENT_ENTRY_ID%TYPE,
635 p_date_earned DATE )
636 RETURN DATE
637 IS
638 l_Date DATE;
639 BEGIN
640 BEGIN
641 select EFFECTIVE_START_DATE
642 INTO l_Date
643 from pay_element_entries_f
644 where element_entry_id=p_EE_ID
645 and p_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
646 EXCEPTION
647 WHEN NO_DATA_FOUND
648 THEN
649
650 l_Date := fnd_date.canonical_to_date('4712/12/31');
651 END;
652
653 RETURN l_Date;
654 END Get_EE_EFF_START_DATE;
655 --
656
657
658 -- function for Norway BIK to get element entry effective end date
659
660 FUNCTION Get_EE_EFF_END_DATE
661 ( p_EE_ID pay_element_entries_f.ELEMENT_ENTRY_ID%TYPE,
662 p_date_earned DATE )
663 RETURN DATE
664 IS
665 l_Date DATE;
666 BEGIN
667 BEGIN
668 select EFFECTIVE_END_DATE
669 INTO l_Date
670 from pay_element_entries_f
671 where element_entry_id=p_EE_ID
672 and p_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
673
674 EXCEPTION
675 WHEN NO_DATA_FOUND
676 THEN
677
678 l_Date := fnd_date.canonical_to_date('4712/12/31');
679 END;
680
681 RETURN l_Date;
682 END Get_EE_EFF_END_DATE;
683
684 --
685
686 -- function for Norway BIK Company Cars to get vehile information using Vehicle Repository
687
688 FUNCTION get_vehicle_info
689 ( p_assignment_id per_all_assignments_f.assignment_id%TYPE,
690 p_date_earned DATE,
691 p_list_price OUT NOCOPY pqp_vehicle_repository_f.LIST_PRICE%TYPE,
692 p_reg_number OUT NOCOPY pqp_vehicle_repository_f.REGISTRATION_NUMBER%TYPE,
693 p_reg_date OUT NOCOPY pqp_vehicle_repository_f.INITIAL_REGISTRATION%TYPE
694 )
695 return NUMBER
696 IS
697 l_value NUMBER;
698 BEGIN
699 BEGIN
700
701 select pvr.LIST_PRICE
702 ,pvr.REGISTRATION_NUMBER
703 ,pvr.INITIAL_REGISTRATION
704 INTO p_list_price
705 ,p_reg_number
706 ,p_reg_date
707 from pqp_vehicle_allocations_f pva
708 ,pqp_vehicle_repository_f pvr
709 where pva.assignment_id = p_assignment_id
710 and pvr.vehicle_repository_id = pva.vehicle_repository_id
711 and p_date_earned between pva.EFFECTIVE_START_DATE and pva.EFFECTIVE_END_DATE
712 and p_date_earned between pvr.EFFECTIVE_START_DATE and pvr.EFFECTIVE_END_DATE;
713
714 l_value :=1;
715
716 EXCEPTION
717 WHEN NO_DATA_FOUND
718 THEN
719 l_value :=0;
720
721 END;
722
723 RETURN l_value;
724 END get_vehicle_info;
725 --
726
727 -- function for Norway BIK Company Cars to get number of periods and months
728
729 /* For BIK , to get number of pay periods and the number of months
730 in the current payroll year for Company Cars */
731
732 FUNCTION get_num_of_periods_n_months
733 ( p_payroll_id IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE ,
734 p_start_date IN DATE,
735 p_end_date IN DATE,
736 p_curr_pay_start_date IN DATE,
737 p_curr_per_pay_date IN DATE,
738 p_num_of_periods OUT NOCOPY VARCHAR2,
739 p_num_of_months OUT NOCOPY VARCHAR2
740 )
741 RETURN NUMBER
742 IS
743 l_num_of_periods NUMBER;
744 l_num_of_months NUMBER;
745 l_start_date DATE;
746 l_val NUMBER;
747
748
749 BEGIN
750 BEGIN
751
752 l_start_date := p_start_date;
753 l_val := 1;
754 l_num_of_periods := 1;
755 l_num_of_months := 0;
756
757 /* if the element was created in a year before the current payroll period pay date year */
758 IF to_number(to_char(l_start_date,'yyyy')) < to_number(to_char(p_curr_per_pay_date,'yyyy'))
759 THEN
760 /* then set the starting of element to the begining of the current payroll period pay date year */
761 l_start_date := to_date('01-01-'||to_char(p_curr_per_pay_date,'RRRR'),'DD-MM-RRRR' );
762 END IF;
763
764 /* p_start_date is the original element entry effective start date */
765 /* l_start_date is the modified element entry effective start date when the payroll changes year*/
766
767 Select COUNT(*)
768 INTO l_num_of_periods
769 from PER_TIME_PERIODS
770 where PAYROLL_ID=p_payroll_id
771 and REGULAR_PAYMENT_DATE <> p_start_date
772 and REGULAR_PAYMENT_DATE between l_start_date and p_end_date
773 and REGULAR_PAYMENT_DATE <= to_date('31-12-' || to_char(p_curr_pay_start_date,'RRRR'),'DD-MM-RRRR');
774
775 l_num_of_months := 12-(to_number(to_char(l_start_date,'mm')))+1 ;
776
777 EXCEPTION
778 WHEN NO_DATA_FOUND
779 THEN
780 l_num_of_periods := 1;
781 l_num_of_months := 0;
782 l_val := 0;
783 END;
784
785 p_num_of_periods := to_char(l_num_of_periods);
786 p_num_of_months := to_char(l_num_of_months);
787
788 Return l_val;
789 END get_num_of_periods_n_months;
790 --
791
792 /* For BIK , to get number of pay periods with pay date
793 in the current payroll year for Preferential Loans */
794
795 FUNCTION get_num_of_periods
796 ( p_payroll_id IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE ,
797 p_curr_per_pay_date IN DATE
798 )
799 RETURN NUMBER
800 IS
801 l_num_of_periods NUMBER;
802
803 BEGIN
804 BEGIN
805
806 Select COUNT(*)
807 INTO l_num_of_periods
808 from PER_TIME_PERIODS
809 where PAYROLL_ID=p_payroll_id
810 and to_char(REGULAR_PAYMENT_DATE,'mm-yyyy') = to_char(p_curr_per_pay_date,'mm-yyyy');
811
812 EXCEPTION
813 WHEN NO_DATA_FOUND
814 THEN
815 l_num_of_periods := 1;
816 END;
817
818 Return l_num_of_periods;
819 END get_num_of_periods;
820 --
821
822
823 /* For BIK , to get the regular payment date
824 for the current payroll period */
825
826 FUNCTION get_regular_pay_date
827 ( p_payroll_id IN PAY_PAYROLLS_F.PAYROLL_ID%TYPE ,
828 p_Curr_Pay_Start_Date IN DATE
829 )
830 RETURN DATE
831 IS
832 l_regular_pay_date DATE;
833
834 BEGIN
835 BEGIN
836
837 Select REGULAR_PAYMENT_DATE
838 INTO l_regular_pay_date
842
839 from PER_TIME_PERIODS
840 where PAYROLL_ID=p_payroll_id
841 and START_DATE = p_Curr_Pay_Start_Date;
843 EXCEPTION
844 WHEN NO_DATA_FOUND
845 THEN
846 l_regular_pay_date := fnd_date.canonical_to_date('4712/12/31');
847 END;
848
849 Return l_regular_pay_date;
850 END get_regular_pay_date;
851 --
852
853
854 /* Function to get the message text */
855
856 FUNCTION get_msg_text
857 ( p_applid IN NUMBER,
858 p_msg_name IN VARCHAR2
859 )
860 RETURN varchar2
861 IS
862
863 l_msg_text varchar2(2000);
864
865 BEGIN
866 BEGIN
867
868 FND_MESSAGE.SET_NAME(hr_general.get_application_short_name(p_applid),p_msg_name);
869 l_msg_text := FND_MESSAGE.GET;
870
871 END;
872
873 Return l_msg_text;
874 END get_msg_text;
875 --
876 ------------------------------------------------------------------------
877 -- Function GET_TABLE_VALUE
878 ------------------------------------------------------------------------
879 FUNCTION get_table_value
880 (p_Date_Earned IN DATE
881 ,p_table_name IN VARCHAR2
882 ,p_column_name IN VARCHAR2
883 ,p_return_type IN VARCHAR2) RETURN NUMBER
884 IS
885 CURSOR csr_get_user_table_id IS
886 SELECT user_table_id
887 FROM pay_user_tables
888 WHERE legislation_code = 'NO'
889 AND UPPER(user_table_name) = UPPER(p_table_name);
890
891 CURSOR csr_get_column_id (l_user_table_id NUMBER) IS
892 SELECT user_column_id
893 FROM pay_user_columns
894 WHERE legislation_code = 'NO'
895 AND UPPER(user_column_name) = UPPER(p_column_name)
896 AND user_table_id = l_user_table_id;
897
898 -- Modifying CURSOR csr_get_row_id , commenting the use of fnd_date.canonical_to_date
899
900 /*
901 CURSOR csr_get_row_id (l_user_table_id NUMBER) IS
902 SELECT user_row_id
903 FROM pay_user_rows_f
904 WHERE legislation_code = 'NO'
905 AND UPPER(row_low_range_or_name) = UPPER(p_return_type)
906 AND user_table_id = l_user_table_id
907 AND fnd_date.canonical_to_date(p_Date_Earned) BETWEEN effective_start_date AND effective_end_date;
908 */
909
910 CURSOR csr_get_row_id (l_user_table_id NUMBER) IS
911 SELECT user_row_id
912 FROM pay_user_rows_f
913 WHERE legislation_code = 'NO'
914 AND UPPER(row_low_range_or_name) = UPPER(p_return_type)
915 AND user_table_id = l_user_table_id
916 AND p_Date_Earned BETWEEN effective_start_date AND effective_end_date;
917
918 -- Modifying CURSOR csr_get_user_table_value , commenting the use of fnd_date.canonical_to_date
919
920 /*
921 CURSOR csr_get_user_table_value (l_user_column_id NUMBER, l_user_row_id NUMBER) IS
922 SELECT value
923 FROM pay_user_column_instances_f
924 WHERE legislation_code = 'NO'
925 AND user_column_id = l_user_column_id
926 AND user_row_id = l_user_row_id
927 AND fnd_date.canonical_to_date(p_Date_Earned) BETWEEN effective_start_date AND effective_end_date;
928 */
929
930 /*
931 CURSOR csr_get_user_table_value (l_user_column_id NUMBER, l_user_row_id NUMBER) IS
932 SELECT value
933 FROM pay_user_column_instances_f
934 WHERE legislation_code = 'NO'
935 AND user_column_id = l_user_column_id
936 AND user_row_id = l_user_row_id
937 AND p_Date_Earned BETWEEN effective_start_date AND effective_end_date;
938 */
939
940 -- Bug Fix 5943303 and 5943317
941 -- A numeric value from a varchar2 column is being returned to a number variable without any conversion.
942 -- Using fnd_number.canonical_to_number on column value.
943
944 CURSOR csr_get_user_table_value (l_user_column_id NUMBER, l_user_row_id NUMBER) IS
945 SELECT fnd_number.canonical_to_number(value)
946 FROM pay_user_column_instances_f
947 WHERE legislation_code = 'NO'
948 AND user_column_id = l_user_column_id
949 AND user_row_id = l_user_row_id
950 AND p_Date_Earned BETWEEN effective_start_date AND effective_end_date;
951
952
953 l_user_table_id NUMBER;
954 l_user_column_id NUMBER;
955 l_user_row_id NUMBER;
956
957 -- l_ret_val NUMBER(15,2);
958
959 -- Bug Fix 5943303 and 5943317
960 l_ret_val NUMBER;
961
962 l_proc VARCHAR2(72) ;
963
964 BEGIN
965 g_package := 'pay_no_travel_expenses';
966 l_proc := g_package||'.get_table_value';
967
968 --
969
970 -- Get the User Table ID
971 OPEN csr_get_user_table_id;
972 FETCH csr_get_user_table_id INTO l_user_table_id;
973 CLOSE csr_get_user_table_id;
974
975 -- Get the Column ID
976 OPEN csr_get_column_id(l_user_table_id);
977 FETCH csr_get_column_id INTO l_user_column_id;
978 CLOSE csr_get_column_id;
979
980 -- Get the Row ID
981 OPEN csr_get_row_id(l_user_table_id);
982 FETCH csr_get_row_id INTO l_user_row_id;
983 CLOSE csr_get_row_id;
984
985 -- Get the value
986 OPEN csr_get_user_table_value(l_user_column_id,l_user_row_id);
987 FETCH csr_get_user_table_value INTO l_ret_val;
988 CLOSE csr_get_user_table_value;
989
990
991 RETURN nvl(l_ret_val,0);
992
993 END get_table_value;
994 PROCEDURE CREATE_NO_DEI_INFO
995 (P_PERSON_ID IN NUMBER DEFAULT NULL,
996 P_ISSUED_DATE IN DATE DEFAULT NULL,
997 P_DATE_FROM IN DATE,
998 P_DATE_TO IN DATE,
999 P_DOCUMENT_NUMBER IN VARCHAR2 DEFAULT NULL,
1000 P_DOCUMENT_TYPE_ID IN NUMBER
1001 )is
1002
1003 l_exists varchar2(1);
1004 cursor csr_doc_exists is
1005 select null from hr_document_extra_info
1006 where person_id = p_person_id
1007 and document_type_id = p_document_type_id
1008 and (date_from between p_date_from and p_date_to or
1009 date_to between p_date_from and p_date_to or
1010 p_date_from between date_from and date_to);
1011
1012 /*CURSOR CHECK_OVERLAP_DEI_INFO IS
1013 SELECT 1 FROM HR_DOCUMENT_EXTRA_INFO WHERE
1014 DOCUMENT_TYPE_ID=P_DOCUMENT_TYPE_ID AND
1015 (P_DATE_FROM< DATE_TO AND P_DATE_TO > DATE_FROM );*/
1016 BEGIN
1017 IF P_ISSUED_DATE IS NULL THEN
1018 HR_UTILITY.SET_MESSAGE(800,'HR_376898_NO_DEI_DATE_REQD');
1019 hr_utility.raise_error;
1020 END IF;
1021
1022 open csr_doc_exists;
1023 fetch csr_doc_exists into l_exists;
1024 if csr_doc_exists%FOUND then
1025 hr_utility.set_message(800,'HR_376897_NO_OVERLAP_DEI_INFO');
1026 hr_utility.raise_error;
1027 end if;
1028 close csr_doc_exists;
1029
1030 END;
1031
1032
1033 PROCEDURE UPDATE_NO_DEI_INFO
1034 (P_PERSON_ID IN NUMBER DEFAULT NULL,
1035 P_ISSUED_DATE IN DATE DEFAULT NULL,
1036 P_DATE_FROM IN DATE,
1037 P_DATE_TO IN DATE,
1038 P_DOCUMENT_NUMBER IN VARCHAR2 DEFAULT NULL,
1039 P_DOCUMENT_EXTRA_INFO_ID IN NUMBER,
1040 P_DOCUMENT_TYPE_ID IN NUMBER
1041 )IS
1042 l_exists varchar2(1);
1043 cursor csr_doc_exists is
1044 select null from hr_document_extra_info
1045 where person_id = p_person_id
1046 and document_type_id = p_document_type_id
1047 and (date_from between p_date_from and p_date_to or
1048 date_to between p_date_from and p_date_to or
1049 p_date_from between date_from and date_to)
1050 and document_extra_info_id <> p_document_extra_info_id;
1051
1052
1053
1054 /*CURSOR CHECK_OVERLAP_DEI_INFO IS
1055 SELECT 1 FROM HR_DOCUMENT_EXTRA_INFO WHERE
1056 DOCUMENT_TYPE_ID=P_DOCUMENT_TYPE_ID AND
1057 (P_DATE_FROM< DATE_TO AND P_DATE_TO > DATE_FROM );*/
1058 BEGIN
1059 IF P_ISSUED_DATE IS NULL THEN
1060 HR_UTILITY.SET_MESSAGE(800,'HR_376898_NO_DEI_DATE_REQD');
1061 HR_UTILITY.RAISE_ERROR;
1062 END IF;
1063
1064 open csr_doc_exists;
1065 fetch csr_doc_exists into l_exists;
1066 if csr_doc_exists%FOUND then
1067 hr_utility.set_message(800,'HR_376897_NO_OVERLAP_DEI_INFO');
1068 hr_utility.raise_error;
1069 end if;
1070 close csr_doc_exists;
1071
1072 END;
1073
1074
1075 --------------------------------------------------------------------------
1076 -- --
1077 -- Name : get_IANA_charset --
1078 -- Type : Function --
1079 -- Access : Public --
1080 -- Description : Function to IANA charset equivalent of --
1081 -- NLS_CHARACTERSET --
1082 -- Parameters : --
1083 -- IN : N/A --
1084 -- OUT : N/A --
1085 -- RETURN : VARCHAR2 --
1086 -- --
1087 --------------------------------------------------------------------------
1088 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
1089 CURSOR csr_get_iana_charset IS
1090 SELECT tag
1091 FROM fnd_lookup_values
1092 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1093 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
1094 INSTR(USERENV('LANGUAGE'), '.') + 1)
1095 AND language = 'US';
1096
1097 lv_iana_charset fnd_lookup_values.tag%type;
1098 BEGIN
1099 OPEN csr_get_iana_charset;
1100 FETCH csr_get_iana_charset INTO lv_iana_charset;
1101 CLOSE csr_get_iana_charset;
1102
1103 hr_utility.trace('IANA Charset = '||lv_iana_charset);
1104 RETURN (lv_iana_charset);
1105 END get_IANA_charset;
1106
1107
1108 --Function to display messages after payroll run.
1109 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);
1110 l_token_name VARCHAR2(20);
1111 l_token_value VARCHAR2(80);
1112 l_colon_position NUMBER;
1113 --l_proc varchar2(72) := g_package||'.get_message';
1114 --
1115 BEGIN
1116 --
1117 --hr_utility.set_location('Entered '||l_proc,5);
1118 hr_utility.set_location('. Message Name: ' || p_message_name, 40);
1119 fnd_message.set_name(p_product, p_message_name);
1120
1121 IF p_token1 IS NOT NULL THEN
1122
1123 /* Obtain token 1 name and value */ l_colon_position := instr(p_token1, ':');
1124 l_token_name := SUBSTR(p_token1, 1, l_colon_position -1);
1125 l_token_value := SUBSTR(p_token1, l_colon_position + 1, LENGTH(p_token1));
1126 fnd_message.set_token(l_token_name, l_token_value);
1127 hr_utility.set_location('. Token1: ' || l_token_name || '. Value: ' || l_token_value, 50);
1128 END IF;
1129
1130 IF p_token2 IS NOT NULL THEN
1131
1132 /* Obtain token 2 name and value */ l_colon_position := instr(p_token2, ':');
1133 l_token_name := SUBSTR(p_token2, 1, l_colon_position -1);
1134 l_token_value := SUBSTR(p_token2, l_colon_position + 1, LENGTH(p_token2));
1135 fnd_message.set_token(l_token_name, l_token_value);
1136 hr_utility.set_location('. Token2: ' || l_token_name || '. Value: ' || l_token_value, 60);
1137 END IF;
1138
1139 IF p_token3 IS NOT NULL THEN
1140
1141 /* Obtain token 3 name and value */ l_colon_position := instr(p_token3, ':');
1142 l_token_name := SUBSTR(p_token3, 1, l_colon_position -1);
1143 l_token_value := SUBSTR(p_token3, l_colon_position + 1, LENGTH(p_token3));
1144 fnd_message.set_token(l_token_name, l_token_value);
1145 hr_utility.set_location('. Token3: ' || l_token_name || '. Value: ' || l_token_value, 70);
1146 END IF;
1147
1148 l_message := SUBSTR(fnd_message.GET, 1, 254);
1149 --hr_utility.set_location('leaving '||l_proc,100);
1150 RETURN l_message;
1151 END get_message;
1152
1153
1154 ---------------------------------------------------------------------------
1155 -- Function : get_global_value
1156 -- Function returns the global value for the given date.
1157 ---------------------------------------------------------------------------
1158
1159 FUNCTION get_global_value (l_global_name VARCHAR2 , l_date DATE ) RETURN VARCHAR2 IS
1160
1161 CURSOR get_global_value(l_global_name VARCHAR2 , l_date date) IS
1162 SELECT GLOBAL_VALUE
1163 FROM ff_globals_f
1164 WHERE global_name = l_global_name
1165 AND LEGISLATION_CODE = 'NO'
1166 AND BUSINESS_GROUP_ID IS NULL
1167 AND l_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
1168
1169
1170 l_value ff_globals_f.global_value%TYPE;
1171
1172 BEGIN
1173
1174 OPEN get_global_value(l_global_name , l_date);
1175 FETCH get_global_value INTO l_value;
1176 CLOSE get_global_value;
1177
1178 RETURN l_value;
1179
1180 EXCEPTION
1181
1182 WHEN others THEN
1183 hr_utility.trace('SQLERRM:'||substr(sqlerrm,1,200));
1184 raise;
1185
1186 END get_global_value;
1187
1188
1189
1190 --
1191 -- End of the Package
1192 END hr_no_utility;
[Home] [Help]