1 Package Body PER_POSITION_MAPPING
2 -- $Header: perpomap.pkb 115.4 99/10/18 20:40:31 porting shi $
3 IS
4 function get_position_id
5 ( p_name IN varchar2,
6 p_effective_date IN date )
7 return number
8 is
9 l_position_id number(15) ;
10 cursor c1 is select position_id from hr_all_positions_f
11 where name = p_name
12 and effective_end_date = to_date('31124712','ddmmyyyy');
13 BEGIN
14 open c1 ;
15 fetch c1 into l_position_id ;
16 close c1;
17 return l_position_id ;
18 END;
19
20 function get_position_definition_id
21 ( p_name IN varchar2,
22 p_effective_date IN date )
23 return number
24 is
25 l_position_definition_id number(15) ;
26 cursor c1 is select position_definition_id from hr_all_positions_f
27 where name = p_name
28 and effective_end_date = to_date('31124712','ddmmyyyy');
29 BEGIN
30 open c1 ;
31 fetch c1 into l_position_definition_id ;
32 close c1;
33 return l_position_definition_id ;
34 END;
35
36 function get_prior_position_id
37 ( p_prior_position_name IN varchar2,
38 p_effective_date IN date )
39 return number
40 is
41 l_position_id number(15) ;
42 BEGIN
43 l_position_id := get_position_id(p_prior_position_name,p_effective_date);
44 return l_position_id ;
45 END;
46
47 function get_supervisor_position_id
48 ( p_supervisor_position_name IN varchar2,
49 p_effective_date IN date )
50 return number
51 is
52 l_position_id number(15) ;
53 BEGIN
54 l_position_id := get_position_id(p_supervisor_position_name,p_effective_date);
55 return l_position_id ;
56 END;
57
58 function get_successor_position_id
59 ( p_successor_position_name IN varchar2,
60 p_effective_date IN date )
61 return number
62 is
63 l_position_id number(15) ;
64 BEGIN
65 l_position_id := get_position_id(p_successor_position_name,p_effective_date);
66 return l_position_id ;
67 END;
68
69 function get_relief_position_id
70 ( p_relief_position_name IN varchar2,
71 p_effective_date IN date )
72 return number
73 is
74 l_position_id number(15) ;
75 BEGIN
76 l_position_id := get_position_id(p_relief_position_name,p_effective_date);
77 return l_position_id ;
78 END;
79
80 function get_pay_freq_payroll_id (
81 p_pay_freq_payroll_name varchar2
82 , p_business_group_id number )
83 return number is
84 cursor csr_lookup is
85 select payroll_id
86 from pay_all_payrolls_f pay, fnd_sessions f
87 where payroll_name = p_pay_freq_payroll_name
88 and f.effective_date between
89 pay.effective_start_date and pay.effective_end_date
90 and f.session_id = userenv ('sessionid')
91 and pay.business_group_id = p_business_group_id ;
92 v_payroll_id number(15) := null;
93 begin
94 if p_pay_freq_payroll_name is not null then
95 open csr_lookup;
96 fetch csr_lookup into v_payroll_id;
97 close csr_lookup;
98 end if;
99 return v_payroll_id;
100 end get_pay_freq_payroll_id;
101
102 function get_entry_step_id (
103 p_spinal_point varchar2
104 , p_effective_date date
105 , p_business_group_id number)
106 return number is
107 cursor csr_step is
108 select sps.step_id
109 from per_spinal_point_steps_f sps, per_spinal_points psp
110 where p_effective_date between sps.effective_start_date
111 and sps.effective_end_date
112 and sps.business_group_id = psp.business_group_id
113 and psp.business_group_id = p_business_group_id
114 and sps.spinal_point_id = psp.spinal_point_id
115 and psp.spinal_point = p_spinal_point ;
116 v_step_id number(15) := null;
117 begin
118 if p_spinal_point is not null and p_effective_date is not null then
119 open csr_step;
120 fetch csr_step into v_step_id;
121 close csr_step;
122 end if;
123 return v_step_id;
124 end get_entry_step_id;
125
126 function get_availability_status_id (
127 p_shared_type_name varchar2
128 ,p_system_type_cd varchar2
129 ,p_business_group_id number )
130 return number is
131 cursor csr_lookup is
132 select shared_type_id
133 from per_shared_types_vl
134 where shared_type_name = p_shared_type_name
135 and lookup_type = 'POSITION_AVAILABILITY_STATUS'
136 and system_type_cd = p_system_type_cd
137 and nvl(business_group_id,p_business_group_id) = p_business_group_id;
138 v_shared_type_id number(15) := null;
139 begin
140 if p_shared_type_name is not null then
141 open csr_lookup;
142 fetch csr_lookup into v_shared_type_id;
143 close csr_lookup;
144 end if;
145 return v_shared_type_id;
146 end get_availability_status_id;
147
148 function get_position_ovn
149 ( p_name IN varchar2,
150 p_effective_date IN date )
151 return number
152 is
153 l_position_ovn number(15) ;
154 cursor c1 is select object_version_number from hr_all_positions_f
155 where name = p_name
156 and effective_end_date = to_date('31124712','ddmmyyyy');
157 BEGIN
158 open c1 ;
159 fetch c1 into l_position_ovn ;
160 close c1;
161 return l_position_ovn ;
162 END;
163
164 END;
[Home] [Help]