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_EXT_EVALUATE_INCLUSION SQL Statements

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

Line: 180


  g_tc_deleted_excld_flag  := 'N';

Line: 187


  g_tc_deleted_incl_rqd  := 'N';

Line: 619


  for i in 1..g_tc_deleted_list.count loop
    if g_tc_deleted_list.exists(i) then
      g_tc_deleted_list(i) := null;

Line: 850


  SELECT val_1
  FROM   ben_ext_crit_val
  WHERE  ext_crit_typ_id = p_crit_typ_id;

Line: 896


  SELECT val_1
  FROM   ben_ext_crit_val
  WHERE  ext_crit_typ_id = p_crit_typ_id;

Line: 944


  SELECT val_1, val_2
  FROM   ben_ext_crit_val
  WHERE  ext_crit_typ_id = p_crit_typ_id;

Line: 990


  SELECT val_1, val_2
  FROM   ben_ext_crit_val
  WHERE  ext_crit_typ_id = p_crit_typ_id;

Line: 1040


  SELECT ext_crit_val_id
  FROM   ben_ext_crit_val
  WHERE  ext_crit_typ_id = p_crit_typ_id
  ORDER BY ext_crit_val_id;

Line: 1046


  SELECT ext_crit_val_id,
         crit_typ_cd,
         oper_cd,
         val_1,
         val_2
  FROM   ben_ext_crit_cmbn
  WHERE  ext_crit_val_id = p_crit_val_id;

Line: 1109


  SELECT pl_id
  FROM   ben_popl_rptg_grp_f rg,
         ben_ext_crit_val xcv
  WHERE to_char(rg.rptg_grp_id) = xcv.val_1
               and xcv.ext_crit_typ_id = p_crit_typ_id
               and rg.pl_id is not null
               and ben_ext_person.g_benefits_ext_dt between rg.effective_start_date
                       and rg.effective_end_date;

Line: 1159


  SELECT ext_crit_typ_id, crit_typ_cd, excld_flag
  FROM   ben_ext_crit_typ
  WHERE  ext_crit_prfl_id = p_ext_crit_prfl_id
  ORDER BY excld_flag ;

Line: 1299


    elsif l_crit_typ_cd = 'BERLUD' then  -- enrollment result - last update date
      g_enrt_last_upd_dt_excld_flag := l_excld_flag;

Line: 1364


    elsif l_crit_typ_cd = 'BECLUD' then  -- electable choice - last update date
      g_elct_last_upd_dt_excld_flag := l_excld_flag;

Line: 1488


    elsif l_crit_typ_cd = 'MPCLUD' then  -- per communication last update date
      g_cm_last_upd_dt_excld_flag := l_excld_flag;

Line: 1494


    elsif l_crit_typ_cd = 'MPCPLUD' then  -- per communication provided last update date
      g_cm_pr_last_upd_dt_excld_flag := l_excld_flag;

Line: 1521


    elsif l_crit_typ_cd = 'EPLDT' then    -- premium last update date
            if g_debug then
              hr_utility.set_location('crit_type ' ||'EPLDT',16);

Line: 1636


    elsif l_crit_typ_cd = 'OTL_TC_DELETED' then
           Get_Incl_Crit_Values(l_crit_typ_id,
                           g_tc_deleted_incl_rqd,
                           g_tc_deleted_list);

Line: 1745


  SELECT postal_code
  FROM   per_addresses addr
  WHERE  addr.person_id = p_person_id
  AND    primary_flag = 'Y'
  AND    p_effective_date between date_from
         and nvl(date_to, p_effective_date);

Line: 1817


  SELECT organization_id
  FROM   per_all_assignments_f asn
  WHERE  asn.person_id = p_person_id
  and    asn.assignment_id = ben_ext_person.g_assignment_id   --1969853
  AND    asn.primary_flag = 'Y'
  AND    p_effective_date between effective_start_date
         and effective_end_date;

