APPS.BEN_PPR_BUS SQL Statements

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

Line: 173

        select bpp.pcp_rpstry_flag
        into   l_pcp_rpstry_flag
        from   ben_prtt_enrt_rslt_f bper,
               ben_pl_pcp           bpp
        where  bper.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
        and    bper.pl_id           = bpp.pl_id
        and    p_effective_date  between  bper.effective_start_date and bper.effective_end_date;

Line: 302

    SELECT  name
    FROM    ben_prmry_care_prvdr_f
    WHERE   prmry_care_prvdr_id      <> nvl(p_prmry_care_prvdr_id, hr_api.g_number)
    AND     prmry_care_prvdr_typ_cd   = p_prmry_care_prvdr_typ_cd
    AND     prtt_enrt_rslt_id         = p_prtt_enrt_rslt_id
    AND     effective_end_date       >= p_validation_start_date
    AND     effective_start_date     <= p_validation_end_date  ;

Line: 372

      SELECT  bper.pl_id
      FROM    ben_prtt_enrt_rslt_f  bper,
              ben_pl_pcp            bpp
      WHERE   bper.prtt_enrt_rslt_id     = p_prtt_enrt_rslt_id
      AND     bper.pl_id                 = bpp.pl_id
      AND     bpp.pcp_dsgn_cd in ('R','O')
      AND     p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;

Line: 382

      SELECT  bper.prtt_enrt_rslt_id
      FROM    ben_elig_cvrd_dpnt_f    becd,
              ben_prtt_enrt_rslt_f    bper,
              ben_pl_pcp              bpp
      WHERE   becd.elig_cvrd_dpnt_id       = p_elig_cvrd_dpnt_id
      AND     becd.prtt_enrt_rslt_id       = bper.prtt_enrt_rslt_id
      AND     bper.pl_id                   = bpp.pl_id
      AND     bpp.pcp_dpnt_dsgn_cd in ('R','O')
      AND     p_effective_date BETWEEN becd.effective_start_date and becd.effective_end_date
      AND     p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;

Line: 394

      SELECT  oipl.oipl_id
      FROM    ben_prtt_enrt_rslt_f  bper,
              ben_oipl_f            oipl
      WHERE   bper.prtt_enrt_rslt_id     = p_prtt_enrt_rslt_id
      AND     oipl.oipl_id                 = bper.oipl_id
      AND     oipl.pcp_dsgn_cd in ('R','O')
      AND     p_effective_date BETWEEN oipl.effective_start_date and oipl.effective_end_date
      AND     p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;

Line: 404

      SELECT  bper.prtt_enrt_rslt_id
      FROM    ben_elig_cvrd_dpnt_f    becd,
              ben_prtt_enrt_rslt_f    bper,
              ben_oipl_f               oipl
      WHERE   becd.elig_cvrd_dpnt_id       = p_elig_cvrd_dpnt_id
      AND     becd.prtt_enrt_rslt_id       = bper.prtt_enrt_rslt_id
      AND     bper.oipl_id                   = oipl.oipl_id
      AND     oipl.pcp_dpnt_dsgn_cd in ('R','O')
      AND     p_effective_date BETWEEN becd.effective_start_date and becd.effective_end_date
      AND     p_effective_date BETWEEN oipl.effective_start_date and oipl.effective_end_date
      AND     p_effective_date BETWEEN bper.effective_start_date and bper.effective_end_date;

Line: 557

       select pen.person_id, nvl(pct.min_age,-1),
              nvl(pct.max_age,9999), pct.gndr_alwd_cd
       from   ben_pl_pcp_typ pct,
              ben_pl_pcp pcp,
              ben_prtt_enrt_rslt_f pen
       where  pen.prtt_enrt_rslt_id = p_rslt_id
         and  pen.pl_id = pcp.pl_id
         and  pcp.pl_pcp_id = pct.pl_pcp_id
         and  pct.pcp_typ_cd = p_prmry_care_prvdr_typ_cd
         and  p_effective_date between pen.effective_start_date
              and pen.effective_end_date;

