[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 p_wcnt_chg_params: */
/* - include_hire: hire count measures will be added */
/* - include_trin: transfer in count measures will be added */
/* - include_trout: transfer out count measures will be added */
/* - include_term: terminations count measures will be added */
/* - include_sep: separations will be added */
/* - include_sep_inv: involuntary separations will be added */
/* - include_sep_vol: voluntary separations will be added */
/* - include_low: length of work measures will be added */
/* */
/* 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 */
/* p_wcnt_chg_params 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. Depending on the */
/* bind format selected the corresponding bind strings are populated into the */
/* global g_binds table. Bind values are then substituted into the SQL from */
/* this global throughout the package. */
/* - bind_format: SQL (direct substitution) or PMV (run time substitution) */
/* */
/******************************************************************************/
/******************************************************************************/
/* 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 wcnt.effective_date =
THEN
ELSE 0
END)';
/* Define generic bucketed select column */
g_template_bucket :=
'SUM(CASE WHEN wcnt.effective_date =
AND =
THEN
ELSE 0
END)';
/* Define generic select column */
g_template_standard :=
'SUM(CASE WHEN wcnt.effective_date BETWEEN AND
THEN
ELSE 0
END)';
/* Define generic bucketed select column */
g_template_bucket :=
'SUM(CASE WHEN wcnt.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_include_hire IN VARCHAR2,
p_include_trin IN VARCHAR2,
p_include_trout IN VARCHAR2,
p_include_term IN VARCHAR2,
p_include_low 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
l_wcnt_vby_table VARCHAR2(30);
/* AND the viewby selected is Manager */
/* */
/* 2) Check whether the supervisor level change fact can be used */
/* (no bucket, no length of work, no parameters) */
/* AND the viewby selected is NOT Manager */
/* */
/* 3) Check whether the termination by assignment fact can be used */
/* (no hires or transfers) */
/* */
/* 4) If neither 1) nor 2) nor 3 then return an invalid fact table message */
/* */
/* The logic is convoluted because the original MV does not support buckets, */
/* other dimensions or the length of service measure and the new MV does not */
/* support hires or transfers */
/*----------------------------------------------------------------------------*/
/* 1) no bucket (or person type bucket), no length of work, no parameters */
/* VIEWBY Manager */
IF ((p_bucket_dim IS NULL OR p_bucket_dim = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') AND
p_include_low = 'N' AND
p_parameter_count = 0
--
and p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H'
--
) THEN
p_fact_table := 'hri_mdp_sup_wcnt_chg_mv';
/* 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_wcnt_chg_params IN wcnt_chg_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_wcnt_chg_params IN wcnt_chg_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_wcnt_chg_params.include_hire = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_wcnt_chg_params => p_wcnt_chg_params,
p_template_standard => REPLACE(g_template_standard, '', 'hire_hdc'),
p_template_bucket => REPLACE(g_template_bucket, '', 'hire_hdc'),
p_measure_alias => 'hire_hdc');
/* Build up SELECT column list */
IF (p_wcnt_chg_params.include_trin = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_wcnt_chg_params => p_wcnt_chg_params,
p_template_standard => REPLACE(g_template_standard, '', 'transfer_in_hdc'),
p_template_bucket => REPLACE(g_template_bucket, '', 'transfer_in_hdc'),
p_measure_alias => 'transfer_in_hdc');
/* Build up SELECT column list */
IF (p_wcnt_chg_params.include_trout = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_wcnt_chg_params => p_wcnt_chg_params,
p_template_standard => REPLACE(g_template_standard, '', 'transfer_out_hdc'),
p_template_bucket => REPLACE(g_template_bucket, '', 'transfer_out_hdc'),
p_measure_alias => 'transfer_out_hdc');
/* Build up SELECT column list */
IF (p_wcnt_chg_params.include_term = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_wcnt_chg_params => p_wcnt_chg_params,
p_template_standard => REPLACE(g_template_standard, '', 'termination_hdc'),
p_template_bucket => REPLACE(g_template_bucket, '', 'termination_hdc'),
p_measure_alias => 'termination_hdc');
/* Build up SELECT column list */
IF (p_wcnt_chg_params.include_low = 'Y') THEN
l_col_list := l_col_list || build_columns
(p_parameter_rec => p_parameter_rec,
p_wcnt_chg_params => p_wcnt_chg_params,
p_template_standard => REPLACE(g_template_standard, '', 'pow_months'),
p_template_bucket => REPLACE(g_template_bucket, '', 'pow_months'),
p_measure_alias => 'low_months');
'SELECT /*+ NO_MERGE INDEX(wcnt) */
' || 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;