The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_tc_deleted_excld_flag := 'N';
g_tc_deleted_incl_rqd := 'N';
for i in 1..g_tc_deleted_list.count loop
if g_tc_deleted_list.exists(i) then
g_tc_deleted_list(i) := null;
SELECT val_1
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = p_crit_typ_id;
SELECT val_1
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = p_crit_typ_id;
SELECT val_1, val_2
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = p_crit_typ_id;
SELECT val_1, val_2
FROM ben_ext_crit_val
WHERE ext_crit_typ_id = p_crit_typ_id;
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;
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;
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;
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 ;
elsif l_crit_typ_cd = 'BERLUD' then -- enrollment result - last update date
g_enrt_last_upd_dt_excld_flag := l_excld_flag;
elsif l_crit_typ_cd = 'BECLUD' then -- electable choice - last update date
g_elct_last_upd_dt_excld_flag := l_excld_flag;
elsif l_crit_typ_cd = 'MPCLUD' then -- per communication last update date
g_cm_last_upd_dt_excld_flag := l_excld_flag;
elsif l_crit_typ_cd = 'MPCPLUD' then -- per communication provided last update date
g_cm_pr_last_upd_dt_excld_flag := l_excld_flag;
elsif l_crit_typ_cd = 'EPLDT' then -- premium last update date
if g_debug then
hr_utility.set_location('crit_type ' ||'EPLDT',16);
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);
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);
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;
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;
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);
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);
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;
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;
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;
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) ;
SELECT ler_id
FROM ben_per_in_ler pil
WHERE pil.person_id = p_person_id
AND pil.per_in_ler_stat_cd = 'STRTD';
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;
(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);
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;
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;
(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);
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;
(p_last_update_login in ben_ext_chg_evt_log.last_update_login%type,
p_excld_flag in varchar2)
is
--
l_proc varchar2(72);
if p_last_update_login = g_chg_login_list(i) then
if p_excld_flag = 'N' then
raise g_include;
(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);
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;
(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);
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;
(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);
hr_utility.set_location(' update dte '|| p_last_update_date,16);
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);
Procedure chk_tc_deleted_incl
(p_tc_deleted in VARCHAR2
,p_excld_flag in varchar2)
is
--
l_proc varchar2(72);
l_proc := g_package||'chk_tc_deleted_incl';
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;
End chk_tc_deleted_incl;
SELECT enp.ASND_LF_EVT_DT
FROM ben_enrt_perd enp
WHERE enp.enrt_perd_id = l_val
;
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;
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);
(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 );
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);
(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 );
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);
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);
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);
(p_last_update_date => p_last_update_date,
p_effective_date => p_effective_date,
p_excld_flag => g_cm_last_upd_dt_excld_flag);
(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);
(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);
(p_last_update_date => p_last_update_date,
p_effective_date => p_effective_date,
p_excld_flag => g_prem_last_updt_dt_excld_flag);
,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);
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 );