1 package body hrgetact as
2 /* $Header: pegetact.pkb 115.2 99/07/18 13:53:35 porting ship $ */
3 --
4 --
5 /* Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved */
6 /*
7 PRODUCT
8 Oracle*Personnel
9 --
10 NAME
11 pegetact.pkb - calculate assignment budget values
12 --
13 DESCRIPTION
14 This procedure was converted to PL/SQL from the release 9 'C' procedure,
15 (file peruga.lpc).
16 calculate head count called from personnel screen PERBUDBU.
17 --
18 This module is called to prevent the form from having to carry out time
19 consuming calculations.
20 It selects the sum of all assignment budget values for all assignments
21 active on the effective_start_date and for all assignments active on the
22 effective_end_date. These values are returned as the start and end value.
23 --
24 The original value from the form is subtracted from the end value to give a
25 return value for the variance, and this is multiplied by 100 to give
26 the percentage variance.
27 --
28 MODIFIED (DD-MON-YYYY)
29 sasmith 31-MAR-98 110.1 - Change of table from per_assignment_budget_values to
30 per_assignment_budget_values_f as this table is now datetracked.
31 Also added the effective start/end dates when these tables are
32 being referenced. Required to restrict the rows to pick up the
33 correct actual values. Code has changed to use cursors.
34 rfine 23-NOV-94 70.8 - Suppressed index on business_group_id
35 rfine 25-OCT-94 70.7 - Fixed bug G1450. The counts of active
36 assignments were not excluding those with a
37 status of Terminated Assignment, resulting in
38 counts which included some leavers.
39 mwcallag 01-MAR-1994 - p_variance_percent set to zero if both
40 p_actual_val and l_variance_amount are zero.
41 mwcallag 11-MAY-1993 - p_variance_percent changed to varchar2 to handle
42 percentage values that are too large
43 mwcallag 07-MAY-1993 - created
44 */
45 ------------------------------------------------------------------------------------
46 procedure get_actuals
47 (
48 p_unit in varchar2,
49 p_bus_group_id in number,
50 p_organisation_id in number,
51 p_job_id in number,
52 p_position_id in number,
53 p_grade_id in number,
54 p_start_date in date,
55 p_end_date in date,
56 p_actual_val in number,
57 p_actual_start_val out number,
58 p_actual_end_val out number,
59 p_variance_amount out number,
60 p_variance_percent out varchar2
61 ) is
62 l_actual_end_val number;
63 l_variance_amount number;
64 l_variance_percent number;
65
66
67 BEGIN
68 --
69 -- Change to code from per_assignment_budget_values to per_assignment_budget_values_f,
70 -- as this table is now date tracked. Also include ref to effective dates.
71 -- Inclusion of effective dates between the start and end dates to ensure
72 -- only 1 row is returned.
73 -- To be used for the start and end date actual values summation.
74 -- Major change to code from doing a 'sum(decode(greatest' to using cursors which simplify
75 -- the logic. Also it was not possible to leave the existing logic to achieve functionality.
76 -- SASmith 31-MAR-1998
77
78 IF (p_organisation_id is null) then
79
80 hr_utility.set_location ('hrgetact.get_actuals', 5);
81 DECLARE
82 CURSOR C IS
83 SELECT NVL(SUM(ABV.VALUE),0)
84 FROM per_assignment_budget_values_f abv,
85 per_assignment_status_types ast,
86 per_all_assignments_f asg
87 WHERE asg.business_group_id + 0 = p_bus_group_id
88 AND asg.assignment_id = abv.assignment_id
89 AND abv.unit = p_unit
90 AND asg.assignment_type = 'E'
91 AND (p_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
92 AND (p_start_date BETWEEN abv.effective_start_date AND abv.effective_end_date)
93 AND NVL(p_organisation_id, NVL(asg.organization_id, -999)) =
94 NVL(asg.organization_id, -999)
95 AND NVL(p_job_id, NVL(asg.job_id, -999)) =
96 NVL(asg.job_id, -999)
97 AND NVL(p_position_id, NVL(asg.position_id, -999)) =
98 NVL(asg.position_id, -999)
99 AND NVL(p_grade_id, NVL(asg.grade_id, -999)) =
100 NVL(asg.grade_id, -999)
101 --
102 -- G1450. New code to join to the per_system_status and ensure it isn't
103 -- TERM_ASSIGN i.e. that we don't pick up terminated assignments
104 -- RMF v70.7 25.10.94.
105 --
106
107 AND asg.assignment_status_type_id = ast.assignment_status_type_id
108 AND ast.per_system_status <> 'TERM_ASSIGN' ;
109 --
110 --
111 BEGIN
112 OPEN C;
113 FETCH C INTO p_actual_start_val;
114 IF (C%NOTFOUND) THEN
115 p_actual_start_val := 0;
116 hr_utility.set_location ('hrgetact.get_actuals', 10);
117 END IF;
118 CLOSE C;
119 END;
120 hr_utility.set_location ('hrgetact.get_actuals', 15);
121 --
122 DECLARE
123 CURSOR C2 IS
124 SELECT NVL(SUM(ABV.VALUE),0)
125 FROM per_assignment_budget_values_f abv,
126 per_assignment_status_types ast,
127 per_all_assignments_f asg
128 WHERE asg.business_group_id + 0 = p_bus_group_id
129 AND asg.assignment_id = abv.assignment_id
130 AND abv.unit = p_unit
131 AND asg.assignment_type = 'E'
132 AND (p_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
133 AND (p_end_date BETWEEN abv.effective_start_date AND abv.effective_end_date)
134 AND NVL(p_organisation_id, NVL(asg.organization_id, -999)) =
135 NVL(asg.organization_id, -999)
136 AND NVL(p_job_id, NVL(asg.job_id, -999)) =
137 NVL(asg.job_id, -999)
138 AND NVL(p_position_id, NVL(asg.position_id, -999)) =
139 NVL(asg.position_id, -999)
140 AND NVL(p_grade_id, NVL(asg.grade_id, -999)) =
141 NVL(asg.grade_id, -999)
142 --
143 -- G1450. New code to join to the per_system_status and ensure it isn't
144 -- TERM_ASSIGN i.e. that we don't pick up terminated assignments
145 -- RMF v70.7 25.10.94.
146 --
147
148 AND asg.assignment_status_type_id = ast.assignment_status_type_id
149 AND ast.per_system_status <> 'TERM_ASSIGN' ;
150 --
151 --
152 BEGIN
153 OPEN C2;
154 FETCH C2 INTO l_actual_end_val;
155 IF (C2%NOTFOUND) THEN
156 l_actual_end_val := 0;
157 hr_utility.set_location ('hrgetact.get_actuals', 20);
158 END IF;
159 CLOSE C2;
160 END;
161
162 ELSE
163 hr_utility.set_location ('hrgetact.get_actuals', 25);
164 DECLARE
165 CURSOR C IS
166 SELECT NVL(SUM(ABV.VALUE),0)
167 FROM per_assignment_budget_values_f abv,
168 per_assignment_status_types ast,
169 per_all_assignments_f asg
170 WHERE asg.business_group_id + 0 = p_bus_group_id
171 AND asg.assignment_id = abv.assignment_id
172 AND abv.unit = p_unit
173 AND asg.assignment_type = 'E'
174 AND (p_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
175 AND (p_start_date BETWEEN abv.effective_start_date AND abv.effective_end_date)
176
177 AND p_organisation_id = asg.organization_id
178 AND NVL(p_job_id, NVL(asg.job_id, -999)) =
179 NVL(asg.job_id, -999)
180 AND NVL(p_position_id, NVL(asg.position_id, -999)) =
181 NVL(asg.position_id, -999)
182 AND NVL(p_grade_id, NVL(asg.grade_id, -999)) =
183 NVL(asg.grade_id, -999)
184 --
185 -- G1450. New code to join to the per_system_status and ensure it isn't
186 -- TERM_ASSIGN i.e. that we don't pick up terminated assignments
187 -- RMF v70.7 25.10.94.
188 --
189 AND asg.assignment_status_type_id = ast.assignment_status_type_id
190 AND ast.per_system_status <> 'TERM_ASSIGN' ;
191
192 --
193 BEGIN
194 OPEN C;
195 FETCH C INTO p_actual_start_val;
196 IF (C%NOTFOUND) THEN
197 p_actual_start_val := 0;
198 hr_utility.set_location ('hrgetact.get_actuals', 30);
199 END IF;
200 CLOSE C;
201 END;
202 --
203 hr_utility.set_location ('hrgetact.get_actuals', 35);
204 DECLARE
205 CURSOR C2 IS
206 SELECT NVL(SUM(ABV.VALUE),0)
207 FROM per_assignment_budget_values_f abv,
208 per_assignment_status_types ast,
209 per_all_assignments_f asg
210 WHERE asg.business_group_id + 0 = p_bus_group_id
211 AND asg.assignment_id = abv.assignment_id
212 AND abv.unit = p_unit
213 AND asg.assignment_type = 'E'
214 AND (p_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
215 AND (p_end_date BETWEEN abv.effective_start_date AND abv.effective_end_date)
216
217 AND p_organisation_id = asg.organization_id
218 AND NVL(p_job_id, NVL(asg.job_id, -999)) =
219 NVL(asg.job_id, -999)
220 AND NVL(p_position_id, NVL(asg.position_id, -999)) =
221 NVL(asg.position_id, -999)
222 AND NVL(p_grade_id, NVL(asg.grade_id, -999)) =
223 NVL(asg.grade_id, -999)
224 --
225 -- G1450. New code to join to the per_system_status and ensure it isn't
226 -- TERM_ASSIGN i.e. that we don't pick up terminated assignments
227 -- RMF v70.7 25.10.94.
228 --
229 AND asg.assignment_status_type_id = ast.assignment_status_type_id
230 AND ast.per_system_status <> 'TERM_ASSIGN' ;
231
232 --
233 BEGIN
234 OPEN C2;
235 FETCH C2 INTO l_actual_end_val;
236 IF (C2%NOTFOUND) THEN
237 l_actual_end_val := 0;
238 hr_utility.set_location ('hrgetact.get_actuals', 40);
239 END IF;
240 CLOSE C2;
241 END;
242
243 END IF;
244 hr_utility.set_location ('hrgetact.get_actuals',45);
245
246
247 --------------------------------------------------------------------
248
249 -- calculate the variance values
250 --
251 l_variance_amount := l_actual_end_val - p_actual_val;
252 --
253 if (p_actual_val <> 0) then
254 l_variance_percent := (l_variance_amount * 100) / p_actual_val;
255 --
256 -- if percentage greater than form field size then set overflow
257 --
258 if (l_variance_percent > 99999) OR (l_variance_percent < -9999) then
259 p_variance_percent := '#####'; -- overflow
260 else
261 p_variance_percent := substr (to_char (l_variance_percent), 1, 5);
262 end if;
263 elsif ((p_actual_val = 0) and (l_variance_amount = 0)) then
264 p_variance_percent := to_char (0);
265 else
266 p_variance_percent := '#####'; -- overflow
267 end if;
268 --
269 p_actual_end_val := l_actual_end_val;
270 p_variance_amount := l_variance_amount;
271
272 end get_actuals;
273 end hrgetact;
[Home] [Help]