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.

[Home] [Help]

APPS.BEN_PER_ASG_EIT_LER SQL Statements

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

Line: 34


select effective_date
from   fnd_sessions
where  session_id = userenv('SESSIONID');

Line: 39


select trunc(sysdate)
from   dual;

Line: 43


 select ler.ler_id
 ,      ler.typ_cd
 ,      ler.ocrd_dt_det_cd
 from   ben_ler_f ler
 where  ler.business_group_id               = l_business_group_id------
 and   l_session_date between ler.effective_start_date
 and   ler.effective_end_date
 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
 and  (  (exists
        (select 1
          from   ben_per_info_chg_cs_ler_f psl
          ,      ben_ler_per_info_cs_ler_f lpl
          where  source_table               = 'PER_PEOPLE_EXTRA_INFO'
          and    psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
          and    lpl.business_group_id    = psl.business_group_id
          and    lpl.business_group_id    = ler.business_group_id
          and    psl.source_column              = nvl(p_new.information_type,p_old.information_type)
          and    l_session_date between psl.effective_start_date and    psl.effective_end_date
          and    l_session_date between lpl.effective_start_date and    lpl.effective_end_date
          and    lpl.ler_id                 = ler.ler_id)
         )
 OR      (exists
          (select 1
           from   ben_rltd_per_chg_cs_ler_f rpc
           ,      ben_ler_rltd_per_cs_ler_f lrp
           where  source_table               = 'PER_PEOPLE_EXTRA_INFO'
           and    lrp.business_group_id    = rpc.business_group_id
           and    lrp.business_group_id    = ler.business_group_id
           and    rpc.source_column              = nvl(p_new.information_type,p_old.information_type)
           and    l_session_date between rpc.effective_start_date and rpc.effective_end_date
           and    l_session_date between lrp.effective_start_date and lrp.effective_end_date
           and    rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
           and    lrp.ler_id                 = ler.ler_id)
           )
      )
  order by ler.ler_id;

Line: 81


select psl.source_column
,      psl.new_val
,      psl.old_val
,      'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
from   ben_per_info_chg_cs_ler_f psl
,      ben_ler_per_info_cs_ler_f lpl
where  lpl.ler_id                     = p_ler_id
and    lpl.business_group_id          = l_business_group_id
and    lpl.business_group_id          = psl.business_group_id
and    psl.source_column              = nvl(p_new.information_type,p_old.information_type)
and    l_session_date between psl.effective_start_date and psl.effective_end_date
and    psl.per_info_chg_cs_ler_id     = lpl.per_info_chg_cs_ler_id
and    source_table                   = 'PER_PEOPLE_EXTRA_INFO'
and    l_session_date between psl.effective_start_date and psl.effective_end_date
and    l_session_date between lpl.effective_start_date and lpl.effective_end_date
UNION
select rpc.source_column
,      rpc.new_val
,      rpc.old_val
	,      'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
from   ben_ler_rltd_per_cs_ler_f lrp
,      ben_rltd_per_chg_cs_ler_f rpc
where  lrp.ler_id = p_ler_id
and    lrp.business_group_id          = l_business_group_id
and    lrp.business_group_id          = rpc.business_group_id
and    rpc.source_column              = nvl(p_new.information_type,p_old.information_type)
and    l_session_date between rpc.effective_start_date and rpc.effective_end_date
and    rpc.rltd_per_chg_cs_ler_id     = lrp.rltd_per_chg_cs_ler_id
and    source_table                   = 'PER_PEOPLE_EXTRA_INFO'
and    l_session_date between rpc.effective_start_date and rpc.effective_end_date
and    l_session_date between lrp.effective_start_date and lrp.effective_end_date
order by 1;

Line: 117


select 'Y'
from   ben_ptnl_ler_for_per
where  person_id                             = p_person_id
and    ler_id                                = p_ler_id
and    ptnl_ler_for_per_stat_cd              = 'DTCTD'
and    lf_evt_ocrd_dt                        = p_lf_evt_ocrd_dt;

Line: 126


select person_id
from per_contact_relationships
where contact_person_id = p_person_id
and business_group_id = l_business_group_id
and l_session_date between nvl(date_start,l_session_date)
and nvl(date_end,l_session_date)
and personal_flag = 'Y'
order by person_id;

Line: 137


select business_group_id from per_all_people_f where person_id = p_person_id;

Line: 280


      if p_mode <> 'UPDATE' THEN
      l_changed := (benutils.column_changed(p_old.information_type
                          ,p_new.information_type,l_new_val) AND
                           benutils.column_changed(p_new.information_type
                          ,p_old.information_type,l_old_val) AND
                          (l_changed));

Line: 288


      if p_mode = 'UPDATE' THEN
         if l_old_val = 'NULL' or l_new_val = 'NULL'  THEN
            l_changed := FALSE;

Line: 413


select effective_date
from   fnd_sessions
where  session_id = userenv('SESSIONID');

Line: 418


select trunc(sysdate)
from   dual;