Line: 570

       select trunc(date_of_birth), sex
       from   per_all_people_f
       where  person_id = p_person_id
       and    p_effective_date between effective_start_date
              and effective_end_date;

Line: 577

       select distinct prtt_enrt_rslt_id, dpnt_person_id
       from   ben_elig_cvrd_dpnt_f
       where  elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
       and    p_effective_date between effective_start_date
              and effective_end_date;

Line: 680

       select distinct prtt_enrt_rslt_id
       from   ben_elig_cvrd_dpnt_f
       where  elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id;

Line: 685

       select pcp.pcp_num_chgs
       from   ben_prtt_enrt_rslt_f pen, ben_pl_pcp pcp
       Where  pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
         and  pen.pl_id = pcp.pl_id
         and  p_effective_date between
              pen.effective_start_date and pen.effective_end_date;

Line: 693

       select count('x')
       from   ben_prmry_care_prvdr_f ppr
       where  (ppr.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
         or   ppr.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id)
         and  ppr.effective_start_date between p_first_day and p_last_day;

Line: 768

Procedure dt_update_validate
            (p_elig_cvrd_dpnt_id             in number default hr_api.g_number,
             p_prtt_enrt_rslt_id             in number default hr_api.g_number,
       p_datetrack_mode        in varchar2,
             p_validation_start_date       in date,
       p_validation_end_date       in date) Is
--
  l_proc      varchar2(72) := g_package||'dt_update_validate';

Line: 847

End dt_update_validate;

Line: 884

Procedure dt_delete_validate
            (p_prmry_care_prvdr_id    in number,
             p_datetrack_mode   in varchar2,
       p_validation_start_date  in date,
       p_validation_end_date  in date) Is
--
  l_proc  varchar2(72)  := g_package||'dt_delete_validate';

Line: 907

  If (p_datetrack_mode = 'DELETE' or
      p_datetrack_mode = 'ZAP') then
    --
    --
    -- Ensure the arguments are not null
    --
    hr_api.mandatory_arg_error
      (p_api_name       => l_proc,
       p_argument       => 'validation_start_date',
       p_argument_value => p_validation_start_date);

Line: 950

End dt_delete_validate;

Line: 955

Procedure insert_validate
  (p_rec       in ben_ppr_shd.g_rec_type,
   p_effective_date  in date,
   p_datetrack_mode  in varchar2,
   p_validation_start_date in date,
   p_validation_end_date   in date) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 1021

End insert_validate;

Line: 1026

Procedure update_validate
  (p_rec       in ben_ppr_shd.g_rec_type,
   p_effective_date  in date,
   p_datetrack_mode  in varchar2,
   p_validation_start_date in date,
   p_validation_end_date   in date) is
--
  l_proc  varchar2(72) := g_package||'update_validate';

Line: 1095

  dt_update_validate
    (p_elig_cvrd_dpnt_id             => p_rec.elig_cvrd_dpnt_id,
             p_prtt_enrt_rslt_id             => p_rec.prtt_enrt_rslt_id,
     p_datetrack_mode                => p_datetrack_mode,
     p_validation_start_date       => p_validation_start_date,
     p_validation_end_date       => p_validation_end_date);

Line: 1103

End update_validate;

Line: 1108

Procedure delete_validate
  (p_rec       in ben_ppr_shd.g_rec_type,
   p_effective_date  in date,
   p_datetrack_mode  in varchar2,
   p_validation_start_date in date,
   p_validation_end_date   in date) is
--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 1122

  dt_delete_validate
    (p_datetrack_mode   => p_datetrack_mode,
     p_validation_start_date  => p_validation_start_date,
     p_validation_end_date  => p_validation_end_date,
     p_prmry_care_prvdr_id    => p_rec.prmry_care_prvdr_id);

Line: 1129

End delete_validate;

Line: 1142

    select a.legislation_code
    from   per_business_groups a,
           ben_prmry_care_prvdr_f b
    where b.prmry_care_prvdr_id      = p_prmry_care_prvdr_id
    and   a.business_group_id = b.business_group_id;