1 PACKAGE BODY hri_oltp_pmv_util_pkg AS
2 /* $Header: hrioputl.pkb 120.5 2005/09/22 07:12 jrstewar noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 /* Returns SQL fragment for applying security to DBI reports */
8 FUNCTION get_security_clause(p_security_type VARCHAR2) -- [MGR, ORG]
9 RETURN VARCHAR2 IS
10
11 l_security_clause VARCHAR2(2000);
12
13 BEGIN
14
15 IF (p_security_type = 'MGR') THEN
16 l_security_clause :=
17 'AND EXISTS
18 (SELECT /*+ NO_UNNEST INDEX(sup hri_cs_suph_n4) */ null
19 FROM hri_cs_suph sup
20 WHERE sup.sup_person_id IN (hri_bpl_security.get_apps_signin_person_id,&BIS_SELECTED_TOP_MANAGER)
21 AND sup.sub_person_id = &HRI_PERSON+HRI_PER_USRDR_H
22 AND sup.sub_invalid_flag_code = ''N''
23 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.effective_start_date
24 AND sup.effective_end_date)';
25
26 END IF;
27
28 RETURN l_security_clause;
29
30 END get_security_clause;
31
32 /* Calculates the annualization factor */
33 FUNCTION calc_anl_factor(p_period_type IN VARCHAR2)
34 RETURN NUMBER IS
35
36 l_anl_factor NUMBER;
37
38 BEGIN
39
40 IF (p_period_type = 'FII_ROLLING_YEAR') THEN
41 l_anl_factor := 1;
42 ELSIF (p_period_type = 'FII_ROLLING_QTR') THEN
43 l_anl_factor := 365 / 90;
44 ELSIF (p_period_type = 'FII_ROLLING_MONTH') THEN
45 l_anl_factor := 365 / 30;
46 ELSIF (p_period_type = 'FII_ROLLING_WEEK') THEN
47 l_anl_factor := 365 / 7;
48 ELSE
49 l_anl_factor := (365/30);
50 END IF;
51
52 RETURN l_anl_factor;
53
54 END calc_anl_factor;
55
56 -- Sets default sort order
57 FUNCTION set_default_order_by(p_order_by_clause IN VARCHAR2)
58 RETURN VARCHAR2 IS
59
60 l_return_value VARCHAR2(1000);
61
62 BEGIN
63
64 IF (p_order_by_clause IS NOT NULL) THEN
65 l_return_value := REPLACE(p_order_by_clause, 'VIEWBY', 'HRI_P_ORDER_BY_1');
66 ELSE
67 l_return_value := 'order_by';
68 END IF;
69
70 RETURN l_return_value;
71
72 END set_default_order_by;
73
74 -- adds a filter on the viewby for "small" view bys
75 FUNCTION set_viewby_filter
76 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
77 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
78 p_view_by_alias IN VARCHAR2)
79 RETURN VARCHAR2 IS
80
81 l_viewby_filter VARCHAR2(1000);
82
83 BEGIN
84
85 /* Trap exceptions for no data found */
86 BEGIN
87
88 l_viewby_filter :=
89 'AND ' || p_view_by_alias || '.id IN (' ||
90 p_bind_tab(p_parameter_rec.view_by).pmv_bind_string || ')' || g_rtn;
91
92 /* When no parameter value is set no filter is needed */
93 EXCEPTION WHEN OTHERS THEN
94 null;
95
96 END;
97
98 /* Special case - filters correct set of LOW bands */
99 IF (p_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
100
101 /* If person type filter is applied use it to filter the bands */
102 IF (p_parameter_rec.wkth_wktyp_sk_fk IS NOT NULL) THEN
103
104 l_viewby_filter := l_viewby_filter ||
105 'AND ' || p_view_by_alias || '.wkth_wktyp_sk_fk = ''' ||
106 p_parameter_rec.wkth_wktyp_sk_fk || '''' || g_rtn;
107
108 END IF;
109
110 END IF;
111
112 RETURN l_viewby_filter;
113
114 END set_viewby_filter;
115 --
116 -- -------------------------------------------------------------------------
117 -- This procedure sets the parameters taking into consideration whethere the
118 -- PMV is being generated in PMV mode or SQL mode. The SQL mode is used for
119 -- debugging.
120 -- -------------------------------------------------------------------------
121 --
122 PROCEDURE substitute_bind_values
123 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
124 p_bind_format IN VARCHAR2,
125 p_sql IN OUT NOCOPY VARCHAR2)
126 IS
127 --
128 l_index VARCHAR2(100);
129 --
130 BEGIN
131 --
132 IF p_bind_format = 'SQL' THEN
133 --
134 -- Loop through all the parameters and set them
135 --
136 l_index := p_bind_tab.FIRST;
137 --
138 WHILE l_index IS NOT NULL LOOP
139 --
140 p_sql := replace(p_sql,
141 p_bind_tab(l_index).pmv_bind_string,
142 p_bind_tab(l_index).sql_bind_string);
143 --
144 l_index := p_bind_tab.NEXT(l_index);
145 --
146 END LOOP;
147 --
148 END IF;
149 --
150 END substitute_bind_values;
151
152 --
153 -- -------------------------------------------------------------------------
154 -- This function checks Profile Option HRI:DBI Link To Transaction System
155 -- Link to HR Employee Directory
156 --
157 -- -------------------------------------------------------------------------
158 --
159 FUNCTION chk_emp_dir_lnk(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE
160 ,p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE)
161
162 RETURN NUMBER IS
163
164 l_return_chk NUMBER;
165 l_hr_dir_chk NUMBER;
166 l_profile_vl VARCHAR2(1000);
167
168 BEGIN
169 /* Checks Employee Directory has been populated */
170 SELECT count(*) INTO l_hr_dir_chk
171 FROM per_empdir_people
172 WHERE orig_system = 'PER'
173 AND rownum <= 1;
174
175 fnd_profile.get('HRI_DBI_LNK_TRNS_SYS',l_profile_vl);
176
177 /* Check the Profile HRI:DBI Link To Transaction System */
178 IF (l_profile_vl = 'HR_EMP_DIR_ONLY') AND (l_hr_dir_chk = 1) THEN
179 --
180 l_return_chk := 1;
181 --
182 ELSIF (l_profile_vl = 'NO_LNK' ) THEN
183 --
184 l_return_chk := 0;
185 ELSE
186 --
187 l_return_chk := 0;
188 --
189 END IF;
190
191 RETURN l_return_chk ;
192
193 END chk_emp_dir_lnk;
194
195 -- Gets sql fragment for change %
196 FUNCTION get_change_percent_sql(p_previous_col IN VARCHAR2,
197 p_current_col IN VARCHAR2)
198 RETURN VARCHAR2 IS
199
200 BEGIN
201
202 --
203 -- --------------------------------------------------------------------
204 -- The change percent calculation is:
205 --
206 -- 100 * (CURRENT - PREVIOUS) / PREVIOUS
207 --
208 -- If PREVIOUS is 0 NULL is returned to avoid the divide by 0 error,
209 -- which is rendered as N/a.
210 -- --------------------------------------------------------------------
211 --
212 RETURN
213 'DECODE(' || p_previous_col || ',
214 0, to_number(null),
215 100 * (' || p_current_col || ' - ' || p_previous_col || ') / ' || p_previous_col || ')';
216
217 EXCEPTION WHEN OTHERS THEN
218
219 RETURN 'to_number(NULL)';
220
221 END get_change_percent_sql;
222
223 END hri_oltp_pmv_util_pkg;
[Home] [Help]