APPS.BEN_PRTN_ELIG_PRFL_API SQL Statements

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

Line: 65

  cursor c1(l_prtn_elig_id number) is select pgm_id,pl_id,ptip_id,plip_id,oipl_id
     from ben_prtn_elig_f
     where prtn_elig_id = l_prtn_elig_id;

Line: 95

        ben_prtn_elig_prfl_api.insert_prtn_elig
          (
           p_pgm_id                    => p_pgm_id
          ,p_pl_id                     => p_pl_id
          ,p_plip_id                   => p_plip_id
          ,p_ptip_id                   => p_ptip_id
          ,p_oipl_id                   => p_oipl_id
          ,p_effective_date            => p_effective_date
          ,p_business_group_id         => p_business_group_id
          ,p_prtn_elig_id              => l_prtn_elig_id
          );

Line: 278

  update ben_pgm_f c
  set   c.elig_apls_flag = 'Y'
  where c.business_group_id = p_business_group_id
  and   c.elig_apls_flag <> 'Y'
  and   c.pgm_id = l_pgm_id ;

Line: 284

  hr_utility.set_location(' update of pgm_f ' , 60);

Line: 289

  update ben_plip_f c
  set    c.elig_apls_flag = 'Y'
  where c.business_group_id = p_business_group_id
  and   c.elig_apls_flag <> 'Y'
  and   c.plip_id = l_plip_id ;

Line: 295

  hr_utility.set_location(' update of plip_f ' , 60);

Line: 299

  update ben_ptip_f c
  set    c.elig_apls_flag = 'Y'
  where c.business_group_id = p_business_group_id
  and   c.elig_apls_flag <> 'Y'
  and  c.ptip_id = l_ptip_id ;

Line: 304

  hr_utility.set_location(' update of ptip_f ' , 60);

Line: 308

  update ben_pl_f c
  set    c.elig_apls_flag = 'Y'
  where c.business_group_id = p_business_group_id
  and   c.elig_apls_flag <> 'Y'
  and   c.pl_id = l_pl_id ;

Line: 314

  hr_utility.set_location(' update of pl_f ' , 60);

Line: 318

  update ben_oipl_f c
  set    c.elig_apls_flag = 'Y'
  where c.business_group_id = p_business_group_id
  and   c.elig_apls_flag <> 'Y'
  and   c.oipl_id = l_oipl_id ;

Line: 324

  hr_utility.set_location(' update of oipl_f ' , 60);

Line: 395

procedure update_PRTN_ELIG_PRFL
  (p_validate                       in  boolean   default false
  ,p_prtn_elig_prfl_id              in  number
  ,p_effective_start_date           out nocopy date
  ,p_effective_end_date             out nocopy date
  ,p_business_group_id              in  number    default hr_api.g_number
  ,p_mndtry_flag                    in  varchar2  default hr_api.g_varchar2
  ,p_prtn_elig_id                   in  number    default hr_api.g_number
  ,p_eligy_prfl_id                  in  number    default hr_api.g_number
  ,p_Elig_prfl_type_cd              in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute_category         in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute1                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute2                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute3                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute4                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute5                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute6                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute7                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute8                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute9                 in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute10                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute11                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute12                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute13                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute14                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute15                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute16                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute17                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute18                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute19                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute20                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute21                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute22                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute23                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute24                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute25                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute26                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute27                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute28                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute29                in  varchar2  default hr_api.g_varchar2
  ,p_cep_attribute30                in  varchar2  default hr_api.g_varchar2
  ,p_object_version_number          in out nocopy number
  ,p_effective_date                 in  date
  ,p_datetrack_mode                 in  varchar2
  ,p_compute_score_flag             in  varchar2  default hr_api.g_varchar2
  ) is
  --
  -- Declare cursors and local variables
  -- 3688111
  cursor c_cep is
  select eligy_prfl_id
  from  ben_prtn_elig_prfl_f
  where prtn_elig_prfl_id = p_prtn_elig_prfl_id
  and  p_effective_date between effective_start_date and effective_end_date ;

Line: 451

    select pgm_id,
           ptip_id,
           plip_id,
           pl_id,
           oipl_id
    from   ben_prtn_elig_f a,
           ben_prtn_elig_prfl_f b
    where  a.prtn_elig_id = b.prtn_elig_id
    and    b.prtn_elig_prfl_id = p_prtn_elig_prfl_id;

Line: 463

  select drvbl_fctr_prtn_elig_flag
  from ben_pgm_f
  where pgm_id = c_id
  and p_effective_date between effective_start_date and effective_end_date ;

Line: 469

  select drvbl_fctr_prtn_elig_flag
  from ben_pl_f
  where pl_id = c_id
  and p_effective_date between effective_start_date and effective_end_date ;

