1 PACKAGE BODY PER_GET_ASG_POS AS
2 /* $Header: pegetpos.pkb 120.2 2011/06/20 09:28:14 srannama noship $ */
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 -- start changes for 8357127
58 and assignment_type in (
59 -- added in 12631799
60 select assignment_type
61 from per_all_assignments_f
62 where assignment_id = p_assignment_id
63 and p_effective_start_date between effective_start_date and effective_end_date
64 )
65 -- end changes for 8357127
66 order by asg.effective_start_date asc;
67
68 begin
69 open c1;
70 loop
71 fetch c1 into c_effective_start_date, c_effective_end_date;
72 exit when c1%notfound;
73 -- if the next record is contiguous, use it's EED
74 if r_effective_end_date = c_effective_start_date -1 then
75 r_effective_end_date := c_effective_end_date;
76 end if;
77 end loop;
78 close c1;
79 -- If date is eot, NULL is returned
80 if r_effective_end_date <> hr_general.end_of_time then
81 return r_effective_end_date;
82 else
83 return NULL;
84 end if;
85 end end_date;
86 --
87 end per_get_asg_pos;
[Home] [Help]