1 PACKAGE BODY hri_bpl_fact_sup_wrkfc_sql AS
2 /* $Header: hribfwrk.pkb 120.6 2006/09/13 08:47:45 rkonduru noship $ */
3
4 TYPE g_bind_rec_tab_type IS TABLE OF VARCHAR2(32000) INDEX BY VARCHAR2(80);
5
6 -- Table of bind strings in required format
7 g_binds g_bind_rec_tab_type;
8 g_binds_reset g_bind_rec_tab_type;
9
10 -- Templates for SELECT columns
11 g_template_standard VARCHAR2(1000);
12 g_template_bucket VARCHAR2(1000);
13 g_template_total VARCHAR2(1000);
14 g_template_total_bucket VARCHAR2(1000);
15
16 g_rtn VARCHAR2(30) := '
17 ';
18
19 /******************************************************************************/
20 /* Package Design */
21 /* ============== */
22 /* */
23 /* [Search for SELECT|FROM|WHERE|BIND to find procedures and functions that */
24 /* impact the respective parts of the SQL returned] */
25 /* */
26 /* SELECT */
27 /* ------ */
28 /* For details of the SELECT column list see package header */
29 /* */
30 /* Note: Snapshot MVs have a different column format and are handled by */
31 /* a different set of functions */
32 /* */
33 /* All columns have one of a few standard formats. Global variables store */
34 /* templates for these formats with tags in for swapping in and out parts */
35 /* of the template (such as measure column, bucket column, bucket value) */
36 /* */
37 /* The columns in the select clause are controlled by various fields in the */
38 /* input parameter record p_wrkfc_params: */
39 /* - include_hdc: headcount measures will be added to SELECT columns */
40 /* - include_sal: salary measures will be added to SELECT columns */
41 /* - include_low: length of work measures will be added to SELECT columns */
42 /* */
43 /* All selected measure columns will be sampled at the effective date. */
44 /* Additionally the following fields in the same input parameter record */
45 /* control sampling at other dates: */
46 /* - include_comp: all measures are sampled at comparison period end date */
47 /* - include_start: all affected measures are sampled at period start dates */
48 /* */
49 /* Currently only headcount is affected by include_start. */
50 /* */
51 /* If a bucket dimension is specified then all measures will be sampled for */
52 /* each date for each bucket value (in addition to the values across all */
53 /* buckets). */
54 /* - bucket_dim: all measures are sampled for all buckets of dimension */
55 /* */
56 /* FROM/WHERE */
57 /* ---------- */
58 /* The FROM and WHERE clauses are separate depending on whether or not */
59 /* the report is view by manager. */
60 /* */
61 /* The fact table is chosen based on the parameters selected in the PMV */
62 /* report in the function set_fact_table. If snapshotting is available the */
63 /* corresponding snapshot fact will be selected. */
64 /* */
65 /* The parameters selected in the PMV report are analysed in the function */
66 /* analyze_parameters. A condition is added to the WHERE clause for each */
67 /* PMV report parameter that is set. */
68 /* */
69 /* VIEW BY */
70 /* ------- */
71 /* The view by grouping is controlled by the parameter passed in by PMV. If */
72 /* view by manager is selected then an additional level of the supervisor */
73 /* hierarchy is brought in so that the result set is grouped by the top */
74 /* manager's direct reports UNLESS in the input parameter record */
75 /* p_wrkfc_params the following field is set: */
76 /* - kpi_mode: groups by top manager instead of their direct reports when */
77 /* view by of manager is selected */
78 /* */
79 /* Binds */
80 /* ----- */
81 /* Bind values are passed in using the p_bind_tab parameter. Depending on the */
82 /* bind format selected the corresponding bind strings are populated into the */
83 /* global g_binds table. Bind values are then substituted into the SQL from */
84 /* this global throughout the package. */
85 /* - bind_format: SQL (direct substitution) or PMV (run time substitution) */
86 /* */
87 /******************************************************************************/
88
89
90 /******************************************************************************/
91 /* Initialization of global variables - called once at package initialization */
92 /* */
93 /* Templates for the SELECT columns are set with tags to represent the parts */
94 /* which vary. */
95 /******************************************************************************/
96 PROCEDURE initialize_globals IS
97
98 BEGIN
99
100 /* Define generic select column */
101 g_template_standard :=
102 'SUM(CASE WHEN effective_date = <date_bind>
103 THEN <measure_column>
104 ELSE 0
105 END)';
106
107 /* Define generic bucket select column */
108 g_template_bucket :=
109 'SUM(CASE WHEN effective_date = <date_bind>
110 AND <bucket_column> = <bucket_id>
111 THEN <measure_column>
112 ELSE 0
113 END)';
114
115 /* Define generic total column */
116 g_template_total :=
117 'SUM(CASE WHEN effective_date = <date_bind>
118 AND direct_ind = 1
119 THEN <measure_column>
120 ELSE 0
121 END)';
122
123 /* Define generic total bucket column */
124 g_template_total_bucket :=
125 'SUM(CASE WHEN effective_date = <date_bind>
126 AND <bucket_column> = <bucket_id>
127 AND direct_ind = 1
128 THEN <measure_column>
129 ELSE 0
130 END)';
131
132 END initialize_globals;
133
134
135 /******************************************************************************/
136 /* Populates g_binds with the selected BIND format */
137 /******************************************************************************/
138 PROCEDURE populate_global_bind_table
139 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
140 p_bind_format IN VARCHAR2) IS
141
142 l_parameter_name VARCHAR2(100);
143
144 BEGIN
145
146 /* Initialize all parameters to be used */
147 g_binds := g_binds_reset;
148 g_binds('CURRENCY') := NULL;
149 g_binds('RATE_TYPE') := NULL;
150 g_binds('TIME_CURR_END_DATE') := NULL;
151 g_binds('TIME_COMP_END_DATE') := NULL;
152 g_binds('TIME_CURR_START_DATE') := NULL;
153 g_binds('TIME_COMP_START_DATE') := NULL;
154
155 l_parameter_name := p_bind_tab.FIRST;
156
157 WHILE (l_parameter_name IS NOT NULL) LOOP
158
159 IF (p_bind_format = 'SQL') THEN
160 g_binds(l_parameter_name) := p_bind_tab(l_parameter_name).sql_bind_string;
161 ELSIF (p_bind_format = 'PMV') THEN
162 g_binds(l_parameter_name) := p_bind_tab(l_parameter_name).pmv_bind_string;
163 ELSE
164 g_binds(l_parameter_name) := l_parameter_name;
165 END IF;
166
167 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
168
169 END LOOP;
170
171 END populate_global_bind_table;
172
173
174 /******************************************************************************/
175 /* Wraps the measure with the conversion function call */
176 /******************************************************************************/
177 FUNCTION add_conv_func(p_measure IN VARCHAR2)
178 RETURN VARCHAR2 IS
179
180 BEGIN
181
182 RETURN
183 'hri_oltp_view_currency.convert_currency_amount' || g_rtn ||
184 ' (wrkfc.anl_slry_currency' || g_rtn ||
185 ' ,' || g_binds('CURRENCY') || g_rtn ||
186 ' ,' || g_binds('TIME_CURR_END_DATE') || g_rtn ||
187 ' ,' || p_measure || g_rtn ||
188 ' ,' || g_binds('RATE_TYPE') || ')';
189
190 END add_conv_func;
191
192 /******************************************************************************/
193 /* Sets up a string with a list of dates to sample the fact. */
194 /* */
195 /* The end date of the current period is always sampled. Additional dates are */
196 /* sampled depending on the value of the following parameters: */
197 /* p_wrkfc_params.include_comp - Include the comparison period */
198 /* p_wrkfc_params.include_start - Sample the start dates of each period */
199 /******************************************************************************/
200 PROCEDURE set_date_list
201 (p_wrkfc_params IN wrkfc_fact_param_type,
202 p_date_list OUT NOCOPY VARCHAR2) IS
203
204 BEGIN
205
206 /* Always sample the effective date */
207 p_date_list := g_binds('TIME_CURR_END_DATE');
208
209 /* Set date list for sampling workforce values */
210 IF (p_wrkfc_params.include_comp = 'Y') THEN
211 p_date_list := p_date_list || ', ' || g_binds('TIME_COMP_END_DATE');
212 IF (p_wrkfc_params.include_start = 'Y') THEN
213 p_date_list := p_date_list || ', ' || g_binds('TIME_CURR_START_DATE') || ' - 1';
214 p_date_list := p_date_list || ', ' || g_binds('TIME_COMP_START_DATE') || ' - 1';
215 END IF;
216 ELSIF (p_wrkfc_params.include_start = 'Y') THEN
217 p_date_list := p_date_list || ', ' || g_binds('TIME_CURR_START_DATE') || ' - 1';
218 END IF;
219
220 END set_date_list;
221
222
223 /******************************************************************************/
224 /* For every report parameter that is set, a condition is added to the WHERE */
225 /* clause. */
226 /* */
227 /* Also to help with deciding which fact table to use in the FROM clause, a */
228 /* count is kept of the number of parameters that are set. If only one */
229 /* parameter is set the name of that parameter is returned. This helps select */
230 /* the most efficient fact to retrieve the data from. */
231 /******************************************************************************/
232 PROCEDURE analyze_parameters
233 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
234 p_fact_conditions OUT NOCOPY VARCHAR2,
235 p_parameter_count OUT NOCOPY PLS_INTEGER,
236 p_single_param OUT NOCOPY VARCHAR2) IS
237
238 l_single_param VARCHAR2(100);
239 l_parameter_name VARCHAR2(100);
240
241 BEGIN
242
243 /* Initialize parameter count */
244 p_parameter_count := 0;
248
245
246 /* Loop through parameters that have been set */
247 l_parameter_name := p_bind_tab.FIRST;
249 WHILE (l_parameter_name IS NOT NULL) LOOP
250 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
251 l_parameter_name = 'GEOGRAPHY+AREA' OR
252 l_parameter_name = 'JOB+JOB_FAMILY' OR
253 l_parameter_name = 'JOB+JOB_FUNCTION' OR
254 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
255 l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
256 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X') THEN
257
258 /* Dynamically set conditions for parameter */
259 p_fact_conditions := p_fact_conditions || g_rtn ||
260 'AND wrkfc.' ||
261 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
262 (l_parameter_name).fact_viewby_col ||
263 ' IN (' || g_binds(l_parameter_name) || ')';
264
265 /* Keep count of parameters set and last parameter used */
266 /* Do not count person type as this is a global parameter */
267 IF (l_parameter_name <> 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
268 p_parameter_count := p_parameter_count + 1;
269 l_single_param := l_parameter_name;
270 END IF;
271
272 END IF;
273
274 /* Move to next parameter */
275 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
276 END LOOP;
277
278 /* Only pass back parameter name if there is only one parameter */
279 IF (p_parameter_count = 1) THEN
280 p_single_param := l_single_param;
281 END IF;
282
283 END analyze_parameters;
284
285
286 /******************************************************************************/
287 /* Decide which fact to use in the main FROM clause based on */
288 /* - number of parameters applied */
289 /* - viewby */
290 /* - buckets */
291 /* - whether a snapshot fact table is available */
292 /* */
293 /* If a fact table is selected that does not have a snapshot available then */
294 /* p_use_snapshot is set accordingly */
295 /******************************************************************************/
296 PROCEDURE set_fact_table
297 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
298 p_bucket_dim IN VARCHAR2,
299 p_include_sal IN VARCHAR2,
300 p_parameter_count IN PLS_INTEGER,
301 p_single_param IN VARCHAR2,
302 p_use_snapshot IN OUT NOCOPY BOOLEAN,
303 p_fact_table OUT NOCOPY VARCHAR2) IS
304
305 /* Fact for headcount/low only for the supervisor dimension level */
306 l_wrkfc_sup_table VARCHAR2(30);
307
308 /* Fact corresponding to the single parameter dimension level */
309 l_wrkfc_prm_table VARCHAR2(30);
310
311 /* Fact corresponding to the view by dimension level */
312 l_wrkfc_vby_table VARCHAR2(30);
313
314 /* Fact corresponding to the bucket dimension level */
315 l_wrkfc_bkt_table VARCHAR2(30);
316
317 /* Fact to use if more than one dimension is required */
318 l_wrkfc_pvt_table VARCHAR2(30);
319
320 /* Return variables */
321 l_fact_table VARCHAR2(30);
322
323 /* Bucket dimension */
324 l_bucket_dim VARCHAR2(240);
325
326 BEGIN
327
328 /* If p_bucket_dim is person type discount it for determining the */
329 /* fact table to use since person type is on every fact */
330 IF (p_bucket_dim = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
331 l_bucket_dim := NULL;
332 ELSE
333 l_bucket_dim := p_bucket_dim;
334 END IF;
335
336 /* Check whether a snapshot is available for the given top manager if called */
337 /* for the first time */
338 IF (p_use_snapshot IS NULL) THEN
339 p_use_snapshot := hri_oltp_pmv_util_snpsht.use_wrkfc_snpsht_for_mgr
340 (p_supervisor_id => p_parameter_rec.peo_supervisor_id,
341 p_effective_date => p_parameter_rec.time_curr_end_date);
342
343 END IF;
344
345 /*----------------------------------------------------------------------------*/
346 /* Set up the local variables with the following fact tables: */
347 /* l_wrkfc_sup_table: Supervisor level fact without currency in grain */
348 /* l_wrkfc_prm_table: Fact corresponding to the single parameter level */
349 /* l_wrkfc_bkt_table: Fact corresponding to the bucket dimension level */
350 /* l_wrkfc_vby_table: Fact corresponding to the view by dimension level */
351 /* l_wrkfc_pvt_table: Fact containing all dimension levels */
352 /* */
353 /* For example, if the following PMV parameters have been passed in: */
354 /* VIEW BY: GEOGRAPHY+COUNTRY */
355 /* JOB+JOB_FAMILY: DEVELOPMENT (single parameter set) */
356 /* */
357 /* and the following control parameters are: */
358 /* bucket_dim: HRI_LOW+HRI_LOW_BAND_X */
359 /* p_use_snapshot: N */
360 /* */
361 /* Then the local variables would be set as follows: */
362 /* l_wrkfc_sup_table: HRI_MDP_SUP_WCNT_SUP_MV */
363 /* l_wrkfc_prm_table: HRI_MDP_SUP_WRKFC_JFM_MV */
364 /* l_wrkfc_bkt_table: HRI_MDP_SUP_WRKFC_E_MV */
368 /*----------------------------------------------------------------------------*/
365 /* l_wrkfc_vby_table: HRI_MDP_SUP_WRKFC_CTR_MV */
366 /* l_wrkfc_pvt_table: HRI_MDP_SUP_WRKFC_CJER_MV */
367 /* */
369
370 /* Set the local variables for which fact table to use based on whether */
371 /* snapshots are available for the given manager or not */
372 IF (p_use_snapshot) THEN
373 IF (p_single_param IS NOT NULL) THEN
374 l_wrkfc_prm_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
375 (p_single_param).sup_lvl_wrkfc_mv_snp;
376 END IF;
377 l_wrkfc_sup_table := 'hri_mds_sup_wmv_sup_mv';
378 l_wrkfc_vby_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
379 (p_parameter_rec.view_by).sup_lvl_wrkfc_mv_snp;
380 l_wrkfc_pvt_table := 'hri_mdp_sup_wrkfc_cjer_mv';
381
382 IF (l_bucket_dim IS NOT NULL) THEN
383 l_wrkfc_bkt_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
384 (l_bucket_dim).sup_lvl_wrkfc_mv_snp;
385 END IF;
386 ELSE
387 IF (p_single_param IS NOT NULL) THEN
388 l_wrkfc_prm_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
389 (p_single_param).sup_lvl_wrkfc_mv_name;
390 END IF;
391 l_wrkfc_sup_table := 'hri_mdp_sup_wcnt_sup_mv';
392 l_wrkfc_vby_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
393 (p_parameter_rec.view_by).sup_lvl_wrkfc_mv_name;
394 l_wrkfc_pvt_table := 'hri_mdp_sup_wrkfc_cjer_mv';
395
396 IF (l_bucket_dim IS NOT NULL) THEN
397 l_wrkfc_bkt_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
398 (l_bucket_dim).sup_lvl_wrkfc_mv_name;
399 END IF;
400 END IF;
401
402 /*----------------------------------------------------------------------------*/
403 /* Decide which of the fact tables to return. */
404 /* */
405 /* The logic goes as follows: */
406 /* */
407 /* Parameter Count: 0 */
408 /* ------------------ */
409 /* 1) Check whether the supervisor level fact without currency can be used */
410 /* (no parameters, no buckets, no salary, view by manager) */
411 /* */
412 /* 2) Check the fact associated with the view by dimension level */
413 /* (no parameters, no bucket set) */
414 /* */
415 /* 3) Check the fact associated with the bucket dimension level */
416 /* (no parameters, bucket set, view by manager) */
417 /* */
418 /* 4) Use the pivot fact if bucket is set and view by is not manager */
419 /* (no parameters, bucket set, view by not manager) */
420 /* */
421 /* Parameter Count: > 0 */
422 /* -------------------- */
423 /* */
424 /* 5) Check the fact associated with the single parameter */
425 /* (one parameter, no bucket set, view by manager) */
426 /* */
427 /* 6) Use the pivot fact if one or more parameters and 5) is not met */
428 /* */
429 /*----------------------------------------------------------------------------*/
430
431 /* If no parameters are supplied use the viewby or bucket dimension table */
432 IF (p_parameter_count = 0) THEN
433 IF (l_bucket_dim IS NULL) THEN
434 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
435 p_include_sal = 'N') THEN
436 /* 1) no parameters, no buckets, no salary, view by manager */
437 l_fact_table := l_wrkfc_sup_table;
438 ELSE
439 /* 2) no parameters, no bucket set */
440 l_fact_table := l_wrkfc_vby_table;
441 END IF;
442 ELSIF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
443 /* 3) no parameters, bucket set, view by manager */
444 l_fact_table := l_wrkfc_bkt_table;
445 ELSE
446 /* 4) no parameters, bucket set, view by not manager */
447 l_fact_table := l_wrkfc_pvt_table;
448
449 /* Since the pivot table does not have the snapshot option */
450 p_use_snapshot := FALSE;
451 END IF;
452
453 /* If parameters are supplied from a single table, viewby is person and no */
454 /* bucket dimension is selected then use the parameter table */
455 ELSIF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
456 l_bucket_dim IS NULL AND
457 p_parameter_count = 1) THEN
458 /* 5) one parameter, no bucket set, view by manager */
459 l_fact_table := l_wrkfc_prm_table;
460
461 /* Otherwise use the lowest level table with all dimensions */
462 ELSE
463 /* 6) No other options */
464 l_fact_table := l_wrkfc_pvt_table;
465
466 /* Since the pivot table does not have the snapshot option */
467 p_use_snapshot := FALSE;
468
469 END IF;
470
471 /* Not all snapshots are available, so check that a table has been found */
472 IF (p_use_snapshot AND
473 l_fact_table IS NULL) THEN
474 /* No snapshot found - turn snapshots off and retry */
478 set_fact_table
475 p_use_snapshot := FALSE;
476
477 /* Call procedure again without using snapshots */
479 (p_parameter_rec => p_parameter_rec,
480 p_bucket_dim => l_bucket_dim,
481 p_include_sal => p_include_sal,
482 p_parameter_count => p_parameter_count,
483 p_single_param => p_single_param,
484 p_use_snapshot => p_use_snapshot,
485 p_fact_table => l_fact_table);
486
487 END IF;
488
489 /* Set the fact table */
490 p_fact_table := l_fact_table;
491
492 END set_fact_table;
493
494
495 /******************************************************************************/
496 /* Replaces tags in a SELECT column template and formats it with an alias */
497 /******************************************************************************/
498 FUNCTION format_column(p_column_string IN VARCHAR2,
499 p_date_bind IN VARCHAR2,
500 p_bucket_id IN VARCHAR2,
501 p_column_alias IN VARCHAR2)
502 RETURN VARCHAR2 IS
503
504 l_column_string VARCHAR2(1000);
505
506 BEGIN
507
508 /* Replace the bucket identifier */
509 l_column_string := REPLACE(p_column_string, '<bucket_id>', p_bucket_id);
510
511 /* Replace the date bind */
512 l_column_string := REPLACE(l_column_string, '<date_bind>', p_date_bind);
513
514 /* Format the column string */
515 l_column_string := ',' || l_column_string || ' ' || p_column_alias || g_rtn;
516
517 RETURN l_column_string;
518
519 END format_column;
520
521
522 /******************************************************************************/
523 /* This function returns a list of columns to be added to the SELECT clause */
524 /* for a given measure. The input fields contain the templates to use for */
525 /* the measure SELECT columns and various control fields. */
526 /* */
527 /* The following fields control sampling across different dates */
528 /* - include_comp: the measure is sampled at comparison period end date */
529 /* - include_start: the measure is sampled at period start dates */
530 /* */
531 /* If a bucket dimension is specified then all measures will be sampled for */
532 /* each date for each bucket value (in addition to the values across all */
533 /* buckets). */
534 /* - bucket_dim: the measures is sampled for all buckets of dimension */
535 /* */
536 /******************************************************************************/
537 FUNCTION build_columns
538 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
539 p_wrkfc_total IN VARCHAR2,
540 p_include_comp IN VARCHAR2,
541 p_include_start IN VARCHAR2,
542 p_bucket_dim IN VARCHAR2,
543 p_select_template IN VARCHAR2,
544 p_bucket_template IN VARCHAR2,
545 p_measure_alias IN VARCHAR2)
546 RETURN VARCHAR2 IS
547
548 l_column_list VARCHAR2(5000);
549 l_bucket_condition VARCHAR2(1000);
550 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
551
552 /* Column Templates */
553 l_select_column VARCHAR2(1000);
554 l_bucket_column VARCHAR2(1000);
555
556 BEGIN
557
558 /* Replace the measure tags in the column templates */
559 l_select_column := REPLACE(p_select_template, '<measure_column>', p_measure_alias);
560 l_bucket_column := REPLACE(p_bucket_template, '<measure_column>', p_measure_alias);
561
562 /* Check and add columns for current period start */
563 IF (p_include_start = 'Y') THEN
564 l_column_list := l_column_list || format_column
565 (p_column_string => l_select_column
566 ,p_date_bind => g_binds('TIME_CURR_START_DATE') || ' - 1'
567 ,p_bucket_id => NULL
568 ,p_column_alias => 'curr_' || p_measure_alias || '_start');
569 END IF;
570
571 /* Check and add columns for current period end */
572 IF (p_wrkfc_total = 'N') THEN
573 l_column_list := l_column_list || format_column
574 (p_column_string => l_select_column
575 ,p_date_bind => g_binds('TIME_CURR_END_DATE')
576 ,p_bucket_id => NULL
577 ,p_column_alias => 'curr_' || p_measure_alias || '_end');
578 END IF;
579
580 /* Check and add columns for comparison period start */
581 IF (p_include_comp = 'Y' AND
582 p_include_start = 'Y') THEN
583 l_column_list := l_column_list || format_column
584 (p_column_string => l_select_column
585 ,p_date_bind => g_binds('TIME_COMP_START_DATE') || ' - 1'
586 ,p_bucket_id => NULL
587 ,p_column_alias => 'comp_' || p_measure_alias || '_start');
588 END IF;
589
590 /* Check and add columns for comparison period end */
591 IF (p_include_comp = 'Y') THEN
592 l_column_list := l_column_list || format_column
593 (p_column_string => l_select_column
594 ,p_date_bind => g_binds('TIME_COMP_END_DATE')
595 ,p_bucket_id => NULL
596 ,p_column_alias => 'comp_' || p_measure_alias || '_end');
597 END IF;
598
599 /* Add columns for the bucket dimension values if required */
600 IF (p_bucket_dim IS NOT NULL) THEN
601
602 /* Get a pl/sql table containing the bucket ids for the given */
603 /* bucket dimension */
604 IF (p_bucket_dim = 'HRI_LOW+HRI_LOW_BAND_X') THEN
605 hri_mtdt_dim_lvl.set_low_band_buckets(p_parameter_rec.wkth_wktyp_sk_fk);
606 l_bucket_tab := hri_mtdt_dim_lvl.g_low_band_buckets_tab;
610 l_bucket_tab := hri_mtdt_dim_lvl.g_primary_job_role_tab;
607 ELSIF (p_bucket_dim = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X') THEN
608 l_bucket_tab := hri_mtdt_dim_lvl.g_prfmnc_band_buckets_tab;
609 ELSIF (p_bucket_dim = 'JOB+PRIMARY_JOB_ROLE') THEN
611 ELSIF (p_bucket_dim = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
612 l_bucket_tab := hri_mtdt_dim_lvl.g_wkth_wktyp_tab;
613 END IF;
614
615 /* Loop through bucket ids to add required columns */
616 FOR i IN l_bucket_tab.FIRST..l_bucket_tab.LAST LOOP
617
618 /* Add the current period column for the bucket */
619 IF (p_wrkfc_total = 'N') THEN
620 l_column_list := l_column_list || format_column
621 (p_column_string => l_bucket_column
622 ,p_date_bind => g_binds('TIME_CURR_END_DATE')
623 ,p_bucket_id => l_bucket_tab(i).bucket_id_string
624 ,p_column_alias => 'curr_' || p_measure_alias || '_' ||
625 l_bucket_tab(i).bucket_name);
626 END IF;
627
628 /* Add the comparison period column for the bucket */
629 IF (p_include_comp = 'Y') THEN
630 l_column_list := l_column_list || format_column
631 (p_column_string => l_bucket_column
632 ,p_date_bind => g_binds('TIME_COMP_END_DATE')
633 ,p_bucket_id => l_bucket_tab(i).bucket_id_string
634 ,p_column_alias => 'comp_' || p_measure_alias || '_' ||
635 l_bucket_tab(i).bucket_name);
636 END IF;
637
638 END LOOP;
639
640 END IF;
641
642 RETURN l_column_list;
643
644 END build_columns;
645
646
647 /******************************************************************************/
648 /* Returns a string containing columns to be added to the outer SELECT clause */
649 /* for the given measure. */
650 /******************************************************************************/
651 FUNCTION add_outer_measure_columns
652 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
653 p_wrkfc_params IN wrkfc_fact_param_type,
654 p_view_by_manager IN BOOLEAN,
655 p_sample_start_dates IN VARCHAR2,
656 p_measure_alias IN VARCHAR2)
657 RETURN VARCHAR2 IS
658
659 /* Column Templates */
660 l_template_bucket VARCHAR2(1000);
661 l_template_total VARCHAR2(1000);
662 l_template_total_bucket VARCHAR2(1000);
663
664 /* Whether to calculate measure value at start of period */
665 l_include_start VARCHAR2(30);
666
667 /* Return column list */
668 l_column_list VARCHAR2(10000);
669
670 BEGIN
671
672 /* Check for view by manager special case */
673 IF (p_view_by_manager) THEN
674 l_template_total_bucket := g_template_total_bucket;
675 l_template_total := g_template_total;
676 ELSE
677 l_template_total_bucket := g_template_bucket;
678 l_template_total := g_template_standard;
679 END IF;
680
681 /* Set bucket column if applicable */
682 IF (p_wrkfc_params.bucket_dim IS NOT NULL) THEN
683 l_template_bucket := REPLACE(g_template_bucket, '<bucket_column>',
684 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
685 (p_wrkfc_params.bucket_dim).fact_viewby_col);
686 l_template_total_bucket := REPLACE(l_template_total_bucket, '<bucket_column>',
687 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
688 (p_wrkfc_params.bucket_dim).fact_viewby_col);
689 END IF;
690
691 /* Set whether or not to sample the measure value at period start dates */
692 IF (p_sample_start_dates = 'Y') THEN
693 l_include_start := p_wrkfc_params.include_start;
694 ELSE
695 l_include_start := 'N';
696 END IF;
697
698 /* Add measure columns and total columns for the given measure */
699 l_column_list := build_columns
700 (p_parameter_rec => p_parameter_rec,
701 p_wrkfc_total => 'N',
702 p_include_comp => p_wrkfc_params.include_comp,
703 p_include_start => l_include_start,
704 p_bucket_dim => p_wrkfc_params.bucket_dim,
705 p_select_template => g_template_standard,
706 p_bucket_template => l_template_bucket,
707 p_measure_alias => p_measure_alias)
708 || build_columns
709 (p_parameter_rec => p_parameter_rec,
710 p_wrkfc_total => 'Y',
711 p_include_comp => p_wrkfc_params.include_comp,
712 p_include_start => l_include_start,
713 p_bucket_dim => p_wrkfc_params.bucket_dim,
714 p_select_template => l_template_total,
715 p_bucket_template => l_template_total_bucket,
716 p_measure_alias => 'total_' || p_measure_alias);
717
718 RETURN l_column_list;
719
720 END add_outer_measure_columns;
721
722
723 /******************************************************************************/
724 /* Returns the final SQL statement for the PMV report. */
725 /* */
726 /* The SQL returned is in the format: */
727 /* */
728 /* SELECT -- outer */
729 /* Grouping column (view by) */
730 /* Specific measure columns, including totals and */
731 /* sampling across different dates and buckets */
732 /* FROM */
733 /* (SELECT -- inner */
737 /* Fact table */
734 /* Raw measure columns, including function calls */
735 /* e.g. to convert currency */
736 /* FROM */
738 /* Time dimension */
739 /* WHERE */
740 /* Apply parameters corresponding to user selection */
741 /* in the PMV report */
742 /* Join to time dimension sampling all dates required */
743 /* ) */
744 /* GROUP BY */
745 /* Grouping column (view by) */
746 /* */
747 /* SELECT */
748 /* ====== */
749 /* Calls build_columns for each measure selected to build up the SELECT */
750 /* clause. */
751 /* */
752 /* For details of the SELECT column list see package header */
753 /* */
754 /* FROM/WHERE */
755 /* ========== */
756 /* Puts together the FROM/WHERE clauses depending on whether the view by */
757 /* manager special case is selected. */
758 /******************************************************************************/
759 FUNCTION build_sql
760 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
761 p_wrkfc_params IN wrkfc_fact_param_type,
762 p_fact_table IN VARCHAR2,
763 p_date_list IN VARCHAR2,
764 p_fact_conditions IN VARCHAR2)
765 RETURN VARCHAR2 IS
766
767 /* Whether to format the SQL for the view by manager special case */
768 l_view_by_manager BOOLEAN;
769
770 /* Dynamic SQL columns */
771 /***********************/
772 /* Select */
773 l_direct_ind VARCHAR2(100);
774 l_inner_col_list VARCHAR2(10000);
775 l_outer_col_list VARCHAR2(10000);
776
777 /* From / Where */
778 l_inner_from VARCHAR2(10000);
779
780 /* Measure columns */
781 l_hdc_col VARCHAR2(1000);
782 l_sal_col VARCHAR2(1000);
783 l_low_col VARCHAR2(1000);
784 l_total_hdc_col VARCHAR2(1000);
785 l_total_sal_col VARCHAR2(1000);
786 l_pasg_cnt_col VARCHAR2(1000);
787 l_total_low_col VARCHAR2(100);
788
789 /* Return string */
790 l_sql_string VARCHAR2(32767);
791
792 BEGIN
793
794 /******************************************************************************/
795 /* INITIALIZATION */
796 /******************/
797
798 /* Check for view by manager special case */
799 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
800 p_wrkfc_params.kpi_mode = 'N') THEN
801 l_view_by_manager := TRUE;
802 ELSE
803 l_view_by_manager := FALSE;
804 END IF;
805
806 /* Set total columns, which do not depend on the view by manager special case */
807 l_total_hdc_col := 'wrkfc.total_headcount';
808
809 l_total_sal_col :=
810 'hri_oltp_view_currency.convert_currency_amount' || g_rtn ||
811 ' (wrkfc.anl_slry_currency' || g_rtn ||
812 ' ,' || g_binds('CURRENCY') || g_rtn ||
813 ' ,' || g_binds('TIME_CURR_END_DATE') || g_rtn ||
814 ' ,wrkfc.total_anl_slry' || g_rtn ||
815 ' ,' || g_binds('RATE_TYPE') || ')';
816
817 l_total_low_col := 'wrkfc.total_primary_asg_pow + (wrkfc.total_primary_asg_cnt * ' ||
818 '(cal.id - wrkfc.effective_start_date))';
819
820 /* Set dynamic SQL based on view by (manager special case for non-KPI reports) */
821 IF (l_view_by_manager) THEN
822
823 /******************************************************************************/
824 /* VIEW BY MANAGER SPECIAL CASE */
825 /********************************/
826
827 l_direct_ind := '1 - suph.sub_relative_level';
828
829 l_hdc_col := 'DECODE(suph.sub_relative_level,' ||
830 ' 0, wrkfc.direct_headcount,' ||
831 ' wrkfc.total_headcount)';
832 l_sal_col := add_conv_func('DECODE(suph.sub_relative_level,' ||
833 ' 0, wrkfc.direct_anl_slry,' ||
834 'wrkfc.total_anl_slry)');
835
836 l_low_col := 'DECODE(suph.sub_relative_level,' || g_rtn ||
837 ' 0, wrkfc.direct_primary_asg_pow + (wrkfc.direct_primary_asg_cnt * ' ||
838 '(cal.id - wrkfc.effective_start_date)),' || g_rtn ||
839 ' wrkfc.total_primary_asg_pow + (wrkfc.total_primary_asg_cnt * ' ||
840 '(cal.id - wrkfc.effective_start_date)))';
841
842 /* FROM CLAUSE */
843 /***************/
844 l_inner_from :=
845 ' fii_time_day_v cal,
846 hri_cs_suph suph,
847 ' || p_fact_table || ' wrkfc
848 WHERE cal.id IN (' || p_date_list || ')
849 AND cal.id BETWEEN wrkfc.effective_start_date ' ||
850 'AND wrkfc.effective_end_date
851 AND ' || g_binds('TIME_CURR_END_DATE') || ' BETWEEN suph.effective_start_date ' ||
852 'AND suph.effective_end_date
856 AND suph.sub_relative_level <= 1' ||
853 AND suph.sup_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || '
854 AND suph.sub_person_id = wrkfc.supervisor_person_id
855 AND suph.sub_invalid_flag_code = ''N''
857 p_fact_conditions;
858
859 ELSE
860
861 /******************************************************************************/
862 /* GENERIC (OTHER VIEW BYs) */
863 /****************************/
864
865 l_direct_ind := '0';
866 l_hdc_col := 'wrkfc.total_headcount';
867 /* Added because of Bug 5461651 */
868 l_pasg_cnt_col := 'wrkfc.total_primary_asg_cnt';
869 l_sal_col :=
870 'hri_oltp_view_currency.convert_currency_amount' || g_rtn ||
871 ' (wrkfc.anl_slry_currency' || g_rtn ||
872 ' ,' || g_binds('CURRENCY') || g_rtn ||
873 ' ,' || g_binds('TIME_CURR_END_DATE') || g_rtn ||
874 ' ,wrkfc.total_anl_slry' || g_rtn ||
875 ' ,' || g_binds('RATE_TYPE') || ')';
876
877 l_low_col := 'wrkfc.total_primary_asg_pow + (wrkfc.total_primary_asg_cnt * ' ||
878 '(cal.id - wrkfc.effective_start_date))';
879
880 /* FROM CLAUSE */
881 /***************/
882 l_inner_from :=
883 ' fii_time_day_v cal,
884 ' || p_fact_table || ' wrkfc
885 WHERE cal.id IN (' || p_date_list || ')
886 AND cal.id BETWEEN wrkfc.effective_start_date ' ||
887 'AND wrkfc.effective_end_date
888 AND wrkfc.supervisor_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') ||
889 p_fact_conditions;
890
891 END IF;
892
893 /* SELECT CLAUSE */
894 /*****************/
895
896 /* Add bucket column to select clause if applicable */
897 IF (p_wrkfc_params.bucket_dim IS NOT NULL) THEN
898 l_inner_col_list := l_inner_col_list || ',' ||
899 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
900 (p_wrkfc_params.bucket_dim).fact_viewby_col || g_rtn;
901 END IF;
902
903 /* Build up SELECT column list */
904 IF (p_wrkfc_params.include_hdc = 'Y') THEN
905
906 l_inner_col_list := l_inner_col_list ||
907 ',' || l_hdc_col || ' hdc' || g_rtn ||
908 ',' || l_total_hdc_col || ' total_hdc' || g_rtn;
909
910 l_outer_col_list := l_outer_col_list ||
911 add_outer_measure_columns
912 (p_parameter_rec => p_parameter_rec,
913 p_wrkfc_params => p_wrkfc_params,
914 p_view_by_manager => l_view_by_manager,
915 p_sample_start_dates => 'Y',
916 p_measure_alias => 'hdc');
917 END IF;
918
919 /* Build up SELECT column list */
920 IF (p_wrkfc_params.include_sal = 'Y') THEN
921
922 l_inner_col_list := l_inner_col_list ||
923 ',' || l_sal_col || ' sal' || g_rtn ||
924 ',' || l_total_sal_col || ' total_sal' || g_rtn;
925
926 l_outer_col_list := l_outer_col_list ||
927 add_outer_measure_columns
928 (p_parameter_rec => p_parameter_rec,
929 p_wrkfc_params => p_wrkfc_params,
930 p_view_by_manager => l_view_by_manager,
931 p_sample_start_dates => 'N',
932 p_measure_alias => 'sal');
933
934 END IF;
935
936 /* Build up SELECT column list */
937 IF (p_wrkfc_params.include_low = 'Y') THEN
938
939 l_inner_col_list := l_inner_col_list ||
940 ',' || l_low_col || ' low' || g_rtn ||
941 ',' || l_total_low_col || ' total_low' || g_rtn;
942
943 l_outer_col_list := l_outer_col_list ||
944 add_outer_measure_columns
945 (p_parameter_rec => p_parameter_rec,
946 p_wrkfc_params => p_wrkfc_params,
947 p_view_by_manager => l_view_by_manager,
948 p_sample_start_dates => 'N',
949 p_measure_alias => 'low');
950
951 END IF;
952 /* Added because of Bug 5461651 */
953 /* Build up SELECT column list */
954
955 IF (p_wrkfc_params.include_pasg_cnt = 'Y') THEN
956
957 l_inner_col_list := l_inner_col_list ||
958 ',' || l_pasg_cnt_col || ' pasg_cnt' || g_rtn ||
959 ',' || l_pasg_cnt_col || ' total_pasg_cnt' || g_rtn;
960
961 l_outer_col_list := l_outer_col_list ||
962 add_outer_measure_columns
963 (p_parameter_rec => p_parameter_rec,
964 p_wrkfc_params => p_wrkfc_params,
965 p_view_by_manager => l_view_by_manager,
966 p_sample_start_dates => 'N',
967 p_measure_alias => 'pasg_cnt');
968 END IF;
969
970 /* BUILD UP SQL STATEMENT */
971 /**************************/
972
973 l_sql_string :=
974 'SELECT /*+ NO_MERGE */
975 vby_id
976 ,direct_ind' || g_rtn ||
977 /* Dynamically built column list */
978 l_outer_col_list ||
979 'FROM (
980 SELECT /*+ ORDERED INDEX(wrkfc) */
981 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
982 (p_parameter_rec.view_by).fact_viewby_col || ' vby_id
983 ,cal.id effective_date
984 ,' || l_direct_ind || ' direct_ind ' || g_rtn ||
985 l_inner_col_list ||
986 ' FROM ' || g_rtn ||
987 l_inner_from || '
988 )
989 GROUP BY
990 vby_id
991 ,direct_ind';
992
993 RETURN l_sql_string;
994
995 END build_sql;
996
997
998 /******************************************************************************/
999 /* This function returns a list of columns to be added to the SELECT clause */
1000 /* for a given measure using snapshot fact. Analgous to build_columns */
1001 /******************************************************************************/
1002 FUNCTION build_columns_snp
1003 (p_sample_start_dates IN VARCHAR2,
1004 p_include_comp IN VARCHAR2,
1005 p_measure_code IN VARCHAR2,
1009 p_bucket_template IN VARCHAR2,
1006 p_measure_alias IN VARCHAR2,
1007 p_column_template IN VARCHAR2,
1008 p_total_template IN VARCHAR2,
1010 p_bucket_dim IN VARCHAR2,
1011 p_bucket_tab IN hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype)
1012 RETURN VARCHAR2 IS
1013
1014 l_col_list VARCHAR2(10000);
1015
1016 BEGIN
1017
1018 /*****************/
1019 /* Total Columns */
1020 /*****************/
1021
1022 -- Add previous period end total
1023 l_col_list := l_col_list ||
1024 ',SUM(' || REPLACE(p_total_template,
1025 '<total_measure>', 'wrkfc.comp_total_' || p_measure_code || '_end') || ') ' ||
1026 'comp_total_' || p_measure_alias || '_end' || g_rtn;
1027
1028 -- Add current and previous period start totals
1029 IF (p_sample_start_dates = 'Y') THEN
1030 l_col_list := l_col_list ||
1031 ',SUM(' || REPLACE(p_total_template,
1032 '<total_measure>', 'wrkfc.comp_total_' || p_measure_code || '_start') || ') ' ||
1033 'comp_total_' || p_measure_alias || '_start
1034 ,SUM(' || REPLACE(p_total_template,
1035 '<total_measure>', 'wrkfc.curr_total_' || p_measure_code || '_start') || ') ' ||
1036 'curr_total_' || p_measure_alias || '_start' || g_rtn;
1037 END IF;
1038
1039 /*******************/
1040 /* Measure Columns */
1041 /*******************/
1042
1043 -- Add current and previous period end measures
1044 l_col_list := l_col_list ||
1045 ',SUM(' || REPLACE(REPLACE(p_column_template,
1046 '<total_measure>', 'wrkfc.curr_total_' || p_measure_code || '_end'),
1047 '<direct_measure>', 'wrkfc.curr_direct_' || p_measure_code || '_end') ||
1048 ') curr_' || p_measure_alias || '_end
1049 ,SUM(' || REPLACE(REPLACE(p_column_template,
1050 '<total_measure>', 'wrkfc.comp_total_' || p_measure_code || '_end'),
1051 '<direct_measure>', 'wrkfc.comp_direct_' || p_measure_code || '_end') ||
1052 ') comp_' || p_measure_alias || '_end' || g_rtn;
1053
1054 -- Add current and previous period start measures
1055 IF (p_sample_start_dates = 'Y') THEN
1056 l_col_list := l_col_list ||
1057 ',SUM(' || REPLACE(REPLACE(p_column_template,
1058 '<total_measure>', 'wrkfc.curr_total_' || p_measure_code || '_start'),
1059 '<direct_measure>', 'wrkfc.curr_direct_' || p_measure_code || '_start') ||
1060 ') curr_' || p_measure_alias || '_start
1061 ,SUM(' || REPLACE(REPLACE(p_column_template,
1062 '<total_measure>', 'wrkfc.comp_total_' || p_measure_code || '_start'),
1063 '<direct_measure>', 'wrkfc.comp_direct_' || p_measure_code || '_start') ||
1064 ') comp_' || p_measure_alias || '_start' || g_rtn;
1065 END IF;
1066
1067 /******************/
1068 /* Bucket Columns */
1069 /******************/
1070 IF (p_bucket_dim IS NOT NULL) THEN
1071
1072 /* Loop through bucket ids to add required columns */
1073 FOR i IN p_bucket_tab.FIRST..p_bucket_tab.LAST LOOP
1074
1075 l_col_list := l_col_list ||
1076 ',SUM(' ||
1077 REPLACE(REPLACE(REPLACE(p_bucket_template,
1078 '<total_measure>', 'wrkfc.curr_total_' || p_measure_code || '_end'),
1079 '<direct_measure>', 'wrkfc.curr_direct_' || p_measure_code || '_end'),
1080 '<bucket_id>', p_bucket_tab(i).bucket_id_string) ||
1081 ') curr_' || p_measure_alias || '_' || p_bucket_tab(i).bucket_name || g_rtn;
1082
1083 /* Add the comparison period column for the bucket */
1084 IF (p_include_comp = 'Y') THEN
1085
1086 l_col_list := l_col_list ||
1087 ',SUM(' ||
1088 REPLACE(REPLACE(REPLACE(p_bucket_template,
1089 '<total_measure>', 'wrkfc.comp_total_' || p_measure_code || '_end'),
1090 '<direct_measure>', 'wrkfc.comp_direct_' || p_measure_code || '_end'),
1091 '<bucket_id>', p_bucket_tab(i).bucket_id_string) ||
1092 ') comp_' || p_measure_alias || '_' || p_bucket_tab(i).bucket_name || g_rtn;
1093
1094 END IF;
1095
1096 END LOOP;
1097
1098 END IF;
1099
1100 RETURN l_col_list;
1101
1102 END build_columns_snp;
1103
1104 /******************************************************************************/
1105 /* Returns the final SQL statement for the PMV report. */
1106 /* */
1107 /* The SQL returned is in the format: */
1108 /* */
1109 /* SELECT */
1110 /* Grouping column (view by) */
1111 /* Specific measure columns, including totals and */
1112 /* sampling across different dates and buckets */
1113 /* FROM */
1114 /* Fact table */
1115 /* Time dimension */
1116 /* Supervisor table (if view by manager and non-kpi report) */
1117 /* WHERE */
1118 /* Apply parameters corresponding to user selection */
1119 /* in the PMV report */
1120 /* Join to time dimension sampling all dates required */
1121 /* GROUP BY */
1122 /* Grouping column (view by) */
1123 /* */
1124 /* SELECT */
1128 /* For details of the SELECT column list see package header */
1125 /* ====== */
1126 /* Adds columns for each measure selected to build up the SELECT clause */
1127 /* */
1129 /* */
1130 /* FROM/WHERE */
1131 /* ========== */
1132 /* Puts together the FROM/WHERE clauses depending on whether the view by */
1133 /* manager special case is selected. */
1134 /******************************************************************************/
1135 FUNCTION build_sql_snp
1136 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
1137 p_wrkfc_params IN wrkfc_fact_param_type,
1138 p_fact_table IN VARCHAR2,
1139 p_date_list IN VARCHAR2,
1140 p_fact_conditions IN VARCHAR2)
1141 RETURN VARCHAR2 IS
1142
1143 /* Whether to format the SQL for the view by manager special case */
1144 l_view_by_manager BOOLEAN;
1145
1146 /* Table of bucket values */
1147 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
1148
1149 /* Column templates */
1150 l_column_template VARCHAR2(1000);
1151 l_total_template VARCHAR2(1000);
1152 l_bucket_template VARCHAR2(1000);
1153
1154 /* Dynamic SQL columns */
1155 /***********************/
1156 /* Select */
1157 l_direct_ind VARCHAR2(100);
1158 l_col_list VARCHAR2(10000);
1159 l_from_clause VARCHAR2(100);
1160 l_where_clause VARCHAR2(10000);
1161
1162 /* Return string */
1163 l_sql_string VARCHAR2(32767);
1164
1165 BEGIN
1166
1167 /******************************************************************************/
1168 /* INITIALIZATION */
1169 /******************/
1170
1171 /* Check for view by manager special case */
1172 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
1173 p_wrkfc_params.kpi_mode = 'N') THEN
1174 l_view_by_manager := TRUE;
1175 l_column_template :=
1176 'DECODE(suph.sub_relative_level,
1177 0, <direct_measure>,
1178 <total_measure>)';
1179 l_total_template :=
1180 'DECODE(suph.sub_relative_level,
1181 0, <total_measure>,
1182 0)';
1183
1184 ELSE
1185 l_view_by_manager := FALSE;
1186 l_column_template := '<total_measure>';
1187 l_total_template := '<total_measure>';
1188 END IF;
1189
1190 /* Set up a table of bucket ids if a bucket dimension is used */
1191 IF (p_wrkfc_params.bucket_dim IS NOT NULL) THEN
1192
1193 /* Get a pl/sql table containing the bucket ids for the given */
1194 /* bucket dimension */
1195 IF (p_wrkfc_params.bucket_dim = 'HRI_LOW+HRI_LOW_BAND_X') THEN
1196 hri_mtdt_dim_lvl.set_low_band_buckets(p_parameter_rec.wkth_wktyp_sk_fk);
1197 l_bucket_tab := hri_mtdt_dim_lvl.g_low_band_buckets_tab;
1198 ELSIF (p_wrkfc_params.bucket_dim = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X') THEN
1199 l_bucket_tab := hri_mtdt_dim_lvl.g_prfmnc_band_buckets_tab;
1200 ELSIF (p_wrkfc_params.bucket_dim = 'JOB+PRIMARY_JOB_ROLE') THEN
1201 l_bucket_tab := hri_mtdt_dim_lvl.g_primary_job_role_tab;
1202 ELSIF (p_wrkfc_params.bucket_dim = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
1203 l_bucket_tab := hri_mtdt_dim_lvl.g_wkth_wktyp_tab;
1204 END IF;
1205
1206 l_bucket_template :=
1207 'CASE WHEN ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
1208 (p_wrkfc_params.bucket_dim).fact_viewby_col || ' = <bucket_id>
1209 THEN ' || l_column_template || '
1210 ELSE 0
1211 END';
1212
1213 END IF;
1214
1215 /* Set dynamic SQL based on view by (manager special case for non-KPI reports) */
1216 IF (l_view_by_manager) THEN
1217
1218 /******************************************************************************/
1219 /* VIEW BY MANAGER SPECIAL CASE */
1220 /********************************/
1221
1222 l_direct_ind := '1 - suph.sub_relative_level';
1223
1224 l_from_clause :=
1225 ' hri_cs_suph suph
1226 ,' || p_fact_table || ' wrkfc' || g_rtn;
1227
1228 l_where_clause :=
1229 'AND suph.sup_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || '
1230 AND suph.sub_person_id = wrkfc.supervisor_person_id
1231 AND wrkfc.effective_date = ' || g_binds('TIME_CURR_END_DATE') || '
1232 AND ' || g_binds('TIME_CURR_END_DATE') || ' BETWEEN suph.effective_start_date ' ||
1233 'AND suph.effective_end_date
1234 AND suph.sub_invalid_flag_code = ''N''
1235 AND suph.sub_relative_level <= 1' || g_rtn;
1236
1237 ELSE
1238
1239 /******************************************************************************/
1240 /* GENERIC (OTHER VIEW BYs) */
1241 /****************************/
1242
1243 l_direct_ind := '0';
1244
1245 l_from_clause := ' ' || p_fact_table || ' wrkfc' || g_rtn;
1246
1247 l_where_clause :=
1248 'AND wrkfc.supervisor_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || g_rtn;
1249
1250 END IF;
1251
1252 /* SELECT CLAUSE */
1253 /*****************/
1254
1255 /* Build up SELECT column list */
1256 IF (p_wrkfc_params.include_hdc = 'Y') THEN
1257
1258 l_col_list := l_col_list || build_columns_snp
1259 (p_sample_start_dates => 'Y',
1260 p_include_comp => p_wrkfc_params.include_comp,
1261 p_measure_code => 'hdc',
1262 p_measure_alias => 'hdc',
1263 p_column_template => l_column_template,
1267 p_bucket_tab => l_bucket_tab);
1264 p_total_template => l_total_template,
1265 p_bucket_template => l_bucket_template,
1266 p_bucket_dim => p_wrkfc_params.bucket_dim,
1268 END IF;
1269
1270 /* Build up SELECT column list */
1271 IF (p_wrkfc_params.include_low = 'Y') THEN
1272 l_col_list := l_col_list || build_columns_snp
1276 p_measure_alias => 'low',
1273 (p_sample_start_dates => 'N',
1274 p_include_comp => p_wrkfc_params.include_comp,
1275 p_measure_code => 'pow',
1277 p_column_template => l_column_template,
1278 p_total_template => l_total_template,
1279 p_bucket_template => l_bucket_template,
1280 p_bucket_dim => p_wrkfc_params.bucket_dim,
1281 p_bucket_tab => l_bucket_tab);
1282 END IF;
1283
1284 /* Build up SELECT column list */
1285 IF (p_wrkfc_params.include_sal = 'Y') THEN
1286 l_col_list := l_col_list || build_columns_snp
1287 (p_sample_start_dates => 'N',
1288 p_include_comp => p_wrkfc_params.include_comp,
1289 p_measure_code => 'anl_slry',
1290 p_measure_alias => 'sal',
1291 p_column_template => add_conv_func(l_column_template),
1292 p_total_template => add_conv_func(l_total_template),
1293 p_bucket_template => add_conv_func(l_bucket_template),
1294 p_bucket_dim => p_wrkfc_params.bucket_dim,
1295 p_bucket_tab => l_bucket_tab);
1296 END IF;
1297 /* Added because of Bug 5461651 */
1298 /* Build up SELECT column list */
1299 IF (p_wrkfc_params.include_pasg_cnt = 'Y') THEN
1300 l_col_list := l_col_list || build_columns_snp
1301 (p_sample_start_dates => 'N',
1302 p_include_comp => p_wrkfc_params.include_comp,
1303 p_measure_code => 'pasg_cnt',
1304 p_measure_alias => 'pasg_cnt',
1305 p_column_template => l_column_template,
1306 p_total_template => l_total_template,
1307 p_bucket_template => l_bucket_template,
1308 p_bucket_dim => p_wrkfc_params.bucket_dim,
1309 p_bucket_tab => l_bucket_tab);
1310 END IF;
1311 /* BUILD UP SQL STATEMENT */
1312 /**************************/
1313
1314 l_sql_string :=
1315 'SELECT /*+ NO_MERGE ORDERED INDEX(wrkfc) */
1316 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
1317 (p_parameter_rec.view_by).fact_viewby_col || ' vby_id
1318 ,' || l_direct_ind || ' direct_ind' || g_rtn ||
1319 /* Dynamically built column list */
1320 l_col_list ||
1321 'FROM' || g_rtn ||
1322 l_from_clause ||
1323 'WHERE wrkfc.effective_date = ' || g_binds('TIME_CURR_END_DATE') || '
1324 AND wrkfc.period_type = &PERIOD_TYPE
1325 AND wrkfc.comparison_type = &TIME_COMPARISON_TYPE' || g_rtn ||
1326 l_where_clause ||
1327 p_fact_conditions ||
1328 'GROUP BY
1329 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
1330 (p_parameter_rec.view_by).fact_viewby_col || '
1331 ,' || l_direct_ind;
1332
1333 RETURN l_sql_string;
1334
1335 END build_sql_snp;
1336
1337 /******************************************************************************/
1338 /* Main entry point, takes PMV parameters and SQL control parameters */
1339 /* Returns the SQL statement for the PMV report. */
1340 /******************************************************************************/
1341 FUNCTION get_sql
1342 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
1343 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
1344 p_wrkfc_params IN wrkfc_fact_param_type)
1345 RETURN VARCHAR2 IS
1346
1347 l_date_list VARCHAR2(3000);
1348 l_return_sql VARCHAR2(32767);
1349 l_parameter_count PLS_INTEGER;
1350 l_single_param VARCHAR2(100);
1351 l_fact_table VARCHAR2(30);
1352 l_fact_conditions VARCHAR2(1000);
1353 l_use_snapshot BOOLEAN;
1354
1355 BEGIN
1356
1357 /* Populate a global record with the PMV context in the given bind format */
1358 populate_global_bind_table
1359 (p_bind_tab => p_bind_tab,
1360 p_bind_format => p_wrkfc_params.bind_format);
1361
1362 /* Get list of dates to process (current/previous start/end) */
1363 set_date_list
1364 (p_wrkfc_params => p_wrkfc_params,
1365 p_date_list => l_date_list);
1366
1367 /* Put dimension level parameter binds into a string to add to WHERE clause */
1368 /* Return information about the parameters that are set to help decide */
1369 /* which fact/function to use */
1370 analyze_parameters
1371 (p_bind_tab => p_bind_tab,
1372 p_fact_conditions => l_fact_conditions,
1373 p_parameter_count => l_parameter_count,
1374 p_single_param => l_single_param);
1375
1376 /* Decide which fact table to use */
1377 set_fact_table
1378 (p_parameter_rec => p_parameter_rec,
1379 p_bucket_dim => p_wrkfc_params.bucket_dim,
1380 p_include_sal => p_wrkfc_params.include_sal,
1381 p_parameter_count => l_parameter_count,
1382 p_single_param => l_single_param,
1383 p_use_snapshot => l_use_snapshot,
1384 p_fact_table => l_fact_table);
1385
1386 IF (l_use_snapshot) THEN
1387
1388 /* Build SQL statement using snapshot procedure */
1389 l_return_sql := build_sql_snp
1390 (p_parameter_rec => p_parameter_rec,
1391 p_wrkfc_params => p_wrkfc_params,
1392 p_fact_table => l_fact_table,
1393 p_date_list => l_date_list,
1394 p_fact_conditions => l_fact_conditions);
1395
1396 ELSE
1397
1398 /* Build SQL statement using standard procedure */
1399 l_return_sql := build_sql
1400 (p_parameter_rec => p_parameter_rec,
1401 p_wrkfc_params => p_wrkfc_params,
1402 p_fact_table => l_fact_table,
1403 p_date_list => l_date_list,
1404 p_fact_conditions => l_fact_conditions);
1405
1406 END IF;
1407
1408 RETURN l_return_sql;
1409
1410 END get_sql;
1411
1412
1413 /******************************************************************************/
1414 /* Main entry point, takes PMV parameters and SQL control parameters */
1415 /* Returns the SQL statement for the PMV report. */
1416 /* Version with debugging built in */
1417 /******************************************************************************/
1418 FUNCTION get_sql
1419 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
1420 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
1421 p_wrkfc_params IN wrkfc_fact_param_type,
1422 p_calling_module IN VARCHAR2)
1423 RETURN VARCHAR2 IS
1424
1425 l_wrkfc_params wrkfc_fact_param_type;
1426 l_debug_mode BOOLEAN := FALSE;
1427 l_debug_sql VARCHAR2(32767);
1428
1429 BEGIN
1430
1431 /* If debugging is on log the calling module, parameters and debug sql */
1432 IF (l_debug_mode) THEN
1433 l_wrkfc_params := p_wrkfc_params;
1434 l_wrkfc_params.bind_format := 'SQL';
1435 l_debug_sql := get_sql
1436 (p_parameter_rec => p_parameter_rec,
1437 p_bind_tab => p_bind_tab,
1438 p_wrkfc_params => l_wrkfc_params);
1439 -- call_debug_api(l_debug_sql);
1440 END IF;
1441
1442 RETURN get_sql
1443 (p_parameter_rec => p_parameter_rec,
1444 p_bind_tab => p_bind_tab,
1445 p_wrkfc_params => p_wrkfc_params);
1446
1447 END get_sql;
1448
1449 /* Initialization - call procedure to set global variables */
1450 BEGIN
1451
1452 initialize_globals;
1453
1454 END hri_bpl_fact_sup_wrkfc_sql;
[Home] [Help]