Line: 475

  select drvbl_fctr_prtn_elig_flag
  from ben_plip_f
  where plip_id = c_id
  and p_effective_date between effective_start_date and effective_end_date ;

Line: 481

  select drvbl_fctr_prtn_elig_flag
  from ben_ptip_f
  where ptip_id = c_id
  and p_effective_date between effective_start_date and effective_end_date ;

Line: 488

  select drvbl_fctr_prtn_elig_flag
  from ben_oipl_f
  where oipl_id = c_id
  and p_effective_date between effective_start_date and effective_end_date ;

Line: 494

  l_proc varchar2(72) := g_package||'update_PRTN_ELIG_PRFL';

Line: 508

  savepoint update_PRTN_ELIG_PRFL;

Line: 526

    ben_PRTN_ELIG_PRFL_bk2.update_PRTN_ELIG_PRFL_b
      (
       p_prtn_elig_prfl_id              =>  p_prtn_elig_prfl_id
      ,p_business_group_id              =>  p_business_group_id
      ,p_mndtry_flag                    =>  p_mndtry_flag
      ,p_prtn_elig_id                   =>  p_prtn_elig_id
      ,p_eligy_prfl_id                  =>  p_eligy_prfl_id
      ,p_Elig_prfl_type_cd              =>  p_Elig_prfl_type_cd
      ,p_cep_attribute_category         =>  p_cep_attribute_category
      ,p_cep_attribute1                 =>  p_cep_attribute1
      ,p_cep_attribute2                 =>  p_cep_attribute2
      ,p_cep_attribute3                 =>  p_cep_attribute3
      ,p_cep_attribute4                 =>  p_cep_attribute4
      ,p_cep_attribute5                 =>  p_cep_attribute5
      ,p_cep_attribute6                 =>  p_cep_attribute6
      ,p_cep_attribute7                 =>  p_cep_attribute7
      ,p_cep_attribute8                 =>  p_cep_attribute8
      ,p_cep_attribute9                 =>  p_cep_attribute9
      ,p_cep_attribute10                =>  p_cep_attribute10
      ,p_cep_attribute11                =>  p_cep_attribute11
      ,p_cep_attribute12                =>  p_cep_attribute12
      ,p_cep_attribute13                =>  p_cep_attribute13
      ,p_cep_attribute14                =>  p_cep_attribute14
      ,p_cep_attribute15                =>  p_cep_attribute15
      ,p_cep_attribute16                =>  p_cep_attribute16
      ,p_cep_attribute17                =>  p_cep_attribute17
      ,p_cep_attribute18                =>  p_cep_attribute18
      ,p_cep_attribute19                =>  p_cep_attribute19
      ,p_cep_attribute20                =>  p_cep_attribute20
      ,p_cep_attribute21                =>  p_cep_attribute21
      ,p_cep_attribute22                =>  p_cep_attribute22
      ,p_cep_attribute23                =>  p_cep_attribute23
      ,p_cep_attribute24                =>  p_cep_attribute24
      ,p_cep_attribute25                =>  p_cep_attribute25
      ,p_cep_attribute26                =>  p_cep_attribute26
      ,p_cep_attribute27                =>  p_cep_attribute27
      ,p_cep_attribute28                =>  p_cep_attribute28
      ,p_cep_attribute29                =>  p_cep_attribute29
      ,p_cep_attribute30                =>  p_cep_attribute30
      ,p_object_version_number          =>  p_object_version_number
    ,p_effective_date                      => trunc(p_effective_date)
    ,p_datetrack_mode                      => p_datetrack_mode
      ,p_compute_score_flag             =>  p_compute_score_flag
      );

Line: 573

        (p_module_name => 'UPDATE_PRTN_ELIG_PRFL'
        ,p_hook_type   => 'BP'
        );

