APPS.HRI_BPL_TREND_WRKFC_ABS_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                                                                     */
/*    Absences 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 absence 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 absence
  --
  l_absence_sql         VARCHAR2(32767);

Line: 74

  l_sql_select          VARCHAR2(10000);

Line: 126

  /* Add absence measure columns to select list */
    l_sql_select := l_sql_select || ',abs.' || l_abs_measure_cols(j) || g_rtn;

Line: 154

            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_abs') || g_rtn;

Line: 160

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

Line: 168

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

Line: 190

          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_abs') || g_rtn;

Line: 196

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

Line: 207

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

Line: 217

'SELECT
 wmv.period_as_of_date
,wmv.period_order' || g_rtn ||
 l_sql_select ||
'FROM
 ('||l_absence_sql||')  abs
,('||l_headcount_sql  ||')  wmv
WHERE abs.period_as_of_date (+) = wmv.period_as_of_date';