Line: 1887


  SELECT location_id
  FROM   per_all_assignments_f asn
  WHERE  asn.person_id = p_person_id
  and    asn.assignment_id = ben_ext_person.g_assignment_id   --1969853
  AND    asn.primary_flag = 'Y'
  AND    p_effective_date between effective_start_date
         and effective_end_date;

Line: 1956


  SELECT flex.segment1
  FROM   per_all_assignments_f asn, hr_soft_coding_keyflex flex
  WHERE  asn.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
  and    asn.assignment_id = ben_ext_person.g_assignment_id        --1969853
  AND    asn.person_id = p_person_id
  AND    asn.primary_flag = 'Y'
  AND    p_effective_date between asn.effective_start_date
         and asn.effective_end_date
  AND    p_effective_date between nvl(flex.start_date_active, p_effective_date)
         and nvl(flex.end_date_active, p_effective_date);

Line: 2028


  SELECT addr.region_2
  FROM   per_all_people_f per, per_addresses addr
  WHERE  per.person_id = addr.person_id
  AND    per.person_id = p_person_id
  AND    addr.primary_flag = 'Y'
  AND    p_effective_date between per.effective_start_date
         and per.effective_end_date
  AND    p_effective_date between addr.date_from
         and nvl(addr.date_to, p_effective_date);

Line: 2099


  SELECT benefit_group_id
  FROM   per_all_people_f per
  WHERE  per.person_id = p_person_id
  AND    p_effective_date between per.effective_start_date
         and per.effective_end_date;

Line: 2166


  SELECT assignment_status_type_id
  FROM   per_all_assignments_f asn
  WHERE  asn.person_id = p_person_id
  and   asn.assignment_id = ben_ext_person.g_assignment_id       --1969853
  AND    asn.primary_flag = 'Y'
  AND    p_effective_date between effective_start_date and effective_end_date;

Line: 2234


  SELECT payroll_id
  FROM   per_all_assignments_f asn
  WHERE  asn.person_id = p_person_id
  and   asn.assignment_id = ben_ext_person.g_assignment_id    --1969853
  AND    asn.primary_flag = 'Y'
  AND    p_effective_date between effective_start_date and effective_end_date;

Line: 2301


  SELECT asg.assignment_id, asg.business_group_id
  FROM   per_all_assignments_f asg
  WHERE  asg.person_id = p_person_id
   and   asg.assignment_id = ben_ext_person.g_assignment_id   --1969853
  AND    (asg.primary_flag = 'Y' or asg.assignment_type = 'A' )  -- if the asg type is A dont validate the primary flag
  AND    p_effective_date between asg.effective_start_date
         and asg.effective_end_date
  order by  decode(asg.primary_flag , 'Y', 1, 2)  ;

Line: 2383


  SELECT ler_id
  FROM   ben_per_in_ler pil
  WHERE  pil.person_id = p_person_id
  AND    pil.per_in_ler_stat_cd = 'STRTD';

Line: 2447


  SELECT person_type_id
  FROM   per_person_type_usages_f ptu
  WHERE  ptu.person_id = p_person_id
  AND    p_effective_date between ptu.effective_start_date and
             ptu.effective_end_date;

Line: 3155


(p_last_update_date in  ben_prtt_enrt_rslt_f.last_update_date%type,
 p_effective_date   in  date,
 p_excld_flag       in varchar2 ,
 p_pl_id            in number default null )
is
--
  l_proc      varchar2(72);

Line: 3186


      if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
         if p_excld_flag = 'N' then
           raise g_include;

Line: 3518


  select null
  from ben_per_cm_usg_f pcu,
       ben_cm_typ_usg_f ctu
  where pcu.per_cm_id = p_per_cm_id
  and   pcu.cm_typ_usg_id = ctu.cm_typ_usg_id
  and   nvl(ctu.pl_id,p_pl_id) = p_pl_id
  and   nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id
  and   nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id
  and   p_effective_date between pcu.effective_start_date and pcu.effective_end_date
  and   p_effective_date between ctu.effective_start_date and ctu.effective_end_date;

