APPS.HRI_OPL_UTL_ABSNC_FACT SQL Statements

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

Line: 77

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

Line: 102

PROCEDURE insert_fact_record(p_abs_sk_fk       IN NUMBER,
                             p_effective_date  IN DATE,
                             p_drtn_days       IN NUMBER,
                             p_drtn_hours      IN NUMBER,
                             p_start_ind       IN NUMBER,
                             p_end_ind         IN NUMBER,
                             p_ntfctn_blnc     IN NUMBER,
                             p_abs_prsn_id     IN NUMBER) IS

BEGIN

  -- Increment index and store new row
  g_tab_index := g_tab_index + 1;

Line: 124

END insert_fact_record;

Line: 129

PROCEDURE bulk_insert_rows IS

BEGIN

  -- Set constants
  g_sysdate := sysdate;

Line: 141

     INSERT INTO hri_mb_utl_absnc_ct
     (absence_sk_fk
     ,effective_date
     ,abs_drtn_days
     ,abs_drtn_hrs
     ,abs_blnc_ind
     ,abs_start_ind
     ,abs_end_ind
     ,abs_ntfctn_days_blnc
     ,abs_person_id
     ,last_update_date
     ,last_updated_by
     ,last_update_login
     ,created_by
     ,creation_date)
     VALUES
      (g_tab_abs_sk_fk(i),
       g_tab_effective_date(i),
       g_tab_drtn_days(i),
       g_tab_drtn_hours(i),
       1,
       g_tab_start_ind(i),
       g_tab_end_ind(i),
       g_tab_ntfctn_blnc(i),
       g_tab_abs_prsn_id(i),
       g_sysdate,
       g_user,
       g_user,
       g_user,
       g_sysdate);

Line: 180

END bulk_insert_rows;

Line: 201

  SELECT
   dim.absence_sk_pk                    absence_sk_fk
  ,GREATEST(dim.abs_start_date, g_dbi_collection_start_date)
                                        start_date
  ,LEAST(NVL(dim.abs_end_date, TRUNC(SYSDATE)),
         TRUNC(SYSDATE))                end_date
  ,dim.abs_start_date                   start_date_actual
  ,dim.abs_end_date                     end_date_actual
  ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
              THEN dim.abs_drtn_days / (trunc(sysdate) - dim.abs_start_date + 1)
              ELSE dim.abs_drtn_days / (dim.abs_end_date - dim.abs_start_date + 1)
         END, g_rounding)               abs_drtn_days
  ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
              THEN dim.abs_drtn_hrs / (trunc(sysdate) - dim.abs_start_date + 1)
              ELSE dim.abs_drtn_hrs / (dim.abs_end_date - dim.abs_start_date + 1)
         END, g_rounding)               abs_drtn_hrs
  ,CASE WHEN (dim.abs_start_date < dim.abs_notification_date)
        THEN 0
        ELSE dim.abs_start_date - dim.abs_notification_date
   END                                  abs_ntfctn_days_blnc
  ,dim.abs_person_id                    abs_person_id
  FROM
   hri_cs_absence_ct  dim
  WHERE dim.abs_person_id = p_person_id
  AND dim.abs_start_date <= trunc(sysdate);

Line: 229

  SELECT
   dim.absence_sk_pk                    absence_sk_fk
  ,GREATEST(dim.abs_start_date, g_dbi_collection_start_date)
                                        start_date
  ,LEAST(NVL(dim.abs_end_date, TRUNC(SYSDATE)),
         TRUNC(SYSDATE))                end_date
  ,dim.abs_start_date                   start_date_actual
  ,dim.abs_end_date                     end_date_actual
  ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
              THEN dim.abs_drtn_days / (trunc(sysdate) - dim.abs_start_date + 1)
              ELSE dim.abs_drtn_days / (dim.abs_end_date - dim.abs_start_date + 1)
         END, g_rounding)               abs_drtn_days
  ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
              THEN dim.abs_drtn_hrs / (trunc(sysdate) - dim.abs_start_date + 1)
              ELSE dim.abs_drtn_hrs / (dim.abs_end_date - dim.abs_start_date + 1)
         END, g_rounding)               abs_drtn_hrs
  ,CASE WHEN (dim.abs_start_date < dim.abs_notification_date)
        THEN 0
        ELSE dim.abs_start_date - dim.abs_notification_date
   END                                  abs_ntfctn_days_blnc
  ,dim.abs_person_id                    abs_person_id
  FROM
   hri_cs_absence_ct      dim
  ,hri_eq_utl_absnc_fact  eq
  WHERE dim.absence_sk_pk = eq.absence_sk_fk
  AND eq.absence_sk_fk BETWEEN p_start_abs_id
                       AND p_end_abs_id
  AND dim.abs_start_date <= trunc(sysdate);

Line: 352

        insert_fact_record
         (p_abs_sk_fk      => l_abs_sk_fk(i),
          p_effective_date => l_abs_start_date(i) + j - 1,
          p_drtn_days      => l_abs_drtn_days(i),
          p_drtn_hours     => l_abs_drtn_hrs(i),
          p_start_ind      => l_abs_start_ind,
          p_end_ind        => l_abs_end_ind,
          p_ntfctn_blnc    => l_abs_ntfctn_blnc(i),
          p_abs_prsn_id    => l_abs_person_id(i));

Line: 370

    bulk_insert_rows;

Line: 384

  DELETE FROM hri_mb_utl_absnc_ct fact
  WHERE fact.absence_sk_fk IN
   (SELECT eq.absence_sk_fk
    FROM hri_eq_utl_absnc_fact  eq
    WHERE eq.absence_sk_fk BETWEEN p_start_abs_id AND p_end_abs_id);

Line: 400

  bulk_insert_rows;

Line: 412

  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: 434

  bulk_insert_rows;

Line: 525

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

Line: 536

      'SELECT /*+ PARALLEL(eq, DEFAULT, DEFAULT) */
         absence_sk_fk object_id
       FROM hri_eq_utl_absnc_fact eq
       ORDER BY absence_sk_fk';

Line: 607

  SELECT max(person_id) INTO l_end_psn_id
  FROM per_all_people_f;

Line: 609

  SELECT max(absence_attendance_id) INTO l_end_abs_id
  FROM per_absence_attendances;