Line: 632

    ben_PRTN_ELIG_PRFL_bk2.update_PRTN_ELIG_PRFL_a
      (
       p_prtn_elig_prfl_id              =>  p_prtn_elig_prfl_id
      ,p_effective_start_date           =>  l_effective_start_date
      ,p_effective_end_date             =>  l_effective_end_date
      ,p_business_group_id              =>  p_business_group_id
      ,p_mndtry_flag                    =>  p_mndtry_flag
      ,p_prtn_elig_id                   =>  p_prtn_elig_id
      ,p_eligy_prfl_id                  =>  p_eligy_prfl_id
      ,p_Elig_prfl_type_cd              =>  p_Elig_prfl_type_cd
      ,p_cep_attribute_category         =>  p_cep_attribute_category
      ,p_cep_attribute1                 =>  p_cep_attribute1
      ,p_cep_attribute2                 =>  p_cep_attribute2
      ,p_cep_attribute3                 =>  p_cep_attribute3
      ,p_cep_attribute4                 =>  p_cep_attribute4
      ,p_cep_attribute5                 =>  p_cep_attribute5
      ,p_cep_attribute6                 =>  p_cep_attribute6
      ,p_cep_attribute7                 =>  p_cep_attribute7
      ,p_cep_attribute8                 =>  p_cep_attribute8
      ,p_cep_attribute9                 =>  p_cep_attribute9
      ,p_cep_attribute10                =>  p_cep_attribute10
      ,p_cep_attribute11                =>  p_cep_attribute11
      ,p_cep_attribute12                =>  p_cep_attribute12
      ,p_cep_attribute13                =>  p_cep_attribute13
      ,p_cep_attribute14                =>  p_cep_attribute14
      ,p_cep_attribute15                =>  p_cep_attribute15
      ,p_cep_attribute16                =>  p_cep_attribute16
      ,p_cep_attribute17                =>  p_cep_attribute17
      ,p_cep_attribute18                =>  p_cep_attribute18
      ,p_cep_attribute19                =>  p_cep_attribute19
      ,p_cep_attribute20                =>  p_cep_attribute20
      ,p_cep_attribute21                =>  p_cep_attribute21
      ,p_cep_attribute22                =>  p_cep_attribute22
      ,p_cep_attribute23                =>  p_cep_attribute23
      ,p_cep_attribute24                =>  p_cep_attribute24
      ,p_cep_attribute25                =>  p_cep_attribute25
      ,p_cep_attribute26                =>  p_cep_attribute26
      ,p_cep_attribute27                =>  p_cep_attribute27
      ,p_cep_attribute28                =>  p_cep_attribute28
      ,p_cep_attribute29                =>  p_cep_attribute29
      ,p_cep_attribute30                =>  p_cep_attribute30
      ,p_object_version_number          =>  l_object_version_number
      ,p_effective_date                     => trunc(p_effective_date)
      ,p_datetrack_mode                     => p_datetrack_mode
      ,p_compute_score_flag             =>  p_compute_score_flag
      );

Line: 681

        (p_module_name => 'UPDATE_PRTN_ELIG_PRFL'
        ,p_hook_type   => 'AP'
        );

Line: 724

         ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pgm_f','pgm_id',l_c1.pgm_id);

Line: 740

           ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pl_f','pl_id',l_c1.pl_id);

Line: 755

          ben_derivable_factor.eligy_prfl_handler('DELETE','ben_plip_f','plip_id',l_c1.plip_id);

Line: 770

          ben_derivable_factor.eligy_prfl_handler('DELETE','ben_ptip_f','ptip_id',l_c1.ptip_id);

Line: 786

          ben_derivable_factor.eligy_prfl_handler('DELETE','ben_oipl_f','oipl_id',l_c1.oipl_id);

Line: 808

    ROLLBACK TO update_PRTN_ELIG_PRFL;

Line: 820

    ROLLBACK TO update_PRTN_ELIG_PRFL;

Line: 826

end update_PRTN_ELIG_PRFL;

Line: 831

