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_BPL_DBI_CALC_PERIOD SQL Statements

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

Line: 21


    SELECT
         SUM(CASE WHEN a.effective_date BETWEEN p_from_date AND p_to_date
                 THEN a.abs_drtn_days
                 ELSE 0
             END)  abs_drtn_days
        ,SUM(CASE WHEN a.effective_date BETWEEN p_from_date AND p_to_date
                 THEN abs_drtn_hrs
                 ELSE 0
             END)  abs_drtn_hrs
        ,SUM(CASE WHEN a.effective_date = p_from_date
                 THEN a.abs_start_blnc + a.abs_nstart_blnc
                  WHEN a.effective_date > p_from_date
                    AND a.effective_date <= p_to_date
                 THEN abs_start_blnc
                 ELSE 0
             END)  abs_in_period
        ,SUM(CASE WHEN a.effective_date = p_from_date
                THEN a.abs_ntfctn_days_start_blnc + a.abs_ntfctn_days_nstart_blnc
                  WHEN a.effective_date > p_from_date
                    AND a.effective_date <= p_to_date
                THEN abs_ntfctn_days_start_blnc
                ELSE 0
             END)  abs_ntfctn_period
  FROM HRI_MDP_SUP_ABSNC_SUP_MV a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date BETWEEN  p_from_date AND p_to_date;

Line: 89


  SELECT
   NVL(SUM(a.hire_hdc),0)          tot_gain_hire
  ,NVL(SUM(a.transfer_in_hdc),0)   tot_gain_transfer
  ,NVL(SUM(a.termination_hdc),0)   tot_loss_term
  ,NVL(SUM(a.transfer_out_hdc),0)  tot_loss_transfer
  FROM hri_mdp_sup_wcnt_chg_mv a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date BETWEEN p_from_date
                       AND p_to_date;

Line: 101


  SELECT /*+ INDEX(a) */
   NVL(SUM(a.hire_hdc),0)          tot_gain_hire
  ,NVL(SUM(a.transfer_in_hdc),0)   tot_gain_transfer
  ,NVL(SUM(a.termination_hdc),0)   tot_loss_term
  ,NVL(SUM(a.transfer_out_hdc),0)  tot_loss_transfer
  FROM hri_mds_sup_wcnt_chg_mv a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date = p_to_date
  AND a.period_type = p_period_type
  AND a.comparison_type IN (p_comparison_type, 'CURRENT');

Line: 172


  SELECT
   NVL(SUM(a.hire_hdc),0)          tot_gain_hire
  ,NVL(SUM(a.transfer_in_hdc),0)   tot_gain_transfer
  ,NVL(SUM(a.termination_hdc),0)   tot_loss_term
  ,NVL(SUM(a.transfer_out_hdc),0)  tot_loss_transfer
  FROM hri_mdp_sup_wcnt_chg_mv a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date BETWEEN p_from_date
                       AND p_to_date;

Line: 185


  SELECT /*+ INDEX(a) */
   NVL(SUM(a.hire_hdc),0)          tot_gain_hire
  ,NVL(SUM(a.transfer_in_hdc),0)   tot_gain_transfer
  ,NVL(SUM(a.termination_hdc),0)   tot_loss_term
  ,NVL(SUM(a.transfer_out_hdc),0)  tot_loss_transfer
  FROM hri_mds_sup_wcnt_chg_mv a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date = p_to_date
  AND a.period_type = p_period_type
  AND a.comparison_type IN (p_comparison_type, 'CURRENT');

Line: 255


  SELECT
   NVL(SUM(a.sep_vol_hdc),0)     total_trn_vol
  ,NVL(SUM(a.sep_invol_hdc),0)   total_trn_invol
  FROM hri_mdp_sup_wcnt_chg_mv a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date BETWEEN p_from_date AND p_to_date
  AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk;

Line: 265


  SELECT /*+ INDEX(a) */
   NVL(SUM(a.sep_vol_hdc),0)     total_trn_vol
  ,NVL(SUM(a.sep_invol_hdc),0)   total_trn_invol
  FROM hri_mds_sup_wcnt_chg_mv a
  WHERE a.supervisor_person_id = p_supervisor_id
  AND a.direct_record_ind = v_direct_record_ind
  AND a.effective_date = p_to_date
  AND a.period_type = p_period_type
  AND a.comparison_type IN (p_comparison_type, 'CURRENT')
  AND a.wkth_wktyp_sk_fk = p_wkth_wktyp_sk_fk;

Line: 368


'SELECT
   NVL(SUM(term.separation_hdc), 0) total_term
  ,NVL(SUM(CASE WHEN pow.pow_band = 1
                THEN term.separation_hdc
                ELSE 0
           END), 0)  total_term_pow_band1
  ,NVL(SUM(CASE WHEN pow.pow_band = 2
                THEN term.separation_hdc
                ELSE 0
           END), 0)  total_term_pow_band2
  ,NVL(SUM(CASE WHEN pow.pow_band = 3
                THEN term.separation_hdc
                ELSE 0
           END), 0)  total_term_pow_band3
  ,NVL(SUM(CASE WHEN pow.pow_band = 4
                THEN term.separation_hdc
                ELSE 0
           END), 0)  total_term_pow_band4
  ,NVL(SUM(CASE WHEN pow.pow_band = 5
                THEN term.separation_hdc
                ELSE 0
           END), 0)  total_term_pow_band5
FROM
 hri_mdp_sup_wcnt_term_asg_mv  term
,hri_dbi_cl_pow_all_band_v     pow
WHERE term.supervisor_person_id = :1
AND term.effective_date BETWEEN :2 AND :3
AND term.pow_band_sk_fk = pow.id'
  || l_where_clause;

Line: 468


'SELECT
   NVL(SUM(separation_hdc), 0)
  ,NVL(SUM(CASE WHEN perf_band = 1
                THEN separation_hdc
                ELSE 0
           END), 0)  total_term_perf_band1
  ,NVL(SUM(CASE WHEN perf_band = 2
                THEN separation_hdc
                ELSE 0
           END), 0)  total_term_perf_band2
  ,NVL(SUM(CASE WHEN perf_band = 3
                THEN separation_hdc
                ELSE 0
           END), 0)  total_term_perf_band3
  ,NVL(SUM(CASE WHEN perf_band = -5
                THEN separation_hdc
                ELSE 0
           END), 0)  total_term_perf_bandna
FROM
 hri_mdp_sup_wcnt_term_asg_mv  term
WHERE term.supervisor_person_id = :1
AND term.effective_date BETWEEN :2 AND :3'
  || l_where_clause;

Line: 560


'SELECT
 NVL(SUM(CASE WHEN separation_category = ''SEP_INV''
              THEN term.separation_hdc
         ELSE 0
         END), 0)
,NVL(SUM(CASE WHEN separation_category = ''SEP_VOL''
              THEN term.separation_hdc
         ELSE 0
         END), 0)
,NVL(SUM(term.separation_hdc), 0)
FROM
 hri_mdp_sup_wcnt_term_asg_mv  term
WHERE term.supervisor_person_id = :1
AND term.effective_date BETWEEN :2 AND :3'
  || l_where_clause;

Line: 596


  SELECT effective_change_end_date  INTO l_end_place_date
  FROM hri_mb_asgn_events_ct asgn
  WHERE
       asgn.person_id = p_person_id
  AND  asgn.assignment_id = p_assignment_id
  and  asgn.worker_term_nxt_ind = 1 ;