Line: 3943


(p_last_update_date   in ben_elig_per_elctbl_chc.last_update_date%type,
 p_effective_date     in date,
 p_excld_flag         in varchar2,
 p_pl_id              in number default null )
is
--
  l_proc      varchar2(72);

Line: 3972


      if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
         if p_excld_flag = 'N' then
           raise g_include;

Line: 4552


(p_last_update_login in ben_ext_chg_evt_log.last_update_login%type,
 p_excld_flag         in varchar2)
is
--
  l_proc      varchar2(72);

Line: 4567


      if p_last_update_login = g_chg_login_list(i) then
         if p_excld_flag = 'N' then
           raise g_include;

Line: 4652


(p_last_update_date in  ben_per_cm_f.last_update_date%type,
 p_effective_date in  date,
 p_excld_flag     in varchar2)
is
--
  l_proc      varchar2(72);

Line: 4678


      if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
         if p_excld_flag = 'N' then
           raise g_include;

Line: 4713


(p_pvdd_last_update_date  in  ben_per_cm_prvdd_f.last_update_date%type,
 p_effective_date in  date,
 p_excld_flag     in varchar2)
is
--
  l_proc      varchar2(72);

Line: 4739


      if p_pvdd_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
         if p_excld_flag = 'N' then
           raise g_include;

Line: 4856


(p_last_update_date  in  ben_prtt_prem_by_mo_f.last_update_date%type,
 p_effective_date in  date,
 p_excld_flag     in varchar2)
is
--
  l_proc      varchar2(72);

Line: 4885


         hr_utility.set_location(' update dte '||  p_last_update_date,16);

Line: 4887


      if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
         if p_excld_flag = 'N' then
           if g_debug then
             hr_utility.set_location('Y Exiting:'||l_proc, 15);

Line: 5306


Procedure chk_tc_deleted_incl
(p_tc_deleted      in  VARCHAR2
,p_excld_flag     in varchar2)
is
--
  l_proc      varchar2(72);

Line: 5316


    l_proc := g_package||'chk_tc_deleted_incl';

Line: 5322


    for i in 1..g_tc_deleted_list.count
    loop
      if p_tc_deleted = g_tc_deleted_list(i) then
         if p_excld_flag = 'N' then
           raise g_include;

Line: 5351


End chk_tc_deleted_incl;

Line: 5632


  SELECT enp.ASND_LF_EVT_DT
  FROM   ben_enrt_perd enp
  WHERE  enp.enrt_perd_id  = l_val
  ;

Line: 6055


  SELECT asg.assignment_id, asg.business_group_id
  FROM   per_all_assignments_f asg
  WHERE  asg.person_id = p_person_id
   and   asg.assignment_id = ben_ext_person.g_assignment_id   --1969853
  AND    asg.primary_flag = 'Y'
  AND    p_effective_date between asg.effective_start_date
         and asg.effective_end_date;

Line: 6328


 p_last_update_date in ben_prtt_enrt_rslt_f.last_update_date%type default null,
 p_ler_id    in ben_per_in_ler.ler_id%type default null,
 p_ntfn_dt          in ben_per_in_ler.ntfn_dt%type default null,
 p_lf_evt_ocrd_dt   in ben_per_in_ler.lf_evt_ocrd_dt%type default null,
 p_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type default null,
 p_per_in_ler_id    in ben_per_in_ler.per_in_ler_id%type default null,
 p_prtt_enrt_rslt_id in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type default null,
 p_effective_date   in date default null,
 p_dpnt_id          in number default null,
 p_include          out nocopy varchar2)
is
--
  l_proc      varchar2(72);

Line: 6410


      (p_last_update_date => p_last_update_date,
       p_effective_date   => p_effective_date,
       p_excld_flag       => g_enrt_last_upd_dt_excld_flag ,
       p_pl_id            => p_pl_id );