procedure delete_PRTN_ELIG_PRFL
  (p_validate                       in  boolean  default false
  ,p_prtn_elig_prfl_id              in  number
  ,p_effective_start_date           out nocopy date
  ,p_effective_end_date             out nocopy date
  ,p_object_version_number          in out nocopy number
  ,p_effective_date                 in  date
  ,p_datetrack_mode                 in  varchar2
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc varchar2(72) := g_package||'update_PRTN_ELIG_PRFL';

Line: 849

    select pgm_id,
           ptip_id,
           plip_id,
           pl_id,
           oipl_id
    from   ben_prtn_elig_f a,
           ben_prtn_elig_prfl_f b
    where  a.prtn_elig_id = b.prtn_elig_id
    and    b.prtn_elig_prfl_id = p_prtn_elig_prfl_id;

Line: 871

  savepoint delete_PRTN_ELIG_PRFL;

Line: 884

    ben_PRTN_ELIG_PRFL_bk3.delete_PRTN_ELIG_PRFL_b
      (
       p_prtn_elig_prfl_id              =>  p_prtn_elig_prfl_id
      ,p_object_version_number          =>  p_object_version_number
    ,p_effective_date                      => trunc(p_effective_date)
    ,p_datetrack_mode                      => p_datetrack_mode
      );

Line: 894

        (p_module_name => 'DELETE_PRTN_ELIG_PRFL'
        ,p_hook_type   => 'BP'
        );

Line: 916

    ben_PRTN_ELIG_PRFL_bk3.delete_PRTN_ELIG_PRFL_a
      (
       p_prtn_elig_prfl_id              =>  p_prtn_elig_prfl_id
      ,p_effective_start_date           =>  l_effective_start_date
      ,p_effective_end_date             =>  l_effective_end_date
      ,p_object_version_number          =>  l_object_version_number
      ,p_effective_date                 => trunc(p_effective_date)
      ,p_datetrack_mode                 => p_datetrack_mode
      );

Line: 928

        (p_module_name => 'DELETE_PRTN_ELIG_PRFL'
        ,p_hook_type   => 'AP'
        );

Line: 940

    update ben_pgm_f c
      set    c.elig_apls_flag = 'N'
      where not exists
          (select a.pgm_id
           from   ben_prtn_elig_f a,
                  ben_prtn_elig_prfl_f b
           where  a.prtn_elig_id = b.prtn_elig_id
           and    a.pgm_id = c.pgm_id)
      and not exists
            (select a.pgm_id
             from   ben_prtn_elig_f a,
                    ben_prtn_eligy_rl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.pgm_id = c.pgm_id)
      and c.pgm_id = l_c1.pgm_id;

Line: 956

     ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pgm_f','pgm_id',l_c1.pgm_id);

Line: 962

    update ben_plip_f c
      set    c.elig_apls_flag = 'N'
      where not exists
            (select a.plip_id
             from   ben_prtn_elig_f a,
                    ben_prtn_elig_prfl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.plip_id = c.plip_id)
      and not exists
            (select a.plip_id
             from   ben_prtn_elig_f a,
                    ben_prtn_eligy_rl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.plip_id = c.plip_id)
      and c.plip_id = l_c1.plip_id;

Line: 979

     ben_derivable_factor.eligy_prfl_handler('DELETE','ben_plip_f','plip_id',l_c1.plip_id);

Line: 985

    update ben_ptip_f c
      set    c.elig_apls_flag = 'N'
      where not exists
            (select a.ptip_id
             from   ben_prtn_elig_f a,
                    ben_prtn_elig_prfl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.ptip_id = c.ptip_id)
      and not exists
            (select a.ptip_id
             from   ben_prtn_elig_f a,
                    ben_prtn_eligy_rl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.ptip_id = c.ptip_id)
      and c.ptip_id = l_c1.ptip_id;

Line: 1002

     ben_derivable_factor.eligy_prfl_handler('DELETE','ben_ptip_f','ptip_id',l_c1.ptip_id);

Line: 1007

    update ben_pl_f c
      set    c.elig_apls_flag = 'N'
      where not exists
            (select a.pl_id
             from   ben_prtn_elig_f a,
                    ben_prtn_elig_prfl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.pl_id = c.pl_id)
      and not exists
            (select a.pl_id
             from   ben_prtn_elig_f a,
                    ben_prtn_eligy_rl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.pl_id = c.pl_id)
      and c.pl_id = l_c1.pl_id;

Line: 1024

     ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pl_f','pl_id',l_c1.pl_id);

Line: 1030

    update ben_oipl_f c
      set    c.elig_apls_flag = 'N'
      where not exists
            (select a.oipl_id
             from   ben_prtn_elig_f a,
                    ben_prtn_elig_prfl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.oipl_id = c.oipl_id)
      and not exists
            (select a.oipl_id
             from   ben_prtn_elig_f a,
                    ben_prtn_eligy_rl_f b
             where  a.prtn_elig_id = b.prtn_elig_id
             and    a.oipl_id = c.oipl_id)
      and c.oipl_id = l_c1.oipl_id;

Line: 1047

     ben_derivable_factor.eligy_prfl_handler('DELETE','ben_oipl_f','oipl_id',l_c1.oipl_id);

Line: 1068

    ROLLBACK TO delete_PRTN_ELIG_PRFL;

Line: 1081

    ROLLBACK TO delete_PRTN_ELIG_PRFL;

Line: 1087

end delete_PRTN_ELIG_PRFL;

Line: 1128

procedure insert_prtn_elig
 (
    p_pgm_id                in number  default null
   ,p_pl_id                 in number  default null
   ,p_plip_id               in number  default null
   ,p_oipl_id               in number  default null
   ,p_ptip_id               in number  default null
   ,p_effective_date        in date
   ,p_business_group_id     in number  default null
   ,p_prtn_elig_id          out nocopy number
  )  is
  --
     l_effective_start_date      date;

Line: 1146

       select prtn_elig_id
        from ben_prtn_elig_f
        where ( pgm_id   = p_pgm_id or p_pgm_id is null  ) and
              ( pl_id    = p_pl_id  or p_pl_id is null   ) and
              ( plip_id  = p_plip_id or p_plip_id is null) and
              ( ptip_id  = p_ptip_id or p_ptip_id is null) and
              ( oipl_id  = p_oipl_id or p_oipl_id is null) and
                effective_start_date <= p_effective_date   and
                effective_end_date   >= p_effective_date;