1 PACKAGE BODY hri_oltp_disc_utlztn AS
2 /* $Header: hriodutl.pkb 115.2 2003/01/24 10:07:20 jtitmas noship $ */
3
4 g_formula_type_id NUMBER;
5
6
7 /******************************************************************************/
8 /* Coverts value to hours depending on the unit of measure given */
9 /******************************************************************************/
10 FUNCTION convert_entry_to_hours( p_assignment_id IN NUMBER,
11 p_business_group_id IN NUMBER,
12 p_screen_value IN VARCHAR2,
13 p_uom IN VARCHAR2,
14 p_effective_date IN DATE)
15 RETURN NUMBER IS
16
17 l_seconds_per_hour CONSTANT NUMBER := 60*60;
18 l_days NUMBER;
19 l_hours NUMBER;
20 l_seconds NUMBER;
21
22 BEGIN
23
24 /* If UOM is in hours then no conversion required */
25 IF (p_uom LIKE 'H_DECIMAL%' OR p_uom = 'H_HH') THEN
26
27 l_hours := TO_NUMBER(p_screen_value);
28
29 /* If UOM is in hours and minutes */
30 ELSIF (p_uom = 'H_HHMM') THEN
31
32 /* convert to seconds and then to hours */
33 l_seconds := TO_NUMBER(TO_CHAR(TO_DATE(p_screen_value,'HH:MI'),'SSSSS'));
34 l_hours := l_seconds / l_seconds_per_hour;
35
36 /* If UOM is in hours, minutes and seconds */
37 ELSIF (p_uom = 'H_HHMMSS') THEN
38
39 /* convert to seconds and then to hours */
40 l_seconds := to_number(to_char(to_date(p_screen_value,'HH:MI:SS'),'SSSSS'));
41 l_hours := l_seconds / l_seconds_per_hour;
42
43 /* If UOM is in days, call the fast formula via the bpl package to convert */
44 ELSIF (p_uom in ('I','N','ND')) THEN
45
46 l_days := TO_NUMBER(p_screen_value);
47 l_hours := hri_bpl_utilization.convert_days_to_hours
48 (p_assignment_id => p_assignment_id,
49 p_business_group_id => p_business_group_id,
50 p_effective_date => p_effective_date,
51 p_session_date => SYSDATE,
52 p_number_of_days => l_days);
53
54 END IF;
55
56 RETURN l_hours;
57
58 EXCEPTION WHEN OTHERS THEN
59
60 RETURN to_number(null);
61
62 END convert_entry_to_hours;
63
64 /******************************************************************************/
65 /* Look up formula id and run formula to get hours worked */
66 /******************************************************************************/
67 FUNCTION calc_hours_worked_from_formula
68 (p_formula_name IN VARCHAR2,
69 p_assignment_id IN NUMBER,
70 p_business_group_id IN NUMBER,
71 p_effective_date IN DATE)
72 RETURN NUMBER IS
73
74 l_hours_worked NUMBER;
75 l_formula_id NUMBER;
76 l_ff_inputs FF_Exec.Inputs_t;
77 l_ff_outputs FF_Exec.Outputs_t;
78
79 BEGIN
80
81 SELECT formula_id INTO l_formula_id
82 FROM ff_formulas_f
83 WHERE formula_type_id = g_formula_type_id
84 AND formula_name = p_formula_name
85 AND TRUNC(sysdate) BETWEEN effective_start_date AND effective_end_date
86 AND (business_group_id = p_business_group_id
87 OR (business_group_id IS NULL AND p_business_group_id IS NULL));
88
89 -- Initialise the Inputs and Outputs tables
90 FF_Exec.Init_Formula
91 ( p_formula_id => l_formula_id
92 , p_effective_date => SYSDATE
93 , p_inputs => l_ff_inputs
94 , p_outputs => l_ff_outputs );
95
96 -- Set up context values for the formula
97 FOR i IN l_ff_inputs.first .. l_ff_inputs.last LOOP
98
99 IF (l_ff_inputs(i).name = 'DATE_EARNED') THEN
100 l_ff_inputs(i).value := FND_Date.Date_To_Canonical(p_effective_date);
101 ELSIF (l_ff_inputs(i).name = 'ASSIGNMENT_ID') THEN
102 l_ff_inputs(i).value := p_assignment_id;
103 END IF;
104
105 END LOOP;
106
107 -- Run the formula and get the return value
108 FF_Exec.Run_Formula
109 ( p_inputs => l_ff_inputs
110 , p_outputs => l_ff_outputs);
111
112 l_hours_worked := TO_NUMBER(l_ff_outputs(l_ff_outputs.first).value);
113
114 RETURN l_hours_worked;
115
116 EXCEPTION WHEN OTHERS THEN
117
118 RETURN to_number(null);
119
120 END calc_hours_worked_from_formula;
121
122 /******************************************************************************/
123 /* Initialize formula type id */
124 /******************************/
125 BEGIN
126
127 SELECT formula_type_id INTO g_formula_type_id
128 FROM ff_formula_types
129 WHERE formula_type_name = 'QuickPaint';
130
131 END hri_oltp_disc_utlztn;
[Home] [Help]