APPS.HRI_OPL_UTL_ABSNC_DIM 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: 104

PROCEDURE update_eq_with_status_changes IS

BEGIN

  INSERT INTO hri_eq_utl_absnc_dim
   (absence_attendance_id)
  SELECT
   dim.absence_attendance_id
  FROM
   hri_cs_absence_ct  dim
  WHERE dim.absence_status_code IN ('FUTURE','OCCURRING')
  AND dim.abs_start_date <= trunc(sysdate)
  AND NOT EXISTS
   (SELECT null
    FROM hri_eq_utl_absnc_dim eq
    WHERE eq.absence_attendance_id = dim.absence_attendance_id);

Line: 123

END update_eq_with_status_changes;

Line: 139

    INSERT INTO hri_eq_utl_absnc_fact
     (absence_sk_fk)
    SELECT
     dim.absence_sk_pk
    FROM
     hri_cs_absence_ct     dim
    ,hri_eq_utl_absnc_dim  eq
    WHERE dim.absence_attendance_id = eq.absence_attendance_id
    AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
    AND NOT EXISTS
     (SELECT null
      FROM per_absence_attendances  tab
      WHERE tab.absence_attendance_id = eq.absence_attendance_id)
    AND NOT EXISTS
     (SELECT null
      FROM hri_eq_utl_absnc_fact eq2
      WHERE eq2.absence_sk_fk = dim.absence_sk_pk);

Line: 159

    INSERT INTO hri_eq_sup_absnc
     (source_id
     ,source_type
     ,erlst_evnt_effective_date)
    SELECT
     dim.absence_sk_pk
    ,'ABSENCE'
    ,to_date(null)
    FROM
     hri_cs_absence_ct     dim
    ,hri_eq_utl_absnc_dim  eq
    WHERE dim.absence_attendance_id = eq.absence_attendance_id
    AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
    AND NOT EXISTS
     (SELECT null
      FROM per_absence_attendances  tab
      WHERE tab.absence_attendance_id = eq.absence_attendance_id)
    AND NOT EXISTS
     (SELECT null
      FROM hri_eq_sup_absnc eq2
      WHERE eq2.source_id = dim.absence_sk_pk
      AND eq2.source_type = 'ABSENCE');

Line: 182

  ELSIF (p_event_type = 'UPDATES') THEN

    -- Update fact event queue with surrogate key
    INSERT INTO hri_eq_utl_absnc_fact
     (absence_sk_fk)
    SELECT
     dim.absence_sk_pk
    FROM
     hri_cs_absence_ct     dim
    ,hri_eq_utl_absnc_dim  eq
    WHERE dim.absence_attendance_id = eq.absence_attendance_id
    AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
    AND NOT EXISTS
     (SELECT null
      FROM hri_eq_utl_absnc_fact eq2
      WHERE eq2.absence_sk_fk = dim.absence_sk_pk);

Line: 200

    INSERT INTO hri_eq_sup_absnc
     (source_id
     ,source_type
     ,erlst_evnt_effective_date)
    SELECT
     dim.absence_sk_pk
    ,'ABSENCE'
    ,to_date(null)
    FROM
     hri_cs_absence_ct     dim
    ,hri_eq_utl_absnc_dim  eq
    WHERE dim.absence_attendance_id = eq.absence_attendance_id
    AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
    AND NOT EXISTS
     (SELECT null
      FROM hri_eq_sup_absnc eq2
      WHERE eq2.source_id = dim.absence_sk_pk
      AND eq2.source_type = 'ABSENCE');

Line: 238

  DELETE FROM hri_cs_absence_ct dim
  WHERE dim.absence_attendance_id IN
   (SELECT eq.absence_attendance_id
    FROM hri_eq_utl_absnc_dim  eq
    WHERE eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id);

