[Home] [Help]
PACKAGE BODY: APPS.HR_SPP_WI_DATA
Source
1 PACKAGE BODY HR_SPP_WI_DATA AS
2 /* $Header: pesppwif.pkb 120.0 2005/05/31 21:35:17 appldev noship $ */
3
4 function get_placement_id_val(p_placement_id NUMBER) return NUMBER is
5 begin
6 return(SPP_WI_DATA(p_placement_id).placement_id_val);
7 exception
8 when others then
9 return null;
10 end;
11 --
12 function get_assignment_number_val(p_placement_id NUMBER) return VARCHAR2 is
13 begin
14 return(SPP_WI_DATA(p_placement_id).assignment_number_val);
15 exception
16 when others then
17 return null;
18 end;
19 --
20 function get_pay_scale_val(p_placement_id NUMBER) return VARCHAR2 is
21 begin
22 return(SPP_WI_DATA(p_placement_id).pay_scale_val);
23 exception
24 when others then
25 return null;
26 end;
27 --
28 function get_grade_name_val(p_placement_id NUMBER) return VARCHAR2 is
29 begin
30 return(SPP_WI_DATA(p_placement_id).grade_name_val);
31 exception
32 when others then
33 return null;
34 end;
35 --
36 function get_old_spinal_point_val(p_placement_id NUMBER) return VARCHAR2 is
37 begin
38 return(SPP_WI_DATA(p_placement_id).old_spinal_point_val);
39 exception
40 when others then
41 return null;
42 end;
43 --
44 function get_new_spinal_point_val(p_placement_id NUMBER) return VARCHAR2 is
45 begin
46 return(SPP_WI_DATA(p_placement_id).new_spinal_point_val);
47 exception
48 when others then
49 return null;
50 end;
51 --
52 function get_old_value_val(p_placement_id NUMBER) return NUMBER is
53 begin
54 return(SPP_WI_DATA(p_placement_id).old_value_val);
55 exception
56 when others then
57 return null;
58 end;
59 --
60 function get_new_value_val(p_placement_id NUMBER) return NUMBER is
61 begin
62 return(SPP_WI_DATA(p_placement_id).new_value_val);
63 exception
64 when others then
65 return null;
66 end;
67 --
68 function get_difference_val(p_placement_id NUMBER) return NUMBER is
69 begin
70 return(SPP_WI_DATA(p_placement_id).difference_val);
71 exception
72 when others then
73 return null;
74 end;
75 --
76 function get_full_name_val(p_placement_id NUMBER) return VARCHAR2 is
77 begin
78 return(SPP_WI_DATA(p_placement_id).full_name_val);
79 exception
80 when others then
81 return null;
82 end;
83 --
84 function get_assignment_id_val(p_placement_id NUMBER) return NUMBER is
85 begin
86 return(SPP_WI_DATA(p_placement_id).assignment_id_val);
87 exception
88 when others then
89 return null;
90 end;
91 --
92 function get_org_name_val(p_placement_id NUMBER) return VARCHAR2 is
93 begin
94 return(SPP_WI_DATA(p_placement_id).org_name_val);
95 exception
96 when others then
97 return null;
98 end;
99
100 procedure populate_spp_wi_table
101 (p_placement_id number
102 ,p_assignment_id number
103 ,p_effective_date date
104 ,p_parent_spine_id number
105 ,p_step_id number
106 ,p_spinal_point_id number
107 ,p_rate_id number
108 ) is
109
110 -- Bug fix 3571874. Cursor modified to check rate_type while min(rate_id)
111 -- is fetched.
112 cursor csr_point_values( l_old_spinal_point_id number ,
113 l_new_spinal_point_id number ) is
114 select to_number(pgr1.value),
115 to_number(pgr2.value)
116 from pay_grade_rules_f pgr1,
117 pay_grade_rules_f pgr2
118 where pgr1.grade_or_spinal_point_id = l_old_spinal_point_id
119 and pgr2.grade_or_spinal_point_id = l_new_spinal_point_id
120 and pgr1.rate_type = 'SP'
121 and pgr2.rate_type = 'SP'
122 and pgr1.rate_id = (select min(rate_id)
123 from pay_grade_rules_f pgr3
124 where pgr1.grade_or_spinal_point_id = pgr3.grade_or_spinal_point_id
125 and pgr3.rate_type = 'SP'
126 and (p_rate_id is null
127 or p_rate_id = pgr3.rate_id))
128 and pgr2.rate_id = (select min(rate_id)
129 from pay_grade_rules_f pgr4
130 where pgr2.grade_or_spinal_point_id = pgr4.grade_or_spinal_point_id
131 and pgr4.rate_type = 'SP'
132 and (p_rate_id is null
133 or p_rate_id = pgr4.rate_id))
134 and p_effective_date between pgr2.effective_start_date
135 and pgr2.effective_end_date
136 and (p_effective_date - 1) between pgr1.effective_start_date
137 and pgr1.effective_end_date;
138
139 l_full_name varchar2(60);
140 l_pay_scale varchar2(30);
141 l_old_spinal_point varchar2(30);
142 l_new_spinal_point varchar2(30);
143 l_old_value number;
144 l_new_value number;
145 l_grade_name per_grades.name%TYPE;
146 l_old_spinal_point_id number;
147 l_new_spinal_point_id number;
148 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
149 l_effective_start_date date;
150 l_org_name varchar2(60);
151
152 begin
153 hr_utility.set_location(p_placement_id,191);
154 hr_utility.set_location(p_assignment_id,191);
155 hr_utility.set_location(p_effective_date,191);
156 hr_utility.set_location(p_parent_spine_id,191);
157 hr_utility.set_location(p_step_id,191);
158 hr_utility.set_location(p_spinal_point_id,191);
159
160 select distinct substr(pap.full_name,1,60) ,paa.assignment_number,substr(org.name,1,60)
161 into l_full_name ,l_assignment_number,l_org_name
162 from per_all_people_f pap,
163 per_all_assignments_f paa,
164 hr_all_organization_units org
165 where pap.person_id = paa.person_id
166 and paa.organization_id = org.organization_id
167 and paa.assignment_id = p_assignment_id
168 and p_effective_date between paa.effective_start_date
169 and paa.effective_end_date
170 and p_effective_date between pap.effective_start_date -- 2276901
171 and pap.effective_end_date; -- 2276901
172
173 --
174 -- get the pay scale name
175 --
176 hr_utility.set_location(' Pay scale name',121);
177 hr_utility.set_location('parent_spine_id'||p_parent_spine_id,121);
178 select substr(pps.name,1,30)
179 into l_pay_scale
180 from per_parent_spines pps
181 where pps.parent_spine_id = p_parent_spine_id;
182
183 --
184 -- get the grade name
185 --
186 hr_utility.set_location('grade name',122);
187 select substr(pg.name,1,30)
188 into l_grade_name
189 from per_grades_vl pg,
190 per_spinal_point_steps_f sps,
191 per_grade_spines_f pgs
192 where pg.grade_id = pgs.grade_id
193 and pgs.grade_spine_id = sps.grade_spine_id
194 and sps.step_id = p_step_id
195 and p_effective_date between sps.effective_start_date -- 2276901
196 and sps.effective_end_date -- 2276901
197 and p_effective_date between pgs.effective_start_date -- 2276901
198 and pgs.effective_end_date; -- 2276901
199 -- and sps.spinal_point_id = p_spinal_point_id;
200
201 select min(effective_start_date)
202 into l_effective_start_date
203 from per_spinal_point_placements_f
204 where placement_id = p_placement_id;
205
206 if l_effective_start_date = p_effective_date
207 then
208
209 select substr(psp.spinal_point,1,30),
210 psp.spinal_point_id
211 into l_new_spinal_point,
212 l_new_spinal_point_id
213 from per_spinal_points psp,
214 per_spinal_point_steps_f sps
215 where psp.spinal_point_id = sps.spinal_point_id
216 and sps.step_id = p_step_id
217 and p_effective_date between sps.effective_start_date
218 and sps.effective_end_date;
219
220 l_old_spinal_point := 'NULL';
221 l_old_spinal_point_id := null;
222
223 else
224
225 --
226 -- get the old and new spinal points
227 --
228 hr_utility.set_location('spinal points',123);
229 select substr(psp1.spinal_point,1,30),
230 substr(psp2.spinal_point,1,30),
231 psp1.spinal_point_id,
232 psp2.spinal_point_id
233 into l_old_spinal_point,
234 l_new_spinal_point,
235 l_old_spinal_point_id,
236 l_new_spinal_point_id
237 from per_spinal_points psp1,
238 per_spinal_points psp2,
239 per_spinal_point_placements_f spp,
240 per_spinal_point_steps_f sps1,
241 per_spinal_point_steps_f sps2
242 where psp1.spinal_point_id = sps1.spinal_point_id
243 and psp2.spinal_point_id = sps2.spinal_point_id
244 and sps2.step_id = p_step_id
245 and sps1.step_id = spp.step_id
246 and spp.placement_id = p_placement_id
247 and spp.effective_end_date = p_effective_date - 1
248 and p_effective_date between sps1.effective_start_date
249 and sps1.effective_end_date
250 and p_effective_date between sps2.effective_start_date
251 and sps2.effective_end_date;
252
253 end if;
254
255 hr_utility.set_location('l_old_spinal_point:'||l_old_spinal_point,124);
256 hr_utility.set_location('l_new_spinal_point:'||l_new_spinal_point,124);
257 hr_utility.set_location('l_old_spinal_point_id:'||l_old_spinal_point_id,124);
258
259 open csr_point_values(l_old_spinal_point_id,l_new_spinal_point_id);
260 fetch csr_point_values into l_old_value,l_new_value;
261 if csr_point_values%notfound then
262 l_old_value := 0;
263 l_new_value := 0;
264 end if;
265 close csr_point_values;
266
267
268 SPP_WI_DATA(p_placement_id).placement_id_val := p_placement_id;
269 SPP_WI_DATA(p_placement_id).assignment_id_val := p_assignment_id;
270 SPP_WI_DATA(p_placement_id).assignment_number_val := l_assignment_number;
271 SPP_WI_DATA(p_placement_id).full_name_val := l_full_name;
272 SPP_WI_DATA(p_placement_id).pay_scale_val := l_pay_scale;
273 SPP_WI_DATA(p_placement_id).grade_name_val := l_grade_name;
274 SPP_WI_DATA(p_placement_id).old_spinal_point_val := l_old_spinal_point;
275 SPP_WI_DATA(p_placement_id).new_spinal_point_val := l_new_spinal_point;
276 SPP_WI_DATA(p_placement_id).old_value_val := l_old_value;
277 SPP_WI_DATA(p_placement_id).new_value_val := l_new_value;
278 SPP_WI_DATA(p_placement_id).difference_val := (l_new_value - l_old_value);
279 SPP_WI_DATA(p_placement_id).org_name_val := l_org_name;
280
281 end populate_spp_wi_table;
282
283 END HR_SPP_WI_DATA;