eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_PRMRY_CARE_PRVDR_API SQL Statements

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

Line: 19


  l_update               boolean;

Line: 20


  l_update_override      boolean;

Line: 21


  l_update_change_insert boolean;

Line: 23


  l_delete               boolean;

Line: 25


  l_delete_next_change   boolean;

Line: 31


   if p_mini_mode = 'U' then  -- update

      ben_ppr_shd.find_dt_upd_modes
       (p_effective_date   => p_effective_date
   ,p_base_key_value   => p_base_key_value
   ,p_correction         => l_correction
   ,p_update               => l_update
   ,p_update_override  => l_update_override
   ,p_update_change_insert => l_update_change_insert
       );

Line: 42


      if (p_desired_datetrack_mode = hr_api.g_update and l_update) then
          p_datetrack_allow := hr_api.g_update;

Line: 46


      elsif(p_desired_datetrack_mode = hr_api.g_update_override
            and l_update_override) then
          p_datetrack_allow := hr_api.g_update_override;

Line: 49


      elsif(p_desired_datetrack_mode = hr_api.g_update_change_insert
            and l_update_change_insert) then
          p_datetrack_allow := hr_api.g_update_change_insert;

Line: 52


      elsif(l_update) then
          p_datetrack_allow := hr_api.g_update;

Line: 61


   else  -- mini-mode = 'D' for delete
      ben_ppr_shd.find_dt_del_modes
       (p_effective_date   => p_effective_date
   ,p_base_key_value   => p_base_key_value
       ,p_zap                  => l_zap
       ,p_delete               => l_delete
       ,p_future_change        => l_future_change
       ,p_delete_next_change   => l_delete_next_change);

Line: 70


      if (p_desired_datetrack_mode = hr_api.g_delete and l_delete) then
          p_datetrack_allow := hr_api.g_delete;

Line: 76


      elsif(p_desired_datetrack_mode = hr_api.g_delete_next_change
            and l_delete_next_change) then
          p_datetrack_allow := hr_api.g_delete_next_change;

Line: 79


      elsif (l_delete) then
          p_datetrack_allow := hr_api.g_delete;

Line: 146


  ,p_program_update_date            in  date      default null
  ,p_object_version_number          out nocopy number
  ,p_effective_date                 in  date
  ) is
  --
  -- Declare cursors and local variables
  --
  l_prmry_care_prvdr_id ben_prmry_care_prvdr_f.prmry_care_prvdr_id%TYPE;

Line: 217


      ,p_program_update_date            =>  p_program_update_date
      ,p_effective_date                 => trunc(p_effective_date)
      );

Line: 277


    ,p_program_update_date           => p_program_update_date
    ,p_object_version_number         => l_object_version_number
    ,p_effective_date                => trunc(p_effective_date)
    );

Line: 353


      ,p_program_update_date            =>  p_program_update_date
      ,p_object_version_number          =>  l_object_version_number
      ,p_effective_date                      => trunc(p_effective_date)
      );

Line: 493