Line: 245

  INSERT INTO hri_cs_absence_ct
   (absence_sk_pk
   ,absence_attendance_id
   ,abs_start_date
   ,abs_end_date
   ,abs_notification_date
   ,abs_person_id
   ,absence_category_code
   ,absence_reason_code
   ,absence_status_code
   ,absence_attendance_type_id
   ,abs_attendance_reason_id
   ,abs_drtn_days
   ,abs_drtn_hrs
   ,last_update_date
   ,last_updated_by
   ,last_update_login
   ,created_by
   ,creation_date)
  SELECT
   paa.absence_attendance_id              absence_sk_pk
  ,paa.absence_attendance_id              absence_attendance_id
  ,paa.date_start                         abs_start_date
  ,NVL(paa.date_end, g_end_of_time)       abs_end_date
  ,paa.date_notification                  abs_notification_date
  ,paa.person_id                          abs_person_id
  ,NVL(pat.absence_category, 'NA_EDW')    absence_category_code
  ,NVL(par.name, 'NA_EDW')                absence_reason_code
  ,CASE WHEN paa.date_start > TRUNC(SYSDATE)
        THEN 'FUTURE'
        WHEN (TRUNC(SYSDATE) BETWEEN paa.date_start
                             AND NVL(paa.date_end, g_end_of_time))
        THEN 'OCCURRING'
        ELSE 'OCCURRED'
   END                                    absence_status_code
  ,paa.absence_attendance_type_id
  ,NVL(paa.abs_attendance_reason_id, -1)  abs_attendance_reason_id
  ,SUM(hri_bpl_utilization.calculate_absence_duration
        (paa.absence_attendance_id
        ,'DAYS'
        ,paa.absence_hours
        ,paa.absence_days
        ,asg.assignment_id
        ,asg.business_group_id
        ,asg.primary_flag
        ,paa.date_start
        ,NVL(paa.date_end, trunc(sysdate))
        ,paa.time_start
        ,paa.time_end))                   abs_drtn_days
  ,SUM(hri_bpl_utilization.calculate_absence_duration
        (paa.absence_attendance_id
        ,'HOURS'
        ,paa.absence_hours
        ,paa.absence_days
        ,asg.assignment_id
        ,asg.business_group_id
        ,asg.primary_flag
        ,paa.date_start
        ,NVL(paa.date_end, trunc(sysdate))
        ,paa.time_start
        ,paa.time_end))                   abs_drtn_hrs
  ,g_sysdate
  ,g_user
  ,g_user
  ,g_user
  ,g_sysdate
  FROM
   per_absence_attendances       paa
  ,per_absence_attendance_types  pat
  ,per_abs_attendance_reasons    par
  ,per_all_assignments_f         asg
  ,hri_eq_utl_absnc_dim          eq
  WHERE eq.absence_attendance_id BETWEEN p_start_abs_id
                                 AND p_end_abs_id
  AND paa.absence_attendance_id = eq.absence_attendance_id
  AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
  AND paa.abs_attendance_reason_id = par.abs_attendance_reason_id (+)
  AND paa.person_id = asg.person_id
  AND asg.assignment_type IN ('E','C')
  AND paa.date_start BETWEEN asg.effective_start_date
                     AND asg.effective_end_date
  AND paa.date_start IS NOT NULL
  AND NVL(paa.date_end, g_end_of_time) >= g_dbi_collection_start_date
  GROUP BY
   paa.absence_attendance_id
  ,paa.date_start
  ,paa.date_end
  ,paa.date_notification
  ,paa.person_id
  ,pat.absence_category
  ,par.name
  ,paa.absence_attendance_type_id
  ,paa.abs_attendance_reason_id
  ,paa.absence_days
  ,paa.absence_hours;

Line: 343

   (p_event_type   => 'UPDATES',
    p_start_abs_id => p_start_abs_id,
    p_end_abs_id   => p_end_abs_id);

Line: 355

PROCEDURE bulk_insert_rows IS

BEGIN

  g_user := fnd_global.user_id;

Line: 366

     INSERT INTO hri_cs_absence_ct
     (absence_sk_pk
     ,absence_attendance_id
     ,abs_start_date
     ,abs_end_date
     ,abs_notification_date
     ,abs_person_id
     ,absence_category_code
     ,absence_reason_code
     ,absence_status_code
     ,absence_attendance_type_id
     ,abs_attendance_reason_id
     ,abs_drtn_days
     ,abs_drtn_hrs
     ,last_update_date
     ,last_updated_by
     ,last_update_login
     ,created_by
     ,creation_date)
     VALUES
      (g_abs_sk_pk(i),
       g_abs_attendance_id(i),
       g_abs_start_date(i),
       g_abs_end_date(i),
       g_abs_notification_date(i),
       g_abs_person_id(i),
       g_abs_category_code(i),
       g_abs_reason_code(i),
       g_abs_status_code(i),
       g_abs_attendance_type_id(i),
       g_abs_attendance_reason_id(i),
       g_abs_drtn_days(i),
       g_abs_drtn_hrs(i),
       g_sysdate,
       g_user,
       g_user,
       g_user,
       g_sysdate);

Line: 413

END bulk_insert_rows;

Line: 418

PROCEDURE insert_row(
  p_abs_sk_pk                 IN NUMBER,
  p_abs_attendance_id         IN NUMBER,
  p_abs_start_date            IN DATE,
  p_abs_end_date              IN DATE,
  p_abs_notification_date     IN DATE,
  p_abs_person_id             IN NUMBER,
  p_abs_category_code         IN VARCHAR2,
  p_abs_reason_code           IN VARCHAR2,
  p_abs_status_code           IN VARCHAR2,
  p_abs_attendance_type_id    IN NUMBER,
  p_abs_attendance_reason_id  IN NUMBER,
  p_abs_drtn_days             IN NUMBER,
  p_abs_drtn_hrs              IN NUMBER) IS

BEGIN

  g_abs_index := g_abs_index + 1;

Line: 450

END insert_row;

