[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SUP
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_SUP AS
2 /* $Header: hriopwmv.pkb 120.7 2006/08/18 06:24:08 rkonduru noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6
7 PROCEDURE get_sql2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 x_custom_sql OUT NOCOPY VARCHAR2,
9 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
10
11 l_sqltext VARCHAR2(32767);
12 l_security_clause VARCHAR2(4000);
13 l_custom_rec BIS_QUERY_ATTRIBUTES;
14
15 /* Parameter values */
16 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
17 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
18
19 /* Pre-calculations */
20 l_tot_gain NUMBER;
21 l_tot_gain_hire NUMBER;
22 l_tot_gain_transfer NUMBER;
23 l_tot_loss NUMBER;
24 l_tot_loss_term NUMBER;
25 l_tot_loss_transfer NUMBER;
26 l_tot_net NUMBER;
27
28 /* Direct reports string */
29 l_direct_reports_string VARCHAR2(30);
30
31 /* To support selective drill across urls */
32 l_drill_to_function1 VARCHAR2(300);
33 l_drill_to_function2 VARCHAR2(300);
34 l_drill_to_function3 VARCHAR2(300);
35 l_drill_to_function4 VARCHAR2(300);
36 l_drill_to_function5 VARCHAR2(300);
37 l_drill_to_function6 VARCHAR2(300);
38 l_drill_url1 VARCHAR2(300);
39 l_drill_url2 VARCHAR2(300);
40 l_drill_url3 VARCHAR2(300);
41 l_drill_url4 VARCHAR2(300);
42 l_drill_url5 VARCHAR2(300);
43 l_drill_url6 VARCHAR2(300);
44
45 /* Dynamic SQL controls */
46 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
47 l_wrkfc_fact_sql VARCHAR2(10000);
48 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
49 l_wcnt_chg_fact_sql VARCHAR2(10000);
50
51 BEGIN
52
53 /* Initialize out parameters */
54 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
55 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
56
57 /* Get common parameter values */
58 hri_oltp_pmv_util_param.get_parameters_from_table
59 (p_page_parameter_tbl => p_page_parameter_tbl,
60 p_parameter_rec => l_parameter_rec,
61 p_bind_tab => l_bind_tab);
62
63 /* Employee Activity by Manager */
64 IF (l_parameter_rec.bis_region_code = 'HRI_P_WMV_SUP') THEN
65 l_drill_to_function1 := 'HRI_P_WAC_HIR_SUP_DTL';
66 l_drill_to_function2 := 'HRI_P_WAC_IN_SUP_DTL';
67 l_drill_to_function3 := 'HRI_P_WAC_SEP_SUP_DTL';
68 l_drill_to_function4 := 'HRI_P_WAC_OUT_SUP_DTL';
69 l_drill_to_function5 := 'HRI_P_WMV_SUP';
70 l_drill_to_function6 := 'HRI_P_WMV_SAL_SUP_DTL';
71 /* Contingent Worker Activity by Manager */
72 ELSIF (l_parameter_rec.bis_region_code = 'HRI_P_WMV_C_SUP') THEN
73 l_drill_to_function1 := 'HRI_P_WAC_C_HIR_SUP_DTL';
74 l_drill_to_function2 := 'HRI_P_WAC_C_IN_SUP_DTL';
75 l_drill_to_function3 := 'HRI_P_WAC_C_SEP_SUP_DTL';
76 l_drill_to_function4 := 'HRI_P_WAC_C_OUT_SUP_DTL';
77 l_drill_to_function5 := 'HRI_P_WMV_C_SUP';
78 l_drill_to_function6 := 'HRI_P_WMV_C_SUP_DTL';
79 END IF;
80
81 /* use selective drill across feature */
82 l_drill_url1 := 'pFunctionName=' || l_drill_to_function1 || '&' ||
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_url2 := 'pFunctionName=' || l_drill_to_function2 || '&' ||
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 l_drill_url3 := 'pFunctionName=' || l_drill_to_function3 || '&' ||
95 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
96 'VIEW_BY_NAME=VIEW_BY_ID&' ||
97 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
98 'pParamIds=Y';
99
100 l_drill_url4 := 'pFunctionName=' || l_drill_to_function4 || '&' ||
101 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
102 'VIEW_BY_NAME=VIEW_BY_ID&' ||
103 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
104 'pParamIds=Y';
105
106 l_drill_url5 := 'pFunctionName=' || l_drill_to_function5 || '&' ||
107 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
108 'VIEW_BY_NAME=VIEW_BY_ID&' ||
109 'pParamIds=Y';
110
111 l_drill_url6 := 'pFunctionName=' || l_drill_to_function6 || '&' ||
112 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
113 'VIEW_BY_NAME=VIEW_BY_ID&' ||
114 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
115 'pParamIds=Y';
116
117
118 /* Get security clause for Manager based security */
119 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
120
121 /* Set direct reports string */
122 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
123
124 /* Get WMV Change totals for supervisor from cursor */
125 hri_bpl_dbi_calc_period.calc_sup_wcnt_chg
126 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
127 p_from_date => l_parameter_rec.time_curr_start_date,
128 p_to_date => l_parameter_rec.time_curr_end_date,
129 p_period_type => l_parameter_rec.page_period_type,
130 p_comparison_type => l_parameter_rec.time_comparison_type,
131 p_total_type => 'ROLLUP',
132 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
133 p_total_gain_hire => l_tot_gain_hire,
134 p_total_gain_transfer => l_tot_gain_transfer,
135 p_total_loss_term => l_tot_loss_term,
136 p_total_loss_transfer => l_tot_loss_transfer);
137
138 /* Set WMV Change dependent totals */
139 l_tot_gain := l_tot_gain_hire + l_tot_gain_transfer;
140 l_tot_loss := l_tot_loss_term + l_tot_loss_transfer;
141 l_tot_net := l_tot_gain - l_tot_loss;
142
143 /* Set the dynamic order by from the dimension metadata */
144 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
145 (p_order_by_clause => l_parameter_rec.order_by);
146
147 /* Get SQL for workforce fact */
148 l_wrkfc_fact_params.bind_format := 'PMV';
149 l_wrkfc_fact_params.include_comp := 'Y';
150 l_wrkfc_fact_params.include_hdc := 'Y';
151 l_wrkfc_fact_params.include_start := 'Y';
152 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
153 (p_parameter_rec => l_parameter_rec,
154 p_bind_tab => l_bind_tab,
155 p_wrkfc_params => l_wrkfc_fact_params,
156 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP.GET_SQL2');
157
158 /* Get SQL for workforce changes fact */
159 l_wcnt_chg_fact_params.bind_format := 'PMV';
160 l_wcnt_chg_fact_params.include_hire := 'Y';
161 l_wcnt_chg_fact_params.include_trin := 'Y';
162 l_wcnt_chg_fact_params.include_trout := 'Y';
163 l_wcnt_chg_fact_params.include_term := 'Y';
164 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
165 (p_parameter_rec => l_parameter_rec,
166 p_bind_tab => l_bind_tab,
167 p_wcnt_chg_params => l_wcnt_chg_fact_params,
168 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP.GET_SQL2');
169
170 /* Build query */
171 l_sqltext :=
172 'SELECT -- Headcount Portlet
173 tots.id VIEWBYID
174 ,tots.value VIEWBY ' || g_rtn ||
175 ',DECODE(tots.suph_rollup_flag,
176 ''N'', ''' || l_drill_url6 || ''',
177 ''' || l_drill_url5 || ''') HRI_P_DRILL_URL5' || g_rtn ||
178 ',tots.value HRI_P_CHAR1_GA' || g_rtn ||
179 /* WMV value at Start */
180 ',tots.current_wmv_start HRI_P_MEASURE7 ' || g_rtn ||
181 /* WMV gained through hires */
182 ',tots.wmv_gain_hire HRI_P_MEASURE3 ' || g_rtn ||
183 ',''' || l_drill_url1 ||''' HRI_P_DRILL_URL1 ' || g_rtn ||
184 /* WMV gained through transfers in */
185 ',tots.wmv_gain_transfer HRI_P_MEASURE4 ' || g_rtn ||
186 ',''' || l_drill_url2 ||''' HRI_P_DRILL_URL2 ' || g_rtn ||
187 /* WMV lost through terminations */
188 ',tots.wmv_loss_term HRI_P_MEASURE5 ' || g_rtn ||
189 ',''' || l_drill_url3 ||''' HRI_P_DRILL_URL3 ' || g_rtn ||
190 /* WMV lost through transfers out */
191 ',tots.wmv_loss_transfer HRI_P_MEASURE6 ' || g_rtn ||
192 ',''' || l_drill_url4 ||''' HRI_P_DRILL_URL4 ' || g_rtn ||
193 /* Current WMV value */
194 ',tots.current_wmv_end HRI_P_MEASURE2 ' || g_rtn ||
195 /* Net change in WMV value */
196 ',tots.wmv_net HRI_P_WMV_CHNG_NET_SUM_MV
197 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
198 (p_previous_col => 'tots.previous_wmv_end',
199 p_current_col => 'tots.current_wmv_end') || '
200 HRI_P_WMV_CHNG_PCT_SUM_MV
201 ,tots.comp_total_hdc_end HRI_P_WMV_SUM_PREV_MV
202 ,tots.comp_total_hdc_end HRI_P_MEASURE1
203 ,tots.wmv_gain_hire HRI_P_MEASURE8
204 ,tots.wmv_loss_term HRI_P_MEASURE9
205 ,tots.curr_total_hdc_end HRI_P_GRAND_TOTAL1
206 ,:HRI_TOT_GAIN_HIRE HRI_P_GRAND_TOTAL5
207 ,:HRI_TOT_GAIN_TRANSFER HRI_P_GRAND_TOTAL6
208 ,:HRI_TOT_LOSS_TERM HRI_P_GRAND_TOTAL7
209 ,:HRI_TOT_LOSS_TRANSFER HRI_P_GRAND_TOTAL8
210 ,tots.curr_total_hdc_start HRI_P_GRAND_TOTAL2
211 ,:HRI_TOT_NET_GAIN_LOSS HRI_P_GRAND_TOTAL3
212 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
213 (p_previous_col => 'tots.comp_total_hdc_end',
214 p_current_col => 'tots.curr_total_hdc_end') || '
215 HRI_P_GRAND_TOTAL4
216 ,tots.comp_total_hdc_end HRI_P_GRAND_TOTAL9 ' || g_rtn ||
217 /* Order by person name default sort order */
218 ',tots.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
219 /* Whether the row is a supervisor rollup row */
220 ',tots.suph_rollup_flag HRI_P_SUPH_RO_CA
221 FROM
222 (SELECT
223 per.id
224 ,DECODE(wmv.direct_ind,
225 1, ''' || l_direct_reports_string || ''',
226 per.value) value
227 ,to_char(wmv.direct_ind) || per.order_by order_by
228 ,NVL(chg.curr_hire_hdc + chg.curr_transfer_in_hdc,0) wmv_gain
229 ,NVL(chg.curr_hire_hdc,0) wmv_gain_hire
230 ,NVL(chg.curr_transfer_in_hdc,0) wmv_gain_transfer
231 ,NVL(chg.curr_termination_hdc + chg.curr_transfer_out_hdc,0) wmv_loss
232 ,NVL(chg.curr_termination_hdc,0) wmv_loss_term
233 ,NVL(chg.curr_transfer_out_hdc,0) wmv_loss_transfer
234 ,NVL(chg.curr_hire_hdc + chg.curr_transfer_in_hdc -
235 (chg.curr_termination_hdc + chg.curr_transfer_out_hdc), 0) wmv_net
236 ,wmv.curr_hdc_start current_wmv_start
237 ,wmv.curr_hdc_end current_wmv_end
238 ,wmv.comp_hdc_end previous_wmv_end
239 ,SUM(wmv.curr_hdc_end) OVER () curr_total_hdc_end
240 ,SUM(wmv.curr_total_hdc_start) OVER () curr_total_hdc_start
241 ,SUM(wmv.comp_total_hdc_end) OVER () comp_total_hdc_end
242 ,DECODE(wmv.direct_ind,
243 1, ''N'',
244 '''') suph_rollup_flag
245 FROM
246 hri_dbi_cl_per_n_v per
247 ,(' || l_wrkfc_fact_sql || ') wmv
248 ,(' || l_wcnt_chg_fact_sql || ') chg
249 WHERE wmv.vby_id = chg.vby_id (+)
250 AND wmv.vby_id = per.id
251 AND &BIS_CURRENT_ASOF_DATE BETWEEN per.effective_start_date
252 AND per.effective_end_date
253 AND (wmv.curr_hdc_end > 0
254 OR chg.curr_hire_hdc > 0
255 OR chg.curr_transfer_in_hdc > 0
256 OR chg.curr_transfer_out_hdc > 0
257 OR chg.curr_termination_hdc > 0
258 OR wmv.direct_ind = 1)
259 ) tots
260 WHERE 1 = 1
261 ' || l_security_clause || '
262 ORDER BY ' || l_parameter_rec.order_by;
263
264 x_custom_sql := l_SQLText;
265
266 l_custom_rec.attribute_name := ':HRI_TOT_GAIN_HIRE';
267 l_custom_rec.attribute_value := l_tot_gain_hire;
268 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
269 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
270 x_custom_output.extend;
271 x_custom_output(1) := l_custom_rec;
272
273 l_custom_rec.attribute_name := ':HRI_TOT_GAIN_TRANSFER';
274 l_custom_rec.attribute_value := l_tot_gain_transfer;
275 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
276 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
277 x_custom_output.extend;
278 x_custom_output(2) := l_custom_rec;
279
280 l_custom_rec.attribute_name := ':HRI_TOT_LOSS_TERM';
281 l_custom_rec.attribute_value := l_tot_loss_term;
282 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
283 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
284 x_custom_output.extend;
285 x_custom_output(3) := l_custom_rec;
286
287 l_custom_rec.attribute_name := ':HRI_TOT_LOSS_TRANSFER';
288 l_custom_rec.attribute_value := l_tot_loss_transfer;
289 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
290 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
291 x_custom_output.extend;
292 x_custom_output(4) := l_custom_rec;
293
294 l_custom_rec.attribute_name := ':HRI_TOT_NET_GAIN_LOSS';
295 l_custom_rec.attribute_value := l_tot_net;
296 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
297 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
298 x_custom_output.extend;
299 x_custom_output(5) := l_custom_rec;
300
301 END get_sql2;
302
303 --
304 -- ----------------------------------------------------------------------
305 -- Procedure to fetch the headcount KPI
306 -- It fetched the values for the following KPIs
307 -- 1. Total Headcount
308 -- 2. Previous Total Headcount
309 -- 3. Average Length of Service
310 -- 4. Previous Average Length of Service
311 -- ----------------------------------------------------------------------
312 --
313 PROCEDURE get_wmv_low_kpi(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
314 x_custom_sql OUT NOCOPY VARCHAR2,
315 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
316 --
317 -- The security clause
318 --
319 l_security_clause VARCHAR2(4000);
320 --
321 -- Page parameters
322 --
323 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
324 --
325 -- Bind values for SQL and PMV mode
326 --
327 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
328 --
329 -- Parameter values for getting the inner SQL
330 --
331 l_wrkfc_params hri_bpl_fact_sup_wrkfc_sql.WRKFC_FACT_PARAM_TYPE;
332 --
333 -- Inner SQL
334 --
335 l_trend_sql VARCHAR2(32767);
336 l_custom_rec BIS_QUERY_ATTRIBUTES;
337 --
338 BEGIN
339 --
340 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
341 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
342 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
343 --
344 -- Get the parameter information from the page parameter table
345 --
346 hri_oltp_pmv_util_param.get_parameters_from_table
347 (p_page_parameter_tbl => p_page_parameter_tbl,
348 p_parameter_rec => l_parameter_rec,
349 p_bind_tab => l_bind_tab);
350 --
351 -- Set the parameters for getting the inner SQL
352 --
353 l_wrkfc_params.bind_format := 'PMV';
354 l_wrkfc_params.include_comp := 'Y';
355 l_wrkfc_params.include_hdc := 'Y';
356 l_wrkfc_params.include_low := 'Y';
357 l_wrkfc_params.include_pasg_cnt := 'Y';
358 l_wrkfc_params.kpi_mode := 'Y';
359 --
360 -- Get the inner SQL
361 --
362 l_trend_sql := HRI_OLTP_PMV_QUERY_WRKFC.get_sql
363 (p_parameter_rec => l_parameter_rec,
364 p_bind_tab => l_bind_tab,
365 p_wrkfc_params => l_wrkfc_params,
366 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP.get_wmv_low_kpi');
367 --
368 -- Form the SQL
369 --
370 x_custom_sql :=
371 'SELECT -- Headcount KPI
372 qry.vby_id VIEWBYID
373 ,qry.vby_id VIEWBY
374 ,qry.curr_hdc_end HRI_P_MEASURE1
375 ,qry.comp_hdc_end HRI_P_MEASURE2
376 ,DECODE(qry.curr_pasg_cnt_end,0,0,qry.curr_low_end/(365*qry.curr_pasg_cnt_end))
377 HRI_P_MEASURE4
378 ,DECODE(qry.comp_pasg_cnt_end,0,0,qry.comp_low_end/(365*qry.comp_pasg_cnt_end))
379 HRI_P_MEASURE5
380 ,qry.curr_hdc_end HRI_P_GRAND_TOTAL1
381 ,qry.comp_hdc_end HRI_P_GRAND_TOTAL2
382 ,DECODE(qry.curr_pasg_cnt_end,0,0,qry.curr_low_end/(365*qry.curr_pasg_cnt_end))
383 HRI_P_GRAND_TOTAL4
384 ,DECODE(qry.comp_pasg_cnt_end,0,0,qry.comp_low_end/(365*qry.comp_pasg_cnt_end))
385 HRI_P_GRAND_TOTAL5
386 FROM
387 ('||l_trend_sql||') qry
388 WHERE 1=1
389 ' || l_security_clause;
390 --
391 END get_wmv_low_kpi;
392
393
394 /* CWK KPIs */
395 PROCEDURE get_wmv_c_low_kpi(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
396 x_custom_sql OUT NOCOPY VARCHAR2,
397 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
398
399 l_sqltext VARCHAR2(32767);
400 l_security_clause VARCHAR2(4000);
401 l_custom_rec BIS_QUERY_ATTRIBUTES;
402
403 /* Parameter values */
404 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
405 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
406
407 /* Dynamic SQL controls */
408 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
409 l_wrkfc_fact_sql VARCHAR2(10000);
410 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
411 l_wcnt_chg_fact_sql VARCHAR2(10000);
412
413 l_page_parameter_tbl BIS_PMV_PAGE_PARAMETER_TBL;
414
415 BEGIN
416
417 /* Initialize out parameters */
418 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
419 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
420
421 /* Get common parameter values */
422 hri_oltp_pmv_util_param.get_parameters_from_table
423 (p_page_parameter_tbl => p_page_parameter_tbl,
424 p_parameter_rec => l_parameter_rec,
425 p_bind_tab => l_bind_tab);
426
427 /* Get security clause for Manager based security */
428 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
429
430 /* Set the dynamic order by from the dimension metadata */
431 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
432 (p_order_by_clause => l_parameter_rec.order_by);
433
434 /* Get SQL for workforce fact */
435 l_wrkfc_fact_params.bind_format := 'PMV';
436 l_wrkfc_fact_params.include_comp := 'Y';
437 l_wrkfc_fact_params.include_hdc := 'Y';
438 l_wrkfc_fact_params.include_start := 'Y';
439 l_wrkfc_fact_params.include_low := 'Y';
440 l_wrkfc_fact_params.include_pasg_cnt := 'Y';
441 l_wrkfc_fact_params.kpi_mode := 'Y';
442 l_wrkfc_fact_params.bucket_dim := 'HRI_PRSNTYP+HRI_WKTH_WKTYP';
443 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
444 (p_parameter_rec => l_parameter_rec,
445 p_bind_tab => l_bind_tab,
446 p_wrkfc_params => l_wrkfc_fact_params,
447 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP.GET_WMV_C_LOW_KPI');
448
449 /* Form the dynamic SQL to PMV */
450 x_custom_sql :=
451 --l_parameter_rec.debug_header || g_rtn ||
452 'SELECT -- Contingent Worker Headcount KPI
453 qry.vby_id VIEWBYID
454 ,qry.vby_id VIEWBY
455 ,qry.curr_hdc_cwk HRI_P_MEASURE1
456 ,qry.comp_hdc_cwk HRI_P_MEASURE2
457 ,DECODE(qry.curr_pasg_cnt_cwk,0,0,qry.curr_low_cwk/(:MONTHS_MULTIPLIER*qry.curr_pasg_cnt_cwk))
458 HRI_P_MEASURE4
459 ,DECODE(qry.comp_pasg_cnt_cwk,0,0,qry.comp_low_cwk/(:MONTHS_MULTIPLIER*qry.comp_pasg_cnt_cwk))
460 HRI_P_MEASURE5
461 ,qry.curr_hdc_cwk HRI_P_GRAND_TOTAL1
462 ,qry.comp_hdc_cwk HRI_P_GRAND_TOTAL2
463 ,DECODE(qry.curr_pasg_cnt_cwk,0,0,qry.curr_low_cwk/(:MONTHS_MULTIPLIER*qry.curr_pasg_cnt_cwk))
464 HRI_P_GRAND_TOTAL4
465 ,DECODE(qry.comp_pasg_cnt_cwk,0,0,qry.comp_low_cwk/(:MONTHS_MULTIPLIER*qry.comp_pasg_cnt_cwk))
466 HRI_P_GRAND_TOTAL5
467 FROM
468 ('||l_wrkfc_fact_sql||') qry
469 WHERE 1=1' || g_rtn
470 ||l_security_clause;
471
472 l_custom_rec.attribute_name := ':MONTHS_MULTIPLIER';
473 l_custom_rec.attribute_value := 30.42;
474 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
475 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
476 x_custom_output.extend;
477 x_custom_output(1) := l_custom_rec;
478
479 END get_wmv_c_low_kpi;
480
481 PROCEDURE get_wmv_c_atvty_kpi(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
482 x_custom_sql OUT NOCOPY VARCHAR2,
483 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
484
485 l_sqltext VARCHAR2(32767);
486 l_security_clause VARCHAR2(4000);
487 l_custom_rec BIS_QUERY_ATTRIBUTES;
488
489 /* Parameter values */
490 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
491 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
492
493 /* Dynamic SQL controls */
494 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
495 l_wrkfc_fact_sql VARCHAR2(10000);
496 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
497 l_wcnt_chg_fact_sql VARCHAR2(10000);
498
499 l_page_parameter_tbl BIS_PMV_PAGE_PARAMETER_TBL;
500
501 BEGIN
502
503 /* Initialize out parameters */
504 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
505 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
506
507 /* Get common parameter values */
508 hri_oltp_pmv_util_param.get_parameters_from_table
509 (p_page_parameter_tbl => p_page_parameter_tbl,
510 p_parameter_rec => l_parameter_rec,
511 p_bind_tab => l_bind_tab);
512
513 /* Get security clause for Manager based security */
514 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
515
516 /* Set the dynamic order by from the dimension metadata */
517 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
518 (p_order_by_clause => l_parameter_rec.order_by);
519
520 /* Get SQL for workforce fact */
521 l_wrkfc_fact_params.bind_format := 'PMV';
522 l_wrkfc_fact_params.include_comp := 'Y';
523 l_wrkfc_fact_params.include_hdc := 'Y';
524 l_wrkfc_fact_params.kpi_mode := 'Y';
525 l_wrkfc_fact_params.bucket_dim := 'HRI_PRSNTYP+HRI_WKTH_WKTYP';
526 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
527 (p_parameter_rec => l_parameter_rec,
528 p_bind_tab => l_bind_tab,
529 p_wrkfc_params => l_wrkfc_fact_params,
530 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP.GET_WMV_C_KPI');
531
532 /* Get SQL for workforce changes fact */
533 l_wcnt_chg_fact_params.bind_format := 'PMV';
534 l_wcnt_chg_fact_params.include_comp := 'Y';
535 l_wcnt_chg_fact_params.include_hire := 'Y';
536 l_wcnt_chg_fact_params.include_term := 'Y';
537 l_wcnt_chg_fact_params.kpi_mode := 'Y';
538 l_wcnt_chg_fact_params.bucket_dim := 'HRI_PRSNTYP+HRI_WKTH_WKTYP';
539 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
540 (p_parameter_rec => l_parameter_rec,
541 p_bind_tab => l_bind_tab,
542 p_wcnt_chg_params => l_wcnt_chg_fact_params,
543 p_calling_module => 'HRI_OLTP_PMV_WMV_SUP.GET_WMV_C_ATVTY_KPI');
544
545 /* Form the dynamic SQL to PMV */
546 x_custom_sql :=
547 --l_parameter_rec.debug_header || g_rtn ||
548 'SELECT -- Contingent Worker Activity KPI
549 wmv.vby_id VIEWBYID
550 ,wmv.vby_id VIEWBY
551 ,wmv.curr_hdc_cwk HRI_P_MEASURE1
552 ,wmv.comp_hdc_cwk HRI_P_MEASURE2
553 ,NVL(atvty.curr_hire_hdc_cwk,0) HRI_P_MEASURE4
554 ,NVL(atvty.comp_hire_hdc_cwk,0) HRI_P_MEASURE5
555 ,NVL(atvty.curr_termination_hdc_cwk,0) HRI_P_MEASURE7
556 ,NVL(atvty.comp_termination_hdc_cwk,0) HRI_P_MEASURE8
557 ,wmv.curr_hdc_cwk HRI_P_GRAND_TOTAL1
558 ,wmv.comp_hdc_cwk HRI_P_GRAND_TOTAL2
559 ,NVL(atvty.curr_hire_hdc_cwk,0) HRI_P_GRAND_TOTAL4
560 ,NVL(atvty.comp_hire_hdc_cwk,0) HRI_P_GRAND_TOTAL5
561 ,NVL(atvty.curr_termination_hdc_cwk,0) HRI_P_GRAND_TOTAL7
562 ,NVL(atvty.comp_termination_hdc_cwk,0) HRI_P_GRAND_TOTAL8
563 FROM
564 ('||l_wcnt_chg_fact_sql||') atvty ' || g_rtn
565 ||',('||l_wrkfc_fact_sql||') wmv
566 WHERE 1=1
567 AND wmv.vby_id = atvty.vby_id (+)' || g_rtn
568 ||l_security_clause;
569
570 END get_wmv_c_atvty_kpi;
571
572
573 END HRI_OLTP_PMV_WMV_SUP;