eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HRI_OLTP_PMV_ABS_WMV_PVT SQL Statements

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

Line: 122


  /* formulate the dynmaic column selection based on Absence Duration
     unit of measure paramter selection  Default Days                */

      IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS')  THEN
        l_dynmc_drtn_curr := 'curr_abs_drtn_days';

Line: 173


'SELECT  -- Absence Summary by Manager Status
 babs.vby_id	VIEWBYID
,babs.value	VIEWBY '|| g_rtn
/* Workforce */  || g_rtn ||
',NVL(curr_hdc_end, 0 )                   HRI_P_MEASURE1 '|| g_rtn
/* Absence  */ || g_rtn ||'
,NVL(babs.curr_abs_in_period,0)           HRI_P_MEASURE2
,NVL(babs.comp_abs_in_period,0)           HRI_P_MEASURE3 '|| g_rtn
/* Total Notification  */ || g_rtn ||'
,NVL(babs.curr_abs_ntfctn_period,0)       HRI_P_MEASURE4
,NVL(babs.comp_abs_ntfctn_period,0)       HRI_P_MEASURE5 '|| g_rtn
/* Average Notification  */ || g_rtn ||'
,NVL(curr_abs_avg_ntfctn_period,0)        HRI_P_MEASURE6
,NVL(comp_abs_avg_ntfctn_period,0)        HRI_P_MEASURE7'|| g_rtn
/* Change - Average Notification  */ || g_rtn ||'
,'|| hri_oltp_pmv_util_pkg.get_change_percent_sql
         (p_previous_col => 'comp_abs_avg_ntfctn_period',
          p_current_col  => 'curr_abs_avg_ntfctn_period') || '
                                          HRI_P_MEASURE6_MP'|| g_rtn
/* Total Absence Duration */ || g_rtn ||'
,NVL(babs.curr_abs_drtn,0)                HRI_P_MEASURE8
,NVL(babs.comp_abs_drtn,0)                HRI_P_MEASURE9'|| g_rtn
/* Change - Total Absence Duration  */ || g_rtn ||'
,'|| hri_oltp_pmv_util_pkg.get_change_percent_sql
         (p_previous_col => 'babs.comp_abs_drtn',
          p_current_col  => 'babs.curr_abs_drtn') || '
                                          HRI_P_MEASURE8_MP'|| g_rtn
/* Average Absence Duration  */ || g_rtn ||'
,DECODE(babs.curr_abs_in_period,0,to_number(NULL)
       ,(babs.curr_abs_drtn / babs.curr_abs_in_period)
	   )                              HRI_P_MEASURE10
,DECODE(babs.comp_abs_in_period,0,to_number(NULL)
       ,(babs.curr_abs_drtn / babs.comp_abs_in_period)
	   )                              HRI_P_MEASURE11 '|| g_rtn
/* Total Workforce */ || g_rtn ||'
,NVL(tot_curr_hdc_end,0)                  HRI_P_GRAND_TOTAL1 '|| g_rtn
/* Total Absence  */ || g_rtn ||'
,NVL(babs.curr_tot_abs_in_period,0)       HRI_P_GRAND_TOTAL2
,NVL(babs.comp_tot_abs_in_period,0)       HRI_P_GRAND_TOTAL3 '|| g_rtn
/* Total Notification  */ || g_rtn ||'
,NVL(babs.curr_tot_abs_ntfctn_period,0)   HRI_P_GRAND_TOTAL4
,NVL(babs.comp_tot_abs_ntfctn_period,0)   HRI_P_GRAND_TOTAL5 '|| g_rtn
/* Total Average Notification */ || g_rtn ||'
,NVL(babs.curr_tot_avg_abs_ntfctn_period,0)
                                          HRI_P_GRAND_TOTAL6
,NVL(babs.comp_tot_avg_abs_ntfctn_period,0)
                                          HRI_P_GRAND_TOTAL7'|| g_rtn
/* Change Total - Average Notification  */ || g_rtn ||'
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
         (p_previous_col => 'babs.comp_tot_avg_abs_ntfctn_period',
          p_current_col  => 'babs.curr_tot_avg_abs_ntfctn_period') || '
                                          HRI_P_GRAND_TOTAL6_MP'|| g_rtn
