1 PACKAGE BODY hr_discoverer AS
2 /* $Header: hrdiscov.pkb 115.2 2003/12/18 05:29:47 prasharm ship $ */
3 --
4 FUNCTION time_in
5 (p_assignment_id IN NUMBER
6 ,p_mode IN VARCHAR2
7 ,p_terminate IN VARCHAR2 DEFAULT NULL
8 )
9 RETURN NUMBER
10 IS
11 l_number NUMBER := NULL;
12 --
13 -- declaring function variables
14 --
15 l_assignment_id NUMBER := NULL;
16 l_organization_id NUMBER := NULL;
17 l_job_id NUMBER := NULL;
18 l_position_id NUMBER := NULL;
19 l_grade_id NUMBER := NULL;
20 l_location_id NUMBER := NULL;
21 l_service_date_start DATE := NULL;
22 l_date_start DATE := NULL;
23 l_actual_termination_date DATE := NULL;
24 l_change_date DATE := NULL;
25 --
26 -- cursor to select an assignment as of sysdate
27 --
28 CURSOR c_get_1 IS
29 SELECT a.organization_id,
30 a.job_id,
31 a.position_id,
32 a.grade_id,
33 a.location_id,
34 b.date_start,
35 SYSDATE
36 FROM per_periods_of_service b,
37 per_assignments_f a
38 WHERE (TRUNC(SYSDATE)
39 BETWEEN TRUNC(a.effective_start_date)
40 AND TRUNC(a.effective_end_date))
41 AND a.assignment_id = p_assignment_id
42 AND a.period_of_service_id = b.period_of_service_id
43 AND ROWNUM = 1;
44 --
45 -- cursor to select an assignment which has ended before sysdate
46 --
47 CURSOR c_get_2 IS
48 SELECT a.organization_id,
49 a.job_id,
50 a.position_id,
51 a.grade_id,
52 a.location_id,
53 b.date_start,
54 b.actual_termination_date
55 FROM per_periods_of_service b,
56 per_assignment_status_types c,
57 per_assignments_f a
58 WHERE (TRUNC(SYSDATE) > TRUNC(a.effective_end_date))
59 AND a.assignment_id = p_assignment_id
60 AND a.period_of_service_id = b.period_of_service_id
61 AND a.assignment_status_type_id = c.assignment_status_type_id
62 AND c.per_system_status = 'ACTIVE_ASSIGN'
63 AND a.effective_end_date = b.actual_termination_date
64 AND TRUNC(b.final_process_date)
65 = TRUNC(b.actual_termination_date)
66 AND ROWNUM = 1;
67 --
68 BEGIN
69 --
70 -- selection modes SERVICE, ORGANIZATION, JOB, POSITION, GRADE, LOCATION
71 --
72 IF p_terminate = 'TERMINATE' THEN
73 SELECT a.organization_id,
74 a.job_id,
75 a.position_id,
76 a.grade_id,
77 a.location_id,
78 b.date_start,
79 b.actual_termination_date
80 INTO l_organization_id,
81 l_job_id,
82 l_position_id,
83 l_grade_id,
84 l_location_id,
85 l_service_date_start,
86 l_actual_termination_date
87 FROM per_periods_of_service b,
88 per_assignments_f a,
89 per_assignment_status_types c
90 WHERE a.assignment_id = p_assignment_id
91 AND a.period_of_service_id = b.period_of_service_id
92 AND a.assignment_status_type_id = c.assignment_status_type_id
93 AND ROWNUM = 1
94 AND ((TRUNC(SYSDATE) >= TRUNC(a.effective_start_date)
95 AND c.per_system_status = 'TERM_ASSIGN')
96 OR (TRUNC(SYSDATE) > TRUNC(a.effective_end_date)
97 AND b.final_process_date = b.actual_termination_date
98 AND c.per_system_status = 'ACTIVE_ASSIGN')
99 OR (TRUNC(SYSDATE)
100 BETWEEN TRUNC(a.effective_start_date)
101 AND TRUNC(a.effective_end_date))
102 AND c.per_system_status not in ('ACTIVE_ASSIGN','TERM_ASSIGN'));
103 ELSE
104 OPEN c_get_1;
105 FETCH c_get_1
106 INTO l_organization_id,
107 l_job_id,
108 l_position_id,
109 l_grade_id,
110 l_location_id,
111 l_service_date_start,
112 l_actual_termination_date;
113 IF c_get_1%NOTFOUND THEN
114 OPEN c_get_2;
115 FETCH c_get_2
116 INTO l_organization_id,
117 l_job_id,
118 l_position_id,
119 l_grade_id,
120 l_location_id,
121 l_service_date_start,
122 l_actual_termination_date;
123 CLOSE c_get_2;
124 END IF;
125 CLOSE c_get_1;
126 END IF;
127 --
128 --
129 -- MODE SERVICE
130 --
131 -- identify time in service
132 --
133 IF l_actual_termination_date IS NOT NULL THEN
134 IF p_mode = 'SERVICE' THEN
135 --
136 l_date_start := l_service_date_start;
137 --
138 -- MODE ORGANIZATION
139 --
140 -- identify time in organization
141 --
142 ELSIF p_mode = 'ORGANIZATION' THEN
143 --
144 SELECT MIN(ass.effective_start_date)
145 INTO l_date_start
146 FROM per_assignments_f ass
147 WHERE ass.organization_id = l_organization_id
148 AND ass.assignment_id = p_assignment_id
149 AND ass.assignment_type = 'E'
150 AND NOT EXISTS
151 (
152 SELECT null
153 FROM per_assignments_f ass1
154 WHERE ass1.assignment_id = ass.assignment_id
155 AND NVL(ass1.organization_id,9.9)+0 = NVL(ass.organization_id,9.9)+0
156 AND ass1.effective_start_date =
157 (
158 SELECT MAX(ass2.effective_start_date)
159 FROM per_assignments_f ass2
160 WHERE ass2.assignment_id = ass1.assignment_id
161 AND ass2.effective_start_date < ass.effective_start_date
162 )
163 AND ass1.assignment_type = 'E'
164 )
165 AND ass.business_group_id+0=
166 NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id); /* changed for bug 3294224 */
167 --
168 -- checking for organization changes
169 --
170 SELECT MAX(cass.effective_end_date+1)
171 INTO l_change_date
172 FROM per_assignments_f cass
173 WHERE cass.assignment_id = p_assignment_id
174 AND cass.organization_id <> l_organization_id
175 AND cass.effective_start_date > l_date_start
176 AND cass.effective_end_date < l_actual_termination_date;
177 --
178 -- MODE JOB
179 --
180 -- identify time in job
181 --
182 ELSIF p_mode = 'JOB' THEN
183 --
184 SELECT MIN(ass.effective_start_date)
185 INTO l_date_start
186 FROM per_assignments_f ass
187 WHERE ass.job_id = l_job_id
188 AND ass.assignment_id = p_assignment_id
189 AND ass.assignment_type = 'E'
190 AND NOT EXISTS
191 (
192 SELECT null
193 FROM per_assignments_f ass1
194 WHERE ass1.assignment_id = ass.assignment_id
195 AND NVL(ass1.job_id,9.9)+0 = NVL(ass.job_id,9.9)+0
196 AND ass1.effective_start_date =
197 (
198 SELECT MAX(ass2.effective_start_date)
199 FROM per_assignments_f ass2
200 WHERE ass2.assignment_id = ass1.assignment_id
201 AND ass2.effective_start_date < ass.effective_start_date
202 )
203 AND ass1.assignment_type = 'E'
204 )
205 AND ass.business_group_id+0=
206 NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id); /* changed for bug 3294224 */
207 --
208 -- checking for job changes
209 --
210 SELECT MAX(cass.effective_end_date+1)
211 INTO l_change_date
212 FROM per_assignments_f cass
213 WHERE cass.assignment_id = p_assignment_id
214 AND cass.job_id <> l_job_id
215 AND cass.effective_start_date > l_date_start
216 AND cass.effective_end_date < l_actual_termination_date;
217 --
218 -- MODE POSITION
219 --
220 -- identify time in position
221 --
222 ELSIF p_mode = 'POSITION' THEN
223 --
224 SELECT MIN(ass.effective_start_date)
225 INTO l_date_start
226 FROM per_assignments_f ass
227 WHERE ass.position_id = l_position_id
228 AND ass.assignment_id = p_assignment_id
229 AND ass.assignment_type = 'E'
230 AND NOT EXISTS
231 (
232 SELECT null
233 FROM per_assignments_f ass1
234 WHERE ass1.assignment_id = ass.assignment_id
235 AND NVL(ass1.position_id,9.9)+0 =
236 NVL(ass.position_id,9.9)+0
237 AND ass1.effective_start_date =
238 (
239 SELECT MAX(ass2.effective_start_date)
240 FROM per_assignments_f ass2
241 WHERE ass2.assignment_id = ass1.assignment_id
242 AND ass2.effective_start_date < ass.effective_start_date
243 )
244 AND ass1.assignment_type = 'E'
245 )
246 AND ass.business_group_id+0=
247 NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id); /* changed for bug 3294224 */
248 --
249 -- checking for position changes
250 --
251 SELECT MAX(cass.effective_end_date+1)
252 INTO l_change_date
253 FROM per_assignments_f cass
254 WHERE cass.assignment_id = p_assignment_id
255 AND cass.position_id <> l_position_id
256 AND cass.effective_start_date > l_date_start
257 AND cass.effective_end_date < l_actual_termination_date;
258 --
259 -- MODE GRADE
260 --
261 -- identify time in grade
262 --
263 ELSIF p_mode = 'GRADE' THEN
264 --
265 SELECT MIN(ass.effective_start_date)
266 INTO l_date_start
267 FROM per_assignments_f ass
268 WHERE ass.grade_id = l_grade_id
269 AND ass.assignment_id = p_assignment_id
270 AND ass.assignment_type = 'E'
271 AND NOT EXISTS
272 (
273 SELECT null
274 FROM per_assignments_f ass1
275 WHERE ass1.assignment_id = ass.assignment_id
276 AND NVL(ass1.grade_id,9.9)+0 = NVL(ass.grade_id,9.9)+0
277 AND ass1.effective_start_date =
278 (
279 SELECT MAX(ass2.effective_start_date)
280 FROM per_assignments_f ass2
281 WHERE ass2.assignment_id = ass1.assignment_id
282 AND ass2.effective_start_date < ass.effective_start_date
283 )
284 AND ass1.assignment_type = 'E'
285 )
286 AND ass.business_group_id+0=
287 NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id); /* changed for bug 3294224 */
288 --
289 -- checking for grade changes
290 --
291 SELECT MAX(cass.effective_end_date+1)
292 INTO l_change_date
293 FROM per_assignments_f cass
294 WHERE cass.assignment_id = p_assignment_id
295 AND cass.grade_id <> l_grade_id
296 AND cass.effective_start_date > l_date_start
297 AND cass.effective_end_date < l_actual_termination_date;
298 --
299 -- MODE LOCATION
300 --
301 -- identify time in location
302 --
303 ELSIF p_mode = 'LOCATION' THEN
304 --
305 SELECT MIN(ass.effective_start_date)
306 INTO l_date_start
307 FROM per_assignments_f ass
308 WHERE ass.location_id = l_location_id
309 AND ass.assignment_id = p_assignment_id
310 AND ass.assignment_type = 'E'
311 AND NOT EXISTS
312 (
313 SELECT null
314 FROM per_assignments_f ass1
315 WHERE ass1.assignment_id = ass.assignment_id
316 AND NVL(ass1.location_id,9.9)+0 =
317 NVL(ass.location_id,9.9)+0
318 AND ass1.effective_start_date =
319 (
320 SELECT MAX(ass2.effective_start_date)
321 FROM per_assignments_f ass2
322 WHERE ass2.assignment_id = ass1.assignment_id
323 AND ass2.effective_start_date < ass.effective_start_date
324 )
325 AND ass1.assignment_type = 'E'
326 )
327 AND ass.business_group_id+0=
328 NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id); /* changed for bug 3294224 */
329 --
330 -- checking for location changes
331 --
332 SELECT MAX(cass.effective_end_date+1)
333 INTO l_change_date
334 FROM per_assignments_f cass
335 WHERE cass.assignment_id = p_assignment_id
336 AND cass.location_id <> l_location_id
337 AND cass.effective_start_date > l_date_start
338 AND cass.effective_end_date < l_actual_termination_date;
339 END IF;
340 --
341 -- if there have been more recent changes then update
342 -- the start date with that of the most recent change
343 --
344 IF l_change_date IS NOT NULL THEN
345 l_date_start := l_change_date;
346 END IF;
347 --
348 -- FOR ASSIGNMENT(SERVICE):
349 -- calculate months between the start date of the assignment
350 -- and sysdate or termination date whichever is earliest.
351 -- FOR ORGANIZATION, JOB, POSITION, GRADE, LOCATION
352 -- calculate months between the start date of the organization
353 -- job, position, grade or location and the end of the organization
354 -- job, position, grade or location or sysdate whichever is earliest
355 --
356 IF TRUNC(l_actual_termination_date) < TRUNC(SYSDATE) THEN
357 l_number :=
358 TRUNC(MONTHS_BETWEEN(TRUNC(l_actual_termination_date+1),
359 TRUNC(l_date_start)));
360 ELSE
361 l_number :=
362 TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE+1),
363 TRUNC(l_date_start)));
364 END IF;
365 END IF;
366 RETURN l_number;
367 END time_in;
368 --
369 -- function over 70 check
370 --
371 --
372 -- this function checks to see if a person is over 70 years of age
373 -- it returns 1 for true and 0 for false.
374 --
375 FUNCTION over_70_check
376 (p_date_of_birth IN DATE
377 )
378 RETURN NUMBER IS
379 l_number NUMBER;
380 --
381 BEGIN
382 --
383 IF (TRUNC((SYSDATE-p_date_of_birth)/365)) > 70
384 THEN
385 l_number := 1;
386 ELSE
387 l_number := 0;
388 END IF;
389 RETURN l_number;
390 END over_70_check;
391 --
392 -- function end_date
393 --
394 -- this function checks to see if a date is equivalent to the end of time
395 -- if this is true it will return null.
396 --
397 FUNCTION check_end_date
398 (p_end_date IN DATE
399 )
400 RETURN DATE IS
401 l_end_date DATE;
402 --
403 BEGIN
404 --
405 IF p_end_date = hr_general.end_of_time THEN
406 l_end_date := NULL;
407 ELSE
408 l_end_date := p_end_date;
409 END IF;
410 --
411 RETURN l_end_date;
412 --
413 END check_end_date;
414 --
415 -- this function get the actual budget values
416 --
417 FUNCTION get_actual_budget_values
418 (p_unit IN VARCHAR2,
419 p_bus_group_id IN NUMBER,
420 p_organization_id IN NUMBER,
421 p_job_id IN NUMBER,
422 p_position_id IN NUMBER,
423 p_grade_id IN NUMBER,
424 p_start_date IN DATE,
425 p_end_date IN DATE,
426 p_actual_val IN NUMBER
427 )
428 RETURN NUMBER IS
429 l_actual_end_val NUMBER;
430 l_actual_start_val NUMBER;
431 l_variance_amount NUMBER;
432 l_variance_percent VARCHAR2(240);
433 --
434 BEGIN
435 hrgetact.get_actuals(p_unit,
436 p_bus_group_id,
437 p_organization_id,
438 p_job_id,
439 p_position_id,
440 p_grade_id,
441 p_start_date,
442 p_end_date,
443 p_actual_val,
444 l_actual_start_val,
445 l_actual_end_val,
446 l_variance_amount,
447 l_variance_percent);
448 return l_actual_end_val;
449 END get_actual_budget_values;
450 --
451 END hr_discoverer;
[Home] [Help]