[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_VIEW_WRKFRC
Source
1 PACKAGE BODY HRI_OLTP_VIEW_WRKFRC AS
2 /* $Header: hriovwrk.pkb 120.2 2006/10/26 14:13:47 jtitmas noship $ */
3 --
4 TYPE g_wrkfc_fk_rec_type IS RECORD
5 (per_person_mgr_fk NUMBER
6 ,mgr_mngrsc_fk NUMBER
7 ,org_organztn_fk NUMBER
8 ,job_job_fk NUMBER
9 ,grd_grade_fk NUMBER
10 ,pos_position_fk NUMBER
11 ,geo_location_fk NUMBER);
12 --
13 /******************************************************************************/
14 /* Calculates the ABV given a BMT code, business group and assignment */
15 /******************************************************************************/
16 --
17 FUNCTION calc_abv(p_assignment_id IN NUMBER,
18 p_business_group_id IN NUMBER,
19 p_bmt_code IN VARCHAR2,
20 p_effective_date IN DATE)
21 RETURN NUMBER IS
22 --
23 BEGIN
24 --
25 RETURN (hri_bpl_abv.calc_abv
26 (p_assignment_id => p_assignment_id,
27 p_business_group_id => p_business_group_id,
28 p_budget_type => p_bmt_code,
29 p_effective_date => p_effective_date));
30 --
31 EXCEPTION
32 WHEN OTHERS THEN
33 RETURN to_number(null);
34 END calc_abv;
35 --
36 /******************************************************************************/
37 /* Calculates the ABV given a BMT code, business group and assignment */
38 /******************************************************************************/
39 --
40 FUNCTION calc_abv(p_assignment_id IN NUMBER,
41 p_business_group_id IN NUMBER,
42 p_bmt_code IN VARCHAR2,
43 p_effective_date IN DATE,
44 p_primary_flag IN VARCHAR2)
45 RETURN NUMBER IS
46 --
47 BEGIN
48 --
49 RETURN (hri_bpl_abv.calc_abv
50 (p_assignment_id => p_assignment_id,
51 p_business_group_id => p_business_group_id,
52 p_budget_type => p_bmt_code,
53 p_effective_date => p_effective_date,
54 p_primary_flag => p_primary_flag));
55 --
56 EXCEPTION
57 WHEN OTHERS THEN
58 RETURN to_number(null);
59 END calc_abv;
60 --
61 PROCEDURE get_wrkfc_fks(p_assignment_id IN NUMBER,
62 p_effective_date IN DATE,
63 p_wrkfc_fk_rec OUT NOCOPY g_wrkfc_fk_rec_type) IS
64
65 CURSOR wrkfc_fk_csr IS
66 SELECT
67 fct.per_person_mgr_fk
68 ,fct.mgr_mngrsc_fk
69 ,fct.org_organztn_fk
70 ,fct.job_job_fk
71 ,fct.grd_grade_fk
72 ,fct.pos_position_fk
73 ,fct.geo_location_fk
74 FROM hri_mds_wrkfc_mnth_ct fct
75 WHERE fct.asg_assgnmnt_fk = p_assignment_id
76 AND fct.time_month_snp_fk = to_number(to_char(p_effective_date, 'YYYYQMM'));
77
78 BEGIN
79
80 OPEN wrkfc_fk_csr;
81 FETCH wrkfc_fk_csr INTO
82 p_wrkfc_fk_rec.per_person_mgr_fk,
83 p_wrkfc_fk_rec.mgr_mngrsc_fk,
84 p_wrkfc_fk_rec.org_organztn_fk,
85 p_wrkfc_fk_rec.job_job_fk,
86 p_wrkfc_fk_rec.grd_grade_fk,
87 p_wrkfc_fk_rec.pos_position_fk,
88 p_wrkfc_fk_rec.geo_location_fk;
89 CLOSE wrkfc_fk_csr;
90
91 END get_wrkfc_fks;
92
93 FUNCTION get_sup_fk(p_assignment_id IN NUMBER,
94 p_effective_date IN DATE)
95 RETURN NUMBER IS
96
97 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
98
99 BEGIN
100
101 get_wrkfc_fks
102 (p_assignment_id => p_assignment_id,
103 p_effective_date => p_effective_date,
104 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
105
106 RETURN l_wrkfc_fk_rec.per_person_mgr_fk;
107
108 END get_sup_fk;
109 --
110 FUNCTION get_mgrsc_fk(p_assignment_id IN NUMBER,
111 p_effective_date IN DATE)
112 RETURN NUMBER IS
113
114 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
115
116 BEGIN
117
118 get_wrkfc_fks
119 (p_assignment_id => p_assignment_id,
120 p_effective_date => p_effective_date,
121 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
122
123 RETURN l_wrkfc_fk_rec.mgr_mngrsc_fk;
124
125 END get_mgrsc_fk;
126 --
127 FUNCTION get_org_fk(p_assignment_id IN NUMBER,
128 p_effective_date IN DATE)
129 RETURN NUMBER IS
130
131 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
132
133 BEGIN
134
135 get_wrkfc_fks
136 (p_assignment_id => p_assignment_id,
137 p_effective_date => p_effective_date,
138 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
139
140 RETURN l_wrkfc_fk_rec.org_organztn_fk;
141
142 END get_org_fk;
143 --
144 FUNCTION get_job_fk(p_assignment_id IN NUMBER,
145 p_effective_date IN DATE)
146 RETURN NUMBER IS
147
148 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
149
150 BEGIN
151
152 get_wrkfc_fks
153 (p_assignment_id => p_assignment_id,
154 p_effective_date => p_effective_date,
155 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
156
157 RETURN l_wrkfc_fk_rec.job_job_fk;
158
159 END get_job_fk;
160 --
161 FUNCTION get_loc_fk(p_assignment_id IN NUMBER,
162 p_effective_date IN DATE)
163 RETURN NUMBER IS
164
165 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
166
167 BEGIN
168
169 get_wrkfc_fks
170 (p_assignment_id => p_assignment_id,
171 p_effective_date => p_effective_date,
172 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
173
174 RETURN l_wrkfc_fk_rec.geo_location_fk;
175
176 END get_loc_fk;
177 --
178 FUNCTION get_grd_fk(p_assignment_id IN NUMBER,
179 p_effective_date IN DATE)
180 RETURN NUMBER IS
181
182 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
183
184 BEGIN
185
186 get_wrkfc_fks
187 (p_assignment_id => p_assignment_id,
188 p_effective_date => p_effective_date,
189 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
190
191 RETURN l_wrkfc_fk_rec.grd_grade_fk;
192
193 END get_grd_fk;
194 --
195 FUNCTION get_pos_fk(p_assignment_id IN NUMBER,
196 p_effective_date IN DATE)
197 RETURN NUMBER IS
198
199 l_wrkfc_fk_rec g_wrkfc_fk_rec_type;
200
201 BEGIN
202
203 get_wrkfc_fks
204 (p_assignment_id => p_assignment_id,
205 p_effective_date => p_effective_date,
206 p_wrkfc_fk_rec => l_wrkfc_fk_rec);
207
208 RETURN l_wrkfc_fk_rec.pos_position_fk;
209
210 END get_pos_fk;
211 --
212 FUNCTION get_hire_info(p_assignment_id IN NUMBER,
213 p_effective_start_date IN DATE,
214 p_effective_end_date IN DATE,
215 p_budget_type IN VARCHAR2)
216 RETURN NUMBER IS
217
218 CURSOR hire_csr IS
219 SELECT
220 NVL(SUM(headcount_hire), 0)
221 ,NVL(SUM(fte_hire), 0)
222 FROM hri_mds_wrkfc_mnth_ct
223 WHERE asg_assgnmnt_fk = p_assignment_id
224 AND time_month_snp_fk BETWEEN to_number(to_char(p_effective_start_date, 'YYYYQMM'))
225 AND to_number(to_char(p_effective_end_date, 'YYYYQMM'));
226
227 l_hdc NUMBER;
228 l_fte NUMBER;
229
230 BEGIN
231
232 OPEN hire_csr;
233 FETCH hire_csr INTO l_hdc, l_fte;
234 CLOSE hire_csr;
235
236 IF p_budget_type = 'HEADCOUNT' THEN
237 RETURN l_hdc;
238 ELSIF p_budget_type = 'FTE' THEN
239 RETURN l_fte;
240 END IF;
241
242 RETURN to_number(null);
243
244 END get_hire_info;
245
246 FUNCTION get_prmtn_info(p_assignment_id IN NUMBER,
247 p_effective_start_date IN DATE,
248 p_effective_end_date IN DATE,
249 p_budget_type IN VARCHAR2)
250 RETURN NUMBER IS
251
252 CURSOR prmtn_csr IS
253 SELECT
254 NVL(SUM(headcount_prmtn), 0)
255 ,NVL(SUM(fte_prmtn), 0)
256 FROM hri_mds_wrkfc_mnth_ct
257 WHERE asg_assgnmnt_fk = p_assignment_id
258 AND time_month_snp_fk BETWEEN to_number(to_char(p_effective_start_date, 'YYYYQMM'))
259 AND to_number(to_char(p_effective_end_date, 'YYYYQMM'));
260
261 l_hdc NUMBER;
262 l_fte NUMBER;
263
264 BEGIN
265
266 OPEN prmtn_csr;
267 FETCH prmtn_csr INTO l_hdc, l_fte;
268 CLOSE prmtn_csr;
269
270 IF p_budget_type = 'HEADCOUNT' THEN
271 RETURN l_hdc;
272 ELSIF p_budget_type = 'FTE' THEN
273 RETURN l_fte;
274 END IF;
275
276 RETURN to_number(null);
277
278 END get_prmtn_info;
279
280 FUNCTION get_hire_hdc(p_assignment_id IN NUMBER,
281 p_effective_start_date IN DATE,
282 p_effective_end_date IN DATE)
283 RETURN NUMBER IS
284
285 BEGIN
286
287 RETURN get_hire_info
288 (p_assignment_id => p_assignment_id,
289 p_effective_start_date => p_effective_start_date,
290 p_effective_end_date => p_effective_end_date,
291 p_budget_type => 'HEADCOUNT');
292
293 END get_hire_hdc;
294 --
295 FUNCTION get_hire_fte(p_assignment_id IN NUMBER,
296 p_effective_start_date IN DATE,
297 p_effective_end_date IN DATE)
298 RETURN NUMBER IS
299
300 BEGIN
301
302 RETURN get_hire_info
303 (p_assignment_id => p_assignment_id,
304 p_effective_start_date => p_effective_start_date,
305 p_effective_end_date => p_effective_end_date,
306 p_budget_type => 'FTE');
307
308 END get_hire_fte;
309 --
310 FUNCTION get_prmtn_hdc(p_assignment_id IN NUMBER,
311 p_effective_start_date IN DATE,
312 p_effective_end_date IN DATE)
313 RETURN NUMBER IS
314
315 BEGIN
316
317 RETURN get_prmtn_info
318 (p_assignment_id => p_assignment_id,
319 p_effective_start_date => p_effective_start_date,
320 p_effective_end_date => p_effective_end_date,
321 p_budget_type => 'HEADCOUNT');
322
323 END get_prmtn_hdc;
324 --
325 FUNCTION get_prmtn_fte(p_assignment_id IN NUMBER,
326 p_effective_start_date IN DATE,
327 p_effective_end_date IN DATE)
328 RETURN NUMBER IS
329
330 BEGIN
331
332 RETURN get_prmtn_info
333 (p_assignment_id => p_assignment_id,
334 p_effective_start_date => p_effective_start_date,
335 p_effective_end_date => p_effective_end_date,
336 p_budget_type => 'FTE');
337
338 END get_prmtn_fte;
339 --
340 END HRI_OLTP_VIEW_WRKFRC;