APPS.HRI_OLTP_PMV_QUERY_TREND SQL Statements

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

Line: 19

/* the select list.                                                           */
/*                                                                            */
/* Third the merged SQL is put together, which looks like:                    */
/*                                                                            */
/*   SELECT                                                                   */
/*    Period Id (Date)                                                        */
/*    Period Order                                                            */
/*    SUM(Measure columns)    (returned from fact sql)                        */
/*   FROM                                                                     */
/*    (Fact SQL                                                               */
/*     UNION ALL                                                              */
/*     SELECT                                                                 */
/*      Period Id (Date)                                                      */
/*      Period Order                                                          */
/*      0 (Measure columns)                                                   */
/*     FROM                                                                   */
/*      Trend Period SQL)                                                     */
/*   GROUP BY                                                                 */
/*    Period Id (Date)                                                        */
/*    Period Order                                                            */
/*                                                                            */
/******************************************************************************/

/* This procedure decides whether to call workforce, terminations or */
/* both (turnover), gets the sql for the accessing the required info */
/* and returns this along with a list of the measure columns used    */
PROCEDURE get_fact_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 trend_measure_cols_type)

IS

  l_query_wrkfc        VARCHAR2(30);

Line: 176

  l_fact_select        VARCHAR2(10000);

Line: 177

  l_union_select       VARCHAR2(10000);

Line: 178

  l_group_select       VARCHAR2(10000);

Line: 203

    l_fact_select := l_fact_select || ',' || l_measure_columns(i) || g_rtn;

Line: 204

    l_union_select := l_union_select || ',0  ' || l_measure_columns(i) || g_rtn;

Line: 205

    l_group_select := l_group_select || ',SUM(' || l_measure_columns(i) || ')  ' ||
                      l_measure_columns(i) || g_rtn;

Line: 214

'SELECT
 grp.period_as_of_date
,grp.period_order' || g_rtn ||
 l_group_select ||
'FROM (' || g_rtn ||
  l_fact_sql || g_rtn ||
'  UNION ALL
  SELECT
   tro.period_as_of_date
  ,tro.period_order'
   || g_rtn ||
  l_union_select ||
'  FROM
   (' || l_trend_periods_tbl || ')  tro
)  grp
GROUP BY
 grp.period_as_of_date
,grp.period_order';