1 PACKAGE BODY PER_GET_ASG_POS AS
2 /* $Header: pegetpos.pkb 115.2 99/07/18 13:53:41 porting ship $ */
3 function start_date (p_assignment_id in number,
4 p_position_id in number,
5 p_effective_start_date in date,
6 p_effective_end_date in date) return date is
7 -- set up the variables used in the cursor
8
9 c_effective_start_date date := null;
10 c_effective_end_date date := null;
11 r_effective_start_date date := p_effective_start_date;
12
13 cursor c1 is
14 select effective_start_date,
15 effective_end_date
16 from per_all_assignments_f x
17 where p_assignment_id = x.assignment_id
18 and p_position_id = x.position_id
19 and p_effective_start_date >= x.effective_start_date
20 order by x.effective_start_date desc;
21 begin
22 open c1;
23 loop
24 fetch c1 into c_effective_start_date, c_effective_end_date ;
25 exit when c1%notfound;
26 if r_effective_start_date -1 = c_effective_end_date then
27 r_effective_start_date := c_effective_start_date;
28 end if ;
29 end loop;
30 close c1;
31 return r_effective_start_date;
32 end start_date;
33 --
34 function end_date (p_assignment_id in number,
35 p_position_id in number,
36 p_effective_start_date in date,
37 p_effective_end_date in date ) return date is
38
39 -- set up the variables used in the cursor
40
41 c_effective_start_date date := null;
42 c_effective_end_date date := null;
43 r_effective_end_date date := p_effective_end_date;
44
45
46 -- Get the start and end dates of rows >= the row passed in
47 cursor c1 is
48 select asg.effective_start_date,
49 asg.effective_end_date
50 from per_all_assignments_f asg
51 ,per_assignment_status_types past
52 where p_assignment_id = assignment_id
53 and p_position_id = position_id
54 and p_effective_start_date <= effective_start_date
55 and ( asg.assignment_status_type_id = past.assignment_status_type_id
56 and past.per_system_status <> 'TERM_ASSIGN')
57 order by asg.effective_start_date asc;
58
59 begin
60 open c1;
61 loop
62 fetch c1 into c_effective_start_date, c_effective_end_date;
63 exit when c1%notfound;
64 -- if the next record is contiguous, use it's EED
65 if r_effective_end_date = c_effective_start_date -1 then
66 r_effective_end_date := c_effective_end_date;
67 end if;
68 end loop;
69 close c1;
70 -- If date is eot, NULL is returned
71 if r_effective_end_date <> hr_general.end_of_time then
72 return r_effective_end_date;
73 else
74 return NULL;
75 end if;
76 end end_date;
77 --
78 end per_get_asg_pos;
[Home] [Help]