[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_ABS_SQL is used to determine */
/* the appropriate fact object. */
/* */
/* WHERE */
/* ----- */
/* set_conditions adds in any extra conditions required e.g. in top 4 */
/* categories a filter on the top 4 categories codes is added */
/* */
/* 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 that brings in periods with no data by */
/* doing a UNION ALL with the trend periods table. */
/* */
/* Note: Snapshoting not currently supported in first release of Absences */
/* */
/******************************************************************************/
--
-- 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_abs_drtn_days IN VARCHAR2,
p_include_abs_drtn_hrs IN VARCHAR2,
p_include_abs_in_period IN VARCHAR2,
p_include_abs_ntfctn_period 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_abs_drtn_days) ||
' period_abs_drtn_days' || g_rtn;
p_select_sql := p_select_sql || ',' ||
REPLACE(REPLACE(l_column_bucket,
'', l_measure_abs_drtn_days),
'', l_bucket_tab(i).bucket_id_string) ||
' period_abs_drtn_days_' || l_bucket_tab(i).bucket_name || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_abs_drtn_hrs) ||
' period_abs_drtn_hrs' || g_rtn;
p_select_sql := p_select_sql || ',' ||
REPLACE(REPLACE(l_column_bucket,
'', l_measure_abs_drtn_hrs),
'', l_bucket_tab(i).bucket_id_string) ||
' period_abs_drtn_hrs_' || l_bucket_tab(i).bucket_name || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_abs_drtn_in_prd) ||
' period_abs_in_period' || g_rtn;
p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
'', l_measure_abs_drtn_ntf_prd) ||
' period_abs_ntfctn_period' || 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_abs_drtn_days => p_trend_sql_params.include_abs_drtn_days,
p_include_abs_drtn_hrs => p_trend_sql_params.include_abs_drtn_hrs,
p_include_abs_in_period => p_trend_sql_params.include_abs_in_period,
p_include_abs_ntfctn_period => p_trend_sql_params.include_abs_ntfctn_period,
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';