/* Total Absence Duration */ || g_rtn ||'
,NVL(babs.curr_tot_abs_drtn,0)            HRI_P_GRAND_TOTAL8
,NVL(babs.comp_tot_abs_drtn,0)            HRI_P_GRAND_TOTAL9'|| g_rtn
/* Change Total - Total Absence Duration  */ || g_rtn ||'
,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
         (p_previous_col => 'babs.comp_tot_abs_drtn',
          p_current_col  => 'babs.curr_tot_abs_drtn') || '
                                          HRI_P_GRAND_TOTAL8_MP'|| g_rtn
/* Total Average Absence Duration  */ || g_rtn ||'
,DECODE(babs.curr_tot_abs_in_period,0,to_number(NULL)
       ,(babs.curr_tot_abs_drtn  / babs.curr_tot_abs_in_period)
	   )                              HRI_P_GRAND_TOTAL10
,DECODE(babs.comp_tot_abs_in_period,0,to_number(NULL)
       ,(babs.comp_tot_abs_drtn  / babs.comp_tot_abs_in_period)
	   )                              HRI_P_GRAND_TOTAL11 '|| g_rtn
/* Order by person name default sort order */ || g_rtn ||'
,babs.order_by                           HRI_P_ORDER_BY_1 ' || g_rtn
/* Whether the row is a supervisor rollup row */ || g_rtn ||'
,DECODE(babs.direct_ind , 0, '''', ''N'') HRI_P_SUPH_RO_CA '|| g_rtn
/* Drill URLs */ || g_rtn ||'
,DECODE(babs.direct_ind,0, ''' || l_drill_mgr_sup  || '''
        ,1, ''' || l_drill_to_detail  || '''
        ,'''')                            HRI_P_DRILL_URL1
,DECODE(babs.direct_ind,0, ''''
        ,1, ''' || l_drill_abs_detail || '''
        ,'''')	                          HRI_P_DRILL_URL2
FROM
(
SELECT
/* Base Measures */
 vby.id                                      vby_id
,DECODE(wfact.direct_ind,
           1, ''' || l_direct_reports_string || ''',
         vby.value)  value
,to_char(NVL(wfact.direct_ind, 0)) || vby.order_by
                                            order_by
,wfact.direct_ind                           direct_ind
,NVL(wfact.curr_hdc_end,0)                  curr_hdc_end
,NVL(afact.'||l_dynmc_drtn_curr ||',0)      curr_abs_drtn
,NVL(afact.curr_abs_in_period,0)            curr_abs_in_period
,NVL(afact.'||l_dynmc_drtn_comp ||',0)      comp_abs_drtn
,NVL(afact.comp_abs_in_period,0)            comp_abs_in_period
,NVL(afact.curr_abs_ntfctn_period,0) 		curr_abs_ntfctn_period
,NVL(afact.comp_abs_ntfctn_period,0) 		comp_abs_ntfctn_period
,DECODE(afact.curr_abs_ntfctn_period,0,to_number(NULL)
       ,DECODE(afact.curr_abs_in_period,0,to_number(NULL)
	          ,(afact.curr_abs_ntfctn_period / afact.curr_abs_in_period)
	          )
       )                                    curr_abs_avg_ntfctn_period
,DECODE(afact.comp_abs_ntfctn_period,0,to_number(NULL)
       ,DECODE(afact.curr_abs_in_period,0,to_number(NULL)
	          ,(afact.comp_abs_ntfctn_period / afact.comp_abs_in_period)
	          )
      )                                     comp_abs_avg_ntfctn_period
,SUM(wfact.curr_hdc_end) OVER()             tot_curr_hdc_end
,:CURR_TOT_ABS_DRTN                         curr_tot_abs_drtn
,:CURR_TOT_ABS_IN_PERIOD                    curr_tot_abs_in_period
,:COMP_TOT_ABS_DRTN                         comp_tot_abs_drtn
,:COMP_TOT_ABS_IN_PERIOD                    comp_tot_abs_in_period
,:CURR_TOT_ABS_NTFCTN_PERIOD                curr_tot_abs_ntfctn_period
,:COMP_TOT_ABS_NTFCTN_PERIOD                comp_tot_abs_ntfctn_period
,DECODE(:CURR_TOT_ABS_NTFCTN_PERIOD,0,to_number(NULL)
       ,DECODE(:CURR_TOT_ABS_IN_PERIOD ,0,to_number(NULL)
              ,(:CURR_TOT_ABS_NTFCTN_PERIOD / :CURR_TOT_ABS_IN_PERIOD )
	          )
       )                                    curr_tot_avg_abs_ntfctn_period
,DECODE(:COMP_TOT_ABS_NTFCTN_PERIOD,0,to_number(NULL)
       ,DECODE(:COMP_TOT_ABS_IN_PERIOD,0,to_number(NULL)
              ,(:COMP_TOT_ABS_NTFCTN_PERIOD / :COMP_TOT_ABS_IN_PERIOD)
	          )
       )                                    comp_tot_avg_abs_ntfctn_period
FROM
 hri_dbi_cl_per_n_v   vby
,('|| l_abs_fact_sql ||') afact
,('|| l_wrkfc_fact_sql ||') wfact
WHERE
   vby.id = wfact.vby_id
AND afact.vby_id (+) = wfact.vby_id
AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date
 ' || l_security_clause || '
) babs
ORDER BY babs.direct_ind,' || l_dynsql_order_by;

Line: 407


  /* formulate the dynmaic column selection based on Absence Duration
     unit of measure paramter selection  Default Days                */

      IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS')  THEN
        l_dynmc_drtn_curr := 'curr_abs_drtn_days';

Line: 465


SELECT
 babs.vby_id	VIEWBYID
,babs.value	VIEWBY'|| g_rtn
/* Workforce */|| g_rtn ||'
,NVL(curr_hdc_end,0)                      HRI_P_MEASURE1
,NVL(comp_hdc_end,0)                      HRI_P_MEASURE2'|| g_rtn
/* Absence  */|| g_rtn ||'
,NVL(babs.curr_abs_in_period,0)           HRI_P_MEASURE3
,NVL(babs.comp_abs_in_period,0)           HRI_P_MEASURE4'|| g_rtn
/* Total Notification  */|| g_rtn ||'
,NVL(babs.curr_abs_ntfctn_period,0)       HRI_P_MEASURE5
,NVL(babs.comp_abs_ntfctn_period,0)       HRI_P_MEASURE6'|| g_rtn
/* Average Notification  */|| g_rtn ||'
,DECODE(babs.curr_abs_ntfctn_period,0, to_number(NULL)
	   ,DECODE(babs.curr_abs_in_period,0, to_number(NULL)
              ,(babs.curr_abs_ntfctn_period / babs.curr_abs_in_period)
	          )
        )                                 HRI_P_MEASURE7
,DECODE(babs.comp_abs_ntfctn_period,0,to_number(NULL)
	   ,DECODE(babs.comp_abs_in_period,0,to_number(NULL)
              ,(babs.comp_abs_ntfctn_period / babs.comp_abs_in_period)
	          )
        )                                 HRI_P_MEASURE8'|| g_rtn
/* Total Absence Duration */|| g_rtn ||'
,NVL(babs.curr_abs_drtn,0)                HRI_P_MEASURE9
,NVL(babs.comp_abs_drtn,0)                HRI_P_MEASURE10'|| g_rtn
/* Average Absence Duration  */|| g_rtn ||'
,DECODE(babs.curr_abs_in_period,0, to_number(NULL)
       ,(babs.curr_abs_drtn / babs.curr_abs_in_period)
	   )                              HRI_P_MEASURE11
,DECODE(babs.comp_abs_in_period,0, to_number(NULL)
       ,(babs.comp_abs_drtn / babs.comp_abs_in_period)
	   )                              HRI_P_MEASURE12'|| g_rtn
/* Average Absence Duration by Emp */|| g_rtn ||'
,DECODE('|| l_dynmc_hdc_curr||',0, to_number(NULL)
       ,(babs.curr_abs_drtn / '|| l_dynmc_hdc_curr||' )
	   )                              HRI_P_MEASURE13
,DECODE('|| l_dynmc_hdc_curr||' ,0,to_number(NULL)
       ,(babs.comp_abs_drtn / '|| l_dynmc_hdc_curr||' )
	   )                              HRI_P_MEASURE14'|| g_rtn
/* Total Workforce */|| g_rtn ||'
,NVL(tot_curr_hdc_end,0)                  HRI_P_GRAND_TOTAL1
,NVL(tot_comp_hdc_end,0)                  HRI_P_GRAND_TOTAL2'|| g_rtn
/* Total Absence  */|| g_rtn ||'
,NVL(babs.curr_tot_abs_in_period,0)       HRI_P_GRAND_TOTAL3
,NVL(babs.comp_tot_abs_in_period,0)       HRI_P_GRAND_TOTAL4'|| g_rtn
/* Total Notification  */|| g_rtn ||'
,NVL(babs.curr_tot_abs_ntfctn_period,0)   HRI_P_GRAND_TOTAL5
,NVL(babs.comp_tot_abs_ntfctn_period,0)   HRI_P_GRAND_TOTAL6'|| g_rtn
/* Total Average Notification */|| g_rtn ||'
,DECODE(babs.curr_tot_abs_ntfctn_period,0, to_number(NULL)
       ,DECODE(babs.curr_abs_in_period,0, to_number(NULL)
              ,(babs.curr_tot_abs_ntfctn_period / babs.curr_tot_abs_in_period)
	          )
       )                                  HRI_P_GRAND_TOTAL7
,DECODE(babs.comp_tot_abs_ntfctn_period,0, to_number(NULL)
       ,DECODE(babs.comp_tot_abs_in_period,0, to_number(NULL)
              ,(babs.comp_tot_abs_ntfctn_period / babs.comp_tot_abs_in_period)
	          )
       )                                  HRI_P_GRAND_TOTAL8'|| g_rtn
/* Total Absence Duration */|| g_rtn ||'
,NVL(babs.curr_tot_abs_drtn,0)            HRI_P_GRAND_TOTAL9
,NVL(babs.comp_tot_abs_drtn,0)            HRI_P_GRAND_TOTAL10'|| g_rtn
/* Total Average Absence Duration  */|| g_rtn ||'
,DECODE(babs.curr_tot_abs_in_period,0, to_number(NULL)
       ,(babs.curr_tot_abs_drtn  / babs.curr_tot_abs_in_period)
	   )                              HRI_P_GRAND_TOTAL11
,DECODE(babs.comp_tot_abs_in_period,0, to_number(NULL)
       ,(babs.comp_tot_abs_drtn  / babs.comp_tot_abs_in_period)
	   )                              HRI_P_GRAND_TOTAL12'|| g_rtn
/* Total Average Absence Duration by Emp */	|| g_rtn ||'
,DECODE('||l_dynmc_tot_hdc_curr||' ,0, to_number(NULL)
       ,(babs.curr_tot_abs_drtn  / '||l_dynmc_tot_hdc_curr||' )
	   )                              HRI_P_GRAND_TOTAL13
,DECODE('||l_dynmc_tot_hdc_comp||' ,0, to_number(NULL)
       ,(babs.comp_tot_abs_drtn  / '||l_dynmc_tot_hdc_comp||' )
	   )                              HRI_P_GRAND_TOTAL14
FROM
(
SELECT
/* Base Measures */
 wfact.vby_id                               vby_id
,wfact.vby_id                               value
,wfact.direct_ind
,NVL(wfact.curr_hdc_start,0)                curr_hdc_start
,NVL(wfact.comp_hdc_start,0)                comp_hdc_start
,NVL(wfact.curr_hdc_end,0)                  curr_hdc_end
,NVL(wfact.comp_hdc_end,0)                  comp_hdc_end
,NVL(afact.'|| l_dynmc_drtn_curr ||',0)     curr_abs_drtn
,NVL(afact.curr_abs_in_period,0)            curr_abs_in_period
,NVL(afact.'|| l_dynmc_drtn_comp ||',0)     comp_abs_drtn
,NVL(afact.comp_abs_in_period,0)            comp_abs_in_period
,NVL(afact.curr_abs_ntfctn_period,0)        curr_abs_ntfctn_period
,NVL(afact.comp_abs_ntfctn_period,0)        comp_abs_ntfctn_period
,SUM(wfact.curr_hdc_start) OVER()           tot_curr_hdc_start
,SUM(wfact.comp_hdc_start) OVER()           tot_comp_hdc_start
,SUM(wfact.curr_hdc_end) OVER()             tot_curr_hdc_end
,SUM(wfact.comp_hdc_end) OVER()             tot_comp_hdc_end
,SUM(afact.'|| l_dynmc_drtn_curr ||') OVER()
                                            curr_tot_abs_drtn
,SUM(afact.curr_abs_in_period) OVER()       curr_tot_abs_in_period
,SUM(afact.'|| l_dynmc_drtn_comp ||') OVER()
                                            comp_tot_abs_drtn
,SUM(afact.comp_abs_in_period) OVER()       comp_tot_abs_in_period
,SUM(afact.curr_abs_ntfctn_period) OVER()   curr_tot_abs_ntfctn_period
,SUM(afact.comp_abs_ntfctn_period) OVER()   comp_tot_abs_ntfctn_period
FROM
('|| l_abs_fact_sql ||') afact
,('|| l_wrkfc_fact_sql ||') wfact
WHERE
    afact.vby_id (+) = wfact.vby_id
 ' || l_security_clause || '
) babs';