Line: 6484


 p_elct_last_upd_dt   in ben_elig_per_elctbl_chc.last_update_date%type default null,
 p_elct_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type default null,
 p_elct_ler_id    in ben_per_in_ler.ler_id%type default null,
 p_elct_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type default null,
 p_elct_lf_evt_ocrd_dt   in ben_per_in_ler.lf_evt_ocrd_dt%type default null,
 p_elct_ntfn_dt          in ben_per_in_ler.ntfn_dt%type default null,
 p_prtt_enrt_rslt_id in ben_elig_per_elctbl_chc.prtt_enrt_rslt_id%type default null,
 p_effective_date   in date default null,
 p_include          out nocopy varchar2)

is
--
  l_proc      varchar2(72);

Line: 6552


      (p_last_update_date => p_elct_last_upd_dt,
       p_effective_date   => p_effective_date,
       p_excld_flag       => g_elct_last_upd_dt_excld_flag,
       p_pl_id            => p_elct_pl_id );

Line: 6615


   p_last_update_login in ben_ext_chg_evt_log.last_update_login%type,
   p_effective_date    in date default null,
   p_include           out nocopy varchar2)
  is
--
  l_proc      varchar2(72);

Line: 6652


  if g_chg_login_incl_rqd = 'Y' and  ( p_chg_evt_source = 'BEN' or p_last_update_login  is not null   )  then
    chk_chg_login_incl
      (p_last_update_login => p_last_update_login,
       p_excld_flag        => g_chg_login_excld_flag);

Line: 6677


   p_last_update_date in ben_per_cm_f.last_update_date%type,
   p_pvdd_last_update_date in ben_per_cm_prvdd_f.last_update_date%type,
   p_sent_dt          in ben_per_cm_prvdd_f.sent_dt%type,
   p_to_be_sent_dt    in ben_per_cm_prvdd_f.to_be_sent_dt%type,
   p_effective_date   in date default null,
   p_include          out nocopy varchar2)
  is
--
  l_proc      varchar2(72);

Line: 6704


      (p_last_update_date => p_last_update_date,
       p_effective_date   => p_effective_date,
       p_excld_flag       => g_cm_last_upd_dt_excld_flag);

Line: 6710


      (p_pvdd_last_update_date => p_pvdd_last_update_date,
       p_effective_date   => p_effective_date,
       p_excld_flag       => g_cm_pr_last_upd_dt_excld_flag);

Line: 6749


  (p_last_update_date in ben_prtt_prem_by_mo_f.last_update_date%type,
   p_mo_num           in ben_prtt_prem_by_mo_f.mo_num%type default null ,
   p_yr_num           in ben_prtt_prem_by_mo_f.yr_num%type default null,
   p_effective_date   in date default null,
   p_include          out nocopy varchar2)
  is
--
  l_proc      varchar2(72);

Line: 6770


        (p_last_update_date  => p_last_update_date,
         p_effective_date    => p_effective_date,
         p_excld_flag        => g_prem_last_updt_dt_excld_flag);

Line: 7151


,p_tc_deleted          IN VARCHAR2 DEFAULT NULL
,p_project_id          IN VARCHAR2 DEFAULT NULL
,p_task_id             IN VARCHAR2 DEFAULT NULL
,p_exp_typ_id          IN VARCHAR2 DEFAULT NULL
,p_element_type_id     IN VARCHAR2 DEFAULT NULL
,p_po_num              IN VARCHAR2 DEFAULT NULL
,p_include          out nocopy varchar2)
is
--
  l_proc      varchar2(72);

Line: 7182


	IF ( g_tc_deleted_incl_rqd = 'Y' )
	THEN
		chk_tc_deleted_incl
			( p_tc_deleted  => p_tc_deleted
			, p_excld_flag  => g_tc_deleted_excld_flag );