procedure update_PRMRY_CARE_PRVDR
  (p_validate                       in  boolean   default false
  ,p_prmry_care_prvdr_id            in  number
  ,p_effective_start_date           out nocopy date
  ,p_effective_end_date             out nocopy date
  ,p_prmry_care_prvdr_typ_cd        in  varchar2  default hr_api.g_varchar2
  ,p_name                           in  varchar2  default hr_api.g_varchar2
  ,p_ext_ident                      in  varchar2  default hr_api.g_varchar2
  ,p_prtt_enrt_rslt_id              in  number    default hr_api.g_number
  ,p_elig_cvrd_dpnt_id              in  number    default hr_api.g_number
  ,p_business_group_id              in  number    default hr_api.g_number
  ,p_ppr_attribute_category         in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute1                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute2                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute3                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute4                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute5                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute6                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute7                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute8                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute9                 in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute10                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute11                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute12                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute13                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute14                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute15                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute16                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute17                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute18                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute19                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute20                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute21                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute22                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute23                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute24                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute25                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute26                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute27                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute28                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute29                in  varchar2  default hr_api.g_varchar2
  ,p_ppr_attribute30                in  varchar2  default hr_api.g_varchar2
  ,p_request_id                     in  number    default hr_api.g_number
  ,p_program_application_id         in  number    default hr_api.g_number
  ,p_program_id                     in  number    default hr_api.g_number
  ,p_program_update_date            in  date      default hr_api.g_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_PRMRY_CARE_PRVDR';

Line: 553


  select pcp.prmry_care_prvdr_typ_cd,
         pcp.name,
         pcp.ext_ident,
         pcp.prtt_enrt_rslt_id,
         pcp.elig_cvrd_dpnt_id
  from ben_prmry_care_prvdr_f pcp
  where pcp.prmry_care_prvdr_id = p_prmry_care_prvdr_id
  and p_effective_date between pcp.effective_start_date and pcp.effective_end_date;

Line: 570


  savepoint update_PRMRY_CARE_PRVDR;

Line: 594


    ben_PRMRY_CARE_PRVDR_bk2.update_PRMRY_CARE_PRVDR_b
      (
       p_prmry_care_prvdr_id            =>  p_prmry_care_prvdr_id
      ,p_prmry_care_prvdr_typ_cd        =>  p_prmry_care_prvdr_typ_cd
      ,p_name                           =>  p_name
      ,p_ext_ident                      =>  p_ext_ident
      ,p_prtt_enrt_rslt_id              =>  p_prtt_enrt_rslt_id
      ,p_elig_cvrd_dpnt_id              =>  p_elig_cvrd_dpnt_id
      ,p_business_group_id              =>  p_business_group_id
      ,p_ppr_attribute_category         =>  p_ppr_attribute_category
      ,p_ppr_attribute1                 =>  p_ppr_attribute1
      ,p_ppr_attribute2                 =>  p_ppr_attribute2
      ,p_ppr_attribute3                 =>  p_ppr_attribute3
      ,p_ppr_attribute4                 =>  p_ppr_attribute4
      ,p_ppr_attribute5                 =>  p_ppr_attribute5
      ,p_ppr_attribute6                 =>  p_ppr_attribute6
      ,p_ppr_attribute7                 =>  p_ppr_attribute7
      ,p_ppr_attribute8                 =>  p_ppr_attribute8
      ,p_ppr_attribute9                 =>  p_ppr_attribute9
      ,p_ppr_attribute10                =>  p_ppr_attribute10
      ,p_ppr_attribute11                =>  p_ppr_attribute11
      ,p_ppr_attribute12                =>  p_ppr_attribute12
      ,p_ppr_attribute13                =>  p_ppr_attribute13
      ,p_ppr_attribute14                =>  p_ppr_attribute14
      ,p_ppr_attribute15                =>  p_ppr_attribute15
      ,p_ppr_attribute16                =>  p_ppr_attribute16
      ,p_ppr_attribute17                =>  p_ppr_attribute17
      ,p_ppr_attribute18                =>  p_ppr_attribute18
      ,p_ppr_attribute19                =>  p_ppr_attribute19
      ,p_ppr_attribute20                =>  p_ppr_attribute20
      ,p_ppr_attribute21                =>  p_ppr_attribute21
      ,p_ppr_attribute22                =>  p_ppr_attribute22
      ,p_ppr_attribute23                =>  p_ppr_attribute23
      ,p_ppr_attribute24                =>  p_ppr_attribute24
      ,p_ppr_attribute25                =>  p_ppr_attribute25
      ,p_ppr_attribute26                =>  p_ppr_attribute26
      ,p_ppr_attribute27                =>  p_ppr_attribute27
      ,p_ppr_attribute28                =>  p_ppr_attribute28
      ,p_ppr_attribute29                =>  p_ppr_attribute29
      ,p_ppr_attribute30                =>  p_ppr_attribute30
      ,p_request_id                     =>  p_request_id
      ,p_program_application_id         =>  p_program_application_id
      ,p_program_id                     =>  p_program_id
      ,p_program_update_date            =>  p_program_update_date
      ,p_object_version_number          =>  p_object_version_number
      ,p_effective_date                 => trunc(p_effective_date)
      ,p_datetrack_mode                 => l_datetrack_mode
      );

Line: 645


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

Line: 698


    ,p_program_update_date           => p_program_update_date
    ,p_object_version_number         => l_object_version_number
    ,p_effective_date                => trunc(p_effective_date)
    ,p_datetrack_mode                => l_datetrack_mode
    );

Line: 705


       (p_action               => 'UPDATE',
        p_ext_ident            => p_ext_ident,
        p_old_ext_ident        => l_old_pcp_values.ext_ident,
        p_name                 => p_name,
        p_old_name             => l_old_pcp_values.name,
        p_prmry_care_prvdr_typ_cd => p_prmry_care_prvdr_typ_cd,
        p_old_prmry_care_prvdr_typ_cd => l_old_pcp_values.prmry_care_prvdr_typ_cd,
        p_prmry_care_prvdr_id  => p_prmry_care_prvdr_id,
        p_elig_cvrd_dpnt_id    => l_old_pcp_values.elig_cvrd_dpnt_id,
        p_prtt_enrt_rslt_id    => l_old_pcp_values.prtt_enrt_rslt_id,
        p_business_group_id    => p_business_group_id,
        p_effective_date       => p_effective_date);

