1 PACKAGE BODY hri_bpl_dbi_calc_period AS
2 /* $Header: hribdcrp.pkb 120.7 2005/11/10 01:53:49 jrstewar noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6 /* Total Absence events for a supervisor in a period */
7 /******************************************************/
8 PROCEDURE calc_sup_absence(p_supervisor_id IN NUMBER,
9 p_from_date IN DATE,
10 p_to_date IN DATE,
11 p_period_type IN VARCHAR2,
12 p_comparison_type IN VARCHAR2,
13 p_total_type IN VARCHAR2,
14 p_wkth_wktyp_sk_fk IN VARCHAR2,
15 p_total_abs_drtn_days OUT NOCOPY NUMBER,
16 p_total_abs_drtn_hrs OUT NOCOPY NUMBER,
17 p_total_abs_in_period OUT NOCOPY NUMBER,
18 p_total_abs_ntfctn_period OUT NOCOPY NUMBER) IS
19
20 CURSOR calc_totals_for_sup(v_direct_record_ind IN NUMBER) IS
21 SELECT
22 SUM(CASE WHEN a.effective_date BETWEEN p_from_date AND p_to_date
23 THEN a.abs_drtn_days
24 ELSE 0
25 END) abs_drtn_days
26 ,SUM(CASE WHEN a.effective_date BETWEEN p_from_date AND p_to_date
27 THEN abs_drtn_hrs
28 ELSE 0
29 END) abs_drtn_hrs
30 ,SUM(CASE WHEN a.effective_date = p_from_date
31 THEN a.abs_start_blnc + a.abs_nstart_blnc
32 WHEN a.effective_date > p_from_date
33 AND a.effective_date <= p_to_date
34 THEN abs_start_blnc
35 ELSE 0
36 END) abs_in_period
37 ,SUM(CASE WHEN a.effective_date = p_from_date
38 THEN a.abs_ntfctn_days_start_blnc + a.abs_ntfctn_days_nstart_blnc
39 WHEN a.effective_date > p_from_date
40 AND a.effective_date <= p_to_date
41 THEN abs_ntfctn_days_start_blnc
42 ELSE 0
43 END) abs_ntfctn_period
44 FROM HRI_MDP_SUP_ABSNC_SUP_MV a
45 WHERE a.supervisor_person_id = p_supervisor_id
46 AND a.direct_record_ind = v_direct_record_ind
47 AND a.effective_date BETWEEN p_from_date AND p_to_date;
48
49
50 l_direct_record_ind NUMBER;
51 l_use_snapshot BOOLEAN;
52
53 BEGIN
54
55
56 /* Set record type indicator */
57 IF p_total_type = 'ROLLUP' THEN
58 l_direct_record_ind := 0;
59 ELSE
60 l_direct_record_ind := 1;
61 END IF;
62
63 /* Get WMV Change totals for supervisor from cursor */
64 OPEN calc_totals_for_sup(l_direct_record_ind);
65 FETCH calc_totals_for_sup INTO p_total_abs_drtn_days,
66 p_total_abs_drtn_hrs,
67 p_total_abs_in_period,
68 p_total_abs_ntfctn_period;
69 CLOSE calc_totals_for_sup;
70
71
72 END calc_sup_absence;
73
74
75 /* Total workforce change events for a supervisor in a period */
76 /**************************************************************/
77 PROCEDURE calc_sup_wcnt_chg(p_supervisor_id IN NUMBER,
78 p_from_date IN DATE,
79 p_to_date IN DATE,
80 p_period_type IN VARCHAR2,
81 p_comparison_type IN VARCHAR2,
82 p_total_type IN VARCHAR2,
83 p_total_gain_hire OUT NOCOPY NUMBER,
84 p_total_gain_transfer OUT NOCOPY NUMBER,
85 p_total_loss_term OUT NOCOPY NUMBER,
86 p_total_loss_transfer OUT NOCOPY NUMBER) IS
87
88 CURSOR calc_totals_for_sup(v_direct_record_ind IN NUMBER) IS
89 SELECT
90 NVL(SUM(a.hire_hdc),0) tot_gain_hire
91 ,NVL(SUM(a.transfer_in_hdc),0) tot_gain_transfer
92 ,NVL(SUM(a.termination_hdc),0) tot_loss_term
93 ,NVL(SUM(a.transfer_out_hdc),0) tot_loss_transfer
94 FROM hri_mdp_sup_wcnt_chg_mv a
95 WHERE a.supervisor_person_id = p_supervisor_id
96 AND a.direct_record_ind = v_direct_record_ind
97 AND a.effective_date BETWEEN p_from_date
98 AND p_to_date;
99
100 CURSOR calc_totals_for_sup_snp(v_direct_record_ind IN NUMBER) IS
101 SELECT /*+ INDEX(a) */
102 NVL(SUM(a.hire_hdc),0) tot_gain_hire
103 ,NVL(SUM(a.transfer_in_hdc),0) tot_gain_transfer
104 ,NVL(SUM(a.termination_hdc),0) tot_loss_term
105 ,NVL(SUM(a.transfer_out_hdc),0) tot_loss_transfer
106 FROM hri_mds_sup_wcnt_chg_mv a
107 WHERE a.supervisor_person_id = p_supervisor_id
108 AND a.direct_record_ind = v_direct_record_ind
109 AND a.effective_date = p_to_date
110 AND a.period_type = p_period_type
111 AND a.comparison_type IN (p_comparison_type, 'CURRENT');
112
113 l_direct_record_ind NUMBER;
114 l_use_snapshot BOOLEAN;
115
116 BEGIN
117
118 /* Check whether a snapshot can be used */
119 /* Note a snapshot may be used to get the total even if the portlet query */
120 /* is not able to use snapshots */
121 l_use_snapshot := hri_oltp_pmv_util_snpsht.use_wcnt_chg_snpsht_for_mgr
122 (p_supervisor_id => p_supervisor_id,
123 p_effective_date => p_to_date);
124
125 /* Set record type indicator */
126 IF p_total_type = 'ROLLUP' THEN
127 l_direct_record_ind := 0;
128 ELSE
129 l_direct_record_ind := 1;
130 END IF;
131
132 /* Open the cursor corresponding to the snapshot flag */
133 IF l_use_snapshot THEN
134
135 /* Get WMV Change totals for supervisor from snapshot cursor */
136 OPEN calc_totals_for_sup_snp(l_direct_record_ind);
137 FETCH calc_totals_for_sup_snp INTO p_total_gain_hire,
138 p_total_gain_transfer,
139 p_total_loss_term,
140 p_total_loss_transfer;
141 CLOSE calc_totals_for_sup_snp;
142
143 ELSE
144
145 /* Get WMV Change totals for supervisor from cursor */
146 OPEN calc_totals_for_sup(l_direct_record_ind);
147 FETCH calc_totals_for_sup INTO p_total_gain_hire,
148 p_total_gain_transfer,
149 p_total_loss_term,
150 p_total_loss_transfer;
151 CLOSE calc_totals_for_sup;
152
153 END IF;
154
155 END calc_sup_wcnt_chg;
156
157 /* Total Employee Or Contingent change events for a supervisor in a period */
158 /***************************************************************************/
159 PROCEDURE calc_sup_wcnt_chg(p_supervisor_id IN NUMBER,
160 p_from_date IN DATE,
161 p_to_date IN DATE,
162 p_period_type IN VARCHAR2,
163 p_comparison_type IN VARCHAR2,
164 p_total_type IN VARCHAR2,
165 p_wkth_wktyp_sk_fk IN VARCHAR2,
166 p_total_gain_hire OUT NOCOPY NUMBER,
167 p_total_gain_transfer OUT NOCOPY NUMBER,
168 p_total_loss_term OUT NOCOPY NUMBER,
169 p_total_loss_transfer OUT NOCOPY NUMBER) IS
170
171 CURSOR calc_totals_for_sup(v_direct_record_ind IN NUMBER) IS
172 SELECT
173 NVL(SUM(a.hire_hdc),0) tot_gain_hire
174 ,NVL(SUM(a.transfer_in_hdc),0) tot_gain_transfer
175 ,NVL(SUM(a.termination_hdc),0) tot_loss_term
176 ,NVL(SUM(a.transfer_out_hdc),0) tot_loss_transfer
177 FROM hri_mdp_sup_wcnt_chg_mv a
178 WHERE a.supervisor_person_id = p_supervisor_id
179 AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
180 AND a.direct_record_ind = v_direct_record_ind
181 AND a.effective_date BETWEEN p_from_date
182 AND p_to_date;
183
184 CURSOR calc_totals_for_sup_snp(v_direct_record_ind IN NUMBER) IS
185 SELECT /*+ INDEX(a) */
186 NVL(SUM(a.hire_hdc),0) tot_gain_hire
187 ,NVL(SUM(a.transfer_in_hdc),0) tot_gain_transfer
188 ,NVL(SUM(a.termination_hdc),0) tot_loss_term
189 ,NVL(SUM(a.transfer_out_hdc),0) tot_loss_transfer
190 FROM hri_mds_sup_wcnt_chg_mv a
191 WHERE a.supervisor_person_id = p_supervisor_id
192 AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
193 AND a.direct_record_ind = v_direct_record_ind
194 AND a.effective_date = p_to_date
195 AND a.period_type = p_period_type
196 AND a.comparison_type IN (p_comparison_type, 'CURRENT');
197
198 l_direct_record_ind NUMBER;
199 l_use_snapshot BOOLEAN;
200
201 BEGIN
202
203 /* Check whether a snapshot can be used */
204 /* Note a snapshot may be used to get the total even if the portlet query */
205 /* is not able to use snapshots */
206 l_use_snapshot := hri_oltp_pmv_util_snpsht.use_wcnt_chg_snpsht_for_mgr
207 (p_supervisor_id => p_supervisor_id,
208 p_effective_date => p_to_date);
209
210 /* Set record type indicator */
211 IF p_total_type = 'ROLLUP' THEN
212 l_direct_record_ind := 0;
213 ELSE
214 l_direct_record_ind := 1;
215 END IF;
216
217 /* Open the cursor corresponding to the snapshot flag */
218 IF l_use_snapshot THEN
219
220 /* Get WMV Change totals for supervisor from snapshot cursor */
221 OPEN calc_totals_for_sup_snp(l_direct_record_ind);
222 FETCH calc_totals_for_sup_snp INTO p_total_gain_hire,
223 p_total_gain_transfer,
224 p_total_loss_term,
225 p_total_loss_transfer;
226 CLOSE calc_totals_for_sup_snp;
227
228 ELSE
229
230 /* Get WMV Change totals for supervisor from cursor */
231 OPEN calc_totals_for_sup(l_direct_record_ind);
232 FETCH calc_totals_for_sup INTO p_total_gain_hire,
233 p_total_gain_transfer,
234 p_total_loss_term,
235 p_total_loss_transfer;
236 CLOSE calc_totals_for_sup;
237
238 END IF;
239
240 END calc_sup_wcnt_chg;
241
242 /* Total turnover events for a supervisor in a period */
243 /******************************************************/
244 PROCEDURE calc_sup_turnover(p_supervisor_id IN NUMBER,
245 p_from_date IN DATE,
246 p_to_date IN DATE,
247 p_period_type IN VARCHAR2,
248 p_comparison_type IN VARCHAR2,
249 p_total_type IN VARCHAR2,
250 p_wkth_wktyp_sk_fk IN VARCHAR2,
251 p_total_trn_vol OUT NOCOPY NUMBER,
252 p_total_trn_invol OUT NOCOPY NUMBER) IS
253
254 CURSOR calc_totals_for_sup(v_direct_record_ind IN NUMBER) IS
255 SELECT
256 NVL(SUM(a.sep_vol_hdc),0) total_trn_vol
257 ,NVL(SUM(a.sep_invol_hdc),0) total_trn_invol
258 FROM hri_mdp_sup_wcnt_chg_mv a
259 WHERE a.supervisor_person_id = p_supervisor_id
260 AND a.direct_record_ind = v_direct_record_ind
261 AND a.effective_date BETWEEN p_from_date AND p_to_date
262 AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk;
263
264 CURSOR calc_totals_for_sup_snp(v_direct_record_ind IN NUMBER) IS
265 SELECT /*+ INDEX(a) */
266 NVL(SUM(a.sep_vol_hdc),0) total_trn_vol
267 ,NVL(SUM(a.sep_invol_hdc),0) total_trn_invol
268 FROM hri_mds_sup_wcnt_chg_mv a
269 WHERE a.supervisor_person_id = p_supervisor_id
270 AND a.direct_record_ind = v_direct_record_ind
271 AND a.effective_date = p_to_date
272 AND a.period_type = p_period_type
273 AND a.comparison_type IN (p_comparison_type, 'CURRENT')
274 AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk;
275
276 l_direct_record_ind NUMBER;
277 l_use_snapshot BOOLEAN;
278
279 BEGIN
280
281 /* Check whether a snapshot can be used */
282 /* Note a snapshot may be used to get the total even if the portlet query */
283 /* is not able to use snapshots */
284 l_use_snapshot := hri_oltp_pmv_util_snpsht.use_wcnt_chg_snpsht_for_mgr
285 (p_supervisor_id => p_supervisor_id,
286 p_effective_date => p_to_date);
287
288 /* Set record type indicator */
289 IF p_total_type = 'ROLLUP' THEN
290 l_direct_record_ind := 0;
291 ELSE
292 l_direct_record_ind := 1;
293 END IF;
294
295 /* Open the cursor corresponding to the snapshot flag */
296 IF l_use_snapshot THEN
297
298 /* Get WMV Change totals for supervisor from cursor */
299 OPEN calc_totals_for_sup_snp(l_direct_record_ind);
300 FETCH calc_totals_for_sup_snp INTO p_total_trn_vol,
301 p_total_trn_invol;
302 CLOSE calc_totals_for_sup_snp;
303
304 ELSE
305
306 /* Get WMV Change totals for supervisor from cursor */
307 OPEN calc_totals_for_sup(l_direct_record_ind);
308 FETCH calc_totals_for_sup INTO p_total_trn_vol,
309 p_total_trn_invol;
310 CLOSE calc_totals_for_sup;
311
312 END IF;
313
314 END calc_sup_turnover;
315
316 /* Total terminations by supervisor and length of service */
317 /**********************************************************/
318 PROCEDURE calc_sup_term_low_pvt
319 (p_supervisor_id IN NUMBER,
320 p_from_date IN DATE,
321 p_to_date IN DATE,
322 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
323 p_total_term OUT NOCOPY NUMBER,
324 p_total_term_b1 OUT NOCOPY NUMBER,
325 p_total_term_b2 OUT NOCOPY NUMBER,
326 p_total_term_b3 OUT NOCOPY NUMBER,
327 p_total_term_b4 OUT NOCOPY NUMBER,
328 p_total_term_b5 OUT NOCOPY NUMBER) IS
329
330 TYPE term_csr_type IS REF CURSOR;
331
332 term_cv term_csr_type;
333 l_sql_stmt VARCHAR2(8000);
334 l_where_clause VARCHAR2(4000);
335 l_parameter_name VARCHAR2(100);
336
337 BEGIN
338
339 l_parameter_name := p_bind_tab.FIRST;
340
341 /* Loop through parameters that have been set */
342 WHILE l_parameter_name IS NOT NULL LOOP
343
344 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
345 l_parameter_name = 'GEOGRAPHY+AREA' OR
349 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
346 l_parameter_name = 'JOB+JOB_FAMILY' OR
347 l_parameter_name = 'JOB+JOB_FUNCTION' OR
348 l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
350 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X' OR
351 l_parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X') THEN
352
353 /* Dynamically set conditions for parameter */
354 l_where_clause := l_where_clause || g_rtn ||
355 'AND term.' ||
356 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
357 (l_parameter_name).fact_viewby_col ||
358 ' IN (' || p_bind_tab(l_parameter_name).sql_bind_string || ')';
359
360 END IF;
361
362 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
363
364 END LOOP;
365
366
367 l_sql_stmt :=
368 'SELECT
369 NVL(SUM(term.separation_hdc), 0) total_term
370 ,NVL(SUM(CASE WHEN pow.pow_band = 1
371 THEN term.separation_hdc
372 ELSE 0
373 END), 0) total_term_pow_band1
374 ,NVL(SUM(CASE WHEN pow.pow_band = 2
375 THEN term.separation_hdc
376 ELSE 0
377 END), 0) total_term_pow_band2
378 ,NVL(SUM(CASE WHEN pow.pow_band = 3
379 THEN term.separation_hdc
380 ELSE 0
381 END), 0) total_term_pow_band3
382 ,NVL(SUM(CASE WHEN pow.pow_band = 4
383 THEN term.separation_hdc
384 ELSE 0
385 END), 0) total_term_pow_band4
386 ,NVL(SUM(CASE WHEN pow.pow_band = 5
387 THEN term.separation_hdc
388 ELSE 0
389 END), 0) total_term_pow_band5
390 FROM
391 hri_mdp_sup_wcnt_term_asg_mv term
392 ,hri_dbi_cl_pow_all_band_v pow
393 WHERE term.supervisor_person_id = :1
394 AND term.effective_date BETWEEN :2 AND :3
395 AND term.pow_band_sk_fk = pow.id'
396 || l_where_clause;
397
398 OPEN term_cv FOR l_sql_stmt
399 USING
400 p_supervisor_id,
401 p_from_date,
402 p_to_date;
403 FETCH term_cv INTO
404 p_total_term,
405 p_total_term_b1,
406 p_total_term_b2,
407 p_total_term_b3,
408 p_total_term_b4,
409 p_total_term_b5;
410 CLOSE term_cv;
411
412 EXCEPTION WHEN OTHERS THEN
413
414 RETURN;
415
416 END calc_sup_term_low_pvt;
417
418 /* Total terminations by supervisor and performance band */
419 /*********************************************************/
420 PROCEDURE calc_sup_term_perf_pvt
421 (p_supervisor_id IN NUMBER,
422 p_from_date IN DATE,
423 p_to_date IN DATE,
424 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
425 p_total_term OUT NOCOPY NUMBER,
426 p_total_term_b1 OUT NOCOPY NUMBER,
427 p_total_term_b2 OUT NOCOPY NUMBER,
428 p_total_term_b3 OUT NOCOPY NUMBER,
429 p_total_term_na OUT NOCOPY NUMBER) IS
430
431 TYPE term_csr_type IS REF CURSOR;
432
433 term_cv term_csr_type;
434 l_sql_stmt VARCHAR2(8000);
435 l_where_clause VARCHAR2(4000);
436 l_parameter_name VARCHAR2(100);
437
438 BEGIN
439
440 l_parameter_name := p_bind_tab.FIRST;
441
442 /* Loop through parameters that have been set */
443 WHILE l_parameter_name IS NOT NULL LOOP
444
445 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
446 l_parameter_name = 'GEOGRAPHY+AREA' OR
447 l_parameter_name = 'JOB+JOB_FAMILY' OR
448 l_parameter_name = 'JOB+JOB_FUNCTION' OR
449 l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
450 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
451 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X' OR
452 l_parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X') THEN
453
454 /* Dynamically set conditions for parameter */
455 l_where_clause := l_where_clause || g_rtn ||
456 'AND term.' ||
457 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
458 (l_parameter_name).fact_viewby_col ||
459 ' IN (' || p_bind_tab(l_parameter_name).sql_bind_string || ')';
460
461 END IF;
462
463 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
464
465 END LOOP;
466
467 l_sql_stmt :=
468 'SELECT
469 NVL(SUM(separation_hdc), 0)
470 ,NVL(SUM(CASE WHEN perf_band = 1
471 THEN separation_hdc
472 ELSE 0
473 END), 0) total_term_perf_band1
474 ,NVL(SUM(CASE WHEN perf_band = 2
475 THEN separation_hdc
476 ELSE 0
477 END), 0) total_term_perf_band2
478 ,NVL(SUM(CASE WHEN perf_band = 3
479 THEN separation_hdc
480 ELSE 0
481 END), 0) total_term_perf_band3
482 ,NVL(SUM(CASE WHEN perf_band = -5
483 THEN separation_hdc
484 ELSE 0
485 END), 0) total_term_perf_bandna
486 FROM
487 hri_mdp_sup_wcnt_term_asg_mv term
488 WHERE term.supervisor_person_id = :1
489 AND term.effective_date BETWEEN :2 AND :3'
490 || l_where_clause;
491
492 OPEN term_cv FOR l_sql_stmt
493 USING
494 p_supervisor_id,
495 p_from_date,
496 p_to_date;
497 FETCH term_cv INTO
498 p_total_term,
499 p_total_term_b1,
500 p_total_term_b2,
501 p_total_term_b3,
502 p_total_term_na;
503 CLOSE term_cv;
504
505 EXCEPTION WHEN OTHERS THEN
506
507 RETURN;
508
509 END calc_sup_term_perf_pvt;
510
511 /* Total terminations by supervisor pivot */
515 p_from_date IN DATE,
512 /******************************************/
513 PROCEDURE calc_sup_term_pvt
514 (p_supervisor_id IN NUMBER,
516 p_to_date IN DATE,
517 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
518 p_total_term_vol OUT NOCOPY NUMBER,
519 p_total_term_invol OUT NOCOPY NUMBER,
520 p_total_term OUT NOCOPY NUMBER) IS
521
522 TYPE term_csr_type IS REF CURSOR;
523
524 term_cv term_csr_type;
525 l_sql_stmt VARCHAR2(8000);
526 l_where_clause VARCHAR2(4000);
527 l_parameter_name VARCHAR2(100);
528
529 BEGIN
530
531 l_parameter_name := p_bind_tab.FIRST;
532
533 /* Loop through parameters that have been set */
534 WHILE l_parameter_name IS NOT NULL LOOP
535
536 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
537 l_parameter_name = 'GEOGRAPHY+AREA' OR
538 l_parameter_name = 'JOB+JOB_FAMILY' OR
539 l_parameter_name = 'JOB+JOB_FUNCTION' OR
540 l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
541 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
542 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X' OR
543 l_parameter_name = 'HRI_REASON+HRI_RSN_SEP_X' OR
544 l_parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X') THEN
545
546 /* Dynamically set conditions for parameter */
547 l_where_clause := l_where_clause || g_rtn ||
548 'AND term.' ||
549 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
550 (l_parameter_name).fact_viewby_col ||
551 ' IN (' || p_bind_tab(l_parameter_name).sql_bind_string || ')';
552
553 END IF;
554
555 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
556
557 END LOOP;
558
559 l_sql_stmt :=
560 'SELECT
561 NVL(SUM(CASE WHEN separation_category = ''SEP_INV''
562 THEN term.separation_hdc
563 ELSE 0
564 END), 0)
565 ,NVL(SUM(CASE WHEN separation_category = ''SEP_VOL''
566 THEN term.separation_hdc
567 ELSE 0
568 END), 0)
569 ,NVL(SUM(term.separation_hdc), 0)
570 FROM
571 hri_mdp_sup_wcnt_term_asg_mv term
572 WHERE term.supervisor_person_id = :1
573 AND term.effective_date BETWEEN :2 AND :3'
574 || l_where_clause;
575
576 OPEN term_cv FOR l_sql_stmt USING p_supervisor_id, p_from_date, p_to_date;
577 FETCH term_cv INTO p_total_term_invol, p_total_term_vol, p_total_term;
578 CLOSE term_cv;
579
580 EXCEPTION WHEN OTHERS THEN
581
582 RETURN;
583
584 END calc_sup_term_pvt;
585
586 /******************************************************************************/
587 /* Returns worker Termination Date */
588 /******************************************************************************/
589 FUNCTION get_term_date (p_assignment_id IN NUMBER
590 ,p_person_id IN NUMBER)
591
592 RETURN DATE IS
593 l_end_place_date DATE;
594 BEGIN
595
596 SELECT effective_change_end_date INTO l_end_place_date
597 FROM hri_mb_asgn_events_ct asgn
598 WHERE
599 asgn.person_id = p_person_id
600 AND asgn.assignment_id = p_assignment_id
601 and asgn.worker_term_nxt_ind = 1 ;
602
603 RETURN l_end_place_date ;
604
605 EXCEPTION WHEN OTHERS THEN
606 RETURN null;
607
608 END get_term_date;
609
610
611 END hri_bpl_dbi_calc_period;
[Home] [Help]