1 PACKAGE BODY hri_bpl_period_of_work AS
2 /* $Header: hriblow.pkb 120.3 2005/07/05 01:40:40 anmajumd noship $ */
3
4 TYPE g_num_array_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 g_service_months g_num_array_t; -- Holds sequence of band min months
6 g_service_days g_num_array_t; -- Holds sequence of band min days
7
8 g_service_band_count NUMBER := 0; -- Keeps a count of the number of bands
9 g_band_number NUMBER; -- Primary key for temporary band table
10 g_period_type VARCHAR2(30); -- Fast formula output - DAYS or MONTHS
11 g_service_ff_update DATE; -- Last update date of Fast Formula
12
13 g_low_person_id NUMBER := -1;
14 g_low_effective_date DATE;
15 g_low_months_service NUMBER;
16 g_low_latest_hire_date DATE;
17
18 --
19 -- For storing the row fetched from BIS_BUCKET that stores POW information
20 --
21 g_pow_bucket_emp BIS_BUCKET_CUSTOMIZATIONS%rowtype;
22 g_pow_bucket_cwk BIS_BUCKET_CUSTOMIZATIONS%rowtype;
23 --
24 -- Number table type with varchar2 indexing.
25 --
26 TYPE g_index_by_varchar2_num_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
27 g_pow_band_cache g_index_by_varchar2_num_tab;
28
29 /******************************************************************************/
30 /* PRIVATE Procedures and Functions */
31 /******************************************************************************/
32
33 /******************************************************************************/
34 /* PUBLIC Procedures and Functions */
35 /******************************************************************************/
36
37 /******************************************************************************/
38 /* Takes values in years, months, weeks and days, and the constant for how */
39 /* many days in a months, and returns the equivalent value in months */
40 /******************************************************************************/
41 FUNCTION normalize_band( p_band_years NUMBER,
42 p_band_months NUMBER,
43 p_band_weeks NUMBER,
44 p_band_days NUMBER,
45 p_days_to_month NUMBER)
46 RETURN NUMBER IS
47
48 l_return_value NUMBER; -- Holds value to return
49
50 BEGIN
51
52 l_return_value := (((p_band_weeks * 7) + p_band_days) / p_days_to_month)
53 + ((p_band_years * 12) + p_band_months);
54
55 RETURN l_return_value;
56
57 END normalize_band;
58
59
60 /******************************************************************************/
61 /* Retrieves constant converting days to months */
62 /******************************************************************************/
63 FUNCTION get_days_to_month RETURN NUMBER IS
64
65 l_days_to_month NUMBER; -- Holds constant
66
67 CURSOR ratio_cur IS
68 SELECT days_to_month
69 FROM hri_service_bands
70 WHERE days_to_month IS NOT NULL;
71
72 BEGIN
73
74 OPEN ratio_cur;
75 FETCH ratio_cur INTO l_days_to_month;
76 CLOSE ratio_cur;
77
78 RETURN l_days_to_month;
79
80 END get_days_to_month;
81
82 /******************************************************************************/
83 /* Retrieves constant converting days to months */
84 /******************************************************************************/
85 PROCEDURE set_days_to_months( p_days_to_month NUMBER)
86 IS
87
88 BEGIN
89
90 UPDATE hri_service_bands
91 SET days_to_month = p_days_to_month
92 WHERE days_to_month IS NOT NULL;
93
94 END set_days_to_months;
95
96 /******************************************************************************/
97 /* This procedure inserts a service band into the hri_service_bands table. */
98 /* The PK is the minimum year, month, week and day for the service band. */
99 /* There will always be a row with all of these zero since this cannot be */
100 /* removed by the remove_service_band API) and there will always be (possibly */
101 /* the same row) a row with null maximum year, month, week and day values */
102 /* since inserting a row always works by picking the band that the new */
103 /* service length falls into, and splitting it into two */
104 /* */
105 /* If a service length is given that already exists, nothing will happen. */
106 /* */
107 /* E.g. if the following bands exist (Years, Months, Weeks, Days): */
108 /* (0,0,0,0) - (0,3,0,0) */
109 /* (0,3,0,0) - (0,6,0,0) */
110 /* (0,6,0,0) - (0,9,0,0) */
111 /* (0,9,0,0) - (,,,) */
112 /* */
113 /* Then insert_service_band(1,0,0,0) would give the new set of bands as: */
114 /* (0,0,0,0) - (0,3,0,0) */
115 /* (0,3,0,0) - (0,6,0,0) */
116 /* (0,6,0,0) - (0,9,0,0) */
117 /* (0,9,0,0) - (1,0,0,0) */
118 /* (1,0,0,0) - (,,,) */
119 /******************************************************************************/
120 PROCEDURE insert_service_band( p_service_min_years NUMBER,
121 p_service_min_months NUMBER,
122 p_service_min_weeks NUMBER,
123 p_service_min_days NUMBER)
124 IS
125
126 l_total_min_service_months NUMBER; -- Holds the service length in months
127
128 l_band_to_split_min NUMBER; -- Service (in months) of band to split
129 l_band_to_split_max_years NUMBER; -- Max years of band to split
130 l_band_to_split_max_months NUMBER; -- Max months of band to split
131 l_band_to_split_max_weeks NUMBER; -- Max weeks of band to split
132 l_band_to_split_max_days NUMBER; -- Max days of band to split
133
134 l_days_to_month NUMBER; -- Constant converting days to months
135
136 /* Get details of band to split */
137 CURSOR split_cur
138 (v_total_min_service_months NUMBER,
139 v_days_to_month NUMBER) IS
140 SELECT normalize_band( band_min_total_years
141 , band_min_total_months
142 , band_min_total_weeks
143 , band_min_total_days
144 , v_days_to_month) band_months
145 , band_max_total_years
146 , band_max_total_months
147 , band_max_total_weeks
148 , band_max_total_days
149 FROM hri_service_bands
150 WHERE (normalize_band( band_max_total_years
151 , band_max_total_months
152 , band_max_total_weeks
153 , band_max_total_days
154 , v_days_to_month) > v_total_min_service_months
155 OR ( band_max_total_years IS NULL AND band_max_total_months IS NULL
156 AND band_max_total_weeks IS NULL AND band_max_total_days IS NULL))
157 AND normalize_band( band_min_total_years
158 , band_min_total_months
159 , band_min_total_weeks
160 , band_min_total_days
161 , v_days_to_month) < v_total_min_service_months;
162
163 BEGIN
164
165 /* Retrive constant */
166 l_days_to_month := get_days_to_month;
167
168 /* Convert input to months */
169 l_total_min_service_months := normalize_band( p_service_min_years
170 , p_service_min_months
171 , p_service_min_weeks
172 , p_service_min_days
173 , l_days_to_month );
174
175 /* Find which service band contains input service length */
176 OPEN split_cur(l_total_min_service_months, l_days_to_month);
177 FETCH split_cur INTO l_band_to_split_min,
178 l_band_to_split_max_years,
179 l_band_to_split_max_months,
180 l_band_to_split_max_weeks,
181 l_band_to_split_max_days;
182 IF (split_cur%NOTFOUND OR split_cur%NOTFOUND IS NULL) THEN
183
184 /* Service Band already exists */
185 CLOSE split_cur;
186 ELSE
187 /* Create new service band using maximum of band to split */
188 INSERT INTO hri_service_bands
189 (band_min_total_years,
190 band_min_total_months,
191 band_min_total_weeks,
192 band_min_total_days,
193 band_max_total_years,
194 band_max_total_months,
195 band_max_total_weeks,
196 band_max_total_days)
197 VALUES
198 ( p_service_min_years,
199 p_service_min_months,
200 p_service_min_weeks,
201 p_service_min_days,
202 l_band_to_split_max_years,
203 l_band_to_split_max_months,
204 l_band_to_split_max_weeks,
205 l_band_to_split_max_days );
206
207 /* Update the maximum of band to split with the input */
208 UPDATE hri_service_bands
209 SET band_max_total_years = p_service_min_years,
210 band_max_total_months = p_service_min_months,
211 band_max_total_weeks = p_service_min_weeks,
212 band_max_total_days = p_service_min_days
213 WHERE normalize_band( band_min_total_years,
214 band_min_total_months,
215 band_min_total_weeks,
216 band_min_total_days,
217 l_days_to_month ) = l_band_to_split_min;
218 END IF;
219
220 END insert_service_band;
221
222
223 /******************************************************************************/
224 /* Removes a service band, if it exists. The maximum of the band preceding */
225 /* the removed band is updated with the maximim of the removed band. */
226 /******************************************************************************/
227 PROCEDURE remove_service_band( p_service_min_years NUMBER,
228 p_service_min_months NUMBER,
229 p_service_min_weeks NUMBER,
230 p_service_min_days NUMBER)
231 IS
232
233 l_total_min_service_months NUMBER; -- Service length of band to remove
234
235 l_band_to_grow_max_years NUMBER; -- Max years of band to remove
236 l_band_to_grow_max_months NUMBER; -- Max months of band to remove
237 l_band_to_grow_max_weeks NUMBER; -- Max weeks of band to remove
238 l_band_to_grow_max_days NUMBER; -- Max days of band to remove
239
240 /* Get maximum service length of band to remove */
241 CURSOR grow_cur IS
242 SELECT
243 band_max_total_years
244 ,band_max_total_months
245 ,band_max_total_weeks
246 ,band_max_total_days
247 FROM hri_service_bands
248 WHERE band_min_total_years = p_service_min_years
249 AND band_min_total_months = p_service_min_months
250 AND band_min_total_weeks = p_service_min_weeks
251 AND band_min_total_days = p_service_min_days;
252
253 BEGIN
254
255 /* Populate variables with maximum service length of band to remove */
256 OPEN grow_cur;
257 FETCH grow_cur INTO l_band_to_grow_max_years,
258 l_band_to_grow_max_months,
259 l_band_to_grow_max_weeks,
260 l_band_to_grow_max_days;
261 IF (grow_cur%NOTFOUND OR grow_cur%NOTFOUND IS NULL) THEN
262 /* Age Band doesn't exist */
263 CLOSE grow_cur;
264 ELSE
265 /* Remove the band */
266 DELETE FROM hri_service_bands
267 WHERE band_min_total_years = p_service_min_years
268 AND band_min_total_months = p_service_min_months
269 AND band_min_total_weeks = p_service_min_weeks
270 AND band_min_total_days = p_service_min_days;
271
272 /* Update the previous band, which can be identified by its maximum */
273 /* being the minimum of the band removed */
274 UPDATE hri_service_bands
275 SET band_max_total_years = l_band_to_grow_max_years,
276 band_max_total_months = l_band_to_grow_max_months,
277 band_max_total_weeks = l_band_to_grow_max_weeks,
278 band_max_total_days = l_band_to_grow_max_days
279 WHERE band_max_total_years = p_service_min_years
280 AND band_max_total_months = p_service_min_months
281 AND band_max_total_weeks = p_service_min_weeks
282 AND band_max_total_days = p_service_min_days;
283 END IF;
284
285 END remove_service_band;
286
287 /******************************************************************************/
288 /* Inserts row into table, or updates it if the row already exists. Called */
289 /* from the UPLOAD section of FNDLOAD. */
290 /******************************************************************************/
291 PROCEDURE load_row( p_band_min_yrs IN NUMBER,
292 p_band_min_mths IN NUMBER,
293 p_band_min_wks IN NUMBER,
294 p_band_min_days IN NUMBER,
295 p_band_max_yrs IN NUMBER,
296 p_band_max_mths IN NUMBER,
297 p_band_max_wks IN NUMBER,
298 p_band_max_days IN NUMBER,
299 p_days_to_month IN NUMBER,
300 p_owner IN VARCHAR2 )
301 IS
302
303 l_row_exists NUMBER;
304
305 /* Standard WHO columns */
306 l_last_update_date DATE;
307 l_last_updated_by NUMBER;
308 l_last_update_login NUMBER;
309 l_created_by NUMBER;
310 l_creation_date DATE;
311
312 CURSOR row_exists_cur IS
313 SELECT 1
314 FROM hri_service_bands
315 WHERE (band_min_total_years = p_band_min_yrs
316 AND band_min_total_months = p_band_min_mths
317 AND band_min_total_weeks = p_band_min_wks
318 AND band_min_total_days = p_band_min_days)
319 OR (band_min_total_years IS NULL AND p_band_min_yrs IS NULL
320 AND band_min_total_months IS NULL AND p_band_min_mths IS NULL
321 AND band_min_total_weeks IS NULL AND p_band_min_wks IS NULL
322 AND band_min_total_days IS NULL AND p_band_min_days IS NULL);
323
324 BEGIN
325 --
326 l_last_update_date := SYSDATE;
327 l_last_updated_by := 0;
328 l_last_update_login := 0;
329 l_created_by := 0;
330 l_creation_date := SYSDATE;
331 --
332 OPEN row_exists_cur;
333 FETCH row_exists_cur INTO l_row_exists;
334 IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
335 CLOSE row_exists_cur;
336 INSERT INTO hri_service_bands
337 ( band_min_total_years
338 , band_min_total_months
339 , band_min_total_weeks
340 , band_min_total_days
341 , band_max_total_years
342 , band_max_total_months
343 , band_max_total_weeks
344 , band_max_total_days
345 , days_to_month
346 , last_update_date
347 , last_update_login
348 , last_updated_by
349 , created_by
350 , creation_date )
351 VALUES
352 ( p_band_min_yrs
353 , p_band_min_mths
354 , p_band_min_wks
355 , p_band_min_days
356 , p_band_max_yrs
357 , p_band_max_mths
358 , p_band_max_wks
359 , p_band_max_days
360 , p_days_to_month
361 , l_last_update_date
362 , l_last_update_login
363 , l_last_updated_by
364 , l_created_by
365 , l_creation_date );
366 ELSE
367 CLOSE row_exists_cur;
368 UPDATE hri_service_bands
369 SET
370 band_max_total_years = p_band_max_yrs
371 ,band_max_total_months = p_band_max_mths
372 ,band_max_total_weeks = p_band_max_wks
373 ,band_max_total_days = p_band_max_days
374 ,days_to_month = p_days_to_month
375 ,last_update_date = l_last_update_date
376 ,last_update_login = l_last_update_login
380 AND band_min_total_weeks = p_band_min_wks
377 ,last_updated_by = l_last_updated_by
378 WHERE (band_min_total_years = p_band_min_yrs
379 AND band_min_total_months = p_band_min_mths
381 AND band_min_total_days = p_band_min_days)
382 OR (band_min_total_years IS NULL AND p_band_min_yrs IS NULL
383 AND band_min_total_months IS NULL AND p_band_min_mths IS NULL
384 AND band_min_total_weeks IS NULL AND p_band_min_wks IS NULL
385 AND band_min_total_days IS NULL AND p_band_min_days IS NULL);
386 END IF;
387
388 END load_row;
389
390 PROCEDURE cache_period_of_work(p_person_id IN NUMBER,
391 p_effective_date IN DATE,
392 p_assignment_type IN VARCHAR2) IS
393
394 CURSOR get_emp_pow_csr IS
395 SELECT
396 SUM(months_between(least(nvl(actual_termination_date + 1,
397 p_effective_date + 1),
398 p_effective_date + 1),
399 date_start)) total_months
400 ,MAX(date_start) latest_hire_date
401 FROM per_periods_of_service
402 WHERE person_id = p_person_id
403 AND date_start <= p_effective_date;
404
405 BEGIN
406
407 IF (p_person_id <> g_low_person_id OR
408 p_effective_date <> g_low_effective_date) THEN
409
410 IF (p_assignment_type = 'E') THEN
411
412 OPEN get_emp_pow_csr;
413 FETCH get_emp_pow_csr INTO g_low_months_service, g_low_latest_hire_date;
414 CLOSE get_emp_pow_csr;
415
416 g_low_person_id := p_person_id;
417 g_low_effective_date := p_effective_date;
418
419 END IF;
420
421 END IF;
422
423 END cache_period_of_work;
424
425 -- returns period of time, in years, of the persons period of service
426 -- taking into account breaks in service for rehires
427 FUNCTION get_period_of_work_years(p_person_id IN NUMBER,
428 p_effective_date IN DATE,
429 p_assignment_type IN VARCHAR2)
430 RETURN NUMBER IS
431
432 BEGIN
433
434 cache_period_of_work(p_person_id => p_person_id,
435 p_effective_date => p_effective_date,
436 p_assignment_type => p_assignment_type);
437
438 RETURN g_low_months_service/12;
439
440 END get_period_of_work_years;
441
442 -- returns period of time, in months, of the persons period of service
443 -- taking into account breaks in service for rehires
444 FUNCTION get_period_of_work_months(p_person_id IN NUMBER,
445 p_effective_date IN DATE,
446 p_assignment_type IN VARCHAR2)
447 RETURN NUMBER IS
448
449 BEGIN
450
451 cache_period_of_work(p_person_id => p_person_id,
452 p_effective_date => p_effective_date,
453 p_assignment_type => p_assignment_type);
454
455 RETURN g_low_months_service;
456
457 END get_period_of_work_months;
458
459 -- returns latest hire date
460 FUNCTION get_latest_hire_date(p_person_id IN NUMBER,
461 p_effective_date IN DATE,
462 p_assignment_type IN VARCHAR2)
463 RETURN DATE IS
464
465 BEGIN
466
467 cache_period_of_work(p_person_id => p_person_id,
468 p_effective_date => p_effective_date,
469 p_assignment_type => p_assignment_type);
470
471 RETURN g_low_latest_hire_date;
472
473 END get_latest_hire_date;
474 --
475 -- -----------------------------------------------------------------------------
476 -- GET_POW_BAND_EMP
477 -- This function is invoked by the get_pow_band_high_val
478 -- It reads the definition of the employee period of work bucket returns the high
479 -- value for the bucket
480 -- -----------------------------------------------------------------------------
481 --
482 FUNCTION get_pow_band_emp(p_band_number NUMBER)
483 RETURN NUMBER
484 IS
485 --
486 l_band NUMBER;
487 --
488 -- Cursor to fetch the period of work band range information
489 -- 4293064 Bucket definition should be picked from the bucket customization table
490 --
491 CURSOR c_bucket (c_bucket VARCHAR2) IS
492 SELECT bb.*
493 FROM bis_bucket_customizations bb,
494 bis_bucket b
495 WHERE b.short_name = c_bucket
496 AND b.bucket_id = bb.bucket_id;
497 --
498 BEGIN
499 --
500 -- Open the cursor only if the global cache record is not populated
501 --
502 IF g_pow_bucket_emp.bucket_id is null THEN
503 --
504 OPEN c_bucket('HRI_DBI_LOW_BAND_CURRENT');
505 FETCH c_bucket INTO g_pow_bucket_emp;
506 CLOSE c_bucket;
507 --
508 END IF;
509 --
510 -- Identify the band within which the normalized rating falls and return the value
511 --
512 IF p_band_number = 1 THEN
513 --
514 l_band := g_pow_bucket_emp.range1_high;
515 --
516 ELSIF p_band_number = 2 THEN
517 --
518 l_band := g_pow_bucket_emp.range2_high;
519 --
520 ELSIF p_band_number = 3 THEN
521 --
522 l_band := g_pow_bucket_emp.range3_high;
523 --
524 ELSIF p_band_number = 4 THEN
525 --
526 l_band := g_pow_bucket_emp.range4_high;
527 --
528 ELSIF p_band_number = 5 THEN
529 --
530 -- Since this is the highest band, the high value for this band should always be null
531 --
532 l_band := null;
533 --
537 --
534 ELSE
535 --
536 l_band := null;
538 END IF;
539 --
540 RETURN l_band;
541 --
542 EXCEPTION
543 WHEN OTHERS THEN
544 --
545 IF c_bucket%ISOPEN THEN
546 --
547 CLOSE c_bucket;
548 --
549 END IF;
550 --
551 RAISE;
552 --
553 END get_pow_band_emp;
554 --
555 -- -----------------------------------------------------------------------------
556 -- GET_POW_BAND_EMP
557 -- This function is invoked by the get_pow_band_high_val
558 -- It reads the definition of the employee period of work bucket returns the high
559 -- value for the bucket
560 -- -----------------------------------------------------------------------------
561 --
562 FUNCTION get_pow_band_cwk(p_band_number NUMBER)
563 RETURN NUMBER
564 IS
565 --
566 l_band NUMBER;
567 --
568 CURSOR c_bucket (c_bucket VARCHAR2) IS
569 SELECT bb.*
570 FROM bis_bucket_customizations bb,
571 bis_bucket b
572 WHERE b.short_name = c_bucket
573 AND b.bucket_id = bb.bucket_id;
574 --
575 BEGIN
576 --
577 -- Open the cursor only if the global cache record is not populated
578 --
579 IF g_pow_bucket_cwk.bucket_id is null THEN
580 --
581 OPEN c_bucket('HRI_DBI_POW_PLCMNT_BAND');
582 FETCH c_bucket INTO g_pow_bucket_cwk;
583 CLOSE c_bucket;
584 --
585 END IF;
586 --
587 -- Identify the band within which the normalized rating falls and return the value
588 --
589 IF p_band_number = 1 THEN
590 --
591 l_band := g_pow_bucket_cwk.range1_high;
592 --
593 ELSIF p_band_number = 2 THEN
594 --
595 l_band := g_pow_bucket_cwk.range2_high;
596 --
597 ELSIF p_band_number = 3 THEN
598 --
599 l_band := g_pow_bucket_cwk.range3_high;
600 --
601 ELSIF p_band_number = 4 THEN
602 --
603 l_band := g_pow_bucket_cwk.range4_high;
604 --
605 ELSIF p_band_number = 5 THEN
606 --
607 -- Since this is the highest band, the high value for this band should always be null
608 --
609 l_band := null;
610 --
611 ELSE
612 --
613 l_band := null;
614 --
615 END IF;
616 --
617 RETURN l_band;
618 --
619 EXCEPTION
620 WHEN OTHERS THEN
621 --
622 IF c_bucket%ISOPEN THEN
623 --
624 CLOSE c_bucket;
625 --
626 END IF;
627 --
628 RAISE;
629 --
630 END get_pow_band_cwk;
631 --
632 -- -----------------------------------------------------------------------------
633 -- GET_POW_BAND_HIGH_VAL
634 -- This procedure is invoked by the asg events fact collection program
635 -- Based on the assignment type it finds out the high value for the corresponding
636 -- bucket
637 -- -----------------------------------------------------------------------------
638 --
639 FUNCTION get_pow_band_high_val(p_band_number NUMBER,
640 p_assignment_type VARCHAR2)
641 RETURN NUMBER
642 IS
643 --
644 l_band_high_val NUMBER;
645 --
646 BEGIN
647 --
648 IF p_assignment_type = 'E' THEN
649 --
650 l_band_high_val := get_pow_band_emp(p_band_number);
651 --
652 ELSE
653 --
654 l_band_high_val := get_pow_band_cwk(p_band_number);
655 --
656 END IF;
657 --
658 RETURN l_band_high_val;
659 --
660 END get_pow_band_high_val;
661 --
662 -- -----------------------------------------------------------------------------
663 -- GET_POW_BAND_SK_FK
664 -- The function returns the pow band surrogate key for use in assignmene events
665 -- The profram first searched for the details in the cache and when it does not
666 -- find it queries the table to determine the value.
667 -- -----------------------------------------------------------------------------
668 --
669 FUNCTION get_pow_band_sk_fk(p_band_number NUMBER,
670 p_assignment_type VARCHAR2)
671 RETURN NUMBER IS
672 --
673 cursor c_pow_band IS
674 SELECT pow_band_sk_pk
675 FROM hri_cs_pow_band_ct powb
676 WHERE powb.wkth_wktyp_sk_fk = decode(p_assignment_type,'E','EMP','CWK')
677 AND powb.band_sequence = p_band_number;
678 --
679 l_pow_band_sk_fk NUMBER;
680 --
681 BEGIN
682 --
683 l_pow_band_sk_fk := g_pow_band_cache(p_assignment_type||'###'||p_band_number);
684 --
685 RETURN l_pow_band_sk_fk;
686 --
687 EXCEPTION
688 WHEN OTHERS THEN
689 --
690 OPEN c_pow_band;
691 FETCH c_pow_band into l_pow_band_sk_fk;
692 CLOSE c_pow_band;
693 --
694 g_pow_band_cache(p_assignment_type||'###'||p_band_number) := nvl(l_pow_band_sk_fk,-5);
695 --
696 RETURN l_pow_band_sk_fk;
697 --
698 END get_pow_band_sk_fk;
699 --
700 BEGIN
701 --
702 g_low_effective_date := hr_general.start_of_time;
703 --
704 END hri_bpl_period_of_work;
[Home] [Help]