APPS.HRI_BPL_TREND_TRN_SQL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29

/*   SELECT                                                                   */
/*    Period Id (Date)                                                        */
/*    Period Order                                                            */
/*    Measure columns (merged)                                                */
/*   FROM                                                                     */
/*    Terminations by Trend Period                                            */
/*    Headcount at Trend Period End                                           */
/*    Headcount at Trend Period Start (if required)                           */
/*   WHERE                                                                    */
/*    Join fact tables on period id                                           */
/*                                                                            */
/*                                                                            */
/******************************************************************************/
--
-- -------------------------------------------------------------------------
-- This procedure returns the trend inner SQL for turnover and the list of
-- measure columns used
-- -------------------------------------------------------------------------
--
PROCEDURE get_sql
 (p_parameter_rec     IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
  p_bind_tab          IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
  p_trend_sql_params  IN hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE,
  p_fact_sql          OUT NOCOPY VARCHAR2,
  p_measure_columns   OUT NOCOPY hri_oltp_pmv_query_trend.trend_measure_cols_type)
IS
  --
  -- Stores the trend SQL for termination
  --
  l_termination_sql     VARCHAR2(32767);

Line: 75

  l_sql_select          VARCHAR2(10000);

Line: 123

  /* Add turnover measure columns to select list */
    l_sql_select := l_sql_select || ',sep.' || l_trm_measure_cols(j) || g_rtn;

Line: 151

            l_sql_select := l_sql_select ||
',(wmv.' || l_hdc_measure_cols(i) || ' + ' ||
'NVL(wmv_start.' || l_hdc_measure_cols(i) || ', 0)) / 2  ' ||
 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn') || g_rtn;

Line: 157

        l_sql_select := l_sql_select || ',wmv.' || l_hdc_measure_cols(i) || g_rtn;

Line: 165

'SELECT
 sep.period_as_of_date
,sep.period_order' || g_rtn ||
 l_sql_select ||
'FROM
 ('||l_termination_sql      ||')  sep
,('||l_headcount_sql        ||')  wmv
,('||l_headcount_start_sql  ||')  wmv_start
WHERE sep.period_as_of_date = wmv.period_as_of_date
AND wmv.period_as_of_date = wmv_start.period_as_of_date (+)';

Line: 187

          l_sql_select := l_sql_select ||
             ',(wmv.' || l_hdc_measure_cols(i) || ' + wmv.' ||
             REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_start') || ') / 2  ' ||
             REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn') || g_rtn;

Line: 193

          l_sql_select := l_sql_select || ',wmv.' || l_hdc_measure_cols(i) || g_rtn;

Line: 204

        l_sql_select := l_sql_select ||
  ',wmv.' || l_hdc_measure_cols(i) || '  ' ||
             REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn') || g_rtn;

Line: 214

'SELECT
 sep.period_as_of_date
,sep.period_order' || g_rtn ||
 l_sql_select ||
'FROM
 ('||l_termination_sql||')  sep
,('||l_headcount_sql  ||')  wmv
WHERE sep.period_as_of_date = wmv.period_as_of_date';