APPS.HRI_OLTP_PMV_WMV_SAL_SUP SQL Statements

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

Line: 19

/* Selective drill url feature */
  l_drill_url1            VARCHAR2(300);

Line: 49

/* selective drill across urls */
  l_drill_url1 := 'pFunctionName=HRI_P_WRKFC_TRN_SUMMARY_PVT&' ||
                  'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
                  'VIEW_BY_NAME=VIEW_BY_ID&' ||
                  'pParamIds=Y';

Line: 82

'SELECT  -- Headcount and Salary Portlet
 tab.mgr_id                         VIEWBYID
,DECODE(tab.direct_ind , 0, ''Y'', ''N'')  DRILLPIVOTVB
,tab.mgr_value                      VIEWBY
,tab.order_by                       HRI_P_ORDER_BY_1
,tab.mgr_value                      HRI_P_CHAR2_GA
,DECODE(tab.direct_ind,
          ''1'', ''' || l_drill_url3 || ''',
        ''' || l_drill_url1 || ''') HRI_P_DRILL_URL1
,tab.curr_hdc                       HRI_P_WMV_SUM_MV
,tab.curr_sal                       HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.comp_sal',
        p_current_col  => 'tab.curr_sal') || '
                                    HRI_P_MEASURE5
,tab.curr_avg_sal                   HRI_P_MEASURE1
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.comp_avg_sal',
        p_current_col  => 'tab.curr_avg_sal') || '
                                    HRI_P_MEASURE2
,tab.comp_avg_sal                   HRI_P_MEASURE3
,tab.comp_total_sal                 HRI_P_MEASURE4
,tab.curr_total_hdc                 HRI_P_GRAND_TOTAL1
,tab.curr_total_sal                 HRI_P_GRAND_TOTAL2
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.comp_total_sal',
        p_current_col  => 'tab.curr_total_sal') || '
                                    HRI_P_GRAND_TOTAL7
,tab.curr_total_avg_sal             HRI_P_GRAND_TOTAL3
,tab.comp_total_avg_sal             HRI_P_GRAND_TOTAL5
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
       (p_previous_col => 'tab.comp_total_avg_sal',
        p_current_col  => 'tab.curr_total_avg_sal') || '
                                    HRI_P_GRAND_TOTAL4
,tab.comp_total_sal                 HRI_P_GRAND_TOTAL6
,DECODE(tab.direct_ind,
          ''0'', '''|| l_drill_url2 ||''',
        '''')                       HRI_P_CHAR1_GA
,DECODE(tab.direct_ind,
          ''0'', '''',
        ''N'')                      HRI_P_SUPH_RO_CA
FROM
(SELECT
/* View by */
  hsal.vby_id            mgr_id
 ,DECODE(hsal.direct_ind,
           1, ''' || l_direct_reports_string || ''',
         per.value)      mgr_value
 ,to_char(hsal.direct_ind) || per.order_by  order_by
 ,hsal.direct_ind    direct_ind
 ,hsal.curr_hdc_end  curr_hdc
 ,hsal.comp_hdc_end  comp_hdc
 ,hsal.curr_sal_end  curr_sal
 ,hsal.comp_sal_end  comp_sal
 ,DECODE(hsal.curr_hdc_end,
           0, to_number(null),
         hsal.curr_sal_end / hsal.curr_hdc_end)  curr_avg_sal
 ,DECODE(hsal.comp_hdc_end,
           0, to_number(null),
         hsal.comp_sal_end / hsal.comp_hdc_end)  comp_avg_sal
 ,SUM(hsal.curr_hdc_end) OVER ()        curr_total_hdc
 ,SUM(hsal.comp_total_hdc_end) OVER ()  comp_total_hdc
 ,SUM(hsal.curr_sal_end) OVER ()        curr_total_sal
 ,SUM(hsal.comp_total_sal_end) OVER ()  comp_total_sal
 ,DECODE(SUM(hsal.curr_hdc_end) OVER (), 0, to_number(null),
         SUM(hsal.curr_sal_end) OVER () /
         SUM(hsal.curr_hdc_end) OVER ())        curr_total_avg_sal
 ,DECODE(SUM(hsal.comp_total_hdc_end) OVER (), 0, to_number(null),
         SUM(hsal.comp_total_sal_end) OVER () /
         SUM(hsal.comp_total_hdc_end) OVER ())  comp_total_avg_sal
 FROM
  hri_dbi_cl_per_n_v        per
 ,(' || l_wrkfc_fact_sql || ') hsal
 WHERE per.id = hsal.vby_id
 AND (hsal.curr_hdc_end > 0
   OR hsal.curr_sal_end > 0
   OR hsal.direct_ind = 1)
 AND &BIS_CURRENT_ASOF_DATE BETWEEN per.effective_start_date
                            AND per.effective_end_date
 )  tab
WHERE 1 = 1
' || l_security_clause || '
ORDER BY ' || l_parameter_rec.order_by;

Line: 251

'SELECT -- Salary KPIs
 qry.vby_id              VIEWBYID
,qry.vby_id              VIEWBY
,qry.curr_sal_end        HRI_P_MEASURE1
,qry.comp_sal_end        HRI_P_MEASURE2
,DECODE(qry.curr_hdc_end,0, to_number(null),qry.curr_sal_end/qry.curr_hdc_end)
                         HRI_P_MEASURE4
,DECODE(qry.comp_hdc_end,0, to_number(null),qry.comp_sal_end/qry.comp_hdc_end)
                         HRI_P_MEASURE5
,qry.curr_sal_end        HRI_P_GRAND_TOTAL1
,qry.comp_sal_end        HRI_P_GRAND_TOTAL2
,DECODE(qry.curr_hdc_end, 0, to_number(null),qry.curr_sal_end / qry.curr_hdc_end)
                         HRI_P_GRAND_TOTAL4
,DECODE(qry.comp_hdc_end,0, to_number(null),qry.comp_sal_end / qry.comp_hdc_end)
                         HRI_P_GRAND_TOTAL5
FROM
('||l_inn_sql||') qry
WHERE 1=1
' || l_security_clause;