APPS.HRI_OPL_SUP_ABSNC SQL Statements

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

Line: 81

      hri_opl_multi_thread.update_parameters
       (p_mthd_action_id    => p_mthd_action_id,
        p_full_refresh      => g_full_refresh,
        p_global_start_date => l_dbi_collection_start_date);

Line: 104

PROCEDURE insert_row
 (p_sup_person_id         IN NUMBER,
  p_effective_date        IN DATE,
  p_abs_sk_fk             IN NUMBER,
  p_sup_direct_ind        IN NUMBER,
  p_abs_drtn_days         IN NUMBER,
  p_abs_drtn_hrs          IN NUMBER,
  p_abs_start_blnc        IN NUMBER,
  p_abs_nstart_blnc       IN NUMBER,
  p_abs_ntfctn_start      IN NUMBER,
  p_abs_ntfctn_nstart     IN NUMBER,
  p_abs_category_code     IN VARCHAR2,
  p_abs_reason_code       IN VARCHAR2,
  p_abs_person_id         IN NUMBER) IS

BEGIN

  g_tab_index := g_tab_index + 1;

Line: 136

END insert_row;

Line: 141

PROCEDURE bulk_insert_rows IS

BEGIN

  g_user := fnd_global.user_id;

Line: 152

      INSERT INTO hri_mdp_sup_absnc_occ_ct
       (supervisor_person_id
       ,effective_date
       ,absence_sk_fk
       ,direct_ind
       ,abs_drtn_days
       ,abs_drtn_hrs
       ,abs_start_blnc
       ,abs_nstart_blnc
       ,abs_ntfctn_days_start_blnc
       ,abs_ntfctn_days_nstart_blnc
       ,absence_category_code
       ,absence_reason_code
       ,abs_person_id
       ,last_update_date
       ,last_updated_by
       ,last_update_login
       ,created_by
       ,creation_date)
       VALUES
        (g_tab_sup_person_id(i),
         g_tab_effective_date(i),
         g_tab_abs_sk_fk(i),
         g_tab_sup_direct_ind(i),
         g_tab_abs_drtn_days(i),
         g_tab_abs_drtn_hrs(i),
         g_tab_abs_start_blnc(i),
         g_tab_abs_nstart_blnc(i),
         g_tab_abs_ntfctn_start(i),
         g_tab_abs_ntfctn_nstart(i),
         g_tab_abs_category_code(i),
         g_tab_abs_reason_code(i),
         g_tab_abs_person_id(i),
         g_sysdate,
         g_user,
         g_user,
         g_user,
         g_sysdate);

Line: 198

END bulk_insert_rows;

Line: 211

  SELECT
   sup_person_id
  ,sub_relative_level
  ,DECODE(sub_relative_level, 1, 1, 0)  direct_ind
  ,effective_end_date
  FROM
   hri_cs_suph
  WHERE sub_person_id = p_person_id
  AND p_effective_date BETWEEN effective_start_date
                       AND effective_end_date;

Line: 269

  SELECT
   abs_fct.effective_date
  ,abs_fct.absence_sk_fk
  ,abs_fct.abs_drtn_days
  ,abs_fct.abs_drtn_hrs
  ,abs_fct.abs_start_ind
  ,abs_fct.abs_ntfctn_days_blnc
  ,abs_dim.absence_category_code
  ,abs_dim.absence_reason_code
  ,abs_dim.abs_person_id
  FROM
   hri_mb_utl_absnc_ct  abs_fct
  ,hri_cs_absence_ct    abs_dim
  WHERE abs_dim.abs_person_id = p_person_id
  AND abs_dim.absence_sk_pk = abs_fct.absence_sk_fk
  ORDER BY abs_fct.effective_date;

Line: 288

  SELECT
   abs_fct.effective_date
  ,abs_fct.absence_sk_fk
  ,abs_fct.abs_drtn_days
  ,abs_fct.abs_drtn_hrs
  ,abs_fct.abs_start_ind
  ,abs_fct.abs_ntfctn_days_blnc
  ,abs_dim.absence_category_code
  ,abs_dim.absence_reason_code
  ,abs_dim.abs_person_id
  FROM
   hri_mb_utl_absnc_ct  abs_fct
  ,hri_cs_absence_ct    abs_dim
  ,hri_eq_sup_absnc     eq
  WHERE abs_dim.absence_sk_pk = eq.source_id
  AND eq.source_type = 'ABSENCE'
  AND eq.source_id BETWEEN p_start_abs_id
                   AND p_end_abs_id
  AND abs_dim.absence_sk_pk = abs_fct.absence_sk_fk
  ORDER BY abs_person_id, abs_fct.effective_date;

Line: 433

          insert_row
           (p_sup_person_id     => l_sup_ids(j),
            p_effective_date    => l_abs_effective_date(i),
            p_abs_sk_fk         => l_abs_sk_fk(i),
            p_sup_direct_ind    => l_sup_direct_ind(j),
            p_abs_drtn_days     => l_abs_drtn_days(i),
            p_abs_drtn_hrs      => l_abs_drtn_hrs(i),
            p_abs_start_blnc    => l_sup_abs_start_ind,
            p_abs_nstart_blnc   => 1 - l_sup_abs_start_ind,
            p_abs_ntfctn_start  => l_abs_ntfctn_days_blnc(i) * l_sup_abs_start_ind,
            p_abs_ntfctn_nstart => l_abs_ntfctn_days_blnc(i) * (1 - l_sup_abs_start_ind),
            p_abs_category_code => l_abs_category_code(i),
            p_abs_reason_code   => l_abs_reason_code(i),
            p_abs_person_id     => l_abs_person_id(i));

Line: 460

    bulk_insert_rows;

Line: 471

  SELECT DISTINCT
   abs_person_id
  FROM hri_cs_absence_ct
  WHERE abs_person_id BETWEEN p_start_psn_id AND p_end_psn_id;

Line: 493

  bulk_insert_rows;

Line: 506

  DELETE FROM hri_mdp_sup_absnc_occ_ct tab
  WHERE tab.absence_sk_fk IN
   (SELECT eq.source_id
    FROM hri_eq_sup_absnc  eq
    WHERE eq.source_id BETWEEN p_start_abs_id AND p_end_abs_id
    AND eq.source_type = 'ABSENCE');

Line: 523

  bulk_insert_rows;

Line: 568

  INSERT INTO hri_eq_sup_absnc
   (source_id
   ,source_type)
  SELECT
   dim.absence_sk_pk  source_id
  ,'ABSENCE'          source_type
  FROM
   hri_cs_absence_ct  dim
  ,hri_eq_sup_absnc   eq
  WHERE eq.source_type = 'SUPERVISOR'
  AND eq.source_id = dim.abs_person_id
  AND dim.abs_end_date >= eq.erlst_evnt_effective_date
  AND NOT EXISTS
   (SELECT null
    FROM hri_eq_sup_absnc eq_abs
    WHERE eq_abs.source_id = dim.absence_sk_pk
    AND eq_abs.source_type = 'ABSENCE');

Line: 644

        'SELECT /*+ PARALLEL(asg, DEFAULT, DEFAULT) */ DISTINCT
           abs_person_id object_id
         FROM hri_cs_absence_ct
         ORDER BY abs_person_id';

Line: 658

        'SELECT
           source_id object_id
         FROM hri_eq_sup_absnc
         WHERE source_type = ''ABSENCE''
         ORDER BY source_id';

Line: 730

  SELECT max(person_id) INTO l_end_psn_id
  FROM per_all_people_f;

Line: 732

  SELECT max(absence_attendance_id) INTO l_end_abs_id
  FROM per_absence_attendances;