1 PACKAGE BODY hri_bpl_termination AS
2 /* $Header: hribterm.pkb 120.1 2005/07/29 02:54:32 jtitmas noship $ */
3 --
4 -- Varchar2 table type with varchar2 indexing.
5 --
6 TYPE g_index_by_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
7 --
8 -- Global Varchar2 table type with varchar2 indexing to store employee
9 -- separation reason. It gets assigned in the function is_vol_sep.
10 --
11 g_cache_emp_sep_resns_tab g_index_by_varchar2_tab_type;
12 --
13 -- ----------------------------------------------------------------------------
14 -- Returns separation category classification of leaving reason
15 -- ----------------------------------------------------------------------------
16 --
17 FUNCTION get_separation_category(p_leaving_reason IN VARCHAR2)
18 RETURN VARCHAR2 IS
19 --
20 -- Local variable to hold the termination type to return
21 --
22 l_separation_category VARCHAR2(30);
23 --
24 -- Cursor to find out the termination type for a particular leaving reason
25 --
26 CURSOR sep_cat_csr IS
27 SELECT separation_category_code
28 FROM hri_cs_sepcr_v
29 WHERE separation_reason_code = p_leaving_reason;
30 --
31 BEGIN
32 --
33 -- If no leaving reason is passed in return 'NA_EDW'
34 --
35 IF (p_leaving_reason IS NULL) THEN
36 --
37 l_separation_category := 'NA_EDW';
38 --
39 ELSE
40 --
41 BEGIN
42 --
43 -- If the value for this particular leaving reason is not cached in the
44 -- global pl/sql table, then NO_DATA_FOUND Exception will be raised and
45 -- the control will pass to the Exception section
46 --
47 l_separation_category := g_cache_emp_sep_resns_tab(p_leaving_reason);
48 --
49 EXCEPTION WHEN NO_DATA_FOUND THEN
50 --
51 -- When the value for a particular leaving reason is not cached in the
52 -- global table, the control comes here
53 -- The cursor is opened and the termination type for this leaving reason
54 -- is loaded in the global table
55 --
56 OPEN sep_cat_csr;
57 FETCH sep_cat_csr INTO l_separation_category;
58 CLOSE sep_cat_csr;
59 --
60 -- Update cache
61 --
62 g_cache_emp_sep_resns_tab(p_leaving_reason) := l_separation_category;
63 --
64 END;
65 --
66 END IF;
67 --
68 RETURN(l_separation_category);
69 --
70 --
71 -- When an exception is raised, the cursor is closed and the exception is passed
72 -- out of this block and it is handled in the collect procedure where an entry
73 -- of this is made in the concurrent log
74 --
75 EXCEPTION
76 --
77 WHEN OTHERS THEN
78 --
79 IF sep_cat_csr%ISOPEN THEN
80 --
81 CLOSE sep_cat_csr;
82 --
83 END IF;
84 --
85 RAISE;
86 --
87 END get_separation_category;
88
89 END hri_bpl_termination;
[Home] [Help]