1 PACKAGE BODY edw_hr_service_pkg AS
2 /* $Header: hrieklwb.pkb 120.0 2005/05/29 07:11:31 appldev noship $ */
3
4 FUNCTION service_band_fk( p_service_days IN NUMBER)
5 RETURN VARCHAR2 IS
6
7 l_days_to_month NUMBER;
8 l_service_band_pk VARCHAR2(400);
9
10 CURSOR ratio_cur IS
11 SELECT days_to_month
12 FROM hri_service_bands
13 WHERE days_to_month IS NOT NULL;
14
15 CURSOR service_band_cur
16 (v_days_to_month NUMBER) IS
17 SELECT service_band_pk
18 FROM edw_hr_service_fkv
19 WHERE (((service_length_min_year * 12) + service_length_min_month) * v_days_to_month)
20 + ((service_length_min_week * 7) + service_length_min_day)
21 <= p_service_days
22 AND ((service_length_max_year IS NULL AND
23 service_length_max_month IS NULL AND
24 service_length_max_week IS NULL AND
25 service_length_max_day IS NULL)
26 OR ((((service_length_max_year * 12) + service_length_max_month) * v_days_to_month)
27 + ((service_length_max_week * 7) + service_length_max_day)
28 > p_service_days));
29
30 BEGIN
31
32 OPEN ratio_cur;
33 FETCH ratio_cur INTO l_days_to_month;
34 CLOSE ratio_cur;
35
36 OPEN service_band_cur(l_days_to_month);
37 FETCH service_band_cur INTO l_service_band_pk;
38 CLOSE service_band_cur;
39
40 RETURN NVL(l_service_band_pk, 'NA_EDW');
41
42 EXCEPTION when others then
43
44 if service_band_cur%ISOPEN then
45 CLOSE service_band_cur;
46 end if;
47
48 RETURN NVL(l_service_band_pk, 'NA_EDW');
49
50 END service_band_fk;
51
52 END edw_hr_service_pkg;
[Home] [Help]