Line: 458

  SELECT
   paa.absence_attendance_id              absence_sk_pk
  ,paa.absence_attendance_id
  ,paa.date_start                         abs_start_date
  ,NVL(paa.date_end, g_end_of_time)       abs_end_date
  ,paa.date_notification                  abs_notification_date
  ,paa.person_id                          abs_person_id
  ,NVL(pat.absence_category, 'NA_EDW')    absence_category_code
  ,NVL(par.name, 'NA_EDW')                absence_reason_code
  ,CASE WHEN paa.date_start > TRUNC(SYSDATE)
        THEN 'FUTURE'
        WHEN (TRUNC(SYSDATE) BETWEEN paa.date_start
                             AND NVL(paa.date_end, g_end_of_time))
        THEN 'OCCURRING'
        ELSE 'OCCURRED'
   END                                    absence_status_code
  ,paa.absence_attendance_type_id
  ,NVL(paa.abs_attendance_reason_id, -1)  abs_attendance_reason_id
  ,SUM(hri_bpl_utilization.calculate_absence_duration
        (paa.absence_attendance_id
        ,'DAYS'
        ,paa.absence_hours
        ,paa.absence_days
        ,asg.assignment_id
        ,asg.business_group_id
        ,asg.primary_flag
        ,paa.date_start
        ,NVL(paa.date_end, trunc(sysdate))
        ,paa.time_start
        ,paa.time_end))                   abs_drtn_days
  ,SUM(hri_bpl_utilization.calculate_absence_duration
        (paa.absence_attendance_id
        ,'HOURS'
        ,paa.absence_hours
        ,paa.absence_days
        ,asg.assignment_id
        ,asg.business_group_id
        ,asg.primary_flag
        ,paa.date_start
        ,NVL(paa.date_end, trunc(sysdate))
        ,paa.time_start
        ,paa.time_end))                   abs_drtn_hrs
  FROM
   per_absence_attendances       paa
  ,per_absence_attendance_types  pat
  ,per_abs_attendance_reasons    par
  ,per_all_assignments_f         asg
  WHERE paa.person_id = p_person_id
  AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
  AND paa.abs_attendance_reason_id = par.abs_attendance_reason_id (+)
  AND paa.person_id = asg.person_id
  AND asg.assignment_type IN ('E','C')
  AND paa.date_start BETWEEN asg.effective_start_date
                     AND asg.effective_end_date
  AND paa.date_start IS NOT NULL
  AND NVL(paa.date_end, g_end_of_time) >= g_dbi_collection_start_date
  GROUP BY
   paa.absence_attendance_id
  ,paa.date_start
  ,paa.date_end
  ,paa.date_notification
  ,paa.person_id
  ,pat.absence_category
  ,par.name
  ,paa.absence_attendance_type_id
  ,paa.abs_attendance_reason_id
  ,paa.absence_days
  ,paa.absence_hours;

Line: 567

      insert_row
       (p_abs_sk_pk                => l_abs_sk_pk(i),
        p_abs_attendance_id        => l_abs_attendance_id(i),
        p_abs_start_date           => l_abs_start_date(i),
        p_abs_end_date             => l_abs_end_date(i),
        p_abs_notification_date    => l_abs_notification_date(i),
        p_abs_person_id            => l_abs_person_id(i),
        p_abs_category_code        => l_abs_category_code(i),
        p_abs_reason_code          => l_abs_reason_code(i),
        p_abs_status_code          => l_abs_status_code(i),
        p_abs_attendance_type_id   => l_abs_attendance_type_id(i),
        p_abs_attendance_reason_id => l_abs_attendance_reason_id(i),
        p_abs_drtn_days            => l_abs_drtn_days(i),
        p_abs_drtn_hrs             => l_abs_drtn_hrs(i));

Line: 587

    bulk_insert_rows;

Line: 600

  SELECT DISTINCT
   paa.person_id
  FROM per_absence_attendances  paa
  WHERE paa.person_id BETWEEN p_start_psn_id AND p_end_psn_id
  AND paa.date_start IS NOT NULL
  AND NVL(paa.date_end, sysdate) >= g_dbi_collection_start_date;

Line: 621

  bulk_insert_rows;

Line: 713

        'SELECT /*+ PARALLEL(paa, DEFAULT, DEFAULT) */ DISTINCT
           paa.person_id object_id
         FROM per_absence_attendances paa
         WHERE paa.date_start IS NOT NULL
         AND NVL(paa.date_end, sysdate) >= to_date(''' ||
                       to_char(g_dbi_collection_start_date, 'DD-MM-YYYY') ||
                       ''',''DD-MM-YYYY'')
         ORDER BY paa.person_id';

Line: 727

    update_eq_with_status_changes;

Line: 731

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

Line: 802

  SELECT max(person_id) INTO l_end_psn_id
  FROM per_all_people_f;

Line: 804

  SELECT max(absence_attendance_id) INTO l_end_abs_id
  FROM per_absence_attendances;