1 PACKAGE BODY HRI_BPL_FACT_ABS_SQL AS
2 /* $Header: hribfabs.pkb 120.2 2005/09/29 06:07 cbridge noship $ */
3
4 TYPE g_bind_rec_tab_type IS TABLE OF VARCHAR2(32000) INDEX BY VARCHAR2(80);
5 -- Table of bind strings in required format
6 g_binds g_bind_rec_tab_type;
7 g_binds_reset g_bind_rec_tab_type;
8
9 -- Templates for SELECT columns
10 g_template_standard VARCHAR2(1000);
11 g_template_abs_period VARCHAR2(1000);
12 g_template_bucket VARCHAR2(1000);
13
14 g_rtn VARCHAR2(30) := '
15 ';
16
17 /******************************************************************************/
18 /* Package Design */
19 /* ============== */
20 /* */
21 /* [Search for SELECT|FROM|WHERE|BIND to find procedures and functions that */
22 /* impact the respective parts of the SQL returned] */
23 /* */
24 /* SELECT */
25 /* ------ */
26 /* For details of the SELECT column list see package header */
27 /* */
28 /* All columns have one of a few standard formats. Global variables store */
29 /* templates for these formats with tags in for swapping in and out parts */
30 /* of the template (such as measure column, bucket column, bucket value) */
31 /* */
32 /* The columns in the select clause are controlled by various fields in the */
33 /* input parameter record: */
34 /* - <measure list> */
35 /* */
36 /* All selected measure columns will be sampled for the current period. */
37 /* Additionally the following fields in the same input parameter record */
38 /* control sampling for other periods: */
39 /* - include_comp: all measures are sampled for the comparison period */
40 /* */
41 /* If a bucket dimension is specified then all measures will be sampled for */
42 /* each period for each bucket value (in addition to the values across all */
43 /* buckets). */
44 /* - bucket_dim: all measures are sampled for all buckets of dimension */
45 /* */
46 /* FROM/WHERE */
47 /* ---------- */
48 /* The FROM and WHERE clauses are separate depending on whether or not */
49 /* the report is view by manager. */
50 /* */
51 /* The fact table is chosen based on the parameters selected in the PMV */
52 /* report in the function set_fact_table. If snapshotting is available the */
53 /* corresponding snapshot fact will be selected. */
54 /* */
55 /* The parameters selected in the PMV report are analysed in the function */
56 /* analyze_parameters. A condition is added to the WHERE clause for each */
57 /* PMV report parameter that is set. */
58 /* */
59 /* VIEW BY */
60 /* ------- */
61 /* The view by grouping is controlled by the parameter passed in by PMV. If */
62 /* view by manager is selected then an additional level of the supervisor */
63 /* hierarchy is brought in so that the result set is grouped by the top */
64 /* manager's direct reports UNLESS in the input parameter record */
65 /* the following field is set: */
66 /* - kpi_mode: groups by top manager instead of their direct reports when */
67 /* view by of manager is selected */
68 /* */
69 /* Binds */
70 /* ----- */
71 /* Bind values are passed in using the p_bind_tab parameter. */
72 /* - bind_format: SQL (direct substitution) or PMV (run time substitution) */
73 /* */
74 /* If SQL format is chosen the sql string is passed to: */
75 /* hri_oltp_pmv_util_pkg.substitute_bind_values */
76 /* */
77 /* where the actual values are substituted into the binds. */
78 /******************************************************************************/
79
80
81 /******************************************************************************/
82 /* Initialization of global variables - called once at package initialization */
83 /* */
84 /* Templates for the SELECT columns are set with tags to represent the parts */
85 /* which vary. */
86 /******************************************************************************/
87 PROCEDURE initialize_globals(p_use_snapshot IN BOOLEAN) IS
88
89 BEGIN
90
91 IF p_use_snapshot THEN
92
93 /* Define generic select column */
94 g_template_standard :=
95 'SUM(CASE WHEN absn.effective_date BETWEEN <start_date> AND <end_date>
96 THEN <measure_column>
97 ELSE 0
98 END)';
99
100 g_template_abs_period :=
101 'SUM(CASE WHEN absn.effective_date = <start_date>
102 THEN <measure_column>
103 WHEN absn.effective_date > <start_date> AND absn.effective_date <= <end_date>
104 THEN <measure_column2>
105 ELSE 0
106 END)';
107
108 /* Define generic bucketed select column */
109 g_template_bucket :=
110 'SUM(CASE WHEN absn.effective_date = <end_date>
111 AND <bucket_column> = <bucket_id>
112 THEN <measure_column>
113 ELSE 0
114 END)';
115
116 ELSE
117
118 /* Define generic select column */
119 g_template_standard :=
120 'SUM(CASE WHEN absn.effective_date BETWEEN <start_date> AND <end_date>
121 THEN <measure_column>
122 ELSE 0
123 END)';
124
125 g_template_abs_period :=
126 'SUM(CASE WHEN absn.effective_date = <start_date>
127 THEN <measure_column>
128 WHEN absn.effective_date > <start_date> AND absn.effective_date <= <end_date>
129 THEN <measure_column2>
130 ELSE 0
131 END)';
132
133
134 /* Define generic bucketed select column */
135 g_template_bucket :=
136 'SUM(CASE WHEN absn.effective_date BETWEEN <start_date> AND <end_date>
137 AND <bucket_column> = <bucket_id>
138 THEN <measure_column>
139 ELSE 0
140 END)';
141
142 END IF;
143
144 END initialize_globals;
145
146 /******************************************************************************/
147 /* Populates g_binds with the selected BIND format */
148 /******************************************************************************/
149 PROCEDURE populate_global_bind_table
150 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
151 p_bind_format IN VARCHAR2) IS
152
153 l_parameter_name VARCHAR2(100);
154
155 BEGIN
156
157 /* Initialize all parameters to be used */
158 g_binds := g_binds_reset;
159
160 l_parameter_name := p_bind_tab.FIRST;
161
162 WHILE (l_parameter_name IS NOT NULL) LOOP
163 IF (p_bind_format = 'SQL') THEN
164 g_binds(l_parameter_name) := p_bind_tab(l_parameter_name).sql_bind_string;
165 ELSIF (p_bind_format = 'PMV') THEN
166 g_binds(l_parameter_name) := p_bind_tab(l_parameter_name).pmv_bind_string;
167 ELSE
168 g_binds(l_parameter_name) := l_parameter_name;
169 END IF;
170 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
171 END LOOP;
172
173 END populate_global_bind_table;
174
175
176 /******************************************************************************/
177 /* For every report parameter that is set, a condition is added to the WHERE */
178 /* clause. */
179 /* */
180 /* Also to help with deciding which fact table to use in the FROM clause, a */
181 /* count is kept of the number of parameters that are set. If only one */
182 /* parameter is set the name of that parameter is returned. This helps select */
183 /* the most efficient fact to retrieve the data from. */
184 /******************************************************************************/
185 PROCEDURE analyze_parameters
186 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
187 p_fact_conditions OUT NOCOPY VARCHAR2,
188 p_parameter_count OUT NOCOPY PLS_INTEGER,
189 p_single_param OUT NOCOPY VARCHAR2) IS
190
191 l_single_param VARCHAR2(100);
192 l_parameter_name VARCHAR2(100);
193
194 BEGIN
195
196 /* Initialize parameter count */
197 p_parameter_count := 0;
198
199 /* Loop through parameters that have been set */
200 l_parameter_name := p_bind_tab.FIRST;
201
202 WHILE (l_parameter_name IS NOT NULL) LOOP
203 IF (
204 /* Commented View bys currently no supported */
205 -- l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
206 -- l_parameter_name = 'JOB+JOB_FAMILY' OR
207 -- l_parameter_name = 'JOB+JOB_FUNCTION' OR
208 -- l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
209 -- l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
210 -- l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X' OR
211 -- l_parameter_name = 'HRI_REASON+HRI_RSN_SEP_X' OR
212 -- l_parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' OR
213
214 l_parameter_name = 'HRI_ABSNC+HRI_ABSNC_CAT') THEN
215
216 /* Dynamically set conditions for parameter */
217 p_fact_conditions := p_fact_conditions ||
218 'AND absn.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
219 (l_parameter_name).fact_viewby_col ||
220 ' IN (' || p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
221
222 /* Keep count of parameters set */
223 /* Do not count person type as this is a global parameter */
224 IF (l_parameter_name <> 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
225 p_parameter_count := p_parameter_count + 1;
226 l_single_param := l_parameter_name;
227 END IF;
228
229 END IF;
230
231 /* Move to next parameter */
232 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
233
234 END LOOP;
235
236 /* Only pass back parameter name if there is only one parameter */
237 IF (p_parameter_count = 1) THEN
238 p_single_param := l_single_param;
239 END IF;
240
241 END analyze_parameters;
242
243
244 /******************************************************************************/
245 /* Decide which fact to use in the main FROM clause based on */
246 /* - number of parameters applied */
247 /* - viewby */
248 /* - buckets */
249 /* - whether a snapshot fact table is available */
250 /* */
251 /* If a fact table is selected that does not have a snapshot available then */
252 /* p_use_snapshot is set accordingly */
253 /******************************************************************************/
254 PROCEDURE set_fact_table
255 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
256 p_bucket_dim IN VARCHAR2,
257 p_abs_drtn_days IN VARCHAR2,
258 p_abs_drtn_hrs IN VARCHAR2,
259 p_abs_in_period IN VARCHAR2,
260 p_abs_ntfctn_period IN VARCHAR2,
261 p_parameter_count IN PLS_INTEGER,
262 p_single_param IN VARCHAR2,
263 p_use_snapshot IN OUT NOCOPY BOOLEAN,
264 p_fact_table OUT NOCOPY VARCHAR2) IS
265
266 /* Not required at this time */
267 l_wcnt_vby_table VARCHAR2(30);
268 l_wcnt_bkt_table VARCHAR2(30);
269 l_wcnt_prm_table VARCHAR2(30);
270
271 BEGIN
272
273 /* Check whether a snapshot is available if called for the first time */
274
275 /* HR DBI 70C Base Line doesn't Suppoer Snap Shot MV's False will be Returned */
279 p_effective_date => p_parameter_rec.time_curr_end_date);
276 IF (p_use_snapshot IS NULL) THEN
277 p_use_snapshot := hri_oltp_pmv_util_snpsht.use_wcnt_chg_snpsht_for_mgr
278 (p_supervisor_id => p_parameter_rec.peo_supervisor_id,
280 END IF;
281
282 /* Split logic for which table to return by snapshot or non-snapshot */
283
284 /* Snap Shot MV's Not support Baseline 70C */
285 IF p_use_snapshot THEN
286
287 /*----------------------------------------------------------------------------*/
288 /* Decide which of the Snap Shot fact tables to return. */
289 /* */
290 /*----------------------------------------------------------------------------*/
291
292 -- p_fact_table := 'snapshot_mv_not_supported';
293 -- bug 4639127, as snapshots are not supported still need to default
294 -- a fact table to avoid runtime errors
295 --
296 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H')THEN
297 p_fact_table := 'hri_mdp_sup_absnc_sup_mv';
298 ELSIF (p_parameter_rec.view_by = 'HRI_ABSNC+HRI_ABSNC_CAT')THEN
299 p_fact_table := 'hri_mdp_sup_absnc_cat_mv';
300 ELSE
301 p_fact_table := 'no_fact_selected';
302 END IF;
303
304 ELSE
305
306 /*----------------------------------------------------------------------------*/
307 /* Decide which fact table to return. The logic for non-snapshots is: */
308 /*----------------------------------------------------------------------------*/
309
310 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H')THEN
311 p_fact_table := 'hri_mdp_sup_absnc_sup_mv';
312 ELSIF (p_parameter_rec.view_by = 'HRI_ABSNC+HRI_ABSNC_CAT')THEN
313 p_fact_table := 'hri_mdp_sup_absnc_cat_mv';
314 ELSE
315 p_fact_table := 'no_fact_selected';
316 END IF;
317 END IF;
318
319 END set_fact_table;
320
321
322 /******************************************************************************/
323 /* Replaces tags in a SELECT column template and formats it with an alias */
324 /******************************************************************************/
325 FUNCTION format_column(p_column_string IN VARCHAR2,
326 p_start_date IN VARCHAR2,
327 p_end_date IN VARCHAR2,
328 p_bucket_id IN VARCHAR2,
329 p_column_alias IN VARCHAR2)
330 RETURN VARCHAR2 IS
331
332 l_column_string VARCHAR2(1000);
333
334 BEGIN
335
336 /* Replace the start date */
337 l_column_string := REPLACE(p_column_string, '<start_date>', p_start_date);
338
339 /* Replace the end date */
340 l_column_string := REPLACE(l_column_string, '<end_date>', p_end_date);
341
342 /* Replace the bucket identifier */
343 l_column_string := REPLACE(l_column_string, '<bucket_id>', p_bucket_id);
344
345 /* Format the column string replacing the start date */
346 l_column_string :=
347 ',' || l_column_string || ' ' || p_column_alias || g_rtn;
348
349 RETURN l_column_string;
350
351 END format_column;
352
353
354 /******************************************************************************/
355 /* This function returns a list of columns to be added to the SELECT clause */
356 /* for a given measure. The input fields contain the templates to use for */
357 /* the measure SELECT columns and various control fields. */
358 /* */
359 /* The following fields control sampling across different periods */
360 /* - include_comp: the measure is sampled for the comparison period */
361 /* */
362 /* If a bucket dimension is specified then all measures will be sampled for */
363 /* each period for each bucket value (in addition to the values across all */
364 /* buckets). */
365 /* - bucket_dim: the measures is sampled for all buckets of dimension */
366 /* */
367 /******************************************************************************/
368 FUNCTION build_columns
369 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
370 p_abs_params IN abs_fact_param_type,
371 p_template_standard IN VARCHAR2,
372 p_template_bucket IN VARCHAR2,
373 p_measure_alias IN VARCHAR2)
374 RETURN VARCHAR2 IS
375
376 /* Return string */
377 l_column_list VARCHAR2(5000);
378
379 /* Table of buckets for the given bucket dimension */
380 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
381
382 /* Column Templates */
383 l_bucket_column VARCHAR2(1000);
384
385 BEGIN
386
387 /* Always get the column for the current period */
388 /************************************************/
389 l_column_list := l_column_list || format_column
390 (p_column_string => p_template_standard
391 ,p_start_date => g_binds('TIME_CURR_START_DATE')
392 ,p_end_date => g_binds('TIME_CURR_END_DATE')
393 ,p_bucket_id => NULL
394 ,p_column_alias => 'curr_' || p_measure_alias);
395
396 /* Check for comparison period columns */
397 /***************************************/
398 IF (p_abs_params.include_comp = 'Y') THEN
399 l_column_list := l_column_list || format_column
400 (p_column_string => p_template_standard
401 ,p_start_date => g_binds('TIME_COMP_START_DATE')
402 ,p_end_date => g_binds('TIME_COMP_END_DATE')
403 ,p_bucket_id => NULL
407 RETURN l_column_list;
404 ,p_column_alias => 'comp_' || p_measure_alias);
405 END IF;
406
408
409 END build_columns;
410
411 /******************************************************************************/
412 /* Returns the final SQL statement for the PMV report. */
413 /* */
414 /* The SQL returned is in the format: */
415 /* */
416 /* SELECT */
417 /* Grouping column (view by) */
418 /* Specific measure columns, including sampling */
419 /* across different periods and buckets */
420 /* FROM */
421 /* Fact table */
422 /* WHERE */
423 /* Apply parameters corresponding to user selection */
424 /* in the PMV report */
425 /* Join to time dimension sampling all required periods */
426 /* GROUP BY */
427 /* Grouping column (view by) */
428 /* */
429 /* SELECT */
430 /* ====== */
431 /* Calls build_columns for each measure selected to build up the SELECT */
432 /* clause. */
433 /* */
434 /* For details of the SELECT column list see package header */
435 /* */
436 /* FROM/WHERE */
437 /* ========== */
438 /* Puts together the FROM/WHERE clauses depending on whether the view by */
439 /* manager special case is selected. */
440 /******************************************************************************/
441 FUNCTION build_sql
442 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
443 p_abs_params IN abs_fact_param_type,
444 p_use_snapshot IN BOOLEAN,
445 p_fact_table IN VARCHAR2,
446 p_fact_conditions IN VARCHAR2)
447 RETURN VARCHAR2 IS
448
449 /* Whether to format the SQL for the view by manager special case */
450 l_view_by_manager BOOLEAN;
451
452 /* Dynamic SQL columns */
453 /***********************/
454 /* Select */
455 l_direct_ind VARCHAR2(1000);
456 l_col_list VARCHAR2(10000);
457
458 /* From / Where */
459 l_from_clause VARCHAR2(1000);
460 l_mgr_direct_condition VARCHAR2(1000);
461 l_gen_direct_condition VARCHAR2(1000);
462 l_date_condition VARCHAR2(1000);
463 l_snapshot_condition VARCHAR2(1000);
464
465 /* Column Templates */
466 l_template_sepcat VARCHAR2(1000);
467 l_template_sepcat_bucket VARCHAR2(1000);
468 l_template_abs_period VARCHAR2(1000);
469
470 /* Return string */
471 l_fact_sql VARCHAR2(10000);
472
473 BEGIN
474
475 /******************************************************************************/
476 /* INITIALIZATION */
477 /******************/
478
479 /* Check for view by manager special case */
480 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
481 p_abs_params.kpi_mode = 'N') THEN
482 l_view_by_manager := TRUE;
483 ELSE
484 l_view_by_manager := FALSE;
485 END IF;
486
487 /* Set dynamic SQL based on view by (manager special case for non-KPI reports) */
488 IF (l_view_by_manager) THEN
489
490 /******************************************************************************/
491 /* VIEW BY MANAGER SPECIAL CASE */
492 /********************************/
493
494 l_direct_ind := '1 - suph.sub_relative_level';
495
496 l_from_clause :=
497 ' hri_cs_suph suph
498 ,' || p_fact_table || ' absn
499 WHERE suph.sup_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || '
500 AND suph.sub_relative_level <= 1
501 AND suph.sub_invalid_flag_code = ''N''
502 AND ' || l_direct_ind ||' = absn.direct_record_ind
503 AND ' || g_binds('TIME_CURR_END_DATE') || ' BETWEEN suph.effective_start_date ' ||
504 'AND suph.effective_end_date
505 AND suph.sub_person_id = absn.supervisor_person_id ' || g_rtn ||'';
506
507 /* View by anything else */
508 ELSE
509
510 /******************************************************************************/
511 /* GENERIC (OTHER VIEW BYs) */
512 /****************************/
513
514 l_direct_ind := '0';
515
516 l_from_clause :=
517 ' ' || p_fact_table || ' absn
518 WHERE absn.supervisor_person_id = ' || g_binds('HRI_PERSON+HRI_PER_USRDR_H') || g_rtn||
519 'AND '|| l_direct_ind ||' = absn.direct_record_ind ' || g_rtn ||'';
520
521 END IF;
522
523
524 /******************************************************************************/
525 /* SELECT CLAUSE */
526 /*****************/
527
528 /* Build up SELECT column list */
529 IF (p_abs_params.include_abs_drtn_days = 'Y') THEN
530 l_col_list := l_col_list || build_columns
534 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'abs_drtn_days'),
531 (p_parameter_rec => p_parameter_rec,
532 p_abs_params => p_abs_params,
533 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'abs_drtn_days'),
535 p_measure_alias => 'abs_drtn_days');
536 END IF;
537
538 /* Build up SELECT column list */
539 IF (p_abs_params.include_abs_drtn_hrs = 'Y') THEN
540 l_col_list := l_col_list || build_columns
541 (p_parameter_rec => p_parameter_rec,
542 p_abs_params => p_abs_params,
543 p_template_standard => REPLACE(g_template_standard, '<measure_column>', 'abs_drtn_hrs'),
544 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'abs_drtn_hrs'),
545 p_measure_alias => 'abs_drtn_hrs');
546 END IF;
547
548 l_template_abs_period := REPLACE(g_template_abs_period, '<measure_column>', 'abs_start_blnc+abs_nstart_blnc');
549 l_template_abs_period := REPLACE(l_template_abs_period, '<measure_column2>', 'abs_start_blnc');
550
551 /* Build up SELECT column list */
552 IF (p_abs_params.include_abs_in_period = 'Y') THEN
553 l_col_list := l_col_list || build_columns
554 (p_parameter_rec => p_parameter_rec,
555 p_abs_params => p_abs_params,
556 p_template_standard => l_template_abs_period,
557 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'abs_start_blnc + abs_nstart_blnc'),
558 p_measure_alias => 'abs_in_period');
559 END IF;
560
561 l_template_abs_period := REPLACE(g_template_abs_period, '<measure_column>', 'abs_ntfctn_days_start_blnc + abs_ntfctn_days_nstart_blnc');
562 l_template_abs_period := REPLACE(l_template_abs_period, '<measure_column2>', 'abs_ntfctn_days_start_blnc');
563
564 /* Build up SELECT column list */
565 IF (p_abs_params.include_abs_ntfctn_period = 'Y') THEN
566 l_col_list := l_col_list || build_columns
567 (p_parameter_rec => p_parameter_rec,
568 p_abs_params => p_abs_params,
569 p_template_standard => l_template_abs_period,
570 p_template_bucket => REPLACE(g_template_bucket, '<measure_column>', 'abs_ntfctn_days_start_blnc + abs_ntfctn_days_nstart_blnc'),
571 p_measure_alias => 'abs_ntfctn_period');
572 END IF;
573
574 /******************************************************************************/
575 /* FROM CLAUSE */
576 /***************/
577
578 IF p_use_snapshot THEN
579
580 /* Set date condition depending on whether the previous period is set */
581 IF (p_abs_params.include_comp = 'Y') THEN
582 l_date_condition := l_date_condition ||
583 'AND absn.effective_date BETWEEN ' || g_binds('TIME_COMP_START_DATE') ||
584 ' AND ' || g_binds('TIME_CURR_END_DATE') || '
585 AND absn.effective_date NOT BETWEEN ' || g_binds('TIME_COMP_END_DATE') || ' + 1 ' ||
586 'AND ' || g_binds('TIME_CURR_START_DATE') || ' - 1' || g_rtn;
587
588 ELSE
589 l_date_condition := l_date_condition ||
590 'AND absn.effective_date BETWEEN ' || g_binds('TIME_CURR_START_DATE') ||
591 ' AND ' || g_binds('TIME_CURR_END_DATE') || g_rtn;
592 END IF;
593
594
595 ELSE
596
597 /* Set date condition depending on whether the previous period is set */
598 IF (p_abs_params.include_comp = 'Y') THEN
599 l_date_condition := l_date_condition ||
600 'AND absn.effective_date BETWEEN ' || g_binds('TIME_COMP_START_DATE') ||
601 ' AND ' || g_binds('TIME_CURR_END_DATE') || '
602 AND absn.effective_date NOT BETWEEN ' || g_binds('TIME_COMP_END_DATE') || ' + 1 ' ||
603 'AND ' || g_binds('TIME_CURR_START_DATE') || ' - 1' || g_rtn;
604
605 ELSE
606 l_date_condition := l_date_condition ||
607 'AND absn.effective_date BETWEEN ' || g_binds('TIME_CURR_START_DATE') ||
608 ' AND ' || g_binds('TIME_CURR_END_DATE') || g_rtn;
609 END IF;
610
611 END IF;
612
613 /******************************************************************************/
614 /* BUILD UP SQL STATEMENT */
615 /**************************/
616
617 l_fact_sql :=
618 'SELECT /*+ NO_MERGE INDEX(absn) */
619 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
620 (p_parameter_rec.view_by).fact_viewby_col || ' vby_id
621 ,' || l_direct_ind || ' direct_ind ' || g_rtn ||
622 l_col_list ||
623 'FROM' || g_rtn ||
624 l_from_clause ||
625 p_fact_conditions ||
626 l_date_condition ||
627 l_snapshot_condition ||
628 'GROUP BY
629 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
630 (p_parameter_rec.view_by).fact_viewby_col || g_rtn ||
631 ',' || l_direct_ind;
632
633 RETURN l_fact_sql;
634
635 END build_sql;
636
637
638 /******************************************************************************/
639 /* Main entry point, takes PMV parameters and SQL control parameters */
640 /* Returns the SQL statement for the PMV report. */
641 /******************************************************************************/
642 FUNCTION get_sql
643 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
644 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
645 p_abs_params IN abs_fact_param_type)
646 RETURN VARCHAR2 IS
647
648 l_date_list VARCHAR2(3000);
649 l_return_sql VARCHAR2(32767);
650 l_parameter_count PLS_INTEGER;
651 l_single_param VARCHAR2(100);
652 l_fact_table VARCHAR2(30);
653 l_fact_conditions VARCHAR2(1000);
654 l_use_snapshot BOOLEAN;
655
656 BEGIN
657
658 /* Populate a global record with the PMV context in the given bind format */
659 populate_global_bind_table
660 (p_bind_tab => p_bind_tab,
664 /* which fact/function to use */
661 p_bind_format => p_abs_params.bind_format);
662
663 /* Analyze parameters to build up fact condition and help decide */
665 analyze_parameters
666 (p_bind_tab => p_bind_tab,
667 p_fact_conditions => l_fact_conditions,
668 p_parameter_count => l_parameter_count,
669 p_single_param => l_single_param);
670
671 /* Decide which fact table(s) to use */
672 set_fact_table
673 (p_parameter_rec => p_parameter_rec,
674 p_bucket_dim => p_abs_params.bucket_dim,
675 p_abs_drtn_days => p_abs_params.include_abs_drtn_days,
676 p_abs_drtn_hrs => p_abs_params.include_abs_drtn_hrs,
677 p_abs_in_period => p_abs_params.include_abs_in_period,
678 p_abs_ntfctn_period => p_abs_params.include_abs_ntfctn_period,
679
680 p_parameter_count => l_parameter_count,
681 p_single_param => l_single_param,
682 p_use_snapshot => l_use_snapshot,
683 p_fact_table => l_fact_table);
684
685 /* Set column templates */
686 initialize_globals(p_use_snapshot => l_use_snapshot);
687
688 /* Build SQL statement */
689 l_return_sql := build_sql
690 (p_parameter_rec => p_parameter_rec,
691 p_abs_params => p_abs_params,
692 p_use_snapshot => l_use_snapshot,
693 p_fact_table => l_fact_table,
694 p_fact_conditions => l_fact_conditions);
695
696 RETURN l_return_sql;
697
698 END get_sql;
699
700
701 /******************************************************************************/
702 /* Main entry point, takes PMV parameters and SQL control parameters */
703 /* Returns the SQL statement for the PMV report. */
704 /* Version with debugging built in */
705 /******************************************************************************/
706 FUNCTION get_sql
707 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
708 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
709 p_abs_params IN abs_fact_param_type,
710 p_calling_module IN VARCHAR2)
711 RETURN VARCHAR2 IS
712
713 l_abs_params abs_fact_param_type;
714 l_debug_mode BOOLEAN := FALSE;
715 l_debug_sql VARCHAR2(32767);
716
717 BEGIN
718
719 /* If debugging is on log the calling module, parameters and debug sql */
720 IF (l_debug_mode) THEN
721 l_abs_params := p_abs_params;
722 l_abs_params.bind_format := 'SQL';
723 l_debug_sql := get_sql
724 (p_parameter_rec => p_parameter_rec,
725 p_bind_tab => p_bind_tab,
726 p_abs_params => l_abs_params);
727 -- call_debug_api(l_debug_sql);
728 END IF;
729
730 RETURN get_sql
731 (p_parameter_rec => p_parameter_rec,
732 p_bind_tab => p_bind_tab,
733 p_abs_params => p_abs_params);
734
735 END get_sql;
736
737 END hri_bpl_fact_abs_sql;
[Home] [Help]