1 PACKAGE BODY HrFastAnswers AS
2 /* $Header: hrfstans.pkb 115.26 2004/06/17 23:16:15 prasharm ship $ */
3 --
4 -- Package Globals
5 --
6 G_BUSINESS_GROUP_ID number;
7 G_ORG_STRUCTURE_VERSION_ID number;
8
9 --
10 type OrgRecType is record
11 ( organization_id_group Number
12 , organization_id_child Number );
13 --
14 type OrgTabType is table of OrgRecType
15 index by Binary_Integer;
16 --
17 type OrgIndType is table of Number
18 index by Binary_Integer;
19 --
20 ---------------------------------------------------------------------------
21 --
22 OrgTable OrgTabType;
23 OrgIndex OrgIndType;
24 LeavingReasons LeavingReasonsType;
25 --
26 ---------------------------------------------------------------------------
27 -- Private function to add an Organization pair
28 -- to the OrgTable and OrgIndex PL/SQL tables
29 procedure Add_Org
30 ( p_org_id_group in Number
31 , p_org_id_child in Number )
32 is
33 l_index Number := nvl(OrgTable.Last,0) + 1;
34 --
35 begin
36 OrgTable(l_index).organization_id_group := p_org_id_group;
37 OrgTable(l_index).organization_id_child := p_org_id_child;
38 --
39 if (not OrgIndex.Exists(p_org_id_group))
40 then
41 OrgIndex(p_org_id_group) := l_index;
42 end if;
43 end Add_Org;
44 --
45 ---------------------------------------------------------------------------
46 -- Private function to determine whether a specified
47 -- Organization pair exists in the OrgTable table
48 function Find_Org
49 ( p_org_id_group in Number
50 , p_org_id_child in Number )
51 return Number is
52 l_found Boolean := FALSE;
53 l_index Number;
54 --
55 begin
56 if (OrgIndex.Exists(p_org_id_group))
57 then
58 l_index := OrgIndex(p_org_id_group);
59 --
60 loop
61 if (OrgTable(l_index).organization_id_child = p_org_id_child)
62 then
63 l_found := TRUE;
64 end if;
65 --
66 exit when (l_index = OrgTable.Last) or (l_found);
67 l_index := OrgTable.Next(l_index);
68 --
69 exit when (OrgTable(l_index).organization_id_group <> p_org_id_group);
70 end loop;
71 end if;
72 --
73 if (l_found)
74 then
75 return(1);
76 else
77 return(0);
78 end if;
79
80 exception
81 when Others then
82 return(0);
83
84 end Find_Org;
85 --
86 ---------------------------------------------------------------------------
87 -- Private function to get the ID of the BIS Organization Hierarchy to
88 -- to be used in reporting. This can be set by the System Administrator
89 -- using the profile option "HR:BIS Reporting Hierarchy"
90 --
91 -- 17-Jun-1999 (BG)
92 -- Altered to check for existence of Profile Option first. If not found
93 -- or Profile not set, we fetch the Organization Structure ID from the
94 -- Security Profile associated with the User/Resp/Appl/SecGroup.
95 --
96 function GetReportingHierarchy return Number is
97
98 cursor c_struct
99 ( cp_bus_id Number
100 , cp_str_id Number )
101 is
102 select ost.organization_structure_id
103 from per_organization_structures ost
104 where ost.organization_structure_id = cp_str_id
105 and ost.business_group_id = cp_bus_id;
106
107 cursor c_sp_bureau
108 ( cp_user_id Number
109 , cp_resp_id Number
110 , cp_appl_id Number
111 , cp_secg_id Number )
112 is
113 select spr.view_all_organizations_flag
114 , spr.organization_structure_id
115 from per_security_profiles spr
116 , per_sec_profile_assignments spa
117 where spr.security_profile_id = spa.security_profile_id
118 and spa.user_id = cp_user_id
119 and spa.responsibility_id = cp_resp_id
120 and spa.responsibility_application_id = cp_appl_id
121 and spa.security_group_id = cp_secg_id;
122
123 -- Cursor added by S.Bhattal, 19-OCT-99, 115.9
124
125 cursor c_sp_non_bureau
126 ( cp_security_profile_id number )
127 is
128 select view_all_organizations_flag
129 ,organization_structure_id
130 from per_security_profiles
131 where security_profile_id = cp_security_profile_id;
132
133 cursor c_primary
134 ( cp_bus_id Number )
135 is
136 select ost.organization_structure_id
137 from per_organization_structures ost
138 where ost.business_group_id = cp_bus_id
139 and ost.primary_structure_flag = 'Y';
140
141 l_all_org Varchar2(80);
142 l_bus_id Number := null;
143 l_enable_sg varchar2(1);
144 l_security_profile_id per_security_profiles.security_profile_id%type;
145 l_str_id Number := null;
146
147 l_user_id Number := FND_Global.User_Id;
148 l_resp_id Number := FND_Global.Resp_Id;
149 l_appl_id Number := FND_Global.Resp_Appl_Id;
150 l_secg_id Number := FND_Global.Security_Group_Id;
151
152 begin
153
154 -- bug 2968520;
155 l_bus_id := hr_bis.get_sec_profile_bg_id;
156
157 /************************************************************************
158 1. Get org hierarchy from BIS Reporting Hierarchy profile option
159 (for customers who have upgraded from BIS 1.2 to BIS 11i)
160 ************************************************************************/
161
162 if (FND_Profile.Value('HR_BIS_REPORTING_HIERARCHY') is not null) then
163
164 l_str_id := to_number( FND_Profile.Value('HR_BIS_REPORTING_HIERARCHY') );
165
166 -- Check to see if Structure still exists
167 if (l_str_id is not null) then
168
169 open c_struct (l_bus_id, l_str_id);
170 fetch c_struct into l_str_id;
171
172 if (c_struct%notfound) then
173 l_str_id := null;
174 end if;
175
176 close c_struct;
177 end if;
178
179 end if;
180
181 /************************************************************************
182 2. Get org hierarchy from the appropriate Security Profile.
183
184 If the customer is not a bureau, get the security profile from
185 the system profile option.
186
187 If the customer is a bureau, get the security profile using their
188 login details (including the security group).
189 ************************************************************************/
190
191 if (l_str_id is null) then
192
193 l_enable_sg := fnd_profile.value('ENABLE_SECURITY_GROUPS');
194
195 if (l_enable_sg = 'N') then
196
197 -- Not a bureau, i.e. a regular customer
198
199 l_security_profile_id := fnd_profile.value('PER_SECURITY_PROFILE_ID');
200
201 open c_sp_non_bureau( l_security_profile_id );
202
203 fetch c_sp_non_bureau into l_all_org, l_str_id;
204
205 if (c_sp_non_bureau%notfound) or (l_all_org='Y')then
206 l_str_id := null;
207 end if;
208
209 close c_sp_non_bureau;
210
211 elsif (l_enable_sg = 'Y') then
212
213 -- A bureau with multiple Security Groups per responsibility
214
215 open c_sp_bureau( l_user_id, l_resp_id, l_appl_id, l_secg_id);
216
217 fetch c_sp_bureau into l_all_org, l_str_id;
218
219 if (c_sp_bureau%notfound) or (l_all_org='Y') then
220 l_str_id := null;
221 end if;
222
223 close c_sp_bureau;
224 end if;
225
226 end if;
227
228 /***************************************************************************
229 3. Get org hierarchy from the primary hierarchy for the Business Group
230 ***************************************************************************/
231
232 if (l_str_id is null) then
233
234 open c_primary (l_bus_id);
235 fetch c_primary into l_str_id;
236 close c_primary;
237
238 end if;
239
240 return (l_str_id);
241 end;
242
243 ---------------------------------------------------------------------------
244 --
245 FUNCTION GetBudgetValue
246 ( p_budget_metric_formula_id IN NUMBER
247 , p_assignment_id IN NUMBER
248 , p_effective_date IN DATE
249 , p_session_date IN DATE)
250 RETURN NUMBER IS
251
252 l_budget_value number;
253 l_inputs ff_exec.inputs_t;
254 l_outputs ff_exec.outputs_t;
255
256 BEGIN
257 -- Initialise the Inputs and Outputs tables
258 FF_Exec.Init_Formula
259 ( p_budget_metric_formula_id
260 , p_session_date
261 , l_inputs
262 , l_outputs );
263
264 if (l_inputs.first is not null)
265 and (l_inputs.last is not null)
266 then
267 -- Set up context values for the formula
268 for i in l_inputs.first..l_inputs.last loop
269
270 if l_inputs(i).name = 'DATE_EARNED' then
271 l_inputs(i).value := FND_Date.Date_To_Canonical (p_effective_date);
272
273 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
274 l_inputs(i).value := p_assignment_id;
275 end if;
276 end loop;
277 end if;
278
279 -- Run the formula
280 FF_Exec.Run_Formula (l_inputs, l_outputs);
281
282 -- Get the result
283 l_budget_value := to_number( l_outputs(l_outputs.first).value );
284
285 return (l_budget_value);
286
287 EXCEPTION
288 -- Changed so it raises an exception and appropriate error message if
289 -- the fast formula fails to run (usually due to not being compiled).
290 -- Previously the function just returned zero.
291 -- mjandrew - 28-JUN-2000 Bug #1323212
292 when Others then
293 Raise_FF_Not_compiled( p_budget_metric_formula_id );
294
295 END GetBudgetValue;
296
297
298 FUNCTION GetBudgetValue
299 ( p_budget_metric IN VARCHAR2
300 , p_assignment_id IN NUMBER
301 , p_session_date IN DATE default sysdate )
302 RETURN NUMBER IS
303
304 cursor c_budget_value is
305 select value
306 from per_assignment_budget_values_f
307 where assignment_id = p_assignment_id
308 and unit = p_budget_metric
309 and p_session_date between effective_start_date and effective_end_date;
310
311 l_budget_value Number := null;
312
313 BEGIN
314 open c_budget_value;
315 fetch c_budget_value into l_budget_value;
316
317 if (c_budget_value%notfound)
318 then
319 l_budget_value := null;
320 end if;
321
322 close c_budget_value;
323
324 return l_budget_value;
325
326 END GetBudgetValue;
327
328
329 FUNCTION GetBudgetValue
330 ( p_budget_metric_formula_id IN NUMBER
331 , p_budget_metric IN VARCHAR2
332 , p_assignment_id IN NUMBER
333 , p_effective_date IN DATE
334 , p_session_date IN DATE )
335 RETURN NUMBER IS
336
337 l_metric_value Number;
338
339 BEGIN
340 -- First check Assignment Budget Values table
341 l_metric_value := GetBudgetValue
342 ( p_budget_metric => p_budget_metric
343 , p_assignment_id => p_assignment_id
344 , p_session_date => p_effective_date );
345
346 if (l_metric_value is null)
347 then
348 -- There is no ABV value in table, so try FastFormula
349 if (p_budget_metric_formula_id is not null)
350 then
351 -- Execute FastFormula
352 l_metric_value := GetBudgetValue
353 ( p_budget_metric_formula_id => p_budget_metric_formula_id
354 , p_assignment_id => p_assignment_id
355 , p_effective_date => p_effective_date
356 , p_session_date => p_session_date );
357
358 else
359 -- Changed so it raises an exception and appropriate error message if
360 -- the fast formula does not exist, and no ABV exists for the assignment.
361 -- Previously the function just returned zero by setting l_metric_value to 0.
362 -- mjandrew - 28-JUN-2000 Bug #1323212
363 Raise_FF_Not_exist( p_budget_metric );
364 end if;
365 end if;
366
367 return l_metric_value;
368
369 END GetBudgetValue;
370 ---------------------------------------------------------------------------
371 --
372 FUNCTION GetUtilHours(
373 p_formula_id IN NUMBER
374 ,p_assignment_id IN NUMBER
375 ,p_effective_date IN DATE
376 ,p_session_date IN DATE
377 ) RETURN NUMBER IS
378 --
379 l_hours_value number;
380 l_inputs ff_exec.inputs_t;
381 l_outputs ff_exec.outputs_t;
382 --
383 BEGIN
384 --
385 -- Initialise the Inputs and Outputs tables
386 ff_exec.init_formula( p_formula_id
387 ,p_session_date
388 ,l_inputs
389 ,l_outputs
390 );
391 --
392 -- Set up context values for the formula
393 --
394 if (l_inputs.first is not null)
395 and (l_inputs.last is not null)
396 then
397 for i in l_inputs.first..l_inputs.last loop
398 --
399 if l_inputs(i).name = 'DATE_EARNED' then
400 l_inputs(i).value := FND_Date.Date_To_Canonical (p_effective_date);
401 --
402 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
403 l_inputs(i).value := p_assignment_id;
404 end if;
405 --
406 end loop;
407 --
408 end if;
409 --
410 -- Run the formula
411 ff_exec.run_formula( l_inputs, l_outputs, FALSE );
412 --
413 -- Get the result
414 l_hours_value := to_number( l_outputs(l_outputs.first).value );
415 --
416 return( l_hours_value );
417 --
418 EXCEPTION
419 when others then
420 return(0);
421 END GetUtilHours;
422 --
423 ---------------------------------------------------------------------------
424 --
425 FUNCTION Get_Hours_Worked(
426 p_assign_id IN NUMBER
427 ,p_earned_date IN DATE
428 ,p_multiple IN NUMBER
429 ) RETURN NUMBER IS
430 --
431 l_hours_worked number;
432 --
433 begin
434 --
435 /* 115.22 - Added to_char around p_multiple */
436 /*Bug 3658456: Rewritten the SQL to get l_hours_worked*/
437 select sum(ev.screen_entry_value)
438 into l_hours_worked
439 from pay_element_entry_values_f ev,
440 pay_element_entries_f ee
441 where ev.element_entry_id = ee.element_entry_id and
442 ee.assignment_id = p_assign_id and
443 (ev.input_value_id,ee.element_link_id) in
444 ( select iv.input_value_id,el.element_link_id
445 from pay_element_types_f et,
446 pay_input_values_f iv,
447 pay_element_links_f el
448 where iv.name = 'Hours' and
449 et.element_name = 'Overtime' and
450 iv.element_type_id = et.element_type_id and
451 et.element_type_id = el.element_type_id and
452 p_earned_date between et.effective_start_date and et.effective_end_date and
453 p_earned_date between iv.effective_start_date and iv.effective_end_date and
454 p_earned_date between el.effective_start_date and el.effective_end_date
455 ) and
456 p_earned_date between ee.effective_start_date and ee.effective_end_date and
457 exists (select null
458 from pay_element_entry_values_f ev2,
459 pay_element_entries_f ee2
460 where ev2.element_entry_id = ee2.element_entry_id and
461 ee2.assignment_id = p_assign_id and
462 ev2.screen_entry_value = to_char(p_multiple) and
463 ev2.element_entry_id = ev.element_entry_id and
464 (ev2.input_value_id,ee2.element_link_id) in
465 ( select iv2.input_value_id,el2.element_link_id
466 from pay_element_types_f et2,
467 pay_input_values_f iv2,
468 pay_element_links_f el2
469 where iv2.element_type_id = et2.element_type_id and
470 et2.element_type_id = el2.element_type_id and
471 iv2.name = 'Multiple' and
472 et2.element_name = 'Overtime' and
473 p_earned_date between et2.effective_start_date and et2.effective_end_date and
474 p_earned_date between iv2.effective_start_date and iv2.effective_end_date and
475 p_earned_date between el2.effective_start_date and el2.effective_end_date
476 ) and
477 p_earned_date between ee2.effective_start_date and ee2.effective_end_date
478 );
479 --
480 return (l_hours_worked);
481 --
482 EXCEPTION
483 when others then
484 return(0);
485 END Get_Hours_Worked;
486
487 PROCEDURE GetAssignmentCategory(
488 p_org_param_id IN NUMBER
489 ,p_assignment_id IN NUMBER
490 ,p_period_start_date IN DATE
491 ,p_period_end_date IN DATE
492 ,p_top_org IN NUMBER
493 ,p_movement_type IN VARCHAR2
494 ,p_assignment_category OUT NOCOPY VARCHAR2
495 ,p_leaving_reason OUT NOCOPY VARCHAR2
496 ,p_service_band OUT NOCOPY VARCHAR2
497 ) IS
498 --
499 cursor asg_csr is
500 select ast.per_system_status status
501 , asg.effective_start_date
502 , asg.effective_end_date
503 , asg.organization_id
504 from per_assignment_status_types ast
505 , per_all_assignments_f asg
506 where asg.assignment_status_type_id = ast.assignment_status_type_id
507 and asg.assignment_id = p_assignment_id
508 and asg.effective_start_date <= p_period_end_date
509 order by asg.effective_start_date desc;
510 --
511 cursor hire_date_csr is
512 select per.start_date
513 from per_all_people_f per
514 , per_all_assignments_f asg
515 where asg.person_id = per.person_id
516 and p_period_end_date between per.effective_start_date and per.effective_end_date
517 and p_period_end_date between asg.effective_start_date and asg.effective_end_date
518 and asg.assignment_id = p_assignment_id;
519 --
520 cursor term_date_csr is
521 select pos.actual_termination_date
522 , pos.leaving_reason
523 , decode( pos.actual_termination_date,
524 null, 'Not Terminated',
525 decode( floor(
526 months_between( pos.actual_termination_date, pos.date_start ) / 12 ),
527 0, '<1 Year',
528 1, '1-3 Years',
529 2, '1-3 Years',
530 3, '3-5 Years',
531 4, '3-5 Years',
532 '5 Years+')) service_band
533 from per_periods_of_service pos
534 , per_all_assignments_f asg
535 where asg.period_of_service_id = pos.period_of_service_id
536 and p_period_start_date-1 between asg.effective_start_date and asg.effective_end_date
537 and asg.assignment_id = p_assignment_id;
538 --
539 asg_rec asg_csr%rowtype;
540 hire_date_rec hire_date_csr%rowtype;
541 term_date_rec term_date_csr%rowtype;
542 --
543 l_assignment_category varchar2(30);
544 --
545 l_first_time boolean := true;
546 l_hire_date date;
547 l_start_date date;
548 l_status varchar2(20);
549 l_term_date date;
550 l_leaving_reason varchar2(30);
551 --
552 BEGIN
553 --
554 p_leaving_reason := null;
555 --
556 if p_movement_type = 'IN' then
557 --
558 -- If p_movement_type = 'IN', the employee assignment is Active at the
559 -- Period End Date, and is not Active at Period Start Date minus 1 day.
560 --
561 -- Loop backwards through date-tracked assignment rows
562 for asg_rec in asg_csr loop
563 --
564 if l_first_time = true then
565 null;
566 --
567 elsif asg_rec.status = 'SUSP_ASSIGN' then
568 l_assignment_category := 'REACTIVATED';
569 exit;
570 --
571 elsif HrBisOrgParams.OrgInHierarchy
572 ( p_org_param_id
573 , p_top_org
574 , asg_rec.organization_id ) <>1
575 then
576 l_assignment_category := 'TRANSFER_IN';
577 exit;
578 --
579 end if;
580 --
581 l_first_time := false;
582 l_start_date := asg_rec.effective_start_date;
583 --
584 end loop;
585 --
586 if l_assignment_category is null then
587 --
588 -- Determine Hire Date
589 for hire_date_rec in hire_date_csr loop
590 l_hire_date := hire_date_rec.start_date;
591 end loop;
592 --
593 if l_start_date = l_hire_date then
594 l_assignment_category := 'NEW_HIRE';
595 else
596 l_assignment_category := 'START';
597 end if;
598 --
599 end if;
600 --
601 p_assignment_category := l_assignment_category;
602 p_leaving_reason := null;
603 p_service_band := null;
604 --
605 elsif p_movement_type = 'OUT' then
606
607 -- If p_movement_type = 'OUT', the employee assignment is Active at
608 -- Period Start Date minus 1 day, and is not Active at Period End Date.
609
610 -- Determine Actual Termination Date
611 for term_date_rec in term_date_csr loop
612 l_term_date := term_date_rec.actual_termination_date;
613 l_leaving_reason := term_date_rec.leaving_reason;
614 p_service_band := term_date_rec.service_band;
615 end loop;
616 --
617 for asg_rec in asg_csr loop
618 --
619 if ( asg_rec.status = 'ACTIVE_ASSIGN' ) and
620 ( HrBisOrgParams.OrgInHierarchy
621 ( p_org_param_id
622 , p_top_org
623 , asg_rec.organization_id) = 1 )
624 then
625
626 -- When an assignment's status is changed to 'End', all that happens is the
627 -- assignment row with status Active is given an Effective End Date. No new
628 -- rows are created, the status End is not stored on the database anywhere.
629 -- Consequently, the IF test above is satisfied the 1st time through the loop.
630
631 if ( l_first_time = true ) then
632
633 if l_term_date is null then
634
635 -- Assignment has been given an End Date
636
637 p_leaving_reason := 'NOTSEPARATED';
638 p_assignment_category := 'ENDED';
639 exit;
640 else
641
642 -- Employee terminated with Actual Termination Date = Final Processing Date.
643 -- In this case, no assignment row with status TERM_ASSIGN is created.
644
645 p_leaving_reason := l_leaving_reason;
646 p_assignment_category := 'SEPARATED';
647 exit;
648 end if;
649
650 elsif ( l_status = 'TERM_ASSIGN' ) then
651 --
652 p_leaving_reason := l_leaving_reason;
653 p_assignment_category := 'SEPARATED';
654 exit;
655 --
656 elsif ( l_status = 'SUSP_ASSIGN' ) then
657 p_leaving_reason := 'NOTSEPARATED';
658 p_assignment_category := 'SUSPENDED';
659 exit;
660 --
661 else
662 p_leaving_reason := 'NOTSEPARATED';
663 p_assignment_category := 'TRANSFER_OUT';
664 exit;
665 end if;
666 --
667 else
668
669 -- Save assignment status of the last row before the 'ACTIVE_ASSIGN' row
670 l_status := asg_rec.status;
671 end if;
672 --
673 l_first_time := false;
674 end loop;
675 end if;
676
677 END GetAssignmentCategory;
678
679 -- Overloaded version of GetAssignmentCategory,
680 -- so that Leaving Reason can be ignored
681
682 FUNCTION GetAssignmentCategory
683 ( p_org_param_id IN NUMBER
684 , p_assignment_id IN NUMBER
685 , p_period_start_date IN DATE
686 , p_period_end_date IN DATE
687 , p_top_org IN NUMBER
688 , p_movement_type IN VARCHAR2 )
689 RETURN VARCHAR2 IS
690 --
691 l_assignment_category varchar2(30);
692 l_leaving_reason varchar2(30);
693 l_service_band varchar2(30);
694 --
695 BEGIN
696 --
697 HrFastAnswers.GetAssignmentCategory
698 ( p_org_param_id => p_org_param_id
699 , p_assignment_id => p_assignment_id
700 , p_period_start_date => p_period_start_date
701 , p_period_end_date => p_period_end_date
702 , p_top_org => p_top_org
703 , p_movement_type => p_movement_type
704 , p_assignment_category => l_assignment_category
705 , p_leaving_reason => l_leaving_reason
706 , p_service_band => l_service_band);
707 --
708 return (l_assignment_category);
709 --
710 END GetAssignmentCategory;
711
712 --
713 -- Overloaded version that accepts an organization_id instead of
714 -- an org_param_id and uses the first SINR org_param_id for that
715 -- organization_id it can find.
716 --
717 FUNCTION GetAssignmentCategory
718 ( p_assignment_id IN NUMBER
719 , p_period_start_date IN DATE
720 , p_period_end_date IN DATE
721 , p_top_org IN NUMBER
722 , p_movement_type IN VARCHAR2 )
723 RETURN VARCHAR2 IS
724 --
725 l_assignment_category varchar2(30);
726 l_leaving_reason varchar2(30);
727 l_service_band varchar2(30);
728 --
729 l_org_param_id number;
730 --
731 -- This cursor will select the first org_param_id it finds for the organization
732 -- regardless of organization_structure_id
733 --
734 cursor c_org_param
735 ( cp_organization_id Number )
736 is
737 select org_param_id
738 from hri_org_params
739 where organization_id = cp_organization_id
740 and organization_process = 'SINR';
741 --
742 BEGIN
743 --
744 open c_org_param(p_top_org);
745 fetch c_org_param into l_org_param_id;
746 close c_org_param;
747 --
748 HrFastAnswers.GetAssignmentCategory
749 ( p_org_param_id => l_org_param_id
750 , p_assignment_id => p_assignment_id
751 , p_period_start_date => p_period_start_date
752 , p_period_end_date => p_period_end_date
753 , p_top_org => p_top_org
754 , p_movement_type => p_movement_type
755 , p_assignment_category => l_assignment_category
756 , p_leaving_reason => l_leaving_reason
757 , p_service_band => l_service_band);
758 --
759 return (l_assignment_category);
760 --
761 END GetAssignmentCategory;
762 --
763
764 FUNCTION GetLeavingReason
765 ( p_org_param_id IN NUMBER
766 , p_assignment_id IN NUMBER
767 , p_period_start_date IN DATE
768 , p_period_end_date IN DATE
769 , p_top_org IN NUMBER
770 , p_movement_type IN VARCHAR2 )
771 RETURN VARCHAR2 IS
772 --
773 l_assignment_category varchar2(30);
774 l_leaving_reason varchar2(30);
775 l_service_band varchar2(30);
776 --
777 BEGIN
778 --
779 HrFastAnswers.GetAssignmentCategory
780 ( p_org_param_id => p_org_param_id
781 , p_assignment_id => p_assignment_id
782 , p_period_start_date => p_period_start_date
783 , p_period_end_date => p_period_end_date
784 , p_top_org => p_top_org
785 , p_movement_type => p_movement_type
786 , p_assignment_category => l_assignment_category
787 , p_leaving_reason => l_leaving_reason
788 , p_service_band => l_service_band );
789 --
790 RETURN (l_leaving_reason);
791 --
792 END GetLeavingReason;
793
794 FUNCTION GetLeavingReasonMeaning
795 ( p_org_param_id IN NUMBER
796 , p_assignment_id IN NUMBER
797 , p_period_start_date IN DATE
798 , p_period_end_date IN DATE
799 , p_top_org IN NUMBER
800 , p_movement_type IN VARCHAR2)
801 RETURN VARCHAR2 IS
802 --
803 l_assignment_category varchar2(30);
804 l_leaving_reason varchar2(30);
805 l_leaving_reason_meaning varchar2(2000);
806 l_service_band varchar2(30);
807 --
808 cursor get_meaning_csr( p_leaving_reason varchar2 ) is
809 select meaning
810 from hr_lookups
811 where lookup_type = 'LEAV_REAS'
812 and lookup_code = p_leaving_reason;
813 --
814 get_meaning_rec get_meaning_csr%rowtype;
815 --
816 BEGIN
817 --
818 HrFastAnswers.GetAssignmentCategory
819 ( p_org_param_id => p_org_param_id
820 , p_assignment_id => p_assignment_id
821 , p_period_start_date => p_period_start_date
822 , p_period_end_date => p_period_end_date
823 , p_top_org => p_top_org
824 , p_movement_type => p_movement_type
825 , p_assignment_category => l_assignment_category
826 , p_leaving_reason => l_leaving_reason
827 , p_service_band => l_service_band );
828 --
829 if (l_leaving_reason is null) then
830 l_leaving_reason_meaning := fnd_message.get_string('HRI','HR_BIS_UNKNOWN');
831 elsif
832 (l_leaving_reason = 'NOTSEPARATED') then
833 --
834 l_leaving_reason_meaning := 'NotSeparated';
835 else
836 --
837 for get_meaning_rec in get_meaning_csr(l_leaving_reason) loop
838 l_leaving_reason_meaning := get_meaning_rec.meaning;
839 end loop;
840 --
841 end if;
842 --
843 RETURN (l_leaving_reason_meaning);
844 --
845 END GetLeavingReasonMeaning;
846
847 FUNCTION Get_Service_Band_Name
848 ( p_org_param_id IN NUMBER
849 , p_assignment_id IN NUMBER
850 , p_period_start_date IN DATE
851 , p_period_end_date IN DATE
852 , p_top_org IN NUMBER
853 , p_movement_type IN VARCHAR2)
854 RETURN VARCHAR2 IS
855 --
856 l_assignment_category varchar2(30);
857 l_leaving_reason varchar2(30);
858 l_service_band varchar2(2000);
859 --
860 BEGIN
861 --
862 HrFastAnswers.GetAssignmentCategory
863 ( p_org_param_id => p_org_param_id
864 , p_assignment_id => p_assignment_id
865 , p_period_start_date => p_period_start_date
866 , p_period_end_date => p_period_end_date
867 , p_top_org => p_top_org
868 , p_movement_type => p_movement_type
869 , p_assignment_category => l_assignment_category
870 , p_leaving_reason => l_leaving_reason
871 , p_service_band => l_service_band );
872
873 l_service_band := substr(l_service_band,1,1);
874 --
875 if(l_service_band = '<') then
876 l_service_band := fnd_message.get_string('HRI','HR_BIS_LESS_THAN_1_YEAR');
877 elsif(l_service_band = '1') then
878 l_service_band := fnd_message.get_string('HRI','HR_BIS_1_TO_3_YEARS');
879 elsif(l_service_band = '3') then
880 l_service_band := fnd_message.get_string('HRI','HR_BIS_3_TO_5_YEARS');
881 elsif(l_service_band = '5') then
882 l_service_band := fnd_message.get_string('HRI','HR_BIS_5_YEARS+');
883 end if;
884 --
885 RETURN (l_service_band);
886 --
887 END Get_Service_Band_Name;
888
889 FUNCTION Get_Service_Band_Order
890 ( p_org_param_id IN NUMBER
891 , p_assignment_id IN NUMBER
892 , p_period_start_date IN DATE
893 , p_period_end_date IN DATE
894 , p_top_org IN NUMBER
895 , p_movement_type IN VARCHAR2)
896 RETURN NUMBER IS
897 --
898 l_assignment_category varchar2(30);
899 l_leaving_reason varchar2(30);
900 l_service_band varchar2(30);
901 --
902 BEGIN
903 --
904 HrFastAnswers.GetAssignmentCategory
905 ( p_org_param_id => p_org_param_id
906 , p_assignment_id => p_assignment_id
907 , p_period_start_date => p_period_start_date
908 , p_period_end_date => p_period_end_date
909 , p_top_org => p_top_org
910 , p_movement_type => p_movement_type
911 , p_assignment_category => l_assignment_category
912 , p_leaving_reason => l_leaving_reason
913 , p_service_band => l_service_band );
914
915 l_service_band := substr(l_service_band,1,1);
916 --
917 if(l_service_band = '<') then
918 RETURN (1);
919 elsif(l_service_band = '1') then
920 RETURN (2);
921 elsif(l_service_band = '3') then
922 RETURN (3);
923 elsif(l_service_band = '5') then
924 RETURN (4);
925 else
926 RETURN (0);
927 end if;
928 --
929 END Get_Service_Band_Order;
930
931 procedure LoadOrgHierarchy
932 ( p_organization_id IN Number
933 , p_org_struct_version_id IN Number )
934 is
935 l_org_list Varchar2(2000);
936 --
937 begin
938 LoadOrgHierarchy
939 ( p_organization_id => p_organization_id
940 , p_org_struct_version_id => p_org_struct_version_id
941 , p_organization_process => 'ISNR'
942 , p_org_list => l_org_list );
943 end LoadOrgHierarchy;
944 --
945 procedure LoadOrgHierarchy
946 ( p_organization_id IN Number )
947 is
948 begin
949 -- Clear both PL/SQL tables
950 OrgTable.Delete;
951 OrgIndex.Delete;
952
953 -- Add single Organization to tables
954 Add_Org (p_organization_id, p_organization_id);
955
956 end LoadOrgHierarchy;
957 --
958 procedure LoadOrgHierarchy
959 ( p_organization_id IN Number
960 , p_org_struct_version_id IN Number
961 , p_organization_process IN Varchar2
962 , p_org_list OUT NOCOPY Varchar2 )
963 is
964 cursor c_toporg
965 is
966 select ose.organization_id_parent
967 from per_org_structure_elements ose
968 where ose.org_structure_element_id = GetOrgStructElement;
969
970 cursor c_main
971 ( cp_organization_id Number
972 , cp_org_structure_version_id Number
973 , cp_organization_process Varchar2 )
974 is
975 select TREE.organization_id_start
976 from hr_organization_units org
977 , (select ele.organization_id_parent organization_id_start
978 from per_org_structure_elements ele
979 where cp_organization_process in ('ISNR', 'ISRO')
980 connect by prior ele.organization_id_child = ele.organization_id_parent
981 and ele.org_structure_version_id = cp_org_structure_version_id
982 start with ele.organization_id_parent = cp_organization_id
983 and ele.org_structure_version_id = cp_org_structure_version_id) TREE
984 where TREE.organization_id_start = org.organization_id
985 UNION
986 select TREE.organization_id_start
987 from hr_organization_units org
988 , (select ele.organization_id_child organization_id_start
989 from per_org_structure_elements ele
990 where cp_organization_process in ('ISNR', 'ISRO')
991 connect by prior ele.organization_id_child = ele.organization_id_parent
992 and ele.org_structure_version_id = cp_org_structure_version_id
993 start with ele.organization_id_parent = cp_organization_id
994 and ele.org_structure_version_id = cp_org_structure_version_id) TREE
995 where TREE.organization_id_start = org.organization_id
996 UNION
997 select org.organization_id organization_id_start
998 from hr_organization_units org
999 where org.organization_id = cp_organization_id
1000 order by 1;
1001 --
1002 cursor c_child
1003 ( cp_organization_id_start Number
1004 , cp_org_structure_version_id Number
1005 , cp_organization_process Varchar2 )
1006 is
1007 select TREE.organization_id_group
1008 , TREE.organization_id_child
1009 from hr_organization_units org
1010 , (select cp_organization_id_start organization_id_group
1011 , ele.organization_id_child organization_id_child
1012 from per_org_structure_elements ele
1013 where cp_organization_process in ('SIRO', 'ISRO')
1014 connect by prior ele.organization_id_child = ele.organization_id_parent
1015 and ele.org_structure_version_id = cp_org_structure_version_id
1016 start with ele.organization_id_parent = cp_organization_id_start
1017 and ele.org_structure_version_id = cp_org_structure_version_id) TREE
1018 where TREE.organization_id_child = org.organization_id
1019 UNION
1020 select org.organization_id organization_id_group
1021 , org.organization_id organization_id_child
1022 from hr_organization_units org
1023 where org.organization_id = cp_organization_id_start
1024 order by 1,2;
1025 --
1026 l_org_id Number;
1027 l_process Varchar2(4);
1028 --
1029 l_first Boolean;
1030 l_index Number;
1031 --
1032 begin
1033 HR_Utility.Set_Location('Load Org Hierarchy '||p_organization_process,5);
1034
1035 -- If Organization parameter is -1 this means "whole hierarachy"
1036 -- so we need to point the SQL to the top org
1037 if (p_organization_id = -1)
1038 then
1039 open c_toporg;
1040 fetch c_toporg into l_org_id;
1041 close c_toporg;
1042 l_process := 'ISNR'; -- ||substr(p_organization_process,3);
1043
1044 else
1045 l_org_id := p_organization_id;
1046 l_process := p_organization_process;
1047 end if;
1048
1049 -- Clear both PL/SQL tables
1050 OrgTable.Delete;
1051 OrgIndex.Delete;
1052
1053 -- Populate OrgTable and OrgIndex
1054 for r_main in c_main
1055 ( l_org_id, p_org_struct_version_id, l_process )
1056 loop
1057 for r_child in c_child
1058 ( r_main.organization_id_start, p_org_struct_version_id, l_process )
1059 loop
1060 Add_Org
1061 ( r_child.organization_id_group
1062 , r_child.organization_id_child );
1063 end loop;
1064 end loop;
1065
1066 -- Determine lexical for use in Reports
1067 if (l_process in ('SIRO', 'ISRO'))
1068 then
1069 l_first := TRUE;
1070 l_index := OrgIndex.First;
1071
1072 loop
1073 if (l_first)
1074 then
1075 p_org_list := 'in (' || to_char(l_index);
1076 l_first := FALSE;
1077 else
1078 p_org_list := p_org_list || ',' || to_char(l_index);
1079 end if;
1080
1081 exit when (l_index = OrgIndex.Last);
1082 l_index := OrgIndex.Next(l_index);
1083 end loop;
1084
1085 p_org_list := p_org_list || ')';
1086
1087 else
1088 p_org_list := '= 0';
1089
1090 end if;
1091
1092 end LoadOrgHierarchy;
1093 --
1094 ---------------------------------------------------------------------------
1095 --
1096 function OrgInHierarchy
1097 ( p_organization_id Number )
1098 return Number is
1099 begin
1100 return (Find_Org
1101 ( p_org_id_group => p_organization_id
1102 , p_org_id_child => p_organization_id ));
1103 end OrgInHierarchy;
1104 --
1105
1106 function OrgInHierarchy
1107 ( p_organization_id_group Number
1108 , p_organization_id_child Number )
1109 return Number is
1110 begin
1111 if (p_organization_id_group = -1)
1112 then
1113 return (Find_Org
1114 ( p_org_id_group => p_organization_id_child
1115 , p_org_id_child => p_organization_id_child ));
1116 else
1117 return (Find_Org
1118 ( p_org_id_group => p_organization_id_group
1119 , p_org_id_child => p_organization_id_child ));
1120 end if;
1121 end OrgInHierarchy;
1122 --
1123 ---------------------------------------------------------------------------
1124 --
1125 function GetOrgStructElement
1126 return Number is
1127
1128 -- This cursor finds the top element in the current version of the
1129 -- primary org hierarchy within the responsibilities business group
1130 --
1131 -- It can return >1 row, they all have the same ORGANIZATION_ID_PARENT
1132 -- therefore just use the 1st one;
1133 --
1134 cursor c_get_element_id
1135 ( cp_bus_id Number
1136 , cp_str_id Number )
1137 is
1138 select ose.org_structure_element_id
1139 from per_organization_structures ost
1140 , per_org_structure_versions osv
1141 , per_org_structure_elements ose
1142 where ost.business_group_id = cp_bus_id
1143 and ost.organization_structure_id = cp_str_id
1144 and ost.organization_structure_id = osv.organization_structure_id
1145 and osv.org_structure_version_id = ose.org_structure_version_id
1146 and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
1147 and not exists
1148 ( select null
1149 from per_org_structure_elements ose2
1150 where ose2.org_structure_version_id = osv.org_structure_version_id
1151 and ose.organization_id_parent = ose2.organization_id_child );
1152
1153 l_business_group_id Number := hr_bis.get_sec_profile_bg_id ;
1154 l_org_structure_id Number := GetReportingHierarchy;
1155 l_org_structure_element_id Number;
1156
1157 BEGIN
1158 if (nvl(l_business_group_id,-1) = -1)
1159 then
1160 l_org_structure_element_id := -1;
1161
1162 else
1163 open c_get_element_id
1164 ( l_business_group_id
1165 , l_org_structure_id );
1166
1167 fetch c_get_element_id into l_org_structure_element_id;
1168 close c_get_element_id;
1169 end if;
1170
1171 return (l_org_structure_element_id);
1172
1173 exception
1174 when Others then
1175 return (-1);
1176
1177 END GetOrgStructElement;
1178 --
1179 ---------------------------------------------------------------------------
1180 --
1181 function GetOrgStructVersion
1182 return Number is
1183
1184 -- This cursor finds the current version of the
1185 -- primary org hierarchy within the responsibilities business group
1186 --
1187 cursor c_get_version_id
1188 ( cp_bus_id Number
1189 , cp_str_id Number )
1190 is
1191 select osv.org_structure_version_id
1192 from per_organization_structures ost
1193 , per_org_structure_versions osv
1194 where ost.business_group_id = cp_bus_id
1195 and ost.organization_structure_id = cp_str_id
1196 and ost.organization_structure_id = osv.organization_structure_id
1197 and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate);
1198
1199 l_business_group_id Number := hr_bis.get_sec_profile_bg_id ;
1200 l_org_structure_id Number := GetReportingHierarchy;
1201 l_org_structure_version_id Number;
1202
1203 BEGIN
1204 if (nvl(l_business_group_id,-1) = -1)
1205 then
1206 l_org_structure_version_id := -1;
1207
1208 else
1209 open c_get_version_id
1210 ( l_business_group_id
1211 , l_org_structure_id );
1212
1213 fetch c_get_version_id into l_org_structure_version_id;
1214 close c_get_version_id;
1215 end if;
1216
1217 return (l_org_structure_version_id);
1218
1219 exception
1220 when Others then
1221 return (-1);
1222
1223 END GetOrgStructVersion;
1224 --
1225 ---------------------------------------------------------------------------
1226 -- Initialize package globals for use in the report
1227 ----------------------------------------------------
1228 PROCEDURE Initialize
1229 ( p_user_id IN Number
1230 , p_resp_id IN Number
1231 , p_resp_appl_id IN Number
1232 , p_business_group_id OUT NOCOPY Number
1233 , p_org_structure_version_id OUT NOCOPY Number
1234 , p_sec_group_id IN Number default 0 )
1235 is
1236
1237 cursor c_org_structure_version
1238 ( cp_bus_id Number
1239 , cp_str_id Number )
1240 is
1241 select v.org_structure_version_id
1242 from per_organization_structures s
1243 , per_org_structure_versions v
1244 where s.business_group_id = cp_bus_id
1245 and s.organization_structure_id = cp_str_id
1246 and s.organization_structure_id = v.organization_structure_id
1247 and trunc(sysdate) between nvl(v.date_from,trunc(sysdate)) and nvl(v.date_to,sysdate);
1248
1249 l_business_group_id Number;
1250 l_org_structure_id Number;
1251 l_org_structure_version_id Number;
1252
1253 BEGIN
1254 FND_Global.Apps_Initialize
1255 ( user_id => p_user_id
1256 , resp_id => p_resp_id
1257 , resp_appl_id => p_resp_appl_id
1258 , security_group_id => p_sec_group_id );
1259
1260 -- bug 2968520
1261 l_business_group_id := hr_bis.get_sec_profile_bg_id;
1262
1263 if (l_business_group_id is not null)
1264 then
1265 l_org_structure_id := GetReportingHierarchy;
1266
1267 open c_org_structure_version
1268 ( l_business_group_id
1269 , l_org_structure_id );
1270 fetch c_org_structure_version into l_org_structure_version_id;
1271 close c_org_structure_version;
1272
1273 -- Set package globals
1274 g_business_group_id := l_business_group_id;
1275 g_org_structure_version_id := l_org_structure_version_id;
1276
1277 -- Return values to report
1278 p_business_group_id := l_business_group_id;
1279 p_org_structure_version_id := l_org_structure_version_id;
1280 end if;
1281
1282 END Initialize;
1283 --
1284 ---------------------------------------------------------------------------
1285 --
1286 PROCEDURE ClearLeavingReasons IS
1287 l_count number;
1288 BEGIN
1289 --
1290 -- Clear down global PL/SQL table
1291 for l_count in 1..9 loop
1292 HrFastAnswers.LeavingReasons(l_count) := '';
1293 end loop;
1294 --
1295 END ClearLeavingReasons;
1296 --
1297 ---------------------------------------------------------------------------
1298 --
1299 FUNCTION GetLeavingReasons RETURN LeavingReasonsType IS
1300 l_leaving_reasons LeavingReasonsType;
1301 BEGIN
1302 l_leaving_reasons := HrFastAnswers.LeavingReasons;
1303 return( l_leaving_reasons );
1304 END GetLeavingReasons;
1305 --
1306 ---------------------------------------------------------------------------
1307 --
1308 PROCEDURE SetLeavingReasons(
1309 p_index IN NUMBER
1310 ,p_value IN VARCHAR2 ) IS
1311 --
1312 BEGIN
1313 HrFastAnswers.LeavingReasons( p_index ) := p_value;
1314 END SetLeavingReasons;
1315 --
1316 ---------------------------------------------------------------------------
1317 FUNCTION get_poplist(p_select_statement VARCHAR2
1318 ,p_parameter_list VARCHAR2
1319 ,p_parameter_name VARCHAR2
1320 ,p_parameter_value VARCHAR2
1321 ,p_report_name VARCHAR2
1322 ,p_report_link VARCHAR2) RETURN VARCHAR2 AS
1323 l_poplist VARCHAR2(32767);
1324 l_cursorID INTEGER;
1325 l_new_select_statement VARCHAR2(2000);
1326 --l_name VARCHAR2(80);
1327 --l_code VARCHAR2(80);
1328 --l_from VARCHAR2(2000);
1329 l_point1 INTEGER;
1330 l_point2 INTEGER;
1331 l_length1 INTEGER;
1332 l_code_out VARCHAR2(80);
1333 l_name_out VARCHAR2(80);
1334 l_dummy VARCHAR2(1);
1335 --
1336 l_parameters VARCHAR2(2000);
1337 --
1338 BEGIN
1339 --
1340 -- open the cursor
1341 l_cursorID:=DBMS_SQL.OPEN_CURSOR;
1342 --
1343 l_new_select_statement:=p_select_statement;
1344 --
1345 l_poplist:='<form><select name="'||p_parameter_name||
1346 '" onChange="window.location=form.'||p_parameter_name||
1347 '.options[form.'||p_parameter_name||'.selectedIndex].value" size=1>';
1348 --
1349 l_point1:=instr(p_parameter_list,p_parameter_name);
1350 l_point2:=instr(p_parameter_list,'*',l_point1);
1351 --
1352 l_parameters:=substr(p_parameter_list,0,l_point1-1)||substr(p_parameter_list,l_point2+1);
1353 l_parameters:=l_parameters||p_parameter_name||'=';
1354 --
1355 -- Parse the query
1356 DBMS_SQL.PARSE(l_cursorID,l_new_select_statement,dbms_sql.v7);
1357 --
1358 -- Define the outputs
1359 DBMS_SQL.DEFINE_COLUMN(l_cursorID,1,l_code_out,80);
1360 DBMS_SQL.DEFINE_COLUMN(l_cursorID,2,l_name_out,80);
1361 --
1362 -- Execute the query
1363 l_dummy:=DBMS_SQL.EXECUTE(l_cursorID);
1364 --
1365 -- Loop over the output rows, building our statement
1366 LOOP
1367 -- fetch the next row and check it exists
1368 IF (DBMS_SQL.FETCH_ROWS(l_cursorID)=0) THEN
1369 hr_utility.set_location('get poplist',10);
1370 EXIT;
1371 END IF;
1372 --
1373 DBMS_SQL.COLUMN_VALUE(l_cursorID,1,l_code_out);
1374 DBMS_SQL.COLUMN_VALUE(l_cursorID,2,l_name_out);
1375 --
1376 l_poplist:=l_poplist||'<option ';
1377 --
1378 if(p_parameter_value=l_code_out) then
1379 l_poplist:=l_poplist||'SELECTED ';
1380 end if;
1381 --
1382 l_poplist:=l_poplist||'value='||p_report_link||'OracleOASIS.RunReport?parameters='||l_parameters||l_code_out;
1383 l_poplist:=l_poplist||'*plsql_basepath='||p_report_link||'*paramform=no*';
1384 l_poplist:=l_poplist||fnd_global.local_chr(38)||'report='||p_report_name||'>'||l_name_out; /*changed for bug 3282860*/
1385 --
1386
1387 --
1388 END LOOP;
1389 --
1390 l_poplist:=l_poplist||'</select>
1391 </form>';
1392 --
1393 return l_poplist;
1394 END get_poplist;
1395 --
1396 function business_group_id return NUMBER is
1397 begin
1398 if(g_business_group_id is not null) then
1399 return(g_business_group_id);
1400 else
1401 return(-1);
1402 end if;
1403 exception
1404 when others then
1405 return (-1);
1406 end business_group_id;
1407 --
1408 --
1409 function org_structure_version_id return NUMBER is
1410 begin
1411 if(G_ORG_STRUCTURE_VERSION_ID is not null) then
1412 return(G_ORG_STRUCTURE_VERSION_ID);
1413 else
1414 return(-1);
1415 end if;
1416 exception
1417 when others then
1418 return (-1);
1419 end org_structure_version_id;
1420 --
1421 function ConvertToHours
1422 ( p_formula_id in Number
1423 , p_assignment_id in Number
1424 , p_screen_value in Varchar2
1425 , p_uom in Varchar2
1426 , p_effective_date in Date
1427 , p_session_date in Date )
1428 return Number is
1429 k_seconds_per_hour Constant Number := 60*60;
1430
1431 l_days Number := 0;
1432 l_hours Number := 0;
1433 l_seconds Number := 0;
1434
1435 l_ff_inputs FF_Exec.Inputs_t;
1436 l_ff_outputs FF_Exec.Outputs_t;
1437
1438 begin
1439 if (p_uom like 'H_DECIMAL%') or (p_uom = 'H_HH')
1440 then
1441 l_hours := to_number(p_screen_value);
1442
1443 elsif (p_uom = 'H_HHMM')
1444 then
1445 l_seconds := to_number(to_char(to_date(p_screen_value,'HH:MI'),'SSSSS'));
1446 l_hours := l_seconds / k_seconds_per_hour;
1447
1448 elsif (p_uom = 'H_HHMMSS')
1449 then
1450 l_seconds := to_number(to_char(to_date(p_screen_value,'HH:MI:SS'),'SSSSS'));
1451 l_hours := l_seconds / k_seconds_per_hour;
1452
1453 elsif (p_uom in ('I','N','ND'))
1454 then
1455 l_days := to_number(p_screen_value);
1456
1457 -- Initialise the Inputs and Outputs tables
1458 FF_Exec.Init_Formula
1459 ( p_formula_id => p_formula_id
1460 , p_effective_date => p_session_date
1461 , p_inputs => l_ff_inputs
1462 , p_outputs => l_ff_outputs );
1463
1464 -- Set up context values for the formula
1465 for i in l_ff_inputs.first .. l_ff_inputs.last
1466 loop
1467
1468 if (l_ff_inputs(i).name = 'DATE_EARNED')
1469 then
1470 l_ff_inputs(i).value := FND_Date.Date_To_Canonical(p_effective_date);
1471
1472 elsif (l_ff_inputs(i).name = 'ASSIGNMENT_ID')
1473 then
1474 l_ff_inputs(i).value := p_assignment_id;
1475
1476 elsif (l_ff_inputs(i).name = 'DAYS_WORKED')
1477 then
1478 l_ff_inputs(i).value := l_days;
1479
1480 end if;
1481
1482 end loop;
1483
1484 -- Run the formula and get the return value
1485 FF_Exec.Run_Formula
1486 ( p_inputs => l_ff_inputs
1487 , p_outputs => l_ff_outputs);
1488
1489 l_hours := to_number(l_ff_outputs(l_ff_outputs.first).value);
1490
1491 else
1492 l_hours := 0;
1493
1494 end if;
1495
1496 return (l_hours);
1497
1498 exception
1499 when others then
1500 return (0);
1501
1502 end ConvertToHours;
1503 --
1504 FUNCTION TrainingConvertDuration
1505 ( p_formula_id In Number
1506 , p_from_duration In Number
1507 , p_from_units In Varchar2
1508 , p_to_units In Varchar2
1509 , p_activity_version_name In Varchar2
1510 , p_event_name In Varchar2
1511 , p_session_date In Date )
1512 RETURN NUMBER IS
1513 l_inputs FF_Exec.Inputs_T;
1514 l_outputs FF_Exec.Outputs_T;
1515
1516 BEGIN
1517 -- Initialise the Inputs and Outputs tables
1518 FF_Exec.Init_Formula
1519 ( p_formula_id
1520 , p_session_date
1521 , l_inputs
1522 , l_outputs );
1523
1524 if (l_inputs.first is not null)
1525 and (l_inputs.last is not null)
1526 then
1527 -- Set up context values for the formula
1528 for i in l_inputs.first..l_inputs.last loop
1529
1530 if l_inputs(i).name = 'FROM_DURATION' then
1531 l_inputs(i).value := to_char(p_from_duration);
1532
1533 elsif l_inputs(i).name = 'FROM_DURATION_UNITS' then
1534 l_inputs(i).value := p_from_units;
1535
1536 elsif l_inputs(i).name = 'TO_DURATION_UNITS' then
1537 l_inputs(i).value := p_to_units;
1538
1539 elsif l_inputs(i).name = 'ACTIVITY_VERSION_NAME' then
1540 l_inputs(i).value := p_activity_version_name;
1541
1542 elsif l_inputs(i).name = 'EVENT_NAME' then
1543 l_inputs(i).value := p_event_name;
1544 end if;
1545
1546 end loop;
1547 end if;
1548
1549 -- Run the formula
1550 FF_Exec.Run_Formula (l_inputs, l_outputs);
1551
1552 return (to_number(l_outputs(l_outputs.first).value));
1553
1554 END TrainingConvertDuration;
1555
1556 -------------------------------------------------------------------------------
1557 -- Function to return the correct Location_Id for the Revenue Model reports.
1558 -- This function is called from package HrViewBy in HRIRPRT.pll
1559 --
1560 -- It determines the Location_Id by travelling up the following hierarchy;
1561 -- 1. Assignment Location
1562 -- 2. Position Location
1563 -- 3. Organization Location
1564 -- 4. Business Group Location
1565 --
1566 -- Rewritten by S.Bhattal, 05-JAN-2000, version 115.11, bug 1123310.
1567 -- (as a result of bugs found during Release 11i system testing)
1568 -------------------------------------------------------------------------------
1569
1570 function GetLocationId
1571 ( p_level IN Number
1572 , p_location_id IN Number
1573 , p_position_id IN Number
1574 , p_organization_id IN Number
1575 , p_business_group_id IN Number )
1576 return number is
1577
1578 cursor location_csr is
1579 select country
1580 from hr_locations
1581 where location_id = p_location_id;
1582
1583 cursor c_get_loc_pos is
1584 select pos.location_id
1585 ,loc.country
1586 from hr_locations loc
1587 ,per_positions pos
1588 where pos.position_id = p_position_id
1589 and pos.location_id = loc.location_id;
1590
1591 cursor c_get_loc_org is
1592 select org.location_id
1593 ,loc.country
1594 from hr_locations loc
1595 ,hr_organization_units org
1596 where org.organization_id = p_organization_id
1597 and org.location_id = loc.location_id;
1598
1599 cursor c_get_loc_bus is
1600 select bg.location_id
1601 ,loc.country
1602 from hr_locations loc
1603 ,per_business_groups bg
1604 where bg.business_group_id = p_business_group_id
1605 and bg.location_id = loc.location_id;
1606
1607 l_country hr_locations.country%type;
1608 l_location_id hr_locations.location_id%type;
1609 l_region hr_locations.attribute1%type;
1610
1611 type RefCursorType is REF CURSOR;
1612 ref_csr RefCursorType;
1613
1614 begin
1615
1616 if p_level in (1,2) then -- Geography level = 'Area' or 'Country'
1617
1618 if (p_location_id is not null) then
1619 open location_csr;
1620 fetch location_csr into l_country;
1621 close location_csr;
1622 end if;
1623
1624 if (l_country is not null) then
1625 l_location_id := p_location_id;
1626 else
1627
1628 if (p_position_id is not null) then
1629 open c_get_loc_pos;
1630 fetch c_get_loc_pos into l_location_id, l_country;
1631 close c_get_loc_pos;
1632 end if;
1633
1634 if (l_country is null) then
1635
1636 if (p_organization_id is not null) then
1637 open c_get_loc_org;
1638 fetch c_get_loc_org into l_location_id, l_country;
1639 close c_get_loc_org;
1640 end if;
1641
1642 if (l_country is null) then
1643 open c_get_loc_bus;
1644 fetch c_get_loc_bus into l_location_id, l_country;
1645 close c_get_loc_bus;
1646
1647 if (l_country is null) then
1648 l_location_id := null;
1649 end if;
1650
1651 end if;
1652 end if;
1653 end if;
1654
1655 elsif (p_level = 3) then -- Geography level = 'Region'
1656
1657 if (p_location_id is not null) then
1658
1659 open ref_csr for
1660 'select loc.' || g_region_segment ||
1661 ' from hr_locations loc' ||
1662 ' where loc.location_id = :p_location_id'
1663 using p_location_id;
1664
1665 fetch ref_csr into l_region;
1666 close ref_csr;
1667 end if;
1668
1669 if (l_region is not null) then
1670 l_location_id := p_location_id;
1671 else
1672
1673 if (p_position_id is not null) then
1674
1675 open ref_csr for
1676 'select loc.' || g_region_segment ||
1677 ' ,loc.location_id' ||
1678 ' from hr_locations loc' ||
1679 ' , per_positions pos' ||
1680 ' where loc.location_id = pos.location_id' ||
1681 ' and pos.position_id = :p_position_id'
1682 using p_position_id;
1683
1684 fetch ref_csr into l_region, l_location_id;
1685 close ref_csr;
1686 end if;
1687
1688 if (l_region is null) then
1689 if (p_organization_id is not null) then
1690
1691 open ref_csr for
1692 'select loc.' || g_region_segment ||
1693 ' ,loc.location_id' ||
1694 ' from hr_locations loc' ||
1695 ' , hr_organization_units hou' ||
1696 ' where loc.location_id = hou.location_id' ||
1697 ' and hou.organization_id = :p_organization_id'
1698 using p_organization_id;
1699
1700 fetch ref_csr into l_region, l_location_id;
1701 close ref_csr;
1702 end if;
1703
1704 if (l_region is null) then
1705
1706 open ref_csr for
1707 'select loc.' || g_region_segment ||
1708 ' ,loc.location_id' ||
1709 ' from hr_locations loc' ||
1710 ' , per_business_groups bgr' ||
1711 ' where loc.location_id = bgr.location_id' ||
1712 ' and bgr.organization_id = :p_business_group_id'
1713 using p_business_group_id;
1714
1715 fetch ref_csr into l_region, l_location_id;
1716 close ref_csr;
1717
1718 if (l_region is null) then
1719 l_location_id := null;
1720 end if;
1721
1722 end if;
1723 end if;
1724 end if;
1725 end if;
1726
1727 return(l_location_id);
1728
1729 end GetLocationId;
1730
1731 -------------------------------------------------------------------------------
1732 -- Function to return the Geography dimension level value
1733 -- (either Area, Country or Region) for a single assignment.
1734 --
1735 -- This functionality is used by the Revenue Model reports.
1736 -- This function is called from package HrViewBy in HRIRPRT.pll
1737 --
1738 -- It is passed the Location Id, Position Id, Organization Id and
1739 -- Business Group Id of the assignment concerned.
1740 --
1741 -- It determines the Area,Country or Region by travelling up the following
1742 -- hierarchy;
1743 -- 1. Assignment Location
1744 -- 2. Position Location
1745 -- 3. Organization Location
1746 -- 4. Business Group Location
1747 -------------------------------------------------------------------------------
1748
1749 function GetGeographyDimension
1750 ( p_level IN Number
1751 , p_location_id IN Number
1752 , p_position_id IN Number
1753 , p_organization_id IN Number
1754 , p_business_group_id IN Number )
1755 return varchar2 is
1756
1757 -- First look at location on the assignment
1758 -- Need nvl on this query so that if a country is not
1759 -- assigned to an area the record will bring back a
1760 -- value of Unassigned. This will stop it going on down
1761 -- to the next level as it's found a record
1762 cursor get_area is
1763 select nvl(ter.parent_territory_code,'Unassigned')
1764 from bis_territory_hierarchies_v ter
1765 , hr_locations loc
1766 where ter.child_territory_code(+) = loc.country
1767 and decode(ter.parent_territory_type,null,'AREA'
1768 ,ter.parent_territory_type) = 'AREA'
1769 and loc.location_id = p_location_id;
1770
1771 -- Then look at location on the position
1772 cursor get_area_pos is
1773 select nvl(ter.parent_territory_code, 'Unassigned')
1774 from bis_territory_hierarchies_v ter
1775 , hr_locations loc
1776 , per_positions pos
1777 where ter.child_territory_code(+) = loc.country
1778 and decode(ter.parent_territory_type,null,'AREA'
1779 ,ter.parent_territory_type) = 'AREA'
1780 and pos.location_id = loc.location_id
1781 and pos.position_id = p_position_id;
1782
1783 -- Then look at location on the organization
1784 cursor get_area_org is
1785 select nvl(ter.parent_territory_code, 'Unassigned')
1786 from bis_territory_hierarchies_v ter
1787 , hr_locations loc
1788 , hr_organization_units hou
1789 where ter.child_territory_code(+) = loc.country
1790 and decode(ter.parent_territory_type,null,'AREA'
1791 ,ter.parent_territory_type) = 'AREA'
1792 and hou.location_id = loc.location_id
1793 and hou.organization_id = p_organization_id;
1794
1795 -- Then look at location on the business group
1796 -- NB. Doesn't matter if don't find Unassigned here as
1797 -- don't want to drill any further
1798 cursor get_area_bus is
1799 select ter.parent_territory_code
1800 from bis_territory_hierarchies_v ter
1801 , hr_locations loc
1802 , per_business_groups bgr
1803 where ter.child_territory_code = loc.country
1804 and ter.parent_territory_type = 'AREA'
1805 and bgr.location_id = loc.location_id
1806 and bgr.business_group_id = p_business_group_id;
1807
1808 -- First look at location on the assignment
1809 cursor get_country is
1810 select loc.country
1811 from hr_locations loc
1812 where loc.location_id = p_location_id;
1813
1814 -- Then look at location on the position
1815 cursor get_country_pos is
1816 select loc.country
1817 from hr_locations loc
1818 , per_positions pos
1819 where loc.location_id = pos.location_id
1820 and pos.position_id = p_position_id;
1821
1822 -- Then look at the location on the organization
1823 cursor get_country_org is
1824 select loc.country
1825 from hr_locations loc
1826 , hr_organization_units hou
1827 where loc.location_id = hou.location_id
1828 and hou.organization_id = p_organization_id;
1829
1830 -- Then look at the location on the business group
1831 cursor get_country_bus is
1832 select loc.country
1833 from hr_locations loc
1834 , per_business_groups bgr
1835 where loc.location_id = bgr.location_id
1836 and bgr.business_group_id = p_business_group_id;
1837
1838 l_geog varchar2(2000);
1839 l_sqlstring varchar2(10000);
1840
1841 TYPE RegCurType is REF CURSOR;
1842 reg_cv RegCurType;
1843
1844 begin
1845 if (p_level = 1) then
1846
1847 -- If Geography Level is 1 then we are interested in Area
1848
1849 open get_area;
1850 fetch get_area into l_geog;
1851
1852 if (l_geog is null or get_area%notfound) then
1853
1854 -- Not found at Assignment Level, now look at Position Level
1855 open get_area_pos;
1856 fetch get_area_pos into l_geog;
1857
1858 if (l_geog is null or get_area_pos%notfound) then
1859
1860 -- Not found at Position Level, now look at Organization Level
1861 open get_area_org;
1862 fetch get_area_org into l_geog;
1863
1864 if (l_geog is null or get_area_org%notfound) then
1865
1866 -- Not found at Organization Level, now look at Business Group Level
1867 open get_area_bus;
1868 fetch get_area_bus into l_geog;
1869 close get_area_bus;
1870 end if;
1871
1872 close get_area_org;
1873 end if;
1874
1875 close get_area_pos;
1876 end if;
1877
1878 close get_area;
1879
1880 elsif (p_level = 2) then
1881
1882 -- If Geography Level is 2 then we are interested in Country
1883 open get_country;
1884 fetch get_country into l_geog;
1885
1886 if (l_geog is null or get_country%notfound) then
1887
1888 -- Not found at Assignment Level, now look at Position Level
1889 open get_country_pos;
1890 fetch get_country_pos into l_geog;
1891
1892 if (l_geog is null or get_country_pos%notfound) then
1893
1894 -- Not found at Position Level, now look at Organization Level
1895 open get_country_org;
1896 fetch get_country_org into l_geog;
1897
1898 if (l_geog is null or get_country_org%notfound) then
1899
1900 -- Not found at Organization Level, now look at Business Group Level
1901 open get_country_bus;
1902 fetch get_country_bus into l_geog;
1903 close get_country_bus;
1904 end if;
1905
1906 close get_country_org;
1907 end if;
1908
1909 close get_country_pos;
1910 end if;
1911
1912 close get_country;
1913
1914 elsif (p_level = 3) then
1915
1916 -- Build and execute the dynamic sql statement
1917
1918 open reg_cv for
1919 'select loc.'||g_region_segment||
1920 ' from hr_locations loc'||
1921 ' where loc.location_id = :p_location_id'
1922 using p_location_id;
1923
1924 fetch reg_cv into l_geog;
1925
1926 if (l_geog is null or reg_cv%notfound) then
1927
1928 -- Not found at Assignment Level, now look at Position Level
1929 -- Build and execute the dynamic sql statement
1930
1931 close reg_cv;
1932 open reg_cv for
1933 'select loc.'||g_region_segment||
1934 ' from hr_locations loc'||
1935 ' , per_positions pos'||
1936 ' where loc.location_id = pos.location_id'||
1937 ' and pos.position_id = :p_position_id'
1938 using p_position_id;
1939
1940 fetch reg_cv into l_geog;
1941
1942 if (l_geog is null or reg_cv%notfound) then
1943
1944 -- Not found at Position Level, now look at Organization Level
1945 -- Build and execute the dynamic sql statement
1946
1947 close reg_cv;
1948
1949 open reg_cv for
1950 'select loc.'||g_region_segment||
1951 ' from hr_locations loc'||
1952 ' , hr_organization_units hou'||
1953 ' where loc.location_id = hou.location_id'||
1954 ' and hou.organization_id = :p_organization_id'
1955 using p_organization_id;
1956
1957 fetch reg_cv into l_geog;
1958
1959 if (l_geog is null or reg_cv%notfound) then
1960
1961 -- Not found at Organization Level, now look at Business Group Level
1962 -- Build and execute the dynamic sql statement
1963
1964 close reg_cv;
1965
1966 open reg_cv for
1967 'select loc.'||g_region_segment||
1968 ' from hr_locations loc'||
1969 ' , per_business_groups bgr'||
1970 ' where loc.location_id = bgr.location_id'||
1971 ' and bgr.organization_id = :p_business_group_id'
1972 using p_business_group_id;
1973
1974 fetch reg_cv into l_geog;
1975
1976 end if;
1977 end if;
1978 end if;
1979
1980 close reg_cv;
1981 end if;
1982
1983 return(l_geog);
1984
1985 end GetGeographyDimension;
1986
1987 -------------------------------------------------------------------------------
1988 -- New function added by S.Bhattal, 06-JAN-2000, version 115.11, bug 1123310
1989 -- This function returns the DFF segment used to hold Region.
1990 -- This function is called by package HrViewBy in report library HRIRPRT.pll
1991 -------------------------------------------------------------------------------
1992
1993 function Get_Region_Segment
1994 return varchar2 is
1995
1996 -- Region is stored in a flex segment and mapped using flex wizard
1997 cursor region_csr is
1998 select bfm.application_column_name
1999 from bis_flex_mappings_v bfm
2000 , bis_dimensions bd
2001 where bfm.dimension_id = bd.dimension_id
2002 and bd.short_name = 'GEOGRAPHY'
2003 and bfm.level_short_name = 'REGION'
2004 and bfm.application_id = 800;
2005
2006 begin
2007
2008 -- Determine which segment is being used to store the Region dimension
2009
2010 open region_csr;
2011
2012 LOOP
2013 fetch region_csr into g_region_segment;
2014 EXIT WHEN region_csr%NOTFOUND;
2015 IF region_csr%ROWCOUNT > 1 THEN
2016 g_region_segment := '*ERROR*';
2017 END IF;
2018 END LOOP;
2019
2020 close region_csr;
2021
2022 return(g_region_segment);
2023
2024 end Get_Region_Segment;
2025
2026 -------------------------------------------------------------------------------
2027 -- New procedures added by M.J.Andrews, 28-JUN-2000, version 115.14, bug 1323212
2028 -- The CheckFastFormulaCompiled procedure should be called from a report's before
2029 -- report trigger in all reports which use fast formula. It checks if the
2030 -- appropriate fast formula exists, and if it's compiled. If either is false
2031 -- then it raises the appropriate exception for the report trigger to catch and
2032 -- display.
2033 -- Raise_FF_Not_Compiled and Raise_FF_Not_Exist have been seperated out, so that
2034 -- if the formulas are uncompiled and needed in GetBudgetValue, then it raises
2035 -- the same exception, ensuring the correct error message is displayed.
2036 -------------------------------------------------------------------------------
2037
2038 PROCEDURE Raise_FF_Not_Exist
2039 ( p_bgttyp in VarChar2 )
2040 IS
2041 BEGIN
2042 Fnd_Message.Set_Name('HRI', 'HR_BIS_FF_NOT_EXIST');
2043
2044 -- Removed tokens in version 115.15
2045 -- Fnd_Message.Set_Token('BUDGET_FORMULA', 'BUDGET_'||p_bgttyp, FALSE);
2046 -- Fnd_Message.Set_Token('TEMPLATE_FORMULA', 'TEMPLATE_'||p_bgttyp, FALSE);
2047
2048 raise ff_not_exist;
2049 END Raise_FF_Not_Exist;
2050
2051 --
2052
2053 PROCEDURE Raise_FF_Not_Compiled
2054 ( p_formula_id in Number )
2055 IS
2056 cursor fast_formula_csr is
2057 select formula_name
2058 from ff_formulas_f
2059 where formula_id = p_formula_id;
2060
2061 l_formula_name ff_formulas_f.formula_name%type := null;
2062 BEGIN
2063 open fast_formula_csr;
2064 fetch fast_formula_csr into l_formula_name;
2065 close fast_formula_csr;
2066
2067 Fnd_Message.Set_Name('HRI', 'HR_BIS_FF_NOT_COMPILED');
2068 Fnd_Message.Set_Token('FORMULA', l_formula_name, FALSE);
2069
2070 raise ff_not_compiled;
2071 END Raise_FF_Not_Compiled;
2072
2073 --
2074
2075 PROCEDURE CheckFastFormulaCompiled
2076 ( p_formula_id in Number
2077 , p_bgttyp in VarChar2 )
2078 IS
2079 cursor fast_formula_compiled_csr is
2080 select formula_id
2081 from ff_compiled_info_f
2082 where formula_id = p_formula_id;
2083
2084 l_formula_id ff_compiled_info_f.formula_id%type := null;
2085
2086 BEGIN
2087 if p_formula_id is null then
2088 Raise_FF_Not_Exist( p_bgttyp );
2089 end if;
2090
2091 open fast_formula_compiled_csr;
2092 fetch fast_formula_compiled_csr into l_formula_id;
2093 close fast_formula_compiled_csr;
2094 if l_formula_id is null then
2095 Raise_FF_Not_Compiled( p_formula_id );
2096 end if;
2097
2098 END CheckFastFormulaCompiled;
2099
2100
2101 END HrFastAnswers;
[Home] [Help]