[Home] [Help]
PACKAGE BODY: APPS.HRI_BPL_WRKFC_EVT
Source
1 PACKAGE BODY hri_bpl_wrkfc_evt AS
2 /* $Header: hribwevt.pkb 120.0.12000000.2 2007/04/12 12:08:27 smohapat noship $ */
3
4 g_hri_promotion_formula_id NUMBER;
5 g_hr_promotion_formula_id NUMBER;
6
7 FUNCTION get_job_name(p_job_id IN NUMBER)
8 RETURN VARCHAR2 IS
9
10 l_job_name VARCHAR2(240);
11
12 BEGIN
13
14 SELECT name INTO l_job_name
15 FROM per_jobs
16 WHERE job_id = p_job_id;
17
18 RETURN l_job_name;
19
20 EXCEPTION WHEN OTHERS THEN
21
22 RETURN 'Unassigned';
23
24 END get_job_name;
25
26 FUNCTION get_position_name(p_position_id IN NUMBER)
27 RETURN VARCHAR2 IS
28
29 l_position_name VARCHAR2(240);
30
31 BEGIN
32
33 SELECT name INTO l_position_name
34 FROM per_all_positions
35 WHERE position_id = p_position_id;
36
37 RETURN l_position_name;
38
39 EXCEPTION WHEN OTHERS THEN
40
41 RETURN 'Unassigned';
42
43 END get_position_name;
44
45 FUNCTION get_grade_name(p_grade_id IN NUMBER)
46 RETURN VARCHAR2 IS
47
48 l_grade_name VARCHAR2(240);
49
50 BEGIN
51
52 SELECT name INTO l_grade_name
53 FROM per_grades
54 WHERE grade_id = p_grade_id;
55
56 RETURN l_grade_name;
57
58 EXCEPTION WHEN OTHERS THEN
59
60 RETURN 'Unassigned';
61
62 END get_grade_name;
63
64 FUNCTION get_promotion_ind
65 (p_assignment_id IN NUMBER,
66 p_business_group_id IN NUMBER,
67 p_effective_date IN DATE,
68 p_new_job_id IN NUMBER,
69 p_new_pos_id IN NUMBER,
70 p_new_grd_id IN NUMBER,
71 p_old_job_id IN NUMBER,
72 p_old_pos_id IN NUMBER,
73 p_old_grd_id IN NUMBER)
74 RETURN NUMBER IS
75
76 l_formula_input_tab hri_bpl_fast_formula_util.formula_param_type;
77 l_formula_output_tab hri_bpl_fast_formula_util.formula_param_type;
78 l_promotion_flag VARCHAR2(30);
79 l_promotion_ind NUMBER;
80
81 BEGIN
82
83 -- Check if hr formula exists
84 g_hr_promotion_formula_id := hri_bpl_fast_formula_util.fetch_bg_formula_id
85 (p_formula_name => 'PROMOTION',
86 p_business_group_id => p_business_group_id,
87 p_formula_type_name => 'Promotion');
88
89 -- Use HR formula if it exists
90 IF g_hr_promotion_formula_id IS NOT NULL THEN
91
92 -- Initialize formula input parameters
93 l_formula_input_tab('ASSIGNMENT_ID') := p_assignment_id;
94 l_formula_input_tab('DATE_EARNED') := fnd_date.date_to_canonical(p_effective_date);
95
96 -- Extract outputs
97 BEGIN
98
99 -- Run formula
100 hri_bpl_fast_formula_util.run_formula
101 (p_formula_id => g_hr_promotion_formula_id,
102 p_input_tab => l_formula_input_tab,
103 p_output_tab => l_formula_output_tab);
104
105 -- Set output values
106 IF l_formula_output_tab(l_formula_output_tab.FIRST) > 0 THEN
107 l_promotion_flag := 'Y';
108 END IF;
109
110 -- Trap exception if formula does not exists, or errors
111 EXCEPTION WHEN OTHERS THEN
112 null;
113 END;
114
115 -- Otherwise check if HRI formula is available
116 ELSIF (g_hri_promotion_formula_id IS NOT NULL AND
117 (p_new_grd_id <> p_old_grd_id OR
118 p_new_job_id <> p_old_job_id OR
119 p_new_pos_id <> p_old_pos_id)) THEN
120
121 -- Initialize formula input parameters
122 l_formula_input_tab('JOB_NEW') := get_job_name(p_new_job_id);
123 l_formula_input_tab('JOB_OLD') := get_job_name(p_old_job_id);
124 l_formula_input_tab('GRADE_NEW') := get_grade_name(p_new_grd_id);
125 l_formula_input_tab('GRADE_OLD') := get_grade_name(p_old_grd_id);
126 l_formula_input_tab('POSITION_NEW') := get_position_name(p_new_pos_id);
127 l_formula_input_tab('POSITION_OLD') := get_position_name(p_old_pos_id);
128
129 -- Extract outputs
130 BEGIN
131
132 -- Run formula
133 hri_bpl_fast_formula_util.run_formula
134 (p_formula_id => g_hri_promotion_formula_id,
135 p_input_tab => l_formula_input_tab,
136 p_output_tab => l_formula_output_tab);
137
138 -- Set output values
139 l_promotion_flag := l_formula_output_tab('PROMOTION_CODE');
140
141 -- Trap exception if formula does not exists, or errors
142 EXCEPTION WHEN OTHERS THEN
143 null;
144 END;
145
146 -- If formula undefined default to grade change = promotion
147 ELSIF p_new_grd_id = p_old_grd_id THEN
148 l_promotion_flag := 'N';
149 ELSE
150 l_promotion_flag := 'Y';
151 END IF;
152
153 -- Return indicator
154 IF l_promotion_flag = 'Y' THEN
155 l_promotion_ind := 1;
156 ELSE
157 l_promotion_ind := 0;
158 END IF;
159
160 RETURN l_promotion_ind;
161
162 END get_promotion_ind;
163
164 BEGIN
165
166 g_hri_promotion_formula_id := hri_bpl_fast_formula_util.fetch_setup_formula_id
167 (p_formula_name => 'HRI_MAP_PROMOTION_EVENT');
168
169 END hri_bpl_wrkfc_evt;