APPS.GHR_BENEFITS_EIT SQL Statements

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

Line: 19

    SELECT pgm.pgm_id
    FROM   ben_pgm_f pgm
    WHERE  pgm.name = c_prog_name
    AND    pgm.business_group_id  = c_business_group_id
    AND    c_effective_date between effective_start_date and effective_end_date;

Line: 27

    SELECT 1
    FROM   ben_prtt_enrt_rslt_f
    WHERE  person_id = c_person_id
    AND    pgm_id    = c_pgm_id
    AND    prtt_enrt_rslt_stat_cd IS NULL
    AND    c_effective_date between effective_start_date and effective_end_date;

Line: 37

SELECT plt.pl_typ_id
FROM   ben_pl_typ_f plt
WHERE  plt.name =  c_plan_type -- 'Savings Plan'
AND    plt.business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 45

 SELECT ptip_id
 FROM   ben_ptip_f
 WHERE  pl_typ_id = c_plan_type_id
 AND    pgm_id = c_pgm_id
 AND    c_effective_date between effective_start_date and effective_end_date;

Line: 53

 SELECT pln.pl_id  pl_id
 FROM   ben_pl_f pln
 WHERE  pln.short_code = c_health_plan
 AND    pln.business_group_id = c_business_group_id
 AND    c_effective_date between effective_start_date and effective_end_date
 AND    pl_stat_cd = 'A';

Line: 63

SELECT opt_id
FROM   ben_opt_f opt
WHERE  opt.short_code = c_option_code
AND    opt.business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 72

SELECT plip.plip_id
FROM   ben_plip_f plip
WHERE  plip.pl_id  =    c_plan_id
AND    plip.pgm_id = c_pgm_id
AND    plip.business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 83

SELECT oipl_id
FROM   ben_oipl_f
WHERE  pl_id =  c_pl_id
AND    opt_id = c_opt_id
AND    business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 92

select ler.ler_id
from   ben_ler_f ler
where  ler.business_group_id = c_business_group_id
and    ler.name              = c_life_event
and    c_effective_date between effective_start_date and effective_end_date;

Line: 102

SELECT elig_per_elctbl_chc_id,
	pil.per_in_ler_id,
	prtt_enrt_rslt_id
FROM   ben_elig_per_ELCTBL_chc chc ,
	ben_per_in_ler pil
WHERE chc.pgm_id = c_pgm_id
AND   chc.pl_typ_id = c_pl_typ_id
AND   chc.pl_id = c_pl_id
AND   chc.plip_id = c_plip_id
AND   chc.ptip_id = c_ptip_id
AND   chc.oipl_id = c_oipl_id
AND   pil.per_in_ler_id = chc.per_in_ler_id
AND   pil.ler_id  = c_ler_id
AND   pil.person_id = c_person_id
AND   PER_IN_LER_STAT_CD NOT IN ('BCKDT','PROCD');

Line: 122

         select elig_per_elctbl_chc_id,
                pil.per_in_ler_id,
		prtt_enrt_rslt_id
         from   ben_elig_per_ELCTBL_chc chc ,
                ben_per_in_ler pil
         where chc.pgm_id = c_pgm_id
         and   chc.pl_typ_id = c_pl_typ_id
         and   chc.pl_id = c_pl_id
         and   chc.plip_id = c_plip_id
         and   chc.ptip_id = c_ptip_id
         and   pil.per_in_ler_id = chc.per_in_ler_id
         and   pil.ler_id  = c_ler_id
         and   pil.person_id = c_person_id
	 AND   PER_IN_LER_STAT_CD NOT IN ('BCKDT','PROCD');

Line: 192

SELECT 1 FROM fnd_sessions
where session_id = c_session_id;

Line: 196

select ler_id from ben_ptnl_ler_for_per
where business_group_id = p_business_group_id
and person_id = p_person_id
and ptnl_ler_for_per_stat_cd ='UNPROCD'
and ler_id not in (select ler_id from ben_ler_f where name
= 'Unrestricted' and business_group_id = p_business_group_id)
and LF_EVT_OCRD_DT  = l_effective_date;

Line: 206

    select enrt_cvg_strt_dt
    from   ben_prtt_enrt_rslt_f
    where  prtt_enrt_rslt_id = l_prtt_enrt_rslt_id;

Line: 214

    select le.name
    from   ben_ptnl_ler_for_per ptnl,
           ben_ler_f le
    where  ptnl.business_group_id = p_business_group_id
    and    ptnl.person_id = p_person_id
    and    ptnl_ler_for_per_stat_cd ='UNPROCD'
    and    le.name <> 'Unrestricted'
    and    ptnl.ler_id = le.ler_id
    and    lf_evt_ocrd_dt < l_effective_date
    order by ptnl_ler_for_per_id;

Line: 229

SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment,
           eef.element_entry_id ,
	   eef.object_version_number
    FROM   pay_element_entries_f eef,
           pay_element_types_f elt
    WHERE  assignment_id = p_asg_id
    AND    elt.element_type_id = eef.element_type_id
    AND    eef.effective_start_date BETWEEN elt.effective_start_date  AND
           elt.effective_end_date
    AND    p_effective_date between eef.effective_start_date and eef.effective_end_date
    AND    upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
                                                                   p_business_group_id,
                                                                   p_effective_date))
              IN  ('HEALTH BENEFITS');

Line: 248

    select per_in_ler_id
    from   ben_per_in_ler pil,ben_ler_f lf
    where  pil.person_id = p_person_id
    and    pil.business_group_id = p_business_group_id
    and    pil.PER_IN_LER_STAT_CD in ('STRTD')
    and    lf.ler_id = pil.ler_id
    and    name <> 'Unrestricted'
    and    lf_evt_ocrd_dt <> p_effective_date
    and    p_effective_date between lf.effective_start_date and lf.effective_end_date;

Line: 260

    select 1
    from   per_all_assignments_f asg
    where  asg.assignment_id = p_assignment_id
    and    p_effective_date between asg.effective_start_date
                            and     asg.effective_end_date;

Line: 271

    l_delete_warning           boolean;

Line: 527

        ,p_datetrack_mode         => 'INSERT'
        ,p_suspend_flag           => l_suspend_flag
        ,p_effective_start_date   => l_esd
        ,p_effective_end_date     => l_eed
        ,p_object_version_number  => l_ovn
        ,p_prtt_enrt_interim_id   => l_prtt_enrt_interim_id
        ,p_business_group_id      => l_business_group_id
        ,p_dpnt_actn_warning      => l_Boolean
        ,p_bnf_actn_warning       => l_Boolean
        ,p_ctfn_actn_warning      => l_Boolean
        );

Line: 618

    SELECT pgm.pgm_id
    FROM   ben_pgm_f pgm
    WHERE  pgm.name = c_prog_name
    AND    pgm.business_group_id  = c_business_group_id
    AND    c_effective_date between effective_start_date and effective_end_date;

Line: 626

    SELECT 1
    FROM   ben_prtt_enrt_rslt_f
    WHERE  person_id = c_person_id
    AND    pgm_id    = c_pgm_id
    AND    c_effective_date between effective_start_date and effective_end_date;

Line: 635

SELECT plt.pl_typ_id
FROM   ben_pl_typ_f plt
WHERE  plt.name =  c_plan_type -- 'Savings Plan'
AND    plt.business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 643

 SELECT ptip_id
 FROM   ben_ptip_f
 WHERE  pl_typ_id = c_plan_type_id
 AND    pgm_id = c_pgm_id
 AND    c_effective_date between effective_start_date and effective_end_date;

Line: 651

 SELECT pln.pl_id  pl_id
 FROM   ben_pl_f pln
 WHERE  pln.name = c_pl_name
 AND    pln.business_group_id = c_business_group_id
 AND    c_effective_date between effective_start_date and effective_end_date;

Line: 660

SELECT opt_id
FROM   ben_opt_f opt
WHERE  opt.name = c_opt_name
AND    opt.business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 669

SELECT plip.plip_id
FROM   ben_plip_f plip
WHERE  plip.pl_id  =    c_plan_id
AND    plip.pgm_id = c_pgm_id
AND    plip.business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 680

SELECT oipl_id
FROM   ben_oipl_f
WHERE  pl_id =  c_pl_id
AND    opt_id = c_opt_id
AND    business_group_id = c_business_group_id
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 691

SELECT elig_per_elctbl_chc_id,
	pil.per_in_ler_id
FROM   ben_elig_per_ELCTBL_chc chc ,
	ben_per_in_ler pil
WHERE chc.pgm_id = c_pgm_id
AND   chc.pl_typ_id = c_pl_typ_id
AND   chc.pl_id = c_pl_id
AND   chc.plip_id = c_plip_id
AND   chc.ptip_id = c_ptip_id
AND   chc.oipl_id = c_oipl_id
AND   pil.per_in_ler_id = chc.per_in_ler_id
--AND   pil.ler_id  = c_ler_id
AND   pil.person_id = c_person_id;

Line: 706

SELECT enrt_rt_id
FROM   ben_enrt_rt
WHERE  elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id;

Line: 763

SELECT 1 FROM fnd_sessions
where session_id = c_session_id;

Line: 934

        ,p_datetrack_mode         => 'INSERT'
        ,p_suspend_flag           => l_suspend_flag
        ,p_effective_start_date   => l_esd
        ,p_effective_end_date     => l_eed
        ,p_object_version_number  => l_ovn
        ,p_prtt_enrt_interim_id   => l_prtt_enrt_interim_id
        ,p_business_group_id      => l_business_group_id
        ,p_dpnt_actn_warning      => l_Boolean
        ,p_bnf_actn_warning       => l_Boolean
        ,p_ctfn_actn_warning      => l_Boolean
        );