1 PACKAGE BODY HR_SPP_EXCEPTION_DATA AS
2 /* $Header: pesppexc.pkb 115.20 2003/07/11 13:29:53 vramanai noship $ */
3
4 --
5 function get_full_name_val(p_placement_id NUMBER) return VARCHAR2 is
6 begin
7 return(SPPDATA(p_placement_id).full_name_val);
8 exception
9 when others then
10 return ('0');
11 end;
12 --
13 function get_start_date_val(p_placement_id NUMBER) return DATE is
14 begin
15 return(SPPDATA(p_placement_id).start_date_val);
16 exception
17 when others then
18 return null;
19 end;
20 --
21 function get_end_date_val(p_placement_id NUMBER) return DATE is
22 begin
23 return(SPPDATA(p_placement_id).end_date_val);
24 exception
25 when others then
26 return null;
27 end;
28 --
29 function get_assignment_number_val(p_placement_id NUMBER) return varchar2 is
30 begin
31 return(SPPDATA(p_placement_id).assignment_number_val);
32 exception
33 when others then
34 return (0);
35 end;
36 --
37 function get_increment_number_val(p_placement_id NUMBER) return NUMBER is
38 begin
39 return(SPPDATA(p_placement_id).increment_number_val);
40 exception
41 when others then
42 return (0);
43 end;
44 --
45 function get_sequence_number_val(p_placement_id NUMBER) return NUMBER is
46 begin
47 return(SPPDATA(p_placement_id).sequence_number_val);
48 exception
49 when others then
50 return (0);
51 end;
52 --
53 function get_next_sequence_number_val(p_placement_id NUMBER) return NUMBER is
54 begin
55 return(SPPDATA(p_placement_id).next_sequence_number_val);
56 exception
57 when others then
58 return (0);
59 end;
60 --
61 function get_original_inc_number_val(p_placement_id NUMBER) return NUMBER is
62 begin
63 return(SPPDATA(p_placement_id).original_inc_number_val);
64 exception
65 when others then
66 return (0);
67 end;
68 --
69 function get_spinal_point_val(p_placement_id NUMBER) return NUMBER is
70 begin
71 return(SPPDATA(p_placement_id).spinal_point_val);
72 exception
73 when others then
74 return (0);
75 end;
76 --
77 function get_reason_val(p_placement_id NUMBER) return VARCHAR2 is
78 begin
79 return(SPPDATA(p_placement_id).reason_val);
80 exception
81 when others then
82 return null;
83 end;
84 --
85 function get_pay_scale_val(p_placement_id NUMBER) return VARCHAR2 is
86 begin
87 return(SPPDATA(p_placement_id).pay_scale_val);
88 exception
89 when others then
90 return null;
91 end;
92 --
93 function get_grade_name_val(p_placement_id NUMBER) return VARCHAR2 is
94 begin
95 return(SPPDATA(p_placement_id).grade_name_val);
96 exception
97 when others then
98 return null;
99 end;
100 --
101
102 function get_placement_id_val(p_placement_id NUMBER) return NUMBER is
103 begin
104 return(SPPDATA(p_placement_id).placement_id_val);
105 exception
106 when others then
107 return null;
108 end;
109 --
110
111 function get_org_name_val(p_placement_id NUMBER) return VARCHAR2 is
112 begin
113 return(SPPDATA(p_placement_id).org_name_val);
114 exception
115 when others
116 then
117 return ('0');
118 end;
119 --
120
121 procedure populate_spp_table
122 (p_effective_date date
123 ,p_placement_id number
124 ,p_effective_start_date date
125 ,p_effective_end_date date
126 ,p_assignment_id number
127 ,p_parent_spine_id number
128 ,p_increment_number number
129 ,p_original_increment_number number
130 ,p_sequence_number number
131 ,p_next_sequence_number number
132 ,p_spinal_point_id number
133 ,p_step_id number
134 ,p_new_step_id number
135 ,p_grade_spine_id number
136 ,p_update varchar2
137 ) is
138
139 l_pay_scale varchar2(30);
140 l_grade_name per_grades.name%TYPE;
141 l_full_name varchar2(60);
142 l_sequence_number number;
143 l_parent_spine_id number;
144 l_special_ceiling_step_id number;
145 l_ceiling_step_id number;
146 l_step_id number;
147 l_lookup_code varchar2(10);
148 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
149 l_new_sequence_number number;
150 l_flag varchar2(2);
151 l_effective_end_date date;
152 l_grade_spine_id number;
153 l_payroll_id number;
154 l_org_name varchar2(60);
155
156 --
157 -- Cursor to determine if the record was flagged due to the next
158 -- record being on the same pay scale with a greater sequence number
159 -- than the one being inserted or if the step_id beeing inserted has
160 -- a greater sequence number than the future record ands so no update
161 -- done.
162 --
163 cursor csr_sequence_number is
164 select sps.sequence,spp1.parent_spine_id
165 from per_spinal_point_placements_f spp1,
166 per_spinal_point_placements_f spp2,
167 per_spinal_point_steps_f sps
168 where spp1.step_id = sps.step_id
169 and p_effective_date = spp1.effective_start_date
170 and spp2.effective_start_date = spp1.effective_end_date +1
171 and spp2.placement_id = spp1.placement_id
172 and spp1.placement_id = p_placement_id;
173
174 --
175 -- If csr_sequence_number not found then check for future record
176 -- with same parent spine
177 --
178 cursor csr_future_record is
179 select parent_spine_id
180 from per_spinal_point_placements_f
181 where placement_id = p_placement_id
182 and parent_spine_id = p_parent_spine_id
183 and p_effective_date between effective_start_date
184 and effective_end_date;
185
186 begin
187 hr_utility.set_location(p_placement_id,191);
188 hr_utility.set_location(p_effective_start_date,192);
189 hr_utility.set_location(p_effective_end_date,193);
190 hr_utility.set_location(p_assignment_id,194);
191 hr_utility.set_location(p_parent_spine_id,195);
192 hr_utility.set_location(p_increment_number,196);
193 hr_utility.set_location(p_original_increment_number,197);
194 hr_utility.set_location(p_sequence_number,198);
195 hr_utility.set_location(p_next_sequence_number,199);
196 hr_utility.set_location(p_spinal_point_id,200);
197 hr_utility.set_location(p_step_id,201);
198 hr_utility.set_location(p_new_step_id,202);
199 --
200 -- get the full name and assignment number of the employee
201 --
202 hr_utility.set_location('full name and assignment number',120);
203 select distinct substr(pap.full_name,1,60),paa.assignment_number,paa.payroll_id,
204 substr(org.name ,1,60)
205 into l_full_name,l_assignment_number,l_payroll_id,l_org_name
206 from per_all_people_f pap,
207 per_all_assignments_f paa,
208 hr_all_organization_units org
209 where pap.person_id = paa.person_id
210 and paa.organization_id = org.organization_id
211 and paa.assignment_id = p_assignment_id
212 and p_effective_date between paa.effective_start_date
213 and paa.effective_end_date
214 and p_effective_date between pap.effective_start_date -- 2276901
215 and pap.effective_end_date; -- 2276901
216
217
218 --
219 -- get the pay scale name
220 --
221 hr_utility.set_location(' Pay scale name',121);
222 hr_utility.set_location('parent_spine_id'||p_parent_spine_id,121);
223 select pps.name
224 into l_pay_scale
225 from per_parent_spines pps
226 where pps.parent_spine_id = p_parent_spine_id;
227
228 --
229 -- get the grade name
230 --
231 hr_utility.set_location('grade name',122);
232 select substr(pg.name,1,30),pgs.grade_spine_id
233 into l_grade_name,l_grade_spine_id
234 from per_grades_vl pg,
235 per_spinal_point_steps_f sps,
236 per_grade_spines_f pgs
237 where pg.grade_id = pgs.grade_id
238 and pgs.grade_spine_id = sps.grade_spine_id
239 and sps.step_id = p_step_id
240 and p_effective_date between sps.effective_start_date
241 and sps.effective_end_date
242 and p_effective_date between pgs.effective_start_date -- 2276901
243 and pgs.effective_end_date -- 2276901
244 and sps.spinal_point_id = p_spinal_point_id;
245
246 --
247 -- get the max step_id for the pay scale
248 --
249 hr_utility.set_location('max step_id for the pay scale',123);
250 select max(sps2.sequence)
251 into l_step_id
252 from per_spinal_point_steps_f sps2
253 where sps2.grade_spine_id = p_grade_spine_id
254 and p_effective_date between sps2.effective_start_date
255 and sps2.effective_end_date;
256
257 --
258 -- select the ceiling step id for the pay scale and the special
259 -- ceiling step id fro the assignment
260 --
261 hr_utility.set_location('ceiling step id for the assignment',124);
262 hr_utility.set_location('p_assignment_id:'||p_assignment_id,124);
263 hr_utility.set_location('p_new_step_id:'||p_new_step_id,124);
264 hr_utility.set_location('p_step_id:'||p_step_id,124);
265 hr_utility.set_location('p_effective_start_date:'||p_effective_start_date,124);
266 --
267 select distinct pgs.ceiling_step_id,
268 nvl(paa.special_ceiling_step_id,pgs.ceiling_step_id)
269 into l_ceiling_step_id, l_special_ceiling_step_id
270 from per_grade_spines_f pgs,
271 per_spinal_point_steps_f sps,
272 per_spinal_point_placements_f spp,
273 per_all_assignments_f paa
274 where pgs.grade_spine_id = sps.grade_spine_id
275 and paa.assignment_id = spp.assignment_id
276 and spp.assignment_id = p_assignment_id
277 and sps.step_id = spp.step_id
278 -- and spp.step_id = nvl(p_new_step_id,p_step_id)
279 and p_effective_start_date between paa.effective_start_date
280 and paa.effective_end_date
281 and p_effective_start_date between pgs.effective_start_date
282 and pgs.effective_end_date
283 and p_effective_start_date between sps.effective_start_date
284 and sps.effective_end_date
285 and p_effective_start_date between spp.effective_start_date
286 and spp.effective_end_date;
287 --
288 hr_utility.set_location('CEILINGS - Special: '||l_special_ceiling_step_id,125);
289 hr_utility.set_location('Pay Scale Ceiling : '||l_ceiling_step_id,125);
290 hr_utility.set_location('Entering csr_sequence_number',125);
291 hr_utility.set_location('Lookup Code : '||l_lookup_code,125);
292 --
293 -- Set the lookup code for the reason for employee being included in
294 -- the report
295 --
296
297 -- ------------------------------------------------------------------
298 -- Open cursor to discover if future record existed.
299 -- ------------------------------------------------------------------
300 open csr_sequence_number;
301 hr_utility.set_location('opening cursor',125);
302 fetch csr_sequence_number into l_sequence_number,l_parent_spine_id;
303 if csr_sequence_number%found then
304 hr_utility.set_location('cursor found',126);
305 hr_utility.set_location('l_sequence_number'||l_sequence_number,126);
306 hr_utility.set_location('p_sequence_number:'||p_sequence_number,126);
307 hr_utility.set_location('p_parent_spine_id:'||p_parent_spine_id,126);
308 hr_utility.set_location('l_parent_spine_id'||l_parent_spine_id,126);
309 hr_utility.set_location('p_original_increment_number:'||p_original_increment_number,126);
310 hr_utility.set_location('p_increment_number:'||p_increment_number,126);
311 -- ------------------------------------------------------------------
312 -- update done as sequence number <= next sequence number
313 -- l_sequence_number = sequence number of next record for placement
314 -- ------------------------------------------------------------------
315 if l_sequence_number >= p_sequence_number
316 and l_parent_spine_id = p_parent_spine_id
317 and l_grade_spine_id = p_grade_spine_id then
318 hr_utility.set_location('Insert done but flagged as future change',127);
319 l_lookup_code := 'EXC_INC_1';
320 -- ------------------------------------------------------------------
321 -- Check that ceiling step id hasn't been reached
322 -- ------------------------------------------------------------------
323 elsif (p_new_step_id = l_ceiling_step_id) then
324 hr_utility.set_location('Ceiling step id reached',130);
325 l_lookup_code := 'EXC_INC_2';
326 -- ------------------------------------------------------------------
327 -- Check that special ceiling step id hasn't been reached
328 -- ------------------------------------------------------------------
329 elsif (p_new_step_id = l_special_ceiling_step_id) then
330 hr_utility.set_location('Special ceiling step id reached',131);
331 l_lookup_code := 'EXC_INC_3';
332 -- ------------------------------------------------------------------
333 -- Check to see if max step id for pay scale reached
334 -- ------------------------------------------------------------------
335 elsif (l_step_id = p_new_step_id) then
336 hr_utility.set_location('Max step id for pay scale reached',132);
337 l_lookup_code := 'EXC_INC_4';
338 -- ------------------------------------------------------------------
339 -- Check increment number was greater than the number of steps
340 -- available for the pay scale
341 -- ------------------------------------------------------------------
342 elsif (p_original_increment_number <> p_increment_number)
343 and p_increment_number <> 0 then
344 hr_utility.set_location('Increment number changed',129);
345 l_lookup_code := 'EXC_INC_5';
346 -- ------------------------------------------------------------------
347 -- Future grade scale change
348 -- ------------------------------------------------------------------
349 elsif (p_grade_spine_id <> l_grade_spine_id) then
350 hr_utility.set_location('Future grade change',129);
351 l_lookup_code := 'EXC_INC_1';
352 end if;
353 --
354 elsif csr_sequence_number%notfound or
355 l_lookup_code is null then
356
357 open csr_future_record;
358 fetch csr_future_record into l_parent_spine_id;
359 if csr_future_record%found then
360 l_flag := 'Y';
361 else
362 l_flag := 'N';
363 end if;
364 close csr_future_record;
365 --
366 hr_utility.set_location('cursor not found',127);
367 hr_utility.set_location('l_sequence_number:'||l_sequence_number,127);
368 hr_utility.set_location('p_sequence_number:'||p_sequence_number,127);
369 hr_utility.set_location('l_parent_spine_id:'||l_parent_spine_id,127);
370 hr_utility.set_location('p_parent_spine_id:'||p_parent_spine_id,127);
371 hr_utility.set_location('p_original_increment_number:'||p_original_increment_number,127);
372 hr_utility.set_location('p_increment_number:'||p_increment_number,127);
373 hr_utility.set_location('***********************',127);
374 hr_utility.set_location('p_step_id:'||p_step_id,128);
375 hr_utility.set_location('l_ceiling_step_id:'||l_ceiling_step_id,128);
376 hr_utility.set_location('l_special_ceiling_step_id:'||l_special_ceiling_step_id,128);
377 hr_utility.set_location('l_step_id:'||l_step_id,128);
378 hr_utility.set_location('l_flag:'||l_flag,128);
379 --
380 -- ------------------------------------------------------------------
381 -- Check if record not inserted
382 -- ------------------------------------------------------------------
383 if p_original_increment_number <> p_increment_number
384 and l_flag = 'Y'
385 and p_new_step_id <> l_ceiling_step_id
386 and p_new_step_id <> l_special_ceiling_step_id
387 and p_new_step_id <> l_step_id then
388 hr_utility.set_location('No insert done as future change',128);
389 l_lookup_code := 'EXC_4';
390 -- ------------------------------------------------------------------
391 -- Check that ceiling step id hasn't been reached
395 l_lookup_code := 'EXC_INC_2';
392 -- ------------------------------------------------------------------
393 elsif (p_new_step_id = l_ceiling_step_id) then
394 if p_update = 'Y' then
396 else
397 hr_utility.set_location('Ceiling step id reached',130);
398 l_lookup_code := 'EXC_1';
399 end if;
400 -- ------------------------------------------------------------------
401 -- Check that special ceiling step id hasn't been reached
402 -- ------------------------------------------------------------------
403 elsif (p_new_step_id = l_special_ceiling_step_id) then
404 if p_update = 'Y' then
405 l_lookup_code := 'EXC_INC_3';
406 else
407 hr_utility.set_location('Special ceiling step id reached',131);
408 l_lookup_code := 'EXC_2';
409 end if;
410 -- ------------------------------------------------------------------
411 -- Check to see if max step id for pay scale reached
412 -- ------------------------------------------------------------------
413 elsif (l_step_id = p_new_step_id) then
414 if p_update = 'Y' then
415 l_lookup_code := 'EXC_INC_4';
416 else
417 hr_utility.set_location('Max step id for pay scale reached',132);
418 l_lookup_code := 'EXC_3';
419 end if;
420 end if;
421 end if;
422 close csr_sequence_number;
423
424 -- ----------------------------------------------------------------------
425 -- Check if the lookup code is null and if so the payroll_id
426 -- ----------------------------------------------------------------------
427
428 if l_lookup_code is null and l_payroll_id is null then
429
430 hr_utility.set_location('No payroll set when using business rule for next pay period.',133);
431 l_lookup_code := 'EXC_5';
432
433 end if;
434
435
436 -- ----------------------------------------------------------------------
437 -- Setup the reason code if spinal point
438 -- ----------------------------------------------------------------------
439 --
440 -- End of lookup code
441 --
442 SPPDATA(p_placement_id).placement_id_val := p_placement_id;
443 SPPDATA(p_placement_id).full_name_val := l_full_name;
444 SPPDATA(p_placement_id).pay_scale_val := l_pay_scale;
445 SPPDATA(p_placement_id).grade_name_val := l_grade_name;
446 SPPDATA(p_placement_id).start_date_val := p_effective_start_date;
447 SPPDATA(p_placement_id).end_date_val := p_effective_end_date;
448 SPPDATA(p_placement_id).assignment_number_val := l_assignment_number;
449 SPPDATA(p_placement_id).increment_number_val := p_increment_number;
450 SPPDATA(p_placement_id).original_inc_number_val := p_original_increment_number;
451 SPPDATA(p_placement_id).sequence_number_val := p_sequence_number;
452 SPPDATA(p_placement_id).next_sequence_number_val := p_next_sequence_number;
453 SPPDATA(p_placement_id).spinal_point_val := p_spinal_point_id;
454 SPPDATA(p_placement_id).reason_val := l_lookup_code;
455 SPPDATA(p_placement_id).org_name_val := l_org_name;
456
457 end populate_spp_table;
458
459 END HR_SPP_EXCEPTION_DATA;
[Home] [Help]