[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 */
/* */
/* Note: Snapshot MVs have a different column format and are handled by */
/* a different set of functions */
/* */
/* 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_wrkfc_params: */
/* - include_hdc: headcount measures will be added to SELECT columns */
/* - include_sal: salary measures will be added to SELECT columns */
/* - include_low: length of work measures will be added to SELECT columns */
/* */
/* All selected measure columns will be sampled at the effective date. */
/* Additionally the following fields in the same input parameter record */
/* control sampling at other dates: */
/* - include_comp: all measures are sampled at comparison period end date */
/* - include_start: all affected measures are sampled at period start dates */
/* */
/* Currently only headcount is affected by include_start. */
/* */
/* If a bucket dimension is specified then all measures will be sampled for */
/* each date 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_wrkfc_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 IS
BEGIN
/* Define generic select column */
g_template_standard :=
'SUM(CASE WHEN effective_date =
THEN
ELSE 0
END)';
/* Define generic bucket select column */
g_template_bucket :=
'SUM(CASE WHEN effective_date =
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_sal 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
/* Fact for headcount/low only for the supervisor dimension level */
l_wrkfc_sup_table VARCHAR2(30);
/* bucket dimension is selected then use the parameter table */
ELSIF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H' AND
l_bucket_dim IS NULL AND
p_parameter_count = 1) THEN
/* 5) one parameter, no bucket set, view by manager */
l_fact_table := l_wrkfc_prm_table;
/* Replaces tags in a SELECT column template and formats it with an alias */
/******************************************************************************/
FUNCTION format_column(p_column_string IN VARCHAR2,
p_date_bind 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 dates */
/* - include_comp: the measure is sampled at comparison period end date */
/* - include_start: the measure is sampled at period start dates */
/* */
/* If a bucket dimension is specified then all measures will be sampled for */
/* each date 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_wrkfc_total IN VARCHAR2,
p_include_comp IN VARCHAR2,
p_include_start IN VARCHAR2,
p_bucket_dim IN VARCHAR2,
p_select_template IN VARCHAR2,
p_bucket_template IN VARCHAR2,
p_measure_alias IN VARCHAR2)
RETURN VARCHAR2 IS
l_column_list VARCHAR2(5000);
l_select_column VARCHAR2(1000);
l_select_column := REPLACE(p_select_template, '', p_measure_alias);
(p_column_string => l_select_column
,p_date_bind => g_binds('TIME_CURR_START_DATE') || ' - 1'
,p_bucket_id => NULL
,p_column_alias => 'curr_' || p_measure_alias || '_start');
(p_column_string => l_select_column
,p_date_bind => g_binds('TIME_CURR_END_DATE')
,p_bucket_id => NULL
,p_column_alias => 'curr_' || p_measure_alias || '_end');
(p_column_string => l_select_column
,p_date_bind => g_binds('TIME_COMP_START_DATE') || ' - 1'
,p_bucket_id => NULL
,p_column_alias => 'comp_' || p_measure_alias || '_start');
(p_column_string => l_select_column
,p_date_bind => g_binds('TIME_COMP_END_DATE')
,p_bucket_id => NULL
,p_column_alias => 'comp_' || p_measure_alias || '_end');
/* Returns a string containing columns to be added to the outer SELECT clause */
/* for the given measure. */
/******************************************************************************/
FUNCTION add_outer_measure_columns
(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
p_wrkfc_params IN wrkfc_fact_param_type,
p_view_by_manager IN BOOLEAN,
p_sample_start_dates IN VARCHAR2,
p_measure_alias IN VARCHAR2)
RETURN VARCHAR2 IS
/* Column Templates */
l_template_bucket VARCHAR2(1000);
p_select_template => g_template_standard,
p_bucket_template => l_template_bucket,
p_measure_alias => p_measure_alias)
|| build_columns
(p_parameter_rec => p_parameter_rec,
p_wrkfc_total => 'Y',
p_include_comp => p_wrkfc_params.include_comp,
p_include_start => l_include_start,
p_bucket_dim => p_wrkfc_params.bucket_dim,
p_select_template => l_template_total,
p_bucket_template => l_template_total_bucket,
p_measure_alias => 'total_' || p_measure_alias);
/* SELECT -- outer */
/* Grouping column (view by) */
/* Specific measure columns, including totals and */
/* sampling across different dates and buckets */
/* FROM */
/* (SELECT -- inner */
/* Raw measure columns, including function calls */
/* e.g. to convert currency */
/* FROM */
/* Fact table */
/* Time dimension */
/* WHERE */
/* Apply parameters corresponding to user selection */
/* in the PMV report */
/* Join to time dimension sampling all dates required */
/* ) */
/* 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_wrkfc_params IN wrkfc_fact_param_type,
p_fact_table IN VARCHAR2,
p_date_list 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(100);
/* SELECT CLAUSE */
/*****************/
/* Add bucket column to select clause if applicable */
IF (p_wrkfc_params.bucket_dim IS NOT NULL) THEN
l_inner_col_list := l_inner_col_list || ',' ||
hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(p_wrkfc_params.bucket_dim).fact_viewby_col || g_rtn;
/* Build up SELECT column list */
IF (p_wrkfc_params.include_hdc = 'Y') THEN
l_inner_col_list := l_inner_col_list ||
',' || l_hdc_col || ' hdc' || g_rtn ||
',' || l_total_hdc_col || ' total_hdc' || g_rtn;
/* Build up SELECT column list */
IF (p_wrkfc_params.include_sal = 'Y') THEN
l_inner_col_list := l_inner_col_list ||
',' || l_sal_col || ' sal' || g_rtn ||
',' || l_total_sal_col || ' total_sal' || g_rtn;
/* Build up SELECT column list */
IF (p_wrkfc_params.include_low = 'Y') THEN
l_inner_col_list := l_inner_col_list ||
',' || l_low_col || ' low' || g_rtn ||
',' || l_total_low_col || ' total_low' || g_rtn;
/* Build up SELECT column list */
IF (p_wrkfc_params.include_pasg_cnt = 'Y') THEN
l_inner_col_list := l_inner_col_list ||
',' || l_pasg_cnt_col || ' pasg_cnt' || g_rtn ||
',' || l_pasg_cnt_col || ' total_pasg_cnt' || g_rtn;
'SELECT /*+ NO_MERGE */
vby_id
,direct_ind' || g_rtn ||
/* Dynamically built column list */
l_outer_col_list ||
'FROM (
SELECT /*+ ORDERED INDEX(wrkfc) */
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(p_parameter_rec.view_by).fact_viewby_col || ' vby_id
,cal.id effective_date
,' || l_direct_ind || ' direct_ind ' || g_rtn ||
l_inner_col_list ||
' FROM ' || g_rtn ||
l_inner_from || '
)
GROUP BY
vby_id
,direct_ind';
/* This function returns a list of columns to be added to the SELECT clause */
/* for a given measure using snapshot fact. Analgous to build_columns */
/******************************************************************************/
FUNCTION build_columns_snp
(p_sample_start_dates IN VARCHAR2,
p_include_comp IN VARCHAR2,
p_measure_code IN VARCHAR2,
p_measure_alias IN VARCHAR2,
p_column_template IN VARCHAR2,
p_total_template IN VARCHAR2,
p_bucket_template IN VARCHAR2,
p_bucket_dim IN VARCHAR2,
p_bucket_tab IN hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype)
RETURN VARCHAR2 IS
l_col_list VARCHAR2(10000);
/* SELECT */
/* Grouping column (view by) */
/* Specific measure columns, including totals and */
/* sampling across different dates and buckets */
/* FROM */
/* Fact table */
/* Time dimension */
/* Supervisor table (if view by manager and non-kpi report) */
/* WHERE */
/* Apply parameters corresponding to user selection */
/* in the PMV report */
/* Join to time dimension sampling all dates required */
/* GROUP BY */
/* Grouping column (view by) */
/* */
/* SELECT */
/* ====== */
/* Adds 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_snp
(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
p_wrkfc_params IN wrkfc_fact_param_type,
p_fact_table IN VARCHAR2,
p_date_list 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(100);
/* SELECT CLAUSE */
/*****************/
/* Build up SELECT column list */
IF (p_wrkfc_params.include_hdc = 'Y') THEN
l_col_list := l_col_list || build_columns_snp
(p_sample_start_dates => 'Y',
p_include_comp => p_wrkfc_params.include_comp,
p_measure_code => 'hdc',
p_measure_alias => 'hdc',
p_column_template => l_column_template,
p_total_template => l_total_template,
p_bucket_template => l_bucket_template,
p_bucket_dim => p_wrkfc_params.bucket_dim,
p_bucket_tab => l_bucket_tab);
/* Build up SELECT column list */
IF (p_wrkfc_params.include_low = 'Y') THEN
l_col_list := l_col_list || build_columns_snp
(p_sample_start_dates => 'N',
p_include_comp => p_wrkfc_params.include_comp,
p_measure_code => 'pow',
p_measure_alias => 'low',
p_column_template => l_column_template,
p_total_template => l_total_template,
p_bucket_template => l_bucket_template,
p_bucket_dim => p_wrkfc_params.bucket_dim,
p_bucket_tab => l_bucket_tab);
/* Build up SELECT column list */
IF (p_wrkfc_params.include_sal = 'Y') THEN
l_col_list := l_col_list || build_columns_snp
(p_sample_start_dates => 'N',
p_include_comp => p_wrkfc_params.include_comp,
p_measure_code => 'anl_slry',
p_measure_alias => 'sal',
p_column_template => add_conv_func(l_column_template),
p_total_template => add_conv_func(l_total_template),
p_bucket_template => add_conv_func(l_bucket_template),
p_bucket_dim => p_wrkfc_params.bucket_dim,
p_bucket_tab => l_bucket_tab);
/* Build up SELECT column list */
IF (p_wrkfc_params.include_pasg_cnt = 'Y') THEN
l_col_list := l_col_list || build_columns_snp
(p_sample_start_dates => 'N',
p_include_comp => p_wrkfc_params.include_comp,
p_measure_code => 'pasg_cnt',
p_measure_alias => 'pasg_cnt',
p_column_template => l_column_template,
p_total_template => l_total_template,
p_bucket_template => l_bucket_template,
p_bucket_dim => p_wrkfc_params.bucket_dim,
p_bucket_tab => l_bucket_tab);
'SELECT /*+ NO_MERGE ORDERED INDEX(wrkfc) */
' || 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 ||
/* Dynamically built column list */
l_col_list ||
'FROM' || g_rtn ||
l_from_clause ||
'WHERE wrkfc.effective_date = ' || g_binds('TIME_CURR_END_DATE') || '
AND wrkfc.period_type = &PERIOD_TYPE
AND wrkfc.comparison_type = &TIME_COMPARISON_TYPE' || g_rtn ||
l_where_clause ||
p_fact_conditions ||
'GROUP BY
' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
(p_parameter_rec.view_by).fact_viewby_col || '
,' || l_direct_ind;