1 PACKAGE BODY HR_SPP_EXCEPTION_DATA AS
2 /* $Header: pesppexc.pkb 120.0.12020000.2 2013/02/14 09:14:40 lbodired ship $ */
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); Fix for bug 16090637
142 l_full_name varchar2(240);
143 l_sequence_number number;
144 l_parent_spine_id number;
145 l_special_ceiling_step_id number;
146 l_ceiling_step_id number;
147 l_step_id number;
148 l_lookup_code varchar2(10);
149 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
150 l_new_sequence_number number;
151 l_flag varchar2(2);
152 l_effective_end_date date;
153 l_grade_spine_id number;
154 l_payroll_id number;
155 -- l_org_name varchar2(60); Fix for bug 16090637
156 l_org_name varchar2(240);
157
158 --
159 -- Cursor to determine if the record was flagged due to the next
160 -- record being on the same pay scale with a greater sequence number
161 -- than the one being inserted or if the step_id beeing inserted has
162 -- a greater sequence number than the future record ands so no update
163 -- done.
164 --
165 cursor csr_sequence_number is
166 select sps.sequence,spp1.parent_spine_id
167 from per_spinal_point_placements_f spp1,
168 per_spinal_point_placements_f spp2,
169 per_spinal_point_steps_f sps
170 where spp1.step_id = sps.step_id
171 and p_effective_date = spp1.effective_start_date
172 and spp2.effective_start_date = spp1.effective_end_date +1
173 and spp2.placement_id = spp1.placement_id
174 and spp1.placement_id = p_placement_id;
175
176 --
177 -- If csr_sequence_number not found then check for future record
178 -- with same parent spine
179 --
180 cursor csr_future_record is
181 select parent_spine_id
182 from per_spinal_point_placements_f
183 where placement_id = p_placement_id
184 and parent_spine_id = p_parent_spine_id
185 and p_effective_date between effective_start_date
186 and effective_end_date;
187
188 begin
189 hr_utility.set_location(p_placement_id,191);
190 hr_utility.set_location(p_effective_start_date,192);
191 hr_utility.set_location(p_effective_end_date,193);
192 hr_utility.set_location(p_assignment_id,194);
193 hr_utility.set_location(p_parent_spine_id,195);
194 hr_utility.set_location(p_increment_number,196);
195 hr_utility.set_location(p_original_increment_number,197);
196 hr_utility.set_location(p_sequence_number,198);
197 hr_utility.set_location(p_next_sequence_number,199);
198 hr_utility.set_location(p_spinal_point_id,200);
199 hr_utility.set_location(p_step_id,201);
200 hr_utility.set_location(p_new_step_id,202);
201 --
202 -- get the full name and assignment number of the employee
203 --
204 hr_utility.set_location('full name and assignment number',120);
205 select distinct substr(pap.full_name,1,60),paa.assignment_number,paa.payroll_id,
206 substr(org.name ,1,60)
207 into l_full_name,l_assignment_number,l_payroll_id,l_org_name
208 from per_all_people_f pap,
209 per_all_assignments_f paa,
210 hr_all_organization_units org
211 where pap.person_id = paa.person_id
212 and paa.organization_id = org.organization_id
213 and paa.assignment_id = p_assignment_id
214 and p_effective_date between paa.effective_start_date
215 and paa.effective_end_date
216 and p_effective_date between pap.effective_start_date -- 2276901
217 and pap.effective_end_date; -- 2276901
218
219
220 --
221 -- get the pay scale name
222 --
223 hr_utility.set_location(' Pay scale name',121);
224 hr_utility.set_location('parent_spine_id'||p_parent_spine_id,121);
225 select pps.name
226 into l_pay_scale
227 from per_parent_spines pps
228 where pps.parent_spine_id = p_parent_spine_id;
229
230 --
231 -- get the grade name
232 --
233 hr_utility.set_location('grade name',122);
234 select substr(pg.name,1,30),pgs.grade_spine_id
235 into l_grade_name,l_grade_spine_id
236 from per_grades_vl pg,
237 per_spinal_point_steps_f sps,
238 per_grade_spines_f pgs
239 where pg.grade_id = pgs.grade_id
240 and pgs.grade_spine_id = sps.grade_spine_id
241 and sps.step_id = p_step_id
242 and p_effective_date between sps.effective_start_date
243 and sps.effective_end_date
244 and p_effective_date between pgs.effective_start_date -- 2276901
245 and pgs.effective_end_date -- 2276901
246 and sps.spinal_point_id = p_spinal_point_id;
247
248 --
249 -- get the max step_id for the pay scale
250 --
251 hr_utility.set_location('max step_id for the pay scale',123);
252 select max(sps2.sequence)
253 into l_step_id
254 from per_spinal_point_steps_f sps2
255 where sps2.grade_spine_id = p_grade_spine_id
256 and p_effective_date between sps2.effective_start_date
257 and sps2.effective_end_date;
258
259 --
260 -- select the ceiling step id for the pay scale and the special
261 -- ceiling step id fro the assignment
262 --
263 hr_utility.set_location('ceiling step id for the assignment',124);
264 hr_utility.set_location('p_assignment_id:'||p_assignment_id,124);
265 hr_utility.set_location('p_new_step_id:'||p_new_step_id,124);
266 hr_utility.set_location('p_step_id:'||p_step_id,124);
267 hr_utility.set_location('p_effective_start_date:'||p_effective_start_date,124);
268 --
269 select distinct pgs.ceiling_step_id,
270 nvl(paa.special_ceiling_step_id,pgs.ceiling_step_id)
271 into l_ceiling_step_id, l_special_ceiling_step_id
272 from per_grade_spines_f pgs,
273 per_spinal_point_steps_f sps,
274 per_spinal_point_placements_f spp,
275 per_all_assignments_f paa
276 where pgs.grade_spine_id = sps.grade_spine_id
277 and paa.assignment_id = spp.assignment_id
278 and spp.assignment_id = p_assignment_id
279 and sps.step_id = spp.step_id
280 -- and spp.step_id = nvl(p_new_step_id,p_step_id)
281 and p_effective_start_date between paa.effective_start_date
282 and paa.effective_end_date
283 and p_effective_start_date between pgs.effective_start_date
284 and pgs.effective_end_date
285 and p_effective_start_date between sps.effective_start_date
286 and sps.effective_end_date
287 and p_effective_start_date between spp.effective_start_date
288 and spp.effective_end_date;
289 --
290 hr_utility.set_location('CEILINGS - Special: '||l_special_ceiling_step_id,125);
291 hr_utility.set_location('Pay Scale Ceiling : '||l_ceiling_step_id,125);
292 hr_utility.set_location('Entering csr_sequence_number',125);
293 hr_utility.set_location('Lookup Code : '||l_lookup_code,125);
294 --
295 -- Set the lookup code for the reason for employee being included in
296 -- the report
297 --
298
299 -- ------------------------------------------------------------------
300 -- Open cursor to discover if future record existed.
301 -- ------------------------------------------------------------------
302 open csr_sequence_number;
303 hr_utility.set_location('opening cursor',125);
304 fetch csr_sequence_number into l_sequence_number,l_parent_spine_id;
305 if csr_sequence_number%found then
306 hr_utility.set_location('cursor found',126);
307 hr_utility.set_location('l_sequence_number'||l_sequence_number,126);
308 hr_utility.set_location('p_sequence_number:'||p_sequence_number,126);
309 hr_utility.set_location('p_parent_spine_id:'||p_parent_spine_id,126);
310 hr_utility.set_location('l_parent_spine_id'||l_parent_spine_id,126);
311 hr_utility.set_location('p_original_increment_number:'||p_original_increment_number,126);
312 hr_utility.set_location('p_increment_number:'||p_increment_number,126);
313 -- ------------------------------------------------------------------
314 -- update done as sequence number <= next sequence number
315 -- l_sequence_number = sequence number of next record for placement
316 -- ------------------------------------------------------------------
317 if l_sequence_number >= p_sequence_number
318 and l_parent_spine_id = p_parent_spine_id
319 and l_grade_spine_id = p_grade_spine_id then
320 hr_utility.set_location('Insert done but flagged as future change',127);
321 l_lookup_code := 'EXC_INC_1';
322 -- ------------------------------------------------------------------
323 -- Check that ceiling step id hasn't been reached
324 -- ------------------------------------------------------------------
325 elsif (p_new_step_id = l_ceiling_step_id) then
326 hr_utility.set_location('Ceiling step id reached',130);
327 l_lookup_code := 'EXC_INC_2';
328 -- ------------------------------------------------------------------
329 -- Check that special ceiling step id hasn't been reached
330 -- ------------------------------------------------------------------
331 elsif (p_new_step_id = l_special_ceiling_step_id) then
332 hr_utility.set_location('Special ceiling step id reached',131);
333 l_lookup_code := 'EXC_INC_3';
334 -- ------------------------------------------------------------------
335 -- Check to see if max step id for pay scale reached
336 -- ------------------------------------------------------------------
337 elsif (l_step_id = p_new_step_id) then
338 hr_utility.set_location('Max step id for pay scale reached',132);
339 l_lookup_code := 'EXC_INC_4';
340 -- ------------------------------------------------------------------
341 -- Check increment number was greater than the number of steps
342 -- available for the pay scale
343 -- ------------------------------------------------------------------
344 elsif (p_original_increment_number <> p_increment_number)
345 and p_increment_number <> 0 then
346 hr_utility.set_location('Increment number changed',129);
347 l_lookup_code := 'EXC_INC_5';
348 -- ------------------------------------------------------------------
349 -- Future grade scale change
350 -- ------------------------------------------------------------------
351 elsif (p_grade_spine_id <> l_grade_spine_id) then
352 hr_utility.set_location('Future grade change',129);
353 l_lookup_code := 'EXC_INC_1';
354 end if;
355 --
356 elsif csr_sequence_number%notfound or
357 l_lookup_code is null then
358
359 open csr_future_record;
360 fetch csr_future_record into l_parent_spine_id;
361 if csr_future_record%found then
362 l_flag := 'Y';
363 else
364 l_flag := 'N';
365 end if;
366 close csr_future_record;
367 --
368 hr_utility.set_location('cursor not found',127);
369 hr_utility.set_location('l_sequence_number:'||l_sequence_number,127);
370 hr_utility.set_location('p_sequence_number:'||p_sequence_number,127);
371 hr_utility.set_location('l_parent_spine_id:'||l_parent_spine_id,127);
372 hr_utility.set_location('p_parent_spine_id:'||p_parent_spine_id,127);
373 hr_utility.set_location('p_original_increment_number:'||p_original_increment_number,127);
374 hr_utility.set_location('p_increment_number:'||p_increment_number,127);
375 hr_utility.set_location('***********************',127);
376 hr_utility.set_location('p_step_id:'||p_step_id,128);
377 hr_utility.set_location('l_ceiling_step_id:'||l_ceiling_step_id,128);
378 hr_utility.set_location('l_special_ceiling_step_id:'||l_special_ceiling_step_id,128);
379 hr_utility.set_location('l_step_id:'||l_step_id,128);
380 hr_utility.set_location('l_flag:'||l_flag,128);
381 --
382 -- ------------------------------------------------------------------
383 -- Check if record not inserted
384 -- ------------------------------------------------------------------
385 if p_original_increment_number <> p_increment_number
386 and l_flag = 'Y'
387 and p_new_step_id <> l_ceiling_step_id
388 and p_new_step_id <> l_special_ceiling_step_id
389 and p_new_step_id <> l_step_id then
390 hr_utility.set_location('No insert done as future change',128);
391 l_lookup_code := 'EXC_4';
392 -- ------------------------------------------------------------------
393 -- Check that ceiling step id hasn't been reached
394 -- ------------------------------------------------------------------
395 elsif (p_new_step_id = l_ceiling_step_id) then
396 if p_update = 'Y' then
397 l_lookup_code := 'EXC_INC_2';
398 else
399 hr_utility.set_location('Ceiling step id reached',130);
400 l_lookup_code := 'EXC_1';
401 end if;
402 -- ------------------------------------------------------------------
403 -- Check that special ceiling step id hasn't been reached
404 -- ------------------------------------------------------------------
405 elsif (p_new_step_id = l_special_ceiling_step_id) then
406 if p_update = 'Y' then
407 l_lookup_code := 'EXC_INC_3';
408 else
409 hr_utility.set_location('Special ceiling step id reached',131);
410 l_lookup_code := 'EXC_2';
411 end if;
412 -- ------------------------------------------------------------------
413 -- Check to see if max step id for pay scale reached
414 -- ------------------------------------------------------------------
415 elsif (l_step_id = p_new_step_id) then
416 if p_update = 'Y' then
417 l_lookup_code := 'EXC_INC_4';
418 else
419 hr_utility.set_location('Max step id for pay scale reached',132);
420 l_lookup_code := 'EXC_3';
421 end if;
422 end if;
423 end if;
424 close csr_sequence_number;
425
426 -- ----------------------------------------------------------------------
427 -- Check if the lookup code is null and if so the payroll_id
428 -- ----------------------------------------------------------------------
429
430 if l_lookup_code is null and l_payroll_id is null then
431
432 hr_utility.set_location('No payroll set when using business rule for next pay period.',133);
433 l_lookup_code := 'EXC_5';
434
435 end if;
436
437
438 -- ----------------------------------------------------------------------
439 -- Setup the reason code if spinal point
440 -- ----------------------------------------------------------------------
441 --
442 -- End of lookup code
443 --
444 SPPDATA(p_placement_id).placement_id_val := p_placement_id;
445 SPPDATA(p_placement_id).full_name_val := substr(l_full_name,30); -- l_full_name;
446 SPPDATA(p_placement_id).pay_scale_val := l_pay_scale;
447 SPPDATA(p_placement_id).grade_name_val := l_grade_name;
448 SPPDATA(p_placement_id).start_date_val := p_effective_start_date;
449 SPPDATA(p_placement_id).end_date_val := p_effective_end_date;
450 SPPDATA(p_placement_id).assignment_number_val := l_assignment_number;
451 SPPDATA(p_placement_id).increment_number_val := p_increment_number;
452 SPPDATA(p_placement_id).original_inc_number_val := p_original_increment_number;
453 SPPDATA(p_placement_id).sequence_number_val := p_sequence_number;
454 SPPDATA(p_placement_id).next_sequence_number_val := p_next_sequence_number;
455 SPPDATA(p_placement_id).spinal_point_val := p_spinal_point_id;
456 SPPDATA(p_placement_id).reason_val := l_lookup_code;
457 SPPDATA(p_placement_id).org_name_val := substr(l_org_name,30); -- l_org_name;
458
459 end populate_spp_table;
460
461 END HR_SPP_EXCEPTION_DATA;
[Home] [Help]