Line: 722


    ben_PRMRY_CARE_PRVDR_bk2.update_PRMRY_CARE_PRVDR_a
      (
       p_prmry_care_prvdr_id            =>  p_prmry_care_prvdr_id
      ,p_effective_start_date           =>  l_effective_start_date
      ,p_effective_end_date             =>  l_effective_end_date
      ,p_prmry_care_prvdr_typ_cd        =>  p_prmry_care_prvdr_typ_cd
      ,p_name                           =>  p_name
      ,p_ext_ident                      =>  p_ext_ident
      ,p_prtt_enrt_rslt_id              =>  p_prtt_enrt_rslt_id
      ,p_elig_cvrd_dpnt_id              =>  p_elig_cvrd_dpnt_id
      ,p_business_group_id              =>  p_business_group_id
      ,p_ppr_attribute_category         =>  p_ppr_attribute_category
      ,p_ppr_attribute1                 =>  p_ppr_attribute1
      ,p_ppr_attribute2                 =>  p_ppr_attribute2
      ,p_ppr_attribute3                 =>  p_ppr_attribute3
      ,p_ppr_attribute4                 =>  p_ppr_attribute4
      ,p_ppr_attribute5                 =>  p_ppr_attribute5
      ,p_ppr_attribute6                 =>  p_ppr_attribute6
      ,p_ppr_attribute7                 =>  p_ppr_attribute7
      ,p_ppr_attribute8                 =>  p_ppr_attribute8
      ,p_ppr_attribute9                 =>  p_ppr_attribute9
      ,p_ppr_attribute10                =>  p_ppr_attribute10
      ,p_ppr_attribute11                =>  p_ppr_attribute11
      ,p_ppr_attribute12                =>  p_ppr_attribute12
      ,p_ppr_attribute13                =>  p_ppr_attribute13
      ,p_ppr_attribute14                =>  p_ppr_attribute14
      ,p_ppr_attribute15                =>  p_ppr_attribute15
      ,p_ppr_attribute16                =>  p_ppr_attribute16
      ,p_ppr_attribute17                =>  p_ppr_attribute17
      ,p_ppr_attribute18                =>  p_ppr_attribute18
      ,p_ppr_attribute19                =>  p_ppr_attribute19
      ,p_ppr_attribute20                =>  p_ppr_attribute20
      ,p_ppr_attribute21                =>  p_ppr_attribute21
      ,p_ppr_attribute22                =>  p_ppr_attribute22
      ,p_ppr_attribute23                =>  p_ppr_attribute23
      ,p_ppr_attribute24                =>  p_ppr_attribute24
      ,p_ppr_attribute25                =>  p_ppr_attribute25
      ,p_ppr_attribute26                =>  p_ppr_attribute26
      ,p_ppr_attribute27                =>  p_ppr_attribute27
      ,p_ppr_attribute28                =>  p_ppr_attribute28
      ,p_ppr_attribute29                =>  p_ppr_attribute29
      ,p_ppr_attribute30                =>  p_ppr_attribute30
      ,p_request_id                     =>  p_request_id
      ,p_program_application_id         =>  p_program_application_id
      ,p_program_id                     =>  p_program_id
      ,p_program_update_date            =>  p_program_update_date
      ,p_object_version_number          =>  l_object_version_number
      ,p_effective_date                 => trunc(p_effective_date)
      ,p_datetrack_mode                 => l_datetrack_mode
      );

Line: 775


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

Line: 805


    ROLLBACK TO update_PRMRY_CARE_PRVDR;

Line: 817


    ROLLBACK TO update_PRMRY_CARE_PRVDR;

Line: 820


end update_PRMRY_CARE_PRVDR;

Line: 826


procedure update_prmry_care_prvdr_w
(
    p_prmry_care_prvdr_id            in  number
   ,p_effective_start_date           out nocopy date
   ,p_effective_end_date             out nocopy date
   ,p_prmry_care_prvdr_typ_cd        in  varchar2  default hr_api.g_varchar2
   ,p_name                           in  varchar2  default hr_api.g_varchar2
   ,p_ext_ident                      in  varchar2  default hr_api.g_varchar2
   ,p_prtt_enrt_rslt_id              in  number    default hr_api.g_number
   ,p_elig_cvrd_dpnt_id              in  number    default hr_api.g_number
   ,p_business_group_id              in  number    default hr_api.g_number
   ,p_object_version_number          in out nocopy number
   ,p_effective_date                 in  date
   ,p_datetrack_mode                 in  varchar2
   ,p_return_status                  out nocopy    varchar2
)
is
  --
  l_proc varchar2(72) := g_package||'update_prmry_care_prvdr_w';

