[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_VIEW_WRKFRC_MGRH
Source
1 PACKAGE BODY HRI_OLTP_VIEW_WRKFRC_MGRH AS
2 /* $Header: hriovwrkmgr.pkb 120.2 2011/09/21 09:21:07 vepravee ship $ */
3
4 FUNCTION get_hdc(p_sup_person_id IN NUMBER,
5 p_effective_date IN DATE,
6 p_worker_type IN VARCHAR2,
7 p_directs_only IN VARCHAR2)
8 RETURN NUMBER IS
9
10 CURSOR hdc_csr IS
11 SELECT NVL(SUM(wrkfc.headcount_end), 0)
12 FROM hri_mds_wrkfc_mgrh_c01_ct wrkfc
13 WHERE wrkfc.sup_person_fk = p_sup_person_id
14 AND wrkfc.time_month_snp_fk = to_number(to_char(p_effective_date, 'YYYYQMM'))
15 AND wrkfc.ptyp_wrktyp_fk = p_worker_type
16 AND wrkfc.sup_directs_only_flag = p_directs_only;
17
18 l_hdc NUMBER;
19
20 BEGIN
21
22 OPEN hdc_csr;
23 FETCH hdc_csr INTO l_hdc;
24 CLOSE hdc_csr;
25
26 RETURN l_hdc;
27
28 END get_hdc;
29
30 FUNCTION get_fte(p_sup_person_id IN NUMBER,
31 p_effective_date IN DATE,
32 p_worker_type IN VARCHAR2,
33 p_directs_only IN VARCHAR2)
34 RETURN NUMBER IS
35
36 CURSOR fte_csr IS
37 SELECT NVL(SUM(wrkfc.fte_end), 0)
38 FROM hri_mds_wrkfc_mgrh_c01_ct wrkfc
39 WHERE wrkfc.sup_person_fk = p_sup_person_id
40 AND wrkfc.time_month_snp_fk = to_char(p_effective_date, 'YYYYQMM')
41 AND wrkfc.ptyp_wrktyp_fk = p_worker_type
42 AND wrkfc.sup_directs_only_flag = p_directs_only;
43
44 l_fte NUMBER;
45
46 BEGIN
47
48 OPEN fte_csr;
49 FETCH fte_csr INTO l_fte;
50 CLOSE fte_csr;
51
52 RETURN l_fte;
53
54 END get_fte;
55
56 FUNCTION get_pasg_cnt(p_sup_person_id IN NUMBER,
57 p_effective_date IN DATE,
58 p_worker_type IN VARCHAR2,
59 p_directs_only IN VARCHAR2)
60 RETURN NUMBER IS
61
62 CURSOR pasg_cnt_csr IS
63 SELECT NVL(SUM(wrkfc.count_pasg_end), 0)
64 FROM hri_mds_wrkfc_mgrh_c01_ct wrkfc
65 WHERE wrkfc.sup_person_fk = p_sup_person_id
66 AND wrkfc.time_month_snp_fk = to_char(p_effective_date, 'YYYYQMM')
67 AND wrkfc.ptyp_wrktyp_fk = p_worker_type
68 AND wrkfc.sup_directs_only_flag = p_directs_only;
69
70 l_pasg_cnt NUMBER;
71
72 BEGIN
73
74 OPEN pasg_cnt_csr;
75 FETCH pasg_cnt_csr INTO l_pasg_cnt;
76 CLOSE pasg_cnt_csr;
77
78 RETURN l_pasg_cnt;
79
80 END get_pasg_cnt;
81
82 FUNCTION get_asg_cnt(p_sup_person_id IN NUMBER,
83 p_effective_date IN DATE,
84 p_worker_type IN VARCHAR2,
85 p_directs_only IN VARCHAR2)
86 RETURN NUMBER IS
87
88 CURSOR asg_cnt_csr IS
89 SELECT NVL(SUM(wrkfc.count_asg_end), 0)
90 FROM hri_mds_wrkfc_mgrh_c01_ct wrkfc
91 WHERE wrkfc.sup_person_fk = p_sup_person_id
92 AND wrkfc.time_month_snp_fk = to_char(p_effective_date, 'YYYYQMM')
93 AND wrkfc.ptyp_wrktyp_fk = p_worker_type
94 AND wrkfc.sup_directs_only_flag = p_directs_only;
95
96 l_asg_cnt NUMBER;
97
98 BEGIN
99
100 OPEN asg_cnt_csr;
101 FETCH asg_cnt_csr INTO l_asg_cnt;
102 CLOSE asg_cnt_csr;
103
104 RETURN l_asg_cnt;
105
106 END get_asg_cnt;
107
108 FUNCTION get_transfer_info(p_sup_person_id IN NUMBER,
109 p_from_date IN DATE,
110 p_to_date IN DATE,
111 p_worker_type IN VARCHAR2,
112 p_directs_only IN VARCHAR2,
113 p_budget_type IN VARCHAR2,
114 p_transfer_type IN VARCHAR2)
115 RETURN NUMBER IS
116
117 CURSOR trn_csr(v_direct_record_ind IN NUMBER) IS
118 SELECT
119 NVL(SUM(wevt.headcount * trn.transfer_in_ind), 0) hdc_trn_in
120 ,NVL(SUM(wevt.headcount * trn.transfer_out_ind), 0) hdc_trn_out
121 ,NVL(SUM(wevt.fte * trn.transfer_in_ind), 0) fte_trn_in
122 ,NVL(SUM(wevt.fte * trn.transfer_out_ind), 0) fte_trn_out
123 ,NVL(SUM(wevt.primary_ind * trn.transfer_in_ind), 0) pasg_cnt_trn_in
124 ,NVL(SUM(wevt.primary_ind * trn.transfer_out_ind), 0) pasg_cnt_trn_out
125 ,NVL(SUM(trn.transfer_in_ind), 0) asg_cnt_trn_in
126 ,NVL(SUM(trn.transfer_out_ind), 0) asg_cnt_trn_out
127 FROM
128 hri_mdp_mgrh_transfers_ct trn
129 ,hri_mb_wrkfc_evt_ct wevt
130 WHERE trn.mgr_sup_person_fk = p_sup_person_id
131 AND trn.ptyp_wrktyp_fk = p_worker_type
132 AND trn.asg_assgnmnt_fk = wevt.asg_assgnmnt_fk
133 AND trn.time_day_evt_fk BETWEEN wevt.time_day_evt_fk
134 AND wevt.time_day_evt_end_fk
135 AND trn.direct_record_ind = v_direct_record_ind
136 AND trn.time_day_evt_fk BETWEEN p_from_date
137 AND p_to_date;
138
139 l_direct_record_ind NUMBER;
140 l_hdc_trn_in NUMBER;
141 l_hdc_trn_out NUMBER;
142 l_fte_trn_in NUMBER;
143 l_fte_trn_out NUMBER;
144 l_pasg_cnt_trn_in NUMBER;
145 l_pasg_cnt_trn_out NUMBER;
146 l_asg_cnt_trn_in NUMBER;
147 l_asg_cnt_trn_out NUMBER;
148
149 BEGIN
150
151 IF (p_directs_only = 'Y') THEN
152 l_direct_record_ind := 1;
153 ELSE
154 l_direct_record_ind := 0;
155 END IF;
156
157 OPEN trn_csr(l_direct_record_ind);
158 FETCH trn_csr INTO
159 l_hdc_trn_in,
160 l_hdc_trn_out,
161 l_fte_trn_in,
162 l_fte_trn_out,
163 l_pasg_cnt_trn_in,
164 l_pasg_cnt_trn_out,
165 l_asg_cnt_trn_in,
166 l_asg_cnt_trn_out;
167 CLOSE trn_csr;
168
169 IF (p_budget_type = 'HEADCOUNT' AND p_transfer_type = 'IN') THEN
170 RETURN l_hdc_trn_in;
171 ELSIF (p_budget_type = 'HEADCOUNT' AND p_transfer_type = 'OUT') THEN
172 RETURN l_hdc_trn_out;
173 ELSIF (p_budget_type = 'FTE' AND p_transfer_type = 'IN') THEN
174 RETURN l_fte_trn_in;
175 ELSIF (p_budget_type = 'FTE' AND p_transfer_type = 'OUT') THEN
176 RETURN l_fte_trn_out;
177 ELSIF (p_budget_type = 'PASG_CNT' AND p_transfer_type = 'IN') THEN
178 RETURN l_pasg_cnt_trn_in;
179 ELSIF (p_budget_type = 'PASG_CNT' AND p_transfer_type = 'OUT') THEN
180 RETURN l_pasg_cnt_trn_out;
181 ELSIF (p_budget_type = 'ASG_CNT' AND p_transfer_type = 'IN') THEN
182 RETURN l_asg_cnt_trn_in;
183 ELSIF (p_budget_type = 'ASG_CNT' AND p_transfer_type = 'OUT') THEN
184 RETURN l_asg_cnt_trn_out;
185 END IF;
186
187 RETURN to_number(null);
188
189 END get_transfer_info;
190
191 FUNCTION get_trn_in_hdc(p_sup_person_id IN NUMBER,
192 p_from_date IN DATE,
193 p_to_date IN DATE,
194 p_worker_type IN VARCHAR2,
195 p_directs_only IN VARCHAR2)
196 RETURN NUMBER IS
197
198 BEGIN
199
200 RETURN get_transfer_info
201 (p_sup_person_id => p_sup_person_id,
202 p_from_date => p_from_date,
203 p_to_date => p_to_date,
204 p_worker_type => p_worker_type,
205 p_directs_only => p_directs_only,
206 p_budget_type => 'HEADCOUNT',
207 p_transfer_type => 'IN');
208
209 END get_trn_in_hdc;
210
211 FUNCTION get_trn_out_hdc(p_sup_person_id IN NUMBER,
212 p_from_date IN DATE,
213 p_to_date IN DATE,
214 p_worker_type IN VARCHAR2,
215 p_directs_only IN VARCHAR2)
216 RETURN NUMBER IS
217
218 BEGIN
219
220 RETURN get_transfer_info
221 (p_sup_person_id => p_sup_person_id,
222 p_from_date => p_from_date,
223 p_to_date => p_to_date,
224 p_worker_type => p_worker_type,
225 p_directs_only => p_directs_only,
226 p_budget_type => 'HEADCOUNT',
227 p_transfer_type => 'OUT');
228
229 END get_trn_out_hdc;
230
231 FUNCTION get_trn_in_fte(p_sup_person_id IN NUMBER,
232 p_from_date IN DATE,
233 p_to_date IN DATE,
234 p_worker_type IN VARCHAR2,
235 p_directs_only IN VARCHAR2)
236 RETURN NUMBER IS
237
238 BEGIN
239
240 RETURN get_transfer_info
241 (p_sup_person_id => p_sup_person_id,
242 p_from_date => p_from_date,
243 p_to_date => p_to_date,
244 p_worker_type => p_worker_type,
245 p_directs_only => p_directs_only,
246 p_budget_type => 'FTE',
247 p_transfer_type => 'IN');
248
249 END get_trn_in_fte;
250
251 FUNCTION get_trn_out_fte(p_sup_person_id IN NUMBER,
252 p_from_date IN DATE,
253 p_to_date IN DATE,
254 p_worker_type IN VARCHAR2,
255 p_directs_only IN VARCHAR2)
256 RETURN NUMBER IS
257
258 BEGIN
259
260 RETURN get_transfer_info
261 (p_sup_person_id => p_sup_person_id,
262 p_from_date => p_from_date,
263 p_to_date => p_to_date,
264 p_worker_type => p_worker_type,
265 p_directs_only => p_directs_only,
266 p_budget_type => 'FTE',
267 p_transfer_type => 'OUT');
268
269 END get_trn_out_fte;
270
271 FUNCTION get_trn_in_pasg_cnt(p_sup_person_id IN NUMBER,
272 p_from_date IN DATE,
273 p_to_date IN DATE,
274 p_worker_type IN VARCHAR2,
275 p_directs_only IN VARCHAR2)
276 RETURN NUMBER IS
277
278 BEGIN
279
280 RETURN get_transfer_info
281 (p_sup_person_id => p_sup_person_id,
282 p_from_date => p_from_date,
283 p_to_date => p_to_date,
284 p_worker_type => p_worker_type,
285 p_directs_only => p_directs_only,
286 p_budget_type => 'PASG_CNT',
287 p_transfer_type => 'IN');
288
289 END get_trn_in_pasg_cnt;
290
291 FUNCTION get_trn_out_pasg_cnt(p_sup_person_id IN NUMBER,
292 p_from_date IN DATE,
293 p_to_date IN DATE,
294 p_worker_type IN VARCHAR2,
295 p_directs_only IN VARCHAR2)
296 RETURN NUMBER IS
297
298 BEGIN
299
300 RETURN get_transfer_info
301 (p_sup_person_id => p_sup_person_id,
302 p_from_date => p_from_date,
303 p_to_date => p_to_date,
304 p_worker_type => p_worker_type,
305 p_directs_only => p_directs_only,
306 p_budget_type => 'PASG_CNT',
307 p_transfer_type => 'OUT');
308
309 END get_trn_out_pasg_cnt;
310
311 FUNCTION get_trn_in_asg_cnt(p_sup_person_id IN NUMBER,
312 p_from_date IN DATE,
313 p_to_date IN DATE,
314 p_worker_type IN VARCHAR2,
315 p_directs_only IN VARCHAR2)
316 RETURN NUMBER IS
317
318 BEGIN
319
320 RETURN get_transfer_info
321 (p_sup_person_id => p_sup_person_id,
322 p_from_date => p_from_date,
323 p_to_date => p_to_date,
324 p_worker_type => p_worker_type,
325 p_directs_only => p_directs_only,
326 p_budget_type => 'ASG_CNT',
327 p_transfer_type => 'IN');
328
329 END get_trn_in_asg_cnt;
330
331 FUNCTION get_trn_out_asg_cnt(p_sup_person_id IN NUMBER,
332 p_from_date IN DATE,
333 p_to_date IN DATE,
334 p_worker_type IN VARCHAR2,
335 p_directs_only IN VARCHAR2)
336 RETURN NUMBER IS
337
338 BEGIN
339
340 RETURN get_transfer_info
341 (p_sup_person_id => p_sup_person_id,
342 p_from_date => p_from_date,
343 p_to_date => p_to_date,
344 p_worker_type => p_worker_type,
345 p_directs_only => p_directs_only,
346 p_budget_type => 'ASG_CNT',
347 p_transfer_type => 'OUT');
348
349 END get_trn_out_asg_cnt;
350
351 END HRI_OLTP_VIEW_WRKFRC_MGRH;