[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_VIEW_WRKFRC_ORGH
Source
1 PACKAGE BODY HRI_OLTP_VIEW_WRKFRC_ORGH AS
2 /* $Header: hriovwrkorg.pkb 120.2 2011/09/21 09:24:57 vepravee ship $ */
3
4 FUNCTION get_hdc(p_sup_organization_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_orgh_c01_ct wrkfc
13 WHERE wrkfc.sup_organztn_fk = p_sup_organization_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_organization_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_orgh_c01_ct wrkfc
39 WHERE wrkfc.sup_organztn_fk = p_sup_organization_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_organization_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_orgh_c01_ct wrkfc
65 WHERE wrkfc.sup_organztn_fk = p_sup_organization_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_organization_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_orgh_c01_ct wrkfc
91 WHERE wrkfc.sup_organztn_fk = p_sup_organization_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_organization_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 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_orgh_transfers_ct trn
129 ,hri_mb_wrkfc_evt_ct wevt
130 WHERE trn.org_sup_organztn_fk = p_sup_organization_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 (p_directs_only = 'N' OR trn.direct_ind = 1)
136 AND trn.time_day_evt_fk BETWEEN p_from_date
137 AND p_to_date;
138
139 l_hdc_trn_in NUMBER;
140 l_hdc_trn_out NUMBER;
141 l_fte_trn_in NUMBER;
142 l_fte_trn_out NUMBER;
143 l_pasg_cnt_trn_in NUMBER;
144 l_pasg_cnt_trn_out NUMBER;
145 l_asg_cnt_trn_in NUMBER;
146 l_asg_cnt_trn_out NUMBER;
147
148 BEGIN
149
150 OPEN trn_csr;
151 FETCH trn_csr INTO
152 l_hdc_trn_in,
153 l_hdc_trn_out,
154 l_fte_trn_in,
155 l_fte_trn_out,
156 l_pasg_cnt_trn_in,
157 l_pasg_cnt_trn_out,
158 l_asg_cnt_trn_in,
159 l_asg_cnt_trn_out;
160 CLOSE trn_csr;
161
162 IF (p_budget_type = 'HEADCOUNT' AND p_transfer_type = 'IN') THEN
163 RETURN l_hdc_trn_in;
164 ELSIF (p_budget_type = 'HEADCOUNT' AND p_transfer_type = 'OUT') THEN
165 RETURN l_hdc_trn_out;
166 ELSIF (p_budget_type = 'FTE' AND p_transfer_type = 'IN') THEN
167 RETURN l_fte_trn_in;
168 ELSIF (p_budget_type = 'FTE' AND p_transfer_type = 'OUT') THEN
169 RETURN l_fte_trn_out;
170 ELSIF (p_budget_type = 'PASG_CNT' AND p_transfer_type = 'IN') THEN
171 RETURN l_pasg_cnt_trn_in;
172 ELSIF (p_budget_type = 'PASG_CNT' AND p_transfer_type = 'OUT') THEN
173 RETURN l_pasg_cnt_trn_out;
174 ELSIF (p_budget_type = 'ASG_CNT' AND p_transfer_type = 'IN') THEN
175 RETURN l_asg_cnt_trn_in;
176 ELSIF (p_budget_type = 'ASG_CNT' AND p_transfer_type = 'OUT') THEN
177 RETURN l_asg_cnt_trn_out;
178 END IF;
179
180 RETURN to_number(null);
181
182 END get_transfer_info;
183
184 FUNCTION get_trn_in_hdc(p_sup_organization_id IN NUMBER,
185 p_from_date IN DATE,
186 p_to_date IN DATE,
187 p_worker_type IN VARCHAR2,
188 p_directs_only IN VARCHAR2)
189 RETURN NUMBER IS
190
191 BEGIN
192
193 RETURN get_transfer_info
194 (p_sup_organization_id => p_sup_organization_id,
195 p_from_date => p_from_date,
196 p_to_date => p_to_date,
197 p_worker_type => p_worker_type,
198 p_directs_only => p_directs_only,
199 p_budget_type => 'HEADCOUNT',
200 p_transfer_type => 'IN');
201
202 END get_trn_in_hdc;
203
204 FUNCTION get_trn_out_hdc(p_sup_organization_id IN NUMBER,
205 p_from_date IN DATE,
206 p_to_date IN DATE,
207 p_worker_type IN VARCHAR2,
208 p_directs_only IN VARCHAR2)
209 RETURN NUMBER IS
210
211 BEGIN
212
213 RETURN get_transfer_info
214 (p_sup_organization_id => p_sup_organization_id,
215 p_from_date => p_from_date,
216 p_to_date => p_to_date,
217 p_worker_type => p_worker_type,
218 p_directs_only => p_directs_only,
219 p_budget_type => 'HEADCOUNT',
220 p_transfer_type => 'OUT');
221
222 END get_trn_out_hdc;
223
224 FUNCTION get_trn_in_fte(p_sup_organization_id IN NUMBER,
225 p_from_date IN DATE,
226 p_to_date IN DATE,
227 p_worker_type IN VARCHAR2,
228 p_directs_only IN VARCHAR2)
229 RETURN NUMBER IS
230
231 BEGIN
232
233 RETURN get_transfer_info
234 (p_sup_organization_id => p_sup_organization_id,
235 p_from_date => p_from_date,
236 p_to_date => p_to_date,
237 p_worker_type => p_worker_type,
238 p_directs_only => p_directs_only,
239 p_budget_type => 'FTE',
240 p_transfer_type => 'IN');
241
242 END get_trn_in_fte;
243
244 FUNCTION get_trn_out_fte(p_sup_organization_id IN NUMBER,
245 p_from_date IN DATE,
246 p_to_date IN DATE,
247 p_worker_type IN VARCHAR2,
248 p_directs_only IN VARCHAR2)
249 RETURN NUMBER IS
250
251 BEGIN
252
253 RETURN get_transfer_info
254 (p_sup_organization_id => p_sup_organization_id,
255 p_from_date => p_from_date,
256 p_to_date => p_to_date,
257 p_worker_type => p_worker_type,
258 p_directs_only => p_directs_only,
259 p_budget_type => 'FTE',
260 p_transfer_type => 'OUT');
261
262 END get_trn_out_fte;
263
264 FUNCTION get_trn_in_pasg_cnt(p_sup_organization_id IN NUMBER,
265 p_from_date IN DATE,
266 p_to_date IN DATE,
267 p_worker_type IN VARCHAR2,
268 p_directs_only IN VARCHAR2)
269 RETURN NUMBER IS
270
271 BEGIN
272
273 RETURN get_transfer_info
274 (p_sup_organization_id => p_sup_organization_id,
275 p_from_date => p_from_date,
276 p_to_date => p_to_date,
277 p_worker_type => p_worker_type,
278 p_directs_only => p_directs_only,
279 p_budget_type => 'PASG_CNT',
280 p_transfer_type => 'IN');
281
282 END get_trn_in_pasg_cnt;
283
284 FUNCTION get_trn_out_pasg_cnt(p_sup_organization_id IN NUMBER,
285 p_from_date IN DATE,
286 p_to_date IN DATE,
287 p_worker_type IN VARCHAR2,
288 p_directs_only IN VARCHAR2)
289 RETURN NUMBER IS
290
291 BEGIN
292
293 RETURN get_transfer_info
294 (p_sup_organization_id => p_sup_organization_id,
295 p_from_date => p_from_date,
296 p_to_date => p_to_date,
297 p_worker_type => p_worker_type,
298 p_directs_only => p_directs_only,
299 p_budget_type => 'PASG_CNT',
300 p_transfer_type => 'OUT');
301
302 END get_trn_out_pasg_cnt;
303
304 FUNCTION get_trn_in_asg_cnt(p_sup_organization_id IN NUMBER,
305 p_from_date IN DATE,
306 p_to_date IN DATE,
307 p_worker_type IN VARCHAR2,
308 p_directs_only IN VARCHAR2)
309 RETURN NUMBER IS
310
311 BEGIN
312
313 RETURN get_transfer_info
314 (p_sup_organization_id => p_sup_organization_id,
315 p_from_date => p_from_date,
316 p_to_date => p_to_date,
317 p_worker_type => p_worker_type,
318 p_directs_only => p_directs_only,
319 p_budget_type => 'ASG_CNT',
320 p_transfer_type => 'IN');
321
322 END get_trn_in_asg_cnt;
323
324 FUNCTION get_trn_out_asg_cnt(p_sup_organization_id IN NUMBER,
325 p_from_date IN DATE,
326 p_to_date IN DATE,
327 p_worker_type IN VARCHAR2,
328 p_directs_only IN VARCHAR2)
329 RETURN NUMBER IS
330
331 BEGIN
332
333 RETURN get_transfer_info
334 (p_sup_organization_id => p_sup_organization_id,
335 p_from_date => p_from_date,
336 p_to_date => p_to_date,
337 p_worker_type => p_worker_type,
338 p_directs_only => p_directs_only,
339 p_budget_type => 'ASG_CNT',
340 p_transfer_type => 'OUT');
341
342 END get_trn_out_asg_cnt;
343
344 END HRI_OLTP_VIEW_WRKFRC_ORGH;