1 PACKAGE BODY hri_bpl_fact_sup_wcnt_chg_sql AS
2 /* $Header: hribfwch.pkb 120.1 2005/06/03 08:06:16 jtitmas 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_sepcat VARCHAR2(1000);
14 g_template_sepcat_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 /* All columns have one of a few standard formats. Global variables store */
31 /* templates for these formats with tags in for swapping in and out parts */
32 /* of the template (such as measure column, bucket column, bucket value) */
33 /* */
34 /* The columns in the select clause are controlled by various fields in the */
35 /* input parameter record p_wcnt_chg_params: */
36 /* - include_hire: hire count measures will be added */
37 /* - include_trin: transfer in count measures will be added */
38 /* - include_trout: transfer out count measures will be added */
39 /* - include_term: terminations count measures will be added */
40 /* - include_sep: separations will be added */
41 /* - include_sep_inv: involuntary separations will be added */
42 /* - include_sep_vol: voluntary separations will be added */
43 /* - include_low: length of work measures will be added */
44 /* */
45 /* All selected measure columns will be sampled for the current period. */
46 /* Additionally the following fields in the same input parameter record */
47 /* control sampling for other periods: */
48 /* - include_comp: all measures are sampled for the comparison period */
49 /* */
50 /* If a bucket dimension is specified then all measures will be sampled for */
51 /* each period for each bucket value (in addition to the values across all */
52 /* buckets). */
53 /* - bucket_dim: all measures are sampled for all buckets of dimension */
54 /* */
55 /* FROM/WHERE */
56 /* ---------- */
57 /* The FROM and WHERE clauses are separate depending on whether or not */
58 /* the report is view by manager. */
59 /* */
60 /* The fact table is chosen based on the parameters selected in the PMV */
61 /* report in the function set_fact_table. If snapshotting is available the */
62 /* corresponding snapshot fact will be selected. */
63 /* */
64 /* The parameters selected in the PMV report are analysed in the function */
65 /* analyze_parameters. A condition is added to the WHERE clause for each */
66 /* PMV report parameter that is set. */
67 /* */
68 /* VIEW BY */
69 /* ------- */
70 /* The view by grouping is controlled by the parameter passed in by PMV. If */
71 /* view by manager is selected then an additional level of the supervisor */
72 /* hierarchy is brought in so that the result set is grouped by the top */
73 /* manager's direct reports UNLESS in the input parameter record */
74 /* p_wcnt_chg_params the following field is set: */
75 /* - kpi_mode: groups by top manager instead of their direct reports when */
76 /* view by of manager is selected */
77 /* */
78 /* Binds */
79 /* ----- */
80 /* Bind values are passed in using the p_bind_tab parameter. Depending on the */
81 /* bind format selected the corresponding bind strings are populated into the */
82 /* global g_binds table. Bind values are then substituted into the SQL from */
83 /* this global throughout the package. */
84 /* - bind_format: SQL (direct substitution) or PMV (run time substitution) */
85 /* */
86 /******************************************************************************/
87
88
89 /******************************************************************************/
90 /* Initialization of global variables - called once at package initialization */
91 /* */
92 /* Templates for the SELECT columns are set with tags to represent the parts */
93 /* which vary. */
94 /******************************************************************************/
95 PROCEDURE initialize_globals(p_use_snapshot IN BOOLEAN) IS
96
97 BEGIN
98
99 IF p_use_snapshot THEN
100
101 /* Define generic select column */
102 g_template_standard :=
103 'SUM(CASE WHEN wcnt.effective_date = <end_date>
104 THEN <measure_column>
105 ELSE 0
106 END)';
107
108 /* Define generic bucketed select column */
109 g_template_bucket :=
110 'SUM(CASE WHEN wcnt.effective_date = <end_date>
111 AND <bucket_column> = <bucket_id>
112 THEN <measure_column>
113 ELSE 0
114 END)';
115
116 /* Special case separation category columns used for the following MVs */
117 /* - hri_mdp_sup_wcnt_term_asg_mv */
118 g_template_sepcat :=
119 'SUM(CASE WHEN wcnt.effective_date = <end_date>
120 AND separation_category = <sepcat_id>
121 THEN separation_hdc
122 ELSE 0
123 END)';
124
125 g_template_sepcat_bucket :=
126 'SUM(CASE WHEN wcnt.effective_date = <end_date>
127 AND separation_category = <sepcat_id>
128 AND <bucket_column> = <bucket_id>
129 THEN separation_hdc
130 ELSE 0
131 END)';
132
133 ELSE
134
135 /* Define generic select column */
136 g_template_standard :=
137 'SUM(CASE WHEN wcnt.effective_date BETWEEN <start_date> AND <end_date>
138 THEN <measure_column>
139 ELSE 0
140 END)';
141
142 /* Define generic bucketed select column */
143 g_template_bucket :=
144 'SUM(CASE WHEN wcnt.effective_date BETWEEN <start_date> AND <end_date>
145 AND <bucket_column> = <bucket_id>
146 THEN <measure_column>
147 ELSE 0
148 END)';
149
150 /* Special case separation category columns used for the following MVs */
151 /* - hri_mdp_sup_wcnt_term_asg_mv */
152 g_template_sepcat :=
153 'SUM(CASE WHEN wcnt.effective_date BETWEEN <start_date> AND <end_date>
154 AND separation_category = <sepcat_id>
155 THEN separation_hdc
156 ELSE 0
157 END)';
158
159 g_template_sepcat_bucket :=
160 'SUM(CASE WHEN wcnt.effective_date BETWEEN <start_date> AND <end_date>
161 AND separation_category = <sepcat_id>
162 AND <bucket_column> = <bucket_id>
163 THEN separation_hdc
164 ELSE 0
165 END)';
166
167 END IF;
168
169 END initialize_globals;
170
171
172 /******************************************************************************/
173 /* Populates g_binds with the selected BIND format */
174 /******************************************************************************/
175 PROCEDURE populate_global_bind_table
176 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
177 p_bind_format IN VARCHAR2) IS
178
179 l_parameter_name VARCHAR2(100);
180
181 BEGIN
182
183 /* Initialize all parameters to be used */
184 g_binds := g_binds_reset;
185
186 l_parameter_name := p_bind_tab.FIRST;
187
188 WHILE (l_parameter_name IS NOT NULL) LOOP
189 IF (p_bind_format = 'SQL') THEN
190 g_binds(l_parameter_name) := p_bind_tab(l_parameter_name).sql_bind_string;
191 ELSIF (p_bind_format = 'PMV') THEN
192 g_binds(l_parameter_name) := p_bind_tab(l_parameter_name).pmv_bind_string;
193 ELSE
194 g_binds(l_parameter_name) := l_parameter_name;
195 END IF;
196 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
197 END LOOP;
198
199 END populate_global_bind_table;
200
201
202 /******************************************************************************/
203 /* For every report parameter that is set, a condition is added to the WHERE */
204 /* clause. */
205 /* */
206 /* Also to help with deciding which fact table to use in the FROM clause, a */
207 /* count is kept of the number of parameters that are set. If only one */
208 /* parameter is set the name of that parameter is returned. This helps select */
209 /* the most efficient fact to retrieve the data from. */
210 /******************************************************************************/
211 PROCEDURE analyze_parameters
212 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
213 p_fact_conditions OUT NOCOPY VARCHAR2,
214 p_parameter_count OUT NOCOPY PLS_INTEGER,
215 p_single_param OUT NOCOPY VARCHAR2) IS
216
217 l_single_param VARCHAR2(100);
218 l_parameter_name VARCHAR2(100);
219
220 BEGIN
221
222 /* Initialize parameter count */
223 p_parameter_count := 0;
224
225 /* Loop through parameters that have been set */
226 l_parameter_name := p_bind_tab.FIRST;
227
228 WHILE (l_parameter_name IS NOT NULL) LOOP
229 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
230 l_parameter_name = 'JOB+JOB_FAMILY' OR
231 l_parameter_name = 'JOB+JOB_FUNCTION' OR
232 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
233 l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
234 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X' OR
235 l_parameter_name = 'HRI_REASON+HRI_RSN_SEP_X' OR
236 l_parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X') THEN
237
238 /* Dynamically set conditions for parameter */
239 p_fact_conditions := p_fact_conditions ||
240 'AND wcnt.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
241 (l_parameter_name).fact_viewby_col ||
242 ' IN (' || g_binds(l_parameter_name) || ')' || g_rtn;
243
244 /* Keep count of parameters set */
245 /* Do not count person type as this is a global parameter */
246 IF (l_parameter_name <> 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
247 p_parameter_count := p_parameter_count + 1;
248 l_single_param := l_parameter_name;
249 END IF;
250
251 END IF;
252
253 /* Move to next parameter */
254 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
255
256 END LOOP;
257
258 /* Only pass back parameter name if there is only one parameter */
259 IF (p_parameter_count = 1) THEN
260 p_single_param := l_single_param;
261 END IF;
262
263 END analyze_parameters;
264
265
266 /******************************************************************************/
267 /* Decide which fact to use in the main FROM clause based on */
268 /* - number of parameters applied */
269 /* - viewby */
270 /* - buckets */
271 /* - whether a snapshot fact table is available */
272 /* */
273 /* If a fact table is selected that does not have a snapshot available then */
274 /* p_use_snapshot is set accordingly */
275 /******************************************************************************/
276 PROCEDURE set_fact_table
277 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
278 p_bucket_dim IN VARCHAR2,
279 p_include_hire IN VARCHAR2,
280 p_include_trin IN VARCHAR2,
281 p_include_trout IN VARCHAR2,
282 p_include_term IN VARCHAR2,
283 p_include_low IN VARCHAR2,
284 p_parameter_count IN PLS_INTEGER,
285 p_single_param IN VARCHAR2,
286 p_use_snapshot IN OUT NOCOPY BOOLEAN,
287 p_fact_table OUT NOCOPY VARCHAR2) IS
288
289 l_wcnt_vby_table VARCHAR2(30);
290 l_wcnt_bkt_table VARCHAR2(30);
291 l_wcnt_prm_table VARCHAR2(30);
292
293 BEGIN
294
295 /* Check whether a snapshot is available if called for the first time */
296 IF (p_use_snapshot IS NULL) THEN
297 p_use_snapshot := hri_oltp_pmv_util_snpsht.use_wcnt_chg_snpsht_for_mgr
301
298 (p_supervisor_id => p_parameter_rec.peo_supervisor_id,
299 p_effective_date => p_parameter_rec.time_curr_end_date);
300 END IF;
302 /* Split logic for which table to return by snapshot or non-snapshot */
303 IF p_use_snapshot THEN
304
305 /*----------------------------------------------------------------------------*/
306 /* Decide which of the fact tables to return. */
307 /* */
308 /* The logic goes as follows: */
309 /* */
310 /* Parameter Count: 0 */
311 /* ------------------ */
312 /* 1) Check whether the supervisor level fact can be used */
313 /* (no parameters, no buckets, no low, view by manager) */
314 /* */
315 /* 2) Check the fact associated with the view by dimension level */
316 /* (no parameters, no bucket set, no hires or transfers */
317 /* */
318 /* 3) Check the fact associated with the bucket dimension level */
319 /* (no parameters, bucket set, view by manager, no hires or transfers */
320 /* */
321 /* 4) Otherwise snapshot is not available - call function again for non- */
322 /* snapshot */
323 /*----------------------------------------------------------------------------*/
324
325 /* Set the local variables for which fact table to use */
326 l_wcnt_vby_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
327 (p_parameter_rec.view_by).sup_lvl_wcnt_mv_snp;
328 IF p_bucket_dim IS NOT NULL THEN
329 l_wcnt_bkt_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
330 (p_bucket_dim).sup_lvl_wcnt_mv_snp;
331 END IF;
332 IF p_single_param IS NOT NULL THEN
333 l_wcnt_prm_table := hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
334 (p_single_param).sup_lvl_wcnt_mv_snp;
335 END IF;
336
337 /* If no parameters are supplied use the viewby or bucket dimension table */
338 IF (p_parameter_count = 0) THEN
339 IF (p_bucket_dim IS NULL) THEN
340 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
341 p_include_low = 'N') THEN
342
343 /* 1) no parameters, no buckets, no low, view by manager */
344 p_fact_table := 'hri_mds_sup_wcnt_chg_mv';
345
346 ELSE
347
348 /* 2) no parameters, no bucket set, view by not manager */
349 p_fact_table := l_wcnt_vby_table;
350
351 END IF;
352
353 ELSIF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
354
355 /* 3) no parameters, bucket set, view by manager */
356 p_fact_table := l_wcnt_bkt_table;
357
358 END IF;
359 END IF;
360
361 IF (p_fact_table IS NULL) THEN
362
363 /* 4) Snapshot not available - call function again for non-snapshot */
364 p_use_snapshot := FALSE;
365 set_fact_table
366 (p_parameter_rec => p_parameter_rec,
367 p_bucket_dim => p_bucket_dim,
368 p_include_hire => p_include_hire,
369 p_include_trin => p_include_trin,
370 p_include_trout => p_include_trout,
371 p_include_term => p_include_term,
372 p_include_low => p_include_low,
373 p_parameter_count => p_parameter_count,
374 p_single_param => p_single_param,
375 p_use_snapshot => p_use_snapshot,
376 p_fact_table => p_fact_table);
377
378 END IF;
379
380 ELSE
381
382 /*----------------------------------------------------------------------------*/
383 /* Decide which fact table to return. The logic for non-snapshots is: */
384 /* */
385 /* 1) Check whether the supervisor level change fact can be used */
386 /* (no bucket, no length of work, no parameters) */
387 /* AND the viewby selected is Manager */
388 /* */
389 /* 2) Check whether the supervisor level change fact can be used */
390 /* (no bucket, no length of work, no parameters) */
391 /* AND the viewby selected is NOT Manager */
392 /* */
393 /* 3) Check whether the termination by assignment fact can be used */
394 /* (no hires or transfers) */
395 /* */
396 /* 4) If neither 1) nor 2) nor 3 then return an invalid fact table message */
397 /* */
398 /* The logic is convoluted because the original MV does not support buckets, */
399 /* other dimensions or the length of service measure and the new MV does not */
400 /* support hires or transfers */
401 /*----------------------------------------------------------------------------*/
402
403 /* 1) no bucket (or person type bucket), no length of work, no parameters */
404 /* VIEWBY Manager */
405 IF ((p_bucket_dim IS NULL OR p_bucket_dim = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') AND
406 p_include_low = 'N' AND
407 p_parameter_count = 0
408 --
412 p_fact_table := 'hri_mdp_sup_wcnt_chg_mv';
409 and p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H'
410 --
411 ) THEN
413
414 /* 2) no bucket, no length of work, no parameters, viewby Manager */
415 ELSIF (p_bucket_dim IS NULL AND
416 p_include_low = 'N' AND
417 p_parameter_count = 0
418 --
419 and p_parameter_rec.view_by <> 'HRI_PERSON+HRI_PER_USRDR_H'
420 --
421 ) THEN
422 p_fact_table := 'hri_mdp_sup_wcnt_term_asg_mv';
423
424 ELSIF (p_include_hire = 'N' AND
425 p_include_trin = 'N' AND
426 p_include_trout = 'N' AND
427 p_include_term = 'N') THEN
428 /* 3) no hires or transfers */
429 p_fact_table := 'hri_mdp_sup_wcnt_term_asg_mv';
430 ELSE
431 /* 4) invalid fact table */
432 p_fact_table := 'invalid_fact_table';
433 END IF;
434 END IF;
435
436 END set_fact_table;
437
438
439 /******************************************************************************/
440 /* Replaces tags in a SELECT column template and formats it with an alias */
441 /******************************************************************************/
442 FUNCTION format_column(p_column_string IN VARCHAR2,
443 p_start_date IN VARCHAR2,
444 p_end_date IN VARCHAR2,
445 p_bucket_id IN VARCHAR2,
446 p_column_alias IN VARCHAR2)
447 RETURN VARCHAR2 IS
448
449 l_column_string VARCHAR2(1000);
450
451 BEGIN
452
453 /* Replace the start date */
454 l_column_string := REPLACE(p_column_string, '<start_date>', p_start_date);
455
456 /* Replace the end date */
457 l_column_string := REPLACE(l_column_string, '<end_date>', p_end_date);
458
459 /* Replace the bucket identifier */
460 l_column_string := REPLACE(l_column_string, '<bucket_id>', p_bucket_id);
461
462 /* Format the column string replacing the start date */
463 l_column_string :=
464 ',' || l_column_string || ' ' || p_column_alias || g_rtn;
465
466 RETURN l_column_string;
467
468 END format_column;
469
470
471 /******************************************************************************/
472 /* This function returns a list of columns to be added to the SELECT clause */
473 /* for a given measure. The input fields contain the templates to use for */
474 /* the measure SELECT columns and various control fields. */
475 /* */
476 /* The following fields control sampling across different periods */
477 /* - include_comp: the measure is sampled for the comparison period */
478 /* */
479 /* If a bucket dimension is specified then all measures will be sampled for */
480 /* each period for each bucket value (in addition to the values across all */
481 /* buckets). */
482 /* - bucket_dim: the measures is sampled for all buckets of dimension */
483 /* */
484 /******************************************************************************/
485 FUNCTION build_columns
486 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
487 p_wcnt_chg_params IN wcnt_chg_fact_param_type,
488 p_template_standard IN VARCHAR2,
489 p_template_bucket IN VARCHAR2,
490 p_measure_alias IN VARCHAR2)
491 RETURN VARCHAR2 IS
492
493 /* Return string */
494 l_column_list VARCHAR2(5000);
495
496 /* Table of buckets for the given bucket dimension */
497 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
498
499 /* Column Templates */
500 l_bucket_column VARCHAR2(1000);
501
502 BEGIN
503
504 /* Always get the column for the current period */
505 /************************************************/
506 l_column_list := l_column_list || format_column
507 (p_column_string => p_template_standard
508 ,p_start_date => g_binds('TIME_CURR_START_DATE')
509 ,p_end_date => g_binds('TIME_CURR_END_DATE')
510 ,p_bucket_id => NULL
511 ,p_column_alias => 'curr_' || p_measure_alias);
512
513 /* Check for comparison period columns */
514 /***************************************/
515 IF (p_wcnt_chg_params.include_comp = 'Y') THEN
516 l_column_list := l_column_list || format_column
517 (p_column_string => p_template_standard
518 ,p_start_date => g_binds('TIME_COMP_START_DATE')
519 ,p_end_date => g_binds('TIME_COMP_END_DATE')
520 ,p_bucket_id => NULL
521 ,p_column_alias => 'comp_' || p_measure_alias);
522 END IF;
523
524 /* Checks for bucket dimension */
525 /*******************************/
526 IF (p_wcnt_chg_params.bucket_dim IS NOT NULL) THEN
527
528 /* Replace the bucket column tag in the bucket column template */
529 l_bucket_column := REPLACE(p_template_bucket, '<bucket_column>',
530 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
531 (p_wcnt_chg_params.bucket_dim).fact_viewby_col);
532
533 /* Set buckets table */
534 IF (p_wcnt_chg_params.bucket_dim = 'HRI_LOW+HRI_LOW_BAND_X') THEN
535 hri_mtdt_dim_lvl.set_low_band_buckets(p_parameter_rec.wkth_wktyp_sk_fk);
536 l_bucket_tab := hri_mtdt_dim_lvl.g_low_band_buckets_tab;
537 ELSIF (p_wcnt_chg_params.bucket_dim = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X') THEN
538 l_bucket_tab := hri_mtdt_dim_lvl.g_prfmnc_band_buckets_tab;
539 ELSIF (p_wcnt_chg_params.bucket_dim = 'JOB+PRIMARY_JOB_ROLE') THEN
543 END IF;
540 l_bucket_tab := hri_mtdt_dim_lvl.g_primary_job_role_tab;
541 ELSIF (p_wcnt_chg_params.bucket_dim = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
542 l_bucket_tab := hri_mtdt_dim_lvl.g_wkth_wktyp_tab;
544
545 /* Loop through buckets table to add required columns */
546 FOR i IN l_bucket_tab.FIRST..l_bucket_tab.LAST LOOP
547
548 /* Add the current period column for the bucket */
549 l_column_list := l_column_list || format_column
550 (p_column_string => l_bucket_column
551 ,p_start_date => g_binds('TIME_CURR_START_DATE')
552 ,p_end_date => g_binds('TIME_CURR_END_DATE')
553 ,p_bucket_id => l_bucket_tab(i).bucket_id_string
554 ,p_column_alias => 'curr_' || p_measure_alias || '_' ||
555 l_bucket_tab(i).bucket_name);
556
557 /* Add the comparison period column for the bucket */
558 IF (p_wcnt_chg_params.include_comp = 'Y') THEN
559 l_column_list := l_column_list || format_column
560 (p_column_string => l_bucket_column
561 ,p_start_date => g_binds('TIME_COMP_START_DATE')
562 ,p_end_date => g_binds('TIME_COMP_END_DATE')
563 ,p_bucket_id => l_bucket_tab(i).bucket_id_string
564 ,p_column_alias => 'comp_' || p_measure_alias || '_' ||
565 l_bucket_tab(i).bucket_name);
566 END IF;
567
568 END LOOP;
569
570 END IF;
571
572 RETURN l_column_list;
573
574 END build_columns;
575
576
577 /******************************************************************************/
578 /* Returns the final SQL statement for the PMV report. */
579 /* */
580 /* The SQL returned is in the format: */
581 /* */
582 /* SELECT */
583 /* Grouping column (view by) */
584 /* Specific measure columns, including sampling */
585 /* across different periods and buckets */
586 /* FROM */
587 /* Fact table */
588 /* WHERE */
589 /* Apply parameters corresponding to user selection */
590 /* in the PMV report */
591 /* Join to time dimension sampling all required periods */
592 /* GROUP BY */
593 /* Grouping column (view by) */
594 /* */
595 /* SELECT */
596 /* ====== */
597 /* Calls build_columns for each measure selected to build up the SELECT */
598 /* clause. */
599 /* */
600 /* For details of the SELECT column list see package header */
601 /* */
602 /* FROM/WHERE */
603 /* ========== */
604 /* Puts together the FROM/WHERE clauses depending on whether the view by */
605 /* manager special case is selected. */
606 /******************************************************************************/
607 FUNCTION build_sql
608 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
609 p_wcnt_chg_params IN wcnt_chg_fact_param_type,
610 p_use_snapshot IN BOOLEAN,
611 p_fact_table IN VARCHAR2,
612 p_fact_conditions IN VARCHAR2)
613 RETURN VARCHAR2 IS
614
615 /* Whether to format the SQL for the view by manager special case */
616 l_view_by_manager BOOLEAN;
617
618 /* Dynamic SQL columns */
619 /***********************/
620 /* Select */
621 l_direct_ind VARCHAR2(1000);
622 l_col_list VARCHAR2(10000);
623
624 /* From / Where */
625 l_from_clause VARCHAR2(1000);
626 l_mgr_direct_condition VARCHAR2(1000);
627 l_gen_direct_condition VARCHAR2(1000);
628 l_date_condition VARCHAR2(1000);
629 l_snapshot_condition VARCHAR2(1000);
630
631 /* Column Templates */
632 l_template_sepcat VARCHAR2(1000);
633 l_template_sepcat_bucket VARCHAR2(1000);
634
635 /* Return string */
636 l_fact_sql VARCHAR2(10000);
637
638 BEGIN
639
640 /******************************************************************************/
641 /* INITIALIZATION */
642 /******************/
643
644 /* Check for view by manager special case */
645 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
646 p_wcnt_chg_params.kpi_mode = 'N') THEN
647 l_view_by_manager := TRUE;
648 ELSE
649 l_view_by_manager := FALSE;
650 END IF;
651
652 /* Set table specific conditions/templates */
653 IF (p_fact_table = 'hri_mdp_sup_wcnt_term_asg_mv') THEN
654 l_mgr_direct_condition := 'AND (suph.sub_relative_level = 1 ' || g_rtn ||
655 ' OR wcnt.direct_ind = 1)' || g_rtn;
656 l_template_sepcat := g_template_sepcat;
657 l_template_sepcat_bucket := g_template_sepcat_bucket;
661 || g_rtn;
658 ELSIF (p_fact_table = 'hri_mdp_sup_wcnt_chg_mv' OR
659 p_fact_table = 'hri_mds_sup_wcnt_chg_mv') THEN
660 l_mgr_direct_condition := 'AND 1 - suph.sub_relative_level = wcnt.direct_record_ind'
662 l_gen_direct_condition := 'AND wcnt.direct_record_ind = 0' || g_rtn;
663 l_template_sepcat := g_template_standard;
664 l_template_sepcat_bucket := g_template_bucket;
665 ELSE
666 l_mgr_direct_condition := 'AND (suph.sub_relative_level = 1 ' || g_rtn ||
667 ' OR wcnt.direct_ind = 1)' || g_rtn;
668 l_template_sepcat := g_template_standard;
669 l_template_sepcat_bucket := g_template_bucket;
670 END IF;
671
672 /* Set dynamic SQL based on view by (manager special case for non-KPI reports) */
673 IF (l_view_by_manager) THEN
674
675 /******************************************************************************/
676 /* VIEW BY MANAGER SPECIAL CASE */
677 /********************************/
678
679 l_direct_ind := '1 - suph.sub_relative_level';
680
681 l_from_clause :=
682 ' hri_cs_suph suph
683 ,' || p_fact_table || ' wcnt
684 WHERE suph.sup_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || '
685 AND suph.sub_relative_level <= 1
686 AND suph.sub_invalid_flag_code = ''N''
687 AND ' || g_binds('TIME_CURR_END_DATE') || ' BETWEEN suph.effective_start_date ' ||
688 'AND suph.effective_end_date
689 AND suph.sub_person_id = wcnt.supervisor_person_id ' || g_rtn ||
690 l_mgr_direct_condition;
691
692 /* View by anything else */
693 ELSE
694
695 /******************************************************************************/
696 /* GENERIC (OTHER VIEW BYs) */
697 /****************************/
698
699 l_direct_ind := '0';
700
701 l_from_clause :=
702 ' ' || p_fact_table || ' wcnt
703 WHERE wcnt.supervisor_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || g_rtn||
704 l_gen_direct_condition;
705
706 END IF;
707
708 /******************************************************************************/
709 /* SELECT CLAUSE */
710 /*****************/
711
712 /* Build up SELECT column list */
713 IF (p_wcnt_chg_params.include_hire = 'Y') THEN
714 l_col_list := l_col_list || build_columns
715 (p_parameter_rec => p_parameter_rec,
716 p_wcnt_chg_params => p_wcnt_chg_params,
717 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'hire_hdc'),
718 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'hire_hdc'),
719 p_measure_alias => 'hire_hdc');
720 END IF;
721
722 /* Build up SELECT column list */
723 IF (p_wcnt_chg_params.include_trin = 'Y') THEN
724 l_col_list := l_col_list || build_columns
725 (p_parameter_rec => p_parameter_rec,
726 p_wcnt_chg_params => p_wcnt_chg_params,
727 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'transfer_in_hdc'),
728 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'transfer_in_hdc'),
729 p_measure_alias => 'transfer_in_hdc');
730 END IF;
731
732 /* Build up SELECT column list */
733 IF (p_wcnt_chg_params.include_trout = 'Y') THEN
734 l_col_list := l_col_list || build_columns
735 (p_parameter_rec => p_parameter_rec,
736 p_wcnt_chg_params => p_wcnt_chg_params,
737 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'transfer_out_hdc'),
738 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'transfer_out_hdc'),
739 p_measure_alias => 'transfer_out_hdc');
740 END IF;
741
742 /* Build up SELECT column list */
743 IF (p_wcnt_chg_params.include_term = 'Y') THEN
744 l_col_list := l_col_list || build_columns
745 (p_parameter_rec => p_parameter_rec,
746 p_wcnt_chg_params => p_wcnt_chg_params,
747 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'termination_hdc'),
748 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'termination_hdc'),
749 p_measure_alias => 'termination_hdc');
750 END IF;
751
752 IF (p_wcnt_chg_params.include_sep = 'Y') THEN
753 l_col_list := l_col_list || build_columns
754 (p_parameter_rec => p_parameter_rec,
755 p_wcnt_chg_params => p_wcnt_chg_params,
756 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'separation_hdc'),
757 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'separation_hdc'),
758 p_measure_alias => 'separation_hdc');
759 END IF;
760
761 IF (p_wcnt_chg_params.include_sep_vol = 'Y') THEN
762 l_col_list := l_col_list || build_columns
763 (p_parameter_rec => p_parameter_rec,
764 p_wcnt_chg_params => p_wcnt_chg_params,
765 p_template_standard => REPLACE(REPLACE(l_template_sepcat,
766 '<sepcat_id>', '''SEP_VOL'''),
767 '<measure_column>', 'sep_vol_hdc'),
768 p_template_bucket => REPLACE(REPLACE(l_template_sepcat_bucket,
769 '<sepcat_id>', '''SEP_VOL'''),
770 '<measure_column>', 'sep_vol_hdc'),
771 p_measure_alias => 'sep_vol_hdc');
772 END IF;
773
774 IF (p_wcnt_chg_params.include_sep_inv = 'Y') THEN
775 l_col_list := l_col_list || build_columns
776 (p_parameter_rec => p_parameter_rec,
777 p_wcnt_chg_params => p_wcnt_chg_params,
778 p_template_standard => REPLACE(REPLACE(l_template_sepcat,
779 '<sepcat_id>', '''SEP_INV'''),
780 '<measure_column>', 'sep_invol_hdc'),
781 p_template_bucket => REPLACE(REPLACE(l_template_sepcat_bucket,
782 '<sepcat_id>', '''SEP_INV'''),
786
783 '<measure_column>', 'sep_invol_hdc'),
784 p_measure_alias => 'sep_invol_hdc');
785 END IF;
787 /* Build up SELECT column list */
788 IF (p_wcnt_chg_params.include_low = 'Y') THEN
789 l_col_list := l_col_list || build_columns
790 (p_parameter_rec => p_parameter_rec,
791 p_wcnt_chg_params => p_wcnt_chg_params,
792 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'pow_months'),
793 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'pow_months'),
794 p_measure_alias => 'low_months');
795
796 l_col_list := l_col_list || build_columns
797 (p_parameter_rec => p_parameter_rec,
798 p_wcnt_chg_params => p_wcnt_chg_params,
799 p_template_standard => REPLACE(g_template_standard, '<measure_column>',
800 'effective_date - pow_start_date'),
801 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>',
802 'effective_date - pow_start_date'),
803 p_measure_alias => 'low_days');
804 END IF;
805
806 /******************************************************************************/
807 /* FROM CLAUSE */
808 /***************/
809
810 IF p_use_snapshot THEN
811
812 l_snapshot_condition :=
813 'AND wcnt.period_type = &PERIOD_TYPE' || g_rtn;
814
815 IF (p_wcnt_chg_params.include_comp = 'Y') THEN
816 l_date_condition := l_date_condition ||
817 'AND (wcnt.effective_date, wcnt.comparison_type) IN (
818 ('|| g_binds('TIME_CURR_END_DATE') || ',''CURRENT''),
819 ('|| g_binds('TIME_COMP_END_DATE') || ',&TIME_COMPARISON_TYPE)
820 )' || g_rtn;
821 ELSE
822 l_date_condition := l_date_condition ||
823 'AND wcnt.effective_date = '|| g_binds('TIME_CURR_END_DATE') || g_rtn ||
824 'AND wcnt.comparison_type = ''CURRENT''' || g_rtn;
825
826 END IF;
827
828 ELSE
829
830 /* Set date condition depending on whether the previous period is set */
831 IF (p_wcnt_chg_params.include_comp = 'Y') THEN
832 l_date_condition := l_date_condition ||
833 'AND wcnt.effective_date BETWEEN ' || g_binds('TIME_COMP_START_DATE') ||
834 ' AND ' || g_binds('TIME_CURR_END_DATE') || '
835 AND wcnt.effective_date NOT BETWEEN ' || g_binds('TIME_COMP_END_DATE') || ' + 1 ' ||
836 'AND ' || g_binds('TIME_CURR_START_DATE') || ' - 1' || g_rtn;
837
838 ELSE
839 l_date_condition := l_date_condition ||
840 'AND wcnt.effective_date BETWEEN ' || g_binds('TIME_CURR_START_DATE') ||
841 ' AND ' || g_binds('TIME_CURR_END_DATE') || g_rtn;
842 END IF;
843
844 END IF;
845
846 /******************************************************************************/
847 /* BUILD UP SQL STATEMENT */
848 /**************************/
849
850 l_fact_sql :=
851 'SELECT /*+ NO_MERGE INDEX(wcnt) */
852 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
853 (p_parameter_rec.view_by).fact_viewby_col || ' vby_id
854 ,' || l_direct_ind || ' direct_ind ' || g_rtn ||
855 l_col_list ||
856 'FROM' || g_rtn ||
857 l_from_clause ||
858 p_fact_conditions ||
859 l_date_condition ||
860 l_snapshot_condition ||
861 'GROUP BY
862 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
863 (p_parameter_rec.view_by).fact_viewby_col || g_rtn ||
864 ',' || l_direct_ind;
865
866 RETURN l_fact_sql;
867
868 END build_sql;
869
870
871 /******************************************************************************/
872 /* Main entry point, takes PMV parameters and SQL control parameters */
873 /* Returns the SQL statement for the PMV report. */
874 /******************************************************************************/
875 FUNCTION get_sql
876 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
877 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
878 p_wcnt_chg_params IN wcnt_chg_fact_param_type)
879 RETURN VARCHAR2 IS
880
881 l_date_list VARCHAR2(3000);
882 l_return_sql VARCHAR2(32767);
883 l_parameter_count PLS_INTEGER;
884 l_single_param VARCHAR2(100);
885 l_fact_table VARCHAR2(30);
886 l_fact_conditions VARCHAR2(1000);
887 l_use_snapshot BOOLEAN;
888
889 BEGIN
890
891 /* Populate a global record with the PMV context in the given bind format */
892 populate_global_bind_table
893 (p_bind_tab => p_bind_tab,
894 p_bind_format => p_wcnt_chg_params.bind_format);
895
896 /* Analyze parameters to build up fact condition and help decide */
897 /* which fact/function to use */
898 analyze_parameters
899 (p_bind_tab => p_bind_tab,
900 p_fact_conditions => l_fact_conditions,
901 p_parameter_count => l_parameter_count,
902 p_single_param => l_single_param);
903
904 /* Decide which fact table(s) to use */
905 set_fact_table
906 (p_parameter_rec => p_parameter_rec,
907 p_bucket_dim => p_wcnt_chg_params.bucket_dim,
908 p_include_hire => p_wcnt_chg_params.include_hire,
909 p_include_trin => p_wcnt_chg_params.include_trin,
910 p_include_trout => p_wcnt_chg_params.include_trout,
911 p_include_term => p_wcnt_chg_params.include_term,
912 p_include_low => p_wcnt_chg_params.include_low,
913 p_parameter_count => l_parameter_count,
914 p_single_param => l_single_param,
915 p_use_snapshot => l_use_snapshot,
916 p_fact_table => l_fact_table);
917
918 /* Set column templates */
919 initialize_globals(p_use_snapshot => l_use_snapshot);
920
921 /* Build SQL statement */
922 l_return_sql := build_sql
926 p_fact_table => l_fact_table,
923 (p_parameter_rec => p_parameter_rec,
924 p_wcnt_chg_params => p_wcnt_chg_params,
925 p_use_snapshot => l_use_snapshot,
927 p_fact_conditions => l_fact_conditions);
928
929 RETURN l_return_sql;
930
931 END get_sql;
932
933
934 /******************************************************************************/
935 /* Main entry point, takes PMV parameters and SQL control parameters */
936 /* Returns the SQL statement for the PMV report. */
937 /* Version with debugging built in */
938 /******************************************************************************/
939 FUNCTION get_sql
940 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
941 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
942 p_wcnt_chg_params IN wcnt_chg_fact_param_type,
943 p_calling_module IN VARCHAR2)
944 RETURN VARCHAR2 IS
945
946 l_wcnt_chg_params wcnt_chg_fact_param_type;
947 l_debug_mode BOOLEAN := FALSE;
948 l_debug_sql VARCHAR2(32767);
949
950 BEGIN
951
952 /* If debugging is on log the calling module, parameters and debug sql */
953 IF (l_debug_mode) THEN
954 l_wcnt_chg_params := p_wcnt_chg_params;
955 l_wcnt_chg_params.bind_format := 'SQL';
956 l_debug_sql := get_sql
957 (p_parameter_rec => p_parameter_rec,
958 p_bind_tab => p_bind_tab,
959 p_wcnt_chg_params => l_wcnt_chg_params);
960 -- call_debug_api(l_debug_sql);
961 END IF;
962
963 RETURN get_sql
964 (p_parameter_rec => p_parameter_rec,
965 p_bind_tab => p_bind_tab,
966 p_wcnt_chg_params => p_wcnt_chg_params);
967
968 END get_sql;
969
970 END hri_bpl_fact_sup_wcnt_chg_sql;
[Home] [Help]