APPS.BEN_BATCH_DT_API SQL Statements

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

Line: 21

        18 Sep 03        mhoyes     115.4      3150329 - Update eligibility
                                               APIs.
*/
--------------------------------------------------------------------------------
--
g_package varchar2(30) := 'ben_batch_dt_api.';

Line: 133

    select person_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   per_all_people_f
    where  person_id = c_id
    group by person_id;

Line: 270

    select ler_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   ben_ler_f
    where  ler_id = c_id
    group by ler_id;

Line: 407

    select pgm_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   ben_pgm_f
    where  pgm_id = c_id
    group by pgm_id;

Line: 544

    select ptip_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   ben_ptip_f
    where  ptip_id = c_id
    group by ptip_id;

Line: 681

    select plip_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   ben_plip_f
    where  plip_id = c_id
    group by plip_id;

Line: 818

    select pl_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   ben_pl_f
    where  pl_id = c_id
    group by pl_id;

Line: 955

    select elig_per_id id,
           min(effective_start_date) min_esd,
           max(effective_end_date) max_eed
    from   ben_elig_per_f
    where  elig_per_id = c_id
    group by elig_per_id;

Line: 1048

  g_person_dtsum_odcache.delete;

Line: 1051

  g_ler_dtsum_odcache.delete;

Line: 1054

  g_pgm_dtsum_odcache.delete;

Line: 1057

  g_ptip_dtsum_odcache.delete;

Line: 1060

  g_plip_dtsum_odcache.delete;

Line: 1063

  g_pl_dtsum_odcache.delete;

Line: 1066

  g_elig_per_dtsum_odcache.delete;

Line: 1092

procedure validate_dt_mode_insert
  (p_effective_date       in     date
  ,p_person_id            in     number default null
  ,p_ler_id               in     number default null
  ,p_pgm_id               in     number default null
  ,p_ptip_id              in     number default null
  ,p_plip_id              in     number default null
  ,p_pl_id                in     number default null
  --
  ,p_effective_start_date in out nocopy date
  ,p_effective_end_date   in out nocopy date
  )
is
  --
  l_minmax_rec           ben_batch_dt_api.gtyp_dtsum_row;

Line: 1239

end validate_dt_mode_insert;

Line: 1258

    select pep.effective_start_date,
           pep.effective_end_date
    from ben_elig_per_f pep
    where pep.elig_per_id = c_pep_id
    and   c_eff_date
      between pep.effective_start_date and pep.effective_end_date;

Line: 1270

    select epo.effective_start_date,
           epo.effective_end_date
    from ben_elig_per_opt_f epo
    where epo.elig_per_opt_id = c_epo_id
    and   c_eff_date
      between epo.effective_start_date and epo.effective_end_date;

Line: 1347

    select max(pep.effective_end_date)
    from ben_elig_per_f pep
    where pep.elig_per_id = c_id;

Line: 1355

    select max(epo.effective_end_date)
    from ben_elig_per_opt_f epo
    where epo.elig_per_opt_id = c_id;

Line: 1471

  elsif p_datetrack_mode = hr_api.g_update
  then
    --
    -- Determine if any future rows exist
    --
    If NOT (ben_batch_dt_api.Future_Rows_Exist
            (p_base_table_name => l_table_name
            ,p_effective_date  => p_effective_date
            ,p_base_key_value  => p_elig_per_id
            )
           )
    then
      --
      ben_batch_dt_api.return_effective_dates
        (p_base_table_name      => l_table_name
        ,p_effective_date       => p_effective_date
        ,p_base_key_value       => p_elig_per_id
        ,p_effective_start_date => l_effective_start_date
        ,p_effective_end_date   => l_effective_end_date
        );

Line: 1591

  elsif p_datetrack_mode = hr_api.g_update
  then
    --
    -- Determine if any future rows exist
    --
    If NOT (ben_batch_dt_api.Future_Rows_Exist
            (p_base_table_name => l_table_name
            ,p_effective_date  => p_effective_date
            ,p_base_key_value  => p_elig_per_opt_id
            )
           )
    then
      --
      ben_batch_dt_api.return_effective_dates
        (p_base_table_name      => l_table_name
        ,p_effective_date       => p_effective_date
        ,p_base_key_value       => p_elig_per_opt_id
        ,p_effective_start_date => l_effective_start_date
        ,p_effective_end_date   => l_effective_end_date
        );