Line: 422


 select ler.ler_id
 ,      ler.typ_cd
 ,      ler.ocrd_dt_det_cd
 from   ben_ler_f ler
 where  ler.business_group_id               = l_business_group_id------
 and   l_session_date between ler.effective_start_date
 and   ler.effective_end_date
 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
 and  (  (exists
        (select 1
          from   ben_per_info_chg_cs_ler_f psl
          ,      ben_ler_per_info_cs_ler_f lpl
          where  source_table               = 'PER_ASSIGNMENT_EXTRA_INFO'
          and    psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
          and    lpl.business_group_id      = psl.business_group_id
          and    lpl.business_group_id      = ler.business_group_id
          and    psl.source_column          =nvl(p_new.information_type,p_old.information_type)
          and    l_session_date between psl.effective_start_date and    psl.effective_end_date
          and    l_session_date between lpl.effective_start_date and    lpl.effective_end_date
          and    lpl.ler_id                 = ler.ler_id)
         )
 OR      (exists
          (select 1
           from   ben_rltd_per_chg_cs_ler_f rpc
           ,      ben_ler_rltd_per_cs_ler_f lrp
           where  source_table               = 'PER_ASSIGNMENT_EXTRA_INFO'
           and    lrp.business_group_id      = rpc.business_group_id
           and    lrp.business_group_id      = ler.business_group_id
           and    rpc.source_column          = nvl(p_new.information_type,p_old.information_type)
           and    l_session_date between rpc.effective_start_date and rpc.effective_end_date
           and    l_session_date between lrp.effective_start_date and lrp.effective_end_date
           and    rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
           and    lrp.ler_id                 = ler.ler_id)
           )
      )
  order by ler.ler_id;

Line: 460


select psl.source_column
,      psl.new_val
,      psl.old_val
,      'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
from   ben_per_info_chg_cs_ler_f psl
,      ben_ler_per_info_cs_ler_f lpl
where  lpl.ler_id                     = p_ler_id
and    lpl.business_group_id          = l_business_group_id
and    lpl.business_group_id          = psl.business_group_id
and    psl.source_column              =nvl(p_new.information_type,p_old.information_type)
and    l_session_date between psl.effective_start_date and psl.effective_end_date
and    psl.per_info_chg_cs_ler_id     = lpl.per_info_chg_cs_ler_id
and    source_table                   = 'PER_ASSIGNMENT_EXTRA_INFO'
and    l_session_date between psl.effective_start_date and psl.effective_end_date
and    l_session_date between lpl.effective_start_date and lpl.effective_end_date
UNION
select rpc.source_column
,      rpc.new_val
,      rpc.old_val
	,      'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
from   ben_ler_rltd_per_cs_ler_f lrp
,      ben_rltd_per_chg_cs_ler_f rpc
where  lrp.ler_id = p_ler_id
and    lrp.business_group_id          = l_business_group_id
and    lrp.business_group_id          = rpc.business_group_id
and    rpc.source_column              = nvl(p_new.information_type,p_old.information_type)
and    l_session_date between rpc.effective_start_date and rpc.effective_end_date
and    rpc.rltd_per_chg_cs_ler_id     = lrp.rltd_per_chg_cs_ler_id
and    source_table                   = 'PER_ASSIGNMENT_EXTRA_INFO'
and    l_session_date between rpc.effective_start_date and rpc.effective_end_date
and    l_session_date between lrp.effective_start_date and lrp.effective_end_date
order by 1;

Line: 496


select 'Y'
from   ben_ptnl_ler_for_per
where  person_id                             = p_person_id
and    ler_id                                = p_ler_id
and    ptnl_ler_for_per_stat_cd              = 'DTCTD'
and    lf_evt_ocrd_dt                        = p_lf_evt_ocrd_dt;

Line: 505


select person_id
from per_contact_relationships
where contact_person_id = p_person_id
and business_group_id = l_business_group_id
and l_session_date between nvl(date_start,l_session_date)
and nvl(date_end,l_session_date)
and personal_flag = 'Y'
order by person_id;

Line: 516


select business_group_id from per_all_assignments_f where assignment_id = p_assignment_id;

Line: 520


SELECT  primary_flag
       ,person_id
FROM    per_all_assignments_f paf
WHERE   assignment_id = p_assignment_id
AND     l_effective_start_date BETWEEN paf.effective_start_date
                               AND     paf.effective_end_date;

Line: 673


      if p_mode <> 'UPDATE' THEN
      l_changed := (benutils.column_changed(p_old.information_type
                          ,p_new.information_type,l_new_val) AND
                           benutils.column_changed(p_new.information_type
                          ,p_old.information_type,l_old_val) AND
                          (l_changed));

Line: 681


      if p_mode = 'UPDATE' THEN
         if l_old_val = 'NULL' or l_new_val = 'NULL'  THEN
            l_changed := FALSE;

Line: 825


procedure update_ler_per (p_person_extra_info_id in number)
is

l_proc varchar2(200) := 'ben_per_asg_eit_ler.update_ler_per';

Line: 832


SELECT  person_extra_info_id
       ,person_id
       ,information_type
FROM    per_people_extra_info
WHERE   person_extra_info_id = p_person_extra_info_id;

Line: 871


																	p_mode => 'UPDATE'
																	);

Line: 878


end update_ler_per;

Line: 881


procedure delete_ler_per (p_person_extra_info_id In number)
is

l_proc varchar2(200) := 'ben_per_asg_eit_ler.delete_ler_per';

Line: 909


																	p_mode => 'DELETE'
																	);

Line: 916


end delete_ler_per;

Line: 955


procedure update_ler_asg (p_assignment_extra_info_id In number)
is

l_proc varchar2(200) := 'ben_per_asg_eit_ler.update_ler_asg';

Line: 962


SELECT  assignment_extra_info_id
       ,assignment_id
       ,information_type
FROM    per_assignment_extra_info
WHERE   assignment_extra_info_id = p_assignment_extra_info_id;

Line: 1001


																p_mode => 'UPDATE'
																);

Line: 1008


end update_ler_asg;

Line: 1011


procedure delete_ler_asg (p_assignment_extra_info_id In number)
is

l_proc varchar2(200) := 'ben_per_asg_eit_ler.delete_ler_asg';

Line: 1039


																p_mode => 'DELETE'
																);

Line: 1046


end delete_ler_asg;