1 PACKAGE BODY hri_oltp_pmv_util_snpsht AS
2 /* $Header: hrioputs.pkb 120.1 2005/09/20 05:12 jrstewar noship $ */
3
4 /* Returns whether or not the given manager is classified as */
5 /* senior on the given date */
6 FUNCTION is_manager_senior(p_supervisor_id IN NUMBER,
7 p_effective_date IN DATE)
8 RETURN BOOLEAN IS
9
10 l_count PLS_INTEGER;
11
12 CURSOR senior_mgr_csr IS
13 SELECT 1
14 FROM hri_cl_per_snrmgr_ct
15 WHERE id = p_supervisor_id
16 AND p_effective_date BETWEEN start_date AND end_date;
17
18 BEGIN
19
20 /* Get a rowcount from the senior manager table */
21 OPEN senior_mgr_csr;
22 FETCH senior_mgr_csr INTO l_count;
23 CLOSE senior_mgr_csr;
24
25 IF (l_count > 0) THEN
26 RETURN TRUE;
27 END IF;
28
29 RETURN FALSE;
30
31 END is_manager_senior;
32
33 /* Returns whether to use a snapshot for the given manager and date */
34 FUNCTION use_wrkfc_snpsht_for_mgr(p_supervisor_id IN NUMBER,
35 p_effective_date IN DATE)
36 RETURN BOOLEAN IS
37
38 l_count PLS_INTEGER;
39
40 CURSOR wrkfc_snapshot_csr IS
41 SELECT 1
42 FROM hri_cal_snpsht_wrkfc
43 WHERE snapshot_date = p_effective_date;
44
45 BEGIN
46
47 /* If the manager is classified as senior, check whether a snapshot is */
48 /* available for the given date */
49 IF (is_manager_senior(p_supervisor_id => p_supervisor_id,
50 p_effective_date => p_effective_date)) THEN
51 OPEN wrkfc_snapshot_csr;
52 FETCH wrkfc_snapshot_csr INTO l_count;
53 CLOSE wrkfc_snapshot_csr;
54
55 IF (l_count > 0) THEN
56 RETURN TRUE;
57 END IF;
58 END IF;
59
60 RETURN FALSE;
61
62 END use_wrkfc_snpsht_for_mgr;
63
64 /* Returns whether to use a snapshot for the given manager and date */
65 FUNCTION use_wcnt_chg_snpsht_for_mgr(p_supervisor_id IN NUMBER,
66 p_effective_date IN DATE)
67 RETURN BOOLEAN IS
68
69 l_count PLS_INTEGER;
70
71 CURSOR wcnt_chg_snapshot_csr IS
72 SELECT 1
73 FROM hri_cal_snpsht_wcnt_chg
74 WHERE snapshot_date = p_effective_date;
75
76 BEGIN
77
78 /* If the manager is classified as senior, check whether a snapshot is */
79 /* available for the given date */
80 IF (is_manager_senior(p_supervisor_id => p_supervisor_id,
81 p_effective_date => p_effective_date)) THEN
82 OPEN wcnt_chg_snapshot_csr;
83 FETCH wcnt_chg_snapshot_csr INTO l_count;
84 CLOSE wcnt_chg_snapshot_csr;
85
86 IF (l_count > 0) THEN
87 RETURN TRUE;
88 END IF;
89 END IF;
90
91 RETURN FALSE;
92
93 END use_wcnt_chg_snpsht_for_mgr;
94
95 /* Returns whether to use a snapshot for the given manager and date */
96 FUNCTION use_absnc_snpsht_for_mgr(p_supervisor_id IN NUMBER,
97 p_effective_date IN DATE)
98 RETURN BOOLEAN IS
99
100 l_count PLS_INTEGER;
101
102
103 BEGIN
104
105 /* If the manager is classified as senior, check whether a snapshot is */
106 /* available for the given date */
107
108 /* Absence doesn't support Snap Shot MV's 70C baseline */
109
110 RETURN FALSE;
111
112 END use_absnc_snpsht_for_mgr;
113
114 END hri_oltp_pmv_util_snpsht;
[Home] [Help]