[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_column_select VARCHAR2(1000);
/* SELECT */
/* ------ */
/* Templates for the measure columns are set by the set_metadata function. */
/* Appropriate measures are then added to the select list using the template */
/* depending on the input trend parameter record */
/* */
/* A list of all the measure columns added is maintained and returned to the */
/* calling function so that outer layers of SQL can reference all the columns */
/* */
/* FROM */
/* ---- */
/* The set_fact_table function in HRI_BPL_FACT_WRKFC_SQL is used to determine */
/* the appropriate fact object. */
/* */
/* WHERE */
/* ----- */
/* set_conditions adds in any extra conditions required e.g. in top 4 */
/* countries a filter on the top 4 country codes is added */
/* */
/* If the fact object is a snapshot MV then the date join will be an equality */
/* join rather than a between */
/* */
/* A parameter date_join_type controls whether the fact is sampled at the */
/* start or end of the trend period. This is used for e.g. the headcount for */
/* turnover calculation which may be a start/end average */
/* */
/* SQL RETURNED */
/* ============ */
/* The SQL is returned along with a list of all the measure columns in the */
/* SELECT list: */
/* */
/* SELECT */
/* Period Id (Date) */
/* Period Order */
/* Measure Columns */
/* FROM */
/* Table of periods to plot (sub-query) */
/* Snapshot/standard fact object */
/* WHERE */
/* Filter on selected manager */
/* Date filter (varies with snapshot/standard fact) */
/* Additional filters (e.g. top 4 countries) */
/* */
/* An outer layer of SQL is added for the TURNOVER calculation. */
/* An outer layer of SQL is added that brings in periods with no data by */
/* doing a UNION ALL with the trend periods table. */
/* */
/******************************************************************************/
--
-- Sets select column templates for accessing the workforce fact
--
PROCEDURE set_metadata IS
--
BEGIN
--
g_column_select := 'NVL(SUM(), 0)';
PROCEDURE set_select
(p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
p_bucket_dim IN VARCHAR2,
p_include_hdc IN VARCHAR2,
p_include_pasg_cnt IN VARCHAR2,
p_include_pasg_pow IN VARCHAR2,
p_include_extn_cnt IN VARCHAR2,
p_include_extn_pow IN VARCHAR2,
p_include_hdc_trn IN VARCHAR2,
p_include_sal IN VARCHAR2,
p_use_snapshot IN BOOLEAN,
p_select_sql OUT NOCOPY VARCHAR2,
p_measure_columns OUT NOCOPY hri_oltp_pmv_query_trend.trend_measure_cols_type)
IS
-- template
l_column_bucket VARCHAR2(1000);
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_hdc) ||
' period_hdc' || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', 'fact.curr_total_hdc_start') ||
' period_hdc_start' || g_rtn;
p_select_sql := p_select_sql || ',' ||
REPLACE(REPLACE(l_column_bucket,
'', l_measure_hdc),
'', l_bucket_tab(i).bucket_id_string) ||
' period_hdc_' || l_bucket_tab(i).bucket_name || g_rtn;
p_select_sql := p_select_sql || ',' ||
REPLACE(REPLACE(l_column_bucket,
'', 'fact.curr_total_hdc_start'),
'', l_bucket_tab(i).bucket_id_string) ||
' period_hdc_start_' || l_bucket_tab(i).bucket_name || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_sal) ||
' period_sal_end' || g_rtn;
p_select_sql := p_select_sql || ',' ||
REPLACE(REPLACE(l_column_bucket,
'', l_measure_sal),
'', l_bucket_tab(i).bucket_id_string) ||
' period_sal_' || l_bucket_tab(i).bucket_name || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_pasg_cnt) ||
' period_pasg_cnt' || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_pasg_pow) ||
' period_pasg_pow' || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_extn_cnt) ||
' period_extn_cnt' || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_extn_pow) ||
' period_extn_pow' || g_rtn;
END set_select;
l_select_sql VARCHAR2(32767);
set_select
(p_parameter_rec => l_parameter_rec,
p_bucket_dim => p_trend_sql_params.bucket_dim,
p_include_hdc => p_trend_sql_params.include_hdc,
p_include_pasg_cnt => p_trend_sql_params.include_pasg_cnt,
p_include_pasg_pow => p_trend_sql_params.include_pasg_pow,
p_include_extn_cnt => p_trend_sql_params.include_extn_cnt,
p_include_extn_pow => p_trend_sql_params.include_extn_pow,
p_include_hdc_trn => p_trend_sql_params.include_hdc_trn,
p_include_sal => p_trend_sql_params.include_sal,
p_use_snapshot => l_use_snapshot,
p_select_sql => l_select_sql,
p_measure_columns => p_measure_columns);
'SELECT /*+ LEADING(tro) INDEX(fact) */
tro.period_as_of_date
,tro.period_order' || g_rtn ||
l_select_sql ||
'FROM
' || l_trend_periods_tbl || ' tro
,' || l_fact_table || ' fact
WHERE fact.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H' || g_rtn ||
l_date_join ||
l_fact_condition ||
l_param_conditions ||
'GROUP BY
tro.period_order
,tro.period_as_of_date';