[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_TRN_SUP
Source
1 PACKAGE BODY hri_oltp_pmv_wmv_trn_sup AS
2 /* $Header: hriopwts.pkb 120.4 2005/12/12 08:11:07 cbridge noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6
7 /******************************************************************************/
8 /* Annualized Turnover Portlet */
9 /******************************************************************************/
10 PROCEDURE get_sql2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
11 x_custom_sql OUT NOCOPY VARCHAR2,
12 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
13
14 /* Variables for SQL returned */
15 l_sqltext VARCHAR2(30000) ;
16 l_security_clause VARCHAR2(4000);
17 l_custom_rec BIS_QUERY_ATTRIBUTES;
18 l_drill_url VARCHAR2(500);
19 l_drill_url2 VARCHAR2(500);
20
21 /* Parameter values */
22 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
23 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
24
25 /* Pre-calculations */
26 l_calc_anl_factor NUMBER;
27 l_curr_term_vol NUMBER;
28 l_curr_term_invol NUMBER;
29 l_comp_term_vol NUMBER;
30 l_comp_term_invol NUMBER;
31
32 /* Columns */
33 l_col_curr_trn_hdc VARCHAR2(100);
34 l_col_comp_trn_hdc VARCHAR2(100);
35 l_col_curr_tot_trn_hdc VARCHAR2(100);
36 l_col_comp_tot_trn_hdc VARCHAR2(100);
37
38 /* Dynamic SQL Controls */
39 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
40 l_wrkfc_fact_sql VARCHAR2(10000);
41 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
42 l_wcnt_chg_fact_sql VARCHAR2(10000);
43
44 /* Messages */
45 l_direct_reports_string VARCHAR2(100);
46
47 BEGIN
48
49 /* Initialize out parameters */
50 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
51 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
52
53 /* Get security clause for Manager based security */
54 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
55
56 /* Get direct reports string */
57 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
58
59 /* Drill url creation */
60 l_drill_url := 'pFunctionName=HRI_P_WMV_TRN_SUMMARY_PVT&' ||
61 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
62 'VIEW_BY_NAME=VIEW_BY_ID&' ||
63 'pParamIds=Y';
64 l_drill_url2 := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
65 'VIEW_BY_NAME=VIEW_BY_ID&' ||
66 'HRI_P_SUPH_RO_CA=N&' ||
67 'pParamIds=Y';
68
69 /* Get common parameter values */
70 hri_oltp_pmv_util_param.get_parameters_from_table
71 (p_page_parameter_tbl => p_page_parameter_tbl,
72 p_parameter_rec => l_parameter_rec,
73 p_bind_tab => l_bind_tab);
74
75 /* Turnover calculation method is either start/end average or end value */
76 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
77
78 /* Set Column Strings */
79 l_col_curr_trn_hdc := '(wmv.curr_hdc_end + wmv.curr_hdc_start) / 2';
80 l_col_comp_trn_hdc := '(wmv.comp_hdc_end + wmv.comp_hdc_start) / 2';
81 l_col_curr_tot_trn_hdc := '(wmv.curr_hdc_end + wmv.curr_total_hdc_start) / 2';
82 l_col_comp_tot_trn_hdc := '(wmv.comp_total_hdc_end + wmv.comp_total_hdc_start) / 2';
83
84 ELSE
85
86 /* Set Column Strings */
87 l_col_curr_trn_hdc := 'wmv.curr_hdc_end';
88 l_col_comp_trn_hdc := 'wmv.comp_hdc_end';
89 l_col_curr_tot_trn_hdc := 'wmv.curr_hdc_end';
90 l_col_comp_tot_trn_hdc := 'wmv.comp_total_hdc_end';
91
92 END IF;
93
94 /* Get the annualization factor for the different periods */
95 l_calc_anl_factor := hri_oltp_pmv_util_pkg.calc_anl_factor
96 (p_period_type => l_parameter_rec.page_period_type);
97
98 /* Get current period turnover totals for supervisor from cursor */
99 hri_bpl_dbi_calc_period.calc_sup_turnover
100 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
101 p_from_date => l_parameter_rec.time_curr_start_date,
102 p_to_date => l_parameter_rec.time_curr_end_date,
103 p_period_type => l_parameter_rec.page_period_type,
104 p_comparison_type => l_parameter_rec.time_comparison_type,
105 p_total_type => 'ROLLUP',
106 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
107 p_total_trn_vol => l_curr_term_vol,
108 p_total_trn_invol => l_curr_term_invol);
109
110 /* Get previous period turnover totals for supervisor from cursor */
111 hri_bpl_dbi_calc_period.calc_sup_turnover
112 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
113 p_from_date => l_parameter_rec.time_comp_start_date,
114 p_to_date => l_parameter_rec.time_comp_end_date,
115 p_period_type => l_parameter_rec.page_period_type,
116 p_comparison_type => l_parameter_rec.time_comparison_type,
117 p_total_type => 'ROLLUP',
118 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
119 p_total_trn_vol => l_comp_term_vol,
120 p_total_trn_invol => l_comp_term_invol);
121
122 /* Get SQL for workforce fact */
123 l_wrkfc_fact_params.bind_format := 'PMV';
124 l_wrkfc_fact_params.include_comp := 'Y';
125 l_wrkfc_fact_params.include_start := 'Y';
126 l_wrkfc_fact_params.include_hdc := 'Y';
127 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
128 (p_parameter_rec => l_parameter_rec,
129 p_bind_tab => l_bind_tab,
130 p_wrkfc_params => l_wrkfc_fact_params,
131 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP.GET_SQL2');
132
133 /* Get SQL for workforce changes fact */
134 l_wcnt_chg_fact_params.bind_format := 'PMV';
135 l_wcnt_chg_fact_params.include_comp := 'Y';
136 l_wcnt_chg_fact_params.include_sep := 'Y';
137 l_wcnt_chg_fact_params.include_sep_vol := 'Y';
138 l_wcnt_chg_fact_params.include_sep_inv := 'Y';
139 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
140 (p_parameter_rec => l_parameter_rec,
141 p_bind_tab => l_bind_tab,
142 p_wcnt_chg_params => l_wcnt_chg_fact_params,
143 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP.GET_SQL2');
144
145 /* Format report query */
146 l_sqltext :=
147 'SELECT -- Turnover Portlet
148 a.id VIEWBYID
149 ,a.value VIEWBY
150 ,a.value HRI_P_PER_SUP_LNAME_CN
151 ,a.order_by HRI_P_ORDER_BY_1
152 ,DECODE(a.direct_ind,
153 1, ''' || l_drill_url2 || ''',
154 ''' || l_drill_url || ''') HRI_P_DRILL_URL1
155 ,a.anl_factor * 100 * a.curr_term_vol_hdc / a.curr_trn_div
156 HRI_P_WMV_TRN_SEP_VOL_ANL_MV
157 ,a.anl_factor * 100 * a.curr_term_invol_hdc / a.curr_trn_div
158 HRI_P_WMV_TRN_SEP_INV_ANL_MV
159 ,a.anl_factor * 100 * a.curr_termination_hdc / a.curr_trn_div
160 HRI_P_WMV_TRN_ANL_SUM_MV
161 ,a.anl_factor * 100 * a.comp_termination_hdc / a.comp_trn_div
162 HRI_P_WMV_TRN_ANL_SUM_PREV_MV
163 ,a.anl_factor * 100 * a.comp_term_vol_hdc / a.comp_trn_div
164 HRI_P_MEASURE1
165 ,a.anl_factor * 100 * a.comp_term_invol_hdc / a.comp_trn_div
166 HRI_P_MEASURE2
167 ,a.anl_factor * 100 * (a.curr_termination_hdc / a.curr_trn_div -
168 a.comp_termination_hdc / a.comp_trn_div)
169 HRI_P_WMV_CHNG_PCT_SUM_MV' || g_rtn ||
170 /* Grand total of Annualized Current Period Voluntary turnover */
171 ',a.anl_factor * 100 * a.total_curr_trn_vol / a.total_curr_trn_div
172 HRI_P_GRAND_TOTAL1' || g_rtn ||
173 /* Grand total of Annualized Current Period Involuntary turnover */
174 ',a.anl_factor * 100 * a.total_curr_trn_inv / a.total_curr_trn_div
175 HRI_P_GRAND_TOTAL2' || g_rtn ||
176 /* Grand total of Annualized Current Period Total turnover */
177 ',a.anl_factor * 100 * a.total_curr_trn_tot / a.total_curr_trn_div
178 HRI_P_GRAND_TOTAL3' || g_rtn ||
179 /* Grand total of Annualized Prior Period Total turnover */
180 ',a.anl_factor * 100 * a.total_comp_trn_tot / a.total_comp_trn_div
181 HRI_P_GRAND_TOTAL4' || g_rtn ||
182 /* Grand total of Annualized Turnover Change Percentage */
183 ',a.anl_factor * 100 * (a.total_curr_trn_tot / total_curr_trn_div -
184 a.total_comp_trn_tot / total_comp_trn_div)
185 HRI_P_GRAND_TOTAL5' || g_rtn ||
186 /* Grand total of Annualized Prior Period Voluntary turnover */
187 ',a.anl_factor * 100 * a.total_comp_trn_vol / a.total_comp_trn_div
188 HRI_P_GRAND_TOTAL6' || g_rtn ||
189 /* Grand total of Annualized Prior Period Involuntary turnover */
190 ',a.anl_factor * 100 * a.total_comp_trn_inv / a.total_comp_trn_div
191 HRI_P_GRAND_TOTAL7
192 FROM
193 (SELECT
194 tots.* ' || g_rtn ||
195 /* Headcount change */
196 ' ,DECODE(tots.comp_hdc_end,
197 0, 0,
198 100 * (tots.curr_hdc_end - tots.comp_hdc_end) / tots.comp_hdc_end)
199 hdc_change_pct' || g_rtn ||
200 /* Terminations Factor */
201 ' ,DECODE(tots.curr_hdc_trn,
202 0, DECODE(tots.curr_termination_hdc, 0 , 1, tots.curr_termination_hdc),
203 tots.curr_hdc_trn) curr_trn_div
204 ,DECODE(tots.comp_hdc_trn,
205 0, DECODE(tots.comp_termination_hdc, 0 , 1, tots.comp_termination_hdc),
206 tots.comp_hdc_trn) comp_trn_div
207 ,:HRI_ANL_FACTOR anl_factor' || g_rtn ||
208 /* Grand Totals - Terminations */
209 ' ,DECODE(tots.total_curr_hdc_trn,
210 0, DECODE(tots.total_curr_trn_tot, 0 , 1, tots.total_curr_trn_tot),
211 tots.total_curr_hdc_trn) total_curr_trn_div
212 ,DECODE(tots.total_comp_hdc_trn,
213 0, DECODE(tots.total_comp_trn_tot, 0 , 1, tots.total_comp_trn_tot),
214 tots.total_comp_hdc_trn) total_comp_trn_div
215 FROM
216 (SELECT
217 vby.id
218 ,DECODE(wmv.direct_ind,
219 1, ''' || l_direct_reports_string || ''',
220 vby.value) value
221 ,to_char(wmv.direct_ind) || vby.order_by order_by' || g_rtn ||
222 /* Indicators */
223 ' ,wmv.direct_ind' || g_rtn ||
224 /* Headcount */
225 ' ,wmv.curr_hdc_end
226 ,wmv.comp_hdc_end' || g_rtn ||
227 /* Headcount for turnover calculation */
228 ' ,' || l_col_curr_trn_hdc || ' curr_hdc_trn
229 ,' || l_col_comp_trn_hdc || ' comp_hdc_trn' || g_rtn ||
230 /* Turnover */
231 ' ,NVL(trn.curr_sep_vol_hdc, 0) curr_term_vol_hdc
232 ,NVL(trn.curr_sep_invol_hdc, 0) curr_term_invol_hdc
233 ,NVL(trn.curr_separation_hdc, 0) curr_termination_hdc
234 ,NVL(trn.comp_sep_vol_hdc, 0) comp_term_vol_hdc
235 ,NVL(trn.comp_sep_invol_hdc, 0) comp_term_invol_hdc
236 ,NVL(trn.comp_separation_hdc, 0) comp_termination_hdc' || g_rtn ||
237 /* Grand Totals - Headcount */
238 ' ,SUM(wmv.curr_hdc_end) OVER () curr_total_hdc_end
239 ,SUM(wmv.comp_total_hdc_end) OVER () comp_total_hdc_end' || g_rtn ||
240 /* Grand Totals - Headcount for turnover calculation */
241 ' ,SUM(' || l_col_curr_tot_trn_hdc || ') OVER () total_curr_hdc_trn
242 ,SUM(' || l_col_comp_tot_trn_hdc || ') OVER () total_comp_hdc_trn' || g_rtn ||
243 /* Grand Totals - Turnover */
244 ' ,:HRI_CURR_TERM_VOL total_curr_trn_vol
245 ,:HRI_CURR_TERM_INVOL total_curr_trn_inv
246 ,:HRI_CURR_TERM_INVOL + :HRI_CURR_TERM_VOL total_curr_trn_tot
247 ,:HRI_COMP_TERM_VOL total_comp_trn_vol
248 ,:HRI_COMP_TERM_INVOL total_comp_trn_inv
249 ,:HRI_COMP_TERM_VOL + :HRI_COMP_TERM_INVOL total_comp_trn_tot
250 FROM
251 hri_dbi_cl_per_n_v vby
252 ,(' || l_wrkfc_fact_sql || ') wmv
253 ,(' || l_wcnt_chg_fact_sql || ') trn
254 WHERE wmv.vby_id = trn.vby_id (+)
255 AND wmv.vby_id = vby.id
256 AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date
257 ) tots
258 WHERE (tots.curr_hdc_end > 0
259 OR tots.comp_termination_hdc > 0
260 OR tots.curr_termination_hdc > 0
261 OR tots.direct_ind = 1)
262 ) a
263 WHERE 1 = 1
264 ' || l_security_clause || '
265 &ORDER_BY_CLAUSE';
266
267 x_custom_sql := l_SQLText;
268
269 l_custom_rec.attribute_name := ':HRI_ANL_FACTOR';
270 l_custom_rec.attribute_value := l_calc_anl_factor;
271 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
272 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
273 x_custom_output.extend;
274 x_custom_output(1) := l_custom_rec;
275
276 l_custom_rec.attribute_name := ':HRI_CURR_TERM_VOL';
277 l_custom_rec.attribute_value := l_curr_term_vol;
278 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
279 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
280 x_custom_output.extend;
281 x_custom_output(2) := l_custom_rec;
282
283 l_custom_rec.attribute_name := ':HRI_CURR_TERM_INVOL';
284 l_custom_rec.attribute_value := l_curr_term_invol;
285 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
286 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
287 x_custom_output.extend;
288 x_custom_output(3) := l_custom_rec;
289
290 l_custom_rec.attribute_name := ':HRI_COMP_TERM_VOL';
291 l_custom_rec.attribute_value := l_comp_term_vol;
292 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
293 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
294 x_custom_output.extend;
295 x_custom_output(4) := l_custom_rec;
296
297 l_custom_rec.attribute_name := ':HRI_COMP_TERM_INVOL';
298 l_custom_rec.attribute_value := l_comp_term_invol;
299 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
300 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
301 x_custom_output.extend;
302 x_custom_output(5) := l_custom_rec;
303
304 END get_sql2;
305
306 /******************************************************************************/
307 /* Annualized Turnover Status Portlet */
308 /******************************************************************************/
309 PROCEDURE get_actual_detail_sql2
310 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
311 x_custom_sql OUT NOCOPY VARCHAR2,
312 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
313
314 /* Variables for SQL returned */
315 l_sqltext VARCHAR2(30000) ;
316 l_security_clause VARCHAR2(4000);
317 l_custom_rec BIS_QUERY_ATTRIBUTES;
318
319 /* Parameter values */
320 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
321 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
322
323 /* Annualization factor for period type parameter */
324 l_calc_anl_factor NUMBER;
325
326 /* Pre-calculations */
327 l_curr_term_vol NUMBER;
328 l_curr_term_invol NUMBER;
329 l_comp_term_vol NUMBER;
330 l_comp_term_invol NUMBER;
331
332 /* selective drill across urls */
333 l_drill_url1 VARCHAR2(300);
334 l_drill_url2 VARCHAR2(300);
335 l_drill_url3 VARCHAR2(300);
336 l_drill_url4 VARCHAR2(300);
337 l_drill_url5 VARCHAR2(300);
338
339 /* Columns */
340 l_col_curr_trn_hdc VARCHAR2(100);
341 l_col_comp_trn_hdc VARCHAR2(100);
342 l_col_curr_tot_trn_hdc VARCHAR2(100);
343 l_col_comp_tot_trn_hdc VARCHAR2(100);
344
345 /* Dynamic SQL Controls */
346 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
347 l_wrkfc_fact_sql VARCHAR2(10000);
348 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
349 l_wcnt_chg_fact_sql VARCHAR2(10000);
350
351 /* Messages */
352 l_direct_reports_string VARCHAR2(100);
353
354 BEGIN
355
356 /* define the selective drill across urls */
357 l_drill_url1 := 'pFunctionName=HRI_P_WMV_TRN_SUP_PVT&' ||
358 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
359 'VIEW_BY_NAME=VIEW_BY_ID&' ||
360 'pParamIds=Y';
361
362 l_drill_url2 := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
363 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X=SEP_VOL&' ||
364 'VIEW_BY_NAME=VIEW_BY_ID&' ||
365 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
366 'pParamIds=Y';
367
368 l_drill_url3 := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
369 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X=SEP_INV&' ||
370 'VIEW_BY_NAME=VIEW_BY_ID&' ||
371 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
372 'pParamIds=Y';
373
374 l_drill_url4 := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
375 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X=ALL&' ||
376 'VIEW_BY_NAME=VIEW_BY_ID&' ||
377 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
378 'pParamIds=Y';
379
380 l_drill_url5 := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
381 'VIEW_BY_NAME=VIEW_BY_ID&' ||
382 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
383 'pParamIds=Y';
384
385 /* Initialize out parameters */
386 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
387 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
388
389 /* Get security clause for Manager based security */
390 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
391
392 /* Get direct reports string */
393 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
394
395 /* Get common parameter values */
396 hri_oltp_pmv_util_param.get_parameters_from_table
397 (p_page_parameter_tbl => p_page_parameter_tbl,
398 p_parameter_rec => l_parameter_rec,
399 p_bind_tab => l_bind_tab);
400
401 /* Turnover calculation method is either start/end average or end value */
402 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
403
404 /* Set Column Strings */
405 l_col_curr_trn_hdc := '(wmv.curr_hdc_end + wmv.curr_hdc_start) / 2';
406 l_col_curr_tot_trn_hdc := '(wmv.curr_hdc_end + wmv.curr_total_hdc_start) / 2';
407
408 ELSE
409
410 /* Set Column Strings */
411 l_col_curr_trn_hdc := 'wmv.curr_hdc_end';
412 l_col_curr_tot_trn_hdc := 'wmv.curr_hdc_end';
413
414 END IF;
415
416 /* Get the annualization factor for the different periods */
417 l_calc_anl_factor := hri_oltp_pmv_util_pkg.calc_anl_factor
418 (p_period_type => l_parameter_rec.page_period_type);
419
420 /* Get current period turnover totals for supervisor from cursor */
421 hri_bpl_dbi_calc_period.calc_sup_turnover
422 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
423 p_from_date => l_parameter_rec.time_curr_start_date,
424 p_to_date => l_parameter_rec.time_curr_end_date,
425 p_period_type => l_parameter_rec.page_period_type,
426 p_comparison_type => l_parameter_rec.time_comparison_type,
427 p_total_type => 'ROLLUP',
428 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
429 p_total_trn_vol => l_curr_term_vol,
430 p_total_trn_invol => l_curr_term_invol);
431
432 /* Get SQL for workforce fact */
433 l_wrkfc_fact_params.bind_format := 'PMV';
434 l_wrkfc_fact_params.include_start := 'Y';
435 l_wrkfc_fact_params.include_hdc := 'Y';
436 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
437 (p_parameter_rec => l_parameter_rec,
438 p_bind_tab => l_bind_tab,
439 p_wrkfc_params => l_wrkfc_fact_params,
440 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP.GET_ACTUAL_DETAIL_SQL2');
441
442 /* Get SQL for workforce changes fact */
443 l_wcnt_chg_fact_params.bind_format := 'PMV';
444 l_wcnt_chg_fact_params.include_sep := 'Y';
445 l_wcnt_chg_fact_params.include_sep_vol := 'Y';
446 l_wcnt_chg_fact_params.include_sep_inv := 'Y';
447 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
448 (p_parameter_rec => l_parameter_rec,
449 p_bind_tab => l_bind_tab,
450 p_wcnt_chg_params => l_wcnt_chg_fact_params,
451 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP.GET_ACTUAL_DETAIL_SQL2');
452
453 /* Set the default order by */
454 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
455 (p_order_by_clause => l_parameter_rec.order_by);
456
457 /* Build SQL Query */
458 l_SQLText :=
459 'SELECT -- Turnover Status
460 a.id VIEWBYID
461 ,a.value VIEWBY ' || g_rtn ||
462 /* Order by default person sort name */
463 ',a.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
464 ',a.value HRI_P_CHAR1_GA' || g_rtn ||
465 ',DECODE(a.direct_ind,
466 1, ''' || l_drill_url5 || ''',
467 ''' || l_drill_url1 || ''') HRI_P_DRILL_URL1' || g_rtn ||
468 /* WMV value at current period start */
469 ',a.curr_hdc_start HRI_P_MEASURE1 ' || g_rtn ||
470 /* WMV value at current period end */
471 ',a.curr_hdc_end HRI_P_MEASURE2 ' || g_rtn ||
472 /* Voluntary separations */
473 ',a.curr_term_vol_hdc HRI_P_MEASURE3 ' || g_rtn ||
474 ','''|| l_drill_url2 || ''' HRI_P_DRILL_URL2' || g_rtn ||
475 /* Annualized voluntary separations as a percentage of calculated WMV */
476 ',a.anl_factor * 100 * a.curr_term_vol_hdc / a.curr_trn_div
477 HRI_P_MEASURE3_MP ' || g_rtn ||
478 /* Involuntary separations */
479 ',a.curr_term_invol_hdc HRI_P_MEASURE4 ' || g_rtn ||
480 ','''|| l_drill_url3 || ''' HRI_P_DRILL_URL3' || g_rtn ||
481 /* Annualized involuntary separations as a percentage of calculated WMV */
482 ',a.anl_factor * 100 * a.curr_term_invol_hdc / a.curr_trn_div
483 HRI_P_MEASURE4_MP ' || g_rtn ||
484 /* Total separations */
485 ',a.curr_termination_hdc HRI_P_MEASURE5 ' || g_rtn ||
486 ','''|| l_drill_url4 || ''' HRI_P_DRILL_URL4' || g_rtn ||
487 /* Total annualized separations as a percentage of calculated WMV */
488 ',a.anl_factor * 100 * a.curr_termination_hdc / a.curr_trn_div
489 HRI_P_MEASURE5_MP ' || g_rtn ||
490 /* Grand total of Start Headcount as of start date for a top supervisor_id */
491 ',a.curr_total_hdc_start HRI_P_GRAND_TOTAL1 ' || g_rtn ||
492 /* Grand total of End Headcount as of end date for a top supervisor_id */
493 ',a.curr_total_hdc_end HRI_P_GRAND_TOTAL2 ' || g_rtn ||
494 /* Grand total of Vol Headcount as of end date for a top supervisor_id */
495 ',a.total_curr_trn_vol HRI_P_GRAND_TOTAL3 ' || g_rtn ||
496 /* Grand total of Vol Headcount Percent as of end date for a top supervisor_id */
497 ',a.anl_factor * 100 * a.total_curr_trn_vol / a.total_curr_trn_div
498 HRI_P_GRAND_TOTAL4 ' || g_rtn ||
499 /* Grand total of Invol Headcount as of end date for a top supervisor_id */
500 ',a.total_curr_trn_inv HRI_P_GRAND_TOTAL5 ' || g_rtn ||
501 /* Grand total of Invol Headcount Percent as of end date for a top supervisor_id */
502 ',a.anl_factor * 100 * a.total_curr_trn_inv / a.total_curr_trn_div
503 HRI_P_GRAND_TOTAL6 ' || g_rtn ||
504 /* Grand total of vol and invol Headcount as of end date for a top supervisor_id */
505 ',a.total_curr_trn_tot HRI_P_GRAND_TOTAL7 ' || g_rtn ||
506 /* Grand total of vol and Invol Headcount Percent as of end date for a top supervisor_id */
507 ',a.anl_factor * 100 * a.total_curr_trn_tot / a.total_curr_trn_div
508 HRI_P_GRAND_TOTAL8 ' || g_rtn ||
509 /* Whether the row is a rolled up supervisor (Y) or direct report (N) */
510 ',DECODE(a.direct_ind,
511 1, ''N'',
512 '''') HRI_P_SUPH_RO_CA
513 FROM
514 (SELECT
515 tots.* ' || g_rtn ||
516 /* Terminations Factor */
517 ' ,DECODE(tots.curr_hdc_trn,
518 0, DECODE(tots.curr_termination_hdc, 0 , 1, tots.curr_termination_hdc),
519 tots.curr_hdc_trn) curr_trn_div
520 ,:HRI_ANL_FACTOR anl_factor' || g_rtn ||
521 /* Grand Totals - Terminations */
522 ' ,DECODE(tots.total_curr_hdc_trn,
523 0, DECODE(tots.total_curr_trn_tot, 0 , 1, tots.total_curr_trn_tot),
524 tots.total_curr_hdc_trn) total_curr_trn_div
525 FROM
526 (SELECT
527 vby.id
528 ,DECODE(wmv.direct_ind,
529 1, ''' || l_direct_reports_string || ''',
530 vby.value) value
531 ,to_char(wmv.direct_ind) || vby.order_by order_by' || g_rtn ||
532 /* Indicators */
533 ' ,wmv.direct_ind' || g_rtn ||
534 /* Headcount */
535 ' ,wmv.curr_hdc_end
536 ,wmv.curr_hdc_start' || g_rtn ||
537 /* Headcount for turnover calculation */
538 ' ,' || l_col_curr_trn_hdc || ' curr_hdc_trn' || g_rtn ||
539 /* Turnover */
540 ' ,NVL(trn.curr_sep_vol_hdc, 0) curr_term_vol_hdc
541 ,NVL(trn.curr_sep_invol_hdc, 0) curr_term_invol_hdc
542 ,NVL(trn.curr_separation_hdc, 0) curr_termination_hdc' || g_rtn ||
543 /* Grand Totals - Headcount */
544 ' ,SUM(wmv.curr_hdc_end) OVER () curr_total_hdc_end
545 ,SUM(wmv.curr_total_hdc_start) OVER () curr_total_hdc_start' || g_rtn ||
546 /* Grand Totals - Headcount for turnover calculation */
547 ' ,SUM(' || l_col_curr_tot_trn_hdc || ') OVER () total_curr_hdc_trn' || g_rtn ||
548 /* Grand Totals - Turnover */
549 ' ,:HRI_CURR_TERM_VOL total_curr_trn_vol
550 ,:HRI_CURR_TERM_INVOL total_curr_trn_inv
551 ,:HRI_CURR_TERM_INVOL + :HRI_CURR_TERM_VOL total_curr_trn_tot
552 FROM
553 hri_dbi_cl_per_n_v vby
554 ,(' || l_wrkfc_fact_sql || ') wmv
555 ,(' || l_wcnt_chg_fact_sql || ') trn
556 WHERE wmv.vby_id = trn.vby_id (+)
557 AND wmv.vby_id = vby.id
558 AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date
559 ) tots
560 WHERE (tots.curr_hdc_end > 0
561 OR tots.curr_hdc_start > 0
562 OR tots.curr_term_vol_hdc > 0
563 OR tots.curr_term_invol_hdc > 0
564 OR tots.direct_ind = 1)
565 ) a
566 WHERE 1 = 1
567 ' || l_security_clause || '
568 ORDER BY ' || l_parameter_rec.order_by;
569
570 x_custom_sql := l_SQLText;
571
572 l_custom_rec.attribute_name := ':HRI_ANL_FACTOR';
573 l_custom_rec.attribute_value := l_calc_anl_factor;
574 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
575 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
576 x_custom_output.extend;
577 x_custom_output(1) := l_custom_rec;
578
579 l_custom_rec.attribute_name := ':HRI_CURR_TERM_VOL';
580 l_custom_rec.attribute_value := l_curr_term_vol;
581 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
582 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
583 x_custom_output.extend;
584 x_custom_output(2) := l_custom_rec;
585
586 l_custom_rec.attribute_name := ':HRI_CURR_TERM_INVOL';
587 l_custom_rec.attribute_value := l_curr_term_invol;
588 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
589 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
590 x_custom_output.extend;
591 x_custom_output(3) := l_custom_rec;
592
593 END get_actual_detail_sql2;
594
595 PROCEDURE GET_SQL_TRN_PVT(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
596 x_custom_sql OUT NOCOPY VARCHAR2,
597 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
598
599 l_SQLText VARCHAR2(32767) ;
600 l_custom_rec BIS_QUERY_ATTRIBUTES;
601
602 /* Parameter values */
603 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
604 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
605
606 /* Dynamic SQL */
607 l_security_clause VARCHAR2(4000);
608 l_termination_count_filter VARCHAR2(100);
609 l_view_by_filter VARCHAR2(1000);
610 l_outer_join VARCHAR2(30);
611
612 /* Dynamic SQL Controls */
613 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
614 l_wrkfc_fact_sql VARCHAR2(10000);
615 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
616 l_wcnt_chg_fact_sql VARCHAR2(10000);
617
618 /* Messages */
619 l_direct_reports_string VARCHAR2(100);
620
621 /* Drill URLs */
622 l_sup_drill_url VARCHAR2(1000);
623 l_dir_drill_url VARCHAR2(1000);
624 l_vb_sup_drill_url VARCHAR2(1000);
625 l_vb_drill_mgr_dir VARCHAR2(1000);
626
627 /* Pre-calculations for turnover total */
628 l_curr_trn_vol NUMBER;
629 l_curr_trn_invol NUMBER;
630 l_comp_trn_vol NUMBER;
631 l_comp_trn_invol NUMBER;
632 l_dummy1 NUMBER;
633
634 BEGIN
635 /* Initialize out parameters */
636 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
637 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
638
639 /* Get common parameter values */
640 hri_oltp_pmv_util_param.get_parameters_from_table
641 (p_page_parameter_tbl => p_page_parameter_tbl,
642 p_parameter_rec => l_parameter_rec,
643 p_bind_tab => l_bind_tab);
644
645 /* Get security clause for Manager based security */
646 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
647
648 /* Get direct reports string */
649 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
650
651 /* Get current period turnover totals for supervisor from cursor */
652 hri_bpl_dbi_calc_period.calc_sup_term_pvt
653 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
654 p_from_date => l_parameter_rec.time_curr_start_date,
655 p_to_date => l_parameter_rec.time_curr_end_date,
656 p_bind_tab => l_bind_tab,
657 p_total_term_vol => l_curr_trn_vol,
658 p_total_term_invol => l_curr_trn_invol,
659 p_total_term => l_dummy1);
660
661 /* Get previous period turnover totals for supervisor from cursor */
662 hri_bpl_dbi_calc_period.calc_sup_term_pvt
663 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
664 p_from_date => l_parameter_rec.time_comp_start_date,
665 p_to_date => l_parameter_rec.time_comp_end_date,
666 p_bind_tab => l_bind_tab,
667 p_total_term_vol => l_comp_trn_vol,
668 p_total_term_invol => l_comp_trn_invol,
669 p_total_term => l_dummy1);
670
671 /* Setup any drill urls */
672 l_sup_drill_url := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
673 'VIEW_BY_NAME=VIEW_BY_ID&' ||
674 'pParamIds=Y';
675 l_dir_drill_url := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
676 'VIEW_BY_NAME=VIEW_BY_ID&' ||
677 'HRI_P_SUPH_RO_CA=N&' ||
678 'pParamIds=Y';
679
680 -- ----------------------
681 -- View by Person, enable different drill urls on viewby
682 -- ----------------------
683 IF (l_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
684 l_vb_sup_drill_url := 'pFunctionName=HRI_P_WMV_TRN_PVT&' ||
685 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
686 'VIEW_BY_NAME=VIEW_BY_ID&' ||
687 'pParamIds=Y';
688
689 l_vb_drill_mgr_dir := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
690 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
691 'VIEW_BY_NAME=VIEW_BY_ID&' ||
692 'HRI_P_SUPH_RO_CA=N&' ||
693 'pParamIds=Y';
694 END IF;
695
696 /* Set the dynamic order by from the dimension metadata */
697 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
698 (p_order_by_clause => l_parameter_rec.order_by);
699
700 /* Get SQL for workforce changes fact */
701 l_wcnt_chg_fact_params.bind_format := 'PMV';
702 l_wcnt_chg_fact_params.include_comp := 'Y';
703 l_wcnt_chg_fact_params.include_sep := 'Y';
704 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
705 (p_parameter_rec => l_parameter_rec,
706 p_bind_tab => l_bind_tab,
707 p_wcnt_chg_params => l_wcnt_chg_fact_params,
708 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP.GET_SQL_PVT');
709
710 /* Check the view by for a terminations only report */
711 /* If the viewby is NOT terminations only then join to workforce */
712 IF (l_parameter_rec.view_by <> 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' AND
713 l_parameter_rec.view_by <> 'HRI_REASON+HRI_RSN_SEP_X') THEN
714
715 /* Get SQL for workforce fact */
716 l_wrkfc_fact_params.bind_format := 'PMV';
717 l_wrkfc_fact_params.include_hdc := 'Y';
718 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
719 (p_parameter_rec => l_parameter_rec,
720 p_bind_tab => l_bind_tab,
721 p_wrkfc_params => l_wrkfc_fact_params,
722 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP.GET_SQL_PVT');
723
724 /* Set the display row conditions */
725 IF (l_parameter_rec.view_by = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
726 l_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
727 l_outer_join := ' (+)';
728 /* Set the view by filter */
729 l_view_by_filter := hri_oltp_pmv_util_pkg.set_viewby_filter
730 (p_parameter_rec => l_parameter_rec,
731 p_bind_tab => l_bind_tab,
732 p_view_by_alias => 'cl');
733 ELSE
734 /* Only display rows with current headcount */
735 l_termination_count_filter :=
736 'AND (a.curr_termination_hdc > 0
737 OR a.curr_hdc_end > 0
738 OR a.direct_ind = 1)' || g_rtn;
739 END IF;
740
741 /* Else if the view by is a terminations only report, set the outer */
742 /* join for the separation category view by */
743 ELSIF (l_parameter_rec.view_by = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X') THEN
744
745 l_outer_join := ' (+)';
746 l_termination_count_filter :=
747 'AND a.vby_id IN (''SEP_VOL'',''SEP_INV'')' || g_rtn;
748
749 /* Set the view by filter */
750 l_view_by_filter := hri_oltp_pmv_util_pkg.set_viewby_filter
751 (p_parameter_rec => l_parameter_rec,
752 p_bind_tab => l_bind_tab,
753 p_view_by_alias => 'cl');
754
755 /* Else set the filter for a terminations only report */
756 ELSE
757 l_termination_count_filter :=
758 'AND (a.curr_termination_hdc > 0
759 OR a.comp_termination_hdc > 0)' || g_rtn;
760
761 END IF;
762
763 /* Set any additional viewby conditions */
764 IF (l_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
765 l_view_by_filter :=
766 'AND &BIS_CURRENT_ASOF_DATE BETWEEN cl.start_date AND cl.end_date' || g_rtn;
767 END IF;
768
769 /* Return AK Sql To PMV */
770 l_SQLText :=
771 'SELECT -- Terminations Status
772 a.order_by HRI_P_ORDER_BY_1
773 ,a.vby_id VIEWBYID
774 ,DECODE(a.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB
775 ,DECODE(a.direct_ind,
776 1, ''' || l_vb_drill_mgr_dir || ''',
777 ''' || l_vb_sup_drill_url || ''') HRI_P_DRILL_URL2
778 ,a.vby_value VIEWBY
779 ,a.curr_termination_hdc HRI_P_MEASURE1
780 ,DECODE(a.direct_ind,
781 1, ''' || l_dir_drill_url || ''',
782 ''' || l_sup_drill_url || ''') HRI_P_DRILL_URL1
783 ,a.comp_termination_hdc HRI_P_MEASURE2
784 ,DECODE(a.comp_termination_hdc,
785 0, DECODE(a.curr_termination_hdc, 0, 0, 100),
786 (a.curr_termination_hdc - a.comp_termination_hdc) * 100 /
787 a.comp_termination_hdc) HRI_P_MEASURE1_MP
788 ,a.curr_total_term_hdc HRI_P_GRAND_TOTAL1
789 ,a.comp_total_term_hdc HRI_P_GRAND_TOTAL2
790 ,DECODE(a.comp_total_term_hdc,
791 0, DECODE(a.curr_total_term_hdc, 0, 0, 100),
792 (a.curr_total_term_hdc - a.comp_total_term_hdc) * 100 /
793 a.comp_total_term_hdc) HRI_P_GRAND_TOTAL1_MP
794 FROM' || g_rtn;
795
796 /* Check the view by for a terminations only report */
797 /* If the view by is NOT terminations only join to workforce */
798 IF (l_parameter_rec.view_by <> 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' AND
799 l_parameter_rec.view_by <> 'HRI_REASON+HRI_RSN_SEP_X') THEN
800
801 l_sqltext := l_sqltext ||
802 '(SELECT
803 cl.id vby_id
804 ,DECODE(wmv.direct_ind,
805 1, ''' || l_direct_reports_string || ''',
806 cl.value) vby_value
807 ,NVL(wmv.direct_ind, 0) direct_ind
808 ,to_char(NVL(wmv.direct_ind, 0)) || cl.order_by order_by
809 ,NVL(wmv.curr_hdc_end, 0) curr_hdc_end
810 ,NVL(trn.curr_separation_hdc, 0) curr_termination_hdc
811 ,NVL(trn.comp_separation_hdc, 0) comp_termination_hdc
812 ,:HRI_CURR_TERM_HDC curr_total_term_hdc
813 ,:HRI_PREV_TERM_HDC comp_total_term_hdc
814 FROM
815 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
816 (l_parameter_rec.view_by).viewby_table || ' cl
817 ,(' || l_wcnt_chg_fact_sql || ') trn
818 ,(' || l_wrkfc_fact_sql || ') wmv
819 WHERE cl.id = wmv.vby_id' || l_outer_join || '
820 AND wmv.vby_id = trn.vby_id (+) ' || g_rtn ||
821 l_view_by_filter ||
822 ') a
823 WHERE 1 = 1
824 ' || l_security_clause || g_rtn ||
825 l_termination_count_filter ||
826 'ORDER BY ' || l_parameter_rec.order_by;
827
828 /* Otherwise get terminations data only */
829 ELSE
830 l_sqltext := l_sqltext ||
831 '(SELECT
832 cl.id vby_id
833 ,DECODE(trn.direct_ind,
834 1, ''' || l_direct_reports_string || ''',
835 cl.value) vby_value
836 ,NVL(trn.direct_ind, 0) direct_ind
837 ,to_char(NVL(trn.direct_ind, 0)) || cl.order_by order_by
838 ,NVL(trn.curr_separation_hdc, 0) curr_termination_hdc
839 ,NVL(trn.comp_separation_hdc, 0) comp_termination_hdc
840 ,:HRI_CURR_TERM_HDC curr_total_term_hdc
841 ,:HRI_PREV_TERM_HDC comp_total_term_hdc
842 FROM
843 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
844 (l_parameter_rec.view_by).viewby_table || ' cl
845 ,(' || l_wcnt_chg_fact_sql || ') trn
846 WHERE cl.id = trn.vby_id ' || l_outer_join || g_rtn ||
847 l_view_by_filter ||
848 ') a
849 WHERE 1 = 1
850 ' || l_security_clause || g_rtn ||
851 l_termination_count_filter ||
852 'ORDER BY ' || l_parameter_rec.order_by;
853
854 END IF;
855
856 x_custom_sql := l_SQLText;
857
858 /* Binds Will be inserted Below */
859 l_custom_rec.attribute_name := ':HRI_CURR_TERM_HDC';
860 l_custom_rec.attribute_value := l_curr_trn_vol + l_curr_trn_invol;
861 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
862 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
863 x_custom_output.extend;
864 x_custom_output(1) := l_custom_rec;
865
866 l_custom_rec.attribute_name := ':HRI_PREV_TERM_HDC';
867 l_custom_rec.attribute_value := l_comp_trn_vol + l_comp_trn_invol;
868 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
869 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
870 x_custom_output.extend;
871 x_custom_output(2) := l_custom_rec;
872
873 END GET_SQL_TRN_PVT;
874
875 --
876 -- ----------------------------------------------------------------------
877 -- Procedure to fetch the termination KPI
878 -- It fetched the values for the following KPIs
879 -- 1. Current Terminations
880 -- 2. Previous Terminations
881 -- 3. Current average length of service
882 -- 4. Previous average length of service
883 -- -----------------------------------------------------------------------
884 --
885 PROCEDURE get_trn_los_kpi(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
886 x_custom_sql OUT NOCOPY VARCHAR2,
887 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
888 --
889 l_custom_rec BIS_QUERY_ATTRIBUTES;
890 --
891 -- The security clause
892 --
893 l_security_clause VARCHAR2(4000);
894 --
895 -- Inner SQL
896 --
897 l_inn_sql VARCHAR2(32767);
898 --
899 -- Page parameters
900 --
901 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
902 --
903 -- Bind values for SQL and PMV mode
904 --
905 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
906 --
907 -- Parameter values for getting the inner SQL
908 --
909 l_wcnt_chg_params hri_bpl_fact_sup_wcnt_chg_sql.WCNT_CHG_FACT_PARAM_TYPE;
910 --
911
912 BEGIN
913 --
914 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
915 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
916 --
917 -- Get the parameter information from the page parameter table
918 --
919 hri_oltp_pmv_util_param.get_parameters_from_table
920 (p_page_parameter_tbl => p_page_parameter_tbl,
921 p_parameter_rec => l_parameter_rec,
922 p_bind_tab => l_bind_tab);
923 --
924 -- Set the parameters for getting the inner SQL
925 --
926 l_wcnt_chg_params.bind_format := 'PMV';
927 l_wcnt_chg_params.include_comp := 'Y';
928 l_wcnt_chg_params.include_sep := 'Y';
929 l_wcnt_chg_params.include_low := 'Y';
930 l_wcnt_chg_params.kpi_mode := 'Y';
931 --
932 -- Get the inner SQL
933 --
934 l_inn_sql := HRI_OLTP_PMV_QUERY_WCNT_CHG.get_sql
935 (p_parameter_rec => l_parameter_rec,
936 p_bind_tab => l_bind_tab,
937 p_wcnt_chg_params => l_wcnt_chg_params,
938 p_calling_module => 'get_trn_kpi');
939 --
940 -- Form the SQL
941 --
942 x_custom_sql :='
943 SELECT -- Terminations KPI
944 qry.vby_id VIEWBYID
945 ,qry.vby_id VIEWBY
946 ,qry.curr_separation_hdc HRI_P_MEASURE1
947 ,qry.comp_separation_hdc HRI_P_MEASURE2
948 ,DECODE(qry.curr_separation_hdc,
949 0, 0,
950 qry.curr_low_months / (12 * qry.curr_separation_hdc))
951 HRI_P_MEASURE4
952 ,DECODE(qry.comp_separation_hdc,
953 0, 0,
954 qry.comp_low_months / (12 * qry.comp_separation_hdc))
955 HRI_P_MEASURE5
956 ,qry.curr_separation_hdc HRI_P_GRAND_TOTAL1
957 ,qry.comp_separation_hdc HRI_P_GRAND_TOTAL2
958 ,DECODE(qry.curr_separation_hdc,
959 0, 0,
960 qry.curr_low_months / (12 * qry.curr_separation_hdc))
961 HRI_P_GRAND_TOTAL4
962 ,DECODE(qry.comp_separation_hdc,
963 0, 0,
964 qry.comp_low_months / (12 * qry.comp_separation_hdc))
965 HRI_P_GRAND_TOTAL5
966 FROM
967 ('||l_inn_sql||') qry
968 WHERE 1=1
969 ' || l_security_clause;
970 --
971 END get_trn_los_kpi;
972 --
973 -- ----------------------------------------------------------------------
974 -- Procedure to fetch the termination by separation type KPI
975 -- It fetched the values for the following KPIs
976 -- 1. Total Terminations
977 -- 2. Voluntary Terminations
978 -- 3. Involuntary terminations
979 -- -----------------------------------------------------------------------
980 --
981 PROCEDURE get_wmv_trn_kpi(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
982 x_custom_sql OUT NOCOPY VARCHAR2,
983 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
984 --
985 l_custom_rec BIS_QUERY_ATTRIBUTES;
986 --
987 -- The security clause
988 --
989 l_security_clause VARCHAR2(4000);
990 l_calc_anl_factor NUMBER;
991 --
992 -- Inner SQL for termination
993 --
994 l_trn_sql VARCHAR2(32767);
995 --
996 -- Inner SQL for headcount
997 --
998 l_hdc_sql VARCHAR2(32767);
999 --
1000 -- Page parameters
1001 --
1002 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
1003 --
1004 -- Bind values for SQL and PMV mode
1005 --
1006 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
1007 --
1008 -- Parameter values for getting the inner SQL for termination
1009 --
1010 l_wcnt_chg_params hri_bpl_fact_sup_wcnt_chg_sql.WCNT_CHG_FACT_PARAM_TYPE;
1011 --
1012 -- Parameter values for getting the inner SQL for headcount
1013 --
1014 l_wrkfc_params hri_bpl_fact_sup_wrkfc_sql.WRKFC_FACT_PARAM_TYPE;
1015 --
1016
1017 l_curr_hdc_end_col VARCHAR2(100);
1018 l_comp_hdc_end_col VARCHAR2(100);
1019
1020 BEGIN
1021 --
1022 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1023 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1024 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
1025 --
1026 -- Get the parameter information from the page parameter table
1027 --
1028 hri_oltp_pmv_util_param.get_parameters_from_table
1029 (p_page_parameter_tbl => p_page_parameter_tbl,
1030 p_parameter_rec => l_parameter_rec,
1031 p_bind_tab => l_bind_tab);
1032 --
1033 -- Get the annualization factor
1034 --
1035 l_calc_anl_factor := hri_oltp_pmv_util_pkg.calc_anl_factor
1036 (p_period_type => l_parameter_rec.page_period_type);
1037 --
1038 -- Set the parameters for getting the inner SQL for termination
1039 --
1040 l_wcnt_chg_params.bind_format := 'PMV';
1041 l_wcnt_chg_params.include_comp := 'Y';
1042 l_wcnt_chg_params.include_sep := 'Y';
1043 l_wcnt_chg_params.include_sep_inv := 'Y';
1044 l_wcnt_chg_params.include_sep_vol := 'Y';
1045 l_wcnt_chg_params.kpi_mode := 'Y';
1046
1047 -- bug 4294146
1048 -- Check the Turnover Profile option and change dynamic calculations
1049 -- columns in the report SQL statement
1050 --
1051 IF fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG' THEN
1052 -- use average turnover headcount calculation method
1053 l_curr_hdc_end_col := '((hdc.curr_hdc_start+hdc.curr_hdc_end)/2)';
1054 l_comp_hdc_end_col := '((hdc.comp_hdc_start+hdc.comp_hdc_end)/2)';
1055
1056 ELSE
1057 -- use end headcount turnover calculation method
1058 l_curr_hdc_end_col := 'hdc.curr_hdc_end';
1059 l_comp_hdc_end_col := 'hdc.comp_hdc_end';
1060
1061 END IF;
1062
1063
1064 --
1065 -- Get the inner SQL for termination
1066 --
1067 l_trn_sql := HRI_OLTP_PMV_QUERY_WCNT_CHG.get_sql
1068 (p_parameter_rec => l_parameter_rec,
1069 p_bind_tab => l_bind_tab,
1070 p_wcnt_chg_params => l_wcnt_chg_params,
1071 p_calling_module => 'hri_oltp_pmv_wmv_trn_sup.get_wmv_trn_kpi');
1072 --
1073 -- Set the parameters for getting the inner SQL for headcount
1074 --
1075 l_wrkfc_params.bind_format := 'PMV';
1076 l_wrkfc_params.include_comp := 'Y';
1077 l_wrkfc_params.include_start := 'Y';
1078 l_wrkfc_params.include_hdc := 'Y';
1079 l_wrkfc_params.include_sal := 'N';
1080 l_wrkfc_params.include_low := 'N';
1081 l_wrkfc_params.kpi_mode := 'Y';
1082 l_wrkfc_params.bucket_dim := '';
1083 --
1084 -- Get the inner SQL for headcount
1085 --
1086 l_hdc_sql := HRI_OLTP_PMV_QUERY_WRKFC.get_sql
1087 (p_parameter_rec => l_parameter_rec,
1088 p_bind_tab => l_bind_tab,
1089 p_wrkfc_params => l_wrkfc_params,
1090 p_calling_module => 'hri_oltp_pmv_wmv_trn_sup.get_wmv_trn_kpi');
1091 --
1092 -- Form the SQL
1093 --
1094 x_custom_sql := '
1095 SELECT -- Terminations by Separation KPI
1096 a.vby_id VIEWBYID
1097 ,a.vby_id VIEWBY
1098 ,a.anl_factor * 100 * a.curr_separation_hdc / a.curr_trn_div HRI_P_MEASURE1
1099 ,a.anl_factor * 100 * a.comp_separation_hdc / a.comp_trn_div HRI_P_MEASURE2
1100 ,a.anl_factor * 100 * a.curr_sep_vol_hdc / a.curr_trn_div HRI_P_MEASURE4
1101 ,a.anl_factor * 100 * a.comp_sep_vol_hdc / a.comp_trn_div HRI_P_MEASURE5
1102 ,a.anl_factor * 100 * a.curr_sep_invol_hdc / a.curr_trn_div HRI_P_MEASURE7
1103 ,a.anl_factor * 100 * a.comp_sep_invol_hdc / a.comp_trn_div HRI_P_MEASURE8
1104 ,a.anl_factor * 100 * a.curr_separation_hdc / a.curr_trn_div HRI_P_GRAND_TOTAL1
1105 ,a.anl_factor * 100 * a.comp_separation_hdc / a.comp_trn_div HRI_P_GRAND_TOTAL2
1106 ,a.anl_factor * 100 * a.curr_sep_vol_hdc / a.curr_trn_div HRI_P_GRAND_TOTAL4
1107 ,a.anl_factor * 100 * a.comp_sep_vol_hdc / a.comp_trn_div HRI_P_GRAND_TOTAL5
1108 ,a.anl_factor * 100 * a.curr_sep_invol_hdc / a.curr_trn_div HRI_P_GRAND_TOTAL7
1109 ,a.anl_factor * 100 * a.comp_sep_invol_hdc / a.comp_trn_div HRI_P_GRAND_TOTAL8
1110 FROM
1111 (SELECT
1112 hdc.vby_id
1113 ,NVL(trn.curr_separation_hdc, 0) curr_separation_hdc
1114 ,NVL(trn.curr_sep_invol_hdc, 0) curr_sep_invol_hdc
1115 ,NVL(trn.curr_sep_vol_hdc, 0) curr_sep_vol_hdc
1116 ,NVL(trn.comp_separation_hdc, 0) comp_separation_hdc
1117 ,NVL(trn.comp_sep_invol_hdc, 0) comp_sep_invol_hdc
1118 ,NVL(trn.comp_sep_vol_hdc, 0) comp_sep_vol_hdc
1119 ,DECODE(' || l_curr_hdc_end_col || ',
1120 0, DECODE(trn.curr_separation_hdc, 0 , 1, trn.curr_separation_hdc),
1121 ' || l_curr_hdc_end_col || ') curr_trn_div
1122 ,DECODE(' || l_comp_hdc_end_col || ',
1123 0, DECODE(trn.comp_separation_hdc, 0 , 1, trn.comp_separation_hdc),
1124 ' || l_comp_hdc_end_col || ') comp_trn_div
1125 ,:HRI_ANL_FACTOR anl_factor
1126 FROM
1127 ('||l_trn_sql||') trn
1128 ,('||l_hdc_sql||') hdc
1129 WHERE hdc.vby_id = trn.vby_id (+)
1130 ) a
1131 WHERE 1 = 1
1132 ' || l_security_clause;
1133 --
1134 -- Set the annualization factor
1135 --
1136 l_custom_rec.attribute_name := ':HRI_ANL_FACTOR';
1137 l_custom_rec.attribute_value := l_calc_anl_factor;
1138 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1139 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
1140 x_custom_output.extend;
1141 x_custom_output(1) := l_custom_rec;
1142 --
1143 END get_wmv_trn_kpi;
1144
1145 END hri_oltp_pmv_wmv_trn_sup;