Line: 855


  update_prmry_care_prvdr
  (
     p_prmry_care_prvdr_id      => p_prmry_care_prvdr_id
    ,p_effective_start_date     => l_effective_start_date
    ,p_effective_end_date       => l_effective_end_date
    ,p_prmry_care_prvdr_typ_cd  => p_prmry_care_prvdr_typ_cd
    ,p_name                     => p_name
    ,p_ext_ident                => p_ext_ident
    ,p_business_group_id        => p_business_group_id
    ,p_object_version_number    => l_object_version_number
    ,p_effective_date           => p_effective_date
    ,p_datetrack_mode           => p_datetrack_mode
  );

Line: 890


end update_prmry_care_prvdr_w;

Line: 896


procedure delete_PRMRY_CARE_PRVDR
  (p_validate                       in  boolean  default false
  ,p_prmry_care_prvdr_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
  ,p_called_from                    in varchar2
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc varchar2(72) := g_package||'delete_PRMRY_CARE_PRVDR';

Line: 917


  select pcp.prmry_care_prvdr_typ_cd,
         pcp.name,
         pcp.ext_ident,
         pcp.prtt_enrt_rslt_id,
         pcp.elig_cvrd_dpnt_id,
         pcp.business_group_id,
         pcp.effective_start_date
  from ben_prmry_care_prvdr_f pcp
  where pcp.prmry_care_prvdr_id = p_prmry_care_prvdr_id
  and p_effective_date between pcp.effective_start_date and pcp.effective_end_date;

Line: 936


  savepoint delete_PRMRY_CARE_PRVDR;

Line: 973


    ben_PRMRY_CARE_PRVDR_bk3.delete_PRMRY_CARE_PRVDR_b
      (
       p_prmry_care_prvdr_id            => p_prmry_care_prvdr_id
      ,p_object_version_number          => p_object_version_number
      ,p_effective_date                 => trunc(l_effective_date)
      ,p_datetrack_mode                 => l_datetrack_mode
      );

Line: 983


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

Line: 1003


       (p_action               => 'DELETE',
        p_old_ext_ident        => l_old_pcp_values.ext_ident,
        p_old_name             => l_old_pcp_values.name,
        p_old_prmry_care_prvdr_typ_cd => l_old_pcp_values.prmry_care_prvdr_typ_cd,
        p_prmry_care_prvdr_id  => p_prmry_care_prvdr_id,
        p_elig_cvrd_dpnt_id    => l_old_pcp_values.elig_cvrd_dpnt_id,
        p_prtt_enrt_rslt_id    => l_old_pcp_values.prtt_enrt_rslt_id,
        p_business_group_id    => l_old_pcp_values.business_group_id,
        p_effective_date       => l_effective_date);

Line: 1034


    ben_PRMRY_CARE_PRVDR_bk3.delete_PRMRY_CARE_PRVDR_a
      (
       p_prmry_care_prvdr_id            => p_prmry_care_prvdr_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(l_effective_date)
      ,p_datetrack_mode                 => l_datetrack_mode
      );

Line: 1046


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

Line: 1071


    ROLLBACK TO delete_PRMRY_CARE_PRVDR;

Line: 1084


    ROLLBACK TO delete_PRMRY_CARE_PRVDR;

Line: 1087


end delete_PRMRY_CARE_PRVDR;

Line: 1092


procedure delete_prmry_care_prvdr_w
(
     p_prmry_care_prvdr_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
    ,p_return_status                  out nocopy    varchar2
)
is

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

Line: 1116


  delete_prmry_care_prvdr
  (
     p_prmry_care_prvdr_id      => p_prmry_care_prvdr_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           => p_effective_date
    ,p_datetrack_mode           => p_datetrack_mode
  );

Line: 1145


end delete_prmry_care_prvdr_w;

Line: 1209


   select ecd.prtt_enrt_rslt_id,
          ecd.business_group_id
    from ben_elig_cvrd_dpnt_f ecd
    where ecd.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
      and p_effective_date between ecd.effective_start_date
                               and ecd.effective_end_date;

Line: 1217


   select pen.object_version_number,
          pen.sspndd_flag
   from   ben_prtt_enrt_rslt_f pen
   where  pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
   and    pen.prtt_enrt_rslt_stat_cd is null
   and    pen.business_group_id = l_business_group_id
   and    p_effective_date
          between pen.effective_start_date and pen.effective_end_date;