[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* [Search for SELECT|FROM|WHERE|BIND to find procedures and functions that */
/* impact the respective parts of the SQL returned] */
/* */
/* SELECT */
/* ------ */
/* For details of the SELECT column list see package header */
/* */
/* All columns have one of a few standard formats. Global variables store */
/* templates for these formats with tags in for swapping in and out parts */
/* of the template (such as measure column, bucket column, bucket value) */
/* */
/* The columns in the select clause are controlled by various fields in the */
/* input parameter record: */
/* - */
/* */
/* All selected measure columns will be sampled for the current period. */
/* Additionally the following fields in the same input parameter record */
/* control sampling for other periods: */
/* - include_comp: all measures are sampled for the comparison period */
/* */
/* If a bucket dimension is specified then all measures will be sampled for */
/* each period for each bucket value (in addition to the values across all */
/* buckets). */
/* - bucket_dim: all measures are sampled for all buckets of dimension */
/* */
/* FROM/WHERE */
/* ---------- */
/* The FROM and WHERE clauses are separate depending on whether or not */
/* the report is view by manager. */
/* */
/* The fact table is chosen based on the parameters selected in the PMV */
/* report in the function set_fact_table. If snapshotting is available the */
/* corresponding snapshot fact will be selected. */
/* */
/* The parameters selected in the PMV report are analysed in the function */
/* analyze_parameters. A condition is added to the WHERE clause for each */
/* PMV report parameter that is set. */
/* */
/* VIEW BY */
/* ------- */
/* The view by grouping is controlled by the parameter passed in by PMV. If */
/* view by manager is selected then an additional level of the supervisor */
/* hierarchy is brought in so that the result set is grouped by the top */
/* manager's direct reports UNLESS in the input parameter record */
/* the following field is set: */
/* - kpi_mode: groups by top manager instead of their direct reports when */
/* view by of manager is selected */
/* */
/* Binds */
/* ----- */
/* Bind values are passed in using the p_bind_tab parameter. */
/* - bind_format: SQL (direct substitution) or PMV (run time substitution) */
/* */
/* If SQL format is chosen the sql string is passed to: */
/* hri_oltp_pmv_util_pkg.substitute_bind_values */
/* */
/* where the actual values are substituted into the binds. */
/******************************************************************************/
/******************************************************************************/
/* Initialization of global variables - called once at package initialization */
/* */
/* Templates for the SELECT columns are set with tags to represent the parts */
/* which vary. */
/******************************************************************************/
PROCEDURE initialize_globals(p_use_snapshot IN BOOLEAN) IS
BEGIN
IF p_use_snapshot THEN
/* Define generic select column */
g_template_standard :=
'SUM(CASE WHEN absn.effective_date BETWEEN AND
THEN
ELSE 0
END)';
/* Define generic bucketed select column */
g_template_bucket :=
'SUM(CASE WHEN absn.effective_date =
AND =
THEN
ELSE 0
END)';
/* Define generic select column */
g_template_standard :=
'SUM(CASE WHEN absn.effective_date BETWEEN AND
THEN
ELSE 0
END)';
/* Define generic bucketed select column */
g_template_bucket :=
'SUM(CASE WHEN absn.effective_date BETWEEN AND
AND =
THEN
ELSE 0
END)';
/* Populates g_binds with the selected BIND format */
/******************************************************************************/
PROCEDURE populate_global_bind_table
(p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
p_bind_format IN VARCHAR2) IS
l_parameter_name VARCHAR2(100);
/* parameter is set the name of that parameter is returned. This helps select */
/* the most efficient fact to retrieve the data from. */
/******************************************************************************/
PROCEDURE analyze_parameters
(p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
p_fact_conditions OUT NOCOPY VARCHAR2,
p_parameter_count OUT NOCOPY PLS_INTEGER,
p_single_param OUT NOCOPY VARCHAR2) IS
l_single_param VARCHAR2(100);
/* If a fact table is selected that does not have a snapshot available then */
/* p_use_snapshot is set accordingly */
/******************************************************************************/
PROCEDURE set_fact_table
(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
p_bucket_dim IN VARCHAR2,
p_abs_drtn_days IN VARCHAR2,
p_abs_drtn_hrs IN VARCHAR2,
p_abs_in_period IN VARCHAR2,
p_abs_ntfctn_period IN VARCHAR2,
p_parameter_count IN PLS_INTEGER,
p_single_param IN VARCHAR2,
p_use_snapshot IN OUT NOCOPY BOOLEAN,
p_fact_table OUT NOCOPY VARCHAR2) IS
/* Not required at this time */
l_wcnt_vby_table VARCHAR2(30);
p_fact_table := 'no_fact_selected';
p_fact_table := 'no_fact_selected';
/* Replaces tags in a SELECT column template and formats it with an alias */
/******************************************************************************/
FUNCTION format_column(p_column_string IN VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_bucket_id IN VARCHAR2,
p_column_alias IN VARCHAR2)
RETURN VARCHAR2 IS
l_column_string VARCHAR2(1000);
/* This function returns a list of columns to be added to the SELECT clause */
/* for a given measure. The input fields contain the templates to use for */
/* the measure SELECT columns and various control fields. */
/* */
/* The following fields control sampling across different periods */
/* - include_comp: the measure is sampled for the comparison period */
/* */
/* If a bucket dimension is specified then all measures will be sampled for */
/* each period for each bucket value (in addition to the values across all */
/* buckets). */
/* - bucket_dim: the measures is sampled for all buckets of dimension */
/* */
/******************************************************************************/
FUNCTION build_columns
(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
p_abs_params IN abs_fact_param_type,
p_template_standard IN VARCHAR2,
p_template_bucket IN VARCHAR2,
p_measure_alias IN VARCHAR2)
RETURN VARCHAR2 IS
/* Return string */
l_column_list VARCHAR2(5000);
/* SELECT */
/* Grouping column (view by) */
/* Specific measure columns, including sampling */
/* across different periods and buckets */
/* FROM */
/* Fact table */
/* WHERE */
/* Apply parameters corresponding to user selection */
/* in the PMV report */
/* Join to time dimension sampling all required periods */
/* GROUP BY */
/* Grouping column (view by) */
/* */
/* SELECT */
/* ====== */
/* Calls build_columns for each measure selected to build up the SELECT */
/* clause. */
/* */
/* For details of the SELECT column list see package header */
/* */
/* FROM/WHERE */
/* ========== */
/* Puts together the FROM/WHERE clauses depending on whether the view by */
/* manager special case is selected. */
/******************************************************************************/
FUNCTION build_sql
(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
p_abs_params IN abs_fact_param_type,
p_use_snapshot IN BOOLEAN,
p_fact_table IN VARCHAR2,
p_fact_conditions IN VARCHAR2)
RETURN VARCHAR2 IS
/* Whether to format the SQL for the view by manager special case */
l_view_by_manager BOOLEAN;
/* Select */
l_direct_ind VARCHAR2(1000);
/* SELECT CLAUSE */
/*****************/
/* Build up SELECT column list */
IF (p_abs_params.include_abs_drtn_days = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_abs_params => p_abs_params,
p_template_standard => REPLACE(g_template_standard, '', 'abs_drtn_days'),
p_template_bucket => REPLACE(g_template_bucket, '', 'abs_drtn_days'),
p_measure_alias => 'abs_drtn_days');
/* Build up SELECT column list */
IF (p_abs_params.include_abs_drtn_hrs = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_abs_params => p_abs_params,
p_template_standard => REPLACE(g_template_standard, '', 'abs_drtn_hrs'),
p_template_bucket => REPLACE(g_template_bucket, '', 'abs_drtn_hrs'),
p_measure_alias => 'abs_drtn_hrs');
/* Build up SELECT column list */
IF (p_abs_params.include_abs_in_period = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_abs_params => p_abs_params,
p_template_standard => l_template_abs_period,
p_template_bucket => REPLACE(g_template_bucket, '', 'abs_start_blnc + abs_nstart_blnc'),
p_measure_alias => 'abs_in_period');
/* Build up SELECT column list */
IF (p_abs_params.include_abs_ntfctn_period = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_abs_params => p_abs_params,
p_template_standard => l_template_abs_period,
p_template_bucket => REPLACE(g_template_bucket, '', 'abs_ntfctn_days_start_blnc + abs_ntfctn_days_nstart_blnc'),
p_measure_alias => 'abs_ntfctn_period');
'SELECT /*+ NO_MERGE INDEX(absn) */
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(p_parameter_rec.view_by).fact_viewby_col || ' vby_id
,' || l_direct_ind || ' direct_ind ' || g_rtn ||
l_col_list ||
'FROM' || g_rtn ||
l_from_clause ||
p_fact_conditions ||
l_date_condition ||
l_snapshot_condition ||
'GROUP BY
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(p_parameter_rec.view_by).fact_viewby_col || g_rtn ||
',' || l_direct_ind;