1 PACKAGE BODY HR_PERSON_FLEX_LOGIC AS
2 /* $Header: hrperlog.pkb 115.21 2003/07/07 18:21:17 asahay noship $ */
3
4
5 /************************************************************
6 Function : GetABV
7 Inputs : ABV Formula ID
8 ABV eg. HeadCount / FTE
9 Assignment ID
10 Effective Date
11 Session Date
12 Outputs : Assignment Budget Value
13 ************************************************************/
14
15 FUNCTION GetABV
16 (p_ABV_formula_id IN NUMBER
17 ,p_assignment_id IN NUMBER
18 ,p_effective_date IN DATE
19 ,p_session_date IN DATE)
20 RETURN NUMBER IS
21
22 l_budget_value number;
23 l_inputs ff_exec.inputs_t;
24 l_outputs ff_exec.outputs_t;
25
26 BEGIN
27
28 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 10');
29
30 -- Initialise the Inputs and Outputs tables
31
32 FF_Exec.Init_Formula
33 ( p_ABV_formula_id
34 , p_session_date
35 , l_inputs
36 , l_outputs );
37
38 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 15');
39
40 if (l_inputs.first is not null)
41 and (l_inputs.last is not null)
42 then
43
44 -- Set up context values for the formula
45
46 for i in l_inputs.first..l_inputs.last
47
48 loop
49
50 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 20');
51
52 if l_inputs(i).name = 'DATE_EARNED' then
53
54 l_inputs(i).value := FND_Date.Date_To_Canonical (p_effective_date);
55
56 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 25');
57
58 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
59
60 l_inputs(i).value := p_assignment_id;
61
62 end if;
63
64 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 30');
65
66 end loop;
67
68 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 35');
69
70 end if;
71
72 -- Run the formula
73
74 FF_Exec.Run_Formula (l_inputs, l_outputs);
75
76 -- Get the result
77
78 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 40');
79
80 l_budget_value := to_number( l_outputs(l_outputs.first).value );
81
82 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 45');
83
84 return (l_budget_value);
85
86 EXCEPTION
87
88 -- raises an exception and appropriate error message if
89 -- the fast formula fails to run (usually due to not being compiled).
90
91 when Others then
92
93 Raise_FF_Not_Compiled( p_ABV_formula_id );
94
95 END GetABV;
96
97
98 FUNCTION GetABV
99 (p_ABV IN VARCHAR2
100 ,p_assignment_id IN NUMBER
101 ,p_session_date IN DATE default sysdate )
102 RETURN NUMBER IS
103
104 cursor c_budget_value
105 is
106 select value
107 from per_assignment_budget_values_f
108 where assignment_id = p_assignment_id
109 and unit = p_ABV
110 and p_session_date between effective_start_date and effective_end_date;
111
112 l_budget_value Number := null;
113
114 BEGIN
115
116 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 50');
117
118 open c_budget_value;
119 fetch c_budget_value into l_budget_value;
120
121 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 55');
122
123 if (c_budget_value%notfound)
124 then
125
126 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 60');
127
128 l_budget_value := null;
129
130 end if;
131
132 close c_budget_value;
133
134 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 65');
135
136 return l_budget_value;
137
138 END GetABV;
139
140
141 FUNCTION GetABV
142 (p_ABV_formula_id IN NUMBER
143 ,p_ABV IN VARCHAR2
144 ,p_assignment_id IN NUMBER
145 ,p_effective_date IN DATE
146 ,p_session_date IN DATE )
147 RETURN NUMBER IS
148
149 l_metric_value Number;
150 l_ABV_formula_id Number;
151
152 BEGIN
153
154 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 70');
155
156 -- First check Assignment Budget Values table
157
158 l_metric_value := GetABV
159 ( p_ABV => p_ABV
160 , p_assignment_id => p_assignment_id
161 , p_session_date => p_effective_date );
162
163 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 75');
164
165 if (l_metric_value is null)
166 then
167
168 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 16 p_ABV_formula_id = '||p_ABV_formula_id);
169 -- There is no ABV value in table, so try FastFormula
170
171 if (p_ABV_formula_id is not null)
172 then
173
174 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 17');
175
176 -- Execute FastFormula
177
178 l_metric_value := GetABV
179 (p_ABV_formula_id => p_ABV_formula_id
180 ,p_assignment_id => p_assignment_id
181 ,p_effective_date => p_effective_date
182 ,p_session_date => p_session_date );
183
184 else
185
186 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 18');
187
188 -- raise an exception and appropriate error message if
189 -- the fast formula does not exist, and no ABV exists for the assignment.
190
191 Raise_FF_Not_exist( p_ABV );
192 end if;
193 end if;
194
195 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetABV - 19');
196
197 return l_metric_value;
198
199 END GetABV;
200
201
202 /************************************************************
203 Function : GetAsgWorkerType
204 Inputs : Assignment ID
205 Outputs : 'P' (Permanent) ,
206 'T' (Temporary) Worker Types
207 ************************************************************/
208
209 Function GetAsgWorkerType
210 (p_AsgWorkerType_formula_id IN NUMBER
211 ,p_assignment_id IN NUMBER
212 ,p_effective_date IN DATE
213 ,p_session_date IN DATE
214 ) RETURN VARCHAR2 IS
215
216 l_asgpertype varchar2(2000);
217 l_inputs ff_exec.inputs_t;
218 l_outputs ff_exec.outputs_t;
219
220 BEGIN
221
222 -- dbms_output.put_line('Function GetAsgWorkerType - 20');
223
224 -- Initialise the Inputs and Outputs tables
225 ff_exec.init_formula(
226 p_AsgWorkerType_formula_id
227 ,p_session_date
228 ,l_inputs
229 ,l_outputs
230 );
231 --
232 -- Set up context values for the formula
233 --
234
235 if (l_inputs.first is not null)
236 and (l_inputs.last is not null)
237 then
238
239 -- dbms_output.put_line('Function GetAsgWorkerType - 25');
240
241 for i in l_inputs.first..l_inputs.last
242
243 loop
244
245 -- dbms_output.put_line('Function GetAsgWorkerType - 30');
246
247 if l_inputs(i).name = 'DATE_EARNED' then
248 l_inputs(i).value := FND_Date.Date_To_Canonical (p_effective_date);
249
250 -- dbms_output.put_line('Function GetAsgWorkerType - 35');
251
252 elsif
253
254 l_inputs(i).name = 'ASSIGNMENT_ID' then
255 l_inputs(i).value := p_assignment_id;
256
257 -- dbms_output.put_line('Function GetAsgWorkerType - 40');
258
259 end if;
260
261 end loop;
262
263 -- dbms_output.put_line('Function GetAsgWorkerType - 45');
264
265 end if;
266
267 -- Run the formula
268
269 ff_exec.run_formula( l_inputs, l_outputs);
270
271 for i in l_outputs.first..l_outputs.last
272
273 loop
274
275 -- Get the result
276 -- dbms_output.put_line('Function GetAsgWorkerType - 50');
277
278 if l_outputs(i).name='PERSON' then
279
280 l_asgpertype := l_outputs(i).value;
281
282 -- dbms_output.put_line('Function GetAsgWorkerType - 55 '||l_outputs(i).name);
283
284 end if;
285
286 end loop;
287
288 -- dbms_output.put_line('Function GetAsgWorkerType - 60');
289
290 return(l_asgpertype);
291
292 /*
293 EXCEPTION
294 when others then
295 return('');
296 */
297
298 END GetAsgWorkerType;
299
300
301
302 /************************************************************
303 Function : GetJobCategory
304 Inputs : Job Category
305 : Job ID
306 Outputs : 'Y' (whether the Assignment is of
307 Job Catgeory passed )
308 ************************************************************/
309
310 FUNCTION GetJobCategory
311 (p_job_id IN NUMBER
312 ,p_job_category IN VARCHAR2)
313 RETURN VARCHAR2 IS
314
315 cursor getjobcatg
316 (p_job_id NUMBER
317 ,p_job_category VARCHAR2)
318 is
319 select 'Y'
320 from per_job_extra_info
321 where information_type = 'Job Category'
322 and JEI_INFORMATION1 = p_job_category
323 and job_id = p_job_id;
324
325 l_job_category varchar2(1);
326
327 begin
328
329 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC - 25');
330
331 open getjobcatg(p_job_id,p_job_category);
332
333 fetch getjobcatg into l_job_category;
334
335 if getjobcatg%notfound then
336
337 close getjobcatg;
338
339 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC - 26');
340
341 l_job_category := 'N';
342
343 end if;
344
345 return(l_job_category);
346
347 end GetJobCategory;
348
349 /************************************************************
350 Function : GetTermTypeFormula
351 Inputs : Business Group Id
352 Outputs : Term Type Formula Id
353 ************************************************************/
354
355 FUNCTION GetTermTypeFormula
356 (p_business_group_id IN NUMBER )
357 RETURN NUMBER IS
358
359 l_formula_id NUMBER;
360
361 CURSOR c_term_formula
362 IS
363 SELECT formula_id
364 FROM ff_formulas_f
365 WHERE business_group_id+0 = p_business_group_id
366 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
367 AND formula_name = 'HR_MOVE_TYPE';
368
369 CURSOR c_tmplt_term_formula
370 IS
371 SELECT formula_id
372 FROM ff_formulas_f
373 WHERE business_group_id+0 is null
374 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
375 AND formula_name = 'HR_MOVE_TYPE_TEMPLATE';
376
377 BEGIN
378
379 -- Look for a customer formula
380
381 OPEN c_term_formula;
382
383 FETCH c_term_formula INTO l_formula_id;
384
385 -- If a customer formula does not exist
386
387 IF (c_term_formula%NOTFOUND OR c_term_formula%NOTFOUND IS NULL) THEN
388
389 CLOSE c_term_formula;
390
391 -- Look for the template formula
392
393 OPEN c_tmplt_term_formula;
394
395 FETCH c_tmplt_term_formula INTO l_formula_id;
396
397 -- If the template formula does not exist
398
399 IF (c_tmplt_term_formula%NOTFOUND OR
400 c_tmplt_term_formula%NOTFOUND IS NULL) THEN
401
402 CLOSE c_tmplt_term_formula;
403
404 -- Raise an error
405
406 raise_ff_not_exist(0);
407
408 ELSE
409
410 CLOSE c_tmplt_term_formula;
411
412 END IF;
413
414 ELSE
415
416 CLOSE c_term_formula;
417
418 END IF;
419
420 RETURN l_formula_id;
421
422 END GetTermTypeFormula;
423
424 /************************************************************
425 Function : GetTermType
426 Inputs : Term Formula ID
427 Leaving Reason
428 Session Date
429 Outputs : Term Type
430 ************************************************************/
431
432 FUNCTION GetTermType
433 ( p_term_formula_id IN NUMBER
434 , p_leaving_reason IN VARCHAR2
435 , p_session_date IN DATE)
436 RETURN VARCHAR2 IS
437
438 l_term_type varchar2(10);
439 l_inputs ff_exec.inputs_t;
440 l_outputs ff_exec.outputs_t;
441
442 BEGIN
443 -- dbms_output.put_line('Entering HR_PERSON_FLEX_LOGIC.GetTermType - 27');
444 -- Initialise the Inputs and Outputs tables
445 FF_Exec.Init_Formula
446 ( p_term_formula_id
447 , p_session_date
448 , l_inputs
449 , l_outputs );
450
451 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetTermType - 28');
452 if (l_inputs.first is not null)
453 and (l_inputs.last is not null)
454 then
455 -- Set up context values for the formula
456 for i in l_inputs.first..l_inputs.last loop
457
458 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetTermType - 29');
459 if l_inputs(i).name = 'LEAVING_REASON' then
460 l_inputs(i).value := p_leaving_reason;
461 end if;
462 end loop;
463 end if;
464
465 -- Run the formula
466 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetTermType - 30');
467 FF_Exec.Run_Formula (l_inputs, l_outputs);
468
469 -- Get the result
470 l_term_type := l_outputs(l_outputs.first).value ;
471
472 -- dbms_output.put_line('HR_PERSON_FLEX_LOGIC.GetTermType - 31');
473 return (l_term_type);
474
475 EXCEPTION
476 -- raises an exception and appropriate error message if
477 -- the fast formula fails to run (usually due to not being compiled).
478 when Others then
479 Raise_FF_Not_compiled( p_term_formula_id );
480
481 END GetTermType;
482
483 /************************************************************
484 Function : GetMovementCategory
485 Inputs : Organization_id
486 Assignment Id
487 Period Start Date
488 Period End Date
489 Movement Type
490 Assignment Type
491 Outputs : Movement Category
492 ************************************************************/
493
494 PROCEDURE GetMovementCategory(
495 p_organization_id IN NUMBER
496 ,p_assignment_id IN NUMBER
497 ,p_period_start_date IN DATE
498 ,p_period_end_date IN DATE
499 ,p_movement_type IN VARCHAR2
500 ,p_assignment_type IN VARCHAR2 default 'E'
501 ,p_movement_category OUT NOCOPY VARCHAR2
502 ) IS
503 --
504 cursor asg_csr is
505 select ast.per_system_status status
506 , asg.effective_start_date
507 , asg.effective_end_date
508 , asg.organization_id
509 from per_assignment_status_types ast
510 , per_all_assignments_f asg
511 where asg.assignment_status_type_id = ast.assignment_status_type_id
512 and asg.assignment_id = p_assignment_id
513 and asg.effective_start_date <= p_period_end_date
514 order by asg.effective_start_date desc;
515 --
516 cursor hire_date_csr is
517 select per.start_date
518 from per_all_people_f per
519 , per_all_assignments_f asg
520 where asg.person_id = per.person_id
521 and p_period_end_date between per.effective_start_date and per.effective_end_date
522 and p_period_end_date between asg.effective_start_date and asg.effective_end_date
523 and asg.assignment_id = p_assignment_id;
524 --
525 cursor term_date_csr is
526 select pos.actual_termination_date
527 from per_periods_of_service pos
528 ,per_all_assignments_f asg
529 where asg.period_of_service_id = pos.period_of_service_id
530 and p_period_start_date-1 between asg.effective_start_date
531 and asg.effective_end_date
532 and asg.assignment_id = p_assignment_id;
533 --
534 cursor cwk_term_date_csr is
535 select pps.actual_termination_date
536 from per_periods_of_placement pps
537 ,per_all_assignments_f asg
538 where asg.person_id = pps.person_id
539 and asg.period_of_placement_date_start = pps.date_start
540 and p_period_start_date-1 between asg.effective_start_date
541 and asg.effective_end_date
542 and asg.assignment_id = p_assignment_id;
543 --
544 asg_rec asg_csr%rowtype;
545 hire_date_rec hire_date_csr%rowtype;
546 term_date_rec term_date_csr%rowtype;
547 --
548 l_assignment_category varchar2(30);
549 --
550 l_first_time boolean := true;
551 l_hire_date date;
552 l_start_date date;
553 l_status varchar2(20);
554 l_term_date date;
555 --
556 BEGIN
557 --
558 -- dbms_output.put_line('Organization_id='||p_organization_id);
559 -- dbms_output.put_line('Assignment_id ='||p_assignment_id);
560 -- dbms_output.put_line('Start Date ='||to_char(p_period_start_date,'DD-MON-YYYY'));
561 -- dbms_output.put_line('End Date ='||to_char(p_period_end_date,'DD-MON-YYYY'));
562 -- dbms_output.put_line('Movement Type ='||p_movement_type);
563 --
564 if p_movement_type = 'IN' then
565 --
566 -- If p_movement_type = 'IN', the employee assignment is Active at the
567 -- Period End Date, and is not Active at Period Start Date minus 1 day.
568 --
569 -- Loop backwards through date-tracked assignment rows
570 for asg_rec in asg_csr loop
571 --
572 if l_first_time = true then
573 null;
574 --
575 elsif asg_rec.status = 'SUSP_ASSIGN' then
576 l_assignment_category := 'REACTIVATED';
577 exit;
578 --
579 elsif (asg_rec.organization_id <> p_organization_id
580 and asg_rec.effective_end_date >= p_period_start_date) -- Added Condition
581 then
582 l_assignment_category := 'TRANSFER_IN';
583 exit;
584 --
585 end if;
586 --
587 l_first_time := false;
588 l_start_date := asg_rec.effective_start_date;
589 --
590 end loop;
591 --
592 if l_assignment_category is null then
593 --
594 -- Determine Hire Date
595 for hire_date_rec in hire_date_csr loop
596 l_hire_date := hire_date_rec.start_date;
597 end loop;
598 --
599 if ((l_start_date = l_hire_date) and
600 (l_hire_date > p_period_start_date)) -- Added check for New Hire
601 then
602 l_assignment_category := 'NEW_HIRE';
603 else
604 l_assignment_category := 'START';
605 end if;
606 --
607 end if;
608 --
609 p_movement_category := l_assignment_category;
610 --
611 elsif p_movement_type = 'OUT' then
612
613 -- If p_movement_type = 'OUT', the employee assignment is Active at
614 -- Period Start Date minus 1 day, and is not Active at Period End Date.
615
616 -- Determine Actual Termination Date
617 if p_assignment_type = 'E' then
618 for term_date_rec in term_date_csr loop
619 l_term_date := term_date_rec.actual_termination_date;
620 end loop;
621 elsif p_assignment_type = 'C' then
622 for cwk_term_date_rec in cwk_term_date_csr loop
623 l_term_date := cwk_term_date_rec.actual_termination_date;
624 end loop;
625
626 end if;
627 --
628 for asg_rec in asg_csr loop
629 --
630 if (( asg_rec.status = 'ACTIVE_ASSIGN'
631 OR asg_rec.status = 'ACTIVE_CWK' ) and
632 asg_rec.organization_id = p_organization_id )
633 then
634
635 -- When an assignment's status is changed to 'End', all that happens is the
636 -- assignment row with status Active is given an Effective End Date. No new
637 -- rows are created, the status End is not stored on the database anywhere.
638 -- Consequently, the IF test above is satisfied the 1st time through the loop.
639
640 if ( l_first_time = true ) then
641
642 if l_term_date is null then
643
644 -- Assignment has been given an End Date
645
646 p_movement_category := 'ENDED';
647 exit;
648 else
649
650 -- Employee terminated with Actual Termination Date = Final Processing Date.
651 -- In this case, no assignment row with status TERM_ASSIGN is created.
652
653 p_movement_category := 'SEPARATED';
654 exit;
655 end if;
656
657 elsif ( l_status = 'TERM_ASSIGN'
658 OR l_status = 'TERM_CWK_ASSIGN' ) then
659 --
660 p_movement_category := 'SEPARATED';
661 exit;
662 --
663 elsif ( l_status = 'SUSP_ASSIGN'
664 OR l_status = 'SUSP_CWK_ASSIGN' ) then
665 p_movement_category := 'SUSPENDED';
666 exit;
667 --
668 else
669 p_movement_category := 'TRANSFER_OUT';
670 exit;
671 end if;
672 --
673 else
674
675 -- Save assignment status of the last row before the 'ACTIVE_ASSIGN' row
676 l_status := asg_rec.status;
677 end if;
678 --
679 l_first_time := false;
680 end loop;
681 end if;
682
683 END GetMovementCategory;
684
685 /************************************************************
686 Function : GetCurNH
687 Inputs : Organization ID
688 Assignment ID
689 Session Date
690 Outputs : Current New Hire
691 ************************************************************/
692
693 FUNCTION GetCurNH
694 ( p_organization_id IN NUMBER
695 , p_assignment_id IN VARCHAR2
696 , p_report_date IN DATE)
697 RETURN VARCHAR2 IS
698
699 cursor c_cur_nh
700 (p_organization_id NUMBER,
701 p_assignment_id NUMBER,
702 p_report_date DATE)
703 is
704 select 'Y'
705 from per_periods_of_service pos
706 where pos.date_start between add_months(p_report_date,-1) and p_report_date
707 and pos.period_of_service_id in (
708 select paf.period_of_service_id
709 from per_all_assignments_f paf
710 where paf.period_of_service_id = pos.period_of_service_id
711 and paf.organization_id = p_organization_id
712 and paf.assignment_id = p_assignment_id);
713
714 l_cur_nh varchar2(1);
715
716 BEGIN
717
718 l_cur_nh := 'N';
719
720 open c_cur_nh
721 (p_organization_id
722 , p_assignment_id
723 , p_report_date);
724 fetch c_cur_nh into l_cur_nh;
725 if c_cur_nh%NOTFOUND then
726 close c_cur_nh;
727 end if;
728 -- close c_cur_nh;
729
730 return (l_cur_nh);
731
732 end GetCurNH;
733
734 /************************************************************
735 Function : GetCurNHNew
736 Inputs : Organization ID
737 Assignment ID
738 Date From
739 Date To
740 Outputs : Current New Hire
741 ************************************************************/
742
743 FUNCTION GetCurNHNew
744 ( p_organization_id IN NUMBER
745 , p_assignment_id IN VARCHAR2
746 , p_assignment_type IN VARCHAR2
747 , p_cur_date_from IN DATE
748 , p_cur_date_to IN DATE)
749 RETURN VARCHAR2 IS
750
751 cursor c_cur_nh
752 (p_organization_id NUMBER,
753 p_assignment_id NUMBER,
754 p_cur_date_from DATE,
755 p_cur_date_to DATE)
756 is
757 select 'Y'
758 from per_periods_of_service pos
759 where pos.date_start between p_cur_date_from and p_cur_date_to
760 and pos.period_of_service_id in (
761 select paf.period_of_service_id
762 from per_all_assignments_f paf
763 where paf.period_of_service_id = pos.period_of_service_id
764 and paf.organization_id = p_organization_id
765 and paf.assignment_id = p_assignment_id);
766
767 cursor c_cwk_cur_nh
768 (p_organization_id NUMBER,
769 p_assignment_id NUMBER,
770 p_cur_date_from DATE,
771 p_cur_date_to DATE)
772 is
773 select 'Y'
774 from per_periods_of_placement pps
775 where pps.date_start between p_cur_date_from and p_cur_date_to
776 and exists (select 1
777 from per_all_assignments_f paf
778 where paf.person_id = pps.person_id
779 and paf.period_of_placement_date_start = pps.date_start
780 and paf.organization_id = p_organization_id
781 and paf.assignment_id = p_assignment_id);
782
783 l_cur_nh varchar2(1);
784
785 BEGIN
786
787 l_cur_nh := 'N';
788 if p_assignment_type = 'E' then
789 open c_cur_nh
790 (p_organization_id
791 ,p_assignment_id
792 ,p_cur_date_from
793 ,p_cur_date_to);
794 fetch c_cur_nh into l_cur_nh;
795 if c_cur_nh%NOTFOUND then
796 close c_cur_nh;
797 end if;
798
799 return (l_cur_nh);
800
801 elsif p_assignment_type = 'C' then
802 open c_cwk_cur_nh
803 (p_organization_id
804 ,p_assignment_id
805 ,p_cur_date_from
806 ,p_cur_date_to);
807 fetch c_cwk_cur_nh into l_cur_nh;
808 if c_cwk_cur_nh%NOTFOUND then
809 close c_cwk_cur_nh;
810 end if;
811
812 return (l_cur_nh);
813
814 end if;
815
816 return (l_cur_nh);
817
818 end GetCurNHNew;
819
820
821 /*****************************************************
822 Function : GetOrgAliasName
823 Description : This function returns alias name for
824 for Organization in
825 hr_organization_information
826 else returns name from
827 hr_organization_units
828 Inputs : Organization_id
829 Report Date
830 Output : Organization Name
831
832 ****************************************************/
833 Function GetOrgAliasName
834 (P_ORGANIZATION_ID IN NUMBER,
835 P_REPORT_DATE IN DATE)
836 RETURN VARCHAR2 IS
837
838 cursor c_org_alias_name is
839 select substr(org_information1,1,60)
840 from hr_organization_information
841 where organization_id = P_ORGANIZATION_ID
842 and org_information_context = 'Organization Name Alias'
843 and P_REPORT_DATE between
844 nvl(fnd_date.canonical_to_date(org_information3),hr_api.g_sot)
845 and nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot);
846
847 cursor c_org_name is
848 select name
849 from hr_organization_units
850 where organization_id = P_ORGANIZATION_ID
851 and P_REPORT_DATE between date_from and
852 nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
853
854 l_org_alias_name varchar2(240);
855
856 BEGIN
857
858 open c_org_alias_name;
859 fetch c_org_alias_name into l_org_alias_name;
860
861 if c_org_alias_name%notfound
862 then close c_org_alias_name;
863
864 open c_org_name;
865 fetch c_org_name into l_org_alias_name;
866 close c_org_name;
867
868 end if;
869
870 return (l_org_alias_name);
871
872 end GetOrgAliasName;
873
874 /************************************************************
875 Procedures to return meaningful error messages
876 *************************************************************/
877
878 PROCEDURE Raise_FF_Not_Exist
879 ( p_formula_id in number )
880 IS
881 BEGIN
882 Fnd_Message.Set_Name('HRI', 'HR_BIS_FF_NOT_EXIST');
883 raise ff_not_exist;
884 END Raise_FF_Not_Exist;
885
886
887
888 PROCEDURE Raise_FF_Not_Compiled
889 ( p_formula_id in Number )
890 IS
891 cursor fast_formula_csr is
892 select formula_name
893 from ff_formulas_f
894 where formula_id = p_formula_id;
895
896 l_formula_name ff_formulas_f.formula_name%type := null;
897 BEGIN
898 open fast_formula_csr;
899 fetch fast_formula_csr into l_formula_name;
900 close fast_formula_csr;
901
902 Fnd_Message.Set_Name('HRI', 'HR_BIS_FF_NOT_COMPILED');
903 Fnd_Message.Set_Token('FORMULA', l_formula_name, FALSE);
904
905 raise ff_not_compiled;
906 END Raise_FF_Not_Compiled;
907
908 /************************************************************
909 Function : GetFormulaTypeID
910 Inputs : Formula Type Name
911 Outputs : Formula_Type_ID
912 ************************************************************/
913
914 FUNCTION GetFormulaTypeID
915 (p_formula_type_name IN VARCHAR2)
916 RETURN NUMBER IS
917
918 cursor formulatypeid
919 is
920 select formula_type_id
921 from ff_formula_types
922 where upper(formula_type_name) = upper(p_formula_type_name);
923
924 l_formula_type_id NUMBER;
925
926 BEGIN
927
928 -- dbms_output.put_line('GetFormulaTypeID');
929
930 open formulatypeid;
931 fetch formulatypeid into l_formula_type_id;
932 if formulatypeid%notfound
933 then
934 hr_utility.set_message(800,'PER_289164_INVAL_FORMULA_TYPE');
935 hr_utility.set_message_token('FORMULA_TYPE', p_formula_type_name);
936 hr_utility.raise_error;
937 end if;
938 close formulatypeid;
939 return l_formula_type_id;
940
941 END GetFormulaTypeID;
942
943 /************************************************************
944 Function : GetFormulaID
945 Inputs : Business Group Id
946 Formula Name
947 Formula Type
948 Outputs : Formula Id
949 Logic : Checks for the formula and if it does not find it
950 looks for Formula Name ||'_TEMPLATE'
951 ************************************************************/
952
953 FUNCTION GetFormulaID
954 (p_business_group_id IN NUMBER
955 ,p_formula_name IN VARCHAR2
956 ,p_formula_type IN VARCHAR2 )
957 RETURN NUMBER IS
958
959 l_formula_id NUMBER;
960
961 CURSOR c_formula
962 IS
963 SELECT formula_id
964 FROM ff_formulas_f
965 WHERE business_group_id+0 = p_business_group_id
966 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
967 AND formula_name = p_formula_name
968 AND formula_type_id
969 = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID(p_formula_type);
970
971 CURSOR c_tmplt_formula
972 IS
973 SELECT formula_id
974 FROM ff_formulas_f
975 WHERE business_group_id+0 is null
976 AND SYSDATE BETWEEN effective_start_date AND effective_end_date
977 AND formula_name = p_formula_name||'_TEMPLATE'
978 AND formula_type_id
979 = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID(p_formula_type);
980
981 BEGIN
982
983 -- Look for a customer formula
984
985 OPEN c_formula;
986
987 FETCH c_formula INTO l_formula_id;
988
989 -- If a customer formula does not exist
990
991 IF (c_formula%NOTFOUND OR c_formula%NOTFOUND IS NULL) THEN
992
993 CLOSE c_formula;
994
995 -- Look for the template formula
996
997 OPEN c_tmplt_formula;
998
999 FETCH c_tmplt_formula INTO l_formula_id;
1000
1001 -- If the template formula does not exist
1002
1003 IF (c_tmplt_formula%NOTFOUND OR
1004 c_tmplt_formula%NOTFOUND IS NULL) THEN
1005
1006 CLOSE c_tmplt_formula;
1007
1008 -- Raise an error
1009
1010 raise_ff_not_exist(0);
1011
1012 ELSE
1013
1014 CLOSE c_tmplt_formula;
1015
1016 END IF;
1017
1018 ELSE
1019
1020 CLOSE c_formula;
1021
1022 END IF;
1023
1024 RETURN l_formula_id;
1025
1026 END GetFormulaID;
1027
1028 /************************************************************
1029 Function : HeadCountForCWK
1030 Inputs : N/A
1031 Outputs : Y or N
1032 Logic : Checks Table pay_action_parameters if data
1033 has been migrated to using CWK. This is done by Users.
1034 ************************************************************/
1035
1036 FUNCTION HeadCountForCWK
1037 RETURN VARCHAR2 IS
1038
1039 l_profile_value VARCHAR2(1);
1040
1041 BEGIN
1042
1043 l_profile_value := nvl(fnd_profile.value('HR_HEADCOUNT_FOR_CWK'),'N');
1044
1045 RETURN l_profile_value;
1046
1047 END HeadCountForCWK;
1048
1049
1050 END HR_PERSON_FLEX_LOGIC;
[Home] [Help]