[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_ABS_WMV_PVT
Source
1 PACKAGE BODY HRI_OLTP_PMV_ABS_WMV_PVT AS
2 /* $Header: hriopabswmvpvt.pkb 120.8 2005/11/17 08:51 jrstewar noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 --
8 --****************************************************************************
9 --* AK SQL For Absence Summary by Manager *
10 --* AK Region : HRI_P_ABS_WMV_PVT *
11 --****************************************************************************
12 --
13 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
14 ,x_custom_sql OUT NOCOPY VARCHAR2
15 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
16 IS
17
18 l_SQLText VARCHAR2(32000);
19 l_security_clause VARCHAR2(4000);
20 l_custom_rec BIS_QUERY_ATTRIBUTES ;
21
22 /* Dynamic SQL Controls */
23 l_abs_fact_params hri_bpl_fact_abs_sql.abs_fact_param_type;
24 l_abs_fact_sql VARCHAR2(10000);
25 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
26 l_wrkfc_fact_sql VARCHAR2(10000);
27 l_direct_reports_string VARCHAR2(100);
28 l_dynsql_order_by VARCHAR2(100);
29
30 l_parameter_name VARCHAR2(100);
31 l_dynmc_drtn_curr VARCHAR2(100) DEFAULT 'curr_abs_drtn_days';
32 l_dynmc_drtn_comp VARCHAR2(100) DEFAULT 'comp_abs_drtn_days';
33 l_drill_mgr_sup VARCHAR2(1000);
34 l_drill_to_detail VARCHAR2(1000);
35 l_drill_abs_detail VARCHAR2(1000);
36
37 /* Parameter values */
38 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
39 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
40 l_debug_header VARCHAR(550);
41
42 /* Total Variables */
43 l_curr_tot_abs_drtn_days NUMBER;
44 l_curr_tot_abs_drtn_hrs NUMBER;
45 l_curr_tot_abs_drtn NUMBER;
46 l_curr_tot_abs_in_period NUMBER;
47 l_curr_tot_abs_ntfctn_period NUMBER;
48
49 l_comp_tot_abs_drtn_days NUMBER;
50 l_comp_tot_abs_drtn_hrs NUMBER;
51 l_comp_tot_abs_drtn NUMBER;
52 l_comp_tot_abs_in_period NUMBER;
53 l_comp_tot_abs_ntfctn_period NUMBER;
54
55 BEGIN
56 /* Initialize out parameters */
57 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
58 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
59
60 /* Get security clause for Manager based security */
61 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
62
63 /* Get common parameter values */
64 hri_oltp_pmv_util_param.get_parameters_from_table
65 (p_page_parameter_tbl => p_page_parameter_tbl,
66 p_parameter_rec => l_parameter_rec,
67 p_bind_tab => l_bind_tab);
68
69 /* Get direct reports string */
70 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
71
72 /* Set the order by */
73 l_dynsql_order_by := hri_oltp_pmv_util_pkg.set_default_order_by
74 (p_order_by_clause => l_parameter_rec.order_by);
75
76 /* Drill URL's for Manager and Direct Reports */
77 l_drill_mgr_sup :='pFunctionName=HRI_P_ABS_WMV_PVT&' ||
78 'VIEW_BY=VIEW_BY_NAME&' ||
79 'VIEW_BY_NAME=VIEW_BY_ID&' ||
80 'pParamIds=Y';
81
82 l_drill_to_detail :='pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
83 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
84 'VIEW_BY_NAME=VIEW_BY_ID&' ||
85 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
86 'pParamIds=Y';
87
88 l_drill_abs_detail :='pFunctionName=HRI_P_ABS_SUP_DTL&' ||
89 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
90 'VIEW_BY_NAME=VIEW_BY_ID&' ||
91 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
92 'pParamIds=Y';
93
94 /* Get current period absence totals for supervisor from cursor */
95 hri_bpl_dbi_calc_period.calc_sup_absence
96 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
97 p_from_date => l_parameter_rec.time_curr_start_date,
98 p_to_date => l_parameter_rec.time_curr_end_date,
99 p_period_type => l_parameter_rec.page_period_type,
100 p_comparison_type => l_parameter_rec.time_comparison_type,
101 p_total_type => 'ROLLUP',
102 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
103 p_total_abs_drtn_days => l_curr_tot_abs_drtn_days,
104 p_total_abs_drtn_hrs => l_curr_tot_abs_drtn_hrs,
105 p_total_abs_in_period => l_curr_tot_abs_in_period,
106 p_total_abs_ntfctn_period => l_curr_tot_abs_ntfctn_period);
107
108 /* Get previous period turnover totals for supervisor from cursor */
109 hri_bpl_dbi_calc_period.calc_sup_absence
110 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
111 p_from_date => l_parameter_rec.time_comp_start_date,
112 p_to_date => l_parameter_rec.time_comp_end_date,
113 p_period_type => l_parameter_rec.page_period_type,
114 p_comparison_type => l_parameter_rec.time_comparison_type,
115 p_total_type => 'ROLLUP',
116 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
117 p_total_abs_drtn_days => l_comp_tot_abs_drtn_days,
118 p_total_abs_drtn_hrs => l_comp_tot_abs_drtn_hrs,
119 p_total_abs_in_period => l_comp_tot_abs_in_period,
120 p_total_abs_ntfctn_period => l_comp_tot_abs_ntfctn_period);
121
122 /* formulate the dynmaic column selection based on Absence Duration
123 unit of measure paramter selection Default Days */
124
125 IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
126 l_dynmc_drtn_curr := 'curr_abs_drtn_days';
127 l_dynmc_drtn_comp := 'comp_abs_drtn_days';
128 l_abs_fact_params.include_abs_drtn_days := 'Y';
129 l_curr_tot_abs_drtn:= l_curr_tot_abs_drtn_days;
130 l_comp_tot_abs_drtn:= l_comp_tot_abs_drtn_days;
131 ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
132 l_dynmc_drtn_curr := 'curr_abs_drtn_hrs';
133 l_dynmc_drtn_comp := 'comp_abs_drtn_hrs';
134 l_abs_fact_params.include_abs_drtn_hrs := 'Y';
135 l_curr_tot_abs_drtn:= l_curr_tot_abs_drtn_hrs;
136 l_comp_tot_abs_drtn:= l_comp_tot_abs_drtn_hrs;
137 ELSE -- functional decision (JC) default to days
138 l_dynmc_drtn_curr := 'curr_abs_drtn_days';
139 l_dynmc_drtn_comp := 'comp_abs_drtn_days';
140 l_abs_fact_params.include_abs_drtn_days := 'Y';
141 l_curr_tot_abs_drtn:= l_curr_tot_abs_drtn_days;
142 l_comp_tot_abs_drtn:= l_comp_tot_abs_drtn_hrs;
143 END IF;
144
145 /* Get SQL for absence fact */
146 l_abs_fact_params.bind_format := 'PMV';
147
148 l_abs_fact_params.include_abs_in_period := 'Y';
149 l_abs_fact_params.include_abs_ntfctn_period := 'Y';
150 l_abs_fact_params.include_comp := 'Y';
151 l_abs_fact_params.kpi_mode := 'N';
152 l_abs_fact_sql := hri_bpl_fact_abs_sql.get_sql
153 (p_parameter_rec => l_parameter_rec,
154 p_bind_tab => l_bind_tab,
155 p_abs_params => l_abs_fact_params,
156 p_calling_module => 'HRI_P_ABS_WMV_PVT');
157
158 /* Get SQL for workforce fact */
159 l_wrkfc_fact_params.bind_format := 'PMV';
160 l_wrkfc_fact_params.include_start := 'N';
161 l_wrkfc_fact_params.include_hdc := 'Y';
162 l_wrkfc_fact_params.include_sal := 'N';
163 l_wrkfc_fact_params.include_low := 'N';
164 l_wrkfc_fact_params.include_comp := 'N';
165 l_wrkfc_fact_params.kpi_mode := 'N';
166 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
167 (p_parameter_rec => l_parameter_rec,
168 p_bind_tab => l_bind_tab,
169 p_wrkfc_params => l_wrkfc_fact_params,
170 p_calling_module => 'HRI_P_ABS_WMV_PVT');
171
172 l_SQLText :=
173 'SELECT -- Absence Summary by Manager Status
174 babs.vby_id VIEWBYID
175 ,babs.value VIEWBY '|| g_rtn
176 /* Workforce */ || g_rtn ||
177 ',NVL(curr_hdc_end, 0 ) HRI_P_MEASURE1 '|| g_rtn
178 /* Absence */ || g_rtn ||'
179 ,NVL(babs.curr_abs_in_period,0) HRI_P_MEASURE2
180 ,NVL(babs.comp_abs_in_period,0) HRI_P_MEASURE3 '|| g_rtn
181 /* Total Notification */ || g_rtn ||'
182 ,NVL(babs.curr_abs_ntfctn_period,0) HRI_P_MEASURE4
183 ,NVL(babs.comp_abs_ntfctn_period,0) HRI_P_MEASURE5 '|| g_rtn
184 /* Average Notification */ || g_rtn ||'
185 ,NVL(curr_abs_avg_ntfctn_period,0) HRI_P_MEASURE6
186 ,NVL(comp_abs_avg_ntfctn_period,0) HRI_P_MEASURE7'|| g_rtn
187 /* Change - Average Notification */ || g_rtn ||'
188 ,'|| hri_oltp_pmv_util_pkg.get_change_percent_sql
189 (p_previous_col => 'comp_abs_avg_ntfctn_period',
190 p_current_col => 'curr_abs_avg_ntfctn_period') || '
191 HRI_P_MEASURE6_MP'|| g_rtn
192 /* Total Absence Duration */ || g_rtn ||'
193 ,NVL(babs.curr_abs_drtn,0) HRI_P_MEASURE8
194 ,NVL(babs.comp_abs_drtn,0) HRI_P_MEASURE9'|| g_rtn
195 /* Change - Total Absence Duration */ || g_rtn ||'
196 ,'|| hri_oltp_pmv_util_pkg.get_change_percent_sql
197 (p_previous_col => 'babs.comp_abs_drtn',
198 p_current_col => 'babs.curr_abs_drtn') || '
199 HRI_P_MEASURE8_MP'|| g_rtn
200 /* Average Absence Duration */ || g_rtn ||'
201 ,DECODE(babs.curr_abs_in_period,0,to_number(NULL)
202 ,(babs.curr_abs_drtn / babs.curr_abs_in_period)
203 ) HRI_P_MEASURE10
204 ,DECODE(babs.comp_abs_in_period,0,to_number(NULL)
205 ,(babs.curr_abs_drtn / babs.comp_abs_in_period)
206 ) HRI_P_MEASURE11 '|| g_rtn
207 /* Total Workforce */ || g_rtn ||'
208 ,NVL(tot_curr_hdc_end,0) HRI_P_GRAND_TOTAL1 '|| g_rtn
209 /* Total Absence */ || g_rtn ||'
210 ,NVL(babs.curr_tot_abs_in_period,0) HRI_P_GRAND_TOTAL2
211 ,NVL(babs.comp_tot_abs_in_period,0) HRI_P_GRAND_TOTAL3 '|| g_rtn
212 /* Total Notification */ || g_rtn ||'
213 ,NVL(babs.curr_tot_abs_ntfctn_period,0) HRI_P_GRAND_TOTAL4
214 ,NVL(babs.comp_tot_abs_ntfctn_period,0) HRI_P_GRAND_TOTAL5 '|| g_rtn
215 /* Total Average Notification */ || g_rtn ||'
216 ,NVL(babs.curr_tot_avg_abs_ntfctn_period,0)
217 HRI_P_GRAND_TOTAL6
218 ,NVL(babs.comp_tot_avg_abs_ntfctn_period,0)
219 HRI_P_GRAND_TOTAL7'|| g_rtn
220 /* Change Total - Average Notification */ || g_rtn ||'
221 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
222 (p_previous_col => 'babs.comp_tot_avg_abs_ntfctn_period',
223 p_current_col => 'babs.curr_tot_avg_abs_ntfctn_period') || '
224 HRI_P_GRAND_TOTAL6_MP'|| g_rtn
225 /* Total Absence Duration */ || g_rtn ||'
226 ,NVL(babs.curr_tot_abs_drtn,0) HRI_P_GRAND_TOTAL8
227 ,NVL(babs.comp_tot_abs_drtn,0) HRI_P_GRAND_TOTAL9'|| g_rtn
228 /* Change Total - Total Absence Duration */ || g_rtn ||'
229 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
230 (p_previous_col => 'babs.comp_tot_abs_drtn',
231 p_current_col => 'babs.curr_tot_abs_drtn') || '
232 HRI_P_GRAND_TOTAL8_MP'|| g_rtn
233 /* Total Average Absence Duration */ || g_rtn ||'
234 ,DECODE(babs.curr_tot_abs_in_period,0,to_number(NULL)
235 ,(babs.curr_tot_abs_drtn / babs.curr_tot_abs_in_period)
236 ) HRI_P_GRAND_TOTAL10
237 ,DECODE(babs.comp_tot_abs_in_period,0,to_number(NULL)
238 ,(babs.comp_tot_abs_drtn / babs.comp_tot_abs_in_period)
239 ) HRI_P_GRAND_TOTAL11 '|| g_rtn
240 /* Order by person name default sort order */ || g_rtn ||'
241 ,babs.order_by HRI_P_ORDER_BY_1 ' || g_rtn
242 /* Whether the row is a supervisor rollup row */ || g_rtn ||'
243 ,DECODE(babs.direct_ind , 0, '''', ''N'') HRI_P_SUPH_RO_CA '|| g_rtn
244 /* Drill URLs */ || g_rtn ||'
245 ,DECODE(babs.direct_ind,0, ''' || l_drill_mgr_sup || '''
246 ,1, ''' || l_drill_to_detail || '''
247 ,'''') HRI_P_DRILL_URL1
248 ,DECODE(babs.direct_ind,0, ''''
249 ,1, ''' || l_drill_abs_detail || '''
250 ,'''') HRI_P_DRILL_URL2
251 FROM
252 (
253 SELECT
254 /* Base Measures */
255 vby.id vby_id
256 ,DECODE(wfact.direct_ind,
257 1, ''' || l_direct_reports_string || ''',
258 vby.value) value
259 ,to_char(NVL(wfact.direct_ind, 0)) || vby.order_by
260 order_by
261 ,wfact.direct_ind direct_ind
262 ,NVL(wfact.curr_hdc_end,0) curr_hdc_end
263 ,NVL(afact.'||l_dynmc_drtn_curr ||',0) curr_abs_drtn
264 ,NVL(afact.curr_abs_in_period,0) curr_abs_in_period
265 ,NVL(afact.'||l_dynmc_drtn_comp ||',0) comp_abs_drtn
266 ,NVL(afact.comp_abs_in_period,0) comp_abs_in_period
267 ,NVL(afact.curr_abs_ntfctn_period,0) curr_abs_ntfctn_period
268 ,NVL(afact.comp_abs_ntfctn_period,0) comp_abs_ntfctn_period
269 ,DECODE(afact.curr_abs_ntfctn_period,0,to_number(NULL)
270 ,DECODE(afact.curr_abs_in_period,0,to_number(NULL)
271 ,(afact.curr_abs_ntfctn_period / afact.curr_abs_in_period)
272 )
273 ) curr_abs_avg_ntfctn_period
274 ,DECODE(afact.comp_abs_ntfctn_period,0,to_number(NULL)
275 ,DECODE(afact.curr_abs_in_period,0,to_number(NULL)
276 ,(afact.comp_abs_ntfctn_period / afact.comp_abs_in_period)
277 )
278 ) comp_abs_avg_ntfctn_period
279 ,SUM(wfact.curr_hdc_end) OVER() tot_curr_hdc_end
280 ,:CURR_TOT_ABS_DRTN curr_tot_abs_drtn
281 ,:CURR_TOT_ABS_IN_PERIOD curr_tot_abs_in_period
282 ,:COMP_TOT_ABS_DRTN comp_tot_abs_drtn
283 ,:COMP_TOT_ABS_IN_PERIOD comp_tot_abs_in_period
284 ,:CURR_TOT_ABS_NTFCTN_PERIOD curr_tot_abs_ntfctn_period
285 ,:COMP_TOT_ABS_NTFCTN_PERIOD comp_tot_abs_ntfctn_period
286 ,DECODE(:CURR_TOT_ABS_NTFCTN_PERIOD,0,to_number(NULL)
287 ,DECODE(:CURR_TOT_ABS_IN_PERIOD ,0,to_number(NULL)
288 ,(:CURR_TOT_ABS_NTFCTN_PERIOD / :CURR_TOT_ABS_IN_PERIOD )
289 )
290 ) curr_tot_avg_abs_ntfctn_period
291 ,DECODE(:COMP_TOT_ABS_NTFCTN_PERIOD,0,to_number(NULL)
292 ,DECODE(:COMP_TOT_ABS_IN_PERIOD,0,to_number(NULL)
293 ,(:COMP_TOT_ABS_NTFCTN_PERIOD / :COMP_TOT_ABS_IN_PERIOD)
294 )
295 ) comp_tot_avg_abs_ntfctn_period
296 FROM
297 hri_dbi_cl_per_n_v vby
298 ,('|| l_abs_fact_sql ||') afact
299 ,('|| l_wrkfc_fact_sql ||') wfact
300 WHERE
301 vby.id = wfact.vby_id
302 AND afact.vby_id (+) = wfact.vby_id
303 AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date
304 ' || l_security_clause || '
305 ) babs
306 ORDER BY babs.direct_ind,' || l_dynsql_order_by;
307
308 x_custom_sql := l_SQLText ;
309
310 l_custom_rec.attribute_name := ':CURR_TOT_ABS_DRTN';
311 l_custom_rec.attribute_value := l_curr_tot_abs_drtn;
312 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
313 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
314 x_custom_output.extend;
315 x_custom_output(1) := l_custom_rec;
316
317 l_custom_rec.attribute_name := ':CURR_TOT_ABS_IN_PERIOD';
318 l_custom_rec.attribute_value := l_curr_tot_abs_in_period;
319 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
320 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
321 x_custom_output.extend;
322 x_custom_output(2) := l_custom_rec;
323
324 l_custom_rec.attribute_name := ':CURR_TOT_ABS_NTFCTN_PERIOD';
325 l_custom_rec.attribute_value := l_curr_tot_abs_ntfctn_period;
326 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
327 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
328 x_custom_output.extend;
329 x_custom_output(3) := l_custom_rec;
330
331 l_custom_rec.attribute_name := ':COMP_TOT_ABS_DRTN';
332 l_custom_rec.attribute_value := l_comp_tot_abs_drtn;
333 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
334 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
335 x_custom_output.extend;
336 x_custom_output(4) := l_custom_rec;
337
338 l_custom_rec.attribute_name := ':COMP_TOT_ABS_IN_PERIOD';
339 l_custom_rec.attribute_value := l_comp_tot_abs_in_period;
340 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
341 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
342 x_custom_output.extend;
343 x_custom_output(5) := l_custom_rec;
344
345 l_custom_rec.attribute_name := ':COMP_TOT_ABS_NTFCTN_PERIOD';
346 l_custom_rec.attribute_value := l_comp_tot_abs_ntfctn_period;
347 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
348 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
349 x_custom_output.extend;
350 x_custom_output(6) := l_custom_rec;
351
352 END get_sql;
353 --
354 --****************************************************************************
355 --* AK SQL For Absence (Employee) KPI's *
356 --* AK Region : HRI_K_ABS_WMV *
357 --****************************************************************************
358 --
359 PROCEDURE GET_SQL_KPI(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
360 ,x_custom_sql OUT NOCOPY VARCHAR2
361 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
362 IS
363
364 l_SQLText VARCHAR2(32000);
365 l_security_clause VARCHAR2(4000);
366 l_custom_rec BIS_QUERY_ATTRIBUTES ;
367
368 /* Dynamic SQL Controls */
369 l_abs_fact_params hri_bpl_fact_abs_sql.abs_fact_param_type;
370 l_abs_fact_sql VARCHAR2(10000);
371 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
372 l_wrkfc_fact_sql VARCHAR2(10000);
373 l_direct_reports_string VARCHAR2(100);
374 l_dynsql_order_by VARCHAR2(100);
375
376 l_parameter_name VARCHAR2(100);
377 l_dynmc_drtn_curr VARCHAR2(100) DEFAULT 'curr_abs_drtn_days';
378 l_dynmc_drtn_comp VARCHAR2(100) DEFAULT 'comp_abs_drtn_days';
379 l_dynmc_hdc_curr VARCHAR2(100);
380 l_dynmc_hdc_comp VARCHAR2(100);
381 l_dynmc_tot_hdc_curr VARCHAR2(100);
382 l_dynmc_tot_hdc_comp VARCHAR2(100);
383
384 /* Parameter values */
385 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
386 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
387 l_debug_header VARCHAR(550);
388
389
390 BEGIN
391 /* Initialize out parameters */
392 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
393 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
394
395 /* Get security clause for Manager based security */
396 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
397
398 /* Get common parameter values */
399 hri_oltp_pmv_util_param.get_parameters_from_table
400 (p_page_parameter_tbl => p_page_parameter_tbl,
401 p_parameter_rec => l_parameter_rec,
402 p_bind_tab => l_bind_tab);
403
404 /* Get direct reports string */
405 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
406
407 /* formulate the dynmaic column selection based on Absence Duration
408 unit of measure paramter selection Default Days */
409
410 IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
411 l_dynmc_drtn_curr := 'curr_abs_drtn_days';
412 l_dynmc_drtn_comp := 'comp_abs_drtn_days';
413 l_abs_fact_params.include_abs_drtn_days := 'Y';
414 ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
415 l_dynmc_drtn_curr := 'curr_abs_drtn_hrs';
416 l_dynmc_drtn_comp := 'comp_abs_drtn_hrs';
417 l_abs_fact_params.include_abs_drtn_hrs := 'Y';
418 ELSE -- functional decision (JC) default to days
419 l_dynmc_drtn_curr := 'curr_abs_drtn_days';
420 l_dynmc_drtn_comp := 'comp_abs_drtn_days';
421 l_abs_fact_params.include_abs_drtn_days := 'Y';
422 END IF;
423
424 /* Format the SQL differently depending on the headcount calculation method */
425 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
426 l_dynmc_hdc_curr := 'NVL((babs.curr_hdc_start + babs.curr_hdc_end)/2,0)';
427 l_dynmc_hdc_comp := 'NVL((babs.comp_hdc_start + babs.comp_hdc_end)/2,0)';
428 l_dynmc_tot_hdc_curr := 'NVL((babs.tot_curr_hdc_start + babs.tot_curr_hdc_end)/2,0)';
429 l_dynmc_tot_hdc_comp := 'NVL((babs.tot_comp_hdc_start + babs.tot_comp_hdc_end)/2,0)';
430 ELSE
431 l_dynmc_hdc_curr := 'babs.curr_hdc_end';
432 l_dynmc_hdc_comp := 'babs.comp_hdc_end';
433 l_dynmc_tot_hdc_curr := 'babs.tot_curr_hdc_end';
434 l_dynmc_tot_hdc_comp := 'babs.tot_comp_hdc_end';
435 END IF;
436
437 /* Get SQL for absence fact */
438 l_abs_fact_params.bind_format := 'PMV';
439 l_abs_fact_params.include_abs_in_period := 'Y';
440 l_abs_fact_params.include_abs_ntfctn_period := 'Y';
441 l_abs_fact_params.include_comp := 'Y';
442 l_abs_fact_params.kpi_mode := 'Y';
443 l_abs_fact_sql := hri_bpl_fact_abs_sql.get_sql
444 (p_parameter_rec => l_parameter_rec,
445 p_bind_tab => l_bind_tab,
446 p_abs_params => l_abs_fact_params,
447 p_calling_module => 'HRI_K_ABS_WMV');
448
449 /* Get SQL for workforce fact */
450 l_wrkfc_fact_params.bind_format := 'PMV';
451 l_wrkfc_fact_params.include_start := 'Y';
452 l_wrkfc_fact_params.include_hdc := 'Y';
453 l_wrkfc_fact_params.include_sal := 'N';
454 l_wrkfc_fact_params.include_low := 'N';
455 l_wrkfc_fact_params.include_comp := 'Y';
456 l_wrkfc_fact_params.kpi_mode := 'Y';
457 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
458 (p_parameter_rec => l_parameter_rec,
459 p_bind_tab => l_bind_tab,
460 p_wrkfc_params => l_wrkfc_fact_params,
461 p_calling_module => 'HRI_K_ABS_WMV');
462
463 l_SQLText :=
464 ' -- Absence (Employee) KPIs
465 SELECT
466 babs.vby_id VIEWBYID
467 ,babs.value VIEWBY'|| g_rtn
468 /* Workforce */|| g_rtn ||'
469 ,NVL(curr_hdc_end,0) HRI_P_MEASURE1
470 ,NVL(comp_hdc_end,0) HRI_P_MEASURE2'|| g_rtn
471 /* Absence */|| g_rtn ||'
472 ,NVL(babs.curr_abs_in_period,0) HRI_P_MEASURE3
473 ,NVL(babs.comp_abs_in_period,0) HRI_P_MEASURE4'|| g_rtn
474 /* Total Notification */|| g_rtn ||'
475 ,NVL(babs.curr_abs_ntfctn_period,0) HRI_P_MEASURE5
476 ,NVL(babs.comp_abs_ntfctn_period,0) HRI_P_MEASURE6'|| g_rtn
477 /* Average Notification */|| g_rtn ||'
478 ,DECODE(babs.curr_abs_ntfctn_period,0, to_number(NULL)
479 ,DECODE(babs.curr_abs_in_period,0, to_number(NULL)
480 ,(babs.curr_abs_ntfctn_period / babs.curr_abs_in_period)
481 )
482 ) HRI_P_MEASURE7
483 ,DECODE(babs.comp_abs_ntfctn_period,0,to_number(NULL)
484 ,DECODE(babs.comp_abs_in_period,0,to_number(NULL)
485 ,(babs.comp_abs_ntfctn_period / babs.comp_abs_in_period)
486 )
487 ) HRI_P_MEASURE8'|| g_rtn
488 /* Total Absence Duration */|| g_rtn ||'
489 ,NVL(babs.curr_abs_drtn,0) HRI_P_MEASURE9
490 ,NVL(babs.comp_abs_drtn,0) HRI_P_MEASURE10'|| g_rtn
491 /* Average Absence Duration */|| g_rtn ||'
492 ,DECODE(babs.curr_abs_in_period,0, to_number(NULL)
493 ,(babs.curr_abs_drtn / babs.curr_abs_in_period)
494 ) HRI_P_MEASURE11
495 ,DECODE(babs.comp_abs_in_period,0, to_number(NULL)
496 ,(babs.comp_abs_drtn / babs.comp_abs_in_period)
497 ) HRI_P_MEASURE12'|| g_rtn
498 /* Average Absence Duration by Emp */|| g_rtn ||'
499 ,DECODE('|| l_dynmc_hdc_curr||',0, to_number(NULL)
500 ,(babs.curr_abs_drtn / '|| l_dynmc_hdc_curr||' )
501 ) HRI_P_MEASURE13
502 ,DECODE('|| l_dynmc_hdc_curr||' ,0,to_number(NULL)
503 ,(babs.comp_abs_drtn / '|| l_dynmc_hdc_curr||' )
504 ) HRI_P_MEASURE14'|| g_rtn
505 /* Total Workforce */|| g_rtn ||'
506 ,NVL(tot_curr_hdc_end,0) HRI_P_GRAND_TOTAL1
507 ,NVL(tot_comp_hdc_end,0) HRI_P_GRAND_TOTAL2'|| g_rtn
508 /* Total Absence */|| g_rtn ||'
509 ,NVL(babs.curr_tot_abs_in_period,0) HRI_P_GRAND_TOTAL3
510 ,NVL(babs.comp_tot_abs_in_period,0) HRI_P_GRAND_TOTAL4'|| g_rtn
511 /* Total Notification */|| g_rtn ||'
512 ,NVL(babs.curr_tot_abs_ntfctn_period,0) HRI_P_GRAND_TOTAL5
513 ,NVL(babs.comp_tot_abs_ntfctn_period,0) HRI_P_GRAND_TOTAL6'|| g_rtn
514 /* Total Average Notification */|| g_rtn ||'
515 ,DECODE(babs.curr_tot_abs_ntfctn_period,0, to_number(NULL)
516 ,DECODE(babs.curr_abs_in_period,0, to_number(NULL)
517 ,(babs.curr_tot_abs_ntfctn_period / babs.curr_tot_abs_in_period)
518 )
519 ) HRI_P_GRAND_TOTAL7
520 ,DECODE(babs.comp_tot_abs_ntfctn_period,0, to_number(NULL)
521 ,DECODE(babs.comp_tot_abs_in_period,0, to_number(NULL)
522 ,(babs.comp_tot_abs_ntfctn_period / babs.comp_tot_abs_in_period)
523 )
524 ) HRI_P_GRAND_TOTAL8'|| g_rtn
525 /* Total Absence Duration */|| g_rtn ||'
526 ,NVL(babs.curr_tot_abs_drtn,0) HRI_P_GRAND_TOTAL9
527 ,NVL(babs.comp_tot_abs_drtn,0) HRI_P_GRAND_TOTAL10'|| g_rtn
528 /* Total Average Absence Duration */|| g_rtn ||'
529 ,DECODE(babs.curr_tot_abs_in_period,0, to_number(NULL)
530 ,(babs.curr_tot_abs_drtn / babs.curr_tot_abs_in_period)
531 ) HRI_P_GRAND_TOTAL11
532 ,DECODE(babs.comp_tot_abs_in_period,0, to_number(NULL)
533 ,(babs.comp_tot_abs_drtn / babs.comp_tot_abs_in_period)
534 ) HRI_P_GRAND_TOTAL12'|| g_rtn
535 /* Total Average Absence Duration by Emp */ || g_rtn ||'
536 ,DECODE('||l_dynmc_tot_hdc_curr||' ,0, to_number(NULL)
537 ,(babs.curr_tot_abs_drtn / '||l_dynmc_tot_hdc_curr||' )
538 ) HRI_P_GRAND_TOTAL13
539 ,DECODE('||l_dynmc_tot_hdc_comp||' ,0, to_number(NULL)
540 ,(babs.comp_tot_abs_drtn / '||l_dynmc_tot_hdc_comp||' )
541 ) HRI_P_GRAND_TOTAL14
542 FROM
543 (
544 SELECT
545 /* Base Measures */
546 wfact.vby_id vby_id
547 ,wfact.vby_id value
548 ,wfact.direct_ind
549 ,NVL(wfact.curr_hdc_start,0) curr_hdc_start
550 ,NVL(wfact.comp_hdc_start,0) comp_hdc_start
551 ,NVL(wfact.curr_hdc_end,0) curr_hdc_end
552 ,NVL(wfact.comp_hdc_end,0) comp_hdc_end
553 ,NVL(afact.'|| l_dynmc_drtn_curr ||',0) curr_abs_drtn
554 ,NVL(afact.curr_abs_in_period,0) curr_abs_in_period
555 ,NVL(afact.'|| l_dynmc_drtn_comp ||',0) comp_abs_drtn
556 ,NVL(afact.comp_abs_in_period,0) comp_abs_in_period
557 ,NVL(afact.curr_abs_ntfctn_period,0) curr_abs_ntfctn_period
558 ,NVL(afact.comp_abs_ntfctn_period,0) comp_abs_ntfctn_period
559 ,SUM(wfact.curr_hdc_start) OVER() tot_curr_hdc_start
560 ,SUM(wfact.comp_hdc_start) OVER() tot_comp_hdc_start
561 ,SUM(wfact.curr_hdc_end) OVER() tot_curr_hdc_end
562 ,SUM(wfact.comp_hdc_end) OVER() tot_comp_hdc_end
563 ,SUM(afact.'|| l_dynmc_drtn_curr ||') OVER()
564 curr_tot_abs_drtn
565 ,SUM(afact.curr_abs_in_period) OVER() curr_tot_abs_in_period
566 ,SUM(afact.'|| l_dynmc_drtn_comp ||') OVER()
567 comp_tot_abs_drtn
568 ,SUM(afact.comp_abs_in_period) OVER() comp_tot_abs_in_period
569 ,SUM(afact.curr_abs_ntfctn_period) OVER() curr_tot_abs_ntfctn_period
570 ,SUM(afact.comp_abs_ntfctn_period) OVER() comp_tot_abs_ntfctn_period
571 FROM
572 ('|| l_abs_fact_sql ||') afact
573 ,('|| l_wrkfc_fact_sql ||') wfact
574 WHERE
575 afact.vby_id (+) = wfact.vby_id
576 ' || l_security_clause || '
577 ) babs';
578
579 x_custom_sql := l_SQLText ;
580
581 END get_sql_kpi;
582
583 END HRI_OLTP_PMV_ABS_WMV_PVT;